public ActionResult UserAllocationList(DataTablesViewModel param, string fromDate, string toDate, string type, string allocationType)
{
try
{
if (!PermissionControl.CheckPermission(UserAppPermissions.UserAllocation_View))
{
return(RedirectToAction("Restricted", "Home"));
}
DateTime frdate = DateTime.Now;
if (!string.IsNullOrWhiteSpace(fromDate))
{
frdate = DateTime.Parse(fromDate);
}
DateTime tdate = DateTime.Now;
if (!string.IsNullOrWhiteSpace(toDate))
{
tdate = DateTime.Parse(toDate);
}
logger.DebugFormat("Getting User Allocation List with From Date [{0}] and To Date [{1}]", frdate.ToShortDateString(), tdate.ToShortDateString());
IEnumerable <UserDepartment> userDepartments;
bool IsAdmin = false;
if (User.IsInRole("Admin"))
{
IsAdmin = true;
}
var appUser = UserManager.FindById(User.Identity.GetUserId());
userDepartments = userdepartmentManagement.GetAllUserDepartmentById(appUser.Id);
UserAllocationVM allocation = new UserAllocationVM();
allocation.DTObject = param;
var list = userAllocationManagement.GetAllUserAllocationByParam(allocation, frdate, tdate, userDepartments, IsAdmin, type, allocationType);
logger.DebugFormat("Successfully Retrieve User Allocation List Records [{2}] with From Date [{0}] and To Date [1]", frdate.ToShortDateString(), tdate.ToShortDateString(), list.Count());
return(Json(new
{
sEcho = param.draw,
iTotalRecords = list.Select(i => i.DTObject.TotalRecordsCount).FirstOrDefault(),
iTotalDisplayRecords = list.Select(i => i.DTObject.TotalRecordsCount).FirstOrDefault(), // Filtered Count
aaData = list
}));
}
catch (Exception ex)
{
logger.ErrorFormat("Exception Raised : Message[{0}] Stack Trace [{1}] ", ex.Message, ex.StackTrace);
return(null);
}
}
public IEnumerable <UserAllocationVM> GetAllUserAllocationByParam(UserAllocationVM requisition, DateTime frdate, DateTime tdate, IEnumerable <UserDepartment> userDepartments, bool isAdmin, string type, string allocationType)
{
return(userAllocationRepository.GetAllUserAllocationByParam(requisition, frdate, tdate, userDepartments, isAdmin, type, allocationType));
}
public IEnumerable <UserAllocationVM> GetAllUserAllocationByParam(UserAllocationVM param, DateTime fromDate, DateTime toDate, IEnumerable <UserDepartment> userDepartments, bool isAdmin, string type, string allocationType)
{
try
{
List <long> deptId = new List <long>();
foreach (var dept in userDepartments)
{
deptId.Add(dept.DepartmentID);
}
IQueryable <UserAllocationVM> query = null;
if (type == RequestStatus.My_Request)
{
query = (from ua in DbContext.UserAllocations.AsNoTracking()
join dept in DbContext.Departments.AsNoTracking() on ua.RequestedDepartmentID equals dept.DepartmentID
join tempReq in DbContext.Requisitions.AsNoTracking()
on ua.RequisitionID equals tempReq.RequisitionID into Requisition
from tempReq in Requisition.DefaultIfEmpty()
join tempUser in DbContext.Users.AsNoTracking()
on ua.UserID equals tempUser.Id into User
from tempUser in User.DefaultIfEmpty()
where !ua.Status.Equals(RequestStatus.Deleted) && (deptId.Contains(ua.RequestedDepartmentID))
orderby ua.CreatedOn descending
where (DbFunctions.TruncateTime(ua.CreatedOn) >= DbFunctions.TruncateTime(fromDate) && DbFunctions.TruncateTime(ua.CreatedOn) <= DbFunctions.TruncateTime(toDate)) &&
((allocationType.Equals(AllocationType.Requisition) && string.IsNullOrEmpty(ua.UserID)) ||
(allocationType.Equals(AllocationType.Allocation) && !string.IsNullOrEmpty(ua.UserID)) ||
(allocationType == "-1"))
select new UserAllocationVM
{
RequisitionID = tempReq.RequisitionID,
UserAllocationID = ua.UserAllocationID,
UserName = tempUser.FirstName + " " + tempUser.LastName,
Percentage = ua.Percentage,
DepartmentName = (from reqDept in DbContext.Departments where reqDept.DepartmentID == ua.DepartmentID select reqDept.Name).FirstOrDefault(),
DepartmentID = dept.DepartmentID,
RequestedDepartmentID = ua.RequestedDepartmentID,
RequestedDepartmentName = dept.Name,
GroupNumber = ua.GroupNumber.ToString(),
Status = ua.Status,
UserID = ua.UserID,
Comments = ua.Comments,
CreatedOn = ua.CreatedOn,
RequestType = string.IsNullOrEmpty(ua.UserID) ? "Requisition" : "Allocation"
});
}
else
{
query = (from ua in DbContext.UserAllocations.AsNoTracking()
join dept in DbContext.Departments.AsNoTracking() on ua.DepartmentID equals dept.DepartmentID
join tempReq in DbContext.Requisitions.AsNoTracking()
on ua.RequisitionID equals tempReq.RequisitionID into Requisition
from tempReq in Requisition.DefaultIfEmpty()
join tempUser in DbContext.Users.AsNoTracking()
on ua.UserID equals tempUser.Id into User
from tempUser in User.DefaultIfEmpty()
orderby ua.CreatedOn descending
where (DbFunctions.TruncateTime(ua.CreatedOn) >= DbFunctions.TruncateTime(fromDate) && DbFunctions.TruncateTime(ua.CreatedOn) <= DbFunctions.TruncateTime(toDate)) &&
(ua.IsActive && (type.Equals(RequestStatus.Approved) || ua.Status.Equals(RequestStatus.PartialApproved)) ||
(!ua.IsActive && type.Equals(RequestStatus.Pending)) || (!ua.IsActive && type.Equals(RequestStatus.Rejected))) && ua.Status.Equals(type) &&
!ua.Status.Equals(RequestStatus.Deleted) && (deptId.Contains(ua.DepartmentID))
&& ((allocationType.Equals(AllocationType.Requisition) && string.IsNullOrEmpty(ua.UserID)) ||
(allocationType.Equals(AllocationType.Allocation) && !string.IsNullOrEmpty(ua.UserID)) ||
(allocationType == "-1"))
select new UserAllocationVM
{
RequisitionID = tempReq.RequisitionID,
UserAllocationID = ua.UserAllocationID,
UserName = tempUser.FirstName + " " + tempUser.LastName,
Percentage = ua.Percentage,
RequestedDepartmentName = (from reqDept in DbContext.Departments where reqDept.DepartmentID == ua.RequestedDepartmentID select reqDept.Name).FirstOrDefault(),
DepartmentID = ua.DepartmentID,
RequestedDepartmentID = ua.RequestedDepartmentID,
DepartmentName = dept.Name,
Status = ua.Status,
GroupNumber = ua.GroupNumber.ToString(),
UserID = ua.UserID,
Comments = ua.Comments,
CreatedOn = ua.CreatedOn,
RequestType = string.IsNullOrEmpty(ua.UserID) ? "Requisition" : "Allocation"
});
}
query = GetUserAllocationFiltersOrderQuery(query, param);
int totalRecord = query.Count();
var requisition = query.Skip(param.DTObject.start).Take(param.DTObject.length).ToList().Select(index => new UserAllocationVM
{
RequisitionID = index.RequisitionID,
UserAllocationID = index.UserAllocationID,
UserName = index.UserName,
Percentage = index.Percentage,
DepartmentName = index.DepartmentName,
DepartmentID = index.DepartmentID,
RequestedDepartmentID = index.RequestedDepartmentID,
RequestedDepartmentName = index.RequestedDepartmentName,
Status = index.Status,
GroupNumber = index.GroupNumber,
Comments = index.Comments,
UserID = index.UserID,
CreatedOn = index.CreatedOn,
RequestType = index.RequestType,
DTObject = new DataTablesViewModel()
{
TotalRecordsCount = totalRecord
}
}).ToList();
return(requisition);
}
catch (Exception ex)
{
logger.ErrorFormat("Exception Raised : Message[{0}] Stack Trace [{1}] ", ex.Message, ex.StackTrace);
return(null);
}
}
private IQueryable <UserAllocationVM> GetUserAllocationFiltersOrderQuery(IQueryable <UserAllocationVM> query, UserAllocationVM param, bool forAll = false)
{
try
{
if (param == null)
{
return(query);
}
int index = -1;
foreach (var columnData in param.DTObject.columns)
{
index += 1;
if (columnData.orderable)
{
foreach (var row in param.DTObject.order.Where(i => i.column == index))
{
if (row.dir == "asc")
{
query = query.OrderBy(columnData.data);
}
else
{
query = query.OrderByDescending(columnData.data);
}
}
}
if (columnData.search.value == null || string.IsNullOrEmpty(columnData.search.value.Trim()))
{
continue;
}
switch (columnData.data)
{
case "Comments":
if (!forAll)
{
query = query.Where(col => col.Comments.ToUpper().Contains(columnData.search.value.ToUpper()));
}
break;
}
}
string id = Utility.CovertID(param.DTObject.search.value, "UAL-");
if (param.DTObject.search.value != null && !string.IsNullOrEmpty(param.DTObject.search.value))
{
query = query.Where(col => (
col.RequestType.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.UserName.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.DepartmentName.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.RequestedDepartmentName.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.Percentage.ToString().ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.Comments.ToString().ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.UserAllocationID.ToString().Equals(id)));
}
return(query);
}
catch (Exception ex)
{
logger.ErrorFormat("Exception Raised : Message[{0}] Stack Trace [{1}] ", ex.Message, ex.StackTrace);
return(null);
}
}
