public VMExaminationTaskPersonalStatistic GetPersonalStatistic(Int32 uId) { try { String sql; SqlParameter sp; DataTable dataTable; List <VMExaminationTaskPersonalStatistic> etpss; sql = "SELECT * FROM ExaminationTaskPersonalStatistic WHERE ETPS_UserId = @uId"; sp = new SqlParameter("@uId", uId); dataTable = olsDbo.GetDataTable(sql, sp); etpss = (List <VMExaminationTaskPersonalStatistic>) ModelConvert <VMExaminationTaskPersonalStatistic> .ConvertToModel(dataTable); if (etpss.Count == 0) { return(null); } return(etpss[0]); } catch (Exception ex) { StaticHelper.GetExceptionMessageAndRecord(ex); return(null); } }
private Object[] GetModels(String statusFieldName, String sortFieldName, String[] exceptFields) { Int32 total, filter; String sql, orderSql, countSql; DataTable dataTable; Object[] sqlConditions; List <T> ms; List <SqlParameter> sps; sql = "SELECT * FROM " + tableName + " "; sqlConditions = GetSqlCondition(sql, statusFieldName, sortFieldName, exceptFields); sql = (String)sqlConditions[0]; sps = (List <SqlParameter>)sqlConditions[1]; orderSql = (String)sqlConditions[2]; dataTable = olsDbo.GetDataTableWithStart(sql + orderSql, sps, dtRequest.Length, dtRequest.Start); ms = (List <T>) ModelConvert <T> .ConvertToModel(dataTable); countSql = sql.Replace("SELECT * FROM ", "SELECT COUNT(" + idFieldName + ") FROM "); total = Convert.ToInt32(olsDbo.ExecuteSqlScalar(countSql, sps)); filter = Convert.ToInt32(olsDbo.ExecuteSqlScalar(countSql, sps)); return(new Object[] { ms, total, filter }); }
private Object[] GetUserStatistics(DataTablesRequest dtRequest, Int32 eptId) { Int32 total, filter; String sql, countSql; DataTable dataTable; Object[] sqlConditions; List <VMExaminationTaskUserStatistic> ms; List <SqlParameter> sps; sql = "SELECT * FROM ExaminationTaskUserStatistic WHERE ETUS_PaperTemplateId = @eptId "; sqlConditions = GetSqlCondition(sql, dtRequest); sql = (String)sqlConditions[0]; sps = (List <SqlParameter>)sqlConditions[1]; sps.Add(new SqlParameter("@eptid", eptId)); dataTable = olsDbo.GetDataTableWithStart(sql, sps, dtRequest.Length, dtRequest.Start); ms = (List <VMExaminationTaskUserStatistic>) ModelConvert <VMExaminationTaskUserStatistic> .ConvertToModel(dataTable); countSql = sql.Replace("SELECT * FROM ", "SELECT COUNT(ETUS_TaskId) FROM "); total = Convert.ToInt32(olsDbo.ExecuteSqlScalar(countSql, sps)); filter = Convert.ToInt32(olsDbo.ExecuteSqlScalar(countSql, sps)); return(new Object[] { ms, total, filter }); }
private Object[] GetModels(String sql, List <SqlParameter> spsAddition, String statusFieldName) { Int32 total, filter; String orderSql, countSql; DataTable dataTable; Object[] sqlConditions; List <T> ms; List <SqlParameter> sps; Regex regex; sqlConditions = GetSqlCondition(sql, spsAddition, statusFieldName); sql = (String)sqlConditions[0]; sps = (List <SqlParameter>)sqlConditions[1]; orderSql = (String)sqlConditions[2]; dataTable = olsDbo.GetDataTableWithStart(sql + orderSql, sps, dtRequest.Length, dtRequest.Start); ms = (List <T>) ModelConvert <T> .ConvertToModel(dataTable); //countSql = sql.Replace("SELECT * FROM ", "SELECT COUNT(" + idFieldName + ") FROM "); regex = new Regex(@"SELECT ([a-z]+\.)?\* FROM "); countSql = regex.Replace(sql, "SELECT COUNT(*) FROM "); total = Convert.ToInt32(olsDbo.ExecuteSqlScalar(countSql, sps)); filter = Convert.ToInt32(olsDbo.ExecuteSqlScalar(countSql, sps)); return(new Object[] { ms, total, filter }); }
private ResponseJson SetSortDown(Int32 departmentId, Int32 originId) { Int32 repeatCount; ResponseJson resJson; String sql; Double originSort, destSort; DataTable dt; User originUser, destUser; List <SqlParameter> sps; List <User> us; resJson = new ResponseJson(); originUser = olsEni.Users.Single(m => m.U_Id == originId); originSort = originUser.U_Sort; sql = "SELECT TOP 1 * " + "FROM Users u " + " LEFT JOIN User_Department ud ON u.U_Id = ud.U_Id " + " LEFT JOIN Departments d ON d.D_Id = ud.D_Id " + "WHERE d.D_Id = @dId " + " AND u.U_Sort > @uSort " + " AND d.D_Status = 1 " + " AND u.U_Status = 1 " + "ORDER BY u.U_Sort ASC"; sps = new List <SqlParameter>(); sps.Add(new SqlParameter("@dId", departmentId)); sps.Add(new SqlParameter("@uSort", originSort)); dt = olsDbo.GetDataTable(sql, sps); us = (List <User>) ModelConvert <User> .ConvertToModel(dt); if (us.Count == 0) { resJson.status = ResponseStatus.Error; resJson.message = "该用户已位于部门底部。"; return(resJson); } else { destUser = us[0]; destSort = destUser.U_Sort; originSort = Math.Round(destSort + 0.00001, 5, MidpointRounding.AwayFromZero); originUser.U_Sort = originSort; } repeatCount = HandleRepeat(originSort); if (-1 == repeatCount) { resJson.status = ResponseStatus.Error; resJson.message = "处理排序重复时抛出错误。"; return(resJson); } else if (-2 == repeatCount) { resJson.status = ResponseStatus.Error; resJson.message = "处理排序重复时保存出错。"; return(resJson); } else if (0 == repeatCount && olsEni.SaveChanges() == 0) { resJson.status = ResponseStatus.Error; resJson.message = ResponseMessage.SaveChangesError; return(resJson); } resJson.status = ResponseStatus.Success; return(resJson); }