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