public static ReturnObject DoImport(string file_name)
{
leave_details PageObject = new leave_details();
ReturnObject return_object = new ReturnObject();
DBConnection db_connection = new DBConnection();
DataTable excel_data = new DataTable();
string
default_upload_path = string.Empty, full_upload_path = string.Empty,
employee_id = string.Empty, return_message = string.Empty,
query = string.Empty;
try
{
default_upload_path = ConfigurationManager.AppSettings["TEMP_FILE_UPLOAD"].ToString();
full_upload_path = HttpContext.Current.Server.MapPath("~/" + default_upload_path + "/" + file_name);
// Read the excel file and store the data in a DataTable.
excel_data = ExcelImport.ImportExcelToDataTable(full_upload_path, "");
if (excel_data.Rows.Count > 0)
{
return_object.status = "success";
return_object.return_data = PageObject.ImportLeaves(excel_data);
}
else
{
return_object.status = "error";
return_object.return_data = "Oops excel sheet is empty";
}
}
catch (Exception ex)
{
Logger.LogException(ex, page, "DO_IMPORT");
return_object.status = "error";
return_object.return_data = "An error occurred while performing this operation. Please try again. If the error persists, please contact Support.";
}
return(return_object);
}
public static ReturnObject GetNormalLeavesData(int page_number, bool is_filter, string filters)
{
leave_details page_object = new leave_details();
DBConnection db_connection = new DBConnection();
ReturnObject return_object = new ReturnObject();
DataTable normal_leave_data = new DataTable();
DataTable branch_list_table = new DataTable();
DataTable CoManagerID_data = new DataTable();
DataTable self_data_table = new DataTable();
string
user_name = string.Empty,
employee_id = string.Empty,
query = string.Empty,
self_query = string.Empty,
CoManagerID = string.Empty,
BranchList = "'Empty',",
branchqry = string.Empty;
int
start_row = 0, number_of_record = 0,
user_access_level = 0,
IsDelegationManager = 0;
try
{
// getting session data for later use in the function.
user_name = HttpContext.Current.Session["username"].ToString();
user_access_level = Convert.ToInt32(HttpContext.Current.Session["access_level"]);
employee_id = HttpContext.Current.Session["employee_id"].ToString();
// Setting the values for pagination
start_row = (page_number - 1) * 30;
number_of_record = page_number * 30 + 1;
//query = page_object.GetNormalLeavesBaseQuery();
//check IsDelegationManager count
IsDelegationManager = db_connection.GetRecordCount("Select COUNT(DelidationManagerID) from TbAsignDelegation Where DelidationManagerID='" + employee_id + "' And DeliationStatus=1 and Convert(date,Getdate())>=Convert(date,Fromdate) And Convert(date,Getdate())<=Convert(date,Todate)");
if (IsDelegationManager > 0)
{
CoManagerID_data = db_connection.ReturnDataTable("Select ManagerId from TbAsignDelegation Where DelidationManagerID='" + employee_id + "' And DeliationStatus=1 and Convert(date,Getdate())>=Convert(date,Fromdate) And Convert(date,Getdate())<=Convert(date,Todate)");
if (CoManagerID_data.Rows.Count > 0)
{
foreach (DataRow dr in CoManagerID_data.Rows)
{
CoManagerID += "'" + dr["ManagerId"] + "',";
}
CoManagerID = CoManagerID.TrimEnd(',');
}
}
//To get list of managers under logged in manager for two level approval
string InnerManagers = "''";
DataTable dtinnermanager = db_connection.ReturnDataTable("Select Emp_Code From EmployeeMaster Where ManagerID='" + employee_id + "' And Ismanager=1");
if (dtinnermanager.Rows.Count > 0)
{
foreach (DataRow dr in dtinnermanager.Rows)
{
InnerManagers += ",'" + dr["Emp_Code"] + "'";
}
InnerManagers = InnerManagers.TrimEnd(',');
}
//get list of branches assigned to logged in manager hr
BranchList = "'Empty',";
branchqry = "Select BranchCode From TbManagerHrBranchMapping Where ManagerID='" + employee_id + "'";
branch_list_table = db_connection.ReturnDataTable(branchqry);
//make list of Branchs
if (branch_list_table.Rows.Count > 0)
{
foreach (DataRow dr in branch_list_table.Rows)
{
BranchList += "'" + dr["BranchCode"] + "',";
}
BranchList = BranchList.TrimEnd(',');
}
if (user_access_level == 0) //admin
{
query = @"select * from ( Select L1.leave_id as leave_id,L1.empid,E.Emp_Name as 'emp_name',LM.LeaveName as 'leavename', L1.StartDate as 'From',L1.EndDate as 'To',LS.Leave_Status_text,
L1.Flag as flag,L1.hl_status,L1.Remarks,L1.ReasonForLeave, L1.ApprovedbyName,row_number() over(order by L1.leave_id desc) as rcount from
Leave1 L1 JOIN EmployeeMaster E on E.Emp_Code=L1.EMPID JOIN LeaveMaster LM ON L1.LeaveType = LM.LeaveCode JOIN Leave_Status LS on LS.Leave_Status_id = L1.Flag ";
}
if (user_access_level == 3) //HR
{
self_query = @"select * from ( Select L1.leave_id as leave_id,L1.empid,E.Emp_Name as 'emp_name',LM.LeaveName as 'leavename', L1.StartDate as 'From',L1.EndDate as 'To',LS.Leave_Status_text,
L1.Flag as flag,L1.hl_status,L1.Remarks,L1.ReasonForLeave, L1.ApprovedbyName,row_number() over(order by L1.leave_id desc) as rcount from
Leave1 L1 JOIN EmployeeMaster E on E.Emp_Code=L1.EMPID JOIN LeaveMaster LM ON L1.LeaveType = LM.LeaveCode JOIN Leave_Status LS on LS.Leave_Status_id = L1.Flag and E.Emp_Code='" + employee_id + "' ";
query = @"select * from ( Select LTMT.leave_id as leave_id,L1.empid,E.Emp_Name as 'emp_name',LM.LeaveName as 'leavename', L1.StartDate as 'From',L1.EndDate as 'To',LS.Leave_Status_text,
LTMT.LeaveStatus as flag,L1.hl_status,L1.Remarks,L1.ReasonForLeave, L1.ApprovedbyName,row_number() over(order by LTMT.leave_id desc) as rcount from";
query += " ( select Distinct(LeaveApplicationID) as leave_id, max(LeaveStatus) as LeaveStatus from LeaveTransactionMaster L1 where L1.ActionEmpCode='" + employee_id + "' group by L1.LeaveApplicationID ) LTMT";
query += @" JOIN Leave1 L1 on L1.Leave_id=LTMT.leave_id JOIN EmployeeMaster E on E.Emp_Code=L1.EMPID
JOIN LeaveMaster LM ON L1.LeaveType = LM.LeaveCode JOIN Leave_Status LS on LS.Leave_Status_id = LTMT.LeaveStatus where ";
query += " ( E.Emp_Code in (Select Emp_Code from EmployeeMaster where ManagerID='" + employee_id + "') or e.Emp_Branch In(" + BranchList + ")) ";
}
if (user_access_level == 1 && (!string.IsNullOrEmpty(CoManagerID)))//Manager and CoManager
{
self_query = @"select * from ( Select L1.leave_id as leave_id,L1.empid,E.Emp_Name as 'emp_name',LM.LeaveName as 'leavename', L1.StartDate as 'From',L1.EndDate as 'To',LS.Leave_Status_text,
L1.Flag as flag,L1.hl_status,L1.Remarks,L1.ReasonForLeave, L1.ApprovedbyName,row_number() over(order by L1.leave_id desc) as rcount from
Leave1 L1 JOIN EmployeeMaster E on E.Emp_Code=L1.EMPID JOIN LeaveMaster LM ON L1.LeaveType = LM.LeaveCode JOIN Leave_Status LS on LS.Leave_Status_id = L1.Flag and E.Emp_Code='" + employee_id + "' ";
query = @"select * from ( Select LTMT.leave_id as leave_id,L1.empid,E.Emp_Name as 'emp_name',LM.LeaveName as 'leavename', L1.StartDate as 'From',L1.EndDate as 'To',LS.Leave_Status_text,
LTMT.LeaveStatus as flag,L1.hl_status,L1.Remarks,L1.ReasonForLeave, L1.ApprovedbyName,row_number() over(order by LTMT.leave_id desc) as rcount from";
query += " ( select Distinct(LeaveApplicationID) as leave_id, max(LeaveStatus) as LeaveStatus from LeaveTransactionMaster L1 where L1.ActionEmpCode='" + employee_id + "' group by L1.LeaveApplicationID ) LTMT";
query += @" JOIN Leave1 L1 on L1.Leave_id=LTMT.leave_id JOIN EmployeeMaster E on E.Emp_Code=L1.EMPID
JOIN LeaveMaster LM ON L1.LeaveType = LM.LeaveCode JOIN Leave_Status LS on LS.Leave_Status_id = LTMT.LeaveStatus where ";
query += " ( E.Emp_Code in (Select Emp_Code from EmployeeMaster where ManagerID in ('" + employee_id + "'," + CoManagerID + "))) ";
}
if (user_access_level == 1 && (string.IsNullOrEmpty(CoManagerID)))//only Manager
{
self_query = @"select * from ( Select L1.leave_id as leave_id,L1.empid,E.Emp_Name as 'emp_name',LM.LeaveName as 'leavename', L1.StartDate as 'From',L1.EndDate as 'To',LS.Leave_Status_text,
L1.Flag as flag,L1.hl_status,L1.Remarks,L1.ReasonForLeave, L1.ApprovedbyName,row_number() over(order by L1.leave_id desc) as rcount from
Leave1 L1 JOIN EmployeeMaster E on E.Emp_Code=L1.EMPID JOIN LeaveMaster LM ON L1.LeaveType = LM.LeaveCode JOIN Leave_Status LS on LS.Leave_Status_id = L1.Flag and E.Emp_Code='" + employee_id + "' ";
query = @"select * from ( Select LTMT.leave_id as leave_id,L1.empid,E.Emp_Name as 'emp_name',LM.LeaveName as 'leavename', L1.StartDate as 'From',L1.EndDate as 'To',LS.Leave_Status_text,
LTMT.LeaveStatus as flag,L1.hl_status,L1.Remarks,L1.ReasonForLeave, L1.ApprovedbyName,row_number() over(order by LTMT.leave_id desc) as rcount from";
query += " ( select Distinct(LeaveApplicationID) as leave_id, max(LeaveStatus) as LeaveStatus from LeaveTransactionMaster L1 where L1.ActionEmpCode='" + employee_id + "' group by L1.LeaveApplicationID ) LTMT";
query += @" JOIN Leave1 L1 on L1.Leave_id=LTMT.leave_id JOIN EmployeeMaster E on E.Emp_Code=L1.EMPID
JOIN LeaveMaster LM ON L1.LeaveType = LM.LeaveCode JOIN Leave_Status LS on LS.Leave_Status_id = LTMT.LeaveStatus where ";
query += " ( E.Emp_Code in (Select Emp_Code from EmployeeMaster where ManagerID='" + employee_id + "'))";
}
if (user_access_level == 2)//only Employee
{
self_query = @"select * from ( Select L1.leave_id as leave_id,L1.empid,E.Emp_Name as 'emp_name',LM.LeaveName as 'leavename', L1.StartDate as 'From',L1.EndDate as 'To',LS.Leave_Status_text,
L1.Flag as flag,L1.hl_status,L1.Remarks,L1.ReasonForLeave, L1.ApprovedbyName,row_number() over(order by L1.leave_id desc) as rcount from
Leave1 L1 JOIN EmployeeMaster E on E.Emp_Code=L1.EMPID JOIN LeaveMaster LM ON L1.LeaveType = LM.LeaveCode JOIN Leave_Status LS on LS.Leave_Status_id = L1.Flag and E.Emp_Code='" + employee_id + "' ";
}
if (!is_filter)
{
if (!string.IsNullOrEmpty(query))
{
query += " ) a where rcount > " + start_row + " and rcount < " + number_of_record;
}
if (!string.IsNullOrEmpty(self_query))
{
self_query += " ) a where rcount > " + start_row + " and rcount < " + number_of_record;
}
}
if (is_filter)
{
query = page_object.GetFilterQueryNormalLeave(filters, query);
query += " ) a ";
query += "order by a.empid OFFSET " + start_row + " ROWS FETCH NEXT " + number_of_record + " ROWS ONLY ";
if (!string.IsNullOrEmpty(self_query))
{
self_query = page_object.GetFilterQueryNormalLeave(filters, self_query);
self_query += " ) a ";
self_query += "order by a.empid OFFSET " + start_row + " ROWS FETCH NEXT " + number_of_record + " ROWS ONLY ";
}
}
if (!string.IsNullOrEmpty(self_query))
{
self_data_table = db_connection.ReturnDataTable(self_query);
}
if (!string.IsNullOrEmpty(query))
{
normal_leave_data = db_connection.ReturnDataTable(query);
if (self_data_table.Rows.Count > 0)
{
foreach (DataRow dr in self_data_table.Rows)
{
normal_leave_data.Rows.Add(dr.ItemArray);
}
}
}
else
{
normal_leave_data = self_data_table;
}
return_object.status = "success";
return_object.return_data = JsonConvert.SerializeObject(normal_leave_data, Formatting.Indented);
}
catch (Exception ex)
{
Logger.LogException(ex, page, "GET_NORMAL_LEAVES");
return_object.status = "error";
return_object.return_data = "An error occurred while performing this operation. Please try again. If the error persists, please contact Support.";
}
finally
{
page_object.Dispose();
}
return(return_object);
}
public static ReturnObject GetLWPLeavesData(int page_number, bool is_filter, string filters)
{
leave_details page_object = new leave_details();
DBConnection db_connection = new DBConnection();
ReturnObject return_object = new ReturnObject();
DataTable leaveList = new DataTable();
DataTable branch_list_data = new DataTable();
DataTable CoManagerID_data = new DataTable();
string user_name = string.Empty,
employee_id = string.Empty,
query = string.Empty,
CoManagerID = string.Empty,
BranchList = string.Empty;
int user_access_level = 0,
start_row = 0,
number_of_record = 0,
IsDelegationManager = 0;
try
{
user_name = HttpContext.Current.Session["username"].ToString();
employee_id = HttpContext.Current.Session["employee_id"].ToString();
user_access_level = Convert.ToInt32(HttpContext.Current.Session["access_level"]);
start_row = (page_number - 1) * 30;
number_of_record = page_number * 30 + 1;
//check employee is Delegation Manager or not if so get his CoManagerID
IsDelegationManager = Convert.ToInt32(db_connection.ExecuteQuery_WithReturnValueString("Select COUNT(DelidationManagerID) from TbAsignDelegation Where DelidationManagerID='" + employee_id + "' And DeliationStatus=1 and Convert(date,Getdate())>=Convert(date,Fromdate) And Convert(date,Getdate())<=Convert(date,Todate)"));
if (IsDelegationManager > 0)
{
query = "Select ManagerId from TbAsignDelegation Where DelidationManagerID='" + employee_id + "' And DeliationStatus=1 and Convert(date,Getdate())>=Convert(date,Fromdate) And Convert(date,Getdate())<=Convert(date,Todate)";
CoManagerID_data = db_connection.ReturnDataTable(query);
if (CoManagerID_data.Rows.Count > 0)
{
foreach (DataRow dr in CoManagerID_data.Rows)
{
CoManagerID += "'" + dr["ManagerId"] + "',";
}
CoManagerID = CoManagerID.TrimEnd(',');
}
}
//get list of branches assigned to logged in manager hr
BranchList = "'Empty',";
query = "Select BranchCode From TbManagerHrBranchMapping Where ManagerID='" + employee_id + "'";
branch_list_data = db_connection.ReturnDataTable(query);
query = string.Empty;
query = page_object.GetLWPLeavesBaseQuery(); //read main query
if (branch_list_data.Rows.Count > 0)
{
foreach (DataRow dr in branch_list_data.Rows)
{
BranchList += "'" + dr["BranchCode"] + "',";
}
}
BranchList = BranchList.TrimEnd(',');
//Validate CoManagerID
if (string.IsNullOrEmpty(CoManagerID))
{
CoManagerID = "'Empty'";
}
//modify query as per access level
if (user_access_level == 0)//Admin
{
query += " ";
}
else if (user_access_level == 3)//HR
{
query += " and e.Emp_Branch In(" + BranchList + ") ";
}
else if (user_access_level == 1 && !string.IsNullOrEmpty(CoManagerID) && CoManagerID != "'Empty'")//Manager and CoManager
{
query += " and l.EmpID In(Select Emp_Code From EmployeeMaster Where Emp_Code In('" + employee_id + "') OR ManagerID In('" + employee_id + "'," + CoManagerID + ")) Or e.Emp_Branch In(" + BranchList + ") ";
}
else if (user_access_level == 1 && CoManagerID == "'Empty'")//Only Manager
{
query += " and e.Emp_Branch In(" + BranchList + ") or Emp_Code in ( select distinct(Emp_Code) from EmployeeMaster where (managerId='" + employee_id + "' or Emp_Code='" + employee_id + "') and Emp_Status=1 ) ";
}
else
{
query += " and e.Emp_Code='" + employee_id + "'";// Only Employee
}
if (!is_filter)
{
//query += " and l.flag=1 ";
query += " ) a where row > " + start_row + " and row < " + number_of_record;
}
if (is_filter)
{
query += " ) a ";
query = page_object.GetFilterQuery(filters, query);
query = query + "order by a.emp_id OFFSET " + start_row + " ROWS FETCH NEXT " + number_of_record + " ROWS ONLY ";
}
// query += " order by a.Leave_id desc";
leaveList = db_connection.ReturnDataTable(query);
return_object.status = "success";
return_object.return_data = JsonConvert.SerializeObject(leaveList, Formatting.Indented);
}
catch (Exception ex)
{
Logger.LogException(ex, page, "GET_LWP_LEAVES");
return_object.status = "error";
return_object.return_data = "An error occurred while performing this operation. Please try again. If the error persists, please contact Support.";
}
return(return_object);
}
public ActionResult AddorEditLeaves(AddorEditLeave ael)
{
if (ModelState.IsValid)
{
using (mmpEntities mP = new mmpEntities())
{
/*#region Leave Name already assigned
* var isLeaveNameAssigned = IsCategoryTypeAssigned(ael.leave_name, ael.ctd_id, 1);
* if (isLeaveNameAssigned)
* {
* ModelState.AddModelError("LeaveAssigned", "Leave Name is already assigned");
* return View(ael);
* }
#endregion*/
#region Leave Code already assigned
var isLeaveCodeAssigned = IsCategoryTypeAssigned(ael.code, ael.ctd_id, 0);
if (isLeaveCodeAssigned)
{
ModelState.AddModelError("LeaveAssigned", "Leave Code is already assigned");
return(View(ael));
}
#endregion
if (ael.ctd_id == 0)
{
category_type_details ctd = new category_type_details()
{
ctd_name = ael.leave_name,
ctd_code = ael.code,
category_id = ael.category_id,
ctd_created_at = DateTime.Now
};
mP.category_type_details.Add(ctd);
mP.SaveChanges();
leave_details ld = new leave_details()
{
no_of_leaves = ael.no_of_leaves,
encashable = ael.encashable,
carry_forward = ael.carry_forward,
category_type_id = ctd.ctd_id
};
mP.leave_details.Add(ld);
mP.SaveChanges();
return(Json(new { success = true, message = "Saved Successfully" }, JsonRequestBehavior.AllowGet));
}
else
{
category_type_details ctd = new category_type_details()
{
ctd_id = ael.ctd_id,
ctd_name = ael.leave_name,
ctd_code = ael.code,
category_id = ael.category_id,
ctd_created_at = ael.ctd_created_at,
ctd_updated_at = DateTime.Now
};
leave_details ld = new leave_details()
{
id = ael.leave_details_id,
no_of_leaves = ael.no_of_leaves,
encashable = ael.encashable,
carry_forward = ael.carry_forward,
category_type_id = ctd.ctd_id
};
mP.Entry(ctd).State = EntityState.Modified;
mP.Entry(ld).State = EntityState.Modified;
mP.SaveChanges();
return(Json(new { success = true, message = "Updated Successfully" }, JsonRequestBehavior.AllowGet));
}
}
}
else
{
return(View(ael));
}
}