/// <summary> /// 獲取供應商商品 /// </summary> /// <param name="query">查詢條件</param> /// <param name="totalCount">數據總條數</param> /// <returns>與供應商相關的商品列表</returns> public List<VenderProductListCustom> GetVendorProduct(VenderProductList query, out int totalCount) { return _productDao.GetVendorProduct(query, out totalCount); }
public HttpResponseBase QueryVendorProductList() { string json = string.Empty; try { VenderProductList query = new VenderProductList(); #region 查询条件填充 query.IsPage = true; query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); if (!string.IsNullOrEmpty(Request.Form["limit"])) { query.Limit = Convert.ToInt32(Request.Form["limit"]); } query.price_check = true;//價格進階查詢 #region 站台條件(暫設) query.site_id = 1;//站台預設為gigade query.user_level = 1;//一般會員 query.user_id = 0; #endregion if (!string.IsNullOrEmpty(Request.Form["brand_id"])) //品牌 { query.brand_id = uint.Parse(Request.Form["brand_id"]); } if (!string.IsNullOrEmpty(Request.Form["combination"])) //商品類型 { query.combination = int.Parse(Request.Form["combination"]); } if (!string.IsNullOrEmpty(Request.Form["product_status"])) //商品狀態 { query.product_status = int.Parse(Request.Form["product_status"]); } if (!string.IsNullOrEmpty(Request.Form["product_freight_set"])) //運送方式 { query.freight = uint.Parse(Request.Form["product_freight_set"]); } if (!string.IsNullOrEmpty(Request.Form["product_mode"])) //出貨方式 { query.mode = uint.Parse(Request.Form["product_mode"]); } query.date_type = Request.Form["date_type"] ?? ""; //日期條件 if (!string.IsNullOrEmpty(Request.Form["time_start"])) { query.time_start = CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Form["time_start"]).ToString("yyyy/MM/dd 00:00:00")).ToString(); } if (!string.IsNullOrEmpty(Request.Form["time_end"])) { query.time_end = CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Form["time_end"]).ToString("yyyy/MM/dd 23:59:59")).ToString(); } query.name_number = Request.Form["key"] ?? ""; //名稱/編號搜尋 //品牌下拉選單只能看到該供應商品牌。 query.vendor_id = Convert.ToUInt32((Session["vendor"] as BLL.gigade.Model.Vendor).vendor_id); #endregion _productMgr = new ProductMgr(connectionString); int totalCount = 0; List<VenderProductListCustom> pros = _productMgr.GetVendorProduct(query, out totalCount); foreach (var item in pros) { if (item.product_image != "") { item.product_image = imgServerPath + prod50Path + GetDetailFolder(item.product_image) + item.product_image; } else { item.product_image = defaultImg; } } json = "{succes:true,totalCount:" + totalCount + ",item:" + JsonConvert.SerializeObject(pros) + "}"; } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{succes:true,totalCount:0,item:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
/// <summary> /// 獲取供應商商品 /// </summary> /// <param name="query">查詢條件</param> /// <param name="totalCount">數據總條數</param> /// <returns>與供應商相關的商品列表</returns> public List<VenderProductListCustom> GetVendorProduct(VenderProductList query, out int totalCount) { try { query.Replace4MySQL(); StringBuilder sbProCols = new StringBuilder("select a.product_id,a.create_channel as create_channel,b.brand_name,a.product_image,a.product_name,a.prod_sz,c.parametername as combination,d.parametername as product_spec,j.parametername as product_freight_set,a.product_price_list,e.parametername as product_status,a.product_status as product_status_id,k.parametername as product_mode,a.combination as combination_id,f.price_master_id,f.user_id as master_user_id,g.site_name,f.site_id,h.parametername as user_level,f.user_level as level,i.parametername as price_status,f.price,f.event_price,f.cost,f.event_cost,f.event_start,f.event_end ,a.user_id,'0' as temp_status "); StringBuilder sbProTempCols = new StringBuilder("select a.product_id,a.create_channel as create_channel,b.brand_name,a.product_image,a.product_name,a.prod_sz,c.parametername as combination,d.parametername as product_spec,j.parametername as product_freight_set,a.product_price_list,e.parametername as product_status,a.product_status as product_status_id,k.parametername as product_mode,a.combination as combination_id,f.price_master_id,f.user_id as master_user_id,g.site_name,f.site_id,h.parametername as user_level,f.user_level as level,i.parametername as price_status,f.price,f.event_price,f.cost,f.event_cost,f.event_start,f.event_end ,a.writer_id as user_id,a.temp_status "); StringBuilder sbProTbls = new StringBuilder("from product a "); StringBuilder sbProTempTbls = new StringBuilder("from product_temp a "); StringBuilder sbTbls = new StringBuilder(); sbTbls.Append("left join vendor_brand b on a.brand_id=b.brand_id "); sbTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') c on a.combination=c.parametercode "); //商品類型 sbTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_spec') d on a.product_spec=d.parametercode "); //規格 sbTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') e on a.product_status=e.parametercode "); //商品狀態 sbTbls.Append("left join site g on f.site_id=g.site_id "); sbTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='userlevel') h on f.user_level=h.parametercode ");// sbTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='price_status') i on f.price_status=i.parametercode ");//活動價,成本,期間 sbTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') j on a.product_freight_set=j.parametercode "); //運送方式 sbTbls.Append("left join (select parametercode,parametername from t_parametersrc where parameterType='product_mode') k on a.product_mode=k.parametercode "); //出貨方式 sbProTbls.Append("left join price_master f on a.product_id=f.product_id and (f.product_id=f.child_id or f.child_id=0) "); // //正式表商品價格 sbProTempTbls.Append("left join price_master_temp f on a.product_id=f.product_id and (f.product_id=f.child_id or f.child_id=0) "); // //臨時表商品價格 //where 1=1 and f.site_id is not null and f.site_id=1 and f.user_level=1 and f.user_id=0 and a.brand_id in (select brand_id from vendor_brand where vendor_id='2') //where 1=1 and f.site_id is not null and f.site_id=1 and f.user_level=1 and f.user_id=0 and a.temp_status=12 and a.create_channel=2) StringBuilder sbCondition = new StringBuilder("where 1=1 and f.site_id=1 and f.user_level=1 and f.user_id=0 "); StringBuilder sbProCondition = new StringBuilder(""); StringBuilder sbProTempCondition = new StringBuilder(" and temp_status=12 "); StringBuilder sbOrderBy = new StringBuilder(" order by a.product_createdate desc "); StringBuilder sbLimit = new StringBuilder(""); if (query.brand_id != 0) //品牌 { sbCondition.AppendFormat(" and a.brand_id={0}", query.brand_id); } if (query.combination != 0) { sbCondition.AppendFormat(" and a.combination={0}", query.combination); } if (query.product_status != -1) //商品狀態 { sbCondition.AppendFormat(" and a.product_status={0}", query.product_status); } if (query.freight != 0) //運送方式 { sbCondition.AppendFormat(" and a.product_freight_set={0}", query.freight); } if (query.mode != 0) //出貨方式 { sbCondition.AppendFormat(" and a.product_mode={0}", query.mode); } if (!string.IsNullOrEmpty(query.date_type)) { CheckCondition(query, "a", sbCondition); } if (query.price_type == 2) { sbCondition.AppendFormat(" and a.price_type <>2 and a.combination = 1 and l.same_price <> 0 "); } if (!string.IsNullOrEmpty(query.name_number)) { sbCondition.AppendFormat(" and (a.product_name like '%{0}%' or a.product_id='{0}')", query.name_number); } if (!string.IsNullOrEmpty(query.vendor_id.ToString())) { sbCondition.AppendFormat(" and a.brand_id in (select brand_id from vendor_brand where vendor_id='{0}') ", query.vendor_id); } //string strCount = "select count(b.product_id) as totalCount from((" + sbProCols + sbProTbls + sbTbls + sbCondition + sbProCondition + ") union (" + sbProTempCols + sbProTempTbls + sbTbls + sbCondition + sbProTempCondition + ")) as b"; string strCount = "(" + sbProCols + sbProTbls + sbTbls + sbCondition + sbProCondition + ") union (" + sbProTempCols + sbProTempTbls + sbTbls + sbCondition + sbProTempCondition + ")"; System.Data.DataTable _dt = _dbAccess.getDataTable(strCount); totalCount = 0; if (_dt != null && _dt.Rows.Count > 0) { //totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]); totalCount = _dt.Rows.Count; } //sbCondition.Append(" order by a.product_id desc "); if (query.IsPage) { sbLimit.AppendFormat(" limit {0},{1}", query.Start, query.Limit); } string sql = "select * from((" + sbProTempCols + sbProTempTbls + sbTbls + sbCondition + sbProTempCondition + sbOrderBy + ") union (" + sbProCols + sbProTbls + sbTbls + sbCondition + sbProCondition + sbOrderBy + ")) pp order by pp.temp_status desc " + sbLimit; return _dbAccess.getDataTableForObj<VenderProductListCustom>(sql); } catch (Exception ex) { throw new Exception("ProductDao.GetVendorProduct-->" + ex.Message, ex); } }