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