public async Task <TaskSearchResult> GetModelListForwardAsync(long?userId, long?forwardStateId, int pageIndex, int pageSize) { using (MyDbContext dbc = new MyDbContext()) { TaskSearchResult result = new TaskSearchResult(); IQueryable <ForwardEntity> forwards = dbc.GetAll <ForwardEntity>().Where(c => c.UserId == userId); if (forwardStateId != null) { forwards = forwards.Where(f => f.StateId == forwardStateId); } IQueryable <TaskEntity> tasks = forwards.Select(c => c.Task).Where(t => t.IsDeleted == false); result.PageCount = (int)Math.Ceiling((await tasks.LongCountAsync()) * 1.0f / pageSize); var taskResult = await tasks.OrderByDescending(a => a.CreateTime).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync(); DateTime now = DateTime.Now; foreach (var task in taskResult) { task.IsEnabled = task.EndTime > now; } await dbc.SaveChangesAsync(); result.Tasks = taskResult.Select(a => ToDTO(a, dbc.GetId <CollectEntity>(c => c.UserId == userId && c.TaskId == a.Id))).ToArray(); return(result); } }
public async Task <TaskSearchResult> GetModelListAsync(long?userId, int?within, int pageIndex, int pageSize) { using (MyDbContext dbc = new MyDbContext()) { TaskSearchResult result = new TaskSearchResult(); var entities = dbc.GetAll <TaskEntity>().Where(t => t.IsEnabled == true); if (within != null) { DateTime date = DateTime.Now.AddDays(-within.Value); entities = entities.Where(t => t.CreateTime >= date); } result.PageCount = (int)Math.Ceiling((await entities.LongCountAsync()) * 1.0f / pageSize); var taskResult = await entities.OrderByDescending(a => a.CreateTime).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync(); DateTime now = DateTime.Now; foreach (var task in taskResult) { task.IsEnabled = task.EndTime > now; } await dbc.SaveChangesAsync(); if (userId == null) { result.Tasks = taskResult.Select(a => ToDTO(a, 0)).ToArray(); } else { result.Tasks = taskResult.Select(a => ToDTO(a, dbc.GetId <CollectEntity>(c => c.UserId == userId && c.TaskId == a.Id))).ToArray(); } return(result); } }
public async Task <TaskSearchResult> GetModelListAsync(bool isAdmin, long?userId, string keyword, DateTime?startTime, DateTime?endTime, int pageIndex, int pageSize) { using (MyDbContext dbc = new MyDbContext()) { TaskSearchResult result = new TaskSearchResult(); var entities = dbc.GetAll <TaskEntity>(); if (!isAdmin) { entities = entities.Where(t => t.IsEnabled == true); } if (!string.IsNullOrEmpty(keyword)) { entities = entities.Where(g => g.Title.Contains(keyword) || g.Condition.Contains(keyword) || g.Explain.Contains(keyword) || g.Content.Contains(keyword)); } if (startTime != null) { entities = entities.Where(a => a.CreateTime >= startTime); } if (endTime != null) { entities = entities.Where(a => SqlFunctions.DateDiff("day", endTime, a.CreateTime) <= 0); } result.PageCount = (int)Math.Ceiling((await entities.LongCountAsync()) * 1.0f / pageSize); var taskResult = await entities.OrderByDescending(a => a.CreateTime).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync(); DateTime now = DateTime.Now; foreach (var task in taskResult) { task.IsEnabled = task.EndTime > now; } await dbc.SaveChangesAsync(); if (userId == null) { result.Tasks = taskResult.Select(a => ToDTO(a, 0)).ToArray(); } else { result.Tasks = taskResult.Select(a => ToDTO(a, dbc.GetId <CollectEntity>(c => c.UserId == userId && c.TaskId == a.Id))).ToArray(); } return(result); } }
// Private Methods #region public static TaskSearchResult SearchTasks(SearchTaskParameters searchData, int intUserId, int iSAdministrator, string DefaultConnection) public static TaskSearchResult SearchTasks(SearchTaskParameters searchData, int intUserId, int iSAdministrator, string DefaultConnection) { TaskSearchResult objTaskSearchResult = new TaskSearchResult(); objTaskSearchResult.taskList = new List <DTOTask>(); List <AdefHelpDeskRoles> AllRoles = new List <AdefHelpDeskRoles>(); var resultTable = new DataTable(); //If searchData.rowsPerPage = 0 set it to 1 if (searchData.rowsPerPage == 0) { searchData.rowsPerPage = 1; } //If searchData.pageNumber = 0 set it to 1 if (searchData.pageNumber == 0) { searchData.pageNumber = 1; } var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>(); optionsBuilder.UseSqlServer(DefaultConnection); using (var context = new ADefHelpDeskContext(optionsBuilder.Options)) { // Get all possible roles to reduce database calls later AllRoles = (from role in context.AdefHelpDeskRoles select role).ToList(); } using (var conn = new SqlConnection(DefaultConnection)) { using (var cmd = new SqlCommand()) { cmd.CommandText = "[spSearchTasks]"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn; cmd.Parameters.AddWithValue("@paramIsAdmin", iSAdministrator); cmd.Parameters.AddWithValue("@paramUserId", intUserId); cmd.Parameters.AddWithValue("@paramSearchText", searchData.searchText ?? ""); cmd.Parameters.AddWithValue("@paramStatus", searchData.status ?? ""); cmd.Parameters.AddWithValue("@paramPriority", searchData.priority ?? ""); cmd.Parameters.AddWithValue("@paramCreatedDate", searchData.createdDate ?? ""); cmd.Parameters.AddWithValue("@paramDueDate", searchData.dueDate ?? ""); cmd.Parameters.AddWithValue("@paramAssignedRoleId", searchData.assignedRoleId ?? ""); cmd.Parameters.AddWithValue("@paramSelectedTreeNodes", String.Join(",", searchData.selectedTreeNodes)); cmd.Parameters.AddWithValue("@paramSortOrder", searchData.sortOrder ?? ""); cmd.Parameters.AddWithValue("@paramSortField", searchData.sortField ?? ""); cmd.Parameters.AddWithValue("@paramRowsPerPage", searchData.rowsPerPage); cmd.Parameters.AddWithValue("@paramPageNumber", searchData.pageNumber); SqlParameter parTotalCount = new SqlParameter("@paramTotalCount", SqlDbType.Int); parTotalCount.Direction = ParameterDirection.Output; cmd.Parameters.Add(parTotalCount); using (var adapter = new SqlDataAdapter(cmd)) { adapter.Fill(resultTable); } objTaskSearchResult.totalRows = Convert.ToInt32(parTotalCount.Value); } } List <DTOTask> colDTOTasks = new List <DTOTask>(); foreach (System.Data.DataRow item in resultTable.Rows) { DTOTask objDTOTask = new DTOTask(); objDTOTask.taskId = Convert.ToInt32(item.ItemArray[0]); objDTOTask.portalId = Convert.ToInt32(item.ItemArray[1]); objDTOTask.description = Convert.ToString(item.ItemArray[2]); objDTOTask.status = Convert.ToString(item.ItemArray[3]); objDTOTask.priority = Convert.ToString(item.ItemArray[4]); objDTOTask.createdDate = Convert.ToDateTime(item.ItemArray[5]).ToShortDateString(); objDTOTask.estimatedStart = (item.ItemArray[6].ToString() != "") ? Convert.ToDateTime(item.ItemArray[6]).ToShortDateString() : ""; objDTOTask.estimatedCompletion = (item.ItemArray[7].ToString() != "") ? Convert.ToDateTime(item.ItemArray[7]).ToShortDateString() : ""; objDTOTask.dueDate = (item.ItemArray[8].ToString() != "") ? Convert.ToDateTime(item.ItemArray[8]).ToShortDateString() : ""; objDTOTask.assignedRoleId = Convert.ToInt32(item.ItemArray[9]); objDTOTask.ticketPassword = Convert.ToString(item.ItemArray[10]); objDTOTask.requesterUserId = Convert.ToInt32(item.ItemArray[11]); objDTOTask.requesterName = Convert.ToString(item.ItemArray[12]); objDTOTask.requesterEmail = Convert.ToString(item.ItemArray[13]); objDTOTask.requesterPhone = Convert.ToString(item.ItemArray[14]); if (item.ItemArray[15].ToString() != "") { objDTOTask.estimatedHours = Convert.ToInt32(item.ItemArray[15]); } // Set Requester Name if (objDTOTask.requesterUserId > 0) { var User = UtilitySecurity.UserFromUserId(objDTOTask.requesterUserId.Value, DefaultConnection); objDTOTask.requesterName = $"{User.firstName} {User.lastName}"; } else { objDTOTask.requesterName = objDTOTask.requesterName; } // Set AssignedRoleName var objUserRole = AllRoles.Where(x => x.Id == objDTOTask.assignedRoleId).FirstOrDefault(); if (objUserRole != null) { objDTOTask.assignedRoleName = objUserRole.RoleName; } else { objDTOTask.assignedRoleName = "[Unassigned]"; } colDTOTasks.Add(objDTOTask); } objTaskSearchResult.taskList = colDTOTasks; objTaskSearchResult.errorMessage = string.Empty; return(objTaskSearchResult); }