public string SaveProSpec(PromotionsAmountGiftCustom prog)
{
prog.Replace4MySQL();
try
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(@" update serial set serial_value= serial_value+1 where serial_id=18;");
sbSql.Append(@" insert into product_spec (");
sbSql.Append(" spec_id, product_id,spec_type,spec_name) ");
sbSql.AppendFormat(" values ((select serial_value from serial where serial_id=18),{0},{1},'{2}');select serial_value from serial where serial_id=18;", prog.product_id, prog.spec_type, prog.spec_name);
//sbSql.Append(@" insert into product_spec (");
//sbSql.Append(" spec_id, product_id,spec_type,spec_name) ");
//sbSql.AppendFormat(" values ((select max(ps.spec_id) from product_spec ps)+1,{0},{1},'{2}');select max(spec_id) from product_spec;", prog.product_id, prog.spec_type, prog.spec_name);
return sbSql.ToString();
}
catch (Exception ex)
{
throw new Exception("PromotionsAmountGiftDao-->SaveProSpec-->" + ex.Message, ex);
}
}
public string SaveProItem(PromotionsAmountGiftCustom prog)
{
prog.Replace4MySQL();
try
{
StringBuilder piSql = new StringBuilder();
piSql.Append(@"update serial set serial_value= serial_value+1 where serial_id=19;");//更改serial表中的product_item 主鍵
piSql.Append(@"insert into product_item (`item_id`,`product_id`,`spec_id_1`,`Item_Stock`,`Item_Alarm`)values(");
piSql.AppendFormat("(select serial_value from serial where serial_id=19),{0},{1},{2},{3});select serial_value from serial where serial_id=19;", prog.product_id, prog.Spec_Id_1, prog.Item_Stock, prog.Item_Alarm);
//piSql.Append(@"insert into product_item (`item_id`,`product_id`,`spec_id_1`,`Item_Stock`,`Item_Alarm`)values(");
//piSql.AppendFormat("(select max(pi.item_id) from product_item pi)+1,{0},{1},{2},{3});select max(item_id) from product_item;", prog.product_id, prog.Spec_Id_1, prog.Item_Stock, prog.Item_Alarm);
return piSql.ToString();
}
catch (Exception ex)
{
throw new Exception("PromotionsAmountGiftDao-->SaveProItem-->" + ex.Message, ex);
}
}
/// <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;
}
