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