// List all clients with their ticket number
public DatatablePagination <ClientsWithTicketsView> GetClientsWithTickets(PagingModel paging)
{
try
{
var clients = from c in db.Users
join t in db.Tickets on c.Id equals t.ClientId
where c.Type == UserType.Client
group c by c.Id into clientGroups
select new
{
Id = clientGroups.Key,
Count = clientGroups.Count(),
};
var clientsGroup = from u in db.Users
join c in clients
on u.Id equals c.Id
select new ClientsWithTicketsView
{
Id = u.Id,
FirstName = u.FirstName,
LastName = u.LastName,
Email = u.Email,
PhoneNumber = u.PhoneNumber,
DateOfBirth = u.DateOfBirth,
UserStatus = u.UserStatus,
TicketsTotal = c.Count
};
if (clientsGroup != null)
{
var user = new DatatablePagination <ClientsWithTicketsView>
{
Data = clientsGroup.ToList(),
TotalRecord = clientsGroup.Count(),
};
if (!string.IsNullOrEmpty(paging.SearchValue))
{
user.Data = user.Data
.Where(u => u.FirstName.ToLower().Contains(paging.SearchValue.ToLower()) ||
u.LastName.ToString().Contains(paging.SearchValue.ToLower()) ||
u.Email.ToString().Contains(paging.SearchValue.ToLower()) ||
u.PhoneNumber.ToLower().Contains(paging.SearchValue.ToLower())).ToList();
}
user.TotalFilteredRecord = user.Data.Count();
// sorting not working with me ..
user.Data = user.Data.OrderBy(u => u.Id).ToList();
user.Data = user.Data.Skip(paging.DisplayStart).Take(paging.DisplayLength).ToList();
return(user);
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
// List all Support Employees
public DatatablePagination <SupportEmployeesView> GetAllEmployees(PagingModel paging)
{
try
{
var employees = from a in db.Users.Where(i => i.Type == UserType.Employee)
select new SupportEmployeesView()
{
Id = a.Id,
Email = a.Email,
FirstName = a.FirstName,
LastName = a.LastName,
DateOfBirth = a.DateOfBirth,
PhoneNumber = a.PhoneNumber,
UserStatus = a.UserStatus,
// Type = a.Type
};
if (employees != null)
{
var user = new DatatablePagination <SupportEmployeesView>
{
Data = employees.ToList(),
TotalRecord = employees.Count(),
};
if (!string.IsNullOrEmpty(paging.SearchValue))
{
user.Data = user.Data
.Where(u => u.FirstName.ToLower().Contains(paging.SearchValue.ToLower()) ||
u.LastName.ToString().Contains(paging.SearchValue.ToLower()) ||
u.Email.ToString().Contains(paging.SearchValue.ToLower()) ||
u.PhoneNumber.ToLower().Contains(paging.SearchValue.ToLower())).ToList();
}
user.TotalFilteredRecord = user.Data.Count();
// sorting not working with me ..
user.Data = user.Data.OrderBy(u => u.Id).ToList();
user.Data = user.Data.Skip(paging.DisplayStart).Take(paging.DisplayLength).ToList();
return(user);
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
// List all clients with their ticket number
public DatatablePagination <ClientAndTicketCount> GetAllClients(PagingModel paging)
{
try
{
var all = (from u in db.Users.Where(u => u.Type == UserType.Client)
.Select(s => new ClientAndTicketCount
{
ID = s.Id,
FirstName = s.FirstName,
LastName = s.LastName,
DateOfBirth = s.DateOfBirth,
Email = s.Email,
PhoneNumber = s.PhoneNumber,
UserStatus = s.UserStatus,
TicketsTotal = (db.Tickets.Where(t => t.ClientId == s.Id).Count())
})
select u);
if (all != null)
{
var user = new DatatablePagination <ClientAndTicketCount>
{
Data = all.ToList(),
TotalRecord = all.Count(),
};
if (!string.IsNullOrEmpty(paging.SearchValue))
{
user.Data = user.Data
.Where(u => u.FirstName.ToLower().Contains(paging.SearchValue.ToLower()) ||
u.LastName.ToString().Contains(paging.SearchValue.ToLower()) ||
u.Email.ToString().Contains(paging.SearchValue.ToLower()) ||
u.PhoneNumber.ToLower().Contains(paging.SearchValue.ToLower())).ToList();
}
user.TotalFilteredRecord = user.Data.Count();
// sorting not working with me ..
//user.Data = user.Data.OrderBy(u => u.Id).ToList();
//user.Data = user.Data.Skip(paging.DisplayStart).Take(paging.DisplayLength).ToList();
return(user);
}
return(null);
}
catch (Exception ex)
{
throw ex;
}
}
// List all tikets with status for Manager
public DatatablePagination <ViewTicket> GetAllTickets(int id, PagingModel paging)
{
try
{
IQueryable <ViewTicket> tickets = null;
if (id == 0)
{
tickets = from t in db.Tickets
join u in db.Users on t.ClientId equals u.Id
join e in db.Users on t.EmployeeId equals e.Id into ulist
from e in ulist.DefaultIfEmpty()
select new ViewTicket()
{
Id = t.Id,
Status = t.Status,
Category = t.Category,
Priority = t.Priority,
Title = t.Title,
ClientId = t.ClientId,
EmployeeId = t.EmployeeId ?? 0,
OpenDate = t.OpenDate,
ClosedDate = t.ClosedDate,
ClosedBy = t.ClosedBy ?? 0,
ClientName = u.FirstName + " " + u.LastName,
EmployeeName = e.FirstName + " " + e.LastName,
};
}
else
{
tickets = from t in db.Tickets.Where(i => i.EmployeeId == id || i.ClientId == id)
join u in db.Users on t.ClientId equals u.Id
join e in db.Users on t.EmployeeId equals e.Id into ulist
from e in ulist.DefaultIfEmpty()
select new ViewTicket()
{
Id = t.Id,
Status = t.Status,
Category = t.Category,
Priority = t.Priority,
Title = t.Title,
OpenDate = t.OpenDate,
EmployeeId = t.EmployeeId ?? 0,
ClientId = t.ClientId,
ClientName = u.FirstName + " " + u.LastName,
EmployeeName = e.FirstName + " " + e.LastName,
};
}
if (tickets != null)
{
var ticket = new DatatablePagination <ViewTicket>
{
Data = tickets.ToList(),
TotalRecord = tickets.Count(),
};
if (!string.IsNullOrEmpty(paging.SearchValue))
{
ticket.Data = ticket.Data
.Where(u => u.Title.ToLower().Contains(paging.SearchValue.ToLower()) ||
u.Id.ToString().Contains(paging.SearchValue.ToLower()) ||
u.EmployeeName.ToLower().Contains(paging.SearchValue.ToLower()) ||
u.ClientName.ToLower().Contains(paging.SearchValue.ToLower())).ToList();
}
if (!string.IsNullOrEmpty(paging.FilterByStatus) ||
!string.IsNullOrEmpty(paging.FilterByPriority) ||
!string.IsNullOrEmpty(paging.FilterByCategory)
)
{
ticket.Data = ticket.Data.Where(u => u.Status.ToString().Contains(paging.FilterByStatus) &&
u.Priority.ToString().Contains(paging.FilterByPriority) &&
u.Category.ToString().Contains(paging.FilterByCategory)).ToList();
}
if (!string.IsNullOrEmpty(paging.StartDate) && !string.IsNullOrEmpty(paging.EndDate))
{
ticket.Data = ticket.Data.Where(x => x.OpenDate >= DateTime.Parse(paging.StartDate) && x.OpenDate <= DateTime.Parse(paging.EndDate)).ToList();
}
ticket.TotalFilteredRecord = ticket.Data.Count();
ticket.Data = ticket.Data.OrderByDescending(u => u.OpenDate).ToList();
ticket.Data = ticket.Data.Skip(paging.DisplayStart).Take(paging.DisplayLength).ToList();
return(ticket);
}
else
{
return(null);
}
}
catch (Exception ex)
{
GlobalVariable.log.Write(LogLevel.Error, ex);
return(null);
}
}
