/// <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); } }