JC24WMS.git - Gitblit

			@@ -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>
			/// 导出库存总量