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)); } }