/// <summary>
/// 以分页的方式查询实体信息
/// </summary>
public QueryResultDTO<Paper> Query(QueryArgsDTO<Paper> queryDTO)
{
QueryResultDTO<Paper> resultDTO = new QueryResultDTO<Paper>();
using (DbConnection connection = ConnectionManager.OpenConnection)
{
const string sqlBase = "SELECT * FROM Paper WHERE IsDeleted = 0 {0} ORDER BY AddTime DESC LIMIT @StartIndex,@PageSize;";
StringBuilder sqlWhereBuilder = new StringBuilder();
Dictionary<string, object> parameterDictionary = new Dictionary<string, object>();
if (queryDTO.Model.CourseID != -1)
{
sqlWhereBuilder.Append("AND CourseID = @CourseID ");
parameterDictionary.Add("CourseID", queryDTO.Model.CourseID);
}
if (Enum.IsDefined(typeof(PaperType), queryDTO.Model.PaperType))
{
sqlWhereBuilder.Append("AND PaperType = @PaperType ");
parameterDictionary.Add("PaperType", queryDTO.Model.PaperType);
}
if (!string.IsNullOrEmpty(queryDTO.Model.Name))
{
sqlWhereBuilder.Append("AND Name LIKE CONCAT('%',@Name,'%') ");
parameterDictionary.Add("Name", queryDTO.Model.Name);
}
// Pagination (start with 0 in mysql)
int pageSize = queryDTO.PageSize;
int startIndex = pageSize * (queryDTO.PageIndex - 1);
parameterDictionary.Add("StartIndex", startIndex);
parameterDictionary.Add("PageSize", pageSize);
// Execute pagination sql.
string paginationSql = string.Format(sqlBase, sqlWhereBuilder);
var dynamicParameters = new DynamicParameters(parameterDictionary);
resultDTO.List = connection.Query<Paper>(paginationSql, dynamicParameters).ToList();
// Sets paginatiion
resultDTO.PageSize = queryDTO.PageSize;
resultDTO.PageIndex = queryDTO.PageIndex;
// Sets total record with same where sql string.
const string sqlCountBase = "SELECT COUNT(*) FROM Paper WHERE IsDeleted = 0 {0}";
string sqlCount = string.Format(sqlCountBase, sqlWhereBuilder);
int count = Convert.ToInt32(connection.ExecuteScalar(sqlCount, dynamicParameters, null, null, null));
resultDTO.TotalRecordCount = count;
}
return resultDTO;
}
private CorporationListViewModel InitializeCorporationListViewModel(QueryResultDTO <CorporationDTO, CorporationFilterDTO> result)
{
var finalList = result.PagedResult();
foreach (CorporationDTO corporation in finalList)
{
corporation.SumRating = RatingHelper.CountAverageRating(corporation.RatingCount, corporation.SumRating);
}
return(new CorporationListViewModel
{
Corporations = new List <CorporationDTO>(finalList),
Filter = result.Filter,
CurrentPageIndex = result.RequestedPageNumber.HasValue ? (int)result.RequestedPageNumber : 1,
PageCount = (int)Math.Ceiling(result.TotalItemsCount / (double)result.PageSize)
});
}
private FreelancerListViewModel InitializeFreelancerListViewModel(QueryResultDTO <FreelancerDTO, FreelancerFilterDTO> result)
{
var finalList = result.PagedResult();
foreach (FreelancerDTO freelancer in finalList)
{
freelancer.SumRating = RatingHelper.CountAverageRating(freelancer.RatingCount, freelancer.SumRating);
}
return(new FreelancerListViewModel
{
Freelancers = new List <FreelancerDTO>(finalList),
Filter = result.Filter,
CurrentPageIndex = result.RequestedPageNumber.HasValue ? (int)result.RequestedPageNumber : 1,
PageCount = (int)Math.Ceiling(result.TotalItemsCount / (double)result.PageSize)
});
}
/// <summary>
/// 以分页的形式查询不定项选择题
/// </summary>
public ServiceInvokeDTO<QueryResultDTO<UncertainItemDTO>> QueryUncertain(QueryArgsDTO<UncertainItem> queryDTO, int courseID)
{
log.Debug(Constant.DEBUG_START);
ServiceInvokeDTO<QueryResultDTO<UncertainItemDTO>> result = null;
try
{
QueryResultDTO<UncertainItemDTO> resultData = null;
// -->DTO
QueryResultDTO<UncertainItem> queryData = uncertainDAL.Query(queryDTO, courseID);
if (queryData != null)
{
resultData = new QueryResultDTO<UncertainItemDTO>();
resultData.PageIndex = queryData.PageIndex;
resultData.PageSize = queryData.PageSize;
resultData.TotalRecordCount = queryData.TotalRecordCount;
List<UncertainItemDTO> dtos = new List<UncertainItemDTO>();
if (queryData.List != null)
{
foreach (var uncertain in queryData.List)
{
UncertainItemDTO uncertainDTO = new UncertainItemDTO(uncertain);
uncertainDTO.ChapterName = chapterDAL.GetByID(uncertain.ChapterID).Name;
uncertainDTO.SubChoices = uncertainSubChoiceDAL.GetByUncertainItemID(uncertain.ID);
dtos.Add(uncertainDTO);
}
}
resultData.List = dtos;
}
result = new ServiceInvokeDTO<QueryResultDTO<UncertainItemDTO>>(InvokeCode.SYS_INVOKE_SUCCESS, resultData);
}
catch (Exception ex)
{
log.Error(ex);
result = new ServiceInvokeDTO<QueryResultDTO<UncertainItemDTO>>(InvokeCode.SYS_INNER_ERROR);
}
log.Debug(Constant.DEBUG_END);
return result;
}
/// <summary>
/// 以分页的形式查询单选题
/// </summary>
public ServiceInvokeDTO<QueryResultDTO<SingleItemDTO>> QuerySingle(QueryArgsDTO<SingleItem> queryDTO, int courseID)
{
log.Debug(Constant.DEBUG_START);
ServiceInvokeDTO<QueryResultDTO<SingleItemDTO>> result = null;
try
{
QueryResultDTO<SingleItemDTO> resultData = null;
// -->DTO
QueryResultDTO<SingleItem> queryData = singleDAL.Query(queryDTO, courseID);
if (queryData != null)
{
resultData = new QueryResultDTO<SingleItemDTO>();
resultData.PageIndex = queryData.PageIndex;
resultData.PageSize = queryData.PageSize;
resultData.TotalRecordCount = queryData.TotalRecordCount;
List<SingleItemDTO> dtos = new List<SingleItemDTO>();
if (queryData.List != null)
{
foreach (var single in queryData.List)
{
SingleItemDTO singleDTO = new SingleItemDTO(single);
singleDTO.ChapterName = chapterDAL.GetByID(single.ChapterID).Name;
dtos.Add(singleDTO);
}
}
resultData.List = dtos;
}
result = new ServiceInvokeDTO<QueryResultDTO<SingleItemDTO>>(InvokeCode.SYS_INVOKE_SUCCESS, resultData);
}
catch (Exception ex)
{
log.Error(ex);
result = new ServiceInvokeDTO<QueryResultDTO<SingleItemDTO>>(InvokeCode.SYS_INNER_ERROR);
}
log.Debug(Constant.DEBUG_END);
return result;
}
/// <summary>
/// 以分页的形式查询数字填空题
/// </summary>
public ServiceInvokeDTO<QueryResultDTO<NumberBlankItemDTO>> QueryNumberBlank(QueryArgsDTO<NumberBlankItem> queryDTO, int courseID)
{
log.Debug(Constant.DEBUG_START);
ServiceInvokeDTO<QueryResultDTO<NumberBlankItemDTO>> result = null;
try
{
QueryResultDTO<NumberBlankItemDTO> resultData = null;
// -->DTO
QueryResultDTO<NumberBlankItem> queryData = numberBlankDAL.Query(queryDTO, courseID);
if (queryData != null)
{
resultData = new QueryResultDTO<NumberBlankItemDTO>();
resultData.PageIndex = queryData.PageIndex;
resultData.PageSize = queryData.PageSize;
resultData.TotalRecordCount = queryData.TotalRecordCount;
List<NumberBlankItemDTO> dtos = new List<NumberBlankItemDTO>();
if (queryData.List != null)
{
foreach (var numberBlank in queryData.List)
{
NumberBlankItemDTO numberBlankDTO = new NumberBlankItemDTO(numberBlank);
numberBlankDTO.ChapterName = chapterDAL.GetByID(numberBlank.ChapterID).Name;
numberBlankDTO.Answers = numberBlankDAL.GetAnswers(numberBlank.ID);
dtos.Add(numberBlankDTO);
}
}
resultData.List = dtos;
}
result = new ServiceInvokeDTO<QueryResultDTO<NumberBlankItemDTO>>(InvokeCode.SYS_INVOKE_SUCCESS, resultData);
}
catch (Exception ex)
{
log.Error(ex);
result = new ServiceInvokeDTO<QueryResultDTO<NumberBlankItemDTO>>(InvokeCode.SYS_INNER_ERROR);
}
log.Debug(Constant.DEBUG_END);
return result;
}
/// <summary>
/// 以分页的方式查询实体信息
/// </summary>
public QueryResultDTO<JudgeItem> Query(QueryArgsDTO<JudgeItem> queryDTO, int courseID)
{
QueryResultDTO<JudgeItem> resultDTO = new QueryResultDTO<JudgeItem>();
using (DbConnection connection = ConnectionManager.OpenConnection)
{
const string sqlBase = "SELECT * FROM JudgeItem WHERE IsDeleted = 0 {0} ORDER BY AddTime DESC LIMIT @StartIndex,@PageSize;";
StringBuilder sqlWhereBuilder = new StringBuilder();
Dictionary<string, object> parameterDictionary = new Dictionary<string, object>();
if (queryDTO.Model.AgencyID != -1)
{
sqlWhereBuilder.Append("AND AgencyID = @AgencyID ");
parameterDictionary.Add("AgencyID", queryDTO.Model.AgencyID);
}
if (queryDTO.Model.ChapterID != -1)
{
sqlWhereBuilder.Append("AND ChapterID = @ChapterID ");
parameterDictionary.Add("ChapterID", queryDTO.Model.ChapterID);
}
else
{
// 查询本课程的所有章节
sqlWhereBuilder.Append("AND ChapterID IN (SELECT ID FROM Chapter WHERE CourseID = @CourseID) ");
parameterDictionary.Add("CourseID", courseID);
}
if (queryDTO.Model.IsVipItem != -1)
{
sqlWhereBuilder.Append("AND IsVipItem = @IsVipItem ");
parameterDictionary.Add("IsVipItem", queryDTO.Model.IsVipItem);
}
if (!string.IsNullOrEmpty(queryDTO.Model.Title))
{
sqlWhereBuilder.Append("AND Title LIKE CONCAT('%',@Title,'%') ");
parameterDictionary.Add("Title", queryDTO.Model.Title);
}
if (queryDTO.Model.Difficulty != -1)
{
sqlWhereBuilder.Append("AND Difficulty = @Difficulty ");
parameterDictionary.Add("Difficulty", queryDTO.Model.Difficulty);
}
if (!string.IsNullOrEmpty(queryDTO.Model.AddPerson))
{
sqlWhereBuilder.Append("AND AddPerson LIKE CONCAT('%',@AddPerson,'%') ");
parameterDictionary.Add("AddPerson", queryDTO.Model.AddPerson);
}
// Pagination (start with 0 in mysql)
int pageSize = queryDTO.PageSize;
int startIndex = pageSize * (queryDTO.PageIndex - 1);
parameterDictionary.Add("StartIndex", startIndex);
parameterDictionary.Add("PageSize", pageSize);
// Execute pagination sql.
string paginationSql = string.Format(sqlBase, sqlWhereBuilder);
var dynamicParameters = new DynamicParameters(parameterDictionary);
resultDTO.List = connection.Query<JudgeItem>(paginationSql, dynamicParameters).ToList();
// Sets paginatiion
resultDTO.PageSize = queryDTO.PageSize;
resultDTO.PageIndex = queryDTO.PageIndex;
// Sets total record with same where sql string.
const string sqlCountBase = "SELECT COUNT(*) FROM JudgeItem WHERE IsDeleted = 0 {0}";
string sqlCount = string.Format(sqlCountBase, sqlWhereBuilder);
int count = Convert.ToInt32(connection.ExecuteScalar(sqlCount, dynamicParameters, null, null, null));
resultDTO.TotalRecordCount = count;
}
return resultDTO;
}
/// <summary>
/// 以分页的方式查询实体信息
/// </summary>
public QueryResultDTO<TeacherDoRecord> Query(QueryArgsDTO<TeacherDoRecord> queryDTO, DateTime startDate, DateTime endDate)
{
QueryResultDTO<TeacherDoRecord> resultDTO = new QueryResultDTO<TeacherDoRecord>();
using (DbConnection connection = ConnectionManager.OpenConnection)
{
const string sqlBase = "SELECT * FROM AdminDoRecord WHERE IsDeleted = 0 {0} ORDER BY AddTime DESC LIMIT @StartIndex,@PageSize;";
StringBuilder sqlWhereBuilder = new StringBuilder();
Dictionary<string, object> parameterDictionary = new Dictionary<string, object>();
if (!string.IsNullOrEmpty(queryDTO.Model.TeacherName))
{
sqlWhereBuilder.Append("AND TeacherName LIKE CONCAT('%',@TeacherName,'%') ");
parameterDictionary.Add("TeacherName", queryDTO.Model.TeacherName);
}
if (!string.IsNullOrEmpty(queryDTO.Model.DoName))
{
sqlWhereBuilder.Append("AND DoName LIKE CONCAT('%',@DoName,'%') ");
parameterDictionary.Add("DoName", queryDTO.Model.DoName);
}
if (startDate != DateTime.MinValue && endDate != DateTime.MinValue)
{
// 根据操作日期范围查询
sqlWhereBuilder.Append("AND DATE(DoTime) BETWEEN @StartDate AND @EndDate ");
parameterDictionary.Add("StartDate", startDate.ToString("yyyy-MM-dd"));
parameterDictionary.Add("EndDate", endDate.ToString("yyyy-MM-dd"));
}
// Pagination (start with 0 in mysql)
int pageSize = queryDTO.PageSize;
int startIndex = pageSize * (queryDTO.PageIndex - 1);
parameterDictionary.Add("StartIndex", startIndex);
parameterDictionary.Add("PageSize", pageSize);
// Execute pagination sql.
string paginationSql = string.Format(sqlBase, sqlWhereBuilder);
var dynamicParameters = new DynamicParameters(parameterDictionary);
resultDTO.List = connection.Query<TeacherDoRecord>(paginationSql, dynamicParameters).ToList();
// Sets paginatiion
resultDTO.PageSize = queryDTO.PageSize;
resultDTO.PageIndex = queryDTO.PageIndex;
// Sets total record with same where sql string.
const string sqlCountBase = "SELECT COUNT(*) FROM AdminDoRecord WHERE IsDeleted = 0 {0}";
string sqlCount = string.Format(sqlCountBase, sqlWhereBuilder);
int count = Convert.ToInt32(connection.ExecuteScalar(sqlCount, dynamicParameters, null, null, null));
resultDTO.TotalRecordCount = count;
}
return resultDTO;
}
