public void InsertUserPlans(string userId, LotteryType lotteryType, UserBasicNorm userBasicNorm, IList <int> planIds)
{
string sqlStr1 = "INSERT INTO [dbo].[LotteryAnalyseNorms]([Id],[PlanId],[PlanCycle],[LastStartPeriod],[ForecastCount],[BasicHistoryCount] ,[UnitHistoryCount],[HotWeight],[SizeWeight]" +
" ,[ThreeRegionWeight],[MissingValueWeight],[OddEvenWeight],[Modulus],[LotteryType],[Enable],[IsDefault],[CreatTime],[CreateUserId])" +
" VALUES(@Id, @PlanId, @PlanCycle, @LastStartPeriod, @ForecastCount, @BasicHistoryCount, @UnitHistoryCount, @HotWeight, @SizeWeight, @ThreeRegionWeight, @MissingValueWeight" +
", @OddEvenWeight, @Modulus, @LotteryType, @ENABLE, @IsDefault, GETDATE(), @CreateUserId)";
string sqlStr2 = "INSERT INTO [dbo].[UserAnylseNorms]([Id],[UserId],[PlanId],[LotteryAnalyseNormId],[LotteryType],[CreatTime])" +
" VALUES(@Id, @UserId,@PlanId, @LotteryAnalyseNormId, @LotteryType, GETDATE())";
using (var cn = LotteryDbConnection)
{
cn.Open();
using (var trans = cn.BeginTransaction())
{
try
{
foreach (var planId in planIds)
{
var lotteryAnalyseNorm = new LotteryAnalyseNorm()
{
PlanId = planId,
BasicHistoryCount = userBasicNorm.BasicHistoryCount,
CreateUserId = userId,
ForecastCount = userBasicNorm.ForecastCount,
HotWeight = userBasicNorm.HotWeight,
LastStartPeriod = 0, // Todo: set LastStartPeriod
LotteryType = lotteryType.ToString(),
UnitHistoryCount = userBasicNorm.UnitHistoryCount,
Modulus = userBasicNorm.Modulus,
OddEvenWeight = userBasicNorm.OddEvenWeight,
MissingValueWeight = userBasicNorm.MissingValueWeight,
ThreeRegionWeight = userBasicNorm.ThreeRegionWeight,
SizeWeight = userBasicNorm.SizeWeight,
PlanCycle = userBasicNorm.PlanCycle,
};
var userAnalyseNorm = new UserAnylseNorm()
{
LotteryAnalyseNormId = lotteryAnalyseNorm.Id,
LotteryType = lotteryType.ToString(),
UserId = userId,
PlanId = planId,
};
cn.Execute(sqlStr1, lotteryAnalyseNorm, trans);
cn.Execute(sqlStr2, userAnalyseNorm, trans);
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
LogDbHelper.LogError(ex, GetType() + "=>InsertUserPlans");
throw ex;
}
}
}
}
public bool SetUserBasicNorm(UserBasicNorm userBasicNorm)
{
bool isSuccess = false;
using (var cn = LotteryDbConnection)
{
string sqlStr1 =
"SELECT TOP 1 * FROM [Lottery-Dev].[dbo].[UserBasicNorms] WITH(NOLOCK) WHERE UserId=@UserId AND LotteryType=@LotteryType";
string sqlStr2 =
"INSERT INTO [dbo].[UserBasicNorms]([Id] ,[UserId],[PlanCycle],[ForecastCount],[BasicHistoryCount] ,[UnitHistoryCount],[HotWeight],[SizeWeight] ,[ThreeRegionWeight]," +
" [MissingValueWeight],[OddEvenWeight],[Modulus],[LotteryType],[LastStartPeriod],[Enable],[IsDefault],[CreatTime],[ModifyTime])" +
" VALUES(@Id,@UserId,@PlanCycle,@ForecastCount,@BasicHistoryCount,@UnitHistoryCount,@HotWeight,@SizeWeight,@ThreeRegionWeight,@MissingValueWeight,@OddEvenWeight,@Modulus,@LotteryType,@LastStartPeriod,@Enable," +
" @IsDefault,GETDATE(),GETDATE())";
string sqlStr3 = "UPDATE [dbo].[UserBasicNorms]" +
"SET[PlanCycle] = @PlanCycle" +
",[LastStartPeriod] = @LastStartPeriod" +
",[ForecastCount] = @ForecastCount" +
",[BasicHistoryCount] = @BasicHistoryCount" +
",[UnitHistoryCount] = @UnitHistoryCount" +
",[HotWeight] = @HotWeight" +
",[SizeWeight] = @SizeWeight" +
",[ThreeRegionWeight] = @ThreeRegionWeight" +
",[MissingValueWeight] = @MissingValueWeight" +
",[OddEvenWeight] =@OddEvenWeight" +
",[Modulus] = @Modulus " +
",[ModifyTime] = GETDATE()" +
" WHERE[UserId] = @UserId AND LotteryType = @LotteryType";
var userBasicNormExist = cn.QuerySingleOrDefault(sqlStr1, new
{
userBasicNorm.UserId,
userBasicNorm.LotteryType
});
if (userBasicNormExist == null)
{
isSuccess = cn.Execute(sqlStr2, userBasicNorm) > 0;
}
else
{
isSuccess = cn.Execute(sqlStr3, userBasicNorm) > 0;
}
}
return(isSuccess);
}
public UserBasicNorm GetUserBasicNorm(string userId, LotteryType lotteryType)
{
UserBasicNorm userBasicNorm = null;
using (var cn = LotteryDbConnection)
{
string sqlStr1 =
"SELECT TOP 1 * FROM [Lottery-Dev].[dbo].[UserBasicNorms] WHERE UserId=@UserId AND LotteryType=@LotteryType";
string sqlStr2 =
"SELECT TOP 1 * FROM [Lottery-Dev].[dbo].[UserBasicNorms] WHERE IsDefault=@IsDefault AND LotteryType=@LotteryType";
userBasicNorm = cn.QuerySingleOrDefault <UserBasicNorm>(sqlStr1, new
{
UserId = userId,
LotteryType = lotteryType.ToString()
}) ?? cn.QuerySingleOrDefault <UserBasicNorm>(sqlStr2, new
{
IsDefault = 1,
LotteryType = lotteryType.ToString()
});
}
return(userBasicNorm);
}
public void UpdateUserPlans(string userId, LotteryType lotteryType, UserBasicNorm userBasicNorm, IList <int> planIds,
IList <int> userOldLotteryPlanIds)
{
string sqlStr1 = "SELECT * FROM [dbo].[UserAnylseNorms] WHERE UserId=@UserId AND LotteryType=@LotteryType AND PlanId=@PlanId";
string sqlStr2 = "INSERT INTO [dbo].[UserAnylseNorms]([Id],[UserId],[PlanId],[LotteryAnalyseNormId],[LotteryType],[CreatTime])" +
" VALUES(@Id, @UserId,@PlanId, @LotteryAnalyseNormId, @LotteryType, GETDATE())";
string sqlStr2_1 = "INSERT INTO [dbo].[LotteryAnalyseNorms]([Id],[PlanId],[PlanCycle],[LastStartPeriod],[ForecastCount],[BasicHistoryCount] ,[UnitHistoryCount],[HotWeight],[SizeWeight]" +
" ,[ThreeRegionWeight],[MissingValueWeight],[OddEvenWeight],[Modulus],[LotteryType],[Enable],[IsDefault],[CreatTime],[CreateUserId])" +
" VALUES(@Id, @PlanId, @PlanCycle, @LastStartPeriod, @ForecastCount, @BasicHistoryCount, @UnitHistoryCount, @HotWeight, @SizeWeight, @ThreeRegionWeight, @MissingValueWeight" +
", @OddEvenWeight, @Modulus, @LotteryType, @ENABLE, @IsDefault, GETDATE(), @CreateUserId)";
string sqlStr3 = "SELECT * FROM [dbo].[LotteryAnalyseNorms] WHERE Id=@Id";
string sqlStr4 = "DELETE [dbo].[UserAnylseNorms] WHERE Id = @Id";
string sqlStr5 = "UPDATE [dbo].[LotteryAnalyseNorms]" +
"SET [LastStartPeriod] = @LastStartPeriod" +
",[Enable] = @Enable" +
",[ModifyTime] = GETDATE()" +
" WHERE [Id] = @Id";
string lotteryAnalyseNormHashKey = string.Format(LsConstant.LotteryAnalyseNormRedisKey, lotteryType);
using (var cn = LotteryDbConnection)
{
cn.Open();
using (var trans = cn.BeginTransaction())
{
try
{
// 1. 遍历更改的planIds
foreach (var planId in planIds)
{
var userAnylseNorm = cn.QuerySingleOrDefault <UserAnylseNorm>(sqlStr1, new
{
UserId = userId,
LotteryType = lotteryType.ToString(),
PlanId = planId,
}, trans);
// 如果用户还没有添加过该计划
if (userAnylseNorm == null)
{
#region 用户没有添加过该计划
var lotteryAnalyseNorm = new LotteryAnalyseNorm()
{
PlanId = planId,
BasicHistoryCount = userBasicNorm.BasicHistoryCount,
CreateUserId = userId,
ForecastCount = userBasicNorm.ForecastCount,
HotWeight = userBasicNorm.HotWeight,
LastStartPeriod = 0, // Todo: set LastStartPeriod
LotteryType = lotteryType.ToString(),
UnitHistoryCount = userBasicNorm.UnitHistoryCount,
Modulus = userBasicNorm.Modulus,
OddEvenWeight = userBasicNorm.OddEvenWeight,
MissingValueWeight = userBasicNorm.MissingValueWeight,
ThreeRegionWeight = userBasicNorm.ThreeRegionWeight,
SizeWeight = userBasicNorm.SizeWeight,
PlanCycle = userBasicNorm.PlanCycle,
};
userAnylseNorm = new UserAnylseNorm()
{
LotteryAnalyseNormId = lotteryAnalyseNorm.Id,
LotteryType = lotteryType.ToString(),
UserId = userId,
PlanId = planId,
};
cn.Execute(sqlStr2_1, lotteryAnalyseNorm, trans);
cn.Execute(sqlStr2, userAnylseNorm, trans);
RedisHelper.SetHash(lotteryAnalyseNormHashKey, lotteryAnalyseNorm.Id,
lotteryAnalyseNorm);
#endregion
}
else
{
#region 用户添加过该计划
var lotteryAnalyseNorm = cn.QuerySingle <LotteryAnalyseNorm>(sqlStr3, new
{
Id = userAnylseNorm.LotteryAnalyseNormId
}, trans);
if (!lotteryAnalyseNorm.Enable)
{
lotteryAnalyseNorm.Enable = true;
lotteryAnalyseNorm.ModifyTime = DateTime.Now;
lotteryAnalyseNorm.LastStartPeriod = 0; // Todo: set LastStartPeriod
cn.Execute(sqlStr5, new
{
lotteryAnalyseNorm.Id,
lotteryAnalyseNorm.Enable,
LatestStartPeriod = lotteryAnalyseNorm.LastStartPeriod
}, trans);
RedisHelper.SetHash(lotteryAnalyseNormHashKey, lotteryAnalyseNorm.Id,
lotteryAnalyseNorm);
}
#endregion
}
}
// 2. 将旧计划且当前没有选的计划移除
#region 将旧计划且当前没有选的计划移除
foreach (var oldPlanId in userOldLotteryPlanIds)
{
if (planIds.Contains(oldPlanId))
{
continue;
}
var userAnylseNorm = cn.QuerySingleOrDefault <UserAnylseNorm>(sqlStr1, new
{
UserId = userId,
LotteryType = lotteryType.ToString(),
PlanId = oldPlanId,
}, trans);
if (userAnylseNorm != null)
{
cn.Execute(sqlStr4, new
{
Id = userAnylseNorm.Id
}, trans);
#region 修改LotteryAnalyseNorm 指标
var lotteryAnalyseNorm = cn.QuerySingle <LotteryAnalyseNorm>(sqlStr3, new
{
Id = userAnylseNorm.LotteryAnalyseNormId
}, trans);
if (lotteryAnalyseNorm.Enable)
{
lotteryAnalyseNorm.Enable = false;
lotteryAnalyseNorm.LastStartPeriod = 0; // Todo: set LastStartPeriod
cn.Execute(sqlStr5, new
{
lotteryAnalyseNorm.Id,
lotteryAnalyseNorm.Enable,
LatestStartPeriod = lotteryAnalyseNorm.LastStartPeriod
}, trans);
RedisHelper.Remove(lotteryAnalyseNormHashKey, lotteryAnalyseNorm.Id);
}
#endregion
}
}
#endregion
trans.Commit();
}
catch (Exception ex)
{
LogDbHelper.LogError(ex, GetType().FullName + "UpdateUserPlans");
trans.Rollback();
throw ex;
}
}
}
}
public bool SetUserBasicNorm(UserBasicNorm userBasicNorm)
{
return(_analyseNormDapperRepostory.SetUserBasicNorm(userBasicNorm));
}