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 | 171 +++++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 167 insertions(+), 4 deletions(-)
diff --git a/WebApi/Gs.Warehouse/Services/MesDepotsManager.cs b/WebApi/Gs.Warehouse/Services/MesDepotsManager.cs
index 863f42c..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;
@@ -8,6 +9,7 @@
using Gs.Toolbox.ApiCore.Group;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
+using Microsoft.Extensions.Logging;
using Newtonsoft.Json.Linq;
using NPOI.SS.Formula.Functions;
using SqlSugar;
@@ -40,8 +42,7 @@
try
{
var _sbWhere = new StringBuilder(" 1=1" + query.keyWhere);
- var _sbBy =
- new StringBuilder(query.sortName + " " + query.sortOrder);
+ var _sbBy = new StringBuilder(query.sortName + " " + query.sortOrder);
var totalCount = 0;
var itemsList = Db.Queryable<MesDepots, SysOrganization, MesStaff, MesCustomer, MesSupplier, SysDepartment>(
(a, Org, c, d, f, g) => new object[]
@@ -87,6 +88,96 @@
}
/// <summary>
+ /// 鏌ヨ鍒楄〃锛屾敮鎸佸垎椤�
+ /// </summary>
+ /// <param name="query"></param>
+ /// <returns></returns>
+ [RequestMethod(RequestMethods.POST)]
+ public ReturnDto<PageList<MesDepots>> GetListPage3(PageQuery query)
+ {
+ var pageList = new PageList<MesDepots>();
+ try
+ {
+ // 鏋勫缓鏌ヨ鏉′欢
+ var whereClause = "1=1" + (string.IsNullOrEmpty(query.keyWhere) ? "" : query.keyWhere);
+ 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, 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,
+ a.DepotCode,
+ a.DepotName,
+ a.DepotId,
+ a.FSubsidiary,
+ a.CreateBy,
+ a.ClientId,
+ a.SuppLierId,
+ a.department,
+ a.IsNg,
+ a.DocumentStatus
+ })
+ .OrderBy(orderBy);
+
+ // 浣跨敤鏇村畨鍏ㄧ殑鏄犲皠鏂瑰紡
+ var itemsList = queryable.Select<MesDepots>(a => new MesDepots
+ {
+ Guid = a.Guid,
+ DepotCode = a.DepotCode,
+ DepotName = a.DepotName,
+ DepotId = a.DepotId,
+ FSubsidiary = SqlFunc.Subqueryable<SysOrganization>()
+ .Where(org => org.Fid == a.FSubsidiary)
+ .Select(org => "(" + org.FNumber + ")" + org.Name),
+ OrgName = SqlFunc.Subqueryable<SysOrganization>()
+ .Where(org => org.Fid == a.FSubsidiary)
+ .Select(org => org.Name),
+ OrgId = SqlFunc.Subqueryable<SysOrganization>()
+ .Where(org => org.Fid == a.FSubsidiary)
+ .Select(org => org.FNumber),
+ CreateBy = SqlFunc.Subqueryable<MesStaff>()
+ .Where(staff => staff.Id.ToString() == a.CreateBy)
+ .Select(staff => staff.StaffName),
+ ClientId = SqlFunc.Subqueryable<MesCustomer>()
+ .Where(cust => cust.Id.ToString() == a.ClientId)
+ .Select(cust => cust.CustName),
+ SuppLierId = SqlFunc.Subqueryable<MesSupplier>()
+ .Where(supp => supp.Id.ToString() == a.SuppLierId)
+ .Select(supp => supp.SuppName),
+ department = SqlFunc.Subqueryable<SysDepartment>()
+ .Where(dept => dept.Id.ToString() == a.department)
+ .Select(dept => dept.Departmentname),
+ IsNg = a.IsNg + ":" + (a.IsNg == "A" ? "姝ｅ父" : "绂佺敤"),
+ DocumentStatus = a.DocumentStatus + ":" +
+ SqlFunc.IF(a.DocumentStatus == "Z").Return("鏆傚瓨")
+ .ElseIF(a.DocumentStatus == "A").Return("鍒涘缓")
+ .ElseIF(a.DocumentStatus == "B").Return("瀹℃牳涓�")
+ .ElseIF(a.DocumentStatus == "C").Return("宸插鏍�")
+ .ElseIF(a.DocumentStatus == "D").Return("閲嶆柊瀹℃牳")
+ .End(a.DocumentStatus)
+ })
+ .ToPageList(query.currentPage, query.everyPageSize, ref totalCount);
+
+ pageList = new PageList<MesDepots>(itemsList, totalCount, query.everyPageSize);
+ return ReturnDto<PageList<MesDepots>>.QuickReturn(pageList, ReturnCode.Success, "璇诲彇鎴愬姛");
+ }
+ catch (Exception ex)
+ {
+
+ return ReturnDto<PageList<MesDepots>>.QuickReturn(pageList,
+ ReturnCode.Default, $"绯荤粺閿欒: {ex.Message}");
+ }
+ }
+
+ /// <summary>
/// 鏌ヨ鍒楄〃锛屾敮鎸佸垎椤�,鐢ㄤ簬鍚勭缁戝畾
/// </summary>
/// <param name="query"></param>
@@ -99,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] ,");
@@ -107,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))) ");
@@ -185,7 +285,7 @@
}
m.IsNg = m.IsNg + ":" + (m.IsNg == "A" ? "姝ｅ父" : "绂佺敤");
var lst = new MesDepotSectionsManager();
- m.list = lst.GetList(it => it.DepotGuid == m.Guid).OrderBy(it => it.DepotSectionCode).ToList();
+ m.list = lst.GetList(it => it.DepotGuid == m.Guid).OrderBy(it => it.Xh).ToList();
if (m != null)
return ReturnDto<MesDepots>.QuickReturn(m, ReturnCode.Success, "璇诲彇鎴愬姛锛�");
return ReturnDto<MesDepots>.QuickReturn(m, ReturnCode.Default, "璇诲彇澶辫触锛�");
@@ -297,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