From 7f4b5fb7a89052f1fbd2e00b34e60e1b55f73ab3 Mon Sep 17 00:00:00 2001
From: Demo <Demo@DESKTOP-CPA90BF>
Date: 星期六, 16 三月 2024 16:58:40 +0800
Subject: [PATCH] Merge branch 'csc'
---
Wms/WMS.BLL/DataServer/StockServer.cs | 350 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 344 insertions(+), 6 deletions(-)
diff --git a/Wms/WMS.BLL/DataServer/StockServer.cs b/Wms/WMS.BLL/DataServer/StockServer.cs
index 6c0ff69..6f7dbaf 100644
--- a/Wms/WMS.BLL/DataServer/StockServer.cs
+++ b/Wms/WMS.BLL/DataServer/StockServer.cs
@@ -7,10 +7,12 @@
using System.Text;
using Model.ModelDto;
using Model.ModelDto.DataDto;
+using Model.ModelVm.DataVm;
using SqlSugar;
using WMS.DAL;
using WMS.Entity.Context;
using WMS.Entity.DataEntity;
+using WMS.Entity.SysEntity;
using WMS.IBLL.IDataServer;
namespace WMS.BLL.DataServer
@@ -264,9 +266,13 @@
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' and Id in (select ASNDetailNo from DataStockDetail where IsDel = '0' group by ASNDetailNo) ";
+ string rstr = "select LotNo,SkuNo,SkuName,SUM(Qty) RQty,SUM(FactQty) RFactQty,SUM(CompleteQty) RCompleteQty " +
+ "from BllArrivalNoticeDetail where IsDel = '0' and Id in (select ASNDetailNo from DataStockDetail " +
+ "where IsDel = '0' group by ASNDetailNo) ";
//鍑哄簱
- string cstr = "select LotNo,SkuNo,SkuName,SUM(Qty) CQty,SUM(FactQty) CFactQty,SUM(CompleteQty) CompleteQty,SUM(AllotQty) CAllotQty from BllExportNoticeDetail where IsDel = '0' and LotNo in (select LotNo from DataStock 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' and LotNo in (select LotNo from DataStock where IsDel = '0') ";
//鍒ゆ柇鐗╂枡鍙锋槸鍚︿负绌�
if (!string.IsNullOrEmpty(skuNo))
@@ -286,6 +292,18 @@
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))
+ //{
+ // rstr += " and UpdateTime <= @endTime";
+ // cstr += " and UpdateTime <= @endTime";
+ //}
+
rstr += " group by LotNo,SkuNo,SkuName";
cstr += " group by LotNo,SkuNo,SkuName";
//鍏ュ簱
@@ -295,6 +313,9 @@
skuno = "%" + skuNo + "%", //鐗╂枡鍙�
skuname = "%" + skuName + "%", //鐗╂枡鍚嶇О
lotno = "%" + lotNo + "%", //鎵规鍙�
+ //startTime = startTime,
+ //endTime = endTime
+
});
List<TotalRecordDto> totalCListData = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
{
@@ -302,6 +323,8 @@
skuno = "%" + skuNo + "%", //鐗╂枡鍙�
skuname = "%" + skuName + "%", //鐗╂枡鍚嶇О
lotno = "%" + lotNo + "%", //鎵规鍙�
+ //startTime = startTime,
+ //endTime = endTime
});
//鍚堝苟鏁版嵁
@@ -346,12 +369,21 @@
/// <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' and bind.LotNo in (select LotNo from DataStock where IsDel = '0') ";
+ 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' and bind.LotNo in (select LotNo from DataStock where IsDel = '0') ";
//鍑哄簱
- string cstr = "select notice.SONo,allot.LotNo,allot.TaskNo CTaskNo,allot.SkuNo,allot.SkuName,allot.PalletNo,allot.CreateTime 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' and notice.LotNo in (select LotNo from DataStock where IsDel = '0') ";
+ string cstr = "select notice.SONo,allot.LotNo,allot.TaskNo CTaskNo,allot.SkuNo,allot.SkuName,allot.PalletNo," +
+ "allot.CreateTime 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' " +
+ "and notice.LotNo in (select LotNo from DataStock where IsDel = '0') ";
//鍒ゆ柇鐗╂枡鍙锋槸鍚︿负绌�
if (!string.IsNullOrEmpty(skuNo))
@@ -371,12 +403,21 @@
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))
+ {
+ rstr += " and bind.CompleteTime <= @endTime";
+ cstr += " and allot.UpdateTime <= @endTime";
}
//rstr += " group by bind.LotNo,bind.SkuNo,bind.SkuName";
@@ -389,6 +430,8 @@
skuname = "%" + skuName + "%", //鐗╂枡鍚嶇О
lotno = "%" + lotNo + "%", //鎵规鍙�
palletno = "%" + palletNo + "%", //鎵樼洏鍙�
+ startTime = startTime,
+ endTime = endTime
});
//鍑哄簱
List<TotalRecordDto> totalCList = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
@@ -398,6 +441,8 @@
skuname = "%" + skuName + "%", //鐗╂枡鍚嶇О
lotno = "%" + lotNo + "%", //鎵规鍙�
palletno = "%" + palletNo + "%", //鎵樼洏鍙�
+ startTime = startTime,
+ endTime = endTime
});
//鍚堝苟
@@ -456,6 +501,299 @@
#endregion
+ #region 骞村害鎶ヨ〃
+
+ /// <summary>
+ /// 鑾峰彇骞存姤琛ㄦ�昏〃
+ /// </summary>
+ /// <param name="StartTime"></param>
+ /// <param name="EndTime"></param>
+ /// <returns></returns>
+ public List<SysMaterials> GetAssSoInfo(string StartTime, string EndTime)
+ {
+ try
+ {
+ string str = "select* from SysMaterials where SkuNo in (select a.SkuNo from BllArrivalNoticeDetail a inner join BllExportNoticeDetail b on a.SkuNo = b.SkuNo";
+
+ //鍒ゆ柇寮�濮嬫椂闂存槸鍚︿负绌�
+ if (!string.IsNullOrWhiteSpace(StartTime))
+ {
+ str += $" and a.CompleteTime >= '{StartTime}' and b.UpdateTime >= '{StartTime}'";
+ }
+ //鍒ゆ柇缁撴潫鏃堕棿鏄惁涓虹┖
+ if (!string.IsNullOrWhiteSpace(EndTime))
+ {
+ EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
+ str += $" and a.CompleteTime < '{EndTime}' and b.UpdateTime < '{EndTime}'";
+ }
+
+ str += " group by a.SkuNo,b.SkuNo)";
+
+ var sku = Db.Ado.SqlQuery<SysMaterials>(str);
+ return sku;
+ }
+ catch (Exception ex)
+ {
+
+ throw new Exception(ex.Message);
+ }
+ }
+
+ /// <summary>
+ /// 鑾峰彇骞存姤琛ㄦ槑缁�
+ /// </summary>
+ /// <param name="skuNo"></param>
+ /// <param name="StartTime"></param>
+ /// <param name="EndTime"></param>
+ /// <returns></returns>
+ public List<AssSoDateStockInfoDto> GetAssSoInfoDetail(string skuNo, string StartTime, string EndTime)
+ {
+ try
+ {
+ //鐢ㄤ簬鍚堝苟鏁版嵁
+ List<AssSoDateStockInfoDto> infolist = new List<AssSoDateStockInfoDto>();
+ //鑾峰彇寮�濮嬫椂闂村綋澶╃殑缁撳瓨鏁伴噺
+ //DataStockInfo stockInfo = new DataStockInfo();
+ //鑾峰彇鐗╂枡鐞嗚閲嶉噺
+ string weightstr = $"select Weight from SysMaterials where SkuNo = '{skuNo}'";
+ var weight = Db.Ado.SqlQuerySingle<decimal>(weightstr);
+
+ //鍒ゆ柇寮�濮嬫椂闂存槸鍚︿负绌�
+ if (!string.IsNullOrWhiteSpace(StartTime))
+ {
+ //string str = $"select * from DataStockInfo where IsDel = '0' and CONVERT(date,CreateTime) = '{StartTime}'";
+ //鑾峰彇褰撳ぉ搴撳瓨淇℃伅
+ //stockInfo = Db.Ado.SqlQuerySingle<DataStockInfo>(str);
+ }
+ //鍒ゆ柇缁撴潫鏃堕棿鏄惁涓虹┖
+ if (!string.IsNullOrWhiteSpace(EndTime))
+ {
+ EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
+ }
+ int countjie = 0; //缁熻缁撳瓨鏁伴噺
+ ////鍒ゆ柇搴撳瓨淇℃伅鏄惁涓虹┖
+ //if (stockInfo != null)
+ //{
+ // countjie = stockInfo.BalanceQty; //缁熻缁撳瓨鏁伴噺
+ //}
+ //else if (stockInfo == null)
+ //{
+ //鍒ゆ柇寮�濮嬫椂闂存槸鍚︿负绌�
+ if (!string.IsNullOrWhiteSpace(StartTime))
+ {
+ //string StartTimelin = (Convert.ToDateTime(StartTime).AddDays(1)).ToString();
+ //鑾峰彇鍑哄簱鏁伴噺
+ string str = $"select SUM(CompleteQty) as CompleteQtyc from BllExportNoticeDetail where IsDel = '0' and SkuNo = '{skuNo}' and Status != '0' and CreateTime < '{StartTime}'";
+ string CompleteQtyc = Db.Ado.SqlQuerySingle<string>(str);
+ //鑾峰彇鍏ュ簱鏁伴噺
+ str = $"select SUM(CompleteQty) as CompleteQtyr from BllArrivalNoticeDetail where IsDel = '0' and SkuNo = '{skuNo}' and Status != '0' and CreateTime < '{StartTime}'";
+ string CompleteQtyr = Db.Ado.SqlQuerySingle<string>(str);
+
+ //璁＄畻缁撳瓨鏁伴噺
+ countjie = int.Parse(CompleteQtyr) - int.Parse(CompleteQtyc);
+
+ }
+
+ //}
+ int yeara = 0; //骞村叆搴�
+ int years = 0; //骞村嚭搴�
+ int montha = 0; //鏈堝叆搴�
+ int months = 0; //鏈堝嚭搴�
+ DateTime createTime = DateTime.Now.AddYears(-1000); //鏃堕棿
+
+ //鑾峰彇鏈夊灏戝勾
+ string yearstr = "select DATEPART(YEAR, CreateTime) SkuName from BllArrivalNoticeDetail where IsDel = '0' and Status = '2' ";
+ if (!string.IsNullOrWhiteSpace(StartTime))
+ {
+ yearstr += $" and CreateTime >= '{StartTime}'";
+ }
+ if (!string.IsNullOrWhiteSpace(EndTime))
+ {
+
+ yearstr += $" and CreateTime < '{EndTime}'";
+ }
+ yearstr += "group by DATEPART(YEAR, CreateTime)";
+ var yearlist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(yearstr);
+ if (!string.IsNullOrWhiteSpace(StartTime) && !string.IsNullOrWhiteSpace(EndTime))
+ {
+ //鍒ゆ柇寮�濮嬫椂闂村勾涓庣粨鏉熸椂闂村勾鏄惁鐩哥瓑
+ var startyear = StartTime.Split('-');
+ var endyear = EndTime.Split("-");
+ }
+ foreach (var yearcount in yearlist)
+ {
+ //鑾峰彇褰撳墠鐗╂枡姣忓勾鍏ュ簱鍗曟嵁鏄庣粏
+ string ruyearstr = $"select ASNNo,SkuNo,SkuName,CompleteQty as ASNQty,0 as ASNSOQty,CONVERT(date,CreateTime) as NoticeDateTime,DATEPART(MONTH,CreateTime) as month,CreateTime from BllArrivalNoticeDetail where IsDel = '0' and Status != '0' and SkuNo = '{skuNo}' and DATEPART(YEAR, CreateTime) = '{yearcount.SkuName}'";
+ //鑾峰彇褰撳墠鐗╂枡姣忓勾鍑哄簱鍗曟嵁鏄庣粏
+ string chuyearstr = $"select SONo,SkuNo,SkuName,CompleteQty as SOQty,0 as ASNSOQty,CONVERT(date,CreateTime) as NoticeDateTime,DATEPART(MONTH,CreateTime) as month,CreateTime from BllExportNoticeDetail where IsDel = '0' and Status != '0' and SkuNo = '{skuNo}' and DATEPART(YEAR, CreateTime) = '{yearcount.SkuName}'";
+
+ if (!string.IsNullOrWhiteSpace(StartTime))
+ {
+ ruyearstr += $" and CreateTime >= '{StartTime}'";
+ chuyearstr += $" and CreateTime >= '{StartTime}'";
+ }
+ if (!string.IsNullOrWhiteSpace(EndTime))
+ {
+ //EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
+ ruyearstr += $" and CreateTime < '{EndTime}'";
+ chuyearstr += $" and CreateTime < '{EndTime}'";
+ }
+
+ ruyearstr += " order by CreateTime";
+ chuyearstr += " order by CreateTime";
+ //姣忓勾鍏ュ簱
+ List<AssSoDateStockInfoDto> ruinfolist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(ruyearstr);
+ //姣忓勾鍑哄簱
+ List<AssSoDateStockInfoDto> chuinfolist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(chuyearstr);
+
+ //寰幆12涓湀
+ for (int i = 1; i <= 12; i++)
+ {
+ //涓存椂鍏�
+ List<AssSoDateStockInfoDto> linshiru = new List<AssSoDateStockInfoDto>();
+ //涓存椂鍑�
+ List<AssSoDateStockInfoDto> linshichu = new List<AssSoDateStockInfoDto>();
+
+ if (ruinfolist.Count > 0)
+ {
+ linshiru = ruinfolist.Where(a => a.month == i.ToString()).ToList(); //鑾峰彇褰撴湀鍏ュ簱淇℃伅
+ }
+ if (chuinfolist.Count > 0)
+ {
+ linshichu = chuinfolist.Where(a => a.month == i.ToString()).ToList(); //鑾峰彇褰撴湀鍑哄簱淇℃伅
+ }
+ if (linshichu.Count > 0)
+ {
+ foreach (var h in linshichu)
+ {
+ linshiru.Add(h);
+ }
+ }
+ var data = linshiru.OrderBy(a => a.CreateTime).ToList();
+
+ //鍒ゆ柇鏄惁瀛樺湪褰撴湀鍏ュ簱淇℃伅
+ if (linshiru.Count > 0)
+ {
+
+ foreach (var item in data)
+ {
+ item.NoticeDateTime = Convert.ToDateTime(item.NoticeDateTime).ToString("yyyy-MM-dd");
+ //鎬昏缁撳瓨鏁伴噺
+ if (item.ASNNo != null)
+ {
+ //鍏ュ簱
+ countjie += item.ASNQty;
+ montha += item.ASNQty; //鏈堝叆搴�
+ yeara += item.ASNQty; //骞村叆搴�
+ }
+ else if (item.SONo != null)
+ {
+ //鍑哄簱
+ countjie -= item.SOQty;
+ months += item.SOQty; //鏈堝嚭搴�
+ years += item.SOQty; //骞村嚭搴�
+ }
+ item.ASNSOQty = countjie; //缁撳瓨鏁伴噺
+ item.Weight = weight; //閲嶉噺
+ infolist.Add(item);
+
+ if (createTime < item.CreateTime)
+ {
+ createTime = item.CreateTime; //鏃堕棿
+ }
+ }
+ }
+ //鍒ゆ柇鏄惁瀛樺湪褰撴湀鍑哄簱淇℃伅
+ //if (linshichu.Count > 0)
+ //{
+ // //鍑哄簱
+ // foreach (var item in linshichu)
+ // {
+ // item.NoticeDateTime = Convert.ToDateTime(item.NoticeDateTime).ToString("yyyy-MM-dd");
+ // countjie -= item.SOQty; //鎬昏缁撳瓨鏁伴噺
+ // item.ASNSOQty = countjie; //缁撳瓨鏁伴噺
+ // item.Weight = weight; //閲嶉噺
+ // infolist.Add(item);
+ // months += item.SOQty; //鏈堝嚭搴�
+ // years += item.SOQty; //骞村嚭搴�
+ // if (createTime < item.CreateTime)
+ // {
+ // createTime = item.CreateTime; //鏃堕棿
+ // }
+ // }
+ //}
+
+ //鍒ゆ柇鏄惁鏈夋湀鍑哄叆搴�
+ if (linshiru.Count > 0 || linshichu.Count > 0)
+ {
+ //鏈堜俊鎭�
+ var addmonthinfo = new AssSoDateStockInfoDto()
+ {
+ ASNNo = "", // 鍏ュ簱鍗曟嵁
+ SONo = "", // 鍑哄簱鍗曟嵁
+
+ SkuNo = "", // 鐗╂枡缂栫爜
+ SkuName = "", // 鐗╂枡鍚嶇О
+
+ NoticeType = "鏈湀绱", //缁熻绫诲埆
+
+ ASNQty = montha, // 鍏ュ簱鏁伴噺
+ SOQty = months, // 鍑哄簱鏁伴噺
+ ASNSOQty = countjie, // 缁撳瓨鏁伴噺
+
+ Weight = weight, // 鐞嗚閲嶉噺
+
+ NoticeDateTime = yearcount.SkuName + "骞�" + i + "鏈�", // 鍗曟嵁鏃堕棿
+ CreateTime = createTime, //鏃堕棿
+ };
+ infolist.Add(addmonthinfo);
+ }
+ montha = 0; //鏈堝叆搴�
+ months = 0; //鏈堝嚭搴�
+ }
+ //鍒ゆ柇鏄惁鏈夊勾鍑哄叆搴�
+ if (ruinfolist.Count > 0 || chuinfolist.Count > 0)
+ {
+ //骞翠俊鎭�
+ var addyearinfo = new AssSoDateStockInfoDto()
+ {
+ ASNNo = "", // 鍏ュ簱鍗曟嵁
+ SONo = "", // 鍑哄簱鍗曟嵁
+
+ SkuNo = "", // 鐗╂枡缂栫爜
+ SkuName = "", // 鐗╂枡鍚嶇О
+
+ NoticeType = "鏈勾绱", //缁熻绫诲埆
+
+ ASNQty = yeara, // 鍏ュ簱鏁伴噺
+ SOQty = years, // 鍑哄簱鏁伴噺
+ ASNSOQty = countjie, // 缁撳瓨鏁伴噺
+
+ Weight = weight, // 鐞嗚閲嶉噺
+
+ NoticeDateTime = yearcount.SkuName + "骞�", // 鍗曟嵁鏃堕棿
+ CreateTime = createTime, //鏃堕棿
+ };
+ infolist.Add(addyearinfo);
+ }
+ yeara = 0; //骞村叆搴�
+ years = 0; //骞村嚭搴�
+ }
+
+ infolist = infolist.OrderBy(x => x.CreateTime).ToList();
+ return infolist;
+
+ }
+ catch (Exception ex)
+ {
+
+ throw new Exception(ex.Message);
+ }
+ }
+
+
+ #endregion
+
#region 瀵煎嚭搴撳瓨
/// <summary>
/// 瀵煎嚭搴撳瓨鎬婚噺
--
Gitblit v1.8.0