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