From be4bb3387730ebe095283399c77a88b5534c09e3 Mon Sep 17 00:00:00 2001
From: yuyou_x <2336760928@qq.com>
Date: 星期六, 16 三月 2024 15:46:52 +0800
Subject: [PATCH] 增加年度报表页面，修改年度报表自适应代码，编写后台获取单据总表与明细方法。
---
Wms/WMS.BLL/DataServer/StockServer.cs | 295 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 295 insertions(+), 0 deletions(-)
diff --git a/Wms/WMS.BLL/DataServer/StockServer.cs b/Wms/WMS.BLL/DataServer/StockServer.cs
index 6c0ff69..00dd6d4 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
@@ -456,6 +458,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