public IActionResult Page([Required] Int32 index, String id, [Required] int code, [Required] DateTime left, [Required] DateTime right) { if (ModelState.IsValid) { StringBuilder builder = new StringBuilder("select * from LogStudentOperations where LogStudentOperationId > 0"); List <SqlParameter> parameters = new List <SqlParameter>(); if (id != null && id.Trim() != "") { builder.Append(" and StudentId = @Id"); SqlParameter parameter = new SqlParameter { SqlDbType = SqlDbType.NVarChar, ParameterName = "@Id", Value = id.Trim() }; parameters.Add(parameter); } if (code >= 0) { builder.Append($" and StuOperationCode = {code}"); } int pageSize = 12; //每一页的题目数量 // ReSharper disable once CoVariantArrayConversion int dataCount = _context.LogStudentOperations.FromSql(builder.ToString(), parameters.ToArray <SqlParameter>()).Count(); int pageCount = dataCount / pageSize; //有多少页 int lastCount = dataCount % pageSize; //最后一页的题目数量 if (lastCount > 0) { pageCount++; } if (index > pageCount || index <= 0) { return(Json(new { isOk = true, lineCount = 0, pageCount = 1, //总共是多少页 pageNowIndex = 1, //当前是第几页 })); } // ReSharper disable once CoVariantArrayConversion var list = _context.LogStudentOperations .FromSql(builder.ToString(), parameters.ToArray()) .Where(p => p.AddTime >= left && p.AddTime <= right) .OrderByDescending(l => l.AddTime) .Skip((index - 1) * pageSize) .Take(pageSize) .Select(log => new { id = log.LogStudentOperationId, uId = log.StudentId, addTime = _logger.FormatDateLocal(log.AddTime), ip = log.OperationIp, code = log.StuOperationCode.ToString(), target = log.StuOperationName, status = log.StuOperationStatus == StuOperationStatus.Success ? "操作成功" : "操作失败", content = log.StuOperationContent }) .ToList(); return(Json(new { isOk = true, lineCount = dataCount, PageCount = pageCount, //总共是多少页 pageNowIndex = index, //当前是第几页 items = list, size = pageSize })); } else { return(Json(new { isOk = false, error = _analysis.ModelStateDictionaryError(ModelState), title = "错误提示", message = "参数错误,传递了不符合规定的参数" })); } }
public IActionResult Page([Required] int index, [Required] int isFinish, [Required] int paperId, String studentId, float leftScore = 0, float rightScore = float.MaxValue) { if (ModelState.IsValid) { StringBuilder builder = new StringBuilder("select * from [ExaminationPapers] where [PaperId] > 0"); List <SqlParameter> parameters = new List <SqlParameter>(); if (studentId != null && studentId.Trim() != "") { builder.Append(" and StudentId = @StudentId"); parameters.Add(new SqlParameter { ParameterName = "@StudentId", Value = studentId.Trim(), SqlDbType = SqlDbType.NVarChar }); } if (paperId > 0) { builder.Append($" and [PaperId] = {paperId}"); } if (isFinish > -1) { if (isFinish == 0) { builder.Append(" and [IsFinish] = 0"); } else { builder.Append(" and [IsFinish] = 1"); } } builder.Append($" and Score > {leftScore} and Score < {rightScore}"); int pageSize = 12; //每一页的题目数量 // ReSharper disable once CoVariantArrayConversion int dataCount = _context.ExaminationPapers.FromSql(builder.ToString(), parameters.ToArray <SqlParameter>()).Count(); int pageCount = dataCount / pageSize; //有多少页 int lastCount = dataCount % pageSize; //最后一页的题目数量 if (lastCount > 0) { pageCount++; } if (index > pageCount || index <= 0) { return(Json(new { isOk = true, lineCount = 0, pageCount = 1, //总共是多少页 pageNowIndex = 1, //当前是第几页 })); } // ReSharper disable once CoVariantArrayConversion var list = _context.ExaminationPapers .FromSql(builder.ToString(), parameters.ToArray <SqlParameter>()) .OrderByDescending(item => item.PassScore) .ThenBy(ap => ap.Score) .ThenBy(ap => ap.TotleScore) .Skip((index - 1) * pageSize) .Take(pageSize).Select(v => new { studentId = v.StudentId, examTime = v.ExamTime, addTime = _logger.FormatDateLocal(v.AddTime), leaveExamTime = v.LeaveExamTime, totleScore = v.TotleScore, score = v.Score, isFinish = v.IsFinish ? "已完成":"未完成", passScore = v.PassScore, paperId = v.PaperId }).ToList(); return(Json(new { isOk = true, lineCount = dataCount, PageCount = pageCount, //总共是多少页 pageNowIndex = index, //当前是第几页 items = list, size = pageSize })); } else { return(Json(new { isOk = false, error = _analysis.ModelStateDictionaryError(ModelState), title = "错误提示", message = "参数错误,传递了不符合规定的参数" })); } }
public IActionResult Page([Required] int index, [Required] int iId, [Required] int type, [Required] int pId, [Required] int status, [Required] int grade, String keyName) { if (ModelState.IsValid) { StringBuilder builder = new StringBuilder("select * from ApplicationJoinTheExaminations where ApplicationJoinId > 0"); if (iId > 0) { builder.Append($" and InstituteId = {iId}"); } if (type >= 0) { builder.Append($" and StudentType = {type}"); } if (pId >= 0) { builder.Append($" and ProfessionId = {pId}"); } if (status >= 0) { builder.Append($" and ApplicationStatus = {status}"); } if (grade >= 0) { builder.Append($" and Grade = {grade}"); } List <SqlParameter> parameters = new List <SqlParameter>(); if (keyName != null && keyName.Trim() != "") { builder.Append($" and Name like '%@keyName%'"); SqlParameter parameter = new SqlParameter { SqlDbType = SqlDbType.NVarChar, ParameterName = "@keyName", Value = keyName }; parameters.Add(parameter); builder.Append($" or StudentId like '@id'"); SqlParameter parameterId = new SqlParameter { SqlDbType = SqlDbType.NVarChar, ParameterName = "@id", Value = keyName }; parameters.Add(parameterId); } int pageSize = 12; //每一页的题目数量 // ReSharper disable once CoVariantArrayConversion int dataCount = _context.ApplicationJoinTheExaminations.FromSql(builder.ToString(), parameters.ToArray <SqlParameter>()).Count(); int pageCount = dataCount / pageSize; //有多少页 int lastCount = dataCount % pageSize; //最后一页的题目数量 if (lastCount > 0) { pageCount++; } if (index > pageCount || index <= 0) { return(Json(new { isOk = true, lineCount = 0, pageCount = 1, //总共是多少页 pageNowIndex = 1, //当前是第几页 })); } //做了两个内连接 查询出学院 和专业 // ReSharper disable once CoVariantArrayConversion var list = _context.ApplicationJoinTheExaminations .FromSql(builder.ToString(), parameters.ToArray <SqlParameter>()) .OrderBy(item => item.ApplicationStatus) .ThenBy(ap => ap.InstituteId) .ThenBy(ap => ap.AddTime) .Join(_context.Institute, p => p.InstituteId, i => i.InstituteId, (application, institute) => new { app = application, insName = institute.Name }) .Join(_context.Professions, ip => ip.app.ProfessionId, p => p.ProfessionId, (ip, p) => new { applicationForm = ip.app, //ApplicationJoinTheExaminations instName = ip.insName, //学院名称 profesName = p.Name //专业名称 }) .Skip((index - 1) * pageSize) .Take(pageSize) .Select(v => new { type = v.applicationForm.StudentType == StudentType.UnderGraduate?"本科生":"研究生", sex = v.applicationForm.Sex?"男":"女", application = v.applicationForm, insName = v.instName, proName = v.profesName, addTime = _logger.FormatDateLocal(v.applicationForm.AddTime), isInspect = v.applicationForm.ApplicationStatus != ApplicationStatus.Submit, status = v.applicationForm.ApplicationStatus == ApplicationStatus.Submit? "尚未审核":v.applicationForm.ApplicationStatus == ApplicationStatus.Fail? "未通过审核":"已通过审核" }) .ToList(); return(Json(new { isOk = true, lineCount = dataCount, PageCount = pageCount, //总共是多少页 pageNowIndex = index, //当前是第几页 items = list, size = pageSize })); } else { return(Json(new { isOk = false, error = _analysis.ModelStateDictionaryError(ModelState), title = "错误提示", message = "参数错误,传递了不符合规定的参数" })); } }
public IActionResult Page([Required] Int32 index, String id, [Required] int terminal, [Required] DateTime left, [Required] DateTime right) { if (ModelState.IsValid) { StringBuilder builder = new StringBuilder("select * from LogUserLogin where LogUserLoginId > 0"); List <SqlParameter> parameters = new List <SqlParameter>(); if (id != null && id.Trim() != "") { builder.Append(" and ID = @Id"); SqlParameter parameter = new SqlParameter { SqlDbType = SqlDbType.NVarChar, ParameterName = "@Id", Value = id.Trim() }; parameters.Add(parameter); } if (terminal >= 0) { builder.Append($" and Terminal = {terminal}"); } int pageSize = 12; //每一页的题目数量 // ReSharper disable once CoVariantArrayConversion int dataCount = _context.LogUserLogin.FromSql(builder.ToString(), parameters.ToArray <SqlParameter>()).Count(); int pageCount = dataCount / pageSize; //有多少页 int lastCount = dataCount % pageSize; //最后一页的题目数量 if (lastCount > 0) { pageCount++; } if (index > pageCount || index <= 0) { return(Json(new { isOk = true, lineCount = 0, pageCount = 1, //总共是多少页 pageNowIndex = 1, //当前是第几页 })); } // ReSharper disable once CoVariantArrayConversion var list = _context.LogUserLogin .FromSql(builder.ToString(), parameters.ToArray()) .Where(p => p.LoginTime >= left && p.LoginTime <= right) .OrderByDescending(l => l.LoginTime) .Skip((index - 1) * pageSize) .Take(pageSize) .Select(log => new { id = log.LogUserLoginId, uid = log.ID, addTime = _logger.FormatDateLocal(log.LoginTime), ip = log.LoginIp, terminal = log.Terminal == Terminal.Pc?"电脑终端":(log.Terminal == Terminal.Phone? "手机终端":"平板终端") }) .ToList(); return(Json(new { isOk = true, lineCount = dataCount, PageCount = pageCount, //总共是多少页 pageNowIndex = index, //当前是第几页 items = list, size = pageSize })); } else { return(Json(new { isOk = false, error = _analysis.ModelStateDictionaryError(ModelState), title = "错误提示", message = "参数错误,传递了不符合规定的参数" })); } }