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