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