/// <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>
/// <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);
}
}
public static int UpdateCategoryBrand(CategoryBrandRankModel model, BaseDbHelper db)
{
string sql =
@"UPDATE Activity..[tbl_CategoryBrandRank] WITH(ROWLOCK)
SET
[ParentPkid] = @ParentPkid
,[Name] = ISNULL(@Name,[Name])
,[NameIndex] = @NameIndex
,[PageTitle] = ISNULL(@PageTitle,PageTitle)
,[PageShareTitle] = ISNULL(@PageShareTitle,PageShareTitle)
,[PageShareDescription] = ISNULL(@PageShareDescription,PageShareDescription)
,[PageShareContent] = ISNULL(@PageShareContent,PageShareContent)
WHERE PKID=@PKID";
using (var cmd = new SqlCommand(sql))
{
cmd.Parameters.AddWithValue("@ParentPkid", model.ParentPkid);
cmd.Parameters.AddWithValue("@Name", model.Name);
cmd.Parameters.AddWithValue("@NameIndex", model.NameIndex);
cmd.Parameters.AddWithValue("@PageTitle", model.PageTitle);
cmd.Parameters.AddWithValue("@PageShareTitle", model.PageShareTitle);
cmd.Parameters.AddWithValue("@PageShareDescription", model.PageShareDescription);
cmd.Parameters.AddWithValue("@PageShareContent", model.PageShareContent);
cmd.Parameters.AddWithValue("@PKID", model.PKID);
return(db.ExecuteNonQuery(cmd));
}
}
/// <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>
/// 更新用户正在做的任务
/// </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>
/// <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>
/// 根据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();
}
}
public static bool UpdateVehicleAuditStatus(BaseDbHelper db, Guid carId, int status)
{
using (var cmd = new SqlCommand(@"UPDATE Tuhu_profiles..VehicleAuthAppeal WITH(ROWLOCK) SET Status=@Status WHERE CarID=@CarID"))
{
cmd.Parameters.Add(new SqlParameter("@Status", status));
cmd.Parameters.Add(new SqlParameter("@CarID", carId));
return(db.ExecuteNonQuery(cmd) > 0);
}
}
/// <summary>
/// 新增用户奖励
/// </summary>
/// <returns></returns>
public static async Task <long> InsertDragonBallUserLootAsync(BaseDbHelper dbHelper,
DragonBallUserLootModel model)
{
var sql = @" insert into Activity.[dbo].[tbl_DragonBallUserLoot]
(
[UserId]
,[LootName]
,[LootStartTime]
,[LootEndTime]
,[LootDesc]
,[LootPicUrl]
,[CreateDatetime]
,[LastUpdateDateTime]
,[LootType]
,[LootTitile]
,[LootMemo]
)
values (
@UserId
,@LootName
,@LootStartTime
,@LootEndTime
,@LootDesc
,@LootPicUrl
,getdate()
,getdate()
,@LootType
,@LootTitile
,@LootMemo
);
SELECT SCOPE_IDENTITY();
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@UserId", model.UserId);
cmd.AddParameter("@LootName", model.LootName ?? "");
cmd.AddParameter("@LootStartTime", model.LootStartTime);
cmd.AddParameter("@LootEndTime", model.LootEndTime);
cmd.AddParameter("@LootDesc", model.LootDesc ?? "");
cmd.AddParameter("@LootPicUrl", model.LootPicUrl ?? "");
cmd.AddParameter("@LootType", model.LootType);
cmd.AddParameter("@LootTitile", model.LootTitile ?? "");
cmd.AddParameter("@LootMemo", model.LootMemo ?? "");
var result = await dbHelper.ExecuteScalarAsync(cmd);
return(Convert.ToInt64(result));
}
}
public static bool DeleteCondition(Guid taskId, BaseDbHelper dbhelper)
{
const string sqlStr = @"DELETE Configuration..TaskConditionInfo WHERE TaskId=@taskId;";
using (var cmd = new SqlCommand(sqlStr))
{
cmd.Parameters.AddWithValue("@taskId", taskId);
return(DbHelper.ExecuteNonQuery(cmd) > 0);
}
}
public static int JoinWhiteList(BaseDbHelper dbhelper, IEnumerable <string> pids)
{
return(dbhelper.ExecuteNonQuery(@"INSERT INTO Tuhu_bi.dbo.TireStockWhiteList
( PID ,
Type
)
SELECT * ,
1
FROM Tuhu_bi.dbo.Split(@pids, ';')", CommandType.Text, new SqlParameter("@pids", string.Join(";", pids))));
}
/// <summary>
/// 通过主键获取问题对象
/// </summary>
/// <param name="pkid"></param>
/// <returns></returns>
public static async Task <QuestionModel> GetQuestion(long pkid)
{
#region SQL
string sql = @" SELECT [PKID] as [QuestionID]
,[QuestionnaireID]
,[QuestionTitle]
,[QuestionType]
,[IsFork]
,[DefaultValue]
,[ScoreStyle]
,[MinScore]
,[MaxScore]
,[IsRequired]
,[IsValidateMinChar]
,[MinChar]
,[IsValidateMaxChar]
,[MaxChar]
,[IsValidateStartDate]
,[StartTime]
,[IsValidateEndDate]
,[EndTime]
,[IsValidatePhone]
,[Sort]
,[CreateDateTime]
,[LastUpdateDateTime]
,[IsDeleted]
,[QuestionTextResult]
,[QuestionConfirm]
,[DeadLineTime]
FROM [Activity].[dbo].[Question] WITH ( NOLOCK )
WHERE IsDeleted = 0
AND pkid = @pkid ";
#endregion
BaseDbHelper dbHelper = null;
SqlCommand sqlCmd = null;
try
{
SqlParameter[] paras = { new SqlParameter("@pkid", pkid) };
dbHelper = DbHelper.CreateDbHelper(true);
sqlCmd = new SqlCommand(sql);
sqlCmd.Parameters.AddRange(paras.ToArray());
return((await dbHelper.ExecuteSelectAsync <QuestionModel>(sqlCmd)).FirstOrDefault());
}
catch (Exception ex)
{
Logger.Error($"DB异常=>{nameof(GetQuestion)}=>{ex.ToString()}");
return(null);
}
finally
{
dbHelper?.Dispose();
sqlCmd?.Dispose();
}
}
/// <summary>
/// 根据问卷标识和时间范围获取问题列表
/// </summary>
/// <param name="questionnaireId"></param>
/// <param name="time"></param>
/// <returns></returns>
public static async Task <IEnumerable <QuestionModel> > GetQuestionList(long questionnaireId, DateTime time)
{
#region SQL
string sql = @" SELECT [PKID] AS QuestionID
,[QuestionnaireID]
,[QuestionTitle]
,[QuestionType]
,[IsFork]
,[DefaultValue]
,[ScoreStyle]
,[MinScore]
,[MaxScore]
,[IsRequired]
,[IsValidateMinChar]
,[MinChar]
,[IsValidateMaxChar]
,[MaxChar]
,[IsValidateStartDate]
,[StartTime]
,[IsValidateEndDate]
,[EndTime]
,[IsValidatePhone]
,[Sort]
,[CreateDateTime]
,[LastUpdateDateTime]
,[IsDeleted]
,[QuestionTextResult]
,[QuestionConfirm]
,[DeadLineTime]
FROM [Activity].[dbo].[Question] WITH ( NOLOCK )
WHERE IsDeleted=0 AND QuestionnaireID=@QuestionnaireID and StartTime< @time and EndTime > @time
ORDER BY Sort ASC ";
#endregion
BaseDbHelper dbHelper = null;
SqlCommand sqlCmd = null;
try
{
SqlParameter[] paras = { new SqlParameter("@QuestionnaireID", questionnaireId), new SqlParameter("@time", time) };
dbHelper = DbHelper.CreateDbHelper(true);
sqlCmd = new SqlCommand(sql);
sqlCmd.Parameters.AddRange(paras.ToArray());
return(await dbHelper.ExecuteSelectAsync <QuestionModel>(sqlCmd));
}
catch (Exception ex)
{
Logger.Error($"DB异常=>GetQuestionList=>{ex.ToString()}");
return(null);
}
finally
{
dbHelper?.Dispose();
sqlCmd?.Dispose();
}
}
public static int DeleteCategoryBrand(long id, BaseDbHelper db)
{
string sql =
@"DELETE FROM Activity..[tbl_CategoryBrandRank] WHERE PKID=@PKID";
using (var cmd = new SqlCommand(sql))
{
cmd.Parameters.AddWithValue("@PKID", id);
return(db.ExecuteNonQuery(cmd));
}
}
public static CategoryBrandRankModel FetchCategoryBrand(long pkid, BaseDbHelper db)
{
string sql =
@"SELECT * FROM Activity..tbl_CategoryBrandRank WITH(NOLOCK) WHERE PKID=@PKID";
using (var cmd = new SqlCommand(sql))
{
cmd.Parameters.AddWithValue("@PKID", pkid);
return(db.ExecuteDataTable(cmd).ConvertTo <CategoryBrandRankModel>().FirstOrDefault());
}
}
/// <summary>
/// 新增公众号领红包 - 明细数据
/// </summary>
/// <returns></returns>
public static async Task<long> InsertOARedEnvelopeDetailAsync(BaseDbHelper dbHelper,
OARedEnvelopeDetailModel redEnvelopeDetailModel)
{
var sql = @"
INSERT INTO Activity.[dbo].[tbl_OARedEnvelopeDetail]
([UserId]
,[NickName]
,[WXHeadImgUrl]
,[OpenId]
,[ReferrerUserId]
,[GetMoney]
,[GetDate]
,[OfficialAccountType]
,DrivingLicense
,IsDeleted
)
VALUES
(
@UserId
,@NickName
,@WXHeadPicUrl
,@OpenId
,@ReferrerUserId
,@GetMoney
,@GETDATE
,@OfficialAccountType
,@DrivingLicense
,0
);
SELECT SCOPE_IDENTITY();
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@UserId", redEnvelopeDetailModel.UserId);
cmd.AddParameter("@NickName", redEnvelopeDetailModel.NickName ?? "");
cmd.AddParameter("@WXHeadPicUrl", redEnvelopeDetailModel.WXHeadImgUrl ?? "");
cmd.AddParameter("@OpenId", redEnvelopeDetailModel.OpenId ?? "");
cmd.AddParameter("@ReferrerUserId", redEnvelopeDetailModel.ReferrerUserId);
cmd.AddParameter("@GetMoney", redEnvelopeDetailModel.GetMoney);
cmd.AddParameter("@GetDate", DateTime.Now.Date);
cmd.AddParameter("@OfficialAccountType", redEnvelopeDetailModel.OfficialAccountType);
cmd.AddParameter("@DrivingLicense", redEnvelopeDetailModel.DrivingLicense ?? "");
var result = await dbHelper.ExecuteScalarAsync(cmd);
return Convert.ToInt64(result);
}
}
/// <summary>
/// 根据问卷编号获取问卷信息
/// </summary>
/// <param name="userId"></param>
/// <param name="objectId"></param>
/// <returns></returns>
public static async Task <QuestionnaireAnswerRecordModel> GetQuestionnaireAnswerRecordInfo(Guid userId, int objectId)
{
#region SQL
string sql = @"SELECT TOP 1
[PKID] ,
[UserID] ,
[QuestionnaireID] ,
[QuestionnaireName] ,
[QuestionID] ,
[QuestionName] ,
[QuestionType] ,
[AnswerText] ,
[AnswerOptionID] ,
[AnswerOptionContent] ,
[AnswerDate] ,
[QuestionScore] ,
[ObjectID] ,
[CreateDateTime] ,
[LastUpdateDateTime] ,
[IsDeleted]
FROM [Activity].[dbo].[QuestionnaireAnswerRecord] WITH(NOLOCK)
WHERE IsDeleted = 0
AND 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(true);
sqlCmd = new SqlCommand(sql);
sqlCmd.Parameters.AddRange(paras.ToArray());
return(await dbHelper.ExecuteFetchAsync <QuestionnaireAnswerRecordModel>(sqlCmd));
}
catch (Exception ex)
{
Logger.Error($"DB异常=>GetQuestionnaireAnswerRecordInfo=>{ex.ToString()}");
return(null);
}
finally
{
dbHelper?.Dispose();
sqlCmd?.Dispose();
}
}
public static bool UpdateVehicleTypeCertificationInfo(BaseDbHelper db, VehicleAuditInfoModel request)
{
using (var cmd =
new SqlCommand(
@"Update Tuhu_Profiles..VehicleTypeCertificationInfo WITH(ROWLOCK) SET Vehicle_license_img=@Vehicle_license_img,User_IdCard_img=@User_IdCard_img where CarId=@CarId")
)
{
cmd.Parameters.AddWithValue("@Vehicle_license_img", request.ImageUrl);
cmd.Parameters.AddWithValue("@User_IdCard_img", request.IdCardUrl);
cmd.Parameters.AddWithValue("@CarId", request.CarId);
cmd.CommandType = CommandType.Text;
return(db.ExecuteNonQuery(cmd) > 0);
}
}
/// <summary>
/// 根据问题获取问题的所有选项
/// </summary>
/// <param name="questionId"></param>
/// <returns></returns>
public static async Task <IEnumerable <QuestionOptionModel> > GetQuestionOptionListByQuestionId(long questionId)
{
#region SQL
string sql = @" SELECT [PKID] AS OptionID ,
[QuestionnaireID] ,
[QuestionID] ,
[OptionContent] ,
[OptionPicSrc] ,
[NoOptionScore] ,
[YesOptionScore] ,
[IsRightValue] ,
[ForkQuestionID] ,
[Sort] ,
[CreateDateTime] ,
[LastUpdateDateTime] ,
[IsDeleted],
ISNULL([IsAdditionalInfo],0) AS [IsAdditionalInfo],
ISNULL([IsShowAdditionalInfo],0) AS [IsShowAdditionalInfo],
[QuestionParentID],
[UseIntegral],
[WinCouponCount]
FROM [Activity].[dbo].[QuestionOption] WITH ( NOLOCK )
WHERE IsDeleted = 0
AND QuestionID = @QuestionID
ORDER BY Sort ASC ,PKID asc";
#endregion
BaseDbHelper dbHelper = null;
SqlCommand sqlCmd = null;
try
{
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@QuestionID", questionId) };
dbHelper = DbHelper.CreateDbHelper(true);
sqlCmd = new SqlCommand(sql);
sqlCmd.Parameters.AddRange(paras.ToArray());
return(await dbHelper.ExecuteSelectAsync <QuestionOptionModel>(sqlCmd));
}
catch (Exception ex)
{
Logger.Error($"DB异常=>{nameof(GetQuestionOptionListByQuestionId)}=>{ex.ToString()}");
return(null);
}
finally
{
dbHelper?.Dispose();
sqlCmd?.Dispose();
}
}
/// <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> 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);
}
}
public static bool UpdateCarObject(BaseDbHelper db, VehicleAuditInfoModel request)
{
using (var cmd =
new SqlCommand(
@"Update Tuhu_Profiles..CarObject WITH(ROWLOCK) SET VinCode=@VinCode,ClassNo=@ClassNo,u_carno=@CarNo,Engineno=@EngineNo where CarId=@CarId")
)
{
cmd.Parameters.AddWithValue("@VinCode", request.VinCode);
cmd.Parameters.AddWithValue("@CarNo", request.CarNumber);
cmd.Parameters.AddWithValue("@CarId", request.CarId);
cmd.Parameters.AddWithValue("@ClassNo", request.VinCode);
cmd.Parameters.AddWithValue("@EngineNo", request.EngineNo);
cmd.CommandType = CommandType.Text;
return(db.ExecuteNonQuery(cmd) > 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 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 = helper.ExecuteNonQuery(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="orderID"></param>
/// <returns></returns>
public static async Task <UserQuestionnaireURLModel> GetUserQuestionnaireURLInfoByOrderID(int orderID)
{
#region SQL
string sql = @"SELECT [PKID]
,[PageID]
,[QuestionnaireNo]
,[OrderID]
,[ComplaintsID]
,[ComplaintsType]
,[IsAtStore]
,[Department]
,[UserID]
,[UserPhone]
,[StaffEmail]
,[ShortURL]
,[OriginalURL]
,[CreateDateTime]
,[LastUpdateDateTime]
,[IsDeleted]
FROM [Activity].[dbo].[UserQuestionnaireURL] WITH(NOLOCK)
WHERE IsDeleted=0
AND OrderID=@OrderID";
#endregion
BaseDbHelper dbHelper = null;
SqlCommand sqlCmd = null;
try
{
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OrderID", orderID) };
dbHelper = DbHelper.CreateDbHelper(true);
sqlCmd = new SqlCommand(sql);
sqlCmd.Parameters.AddRange(paras.ToArray());
return(await dbHelper.ExecuteFetchAsync <UserQuestionnaireURLModel>(sqlCmd));
}
catch (Exception ex)
{
Logger.Error($"DB异常=>GetUserQuestionnaireURLInfoByOrderID=>{ex.ToString()}");
return(null);
}
finally
{
dbHelper?.Dispose();
sqlCmd?.Dispose();
}
}
/// <summary>
/// 获取问卷和定责部门关系信息
/// </summary>
/// <param name="department"></param>
/// <param name="complaintsType"></param>
/// <param name="isAtStore"></param>
/// <returns></returns>
public static async Task <QuestionnaireDptMappingModel> GetQuestionnaireDptMappingInfo(string department, string complaintsType, int isAtStore)
{
#region SQL
string sql = @" SELECT TOP 1
[PKID] ,
[QuestionnaireNo] ,
[Department] ,
[ComplaintsType] ,
[IsAtStore] ,
[CreateDateTime] ,
[LastUpdateDateTime] ,
[IsDeleted]
FROM [Activity].[dbo].[QuestionnaireDptMapping] WITH ( NOLOCK )
WHERE IsDeleted = 0
AND Department = @Department
AND ComplaintsType = ISNULL(@ComplaintsType,'')
AND IsAtStore = ISNULL(@IsAtStore,0);";
#endregion
BaseDbHelper dbHelper = null;
SqlCommand sqlCmd = null;
try
{
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@Department", department),
new SqlParameter("@ComplaintsType", complaintsType),
new SqlParameter("@IsAtStore", isAtStore)
};
dbHelper = DbHelper.CreateDbHelper(true);
sqlCmd = new SqlCommand(sql);
sqlCmd.Parameters.AddRange(paras.ToArray());
return(await dbHelper.ExecuteFetchAsync <QuestionnaireDptMappingModel>(sqlCmd));
}
catch (Exception ex)
{
Logger.Error($"DB异常=>GetQuestionnaireDptMappingInfo=>{ex.ToString()}");
return(null);
}
finally
{
dbHelper?.Dispose();
sqlCmd?.Dispose();
}
}
/// <summary>
/// 保存公众号领红包统计
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="setting"></param>
/// <returns></returns>
public static async Task <long> InsertOARedEnvelopeStatisticsAsync(BaseDbHelper dbHelper,
OARedEnvelopeStatisticsModel setting)
{
var sql = @"INSERT INTO [Activity].[dbo].[tbl_OARedEnvelopeStatistics]
([StatisticsDate]
,[DayMaxMoney]
,[UserCount]
,[RedEnvelopeCount]
,[RedEnvelopeSumMoney]
,[RedEnvelopeAvg]
,[OfficialAccountType]
,[CreateDatetime]
,[LastUpdateDateTime])
VALUES
(
@StatisticsDate
,@DayMaxMoney
,@UserCount
,@RedEnvelopeCount
,@RedEnvelopeSumMoney
,@RedEnvelopeAvg
,@OfficialAccountType
,getdate()
,getdate()
);
SELECT SCOPE_IDENTITY();
";
using (var cmd = new SqlCommand(sql))
{
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.ExecuteScalarAsync(cmd);
return(Convert.ToInt64(result));
}
}
/// <summary>
/// 新增 - 小游戏 - 马牌用户助力
/// </summary>
/// <returns></returns>
public static async Task <long> InsertGameMaPaiUserSupportAsync(BaseDbHelper dbHelper,
GameMaPaiUserSupportModel data)
{
var sql = @" insert into Activity.[dbo].[tbl_GameMaPaiUserSupport]
(
[ActivityId]
,[UserId]
,[SupportOpenId]
,[SupportNickName]
,[SupportHeadImgURL]
,[Distance]
,[CreateDatetime]
,[LastUpdateDateTime]
)
values (
@ActivityId,
@userId,
@SupportOpenId,
@SupportNickName,
@SupportHeadImgURL,
@Distance,
getdate(),
getdate()
);
SELECT SCOPE_IDENTITY();
";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameter("@ActivityId", data.ActivityId);
cmd.AddParameter("@userId", data.UserId);
cmd.AddParameter("@SupportOpenId", data.SupportOpenId ?? "");
cmd.AddParameter("@SupportNickName", data.SupportNickName ?? "");
cmd.AddParameter("@SupportHeadImgURL", data.SupportHeadImgURL ?? "");
cmd.AddParameter("@Distance", data.Distance);
var result = await dbHelper.ExecuteScalarAsync(cmd);
return(Convert.ToInt64(result));
}
}
/// <summary>
/// 根据问卷类型获取问卷信息
/// </summary>
/// <param name="questionnaireType">问卷类型，1=售后问卷；2=售前问卷; 5=2018世界杯</param>
/// <returns></returns>
public static async Task <QuestionnaireModel> GetQuestionnaireInfoByType(int questionnaireType)
{
#region SQL
string sql = @"SELECT Top 1
[PKID] AS QuestionnaireID
,[QuestionnaireNo]
,[QuestionnaireName]
,[QuestionnaireType]
,[IsShowRegulation]
,[Regulation]
,[ParticipantsCount]
,[Sort]
,[CreateDateTime]
,[LastUpdateDateTime]
,[IsDeleted]
FROM [Activity].[dbo].[Questionnaire] WITH ( NOLOCK )
WHERE IsDeleted=0 AND QuestionnaireType=@QuestionnaireType
ORDER BY CreateDateTime DESC";
#endregion
BaseDbHelper dbHelper = null;
SqlCommand sqlCmd = null;
try
{
SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@QuestionnaireType", questionnaireType) };
dbHelper = DbHelper.CreateDbHelper(true);
sqlCmd = new SqlCommand(sql);
sqlCmd.Parameters.AddRange(paras.ToArray());
return(await dbHelper.ExecuteFetchAsync <QuestionnaireModel>(sqlCmd));
}
catch (Exception ex)
{
Logger.Error($"DB异常=>GetQuestionnaireInfoByType=>{ex.ToString()}");
return(null);
}
finally
{
dbHelper?.Dispose();
sqlCmd?.Dispose();
}
}
/// <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);
}
}
