public PromoPairDao(string connectionstring)
{
_access = DBFactory.getDBAccess(DBType.MySql, connectionstring);
this.connStr = connectionstring;
_proCateDao = new ProductCategoryDao(connectionstring);
_prodCategSet = new ProductCategorySetDao(connectionstring);
_prodPromoDao = new ProdPromoDao(connectionstring);
_promoAllDao = new PromoAllDao(connectionstring);
_serialDao = new SerialDao(connectionstring);
}
/// <summary>
/// 有參構造函數
/// </summary>
/// <param name="connectionstring">數據庫連接字符串</param>
public PromotionsAmountFareDao(string connectionstring)
{
_access = DBFactory.getDBAccess(DBType.MySql, connectionstring);
this.connStr = connectionstring;
_cateDao = new ProductCategoryDao(connectionstring);
_prodCateSetDao = new ProductCategorySetDao(connectionstring);
_prodPromoDao = new ProdPromoDao(connectionstring);
_promoAllDao = new PromoAllDao(connectionstring);
_promAmountReduceDao = new PromotionsAmountReduceDao(connectionstring);
_usconDao = new UserConditionDao(connectionstring);
_serialDao = new SerialDao(connectionstring);
}
/// <summary>
/// 有參構造函數
/// </summary>
/// <param name="connectionstring">數據庫連接字符串</param>
public PromotionsAmountDiscountDao(string connectionstring)
{
_access = DBFactory.getDBAccess(DBType.MySql, connectionstring);
this.connStr = connectionstring;
_prodpromoDao = new ProdPromoDao(connectionstring);
_proCateDao = new ProductCategoryDao(connectionstring);
_prodCategSet = new ProductCategorySetDao(connectionstring);
_proAllDao = new PromoAllDao(connectionstring);
_usconDao = new UserConditionDao(connectionstring);
_promoDisDao = new PromoDiscountDao(connectionstring);
_mySql = new MySqlDao(connectionstring);
}
public PromotionsAmountDiscountMgr(string connectionstring)
{
_mysqlDao = new MySqlDao(connectionstring);
_padDao = new PromotionsAmountDiscountDao(connectionstring);
_prodpromoDao = new ProdPromoDao(connectionstring);
_proCateDao = new ProductCategoryDao(connectionstring);
_prodCategSet = new ProductCategorySetDao(connectionstring);
_proAllDao = new PromoAllDao(connectionstring);
_usconDao = new UserConditionDao(connectionstring);
_promoDisDao = new PromoDiscountDao(connectionstring);
_promoMainDao = new PromotionsMaintainDao(connectionstring);
_prodDao = new ProductDao(connectionstring);
_vendorBrandDao = new VendorBrandDao(connectionstring);
connStr = connectionstring;
}
/// <summary>
/// 有參構造函數
/// </summary>
/// <param name="connectionstring">數據庫連接字符串</param>
public PromotionsAmountGiftDao(string connectionstring)
{
_access = DBFactory.getDBAccess(DBType.MySql, connectionstring);
this.connStr = connectionstring;
_proCateDao = new ProductCategoryDao(connectionstring);
_prodCategSetDao = new ProductCategorySetDao(connectionstring);
_prodpromoDao = new ProdPromoDao(connectionstring);
_proAllDao = new PromoAllDao(connectionstring);
_usconDao = new UserConditionDao(connectionstring);
_promoDisDao = new PromoDiscountDao(connectionstring);
_serialDao = new SerialDao(connectionstring);
_ptDao = new PromoTicketDao(connectionstring);
_prodDao = new ProductDao(connectionstring);
_promoMainDao = new PromotionsMaintainDao(connectionstring);
_vendorBrandDao = new VendorBrandDao(connectionstring);
}
public int Update(PromotionsAmountFareQuery model, string oldEventId)
{
int i = 0;
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sbExSql = new StringBuilder();
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
StringBuilder sqlf = new StringBuilder();
mySqlCmd.CommandText = _prodPromoDao.DeleteProdProm(model.event_id);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//刪除ProdPromo
mySqlCmd.CommandText = _promoAllDao.DelPromAll(model.event_id);
sbExSql.Append(_promoAllDao.DelPromAll(mySqlCmd.CommandText));
mySqlCmd.ExecuteNonQuery();//刪除PromAll
if (model.allClass == 0)
{
//刪除全館商品
ProductCategorySet qgSet = new ProductCategorySet();
qgSet.Category_Id = model.category_id;
qgSet.Product_Id = 999999;//全館商品刪除 id=999999
//根據category_id刪除product_category_set表數據
mySqlCmd.CommandText = _prodCateSetDao.DelProdCateSetByCPID(qgSet);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//刪除全館別商品999999
if (model.brand_id != 0)//當品牌不為空時講該品牌下的所有商品加入set表
{
sqlf.Append(_prodCateSetDao.DeleteProdCateSet(model.category_id));
mySqlCmd.CommandText = sqlf.ToString();
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
sqlf.Clear();
QueryVerifyCondition query = new QueryVerifyCondition();//根據條件從product表中獲取符合條件的商品并添加到set和prodprom表中
query.brand_id = Convert.ToUInt32(model.brand_id);//品牌
query.site_ids = model.site;
query.combination = 1;
PromotionsMaintainDao _promoMainDao = new PromotionsMaintainDao(connStr);
query.IsPage = false;
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.GetProList(query, out totalCount);
List<uint> categorysetProduct = new List<uint>();
foreach (QueryandVerifyCustom qvcItem in qvcList)
{
if (categorysetProduct.Contains(qvcItem.product_id))
{
continue;
}
categorysetProduct.Add(qvcItem.product_id);
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Product_Id = qvcItem.product_id;
pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
pcsModel.Category_Id = model.category_id;
//刪除已有的 新增異動的
sqlf.Append(_prodCateSetDao.SaveProdCategorySet(pcsModel));
}
}
if (!string.IsNullOrEmpty(sqlf.ToString()))
{
mySqlCmd.CommandText = sqlf.ToString();
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
sqlf.Clear();
}
}
else//全館時
{
sqlf.Clear();
sqlf.AppendFormat(_prodCateSetDao.DeleteProdCateSet(model.category_id));
ProductCategorySet pcs = new ProductCategorySet();
pcs.Category_Id = model.category_id;
pcs.Product_Id = 999999;
pcs.Brand_Id = 0;
sqlf.Append(_prodCateSetDao.SaveProdCategorySet(pcs));
mySqlCmd.CommandText = sqlf.ToString();
sbExSql.Append(_promoAllDao.DeletePromAll(mySqlCmd.CommandText));
mySqlCmd.ExecuteNonQuery();
}
sqlf.Clear();
PromoAll pamodel = new PromoAll();
pamodel.event_id = model.event_id;
pamodel.event_type = model.event_type;
pamodel.category_id = Convert.ToInt32(model.category_id);
pamodel.startTime = model.start;
pamodel.end = model.end;
pamodel.status = model.status;
pamodel.kuser = model.kuser;
pamodel.kdate = model.created;
pamodel.muser = model.muser;
pamodel.mdate = model.modified;
pamodel.product_id = model.product_id;
pamodel.class_id = model.class_id;
pamodel.brand_id = model.brand_id;
sqlf.AppendFormat(_promoAllDao.SavePromAll(pamodel));
ProductCategoryDao _categoryDao = new ProductCategoryDao(connStr);
ProductCategory pcmodel = _categoryDao.GetModelById(Convert.ToUInt32(model.category_id));
pcmodel.category_id = Convert.ToUInt32(model.category_id);
pcmodel.category_name = model.name;
pcmodel.banner_image = model.banner_image;
pcmodel.category_link_url = model.category_link_url;
pcmodel.category_father_id = model.category_father_id;
pcmodel.category_updatedate = (uint)BLL.gigade.Common.CommonFunction.GetPHPTime(model.modified.ToString());
pcmodel.category_display = Convert.ToUInt32(model.status);
sqlf.AppendFormat(_cateDao.UpdateProdCate(pcmodel));
PromotionsAmountReduce parModel = new PromotionsAmountReduce();
parModel.id = model.id;
parModel.name = model.name;
parModel.delivery_store = model.delivery_store;
parModel.group_id = model.group_id;
parModel.type = model.type;
parModel.amount = model.amount;
parModel.quantity = model.quantity;
parModel.start = model.start;
parModel.end = model.end;
parModel.updatetime = model.modified;
parModel.created = model.created;
parModel.active = model.active ? 1 : 0;
parModel.status = model.status;
sqlf.AppendFormat(_promAmountReduceDao.Update(parModel));
mySqlCmd.CommandText = sqlf + UpdatePromoFare(model);
i += mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("PromotionsAmountFareDao-->Update-->sql:" + sbExSql.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return i;
}
public int Update(PromoPair model, PromoPairQuery PPquery)
{
int id = 0;
StringBuilder sb = new StringBuilder();
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
sb.AppendFormat(@"update promo_pair set condition_id='{1}',group_id='{2}',`start`='{3}',`end`='{4}',modified='{5}',deliver_type='{6}',device='{7}',muser='******',website='{9}',event_name='{10}',event_desc='{11}',price='{12}',discount='{13}',vendor_coverage='{14}',active=0 where id={0} ; ", model.id, model.condition_id, model.group_id, CommonFunction.DateTimeToString(model.starts), CommonFunction.DateTimeToString(model.end), CommonFunction.DateTimeToString(model.modified), model.deliver_type, model.device, model.muser, model.website, model.event_name, model.event_desc, model.price, model.discount,model.vendor_coverage);
#region 操作修改ProductCategory
ProductCategoryDao _categoryDao = new ProductCategoryDao(connStr);
ProductCategory pcmodel = _categoryDao.GetModelById(Convert.ToUInt32(model.category_id));
pcmodel.category_id = Convert.ToUInt32(model.category_id);
pcmodel.banner_image = PPquery.banner_image;
pcmodel.category_link_url = PPquery.category_link_url;
pcmodel.category_name = model.event_name;
pcmodel.category_display = Convert.ToUInt32(model.status);
sb.Append(_proCateDao.UpdateProdCate(pcmodel));
#endregion
#region 刪除原來的商品信息再新增
mySqlCmd.CommandText = _prodPromoDao.DeleteProdProm(PPquery.event_id);
mySqlCmd.ExecuteScalar();
////查詢出商品信息后插入prod_promo表
//if (model.active)
//{
// DataTable dt = _access.getDataTable("SELECT cate_red,cate_green FROM promo_pair WHERE id=" + model.id + "");
// DataTable dt_red = _access.getDataTable("SELECT product_id FROM product_category_set WHERE category_id=" + dt.Rows[0]["cate_red"] + " ;");
// DataTable dt_green = _access.getDataTable("SELECT product_id FROM product_category_set WHERE category_id=" + dt.Rows[0]["cate_green"] + " ;");
// if (dt_red.Rows.Count > 0)
// {
// for (int i = 0; i < dt_red.Rows.Count; i++)
// {
// ProdPromo ppmodel = new ProdPromo();
// ppmodel.product_id = Convert.ToInt32(dt_red.Rows[i][0].ToString());
// ppmodel.event_id = PPquery.event_id;
// ppmodel.event_type = model.event_type;
// ppmodel.event_desc = model.event_desc;
// ppmodel.start = model.starts;
// ppmodel.end = model.end;
// ppmodel.page_url = PPquery.category_link_url;
// if (model.group_id == 0 && model.condition_id == 0)
// ppmodel.user_specified = 0;
// else
// ppmodel.user_specified = 1;
// ppmodel.kuser = model.muser;
// ppmodel.kdate = model.modified;
// ppmodel.muser = model.muser;
// ppmodel.mdate = model.modified;
// ppmodel.status = model.status;
// sb.Append(_prodPromoDao.SaveProdProm(ppmodel));
// }
// }
// if (dt_green.Rows.Count > 0)
// {
// for (int i = 0; i < dt_green.Rows.Count; i++)
// {
// ProdPromo ppmodel = new ProdPromo();
// ppmodel.product_id = Convert.ToInt32(dt_green.Rows[i][0].ToString());
// ppmodel.event_id = PPquery.event_id;
// ppmodel.event_type = model.event_type;
// ppmodel.event_desc = model.event_desc;
// ppmodel.start = model.starts;
// ppmodel.end = model.end;
// ppmodel.page_url = PPquery.category_link_url;
// if (model.group_id == 0 && model.condition_id == 0)
// ppmodel.user_specified = 0;
// else
// ppmodel.user_specified = 1;
// ppmodel.kuser = model.muser;
// ppmodel.kdate = model.modified;
// ppmodel.muser = model.muser;
// ppmodel.mdate = model.modified;
// ppmodel.status = model.status;
// sb.Append(_prodPromoDao.SaveProdProm(ppmodel));
// }
// }
//}
#endregion
//if (PPquery.event_id != "")
//{
// sb.Append(_promoAllDao.DelPromAll(PPquery.event_id));
//}
mySqlCmd.CommandText = sb.ToString();
id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("PromoPairDao-->Update-->" + ex.Message, ex);
}
finally
{
mySqlConn.Close();
}
return id;
}
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>
/// 獲取庫存資料信息
/// </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>
/// 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>
/// 預購商品導出 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);
}
}
//新添加
#region 新增數據 第一步試吃/紅利抵用保存
#region 事物新增主表數據和product_category表數據 + int Save(PromotionsAmountGiftQuery query)
public int TryEatAndDiscountSave(PromotionsAmountGiftQuery query)
{
_proCateDao = new ProductCategoryDao(connStr);
query.Replace4MySQL();
int id = 0;
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sbExSql = new StringBuilder();
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
//#region 保存第一步到product_category 獲取prodduct_amount_fare的category_id
ProductCategory pmodel = new ProductCategory();
pmodel.category_name = query.name;
pmodel.category_createdate = (uint)BLL.gigade.Common.CommonFunction.GetPHPTime();
pmodel.category_father_id = query.category_father_id;
pmodel.category_ipfrom = query.category_ipfrom;
pmodel.category_display = Convert.ToUInt32(query.status);
mySqlCmd.CommandText = _proCateDao.SaveCategory(pmodel);
sbExSql.Append(mySqlCmd.CommandText);
query.category_id = Convert.ToUInt32(mySqlCmd.ExecuteScalar());
//修改表serial
Serial serial = new Serial();
serial.Serial_id = 12;
serial.Serial_Value = Convert.ToUInt32(query.category_id);
mySqlCmd.CommandText = _serialDao.UpdateAutoIncreament(serial);
sbExSql.Append(mySqlCmd.CommandText);
id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
//#endregion
PromotionsAmountGift pgmodel = new PromotionsAmountGift();
pgmodel.name = query.name;
pgmodel.event_desc = query.event_desc;
pgmodel.vendor_coverage = query.vendor_coverage;
pgmodel.event_type = query.event_type;
pgmodel.freight_price = query.freight_price;
pgmodel.kuser = query.kuser;
pgmodel.created = query.created;
pgmodel.status = query.status;
pgmodel.category_id = query.category_id;
pgmodel.dividend = query.dividend;
mySqlCmd.CommandText = TryEatAndDiscountSavePromoGift(pgmodel);
sbExSql.Append(mySqlCmd.CommandText);
id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("PromotionsAmountGiftDao-->Save-->" + ex.Message + sbExSql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return id;
}
/// <summary>
/// 第二步保存和編輯數據 更新主表和product_category數據 處理product_category_set prod_promo prom_all prom_ticket
/// </summary>
/// <param name="model">query對象</param>
/// <param name="oldeventid">原來的event_type</param>
/// <returns>執行結果</returns>
public int Update(PromotionsAmountGiftQuery model, string oldeventid)
{
//實例dao對象
_ptDao = new PromoTicketDao(connStr);
_proCateDao = new ProductCategoryDao(connStr);
_prodCategSetDao = new ProductCategorySetDao(connStr);
_prodpromoDao = new ProdPromoDao(connStr);
int i = 0;
//聲明鏈接數據庫
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sbExSql = new StringBuilder();
try
{
model.Replace4MySQL();
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
//開啟事物
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
StringBuilder Sql = new StringBuilder();
mySqlCmd.CommandText = _prodpromoDao.DeleteProdProm(oldeventid);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//刪除ProdPromo
mySqlCmd.CommandText = _proAllDao.DelPromAll(oldeventid);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//刪除PromAll
//Sql.AppendFormat(_prodpromoDao.DeleteProdProm(model.event_id));
//處理表product_category_set 和 prod_promo
if (model.quanguan == 1)
{
//根據category_id刪除product_category_set表數據
Sql.Clear();
Sql.AppendFormat(_prodCategSetDao.DeleteProdCateSet(model.category_id));
////添加product_category_set數據
ProductCategorySet pcs = new ProductCategorySet();
pcs.Category_Id = model.category_id;
pcs.Product_Id = 999999;
pcs.Brand_Id = 0;
Sql.AppendFormat(_prodCategSetDao.SaveProdCategorySet(pcs));
mySqlCmd.CommandText = Sql.ToString();
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//添加全館商品到PromAll
Sql.Clear();
}
else//非全館
{
//刪除全館商品
ProductCategorySet qgSet = new ProductCategorySet();
qgSet.Category_Id = model.category_id;
qgSet.Product_Id = 999999;//全館商品刪除 id=999999
//根據category_id刪除product_category_set表數據
mySqlCmd.CommandText = _prodCategSetDao.DelProdCateSetByCPID(qgSet);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//刪除全館別商品999999
if (model.url_by == 1)//專區時 add by shuangshuang0420j 20150309 11：25 區分專區和非專區
{
// Sql.Append(_prodpromoDao.DeleteProdProm(oldeventid));//修改前先刪除已有的數據
//選擇brand_id 的時候處理prod_promo 和product_category_set
if (model.brand_id != 0)//當品牌不為空時講該品牌下的所有商品加入set表
{
Sql.Append(_prodCategSetDao.DeleteProdCateSet(model.category_id));
mySqlCmd.CommandText = Sql.ToString();
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
Sql.Clear();
QueryVerifyCondition query = new QueryVerifyCondition();
query.brand_id = Convert.ToUInt32(model.brand_id);
query.site_ids = model.site.ToString();
PromotionsMaintainDao _promoMainDao = new PromotionsMaintainDao(connStr);
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.QueryByProSite(query, out totalCount, 0);
List<uint> categorysetProduct = new List<uint>();
foreach (QueryandVerifyCustom qvcItem in qvcList)
{
if (categorysetProduct.Contains(qvcItem.product_id))
{
continue;
}
categorysetProduct.Add(qvcItem.product_id);
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Product_Id = qvcItem.product_id;
pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
pcsModel.Category_Id = model.category_id;
Sql.Append(_prodCategSetDao.SaveProdCategorySet(pcsModel));
}
if (!string.IsNullOrEmpty(Sql.ToString()))
{
mySqlCmd.CommandText = Sql.ToString();
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
Sql.Clear();
}
}
}
else
{ //非專區時
mySqlCmd.CommandText = _prodCategSetDao.DeleteProdCateSet(model.category_id);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
if (model.product_id != 0)
{
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Product_Id = Convert.ToUInt32(model.product_id);
pcsModel.Brand_Id = _prodDao.Query(new Product { Product_Id = pcsModel.Product_Id }).FirstOrDefault().Brand_Id;
pcsModel.Category_Id = model.category_id;
//Sql.Append(_prodCategSetDao.SaveProdCategorySet(pcsModel));
mySqlCmd.CommandText = _prodCategSetDao.SaveProdCategorySet(pcsModel);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//新增商品關係
}
else if (model.brand_id != 0)
{
QueryVerifyCondition query = new QueryVerifyCondition();
query.brand_id = Convert.ToUInt32(model.brand_id);
query.site_ids = model.site;
query.combination = 1;
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.QueryByProSite(query, out totalCount, 0);
foreach (QueryandVerifyCustom qvcItem in qvcList)
{
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Product_Id = qvcItem.product_id;
pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
pcsModel.Category_Id = model.category_id;
// arryList.Add(_prodCategSet.SaveProdCategorySet(pcsModel));
mySqlCmd.CommandText = _prodCategSetDao.SaveProdCategorySet(pcsModel);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//新增商品關係
}
}
else if (model.class_id != 0)
{
List<VendorBrand> brandIDs = _vendorBrandDao.GetClassBrandList(new VendorBrand { }, (uint)model.class_id);
foreach (VendorBrand item in brandIDs)
{
QueryVerifyCondition query = new QueryVerifyCondition();
query.brand_id = item.Brand_Id;
query.site_ids = model.site;
query.combination = 1;
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.QueryByProSite(query, out totalCount, 0);
foreach (QueryandVerifyCustom qvcItem in qvcList)
{
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Product_Id = qvcItem.product_id;
pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
pcsModel.Category_Id = model.category_id;
//arryList.Add(_prodCategSet.SaveProdCategorySet(pcsModel));
mySqlCmd.CommandText = _prodCategSetDao.SaveProdCategorySet(pcsModel);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//新增商品關係
}
}
}
}
}
//新增表promo_all數據
// Sql.AppendFormat(_proAllDao.DelPromAll(model.event_id));
Sql.Clear();
PromoAll pamodel = new PromoAll();
pamodel.event_id = model.event_id;
pamodel.product_id = model.product_id;
pamodel.class_id = model.class_id;
pamodel.brand_id = model.brand_id;
pamodel.event_type = model.event_type;
pamodel.category_id = Convert.ToInt32(model.category_id);
pamodel.startTime = model.startdate;
pamodel.end = model.enddate;
pamodel.status = model.status;
pamodel.kuser = model.kuser;
pamodel.kdate = model.created;
pamodel.muser = model.muser;
pamodel.mdate = model.modified;
Sql.AppendFormat(_proAllDao.SavePromAll(pamodel));
//更新product_category表數據
ProductCategory pcmodel = _proCateDao.GetModelById(Convert.ToUInt32(model.category_id));
pcmodel.category_id = Convert.ToUInt32(model.category_id);
pcmodel.banner_image = model.banner_image;
pcmodel.category_link_url = model.category_link_url;
pcmodel.category_display = Convert.ToUInt32(model.status);
Sql.AppendFormat(_proCateDao.UpdateProdCate(pcmodel));
//判斷送禮類型是否是機會,若是的話 修改編輯表promo_ticket
if (model.gift_type == 2)
{ //新增數據到promo_ticket
if (model.ticket_id == 0)
{
PromoTicket pt = new PromoTicket();
pt.ticket_name = model.name;//機會name
pt.event_id = model.event_id;
pt.event_type = model.event_type;
pt.active_now = model.active_now;
pt.use_start = model.use_start;
pt.use_end = model.use_end;
pt.kuser = model.kuser;
pt.kdate = model.created;
pt.muser = model.muser;
pt.mdate = model.modified;
pt.valid_interval = model.valid_interval;
pt.status = model.status;
model.ticket_id = _ptDao.Save(pt);
model.ticket_name = pt.ticket_name;
}
//更新表數據
else
{
PromoTicket pt = _ptDao.Query(model.ticket_id);
pt.kuser = model.kuser;
pt.kdate = model.created;
pt.muser = model.muser;
pt.mdate = model.modified;
pt.ticket_name = model.ticket_name;//機會name
pt.event_id = model.event_id;
pt.event_type = model.event_type;
pt.active_now = model.active_now;
pt.use_start = model.use_start;
pt.use_end = model.use_end;
pt.valid_interval = model.valid_interval;
pt.status = model.status;
Sql.AppendFormat(_ptDao.UpdateSql(pt));
}
}
else if (model.ticket_id != 0)
{
Sql.AppendFormat(_ptDao.DeleteSql(model.ticket_id));
}
if (model.gift_type == 3 || model.gift_type == 4)//當贈送購物金或抵用券時產生gift_id
{
PromotionsAmountGiftCustom promGiftCustom = new PromotionsAmountGiftCustom();
promGiftCustom.product_id = 5669;
promGiftCustom.spec_type = 1;
promGiftCustom.spec_name = model.name;
promGiftCustom.Item_Alarm = 5;
promGiftCustom.Item_Stock = 9999;
mySqlCmd.CommandText = SaveProSpec(promGiftCustom);
uint specId = Convert.ToUInt32(mySqlCmd.ExecuteScalar());
promGiftCustom.Spec_Id_1 = specId;
mySqlCmd.CommandText = SaveProItem(promGiftCustom);
model.gift_id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
}
//更新主表數據
Sql.AppendFormat(UpdatePromoGift(model));
mySqlCmd.CommandText = Sql.ToString();
sbExSql.Append(mySqlCmd.CommandText);
i += mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{//事物回滾
mySqlCmd.Transaction.Rollback();
throw new Exception("PromotionsAmountGiftDao-->Update-->" + ex.Message + sbExSql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return i;
}
public int Update(PromoAdditionalPrice m, PromoAdditionalPriceQuery mq)
{
int id = 0;
StringBuilder sb = new StringBuilder();
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
sb.Append(_prodPromoDao.DeleteProdProm(mq.event_id));
sb.AppendFormat(@"UPDATE promo_additional_price SET condition_id='{1}',group_id='{2}',`start`='{3}',`end`='{4}',modified='{5}',deliver_type='{6}',device='{7}',muser='******',website='{9}',event_name='{10}',event_desc='{11}',fixed_price='{12}',buy_limit='{13}',url_by='{14}',discount='{15}',active={16} where id={0} ; ", m.id, m.condition_id, m.group_id, CommonFunction.DateTimeToString(m.starts), CommonFunction.DateTimeToString(m.end), CommonFunction.DateTimeToString(m.modified), m.deliver_type, m.device, m.muser, m.website, m.event_name, m.event_desc, m.fixed_price, m.buy_limit, m.url_by, m.discount, m.active);
#region 操作修改ProductCategory
ProductCategoryDao _categoryDao = new ProductCategoryDao(connStr);
ProductCategory pcmodel = _categoryDao.GetModelById(Convert.ToUInt32(m.category_id));
pcmodel.category_id = Convert.ToUInt32(m.category_id);
pcmodel.banner_image = mq.banner_image;
pcmodel.category_link_url = mq.category_link_url;
pcmodel.category_name = m.event_name;
pcmodel.category_display = Convert.ToUInt32(m.status);
pcmodel.category_ipfrom = mq.category_ipfrom;
pcmodel.category_updatedate = (uint)BLL.gigade.Common.CommonFunction.GetPHPTime();
sb.Append(_proCateDao.UpdateProdCate(pcmodel));
#endregion
mySqlCmd.CommandText = sb.ToString();
id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("PromoAdditionalPriceDao-->Update-->" + ex.Message + mySqlCmd.CommandText.ToString(), ex);
}
finally
{
mySqlConn.Close();
}
return id;
}