From 8c6e292c508312eca0aef4c203e5857c4fc3094c Mon Sep 17 00:00:00 2001
From: bklLiudl <673013083@qq.com>
Date: 星期六, 07 九月 2024 09:18:39 +0800
Subject: [PATCH] Merge branch 'master' into Liudl
---
Wms/WMS.BLL/DataServer/StockServer.cs | 419 ++++++++++++++++++++++++++++++-----------------------------
1 files changed, 216 insertions(+), 203 deletions(-)
diff --git a/Wms/WMS.BLL/DataServer/StockServer.cs b/Wms/WMS.BLL/DataServer/StockServer.cs
index fa00550..65e4416 100644
--- a/Wms/WMS.BLL/DataServer/StockServer.cs
+++ b/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
@@ -568,7 +581,7 @@
try
{
string str = "select* from SysMaterials where SkuNo in (select a.SkuNo from BllArrivalNoticeDetail a " +
- "inner join BllExportNoticeDetail b on a.SkuNo = b.SkuNo";
+ "left join BllExportNoticeDetail b on a.SkuNo = b.SkuNo";
//鍒ゆ柇寮�濮嬫椂闂存槸鍚︿负绌�
if (!string.IsNullOrWhiteSpace(StartTime))
--
Gitblit v1.8.0