public static ReturnObject DoAction(string selected_tab, int action, string comments, string selected_rows) { leave_approve page_object = new leave_approve(); DBConnection db_connection = new DBConnection(); ReturnObject return_object = new ReturnObject(); List <string> selected_leaves = JsonConvert.DeserializeObject <List <string> >(selected_rows); String Message = string.Empty; string query = string.Empty, leave_status = string.Empty; int leave_id = 0, current_leave_flag = 0, imediatemnrflag = 0; try { switch (action) { case 2: leave_status = "Approved"; break; case 3: leave_status = "Declined"; break; case 4: leave_status = "Cancelled"; break; } for (int i = 0; i < selected_leaves.Count; i++) { leave_id = Convert.ToInt32(selected_leaves[i].ToString()); if (selected_tab == "leave") { query = "select flag from leave1 where leave_id='" + leave_id + "'"; } else { query = "select flag from lossonpay where leave_id='" + leave_id + "'"; } current_leave_flag = Convert.ToInt32(db_connection.ExecuteQuery_WithReturnValueString(query)); if (current_leave_flag == 2 && action == 3) { return_object.status = "error"; return_object.return_data = "Leave is approved and cannot be declined. Please cancel the leave"; return(return_object); } if (current_leave_flag == 2 && action == 2) { return_object.status = "error"; return_object.return_data = "Leave is approved already . "; return(return_object); } if (current_leave_flag == 4) { return_object.status = "error"; return_object.return_data = "Leave is already canceled"; return(return_object); } if (current_leave_flag == 3) { return_object.status = "error"; return_object.return_data = "Leave is already Declined"; return(return_object); } /* STATUS MAP * Submitted = 1 * Approved = 2 * Declined = 3 * Canceled = 4 */ // As the leave has been submitted further actions possible are Approving, Declining or Cancelling. if ((current_leave_flag == 1) && (action == 2 || action == 3 || action == 4)) { Message = page_object.LeaveAction(leave_id, action, leave_status, selected_tab, imediatemnrflag, comments); } // As the leave has been APPROVED, only CANCELLING the leave is possible. if ((current_leave_flag == 2) && (action == 4)) { Message = page_object.LeaveAction(leave_id, action, leave_status, selected_tab, imediatemnrflag, comments); } if (current_leave_flag == 3) { } // As the leave has been DECLINED, no further action is possible. if (current_leave_flag == 4) { } // As the leave has been CANCELLED, no further action is possible. if (current_leave_flag == 5) { action = 2; imediatemnrflag = 2; Message = page_object.LeaveAction(leave_id, action, leave_status, selected_tab, imediatemnrflag, comments); } } return_object.status = "success"; if (Message != "") { return_object.return_data = Message; } else { return_object.return_data = "Leave " + leave_status + " successfully!"; } } catch (Exception ex) { Logger.LogException(ex, page, "UPDATE_LEAVE_APPROVAL_STATUS"); return_object.status = "error"; return_object.return_data = "An error occurred while updating Leave Approval Status. 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_approve page_object = new leave_approve(); DBConnection db_connection = new DBConnection(); ReturnObject return_object = new ReturnObject(); DataTable lwp_leave_data = new DataTable(); DataTable branch_list_table = new DataTable(); DataTable CoManagerID_data = new DataTable(); string user_name = string.Empty, employee_id = string.Empty, 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 { start_row = (page_number - 1) * 30; number_of_record = page_number * 30 + 1; // 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(); query = page_object.GetLWPLeavesBaseQuery(); //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(','); } //check CoManagerID if (string.IsNullOrEmpty(CoManagerID)) { CoManagerID = "'Empty'"; } //change query based on user_access_level if (user_access_level == 0) { query += " and e.Emp_Code !='" + employee_id + "'"; } else if (user_access_level == 3) { query += " And e.Emp_Branch In(" + BranchList + ") "; } else if (user_access_level == 1 && !string.IsNullOrEmpty(CoManagerID) && CoManagerID != "'Empty'") { query += " and (E.ManagerID In('" + employee_id + "'," + CoManagerID + ") and L.Approvallevel in (0,2)) Or (L.EmpID In(Select Emp_Code from employeemaster Where ManagerID In(" + CoManagerID + "," + InnerManagers + ") And L.Approvallevel=1 And L.Flag=5 And L.MFlag=1)) "; query += " and e.Emp_Code in (select distinct(Emp_Code) from EmployeeMaster where managerId='" + employee_id + "' and Emp_Status=1)"; } else if (user_access_level == 1 && CoManagerID == "'Empty'") { query += " And (E.ManagerID In('" + employee_id + "') and L.Approvallevel in (0,2)) Or (L.EmpID In(Select Emp_Code from employeemaster Where ManagerID In(" + InnerManagers + ") And L.Approvallevel=1 And L.Flag=5 And L.MFlag=1)) "; query += " or e.Emp_Code in (select distinct(Emp_Code) from EmployeeMaster where managerId='" + employee_id + "' and Emp_Status=1)"; } else { query += " and 1=0 "; } if (!is_filter) { query += " and l.flag=1 "; } query += " ) a where rcount > " + start_row + " and rcount < " + number_of_record; if (is_filter) { query = page_object.GetFilterQuery(filters, query); } lwp_leave_data = db_connection.ReturnDataTable(query); return_object.status = "success"; return_object.return_data = JsonConvert.SerializeObject(lwp_leave_data, Formatting.Indented); } catch (Exception ex) { Logger.LogException(ex, page, "GET_LOP_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); }
private string LeaveAction(int leave_id, int action, string leave_status, string selected_tab, int imediatemnrflag, string comments) { leave_approve page_object = new leave_approve(); DBConnection db_connection = new DBConnection(); DataTable leave_details = new DataTable(); Hashtable leave_details_data = new Hashtable(); int user_access_level = 0, WFStatus = 0; string Message = "", LeaveType = "", Employee_id = "", Approvedby = ""; string query = string.Empty, wf_code = string.Empty, ActionEmpID = string.Empty, CoActionEmpID = string.Empty; int PreviousFlag = 0, CurrentFlag = 0; CurrentFlag = action; ActionEmpID = HttpContext.Current.Session["username"].ToString(); Approvedby = HttpContext.Current.Session["employee_name"].ToString(); user_access_level = Convert.ToInt32(HttpContext.Current.Session["access_level"]); if (string.IsNullOrEmpty(Approvedby)) { Approvedby = "Admin"; } if (user_access_level != 0) { query = "select max(WFStatus) from LeaveTransactionMaster where ActionEmpCode='" + ActionEmpID + "' and and leaveapplicationid=" + leave_id; WFStatus = db_connection.ExecuteQuery_WithReturnValueInteger(query); if (WFStatus == 1) { Message = "Already you have taken action."; return(Message); } } /* * leave_details_data.Add("pialevel", user_access_level); * leave_details_data.Add("pileave_id", leave_id); * leave_details_data.Add("pistat", action); * leave_details_data.Add("pitxt", selected_tab); * leave_details_data.Add("piapprovedby", Approvedby); * leave_details_data.Add("picomments", comments); * * db_connection.ExecuteStoredProcedureWithHashtable_WithoutReturn("spapproveall", leave_details_data); * * Message=page_object.UpdateLeave(leave_id, action, leave_status, selected_tab, imediatemnrflag); * */ query = "Select EmpID,LeaveType,Flag from Leave1 where leave_id=" + leave_id; leave_details = db_connection.ReturnDataTable(query); Employee_id = leave_details.Rows[0]["EmpID"].ToString(); LeaveType = leave_details.Rows[0]["LeaveType"].ToString(); PreviousFlag = Convert.ToInt32(leave_details.Rows[0]["Flag"].ToString()); /*what if after final approval if anybody wants to cancel/declien leave*/ if (PreviousFlag == 2 && (CurrentFlag == 3 || CurrentFlag == 4)) { /*write code to update flag and revert leave balance*/ /*update status*/ Hashtable leave_update = new Hashtable(); /*revert leave balance*/ leave_update.Add("Flag", CurrentFlag); leave_update.Add("Leaveid", leave_id); leave_update.Add("actioncomment", comments); leave_update.Add("Imdtmngrflag", imediatemnrflag); db_connection.ExecuteStoredProcedureWithHashtable_WithoutReturn_pi("SpUpdateLeaveStatus", leave_update); Hashtable leave_delete = new Hashtable(); leave_delete.Add("Leaveid", leave_id); db_connection.ExecuteStoredProcedureWithHashtable_WithoutReturn_pi("spDeleteLeave", leave_delete); /*this is to get WFCode from procedure by passing empid and leavetype*/ wf_code = db_connection.ExecuteProcedureInOutParameters("ReadApprovalWFCode", Employee_id, LeaveType, "WorkFlowCode"); /*add record into leave transaction*/ query = "insert into LeaveTransactionMaster values (" + leave_id + ",'" + wf_code + "',1,1,'" + ActionEmpID + "',NULL,CONVERT(DATE,GETDATE())," + CurrentFlag + ") "; db_connection.ExecuteQuery_WithOutReturnValue(query); } if (PreviousFlag == 1) { /*this is to get WFCode from procedure by passing empid and leavetype*/ wf_code = db_connection.ExecuteProcedureInOutParameters("ReadApprovalWFCode", Employee_id, LeaveType, "WorkFlowCode"); if (!string.IsNullOrEmpty(wf_code)) { leave_details_data.Add("Action_EmpCode", ActionEmpID); leave_details_data.Add("LeaveApplication_ID", leave_id); leave_details_data.Add("WorkFlow_Code", wf_code); leave_details_data.Add("LeaveStatus", CurrentFlag); leave_details_data.Add("AccessLevel", user_access_level); db_connection.ExecuteStoredProcedureWithHashtable_WithoutReturn("ProcessLeaveApplication", leave_details_data); } else { /*what if emp doesn't belong to any group*/ } } page_object.SendMail(leave_id, selected_tab, leave_status); return(Message); }
public static ReturnObject GetNormalLeavesData(int page_number, bool is_filter, string filters) { leave_approve page_object = new leave_approve(); 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(); string user_name = string.Empty, employee_id = string.Empty, 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 += " 1=1 "; } if (user_access_level == 3) //HR { query += " (1=1 or 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 { query += " (1=1 or 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 { query += " (1=1 or E.Emp_Code in (Select Emp_Code from EmployeeMaster where ManagerID='" + employee_id + "'))"; } if (!is_filter) { query += " ) a where a.flag = 1 and 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 "; } normal_leave_data = db_connection.ReturnDataTable(query); 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); }