bug修复

bklLiudl

2024-08-29 d58fd0905004b5c4abf138c8fb95a597130af928

 Wms/WMS.BLL/DataServer/StockServer.cs

@@ -12,9 +12,12 @@
using Model.ModelDto.SysDto;
using Model.ModelVm.DataVm;
using Model.ModelVm.SysVm;
using MySqlConnector;
using SqlSugar;
using WMS.DAL;
using WMS.Entity.BllAsnEntity;
using WMS.Entity.BllQualityEntity;
using WMS.Entity.BllSoEntity;
using WMS.Entity.Context;
using WMS.Entity.DataEntity;
using WMS.Entity.SysEntity;
@@ -330,90 +333,92 @@
        /// <returns></returns>
        public List<TotalRecordDto> GetTotalRecord(string skuNo, string skuName, string lotNo, string startTime, string endTime)
        {
            //入库
            string rstr = "select LotNo,SkuNo,SkuName,SUM(Qty) RQty,SUM(FactQty) RFactQty,SUM(CompleteQty) RCompleteQty from BllArrivalNoticeDetail where IsDel = '0' ";
            //出库
            string cstr = "select LotNo,SkuNo,SkuName,SUM(Qty) CQty,SUM(FactQty) CFactQty,SUM(CompleteQty) CompleteQty,SUM(AllotQty) CAllotQty from BllExportNoticeDetail where IsDel = '0' ";

            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                rstr += " and SkuNo like @skuno";
                cstr += " and SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                rstr += " and SkuName like @skuname";
                cstr += " and SkuName like @skuname";
            }
            //判断批次号是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                rstr += " and LotNo like @lotno";
                cstr += " and LotNo like @lotno";
            }
            // liudl 时间不准确 暂时注释。
            if (!string.IsNullOrWhiteSpace(startTime))
            {
                rstr += " and UpdateTime >= @startTime";
                cstr += " and UpdateTime >= @startTime";
            }
            if (!string.IsNullOrWhiteSpace(endTime))
            {
                endTime = DateTime.Parse(endTime).AddDays(1).ToString();
                rstr += " and UpdateTime < @endTime";
                cstr += " and UpdateTime < @endTime";
            }

            rstr += " group by LotNo,SkuNo,SkuName";
            cstr += " group by LotNo,SkuNo,SkuName";
            //入库
            List<TotalRecordDto> totalHListData = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
            {
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                lotno = "%" + lotNo + "%", //批次号
                startTime, //开始时间
                endTime //结束时间

            });
            List<TotalRecordDto> totalCListData = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
            {
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                lotno = "%" + lotNo + "%", //批次号
                startTime, //开始时间
                endTime //结束时间
            });

            //合并数据
            int a = 0;
            foreach (var h in totalHListData)
            {
                if (a >= totalCListData.Count)
            // 获取入库单明细信息
            var asnList = Db.Queryable<BllArrivalNoticeDetail>()
                .Where(m => m.IsDel == "0")
                .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
                .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                .Select(a => new TotalRecordDto()
                {
                    continue;
                }
                foreach (var c in totalCListData)
                    LotNo=a.LotNo,
                    SkuNo=a.SkuNo,
                    SkuName=a.SkuName,
                    RQty = SqlFunc.AggregateSumNoNull(a.Qty),
                    RFactQty = (decimal)SqlFunc.AggregateSum(a.FactQty),
                    RCompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
                    CQty = 0,
                    CFactQty = 0,
                    CAllotQty = 0,
                    CompleteQty = 0
                });

            // 获取出库单明细信息
            var soList = Db.Queryable<BllExportNoticeDetail>()
                .Where(m => m.IsDel == "0")
                .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
                .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                .Select(a => new TotalRecordDto()
                {
                    //判断出库是否拥有相同批次 且物料相同
                    if (h.LotNo == c.LotNo || h.LotNo.Contains(c.LotNo) && h.SkuNo == c.SkuNo && h.SkuName == c.SkuName)
                    {
                        h.CQty = c.CQty; //出库数量
                        h.CAllotQty = c.CAllotQty; //分配数量
                        h.CFactQty = c.CFactQty; //下架数量
                        h.CompleteQty = c.CompleteQty; //拣货数量
                                                       //h.SONo = c.SONo; //出库单号
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    RQty = 0,
                    RFactQty = 0,
                    RCompleteQty = 0,
                    CQty = SqlFunc.AggregateSumNoNull(a.Qty),
                    CFactQty = (decimal)SqlFunc.AggregateSum(a.FactQty),
                    CAllotQty = (decimal)SqlFunc.AggregateSum(a.AllotQty),
                    CompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
                });
            // 获取取样拣货信息
            var samplingList = Db.Queryable<BllSamplingDetails>()
                .Where(m => m.IsDel == "0")
                .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
                .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                .Select(a => new TotalRecordDto()
                {
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    RQty = 0,
                    RFactQty = 0,
                    RCompleteQty = 0,
                    CQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
                    CFactQty = 0,
                    CAllotQty = 0,
                    CompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
                });

                        a += 1;
            // 合并集合
            var dataList = Db.UnionAll(asnList, soList, samplingList)
                .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                .Select(a => new TotalRecordDto()
                {
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    RQty = SqlFunc.AggregateSumNoNull(a.RQty),
                    RFactQty = (decimal)SqlFunc.AggregateSum(a.RFactQty),
                    RCompleteQty = (decimal)SqlFunc.AggregateSum(a.RCompleteQty),
                    CQty = SqlFunc.AggregateSumNoNull(a.CQty),
                    CFactQty = (decimal)SqlFunc.AggregateSum(a.CFactQty),
                    CAllotQty = (decimal)SqlFunc.AggregateSum(a.CAllotQty),
                    CompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty)
                }).ToList();

                        break;
                    }
                }
            }

            return totalHListData;
            return dataList;
        }

        /// <summary>
@@ -426,131 +431,139 @@
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public List<TotalRecordDto> GetDetailedRecord(string skuNo, string skuName, string lotNo, string palletNo,
            string startTime, string endTime)
        public List<TotalRecordDto> GetDetailedRecord(string skuNo, string skuName, string lotNo, string palletNo,string startTime, string endTime)
        {
            //入库
            string rstr = "select notice.ASNNo,bind.LotNo,notice.SkuNo,notice.SkuName,bind.PalletNo,bind.Qty RQty,bind.Qty RFactQty,bind.Qty  RCompleteQty,bind.CompleteTime,notice.LotText,notice.SupplierLot,bind.CompleteTime from BllPalletBind bind left join BllArrivalNoticeDetail notice on bind.ASNDetailNo = notice.Id where bind.IsDel = '0' and notice.IsDel = '0'  ";
            //出库
            string cstr = "select notice.SONo,allot.LotNo,allot.TaskNo CTaskNo,allot.SkuNo,allot.SkuName,allot.PalletNo," +
                "allot.UpdateTime CCreateTime,allot.Qty CQty,allot.Qty CAllotQty,Allot.Qty CFactQty,Allot.CompleteQty CompleteQty," +
                "allot.LotText,allot.SupplierLot " +
                "from BllExportAllot allot left join BllExportNoticeDetail notice on allot.SODetailNo = notice.Id " +
                "where allot.IsDel = '0' and notice.IsDel = '0' ";

            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                rstr += " and notice.SkuNo like @skuno";
                cstr += " and allot.SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                rstr += " and notice.SkuName like @skuname";
                cstr += " and allot.SkuName like @skuname";
            }
            //判断批次号是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                rstr += " and notice.LotNo like @lotno";
                cstr += " and allot.LotNo like @lotno";
            }
            //判断托盘是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                rstr += " and bind.PalletNo like @palletno";
                cstr += " and allot.PalletNo like @palletno";
            }
            if (!string.IsNullOrWhiteSpace(startTime))
            {
                rstr += " and bind.CompleteTime >= @startTime";
                cstr += " and allot.UpdateTime >= @startTime";
            }
            if (!string.IsNullOrWhiteSpace(endTime))
            {
                endTime = DateTime.Parse(endTime).AddDays(1).ToString();
                rstr += " and bind.CompleteTime < @endTime";
                cstr += " and allot.UpdateTime < @endTime";
            }

            //rstr += " group by bind.LotNo,bind.SkuNo,bind.SkuName";
            //cstr += " group by LotNo,SkuNo,SkuName";
            //入库
            List<TotalRecordDto> totalRList = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                lotno = "%" + lotNo + "%", //批次号
                palletno = "%" + palletNo + "%", //托盘号
                startTime = startTime,
                endTime = endTime
            });
            //出库
            List<TotalRecordDto> totalCList = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                lotno = "%" + lotNo + "%", //批次号
                palletno = "%" + palletNo + "%", //托盘号
                startTime = startTime,
                endTime = endTime
            });

            //合并
            foreach (var c in totalCList)
            {
                int i = 0;
                foreach (var r in totalRList)
            // 获取入库单明细信息
            var asnList = Db.Queryable<BllPalletBind,BllArrivalNoticeDetail>
                ((tb1,tb2)=>new JoinQueryInfos(
                 JoinType.Left, tb1.ASNDetailNo == tb2.Id
                ))
                .Where((tb1,tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.CompleteTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.CompleteTime <= Convert.ToDateTime(endTime).AddDays(1))
                .WhereIF(!string.IsNullOrEmpty(palletNo), tb1 => tb1.PalletNo.Contains(palletNo))
                .WhereIF(!string.IsNullOrEmpty(skuNo), (tb1,tb2) => tb2.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), (tb1, tb2) => tb2.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), (tb1, tb2) => tb2.LotNo.Contains(lotNo))
                //.GroupBy((tb1,tb2) => new { tb2.LotNo, tb2.SkuNo, tb2.SkuName })
                .Select((tb1,tb2) => new TotalRecordDto()
                {
                    i += 1;
                    //判断入库中是否存在出库中的批次物料托盘
                    if (r.PalletNo == c.PalletNo && r.LotNo == c.LotNo && r.SkuNo == c.SkuNo && r.SkuName == c.SkuName)
                    {
                        if (r.SONo != null)
                        {
                            if (r.SONo != c.SONo)
                            {
                                totalRList.Add(c);
                            }
                        }
                        if (c.CTaskNo != "" && r.SONo == null || r.SONo == c.SONo)
                        {
                            r.SONo = c.SONo; //出库单号
                            r.CQty += c.CQty; //出库数量
                            r.CAllotQty += c.CAllotQty; //分配数量
                            r.CFactQty += c.CFactQty; //下架数量
                            r.CompleteQty += c.CompleteQty; //拣货数量
                            r.CCreateTime = c.CCreateTime; //拣货时间
                        }
                        if (r.CompleteTime == null)
                        {
                            r.RCompleteQty = 0;
                        }
                        break;
                    }
                    if (i == totalRList.Count)
                    {
                        totalRList.Add(c);
                        break;
                    }
                }
            }
            if (totalCList.Count == 0)
            {
                foreach (var r in totalRList)
                {
                    if (r.CompleteTime == null)
                    {
                        r.RCompleteQty = 0;
                        continue;
                    }
                }
            }
                    ASNNo = tb2.ASNNo,
                    LotNo = tb1.LotNo,
                    SkuNo = tb2.SkuNo,
                    SkuName = tb2.SkuName,
                    PalletNo = tb1.PalletNo,
                    RQty = tb1.Qty,
                    RFactQty = tb1.Qty,
                    RCompleteQty = tb1.Qty,
                    CompleteTime = tb1.CompleteTime,
                    LotText = tb1.LotText,
                    SupplierLot = tb2.SupplierLot,
                    
                    SONo = "",
                    CTaskNo = "",
                    CCreateTime = null,
                    CQty = 0,
                    CFactQty = 0,
                    CAllotQty = 0,
                    CompleteQty = 0
                });

            return totalRList;
            // 获取出库单明细信息
            var soList = Db.Queryable<BllExportAllot, BllExportNoticeDetail>
                (((tb1, tb2) => new JoinQueryInfos(
                 JoinType.Left, tb1.SODetailNo == tb2.Id
                )))
                .Where((tb1, tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.UpdateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.UpdateTime <= Convert.ToDateTime(endTime).AddDays(1))
                .WhereIF(!string.IsNullOrEmpty(palletNo), tb1 => tb1.PalletNo.Contains(palletNo))
                .WhereIF(!string.IsNullOrEmpty(skuNo), tb1 => tb1.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), tb1 => tb1.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), tb1 => tb1.LotNo.Contains(lotNo))
                 //.GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                 .Select((tb1, tb2) => new TotalRecordDto()
                 {
                     ASNNo = "",
                     LotNo = tb1.LotNo,
                     SkuNo = tb2.SkuNo,
                     SkuName = tb2.SkuName,
                     PalletNo = tb1.PalletNo,
                     RQty = 0,
                     RFactQty = 0,
                     RCompleteQty = 0,
                     CompleteTime = null,
                     LotText = tb1.LotText,
                     SupplierLot = tb2.SupplierLot,

                     SONo = tb2.SONo,
                     CTaskNo = tb1.TaskNo,
                     CCreateTime = tb1.UpdateTime,
                     CQty = tb1.Qty,
                     CFactQty = tb1.Qty,
                     CAllotQty = tb1.Qty,
                     CompleteQty = (decimal)tb1.CompleteQty
                 });

            // 获取取样拣货信息
            var samplingList = Db.Queryable<BllSamplingDetails>()
                .Where(m => m.IsDel == "0")
                .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
                .WhereIF(!string.IsNullOrEmpty(palletNo), a => a.PalletNo.Contains(palletNo))
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
                //.GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                .Select(a => new TotalRecordDto()
                {
                    ASNNo = "",
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    PalletNo = a.PalletNo,
                    RQty = 0,
                    RFactQty = 0,
                    RCompleteQty = 0,
                    CompleteTime = null,
                    LotText = "",
                    SupplierLot = "",

                    SONo = a.QcNo,
                    CTaskNo = "",
                    CCreateTime = a.CreateTime,
                    CQty = (decimal)a.CompleteQty,
                    CFactQty = 0,
                    CAllotQty = 0,
                    CompleteQty = (decimal)a.CompleteQty
                });

            // 合并集合
            var dataList = Db.UnionAll(asnList, soList, samplingList)
                .Select(a => new TotalRecordDto()
                {
                    ASNNo = a.ASNNo,
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    PalletNo = a.PalletNo,
                    RQty = a.RQty,
                    RFactQty = a.RFactQty,
                    RCompleteQty = a.RCompleteQty,
                    CompleteTime = a.CompleteTime,
                    LotText = a.LotText,
                    SupplierLot = a.SupplierLot,

                    SONo = a.SONo,
                    CTaskNo = a.CTaskNo,
                    CCreateTime = a.CCreateTime,
                    CQty = a.CQty,
                    CFactQty = a.CFactQty,
                    CAllotQty = a.CAllotQty,
                    CompleteQty = a.CompleteQty
                }).ToList();

            return dataList;
        }

        #endregion