public List<Model.Custom.QueryandVerifyCustom> QueryByProSite(Model.Query.QueryVerifyCondition query, out int totalCount)
{
try
{
var regex = new Regex("^[0-9,]+$");
query.Replace4MySQL();
StringBuilder strCols = new StringBuilder("select a.product_id,b.brand_name,a.product_image,a.prod_sz,a.combination AS combination_id,a.product_spec AS product_spec_id,");
strCols.Append("a.product_price_list,a.sale_status AS sale_status_id,v.vendor_name_full,v.vendor_name_simple,v.erp_id,a.product_status as product_status_id,a.user_id, a.create_channel,a.prepaid,a.bag_check_money,a.off_grade ");//添加 失格欄位 a.off_grade add by zhuoqin0830w 2015/06/30
//add by wangwei 2014/9/29 添加a.create_channel字段
strCols.Append(",a.purchase_in_advance_start,a.purchase_in_advance_end,a.expect_time,a.outofstock_days_stopselling,prr.create_time as 'outofstock_create_time' ");//添加預購商品開始時間 ,結束時間 guodong1130w 2015/9/16
StringBuilder strTbls = new StringBuilder("from product a left join vendor_brand b on a.brand_id=b.brand_id ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') c on a.combination=c.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_spec') d on a.product_spec=d.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') e on a.product_status=e.parametercode ");
//strTbls.Append(" LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='sale_status') sa ON a.sale_status = sa.parametercode "); //add by wwei0216w 2015/02/05
//add by wwei 0216w 2015/5/18
strTbls.Append(" LEFT JOIN vendor v ON v.vendor_id = b.vendor_id "); //add by wwei0216w 2015/02/06
strTbls.Append(" LEFT JOIN product_remove_reason prr ON a.product_id = prr.product_id "); //add by wwei0216w 2015/02/06
StringBuilder strCondition = new StringBuilder("where 1=1 ");
if (query.brand_id != 0)
{
strCondition.AppendFormat(" and a.brand_id={0}", query.brand_id);
}
//庫存分類 edit by xiangwang0413w 2014/11/24
if (query.StockStatus != 0)// 1.庫存為0還可販售 2.補貨中停止販售 3.庫存數<1
{
switch (query.StockStatus)
{
case 1://1.庫存為0還可販售
strCondition.Append(" and a.combination=1 and a.ignore_stock=1 ");
break;
case 2:
strCondition.Append(" and a.shortage=1");//2.補貨中停止販售
break;
case 3:
strCondition.Append(" and a.combination=1 and a.product_id in (select distinct product_id from product_item pi where item_stock <1 and pi.product_id=a.product_id)");//3.庫存數<1
break;
default:
throw new Exception("unaccepted StockStatus");
}
}
if (!string.IsNullOrEmpty(query.cate_id))
{
strCondition.AppendFormat(" and a.cate_id in ('{0}')", GetStrbyCate_id(query.cate_id));
}
if (query.category_id != 0)
{//edit by hjiajun1211w 2014/08/08 父商品查詢
IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
string str = string.Empty;
GetAllCategory_id(category, query.category_id, ref str);
strTbls.AppendFormat(" inner join (select distinct product_id from product_category_set where category_id in({0})) j on a.product_id=j.product_id ", str);
}
if (query.combination != 0)
{
if (query.combination == 1 && query.outofstock_days_stopselling > 0)// add by dongya 2015/10/22
{
strCondition.AppendFormat(" and a.combination={0} and a.outofstock_days_stopselling >= '{1}' ", query.combination, query.outofstock_days_stopselling);
}
else
{
strCondition.AppendFormat(" and a.combination={0}", query.combination);
}
}
if (query.product_status != -1)
{
strCondition.AppendFormat(" and a.product_status={0}", query.product_status);
}
if (query.product_type != -1)
{
strCondition.AppendFormat(" and a.product_type={0}", query.product_type);
}
if (query.freight != 0)
{
strCondition.AppendFormat(" and a.product_freight_set={0}", query.freight);
}
if (query.mode != 0)
{
strCondition.AppendFormat(" and a.product_mode={0}", query.mode);
}
if (query.tax_type != 0)
{
strCondition.AppendFormat(" and a.tax_type={0}", query.tax_type);
}
//add by zhuoqin0830w 2015/03/11 已買斷商品的篩選功能
if (query.Prepaid != -1)
{
strCondition.AppendFormat(" and a.prepaid={0}", query.Prepaid);
}
//add by zhuoqin0830w 2015/06/30 失格商品篩選
if (query.off_grade != 0)
{
strCondition.AppendFormat(" and a.off_grade={0} ", query.off_grade);
}
//add by guodong1130w 2015/09/17 預購商品
if (query.purchase_in_advance != 0)
{
strCondition.AppendFormat(" and a.purchase_in_advance={0} ", query.purchase_in_advance);
}
if (!string.IsNullOrEmpty(query.date_type))
{
CheckCondition(query, "a", strCondition);
}
if (query.price_check) //價格進階搜尋條件
{
strCols.Append(",f.product_name,f.price_master_id,f.user_id as master_user_id,g.site_name,f.site_id,f.user_level as level,f.price_status AS price_status_id,f.price,f.cost,f.event_price,f.event_cost,f.event_start,f.event_end,a.prod_classify ");//add by wwei0216w 添加f.cost:成本 //添加館別欄位 eidt by zhuoqin0830w 2015/03/05
strTbls.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) ");
strTbls.Append("left join site g on f.site_id=g.site_id ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='userlevel') h on f.user_level=h.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='price_status') i on f.price_status=i.parametercode ");
strCondition.Append(" and f.site_id is not null ");
if (!query.IsPage) //edit by wangwei0216w 當價格條件被選中,並且不分頁,證明為.商品價格匯出 拼接以下條件
{
//strTbls.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') k on a.product_freight_set=k.parametercode ");
//strTbls.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') l on a.product_mode=l.parametercode ");
strCols.Append(" ,a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.tax_type,a.product_start,a.product_end ");
strCondition.Append(" and a.product_id > 10000");//匯出時不匯出大於10000的商品
}
if (!string.IsNullOrEmpty(query.name_number))
{
strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or f.product_name like '%{0}%' or a.product_id in(select distinct product_id from product_item where item_id in({0})))" : " and f.product_name like '%{0}%'", query.name_number);//add by zhuoqin0830w 2015/03/30 添加商品細項編號
}
if (query.site_id != 0)
{
strCondition.AppendFormat(" and f.site_id={0}", query.site_id);
}
if (query.user_level != 0)
{
strCondition.AppendFormat(" and f.user_level={0}", query.user_level);
}
if (query.price_status != 0)
{
strCondition.AppendFormat(" and f.price_status={0}", query.price_status);
}
}
else
{
strCols.Append(",a.product_name,a.price_type AS price_type_id,a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.tax_type,a.product_sort,a.product_createdate,a.product_start,a.product_end,a.prod_classify ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') f on a.product_freight_set=f.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') g on a.product_mode=g.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='price_type') h on a.price_type=h.parametercode ");
if (!string.IsNullOrEmpty(query.name_number))
{
strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%' or a.product_id in (select distinct product_id from product_item where item_id in({0})))" : " and a.product_name like '%{0}%'", query.name_number); //add by zhuoqin0830w 2015/03/30 添加商品細項編號
}
if (!query.IsPage)//匯出時不匯出大於10000的商品
{
//商品匯出添加商品上下架備註欄位 add by mingwei0727w 2015/09/25
strCols.Append(" ,psh.remark ");
//strTbls.AppendFormat(" LEFT JOIN (select product_id,remark from (select product_id,remark from product_status_history where type =1 order by create_time desc) as kp group by product_id) as psh on a.product_id = psh.product_id ");
strTbls.AppendFormat(" LEFT JOIN (select product_id,remark from product_status_history as pshman inner join ( select max(id) as mid from product_status_history where type = 1 and remark <> '' group by product_id ) as pshson on pshman.id=pshson.mid)as psh on a.product_id = psh.product_id ");
strCondition.Append(" and a.product_id > 10000");
}
}
//添加 按鈕選擇值的查詢條件 edit by zhuoqin0830w 2015/02/10
if (query.priceCondition == 2)
{
strTbls.Append("left join price_master l on a.product_id=l.product_id and l.site_id= 1 and l.user_level=1 and l.user_id=0 and (l.product_id=l.child_id or l.child_id=0) ");
strCondition.AppendFormat(" and ((a.combination not in(0,1) and a.price_type <>2) or (a.combination = 1 and l.same_price =1))");
}
string strCount = "select count(a.product_id) as totalCount " + strTbls.ToString() + strCondition.ToString();
System.Data.DataTable _dt = _dbAccess.getDataTable(strCount);
totalCount = 0;
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
if (query.price_check == false)
{
/*
*以下3行 add by wwei0216w 2015/8/13
*用於查詢商品所對應的子商品item_id
*/
strTbls.Append(" LEFT JOIN product_item pi ON a.product_id = pi.product_id ");
strCols.Append(" ,GROUP_CONCAT(pi.item_id) AS itemIds ");
strCondition.Append(" GROUP BY a.product_id ");
}
strCondition.Append(" order by a.product_id desc ");
if (query.IsPage)
{
strCondition.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_spec", "product_status", "sale_status", "UserLevel", "price_status", "product_freight", "product_mode", "Price_Type");
List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
foreach (QueryandVerifyCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_spec" && m.ParameterCode == q.product_spec_id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.product_status_id.ToString());
var dlist = parameterList.Find(m => m.ParameterType == "sale_status" && m.ParameterCode == q.sale_status_id.ToString());
var elist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.level.ToString());
var flist = parameterList.Find(m => m.ParameterType == "price_status" && m.ParameterCode == q.price_status_id.ToString());
var glist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.product_freight_set_id.ToString());
var hlist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == q.product_mode_id.ToString());
var jlist = parameterList.Find(m => m.ParameterType == "Price_Type" && m.ParameterCode == q.price_type_id.ToString());
if (alist != null)
{
q.combination = alist.parameterName;
}
if (blist != null)
{
q.product_spec = blist.parameterName;
}
if (clist != null)
{
q.product_status = clist.parameterName;
}
if (dlist != null)
{
q.sale_name = dlist.parameterName;
}
if (elist != null)
{
q.user_level = elist.parameterName;
}
if (flist != null)
{
q.price_status = flist.parameterName;
}
if (glist != null)
{
q.product_freight_set = glist.parameterName;
}
if (hlist != null)
{
q.product_mode = hlist.parameterName;
}
if (jlist != null)
{
q.price_type = jlist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.QueryByProSite-->" + ex.Message, ex);
}
}
//根據商品列表的查詢條件查詢出商品信息
public List<Model.Custom.PriceMasterCustom> Query(Model.Query.QueryVerifyCondition query)
{
try
{
query.Replace4MySQL();
StringBuilder strCols = new StringBuilder("select a.product_id,concat('【',b.brand_name,'】',a.product_name) as product_name ,a.prod_sz,a.combination, ");
strCols.Append(" c.price,c.cost,c.site_id,c.user_level,c.user_id ");
StringBuilder strTbls = new StringBuilder(" from product a ");
strTbls.Append(" left join vendor_brand b on a.brand_id=b.brand_id ");
strTbls.Append(" inner join price_master c on a.product_id=c.product_id ");
StringBuilder strCondition = new StringBuilder(" where 1=1 ");
if (query.brand_id != 0)
{
strCondition.AppendFormat(" and a.brand_id={0}", query.brand_id);
}
//add by zhuoqin0830w 2015/07/22 失格商品篩選
if (query.off_grade != 0)
{
strCondition.AppendFormat(" and a.off_grade={0} ", query.off_grade);
}
if (!string.IsNullOrEmpty(query.cate_id))
{
strCondition.AppendFormat(" and a.cate_id='{0}'", query.cate_id);
}
//add by guodong1130w 2015/09/17 預購商品
if (query.purchase_in_advance != 0)
{
strCondition.AppendFormat(" and a.purchase_in_advance={0} ", query.purchase_in_advance);
}
if (query.category_id != 0)
{
//edit by hjiajun1211w 2014/08/08 父商品查詢
IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
string str = string.Empty;
GetAllCategory_id(category, query.category_id, ref str);
strTbls.AppendFormat(" inner join (select distinct product_id from product_category_set where category_id in({0})) j on a.product_id=j.product_id ", str);
}
if (query.combination != 0)
{
strCondition.AppendFormat(" and a.combination={0}", query.combination);
}
if (query.product_status != -1)
{
strCondition.AppendFormat(" and a.product_status={0}", query.product_status);
}
if (query.freight != 0)
{
strCondition.AppendFormat(" and a.product_freight_set={0}", query.freight);
}
if (query.mode != 0)
{
strCondition.AppendFormat(" and a.product_mode={0}", query.mode);
}
if (query.tax_type != 0)
{
strCondition.AppendFormat(" and a.tax_type={0}", query.tax_type);
}
if (!string.IsNullOrEmpty(query.date_type))
{
CheckCondition(query, "a", strCondition);
}
if (!string.IsNullOrEmpty(query.name_number))
{
strCondition.AppendFormat(new Regex("^[0-9,]+$").IsMatch(query.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%') " : " and a.product_name like '%{0}%'", query.name_number);
}
strCondition.AppendFormat(" and c.site_id={0} and c.user_level={1} and c.user_id={2} and a.combination in (1,2,3,4) ", query.site_id, query.user_level, query.user_id);
strCondition.Append(" order by a.product_id desc ");
return _dbAccess.getDataTableForObj<Model.Custom.PriceMasterCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString()); ;
}
catch (Exception ex)
{
throw new Exception("ProductDao.Query(Model.Query.QueryVerifyCondition query)-->" + ex.Message, ex);
}
}
/// <summary>
/// add by jiajun 2014/08/22
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<Model.Custom.QueryandVerifyCustom> QueryForStation(Model.Query.QueryVerifyCondition query, out int totalCount)
{
try
{
var regex = new Regex("^[0-9,]+$");
query.Replace4MySQL();
StringBuilder strCols = new StringBuilder("select a.product_id,b.brand_name,a.product_image,a.product_name,a.prod_sz,a.product_spec AS product_spec_id,");
strCols.Append("a.product_price_list,a.product_status as product_status_id,a.combination as combination_id,a.user_id ");
StringBuilder strTbls = new StringBuilder("from product a left join vendor_brand b on a.brand_id=b.brand_id ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') c on a.combination=c.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_spec') d on a.product_spec=d.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') e on a.product_status=e.parametercode ");
StringBuilder strCondition = new StringBuilder("where 1=1 ");
if (query.brand_id != 0)
{
strCondition.AppendFormat(" and a.brand_id={0}", query.brand_id);
}
if (!string.IsNullOrEmpty(query.cate_id))
{
strCondition.AppendFormat(" and a.cate_id in ('{0}')", GetStrbyCate_id(query.cate_id));
}
if (query.category_id != 0)
{//edit by hjiajun1211w 2014/08/08 父商品查詢
IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
string str = string.Empty;
GetAllCategory_id(category, query.category_id, ref str);
strTbls.AppendFormat(" inner join (select distinct product_id from product_category_set where category_id in({0})) j on a.product_id=j.product_id ", str);
}
if (query.combination != 0)
{
strCondition.AppendFormat(" and a.combination={0}", query.combination);
}
if (query.product_status != -1)
{
strCondition.AppendFormat(" and a.product_status={0}", query.product_status);
}
if (query.freight != 0)
{
strCondition.AppendFormat(" and a.product_freight_set={0}", query.freight);
}
if (query.mode != 0)
{
strCondition.AppendFormat(" and a.product_mode={0}", query.mode);
}
if (query.tax_type != 0)
{
strCondition.AppendFormat(" and a.tax_type={0}", query.tax_type);
}
//add by zhuoqin0830w 2015/03/11 已買斷商品的篩選功能
if (query.Prepaid != -1)
{
strCondition.AppendFormat(" and a.prepaid={0}", query.Prepaid);
}
if (!string.IsNullOrEmpty(query.date_type))
{
CheckCondition(query, "a", strCondition);
}
strCols.Append(",a.price_type AS price_type_id,a.product_freight_set AS product_freight_set_id,a.product_mode as product_mode_id,a.tax_type,a.product_sort,a.product_createdate,a.product_start,a.product_end ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') f on a.product_freight_set=f.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') g on a.product_mode=g.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='price_type') h on a.price_type=h.parametercode ");
//edit 2014/08/26
if (query.priceCondition == 2)
{
strTbls.Append("left join price_master l on a.product_id=l.product_id and l.site_id= 1 and l.user_level=1 and l.user_id=0 and (l.product_id=l.child_id or l.child_id=0) ");
strCondition.AppendFormat(" and ((a.combination not in(0,1) and a.price_type <>2) or (a.combination = 1 and l.same_price =1))");
}
if (!string.IsNullOrEmpty(query.name_number))
{
strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%')" : " and a.product_name like '%{0}%'", query.name_number);
}
string strCount = "select count(a.product_id) as totalCount " + strTbls.ToString() + strCondition.ToString();
System.Data.DataTable _dt = _dbAccess.getDataTable(strCount);
totalCount = 0;
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
strCondition.Append(" order by a.product_id desc ");
if (query.IsPage)
{
strCondition.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_spec", "product_status", "product_freight", "product_mode", "Price_Type");
List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
foreach (QueryandVerifyCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_spec" && m.ParameterCode == q.product_spec_id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.product_status_id.ToString());
var dlist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.product_freight_set_id.ToString());
var elist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == q.product_mode_id.ToString());
var flist = parameterList.Find(m => m.ParameterType == "Price_Type" && m.ParameterCode == q.price_type_id.ToString());
if (alist != null)
{
q.combination = alist.parameterName;
}
if (blist != null)
{
q.product_spec = blist.parameterName;
}
if (clist != null)
{
q.product_status = clist.parameterName;
}
if (dlist != null)
{
q.product_freight_set = dlist.parameterName;
}
if (elist != null)
{
q.product_mode = elist.parameterName;
}
if (flist != null)
{
q.price_type = flist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.QueryByProSite-->" + ex.Message, ex);
}
}
/// <summary>
/// 獲取庫存資料信息
/// </summary>
/// <param name="query">判斷條件</param>
/// <param name="totalCount">總共記錄</param>
/// <returns>符合條件的集合</returns>
/// Add by wangwei0216w 2014/10/20
public List<Model.Custom.ProductItemCustom> GetStockInfo(Model.Query.QueryVerifyCondition query)
{
try
{
//add by zhuoqin0830w 2015/02/25 添加匯出庫存資料中規格一，二的狀態顯示和備註欄位
StringBuilder strCols = new StringBuilder("SELECT b.brand_name,p.product_id,item.item_id,item.erp_id,p.product_name,p.prod_sz,s1.spec_name as Spec_Name_1,s2.spec_name as Spec_Name_2,item.item_stock,p.prepaid,item.item_alarm,p.product_status AS Product_Status_Id,item.export_flag,s1.spec_status as Spec_Status1,s2.spec_status as Spec_Status2,item.remark,p.product_freight_set AS Freight_Set_Id,p.product_mode AS Product_Mode_Id");
//add by wangwei 2014/9/29 添加a.create_channel字段
StringBuilder strTbls = new StringBuilder(" FROM product p ");
strTbls.AppendFormat(" INNER JOIN product_item item ON item.product_id = p.product_id ");
strTbls.AppendFormat(" LEFT JOIN vendor_brand b ON p.brand_id=b.brand_id ");
//strTbls.Append("LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='product_status') e ON p.product_status=e.parametercode ");
//strTbls.Append("LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='product_freight') f ON p.product_freight_set=f.parametercode ");
//strTbls.Append("LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='product_mode') g ON p.product_mode=g.parametercode ");
strTbls.Append("LEFT JOIN product_spec s1 on item.spec_id_1 = s1.spec_id ");
strTbls.Append("LEFT JOIN product_spec s2 on item.spec_id_2 = s2.spec_id ");
StringBuilder strCondition = new StringBuilder(" WHERE 1=1 AND p.product_id > 10000 ");
if (query.StockStatus != 0)// 1.庫存為0還可販售 2.補貨中停止販售 3.庫存數<1
{
switch (query.StockStatus)
{
case 1://1.庫存為0還可販售
strCondition.Append(" and p.combination=1 and p.ignore_stock=1 ");
break;
case 2:
strCondition.Append(" and p.shortage=1");//2.補貨中停止販售
break;
case 3:
strCondition.Append(" and p.combination=1 and p.product_id in (select distinct product_id from product_item pi where item_stock <1 and pi.product_id=p.product_id)");//3.庫存數<1 edit by wwei0216w 不存在a.product_id 改為p.product_id 2015/11/18
break;
default:
throw new Exception("unaccepted StockStatus");
}
}
if (query.combination != 0)
{
strCondition.AppendFormat(" AND p.combination={0}", query.combination);
}
if (query.brand_id != 0)
{
strCondition.AppendFormat(" AND p.brand_id={0}", query.brand_id);
}
if (!string.IsNullOrEmpty(query.cate_id))
{
strCondition.AppendFormat(" AND p.cate_id IN ('{0}')", GetStrbyCate_id(query.cate_id));
}
if (query.category_id != 0)
{
IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
string str = string.Empty;
GetAllCategory_id(category, query.category_id, ref str);
strTbls.AppendFormat(" INNER JOIN (SELECT DISTINCT product_id FROM product_category_set WHERE category_id IN({0})) j ON p.product_id=j.product_id ", str);
}
if (query.product_status != -1)
{
strCondition.AppendFormat(" AND p.product_status={0}", query.product_status);
}
if (query.freight != 0)
{
strCondition.AppendFormat(" AND p.product_freight_set={0}", query.freight);
}
if (query.mode != 0)
{
strCondition.AppendFormat(" AND p.product_mode={0}", query.mode);
}
if (query.tax_type != 0)
{
strCondition.AppendFormat(" AND p.tax_type={0}", query.tax_type);
}
//add by zhuoqin0830w 2015/06/30 失格商品篩選
if (query.off_grade != 0)
{
strCondition.AppendFormat(" AND p.off_grade={0} ", query.off_grade);
}
if (!string.IsNullOrEmpty(query.date_type))
{
CheckCondition(query, "p", strCondition);
}
if (!string.IsNullOrEmpty(query.name_number))
{
strCondition.AppendFormat(new Regex("^[0-9,]+$").IsMatch(query.name_number) ? " and (p.product_id in ({0}) or p.product_name like '%{0}%' or p.product_id in (select distinct product_id from product_item where item_id in({0})))" : " and p.product_name like '%{0}%'", query.name_number);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("product_status", "product_freight", "product_mode");
List<Model.Custom.ProductItemCustom> list = _dbAccess.getDataTableForObj<Model.Custom.ProductItemCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
foreach (ProductItemCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.Product_Status_Id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.Freight_Set_Id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == q.Product_Mode_Id.ToString());
if (alist != null)
{
q.Product_Status = alist.parameterName;
}
if (blist != null)
{
q.Freight_Set = blist.parameterName;
}
if (clist != null)
{
q.Product_Mode = clist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.GetStoreInfo-->" + ex.Message, ex);
}
}
/// <summary>
/// 預購商品導出 guodong1130w 2015/09/17 添加
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<Model.Custom.QueryandVerifyCustom> QueryForPurchase_in_advance(Model.Query.QueryVerifyCondition query, out int totalCount)
{
try
{
var regex = new Regex("^[0-9,]+$");
query.Replace4MySQL();
StringBuilder strCols = new StringBuilder("select a.product_id,b.brand_name,v.vendor_name_full,a.combination AS combination_id");
strCols.Append(",a.purchase_in_advance_start,a.purchase_in_advance_end,a.expect_time,pi.item_stock,CAST(sum(odetail.buy_num) AS SIGNED) as bnum,sdule.schedule_name ");
StringBuilder strTbls = new StringBuilder("from product a left join vendor_brand b on a.brand_id=b.brand_id ");
strTbls.Append(" LEFT JOIN vendor v ON v.vendor_id = b.vendor_id ");
//連接查出未出貨數量
strTbls.Append(" LEFT JOIN product_item pi ON pi.product_id = a.product_id ");
strTbls.Append(@"LEFT JOIN (select ordetail.item_id,ordetail.buy_num,ordetail.detail_id from order_detail as ordetail
INNER JOIN deliver_detail as ddetail on ordetail.detail_id=ddetail.detail_id
and ddetail.delivery_status in (0,1,2,3)
and detail_status=2 and item_mode<>1
LEFT JOIN order_slave AS oslave on ordetail.slave_id=oslave.slave_id
INNER JOIN order_master AS omaster on omaster.order_id=oslave.order_id
WHERE ((order_payment=8 and money_collect_date=0) or money_collect_date<>0)) as odetail on odetail.item_id = pi.item_id ");
//關聯查出排成名稱
strTbls.Append(" LEFT JOIN schedule_relation srelation on srelation.relation_id=a.product_id and srelation.relation_table='product' ");
strTbls.Append(" LEFT JOIN schedule sdule on sdule.schedule_id=srelation.schedule_id ");
StringBuilder strCondition = new StringBuilder(" WHERE 1=1 ");
if (query.brand_id != 0)
{
strCondition.AppendFormat(" and a.brand_id={0}", query.brand_id);
}
//庫存分類 edit by xiangwang0413w 2014/11/24
if (query.StockStatus != 0)// 1.庫存為0還可販售 2.補貨中停止販售 3.庫存數<1
{
switch (query.StockStatus)
{
case 1://1.庫存為0還可販售
strCondition.Append(" and a.combination=1 and a.ignore_stock=1 ");
break;
case 2:
strCondition.Append(" and a.shortage=1");//2.補貨中停止販售
break;
case 3:
strCondition.Append(" and a.combination=1 and a.product_id in (select distinct product_id from product_item pi where item_stock <1 and pi.product_id=a.product_id)");//3.庫存數<1
break;
default:
throw new Exception("unaccepted StockStatus");
}
}
if (!string.IsNullOrEmpty(query.cate_id))
{
strCondition.AppendFormat(" and a.cate_id in ('{0}')", GetStrbyCate_id(query.cate_id));
}
if (query.category_id != 0)
{//父商品查詢
IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
string str = string.Empty;
GetAllCategory_id(category, query.category_id, ref str);
strTbls.AppendFormat(" inner join (select distinct product_id from product_category_set where category_id in({0})) j on a.product_id=j.product_id ", str);
}
if (query.combination != 0)
{
strCondition.AppendFormat(" and a.combination={0}", query.combination);
}
if (query.product_status != -1)
{
strCondition.AppendFormat(" and a.product_status={0}", query.product_status);
}
if (query.product_type != -1)
{
strCondition.AppendFormat(" and a.product_type={0}", query.product_type);
}
if (query.freight != 0)
{
strCondition.AppendFormat(" and a.product_freight_set={0}", query.freight);
}
if (query.mode != 0)
{
strCondition.AppendFormat(" and a.product_mode={0}", query.mode);
}
if (query.tax_type != 0)
{
strCondition.AppendFormat(" and a.tax_type={0}", query.tax_type);
}
// 已買斷商品的篩選功能
if (query.Prepaid != -1)
{
strCondition.AppendFormat(" and a.prepaid={0}", query.Prepaid);
}
// 失格商品篩選
if (query.off_grade != 0)
{
strCondition.AppendFormat(" and a.off_grade={0} ", query.off_grade);
}
//預購商品
if (query.purchase_in_advance != 0)
{
strCondition.AppendFormat(" and a.purchase_in_advance={0} ", query.purchase_in_advance);
}
if (!string.IsNullOrEmpty(query.date_type))
{
CheckCondition(query, "a", strCondition);
}
strCols.Append(",a.product_name ");
if (!string.IsNullOrEmpty(query.name_number))
{
strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%' or a.product_id in (select distinct product_id from product_item where item_id in({0})))" : " and a.product_name like '%{0}%'", query.name_number); //add by zhuoqin0830w 2015/03/30 添加商品細項編號
}
if (!query.IsPage)//匯出時不匯出大於10000的商品
{
strCondition.Append(" and a.product_id > 10000");
}
//添加 按鈕選擇值的查詢條件
if (query.priceCondition == 2)
{
strTbls.Append("left join price_master l on a.product_id=l.product_id and l.site_id= 1 and l.user_level=1 and l.user_id=0 and (l.product_id=l.child_id or l.child_id=0) ");
strCondition.AppendFormat(" and ((a.combination not in(0,1) and a.price_type <>2) or (a.combination = 1 and l.same_price =1))");
}
string strCount = "select count(a.product_id) as totalCount " + strTbls.ToString() + strCondition.ToString();
System.Data.DataTable _dt = _dbAccess.getDataTable(strCount);
totalCount = 0;
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
strCols.Append(" ,GROUP_CONCAT(pi.item_id) AS itemIds ");
strCondition.Append(" GROUP BY a.product_id ");
strCondition.Append(" order by a.product_id desc ");
if (query.IsPage)
{
strCondition.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_spec", "product_status", "sale_status", "UserLevel", "price_status", "product_freight", "product_mode", "Price_Type");
List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
foreach (QueryandVerifyCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
if (alist != null)
{
q.combination = alist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.QueryForPurchase_in_advance-->" + ex.Message, ex);
}
}
