public static ReturnObject GetEmployeeData(int page_number, string filters)
    {
        masters_ot_eligibility page_object = new masters_ot_eligibility();
        DBConnection           db_connection = new DBConnection();
        DataTable    shifts_data_table = new DataTable();
        ReturnObject return_object = new ReturnObject();
        string       employee_id, query = string.Empty;
        int          start_row = (page_number - 1) * 30;
        int          number_of_record = page_number * 30 + 1;

        if (HttpContext.Current.Session["username"] == null)  // checking session expired or not
        {
            return_object = page_object.DoLogout();
        }
        else
        {
            try
            {
                employee_id = HttpContext.Current.Session["employee_id"].ToString();
                // if employee is logged in then showing only that employee   data  who belongs to those (  done for royal group client first then implemnted in standard as well )
                if (employee_id != "")
                {
                    query = "select DISTINCT EmpCode as employee_code, EmpName as employee_name, CompanyName as company_name, CompanyCode as company_code, branchname as branch_name, deptname as department_name, designame as designation_name, emp_card_no as employee_card_number, OT_Eligibility as ot_eligibility from ( select e.emp_code as EmpCode, e.emp_name as EmpName, c.CompanyName, c.CompanyCode, b.branchname, d.deptname, de.designame, e.emp_card_no, e.OT_Eligibility, ROW_NUMBER() OVER (ORDER BY e.emp_code) as row from employeemaster e left join companymaster c on e.emp_company = c.companycode left join branchmaster b on e.emp_branch = b.branchcode left join deptmaster d on e.emp_department = d.deptcode left join desigmaster de on e.emp_designation = de.desigcode where 1=1 and  e.emp_code in ( select empid from FetchEmployees('" + employee_id + "','')) ";
                }
                else
                {
                    query = "select DISTINCT EmpCode as employee_code, EmpName as employee_name, CompanyName as company_name, CompanyCode as company_code, branchname as branch_name, deptname as department_name, designame as designation_name, emp_card_no as employee_card_number, OT_Eligibility as ot_eligibility from ( select e.emp_code as EmpCode, e.emp_name as EmpName, c.CompanyName, c.CompanyCode, b.branchname, d.deptname, de.designame, e.emp_card_no, e.OT_Eligibility, ROW_NUMBER() OVER (ORDER BY e.emp_code) as row from employeemaster e left join companymaster c on e.emp_company = c.companycode left join branchmaster b on e.emp_branch = b.branchcode left join deptmaster d on e.emp_department = d.deptcode left join desigmaster de on e.emp_designation = de.desigcode where 1=1 ";
                }

                query += " and e.Emp_Status='1'";

                query = page_object.GetFilterQuery(filters, query);

                query += " group by e.emp_code, e.emp_name, c.CompanyCode, c.CompanyName, b.branchcode, b.branchname, d.deptname, de.designame, e.emp_card_no, e.OT_Eligibility) a where row > " + start_row + " and row < " + number_of_record;

                shifts_data_table = db_connection.ReturnDataTable(query);

                return_object.status      = "success";
                return_object.return_data = JsonConvert.SerializeObject(shifts_data_table, Formatting.Indented);
            }
            catch (Exception ex)
            {
                Logger.LogException(ex, page, "GET_EMPLOYEE_DATA");

                return_object.status      = "error";
                return_object.return_data = "An error occurred while loading Employee data. Please try again. If the error persists, please contact Support.";

                throw;
            }
            finally
            {
                page_object.Dispose();
            }
        }

        return(return_object);
    }
    public static ReturnObject GetOtherData(string company_code)
    {
        masters_ot_eligibility page_object   = new masters_ot_eligibility();
        DBConnection           db_connection = new DBConnection();
        DataTable    temp_data_table         = new DataTable();
        DataSet      return_data_set         = new DataSet();
        ReturnObject return_object           = new ReturnObject();
        string       department_query        = string.Empty;
        string       designation_query       = string.Empty;
        string       branch_query            = string.Empty;

        if (HttpContext.Current.Session["username"] == null)  // checking session expired or not
        {
            return_object = page_object.DoLogout();
        }
        else
        {
            try
            {
                department_query          = "Select DeptName as department_name, DeptCode as department_code from DeptMaster where CompanyCode='" + company_code + "' order by DeptCode";
                temp_data_table           = db_connection.ReturnDataTable(department_query);
                temp_data_table.TableName = "department";
                return_data_set.Tables.Add(temp_data_table);

                designation_query         = "Select desigcode as designation_code, designame as designation_name from DesigMaster where CompanyCode='" + company_code + "' order by desigcode";
                temp_data_table           = db_connection.ReturnDataTable(designation_query);
                temp_data_table.TableName = "designation";
                return_data_set.Tables.Add(temp_data_table);

                branch_query              = "Select DISTINCT BranchCode as branch_code, BranchName as branch_name from BranchMaster where CompanyCode='" + company_code + "' order by BranchName";
                temp_data_table           = db_connection.ReturnDataTable(branch_query);
                temp_data_table.TableName = "branch";
                return_data_set.Tables.Add(temp_data_table);

                return_object.status      = "success";
                return_object.return_data = JsonConvert.SerializeObject(return_data_set, Formatting.Indented);
            }
            catch (Exception ex)
            {
                Logger.LogException(ex, page, "GET_OTHER_DATA");

                return_object.status      = "error";
                return_object.return_data = "An error occurred while loading data for OT Eligibility. Please try again. If the error persists, please contact Support.";

                throw;
            }
        }

        return(return_object);
    }
    public static ReturnObject SaveOTEligibility(string employees, string action)
    {
        masters_ot_eligibility page_object    = new masters_ot_eligibility();
        DBConnection           db_connection  = new DBConnection();
        ReturnObject           return_object  = new ReturnObject();
        List <string>          employees_list = JsonConvert.DeserializeObject <List <string> >(employees);
        string query = string.Empty;
        int    i     = 0;

        if (HttpContext.Current.Session["username"] == null)  // checking session expired or not
        {
            return_object = page_object.DoLogout();
        }
        else
        {
            try
            {
                for (i = 0; i < employees_list.Count; i++)
                {
                    query = "update EmployeeMaster set OT_Eligibility='" + action + "' where Emp_Code='" + employees_list[i] + "'";
                    db_connection.ExecuteQuery_WithOutReturnValue(query);
                }

                return_object.status      = "success";
                return_object.return_data = "OT Eligibility details saved successfully!";
            }
            catch (Exception ex)
            {
                Logger.LogException(ex, page, "SAVE_OT_ELIGIBILITY");

                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.";

                throw;
            }
        }

        return(return_object);
    }