/// <summary>
/// 更新主表數據
/// </summary>
/// <param name="model">PromotionsAmountGiftQuery對象</param>
/// <returns>更新主表數據的sql語句</returns>
public string UpdatePromoGift(PromotionsAmountGiftQuery model)
{
model.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("update promotions_amount_gift set ");
sql.AppendFormat("name='{0}',group_id='{1}',class_id='{2}',brand_id='{3}',category_id='{4}',", model.name, model.group_id, model.class_id, model.brand_id, model.category_id);
sql.AppendFormat("product_id='{0}', type='{1}', amount='{2}' , quantity='{3}' , `repeat` ='{4}' , gift_id='{5}' , ", model.product_id, model.type, model.amount, model.quantity, model.repeat ? '1' : '0', model.gift_id);
sql.AppendFormat("deduct_welfare='{0}' , bonus_type='{1}' , mailer_id='{2}' , start='{3}' , end='{4}' , created='{5}' , ", model.deduct_welfare, model.bonus_type, model.mailer_id, CommonFunction.DateTimeToString(model.startdate), CommonFunction.DateTimeToString(model.enddate), CommonFunction.DateTimeToString(model.created));
sql.AppendFormat(" modified='{0}' , active='{1}' , event_desc='{2}' , event_type='{3}' , condition_id='{4}' , device='{5}' , ", CommonFunction.DateTimeToString(model.modified), model.active, model.event_desc, model.event_type, model.condition_id, model.device);
sql.AppendFormat(" payment_code='{0}' , gift_type='{1}' , ticket_id='{2}' , ticket_name='{3}' , count_by='{4}' , number='{5}' , num_limit='{6}' , active_now='{7}' , valid_interval='{8}' , ", model.payment_code, model.gift_type, model.ticket_id, model.ticket_name, model.count_by, model.number, model.num_limit, model.active_now, model.valid_interval);
sql.AppendFormat("use_start='{0}' , use_end ='{1}', muser='******' , site='{3}', url_by='{4}' , status='{5}',vendor_coverage='{6}',gift_product_number='{7}'", CommonFunction.DateTimeToString(model.use_start), CommonFunction.DateTimeToString(model.use_end), model.muser, model.site, model.url_by, model.status, model.vendor_coverage, model.gift_product_number);
sql.AppendFormat(" where id='{0}';", model.id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("PromotionsAmountGiftDao-->UpdatePromoGift-->" + ex.Message + sql.ToString(), 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;
}