/// <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;
}
/// <summary>
/// 以分页的形式查询考卷信息
/// </summary>
public ServiceInvokeDTO<QueryResultDTO<Paper>> QueryPaper(QueryArgsDTO<Paper> queryDTO)
{
log.Debug(Constant.DEBUG_START);
ServiceInvokeDTO<QueryResultDTO<Paper>> result = null;
try
{
QueryResultDTO<Paper> resultData = paperDAL.Query(queryDTO);
result = new ServiceInvokeDTO<QueryResultDTO<Paper>>(InvokeCode.SYS_INVOKE_SUCCESS, resultData);
}
catch (Exception ex)
{
log.Error(ex);
result = new ServiceInvokeDTO<QueryResultDTO<Paper>>(InvokeCode.SYS_INNER_ERROR);
}
log.Debug(Constant.DEBUG_END);
return result;
}
/// <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;
}
public ActionResult QueryWord()
{
log.Debug(Constant.DEBUG_START);
string pageSizeString = ApiQueryUtil.QueryArgByGet("limit");
string offsetString = ApiQueryUtil.QueryArgByGet("offset");
string chapterIDString = ApiQueryUtil.QueryArgByGet("chapter_id");
string title = ApiQueryUtil.QueryArgByGet("title");
string difficultyString = ApiQueryUtil.QueryArgByGet("difficulty");
string addPerson = ApiQueryUtil.QueryArgByGet("add_person");
QueryResultDTO<WordItemDTO> queryData = null;
try
{
QueryArgsDTO<WordItem> queryDTO = new QueryArgsDTO<WordItem>();
queryDTO.PageSize = Convert.ToInt32(pageSizeString);
queryDTO.PageIndex = Convert.ToInt32(offsetString) / Convert.ToInt32(pageSizeString) + 1;
queryDTO.Model.ChapterID = Convert.ToInt32(chapterIDString);
queryDTO.Model.Title = title;
queryDTO.Model.Difficulty = string.IsNullOrEmpty(difficultyString) ? -1 : Convert.ToInt32(difficultyString);
queryDTO.Model.AddPerson = addPerson;
int courseID = (Session[Constant.SESSION_KEY_COURSE] as Course).ID;
queryData = itemDataService.QueryWord(queryDTO, courseID).Data;
}
catch (Exception ex)
{
log.Error(ex);
}
string json = JsonConvert.SerializeObject(queryData, Formatting.Indented, Constant.TIME_CONVERTER);
log.Debug(Constant.DEBUG_END);
return Content(json, Constant.JSON_MIME_TYPE);
}
public ActionResult QueryCourse()
{
log.Debug(Constant.DEBUG_START);
string pageSizeString = ApiQueryUtil.QueryArgByGet("limit");
string offsetString = ApiQueryUtil.QueryArgByGet("offset");
string name = ApiQueryUtil.QueryArgByGet("name");
QueryResultDTO<Course> queryData = null;
try
{
QueryArgsDTO<Course> queryDTO = new QueryArgsDTO<Course>();
queryDTO.PageSize = Convert.ToInt32(pageSizeString);
queryDTO.PageIndex = Convert.ToInt32(offsetString) / Convert.ToInt32(pageSizeString) + 1;
queryDTO.Model.Name = name;
queryData = itemDataService.QueryCourse(queryDTO).Data;
}
catch (Exception ex)
{
log.Error(ex);
}
string json = JsonConvert.SerializeObject(queryData, Formatting.Indented, Constant.TIME_CONVERTER);
log.Debug(Constant.DEBUG_END);
return Content(json, Constant.JSON_MIME_TYPE);
}
/// <summary>
/// 分页查询云笔记信息
/// </summary>
private HttpResponseMessage QueryNote(HttpRequestMessage request)
{
log.Debug(Constant.DEBUG_START);
string sign = ApiQueryUtil.QueryHeader(Constant.HTTP_HEADER_SIGN);
string cmd = ApiQueryUtil.QueryHeader(Constant.HTTP_HEADER_CMD);
string random = ApiQueryUtil.QueryHeader(Constant.HTTP_HEADER_RANDOM);
string pageIndexString = ApiQueryUtil.QueryArgByGet("page_index");
string pageSizeString = ApiQueryUtil.QueryArgByGet("page_size");
string userIDString = ApiQueryUtil.QueryArgByGet("user_id");
string title = ApiQueryUtil.QueryArgByGet("title");
string content = ApiQueryUtil.QueryArgByGet("content");
string startDateString = ApiQueryUtil.QueryArgByGet("start_date");
string endDateString = ApiQueryUtil.QueryArgByGet("end_date");
Dictionary<string, string> args = new Dictionary<string, string>()
{
{ Constant.HTTP_HEADER_CMD, cmd},
{ Constant.HTTP_HEADER_RANDOM, random},
{ "page_index", pageIndexString},
{ "page_size", pageSizeString},
{ "user_id", userIDString},
{ "title", title },
{ "content", content },
{ "start_date", startDateString },
{ "end_date", endDateString }
}.OrderBy(element => element.Key).ToDictionary(o => o.Key, p => p.Value);
ServiceInvokeDTO<QueryResultDTO<Note>> result = null;
try
{
// Check sign
if (securityService.CheckSign(args, Config.ApiSignSecretKey, sign))
{
QueryArgsDTO<Note> queryDTO = new QueryArgsDTO<Note>();
queryDTO.PageIndex = Convert.ToInt32(pageIndexString);
queryDTO.PageSize = Convert.ToInt32(pageSizeString);
queryDTO.Model.UserID = string.IsNullOrEmpty(userIDString) ? -1 : Convert.ToInt32(userIDString);
queryDTO.Model.Title = title;
queryDTO.Model.Content = content;
DateTime startDate = string.IsNullOrEmpty(startDateString) ? DateTime.MinValue : Convert.ToDateTime(startDateString);
DateTime endDate = string.IsNullOrEmpty(endDateString) ? DateTime.MinValue : Convert.ToDateTime(endDateString);
result = userDataService.QueryNote(queryDTO, startDate, endDate);
}
else
{
result = new ServiceInvokeDTO<QueryResultDTO<Note>>(InvokeCode.SYS_SIGN_ERROR);
}
}
catch (Exception ex)
{
log.Error(ex);
result = new ServiceInvokeDTO<QueryResultDTO<Note>>(InvokeCode.SYS_INNER_ERROR);
}
log.Debug(Constant.DEBUG_END);
return request.CreateResponse(HttpStatusCode.OK, 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 ServiceInvokeDTO<QueryResultDTO<AdminDoRecord>> QueryAdminDoRecord(QueryArgsDTO<AdminDoRecord> queryDTO, int agencyID, DateTime startDate, DateTime endDate)
{
log.Debug(Constant.DEBUG_START);
ServiceInvokeDTO<QueryResultDTO<AdminDoRecord>> result = null;
try
{
QueryResultDTO<AdminDoRecord> queryData = adminDoRecordDAL.Query(queryDTO, agencyID, startDate, endDate);
result = new ServiceInvokeDTO<QueryResultDTO<AdminDoRecord>>(InvokeCode.SYS_INVOKE_SUCCESS, queryData);
}
catch (Exception ex)
{
log.Error(ex);
result = new ServiceInvokeDTO<QueryResultDTO<AdminDoRecord>>(InvokeCode.SYS_INNER_ERROR);
}
log.Debug(Constant.DEBUG_END);
return result;
}
/// <summary>
/// 分页查询联系人
/// </summary>
private HttpResponseMessage QueryContact(HttpRequestMessage request)
{
log.Debug(Constant.DEBUG_START);
string sign = ApiQueryUtil.QueryHeader(Constant.HTTP_HEADER_SIGN);
string cmd = ApiQueryUtil.QueryHeader(Constant.HTTP_HEADER_CMD);
string random = ApiQueryUtil.QueryHeader(Constant.HTTP_HEADER_RANDOM);
string pageIndexString = ApiQueryUtil.QueryArgByGet("page_index");
string pageSizeString = ApiQueryUtil.QueryArgByGet("page_size");
string userIDString = ApiQueryUtil.QueryArgByGet("user_id");
string name = ApiQueryUtil.QueryArgByGet("name");
string phone = ApiQueryUtil.QueryArgByGet("phone");
Dictionary<string, string> args = new Dictionary<string, string>()
{
{ Constant.HTTP_HEADER_CMD, cmd},
{ Constant.HTTP_HEADER_RANDOM, random},
{ "page_index", pageIndexString},
{ "page_size", pageSizeString},
{ "user_id", userIDString},
{ "name", name },
{ "phone", phone }
}.OrderBy(element => element.Key).ToDictionary(o => o.Key, p => p.Value);
ServiceInvokeDTO<QueryResultDTO<Contact>> result = null;
try
{
// Check sign
if (securityService.CheckSign(args, Config.ApiSignSecretKey, sign))
{
QueryArgsDTO<Contact> queryDTO = new QueryArgsDTO<Contact>();
queryDTO.PageIndex = Convert.ToInt32(pageIndexString);
queryDTO.PageSize = Convert.ToInt32(pageSizeString);
queryDTO.Model.UserID = string.IsNullOrEmpty(userIDString) ? -1 : Convert.ToInt32(userIDString);
queryDTO.Model.Name = name;
queryDTO.Model.Phone = phone;
result = userDataService.QueryContact(queryDTO);
}
else
{
result = new ServiceInvokeDTO<QueryResultDTO<Contact>>(InvokeCode.SYS_SIGN_ERROR);
}
}
catch (Exception ex)
{
log.Error(ex);
result = new ServiceInvokeDTO<QueryResultDTO<Contact>>(InvokeCode.SYS_INNER_ERROR);
}
log.Debug(Constant.DEBUG_END);
return request.CreateResponse(HttpStatusCode.OK, result);
}
public ActionResult QueryDoRecord()
{
log.Debug(Constant.DEBUG_START);
string pageSizeString = ApiQueryUtil.QueryArgByGet("limit");
string offsetString = ApiQueryUtil.QueryArgByGet("offset");
string chineseName = ApiQueryUtil.QueryArgByGet("chinese_name");
string doName = ApiQueryUtil.QueryArgByGet("do_name");
string startDateString = ApiQueryUtil.QueryArgByGet("start_date");
string endDateString = ApiQueryUtil.QueryArgByGet("end_date");
QueryResultDTO<AdminDoRecord> queryData = null;
try
{
QueryArgsDTO<AdminDoRecord> queryDTO = new QueryArgsDTO<AdminDoRecord>();
queryDTO.PageSize = Convert.ToInt32(pageSizeString);
queryDTO.PageIndex = Convert.ToInt32(offsetString) / Convert.ToInt32(pageSizeString) + 1;
queryDTO.Model.AdminName = chineseName;
queryDTO.Model.DoName = doName;
int agencyID = (Session[Constant.SESSION_KEY_ADMIN] as AgencyAdminDTO).Agency.ID;
DateTime startDate = string.IsNullOrEmpty(startDateString) ? DateTime.MinValue : Convert.ToDateTime(startDateString);
DateTime endDate = string.IsNullOrEmpty(endDateString) ? DateTime.MinValue : Convert.ToDateTime(endDateString);
queryData = recordDataService.QueryAdminDoRecord(queryDTO, agencyID, startDate, endDate).Data;
}
catch (Exception ex)
{
log.Error(ex);
}
string json = JsonConvert.SerializeObject(queryData, Formatting.Indented, Constant.TIME_CONVERTER);
log.Debug(Constant.DEBUG_END);
return Content(json, Constant.JSON_MIME_TYPE);
}
public ActionResult QueryAdmin()
{
log.Debug(Constant.DEBUG_START);
string pageSizeString = ApiQueryUtil.QueryArgByGet("limit");
string offsetString = ApiQueryUtil.QueryArgByGet("offset");
string chineseName = ApiQueryUtil.QueryArgByGet("chinese_name");
string phone = ApiQueryUtil.QueryArgByGet("phone");
QueryResultDTO<AgencyAdmin> queryData = null;
try
{
QueryArgsDTO<AgencyAdmin> queryDTO = new QueryArgsDTO<AgencyAdmin>();
queryDTO.PageSize = Convert.ToInt32(pageSizeString);
queryDTO.PageIndex = Convert.ToInt32(offsetString) / Convert.ToInt32(pageSizeString) + 1;
queryDTO.Model.AgencyID = (Session[Constant.SESSION_KEY_ADMIN] as AgencyAdminDTO).Agency.ID;
queryDTO.Model.ChineseName = chineseName;
queryDTO.Model.Phone = phone;
queryDTO.Model.Level = (AdminLevel)(-1);
queryData = agencyDataService.QueryAdmin(queryDTO).Data;
}
catch (Exception ex)
{
log.Error(ex);
}
string json = JsonConvert.SerializeObject(queryData, Formatting.Indented, Constant.TIME_CONVERTER);
log.Debug(Constant.DEBUG_END);
return Content(json, Constant.JSON_MIME_TYPE);
}
/// <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;
}
public ActionResult QueryTeacher()
{
log.Debug(Constant.DEBUG_START);
string pageSizeString = ApiQueryUtil.QueryArgByGet("limit");
string offsetString = ApiQueryUtil.QueryArgByGet("offset");
string chineseName = ApiQueryUtil.QueryArgByGet("chinese_name");
QueryResultDTO<Teacher> queryData = null;
try
{
QueryArgsDTO<Teacher> queryDTO = new QueryArgsDTO<Teacher>();
queryDTO.PageSize = Convert.ToInt32(pageSizeString);
queryDTO.PageIndex = Convert.ToInt32(offsetString) / Convert.ToInt32(pageSizeString) + 1;
queryDTO.Model.ChineseName = chineseName;
queryDTO.Model.Level = (TeacherLevel)(-1);
queryData = accountDataService.QueryTeacher(queryDTO).Data;
}
catch (Exception ex)
{
log.Error(ex);
}
string json = JsonConvert.SerializeObject(queryData, Formatting.Indented, Constant.TIME_CONVERTER);
log.Debug(Constant.DEBUG_END);
return Content(json, Constant.JSON_MIME_TYPE);
}