From a4f39526873f45396bf778413979293eabb3be07 Mon Sep 17 00:00:00 2001
From: username@email.com <yzy2002yzy@163.com>
Date: 星期五, 20 九月 2024 13:16:48 +0800
Subject: [PATCH] 修改优化查询等
---
CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderBasicDAL.cs | 153 ++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 152 insertions(+), 1 deletions(-)
diff --git a/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderBasicDAL.cs b/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderBasicDAL.cs
index a7b3395..44bc467 100644
--- a/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderBasicDAL.cs
+++ b/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderBasicDAL.cs
@@ -505,6 +505,136 @@
else
{
}
+ StringBuilder ssss = new StringBuilder();
+ if (int.Parse(searchParam[18].ToString()) > 0)
+ {
+ var oA_Staffs = _dataBase.SelectModel<OA_Staff>(" * ", " [OA_Staff] ", "[Keyid]='" + searchParam[20] + "'").FirstOrDefault();
+
+
+ if (!string.IsNullOrEmpty(oA_Staffs.ManageCountry))
+ {
+ var sys_CitySites = _dataBase.SelectModel<Sys_CitySite>(" * ", " [Sys_CitySite] ").ToList();
+
+
+
+ var ManageCity = oA_Staffs.ManageCity.Split(',').Where(x => x != "").ToArray();
+
+
+ var ManageCountry = (oA_Staffs.ManageCountry + "," + oA_Staffs.ManageCountry1).Split(',').Where(x => x != "").ToArray();
+
+ int[] intManageCity = new int[ManageCity.Length];
+ for (int i = 0; i < ManageCity.Length; i++)
+ {
+ intManageCity[i] = int.Parse(ManageCity[i]);
+ }
+
+ int[] intManageCountry = new int[ManageCountry.Length];
+ for (int i = 0; i < ManageCountry.Length; i++)
+ {
+ intManageCountry[i] = int.Parse(ManageCountry[i]);
+ }
+ var city = sys_CitySites.Where(x => intManageCity.Contains(x.Keyid)).ToList();
+ var country = sys_CitySites.Where(x => intManageCountry.Contains(x.Keyid)).ToList();
+
+ StringBuilder citystring = new StringBuilder();
+ for (int i = 0; i < city.Count; i++)
+ {
+ if (i == city.Count - 1)
+ {
+ citystring.Append("'" + city[i].Name.ToString() + "'");
+ }
+ else
+ {
+ citystring.Append("'" + city[i].Name.ToString() + "',");
+ }
+
+ }
+
+ StringBuilder countrystring = new StringBuilder();
+ for (int i = 0; i < country.Count; i++)
+ {
+ if (i == country.Count - 1)
+ {
+ countrystring.Append("'" + country[i].Name.ToString() + "'");
+ }
+ else
+ {
+ countrystring.Append("'" + country[i].Name.ToString() + "',");
+ }
+
+ }
+
+
+
+ var resultsss = _dataBase.SelectModel<OA_CorporateClients>(" axz.* ", " [OA_CorporateClients] axz join [OA_CustomerCommunications] bxz on axz.Keyid=bxz.Keyid ", " axz.FirmId ='" + searchParam[1] + "'and ( ( bxz.City in ( " + citystring.ToString() + ") and bxz.County in ( " + countrystring.ToString() + " ) )or axz.AccountManagerId='" + searchParam[20] + "' or axz.BusinessManagerId='" + searchParam[20] + "')").Select(x => x.Keyid).ToList();
+ //return null == result || result.Count < 1 ? null : result[0];
+
+
+
+ for (int i = 0; i < resultsss.Count; i++)
+ {
+ if (i == resultsss.Count - 1)
+ {
+ ssss.Append("'" + resultsss[i].ToString() + "'");
+ }
+ else
+ {
+ ssss.Append("'" + resultsss[i].ToString() + "',");
+ }
+
+ }
+ }
+ else
+ {
+ var resultsss = _dataBase.SelectModel<OA_CorporateClients>(" axz.* ", " [OA_CorporateClients] axz join [OA_CustomerCommunications] bxz on axz.Keyid=bxz.Keyid ", " axz.FirmId ='" + searchParam[1] + "'and ( axz.AccountManagerId='" + searchParam[20] + "' or axz.BusinessManagerId='" + searchParam[20] + "')").Select(x => x.Keyid).ToList();
+ //return null == result || result.Count < 1 ? null : result[0];
+
+
+
+ for (int i = 0; i < resultsss.Count; i++)
+ {
+ if (i == resultsss.Count - 1)
+ {
+ ssss.Append("'" + resultsss[i].ToString() + "'");
+ }
+ else
+ {
+ ssss.Append("'" + resultsss[i].ToString() + "',");
+ }
+
+ }
+ }
+
+ if (string.IsNullOrEmpty(ssss.ToString()))
+ {
+ ssss.Append("'4354654657568345429890'");
+ }
+
+
+ }
+ else
+ {
+ var resultsss = _dataBase.SelectModel<OA_CorporateClients>(" axz.* ", " [OA_CorporateClients] axz ", " axz.FirmId ='" + searchParam[1] + "' ").Select(x => x.Keyid).ToList();
+ //return null == result || result.Count < 1 ? null : result[0];
+
+
+
+ for (int i = 0; i < resultsss.Count; i++)
+ {
+ if (i == resultsss.Count - 1)
+ {
+ ssss.Append("'" + resultsss[i].ToString() + "'");
+ }
+ else
+ {
+ ssss.Append("'" + resultsss[i].ToString() + "',");
+ }
+
+ }
+ }
+
+
+
IList<SqlParameter> sqlParms = new List<SqlParameter>()
{
@@ -582,9 +712,17 @@
Direction = ParameterDirection.Output
});
+ sqlParms.Add(new SqlParameter()
+ {
+ ParameterName = "@CorporateClientsId",
+ SqlDbType = SqlDbType.VarChar,
+ Size=int.MaxValue,
+ Value = ssss.ToString()
+ });
+
IList<EC_OrderBasic> result = null;
//鎵ц鍒嗛〉瀛樺偍
- using (IDataReader reader = _dataBase.QueryDataReader("sp_EC_OrderBasic_Search_Seller", CommandType.StoredProcedure, sqlParms.ToArray()))
+ using (IDataReader reader = _dataBase.QueryDataReader("sp_EC_OrderBasic_Search_Seller_Ceshi", CommandType.StoredProcedure, sqlParms.ToArray()))
{
result = _dataBase.ReadDataToModel<EC_OrderBasic>(reader);
}
@@ -1324,6 +1462,19 @@
return null == result || result.Count == 0 ? null : result[0];
}
+
+ /// <summary>
+ /// 鏍规嵁璁㈠崟缂栧彿鑾峰彇璁㈠崟
+ /// </summary>
+ /// <param name="orderIds">璁㈠崟缂栧彿</param>
+ /// <returns></returns>
+ public IEnumerable<EC_OrderBasic> GetOrderByIds(string orderIds)
+ {
+ //return _eC_OrderBasicDAL.SelectModelById(orderId);
+ IList<EC_OrderBasic> result = _dataBase.SelectModel<EC_OrderBasic>(" ob.*,ts.OnlineMoney,ts.LineMoney,ts.UsedCredit,ts.PayedMoney ", "EC_OrderBasic as ob Inner Join Pay_TradingSingle as ts On ( ob.Keyid = ts.OrderId ) ", string.Format(" ob.Keyid in ({0})", orderIds));
+ return null == result || result.Count < 1 ? null : result;
+ }
+
/// <summary>
/// 鏍规嵁璁㈠崟缂栧彿鑾峰彇鑷姩澶栧崗璁㈠崟
/// </summary>
--
Gitblit v1.9.1