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