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