JC34WMS.git - Gitblit

			@@ -866,75 +866,37 @@
			/// </summary>
			/// <param name="skuNo">物料编码</param>
			/// <param name="skuName">物料名称</param>
			/// <param name="InspectStatus">质检状态</param>
			/// <returns></returns>
			public List<MateDataStockDto> GetDataStockListDaoChu(string skuNo, string skuName)
			public List<MateDataStockDto> GetDataStockListDaoChu(string skuNo, string skuName, string InspectStatus)
			{
			string str = "select stock.SkuNo,stock.SkuName,stock.Standard,stock.Qty,stock.LockQty,stock.FrozenQty,(mate.Weight * stock.Qty) WeightSum from DataStock stock left join SysMaterials mate on stock.SkuNo = mate.SkuNo Where stock.IsDel = @isdel";
			//判断物料编码是否为空
			if (!string.IsNullOrEmpty(skuNo))
			{
			str += " and stock.SkuNo like @skuno";
			}
			//判断物料名称是否为空
			if (!string.IsNullOrEmpty(skuName))
			{
			str += " and stock.SkuName like @skuname";
			}
			//排序
			str += " order by stock.SkuNo";
			List<MateDataStockDto> StockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
			{
			isdel = "0", //是否删除
			skuno = "%" + skuNo + "%", //物料编码
			skuname = "%" + skuName + "%" //物料名称
			});

			//库存总量
			List<MateDataStockDto> StockListDto = new List<MateDataStockDto>();

			foreach (var item in StockList)
			{
			//判断库存总量是否拥有物料
			if (StockListDto.Count > 0)
			// 获取入库单明细信息 GetDataStockListDaoChu
			var asnList = Db.Queryable<DataStockDetail>()
			.Where(m => m.IsDel == "0")
			.WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo)).Distinct()
			.WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName)).Distinct()
			.WhereIF(!string.IsNullOrEmpty(InspectStatus), a => a.InspectStatus == InspectStatus)
			.Select(a => new MateDataStockDto()
			{
			int i = 0;
			//foreach循环库存总量
			foreach (var dto in StockListDto)
			{
			//判断物料是否相同
			if (dto.SkuNo == item.SkuNo)
			{
			dto.Qty = Convert.ToInt32(dto.Qty) + Convert.ToInt32(item.Qty);
			dto.FrozenQty = Convert.ToInt32(dto.FrozenQty) + Convert.ToInt32(item.FrozenQty);
			dto.LockQty = Convert.ToInt32(dto.LockQty) + Convert.ToInt32(item.LockQty);
			dto.ResidueQty = (Convert.ToInt32(dto.ResidueQty) + Convert.ToInt32(item.ResidueQty)).ToString();
			break;
			}
			i += 1;
			//判断已有相同物料
			if (i == StockListDto.Count)
			{
			StockListDto.Add(item);
			break;
			}
			}
			}
			else
			{
			StockListDto.Add(item);
			continue;
			}
			}
			foreach (var item in StockListDto)
			{
			//物料编码，加上单引号是防止导出到excel自动把前面的0给去掉
			if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
			{
			item.SkuNo = $"'{item.SkuNo}";
			}
			}
			SkuNo = a.SkuNo,//物料编码
			SkuName = a.SkuName,//物料名称
			Standard = a.Standard,//物料规格
			LotNo = a.LotNo,//批次号
			OwnerNo = a.OwnerNo,//货主编号
			OwnerName = a.OwnerName,//货主名称
			Status = ((a.Status == "0") ? "待分配" : ((a.Status == "1") ? "部分分配" : ((a.Status == "2") ? "盘点锁定" : ((a.Status == "3") ? "移库锁定" : "异常锁定")))),//库存状态
			InspectStatus = ((a.InspectStatus == "0") ? "待质检" : (a.InspectStatus == "1" ? "检验合格" : "不合格")),//质检状态
			Qty = SqlFunc.AggregateSum(Convert.ToInt32((a.Qty))),//库存数量
			LockQty = SqlFunc.AggregateSum(Convert.ToInt32(a.LockQty)),//锁定数量
			FrozenQty = SqlFunc.AggregateSum(Convert.ToInt32(a.FrozenQty)),//冻结数量
			InspectQty = SqlFunc.AggregateSum(Convert.ToInt32(a.InspectQty))//可抽检数量
			})
			.Distinct()
			.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard, a.LotNo, a.Status, a.InspectStatus, a.OwnerNo, a.OwnerName, a.Qty, a.LockQty, a.FrozenQty, a.InspectQty })
			.ToList();

			return StockListDto;
			Console.WriteLine("结果：" + asnList);
			return asnList;
			}

			/// <summary>
			@@ -944,127 +906,114 @@
			/// <param name="skuName">物料名称</param>
			/// <param name="lotNo">批次</param>
			/// <param name="locatNo">储位地址</param>
			/// <param name="palletNo">托盘条码</param>
			/// <param name="status">库存状态</param>
			/// <param name="inspectStatus">质检状态</param>
			/// <param name="ownerNo">货物编号</param>
			/// <param name="ownerName">货物名称</param>
			/// <param name="startTime">开始时间</param>
			/// <param name="endTime">结束时间</param>
			/// <returns></returns>
			public List<StockDetailDto> GetInventoryList1DaoChu(string skuNo, string skuName, string lotNo, string locatNo,
			string palletNo, string status, string inspectStatus, string startTime, string endTime)
			public List<StockDetailDto> GetDataStockList1DaoChu(string skuNo, string skuName, string lotNo, string locatNo, string status, string inspectStatus, string ownerNo, string ownerName, string startTime, string endTime)
			{
			string str = "select detail.*,house.WareHouseName as WareHouseName,roadway.RoadwayName as RoadwayName from DataStockDetail detail left join SysStorageRoadway roadway on detail.RoadwayNo = roadway.RoadwayNo left join SysWareHouse house on detail.WareHouseNo = house.WareHouseNo Where detail.IsDel = @isdel";
			//判断物料编码是否为空
			if (!string.IsNullOrEmpty(skuNo))
			{
			str += " and detail.SkuNo = @skuno";
			}
			//判断物料名称是否为空
			if (!string.IsNullOrEmpty(skuName))
			{
			str += " and detail.SkuName = @skuname";
			}
			//判断批次是否为空
			if (!string.IsNullOrEmpty(lotNo))
			{
			str += " and detail.LotNo = @lotno";
			}
			//判断储位地址是否为空
			if (!string.IsNullOrEmpty(locatNo))
			{
			str += " and detail.LocatNo = @locatno";
			}
			//判断托盘条码是否为空
			if (!string.IsNullOrEmpty(palletNo))
			{
			str += " and detail.PalletNo = @palletno";
			}
			//判断库存状态是否为空
			if (!string.IsNullOrEmpty(status))
			{
			str += " and detail.Status = @status";
			}
			//判断质检状态是否为空
			if (!string.IsNullOrEmpty(inspectStatus))
			{
			str += " and detail.InspectStatus = @inspectstatus";
			}
			if (!string.IsNullOrEmpty(startTime))
			{
			str += $" and detail.CompleteTime >= '{startTime}'";
			}
			if (!string.IsNullOrEmpty(endTime))
			{
			endTime = Convert.ToDateTime(endTime).AddDays(1).ToString();
			str += $" and detail.CompleteTime < '{endTime}'";
			}
			//排序
			str += " order by detail.SkuNo,detail.PalletNo,detail.LotNo";
			List<StockDetailDto> stockDetailsList = Db.Ado.SqlQuery<StockDetailDto>(str, new
			{
			isdel = "0", //是否删除
			skuno = skuNo, //物料编码
			skuname = skuName, //物料名称
			lotno = lotNo, //批次
			locatno = locatNo, //储位地址
			palletno = palletNo, //托盘条码
			status = status, //库存状态
			inspectstatus = inspectStatus //质检状态
			});
			foreach (var item in stockDetailsList)
			{
			//储位地址，加上单引号是防止导出到excel自动把前面的0给去掉
			if (!string.IsNullOrEmpty(item.LocatNo) && item.LocatNo.Substring(0, 1) == "0")
			// 获取明细信息
			var asnList = Db.Queryable<DataStockDetail, DataStock>
			((tb1, tb2) => new JoinQueryInfos(
			JoinType.Left, tb1.Id == tb2.Id
			))
			.Where((tb1, tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
			.WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.CreateTime >= Convert.ToDateTime(startTime))
			.WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
			.WhereIF(!string.IsNullOrEmpty(skuNo), (tb1, tb2) => tb2.SkuNo.Contains(skuNo))
			.WhereIF(!string.IsNullOrEmpty(skuName), (tb1, tb2) => tb2.SkuName.Contains(skuName))
			.WhereIF(!string.IsNullOrEmpty(ownerNo), (tb1, tb2) => tb2.OwnerNo.Contains(ownerNo))
			.WhereIF(!string.IsNullOrEmpty(ownerName), (tb1, tb2) => tb2.OwnerName.Contains(ownerName))
			.WhereIF(!string.IsNullOrEmpty(lotNo), (tb1, tb2) => tb2.LotNo.Contains(lotNo))
			.WhereIF(!string.IsNullOrEmpty(status), (tb1, tb2) => tb1.Status.Contains(status))
			.WhereIF(!string.IsNullOrEmpty(inspectStatus), (tb1, tb2) => tb1.InspectStatus.Contains(inspectStatus))
			//.GroupBy((tb1,tb2) => new { tb2.LotNo, tb2.SkuNo, tb2.SkuName })
			.Select((tb1, tb2) => new StockDetailDto()
			{
			item.LocatNo = $"'{item.LocatNo}";
			}
			//物料编码
			if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
			{
			item.SkuNo = $"'{item.SkuNo}";
			}
			//库存状态
			switch (item.Status)
			{
			case "0":
			item.Status = "待分配";
			break;
			case "1":
			item.Status = "部分分配";
			break;
			case "2":
			item.Status = "已分配";
			break;
			case "3":
			item.Status = "盘点锁定";
			break;
			case "4":
			item.Status = "移库锁定";
			break;
			default:
			break;
			}
			//质检状态
			switch (item.InspectStatus)
			{
			case "0":
			item.InspectStatus = "待检验";
			break;
			case "1":
			item.InspectStatus = "检验合格";
			break;
			case "2":
			item.InspectStatus = "不合格";
			break;
			case "4":
			item.InspectStatus = "放置期";
			break;
			default:
			break;
			}
			Status = tb1.Status,
			InspectStatus = tb1.InspectStatus,
			LotNo = tb1.LotNo,
			SkuNo = tb2.SkuNo,
			SkuName = tb2.SkuName,
			OwnerNo = tb2.OwnerNo,
			OwnerName = tb2.OwnerName,
			CompleteTime = tb2.CreateTime,
			});

			}
			return stockDetailsList;
			// 获取出库单明细信息
			var soList = Db.Queryable<DataStockDetail, DataStock>
			(((tb1, tb2) => new JoinQueryInfos(
			JoinType.Left, tb1.Id == tb2.Id
			)))
			.Where((tb1, tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
			.WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.CreateTime >= Convert.ToDateTime(startTime))
			.WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
			.WhereIF(!string.IsNullOrEmpty(skuNo), (tb1, tb2) => tb2.SkuNo.Contains(skuNo))
			.WhereIF(!string.IsNullOrEmpty(skuName), (tb1, tb2) => tb2.SkuName.Contains(skuName))
			.WhereIF(!string.IsNullOrEmpty(ownerNo), (tb1, tb2) => tb2.OwnerNo.Contains(ownerNo))
			.WhereIF(!string.IsNullOrEmpty(ownerName), (tb1, tb2) => tb2.OwnerName.Contains(ownerName))
			.WhereIF(!string.IsNullOrEmpty(lotNo), (tb1, tb2) => tb2.LotNo.Contains(lotNo))
			.WhereIF(!string.IsNullOrEmpty(status), (tb1, tb2) => tb1.Status.Contains(status))
			.WhereIF(!string.IsNullOrEmpty(inspectStatus), (tb1, tb2) => tb1.InspectStatus.Contains(inspectStatus))
			//.GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
			.Select((tb1, tb2) => new StockDetailDto()
			{
			Status = tb1.Status,
			InspectStatus = tb1.InspectStatus,
			LotNo = tb1.LotNo,
			SkuNo = tb2.SkuNo,
			SkuName = tb2.SkuName,
			OwnerNo = tb2.OwnerNo,
			OwnerName = tb2.OwnerName,
			CompleteTime = tb2.CreateTime,
			});

			// 获取取样拣货信息
			var samplingList = Db.Queryable<DataStockDetail, DataStock>
			(((tb1, tb2) => new JoinQueryInfos(
			JoinType.Left, tb1.Id == tb2.Id
			)))
			.Where((tb1, tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
			.WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.CreateTime >= Convert.ToDateTime(startTime))
			.WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
			.WhereIF(!string.IsNullOrEmpty(skuNo), (tb1, tb2) => tb2.SkuNo.Contains(skuNo))
			.WhereIF(!string.IsNullOrEmpty(skuName), (tb1, tb2) => tb2.SkuName.Contains(skuName))
			.WhereIF(!string.IsNullOrEmpty(ownerNo), (tb1, tb2) => tb2.OwnerNo.Contains(ownerNo))
			.WhereIF(!string.IsNullOrEmpty(ownerName), (tb1, tb2) => tb2.OwnerName.Contains(ownerName))
			.WhereIF(!string.IsNullOrEmpty(lotNo), (tb1, tb2) => tb2.LotNo.Contains(lotNo))
			.WhereIF(!string.IsNullOrEmpty(status), (tb1, tb2) => tb1.Status.Contains(status))
			.WhereIF(!string.IsNullOrEmpty(inspectStatus), (tb1, tb2) => tb1.InspectStatus.Contains(inspectStatus))
			//.GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
			.Select((tb1, tb2) => new StockDetailDto()
			{
			Status = tb1.Status,
			InspectStatus = tb1.InspectStatus,
			LotNo = tb1.LotNo,
			SkuNo = tb2.SkuNo,
			SkuName = tb2.SkuName,
			OwnerNo = tb2.OwnerNo,
			OwnerName = tb2.OwnerName,
			CompleteTime = tb2.CreateTime,
			});

			// 合并集合
			var dataList = Db.UnionAll(asnList, soList, samplingList)
			.Select(a => new StockDetailDto()
			{
			Status = a.Status,
			InspectStatus = a.InspectStatus,
			LotNo = a.LotNo,
			SkuNo = a.SkuNo,
			SkuName = a.SkuName,
			OwnerNo = a.OwnerNo,
			OwnerName = a.OwnerName,
			CompleteTime = a.CompleteTime,
			}).ToList();

			return dataList;
			}
			#endregion
			}