/// <summary>
/// 删除用户的问卷答案记录
/// </summary>
/// <param name="userId"></param>
/// <param name="objectId"></param>
/// <returns></returns>
public static async Task <int> DeleteQuestionnaireAnswerRecord(Guid userId, int objectId)
{
#region SQL
string sql = @" UPDATE Activity..QuestionnaireAnswerRecord WITH(ROWLOCK)
SET IsDeleted = 1,LastUpdateDateTime=GETDATE()
WHERE UserID = @UserID
AND ObjectID = @ObjectID; ";
#endregion
BaseDbHelper dbHelper = null;
SqlCommand sqlCmd = null;
try
{
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@UserID", userId),
new SqlParameter("@ObjectID", objectId)
};
dbHelper = DbHelper.CreateDbHelper(false);
sqlCmd = new SqlCommand(sql);
sqlCmd.Parameters.AddRange(paras.ToArray());
return(await dbHelper.ExecuteNonQueryAsync(sqlCmd));
}
catch (Exception ex)
{
Logger.Error($"DB异常=>DeleteQuestionnaireAnswerRecord=>{ex.ToString()}");
return(0);
}
finally
{
dbHelper?.Dispose();
sqlCmd?.Dispose();
}
}
/// <summary>
/// 更新七龙珠 用户信息
/// </summary>
/// <returns></returns>
public static async Task <bool> UpdateDragonBallUserInfoModelAsync(BaseDbHelper dbHelper,
DragonBallUserInfoModel model)
{
var sql = @" update Activity.[dbo].[tbl_DragonBallUserInfo]
set
[FinishMissionCount] = @FinishMissionCount
,[DragonBallCount] = @DragonBallCount
,DragonBallSummonCount = @DragonBallSummonCount
,LastUpdateDateTime = getdate()
where pkid = @pkid and LastUpdateDateTime = @LastUpdateDateTime and (DragonBallCount - @DragonBallCount) >= 0
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@FinishMissionCount", model.FinishMissionCount);
cmd.AddParameter("@DragonBallCount", model.DragonBallCount);
cmd.AddParameter("@DragonBallSummonCount", model.DragonBallSummonCount);
cmd.AddParameter("@pkid", model.PKID);
cmd.AddParameter("@LastUpdateDateTime", model.LastUpdateDateTime);
var result = await dbHelper.ExecuteNonQueryAsync(cmd);
return(result > 0);
}
}
/// <summary>
/// 根据pageId删除用户的问卷链接信息
/// </summary>
/// <param name="pageId"></param>
/// <returns></returns>
public static async Task <int> DeleteUserQuestionnaireURL(Guid pageId)
{
#region SQL
string sql = @" UPDATE [Activity].[dbo].[UserQuestionnaireURL] WITH(ROWLOCK) SET IsDeleted=1,LastUpdateDateTime=GETDATE() WHERE PageID=@PageID ";
#endregion
BaseDbHelper dbHelper = null;
SqlCommand sqlCmd = null;
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@PageID", pageId)
};
try
{
dbHelper = DbHelper.CreateDbHelper(false);
sqlCmd = new SqlCommand(sql);
sqlCmd.Parameters.AddRange(paras);
return(await dbHelper.ExecuteNonQueryAsync(sqlCmd));
}
catch (Exception ex)
{
Logger.Error($"DB异常=>AddUserQuestionnaireURL=>{ex.ToString()}");
return(0);
}
finally
{
dbHelper?.Dispose();
sqlCmd?.Dispose();
}
}
/// <summary>
/// 更新公众号领红包统计
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="setting"></param>
/// <returns></returns>
public static async Task <bool> UpdateOARedEnvelopeStatisticsAsync(BaseDbHelper dbHelper,
OARedEnvelopeStatisticsModel setting)
{
var sql = @"
UPDATE Activity.[dbo].[tbl_OARedEnvelopeStatistics]
SET [StatisticsDate] = @StatisticsDate
,[DayMaxMoney] = @DayMaxMoney
,[UserCount] = @UserCount
,[RedEnvelopeCount] = @RedEnvelopeCount
,[RedEnvelopeSumMoney] = @RedEnvelopeSumMoney
,[RedEnvelopeAvg] = @RedEnvelopeAvg
,[OfficialAccountType] = @OfficialAccountType
,[LastUpdateDateTime] = getdate()
WHERE pkid = @pkid
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@pkid", setting.PKID);
cmd.AddParameter("@StatisticsDate", setting.StatisticsDate);
cmd.AddParameter("@DayMaxMoney", setting.DayMaxMoney);
cmd.AddParameter("@UserCount", setting.UserCount);
cmd.AddParameter("@RedEnvelopeCount", setting.RedEnvelopeCount);
cmd.AddParameter("@RedEnvelopeSumMoney", setting.RedEnvelopeSumMoney);
cmd.AddParameter("@RedEnvelopeAvg", setting.RedEnvelopeAvg);
cmd.AddParameter("@OfficialAccountType", setting.OfficialAccountType);
var result = await dbHelper.ExecuteNonQueryAsync(cmd);
return(result > 0);
}
}
/// <summary>
/// 更新公众号领红包设置
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="setting"></param>
/// <returns></returns>
public static async Task <bool> UpdateOARedEnvelopeSettingAsync(BaseDbHelper dbHelper,
OARedEnvelopeSettingModel setting)
{
var sql = @"
UPDATE Configuration.[dbo].[tbl_OARedEnvelopeSetting]
SET [ConditionPrice] = @ConditionPrice
,[ConditionPriceFlag] = @ConditionPriceFlag
,[ConditionCarModelFlag] = @ConditionCarModelFlag
,[DayMaxMoney] = @DayMaxMoney
,[AvgMoney] = @AvgMoney
,[ActivityRuleText] = @ActivityRuleText
,[FailTipText] = @FailTipText
,[QRCodeUrl] = @QRCodeUrl
,[QRCodeTipText] = @QRCodeTipText
,[ShareTitleText] = @ShareTitleText
,[ShareUrl] = @ShareUrl
,[SharePictureUrl] = @SharePictureUrl
,[ShareText] = @ShareText
,[OfficialAccountType] = @OfficialAccountType
,[LastUpdateDateTime] = getdate()
,[LastUpdateBy] = @LastUpdateBy
,PerMaxMoney = @PerMaxMoney
,PerMinMoney = @PerMinMoney
,OpenIdLegalDate = @OpenIdLegalDate
,Channel = @Channel
WHERE pkid = @pkid
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@pkid", setting.PKID);
cmd.AddParameter("@ConditionPrice", setting.ConditionPrice);
cmd.AddParameter("@ConditionPriceFlag", setting.ConditionPriceFlag);
cmd.AddParameter("@ConditionCarModelFlag", setting.ConditionCarModelFlag);
cmd.AddParameter("@DayMaxMoney", setting.DayMaxMoney);
cmd.AddParameter("@AvgMoney", setting.AvgMoney);
cmd.AddParameter("@ActivityRuleText", setting.ActivityRuleText ?? "");
cmd.AddParameter("@FailTipText", setting.FailTipText ?? "");
cmd.AddParameter("@QRCodeUrl", setting.QRCodeUrl ?? "");
cmd.AddParameter("@QRCodeTipText", setting.QRCodeTipText ?? "");
cmd.AddParameter("@ShareTitleText", setting.ShareTitleText ?? "");
cmd.AddParameter("@ShareUrl", setting.ShareUrl ?? "");
cmd.AddParameter("@SharePictureUrl", setting.SharePictureUrl ?? "");
cmd.AddParameter("@ShareText", setting.ShareText ?? "");
cmd.AddParameter("@OfficialAccountType", setting.OfficialAccountType);
cmd.AddParameter("@LastUpdateBy", setting.LastUpdateBy ?? "");
cmd.AddParameter("@PerMaxMoney", setting.PerMaxMoney);
cmd.AddParameter("@PerMinMoney", setting.PerMinMoney);
cmd.AddParameter("@OpenIdLegalDate", ((object)setting.OpenIdLegalDate) ?? DBNull.Value);
cmd.AddParameter("@Channel", setting.Channel ?? "");
var result = await dbHelper.ExecuteNonQueryAsync(cmd);
return(result > 0);
}
}
/// <summary>
/// 更新用户正在做的任务
/// </summary>
/// <returns></returns>
public static async Task <bool> UpdateDragonBallUserMissionAsync(BaseDbHelper dbHelper,
DragonBallUserMissionModel model)
{
var sql = @" update Activity.[dbo].[tbl_DragonBallUserMission]
set
[MissionId] = @MissionId
,[MissionStatus] = @MissionStatus
,[DragonBallCount] = @DragonBallCount
,LastUpdateDateTime = getdate()
where pkid = @pkid and LastUpdateDateTime = @LastUpdateDateTime
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@MissionId", model.MissionId);
cmd.AddParameter("@MissionStatus", model.MissionStatus);
cmd.AddParameter("@DragonBallCount", model.DragonBallCount);
cmd.AddParameter("@pkid", model.PKID);
cmd.AddParameter("@LastUpdateDateTime", model.LastUpdateDateTime);
var result = await dbHelper.ExecuteNonQueryAsync(cmd);
return(result > 0);
}
}
/// <summary>
/// 删除 - 小游戏 - 马牌用户助力
/// </summary>
/// <returns></returns>
public static async Task <bool> DeleteGameMaPaiUserSupportAsync(BaseDbHelper helper, int activityId, string openId)
{
var sql = @" delete Activity.[dbo].[tbl_GameMaPaiUserSupport]
where SupportOpenId = @SupportOpenId and ActivityId = @ActivityId
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@SupportOpenId", openId);
cmd.AddParameter("@ActivityId", activityId);
var result = await helper.ExecuteNonQueryAsync(cmd);
return(result > 0);
}
}
/// <summary>
/// 删除 - 小游戏 - 用户积分信息
/// </summary>
/// <returns></returns>
public static async Task <bool> DeleteGameUserInfoAsync(BaseDbHelper helper, int activityId, Guid userId)
{
var sql = @" delete Activity.[dbo].[tbl_GameUserInfo]
where userid = @userid and ActivityId = @ActivityId
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@userid", userId);
cmd.AddParameter("@ActivityId", activityId);
var result = await helper.ExecuteNonQueryAsync(cmd);
return(result > 0);
}
}
/// <summary>
/// 删除七龙珠 用户信息
/// </summary>
/// <returns></returns>
public static async Task <bool> DeleteDragonBallUserInfoModelAsync(BaseDbHelper dbHelper,
Guid userId)
{
var sql = @" delete Activity.[dbo].[tbl_DragonBallUserInfo]
where userid = @UserId
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@UserId", userId);
var result = await dbHelper.ExecuteNonQueryAsync(cmd);
return(result > 0);
}
}
/// <summary>
/// 设置用户答案结果
/// </summary>
/// <param name="helper"></param>
/// <param name="answerId"></param>
/// <param name="couponCount"></param>
/// <param name="flag"> 1 胜利 2 失败</param>
/// <returns></returns>
public static async Task <bool> UpdateUserAnswerResult(BaseDbHelper helper, long answerId, int couponCount, int flag)
{
var sql = @"
update Activity.dbo.QuestionnaireAnswerResult
set AnswerResultStatus = @AnswerResultStatus,WinCouponCount = @WinCouponCount,LastUpdateDateTime=getdate()
where PKID = @PKID
";
using (var cmd = new SqlCommand(sql))
{
cmd.Parameters.AddWithValue("@AnswerResultStatus", flag);
cmd.Parameters.AddWithValue("@WinCouponCount", couponCount);
cmd.Parameters.AddWithValue("@PKID", answerId);
var result = await helper.ExecuteNonQueryAsync(cmd);
return(result > 0);
}
}
/// <summary>
/// 更新 - 小游戏 - 用户积分信息
/// </summary>
/// <returns></returns>
public static async Task <bool> UpdateGameUserInfoPointAsync(BaseDbHelper helper, long pkid,
int point)
{
var sql = @" update Activity.[dbo].[tbl_GameUserInfo]
set
[point] = point + @point
,LastUpdateDateTime = getdate()
where pkid = @pkid and (point + @point) >= 0
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@pkid", pkid);
cmd.AddParameter("@point", point);
var result = await helper.ExecuteNonQueryAsync(cmd);
return(result > 0);
}
}
/// <summary>
/// 更新 - 小游戏 - 马牌奖品库存
/// </summary>
/// <returns></returns>
public static async Task <bool> UpdateGameMaPaiPrizeSettingCountAsync(BaseDbHelper helper, long pkid,
int count)
{
var sql = @" update Configuration.[dbo].[tbl_GameMaPaiPrizeSetting]
set
[LCount] = LCount + @LCount
,LastUpdateDateTime = getdate()
where pkid = @pkid and ((LCount + @LCount) >= 0 or Count = -1)
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@pkid", pkid);
cmd.AddParameter("@LCount", count);
var result = await helper.ExecuteNonQueryAsync(cmd);
return(result > 0);
}
}
/// <summary>
/// 更新用户积分的使用状态
/// </summary>
/// <param name="activityId"></param>
/// <param name="userID"></param>
/// <returns></returns>
public static async Task <int> UpdateUserPointIsUsedAsync(BaseDbHelper dbHelper, int activityId, Guid userID)
{
var sql = @"UPDATE Activity.[dbo].[tbl_GameUserPointDetail] WITH (ROWLOCK)
SET IsUsed = 1,
LastUpdateDateTime = GETDATE()
WHERE ActivityId = @ActivityId
AND UserId = @UserId
AND DATEDIFF(DAY,CreateDatetime,GETDATE())>0
AND IsUsed <> 1;";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@ActivityId", activityId);
cmd.AddParameter("@UserId", userID);
var result = await dbHelper.ExecuteNonQueryAsync(cmd);
return(result);
}
}
/// <summary>
/// 创建用户的问卷链接信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static async Task <int> AddUserQuestionnaireURL(UserQuestionnaireURLModel model)
{
#region SQL
string sql = @" INSERT INTO [Activity].[dbo].[UserQuestionnaireURL] WITH(ROWLOCK)
([PageID]
,[QuestionnaireNo]
,[OrderID]
,[ComplaintsID]
,[ComplaintsType]
,[IsAtStore]
,[Department]
,[UserID]
,[UserPhone]
,[StaffEmail]
,[ShortURL]
,[OriginalURL])
VALUES
(@PageID
,@QuestionnaireNo
,@OrderID
,@ComplaintsID
,ISNULL(@ComplaintsType,'')
,@IsAtStore
,ISNULL(@Department,'')
,@UserID
,ISNULL(@UserPhone,'')
,ISNULL(@StaffEmail,'')
,@ShortURL
,@OriginalURL) ";
#endregion
BaseDbHelper dbHelper = null;
SqlCommand sqlCmd = null;
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@PageID", model.PageID),
new SqlParameter("@QuestionnaireNo", model.QuestionnaireNo),
new SqlParameter("@OrderID", model.OrderID),
new SqlParameter("@ComplaintsID", model.ComplaintsID),
new SqlParameter("@ComplaintsType", model.ComplaintsType),
new SqlParameter("@IsAtStore", model.IsAtStore),
new SqlParameter("@Department", model.Department),
new SqlParameter("@UserID", model.UserID),
new SqlParameter("@UserPhone", model.UserPhone),
new SqlParameter("@StaffEmail", model.StaffEmail),
new SqlParameter("@ShortURL", model.ShortURL),
new SqlParameter("@OriginalURL", model.OriginalURL)
};
try
{
dbHelper = DbHelper.CreateDbHelper(false);
sqlCmd = new SqlCommand(sql);
sqlCmd.Parameters.AddRange(paras);
return(await dbHelper.ExecuteNonQueryAsync(sqlCmd));
}
catch (Exception ex)
{
Logger.Error($"DB异常=>AddUserQuestionnaireURL=>{ex.ToString()}");
return(0);
}
finally
{
dbHelper?.Dispose();
sqlCmd?.Dispose();
}
}