WEBUI/WMS.BZServices/KLHC/ReportService.cs

using SqlSugar;
using WMS.BZModels;
using WMS.BZModels.Dto.KLHC.BillInvDtos;
using WMS.BZModels.Dto.KLHC.ReportDtos;
using WMS.BZModels.Models.KLHC;
using WMS.BZSqlSugar;
using WMS.Info;
using WMS.Util;

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

        private readonly Dictionary<string, string> dicName = new Dictionary<string, string>
        {
            { "62100101","正常18"},{"string","异常09"}  //,{"62100201","正常09"}
        };

        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";
            }

            ISugarQueryable<ReportDto> queryable = GetQueryable(reportQueryDto);
            //.OrderBy(pagination.sidx)
            var list = queryable.ToPage(pagination);

            return list;
        }

        private ISugarQueryable<ReportDto> GetQueryable(ReportQueryDto reportQueryDto)
        {
            var predicate = Expressionable.Create<BillInvflow, BaseWarehouse, BaseWarecell>();

            predicate = predicate.And((billInvflow, wareHouse, wareCell) => wareHouse.TypeNum == 1);

            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.RFIDBarCode.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.RFIDBarCode.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(!string.IsNullOrEmpty(reportQueryDto?.WarehouseId), (billInvflow, wareHouse, wareCell) => billInvflow.WarehouseId.Equals(reportQueryDto.WarehouseId));
            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
            }).With(SqlWith.NoLock).Where(predicate.ToExpression())

            .Select((billInvflow, wareHouse, wareCell) => new ReportDto
            {
                Id = billInvflow.Id.ToString(),
                WarehouseName = wareHouse.Name,
                Memo = billInvflow.Memo,
                Tunnel = wareCell.Tunnel,
                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,
            }).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>()
                .GroupBy((warecell) => new
                {
                    warecell.StateNum,
                    warecell.IsStop,
                    warecell.SCRel,
                }).Select((warecell) => new
                {
                    Status = warecell.StateNum,
                    warecell.SCRel,
                    warecell.IsStop,
                    Total = SqlFunc.AggregateCount(warecell.SCRel),
                })
               .MergeTable().ToList().OrderBy(o => o.SCRel);
            int _sort = 1;
            //基表：巷道、货架标识和仓库名称为维度查找总货位
            var baseTemp = list.GroupBy(s => new { s.SCRel }).Select(m => new { m.Key.SCRel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找可用货位：未停用.已锁定也算有效货位
            var useLocation = list.Where(s => s.IsStop == 0).GroupBy(s => new { s.SCRel }).Select(m => new { m.Key.SCRel, 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.SCRel }).Select(m => new { m.Key.SCRel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找停用货位
            var stopLocation = list.Where(s => s.IsStop == 1).GroupBy(s => new { s.SCRel }).Select(m => new { m.Key.SCRel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找物料货位
            var materialLocation = list.Where(s => s.Status == (int)LocationState.LocationState_Full).GroupBy(s => new { s.SCRel }).Select(m => new { m.Key.SCRel, Total = m.Sum(n => n.Total) }).ToList();
            var locationUsageReportViewDtos = from basetemp in baseTemp
                                              join uselocation in useLocation on new { basetemp.SCRel } equals new { uselocation.SCRel } into useinfo
                                              join locklocation in lockLocation on new { basetemp.SCRel } equals new { locklocation.SCRel } into lockinfo
                                              from lockinfoif in lockinfo.DefaultIfEmpty()
                                              join stoplocation in stopLocation on new { basetemp.SCRel } equals new { stoplocation.SCRel } into stopinfo
                                              from stopinfoif in stopinfo.DefaultIfEmpty()
                                              join materiallocation in materialLocation on new { basetemp.SCRel } equals new { materiallocation.SCRel } into materialinfo
                                              from materialinfoif in materialinfo.DefaultIfEmpty()
                                              select new LocationUsageReportViewDto()
                                              {
                                                  Sort = _sort++,
                                                  Tunnel = basetemp.SCRel.ToString(),
                                                  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> sugarQueryable = GetStockKeepReportQueryable(billInvNowQueryDto);

            var lists = sugarQueryable.ToPage(pagination);
            return lists;
        }

        public IList<StockKeepReportViewDto> GetStockKeepReports(BillInvNowQueryDto billInvNowQueryDto)
        {
            ISugarQueryable<StockKeepReportViewDto> sugarQueryable = GetStockKeepReportQueryable(billInvNowQueryDto);
            var list = sugarQueryable.ToList();
            return list;
        }

        private ISugarQueryable<StockKeepReportViewDto> GetStockKeepReportQueryable(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 sugarQueryable = _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 sugarQueryable;
        }

        /// <summary>
        /// 库内满托占比
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="mantuoBarCodeRateQueryDto"></param>
        /// <returns></returns>
        public PagedInfo<MantuoBarCodeRateDto> GetMantuoBarCodeRate(Pagination pagination, MantuoBarCodeRateQueryDto mantuoBarCodeRateQueryDto)
        {
            ISugarQueryable<MantuoBarCodeRateDto> sugarQueryable = GetMantuoBarCodeRateQueryable(mantuoBarCodeRateQueryDto);
            var list = sugarQueryable.ToPage(pagination);

            list.Result.ForEach(o =>
            {
                if (!string.IsNullOrWhiteSpace(o.BomMatCode))
                {
                    var bomMatCode = o.BomMatCode.Trim();
                    if (dicName.ContainsKey(bomMatCode))
                    {
                        o.BomMatName = dicName[bomMatCode];
                    }
                }
            });
            return list;
        }

        public IList<MantuoBarCodeRateDto> GetMantuoBarCodeRates(MantuoBarCodeRateQueryDto mantuoBarCodeRateQueryDto)
        {
            ISugarQueryable<MantuoBarCodeRateDto> sugarQueryable = GetMantuoBarCodeRateQueryable(mantuoBarCodeRateQueryDto);
            var list = sugarQueryable.ToList();

            list.ForEach(o =>
            {
                if (!string.IsNullOrWhiteSpace(o.BomMatCode))
                {
                    var bomMatCode = o.BomMatCode.Trim();
                    if (dicName.ContainsKey(bomMatCode))
                    {
                        o.BomMatName = dicName[bomMatCode];
                    }
                }
            });
            return list;
        }

        private ISugarQueryable<MantuoBarCodeRateDto> GetMantuoBarCodeRateQueryable(MantuoBarCodeRateQueryDto mantuoBarCodeRateQueryDto)
        {
            var predicate = Expressionable.Create<BillInvnow, BaseWarehouse, BaseMatinfo, BillBomsetgrp>();
            predicate = predicate.And((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => wareHouse.TypeNum == 1 || wareHouse.TypeNum == 3);
            predicate = predicate.And((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => billInvnow.Secondary == false);
            predicate = predicate.And((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => !SqlFunc.IsNullOrEmpty(billInvnow.BomMatCode));
            predicate = predicate.And((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => billInvnow.InvStateCode.ToLower().Equals(InvState.InvEcecState_In.ToString().ToLower()));
            predicate = predicate.AndIF(!string.IsNullOrWhiteSpace(mantuoBarCodeRateQueryDto.KeyWord), (billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => billInvnow.BomMatCode.Contains(mantuoBarCodeRateQueryDto.KeyWord));
            predicate = predicate.AndIF(!string.IsNullOrWhiteSpace(mantuoBarCodeRateQueryDto.SetGrpCode), (billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => billInvnow.SetGrpCode.Contains(mantuoBarCodeRateQueryDto.SetGrpCode));
            predicate = predicate.AndIF(!string.IsNullOrWhiteSpace(mantuoBarCodeRateQueryDto.WarehouseId), (billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => wareHouse.Id.Equals(mantuoBarCodeRateQueryDto.WarehouseId));
            var sugarQueryable = _billInvnowrepository.Context.Queryable<BillInvnow, BaseWarehouse, BaseMatinfo, BillBomsetgrp>((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => new object[] {
                JoinType.Left, billInvnow.WarehouseId == wareHouse.Id,
                JoinType.Left,billInvnow.BomMatCode== baseMatinfo.Code,
                 JoinType.Left,billInvnow.SetGrpCode== billBomsetgrp.Code
               }).Where(predicate.ToExpression())
                .GroupBy((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => new { SetGrpCode = billBomsetgrp.Name, billInvnow.BomMatCode, BomMatName = baseMatinfo.Name })
                .Select((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => new MantuoBarCodeRateDto
                {
                    SetGrpCode = billBomsetgrp.Name,
                    BomMatCode = billInvnow.BomMatCode,
                    Qty = SqlFunc.AggregateDistinctCount(billInvnow.ContGrpBarCode),
                    //WarehouseName = wareHouse.Name,
                    BomMatName = baseMatinfo.Name,
                });
            return sugarQueryable;
        }

        public PagedInfo<MatNameNetWeightCategory> GetMatNameNetWeightCategory(Pagination pagination, BillInvNowQueryDto billInvNowQueryDto)
        {
            ISugarQueryable<MatNameNetWeightCategory> sugarQueryable = GetMatNameNetWeightCategoryQueryable(billInvNowQueryDto);

            var lists = sugarQueryable.ToPage(pagination);
            return lists;
        }

        public IList<MatNameNetWeightCategory> GetMatNameNetWeightCategorys(BillInvNowQueryDto billInvNowQueryDto)
        {
            ISugarQueryable<MatNameNetWeightCategory> sugarQueryable = GetMatNameNetWeightCategoryQueryable(billInvNowQueryDto);

            var lists = sugarQueryable.ToList();
            return lists;
        }

        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 sugarQueryable = _billInvnowrepository.Queryable().Where(o => o.InvStateCode == "InvEcecState_In" && o.ContGrpType == FJContGrpType.Material)
              .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 sugarQueryable;
        }
    }
}