From d483890cff501685784bbebbc9fddd51faca7b54 Mon Sep 17 00:00:00 2001
From: wangyi <2946747746@qq.com>
Date: 星期三, 17 十二月 2025 13:34:04 +0800
Subject: [PATCH] 阶梯工价查询优化
---
WebAPI/Controllers/GZGL/Gy_SteppedPriceCoefficientController .cs | 279 ++++++++++++++++++++-----------------------------------
1 files changed, 100 insertions(+), 179 deletions(-)
diff --git a/WebAPI/Controllers/GZGL/Gy_SteppedPriceCoefficientController .cs b/WebAPI/Controllers/GZGL/Gy_SteppedPriceCoefficientController .cs
index 0110422..d1ad83a 100644
--- a/WebAPI/Controllers/GZGL/Gy_SteppedPriceCoefficientController .cs
+++ b/WebAPI/Controllers/GZGL/Gy_SteppedPriceCoefficientController .cs
@@ -457,213 +457,134 @@
}
#endregion
- #region 鏌ヨ
+
+ #region 闃舵宸ヤ环绯绘暟 鏌ヨ-椤甸潰璧嬪��
[Route("Gy_SteppedPriceCoefficientBill/list")]
[HttpGet]
- public object getSteppedPriceCoefficient(string sWhere = "", string user = "", int page = 1, int size = 50)
+ public object list(string sWhere, string user)
{
try
{
- // 娣诲姞璋冭瘯淇℃伅
- Console.WriteLine("=== 鏌ヨ寮�濮� ===");
- Console.WriteLine($"鍙傛暟: sWhere={sWhere}, user={user}, page={page}, size={size}");
+ List<object> columnNameList = new List<object>();
- // 纭繚user涓嶄负绌�
- if (string.IsNullOrEmpty(user))
+ // 鏌ョ湅鏉冮檺妫�鏌�
+ //if (!DBUtility.ClsPub.Security_Log("Gy_MateNumRelation_Sec_Query", 1, false, user))
+ //{
+ // objJsonResult.code = "0";
+ // objJsonResult.count = 0;
+ // objJsonResult.Message = "鏃犳煡鐪嬫潈闄�";
+ // objJsonResult.data = null;
+ // return objJsonResult;
+ //}
+
+ // 鏋勫缓鍩虹SQL
+ string baseSql = @"select * from h_v_Gy_SteppedPriceCoefficientBillWithSub";
+
+ // 璋冭瘯锛氳褰曟帴鏀跺埌鐨勫弬鏁�
+ Console.WriteLine($"API 鎺ユ敹鍙傛暟 - sWhere: '{sWhere}', user: '{user}'");
+
+ // 澶勭悊鏌ヨ鏉′欢锛堢害瀹氾細鍓嶇鍙彂閫佹潯浠惰〃杈惧紡锛屽彲鑳戒互 AND 寮�澶达級
+ string whereClause = ProcessWhereClause(sWhere);
+ string orderByClause = "order by HItemID";
+
+ // 鏋勫缓瀹屾暣SQL
+ string sql;
+ if (string.IsNullOrWhiteSpace(whereClause))
{
- user = "admin";
- Console.WriteLine("璀﹀憡: user鍙傛暟涓虹┖锛屼娇鐢ㄩ粯璁ゅ��: admin");
- }
-
- // 鏋勫缓鏌ヨSQL
- string baseSql = @"
- SELECT
- m.HInterID,
- m.HItemMainID,
- m.HBillNo,
- m.HDate,
- m.HYear,
- m.HPeriod,
- m.HBillStatus,
- m.HRemark,
- m.HChecker,
- m.HCheckDate,
- m.HMaker,
- m.HMakeDate,
- m.HUpDater,
- m.HUpDateDate,
- m.HCloseMan,
- m.HCloseDate,
- m.HProcID,
- m.HEmpID,
- m.HDeptID,
- m.HStockOrgID,
- -- 瀛愯〃缁熻淇℃伅
- ISNULL((SELECT COUNT(*) FROM Gy_SteppedPriceCoefficientBillSub s WHERE s.HInterID = m.HInterID), 0) as SubCount,
- ISNULL((SELECT SUM(HMinQty) FROM Gy_SteppedPriceCoefficientBillSub s WHERE s.HInterID = m.HInterID), 0) as TotalMinQty,
- ISNULL((SELECT SUM(HMaxQty) FROM Gy_SteppedPriceCoefficientBillSub s WHERE s.HInterID = m.HInterID), 0) as TotalMaxQty,
- ISNULL((SELECT SUM(HPriceCoefficient) FROM Gy_SteppedPriceCoefficientBillSub s WHERE s.HInterID = m.HInterID), 0) as TotalCoefficient,
- ISNULL((SELECT SUM(HMaxPrice) FROM Gy_SteppedPriceCoefficientBillSub s WHERE s.HInterID = m.HInterID), 0) as TotalMaxPrice
- FROM Gy_SteppedPriceCoefficientBillMain m
- WHERE m.HDeleteMan = '' ";
-
- string whereClause = "";
-
- if (!string.IsNullOrEmpty(sWhere))
- {
- // 澶勭悊HMakeDate鏌ヨ鏉′欢 - 纭繚姝ｇ‘杞崲
- if (sWhere.Contains("HMakeDate"))
- {
- sWhere = System.Text.RegularExpressions.Regex.Replace(
- sWhere,
- @"HMakeDate\s*(>=|<=|>|<|=)\s*'([^']*)'",
- match => $"CONVERT(date, HMakeDate) {match.Groups[1].Value} '{match.Groups[2].Value}'"
- );
- }
-
- whereClause = " AND " + sWhere;
- }
-
- Console.WriteLine($"瀹屾暣鏌ヨ鏉′欢: {whereClause}");
-
- // 1. 鍏堣幏鍙栨�昏褰曟暟
- string countSql = "SELECT COUNT(*) as TotalCount FROM Gy_SteppedPriceCoefficientBillMain WHERE HDeleteMan = '' " + whereClause;
- Console.WriteLine($"缁熻SQL: {countSql}");
-
- DataSet dsCount = oCN.RunProcReturn(countSql, "TotalCount");
- int totalCount = 0;
-
- if (dsCount != null && dsCount.Tables.Count > 0 && dsCount.Tables[0].Rows.Count > 0)
- {
- object countValue = dsCount.Tables[0].Rows[0]["TotalCount"];
- if (countValue != null && countValue != DBNull.Value)
- {
- totalCount = Convert.ToInt32(countValue);
- }
- }
-
- Console.WriteLine($"鎬昏褰曟暟: {totalCount}");
-
- // 2. 鎵ц鍒嗛〉鏌ヨ
- int startRow = (page - 1) * size;
- string pageSql = $@"
- SELECT * FROM (
- SELECT ROW_NUMBER() OVER (ORDER BY HInterID DESC) AS RowNum, *
- FROM ({baseSql + whereClause}) AS T
- ) AS T2
- WHERE RowNum > {startRow} AND RowNum <= {startRow + size}";
-
- Console.WriteLine($"鍒嗛〉鏌ヨSQL: {pageSql}");
-
- ds = oCN.RunProcReturn(pageSql, "Gy_SteppedPriceCoefficientList");
-
- // 璋冭瘯淇℃伅
- if (ds == null)
- {
- Console.WriteLine("鏌ヨ杩斿洖鐨凞ataSet涓簄ull");
+ // 娌℃湁鏉′欢鏃讹紝涓嶉渶瑕乄HERE鍏抽敭瀛�
+ sql = $"{baseSql} {orderByClause}";
}
else
{
- Console.WriteLine($"鏌ヨ杩斿洖鐨凞ataSet琛ㄦ暟閲�: {ds.Tables.Count}");
- if (ds.Tables.Count > 0)
- {
- Console.WriteLine($"绗竴寮犺〃琛屾暟: {ds.Tables[0].Rows.Count}");
- if (ds.Tables[0].Rows.Count > 0)
- {
- Console.WriteLine("绗竴琛屾暟鎹ず渚�:");
- foreach (DataColumn col in ds.Tables[0].Columns)
- {
- Console.WriteLine($" {col.ColumnName}: {ds.Tables[0].Rows[0][col.ColumnName]}");
- }
- }
- }
+ // 鏈夋潯浠舵椂锛屾坊鍔燱HERE鍏抽敭瀛�
+ sql = $"{baseSql} WHERE {whereClause} {orderByClause}";
}
- List<object> columnNameList = new List<object>();
+ // 璋冭瘯锛氳緭鍑烘渶缁圫QL
+ Console.WriteLine($"鏈�缁堟墽琛岀殑SQL: {sql}");
- // 3. 鑾峰彇鍒椾俊鎭�
- if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
+ // 鎵ц鏌ヨ
+ ds = oCN.RunProcReturn(sql, "h_v_Gy_SteppedPriceCoefficientBillWithSub");
+
+ // 娣诲姞鍒楀悕
+ if (ds != null && ds.Tables.Count > 0)
{
foreach (DataColumn col in ds.Tables[0].Columns)
{
Type dataType = col.DataType;
- string colName = col.ColumnName;
- string typeName = dataType.Name;
-
- columnNameList.Add(new
- {
- ColmCols = colName,
- ColmType = typeName
- });
- }
-
- // 娣诲姞瀛愯〃鐩稿叧鐨勫垪
- columnNameList.Add(new { ColmCols = "SubCount", ColmType = "Int32" });
- columnNameList.Add(new { ColmCols = "TotalMinQty", ColmType = "Decimal" });
- columnNameList.Add(new { ColmCols = "TotalMaxQty", ColmType = "Decimal" });
- columnNameList.Add(new { ColmCols = "TotalCoefficient", ColmType = "Decimal" });
- columnNameList.Add(new { ColmCols = "TotalMaxPrice", ColmType = "Decimal" });
- }
-
- // 4. 灏咲ataTable杞崲涓哄璞″垪琛紙鍓嶇layui table闇�瑕佹暟缁勬牸寮忥級
- List<Dictionary<string, object>> dataList = new List<Dictionary<string, object>>();
- if (ds != null && ds.Tables.Count > 0)
- {
- foreach (DataRow row in ds.Tables[0].Rows)
- {
- Dictionary<string, object> rowDict = new Dictionary<string, object>();
- foreach (DataColumn col in ds.Tables[0].Columns)
- {
- rowDict[col.ColumnName] = row[col] == DBNull.Value ? null : row[col];
- }
- dataList.Add(rowDict);
+ string colmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
+ columnNameList.Add(JsonConvert.DeserializeObject(colmString));
}
}
- // 5. 鏋勫缓杩斿洖缁撴灉
- var result = new
+ if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
- code = "1",
- count = totalCount,
- Message = totalCount > 0 ? $"鏌ヨ鎴愬姛锛屽叡{totalCount}鏉¤褰�" : "鏌ヨ鎴愬姛锛屾棤鏁版嵁",
- data = dataList, // 浣跨敤杞崲鍚庣殑鍒楄〃
- list = columnNameList,
- debug = new
- {
- totalCount = totalCount,
- returnedCount = dataList.Count,
- queryTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
- }
- };
-
- Console.WriteLine($"鏌ヨ瀹屾垚锛岃繑鍥瀧dataList.Count}鏉℃暟鎹�");
-
- return result;
+ objJsonResult.code = "1";
+ objJsonResult.count = ds.Tables[0].Rows.Count;
+ objJsonResult.Message = "Success锛�";
+ objJsonResult.data = ds.Tables[0];
+ objJsonResult.list = columnNameList;
+ return objJsonResult;
+ }
+ else
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "娌℃湁鏌ヨ鍒版暟鎹紝璇疯仈绯荤郴缁熺鐞嗗憳杩涜鏍稿";
+ objJsonResult.data = ds?.Tables[0] ?? new DataTable();
+ objJsonResult.list = columnNameList;
+ return objJsonResult;
+ }
}
catch (Exception e)
{
- Console.WriteLine($"=== 鏌ヨ寮傚父 ===");
- Console.WriteLine($"閿欒淇℃伅: {e.Message}");
- Console.WriteLine($"鍫嗘爤璺熻釜: {e.StackTrace}");
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "鏌ヨ澶辫触锛侀敊璇細" + e.Message;
+ objJsonResult.data = null;
- var errorResult = new
- {
- code = "0",
- count = 0,
- Message = $"鏌ヨ澶辫触锛侀敊璇細{e.Message}",
- data = new List<object>(),
- list = new List<object>(),
- debug = new
- {
- error = e.Message,
- stackTrace = e.StackTrace,
- queryTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
- }
- };
+ // 璁板綍瀹屾暣寮傚父淇℃伅
+ Console.WriteLine($"API 寮傚父: {e.ToString()}");
- return errorResult;
+ return objJsonResult;
}
}
+
+ /// <summary>
+ /// 澶勭悊WHERE鏉′欢瀛愬彞
+ /// 绾﹀畾锛氬墠绔彧鍙戦�佹潯浠惰〃杈惧紡锛屽彲鑳戒互 AND 寮�澶�
+ /// 杩斿洖锛氫笉甯� WHERE 鍜屽紑澶� AND 鐨勬潯浠跺瓧绗︿覆
+ /// </summary>
+ private string ProcessWhereClause(string sWhere)
+ {
+ if (string.IsNullOrWhiteSpace(sWhere))
+ return string.Empty;
+
+ string condition = sWhere.Trim();
+
+ Console.WriteLine($"ProcessWhereClause 杈撳叆: '{sWhere}'");
+
+ // 濡傛灉鍓嶇鎰忓鍙戦�佷簡 WHERE 寮�澶达紝绉婚櫎瀹�
+ if (condition.StartsWith("WHERE ", StringComparison.OrdinalIgnoreCase))
+ {
+ condition = condition.Substring(6).Trim();
+ Console.WriteLine($"绉婚櫎浜� WHERE 鍏抽敭瀛楋紝澶勭悊鍚�: '{condition}'");
+ }
+
+ // 绉婚櫎寮�澶寸殑 AND
+ if (condition.StartsWith("AND ", StringComparison.OrdinalIgnoreCase))
+ {
+ condition = condition.Substring(4).Trim();
+ Console.WriteLine($"绉婚櫎浜� AND 鍏抽敭瀛楋紝澶勭悊鍚�: '{condition}'");
+ }
+
+ // 棰濆妫�鏌ワ細纭繚寮�澶存病鏈� WHERE 鎴� AND
+ condition = condition.Trim();
+
+ Console.WriteLine($"ProcessWhereClause 鏈�缁堣緭鍑�: '{condition}'");
+
+ return condition;
+ }
#endregion
#region 闃舵宸ヤ环绯绘暟 缂栬緫-椤甸潰璧嬪��
--
Gitblit v1.9.1