From daf065f5894352e5b739c66fb6fa12d8d028d5c3 Mon Sep 17 00:00:00 2001
From: admin <qiutairan163@163.com>
Date: 星期二, 11 十一月 2025 11:20:03 +0800
Subject: [PATCH] 首页库存和仓库
---
Wms/WMS.BLL/DataServer/StockServer.cs | 557 ++++++++++++++++++++++++++++++++----------------------
1 files changed, 328 insertions(+), 229 deletions(-)
diff --git a/Wms/WMS.BLL/DataServer/StockServer.cs b/Wms/WMS.BLL/DataServer/StockServer.cs
index 4574a47..093a005 100644
--- a/Wms/WMS.BLL/DataServer/StockServer.cs
+++ b/Wms/WMS.BLL/DataServer/StockServer.cs
@@ -1,8 +1,10 @@
锘縰sing System;
using System.Collections;
using System.Collections.Generic;
+using System.Data;
using System.Linq;
using System.Linq.Expressions;
+using System.Net.WebSockets;
using System.Runtime.Intrinsics.X86;
using System.Security.Claims;
using System.Text;
@@ -14,6 +16,7 @@
using Model.ModelVm.SysVm;
using MySqlConnector;
using SqlSugar;
+using Talk.Extensions;
using WMS.DAL;
using WMS.Entity.BllAsnEntity;
using WMS.Entity.BllQualityEntity;
@@ -28,9 +31,152 @@
public class StockServer : DbHelper<DataStock>, IStockServer
{
private static readonly SqlSugarScope Db = DataContext.Db;
+
public StockServer() : base(Db)
{
+ }
+
+
+ /// <summary>
+ /// 鑾峰彇搴撳瓨鐗╂枡绫诲瀷鍒嗗竷
+ /// </summary>
+ /// <returns></returns>
+ public IEnumerable<IdNameNumRateDto> GetGoodsTypeFb()
+ {
+ string stockNumSql = $@"select a.Type,sum(b.num) as num
+ from SysMaterials a
+ inner join (select SkuNo,sum(Qty) as num from DataStockDetail where IsDel='0' group by SkuNo) b on b.SkuNo=a.SkuNo
+ where a.IsDel='0'
+ group by a.Type";
+ DataTable stockNumDt = Db.Ado.GetDataTable(stockNumSql);
+ var stockNumIE = stockNumDt.Rows.Cast<DataRow>().Select(e => new
+ {
+ typeName = e["Type"] + "",
+ num = e["num"] + ""
+ });
+
+ //鐗╂枡绫诲瀷
+ string goodsTypeSql = $@"select DictNo,DictName,0.00 as num
+ from SysDictionary
+ where IsDel='0' and ParentNo='MaterialType'
+ order by Id";
+ DataTable goodsTypeDt = Db.Ado.GetDataTable(goodsTypeSql);
+
+ IEnumerable<IdNameNumRateDto> result = goodsTypeDt.Rows.Cast<DataRow>().Select(e => new IdNameNumRateDto
+ {
+ TypeId = e["DictNo"] + "",
+ TypeName = e["DictName"] + "",
+ TypeNum = Convert.ToDouble((stockNumIE.FirstOrDefault(f => f.typeName == e["DictNo"] + "") == null) ? "0.00" : stockNumIE.First(f => f.typeName == e["DictNo"] + "").num),
+ });
+ return result;
+ }
+
+
+ /// <summary>
+ /// 鑾峰彇搴撳瓨璐ㄩ噺鐘舵�佸垎甯�
+ /// </summary>
+ /// <returns></returns>
+ public IEnumerable<IdNameNumRateDto> GetQualityStatusFb()
+ {
+ string stockNumSql = $@"select InspectStatus,sum(Qty) as num
+ from DataStockDetail
+ where IsDel='0' and InspectStatus is not null
+ group by InspectStatus";
+ DataTable stockNumDt = Db.Ado.GetDataTable(stockNumSql);
+ object totalObj = stockNumDt.Compute("sum(num)", "");
+
+ var stockNumIE = stockNumDt.Rows.Cast<DataRow>().Select(e => new
+ {
+ typeName = e["InspectStatus"] + "",
+ num = e["num"] + ""
+ });
+
+ //鐗╂枡绫诲瀷
+ string inspectStatusSql = $@"select DictNo,DictName,0.00 as num,'0%' as rate
+ from SysDictionary
+ where IsDel='0' and ParentNo='InspectStatus'
+ order by Id";
+ DataTable goodsTypeDt = Db.Ado.GetDataTable(inspectStatusSql);
+
+ foreach (DataRow dr in goodsTypeDt.Rows)
+ {
+ var stockNum = stockNumIE.FirstOrDefault(e => e.typeName == dr["DictNo"] + "");
+
+ if (stockNum != null)
+ {
+ dr["num"] = stockNum.num;
+ dr["rate"] = (stockNum.num.ToDouble() / Convert.ToDouble(totalObj) * 100.00).ToString("0.00") + "%";
+ }
+ }
+
+ IEnumerable<IdNameNumRateDto> result = goodsTypeDt.Rows.Cast<DataRow>().Select(e => new IdNameNumRateDto
+ {
+ TypeId = e["DictNo"] + "",
+ TypeName = e["DictName"] + "",
+ TypeNum = Convert.ToDouble(e["num"]),
+ TypeRate = e["rate"] + ""
+ });
+ return result;
+ }
+
+
+ /// <summary>
+ /// 鑾峰彇浠撳簱鐘跺喌鎬�
+ /// </summary>
+ /// <returns></returns>
+ public WarehouseStateDto GetWareHouseState()
+ {
+ //搴撳尯鎬绘暟
+ string houseNumSql = $@"select count(Id) as num
+ from SysWareHouse
+ where IsDel='0'";
+ int houseNum = Db.Ado.GetInt(houseNumSql);
+
+ //搴撲綅鎬绘暟
+ string stockNumSql = $@"select count(Id) as num
+ from SysStorageLocat
+ where IsDel='0'";
+ int stockNum = Db.Ado.GetInt(stockNumSql);
+
+ //宸蹭娇鐢ㄥ簱浣�
+ string stockUseNumSql = $@"select count(Id) as num
+ from SysStorageLocat
+ where IsDel='0' and Status='1'";
+ int stockUseNum = Db.Ado.GetInt(stockUseNumSql);
+
+ WarehouseStateDto result = new WarehouseStateDto()
+ {
+ WarehouseNum = houseNum + "",
+ StockAllNum = stockNum + "",
+ StockUseNum = stockUseNum + "",
+ StockUseRate = ((double)stockUseNum / stockNum * 100.00).ToString("0.00") + "%"
+ };
+ return result;
+ }
+
+
+ /// <summary>
+ /// 鑾峰彇浠撳簱璇︽儏
+ /// </summary>
+ /// <returns></returns>
+ public IEnumerable<WarehouseUseDto> GetWareHouseStateInfo()
+ {
+ string stockUseSql = $@"select a.Id,a.WareHouseNo,a.WareHouseName,b.num as stockNum,c.num as useNum
+ from SysWareHouse a
+ left join (select WareHouseNo,count(Id) as num from SysStorageLocat where IsDel='0' group by WareHouseNo) b on b.WareHouseNo=a.WareHouseNo
+ left join (select WareHouseNo,count(Id) as num from SysStorageLocat where IsDel='0' and Status='1' group by WareHouseNo) c on c.WareHouseNo=a.WareHouseNo
+ where a.IsDel='0'
+ order by a.WareHouseNo";
+ DataTable stockUseDt = Db.Ado.GetDataTable(stockUseSql);
+
+ IEnumerable<WarehouseUseDto> result = stockUseDt.Rows.Cast<DataRow>().Select(e => new WarehouseUseDto
+ {
+ WarehouseName = e["WareHouseName"] + "",
+ StockAllNum = e["stockNum"] + "",
+ StockUseNum = e["useNum"] + ""
+ });
+ return result;
}
@@ -47,9 +193,11 @@
/// <param name="lotNo">鎵规</param>
/// <param name="status">搴撳瓨鐘舵��</param>
/// <param name="inspectStatus">璐ㄦ鐘舵��</param>
+ /// <param name="wareHouseNo">鎵�灞炰粨搴�</param>
+ /// <param name="areaNo">鎵�灞炲尯鍩�</param>
/// <returns></returns>
- public async Task<List<MateDataStockDto>> GetDataStockList(string selectType, string skuNo, string skuName, string ownerNo,
- string ownerName, string lotNo, string status, string inspectStatus)
+ public async Task<List<MateDataStockDto>> GetDataStockList(string selectType, string skuNo, string skuName, string ownerNo,
+ string ownerName, string lotNo, string status, string inspectStatus, string wareHouseNo, string areaNo)
{
Expression<Func<DataStockDetail, bool>> item = Expressionable.Create<DataStockDetail>()
.AndIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
@@ -59,6 +207,8 @@
.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)
+ .AndIF(!string.IsNullOrEmpty(wareHouseNo), a => a.WareHouseNo == wareHouseNo)
+ .AndIF(!string.IsNullOrEmpty(areaNo), a => a.AreaNo == areaNo)
.And(a => a.IsDel == "0")
.ToExpression();//娉ㄦ剰 杩欎竴鍙� 涓嶈兘灏�
@@ -66,53 +216,53 @@
var data = Db.Queryable<DataStockDetail>().Where(item);
List<MateDataStockDto> data2;
- switch (selectType)
+ switch (selectType)
{
// 鎸夌墿鏂欑粺璁�
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();
+ 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();
+ 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})
+ data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard, a.InspectStatus })
.Select(a => new MateDataStockDto()
{
SkuNo = a.SkuNo, //鐗╂枡缂栫爜
@@ -129,7 +279,7 @@
FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
}).ToListAsync();
- break;
+ break;
// 鎸夎揣涓荤粺璁�
case "3":
@@ -150,11 +300,11 @@
FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
}).ToListAsync();
- break;
+ break;
// 榛樿缁熻锛堢墿鏂� 鎵规 璐ㄦ 搴撳瓨鐘舵�� 璐т富)
default:
- data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard ,a.LotNo, a.LotText, a.OwnerNo, a.OwnerName , a.InspectStatus })
+ 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, //鐗╂枡缂栫爜
@@ -344,9 +494,9 @@
.GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
.Select(a => new TotalRecordDto()
{
- LotNo=a.LotNo,
- SkuNo=a.SkuNo,
- SkuName=a.SkuName,
+ 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),
@@ -431,22 +581,22 @@
/// <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)
{
// 鑾峰彇鍏ュ簱鍗曟槑缁嗕俊鎭�
- var asnList = Db.Queryable<BllPalletBind,BllArrivalNoticeDetail>
- ((tb1,tb2)=>new JoinQueryInfos(
+ 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")
+ .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(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()
+ .Select((tb1, tb2) => new TotalRecordDto()
{
ASNNo = tb2.ASNNo,
LotNo = tb1.LotNo,
@@ -459,7 +609,7 @@
CompleteTime = tb2.CreateTime,
LotText = tb1.LotText,
SupplierLot = tb2.SupplierLot,
-
+
SONo = "",
CTaskNo = "",
CCreateTime = null,
@@ -866,75 +1016,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 +1056,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
}
--
Gitblit v1.8.0