/// <summary>
/// 保存新增promo_all表的數據
/// </summary>
/// <param name="model">PromoAll的Model</param>
/// <returns></returns>
public int Save(PromoAll model)
{
model.Replace4MySQL();
StringBuilder sql = new StringBuilder();
sql.Append(@" insert into promo_all (");
sql.Append(" rid, event_id,event_type,brand_id,class_id,category_id,product_id,start,end,kuser,kdate,muser,mdate,status) ");
sql.AppendFormat(" values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}');", model.rid, model.event_id, model.event_type, model.brand_id, model.class_id, model.category_id, model.product_id, CommonFunction.DateTimeToString(model.startTime), Common.CommonFunction.DateTimeToString(model.end), model.kuser, Common.CommonFunction.DateTimeToString(model.kdate), model.muser, Common.CommonFunction.DateTimeToString(model.mdate), model.status);
try
{
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PromoAllDao-->Save-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 移除幾件幾元或幾件幾折
/// </summary>
/// <returns></returns>
public HttpResponseBase Deletezhe()
{
string jsonStr = String.Empty;
PromotionsAmountDiscount query = new PromotionsAmountDiscount();
ProductCategory pmodel = new ProductCategory();
ProductCategorySet pmsmodel = new ProductCategorySet();
ProductCategorySet querypcs = new ProductCategorySet();
PromoDiscount querypd = new PromoDiscount();
PromoAll pamodel = new PromoAll();
ProdPromo ppmodel = new ProdPromo();
querypcs.Category_Id = pmodel.category_id;
List<ProductCategorySet> smodel = _produCateSetMgr.Query(querypcs);
if (!String.IsNullOrEmpty(Request.Params["rowid"]))
{
try
{
foreach (string rid in Request.Params["rowid"].ToString().Split('|'))//批次移除
{
if (!string.IsNullOrEmpty(rid))
{
//刪除peomotion_amount_discount
//根據id 獲取整個model
query = _promoAmountDiscountMgr.GetModelById(Convert.ToInt32(rid));
var eventid = CommonFunction.GetEventId(query.event_type, query.id.ToString());
if (_promoAmountDiscountMgr.Delete(query, eventid) > 0)
{
jsonStr = "{success:true}";
}
else
{
jsonStr = "{success:false}";
}
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
jsonStr = "{success:false}";
}
}
this.Response.Clear();
this.Response.Write(jsonStr);
this.Response.End();
return this.Response;
}
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 SaveTwo(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}',price='{10}',discount='{11}',vendor_coverage='{12}',status=1 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.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_display = Convert.ToUInt32(model.status);
pcmodel.category_ipfrom = PPquery.category_ipfrom;
sb.Append(_proCateDao.UpdateProdCate(pcmodel));
#endregion
#region 操作PromoAll
PromoAll pamodel = new PromoAll();
pamodel.event_id = PPquery.event_id;
pamodel.event_type = model.event_type;
pamodel.category_id = Convert.ToInt32(model.category_id);
pamodel.startTime = model.starts;
pamodel.end = model.end;
pamodel.status = model.status;
pamodel.kuser = model.muser;
pamodel.kdate = DateTime.Now;
pamodel.muser = model.muser;
pamodel.mdate = pamodel.kdate;
sb.Append(_promoAllDao.SavePromAll(pamodel));
#endregion
//查詢出商品信息后插入prod_promo表
//mySqlCmd.CommandText = _prodPromoDao.DeleteProdProm(PPquery.event_id);
//mySqlCmd.ExecuteScalar();
#region MyRegion
//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.kuser;
// ppmodel.kdate = model.created;
// ppmodel.muser = ppmodel.kuser;
// ppmodel.mdate = ppmodel.kdate;
// 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.kuser;
// ppmodel.kdate = model.created;
// ppmodel.muser = ppmodel.kuser;
// ppmodel.mdate = ppmodel.kdate;
// ppmodel.status = model.status;
// sb.Append(_prodPromoDao.SaveProdProm(ppmodel));
// }
// }
//}
#endregion
mySqlCmd.CommandText = sb.ToString();
id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("PromoPairDao-->SaveTwo-->" + ex.Message, ex);
}
finally
{
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;
}
/// <summary>
/// 保存更新promo_all的數據的sql語句
/// </summary>
/// <param name="model"></param>
/// <returns>保存更新promo_all的數據的sql語句</returns>
public string UpdatePromAll(PromoAll model)
{
model.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.Append("set sql_safe_updates = 0;");
sql.Append(@"update promo_all set ");
sql.AppendFormat(" event_type='{0}',brand_id='{1}',class_id='{2}', category_id='{3}',product_id='{4}',",
model.event_type, model.brand_id, model.class_id, model.category_id, model.product_id);
sql.AppendFormat(" start='{0}',end='{1}',muser='******',mdate='{3}',status='{4}'",
CommonFunction.DateTimeToString(model.startTime), Common.CommonFunction.DateTimeToString(model.end),
model.muser,
Common.CommonFunction.DateTimeToString(model.mdate), model.status);
sql.AppendFormat("where event_id='{0}';", model.event_id);
sql.Append("set sql_safe_updates = 1;");
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("PromoAllDao-->UpdatePromAll-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 刪除promo_all表的數據
/// </summary>
/// <param name="model">promo_all的Model</param>
/// <returns></returns>
public int Delete(PromoAll model)
{
StringBuilder sql = new StringBuilder();
sql.Append("set sql_safe_updates = 0;");
sql.AppendFormat("update promo_all set status=1 where event_id='{0}'", model.event_id);
sql.Append("set sql_safe_updates = 1;");
try
{
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PromoAllDao-->Delete-->" + ex.Message + sql.ToString(), ex);
}
}
public bool ReUpdateDiscount(PromotionsAmountDiscountCustom model, string oldEventId)
{
ArrayList arryList = new ArrayList();
try
{
arryList.Add(_prodpromoDao.DeleteProdProm(oldEventId));//修改時先刪除原有的prodprom
if (model.product_id == 999999)
{
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Brand_Id = 0;
pcsModel.Category_Id = model.category_id;
pcsModel.Product_Id = 999999;
arryList.Add(_prodCategSet.DeleteProdCateSet(pcsModel.Category_Id));
arryList.Add(_prodCategSet.SaveProdCategorySet(pcsModel));
}
else
{
if (model.url_by == 1)
{
if (model.brand_id != 0)//當品牌不為空時講該品牌下的所有商品加入set表
{
arryList.Add(_prodCategSet.DeleteProdCateSet(model.category_id));//brand_id不為0時先刪除set表中數據，在新增
QueryVerifyCondition query = new QueryVerifyCondition();
query.brand_id = Convert.ToUInt32(model.brand_id);
query.site_ids = model.site;
query.combination = 1;
query.IsPage = false;
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.GetProList(query, out totalCount);
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));
}
}
}
else//非專區
{
if (model.event_type == "M1")
{
arryList.Add(_prodCategSet.DeleteProdCateSet(model.category_id));//幾件幾折的非專區時先刪除set表中數據，在新增
}
if (model.product_id != 0)
{
arryList.Add(_prodCategSet.DeleteProdCateSet(model.category_id));//非專區時先刪除set表中數據，在新增
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;
arryList.Add(_prodCategSet.SaveProdCategorySet(pcsModel));
}
else if (model.brand_id != 0)
{
arryList.Add(_prodCategSet.DeleteProdCateSet(model.category_id));//非專區時先刪除set表中數據，在新增
QueryVerifyCondition query = new QueryVerifyCondition();
query.brand_id = Convert.ToUInt32(model.brand_id);
query.site_ids = model.site;
query.combination = 1;
int totalCount = 0;
query.IsPage = false;
List<QueryandVerifyCustom> qvcList = _promoMainDao.GetProList(query, out totalCount);
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));
}
}
else if (model.class_id != 0)
{
arryList.Add(_prodCategSet.DeleteProdCateSet(model.category_id));//非專區時先刪除set表中數據，在新增
VendorBrandSetDao _vbsDao = new VendorBrandSetDao(connStr);
List<VendorBrandSet> brandIDs = _vbsDao.Query(new VendorBrandSet { class_id = (uint)model.class_id });
if (brandIDs.Count > 0)
{
QueryVerifyCondition query = new QueryVerifyCondition();
foreach (VendorBrandSet item in brandIDs)
{
query.brand_ids += item.brand_id + ",";
}
query.brand_ids = query.brand_ids.TrimEnd(',');
query.site_ids = model.site;
query.combination = 1;
query.IsPage = false;
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.GetProList(query, out totalCount);
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));
}
}
}
}
}
if (model.event_id != "")
{
arryList.Add(_proAllDao.DelPromAll(oldEventId));
}
PromoAll pamodel = new PromoAll();
pamodel.event_id = model.event_id;
pamodel.event_type = model.event_type;
pamodel.brand_id = model.brand_id;
pamodel.class_id = model.class_id;
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.status = model.status;
pamodel.product_id = model.product_id;
pamodel.class_id = model.class_id;
pamodel.brand_id = model.brand_id;
arryList.Add(_proAllDao.SavePromAll(pamodel));
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);
pcmodel.category_name = model.name;
pcmodel.category_updatedate = (uint)BLL.gigade.Common.CommonFunction.GetPHPTime(model.modified.ToString());
pcmodel.category_father_id = model.category_father_id;
arryList.Add(_proCateDao.UpdateProdCate(pcmodel));
arryList.Add(_padDao.UpdatePromoAmountDis(model));
return _mysqlDao.ExcuteSqls(arryList);
}
catch (Exception ex)
{
throw new Exception("PromotionsAmountDiscountDao-->ReUpdate-->" + ex.Message, ex);
}
}
public bool ReSaveDiscount(PromotionsAmountDiscountCustom model)
{
ArrayList arryList = new ArrayList();
try
{
if (model.product_id == 999999)
{
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Brand_Id = 0;
pcsModel.Category_Id = model.category_id;
pcsModel.Product_Id = 999999;
arryList.Add(_prodCategSet.SaveProdCategorySet(pcsModel));
}
else
{
//刪除全館商品
ProductCategorySet qgSet = new ProductCategorySet();
qgSet.Category_Id = model.category_id;
qgSet.Product_Id = 999999;//全館商品刪除 id=999999
//根據category_id刪除product_category_set表數據
if (model.url_by == 1)
{
if (model.brand_id != 0)//專區時當品牌不為空時講該品牌下的所有商品加入set表
{
arryList.Add(_prodCategSet.DelProdCateSetByCPID(qgSet));
QueryVerifyCondition query = new QueryVerifyCondition();
query.brand_id = Convert.ToUInt32(model.brand_id);
query.site_ids = model.site;
query.combination = 1;
query.IsPage = false;
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.GetProList(query, out totalCount);
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));
}
}
}
else//非專區時
{
if (model.event_type == "M1")
{
arryList.Add(_prodCategSet.DelProdCateSetByCPID(qgSet));
}
if (model.product_id != 0)
{
arryList.Add(_prodCategSet.DelProdCateSetByCPID(qgSet));
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;
arryList.Add(_prodCategSet.SaveProdCategorySet(pcsModel));
}
else if (model.brand_id != 0)
{
arryList.Add(_prodCategSet.DelProdCateSetByCPID(qgSet));
QueryVerifyCondition query = new QueryVerifyCondition();
query.brand_id = Convert.ToUInt32(model.brand_id);
query.site_ids = model.site;
query.combination = 1;
query.IsPage = false;
int totalCount = 0;//篩選出該品牌下符合條件的商品
List<QueryandVerifyCustom> qvcList = _promoMainDao.GetProList(query, out totalCount);
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));
}
}
else if (model.class_id != 0)
{
arryList.Add(_prodCategSet.DelProdCateSetByCPID(qgSet));
VendorBrandSetDao _vbsDao = new VendorBrandSetDao(connStr);
List<VendorBrandSet> brandIDs = _vbsDao.Query(new VendorBrandSet { class_id = (uint)model.class_id });
if (brandIDs.Count > 0)
{
QueryVerifyCondition query = new QueryVerifyCondition();
foreach (VendorBrandSet item in brandIDs)
{
query.brand_ids += item.brand_id + ",";
}
query.brand_ids = query.brand_ids.TrimEnd(',');
query.site_ids = model.site;
query.combination = 1;
query.IsPage = false;
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.GetProList(query, out totalCount);
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));
}
}
}
}
}
PromoAll pamodel = new PromoAll();
pamodel.event_id = model.event_id;
pamodel.event_type = model.event_type;
pamodel.brand_id = model.brand_id;
pamodel.class_id = model.class_id;
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.kuser;
pamodel.mdate = model.created;
pamodel.product_id = model.product_id;
pamodel.class_id = model.class_id;
pamodel.brand_id = model.brand_id;
arryList.Add(_proAllDao.SavePromAll(pamodel));
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);
pcmodel.category_name = model.name;
pcmodel.category_father_id = model.category_father_id;
arryList.Add(_proCateDao.UpdateProdCate(pcmodel));
arryList.Add(_padDao.UpdatePromoAmountDis(model));
return _mysqlDao.ExcuteSqls(arryList);
}
catch (Exception ex)
{
throw new Exception("PromotionsAmountDiscountMgr-->ReSaveDiscount-->" + ex.Message, ex);
}
}
public int InsertSecond(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.AppendFormat(@"UPDATE promo_additional_price SET condition_id='{1}',group_id='{2}',`start`='{3}',`end`='{4}',modified='{5}',deliver_type='{6}',device='{7}',muser='******',status='{9}',active='{10}',fixed_price='{11}',buy_limit='{12}',website='{13}',url_by='{14}',kuser='******',created='{16}',discount='{17}' 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.status, Convert.ToInt32(m.active), m.fixed_price, m.buy_limit, m.website, m.url_by, m.kuser, CommonFunction.DateTimeToString(m.created), m.discount);
#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_display = Convert.ToUInt32(m.status);
pcmodel.category_ipfrom = mq.category_ipfrom;
sb.Append(_proCateDao.UpdateProdCate(pcmodel));
#endregion
#region 操作PromoAll
PromoAll pamodel = new PromoAll();
pamodel.event_id = mq.event_id;
pamodel.event_type = m.event_type;
pamodel.category_id = Convert.ToInt32(m.category_id);
pamodel.startTime = m.starts;
pamodel.end = m.end;
pamodel.status = m.status;
pamodel.kuser = m.muser;
pamodel.kdate = DateTime.Now;
pamodel.muser = m.muser;
pamodel.mdate = pamodel.kdate;
sb.Append(_promoAllDao.SavePromAll(pamodel));
#endregion
mySqlCmd.CommandText = sb.ToString();
id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("PromoAdditionalPriceDao-->InsertSecond-->" + ex.Message, ex);
}
finally
{
mySqlConn.Close();
}
return id;
}