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