From 131347ad63bb534b5abb6dbc979f0a47d780d8ff Mon Sep 17 00:00:00 2001
From: hwh <332078369@qq.com>
Date: 星期三, 15 一月 2025 15:06:05 +0800
Subject: [PATCH] ·
---
Wms/WMS.BLL/DataServer/StockServer.cs | 642 ++++++++++++++++++++++++++++++++--------------------------
1 files changed, 355 insertions(+), 287 deletions(-)
diff --git a/Wms/WMS.BLL/DataServer/StockServer.cs b/Wms/WMS.BLL/DataServer/StockServer.cs
index 8259a64..65e4416 100644
--- a/Wms/WMS.BLL/DataServer/StockServer.cs
+++ b/Wms/WMS.BLL/DataServer/StockServer.cs
@@ -2,14 +2,22 @@
using System.Collections;
using System.Collections.Generic;
using System.Linq;
+using System.Linq.Expressions;
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 MySqlConnector;
using SqlSugar;
using WMS.DAL;
+using WMS.Entity.BllAsnEntity;
+using WMS.Entity.BllQualityEntity;
+using WMS.Entity.BllSoEntity;
using WMS.Entity.Context;
using WMS.Entity.DataEntity;
using WMS.Entity.SysEntity;
@@ -29,94 +37,144 @@
#region 搴撳瓨鏄庣粏
/// <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))
- {
- 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 + "%", //鎵规鍙�
- });
+ Expression<Func<DataStockDetail, bool>> item = Expressionable.Create<DataStockDetail>()
+ .AndIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
+ .AndIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
+ .AndIF(!string.IsNullOrEmpty(ownerNo), a => a.OwnerNo.Contains(ownerNo))
+ .AndIF(!string.IsNullOrEmpty(ownerName), a => a.OwnerName.Contains(ownerName))
+ .AndIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
+ .AndIF(!string.IsNullOrEmpty(status), a => a.Status == status)
+ .AndIF(!string.IsNullOrEmpty(inspectStatus), a => a.InspectStatus == inspectStatus)
+ .And(a => a.IsDel == "0")
+ .ToExpression();//娉ㄦ剰 杩欎竴鍙� 涓嶈兘灏�
- //搴撳瓨鎬婚噺
- List<MateDataStockDto> StockListDto = new List<MateDataStockDto>();
- foreach (var item in StockList)
+ var data = Db.Queryable<DataStockDetail>().Where(item);
+
+ List<MateDataStockDto> data2;
+ switch (selectType)
{
- 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;
- //}
+ // 鎸夌墿鏂欑粺璁�
+ case "0":
+ data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard })
+ .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.AggregateSumNoNull(a.Qty), //搴撳瓨鏁伴噺
+ LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //閿佸畾鏁伴噺
+ FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
+ InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty)
+ }).ToListAsync();
+ break;
+
+ // 鎸夋壒娆＄粺璁�
+ case "1":
+ data2 = await data.GroupBy(a => new { a.LotNo, a.LotText, a.SkuNo, a.SkuName, a.Standard })
+ .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.AggregateSumNoNull(a.Qty), //搴撳瓨鏁伴噺
+ LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //閿佸畾鏁伴噺
+ FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
+ InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty)
+ }).ToListAsync();
+ break;
+
+ // 鎸夎川妫�鐘舵�佺粺璁�
+ case "2":
+ data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard,a.InspectStatus})
+ .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 = a.InspectStatus, //璐ㄦ鐘舵��
+ Qty = SqlFunc.AggregateSumNoNull(a.Qty), //搴撳瓨鏁伴噺
+ LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //閿佸畾鏁伴噺
+ FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
+ InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
+ }).ToListAsync();
+ break;
+
+ // 鎸夎揣涓荤粺璁�
+ case "3":
+ data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard, a.OwnerNo, a.OwnerName })
+ .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.AggregateSumNoNull(a.Qty), //搴撳瓨鏁伴噺
+ LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //閿佸畾鏁伴噺
+ FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
+ InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
+ }).ToListAsync();
+ break;
+
+ // 榛樿缁熻锛堢墿鏂� 鎵规 璐ㄦ 搴撳瓨鐘舵�� 璐т富)
+ default:
+ data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard ,a.LotNo, a.LotText, a.OwnerNo, a.OwnerName , a.InspectStatus })
+ .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 = a.InspectStatus, //璐ㄦ鐘舵��
+ Qty = SqlFunc.AggregateSumNoNull(a.Qty), //搴撳瓨鏁伴噺
+ LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //閿佸畾鏁伴噺
+ FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
+ InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
+ }).ToListAsync();
+ break;
}
- return StockListDto;
+ return data2;
}
/// <summary>
@@ -275,90 +333,92 @@
/// <returns></returns>
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' ";
- //鍑哄簱
- string cstr = "select LotNo,SkuNo,SkuName,SUM(Qty) CQty,SUM(FactQty) CFactQty,SUM(CompleteQty) CompleteQty,SUM(AllotQty) CAllotQty from BllExportNoticeDetail where IsDel = '0' ";
-
- //鍒ゆ柇鐗╂枡鍙锋槸鍚︿负绌�
- if (!string.IsNullOrEmpty(skuNo))
- {
- rstr += " and SkuNo like @skuno";
- cstr += " and SkuNo like @skuno";
- }
- //鍒ゆ柇鐗╂枡鍚嶇О鏄惁涓虹┖
- if (!string.IsNullOrEmpty(skuName))
- {
- rstr += " and SkuName like @skuname";
- cstr += " and SkuName like @skuname";
- }
- //鍒ゆ柇鎵规鍙锋槸鍚︿负绌�
- if (!string.IsNullOrEmpty(lotNo))
- {
- 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))
- {
- endTime = DateTime.Parse(endTime).AddDays(1).ToString();
- rstr += " and UpdateTime < @endTime";
- cstr += " and UpdateTime < @endTime";
- }
-
- rstr += " group by LotNo,SkuNo,SkuName";
- cstr += " group by LotNo,SkuNo,SkuName";
- //鍏ュ簱
- List<TotalRecordDto> totalHListData = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
- {
- skuno = "%" + skuNo + "%", //鐗╂枡鍙�
- skuname = "%" + skuName + "%", //鐗╂枡鍚嶇О
- lotno = "%" + lotNo + "%", //鎵规鍙�
- startTime, //寮�濮嬫椂闂�
- endTime //缁撴潫鏃堕棿
-
- });
- List<TotalRecordDto> totalCListData = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
- {
- skuno = "%" + skuNo + "%", //鐗╂枡鍙�
- skuname = "%" + skuName + "%", //鐗╂枡鍚嶇О
- lotno = "%" + lotNo + "%", //鎵规鍙�
- startTime, //寮�濮嬫椂闂�
- endTime //缁撴潫鏃堕棿
- });
-
- //鍚堝苟鏁版嵁
- int a = 0;
- foreach (var h in totalHListData)
- {
- if (a >= totalCListData.Count)
+ // 鑾峰彇鍏ュ簱鍗曟槑缁嗕俊鎭�
+ var asnList = Db.Queryable<BllArrivalNoticeDetail>()
+ .Where(m => m.IsDel == "0")
+ .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
+ .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
+ .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
+ .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
+ .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
+ .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
+ .Select(a => new TotalRecordDto()
{
- continue;
- }
- foreach (var c in totalCListData)
+ LotNo=a.LotNo,
+ SkuNo=a.SkuNo,
+ SkuName=a.SkuName,
+ RQty = SqlFunc.AggregateSumNoNull(a.Qty),
+ RFactQty = (decimal)SqlFunc.AggregateSum(a.FactQty),
+ RCompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
+ CQty = 0,
+ CFactQty = 0,
+ CAllotQty = 0,
+ CompleteQty = 0
+ });
+
+ // 鑾峰彇鍑哄簱鍗曟槑缁嗕俊鎭�
+ var soList = Db.Queryable<BllExportNoticeDetail>()
+ .Where(m => m.IsDel == "0")
+ .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
+ .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
+ .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
+ .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
+ .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
+ .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
+ .Select(a => new TotalRecordDto()
{
- //鍒ゆ柇鍑哄簱鏄惁鎷ユ湁鐩稿悓鎵规 涓旂墿鏂欑浉鍚�
- if (h.LotNo == c.LotNo || h.LotNo.Contains(c.LotNo) && h.SkuNo == c.SkuNo && h.SkuName == c.SkuName)
- {
- h.CQty = c.CQty; //鍑哄簱鏁伴噺
- h.CAllotQty = c.CAllotQty; //鍒嗛厤鏁伴噺
- h.CFactQty = c.CFactQty; //涓嬫灦鏁伴噺
- h.CompleteQty = c.CompleteQty; //鎷ｈ揣鏁伴噺
- //h.SONo = c.SONo; //鍑哄簱鍗曞彿
+ LotNo = a.LotNo,
+ SkuNo = a.SkuNo,
+ SkuName = a.SkuName,
+ RQty = 0,
+ RFactQty = 0,
+ RCompleteQty = 0,
+ CQty = SqlFunc.AggregateSumNoNull(a.Qty),
+ CFactQty = (decimal)SqlFunc.AggregateSum(a.FactQty),
+ CAllotQty = (decimal)SqlFunc.AggregateSum(a.AllotQty),
+ CompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
+ });
+ // 鑾峰彇鍙栨牱鎷ｈ揣淇℃伅
+ var samplingList = Db.Queryable<BllSamplingDetails>()
+ .Where(m => m.IsDel == "0")
+ .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
+ .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
+ .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
+ .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
+ .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
+ .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
+ .Select(a => new TotalRecordDto()
+ {
+ LotNo = a.LotNo,
+ SkuNo = a.SkuNo,
+ SkuName = a.SkuName,
+ RQty = 0,
+ RFactQty = 0,
+ RCompleteQty = 0,
+ CQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
+ CFactQty = 0,
+ CAllotQty = 0,
+ CompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
+ });
- a += 1;
+ // 鍚堝苟闆嗗悎
+ var dataList = Db.UnionAll(asnList, soList, samplingList)
+ .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
+ .Select(a => new TotalRecordDto()
+ {
+ LotNo = a.LotNo,
+ SkuNo = a.SkuNo,
+ SkuName = a.SkuName,
+ RQty = SqlFunc.AggregateSumNoNull(a.RQty),
+ RFactQty = (decimal)SqlFunc.AggregateSum(a.RFactQty),
+ RCompleteQty = (decimal)SqlFunc.AggregateSum(a.RCompleteQty),
+ CQty = SqlFunc.AggregateSumNoNull(a.CQty),
+ CFactQty = (decimal)SqlFunc.AggregateSum(a.CFactQty),
+ CAllotQty = (decimal)SqlFunc.AggregateSum(a.CAllotQty),
+ CompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty)
+ }).ToList();
- break;
- }
- }
- }
-
- return totalHListData;
+ return dataList;
}
/// <summary>
@@ -371,131 +431,139 @@
/// <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' ";
- //鍑哄簱
- string cstr = "select notice.SONo,allot.LotNo,allot.TaskNo CTaskNo,allot.SkuNo,allot.SkuName,allot.PalletNo," +
- "allot.UpdateTime 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' ";
-
- //鍒ゆ柇鐗╂枡鍙锋槸鍚︿负绌�
- if (!string.IsNullOrEmpty(skuNo))
- {
- rstr += " and notice.SkuNo like @skuno";
- cstr += " and allot.SkuNo like @skuno";
- }
- //鍒ゆ柇鐗╂枡鍚嶇О鏄惁涓虹┖
- if (!string.IsNullOrEmpty(skuName))
- {
- rstr += " and notice.SkuName like @skuname";
- cstr += " and allot.SkuName like @skuname";
- }
- //鍒ゆ柇鎵规鍙锋槸鍚︿负绌�
- if (!string.IsNullOrEmpty(lotNo))
- {
- 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))
- {
- endTime = DateTime.Parse(endTime).AddDays(1).ToString();
- rstr += " and bind.CompleteTime < @endTime";
- cstr += " and allot.UpdateTime < @endTime";
- }
-
- //rstr += " group by bind.LotNo,bind.SkuNo,bind.SkuName";
- //cstr += " group by LotNo,SkuNo,SkuName";
- //鍏ュ簱
- List<TotalRecordDto> totalRList = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
- {
- isdel = "0", //鏄惁鍒犻櫎
- skuno = "%" + skuNo + "%", //鐗╂枡鍙�
- skuname = "%" + skuName + "%", //鐗╂枡鍚嶇О
- lotno = "%" + lotNo + "%", //鎵规鍙�
- palletno = "%" + palletNo + "%", //鎵樼洏鍙�
- startTime = startTime,
- endTime = endTime
- });
- //鍑哄簱
- List<TotalRecordDto> totalCList = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
- {
- isdel = "0", //鏄惁鍒犻櫎
- skuno = "%" + skuNo + "%", //鐗╂枡鍙�
- skuname = "%" + skuName + "%", //鐗╂枡鍚嶇О
- lotno = "%" + lotNo + "%", //鎵规鍙�
- palletno = "%" + palletNo + "%", //鎵樼洏鍙�
- startTime = startTime,
- endTime = endTime
- });
-
- //鍚堝苟
- foreach (var c in totalCList)
- {
- int i = 0;
- foreach (var r in totalRList)
+ // 鑾峰彇鍏ュ簱鍗曟槑缁嗕俊鎭�
+ var asnList = Db.Queryable<BllPalletBind,BllArrivalNoticeDetail>
+ ((tb1,tb2)=>new JoinQueryInfos(
+ JoinType.Left, tb1.ASNDetailNo == tb2.Id
+ ))
+ .Where((tb1,tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
+ .WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.CompleteTime >= Convert.ToDateTime(startTime))
+ .WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.CompleteTime <= Convert.ToDateTime(endTime).AddDays(1))
+ .WhereIF(!string.IsNullOrEmpty(palletNo), tb1 => tb1.PalletNo.Contains(palletNo))
+ .WhereIF(!string.IsNullOrEmpty(skuNo), (tb1,tb2) => tb2.SkuNo.Contains(skuNo))
+ .WhereIF(!string.IsNullOrEmpty(skuName), (tb1, tb2) => tb2.SkuName.Contains(skuName))
+ .WhereIF(!string.IsNullOrEmpty(lotNo), (tb1, tb2) => tb2.LotNo.Contains(lotNo))
+ //.GroupBy((tb1,tb2) => new { tb2.LotNo, tb2.SkuNo, tb2.SkuName })
+ .Select((tb1,tb2) => new TotalRecordDto()
{
- i += 1;
- //鍒ゆ柇鍏ュ簱涓槸鍚﹀瓨鍦ㄥ嚭搴撲腑鐨勬壒娆＄墿鏂欐墭鐩�
- if (r.PalletNo == c.PalletNo && r.LotNo == c.LotNo && r.SkuNo == c.SkuNo && r.SkuName == c.SkuName)
- {
- if (r.SONo != null)
- {
- if (r.SONo != c.SONo)
- {
- totalRList.Add(c);
- }
- }
- if (c.CTaskNo != "" && r.SONo == null || r.SONo == c.SONo)
- {
- r.SONo = c.SONo; //鍑哄簱鍗曞彿
- r.CQty += c.CQty; //鍑哄簱鏁伴噺
- r.CAllotQty += c.CAllotQty; //鍒嗛厤鏁伴噺
- r.CFactQty += c.CFactQty; //涓嬫灦鏁伴噺
- r.CompleteQty += c.CompleteQty; //鎷ｈ揣鏁伴噺
- r.CCreateTime = c.CCreateTime; //鎷ｈ揣鏃堕棿
- }
- if (r.CompleteTime == null)
- {
- r.RCompleteQty = 0;
- }
- break;
- }
- if (i == totalRList.Count)
- {
- totalRList.Add(c);
- break;
- }
- }
- }
- if (totalCList.Count == 0)
- {
- foreach (var r in totalRList)
- {
- if (r.CompleteTime == null)
- {
- r.RCompleteQty = 0;
- continue;
- }
- }
- }
+ ASNNo = tb2.ASNNo,
+ LotNo = tb1.LotNo,
+ SkuNo = tb2.SkuNo,
+ SkuName = tb2.SkuName,
+ PalletNo = tb1.PalletNo,
+ RQty = tb1.Qty,
+ RFactQty = tb1.Qty,
+ RCompleteQty = tb1.Qty,
+ CompleteTime = tb1.CompleteTime,
+ LotText = tb1.LotText,
+ SupplierLot = tb2.SupplierLot,
+
+ SONo = "",
+ CTaskNo = "",
+ CCreateTime = null,
+ CQty = 0,
+ CFactQty = 0,
+ CAllotQty = 0,
+ CompleteQty = 0
+ });
- return totalRList;
+ // 鑾峰彇鍑哄簱鍗曟槑缁嗕俊鎭�
+ var soList = Db.Queryable<BllExportAllot, BllExportNoticeDetail>
+ (((tb1, tb2) => new JoinQueryInfos(
+ JoinType.Left, tb1.SODetailNo == tb2.Id
+ )))
+ .Where((tb1, tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
+ .WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.UpdateTime >= Convert.ToDateTime(startTime))
+ .WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.UpdateTime <= Convert.ToDateTime(endTime).AddDays(1))
+ .WhereIF(!string.IsNullOrEmpty(palletNo), tb1 => tb1.PalletNo.Contains(palletNo))
+ .WhereIF(!string.IsNullOrEmpty(skuNo), tb1 => tb1.SkuNo.Contains(skuNo))
+ .WhereIF(!string.IsNullOrEmpty(skuName), tb1 => tb1.SkuName.Contains(skuName))
+ .WhereIF(!string.IsNullOrEmpty(lotNo), tb1 => tb1.LotNo.Contains(lotNo))
+ //.GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
+ .Select((tb1, tb2) => new TotalRecordDto()
+ {
+ ASNNo = "",
+ LotNo = tb1.LotNo,
+ SkuNo = tb2.SkuNo,
+ SkuName = tb2.SkuName,
+ PalletNo = tb1.PalletNo,
+ RQty = 0,
+ RFactQty = 0,
+ RCompleteQty = 0,
+ CompleteTime = null,
+ LotText = tb1.LotText,
+ SupplierLot = tb2.SupplierLot,
+
+ SONo = tb2.SONo,
+ CTaskNo = tb1.TaskNo,
+ CCreateTime = tb1.UpdateTime,
+ CQty = tb1.Qty,
+ CFactQty = tb1.Qty,
+ CAllotQty = tb1.Qty,
+ CompleteQty = (decimal)tb1.CompleteQty
+ });
+
+ // 鑾峰彇鍙栨牱鎷ｈ揣淇℃伅
+ var samplingList = Db.Queryable<BllSamplingDetails>()
+ .Where(m => m.IsDel == "0")
+ .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
+ .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
+ .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
+ .WhereIF(!string.IsNullOrEmpty(palletNo), a => a.PalletNo.Contains(palletNo))
+ .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
+ .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
+ //.GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
+ .Select(a => new TotalRecordDto()
+ {
+ ASNNo = "",
+ LotNo = a.LotNo,
+ SkuNo = a.SkuNo,
+ SkuName = a.SkuName,
+ PalletNo = a.PalletNo,
+ RQty = 0,
+ RFactQty = 0,
+ RCompleteQty = 0,
+ CompleteTime = null,
+ LotText = "",
+ SupplierLot = "",
+
+ SONo = a.QcNo,
+ CTaskNo = "",
+ CCreateTime = a.CreateTime,
+ CQty = (decimal)a.CompleteQty,
+ CFactQty = 0,
+ CAllotQty = 0,
+ CompleteQty = (decimal)a.CompleteQty
+ });
+
+ // 鍚堝苟闆嗗悎
+ var dataList = Db.UnionAll(asnList, soList, samplingList)
+ .Select(a => new TotalRecordDto()
+ {
+ ASNNo = a.ASNNo,
+ LotNo = a.LotNo,
+ SkuNo = a.SkuNo,
+ SkuName = a.SkuName,
+ PalletNo = a.PalletNo,
+ RQty = a.RQty,
+ RFactQty = a.RFactQty,
+ RCompleteQty = a.RCompleteQty,
+ CompleteTime = a.CompleteTime,
+ LotText = a.LotText,
+ SupplierLot = a.SupplierLot,
+
+ SONo = a.SONo,
+ CTaskNo = a.CTaskNo,
+ CCreateTime = a.CCreateTime,
+ CQty = a.CQty,
+ CFactQty = a.CFactQty,
+ CAllotQty = a.CAllotQty,
+ CompleteQty = a.CompleteQty
+ }).ToList();
+
+ return dataList;
}
#endregion
@@ -512,7 +580,8 @@
{
try
{
- string str = "select* from SysMaterials where SkuNo in (select a.SkuNo from BllArrivalNoticeDetail a inner join BllExportNoticeDetail b on a.SkuNo = b.SkuNo";
+ string str = "select* from SysMaterials where SkuNo in (select a.SkuNo from BllArrivalNoticeDetail a " +
+ "left join BllExportNoticeDetail b on a.SkuNo = b.SkuNo";
//鍒ゆ柇寮�濮嬫椂闂存槸鍚︿负绌�
if (!string.IsNullOrWhiteSpace(StartTime))
@@ -789,8 +858,6 @@
throw new Exception(ex.Message);
}
}
-
-
#endregion
#region 瀵煎嚭搴撳瓨
@@ -837,9 +904,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;
}
@@ -883,7 +950,8 @@
/// <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> GetInventoryList1DaoChu(string skuNo, string skuName, string lotNo, string locatNo,
+ string palletNo, string status, string inspectStatus, 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";
//鍒ゆ柇鐗╂枡缂栫爜鏄惁涓虹┖
--
Gitblit v1.8.0