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