修改优化查询等

username@email.com

2024-09-20 a4f39526873f45396bf778413979293eabb3be07

 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>