From ac85d00b1c88e0088c9f6f23c09a4ed87415ad38 Mon Sep 17 00:00:00 2001 From: yyk <2336760928@qq.com> Date: 星期四, 18 七月 2024 15:54:39 +0800 Subject: [PATCH] 修改库存信息接口及页面。 --- Wms/WMS.BLL/DataServer/StockServer.cs | 328 +++++++++++++++++++++++++++++++++++++++++------------- 1 files changed, 247 insertions(+), 81 deletions(-) diff --git a/Wms/WMS.BLL/DataServer/StockServer.cs b/Wms/WMS.BLL/DataServer/StockServer.cs index 8259a64..ebf208d 100644 --- a/Wms/WMS.BLL/DataServer/StockServer.cs +++ b/Wms/WMS.BLL/DataServer/StockServer.cs @@ -5,9 +5,12 @@ using System.Runtime.Intrinsics.X86; using System.Security.Claims; using System.Text; +using System.Threading.Tasks; using Model.ModelDto; using Model.ModelDto.DataDto; +using Model.ModelDto.SysDto; using Model.ModelVm.DataVm; +using Model.ModelVm.SysVm; using SqlSugar; using WMS.DAL; using WMS.Entity.Context; @@ -31,92 +34,255 @@ /// <summary> /// 鏌ヨ搴撳瓨鎬婚噺 /// </summary> + /// <param name="selectType">鏌ヨ绫诲瀷 0锛氱墿鏂欎俊鎭� 1锛氭壒娆′俊鎭� 2锛氳川妫�淇℃伅 3锛氳揣涓讳俊鎭�</param> /// <param name="skuNo">鐗╂枡缂栫爜</param> /// <param name="skuName">鐗╂枡鍚嶇О</param> + /// <param name="ownerNo">璐т富缂栫爜</param> + /// <param name="ownerName">璐т富鍚嶇О</param> + /// <param name="lotNo">鎵规</param> + /// <param name="status">搴撳瓨鐘舵��</param> + /// <param name="inspectStatus">璐ㄦ鐘舵��</param> /// <returns></returns> - public List<MateDataStockDto> GetDataStockList(string skuNo, string skuName, string ownerNo, string ownerName, string lotNo) + public async Task<List<MateDataStockDto>> GetDataStockList(string selectType, string skuNo, string skuName, string ownerNo, string ownerName, string lotNo, string status, string inspectStatus) { - string str = "select stock.SkuNo,stock.SkuName,stock.LotNo,stock.LotText,stock.Standard,stock.Qty," + - "stock.LockQty,stock.FrozenQty,stock.OwnerNo,stock.OwnerName,(mate.Weight * stock.Qty) WeightSum " + - "from DataStock stock " + - "left join SysMaterials mate on stock.SkuNo = mate.SkuNo " + - "Where stock.IsDel = @isdel and mate.IsDel = 0 "; - //鍒ゆ柇鐗╂枡缂栫爜鏄惁涓虹┖ - if (!string.IsNullOrEmpty(skuNo)) + List<MateDataStockDto> modUser = new List<MateDataStockDto>(); + if (string.IsNullOrEmpty(selectType)) { - str += " and stock.SkuNo like @skuno"; - } - //鍒ゆ柇鐗╂枡鍚嶇О鏄惁涓虹┖ - if (!string.IsNullOrEmpty(skuName)) - { - str += " and stock.SkuName like @skuname"; - } - //鍒ゆ柇璐т富缂栫爜鏄惁涓虹┖ - if (!string.IsNullOrEmpty(ownerNo)) - { - str += " and stock.OwnerNo like @ownerNo"; - } - //鍒ゆ柇璐т富鍚嶇О鏄惁涓虹┖ - if (!string.IsNullOrEmpty(ownerName)) - { - str += " and stock.OwnerName like @ownerName"; - } - //鍒ゆ柇璐т富鍚嶇О鏄惁涓虹┖ - if (!string.IsNullOrEmpty(lotNo)) - { - str += " and stock.LotNo like @lotNo"; - } - //鎺掑簭 - str += " order by stock.SkuNo"; - List<MateDataStockDto> StockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new - { - isdel = "0", //鏄惁鍒犻櫎 - skuno = "%" + skuNo + "%", //鐗╂枡缂栫爜 - skuname = "%" + skuName + "%", //鐗╂枡鍚嶇О - ownerNo = "%" + ownerNo + "%", //璐т富缂栫爜 - ownerName = "%" + ownerName + "%", //璐т富鍚嶇О - lotNo = "%" + lotNo + "%", //鎵规鍙� - }); + modUser = await Db.Queryable<DataStock>() + .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), a => a.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), a => a.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo == lotNo) + .Where(a => a.IsDel == "0") + .Select(a => new MateDataStockDto() + { + SkuNo = a.SkuNo, //鐗╂枡缂栫爜 + SkuName = a.SkuName, //鐗╂枡鍚嶇О + Standard = a.Standard, //瑙勬牸 + LotNo = a.LotNo, //鎵规 + LotText = a.LotText, //鎵规鎻忚堪 + OwnerNo = a.OwnerNo, //璐т富缂栫爜 + OwnerName = a.OwnerName, //璐т富鍚嶇О + Status = status, //搴撳瓨鐘舵�� + InspectStatus = inspectStatus, //璐ㄦ鐘舵�� + Qty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.LotNo == a.LotNo) + .Sum(s => s.Qty), //搴撳瓨鏁伴噺 + //Qty = SqlFunc.AggregateSum(b.Qty), //搴撳瓨鏁伴噺 + LockQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.LotNo == a.LotNo) + .Sum(s => s.LockQty), //閿佸畾鏁伴噺 + FrozenQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.LotNo == a.LotNo) + .Sum(s => s.FrozenQty), //鍐荤粨鏁伴噺 + InspectQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.LotNo == a.LotNo) + .Sum(s => s.InspectQty), //鍙娊妫�鏁伴噺 + }).ToListAsync(); - //搴撳瓨鎬婚噺 - List<MateDataStockDto> StockListDto = new List<MateDataStockDto>(); - - foreach (var item in StockList) - { - StockListDto.Add(item); - ////鍒ゆ柇搴撳瓨鎬婚噺鏄惁鎷ユ湁鐗╂枡 - //if (StockListDto.Count > 0) - //{ - // int i = 0; - // //foreach寰幆搴撳瓨鎬婚噺 - // foreach (var dto in StockListDto) - // { - // //鍒ゆ柇鐗╂枡鍜屾壒娆℃槸鍚︾浉鍚� - // if (dto.SkuNo == item.SkuNo && dto.LotNo == item.LotNo) - // { - // dto.Qty = (Convert.ToDecimal(dto.Qty) + Convert.ToDecimal(item.Qty)).ToString(); - // dto.FrozenQty = (Convert.ToDecimal(dto.FrozenQty) + Convert.ToDecimal(item.FrozenQty)).ToString(); - // dto.LockQty = (Convert.ToDecimal(dto.LockQty) + Convert.ToDecimal(item.LockQty)).ToString(); - // dto.ResidueQty= (Convert.ToDecimal(dto.ResidueQty) + Convert.ToDecimal(item.ResidueQty)).ToString(); - // break; - // } - // i += 1; - // //鍒ゆ柇宸叉湁鐩稿悓鐗╂枡 - // if (i == StockListDto.Count) - // { - // StockListDto.Add(item); - // break; - // } - // } - //} - //else - //{ - // StockListDto.Add(item); - // continue; - //} + return modUser; } + else + { + var selectList = new List<DataStockDetail>(); + switch (selectType) + { + case "0": // 鐗╂枡淇℃伅 + modUser = await Db.Queryable<DataStock>() + .Where(a => a.IsDel == "0") + .GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard }) + .Select(a => new MateDataStockDto() + { + SkuNo = a.SkuNo, //鐗╂枡缂栫爜 + SkuName = a.SkuName, //鐗╂枡鍚嶇О + Standard = a.Standard, //瑙勬牸 + Qty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.SkuNo == a.SkuNo) + .Sum(s => s.Qty), //搴撳瓨鏁伴噺 + LockQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.SkuNo == a.SkuNo) + .Sum(s => s.LockQty), //閿佸畾鏁伴噺 + FrozenQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.SkuNo == a.SkuNo) + .Sum(s => s.FrozenQty), //鍐荤粨鏁伴噺 + InspectQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.SkuNo == a.SkuNo) + .Sum(s => s.InspectQty), //鍙娊妫�鏁伴噺 + }) + .ToListAsync(); - return StockListDto; + return modUser; + case "1": // 鎵规淇℃伅 + + modUser = await Db.Queryable<DataStock>() + .Where(a => a.IsDel == "0") + .GroupBy(a => new { a.LotNo, a.LotText }) + .Select(a => new MateDataStockDto() + { + LotNo = a.LotNo, //鎵规 + LotText = a.LotText, //鎵规鎻忚堪 + Qty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.LotNo == a.LotNo) + .Sum(s => s.Qty), //搴撳瓨鏁伴噺 + LockQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.LotNo == a.LotNo) + .Sum(s => s.LockQty), //閿佸畾鏁伴噺 + FrozenQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.LotNo == a.LotNo) + .Sum(s => s.FrozenQty), //鍐荤粨鏁伴噺 + InspectQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.LotNo == a.LotNo) + .Sum(s => s.InspectQty), //鍙娊妫�鏁伴噺 + }) + .ToListAsync(); + + return modUser; + case "2": // 璐ㄦ淇℃伅 + modUser = await Db.Queryable<DataStock>() + .LeftJoin<DataStockDetail>((a, b) => a.LotNo == b.LotNo) + .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), a => a.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), a => a.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), (a, b) => b.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), (a, b) => b.InspectStatus == inspectStatus) + .Where(a => a.IsDel == "0") + .GroupBy((a, b) => b.InspectStatus) + .Select((a, b) => new MateDataStockDto() + { + InspectStatus = b.InspectStatus, //璐ㄦ鐘舵�� + Qty = SqlFunc.AggregateSum(b.Qty), //搴撳瓨鏁伴噺 + LockQty = SqlFunc.AggregateSum(b.LockQty), //閿佸畾鏁伴噺 + FrozenQty = SqlFunc.AggregateSum(b.FrozenQty), //鍐荤粨鏁伴噺 + InspectQty = SqlFunc.AggregateSum(b.InspectQty), //鍙娊妫�鏁伴噺 + }) + .ToListAsync(); + + return modUser; + case "3": // 璐т富淇℃伅 + + modUser = await Db.Queryable<DataStock>() + .Where(a => a.IsDel == "0") + .GroupBy(a => new { a.OwnerNo, a.OwnerName }) + .Select(a => new MateDataStockDto() + { + OwnerNo = a.OwnerNo, //璐т富缂栫爜 + OwnerName = a.OwnerName, //璐т富鍚嶇О + Qty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.OwnerNo == a.OwnerNo) + .Sum(s => s.Qty), //搴撳瓨鏁伴噺 + LockQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.OwnerNo == a.OwnerNo) + .Sum(s => s.LockQty), //閿佸畾鏁伴噺 + FrozenQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.OwnerNo == a.OwnerNo) + .Sum(s => s.FrozenQty), //鍐荤粨鏁伴噺 + InspectQty = SqlFunc.Subqueryable<DataStockDetail>() + .WhereIF(!string.IsNullOrEmpty(skuNo), s => s.SkuNo == skuNo) + .WhereIF(!string.IsNullOrEmpty(skuName), s => s.SkuName == skuName) + .WhereIF(!string.IsNullOrEmpty(ownerNo), s => s.OwnerNo == ownerNo) + .WhereIF(!string.IsNullOrEmpty(ownerName), s => s.OwnerName == ownerName) + .WhereIF(!string.IsNullOrEmpty(lotNo), s => s.LotNo == lotNo) + .WhereIF(!string.IsNullOrEmpty(status), s => s.Status == status) + .WhereIF(!string.IsNullOrEmpty(inspectStatus), s => s.InspectStatus == inspectStatus) + .Where(s => s.IsDel == "0" && s.OwnerNo == a.OwnerNo) + .Sum(s => s.InspectQty), //鍙娊妫�鏁伴噺 + }) + .ToListAsync(); + return modUser; + default: + break; + } + + return modUser; + } } /// <summary> @@ -837,9 +1003,9 @@ //鍒ゆ柇鐗╂枡鏄惁鐩稿悓 if (dto.SkuNo == item.SkuNo) { - dto.Qty = (Convert.ToInt32(dto.Qty) + Convert.ToInt32(item.Qty)).ToString(); - dto.FrozenQty = (Convert.ToInt32(dto.FrozenQty) + Convert.ToInt32(item.FrozenQty)).ToString(); - dto.LockQty = (Convert.ToInt32(dto.LockQty) + Convert.ToInt32(item.LockQty)).ToString(); + 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; } -- Gitblit v1.8.0