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