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