From eb86c89d85599da5b3f237a0e1214e45f6a90225 Mon Sep 17 00:00:00 2001
From: cnf <3283105747@qq.com>
Date: 星期三, 17 十二月 2025 16:47:37 +0800
Subject: [PATCH] 期初打印明细查询优化
---
WebApi/Gs.Warehouse/Services/MesDepotsManager.cs | 87 ++++++++++++++++++++++++++++++++++++++++---
1 files changed, 80 insertions(+), 7 deletions(-)
diff --git a/WebApi/Gs.Warehouse/Services/MesDepotsManager.cs b/WebApi/Gs.Warehouse/Services/MesDepotsManager.cs
index de7b05d..59e1d2e 100644
--- a/WebApi/Gs.Warehouse/Services/MesDepotsManager.cs
+++ b/WebApi/Gs.Warehouse/Services/MesDepotsManager.cs
@@ -1,4 +1,5 @@
锘縰sing System.Data;
+using System.Data.SqlClient;
using System.Text;
using Gs.Entity.BaseInfo;
using Gs.Entity.Sys;
@@ -102,14 +103,14 @@
var orderBy = $"{query.sortName ?? "DepotCode"} {query.sortOrder ?? "ASC"}";
var totalCount = 0;
- // 浣跨敤鏇存竻鏅扮殑鏌ヨ璇硶
+ // 淇鍚庣殑鏌ヨ璇硶 - 娉ㄦ剰LeftJoin鐨勫弬鏁�
var queryable = Db.Queryable<MesDepots>()
.LeftJoin<SysOrganization>((a, Org) => a.FSubsidiary == Org.Fid)
- .LeftJoin<MesStaff>((a, c) => a.CreateBy == c.Id.ToString())
- .LeftJoin<MesCustomer>((a, d) => d.Id.ToString() == a.ClientId.ToString())
- .LeftJoin<MesSupplier>((a, f) => f.Id.ToString() == a.SuppLierId.ToString())
- .LeftJoin<SysDepartment>((a, g) => g.Id.ToString() == a.department.ToString())
- .LeftJoin<MesItems>((a, h) => h.DepotCode.ToString() == a.DepotId.ToString())
+ .LeftJoin<MesStaff>((a, Org, MesStaff) => a.CreateBy == MesStaff.Id.ToString()) // 淇杩欎竴琛�
+ .LeftJoin<MesCustomer>((a, Org, MesStaff, d) => d.Id.ToString() == a.ClientId.ToString()) // 淇鍙傛暟
+ .LeftJoin<MesSupplier>((a, Org, MesStaff, d, f) => f.Id.ToString() == a.SuppLierId.ToString()) // 淇鍙傛暟
+ .LeftJoin<SysDepartment>((a, Org, MesStaff, d, f, g) => g.Id.ToString() == a.department.ToString()) // 淇鍙傛暟
+ .LeftJoin<MesItems>((a, Org, MesStaff, d, f, g, h) => h.DepotCode.ToString() == a.DepotId.ToString()) // 淇鍙傛暟
.Where(whereClause)
.GroupBy(a => new {
a.Guid,
@@ -189,6 +190,7 @@
var sortName = string.IsNullOrEmpty(model.sortName) ? "a.PAGE_GROUP" : model.sortName;
var keyWhere = model.keyWhere;
string keyType = model.keyType;
+ string keyWord = model.keyWord;
var sbSql = new StringBuilder();
sbSql.Append(" SELECT * FROM ");
sbSql.Append(" (SELECT N'(' +[Org].[FNumber] + N')' +[Org].[NAME] AS [FSubsidiary2] ,");
@@ -197,12 +199,20 @@
sbSql.Append(" [f].[supp_name] AS [SuppLierId] , [g].[departmentname] AS [department2] ,");
sbSql.Append(" ( CASE WHEN ( [a].[is_ng] = N'A' ) THEN N'姝ｅ父' ELSE N'绂佺敤' END ) AS [IsNg] ");
sbSql.Append(", [a].*,ROW_NUMBER() OVER(ORDER BY org.FNumber asc ,a.depot_code asc) AS RowIndex ");
+ if (string.IsNullOrEmpty(keyWord))
+ {
+ sbSql.Append(",'' as kcQty");
+ }
+ else
+ {
+ sbSql.Append(", isnull((select top 1 搴撳瓨瀵规瘮 from [dbo].[鍗虫椂搴撳瓨宸紓琛╙ where 鐗╂枡ID=" + keyWord + " and 浠撳簱ID=[a].[depot_id]),'('+a.depot_name+')0/0') AS [kcQty] ");
+ }
if (string.IsNullOrEmpty(keyType))
{
sbSql.Append(",cast(0 as bit) as chkInt");
}
else
- sbSql.Append(",cast( (select count(1) from SYS_USER_BIND b where b.userGuid='" + keyType + "' and b.aboutGuid=a.guid) as bit) as chkInt ");
+ sbSql.Append(",cast( (select count(1) from SYS_USER_BIND b where b.userGuid='" + keyType + "' and b.aboutGuid=a.guid and b.fType='浠撳簱') as bit) as chkInt ");
sbSql.Append(" FROM [MES_DEPOTS] [a] Left JOIN [SYS_ORGANIZATION] [Org] ON ( [a].[FSubsidiary] = [Org].[FID] )");
sbSql.Append(" Left JOIN [MES_STAFF] [c] ON ( [a].[create_by] = CAST([c].[id] AS NVARCHAR(50))) ");
sbSql.Append(" Left JOIN [MES_CUSTOMER] [d] ON (CAST([d].[id] AS NVARCHAR(50)) = CAST([a].[Client_Id] AS NVARCHAR(50))) ");
@@ -387,4 +397,67 @@
}
return j;
}
+
+ /// <summary>
+ /// 鏌ヨ鍒楄〃锛屾敮鎸佸垎椤碉紙淇濇寔杈撳嚭鍙傛暟鏂瑰紡锛�
+ /// </summary>
+ [RequestMethod(RequestMethods.POST)]
+ public ReturnDto<PageList<dynamic>> GetListCgy(PageQuery model)
+ {
+ string currentId = "";//榛樿閲囪喘鍛�
+ int currentPage = model.currentPage;
+ int everyPageSize = model.everyPageSize;
+ string sortName = model.sortName;
+ string keyWhere = model.keyWhere;
+ SqlParameter[] parameters =
+ {
+ new("@inCurrentPage", currentPage),
+ new("@inEveryPageSize", everyPageSize),
+ new("@inSortName", sortName),
+ new("@inSortOrder", ""),
+ new("@inQueryWhere", keyWhere),
+ new("@inFid", ""),
+ new("@inP1", ""),
+ new("@inP2", ""),
+ new("@inP3", ""),
+ new("@inP4", _userGuid)//褰撳墠鐧诲綍鐢ㄦ埛guid锛屽皢鏍规嵁浠栬鍙栦粨绠″憳
+ };
+ var dset = new DataSet();
+ var _pglist = new PageList<dynamic>
+ {
+ total = 0,
+ everyPageSize = 0,
+ pages = 0,
+ list = new List<dynamic>()
+ };
+ try
+ {
+ dset = DbHelperSQL.RunProcedure("prc_cgy_lst", parameters, "0");
+ if (dset != null && dset.Tables.Count > 0 &&
+ dset.Tables[0].Rows.Count > 0) //鏈夋暟鎹�
+ {
+ var intTotal = int.Parse(dset.Tables[1].Rows[0]["intTotal"].ToString());
+ var pages = intTotal % everyPageSize != 0
+ ? intTotal / everyPageSize + 1
+ : intTotal / everyPageSize;
+ _pglist.total = intTotal;
+ _pglist.everyPageSize = everyPageSize;
+ _pglist.pages = pages;
+ var _dy = dset.Tables[0].TableToDynamicList();
+ _pglist.list = _dy;
+ currentId = dset.Tables[1].Rows[0]["mrCgy"].ToString();
+ }
+ }
+ catch (Exception ex)
+ {
+ LogHelper.Debug(ToString(), ex.Message);
+ return ReturnDto<PageList<dynamic>>.QuickReturn(_pglist,
+ ReturnCode.Exception,"-1");
+ }
+
+ return ReturnDto<PageList<dynamic>>.QuickReturn(_pglist,
+ ReturnCode.Success, currentId);
+
+
+ }
}
\ No newline at end of file
--
Gitblit v1.9.3