WEBUI/WMS.BZServices/CP/ReportService.cs

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WMS.BZModels;
using WMS.Info;
using wms.sqlsugar.model.cp;
using WMS.BZSqlSugar;
using WMS.Util;
using WMS.BZModels.Dto.CP.TaskDtos;
using NPOI.SS.Formula.Functions;
using WMS.BZModels.Dto.CP.RfidDtos;
using WMS.BZModels.Dto.CP.BaseWareareaDtos;
using WMS.BZModels.Dto.CP.BillInvDtos;
using WMS.BZModels.Dto.CP.ReportDtos;

namespace WMS.BZServices.CP
{
    public class ReportService
    {
        private readonly Repository<BillInvflow> _reportrepository;
        private readonly Repository<BaseWarecell> _warecellRepository;
        private readonly Repository<BillInvnow> _billInvnowrepository;

        public ReportService(Repository<BillInvflow> reportrepository, Repository<BaseWarecell> warecellRepository, Repository<BillInvnow> billInvnowrepository)
        {
            _reportrepository = reportrepository;
            _warecellRepository = warecellRepository;
            _billInvnowrepository = billInvnowrepository;
        }

        public PagedInfo<ReportDto> GetPageList(Pagination pagination, ReportQueryDto reportQueryDto)
        {
            if (pagination.sord.ToUpper() != "ASC")
            {
                pagination.sidx = pagination.sidx.IsEmpty() ? "AddTime DESC" : pagination.sidx + " DESC";

            }
            if (pagination.sidx.IsEmpty())
            {
                pagination.sidx = "AddTime DESC";
            }
            var list = GetQueryable(reportQueryDto).ToPage(pagination);
            return list;
        }
        private ISugarQueryable<ReportDto> GetQueryable(ReportQueryDto reportQueryDto)
        {
            var predicate = Expressionable.Create<BillInvflow, BaseWarehouse, BaseWarecell>();

            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.KeyWord), (billInvflow, wareHouse, wareCell) => billInvflow.ContGrpBarCode.Contains(reportQueryDto.KeyWord) || billInvflow.MatCode.Contains(reportQueryDto.KeyWord) ||
            billInvflow.MatName.Contains(reportQueryDto.KeyWord) || billInvflow.BoxBarCode.Contains(reportQueryDto.KeyWord) || wareCell.Code.Contains(reportQueryDto.KeyWord));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.ContGrpBarCode), (billInvflow, wareHouse, wareCell) => billInvflow.ContGrpBarCode.Contains(reportQueryDto.ContGrpBarCode));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.MatCode), (billInvflow, wareHouse, wareCell) => billInvflow.MatCode.Contains(reportQueryDto.MatCode));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.MatName), (billInvflow, wareHouse, wareCell) => billInvflow.MatName.Contains(reportQueryDto.MatName));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.RFIDBarCode), (billInvflow, wareHouse, wareCell) => billInvflow.BoxBarCode.Contains(reportQueryDto.RFIDBarCode));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.CellCode), (billInvflow, wareHouse, wareCell) => wareCell.Code.Contains(reportQueryDto.CellCode));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.Tunnel), (billInvflow, wareHouse, wareCell) => wareCell.Tunnel == int.Parse(reportQueryDto.Tunnel));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.InvBarCode), (billInvflow, wareHouse, wareCell) => billInvflow.InvBarCode.Contains(reportQueryDto.InvBarCode));
            predicate = predicate.AndIF(reportQueryDto != null && reportQueryDto.AddTimeFrom.HasValue, (billInvflow, wareHouse, wareCell) => billInvflow.AddTime >= reportQueryDto.AddTimeFrom);
            predicate = predicate.AndIF(reportQueryDto != null && reportQueryDto.AddTimeTo.HasValue, (billInvflow, wareHouse, wareCell) => billInvflow.AddTime <= reportQueryDto.AddTimeTo);
            predicate = predicate.AndIF(reportQueryDto != null && reportQueryDto.EndTimeBegin.HasValue, (billInvflow, wareHouse, wareCell) => billInvflow.EditTime >= reportQueryDto.EndTimeBegin);
            predicate = predicate.AndIF(reportQueryDto != null && reportQueryDto.EndTimeEnd.HasValue, (billInvflow, wareHouse, wareCell) => billInvflow.EditTime <= reportQueryDto.EndTimeEnd);

            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.IsSurplus), (billInvflow, wareHouse, wareCell) => billInvflow.IsSurplus.Equals(reportQueryDto.IsSurplus));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.IsRework), (billInvflow, wareHouse, wareCell) => billInvflow.IsRework.Equals(reportQueryDto.IsRework));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.IsFast), (billInvflow, wareHouse, wareCell) => billInvflow.IsFast.Equals(reportQueryDto.IsFast));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.IsFail), (billInvflow, wareHouse, wareCell) => billInvflow.IsFail.Equals(reportQueryDto.IsFail));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.IsBack), (billInvflow, wareHouse, wareCell) => billInvflow.IsBack.Equals(reportQueryDto.IsBack));
            if (!string.IsNullOrEmpty(reportQueryDto?.InvStateCode))
            {
                var enums = (InvState)Enum.ToObject(typeof(InvState), Convert.ToInt32(reportQueryDto?.InvStateCode));
                predicate = predicate.AndIF(!string.IsNullOrEmpty(reportQueryDto?.InvStateCode), (billInvflow, wareHouse, wareCell) => billInvflow.InvStateCode.Equals(enums.ToString()));
            }

            var queryable = _reportrepository.Context.Queryable<BillInvflow, BaseWarehouse, BaseWarecell>((billInvflow, wareHouse, wareCell) => new object[] {
                JoinType.Left, billInvflow.WarehouseId == wareHouse.Id,
                JoinType.Left, billInvflow.PutRow == wareCell.Row && billInvflow.PutCol == wareCell.Col && billInvflow.PutLayer == wareCell.Layer,
                 JoinType.Left,wareCell.WarehouseId==wareHouse.Id
            }).Where(predicate.ToExpression())

            .Select((billInvflow, wareHouse, wareCell) => new ReportDto
            {
                Id = billInvflow.Id.ToString(),
                WarehouseName = wareHouse.Name,
                Tunnel = wareCell.Tunnel,
                Memo = billInvflow.Memo,
                Code = wareCell.Code,
                ContGrpId = billInvflow.ContGrpId.ToString(),
                ContGrpBarCode = billInvflow.ContGrpBarCode,
                ContGrpType = billInvflow.ContGrpType,
                InvStateCode = billInvflow.InvStateCode,
                BoxBarCode = billInvflow.BoxBarCode,
                BomDocsNo = billInvflow.BomDocsNo,
                BomMatId = billInvflow.BomMatId.ToString(),
                BomMatCode = billInvflow.BomMatCode,
                BomMatName = billInvflow.BomMatName,
                BomSetId = billInvflow.BomSetId.ToString(),
                ExecStateCode = billInvflow.ExecStateCode,
                ExecDocsNo = billInvflow.ExecDocsNo,
                ExecDocsRowNo = billInvflow.ExecDocsRowNo,
                ExecDocsTypeCode = billInvflow.ExecDocsTypeCode,
                InvBarCode = billInvflow.InvBarCode,
                InDocsNo = billInvflow.InDocsNo,
                InDocsRowNo = billInvflow.InDocsRowNo,
                SuppCode = billInvflow.SuppCode,
                SuppName = billInvflow.SuppName,
                CustCode = billInvflow.CustCode,
                CustName = billInvflow.CustName,
                IsFast = billInvflow.IsFast,
                IsFail = billInvflow.IsFail,
                FailReason = billInvflow.FailReason,
                PutRow = billInvflow.PutRow,
                PutCol = billInvflow.PutCol,
                PutLayer = billInvflow.PutLayer,
                MatId = billInvflow.MatId,
                MatCode = billInvflow.MatCode,
                MatName = billInvflow.MatName,
                TolWQty = billInvflow.TolWQty,
                NetWQty = billInvflow.NetWQty,
                TareWQty = billInvflow.TareWQty,
                LengthQty = billInvflow.LengthQty,
                CaQty = billInvflow.CaQty,
                SolderQty = billInvflow.SolderQty,
                ContUsageQty = billInvflow.ContUsageQty,
                BatchNo = billInvflow.BatchNo,
                ProductTime = billInvflow.ProductTime,
                OneInTime = billInvflow.OneInTime,
                RodBarCode = billInvflow.RodBarCode,
                HWBarCode = billInvflow.HWBarCode,
                RFIDBarCode = billInvflow.RFIDBarCode,
                CLBarCode = billInvflow.CLBarCode,
                HWTypeCode = billInvflow.HWTypeCode,
                BoilerNo = billInvflow.BoilerNo,
                PackNo = billInvflow.PackNo,
                BrandNo = billInvflow.BrandNo,
                ExecStd = billInvflow.ExecStd,
                LicenceCode = billInvflow.LicenceCode,
                SilkTypeCode = billInvflow.SilkTypeCode,
                Grade = billInvflow.Grade,
                IsBack = billInvflow.IsBack,
                BackReason = billInvflow.BackReason,
                ProcessDocsCode = billInvflow.ProcessDocsCode,
                ProductMachCode = billInvflow.ProductMachCode,
                ProductLineNo = billInvflow.ProductLineNo,
                AddTime = billInvflow.AddTime,
                EditTime = billInvflow.EditTime,
                HuTotalWt = billInvflow.HuTotalWt,
            }).MergeTable();
            return queryable;
        }

        public IList<ReportDto> GetList(ReportQueryDto reportQueryDto)
        {
            ISugarQueryable<ReportDto> sugarQueryable = GetQueryable(reportQueryDto);
            var list = sugarQueryable.ToList();
            return list;
        }

        /// <summary>
        /// 货位分析
        /// </summary>
        /// <returns></returns>
        public List<LocationUsageReportViewDto> GetLocationUsageReportList()
        {
            var list = _warecellRepository.Context.Queryable<BaseWarecell, BaseWarehouse>((warecell, warehouse) => new object[] {
                JoinType.Left,warecell.WarehouseId == warehouse.Id   })
                .GroupBy((warecell, warehouse) => new
                {
                    warecell.StateNum,
                    warecell.IsStop,
                    warecell.Tunnel,
                    warehouse.Id,
                    warehouse.Name
                }).Select((warecell, warehouse) => new
                {
                    Status = warecell.StateNum,
                    warecell.Tunnel,
                    warecell.IsStop,
                    HouseId = warehouse.Id,
                    warehouse.Name,
                    Total = SqlFunc.AggregateCount(warecell.Tunnel),
                })
               .MergeTable().ToList().OrderBy(o => o.Tunnel);
            int _sort = 1;
            //基表：巷道、货架标识和仓库名称为维度查找总货位
            var baseTemp = list.GroupBy(s => new { s.Tunnel, s.HouseId, s.Name }).Select(m => new { m.Key.Name, m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找可用货位：未停用.已锁定也算有效货位
            var useLocation = list.Where(s => s.IsStop == 0).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name }).Select(m => new { m.Key.Name, m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找锁定货位
            var lockLocation = list.Where(s => s.Status == (int)LocationState.LocationState_StockIn || s.Status == (int)LocationState.LocationState_StockOut || s.Status == (int)LocationState.LocationState_StockMove).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name }).Select(m => new { m.Key.Name, m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找停用货位
            var stopLocation = list.Where(s => s.IsStop == 1).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name }).Select(m => new { m.Key.Name, m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找物料货位
            var materialLocation = list.Where(s => s.Status == (int)LocationState.LocationState_Full).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name }).Select(m => new { m.Key.Name, m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            var locationUsageReportViewDtos = from basetemp in baseTemp
                                              join uselocation in useLocation on new { basetemp.Tunnel, basetemp.Name } equals new { uselocation.Tunnel, uselocation.Name } into useinfo
                                              join locklocation in lockLocation on new { basetemp.Tunnel, basetemp.Name } equals new { locklocation.Tunnel, locklocation.Name } into lockinfo
                                              from lockinfoif in lockinfo.DefaultIfEmpty()
                                              join stoplocation in stopLocation on new { basetemp.Tunnel, basetemp.Name } equals new { stoplocation.Tunnel, stoplocation.Name } into stopinfo
                                              from stopinfoif in stopinfo.DefaultIfEmpty()
                                              join materiallocation in materialLocation on new { basetemp.Tunnel, basetemp.Name } equals new { materiallocation.Tunnel, materiallocation.Name } into materialinfo
                                              from materialinfoif in materialinfo.DefaultIfEmpty()
                                              orderby basetemp.Name
                                              select new LocationUsageReportViewDto()
                                              {
                                                  Sort = _sort++,
                                                  Tunnel = basetemp.Tunnel.ToString(),
                                                  WarehouseName = basetemp.Name,
                                                  AllLocationTotal = basetemp.Total,
                                                  CanUseLocation = useinfo.FirstOrDefault() == null ? 0 : useinfo.First().Total,
                                                  LockLocation = lockinfoif == null ? 0 : lockinfoif.Total,
                                                  StopLocation = stopinfoif == null ? 0 : stopinfoif.Total,
                                                  MaterilLocation = materialinfoif == null ? 0 : materialinfoif.Total
                                              };
            return locationUsageReportViewDtos.OrderBy(p => p.WarehouseName).ToList();
        }

        public PagedInfo<StockKeepReportViewDto> GetStockKeepReportList(Pagination pagination, BillInvNowQueryDto billInvNowQueryDto)
        {
            ISugarQueryable<StockKeepReportViewDto> queryable = GetStockKeepQueryable(billInvNowQueryDto);
            var lists = queryable.ToPage(pagination);
            return lists;
        }
        public IList<StockKeepReportViewDto> GetStockKeepReports(BillInvNowQueryDto billInvNowQueryDto)
        {
            ISugarQueryable<StockKeepReportViewDto> sugarQueryable = GetStockKeepQueryable(billInvNowQueryDto);
            var list = sugarQueryable.ToList();
            return list;
        }

        private ISugarQueryable<StockKeepReportViewDto> GetStockKeepQueryable(BillInvNowQueryDto billInvNowQueryDto)
        {
            var predicate = Expressionable.Create<BillInvnow>();
            predicate = predicate.AndIF(!string.IsNullOrEmpty(billInvNowQueryDto?.KeyWord), billInvnow => billInvnow.MatCode.Contains(billInvNowQueryDto.KeyWord) || billInvnow.MatName.Contains(billInvNowQueryDto.KeyWord));

            var queryable = _billInvnowrepository.Context.Queryable<BillInvnow>().Where(predicate.ToExpression())
                .GroupBy(it => new { it.MatCode, it.MatName })
                .Select(it => new StockKeepReportViewDto
                {
                    StockKeepTime7 = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.DateDiff(DateType.Day, it.ProductTime, DateTime.Now) >= 0 && SqlFunc.DateDiff(DateType.Day, it.ProductTime, DateTime.Now) <= 7, 1, 0)),
                    StockKeepTime15 = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.DateDiff(DateType.Day, it.ProductTime, DateTime.Now) > 7 && SqlFunc.DateDiff(DateType.Day, it.ProductTime, DateTime.Now) <= 15, 1, 0)),
                    StockKeepTime30 = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.DateDiff(DateType.Day, it.ProductTime, DateTime.Now) > 15 && SqlFunc.DateDiff(DateType.Day, it.ProductTime, DateTime.Now) <= 30, 1, 0)),
                    StockKeepTime31 = SqlFunc.AggregateSum(SqlFunc.IIF(SqlFunc.DateDiff(DateType.Day, it.ProductTime, DateTime.Now) > 30, 1, 0)),
                    MatName = it.MatName,
                    MatCode = it.MatCode
                });
            return queryable;
        }

        public PagedInfo<MatNameNetWeightCategory> GetMatNameNetWeightCategory(Pagination pagination, BillInvNowQueryDto billInvNowQueryDto)
        {
            ISugarQueryable<MatNameNetWeightCategory> queryable = GetMatNameNetWeightCategoryQueryable(billInvNowQueryDto);
            var lists = queryable.ToPage(pagination); ;
            return lists;
        }
        public IList<MatNameNetWeightCategory> GetMatNameNetWeightCategorys(BillInvNowQueryDto billInvNowQueryDto)
        {
            ISugarQueryable<MatNameNetWeightCategory> sugarQueryable = GetMatNameNetWeightCategoryQueryable(billInvNowQueryDto);
            var list = sugarQueryable.ToList();
            return list;
        }
        private ISugarQueryable<MatNameNetWeightCategory> GetMatNameNetWeightCategoryQueryable(BillInvNowQueryDto billInvNowQueryDto)
        {
            var predicate = Expressionable.Create<BillInvnow>();
            predicate = predicate.AndIF(!string.IsNullOrEmpty(billInvNowQueryDto?.KeyWord), billInvnow => billInvnow.MatCode.Contains(billInvNowQueryDto.KeyWord) || billInvnow.MatName.Contains(billInvNowQueryDto.KeyWord));

            var queryable = _billInvnowrepository.Queryable().Where(o => o.InvStateCode == "InvEcecState_In" && o.ContGrpType == 1)
              .Where(predicate.ToExpression())
              .Select(it => new MatNameNetWeightCategory
              {
                  MatName = it.MatName,
                  MatCode = it.MatCode,
                  NetWQty = SqlFunc.AggregateSum(it.TolWQty),
                  Qty = SqlFunc.AggregateDistinctCount(it.Id)
              })
               .GroupBy(o => new { o.MatName, o.MatCode });
            return queryable;
        }
    }
}