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