public MsgResultModel BatchDeleteCoupon(string oids, string coupons) { MsgResultModel _MsgResultModel = new MsgResultModel(); try { return(handler.BatchDeleteCoupon(oids, coupons)); } catch (Exception ex) { logger.Log(Level.Error, ex.ToString()); _MsgResultModel.State = 0; _MsgResultModel.Message = ex.ToString(); return(_MsgResultModel); } }
public JsonResult BatchAddCouponNew(string oids = "", string rulePKIDs = "", decimal grossProfit = 0) { MsgResultModel result = new MsgResultModel() { State = 1 }; var oidList = GetIntList(oids); var rulePkIdList = GetIntList(rulePKIDs); var unavaiableProducts = new List <QueryProductsModel>(); if (oidList.Any() && rulePkIdList.Any()) { var productConponConfig = _ProductLibraryConfigManage.GetProductCouponConfigByOids(oidList); var products = _ProductLibraryConfigManage.QueryProducts(oidList); var unavaiableOids = new List <int>(); var isCanInsert = false; var ruleList = new List <GetPCodeModel>(); var hasExistsCouponOids = new List <int>(); foreach (var rulePkid in rulePkIdList) { var hasExistCurrentCouponOids = productConponConfig.Where(t => GetIntList(t.CouponIds). Contains(rulePkid)).Select(t => t.Oid); var currentoidList = oidList.Where(t => !hasExistCurrentCouponOids.Contains(t) && !hasExistsCouponOids.Contains(t)).ToList(); if (currentoidList.Any()) { isCanInsert = true; var currentprdocuts = products.Where(t => currentoidList.Contains(t.Oid)); var rule = _ProductLibraryConfigManage.GetGeCouponRulesByRulePkid(rulePkid); if (rule != null) { ruleList.Add(rule); var oidListFiltered = _ProductLibraryConfigManage.GetAvailableCouponProducts(currentprdocuts, rule, grossProfit).Select(t => t.Oid); if (oidListFiltered.Any()) { var oidsFiltered = string.Join(",", oidListFiltered); var addCouponResult = _ProductLibraryConfigManage.BatchAddCoupon(oidsFiltered, rulePkid.ToString(), 0); if (addCouponResult.State == 0) { result.Message = "插券失败"; result.State = 0; break; } else { hasExistsCouponOids.AddRange(oidListFiltered); } } } } } unavaiableProducts = _ProductLibraryConfigManage.GetUnavailableCouponProducts(products, ruleList, grossProfit).ToList(); _ProductLibraryConfigManage.RefreshCache(oids); if (!isCanInsert) { result.Message = "所有产品已经存在该优惠券"; result.State = 0; } } return(Json(new { result = result, unavaiableProducts = unavaiableProducts }, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 批量删除产品 /// </summary> public static MsgResultModel BatchDeleteCoupon(SqlConnection sqlcon, string oids, string coupon) { MsgResultModel _MsgResultModel = new MsgResultModel(); var oidsArr = oids.Split(','); bool compareState = false; if (oidsArr.Length > 0) { string queryProductsSQL = " SELECT DISTINCT Oid FROM Configuration.[dbo].[SE_ProductLibraryConfig] WITH(NOLOCK) WHERE Oid IN (SELECT * FROM [dbo].[SplitString](@oids,',',1))"; DataTable productsTable = SqlHelper.ExecuteDataTable(sqlcon, CommandType.Text, queryProductsSQL, new SqlParameter("@oids", oids)) ?? new DataTable(); //sql 替换 ,号分割的数据 //string sql = @" // DECLARE @coupon VARCHAR(1000) // DECLARE @result VARCHAR(1000) // SET @coupon = ',' + ( SELECT CouponIds // FROM Configuration.[dbo].[SE_ProductLibraryConfig] // WHERE Oid = @Oid // ) + ',' // IF @coupon = @couponnew // BEGIN // UPDATE Configuration.[dbo].[SE_ProductLibraryConfig] // SET CouponIds = '' // WHERE Oid = @Oid // END // IF CHARINDEX(@couponnew, @coupon) > 0 // BEGIN // BEGIN // DECLARE @NewIndex NVARCHAR(200) // DECLARE @NewIndex1 NVARCHAR(200) // SET @NewIndex = SUBSTRING(@coupon, 0, // CHARINDEX(@couponnew, @coupon)) // SET @NewIndex1 = SUBSTRING(@coupon, // CHARINDEX(@couponnew, @coupon) // + LEN(@couponnew), LEN(@coupon)) // IF ( LEN(@NewIndex) > 0 // AND LEN(@NewIndex1) > 0 // ) // BEGIN // SET @result = SUBSTRING(@NewIndex, 2, LEN(@NewIndex)) // + ',' + SUBSTRING(@NewIndex1, 1, LEN(@NewIndex1) - 1) // END // IF ( LEN(@NewIndex) <= 0 // AND LEN(@NewIndex1) > 0 // ) // BEGIN // SET @result = SUBSTRING(@NewIndex1, 1, LEN(@NewIndex1) - 1) // END // IF ( LEN(@NewIndex) > 0 // AND LEN(@NewIndex1) < = 0 // ) // BEGIN // SET @result = SUBSTRING(@NewIndex, 2, LEN(@NewIndex)) // END // UPDATE Configuration.[dbo].[SE_ProductLibraryConfig] // SET CouponIds = @result // WHERE Oid = @Oid // END // END // "; string sql = "UPDATE Configuration.[dbo].[SE_ProductLibraryConfig] SET CouponIds=@CouponIds WHERE Oid=@Oid"; using (SqlTransaction tran = sqlcon.BeginTransaction()) { try { for (int i = 0; i < oidsArr.Length; i++) { for (int j = 0; j < productsTable.Rows.Count; j++) { if (oidsArr[i].Equals(productsTable.Rows[j]["Oid"].ToString())) { compareState = true; break; } } if (compareState) { string sqlSelect = "SELECT CouponIds FROM Configuration.[dbo].[SE_ProductLibraryConfig] WITH(NOLOCK) WHERE Oid =@oid"; object coupons = SqlHelper.ExecuteScalar(tran, CommandType.Text, sqlSelect, new SqlParameter("@oid", oidsArr[i])); if (coupons != DBNull.Value && coupons != null) { string couponNew = string.Empty; var couponArr = coupons.ToString()?.Split(','); foreach (var item in couponArr) { if (item.Trim() != coupon.Trim()) { couponNew += item.Trim() + ","; } } if (!string.IsNullOrWhiteSpace(couponNew)) { couponNew = couponNew.Substring(0, couponNew.Length - 1); } var prams = new SqlParameter[] { new SqlParameter("@CouponIds", couponNew), new SqlParameter("@Oid", oidsArr[i]) }; SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sql, prams); } } } _MsgResultModel.State = 1; tran.Commit(); } catch (Exception ex) { _MsgResultModel.State = 0; _MsgResultModel.Message = ex.ToString(); tran.Rollback(); } } } return(_MsgResultModel); }
public static MsgResultModel BatchDeleteCoupon(SqlConnection sqlcon, string oids, string coupons, int isBatch) { MsgResultModel _MsgResultModel = new MsgResultModel(); var oidsArr = oids.Split(','); bool compareState = false; if (oidsArr.Length > 0) { string queryProductsSQL = "SELECT DISTINCT Oid FROM Configuration.[dbo].[SE_ProductLibraryConfig] WITH(NOLOCK) WHERE Oid IN(" + oids + ")"; DataTable productsTable = SqlHelper.ExecuteDataTable(sqlcon, CommandType.Text, queryProductsSQL, null) ?? new DataTable(); using (SqlTransaction tran = sqlcon.BeginTransaction()) { try { List <SqlParameter> paramsList = new List <SqlParameter>(); StringBuilder sbUpdate = new StringBuilder(); StringBuilder sbInsert = new StringBuilder(); string execSQL = "", updateSQL = isBatch == 0 ? " UPDATE Configuration.[dbo].[SE_ProductLibraryConfig] SET CouponIds = CouponIds + ',' + {0} WHERE Oid = {1} " : " UPDATE Configuration.[dbo].[SE_ProductLibraryConfig] SET CouponIds = {0} WHERE Oid = {1} ", insertSQL = " INSERT INTO Configuration.[dbo].[SE_ProductLibraryConfig](Oid,CouponIds,State,CreateTime) VALUES({0},{1},{2},{3}) "; for (int i = 0; i < oidsArr.Length; i++) { for (int j = 0; j < productsTable.Rows.Count; j++) { if (oidsArr[i].Equals(productsTable.Rows[j]["Oid"].ToString())) { compareState = true; break; } } if (!compareState) { sbInsert.AppendFormat(insertSQL, "@Oid" + i, "@CouponIds" + i, "@State" + i, "@CreateTime" + i); paramsList.Add(new SqlParameter("@Oid" + i, oidsArr[i])); paramsList.Add(new SqlParameter("@CouponIds" + i, coupons)); paramsList.Add(new SqlParameter("@State" + i, 1)); paramsList.Add(new SqlParameter("@CreateTime" + i, DateTime.Now)); } else { sbUpdate.AppendFormat(updateSQL, "@CouponIds" + i, "@Oid" + i); paramsList.Add(new SqlParameter("@Oid" + i, oidsArr[i])); paramsList.Add(new SqlParameter("@CouponIds" + i, coupons)); } compareState = false; } execSQL = sbInsert.ToString() + sbUpdate.ToString(); if (SqlHelper.ExecuteNonQuery(tran, CommandType.Text, execSQL.ToString(), paramsList.ToArray()) > 0) { _MsgResultModel.State = 1; } else { _MsgResultModel.State = 0; } tran.Commit(); } catch (Exception ex) { _MsgResultModel.State = 0; _MsgResultModel.Message = ex.ToString(); tran.Rollback(); } } } return(_MsgResultModel); }
/// <summary> /// 批量修改或添加产品 /// </summary> public static MsgResultModel BatchAddCoupon(SqlConnection sqlcon, string oids, string coupons, int isBatch) { MsgResultModel _MsgResultModel = new MsgResultModel(); var oidsArr = oids.Split(','); bool compareState = false; if (oidsArr.Length > 0) { string queryProductsSQL = " SELECT DISTINCT Oid FROM Configuration.[dbo].[SE_ProductLibraryConfig] WITH(NOLOCK) WHERE Oid IN (SELECT * FROM [dbo].[SplitString](@oids,',',1))"; DataTable productsTable = SqlHelper.ExecuteDataTable(sqlcon, CommandType.Text, queryProductsSQL, new SqlParameter("@oids", oids)) ?? new DataTable(); using (SqlTransaction tran = sqlcon.BeginTransaction()) { try { List <SqlParameter> paramsList = new List <SqlParameter>(); StringBuilder sbUpdate = new StringBuilder(); StringBuilder sbInsert = new StringBuilder(); string updateSQL = ""; updateSQL = isBatch == 0 ? " UPDATE Configuration.[dbo].[SE_ProductLibraryConfig] SET CouponIds = CouponIds + ',' + @CouponIds WHERE Oid IN (SELECT * FROM [dbo].[SplitString](@OidStr,',',1))" : " UPDATE Configuration.[dbo].[SE_ProductLibraryConfig] SET CouponIds = @CouponIds WHERE Oid IN (SELECT * FROM [dbo].[SplitString](@OidStr,',',1))"; for (int i = 0; i < oidsArr.Length; i++) { for (int j = 0; j < productsTable.Rows.Count; j++) { if (oidsArr[i].Equals(productsTable.Rows[j]["Oid"].ToString())) { compareState = true; break; } } if (!compareState) { sbInsert.Append(oidsArr[i]); sbInsert.Append(','); } else { sbUpdate.Append(oidsArr[i]); sbUpdate.Append(','); } compareState = false; } var insertOids = sbInsert.ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Distinct().ToList(); sbInsert.Clear(); if (insertOids.Any()) { MultBatchAddCoupon(sqlcon, tran, insertOids, coupons, isBatch); _MsgResultModel.State = 1; } var updateOids = sbUpdate.ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Distinct().ToList(); sbUpdate.Clear(); if (updateOids.Any()) { paramsList.Add(new SqlParameter("@OidStr", string.Join(",", updateOids))); paramsList.Add(new SqlParameter("@CouponIds", coupons)); if (SqlHelper.ExecuteNonQuery(tran, CommandType.Text, updateSQL.ToString(), paramsList.ToArray()) > 0) { _MsgResultModel.State = 1; } else { _MsgResultModel.State = 0 | _MsgResultModel.State; } } tran.Commit(); } catch (Exception ex) { _MsgResultModel.State = 0; _MsgResultModel.Message = ex.ToString(); tran.Rollback(); } } } return(_MsgResultModel); }