public ActionResult AddorEditDepartments(AddorEditDepartment aed)
{
if (ModelState.IsValid)
{
using (mmpEntities mP = new mmpEntities())
{
/*#region Department Name already exists
* var isDepartmentNameAssigned = IsCategoryTypeAssigned(aed.dept_name, aed.ctd_id, 1);
* if (isDepartmentNameAssigned)
* {
* ModelState.AddModelError("DeparmentAssigned", "Department Name is already assigned");
* return View(aed);
* }
#endregion*/
#region Department Code already assigned
var isDepartmentCodeAssigned = IsCategoryTypeAssigned(aed.code, aed.ctd_id, 0);
if (isDepartmentCodeAssigned)
{
ModelState.AddModelError("DepartmentAssigned", "Department Code is already assigned");
return(View(aed));
}
#endregion
if (aed.ctd_id == 0)
{
category_type_details ctd = new category_type_details()
{
ctd_name = aed.dept_name,
ctd_code = aed.code,
category_id = aed.category_id,
ctd_created_at = DateTime.Now
};
mP.category_type_details.Add(ctd);
mP.SaveChanges();
return(Json(new { success = true, message = "Saved Successfully " }, JsonRequestBehavior.AllowGet));
}
else
{
category_type_details ctd = new category_type_details()
{
ctd_id = aed.ctd_id,
ctd_name = aed.dept_name,
ctd_code = aed.code,
category_id = aed.category_id,
ctd_created_at = aed.ctd_created_at,
ctd_updated_at = DateTime.Now
};
mP.Entry(ctd).State = EntityState.Modified;
mP.SaveChanges();
return(Json(new { success = true, message = "Updated Successfully" }, JsonRequestBehavior.AllowGet));
}
}
}
else
{
return(View(aed));
}
}
public ActionResult GetData(int id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
mP.Configuration.ProxyCreationEnabled = false;
var query = (from user in mP.users
join role in mP.roles on user.role_id equals role.role_id
join region in mP.regions on user.region_id equals region.region_id
join u in mP.users on user.supervisor equals u.user_id into us from u in us.DefaultIfEmpty()
join upd in mP.category_type_details on user.user_primary_department equals upd.ctd_id into upds from upd in upds.DefaultIfEmpty()
join upp in mP.category_type_details on user.user_primary_project equals upp.ctd_id into upps from upp in upps.DefaultIfEmpty()
select new
{
user,
user_role = role.role_name,
supervisor = u.employee_id + " " + u.user_name,
region = region.region_name,
user_primary_department = upd.ctd_name,
user_primary_project = upp.ctd_name
}).Where(x => x.user.user_status == "active");
if (id != 0)
{
query = query.Where(x => x.user.user_id == id);
}
return(Json(new { data = query.AsNoTracking().ToList() }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult AddorEditDepartments(int id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
if (id == 0)
{
return(View(new AddorEditDepartment()
{
category_id = mP.categories.First(x => x.category_name == "departments").category_id
}));
}
else
{
var query = mP.category_type_details.Where(x => x.ctd_id == id).FirstOrDefault <category_type_details>();
AddorEditDepartment aed = new AddorEditDepartment()
{
ctd_id = query.ctd_id,
dept_name = query.ctd_name,
code = query.ctd_code,
category_id = query.category_id,
ctd_created_at = query.ctd_created_at
};
return(View(aed));
}
}
}
public ActionResult GetData()
{
using (mmpEntities mP = new mmpEntities())
{
mP.Configuration.ProxyCreationEnabled = false;
var category_type_details = from ctd in mP.category_type_details
join ld in mP.leave_details on ctd.ctd_id equals ld.category_type_id into ldps from ld in ldps.DefaultIfEmpty()
join proj in mP.project_details on ctd.ctd_id equals proj.category_type_id into projps from proj in projps.DefaultIfEmpty()
join category in mP.categories on ctd.category_id equals category.category_id
join sector in mP.sectors on proj.sector_id equals sector.sector_id into sectorps from sector in sectorps.DefaultIfEmpty()
join region in mP.regions on proj.region_id equals region.region_id into regionps from region in regionps.DefaultIfEmpty()
select new
{
ctd,
ld,
proj,
category,
sector,
region,
sector_name = proj == null ? "" : sector.sector_name,
region_name = proj == null ? "" : region.region_name,
project_model = proj == null ? "" : proj.project_model,
no_of_leaves = ld == null ? "" : ld.no_of_leaves.ToString(),
encashable = ld == null ? "" : ld.encashable,
carry_forward = ld == null ? "" : ld.carry_forward,
project_id = proj == null ? 0 : proj.id,
leave_id = ld == null ? 0 : ld.id
};
Debug.WriteLine(category_type_details.AsNoTracking().ToList());
return(Json(new { data = category_type_details.AsNoTracking().ToList() }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult AddorEditLeaves(int id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
if (id == 0)
{
return(View(new AddorEditLeave()
{
category_id = mP.categories.First(x => x.category_name == "leaves").category_id
}));
}
else
{
//ctd = category type details
var ctd = mP.category_type_details.Where(x => x.ctd_id == id).FirstOrDefault <category_type_details>();
//ld = leave details
var ld = mP.leave_details.Where(x => x.category_type_id == ctd.ctd_id).FirstOrDefault <leave_details>();
AddorEditLeave ael = new AddorEditLeave()
{
ctd_id = ctd.ctd_id,
leave_name = ctd.ctd_name,
code = ctd.ctd_code,
category_id = ctd.category_id,
ctd_created_at = ctd.ctd_created_at,
leave_details_id = ld.id,
no_of_leaves = ld.no_of_leaves,
encashable = ld.encashable,
carry_forward = ld.carry_forward
};
return(View(ael));
}
}
}
public ActionResult GetTimeSheets(int id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
mP.Configuration.ProxyCreationEnabled = false;
var timesheets = (from ts in mP.timesheets
join user in mP.users on ts.timesheet_user equals user.user_id
join supervisor in mP.users on user.supervisor equals supervisor.user_id into supervisorps
from supervisor in supervisorps.DefaultIfEmpty()
join ctd in mP.category_type_details on user.user_primary_department equals ctd.ctd_id into ctdps
from ctd in ctdps.DefaultIfEmpty()
join ctp in mP.category_type_details on user.user_primary_project equals ctp.ctd_id into ctpps
from ctp in ctpps.DefaultIfEmpty()
join tsmr in mP.timesheet_mr on ts.timesheet_caller equals tsmr.tsmr_id
select new
{
ts,
user,
supervisorName = supervisor.employee_id == null ? "" : supervisor.employee_id,
tsmr,
primary_department = ctd.ctd_name == null ? "" : ctd.ctd_name,
primary_project = ctp.ctd_name == null ? "" : ctp.ctd_name
}).Where(x => x.user.user_id == id && x.user.user_status == "inactive").OrderByDescending(x => x.tsmr.tsmr_created_at);
return(Json(new { data = timesheets.AsNoTracking().ToList() }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult AddRegions(List <AddRegion> regions)
{
if (ModelState.IsValid)
{
using (mmpEntities mP = new mmpEntities())
{
mP.Configuration.ProxyCreationEnabled = false;
foreach (var i in regions)
{
#region Region Already exists
var isRegionExists = IsRegionExist(i.region_name);
if (isRegionExists)
{
ModelState.AddModelError("RegionExists", "Region already exists");
return(View(regions));
}
#endregion
region region = new region()
{
region_name = i.region_name
};
mP.regions.Add(region);
}
mP.SaveChanges();
return(Json(new { success = true, message = "Saved Successfully" }, JsonRequestBehavior.AllowGet));
}
}
else
{
return(View(regions));
}
}
public ActionResult EditRegion(EditRegion ae)
{
if (ModelState.IsValid)
{
using (mmpEntities mP = new mmpEntities())
{
#region Region Already exists
var isRegionExists = IsRegionExist(ae.region_name);
if (isRegionExists)
{
ModelState.AddModelError("RegionExists", "Region already exists");
return(View(ae));
}
#endregion
region region = new region()
{
region_id = ae.region_id,
region_name = ae.region_name
};
mP.Entry(region).State = EntityState.Modified;
mP.SaveChanges();
return(Json(new { success = true, message = "Updated Successfully" }, JsonRequestBehavior.AllowGet));
}
}
return(View());
}
public ActionResult AddSectors(List <AddSector> sectors)
{
if (ModelState.IsValid)
{
using (mmpEntities mP = new mmpEntities())
{
mP.Configuration.ProxyCreationEnabled = false;
foreach (var i in sectors)
{
#region Sector Already Exists
var isSectorExists = IsSectorExist(i.sector_name);
if (isSectorExists)
{
ModelState.AddModelError("SectorExists", "Sector already exists");
return(View(sectors));
}
#endregion
sector sector = new sector()
{
sector_name = i.sector_name
};
mP.sectors.Add(sector);
}
mP.SaveChanges();
return(Json(new { success = true, message = "Saved Successfully" }, JsonRequestBehavior.AllowGet));
}
}
else
{
return(View(sectors));
}
}
public ActionResult EditSector(EditSector ae)
{
if (ModelState.IsValid)
{
using (mmpEntities mP = new mmpEntities())
{
#region Sector Already Exists
var isSectorExists = IsSectorExist(ae.sector_name);
if (isSectorExists)
{
ModelState.AddModelError("SectorExists", "Sector already exists");
return(View(ae));
}
#endregion
sector sector = new sector()
{
sector_id = ae.sector_id,
sector_name = ae.sector_name
};
mP.Entry(sector).State = EntityState.Modified;
mP.SaveChanges();
return(Json(new { success = true, message = "Updated Successfully" }, JsonRequestBehavior.AllowGet));
}
}
return(View());
}
public bool IsSectorExist(string sectorName)
{
using (mmpEntities mP = new mmpEntities())
{
var v = mP.sectors.Where(x => x.sector_name == sectorName).FirstOrDefault();
return(v != null);
}
}
private bool IsTimeSheetCategoryTypeExist(int category_type_id = 0, int timesheet_id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
var v = mP.timesheet_details.Where(a => a.tsd_timesheet_id == timesheet_id && a.tsd_category_type_id == category_type_id).FirstOrDefault();
return(v != null);
}
}
public bool IsHolidayAssigned(string hd_name, int hy_id = 0, DateTime?fromdate = null, int hd_id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
var v = mP.holiday_details.Where(a => (a.hd_name == hd_name || a.hd_from == fromdate) /*&& a.hy_id == hy_id*/ && a.hd_id != hd_id).FirstOrDefault();
return(v != null);
}
}
public bool IsRegionExist(string regionName)
{
using (mmpEntities mP = new mmpEntities())
{
var v = mP.regions.Where(x => x.region_name == regionName).FirstOrDefault();
return(v != null);
}
}
private bool IsSectorExist(string sectorName, int sector_id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
var v = mP.sectors.Where(x => x.sector_name == sectorName && x.sector_id != sector_id).FirstOrDefault();
return(v != null);
}
}
private bool IsRegionExist(string regionName, int region_id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
var v = mP.regions.Where(x => x.region_name == regionName && x.region_id != region_id).FirstOrDefault();
return(v != null);
}
}
public bool isEmployeeIDExist(string employeeID, int user_id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
var v = mP.users.Where(a => a.employee_id == employeeID && a.user_id != user_id).FirstOrDefault();
return(v != null);
}
}
public bool IsEmailExist(string email, int user_id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
var v = mP.users.Where(a => a.user_email == email && a.user_id != user_id).FirstOrDefault();
//return v == null ? false : true;
return(v != null);
}
}
private bool IsHolidayAssigned(string hd_name, int hy_id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
var v = mP.holiday_details.Where(a => (a.hd_name == hd_name) && a.hy_id == hy_id).FirstOrDefault();
//Debug.WriteLine(v == null ? "empty" : "not empty");
return(v != null);
}
}
private bool IsCategoryTypeAssigned(string ctd, int ctd_id = 0)
{
using (mmpEntities mP = new mmpEntities())
{
mP.Configuration.ProxyCreationEnabled = false;
var v = mP.category_type_details.Where(x => (x.ctd_code == ctd) && x.ctd_id != ctd_id).FirstOrDefault();
return(v != null);
}
}
//=========================================================================================================================================================
private bool IsEmployeeIDExist(string employee_id, int user_id)
{
using (mmpEntities mP = new mmpEntities())
{
var v = mP.users.Where(a => a.employee_id == employee_id && a.user_id != user_id).FirstOrDefault();
//return v == null ? false : true;
return(v != null);
}
}
public ActionResult SelectCategoryType()
{
using (mmpEntities mP = new mmpEntities())
{
mP.Configuration.ProxyCreationEnabled = false;
ViewBag.categories = mP.categories.ToList <category>();
return(View(new category()));
}
}
public ActionResult USProjectReport()
{
using (mmpEntities mP = new mmpEntities())
{
mP.Configuration.ProxyCreationEnabled = false;
ViewBag.Users = mP.users.ToList <user>();
return(View(new USProjectReport()));
}
}
public static string getUserName()
{
var employeeID = System.Web.HttpContext.Current.User.Identity.Name;
using (mmpEntities mE = new mmpEntities())
{
user user = mE.users.Where(x => x.employee_id == employeeID).FirstOrDefault <user>();
return(user == null ? "" : user.user_name);
}
}
public ActionResult USCategoryReport()
{
using (mmpEntities mP = new mmpEntities())
{
mP.Configuration.ProxyCreationEnabled = false;
ViewBag.Categories = mP.categories.ToList <category>();
return(View(new USCategoryReport()));
}
}
public static string getRole(string employeeID)
{
using (mmpEntities mP = new mmpEntities())
{
//USE SUB QUERY
user user = mP.users.Where(x => x.employee_id == employeeID).FirstOrDefault <user>();
role role = mP.roles.Where(x => x.role_id == user.role_id).FirstOrDefault <role>();
return(role.role_name);
}
}
public ActionResult GetCategoryTypeUserWorkHoursData(int id = 0, string startDate = "", string endDate = "")
{
using (mmpEntities mP = new mmpEntities())
{
mP.Configuration.ProxyCreationEnabled = false;
var ret = mP.ReportCategoryUserWorkHours(id, startDate, endDate).ToList <ReportCategoryUserWorkHours_Result>();
return(Json(new { data = ret }, JsonRequestBehavior.AllowGet));
}
}
public JsonResult GetUser(string Areas, string term = "")
{
using (mmpEntities mP = new mmpEntities())
{
// Use Stored Procedure
var objUserlist = mP.users.Where(c => c.user_status == "active" && c.employee_id.ToUpper()
.Contains(term.ToUpper()))
.Select(c => new { Name = c.employee_id, ID = c.user_id })
.Distinct().ToList();
return(Json(objUserlist, JsonRequestBehavior.AllowGet));
}
}
public ActionResult Delete(int id)
{
using (mmpEntities mP = new mmpEntities())
{
user userDetails = mP.users.Where(x => x.user_id == id).FirstOrDefault <user>();
//mP.users.Remove(userDetails);
//mP.SaveChanges();
userDetails.user_status = "inactive";
mP.Entry(userDetails).State = EntityState.Modified;
mP.SaveChanges();
return(Json(new { success = true, message = "Deleted Successfully" }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult ChangePassword(ChangePasswordVM userpassVM)
{
using (mmpEntities mP = new mmpEntities())
{
user user = mP.users.Where(x => x.user_id == userpassVM.user_id).FirstOrDefault();
#region Password Hashing
user.user_password = Crypto.Hash(userpassVM.password);
#endregion
mP.SaveChanges();
return(Json(new { success = true, message = "Updated Successfully" }, JsonRequestBehavior.AllowGet));
}
}
