WEBUI/WMS.BZServices/SX/BillTimeOutRecordService.cs

using Mapster;
using NPOI.SS.Formula.Functions;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using wms.sqlsugar.model.sx;
using WMS.BZModels;
using WMS.BZModels.Dto.SX.BilltimeoutrecordDtos;
using WMS.BZModels.Models.SX;
using WMS.BZSqlSugar;
using WMS.Info;
using WMS.Util;

namespace WMS.BZServices.SX
{
    public class BillTimeOutRecordService
    {
        private readonly Repository<BillTimeOutRecord> _billTimeOutRecordRepository;
        private readonly Repository<BillInvnow> _billInvnowrepository;



        public BillTimeOutRecordService(Repository<BillTimeOutRecord> billTimeOutRecordRepository, Repository<BillInvnow> billInvnowrepository)
        {
            _billTimeOutRecordRepository = billTimeOutRecordRepository;
            _billInvnowrepository = billInvnowrepository;

        }

        public PagedInfo<BillTimeOutRecordReportDto> GetReportPageList(Pagination pagination, BillTimeOutRecordQueryDto BillTimeOutRecordQueryDto)
        {
            ISugarQueryable<BillTimeOutRecord> billTimeOutRecordQuery = GetQueryable(BillTimeOutRecordQueryDto);
            var invnowQuery = _billInvnowrepository.Queryable().With(SqlWith.NoLock);

            var lists = billTimeOutRecordQuery.LeftJoin(invnowQuery, (billTimeOutRecord, invnow) => billTimeOutRecord.ContGrpBarCode == invnow.ContGrpBarCode)
                .Where((billTimeOutRecord, invnow) => invnow.InvStateCode == "InvEcecState_In")
                .Where((billTimeOutRecord, invnow) => SqlFunc.DateDiff(DateType.Hour, invnow.ProductTime ,DateTime.Now)>=72 )
                .Where((billTimeOutRecord, invnow) => !invnow.InDocsNo.StartsWith("CHA"))
                //   and DATEDIFF(hour , stock.[ProductTime], GETDATE()) >= 72
                .GroupBy((billTimeOutRecord, invnow) => new { billTimeOutRecord.Reason })
                           .Select((billTimeOutRecord, invnow) => new BillTimeOutRecordReportDto
                           {
                               Reason = billTimeOutRecord.Reason,
                               Qty = SqlFunc.AggregateCount(billTimeOutRecord.Reason)
                           }).MergeTable().ToPage(pagination);

            return lists;
        }

        private ISugarQueryable<BillTimeOutRecord> GetQueryable(BillTimeOutRecordQueryDto BillTimeOutRecordQueryDto)
        {
            var predicate = Expressionable.Create<BillTimeOutRecord>();

            predicate = predicate.AndIF(!string.IsNullOrEmpty(BillTimeOutRecordQueryDto?.KeyWord), m => m.ContGrpBarCode.Contains(BillTimeOutRecordQueryDto.KeyWord) || m.Reason.Contains(BillTimeOutRecordQueryDto.KeyWord));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(BillTimeOutRecordQueryDto?.ContGrpBarCode), m => m.ContGrpBarCode.Contains(BillTimeOutRecordQueryDto.ContGrpBarCode));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(BillTimeOutRecordQueryDto?.Reason), m => m.Reason.Contains(BillTimeOutRecordQueryDto.Reason));

            predicate = predicate.AndIF(BillTimeOutRecordQueryDto != null && BillTimeOutRecordQueryDto.AddTimeBegin.HasValue, m => m.AddTime >= BillTimeOutRecordQueryDto.AddTimeBegin);
            predicate = predicate.AndIF(BillTimeOutRecordQueryDto != null && BillTimeOutRecordQueryDto.AddTimeEnd.HasValue, m => m.AddTime <= BillTimeOutRecordQueryDto.AddTimeEnd);

            predicate = predicate.AndIF(BillTimeOutRecordQueryDto != null && BillTimeOutRecordQueryDto.EditTimeBegin.HasValue, m => m.AddTime >= BillTimeOutRecordQueryDto.EditTimeBegin);
            predicate = predicate.AndIF(BillTimeOutRecordQueryDto != null && BillTimeOutRecordQueryDto.EditTimeEnd.HasValue, m => m.AddTime <= BillTimeOutRecordQueryDto.EditTimeEnd);

            var billTimeOutRecordQuery = _billTimeOutRecordRepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(p => p.Take(3));
            if (BillTimeOutRecordQueryDto != null && BillTimeOutRecordQueryDto.AddTimeBegin.HasValue)
            {
                var endtime = DateTime.Now;
                if (BillTimeOutRecordQueryDto.AddTimeEnd.HasValue)
                {
                    endtime = BillTimeOutRecordQueryDto.AddTimeEnd.Value;
                }
                billTimeOutRecordQuery = _billTimeOutRecordRepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(BillTimeOutRecordQueryDto.AddTimeBegin.Value, endtime);
            }

            return billTimeOutRecordQuery;
        }

        public PagedInfo<BillTimeOutRecordDto> GetPageList(Pagination pagination, BillTimeOutRecordQueryDto billTimeOutRecordQueryDto)
        {

            ISugarQueryable<BillTimeOutRecord> billTimeOutRecordQuery = GetQueryable(billTimeOutRecordQueryDto);
            // var lists = billTimeOutRecordQuery.OrderBy(o => o.AddTime, OrderByType.Asc).ToList().Adapt<List<BillTimeOutRecordDto>>();

            var invnowQuery = _billInvnowrepository.Queryable().With(SqlWith.NoLock);

            var lists = billTimeOutRecordQuery.LeftJoin(invnowQuery, (billTimeOutRecord, invnow) => billTimeOutRecord.ContGrpBarCode == invnow.ContGrpBarCode)
                .Where((billTimeOutRecord, invnow) => invnow.InvStateCode == "InvEcecState_In")
                .Where((billTimeOutRecord, invnow) => SqlFunc.DateDiff(DateType.Hour, invnow.ProductTime, DateTime.Now) >= 72)
                .Where((billTimeOutRecord, invnow) => !invnow.InDocsNo.StartsWith("CHA"))
                .Select((billTimeOutRecord, invnow) => billTimeOutRecord).MergeTable()
               .ToPage<BillTimeOutRecord, BillTimeOutRecordDto>(pagination);

            return lists;

        }
    }
}