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