public MResponse UpdateReqStatus(RequisitionVM status)
{
bool success = new RequestDAO().UpdateStatus(status.Status, status.Id);
MResponse response = new MResponse(success);
return(response);
}
public ActionResult RequisitionList(DataTablesViewModel param, string fromDate, string toDate, string type)
{
try
{
if (!PermissionControl.CheckPermission(UserAppPermissions.Requisition_View))
{
return(RedirectToAction("Restricted", "Home"));
}
if (string.IsNullOrEmpty(type) || type.Equals("undefined"))
{
type = RequestStatus.My_Request;
}
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 Requisition 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);
RequisitionVM requisition = new RequisitionVM();
requisition.DTObject = param;
var list = requisitionManagement.GetAllRequisitionByParam(requisition, frdate, tdate, userDepartments, IsAdmin, type);
logger.DebugFormat("Successfully Retrieve Requisition 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);
}
}
private void SendRequisitionEmailsToLevel2Department(long requisitionId)
{
try
{
logger.DebugFormat("Sending Requisition Email to level 2 departments by RequisitionID [{0}]", requisitionId);
RequisitionVM req = requisitionManagement.GetRequisitionCompleteInfoById(requisitionId);
if (req == null)
{
logger.DebugFormat("No Requisition Found With ID [{0}]", requisitionId);
return;
}
string styleSheet = System.IO.File.ReadAllText(Server.MapPath("~/Themes/finance-1/css/emailstyle.css"));
req.StyleSheet = styleSheet;
var viewsPath = Path.GetFullPath(HostingEnvironment.MapPath(@"~/Views/EmailTemplates/RequisitionEmailToDepartments.cshtml"));
string template = System.IO.File.ReadAllText(viewsPath);
string uniqueNumber = Guid.NewGuid().ToString();
string body = Engine.Razor.RunCompile(template, string.Format("RequisitionEmail_{0}", uniqueNumber), typeof(UserTaskEmailVM), req);
var role = RoleManager.Roles.Where(x => x.Name.Equals(UserRoles.Manager)).FirstOrDefault();
var requisitionApprovalDepartments = requisitionApprovalManagement.GetAllRequisitionApprovalByRequisition(req.RequisitionID);
if (requisitionApprovalDepartments != null && requisitionApprovalDepartments.Count() > 0)
{
foreach (var approvalDept in requisitionApprovalDepartments)
{
var departManagers = userManagement.GetAllUsersByRoleAndDepartment(role.Id, approvalDept.DepartmentID);
if (departManagers != null && departManagers.Count() > 0)
{
foreach (var user in departManagers)
{
ICommunicationManagement comManagement = new CommunicationManagement();
comManagement.Subject = string.Format("New Requisition Request #. {0} - {1} ", Utility.FormatedId("UR-", req.RequisitionID.ToString()), req.JobTitle);
comManagement.Body = body;
comManagement.Recipient = user.Email;
comManagement.HeaderImage = Server.MapPath("~/Themes/finance-1/img/logo.png");
Async.Do(() => comManagement.SendEmail());
logger.DebugFormat("Email Successfully Send");
}
}
}
}
}
catch (Exception ex)
{
logger.ErrorFormat("Exception Raised : Message[{0}] Stack Trace [{1}] ", ex.Message, ex.StackTrace);
}
}
public List <RequisitionVM> GetRequistionListByEmpId(int empId)
{
List <RequisitionVM> requisitionList = new List <RequisitionVM>();
RequisitionVM requisition = null;
SqlDataReader data = null;
try
{
string sql = "SELECT r.ID, r.[DateTime], r.[Status], SUM(ir.NeededQty) AS Quantity, e.DeptID " +
"FROM Request r, ItemRequest ir, Employee e " +
"WHERE r.ID = ir.RequestID " +
"AND r.EmployeeID = @value " +
"AND r.EmployeeID = e.ID " +
"GROUP BY r.ID, r.[DateTime], r.[Status], e.DeptID";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.Add("@value", SqlDbType.Int);
cmd.Parameters["@value"].Value = empId;
connection.Open();
data = cmd.ExecuteReader();
while (data.Read())
{
requisition = new RequisitionVM()
{
Id = Convert.ToInt32(data["ID"]),
Date = Convert.ToDateTime(data["DateTime"]),
Status = data["Status"].ToString(),
Quantity = Convert.ToInt32(data["Quantity"]),
DepartmentId = data["DeptID"].ToString()
};
requisitionList.Add(requisition);
}
}
finally
{
if (data != null)
{
data.Close();
}
connection.Close();
}
return(requisitionList);
}
public List <RequisitionVM> GetReqListByDepartment(string deptId)
{
List <RequisitionVM> reqList = new List <RequisitionVM>();
RequisitionVM req = null;
SqlDataReader reader = null;
try
{
string sql = " SELECT e.UserName,req.DateTime,req.Status,req.ID,SUM(ireq.NEEDEDQTY) Quantity " +
" FROM Request req,Employee e,ItemRequest ireq " +
" WHERE req.EmployeeID = e.ID AND ireq.RequestID=req.id AND " +
"req.EmployeeID IN(SELECT e.ID FROM Employee e WHERE e.DeptID = @deptId) " +
" GROUP BY req.ID,e.UserName,req.DateTime,req.Status";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@deptId", deptId);
connection.Open();
reader = cmd.ExecuteReader();
while (reader != null && reader.Read())
{
req = new RequisitionVM()
{
Id = Convert.ToInt32(reader["ID"]),
EmployeeName = reader["UserName"].ToString(),
Date = (DateTime)reader["DateTime"],
Status = reader["Status"].ToString(),
Quantity = (int)reader["Quantity"]
};
reqList.Add(req);
}
}
catch (Exception e)
{
return(null);
}
finally
{
if (reader != null)
{
reader.Close();
}
connection.Close();
}
return(reqList);
}
private void RequisitionStatusEmailToUser(long requisitionId, string status, string comments)
{
try
{
logger.DebugFormat("Sending Requisition Email to request creator by RequisitionID [{0}]", requisitionId);
RequisitionVM req = requisitionManagement.GetRequisitionCompleteInfoById(requisitionId);
if (req == null)
{
logger.DebugFormat("No Requisition Found With ID [{0}]", requisitionId);
return;
}
string styleSheet = System.IO.File.ReadAllText(Server.MapPath("~/Themes/finance-1/css/emailstyle.css"));
req.StyleSheet = styleSheet;
req.Status = status;
req.Comments = comments;
var viewsPath = Path.GetFullPath(HostingEnvironment.MapPath(@"~/Views/EmailTemplates/RequisitionEmailToRequestCreator.cshtml"));
string template = System.IO.File.ReadAllText(viewsPath);
string uniqueNumber = Guid.NewGuid().ToString();
string body = Engine.Razor.RunCompile(template, string.Format("RequisitionEmail_{0}", uniqueNumber), typeof(UserTaskEmailVM), req);
var role = RoleManager.Roles.Where(x => x.Name.Equals(UserRoles.Manager)).FirstOrDefault();
var user = UserManager.FindById(req.CreatedBy.ToString());
if (user != null)
{
ICommunicationManagement comManagement = new CommunicationManagement();
comManagement.Subject = string.Format("Requisition Request #. {0} Status - {1} ", Utility.FormatedId("UR-", req.RequisitionID.ToString()), req.Status);
comManagement.Body = body;
comManagement.Recipient = user.Email;
comManagement.HeaderImage = Server.MapPath("~/Themes/finance-1/img/logo.png");
Async.Do(() => comManagement.SendEmail());
logger.DebugFormat("Email Successfully Send");
}
}
catch (Exception ex)
{
logger.ErrorFormat("Exception Raised : Message[{0}] Stack Trace [{1}] ", ex.Message, ex.StackTrace);
}
}
public ActionResult Create()
{
InitViewBags();
var lstPlateNos = from p in vehicleRepo.List()
select new { No = p.LicenseNo };
ViewBag.PlateNos = new SelectList(lstPlateNos, "No", "No");
ViewBag.Update = "0";
RequisitionVM reqVM = new RequisitionVM
{
Requisition = new Requisition {
No = reqRepo.GenerateReqNo(DateTime.Now),
Date = DateTime.Now,
JobOrderDate = DateTime.Now
},
ItemTypes = new SelectList(itemTypeRepo.List().ToList(), "Id", "Description")
};
return(View(reqVM));
}
public ActionResult Edit(string reqNo)
{
InitViewBags();
ViewBag.PurchaseOrders = new SelectList(reqRepo.List(), "No", "No");
ViewBag.Update = "1";
RequisitionVM reqVM = new RequisitionVM
{
Requisition = reqRepo.GetByNo(reqNo),
ItemTypes = new SelectList(itemTypeRepo.List().ToList(), "Id", "Description")
};
var lstPlateNos = from p in vehicleRepo.List()
select new { No = p.LicenseNo };
ViewBag.PlateNos = new SelectList(lstPlateNos, "No", "No", reqVM.Requisition.UnitPlateNo);
int vehicleId = vehicleRepo.GetIdByPlateNo(reqVM.Requisition.UnitPlateNo);
ViewBag.Companies = new SelectList(companyRepo.List(), "Id", "Name", veAssignRepo.GetIdByVehicleId(vehicleId));
return(View("Create", reqVM));
}
public List <RequisitionVM> GetApprovedRequests()
{
List <RequisitionVM> requisitions = new List <RequisitionVM>();
RequisitionVM requisition = null;
string sql = "SELECT r.ID, r.DateTime, r.Status, e.Name, SUM(ir.NeededQty) AS [TotalItems] " +
"FROM Request r, Employee e, ItemRequest ir " +
"WHERE Status = 'Approved' " +
"AND r.EmployeeID = e.ID " +
"AND r.ID = ir.RequestID " +
"GROUP BY r.ID, r.DateTime, r.Status, e.Name " +
"ORDER BY r.DateTime ASC; ";
SqlCommand cmd = new SqlCommand(sql, connection);
connection.Open();
SqlDataReader data = cmd.ExecuteReader();
while (data.Read())
{
requisition = new RequisitionVM()
{
Id = Convert.ToInt32(data["ID"]),
Date = Convert.ToDateTime(data["DateTime"]),
Status = data["Status"].ToString(),
EmployeeName = data["Name"].ToString(),
Quantity = Convert.ToInt32(data["TotalItems"])
};
requisitions.Add(requisition);
}
data.Close();
connection.Close();
return(requisitions);
}
public IEnumerable <RequisitionVM> GetAllRequisitionByParam(RequisitionVM param, DateTime fromDate, DateTime toDate, IEnumerable <UserDepartment> departments, bool isAdmin, string type)
{
try
{
List <long> deptId = new List <long>();
foreach (var dept in departments)
{
deptId.Add(dept.DepartmentID);
}
IQueryable <RequisitionVM> query = null;
if (type == RequestStatus.My_Request)
{
query = (from req in DbContext.Requisitions.AsNoTracking()
where !req.IsDeleted
orderby req.RequisitionDate descending
where (DbFunctions.TruncateTime(req.RequisitionDate) >= DbFunctions.TruncateTime(fromDate) && DbFunctions.TruncateTime(req.RequisitionDate) <= DbFunctions.TruncateTime(toDate)) &&
(deptId.Contains(req.DepartmentID) || isAdmin)
select new RequisitionVM
{
RequisitionID = req.RequisitionID,
JobTitle = req.JobTitle,
RequisitionDate = req.RequisitionDate,
Status = req.Status,
NoOfPosition = req.NoOfPosition,
GradeLevel = req.GradeLevel,
DepartmentID = req.DepartmentID,
RequisitionApprovalID = 0
});
}
else
{
// int condition = CheckHaveRight(deptId);
query = (from req in DbContext.Requisitions.AsNoTracking()
join reqApp in DbContext.RequisitionApprovals.AsNoTracking()
on req.RequisitionID equals reqApp.RequisitionID
where !req.IsDeleted
orderby req.RequisitionDate descending
where (DbFunctions.TruncateTime(req.RequisitionDate) >= DbFunctions.TruncateTime(fromDate) && DbFunctions.TruncateTime(req.RequisitionDate) <= DbFunctions.TruncateTime(toDate)) &&
((!reqApp.IsActive && type == RequisitionStatus.Level2_Pending && (req.Status.Equals(RequisitionStatus.Level1_Approved) || req.Status.Equals(RequisitionStatus.Level2_Pending))) || (req.Status.Equals(type))) &&
deptId.Contains(reqApp.DepartmentID)
select new RequisitionVM
{
RequisitionID = req.RequisitionID,
JobTitle = req.JobTitle,
RequisitionDate = req.RequisitionDate,
Status = req.Status,
NoOfPosition = req.NoOfPosition,
GradeLevel = req.GradeLevel,
DepartmentID = req.DepartmentID,
RequisitionApprovalID = reqApp.RequisitionApprovalID
});
}
query = GetRequisitionFiltersOrderQuery(query, param);
int totalRecord = query.Count();
var requisition = query.Skip(param.DTObject.start).Take(param.DTObject.length).ToList().Select(index => new RequisitionVM
{
RequisitionID = index.RequisitionID,
JobTitle = index.JobTitle,
RequisitionDate = index.RequisitionDate,
Status = index.Status,
NoOfPosition = index.NoOfPosition,
GradeLevel = index.GradeLevel,
RequisitionApprovalID = index.RequisitionApprovalID,
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 <RequisitionVM> GetRequisitionFiltersOrderQuery(IQueryable <RequisitionVM> query, RequisitionVM 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 "JobTitle":
if (!forAll)
{
query = query.Where(col => col.JobTitle.ToUpper().Contains(columnData.search.value.ToUpper()));
}
break;
}
}
string id = Utility.CovertID(param.DTObject.search.value, "UR-");
if (param.DTObject.search.value != null && !string.IsNullOrEmpty(param.DTObject.search.value))
{
query = query.Where(col => (
col.JobTitle.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.GradeLevel.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.Status.ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.NoOfPosition.ToString().ToUpper().Contains(param.DTObject.search.value.ToUpper()) ||
col.RequisitionID.ToString().Equals(id)));
}
return(query);
}
catch (Exception ex)
{
logger.ErrorFormat("Exception Raised : Message[{0}] Stack Trace [{1}] ", ex.Message, ex.StackTrace);
return(null);
}
}
public IEnumerable <RequisitionVM> GetAllRequisitionByParam(RequisitionVM param, DateTime fromDate, DateTime toDate, IEnumerable <UserDepartment> departments, bool isAdmin, string type)
{
return(requisitionRepository.GetAllRequisitionByParam(param, fromDate, toDate, departments, isAdmin, type));
}
