<?php

class Employee {

    private $db;
    private $year_id;
    private $year_id_actual;
    private $info = array();
    public $stop_transaction_start_end = false;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->year_id = Session::getCurrentYearID();
        $this->year_id_actual = Session::getCurrentYearIDActual();
        $this->permissions = Session::getPermissionsObject();
    }

    public function setData(array $data) {
        $this->info = $data;
        return $this;
    }

    public function updateDepartment($Department_ID) {
        $logged_id = Session::getUserID();
        $this->db->prepare_Stmt('UPDATE tbl_hrm_branch SET Branch_Name= ? ,Logged_ID=? WHERE Branch_ID =?', [&$this->info['Branch_Name'], &$logged_id, &$Department_ID]);
        $error = $this->db->error();
        return $error;
    }

    public function getMaxEmpWrkID() {
        /* where Employee_Work_ID like "' . $row['department_prefix'] . '%" */
        $query1 = $this->db->query('select Employee_Work_ID as work_id from tbl_hrm_employee order by length(Employee_Work_ID) desc, (Employee_Work_ID+0) desc limit 1');
        $row1 = $this->db->assoc($query1);
        if (empty($row1['work_id'])) {
            $work_id_number = /* $row['department_prefix'] . */ '1';
        } else {
            //            $work_id_number = trim(str_replace(range(0, 9), '', $row1['work_id'])) . (preg_replace("/[^0-9.]/", '', $row1['work_id']) + 1);
            $work_id_number = $row1['work_id'] + 1;    //$row['department_prefix'] . ((str_replace($row['department_prefix'], "", $row1['work_id']) + 1));
        }
        $work_id_number = str_pad($work_id_number, 4, '0', STR_PAD_LEFT);

        return $work_id_number;
    }

    public function create($hidden = false) {

        $this->db->query('START TRANSACTION');
        $is_hidden = $this->info['is_actual'];




        $work_id_number = !empty($this->info['Employee_Work_ID']) ? $this->info['Employee_Work_ID'] : self::getMaxEmpWrkID();

        $this->info['Employee_Wife_Nationality'] = !empty($this->info['Employee_Wife_Nationality']) ? $this->info['Employee_Wife_Nationality'] : NULL;
        $email = !empty($this->info['Employee_Email']) ? $this->info['Employee_Email'] : null;

        $mydate = date("Y-m-d H:i:s");
        $is_nssf = isset($this->info['is_nssf']) && $this->info['is_nssf'] = 'on' ? 1 : 0;
        $stop_housing_payroll = isset($this->info['stop_housing_payroll']) && $this->info['stop_housing_payroll'] = 'on' ? 1 : 0;
        $holiday_is_fixed = isset($this->info['holiday_is_fixed']) && $this->info['holiday_is_fixed'] = 'on' ? 1 : 0;
        $early_payroll = isset($this->info['early_payroll']) && $this->info['early_payroll'] = 'on' ? 1 : 0;
        $this->info['holiday_nb'] = $holiday_is_fixed ? (empty($this->info['holiday_nb']) ? smc_session()['default_setting']['default_nb_holiday'] : $this->info['holiday_nb'] ) : 0;
        $this->info['leave_date'] = empty($this->info['leave_date']) ? null : $this->info['leave_date'];
        $data = [&$this->info['Employee_FirstName'],
            &$this->info['Employee_MiddleName'],
            &$this->info['Employee_LastName'],
            &$this->info['second_lang_name'],
            &$this->info['Employee_SocialSecurity'],
            &$this->info['Employee_IsSmoker'],
            &$this->info['Employee_Gender'],
            &$this->info['Employee_DOB'],
            &$this->info['IS_Employee'],
            &$this->info['Employee_RecordNumber'],
            &$this->info['Employee_SocialSecurity_Date'],
            &$is_nssf,
            &$this->info['nssf_salary'],
            &$this->info['other_charges'],
            &$this->info['basic_salary'],
            &$this->info['basic_salary_currency'],
            &$this->info['period_id'],
            //                 &$this->info['pay_period'],
            //                 &$this->info['unit_time'],
            //                 &$this->info['formula_id'],
            &$this->info['Employee_BloodGroup'],
            &$this->info['Employee_HomePhone'],
            &$this->info['Employee_MobilePhone'],
            &$work_id_number,
            &$email,
            &$this->info['Merital_Status_ID'],
            &$this->info['Employee_StartDate'],
            &$this->info['leave_date'],
            &$this->info['Employee_StreetAddress1'],
            &$this->info['Employee_City'],
            &$this->info['Employee_Country'],
            &$this->info['Branch_ID'],
            &$this->info['Paygrade_ID'],
            &$this->info['job_id'],
            &$this->info['Employee_Transportation'],
            &$this->info['trans_unit_time'],
            &$this->info['Employee_Allownace'],
            &$this->info['Employee_Lunch'],
            &$this->info['Employee_Phone_Recharge'],
            &$this->info['Shift_ID'],
            &$this->info[0],
            &$is_hidden,
            &$this->info['Employee_Nationality'],
            &$this->info['Employee_Location'],
            &$this->info['Employee_Major'],
            &$this->info['Employee_Education'],
            &$this->info['Employee_Problem'],
            &$this->info['Employee_Wife_Name'],
            &$this->info['Employee_Wife_DOB'],
            &$this->info['Employee_Wife_Address'],
            &$this->info['Employee_Wife_Phone'],
            &$this->info['Employee_Wife_Problem'],
            &$this->info['Employee_Wife_Work'],
            &$this->info['Employee_Wife_SSNF'],
            &$this->info['Employee_Wife_Nationality'],
            &$this->info['Wife_Works'],
            &$mydate,
            &$mydate,
            &$this->info['bank_id'],
            &$this->info['bank_account'],
            &$this->info['leave_days_nb'],
            &$this->info['holiday_nb'],
            &$holiday_is_fixed,
            &$this->info['previous_days_nb'],
            &$this->info['fingerprint_code'],
            &$this->info['shift_note'],
            &$this->info['location_address'],
            &$this->info['employee_level'],
            &$this->info['absence_type'],
            &$this->info['emp_charge1'],
            &$this->info['emp_charge2'],
            &$this->info['emp_charge3'],
            &$this->info['emp_charge4'],
            $stop_housing_payroll, $early_payroll
        ];


        $this->db->prepare_Stmt("INSERT INTO tbl_hrm_employee (
				Employee_FirstName,
				Employee_MiddleName,
				Employee_LastName,
                                second_lang_name,
				Employee_SocialSecurity,
				Employee_IsSmoker,
				Employee_Gender,
				Employee_DOB,
				IS_Employee,
				Employee_RecordNumber,
				Employee_SocialSecurity_Date,
                                is_nssf,
                                nssf_salary,
                                other_charges,
                                basic_salary,
                                basic_salary_currency,
                                period_id,
				Employee_BloodGroup,
				Employee_HomePhone,
				Employee_MobilePhone,
				Employee_Work_ID,
				Employee_Email,
				Merital_Status_ID,
				Employee_StartDate,leave_date,
				Employee_StreetAddress1,
				Employee_City,
				Employee_Country,
				Branch_ID,
				Paygrade_ID,
                                job_id,
				Employee_Transportation,trans_unit_time,
				Employee_Allownace,
				Employee_Lunch,
				Employee_Phone_Recharge,
				Shift_ID,
				Logged_ID,
				Is_Hidden,
                                Employee_Nationality,
                                Employee_Location,
                                Employee_Major,
                                Employee_Education,
                                Employee_Problem,
                                Employee_Wife_Name,
                                Employee_Wife_DOB,
                                Employee_Wife_Address,
                                Employee_Wife_Phone,
                                Employee_Wife_Problem,
                                Employee_Wife_Work,
                                Employee_Wife_SSNF,
                                Employee_Wife_Nationality,
                                Wife_Works,date_created,date_modified,bank_id,bank_account,leave_days_nb,holiday_nb,
                                holiday_is_fixed,previous_days_nb,fingerprint_code,shift_note,location_address,employee_level,absence_type,
                                emp_charge1,emp_charge2,emp_charge3,emp_charge4,stop_housing_payroll,early_payroll
			)
			VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", $data);
        $id = $this->db->id();
        $arr_prod = ['type' => 'emp', 'main_id' => $id, 'action' => 0, 'date_time' => $mydate,
            'user_id' => $this->info[0]];
        CommonFunctions::add_user_log($arr_prod);

        if (!empty($this->info['absences']) && $this->info['absence_type'] == 2) {
            $this->info['absences'] = json_decode($this->info['absences'], true);
            foreach ($this->info['absences'] as $ab) {
                $this->db->prepare_Stmt('INSERT INTO `tbl_hrm_emp_absence_setting`( `employee_id`, `absence_reason_id`,
                    `nb_days`) values(?,?,?) ', [$id, $ab['absence_reason_id'], $ab['nb_days']]);
            }
        }
        $error = $this->db->error();
        //        die(print_r($error));
        if (isset($_FILES["image_employee_File"]) && empty($error)) {
//            $id = self::getLastEmployeeID();
            $uploaddir = upload_path . 'employees/';
            $this->SaveUploadedPhoto("image_employee_File", $uploaddir . $id . '.jpg');
        }
        //	self::addSkills($id);
        //      $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function update() {
        $this->db->query('START TRANSACTION');
        $query = $this->db->query('select leave_days_nb,holiday_nb,holiday_is_fixed,previous_days_nb,remaining_days
            from tbl_hrm_employee where Employee_ID = "' . $this->info['Employee_ID'] . '"');
        $row = $this->db->assoc($query);
        $email = !empty($this->info['Employee_Email']) ? $this->info['Employee_Email'] : null;
        //        $department_id = $this->info['Branch_ID'];
        //
        //        $query = $this->db->prepare_Stmt('Select Branch_ID FROM tbl_hrm_employee WHERE Employee_ID = ? ', [&$this->info['Employee_ID']]);
        //        $old_row = $this->db->assoc($query);

        $work_id_number = !empty($this->info['Employee_Work_ID']) ? $this->info['Employee_Work_ID'] : self::getMaxEmpWrkID();
        //        if ($work_id_number == '' || $department_id != $old_row['Branch_ID']) {
        //
        //            $query = $this->db->prepare_Stmt('select department_prefix from tbl_department where department_id = ? ', [&$department_id]);
        //            $row = $this->db->assoc($query);
        //            //
        //            $query1 = $this->db->query('select max(Employee_Work_ID) as work_id from tbl_hrm_employee where Employee_Work_ID like "' . $row['department_prefix'] . '%" ');
        //            $row1 = $this->db->assoc($query1);
        //            //
        //            if (count($row1['work_id']) == 0) {
        //                $work_id_number = $row['department_prefix'] . '1';
        //            } else {
        ////            $work_id_number = trim(str_replace(range(0, 9), '', $row1['work_id'])) . (preg_replace("/[^0-9.]/", '', $row1['work_id']) + 1);
        //                $work_id_number = $row['department_prefix'] . ((str_replace($row['department_prefix'], "", $row1['work_id']) + 1));
        //            }
        //        }
        $mydate = date("Y-m-d H:i:s");
        $is_nssf = isset($this->info['is_nssf']) && $this->info['is_nssf'] = 'on' ? 1 : 0;
        $stop_housing_payroll = isset($this->info['stop_housing_payroll']) && $this->info['stop_housing_payroll'] = 'on' ? 1 : 0;
        $holiday_is_fixed = isset($this->info['holiday_is_fixed']) && $this->info['holiday_is_fixed'] = 'on' ? 1 : 0;
        $early_payroll = isset($this->info['early_payroll']) && $this->info['early_payroll'] = 'on' ? 1 : 0;
        $this->info['leave_date'] = empty($this->info['leave_date']) ? null : $this->info['leave_date'];
        $this->info['holiday_nb'] = $holiday_is_fixed ? (empty($this->info['holiday_nb']) ? smc_session()['default_setting']['default_nb_holiday'] : $this->info['holiday_nb'] ) : 0;
        $holiday_diff = $this->info['holiday_nb'] - $row['holiday_nb'];
        $leave_diff = $this->info['leave_days_nb'] - $row['leave_days_nb'];
        $previous_diff = $this->info['previous_days_nb'] - $row['previous_days_nb'];
        $rem_diff = $holiday_diff + $leave_diff + $previous_diff;
        $data = [&$this->info['Employee_FirstName'],
            &$this->info['Employee_MiddleName'],
            &$this->info['Employee_LastName'],
            &$this->info['second_lang_name'],
            &$this->info['Employee_SocialSecurity'],
            &$this->info['Employee_IsSmoker'],
            &$this->info['Employee_Gender'],
            &$this->info['Employee_DOB'],
            &$this->info['IS_Employee'],
            &$this->info['Employee_RecordNumber'],
            &$this->info['Employee_SocialSecurity_Date'],
            &$this->info['Employee_BloodGroup'],
            &$this->info['Employee_HomePhone'],
            &$this->info['Employee_MobilePhone'],
            &$work_id_number,
            &$email,
            &$is_nssf,
            &$this->info['nssf_salary'],
            &$this->info['other_charges'],
            &$this->info['basic_salary'],
            &$this->info['basic_salary_currency'],
            &$this->info['period_id'],
            &$this->info['Merital_Status_ID'],
            &$this->info['Employee_StartDate'],
            &$this->info['leave_date'],
            &$this->info['Employee_StreetAddress1'],
            &$this->info['Employee_City'],
            &$this->info['Employee_Country'],
            &$this->info['Branch_ID'],
            &$this->info['Paygrade_ID'],
            &$this->info['job_id'],
            &$this->info['Employee_Transportation'],
            &$this->info['trans_unit_time'],
            &$this->info['Employee_Allownace'],
            &$this->info['Employee_Lunch'],
            &$this->info['Employee_Phone_Recharge'],
            &$this->info['Shift_ID'],
            &$this->info[0],
            &$this->info['Employee_Nationality'],
            &$this->info['Employee_Location'],
            &$this->info['Employee_Wife_Name'],
            &$this->info['Employee_Wife_DOB'],
            &$this->info['Employee_Wife_Address'],
            &$this->info['Employee_Wife_Phone'],
            &$this->info['Employee_Wife_Problem'],
            &$this->info['Employee_Wife_Work'],
            &$this->info['Employee_Major'],
            &$this->info['Employee_Education'],
            &$this->info['Employee_Problem'],
            &$this->info['Employee_Wife_SSNF'],
            &$this->info['Employee_Wife_Nationality'],
            &$this->info['Wife_Works'],
            &$mydate,
            &$this->info['bank_id'],
            &$this->info['bank_account'],
            &$this->info['leave_days_nb'],
            &$this->info['holiday_nb'],
            &$holiday_is_fixed,
            &$this->info['previous_days_nb'],
            &$this->info['fingerprint_code'],
            &$rem_diff,
            &$this->info['shift_note'],
            &$this->info['location_address'],
            &$this->info['employee_level'],
            &$this->info['absence_type'],
            &$this->info['emp_charge1'],
            &$this->info['emp_charge2'],
            &$this->info['emp_charge3'],
            &$this->info['emp_charge4'],
            $stop_housing_payroll, $early_payroll,
            &$this->info['Employee_ID']
        ];



        $this->db->prepare_Stmt("UPDATE
                                        tbl_hrm_employee
                                    SET  
                                        Employee_FirstName = ?,
                                        Employee_MiddleName = ?,
                                        Employee_LastName = ?,
                                        second_lang_name = ?,
                                        Employee_SocialSecurity = ?,
                                        Employee_IsSmoker = ?,
                                        Employee_Gender = ?,
                                        Employee_DOB = ?,
                                        IS_Employee = ?,
                                        Employee_RecordNumber = ?,
				        Employee_SocialSecurity_Date = ?,
                                        Employee_BloodGroup = ?,
                                        Employee_HomePhone = ?,
                                        Employee_MobilePhone = ?,
                                        Employee_Work_ID = ?,
                                        Employee_Email = ?,
                                        is_nssf=?,
                                        nssf_salary=?,
                                        other_charges=?,
                                        basic_salary=?,
                                        basic_salary_currency=?,
                                        period_id=?,
                                        Merital_Status_ID = ?,
                                        Employee_StartDate = ?,
                                        leave_date=?,
                                        Employee_StreetAddress1 = ?,
                                        Employee_City = ?,
                                        Employee_Country = ?,
                                        Branch_ID = ?,
                                        Paygrade_ID = ?,
                                        job_id=?,
                                        Employee_Transportation = ?,
                                        trans_unit_time = ?,
                                        Employee_Allownace = ?,
                                        Employee_Lunch = ?,
                                        Employee_Phone_Recharge = ?,
                                        Shift_ID = ?,
                                        Logged_ID = ?,
                                        Employee_Nationality = ?,
                                        Employee_Location = ?,
                                        Employee_Wife_Name = ?,
                                        Employee_Wife_DOB = ?,
                                        Employee_Wife_Address = ?,
                                        Employee_Wife_Phone = ?,
                                        Employee_Wife_Problem = ?,
                                        Employee_Wife_Work = ?,
                                        Employee_Major = ?,
                                        Employee_Education = ?,
                                        Employee_Problem = ?,
                                        Employee_Wife_SSNF = ?,
                                        Employee_Wife_Nationality = ?,
                                        Wife_Works = ?,
                                        date_modified=?,bank_id=?,bank_account=?,leave_days_nb = ?,holiday_nb=?,
                                        holiday_is_fixed=?,previous_days_nb=?,fingerprint_code=?,remaining_days = remaining_days + ?,
                                        shift_note=?,location_address=?,employee_level=?,absence_type=?,
                                        emp_charge1=?,emp_charge2=?,emp_charge3=?,emp_charge4=?,stop_housing_payroll=?,early_payroll=?

                                    WHERE
                                        Employee_ID = ?
                    ", $data);
        $arr_prod = ['type' => 'emp', 'main_id' => $this->info['Employee_ID'], 'action' => 1, 'date_time' => $mydate,
            'user_id' => $this->info[0]];
        CommonFunctions::add_user_log($arr_prod);
        $this->db->prepare_Stmt('delete from tbl_hrm_emp_absence_setting where employee_id = ?', [$this->info['Employee_ID']]);
        if (!empty($this->info['absences']) && $this->info['absence_type'] == 2) {
            $this->info['absences'] = json_decode($this->info['absences'], true);
            foreach ($this->info['absences'] as $ab) {
                $this->db->prepare_Stmt('INSERT INTO `tbl_hrm_emp_absence_setting`
                    ( `employee_id`, `absence_reason_id`, `nb_days`) values(?,?,?)
                    on duplicate key update nb_days = ? ', [$this->info['Employee_ID'], $ab['absence_reason_id'], $ab['nb_days'], $ab['nb_days']]);
            }
        }
        $error = $this->db->error();
        if (isset($_FILES["image_employee_File"]) && empty($error)) {
            $uploaddir = upload_path . 'employees/';
            $this->SaveUploadedPhoto("image_employee_File", $uploaddir . $this->info['Employee_ID'] . '.jpg');
        }
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
        //        
        //        
        //        if (empty($error))
        //            return true;
        //        return false;
    }

    public function deleteEmployee($employee_id, $logged_id) {
        $this->db->query('START TRANSACTION');
        //to check if this user exists
        $user_id = self::CheckUserNameExists($employee_id);
        if (!is_null($user_id))
        //if user exists then will delete user before deleting employee
            self::deleteUser($user_id, $logged_id);
        $mydate = date('Y-m-d H:i:s');
        $data = [&$logged_id, &$employee_id];
        $this->db->prepare_Stmt('UPDATE 
                                        tbl_hrm_employee
                                    SET 
                                        Status_ID = 0,
                                        Logged_ID = ?
                                    WHERE 
                                        Employee_ID = ? ', $data);
        $arr_prod = ['type' => 'emp', 'main_id' => $employee_id, 'action' => 2, 'date_time' => $mydate,
            'user_id' => $logged_id];
        CommonFunctions::add_user_log($arr_prod);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function CheckUserNameExists($employee_id) {
        $query = $this->db->query('SELECT User_ID FROM tbl_common_users WHERE Status_ID = 1 AND Employee_ID = "' . $employee_id . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $result = $row['User_ID'];
        else
            $result = null;
        return $result;
    }

    private function deleteUser($user_id, $logged_id) {
        $data = [&$user_id];
        $update = $this->db->prepare_Stmt('UPDATE tbl_common_users SET Status_ID = 0 WHERE User_ID = ?', $data);
    }

    private function getLastEmployeeID() {
        $query = $this->db->query('SELECT 
                                        MAX(Employee_ID) AS Max_ID
                                FROM
                                        tbl_hrm_employee
				');
        $row = $this->db->assoc($query);
        $id = $row['Max_ID'];
        return $id;
    }

    public function addEmployeeExpense() {
        $data = [
            &$this->info['Expense_Date'],
            &$this->info['Expense_Description'],
            &$this->info['Expense_Quantity'],
            &$this->info['Currency_ID'],
            &$this->info['Employee_ID']
        ];
        $this->db->prepare_Stmt('INSERT INTO tbl_hrm_employees_expenses( Expense_Date,
                                                                Expense_Description,
                                                                Expense_Quantity,
                                                                Currency_ID,
                                                                Employee_ID
                                                        )VALUES(?,?,?,?,?)', $data);
        $error = $this->db->error();
        return $error;
    }

    public function addEmergencyContact() {
        $data = [&$this->info['EmergencyContact_FirstName'],
            &$this->info['EmergencyContact_MiddleName'],
            &$this->info['EmergencyContact_LastName'],
            &$this->info['Employee_Relationship_ID'],
            &$this->info['EmergencyContact_HomePhone'],
            &$this->info['EmergencyContact_MobilePhone'],
            &$this->info['EmergencyContact_WorkPhone'],
            &$this->info['Employee_ID'],
            &$this->info[0]];
        $this->db->prepare_Stmt('INSERT INTO tbl_hrm_emergency_contact( EmergencyContact_FirstName,
                                                                        EmergencyContact_MiddleName,
                                                                        EmergencyContact_LastName,
                                                                        Employee_Relationship_ID,
                                                                        EmergencyContact_HomePhone,
                                                                        EmergencyContact_MobilePhone,
                                                                        EmergencyContact_WorkPhone,
                                                                        Employee_ID,
                                                                        Logged_ID
                                                                        )
                                                        VALUES( ?,?,?,?,?,?,?,?,?)', $data);
        $error = $this->db->error();
        return $error;
    }

    public function viewRelationship() {
        $data = array();
        $query = "
			SELECT * FROM tbl_hrm_employee_relationship
		";
        $result = $this->db->query($query);
        while ($row = $this->db->assoc($result))
            $data[] = $row;
        return $data;
    }

    public function viewEmployee($emp_id = null, $is_hidden = false) {

        $data = array();
        $condition = '';
        if (!is_null($emp_id))
            $condition .= ' AND Employee_ID = "' . $emp_id . '"';
        if (!$is_hidden)
            $condition .= ' AND Is_Hidden = 0';
        $query = $this->db->query('SELECT * FROM view_employees WHERE Status_ID = 1' . $condition);
        $i = 0;
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
            $fetch = $this->db->query('SELECT 
                                            ed.Document_File_Name , 
                                            ed.Document_Name ,
                                            ed.Document_Comments ,
                                            dt.Document_Type_Name 
                                       FROM 
                                           tbl_hrm_employee_document ed 
                                       LEFT JOIN 
                                           tbl_hrm_document_types dt
                                       ON 
                                            dt.Document_ID = ed.Document_Type
                                       WHERE
                                           Employee_ID ="' . $row['Employee_ID'] . '"  ');
            $temp = '';
            $temp1 = '';
            $temp2 = '';
            $temp3 = '';
            while ($row_documents = $this->db->assoc($fetch)) {
                $temp .= $row_documents['Document_File_Name'] . ',';
                $temp1 .= $row_documents['Document_Name'] . ',';
                $temp2 .= $row_documents['Document_Comments'] . ',';
                $temp3 .= $row_documents['Document_Type_Name'] . ',';
            }
            $data[$i]['Documents'] = rtrim($temp, ", ");
            $data[$i]['Documents_Name'] = rtrim($temp1, ",");
            $data[$i]['Document_Comments'] = rtrim($temp2, ",");
            $data[$i]['Document_Type_Name'] = rtrim($temp3, ",");
            $i++;
        }
        return $data;
    }

    public function viewEmployeeExpenses($employee_id) {
        $data = array();
        $query = $this->db->query('SELECT 
                                            ex.*,
                                            c.Currency_Symbol,
                                            CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name
                                    FROM 
                                            tbl_hrm_employees_expenses ex
                                    LEFT JOIN
                                            tbl_hrm_employee e
                                    ON
                                            e.Employee_ID = ex.Employee_ID
                                    LEFT JOIN
                                            tbl_common_currency c
                                    ON
                                            c.Currency_ID = ex.Currency_ID
                                    WHERE 
                                            ex.Employee_ID = "' . $employee_id . '"
				');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function viewEmployeeDependent($employee_id) {
        $data = array();
        $query = $this->db->query('SELECT 
                                        d.*,
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name
                                FROM 
                                        tbl_hrm_dependants d
                                LEFT JOIN
                                        tbl_hrm_employee e
                                ON
                                        e.Employee_ID = d.Employee_ID
                                WHERE 
                                        d.Employee_ID = "' . $employee_id . '"
				');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    //    public function viewEmployeeName($employee_id) {
    //        $data = array();
    //        $query = $this->db->query('SELECT
    //                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name
    //                                FROM 
    //                                        tbl_hrm_employee e
    //                                WHERE 
    //                                        e.Employee_ID = "' . $employee_id . '"
    //				');
    //        $row = $this->db->assoc($query);
    //        $name = $row['Employee_Name'];
    //        return $name;
    //    }
    public function viewEmployeeEmergencyContact($employee_id) {
        $data = array();
        $query = $this->db->query('SELECT * FROM employee_emergency_contact WHERE Employee_ID = "' . $employee_id . '"');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function createEmployeeDependent() {
        $childrenNumber = isset($this->info['Childreen']) ? count($this->info['Childreen']) : 0;
        $data = [&$this->info['Wife_Works'],
            &$childrenNumber, //$this->info['Number_Of_Children']
            &$this->info[0],
            &$this->info['Employee_ID'],
            &$this->info['Wife_Works'],
            &$childrenNumber, //$this->info['Number_Of_Children']
            &$this->info[0]
        ];
        $this->db->prepare_Stmt("
			INSERT INTO tbl_hrm_dependants (
			Wife_Works,Number_Of_Children,Logged_ID, Employee_ID) VALUES(?,?,?,?)
                        ON DUPLICATE KEY UPDATE Wife_Works = ?,Number_Of_Children = ?,Logged_ID = ?
                        ", $data);
        //
        $this->db->prepare_Stmt('Delete from tbl_child_employee where Employee_ID = ? ', [&$this->info['Employee_ID']]);
        if (isset($this->info['Childreen'])) {
            foreach ($this->info['Childreen'] as $item) {
                $child_id = empty($item['Child_ID']) ? null : $item['Child_ID'];
                $item_data = [&$child_id, &$this->info['Employee_ID'],
                    &$item['ChildName'],
                    &$item['ChildDob'],
                    &$item['ChildSchool'],
                    &$item['ChildSchoolType'],
                    &$item['ChildClass'],
                    &$item['is_study'],
                    &$item['is_self_nssf'],
                    &$item['has_special_problem'],
                    &$item['gender_id'],
                    &$item['marital_status_id'],
                    &$item['ChildProblem']];
                $this->db->prepare_Stmt('INSERT INTO
                                    tbl_child_employee (
                                         Entry_ID,
                                         Employee_ID,
                                         child_name,
                                         child_dob,
                                         child_school,
                                         school_type,
                                         child_class,
                                         is_study,is_self_nssf,has_special_problem,gender_id,marital_status_id,
                                         child_problem
                                    ) 
                                    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)', $item_data);
            }
        }

        $error = $this->db->error();
        return $error;
    }

    public function createEmployeeChild() {
        $logged_id = Session::getUserID();
        $old_data = json_encode(empty($this->info['data_old']) ? array() : $this->info['data_old']);
        $data = [&$this->info['note'],
            &$this->info['amount_children'],
            &$logged_id,
            &$this->info['Employee_ID'],
            &$this->info['Currency_ID'],
            &$this->info['Amount_Date'],
            &$old_data
        ];
        $this->db->prepare_Stmt("
            INSERT INTO tbl_employee_child (
            note,
            Employee_Amount,
            Logged_ID,
            Employee_ID,
            currency_id,
            Amount_Date,
            old_data_child
                        ) VALUES(?,?,?,?,?,?,?)
                        ", $data);
        $error = $this->db->error();
        return $error;
    }

    public function viewEmployeeChild() {
        $data = array();
        $query = $this->db->query('Select * from viewEmployeeChild');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function deleteEmployeeChild($id) {
        $this->db->prepare_Stmt('DELETE FROM  tbl_employee_child WHERE Entry_id = ?', [&$id]);
        $error = $this->db->error();
        return $error;
    }

    public function updateEmployeeChild($id) {
        $logged_id = Session::getUserID();
        $old_data = json_encode(empty($this->info['data_old']) ? array() : $this->info['data_old']);
        $this->db->prepare_Stmt('UPDATE tbl_employee_child SET Employee_ID= ? ,Employee_Amount=?,currency_id=?,note=?,Logged_ID=?,Amount_Date=?, old_data_child = ? WHERE Entry_id =?', [&$this->info['Employee_ID'], &$this->info['amount_children'], &$this->info['Currency_ID'], &$this->info['note'], &$logged_id, &$this->info['Amount_Date'], &$old_data, &$id]);
        $error = $this->db->error();
        return $error;
    }

    public function view($is_hidden = false) {
        $data = array();
        $condition = "";
        if (!$is_hidden)
            $condition .= " AND e.Is_Hidden = 0";
        $query = "SELECT 
						e.*,
						g.Gender_Name,
						m.Merital_Status_Name
					FROM 
						tbl_hrm_employee e
					LEFT JOIN 
						tbl_common_gender g
					ON 
						g.Gender_ID = e.Employee_Gender	
					LEFT JOIN 
						tbl_hrm_merital_status m
					ON 
						m.Merital_Status_ID = e.Merital_Status_ID
                    WHERE 
                        e.Status_ID = 1
		" . $condition;
        $result = $this->db->query($query);
        while ($row = $this->db->assoc($result))
            $data[] = $row;
        return $data;
    }

    public function viewDependantEmployee($is_hidden = false) {
        $condition = '';
        if (!$is_hidden)
            $condition .= ' AND Is_Hidden = 0';
        $data = array();
        $query = $this->db->query('SELECT 
        						CONCAT(Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) AS Employee_Name,
                                Employee_ID
                               FROM 
                               tbl_hrm_employee
                               WHERE
                               Merital_Status_ID <> 1
                               AND 
                               Status_ID = 1
        ' . $condition);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function returnEmployees($is_hidden = false) {
        $condition = '';
        if (!$is_hidden)
            $condition .= ' AND Is_Hidden = 0';
        $data = array();
        $query = $this->db->query('SELECT 
                                CONCAT(Employee_Work_ID," ",Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) AS Employee_Name,
                                Employee_ID
                               FROM 
                               tbl_hrm_employee
                               WHERE
                               Status_ID = 1
        ' . $condition);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function viewEmployeeForDependent($is_hidden = false) {
        $data = array();
        $condition = "";
        if (!$is_hidden)
            $condition .= " AND Is_Hidden = 0";
        $query = "
			SELECT * FROM tbl_hrm_employee WHERE Status_ID = 1 AND Employee_ID NOT IN  
(  
   SELECT Employee_ID FROM `tbl_common_users`WHERE Status_ID <> 0) 
		" . $condition;
        $result = $this->db->query($query);
        while ($row = $this->db->assoc($result))
            $data[] = $row;
        return $data;
    }

    public function viewGenders() {
        $data = array();
        $query = "
			SELECT * FROM tbl_common_gender 
		";
        $result = $this->db->query($query);
        while ($row = $this->db->assoc($result))
            $data[] = $row;
        return $data;
    }

    public function viewShifts() {
        $data = array();
        $query = "
			SELECT * FROM tbl_hrm_shifts WHERE Status_ID = 1
		";
        $result = $this->db->query($query);
        while ($row = $this->db->assoc($result))
            $data[] = $row;
        return $data;
    }

    public function viewBranches() {
        $data = array();
        $query = $this->db->query('
			SELECT * FROM tbl_hrm_branch WHERE Status_ID = 1
		');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function viewCities() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_cities');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function viewCountries() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_common_countries');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    //    public function viewPayroll($employee_id = null, $year = null, $quarterly = false, $month = null, $week = null, $day = null, $salary_type = null, $shift = null, $is_manager = null, $branch_id = null, $currency_id, $is_hidden = false) {
    //        //week must return as week of month as number
    //        //yearly
    //        $div = 1;
    //        $multiply = 12;
    //        //quarterly
    //        if ($quarterly) {
    //            $div = 4;
    //            $multiply = 3;
    //        }
    //        //based on specific month
    //        elseif ($month != null && $week = null && $day = null) {
    //            $div = 12;
    //            $multiply = 1;
    //        }
    //        //based on specific week
    //        elseif ($week != null) {
    //            //calculate no. of days in this year
    //            //divide the amount over this number of days
    //            //then multiply result with seven
    //            $days = $year / 4; //to check number of this year
    //            if (is_integer($days))
    //                $final_days = 366;
    //            else
    //                $final_days = 365;
    //            $days_in_month = cal_days_in_month(CAL_GREGORIAN, $month, $year);
    //            $div = $final_days / 7;
    //            $multiply = 7 / $days_in_month;
    //        }
    //        elseif ($day != null) {
    //            $days = $year / 4; //to check number of this year
    //            if (is_integer($days))
    //                $final_days = 366;
    //            else
    //                $final_days = 365;
    //            $days_in_month = cal_days_in_month(CAL_GREGORIAN, $month, $year);
    //            $div = $final_days;
    //            $multiply = 1 / $days_in_month;
    //        }
    //        $total_bonus = 0;
    //        $total_deduction_cal = 0;
    //        $total_basic_salary = 0;
    //        $total_transport = 0;
    //        $total_other_charges = 0;
    //        $total_Fam_allow = 0;
    //        $total_gross = 0;
    //        $total_amount_taxable = 0;
    //        $total_tax = 0;
    //        $total_ssnf = 0;
    //        $total_total_deducation = 0;
    //        $total_net_pay = 0;
    //        $total_leave_ind = 0;
    //        $total_fam_tax = 0;
    //        $total_med_ins = 0;
    //        $total_total_comp = 0;
    //        $total_total_absent = 0;
    //        $total_total_lunch = 0;
    //        $total_total_phone = 0;
    //        $total_total_overtime = 0;
    //        $total_advance = 0;
    //        $total_loan = 0;
    //        $total_allow_new = 0;
    //        $pay_roll = array();
    //        $data = self::returnEmployeesInfoForPayroll($date = null, $employee_id, $year, $month, $week, $day, $salary_type, $shift, $is_manager, $branch_id, $is_hidden);
    //        $default_currency = CurrencyFactory::getCurrencySymbol($currency_id);
    //        for ($i = 0; $i < count($data); $i++) {
    //            //            $today = Date::getToday();
    //            //            $cur_year = substr($today,0,4);
    //            //            $cur_month = substr($today,5,2);
    //            //            $cur_day = substr($today,8,2);
    //            $start_year = substr($data[$i]['Employee_StartDate'], 0, 4);
    //            $start_month = substr($data[$i]['Employee_StartDate'], 5, 2);
    //            $start_day = substr($data[$i]['Employee_StartDate'], 8, 2);
    //            $bonus = self::returnBonusOrDeductionForEmployee($data[$i]['Employee_ID'], "Bonus", "tbl_hrm_bonuses", $year, $month, null, null, false, false, $currency_id);
    //            $deduction = self::returnBonusOrDeductionForEmployee($data[$i]['Employee_ID'], "Deduction", "tbl_hrm_deductions", $year, $month, null, null, false, false, $currency_id);
    //            $basic_salary = $data[$i]['Basic_Salary'];
    //            //            $basic_salary = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], $currency_id, $data[$i]['Basic_Salary']);
    //            $days_absent = self::returnAbsentDaysForEmployee($data[$i]['Employee_ID'], $year, $month);
    //            $absent_amount = self::returnAbsentAmountForEmployee($basic_salary, $data[$i]['Employee_ID'], $year, $month);
    //            //			$absent_amount =  ::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'],$currency_id,$absent_amount_first);
    //            $overtime_first = self::returnOverTime($data[$i]['Employee_ID'], $year, $month);
    //            $overtime = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], $currency_id, $overtime_first);
    //            $date_final = $year . "-" . $month . "-" . $day;
    //            $advance_payment = self::returnAdvanceByMonth($data[$i]['Employee_ID'], $year, $month, $date_final, false, $currency_id);
    //            $loan_payment = self::returnLoanByMonth($data[$i]['Employee_ID'], $year, $month, $date_final, false, $currency_id);
    //            //   $tax_amount_first = $basic_salary + $overtime + $bonus + $temp_tax_amount;
    //            //            $amount_taxable = self::returnAmountTaxableForPayroll($data[$i]['Merital_Status_ID'],
    //            //                                                                  $allow_new,
    //            //                                                                  $data[$i]['Wife_Works'],
    //            //                                                                  $data[$i]['Number_Of_Children'],
    //            //                                                                  $tax_amount_first*$multiply,$div,$currency_id);
    //            //$tax = self::returnTaxForPayroll($amount_taxable,$div,$currency_id);
    //            if ($data[$i]['Is_Hidden'] == 1) {
    //                $allow_new = 0.0;
    //                $fam_allow = 0.0;
    //                $transport = 0.0;
    //                $other_charges = 0;
    //                $lunch = 0.0;
    //                $phone_recharge = 0.0;
    //                $tax = 0.0;
    //                $ssnf = 0.0;
    //                $total_deducation = 0.0;
    //                $leav = 0.0;
    //                $fam = 0.0;
    //                $med = 0.0;
    //                $total_comp = 0.0;
    //                $amount_taxable = 0.0;
    //            } else {
    //                $temp_tax_amount = 0.0;
    //                $allow_new = self::viewFamilyAllawance($data[$i]['Employee_ID'], $data[$i]['Merital_Status_ID'], $currency_id);
    //                $fam_allow = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], $currency_id, $data[$i]['Employee_Allownace']);
    //                $transport = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], $currency_id, $data[$i]['Employee_Transportation']);
    //                $other_charges = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], $currency_id, $data[$i]['other_charges']);
    //                $lunch = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], $currency_id, $data[$i]['Employee_Lunch']);
    //                $phone_recharge = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], $currency_id, $data[$i]['Employee_Phone_Recharge']);
    //                //$lebanese_lunch = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'],84,$data[$i]['Employee_Lunch']);
    //                $max_lunch = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(84, $currency_id, (5000 * 30));
    //                if ($lunch > $max_lunch)
    //                    $temp_tax_amount += ($lunch - $max_lunch);
    //                //$lebanese_trans = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'],$currency_id,$data[$i]['Employee_Transportation']);
    //                $max_transp = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(84, $currency_id, (8000 * 30));
    //                if ($transport > $max_transp)
    //                    $temp_tax_amount += ($transport - $max_transp);
    //                $amount_taxable = self::returnAmountTaxableForPayroll($data[$i]['Merital_Status_ID'], $allow_new, $data[$i]['Wife_Works'], $data[$i]['Number_Of_Children'], $basic_salary, $div, $currency_id);
    //                $tax = $amount_taxable * 0.02;
    //                if ($tax == 0)
    //                    $tax = 0;
    //                $ssnf = $data[$i]['is_nssf'] ? (!empty($data[$i]['nssf_salary']) ? $data[$i]['nssf_salary'] : self::returnSSNFForPayroll($basic_salary, $currency_id)) : 0;
    //
    //                //                $ssnf = self::returnSSNFForPayroll($basic_salary, $currency_id);
    //                $total_deducation = self::returnTotalDeducationForPayroll($tax, $ssnf);
    //                $leav = self::returnLeavIndForPayroll($basic_salary);
    //                $fam = self::returnFamAllowForPayroll($basic_salary, $currency_id);
    //                $med = self::returnMedInsForPayroll($basic_salary, $currency_id);
    //                $total_comp = self::returnTotalForPayroll($leav, $fam, $med);
    //            }
    //            $first_gross = $fam_allow + $lunch + $phone_recharge + $transport + $basic_salary + $other_charges;
    //            if ($start_year == $year and $start_month == $month and $start_day > 1) {
    //                $diff = date_diff(date_create($year . '-' . $month . '-01'), date_create($data[$i]['Employee_StartDate']));
    //                $days_diff = $diff->format("%a");
    //                $gross_per_day = $first_gross / 30;
    //                $deduc_gross = $gross_per_day * $days_diff;
    //                $gross = $first_gross - $deduc_gross;
    //            } else
    //                $gross = $first_gross;
    //            //    $net_pay = self::returnNetPayForPayroll($gross,$total_deducation);
    //            $net_pay = $fam_allow + $basic_salary + $transport + $overtime + $bonus - $deduction - $absent_amount - $advance_payment - $loan_payment - $ssnf - $tax;
    //            $tem_array = array(
    //                'Work_ID' => $data[$i]['Employee_Work_ID'],
    //                'Employee_ID' => $data[$i]['Employee_ID'],
    //                'Is_Hidden' => $data[$i]['Is_Hidden'],
    //                'Name' => $data[$i]['Employee_Name'],
    //                'Position' => $data[$i]['Employee_Position'],
    //                'Basic_Salary' => $basic_salary,
    //                'Currency_Symbol' => $default_currency,
    //                'Transport' => $transport,
    //                'other_charges' => $other_charges,
    //                'Fam_Allow' => $fam_allow,
    //                'Allow_New' => $allow_new,
    //                'Bonus' => $bonus,
    //                'Deduction' => $deduction,
    //                'Gross' => $gross,
    //                'Amount_Taxable' => $amount_taxable,
    //                'Tax' => $tax,
    //                'SSNF' => $ssnf,
    //                'Total_Deducation' => $total_deducation,
    //                'Net_Pay' => $net_pay,
    //                'Leave_Ind' => $leav,
    //                'Fam_Allow_Tax' => $fam,
    //                'Lunch' => $lunch,
    //                'Phone_Recharge' => $phone_recharge,
    //                'OverTime' => $overtime,
    //                'Absent_Days' => $med,
    //                'Absent_Amount' => $absent_amount,
    //                'Med_Ins' => $med,
    //                'Advance_Payment' => $advance_payment,
    //                'Loan_Payment' => $loan_payment,
    //                'Total' => $total_comp
    //            );
    //            $total_basic_salary += $basic_salary;
    //            $total_transport += $transport;
    //            $total_other_charges += $other_charges;
    //            $total_Fam_allow += $fam_allow;
    //            $total_bonus += $bonus;
    //            $total_deduction_cal += $deduction;
    //            $total_gross += $gross;
    //            $total_amount_taxable += $amount_taxable;
    //            $total_tax += $tax;
    //            $total_ssnf += $ssnf;
    //            $total_total_deducation += $total_deducation;
    //            $total_net_pay += $net_pay;
    //            $total_leave_ind += $leav;
    //            $total_fam_tax += $fam;
    //            $total_med_ins += $med;
    //            $total_total_comp += $total_comp;
    //            $total_total_absent += $absent_amount;
    //            $total_total_lunch += $lunch;
    //            $total_total_phone += $phone_recharge;
    //            $total_total_overtime += $overtime;
    //            $total_advance += $advance_payment;
    //            $total_loan += $loan_payment;
    //            $total_allow_new += $allow_new;
    //            array_push($pay_roll, $tem_array);
    //            $tem_array = array();
    //        }
    //        $pay_roll['Total_Basic_Salary'] = $total_basic_salary;
    //        $pay_roll['Total_Transportation'] = $total_transport;
    //        $pay_roll['total_other_charges'] = $total_other_charges;
    //        $pay_roll['Total_Allowance'] = $total_Fam_allow;
    //        $pay_roll['Total_Allowance_New'] = $total_allow_new;
    //        $pay_roll['Total_Bonus'] = $total_bonus;
    //        $pay_roll['Total_Deduction_Cal'] = $total_deduction_cal;
    //        $pay_roll['Total_Gross'] = $total_gross;
    //        $pay_roll['Total_Taxable'] = $total_amount_taxable;
    //        $pay_roll['Total_Tax'] = $total_tax;
    //        $pay_roll['Total_SSNF'] = $total_ssnf;
    //        $pay_roll['Total_Total_Deduc'] = $total_total_deducation;
    //        $pay_roll['Total_Net_Pay'] = $total_net_pay;
    //        $pay_roll['Total_Leave_Ind'] = $total_leave_ind;
    //        $pay_roll['Total_Fam_Tax'] = $total_fam_tax;
    //        $pay_roll['Total_Med_Ins'] = $total_med_ins;
    //        $pay_roll['Total_Total_Comp'] = $total_total_comp;
    //        $pay_roll['Total_Absent'] = $total_total_absent;
    //        $pay_roll['Total_Lunch'] = $total_total_lunch;
    //        $pay_roll['Total_Phone'] = $total_total_phone;
    //        $pay_roll['Total_OverTime'] = $total_total_overtime;
    //        $pay_roll['Total_Advance_Payment'] = $total_advance;
    //        $pay_roll['Total_Loan_Payment'] = $total_loan;
    //        $pay_roll['Default_Currency'] = $default_currency;
    //        //        if ($pay_roll['Total_Basic_Salary'] == 0)
    //        //            $pay_roll = array();
    //        return $pay_roll;
    //    }
    public function viewPayroll($employee_id = null, $from_date, $to_date, $month = null, $is_paid = null) {
        $query_leave = $this->db->query('select leave_date from tbl_hrm_employee where Employee_ID = "' . $employee_id . '"');
        $leave_date = $this->db->assoc($query_leave)['leave_date'];
        if (!empty($leave_date) && $leave_date != '0000-00-00' && $leave_date < $from_date) {
            $error = CommonLanguage::getMessage('leave_emp_alert');
            return ["error" => $error];
        }
        $query_start = $this->db->query('select Employee_StartDate from tbl_hrm_employee where Employee_ID = "' . $employee_id . '"');
        $start_date = $this->db->assoc($query_start)['Employee_StartDate'];
        if ($start_date > $to_date) {
            $error = CommonLanguage::getMessage('start_emp_alert');
            return ["error" => $error];
        }

        //        echo $employee_id.'==';
        $condition = '';
        $currency_id = smc_session()['local_currency'];
        if (!is_null($is_paid)) {
//            $condition = $is_paid == 0 ? '' : ' and is_paid = "' . ($is_paid == 1 ? 1 : 0) . '" ';
        }
        ////week must return as week of month as number
        //yearly
        $div = 1;
        $multiply = 12;
        //quarterly
        //based on specific month
        if ($month != null && $week = null && $day = null) {
            $div = 12;
            $multiply = 1;
        }

        $query = $this->db->query('select * from tbl_hrm_payroll 
            where 
            (from_date between "' . $from_date . '" and "' . $to_date . '"
            or Payroll_Date between     "' . $from_date . '" and "' . $to_date . '" 
                or  "' . $from_date . '" between from_date and Payroll_Date
                or  "' . $to_date . '" between from_date and Payroll_Date)  and Employee_ID = "' . $employee_id . '"' . $condition);
        $rowsno = $this->db->rowsno($query);
        if ($rowsno > 0) {
            $row = $this->db->assoc($query);
            $other_prices = $row['other_prices'];
            $paid_amount = $row['Amount_Paid'];
            //            $error[0]=  CommonLanguage::getMessage('already_exist_date');
            //            echo"<pre>";die(print_r(json_decode($row,true)));
            $row_data = json_decode($other_prices, true);
            $row_data['details']['emp_charge1'] = !empty($row_data['details']['emp_charge1']) ? $row_data['details']['emp_charge1'] : 0;
            $row_data['details']['emp_charge2'] = !empty($row_data['details']['emp_charge2']) ? $row_data['details']['emp_charge2'] : 0;
            $row_data['details']['emp_charge3'] = !empty($row_data['details']['emp_charge3']) ? $row_data['details']['emp_charge3'] : 0;
            $row_data['details']['emp_charge4'] = !empty($row_data['details']['emp_charge4']) ? $row_data['details']['emp_charge4'] : 0;
            $row_data['is_paid'] = 1;
            $row_data['Amount_Paid'] = $paid_amount;
            $row_data['payroll_id'] = $row['Payroll_ID'];
            $row_data['payment_account_id'] = $row['payment_account_id'];
            return $row_data;
        }

        $query_cost = $this->db->query("SELECT
        SUM(
            tbl_production_process_employee_type.cost
        ) AS total
        FROM
        `tbl_production_template`,
        tbl_production_process,
        tbl_production_process_employee_type
        WHERE
        tbl_production_process.process_id = tbl_production_process_employee_type.process_id AND tbl_production_process.template_id = tbl_production_template.template_id AND tbl_production_process_employee_type.employee_id = '$employee_id' AND tbl_production_process.date BETWEEN '$from_date' AND '$to_date'"
         );
         $row_cost = $this->db->assoc($query_cost) ;
         
         $total_process =(float) str_replace( ',', '', $row_cost['total']);
         if($total_process == null )
                $total_process=0;
         



        $data = self::returnEmployeesInfoForPayroll($employee_id, $from_date);
        if (empty($data))
            return ["main" => array(), "details" => array(), "is_paid" => 0, "Amount_Paid" => 0];
        $default_currency = CurrencyFactory::getCurrencySymbol($currency_id);
        //        echo$employee_id."==<pre>";print_r($data);echo"<br>";
        //        $start_year = substr($data['Employee_StartDate'], 0, 4);
        //        $start_month = substr($data['Employee_StartDate'], 5, 2);
        //        $start_day = substr($data['Employee_StartDate'], 8, 2);
        $bonus = self::returnBonusOrDeductionForEmployee($data['Employee_ID'], "Bonus", "tbl_hrm_bonuses", $from_date, $to_date);
        $deduction = self::returnBonusOrDeductionForEmployee($data['Employee_ID'], "Deduction", "tbl_hrm_deductions", $from_date, $to_date);
        $basic_salary = self::get_basic_salary_emp($employee_id, $from_date, $to_date);
        $days_absent = self::returnAbsentDaysForEmployee($data['Employee_ID'], $from_date, $to_date);
        $overtime = self::returnOverTime($data['Employee_ID'], $from_date, $to_date);
        $advance_payment = self::returnAdvanceByMonth($data['Employee_ID'], $from_date, $to_date);
        $loan_payment = self::returnLoanByMonth($data['Employee_ID'], $from_date, $to_date);
//        echo $basic_salary.'===';
        //        die('heree');
        if ($data['Is_Hidden'] == 1) {
            $allow_new = 0.0;
            $fam_allow = 0.0;
            $transport = 0.0;
            $other_charges = 0;
            $emp_charge1 = $emp_charge2 = $emp_charge3 = $emp_charge4 = 0;
            $lunch = 0.0;
            $phone_recharge = 0.0;
            $tax = 0.0;
            $ssnf = 0.0;
            $total_deducation = 0.0;
            $leav = 0.0;
            $fam = 0.0;
            $med = 0.0;
            $total_comp = 0.0;
            $amount_taxable = 0.0;
            $basic_salary_tax = 0.0;
        } else {
            $temp_tax_amount = 0.0;
            $allow_new = 0;
            $fam_allow = 0;
            $ssnf = 0;
            if ($data['is_nssf']) {
                $allow_new = self::viewFamilyAllawance($data['Employee_ID'], $data['Merital_Status_ID'], $currency_id);

                //           echo $allow_new.'==';die();
                if (empty($data['stop_housing_payroll']))
                    $fam_allow = self::view_emp_housing($data['Employee_ID'], $data['Merital_Status_ID'], $currency_id, $to_date);
                //            $fam_allow = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data['Paygrade_Currency'], $currency_id, $data['Employee_Allownace']);
            }
            if ($data['trans_unit_time'] == 0) {
                $transport_amount = $data['Employee_Transportation'];
            } else {
                $transport_amount = self::view_emp_transportation($employee_id, $from_date, $to_date, $data['Employee_Transportation']);
            }
            $transport = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data['Paygrade_Currency'], $currency_id, $transport_amount);
            //            $transport = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data['Paygrade_Currency'], $currency_id, $data['Employee_Transportation']);
            $other_charges = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data['Paygrade_Currency'], $currency_id, $data['other_charges']);
            $lunch = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data['Paygrade_Currency'], $currency_id, $data['Employee_Lunch']);
            $emp_charge1 = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data['Paygrade_Currency'], $currency_id, $data['emp_charge1']);
            $emp_charge2 = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data['Paygrade_Currency'], $currency_id, $data['emp_charge2']);
            $emp_charge3 = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data['Paygrade_Currency'], $currency_id, $data['emp_charge3']);
            $emp_charge4 = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data['Paygrade_Currency'], $currency_id, $data['emp_charge4']);
            $phone_recharge = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data['Paygrade_Currency'], $currency_id, $data['Employee_Phone_Recharge']);
            $max_lunch_amt = smc_session()['default_setting']['max_lunch_amount_per_day'];
            $max_lunch = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(84, $currency_id, ($max_lunch_amt * 30));
            //            if ($lunch > $max_lunch)
            //                $temp_tax_amount += ($lunch - $max_lunch);
            //            $trans_
            if ($data['trans_unit_time'] == 0) {
                $max_transp = smc_session()['default_setting']['max_transportation_amount'];
            } else {
                $max_transp = !empty($data['avg_days']) ? ($data['Employee_Transportation'] * $data['avg_days']) : smc_session()['default_setting']['max_transportation_amount'];
            }
            //            if ($transport > $max_transp)
            //                $temp_tax_amount += ($transport - $max_transp);
            $basic_salary_tax = $basic_salary;
//            echo $basic_salary_tax.'===';die();
            if (!empty(smc_session()['default_setting']['goverment_salary_entities'])) {
                $entity_arr = explode(',', smc_session()['default_setting']['goverment_salary_entities']);
                if (in_array(0, $entity_arr)) {
                    $basic_salary_tax += $overtime;
                }

                if (in_array(1, $entity_arr)) {
                    $basic_salary_tax += $bonus;
                }

                if (in_array(2, $entity_arr)) {
                    if ($max_transp < $transport)
                        $basic_salary_tax += ($transport - $max_transp);
                }
                //                echo $basic_salary_tax.'===';die();
                if (in_array(3, $entity_arr)) {
                    $basic_salary_tax += $advance_payment;
                }
                if (in_array(4, $entity_arr)) {
                    $basic_salary_tax += $phone_recharge;
                }
                if (in_array(5, $entity_arr)) {
                    $basic_salary_tax += ($lunch - $max_lunch);
                }
                if (in_array(6, $entity_arr)) {
                    $basic_salary_tax += $other_charges;
                }
                if (in_array(7, $entity_arr)) {
                    $basic_salary_tax += $emp_charge1;
                }
                if (in_array(8, $entity_arr)) {
                    $basic_salary_tax += $emp_charge2;
                }
                if (in_array(9, $entity_arr)) {
                    $basic_salary_tax += $emp_charge3;
                }
                if (in_array(10, $entity_arr)) {
                    $basic_salary_tax += $emp_charge4;
                }
            }



            //                                    die($amount_taxable.'===');
            $basic_salary_for_tax = self::get_basic_salary_for_tax($data['Employee_ID'], $from_date, $basic_salary_tax, $allow_new);
            $amount_taxable = 0;
            $tax = 0;
            if ($data['is_nssf']) {
                $amount_taxable = self::returnAmountTaxableForPayroll($allow_new, $basic_salary_tax);
                $total_allowance = $allow_new * 12;

                $salary_per_year = $basic_salary_for_tax['total_salary'] / $basic_salary_for_tax['month_count'] * 12;
                //                echo $basic_salary_for_tax['total_salary'].'===='.$basic_salary_for_tax['month_count'].'========';
                //                $tax = self::get_emp_tax($amount_taxable, $basic_salary_tax); //$amount_taxable <= 0 ? 0 : $amount_taxable * 0.02;
                //                echo 'total:'.$salary_per_year.'=========';
                $tax = self::get_emp_tax(($salary_per_year - $total_allowance), $basic_salary_for_tax['total_tax'], $basic_salary_for_tax['month_count']); //$amount_taxable <= 0 ? 0 : $amount_taxable * 0.02;
                //                $tax = $tax - $basic_salary_for_tax['total_tax']; //subtract old tax
                //              echo ceil(($tax / 1000));
                $tax = ceil(($tax / 1000)) * 1000;
                $tax = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(84, $currency_id, $tax);

                //                echo $tax;
                //            echo ($tax / 1000).'==';
                //                $roundTo = 1000;
                //                $diff = $tax % $roundTo;
                //                $final_tax = floor($tax / $roundTo);
                //                if ($diff >= ($roundTo / 2)) {
                //                    $final_tax++;
                //                }
                //                $tax = $final_tax * $roundTo;
            }
            //            finalTotal = finalTotal * roundTo;
            //            if ($tax == 0)
            //                $tax = 0;
            if (empty($data['stop_housing_payroll']))
                $ssnf = $data['is_nssf'] ? self::returnSSNFForPayroll((!empty($data['nssf_salary']) ? $data['nssf_salary'] : $basic_salary_tax), $currency_id) : 0;
            $total_deducation = self::returnTotalDeducationForPayroll($tax, $ssnf);
            if ($data['is_nssf']) {
                $leav = self::returnLeavIndForPayroll($basic_salary);
                $fam = self::returnFamAllowForPayroll($basic_salary, $currency_id);
                $med = self::returnMedInsForPayroll($basic_salary, $currency_id);
            } else {
                $leav = 0;
                $fam = 0;
                $med = 0;
            }
            $total_comp = self::returnTotalForPayroll($leav, $fam, $med);
        }
        $first_gross = $fam_allow + $lunch + $phone_recharge + $transport + $basic_salary + $other_charges + $emp_charge1 + $emp_charge2 + $emp_charge3 + $emp_charge4;
        //            if ($start_year == $year and $start_month == $month and $start_day > 1) {
        //                $diff = date_diff(date_create($year . '-' . $month . '-01'), date_create($data[$i]['Employee_StartDate']));
        //                $days_diff = $diff->format("%a");
        //                $gross_per_day = $first_gross / 30;
        //                $deduc_gross = $gross_per_day * $days_diff;
        //                $gross = $first_gross - $deduc_gross;
        //            } else
        $gross = $first_gross;
        //    $net_pay = self::returnNetPayForPayroll($gross,$total_deducation);
        $net_pay = $total_process + $fam_allow + $lunch + $phone_recharge + $transport + $basic_salary + $other_charges + $emp_charge1 + $emp_charge2 + $emp_charge3 + $emp_charge4 + $overtime + $bonus - $deduction - $advance_payment - $loan_payment - $ssnf - $tax;
        $main_arr = array('Work_ID' => $data['Employee_Work_ID'],
            'Employee_ID' => $data['Employee_ID'],
            'start_date' => $data['Employee_StartDate'],
            'pay_period' => $data['pay_period'],
            'Is_Hidden' => $data['Is_Hidden'],
            'Currency_Symbol' => $default_currency,
            'Currency_ID' => $data['Paygrade_Currency'],
            'Gross' => $gross,
            'Net_Pay' => $net_pay,
            'Total' => $total_comp,
            'total_deduction' => $total_deducation,
            
        );
//        echo $basic_salary_tax.'===';die();

if($this->permissions->hasPermission('employee_salary_production')){
        $tem_array = array(
            'Name' => $data['Employee_Name'],
            'second_lang_name' => $data['second_lang_name'],
            'position' => $data['Employee_Position'],
            //            'days_absent' => $days_absent,
            'basic_salary' => $basic_salary,
            'transportation' => $transport,
            'Lunch' => $lunch,
            'emp_charge1' => $emp_charge1,
            'emp_charge2' => $emp_charge2,
            'emp_charge3' => $emp_charge3,
            'emp_charge4' => $emp_charge4,
            'Phone' => $phone_recharge,
            'overtime' => $overtime,
            'other_charges' => $other_charges,
            'allowance' => $fam_allow,
            'family' => $allow_new,
            'bonus' => $bonus,
            'deduction' => $deduction,
            'amount_tax' => $amount_taxable,
            'tax' => $tax,
            'ssnf' => $ssnf,
            'leave_ind' => $leav,
            'fam_allow' => $fam,
            'basic_salary_tax' => $basic_salary_tax,
            //                'Absent_Amount' => $absent_amount,
            'med_ins' => $med,
            'advance_payment' => $advance_payment,
            'employee_salary_production_process'=> $total_process,
            'loan_payment' => $loan_payment
        );
    }else{
        $tem_array = array(
            'Name' => $data['Employee_Name'],
            'second_lang_name' => $data['second_lang_name'],
            'position' => $data['Employee_Position'],
            //            'days_absent' => $days_absent,
            'basic_salary' => $basic_salary,
            'transportation' => $transport,
            'Lunch' => $lunch,
            'emp_charge1' => $emp_charge1,
            'emp_charge2' => $emp_charge2,
            'emp_charge3' => $emp_charge3,
            'emp_charge4' => $emp_charge4,
            'Phone' => $phone_recharge,
            'overtime' => $overtime,
            'other_charges' => $other_charges,
            'allowance' => $fam_allow,
            'family' => $allow_new,
            'bonus' => $bonus,
            'deduction' => $deduction,
            'amount_tax' => $amount_taxable,
            'tax' => $tax,
            'ssnf' => $ssnf,
            'leave_ind' => $leav,
            'fam_allow' => $fam,
            'basic_salary_tax' => $basic_salary_tax,
            //                'Absent_Amount' => $absent_amount,
            'med_ins' => $med,
            'advance_payment' => $advance_payment,
            'loan_payment' => $loan_payment
        );
    }

        $attendence = new Attendence;
        $att_arr = array();
        $emp_att_data = $attendence->attendanceReport($from_date, $to_date, $data['Employee_ID']);
        if (!empty($emp_att_data['data'][$data['Employee_ID']])) {
            $att_arr = $emp_att_data['data'][$data['Employee_ID']];
        }

        //        if ($pay_roll['Total_Basic_Salary'] == 0)
        //            $pay_roll = array();

        return ["main" => $main_arr, "details" => $tem_array, "att_data" => $att_arr, "is_paid" => 0, "Amount_Paid" => 0, "payroll_id" => 0, "payment_account_id" => 0];
    }

    private function view_emp_transportation($employee_id, $from_date, $to_date, $trans_amount) {
        $nb_days = 0;
        $query = $this->db->query('select count(distinct(date(`date_time_in`))) as days_nb
            from `tbl_employee_attendance_sheet` WHERE `employee_id`="' . $employee_id . '" 
                and date(date_time_in) between "' . $from_date . '" and "' . $to_date . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $nb_days = $row['days_nb'];
        return ($trans_amount * $nb_days);
    }

    private function viewFamilyAllawance($employee_id, $marital_status, $currency_id) {
        //monthly values
        //        $husband = 7500000;
        $wife = 2500000;
        $child = 500000;
        $val = 7500000;
        //        $val = 0;
        $wife_works = 0;
        if ($marital_status != 1) {
            $child_query = $this->db->query('SELECT c.*,d.Number_Of_Children,e.Wife_Works ,e.Employee_Wife_SSNF
                FROM tbl_hrm_employee e left join tbl_hrm_dependants d on d.Employee_ID=e.Employee_ID
                left join tbl_child_employee c on c.Employee_ID = d.Employee_ID
                WHERE e.Employee_ID = "' . $employee_id . '"');
            while ($row = $this->db->assoc($child_query)) {
                $wife_works = $row['Wife_Works'];
                $check_wife = ($row['Employee_Wife_SSNF'] == 1 && $row['Wife_Works'] == 1);
                if ($row['Number_Of_Children'] > 0) {
                    $birthDate = explode("-", $row['child_dob']);
                    //get age from date or birthdate
                    $age = (date("md", date("U", mktime(0, 0, 0, $birthDate[2], $birthDate[1], $birthDate[0]))) > date("md") ? ((date("Y") - $birthDate[0]) - 1) : (date("Y") - $birthDate[0]));
                    //                    echo $age.'==';
                    //                    if (!empty($row['has_special_problem']) || $age <= 18 || ($age < 25 && $age > 18 && ($row['gender_id'] == 1 || ($row['gender_id'] == 2 && $row['marital_status_id'] != 2) )))
                    if ($row['marital_status_id'] != 2 && !$row['is_self_nssf'])
                        $val += ($check_wife ? $child / 2 : $child);
                }
            }
            if ($wife_works == 0 && $marital_status == 2)
                $val += $wife;

            //            die($val.'==');
            //            if ($marital_status == 1) {
            //                $val = ($husband / 12);
            //            } else {
            //                $child_query = $this->db->query('SELECT d.Number_Of_Children,e.Wife_Works,e.Employee_Wife_SSNF 
            //                FROM tbl_hrm_employee e left join tbl_hrm_dependants d using(Employee_ID) 
            //                WHERE e.Employee_ID = "' . $employee_id . '"');
            //
            //                $row = $this->db->assoc($child_query);
            //                if (count($row) > 0) {
            //
            //                    $children = ($row['Number_Of_Children'] > 5 ? 5 : $row['Number_Of_Children']) * $child;
            //                    if ($row['Wife_Works'] == 3)
            //                        $wife = 0;
            //                }
            //                else {
            //                    $children = 0;
            //                }
            //                if ($marital_status == 2) {
            //                    $check_wife = ($row['Employee_Wife_SSNF'] == 1 && $row['Wife_Works'] == 1);
            //                    $val = ($husband + ($check_wife ? $children / 2 : $children) + ($check_wife ? 0 : $wife)) / 12;
            //                } else
            //                    $val = ($husband + $children) / 12;
            //            }
        }
        //        echo $val . '==';


        $allow = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(84, $currency_id, ($val / 12));
        return $allow;
    }

    private function view_emp_housing($employee_id, $marital_status, $currency_id, $to_date) {
        //monthly values
        $val = 0;
        $wife = 60000;
        $child = 33000;
        $wife_works = 0;
        if ($marital_status != 1) {
            $child_query = $this->db->query('SELECT c.*,d.Number_Of_Children,e.Wife_Works 
                FROM tbl_hrm_employee e left join tbl_hrm_dependants d on d.Employee_ID=e.Employee_ID
                left join tbl_child_employee c on c.Employee_ID = d.Employee_ID
                WHERE e.Employee_ID = "' . $employee_id . '"');
            while ($row = $this->db->assoc($child_query)) {
                $wife_works = $row['Wife_Works'];
                if ($row['Number_Of_Children'] > 0) {
                    //                    $row['child_dob']='2000-12-02';
                    //                    $birthDate = explode("-", $row['child_dob']);
                    //get age from date or birthdate
                    //$age = (date("md", date("U", mktime(0, 0, 0, $birthDate[2], $birthDate[1], $birthDate[0]))) > date("md") ? ((date("Y") - $birthDate[0]) - 1) : (date("Y") - $birthDate[0]));
                    $age_diff = CommonFunctions::calc_date_diff($row['child_dob'], $to_date, true);
                    //                    echo"<pre>";
                    //                    var_dump($age_diff);
                    $age = $age_diff->y;
                    //                    echo $age.'==';
                    if (!empty($row['has_special_problem']) || $age < 18 || ($age < 25 && $age >= 18 && (!empty($row['is_study']) )))
                        $val += $child;
                }
            }
            if ($wife_works == 0 && $marital_status == 2)
                $val += $wife;
        }
        $housing = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(84, $currency_id, $val);
        return $housing;
    }

    public function returnEmployeesInfoForPayroll($employee_id = null, $from_date = null) {
        $data = array();
        //        echo $from_date.'==';die();
        if (!empty($from_date)) {
            $month = explode('-', $from_date)[1];
            $year = explode('-', $from_date)[0];
        }
        $condition = ' ';
        if (!is_null($employee_id))
            $condition .= ' AND e.Employee_ID = "' . $employee_id . '"';

        $query = $this->db->query('SELECT 
                                            CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                            j.Job_Name AS Employee_Position,
                                            if(e.basic_salary = 0, p.Paygrade_Value,e.basic_salary) AS Basic_Salary,
                                            if(e.basic_salary = 0, p.Paygrade_Currency,e.basic_salary_Currency) AS Paygrade_Currency,
                                            c.Currency_Symbol,
                                            p.Paygrade_Value+e.Employee_Transportation+e.Employee_Allownace AS Gross,
                                            d.Wife_Works,
                                            d.Number_Of_Children,
                                            pe.pay_period,pe.unit_time,
                                            e.*,
                                            ' . ((!empty($from_date) ) ? 'if(isnull(s.avg_days),hr.nb_days,s.avg_days)' : 's.avg_days') . ' as avg_days
                                    FROM tbl_hrm_employee e
                                    LEFT JOIN tbl_hrm_paygrade p ON e.Paygrade_ID = p.Paygrade_ID
                                    LEFT JOIN tbl_common_currency c ON c.Currency_ID = p.Paygrade_Currency
                                    JOIN tbl_hrm_jobs j ON j.Job_ID = e.job_id
                                    LEFT JOIN tbl_hrm_dependants d ON d.Employee_ID = e.Employee_ID
                                    join tbl_hrm_basic_salary_periods pe on pe.period_id = e.period_id
                                    join tbl_hrm_shifts s on e.Shift_ID = s.Shift_ID
                                   ' . (!empty($from_date) ? ' left join tbl_hrm_shift_hours hr on hr.shift_id = s.Shift_ID 
                                    and hr.month = "' . $month . '" and (hr.year = "' . $year . '" or hr.year = 0)' : '') . '
                                    WHERE
                                            e.Status_ID = 1 
                ' . $condition . ((!empty($from_date) ) ? ' order by hr.year desc limit 1' : ''));
        $row = $this->db->assoc($query);
        $currency = $row['basic_salary'] == 0 ? $row['Paygrade_Currency'] : $row['basic_salary_currency'];
        $rate = CurrencyFactory::getLastExRate($currency);
        if (!empty($row))
            $row['Basic_Salary'] = $row['Basic_Salary'] * $rate;
        //            $data[] = $row;
        //die(print_r($row));
        return $row;
    }

    //    public function returnPaySlip($date, $emp_id = null, $year, $month, $is_manager = false, $branch_id = null) {
    //        $pay_slip = array();
    //        $data = self::returnEmployeesInfoForPayroll($date, $emp_id, $year, $month, null, null, null, null, $is_manager, $branch_id);
    //        $total_basic_salary = 0;
    //        $total_lunch = 0;
    //        $total_fam_allow = 0;
    //        $total_transport = 0;
    //        $total_phone_recharge = 0;
    //        $total_overtime = 0;
    //        $total_gross = 0;
    //        $total_advance_loan = 0;
    //        $total_napsa = 0;
    //        $total_paye = 0;
    //        $total_days_absent = 0;
    //        $total_absent_amount = 0;
    //        $total_total_deducation = 0;
    //        $total_net_pay = 0;
    //        $total_bonuses = 0;
    //        $total_deductions = 0;
    //        $default_currency_id = CurrencyFactory::getDefaultCurrency();
    //        if ($is_manager)
    //            $default_currency = CurrencyFactory::getCurrencySymbol("144");
    //        else
    //            $default_currency = CurrencyFactory::getDefaultCurrencySymbol();
    //        $year = substr($date, 0, 4);
    //        $month = substr($date, 5, 2);
    //        for ($i = 0; $i < count($data); $i++) {
    //            $overtime_first = self::returnOverTime($data[$i]['Employee_ID'], $year, $month);
    //            $start_year = substr($data[$i]['Employee_StartDate'], 0, 4);
    //            $start_month = substr($data[$i]['Employee_StartDate'], 5, 2);
    //            $start_day = substr($data[$i]['Employee_StartDate'], 8, 2);
    //            if ($is_manager) {
    //                $basic_salary = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], "144", $data[$i]['Basic_Salary']);
    //                $lunch = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], "144", $data[$i]['Employee_Lunch']);
    //                $transport = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], "144", $data[$i]['Employee_Transportation']);
    //                $phone_recharge = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], "144", $data[$i]['Employee_Phone_Recharge']);
    //                $overtime = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], "144", $overtime_first);
    //                $days_absent = self::returnAbsentDaysForEmployee($data[$i]['Employee_ID'], $year, $month);
    //                $absent_amount_first = self::returnAbsentAmountForEmployee($basic_salary, $data[$i]['Employee_ID'], $year, $month);
    //                $absent_amount = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($data[$i]['Paygrade_Currency'], "144", $absent_amount_first);
    //                $bonus = self::returnBonusOrDeductionForEmployee($data[$i]['Employee_ID'], "Bonus", "tbl_hrm_bonuses", $year, $month, null, null, false, true);
    //                $deduction = self::returnBonusOrDeductionForEmployee($data[$i]['Employee_ID'], "Deduction", "tbl_hrm_deductions", $year, $month, null, null, false, true);
    //                $fam_allow = 0;
    //                $first_gross = $basic_salary + $lunch + $fam_allow + $transport + $phone_recharge;
    //                if ($start_year == $year and $start_month == $month and $start_day > 1) {
    //                    $diff = date_diff(date_create($year . '-' . $month . '-01'), date_create($data[$i]['Employee_StartDate']));
    //                    $days_diff = $diff->format("%a");
    //                    $gross_per_day = $first_gross / 30;
    //                    $deduc_gross = $gross_per_day * $days_diff;
    //                    $gross = $first_gross - $deduc_gross + $overtime + $bonus;
    //                } else
    //                    $gross = $first_gross + $overtime + $bonus;
    //                $napsa = 0;
    //                $paye = 0;
    //                $advance_loan = self::returnAdvanceLoanByMonth($data[$i]['Employee_ID'], $year, $month, $date, true);
    //            }
    //            else {
    //                $basic_salary = CurrencyFactory::returnPriceInDefaultCurrencyValue($data[$i]['Paygrade_Currency'], $data[$i]['Basic_Salary']);
    //                $lunch = CurrencyFactory::returnPriceInDefaultCurrencyValue($data[$i]['Paygrade_Currency'], $data[$i]['Employee_Lunch']);
    //                $transport = CurrencyFactory::returnPriceInDefaultCurrencyValue($data[$i]['Paygrade_Currency'], $data[$i]['Employee_Transportation']);
    //                $phone_recharge = CurrencyFactory::returnPriceInDefaultCurrencyValue($data[$i]['Paygrade_Currency'], $data[$i]['Employee_Phone_Recharge']);
    //                $overtime = CurrencyFactory::returnPriceInDefaultCurrencyValue($data[$i]['Paygrade_Currency'], $overtime_first);
    //                $days_absent = self::returnAbsentDaysForEmployee($data[$i]['Employee_ID'], $year, $month);
    //                $absent_amount_first = self::returnAbsentAmountForEmployee($basic_salary, $data[$i]['Employee_ID'], $year, $month);
    //                $absent_amount = CurrencyFactory::returnPriceInDefaultCurrencyValue($data[$i]['Paygrade_Currency'], $absent_amount_first);
    //                $bonus = self::returnBonusOrDeductionForEmployee($data[$i]['Employee_ID'], "Bonus", "tbl_hrm_bonuses", $year, $month);
    //                $deduction = self::returnBonusOrDeductionForEmployee($data[$i]['Employee_ID'], "Deduction", "tbl_hrm_deductions", $year, $month);
    //                $fam_allow = $basic_salary * 0.3;
    //                $first_gross = $basic_salary + $lunch + $fam_allow + $transport + $phone_recharge;
    //                if ($start_year == $year and $start_month == $month and $start_day > 1) {
    //                    $diff = date_diff(date_create($year . '-' . $month . '-01'), date_create($data[$i]['Employee_StartDate']));
    //                    $days_diff = $diff->format("%a");
    //                    $gross_per_day = $first_gross / 30;
    //                    $deduc_gross = $gross_per_day * $days_diff;
    //                    $gross = $first_gross - $deduc_gross + $overtime + $bonus;
    //                } else
    //                    $gross = $first_gross + $overtime + $bonus;
    //                $napsa = $gross * 0.05;
    //                $paye = self::returnPAYE($gross, $napsa, $default_currency_id);
    //                $advance_loan = self::returnAdvanceLoanByMonth($data[$i]['Employee_ID'], $year, $month, $date);
    //            }
    //            $total_deducation = $absent_amount + $advance_loan + $napsa + $paye + $deduction;
    //            $net_pay = $gross - $total_deducation;
    //            $tem_array = array(
    //                'Work_ID' => $data[$i]['Employee_Work_ID'],
    //                'Employee_ID' => $data[$i]['Employee_ID'],
    //                'Employee_Name' => $data[$i]['Employee_Name'],
    //                'Basic_Salary' => $basic_salary,
    //                'Lunch' => $lunch,
    //                'Allowance' => $fam_allow,
    //                'Transportation' => $transport,
    //                'Phone_Recharge' => $phone_recharge,
    //                'OverTime' => $overtime,
    //                'Gross' => $gross,
    //                'Advance_Loan' => $advance_loan,
    //                'Napsa' => $napsa,
    //                'PAYE' => $paye,
    //                'Days_Absent' => $days_absent,
    //                'Absent_Amount' => $absent_amount,
    //                'Bonus' => $bonus,
    //                'Deduction' => $deduction,
    //                'Total_Deduction' => $total_deducation,
    //                'Net_Pay' => $net_pay
    //            );
    //            $total_basic_salary += $basic_salary;
    //            $total_lunch += $lunch;
    //            $total_fam_allow += $fam_allow;
    //            $total_transport += $transport;
    //            $total_phone_recharge += $phone_recharge;
    //            $total_overtime += $overtime;
    //            $total_gross += $gross;
    //            $total_advance_loan += $advance_loan;
    //            $total_napsa += $napsa;
    //            $total_paye += $paye;
    //            $total_days_absent += $days_absent;
    //            $total_absent_amount += $absent_amount;
    //            $total_total_deducation += $total_deducation;
    //            $total_net_pay += $net_pay;
    //            $total_deductions += $deduction;
    //            $total_bonuses += $bonus;
    //            array_push($pay_slip, $tem_array);
    //            $tem_array = array();
    //        }
    //        $pay_slip['Total_Basic_Salary'] = $total_basic_salary;
    //        $pay_slip['Total_Lunch'] = $total_lunch;
    //        $pay_slip['Total_Allowance'] = $total_fam_allow;
    //        $pay_slip['Total_Transportation'] = $total_transport;
    //        $pay_slip['Total_Phone_Recharge'] = $total_phone_recharge;
    //        $pay_slip['Total_OverTime'] = $total_overtime;
    //        $pay_slip['Total_Gross'] = $total_gross;
    //        $pay_slip['Total_Advance'] = $total_advance_loan;
    //        $pay_slip['Total_Napsa'] = $total_napsa;
    //        $pay_slip['Total_PAYE'] = $total_paye;
    //        $pay_slip['Total_Days_Absent'] = $total_days_absent;
    //        $pay_slip['Total_Absent_Amount'] = $total_absent_amount;
    //        $pay_slip['Total_Deduction'] = $total_total_deducation;
    //        $pay_slip['Total_Net_Pay'] = $total_net_pay;
    //        $pay_slip['Total_Deduc'] = $total_deductions;
    //        $pay_slip['Total_Bonus'] = $total_bonuses;
    //        $pay_slip['Default_Currency'] = $default_currency;
    //        //        if ($pay_slip['Total_Basic_Salary'] == 0)
    //        //            $pay_slip = array();
    //        return $pay_slip;
    //    }
    //    private function returnPAYE($gross, $napsa, $currency) {
    //        //163 currency id of zambia
    //        //		if ($is_manager)
    //        //        	$max = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies("144","144","255");
    //        //		else
    //        //			$max = CurrencyFactory::returnPriceInDefaultCurrencyValue(163,255);
    //        $max = 255;
    //        $napsa = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($currency, "163", "255");
    //        $gross = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($currency, "163", $gross);
    //        if ($napsa > $max)
    //            $napsa = $max;
    //        $sal = $gross - $napsa;
    //        $paye = 0;
    //        //		if ($is_manager) {
    //        //			$first_boundary = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies("163","144","3000");
    //        //			$second_boundary = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies("163","144","3800");
    //        //			$third_boundary = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies("163","144","5900");
    //        //		}
    //        //		else {
    //        //			$first_boundary = CurrencyFactory::returnPriceInDefaultCurrencyValue(163,3000);
    //        //			$second_boundary = CurrencyFactory::returnPriceInDefaultCurrencyValue(163,3800);
    //        //			$third_boundary = CurrencyFactory::returnPriceInDefaultCurrencyValue(163,5900);
    //        //		}
    //        $first_boundary = 3000;
    //        $second_boundary = 3800;
    //        $third_boundary = 5900;
    //        if ($sal > 0 && $sal <= $first_boundary)
    //            $paye = 0;
    //        elseif ($sal > $first_boundary && $sal <= $second_boundary) {
    //            $tax = $sal - $first_boundary;
    //            $paye = $tax * 0.25;
    //        } elseif ($sal > $second_boundary && $sal <= $third_boundary) {
    //            $tax1 = $second_boundary - $first_boundary;
    //            $paye1 = $tax1 * 0.25;
    //            $tax2 = $sal - $second_boundary;
    //            $paye2 = $tax2 * 0.3;
    //            $paye = $paye1 + $paye2;
    //        } else {
    //            $tax1 = $second_boundary - $first_boundary;
    //            $paye1 = $tax1 * 0.25;
    //            $tax2 = $third_boundary - $second_boundary;
    //            $paye2 = $tax2 * 0.3;
    //            $tax3 = $sal - $third_boundary;
    //            $paye3 = $tax3 * 0.35;
    //            $paye = $paye1 + $paye2 + $paye3;
    //        }
    //        $paye = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies("163", $currency, $paye);
    //        return $paye;
    //    }
    //    public function viewPaySlipByEmployeeForUnpaidMonths($employee_id) {
    //        $query = $this->db->query('SELECT 
    //										Employee_StartDate
    //									FROM 
    //										tbl_hrm_employee
    //									WHERE 
    //										Employee_ID = "' . $employee_id . '"
    //				');
    //        $row = $this->db->assoc($query);
    //        $month = substr($row['Employee_StartDate'], 5, 2);
    //        $year = substr($row['Employee_StartDate'], 0, 4);
    //        $current_date = Date::getToday();
    //        $current_month = substr($current_date, 5, 2);
    //        $current_year = substr($current_date, 0, 4);
    //        $data = array();
    //        for ($i = $month; $i < ($current_month + 1); $i++) {
    //            $paid = $this->db->query('SELECT 
    //                                        Is_Paid
    //                                FROM 
    //                                        tbl_hrm_payroll
    //                                WHERE 
    //                                        Employee_ID = "' . $employee_id . '"
    //                                AND 
    //                                        MONTH(Payroll_Date) = "' . $i . '"
    //                                AND 
    //                                        YEAR(Payroll_Date) = "' . $current_year . '"
    //				');
    //            $paid_row = $this->db->assoc($paid);
    //            if (count($paid_row) == 0 or $paid_row['Is_Paid'] == 0) {
    //                $temp = self::returnPaySlip(null, $employee_id, $year, $i);
    //                $data[$i] = $temp;
    //                $temp = array();
    //            }
    //        }
    //        return $data;
    //    }
    public function get_basic_salary_for_tax($emp_id, $from_date, $current_salary, $current_allowance) {
        //        echo 'from date:'.$from_date.'===';
        $total_salary = 0;
        $total_allowance = 0;
        $total_tax = 0;
        $to_month = '';
        $month_count = 0;
        $query = $this->db->query('select o.*
                ,if(year(e.Employee_StartDate)=year("' . $from_date . '"),month(e.Employee_StartDate)-1,0) as month_start
            from tbl_hrm_employee_opening o 
            join tbl_hrm_employee e on e.Employee_ID = o.employee_id
            where o.employee_id = "' . $emp_id . '"
            and year(to_date) = year("' . $from_date . '")');
        $row = $this->db->assoc($query);
        if (!empty($row)) {
            $total_salary = $row['total_salary'] * $row['current_rate'];
            $total_tax = $row['total_tax'] * $row['current_rate'];
            $total_allowance = $row['total_allowance'] * $row['current_rate'];
            if ($total_allowance == 0) {
                $open_month = date('m', strtotime($row['to_date']));
                $total_allowance = $open_month * $current_allowance;
            }
            $to_month = $row['to_date'];
            $month_count = (date('m', strtotime($to_month)) - $row['month_start']);
            //            echo $from_date.'====='.$row['month_start'].'====='.$month_count.'====';
            //            echo $total_salary.'==';
        }
        //         echo 'month count:'.$month_count.'==';
        //if no open data, get already paid payroll data
        $query_payroll = $this->db->query('select other_prices from tbl_hrm_payroll 
            where Employee_ID = "' . $emp_id . '" and year(Payroll_Date) = year("' . $from_date . '")
        and Payroll_Date < "' . $from_date . '" ' . (!empty($to_month) ? ' and Payroll_Date > "' . $to_month . '"' : ''));
        while ($pay_row = $this->db->assoc($query_payroll)) {
            $data_pay = json_decode($pay_row['other_prices'], true);
            //            echo '==---'.$data_pay['details']['basic_salary_tax'].'--==';
            $total_salary += $data_pay['details']['basic_salary_tax'];
            $total_allowance += $data_pay['details']['family'];
            $total_tax += $data_pay['details']['tax'];
            $month_count++;
            //            echo $total_salary.'==';
            //             echo 'month count:'.$month_count.'==';
        }
        //        echo 'month count:'.$month_count;
        //        $current_month = date('m', strtotime($from_date));
        //                echo '======'.$total_salary.'===';
        //add current month amount
        $total_salary += $current_salary;
        $total_allowance += $current_allowance;
        $month_count++;
        //                                echo $total_salary.'===';
        //  echo 'month count:'.$month_count.'==';
        return ["total_salary" => $total_salary, "total_allowance" => $total_allowance, "total_tax" => $total_tax, "month_count" => $month_count];
    }

    private function returnAmountTaxableForPayroll($allow_new, $basic_salary) {
        //                echo $basic_salary.'=='.$allow_new;
        $amount_taxable = $basic_salary - $allow_new;
        return $amount_taxable;
    }

    private function get_emp_tax($amount_taxable, $total_tax, $month_count) {
        //        echo $amount_taxable.'++==='.$total_tax.'++===='.$month_count.'===';
        $tax = 0;
        if ($amount_taxable < 0) {//if he has old paid tax then he wil retake them else will be 0
            $amount_taxable = ((($amount_taxable * 0.02) / 12) * $month_count);
            $tax = max(($total_tax * -1), $amount_taxable);
        } else {
            $tax = min(6000000, $amount_taxable) * 0.02;
            $amount_taxable -= 6000000;
            if ($amount_taxable > 0) {
                $tax += (min(9000000, $amount_taxable) * 0.04); //between 6,000,000 and 15,000,000
                $amount_taxable -= 9000000;
            }
            if ($amount_taxable > 0) {
                $tax += (min(15000000, $amount_taxable) * 0.07); //between 15,000,000 and 30,000,000
                $amount_taxable -= 15000000;
            }
            if ($amount_taxable > 0) {
                $tax += (min(30000000, $amount_taxable) * 0.11); //between 30,000,000 and 60,000,000
                $amount_taxable -= 30000000;
            }
            if ($amount_taxable > 0) {
                $tax += (min(60000000, $amount_taxable) * 0.15); //between 60,000,000 and 120,000,000
                $amount_taxable -= 60000000;
            }
            if ($amount_taxable > 0) {
                $tax += ($amount_taxable * 0.2); //between greater than 120,000,000
            }
            //            echo'tax:'.$tax.'===';
            $tax = (($tax / 12) * $month_count) - $total_tax;
        }
        //        echo $tax.'====';
        return $tax;

        //        if ($amount_taxable <= 0)
        //            return 0;
        //        else if ($amount_taxable <= 6000000)
        //            return $amount_taxable * 0.02;
        //        elseif ($basic_salary > 6000000 && $basic_salary <= 15000000)
        //            return $amount_taxable * 0.04;
        //        elseif ($basic_salary > 15000000 && $basic_salary <= 30000000)
        //            return $amount_taxable * 0.07;
        //        elseif ($basic_salary > 30000000 && $basic_salary <= 60000000)
        //            return $amount_taxable * 0.11;
        //        elseif ($basic_salary > 60000000 && $basic_salary <= 120000000)
        //            return $amount_taxable * 0.15;
        //        elseif ($basic_salary > 120000000)
        //            return $amount_taxable * 0.2;
    }

    public function returnOverTime($employee, $from_date, $to_date) {
        $result = 0.0;
        $query = $this->db->query('SELECT 
                                        sum(Overtime_Amount*current_rate) as Overtime_Amount
                                    FROM 
                                        tbl_hrm_employee_overtime_hours
                                    WHERE 
                                        Employee_ID = "' . $employee . '"
                                    AND OverTime_Date between  "' . $from_date . '" AND "' . $to_date . '"
                                    AND 
                                        Is_Paid = 0 and is_deduction=0 and is_approved = 1 and type in (0,2)
                ');
        $row = $this->db->assoc($query);
        if (!empty($row['Overtime_Amount']))
            $result = $row['Overtime_Amount'];
        else
            $result = 0;
        return $result;
    }

    private function updateOverTimeToPaid($employee, $year, $month) {
        $data = [&$employee, &$year, &$month];
        $this->db->prepare_Stmt('UPDATE
                                            tbl_hrm_employee_overtime_hours
                                    SET 
                                            Is_Paid = 1
                                    WHERE 
                                            Employee_ID = ?
                                    AND 
                                        YEAR(OverTime_Date) = ?
                                    AND 
                                        MONTH(OverTime_Date) = ?
                                    AND 
                                        Is_Paid = 0
				', $data);
    }

    private function updateAdvanceToPaid($employee, $year, $month) {
        $data = [&$employee, &$year, &$month];
        $this->db->prepare_Stmt('UPDATE
                                            tbl_hrm_advance_payments
                                    SET 
                                            is_paid = 1
                                    WHERE 
                                            Employee_ID = ?
                                    AND 
                                        YEAR(Advance_Payment_Date) = ?
                                    AND 
                                        MONTH(Advance_Payment_Date) = ?
                                    AND 
                                        is_paid = 0
				', $data);
    }

    private function updateDedcutionToPaid($employee, $year, $month) {
        $data = [&$employee, &$year, &$month];
        $this->db->prepare_Stmt('UPDATE
                                            tbl_hrm_deductions
                                    SET 
                                            is_paid = 1
                                    WHERE 
                                            Employee_ID = ?
                                    AND 
                                        YEAR(Deduction_Date) = ?
                                    AND 
                                        MONTH(Deduction_Date) = ?
                                    AND 
                                        is_paid = 0
				', $data);
    }

    private function updateBonusToPaid($employee, $year, $month) {
        $data = [&$employee, &$year, &$month];
        $this->db->prepare_Stmt('UPDATE
                                            tbl_hrm_bonuses
                                    SET 
                                            is_paid = 1
                                    WHERE 
                                            Employee_ID = ?
                                    AND 
                                        YEAR(Bonus_Date) = ?
                                    AND 
                                        MONTH(Bonus_Date) = ?
                                    AND 
                                        is_paid = 0
				', $data);
    }

    private function updateLoanToPaid($employee, $year, $month, $amount) {
        $query = $this->db->query('select * from tbl_hrm_loans
                    where  Employee_ID = "' . $employee . '"
                    AND year(Loan_Repayment_Start) <= "' . $year . '"
                    AND month(Loan_Repayment_Start) <= "' . $month . '"
                    AND Loan_Amount > Payment_Amount and is_approved = 1 order by Loan_Repayment_Start asc limit 1');
        $row = $this->db->assoc($query);
        if (!empty($row)) {
            $loan_id = $row['Loan_ID'];
            $currency_id = $row['Currency_ID'];
            $amount = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $currency_id, $amount);

            $data = [&$amount, &$loan_id];
            $this->db->prepare_Stmt('UPDATE
                                            tbl_hrm_loans
                                    SET 
                                            Payment_Amount = Payment_Amount + ?
                                    WHERE 
                                            Loan_ID = ?

				', $data);
        }
    }

    private function returnTaxForPayroll($amount_taxable, $div, $currency_id) {
        $tax = 0;
        if ($amount_taxable <= 0)
            return $tax;
        else {
            $query = $this->db->query('SELECT * FROM tbl_hrm_boundaries_amount_taxable');
            while ($row = $this->db->assoc($query)) {
                $row['Boundary_Amount'] = $row['Boundary_Amount'] / $div;
                $data[] = $row;
            }
            $amount_taxable = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($currency_id, "84", $amount_taxable);
            if ($amount_taxable > 0 && $amount_taxable <= $data[0]['Boundary_Amount'])
                $tax = $amount_taxable * 0.02;
            elseif ($amount_taxable > $data[0]['Boundary_Amount'] && $amount_taxable <= $data[1]['Boundary_Amount']) {
                $remaining = $amount_taxable - $data[0]['Boundary_Amount'];
                $tax = (0.02 * $data[0]['Boundary_Amount']) + (0.04 * $remaining);
            } elseif ($amount_taxable > $data[1]['Boundary_Amount'] && $amount_taxable <= $data[2]['Boundary_Amount']) {
                $remaining = $amount_taxable - $data[1]['Boundary_Amount'];
                $tax = (0.02 * $data[0]['Boundary_Amount']) + (0.04 * $data[1]['Boundary_Amount']) + (0.07 * $remaining);
            } elseif ($amount_taxable > $data[2]['Boundary_Amount'] && $amount_taxable <= $data[3]['Boundary_Amount']) {
                $remaining = $amount_taxable - $data[3]['Boundary_Amount'];
                $tax = (0.02 * $data[0]['Boundary_Amount']) + (0.04 * $data[1]['Boundary_Amount']) + (0.07 * $data[2]['Boundary_Amount']) + (0.11 * $remaining);
            } elseif ($amount_taxable > $data[3]['Boundary_Amount'] && $amount_taxable <= $data[4]['Boundary_Amount']) {
                $remaining = $amount_taxable - $data[3]['Boundary_Amount'];
                $tax = (0.02 * $data[0]['Boundary_Amount']) + (0.04 * $data[1]['Boundary_Amount']) + (0.07 * $data[2]['Boundary_Amount']) + (0.11 * $data[3]['Boundary_Amount']) + (0.15 * $remaining);
            } else {
                $remaining = $amount_taxable - $data[4]['Boundary_Amount'];
                $tax = (0.02 * $data[0]['Boundary_Amount']) + (0.04 * $data[1]['Boundary_Amount']) + (0.07 * $data[2]['Boundary_Amount']) + (0.11 * $data[3]['Boundary_Amount']) + (0.15 * $data[4]['Boundary_Amount']) + (0.2 * $remaining);
            }
        }
        $tax = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies("84", $currency_id, $tax);
        return $tax;
    }

    private function returnSSNFForPayroll($basic_salary, $currency_id) {
        $basic_salary = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($currency_id, smc_session()['local_currency'], $basic_salary);
        $ssnf = $basic_salary * 0.03;
        if ($ssnf > 2500000)
            $ssnf = 2500000;
        $ssnf = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $currency_id, $ssnf);
        return $ssnf;
    }

    private function returnTotalDeducationForPayroll($tax, $ssnf) {
        return $tax + $ssnf;
    }

    private function returnNetPayForPayroll($gross, $total_deducation) {
        return $gross - $total_deducation;
    }

    private function returnLeavIndForPayroll($basic_salary) {
        return $basic_salary * 0.085;
    }

    private function returnFamAllowForPayroll($basic_salary, $currency_id) {
        $fam_allow = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($currency_id, smc_session()['local_currency'], $basic_salary) * 0.06;
        if ($fam_allow > 1500000)
            $fam_allow = 1500000;
        $fam_allow = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $currency_id, $fam_allow);
        return $fam_allow;
    }

    private function returnMedInsForPayroll($basic_salary, $currency_id) {
        $med_ins = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($currency_id, smc_session()['local_currency'], $basic_salary) * 0.08;
        if ($med_ins > 2500000)
            $med_ins = 2500000;
        $med_ins = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $currency_id, $med_ins);
        return $med_ins;
    }

    private function returnTotalForPayroll($leav, $fam, $med) {
        return $leav + $fam + $med;
    }

    public static function viewEmployeeAccount($id) {
        $db = DBConnectorFactory::loadDriver();
        $data = array('exp' => smc_session()['account_setting']['emp_exp_parent_acc_id'],
            'inc' => smc_session()['account_setting']['emp_income_parent_acc_id']);
        $employee_code = $db->query('SELECT 
                                                Account_ID,main_type,sub_type 
                                            FROM 

                                                tbl_chart_of_accounts 
                                            WHERE 
                                                main_id = "' . $id . '" and main_type="emp"');
        while ($emp_code = $db->assoc($employee_code)) {
            if (!empty($emp_code))
                $data[$emp_code['sub_type']] = $emp_code['Account_ID'];
        }
        return $data;
    }

    public function submitUdetailedPayRoll($flag = 0) {
        $check = CommonFunctions::check_lock_data(['type' => 'payroll', 'start_date' => $this->info['main']['from_date'], 'end_date' => $this->info['main']['to_date'], 'is_actual' => $flag]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');

        $today = Date::getToday();
        $mydate = date("Y-m-d H:i:s");
        $date_created = $mydate;
        $created_logged_id = smc_session()['user_logged_id'];
        $logged_id = smc_session()['user_logged_id'];
        $jv = '';
        if (!empty($this->info['payroll_id'])) {
            $query_old = $this->db->query('select JV_ID,date_created,created_logged_id 
                from tbl_accounting_transactions where main_id="' . $this->info['payroll_id'] . '"
                    and main_type="prl" ');
            $old_pay = $this->db->assoc($query_old);
            $jv = $old_pay['JV_ID'];
            $date_created = $old_pay['date_created'];
            $created_logged_id = $old_pay['created_logged_id'];
            $this->db->prepare_Stmt('delete from tbl_hrm_payroll where Payroll_ID = "' . $this->info['payroll_id'] . '"');
            $this->db->prepare_Stmt('delete from tbl_accounting_transactions
                where main_id = "' . $this->info['payroll_id'] . '" and main_type="prl"');
        }
        $query = $this->db->query('select * from tbl_hrm_payroll 
            where (from_date between "' . $this->info['main']['from_date'] . '" and "' . $this->info['main']['to_date'] . '"
            or Payroll_Date between     "' . $this->info['main']['from_date'] . '" and "' . $this->info['main']['to_date'] . '" 
                or  "' . $this->info['main']['from_date'] . '" between from_date and Payroll_Date
                or  "' . $this->info['main']['to_date'] . '" between from_date and Payroll_Date ) and Employee_ID = "' . $this->info['main']['Employee_ID'] . '"');
        $rowsno = $this->db->rowsno($query);
        if ($rowsno > 0) {
            $error[0] = CommonLanguage::getMessage('already_exist_date');
            return $error;
        }


        $date = Date::getToday(); //year-month-day
        $year = substr($this->info['main']['to_date'], 0, 4);
        $month = substr($this->info['main']['to_date'], 5, 2);
        $payment_account = $this->info['main']['Payment_Method'];
        $ref = str_replace("-", "", $date);
        $query = $this->db->prepare_Stmt('select Working_Day_ID from tbl_hrm_shifts join tbl_hrm_employee using(Shift_ID) where Employee_ID = ? ', [&$this->info['main']['Employee_ID']]);
        $shift_work_day = $this->db->assoc($query)['Working_Day_ID'];

        $others = json_encode($this->info);

        $data = [&$this->info['main']['Employee_ID'],
            &$this->info['main']['from_date'],
            &$this->info['main']['to_date'],
            &$this->info['main']['paid_amount'],
            &smc_session()['local_currency'],
            &$today,
            &$this->info['details']['advance_payment'],
            &$this->info['details']['loan_payment'],
            &$this->info['details']['deduction'],
            &$this->info['details']['bonus'],
            &$others,
            &$shift_work_day, &$date_created, &$mydate, &$logged_id, &$created_logged_id, &$payment_account];
//die('INSERT INTO tbl_hrm_payroll ('.(!empty($this->info['payroll_id']) ? 'Payroll_ID,' : '').'Employee_ID,from_date,Payroll_Date,Is_Paid,
//         Amount_Paid,Currency_ID,Payroll_Paid_Date,Advance_Payment,Loan_Payment,Deduction_Payment,
//         Bonus_Payment,other_prices,shift_work_day,date_created,date_modified,Logged_ID,created_logged_id,payment_account_id)
//         VALUES ('.(!empty($this->info['payroll_id']) ? '"'.$this->info['payroll_id'].'",' : '').'?,?,?,"1",?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
        $this->db->prepare_Stmt('INSERT INTO tbl_hrm_payroll (' . (!empty($this->info['payroll_id']) ? 'Payroll_ID,' : '') . 'Employee_ID,from_date,Payroll_Date,Is_Paid,
         Amount_Paid,Currency_ID,Payroll_Paid_Date,Advance_Payment,Loan_Payment,Deduction_Payment,
         Bonus_Payment,other_prices,shift_work_day,date_created,date_modified,Logged_ID,created_logged_id,payment_account_id)
         VALUES (' . (!empty($this->info['payroll_id']) ? '"' . $this->info['payroll_id'] . '",' : '') . '?,?,?,"1",?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
//        $error = $this->db->error();
//        die(print_r($data));
        $payroll_id = $this->db->id();

        $desc = "PayRoll for " . $date . " for employee " . $this->info['details']['Name'];
        $jv = !empty($jv) ? $jv : JV::returnJV($flag);
        $employee_acc_arr = self::viewEmployeeAccount($this->info['main']['Employee_ID']);
//        $codes_arr = array($employee_acc_arr['exp'], "63522", "63591", "63599", "43110", "63180", "63110", "4411",
//            "43150", $payment_account, "4283", "4284", "42810", "6351001", "6351002", "6351003", "43150001", "43150002",
//            "43150003");
//        $ids_arr = JV::getAccountIDsByCodes($codes_arr);
        //        echo"<pre>";
        //        die(print_r($ids_arr));
//        if (empty($this->info['payroll_id'])) {
        $arr_prod = ['type' => 'prl', 'main_id' => $payroll_id, 'action' => (empty($this->info['payroll_id']) ? 0 : 1), 'date_time' => $date_created,
            'user_id' => $created_logged_id];
        CommonFunctions::add_user_log($arr_prod);
//        }
        $emp_query = $this->db->query('select early_payroll from tbl_hrm_employee where Employee_ID = "' . $this->info['main']['Employee_ID'] . '"');
        $pay_early = $this->db->assoc($emp_query)['early_payroll'];
        $trans_date = empty($pay_early) ? $this->info['main']['to_date'] : date('Y-m-d', strtotime($this->info['main']['to_date'] . "+1 days"));
        $trans_array = array("Transaction_Reference" => $ref,
            "Transaction_Account" => array(
                $employee_acc_arr['exp'],
                $employee_acc_arr['inc'],
                smc_session()['account_setting']['payroll_transportation_acc_id'], //transportation
                smc_session()['account_setting']['payroll_lunch_acc_id'],
                smc_session()['account_setting']['payroll_other_charges_acc_id'],
                smc_session()['account_setting']['payroll_emp_charge1_acc_id'],
                smc_session()['account_setting']['payroll_emp_charge2_acc_id'],
                smc_session()['account_setting']['payroll_emp_charge3_acc_id'],
                smc_session()['account_setting']['payroll_emp_charge4_acc_id'],
                smc_session()['account_setting']['payroll_phone_acc_id'],
                smc_session()['account_setting']['payroll_allowance_acc_id'],
                smc_session()['account_setting']['payroll_overtime_acc_id'],
                smc_session()['account_setting']['payroll_bonus_acc_id'],
                smc_session()['account_setting']['payroll_tax_acc_id'],
                smc_session()['account_setting']['payroll_nssf_acc_id'],
                smc_session()['account_setting']['payroll_deduction_acc_id'],
                smc_session()['account_setting']['payroll_adv_pay_acc_id'],
                smc_session()['account_setting']['payroll_loan_pay_acc_id'],
                smc_session()['account_setting']['payroll_leave_debit_acc_id'],
                smc_session()['account_setting']['payroll_family_allow_debit_acc_id'],
                smc_session()['account_setting']['payroll_medical_ins_debit_acc_id'],
                smc_session()['account_setting']['payroll_leave_credit_acc_id'],
                smc_session()['account_setting']['payroll_family_allow_credit_acc_id'],
                smc_session()['account_setting']['payroll_medical_ins_credit_acc_id']
            ),
            "Transaction_Type" => array("Debit", "Credit",
                "Debit",
                "Debit",
                "Debit",
                "Debit",
                "Debit",
                "Debit",
                "Debit",
                "Debit",
                "Debit",
                "Debit",
                "Debit",
                "Credit",
                "Credit",
                "Credit",
                "Credit",
                "Credit",
                "Debit",
                "Debit",
                "Debit",
                "Credit",
                "Credit",
                "Credit"
            ),
            "Transaction_Amount" => array($this->info['details']['basic_salary'],
                $this->info['main']['Net_Pay'],
                $this->info['details']['transportation'],
                $this->info['details']['Lunch'],
                $this->info['details']['other_charges'],
                $this->info['details']['emp_charge1'],
                $this->info['details']['emp_charge2'],
                $this->info['details']['emp_charge3'],
                $this->info['details']['emp_charge4'],
                $this->info['details']['Phone'],
                $this->info['details']['allowance'],
                $this->info['details']['overtime'],
                $this->info['details']['bonus'],
                $this->info['details']['tax'],
                $this->info['details']['ssnf'],
                $this->info['details']['deduction'],
                $this->info['details']['advance_payment'],
                $this->info['details']['loan_payment'],
                $this->info['details']['leave_ind'],
                $this->info['details']['fam_allow'],
                $this->info['details']['med_ins'],
                $this->info['details']['leave_ind'],
                $this->info['details']['fam_allow'],
                $this->info['details']['med_ins']
            ),
            "Transaction_Date" => $trans_date,
            "Currency_ID" => smc_session()['local_currency'],
            "Logged_ID" => $this->info[0],
            "date_created" => $date_created,
            "created_logged_id" => $created_logged_id,
            "Transaction_Description" => $desc
        );
        for ($i = 0; $i < count($trans_array['Transaction_Account']); $i++) {
            if ($trans_array['Transaction_Amount'][$i] > 0) {
                $dat_1 = array(
                    "JV_ID" => $jv,
                    "Transaction_Reference" => $trans_array['Transaction_Reference'],
                    "Transaction_Account" => $trans_array['Transaction_Account'][$i],
                    "Transaction_Type" => $trans_array['Transaction_Type'][$i],
                    "Transaction_Amount" => $trans_array['Transaction_Amount'][$i],
                    "Transaction_Date" => $trans_array['Transaction_Date'],
                    "Currency_ID" => $trans_array['Currency_ID'],
                    "Logged_ID" => $trans_array['Logged_ID'],
                    "date_created" => $date_created,
                    "created_logged_id" => $created_logged_id,
                    "Transaction_Description" => $trans_array['Transaction_Description'],
                    "Current_Ex_Rate" => "0",
                    "main_id" => $payroll_id,
                    "main_type" => "prl"
                );
                JV::addTransaction($dat_1, $flag);
            }
        }
        $dat_1 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $employee_acc_arr['inc'],
            "Transaction_Type" => 'Debit',
            "Transaction_Amount" => $this->info['main']['paid_amount'],
            "Transaction_Date" => $trans_date,
            "Currency_ID" => smc_session()['local_currency'],
            "Logged_ID" => $this->info[0],
            "date_created" => $date_created,
            "created_logged_id" => $created_logged_id,
            "Transaction_Description" => $desc,
            "Current_Ex_Rate" => "0",
            "main_id" => $payroll_id,
            "main_type" => "prl",
            "sub_type" => "emp"
        );
        JV::addTransaction($dat_1, $flag);
        $dat_1 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $payment_account,
            "Transaction_Type" => 'Credit',
            "Transaction_Amount" => $this->info['main']['paid_amount'],
            "Transaction_Date" => $trans_date,
            "Currency_ID" => smc_session()['local_currency'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $desc,
            "Current_Ex_Rate" => "0",
            "main_id" => $payroll_id,
            "main_type" => "prl",
            "sub_type" => "pay"
        );
        JV::addTransaction($dat_1, $flag);
        $updated_date = $today;
        $amount_loan = $this->info['details']['loan_payment'];
        $year_update = substr($updated_date, 0, 4);
        $month_update = substr($updated_date, 5, 2);
        self::updateOverTimeToPaid($this->info['main']['Employee_ID'], $year, $month);

        self::updateAdvanceToPaid($this->info['main']['Employee_ID'], $year_update, $month_update);

        self::updateDedcutionToPaid($this->info['main']['Employee_ID'], $year_update, $month_update);
        self::updateBonusToPaid($this->info['main']['Employee_ID'], $year_update, $month_update);
        self::updateLoanToPaid($this->info['main']['Employee_ID'], $year_update, $month_update, $amount_loan);
        //           $error = $this->db->error();
        //        die(print_r($error));
        $error = $this->db->error();
        if (empty($error)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query('COMMIT');
        } else {
            if (!$this->stop_transaction_start_end)
                $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    private function returnBonusOrDeductionForEmployee($employee_id, $prefix, $tbl, $from_date, $to_date) {
        $condition = '';
        $amount = 0;
        if (!empty($from_date) && !empty($to_date))
            $condition .= 'AND ' . $prefix . '_Date between  "' . $from_date . '" and "' . $to_date . '"';


        $query = $this->db->query('SELECT 
                                    SUM(' . $prefix . '_Amount*current_rate) AS Total_Amount,
                                    Currency_ID
                                FROM
                                    ' . $tbl . '
                                WHERE 
                                    Employee_ID = "' . $employee_id . '"' . $condition
        );
        $row = $this->db->assoc($query);
        if (!empty($row))
            $amount += $row['Total_Amount'];

        if ($prefix == 'Deduction') {
            $query = $this->db->query('SELECT 
                                        sum(Overtime_Amount*current_rate) as Overtime_Amount
                                    FROM 
                                        tbl_hrm_employee_overtime_hours
                                    WHERE 
                                        Employee_ID = "' . $employee_id . '"
                                    AND OverTime_Date between  "' . $from_date . '" AND "' . $to_date . '"
                                    AND 
                                        Is_Paid = 0 and is_deduction=1 and is_approved=1 and type in (0,2)
                ');
            $row = $this->db->assoc($query);
            if (!empty($row['Overtime_Amount']))
                $amount += $row['Overtime_Amount'];
        }

        return $amount;
    }

    //    private function returnBonusOrDeductionForEmployee($employee_id, $prefix, $tbl, $year = null, $month = null, $week = null, $day = null, $quarterly = false, $is_manager = false, $currency_id = null) {
    //        $condition = '';
    //        if (!is_null($year))
    //            $condition .= 'AND Year(' . $prefix . '_Date) = "' . $year . '"';
    //        if (!is_null($month))
    //            $condition .= 'AND Month(' . $prefix . '_Date) = "' . $month . '"';
    //        if (!is_null($week))
    //            $condition .= 'AND ROUND(((DAY(' . $prefix . '_Date)-1)/7)+1)  = "' . $week . '"';
    //        if (!is_null($day))
    //            $condition .= 'AND Day(' . $prefix . '_Date) <= "' . $day . '"';
    //        $query = $this->db->query('SELECT 
    //                                    SUM(' . $prefix . '_Amount) AS Total_Amount,
    //                                    Currency_ID
    //                                FROM
    //                                    ' . $tbl . '
    //                                WHERE 
    //                                    Employee_ID = "' . $employee_id . '"' . $condition
    //        );
    //        $row = $this->db->assoc($query);
    //        $amount = 0;
    //        if (!is_null($row['Total_Amount'])) {
    //            if (is_null($currency_id)) {
    //                if ($is_manager)
    //                    $amount = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($row['Currency_ID'], "144", $row['Total_Amount']);
    //                else
    //                    $amount = CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Total_Amount']);
    //            } else
    //                $amount = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($row['Currency_ID'], $currency_id, $row['Total_Amount']);
    //            if ($quarterly)
    //                $amount = $amount / 4;
    //            return $amount;
    //        }
    //        return $amount;
    //    }

    /**
     * Generic function to get an uploaded $_FILE[name_of_file] and save it to a specific path 
     * @param [[Type]] $FileName The name of the input[file] tag that is assigned in the client-side
     * @param [[Type]] $SavePath Destination path
     */
    public function SaveUploadedPhoto($FileName, $SavePath) {
        if (!isset($_FILES[$FileName]))
            return false;
        $SaveToPath = substr($SavePath, 0, strrpos($SavePath, "/") + 1);
        $SaveAsFile = substr($SavePath, strrpos($SavePath, "/") + 1);
        $SaveAsExtention = explode(".", $SaveAsFile);
        $SaveAsExtention = strtolower($SaveAsExtention[count($SaveAsExtention) - 1]);
        $SaveAsFile = substr($SaveAsFile, 0, strrpos($SaveAsFile, "."));
        $fileType = explode(".", basename($_FILES[$FileName]['name']));
        $fileType = strtolower($fileType[count($fileType) - 1]);
        $DestinationFile = $SaveToPath . $SaveAsFile . ".";
        if (file_exists($DestinationFile . $fileType))
            unlink($DestinationFile . $fileType);
        move_uploaded_file($_FILES[$FileName]['tmp_name'], $DestinationFile . $fileType);
        if ($fileType == "png" && $SaveAsExtention == "jpg") {
            if (file_exists($DestinationFile . $fileType))
                unlink($DestinationFile . "jpg");
            $image = imagecreatefrompng($DestinationFile . $fileType);
            imagejpeg($image, $DestinationFile . $SaveAsExtention, 100);
            imagedestroy($image);
            if (file_exists($DestinationFile . $fileType))
                unlink($DestinationFile . $fileType);
        }
        return file_exists($DestinationFile . $SaveAsExtention);
    }

    //    private function returnAbsentAmountForEmployee($basic_salary, $employee_id, $year, $month) {
    //        $total_days = self::returnAbsentDaysForEmployee($employee_id, $year, $month);
    //        $perday = $basic_salary / 30;
    //        $amount = $total_days * $perday;
    //        return $amount;
    //    }

    private function returnAbsentDaysForEmployee($employee_id, $from_date, $to_date) {
        $total_days = 0;
        $taken_days = array();
        $query = $this->db->query('SELECT sum(DATEDIFF(Absence_To_Date,Absence_From_Date)+1) as total_days,
            Absence_Reason
                        FROM tbl_hrm_absence a
                        WHERE a.Employee_ID = "' . $employee_id . '"
                        AND  a.Absence_From_Date between "' . $from_date . '" and "' . $to_date . '"
                            group by a.Absence_Reason');
        while ($row = $this->db->assoc($query))
            $taken_days[$row['Absence_Reason']] = $row['total_days'];
        //
        $query = $this->db->query('SELECT sum(DATEDIFF(Absence_To_Date,Absence_From_Date)+1) as total_days,
            Absence_Reason,Absence_Reason_Days
            FROM  tbl_hrm_absence a
            inner JOIN tbl_hrm_absence_reasons r  ON a.Absence_Reason = r.Absence_Reason_ID 
            WHERE a.Employee_ID = "' . $employee_id . '"
            AND  a.Absence_From_Date between "' . $from_date . '" and "' . $to_date . '"
                group by a.Absence_Reason');
        while ($row = $this->db->assoc($query)) {
            if (empty($row['Absence_Reason_Days']))
                $total_days += $row['total_days'];
            else {
                $remaining = $row['Absence_Reason_Days'] - $taken_days[$row['Absence_Reason']] - $row['total_days'];
                if ($remaining < 0)
                    $total_days += abs($remaining);
            }
        }
        return $total_days;
    }

    private function returnAdvanceByMonth($employee_id, $from_date, $to_date) {
        //        echo $employee_id.'=====';
        $query = $this->db->query('SELECT
                                        sum(Advance_Payment_Amount*current_rate) as Advance_Payment_Amount,
                                        Currency_ID
                                FROM 
                                        tbl_hrm_advance_payments
                                WHERE
                                        Employee_ID = "' . $employee_id . '"
                                AND Advance_Payment_Date between  "' . $from_date . '" AND "' . $to_date . '"
                                    and is_approved=1
				');

        $row = $this->db->assoc($query);
        if (empty($row['Advance_Payment_Amount']))
            $advance_payment = 0;
        else
            $advance_payment = $row['Advance_Payment_Amount'];
        //        echo $advance_payment.'<br>';
        return $advance_payment;
    }

    private function returnLoanByMonth($employee_id, $from_date, $to_date) {
        $query_loan = $this->db->query('SELECT
                                            sum(LEAST(Loan_Repayment_Amount, (Loan_Amount - Payment_Amount))*current_rate) as Loan_Repayment_Amount,
                                            Currency_ID
                                    FROM 
                                            tbl_hrm_loans
                                    WHERE
                                            Employee_ID = "' . $employee_id . '"
                                    AND Loan_Repayment_Start <= "' . $to_date . '"
                                    AND Loan_Amount > Payment_Amount and is_approved = 1
                                    order by Loan_Repayment_Start asc limit 1
				');
        $row = $this->db->assoc($query_loan);
        if (empty($row['Loan_Repayment_Amount']))
            $loan = 0;
        else
            $loan = $row['Loan_Repayment_Amount'];

        //$final = $total_advance + $total_loan;
        return $loan;
    }

    public function ViewEmployeeEmail($emp_id) {
        $data = array();
        $query = $this->db->query('SELECT 
                                       he.Employee_Email ,
                                       u.Employee_ID
                                  FROM 
								   tbl_common_users u
                                     LEFT JOIN 
                                       tbl_hrm_employee he
                                    ON
                                        he.Employee_ID = u.Employee_ID
                                   WHERE
                                    u.User_ID= "' . $emp_id . '"
                                        ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function checkPaidMonthByEmployee($employee_id, $date) {
        $year = substr($date, 0, 4);
        $month = substr($date, 5, 2);
        $query = $this->db->query('SELECT 
                                            Is_Paid
                                    FROM 
                                            tbl_hrm_payroll
                                    WHERE 
                                            Employee_ID = "' . $employee_id . '"
                                    AND 
                                            MONTH(Payroll_Paid_Date) = "' . $month . '"
                                    AND    
                                        YEAR(Payroll_Paid_Date) = "' . $year . '"
				');
        $row = $this->db->assoc($query);
        if (empty($row))
            $row['Is_Paid'] = 0;
        return $row['Is_Paid'];
    }

    public function TotalPaidPayslip($year) {
        $data = array();
        for ($i = 1; $i < 13; $i++) {
            $query = $this->db->query('SELECT 
										Amount_Paid,
                                        Currency_ID,
                                        Payroll_Date
									FROM 
										tbl_hrm_payroll
                                    WHERE 
                                        MONTH(Payroll_Date) = "' . $i . '"
                                    AND    
                                        YEAR(Payroll_Date) = "' . $year . '"
                                        ');
            $total = 0;
            while ($row = $this->db->assoc($query)) {
                $total += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Amount_Paid']);
            }
            $data[$i] = $total;
        }
        return $data;
    }

    public function viewTotalPaidDetails($date) {
        $year = substr($date, 0, 4);
        $month = substr($date, 5, 2);
        $data = array();
        $query = $this->db->query('SELECT 
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                        b.department_name as Branch_Name,
                                        j.Job_Name,
                                        p.Amount_Paid,
                                        c.Currency_Symbol
                                    FROM 
                                        tbl_hrm_payroll p
                                    LEFT JOIN 
                                        tbl_hrm_employee e
                                    ON 
                                        p.Employee_ID = e.Employee_ID
                                    LEFT JOIN 
                                        tbl_department b
                                    ON 
                                        b.department_id = e.Branch_ID
                                    LEFT JOIN 
                                        tbl_hrm_paygrade g
                                    ON 
                                        g.Paygrade_ID = e.Paygrade_ID
                                    LEFT JOIN 
                                        tbl_hrm_jobs j
                                    ON 
                                        j.Job_ID = e.job_id
                                    LEFT JOIN 
                                        tbl_common_currency c
                                    ON 
                                        c.Currency_ID = p.Currency_ID
                                    WHERE 
                                        YEAR(p.Payroll_Date) = "' . $year . '"
                                    AND 
                                        MONTH(p.Payroll_Date) = "' . $month . '"
                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function TotalPaidOverTime($year) {
        $data = array();
        for ($i = 1; $i < 13; $i++) {
            $query = $this->db->query('SELECT 
                                        o.Overtime_Amount,
                                        o.Currency_ID 
                                    FROM 
                                            tbl_hrm_employee_overtime_hours o

                                    WHERE 
                                        MONTH(OverTime_Date) = "' . $i . '"
                                    AND    
                                        YEAR(OverTime_Date) = "' . $year . '"
                                    AND 
                                        o.Is_Paid = 1 and o.is_approved = 1
                                        ');
            $total = 0;
            while ($row = $this->db->assoc($query)) {
                $total += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Overtime_Amount']);
            }
            $data[$i] = $total;
        }
        return $data;
    }

    public function viewTotalPaidOverTimeDetails($date) {
        $year = substr($date, 0, 4);
        $month = substr($date, 5, 2);
        $data = array();
        $query = $this->db->query('SELECT 
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                        b.department_name as Branch_Name,
                                        j.Job_Name,
                                        o.Overtime_Amount,
                                        o.OverTime_Date,
                                        c.Currency_Symbol
                                    FROM 
                                        tbl_hrm_employee_overtime_hours o
                                    LEFT JOIN 
                                        tbl_hrm_employee e
                                    ON 
                                        o.Employee_ID = e.Employee_ID
                                    LEFT JOIN 
                                        tbl_department b
                                    ON 
                                        b.department_id = e.Branch_ID
                                    LEFT JOIN 
                                        tbl_hrm_paygrade g
                                    ON 
                                        g.Paygrade_ID = e.Paygrade_ID
                                    LEFT JOIN 
                                        tbl_hrm_jobs j
                                    ON 
                                        j.Job_ID = e.job_id
                                    LEFT JOIN 
                                        tbl_common_currency c
                                    ON 
                                        c.Currency_ID = o.Currency_ID
                                    WHERE 
                                        YEAR(o.OverTime_Date) = "' . $year . '"
                                    AND 
                                        MONTH(o.OverTime_Date) = "' . $month . '" and o.is_approved=1
                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function TotalPaidAdvance($year) {
        $data = array();
        for ($i = 1; $i < 13; $i++) {
            $query = $this->db->query('SELECT 
                                        p.Advance_Payment_Amount,
                                        p.Currency_ID
					FROM 
				    tbl_hrm_advance_payments p
                                    LEFT JOIN 
                                        tbl_hrm_payroll l
                                    ON 
                                        l.Employee_ID = p.Employee_ID
                                    WHERE 
                                        MONTH(p.Advance_Payment_Date) = "' . $i . '"
                                    AND    
                                        YEAR(p.Advance_Payment_Date) = "' . $year . '"
                                    AND 
                                        MONTH(l.Payroll_Date) = "' . $i . '"
                                    AND    
                                        YEAR(l.Payroll_Date) = "' . $year . '"
                                    AND
                                        l.Is_Paid = 1
                                        and p.is_approved = 1
                                        ');
            $total = 0;
            while ($row = $this->db->assoc($query)) {
                $total += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Advance_Payment_Amount']);
            }
            $data[$i] = $total;
        }
        return $data;
    }

    public function viewTotalPaidAdvanceDetails($date) {
        $year = substr($date, 0, 4);
        $month = substr($date, 5, 2);
        $data = array();
        $query = $this->db->query('SELECT 
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                        b.department_name as Branch_Name,
                                        j.Job_Name,
                                        p.Advance_Payment_Amount,
                                        p.Currency_ID,
                                        p.Advance_Payment_Date,
                                        c.Currency_Symbol
                                    FROM 
                                       tbl_hrm_advance_payments p
                                    LEFT JOIN 
                                        tbl_hrm_payroll l
                                    ON 
                                        l.Employee_ID = p.Employee_ID
                                    LEFT JOIN 
                                        tbl_hrm_employee e
                                    ON 
                                        p.Employee_ID = e.Employee_ID
                                     LEFT JOIN 
                                        tbl_department b
                                    ON 
                                        b.department_id = e.Branch_ID
                                    LEFT JOIN 
                                        tbl_hrm_paygrade g
                                    ON 
                                        g.Paygrade_ID = e.Paygrade_ID
                                    LEFT JOIN 
                                        tbl_hrm_jobs j
                                    ON 
                                        j.Job_ID = e.job_id
                                    LEFT JOIN 
                                        tbl_common_currency c
                                    ON 
                                        c.Currency_ID = p.Currency_ID
                                    WHERE 
                                        MONTH(p.Advance_Payment_Date) = "' . $month . '"
                                    AND    
                                        YEAR(p.Advance_Payment_Date) = "' . $year . '"
                                    AND 
                                        MONTH(l.Payroll_Date) = "' . $month . '"
                                    AND    
                                        YEAR(l.Payroll_Date) = "' . $year . '"
                                    AND
                                        l.Is_Paid = 1 and p.is_approved=1
                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function TotalPaidBonus($year) {
        $data = array();
        for ($i = 1; $i < 13; $i++) {
            $query = $this->db->query('SELECT 
										b.Bonus_Amount,
                                        b.Currency_ID
									FROM 
										tbl_hrm_bonuses b
                                    LEFT JOIN 
                                        tbl_hrm_payroll l
                                    ON 
                                        l.Employee_ID = b.Employee_ID
                                    WHERE 
                                        MONTH(b.Bonus_Date) = "' . $i . '"
                                    AND    
                                        YEAR(b.Bonus_Date) = "' . $year . '"
                                    AND 
                                        MONTH(l.Payroll_Date) = "' . $i . '"
                                    AND    
                                        YEAR(l.Payroll_Date) = "' . $year . '"
                                    AND
                                        l.Is_Paid = 1
                                        ');
            $total = 0;
            while ($row = $this->db->assoc($query)) {
                $total += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Bonus_Amount']);
            }
            $data[$i] = $total;
        }
        return $data;
    }

    public function viewTotalPaidBonusDetails($date) {
        $year = substr($date, 0, 4);
        $month = substr($date, 5, 2);
        $data = array();
        $query = $this->db->query('SELECT 
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                        b.department_name as Branch_Name,
                                        j.Job_Name,
                                        bo.Bonus_Amount,
                                        bo.Bonus_Date,
                                        bo.Currency_ID,
                                        c.Currency_Symbol
                                    FROM 
                                       tbl_hrm_bonuses bo
                                    LEFT JOIN 
                                        tbl_hrm_payroll l
                                    ON 
                                        l.Employee_ID = bo.Employee_ID
                                    LEFT JOIN 
                                        tbl_hrm_employee e
                                    ON 
                                        bo.Employee_ID = e.Employee_ID
                                     LEFT JOIN 
                                        tbl_department b
                                    ON 
                                        b.department_id = e.Branch_ID
                                    LEFT JOIN 
                                        tbl_hrm_paygrade g
                                    ON 
                                        g.Paygrade_ID = e.Paygrade_ID
                                    LEFT JOIN 
                                        tbl_hrm_jobs j
                                    ON 
                                        j.Job_ID = e.job_id
                                    LEFT JOIN 
                                        tbl_common_currency c
                                    ON 
                                        c.Currency_ID = bo.Currency_ID
                                    WHERE 
                                        MONTH(bo.Bonus_Date) = "' . $month . '"
                                    AND    
                                        YEAR(bo.Bonus_Date) = "' . $year . '"
                                    AND 
                                        MONTH(l.Payroll_Date) = "' . $month . '"
                                    AND    
                                        YEAR(l.Payroll_Date) = "' . $year . '"
                                    AND
                                        l.Is_Paid = 1
                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function TotalPaidDeduction($year) {
        $data = array();
        for ($i = 1; $i < 13; $i++) {
            $query = $this->db->query('SELECT 
										d.Deduction_Amount,
                                        d.Currency_ID
									FROM 
										tbl_hrm_deductions d
                                    LEFT JOIN 
                                        tbl_hrm_payroll l
                                    ON 
                                        l.Employee_ID = d.Employee_ID
                                    WHERE 
                                        MONTH(d.Deduction_Date) = "' . $i . '"
                                    AND    
                                        YEAR(d.Deduction_Date) = "' . $year . '"
                                    AND 
                                        MONTH(l.Payroll_Date) = "' . $i . '"
                                    AND    
                                        YEAR(l.Payroll_Date) = "' . $year . '"
                                    AND
                                        l.Is_Paid = 1
                                        ');
            $total = 0;
            while ($row = $this->db->assoc($query)) {
                $total += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Deduction_Amount']);
            }
            $data[$i] = $total;
        }
        return $data;
    }

    public function viewTotalPaidDeductionDetails($date) {
        $year = substr($date, 0, 4);
        $month = substr($date, 5, 2);
        $data = array();
        $query = $this->db->query('SELECT 
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                        b.department_name as Branch_Name,
                                        j.Job_Name,
                                        d.Deduction_Amount,
                                        d.Deduction_Date,
                                        d.Currency_ID,
                                        c.Currency_Symbol
                                    FROM 
                                       tbl_hrm_deductions d
                                    LEFT JOIN 
                                        tbl_hrm_payroll l
                                    ON 
                                        l.Employee_ID = d.Employee_ID
                                    LEFT JOIN 
                                        tbl_hrm_employee e
                                    ON 
                                        d.Employee_ID = e.Employee_ID
                                     LEFT JOIN 
                                        tbl_department b
                                    ON 
                                        b.department_id = e.Branch_ID
                                    LEFT JOIN 
                                        tbl_hrm_paygrade g
                                    ON 
                                        g.Paygrade_ID = e.Paygrade_ID
                                    LEFT JOIN 
                                        tbl_hrm_jobs j
                                    ON 
                                        j.Job_ID = e.job_id
                                    LEFT JOIN 
                                        tbl_common_currency c
                                    ON 
                                        c.Currency_ID = d.Currency_ID
                                    WHERE 
                                        MONTH(d.Deduction_Date) = "' . $month . '"
                                    AND    
                                        YEAR(d.Deduction_Date) = "' . $year . '"
                                    AND 
                                        MONTH(l.Payroll_Date) = "' . $month . '"
                                    AND    
                                        YEAR(l.Payroll_Date) = "' . $year . '"
                                    AND
                                        l.Is_Paid = 1
                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function TotalPaidLoan($year) {
        $data = array();
        for ($i = 1; $i < 13; $i++) {
            $query = $this->db->query('SELECT 
										lo.Loan_Repayment_Amount,
                                        lo.Currency_ID
									FROM 
										tbl_hrm_loans lo
                                    LEFT JOIN 
                                        tbl_hrm_payroll l
                                    ON 
                                        l.Employee_ID = lo.Employee_ID
                                    WHERE 
                                        ((MONTH(lo.Loan_Repayment_Start) <= "' . $i . '"
                                    AND 
                                        YEAR(lo.Loan_Repayment_Start) <= "' . $year . '"
                                        )
                                    OR  
                                        (MONTH(lo.Loan_Repayment_End)>= "' . $i . '"
                                    AND
                                        YEAR(lo.Loan_Repayment_End)>= "' . $year . '"
                                        ))
                                    AND 
                                        MONTH(l.Payroll_Date) = "' . $i . '"
                                    AND    
                                        YEAR(l.Payroll_Date) = "' . $year . '"
                                    AND
                                        l.Is_Paid = 1 and is_approved = 1
                                        ');
            $total = 0;
            while ($row = $this->db->assoc($query)) {
                $total += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Loan_Repayment_Amount']);
            }
            $data[$i] = $total;
        }
        return $data;
    }

    public function viewTotalPaidLoanDetails($date) {
        $year = substr($date, 0, 4);
        $month = substr($date, 5, 2);
        $data = array();
        $query = $this->db->query('SELECT 
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                        b.department_name as Branch_Name,
                                        j.Job_Name,
                                        lo.Loan_Repayment_Amount,
                                        lo.Loan_Date,
                                        lo.Currency_ID,
                                        c.Currency_Symbol
                                    FROM 
                                       tbl_hrm_loans lo
                                    LEFT JOIN 
                                        tbl_hrm_payroll l
                                    ON 
                                        l.Employee_ID = lo.Employee_ID
                                    LEFT JOIN 
                                        tbl_hrm_employee e
                                    ON 
                                        lo.Employee_ID = e.Employee_ID
                                     LEFT JOIN 
                                        tbl_department b
                                    ON 
                                        b.department_id = e.Branch_ID
                                    LEFT JOIN 
                                        tbl_hrm_paygrade g
                                    ON 
                                        g.Paygrade_ID = e.Paygrade_ID
                                    LEFT JOIN 
                                        tbl_hrm_jobs j
                                    ON 
                                        j.Job_ID = e.job_id
                                    LEFT JOIN 
                                        tbl_common_currency c
                                    ON 
                                        c.Currency_ID = lo.Currency_ID
                                    WHERE 
                                        ((MONTH(lo.Loan_Repayment_Start) <= "' . $month . '"
                                    AND 
                                        YEAR(lo.Loan_Repayment_Start) <= "' . $year . '"
                                        )
                                    OR  
                                        (MONTH(lo.Loan_Repayment_End)>= "' . $month . '"
                                    AND
                                        YEAR(lo.Loan_Repayment_End)>= "' . $year . '"
                                        ))
                                    AND 
                                        MONTH(l.Payroll_Date) = "' . $month . '"
                                    AND    
                                        YEAR(l.Payroll_Date) = "' . $year . '"
                                    AND
                                        l.Is_Paid = 1 and lo.is_approved = 1
                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function insertEmployeeDocument() {
        $Fileextension = $_FILES['Document_File_Name']['name'];
        $SaveAsExtention = explode(".", $Fileextension);
        $Extension = $SaveAsExtention[1];
        $today = Date::getToday();
        $file_format = "Y-m-d-H-i";
        $time = date($file_format);
        $type = $this->info['Is_Vlnt'];

        $emp_id = $this->info['Employee_ID'];
        $main_type = $type === 'true' ? "vlnt" : "emp";

        $FileName = $main_type . '__' . $this->info['Document_Type'] . '__' . $time . '__' . $emp_id . '.' . $Extension;
        //        $Insert = $this->_registry->employee->setData($_POST)->insertEmployeeDocument();
        //        $Employee_ID = $_POST["Employee_ID"];
        //        $Document_Type = $_POST["Document_Type"];
        $uploaddir = upload_path . 'Documents/';
        $RET = self::SaveUploadedPhoto("Document_File_Name", $uploaddir . $FileName);
        //        $Fileextension = $_FILES['Document_File_Name']['name'];
        //        $SaveAsExtention = explode(".", $Fileextension);
        //        $Extension = strtolower($SaveAsExtention[1]);
        if (file_exists($uploaddir . $FileName)) {
            //        $FileName = $this->info['Document_Type'] . ' _ ' . $this->info['Employee_ID'] . '.' . $Extension;
            $data = [&$emp_id,
                &$this->info['Document_Type'],
                &$this->info['Document_Name'],
                &$this->info['Document_Date'],
                &$this->info['Document_Comments'],
                &$FileName,
                &$this->info[0], $main_type];
            $this->db->prepare_Stmt('INSERT INTO
                                        tbl_hrm_employee_document(
				                            Employee_ID,
				                            Document_Type,
				                            Document_Name,
				                            Document_Date,
				                            Document_Comments,
				                            Document_File_Name,
				                            Logged_ID,
                                                            main_type
				                        )
                                 VALUES   (?,?,?,?,?,?,?,?) ', $data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            return true;
        } else {
            unlink($uploaddir . $FileName);
            return false;
        }
        //            return true;
        //        return false;
    }

    public function getInfoFromTable($table) {
        $data = array();
        $query = $this->db->query('SELECT * FROM ' . $table);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function addDocumentType() {
        $data = [&$this->info['Document_Type_Name'],
            &$this->info[0]];
        $this->db->prepare_Stmt('INSERT INTO
                                        tbl_hrm_document_types(
				                            Document_Type_Name,
                                            Logged_ID
                                        )
                                    VALUES (?,?)  ', $data);
        $error = $this->db->error();
        return $error;
    }

    public function addEmployeeNationality() {
        $data = [&$this->info['Nationality_Name']];
        $this->db->prepare_Stmt('INSERT INTO
                                        tbl_hrm_nationalities(
				                            Nationality_Name
                                        )
                                    VALUES (?)  ', $data);
        $error = $this->db->error();
        return $error;
    }

    public function addEmployeeLocation() {
        $data = [&$this->info['Location_Name']];
        $this->db->prepare_Stmt('INSERT INTO
                                        tbl_employee_location(
				                            Location_Name
                                        )
                                    VALUES (?)  ', $data);
        $error = $this->db->error();
        return $error;
    }

    public function createEvaluationQuestion() {
        $this->db->prepare_Stmt('INSERT INTO 
                                    tbl_hrm_document_questions(
                                         Question_Name,
                                         Logged_ID
                                    )
                                   VALUES (?,?)', [&$this->info['Question_Name'], &$this->info[0]]);
        $error = $this->db->error();
        return $error;
    }

    public function createEvaluationPerformanceForEmployee() {
        $CheckValueName = serialize($this->info["CheckValueName"]);
        $CheckValue = serialize($this->info["CheckValue"]);
        $ProposedAction = serialize($this->info["ProposedAction"]);
        $data = [&$this->info['Employee_ID'],
            &$this->info['Evaluation_Date'],
            &$this->info['Evaluation_Title'],
            &$this->info['Evaluation_Remarks'],
            &$CheckValue,
            &$CheckValueName,
            &$this->info['Extra_Training'],
            &$ProposedAction,
            &$this->info['Average_Performance'],
            &$this->info[0]];
        $this->db->prepare_Stmt("INSERT INTO 
                                    tbl_employee_performance(
                                         Employee_ID,
                                         Evaluation_Date,
                                         Evaluation_Title,
                                         Evaluation_Remarks,
                                         CheckValue_Evaluation_ID,
                                         Evaluation_Question_ID,
                                         Extra_Training,
                                         ProposedAction,
                                         Average_Performance,
                                         Logged_ID
                                    )
                                   VALUES (?,?,?,?,?,?,?,?,?,? )", $data);
        $error = $this->db->error();
        return $error;
    }

    public function ViewDepartment($id = null) {
        $condition = '';
        $data = array();
        $condition .=!is_null($id) ? ' AND Branch_ID = "' . $id . '"' : '';
        $query = $this->db->query('SELECT * FROM tbl_hrm_branch WHERE Status_ID = 1 ' . $condition);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getEvalutionPerformance($employee_id = null, $year = null, $month = null) {
        $condition = ' ';
        $temp = ' ';
        $data = array();
        if (!is_null($employee_id)) {
            $condition .= ' AND ep.Employee_ID = "' . $employee_id . '" ';
        }
        if (!is_null($year)) {
            $condition .= ' AND YEAR(ep.Evaluation_Date) = "' . $year . '" ';
        }
        if (!is_null($month)) {
            $condition .= ' AND MONTH(ep.Evaluation_Date) = "' . $month . '" ';
        }
        $query = $this->db->query('SELECT 
                                        ep.Employee_ID,
                                        ep.Evaluation_Date,
                                        ep.Evaluation_Title,
                                        ep.Evaluation_Remarks,
                                        ep.ProposedAction,
                                        ep.Extra_Training,
                                        (ep.Average_Performance) AS PerformanceValue,
                                        ep.Average_Performance ,
                                        he.Employee_Work_ID,
                                       CONCAT(he.Employee_FirstName, " ", he.Employee_MiddleName, " ", he.Employee_LastName) AS Employee_Name
                                  FROM 
                                        tbl_employee_performance ep
                                  LEFT JOIN
                                        tbl_hrm_employee he
                                  ON 
                                        he.Employee_ID = ep.Employee_ID
                                WHERE 1=1
                                ' . $condition);
        $action = array();
        while ($row = $this->db->assoc($query)) {
            $temp = unserialize($row['ProposedAction']);
            for ($i = 0; $i < count($temp); $i++) {
                $days_prposed = self::viewPerformanceAction($temp[$i]);
                $row['Action_Name'][] = $days_prposed['Action_Name'];
                $data [] = $row;
            }
        }
        return $data;
    }

    public function viewPerformanceAction($id) {
        $data = '';
        $query = $this->db->query('SELECT
                                    *
                                    FROM 
                                            tbl_performance_action
                                    WHERE
                                            Action_ID = "' . $id . '"
				');
        $row = $this->db->assoc($query);
        $data['Action_ID'] = $row['Action_ID'];
        $data['Action_Name'] = $row['Action_Name'];
        return $data;
    }

    public function performanceGraph($employee_id, $year, $quartly = false, $half = false) {
        for ($i = 1; $i < 13; $i++) {
            $temp1 = self::getEvalutionPerformance($employee_id, $year, $i);
            if (count($temp1) > 0)
                $value[] = round($temp1[0]['PerformanceValue'], 2);
            else
                $value[] = 0;
        }
        if ($quartly) {
            $j1 = 0;
            $temp_1 = 0.0;
            for ($j = 0; $j < 3; $j++) {
                if ($value[$j] > 0) {
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $j1 = 0;
            $temp_1 = 0.0;
            for ($j = 3; $j < 6; $j++) {
                if ($value[$j] > 0) {
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $j1 = 0;
            $temp_1 = 0.0;
            for ($j = 6; $j < 9; $j++) {
                if ($value[$j] > 0) {
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $j1 = 0;
            $temp_1 = 0.0;
            for ($j = 9; $j < 12; $j++) {
                if ($value[$j] > 0) {
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $j1 = 0;
            $temp_1 = 0.0;
            $value = array();
            $value = $temp;
        }
        if ($half) {
            $j1 = 0;
            $temp_1 = 0.0;
            for ($j = 0; $j < 6; $j++) {
                if ($value[$j] > 0) {
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $j1 = 0;
            $temp_1 = 0.0;
            for ($j = 6; $j < 12; $j++) {
                if ($value[$j] > 0) {
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $value = array();
            $value = $temp;
        }
        return $value;
    }

    public function EvaluationPerfromancePerDepartment($branch_id, $year, $quartly = false, $half = false) {
        for ($i = 1; $i < 13; $i++) {
            $temp1 = self::getQueryAveragePerformance($year, $i, $branch_id); // Average Performance Per Department & Month
            if ($temp1 > 0) {
                $value[] = round($temp1, 2);
            } else
                $value[] = 0;
        }
        if ($quartly) {
            $j1 = 0; //counter for values > 0
            $temp_1 = 0.0; // sum for precalculated values 
            for ($j = 0; $j < 3; $j++) {// for each quarter
                if ($value[$j] > 0) {//  to add values  > 0 and to add counter for these values only
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $j1 = 0;
            $temp_1 = 0.0;
            for ($j = 3; $j < 6; $j++) {
                if ($value[$j] > 0) {
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $j1 = 0;
            $temp_1 = 0.0;
            for ($j = 6; $j < 9; $j++) {
                if ($value[$j] > 0) {
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $j1 = 0;
            $temp_1 = 0.0;
            for ($j = 9; $j < 12; $j++) {
                if ($value[$j] > 0) {
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $j1 = 0;
            $temp_1 = 0.0;
            $value = array();
            $value = $temp;
        }
        if ($half) {
            $j1 = 0;
            $temp_1 = 0.0;
            for ($j = 0; $j < 6; $j++) {
                if ($value[$j] > 0) {
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $j1 = 0;
            $temp_1 = 0.0;
            for ($j = 6; $j < 12; $j++) {
                if ($value[$j] > 0) {
                    $temp_1 += $value[$j];
                    $j1++;
                }
            }
            if ($j1 == 0)
                $j1 = 1;
            $temp[] = round(($temp_1) / $j1, 2);
            $value = array();
            $value = $temp;
        }
        return $value;
    }

    private function getQueryAveragePerformance($year, $month = null, $branch_id = null) {
        $condition = ' ';
        $val = array();
        if (!is_null($year)) {
            $condition .= ' AND YEAR(ep.Evaluation_Date) = "' . $year . '" ';
        }
        if (!is_null($month)) {
            $condition .= ' AND MONTH(ep.Evaluation_Date) = "' . $month . '" ';
        }
        if (!is_null($branch_id)) {
            $condition .= ' AND he.Branch_ID = "' . $branch_id . '" ';
        }
        $query = $this->db->query('SELECT 
                                        SUM((ep.Average_Performance ))/COUNT(ep.Employee_ID) AS Avg_Val
                                    FROM 
                                        tbl_employee_performance ep
                                  LEFT JOIN
                                        tbl_hrm_employee he
                                  ON 
                                        he.Employee_ID = ep.Employee_ID
                                WHERE 1=1
                                ' . $condition);
        $val = $this->db->assoc($query);
        return $val['Avg_Val'];
    }

    public function toDolist() {
        $data = [&$this->info['ToDoList'],
            &$this->info['Module_ID'],
            &$this->info[0]];
        $this->db->prepare_Stmt('INSERT INTO tbl_hrm_todolist (
				                        ToDoList,
				                        Module_ID,
				                        Logged_ID
			                          )
			                         VALUES(?,?,?)', $data);
        $error = $this->db->error();
        return $error;
    }

    public function getToDoList($id = null, $module_id = null) {
        $data = array();
        $condition = '';
        if (!is_null($id))
            $condition .= ' AND List_ID = "' . $id . '"';
        if (!is_null($module_id))
            $condition .= ' AND Module_ID = "' . $module_id . '"';
        $query = $this->db->query('SELECT * FROM tbl_hrm_todolist WHERE Status_ID = 1 ' . $condition);
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function updateStatusToDoList() {
        $this->db->query('START TRANSACTION');
        for ($i = 0; $i < count($this->info['ToDo']); $i++) {
            $this->db->prepare_Stmt('UPDATE tbl_hrm_todolist SET Status_ID = 2 WHERE List_ID = ? ', [&$this->info['ToDo'][$i]]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
            return true;
        } else {
            $this->db->query('ROLLBACK');
            return false;
        }
    }

    public function UpdatetoDolist($List_ID) {
        $this->db->prepare_Stmt('UPDATE tbl_hrm_todolist SET ToDoList = ? ,Logged_ID= ? WHERE List_ID = ? ', [&$this->info['ToDoList'], &$this->info[0], &$List_ID]);
        $error = $this->db->error();
        if (empty($error))
            return true;
        return false;
    }

    public function deleteToDoList($List_ID) {
        $this->db->prepare_Stmt('UPDATE tbl_hrm_todolist SET Status_ID = 0 WHERE List_ID = ? ', [&$List_ID]);
        $error = $this->db->error();
        return $error;
    }

    public function getEmployeeAbsence($date) {
        $query = $this->db->query('SELECT 
                                       l.AssignLeave_Reason,
                                       CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name
                                   FROM
                                        tbl_hrm_leave  l
                                    LEFT JOIN
                                        tbl_hrm_employee e
                                    ON 
                                        e.Employee_ID = l.Employee_ID
                                    WHERE 
                                        l.LeaveRequest_Status = 2
                                    AND  
                                        l.AssignLeave_From_Date = "' . $date . '"
        ');
        $i = 0;
        while ($row = $this->db->assoc($query)) {
            $i++;
            $data[] = $row;
        }
        $data[] = $i;
        $presents = $this->db->query('SELECT 
                                        COUNT(Employee_ID) AS Employee_Num 
                                    FROM 
                                        tbl_hrm_employee
                                    WHERE 
                                        Status_ID = 1
                    ');
        $row_presents = $this->db->assoc($presents);
        $data[] = $row_presents['Employee_Num'] - $i;
        $count = count($data);
        $temp_0 = $data[0];
        $data[0] = $data[$count - 2];
        $data[$count - 2] = $temp_0;
        $temp_1 = $data[1];
        $data[1] = $data[$count - 1];
        $data[$count - 1] = $temp_1;
        return $data;
    }

    public function getALlModule() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_logging_modules');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    //    public function returnNAPSAPAYEReport() {
    //        $data = array();
    //        $total_napsa = 0.0;
    //        $total_paye = 0.0;
    //        $i = 0;
    //        $query = $this->db->query('SELECT DISTINCT Payroll_Paid_Date FROM tbl_hrm_payroll');
    //        while ($row = $this->db->assoc($query)) {
    //            $total_napsa_per_date = 0.0;
    //            $total_paye_per_date = 0.0;
    //            $year = substr($row['Payroll_Paid_Date'], 0, 4);
    //            $month = substr($row['Payroll_Paid_Date'], 5, 2);
    //            $day = substr($row['Payroll_Paid_Date'], 8, 2);
    //            $query2 = $this->db->query('SELECT 
    //                                            CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
    //                                            j.Job_Name AS Employee_Position,
    //                                            p.Paygrade_Value AS Basic_Salary,
    //                                            e.Employee_ID,
    //                                            e.Employee_Lunch,
    //                                            e.Employee_StartDate,
    //                                            e.Employee_Transportation,
    //                                            e.Employee_Allownace,
    //                                            e.Employee_Phone_Recharge
    //                                        FROM 
    //                                            tbl_hrm_employee e
    //                                        LEFT JOIN 
    //                                            tbl_hrm_paygrade p
    //                                        ON 
    //                                            e.Paygrade_ID = p.Paygrade_ID
    //                                        LEFT JOIN 
    //                                            tbl_hrm_jobs j
    //                                        ON
    //                                            j.Job_ID = p.Job_ID
    //                                        LEFT JOIN 
    //                                            tbl_hrm_payroll ps
    //                                        ON
    //                                            ps.Employee_ID = e.Employee_ID
    //                                        WHERE
    //                                            e.Status_ID = 1 
    //                                        AND 
    //                                            j.Job_Name NOT LIKE "%manager%"
    //                                        AND
    //                                            Payroll_Paid_Date = "' . $row['Payroll_Paid_Date'] . '"
    //                    ');
    //            $exists = false;
    //            while ($row2 = $this->db->assoc($query2)) {
    //                $start_year = substr($row2['Employee_StartDate'], 0, 4);
    //                $start_month = substr($row2['Employee_StartDate'], 5, 2);
    //                $start_day = substr($row2['Employee_StartDate'], 8, 2);
    //                $overtime = self::returnOverTime($row2['Employee_ID'], $year, $month);
    //                $bonus = self::returnBonusOrDeductionForEmployee($row2['Employee_ID'], "Bonus", "tbl_hrm_bonuses", $year, $month, false, false, false, false, 163);
    //                $first_gross = $row2['Basic_Salary'] + $row2['Employee_Lunch'] + $row2['Employee_Allownace'] + $row2['Employee_Transportation'] + $row2['Employee_Phone_Recharge'];
    //                if ($start_year == $year and $start_month == $month and $start_day > 1) {
    //                    $diff = date_diff(date_create($year . '-' . $month . '-01'), date_create($row2['Employee_StartDate']));
    //                    $days_diff = $diff->format("%a");
    //                    $gross_per_day = $first_gross / 30;
    //                    $deduc_gross = $gross_per_day * $days_diff;
    //                    $gross = $first_gross - $deduc_gross + $overtime + $bonus;
    //                } else
    //                    $gross = $first_gross + $overtime + $bonus;
    //                $napsa = $gross * 0.05;
    //                $paye = self::returnPAYE($gross, $napsa, 163);
    //                $row2['OverTime'] = $overtime;
    //                $row2['Bonus'] = $bonus;
    //                $row2['Gross'] = $gross;
    //                $row2['NAPSA'] = $napsa;
    //                $row2['PAYE'] = $paye;
    //                $total_napsa_per_date += $napsa;
    //                $total_paye_per_date += $paye;
    //                $data[$i][] = $row2;
    //                $exists = true;
    //            }
    //            if ($exists) {
    //                $data[$i]['Per_Date'] = array('Date' => $row['Payroll_Paid_Date'],
    //                    'NAPSA' => $total_napsa_per_date,
    //                    'PAYE' => $total_paye_per_date
    //                );
    //                $total_napsa += $total_napsa_per_date;
    //                $total_paye += $total_paye_per_date;
    //                $i++;
    //            }
    //        }
    //        $data['Total_NAPSA'] = $total_napsa;
    //        $data['Total_PAYE'] = $total_paye;
    //        return $data;
    //    }

    public function TotalPaidAbsence($year) {
        $data = array();
        for ($i = 1; $i < 13; $i++) {
            $query = $this->db->query('SELECT 
										p.Paid_Amount,
                                        p.Currency_ID
									FROM 
										tbl_hrm_paid_accrued p
                                    LEFT JOIN 
                                        tbl_hrm_employee e
                                    ON 
                                        e.Employee_ID = p.Employee_ID
                                    WHERE 
                                        MONTH(p.Paid_Date) = "' . $i . '"
                                    AND    
                                        YEAR(p.Paid_Date) = "' . $year . '"
                                 	AND
										e.Status_ID  = 1 
                                        ');
            $total = 0;
            while ($row = $this->db->assoc($query)) {
                $total += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Paid_Amount']);
            }
            $data[$i] = $total;
        }
        return $data;
    }

    public function viewTotalPaidAbsenceDetails($date) {
        $year = substr($date, 0, 4);
        $month = substr($date, 5, 2);
        $data = array();
        $query = $this->db->query('SELECT 
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                        b.department_name as Branch_Name,
                                        j.Job_Name,
                                        p.Paid_Amount,
                                        p.Paid_Date,
                                        c.Currency_Symbol
                                    FROM 
                                        tbl_hrm_paid_accrued p
                                    LEFT JOIN 
                                        tbl_hrm_employee e
                                    ON 
                                        p.Employee_ID = e.Employee_ID
                                    LEFT JOIN 
                                        tbl_department b
                                    ON 
                                        b.department_id = e.Branch_ID
                                    LEFT JOIN 
                                        tbl_hrm_paygrade g
                                    ON 
                                        g.Paygrade_ID = e.Paygrade_ID
                                    LEFT JOIN 
                                        tbl_hrm_jobs j
                                    ON 
                                        j.Job_ID = e.job_id
                                    LEFT JOIN 
                                        tbl_common_currency c
                                    ON 
                                        c.Currency_ID = p.Currency_ID
                                    WHERE 
                                        YEAR(p.Paid_Date) = "' . $year . '"
                                    AND 
                                        MONTH(p.Paid_Date) = "' . $month . '"
                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getWarningEmployee() {
        $data = array();
        $query = $this->db->query(' SELECT 
								        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
										d.Document_File_Name,
										d.Document_Name
									From 
										tbl_hrm_employee e
									LEFT JOIN
										 tbl_hrm_employee_document d
									ON 
										d.Employee_ID = e.Employee_ID
									LEFT JOIN 
										tbl_hrm_document_types t
									ON 
										t.Document_ID = d.Document_Type
									WHERE 
										d.Document_Type = 3
									');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    //    public function getPayrollPaymentAccounts() {
    //        $data = array();
    //        $query = $this->db->query(' SELECT 
    //								        *
    //                                    FROM 
    //                                        tbl_chart_of_accounts
    //                                    WHERE 
    //                                        LEFT(Account_Code,2) = 44 
    //                                    OR
    //                                        main_type="pay"
    //                                        OR 
    //                                       LEFT(Account_Code,2) = 42 
    //									');
    //        while ($row = $this->db->assoc($query)) {
    //            $data[] = $row;
    //        }
    //        return $data;
    //    }

    public function employeeForUserSearch($term = null, $page, $limit, $is_actual = 0) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND (Employee_FirstName LIKE ? or Employee_LastName LIKE ? ) ';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        if ($is_actual == 0) {
            $cond .= ' and Is_Hidden = 0 ';
        }
        $query = $this->db->prepare_Stmt('select 
                                    Employee_ID as id,
                                    concat(Employee_FirstName," ",Employee_LastName) as text
                                from 
                                    tbl_hrm_employee
                                    where Status_ID = 1 AND Employee_ID NOT IN  
                                    (SELECT Employee_ID FROM `tbl_common_users` WHERE Status_ID <> 0 and Employee_ID is not null) 
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function employeeSearch($term = null, $page, $limit, $is_actual = 0, $with_id = 0, $customer_id = null) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND (concat(e.Employee_FirstName," ",e.Employee_MiddleName," ",e.Employee_LastName)  LIKE ? or  e.Employee_Work_ID LIKE ? ) ';
            $data_filter[] = "%" . str_replace(' ', '%', trim($term)) . "%";
            $data_filter[] = "%" . $term . "%";
        }

        if ($is_actual == 0) {
            $cond .= ' and e.Is_Hidden = 0 ';
        }
        $cust_join = '';
//        $cust_col='';
        if (!is_null($customer_id)) {
            if (smc_session()['default_setting']['require_relate_cust_salesman'] == 0) {
                $cust_join.=' left join tbl_salesman_customers uc on uc.employee_id = e.Employee_ID  and uc.customer_id="' . $customer_id . '"';
//                $cond.=' and uc.customer_id="' . $customer_id . '" ';
            } else {
                $cust_join.='  join tbl_salesman_customers uc on uc.employee_id = e.Employee_ID and uc.customer_id="' . $customer_id . '"';
            }
//            $cust_col.=' ,uc.customer_id ';
        }
        if (!empty($this->info['emp_ids'])) {
            $cond.=' and e.Employee_ID in (' . $this->info['emp_ids'] . ') ';
        }
        if (!empty($this->info['not_linked'])) {
            $data['data'][] = array('id' => 'no_link', 'text' => CommonLanguage::getMessage('is_not_linked'));
        }
        $emp_arr = array();
        $query = $this->db->prepare_Stmt('select 
                                    e.Employee_ID as id,
                                    concat_ws("",' . ($with_id ? 'e.Employee_Work_ID," - ",' : '') . 'e.Employee_FirstName," ",e.Employee_MiddleName," ",e.Employee_LastName) as text
                                        ' . (!is_null($customer_id) ? ',uc.customer_id' : '') . '
                                from 
                                    tbl_hrm_employee e ' . $cust_join . '
                                where Status_ID = 1 
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            if (isset($row['customer_id'])) {
                $emp_arr[] = $row;
            }
            $data['data'][] = $row;
        }
//        echo"<pre>";die(print_r($emp_arr));
        if (!empty($emp_arr))
            $data['data'] = $emp_arr;
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function employeeDependantSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND (Employee_FirstName LIKE ? or Employee_LastName LIKE ? or  Employee_Work_ID LIKE ? )';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('SELECT 
                                        Employee_ID as id,
        				concat_ws("",Employee_Work_ID," - ",Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) AS text
                                   FROM 
                                        tbl_hrm_employee
                                   WHERE
                                        Merital_Status_ID <> 1
                                   AND 
                                        Status_ID = 1
                        ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function employeeDependantForChildSearch($term = null, $page, $limit, $is_actual = 0) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND (Employee_FirstName LIKE ? or Employee_LastName LIKE ? )';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        if ($is_actual == 0) {
            $cond .= ' and Is_Hidden = 0 ';
        }
        $query = $this->db->prepare_Stmt('SELECT 
                                        Employee_ID as id,
        						        CONCAT(Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) AS text
                                   FROM 
                                        tbl_hrm_employee
                                   join
                                        tbl_hrm_dependants using (Employee_ID)
                                   WHERE
                                        Merital_Status_ID <> 1
                                   AND 
                                        Status_ID = 1
                                   AND  
                                        Number_Of_Children > 0
                        ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function emergencySearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' where Employee_Relationship_Name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    Employee_Relationship_ID as id,
                                    Employee_Relationship_Name as text
                                from 
                                    tbl_hrm_employee_relationship
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function deleteEmergencyContact($id) {
        $this->db->prepare_Stmt('DELETE FROM tbl_hrm_emergency_contact WHERE EmergencyContact_ID = ?', [&$id]);
        $error = $this->db->error();
        return $error;
    }

    public function viewEmergencyContactByID($id) {
        $data = array();
        $query = $this->db->query('SELECT 
                                       *
                                    from
                                        employee_emergency_contact
                                    where 
                                        EmergencyContact_ID = "' . $id . '"');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function updateEmergencyContact($id) {
        $data = [&$this->info['EmergencyContact_FirstName'],
            &$this->info['EmergencyContact_MiddleName'],
            &$this->info['EmergencyContact_LastName'],
            &$this->info['Employee_Relationship_ID'],
            &$this->info['EmergencyContact_HomePhone'],
            &$this->info['EmergencyContact_MobilePhone'],
            &$this->info['EmergencyContact_WorkPhone'],
            &$this->info['Employee_ID'],
            &$this->info[0], &$id];
        $this->db->prepare_Stmt('Update  tbl_hrm_emergency_contact set  EmergencyContact_FirstName = ?,
                                                                        EmergencyContact_MiddleName = ?,
                                                                        EmergencyContact_LastName = ?,
                                                                        Employee_Relationship_ID = ?,
                                                                        EmergencyContact_HomePhone = ?,
                                                                        EmergencyContact_MobilePhone = ?,
                                                                        EmergencyContact_WorkPhone = ?,
                                                                        Employee_ID = ?,
                                                                        Logged_ID = ?
                                    where EmergencyContact_ID = ?                                    
                                                                        ', $data);
        $error = $this->db->error();
        return $error;
    }

    public function addEmployeeDeductionNote() {
        /*
         * if Employee_IS_Deduction == 0 => Bonus , 1 == Deduction
         * status_id == 0 => In review , 1 ==> Done, 2 ==> Rejected 
         */
        $mydate = date("Y-m-d H:i:s");
        $data = [&$this->info['Employee_ID'], &$this->info['Employee_Name'], &$this->info['TimeDuration'], &$this->info['Employee_IS_Deduction'], &$this->info['Employee_Note'], &$this->info[0], &$mydate, &$this->info['is_actual']];
        $this->db->prepare_Stmt('INSERT INTO tbl_employee_dedcution_note (Employee_ID,Employee_Name,TimeDuration, Employee_IS_Deduction,Employee_Note,Logged_ID,date_time,is_actual,status_id) VALUES (?,?,?,?,?,?,?,?,0)', $data);
        $error = $this->db->error();
        //        echo"<pre>"
        return $error;
    }

    public function rejectEmployeeDeductionNote() {
        $this->db->prepare_Stmt('update tbl_employee_dedcution_note set status_id = ?,Logged_ID = ? where Entry_ID = ?', [&$this->info['status'], &$this->info[0], &$this->info['id']]);
        $error = $this->db->error();
        return $error;
    }

    public function deleteEmployeeDeductionNote() {
        $this->db->prepare_Stmt('delete from tbl_employee_dedcution_note  where Entry_ID = ?', [&$this->info['id']]);
        $error = $this->db->error();
        return $error;
    }

    public function getEmployeeDeductionNote($id) {
        $data = array();
        $query = $this->db->query('SELECT 
                                       e.*,
                                       p.Currency_Name,
                                       p.Currency_Code,
                                       p.Currency_ID,
                                       p.Paygrade_Value
                                    from
                                        tbl_employee_dedcution_note e
                                     left join 
                                            view_employees p
                                      using (Employee_ID)      
                                    where 
                                        Entry_ID = "' . smc_escape($id) . '"');
        while ($row = $this->db->assoc($query)) {

            $row['Amount'] = (($row['Paygrade_Value'] / 30) / 8) * ($row['TimeDuration'] / 60);


            $data = $row;
        }
        return $data;
    }

    public function viewPayrollDetails($employee_id, $year, $month) {
        $data = array();

        $query = $this->db->query('SELECT p.*,    
                                e.Employee_Name,
                                e.Job_Name AS Employee_Position,
                                if(e.basic_salary = 0,e.Paygrade_Value,e.basic_salary) AS Basic_Salary,
                                if(e.basic_salary = 0,e.Currency_Symbol,e.basic_currency_symbol) as Currency_Symbol,
                                if(e.basic_salary = 0,e.Currency_ID,e.basic_salary_currency) AS Paygrade_Currency
                                    FROM 
                                         `tbl_hrm_payroll` p
                                    left join
                                            view_employees e
                                    on 
                                        e.Employee_ID = p.Employee_ID
                                    where 
                                         year(p.Payroll_Paid_Date)  = "' . $year . '" 
                                    and  month(p.Payroll_Paid_Date) = "' . $month . '"
                                    and  p.Employee_ID = "' . $employee_id . '"    
            ');

        while ($row = $this->db->assoc($query)) {
            $row['Basic_Salary'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($row['Paygrade_Currency'], "84", $row['Basic_Salary']);
            $row['other_prices'] = json_decode($row['other_prices'], true);
            $data[] = $row;
        }
        return $data;
    }

    public function viewEmployeeDependantChild($id) {
        $data = array();
        $query = $this->db->prepare_Stmt('Select d.*,e.Wife_Works as emp_wife_works from dependant_employee d left join tbl_hrm_employee e using (Employee_ID) where d.Employee_ID = ?', [&$id]);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function searchSchoolType($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' where school_type ? ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    school_id as id,
                                    school_type as text
                                from 
                                    tbl_school_child
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function getSchoolType() {
        $data = array();
        $query = $this->db->query('Select * from tbl_school_child');

        while ($row = $this->db->assoc($query))
            $data[] = $row;

        return $data;
    }

    public function getNationality() {
        $data = array();
        $query = $this->db->query('Select * from tbl_hrm_nationalities');

        while ($row = $this->db->assoc($query))
            $data[] = $row;

        return $data;
    }

    public function getEmployeeLcoation() {
        $data = array();
        $query = $this->db->query('Select * from tbl_employee_location');

        while ($row = $this->db->assoc($query))
            $data[] = $row;

        return $data;
    }

    public function getEmployeeChildSchoolTotal($emp_id, $entry_id) {
        $old_row = array();
        $old_children_arr = array();
        $checked_child_ids = array();
        if (!empty($entry_id)) {
            $query = $this->db->prepare_Stmt('select * from tbl_employee_child where Entry_id = ? ', [&$entry_id]);
            $old_row = $this->db->assoc($query);
            $old_children_arr = json_decode($old_row['old_data_child'], true);
            foreach ($old_children_arr as $old_child_row)
                if ($old_child_row['child_check'] == '1')
                    $checked_child_ids[$old_child_row['Child_ID']] = $old_child_row['Child_ID'];
        }
        //
        if (empty($entry_id) || date('Y') == date('Y', strtotime($old_row['Amount_Date']))) {
            $children_arr = array();
            $query = $this->db->prepare_Stmt('select * from dependant_employee where Employee_ID = ? order by school_value desc', [&$emp_id]);
            while ($row = $this->db->assoc($query))
                $children_arr[] = $row;
        } else
            $children_arr = $old_children_arr;
        //
        $total = 0;
        $num_childreen = smc_session()['default_school_max_no_childreen'];
        $value_school_grant = smc_session()['default_school_max_value_grant'];
        $data_arr = array();
        $calc_child_no = 0;
        foreach ($children_arr as $row) {
            $dateOfBirth = $row['child_dob'];
            $today = date("Y-m-d");
            $diff = date_diff(date_create($dateOfBirth), date_create($today));
            $row['child_age'] = $diff->format('%y');
            $row['child_is_calculated'] = false;
            $row['child_is_accepted'] = false;
            if ($row['child_age'] >= 4 && $row['child_age'] <= 25 && $row['school_type'] != 0) {
                $row['child_is_accepted'] = true;
                if ($calc_child_no < $num_childreen && $total < $value_school_grant) {
                    $calc_child_no++;
                    $row['child_is_calculated'] = true;
                    $total += $row['school_value'];
                }
            }
            $row['child_is_checked'] = false;
            if (isset($checked_child_ids[$row['Child_ID']]))
                $row['child_is_checked'] = true;
            $total = min($total, $value_school_grant);
            $data_arr[] = $row;
        }
        return array('children' => $data_arr, 'total' => $total, 'old_total' => (empty($old_row) ? 0 : $old_row['Employee_Amount']));
    }

    public function CheckEmployeeChild() {

        $emp_id = $this->info['Employee_ID'];
        $year = date('Y', strtotime($this->info['Amount_Date']));
        $cond = empty($this->info['Entry_ID']) ? "" : " and Entry_id <> '" . smc_escape($this->info['Entry_ID']) . "' ";
        $query = $this->db->prepare_Stmt('select Employee_ID from tbl_employee_child where Employee_ID = ? and YEAR(Amount_Date) = ? ' . $cond, [&$emp_id, &$year]);

        $row = $this->db->rowsno($query);

        if ($row > 0)
            return false;
        return true;
    }

    public function addMedicalType() {
        $this->db->query('START TRANSACTION');
        foreach ($this->info['Medical_Type'] as $item) {
            $data = [&$item['medical_type_name'], &$item['medical_type_percent']];
            $this->db->prepare_Stmt('INSERT INTO tbl_mdcl_type ( mdcl_type_name,mdcl_type_percent) VALUES(?,?)', $data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function deleteMedicalType($id) {
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('Delete from  tbl_mdcl_type where mdcl_type_id = ? ', [&$id]);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function viewMedicalType($id) {

        $query = $this->db->prepare_Stmt('select * from tbl_mdcl_type where mdcl_type_id = ? ', [&$id]);

        return $this->db->assoc($query);
    }

    public function updateMedicalType() {
        $this->db->query('START TRANSACTION');
        $data = [&$this->info['Medical_Type'][0]['medical_type_name'],
            &$this->info['Medical_Type'][0]['medical_type_percent'], &$this->info['Medical_Type_ID']];
        $this->db->prepare_Stmt('UPDATE 
                                        tbl_mdcl_type 
                                    SET
                                       mdcl_type_name = ?,
                                        mdcl_type_percent = ?
                                    WHERE 
                                        mdcl_type_id = ?
                    ', $data);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function viewAllMedicalType() {
        $data = array();
        $query = $this->db->query('select * from tbl_mdcl_type');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function addMedicalPlace() {
        $this->db->query('START TRANSACTION');

        $query = $this->db->prepare_Stmt('Insert Into tbl_mdcl_place (mdcl_place_name) values (?)', [&$this->info['mdcl_place_name']]);


        $mdcl_palace_id = $this->db->id();

        foreach ($this->info['mdcl_place_details'] as $item) {
            $item_data = [&$mdcl_palace_id,
                &$item['mdcl_type_id'],
                &$item['mdcl_place_details_percent']];


            $query_details = $this->db->prepare_Stmt('Insert into tbl_mdcl_place_details (mdcl_place_id,mdcl_type_id,mdcl_place_details_percent) values (?,?,?)', $item_data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function updateMedicalPlace() {

        $this->db->query('START TRANSACTION');
        $mdcl_palace_id = $this->info['mdcl_place_id'];
        $query = $this->db->prepare_Stmt('update tbl_mdcl_place set mdcl_place_name = ? where mdcl_place_id = ?', [&$this->info['mdcl_place_name'], &$mdcl_palace_id]);
        $this->db->prepare_Stmt('Delete from  tbl_mdcl_place_details where mdcl_place_id = ? ', [&$mdcl_palace_id]);
        foreach ($this->info['mdcl_place_details'] as $item) {
            $item_data = [&$mdcl_palace_id,
                &$item['mdcl_type_id'],
                &$item['mdcl_place_details_percent']];
            $query_details = $this->db->prepare_Stmt('Insert into tbl_mdcl_place_details (mdcl_place_id,mdcl_type_id,mdcl_place_details_percent) values (?,?,?)', $item_data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function deleteMedicalLocation($id) {
        $this->db->query('START TRANSACTION');

        $this->db->prepare_Stmt('Delete from tbl_mdcl_place where mdcl_place_id = ? ', [&$id]);
        $this->db->prepare_Stmt('Delete from tbl_mdcl_place_details where mdcl_place_id = ? ', [&$id]);



        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function viewMedicalPlace($id = '') {
        $data = array();
        $cond = '';
        $id = !empty($id) ? $id : '';
        $cond .= ' where mdcl_place_id = "' . smc_escape($id) . '"';
        //
        $query = $this->db->query('select * from tbl_mdcl_place ' . $cond);
        $data[0] = $this->db->assoc($query);
        //
        $query = $this->db->query('select * from tbl_mdcl_place_details ' . $cond);
        while ($row = $this->db->assoc($query)) {
            $data['place_dtls'][$row['mdcl_type_id']] = $row;
        }
        return $data;
    }

    public function medicalPlaceSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' where mdcl_place_name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    mdcl_place_id as id,
                                    mdcl_place_name as text
                                from 
                                    tbl_mdcl_place
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function locationSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        if (!is_null($term)) {
            $cond .= ' where Location_Name LIKE "%' . $term . '%" ';
        }
        $query = $this->db->query('select 
                                    Location_ID as id,
                                    Location_Name as text
                                from 
                                    tbl_employee_location
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1));
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function medicalEmployee() {
        $this->db->query('START TRANSACTION');
        $total = 0;
        $total_iswa = 0;
        $total_emp = 0;
        $array_mdcl_data = $this->info['mdcl_employee_data'];

        //        die(print_r($this->info));
        foreach ($array_mdcl_data as $item) {
            $query = $this->db->query('select mdcl_place_details_percent from tbl_mdcl_place_details where mdcl_place_id = "' . smc_escape($item['mdcl_place_id']) . '" and mdcl_type_id = "' . smc_escape($item['mdcl_type_id']) . '"');
            $row = $this->db->assoc($query);
            $total_iswa += $item['mdcl_emp_dtl_total'] * $row['mdcl_place_details_percent'] / 100;
            $total += $item['mdcl_emp_dtl_total'];
        }
        $total_emp += $total - $total_iswa;
        $data_item = [
            &$this->info['Employee_ID'],
            &$this->info['mdcl_employee_date'],
            &$this->info['mdcl_employee_note'],
            &$total_iswa,
            &$total_emp,
            &$total
        ];
        if (empty($this->info['mdcl_employee_id'])) {

            $this->db->prepare_Stmt('INSERT INTO `tbl_mdcl_employee`(Employee_ID,mdcl_employee_date,mdcl_employee_note,mdcl_total_iswa,mdcl_total_employee,mdcl_total_final) 
                                                 VALUES (?,?,?,?,?,?) ', $data_item);
            $mdcl_employee_id = $this->db->id();
        } else {

            $mdcl_employee_id = $this->info['mdcl_employee_id'];
            $data_item[] = &$mdcl_employee_id;
            $this->db->prepare_Stmt('update tbl_mdcl_employee set Employee_ID = ? , mdcl_employee_date = ?, mdcl_employee_note = ?,mdcl_total_iswa = ?,mdcl_total_employee= ?,mdcl_total_final = ? where mdcl_employee_id = ?', $data_item);

            $this->db->prepare_Stmt('Delete from tbl_mdcl_employee_details where mdcl_employee_id = ? ', [&$mdcl_employee_id]);
        }
        foreach ($array_mdcl_data as $item) {
            $query1 = $this->db->query('select mdcl_place_details_percent from tbl_mdcl_place_details where mdcl_place_id = "' . smc_escape($item['mdcl_place_id']) . '" and mdcl_type_id = "' . smc_escape($item['mdcl_type_id']) . '"');
            $row = $this->db->assoc($query1);
            $item_data = [
                &$mdcl_employee_id,
                &$item['mdcl_type_id'],
                &$item['mdcl_place_id'],
                &$item['mdcl_emp_dtl_total'],
                &$row['mdcl_place_details_percent']
            ];
            $this->db->prepare_Stmt('Insert into tbl_mdcl_employee_details (mdcl_employee_id,mdcl_type_id,mdcl_place_id,mdcl_emp_dtl_total,mdcl_emp_dtl_iswa_percent) values (?,?,?,?,?)', $item_data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function deleteMedicalEmployee($id) {
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('Delete from tbl_mdcl_employee_details where mdcl_employee_id = ? ', [&$id]);
        $this->db->prepare_Stmt('Delete from tbl_mdcl_employee where mdcl_employee_id = ? ', [&$id]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function viewMedicalEmployee($id) {
        $data = array();
        $query = $this->db->prepare_Stmt('Select * from mdcl_employee where mdcl_employee_id = ?', [&$id]);
        $data[0] = $this->db->assoc($query);

        $query = $this->db->prepare_Stmt('Select * from mdcl_employee where mdcl_employee_id = ?', [&$id]);

        while ($row = $this->db->assoc($query)) {
            $data['mdcl_emp_dtls'][$row['mdcl_type_id']] = $row;
        }

        return $data;
    }

    public function getWifeWork($emp_id) {

        $query = $this->db->prepare_Stmt('select Wife_Works from tbl_hrm_employee where Employee_ID = ?', [&$emp_id]);

        $row = $this->db->assoc($query)['Wife_Works'];

        return $row;
    }

    public function printExcelSheetPerEmpChild($emp_id, $entry_id) {
        require dirname(__FILE__) . '/../PHPExcel/PHPExcel/IOFactory.php';

        $file_path = SYSTEM_PATH . "child.xlsx";
        $excel = PHPExcel_IOFactory::load($file_path);
        $excel->setActiveSheetIndex(0);
        $data = self::getEmployeeChildSchoolTotal($emp_id, $entry_id);
        $query = $this->db->prepare_Stmt('Select Employee_Name from view_employees where Employee_ID = ?', [&$emp_id]);
        $row = $this->db->assoc($query);
        //        $excel->getActiveSheet()->setRightToLeft(true);
        $excel->getActiveSheet()->setCellValue('B3', $row['Employee_Name']);
        $i = 18;

        //        $excel->getActiveSheet()->getStyle('A5:Z5')->getAlignment()
        //                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        //        $excel->getActiveSheet()->getStyle('A6:Z6')->getAlignment()
        //                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        //        $excel->getActiveSheet()->getStyle('A7:Z7')->getAlignment()
        //                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_GENERAL);


        for ($j = 0; $j < count($data['children']); $j++) {

            if ($data['children'][$j]['child_is_checked'] == 1) {

                $excel->getActiveSheet()->setCellValue('A' . ($i), $data['children'][$j]['child_name'])
                        ->setCellValue('B' . ($i), $data['children'][$j]['child_school'])
                        ->setCellValue('C' . ($i), $data['children'][$j]['school_type_name'])
                        ->setCellValue('D' . ($i), $data['children'][$j]['child_age'])
                        ->setCellValue('E' . ($i), $data['children'][$j]['child_class'])
                        ->setCellValue('F' . ($i), $data['children'][$j]['school_value']);
                if ($i <= 21)
                    $i++;
            }
        }


        $excel->getActiveSheet()->setCellValue('C22', number_format($data['old_total'], 2));
        //        $objWriter = new PHPExcel_Writer_Excel2007($excel);
        //        $objWriter->save($filename);

        $filename = " منحة مدرسية_" . date('d-m-Y_H-i-s') . ".xlsx";
        $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');

        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename=' . $filename);
        $objWriter->save("php://output");
    }

    public function addEmployeeAlert($entry_id = null, $doc_id = null) {
        $fid = '';
        $this->db->query('START TRANSACTION');
        $logged_id = Session::getUserID();
        $FileName = '';

        if (!empty($doc_id) && $doc_id != 0) {
            $query = $this->db->prepare_Stmt('select employee_alert_document from tbl_employee_alert where doc_id = ? ', [&$doc_id]);
            $row = $this->db->assoc($query);
            $FileName = $row['employee_alert_document'];
        }

        if (!empty($entry_id)) {
            self::deleteEmpAlert($entry_id, $doc_id);
        }

        if ($_FILES['Document_File_Name']['name'] !== '') {
            $Extension = pathinfo($_FILES['Document_File_Name']['name'], PATHINFO_EXTENSION);
            //        $FileName = $this->info['Document_Type'] . ' _ ' . $this->info['Employee_ID'] . '.' . $Extension;
            $today = Date::getToday();
            $file_format = "Y-m-d-H-i";
            $time = date($file_format);
            $FileName = ('9_ ' . $time . '__' . $this->info['Employee_ID'] . '.' . $Extension );

            $data1 = [&$this->info['Employee_ID'],
                &$FileName,
                &$today,
                &$FileName,
                &$logged_id, $this->info['is_actual']];
            $uploaddir = upload_path . 'Documents/';
            $RET = self::SaveUploadedPhoto("Document_File_Name", $uploaddir . $FileName);
            if (file_exists($uploaddir . $FileName)) {
                $this->db->prepare_Stmt('INSERT INTO
                                        tbl_hrm_employee_document(
				                            Employee_ID,
				                            Document_Type,
				                            Document_Name,
				                            Document_Date,
				                            Document_File_Name,
				                            Logged_ID,is_actual
				                        )
                                 VALUES   (?,9,?,?,?,?,?) ', $data1);
                $fid = $this->db->id();
            }
        }


        $this->db->prepare_Stmt('INSERT INTO 
                                        tbl_employee_alert (
                                            employee_id,
                                            employee_name,
                                            employee_alert_date,
                                            employee_alert_type,
                                            employee_alert_note,
                                            employee_alert_document,
                                            doc_id

                                        )
                                        VALUES(?,?,?,?,?,?,?)
                ', [&$this->info['Employee_ID'], &$this->info['Employee_Name'], &$this->info['employee_alert_date'], &$this->info['employee_alert_type'], &$this->info['employee_alert_note'], &$FileName, &$fid]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function deleteEmpAlert($entry_id, $doc_id) {
        $this->db->prepare_Stmt('DELETE FROM `tbl_employee_alert` WHERE `entry_id` = ?', [&$entry_id]);
        if ($doc_id != 0)
            $this->db->prepare_Stmt('DELETE FROM `tbl_hrm_employee_document` WHERE `Entry_ID` = ?', [&$doc_id]);
        $error = $this->db->error();
        return $error;
    }

    //    public function updateEmployeeAlert($entry_id, $doc_id) {
    //
    //        $delete = self::deleteEmpAlert($entry_id, $doc_id);
    //        $insert = self::addEmployeeAlert($doc_id);
    //    }

    public function getEmpAlert($id) {
        $data = array();
        $query = $this->db->query('select * FROM `tbl_employee_alert` WHERE `entry_id` = "' . $id . '"');

        $row = $this->db->assoc($query);
        if (!empty($row))
            $data = $row;
        return $data;
    }

    public function getEmployeeAlertCount($emp_id) {

        $query = $this->db->prepare_Stmt('Select COUNT(*) as Allustion from tbl_employee_alert where employee_id = ?  and employee_alert_type = 1', [&$emp_id]);
        $row_allu = $this->db->assoc($query)['Allustion'];

        $query_war = $this->db->prepare_Stmt('Select COUNT(*) as warning from tbl_employee_alert where employee_id = ?  and employee_alert_type = 0', [&$emp_id]);
        $row_war = $this->db->assoc($query_war)['warning'];


        return array('allus' => $row_allu, 'war' => $row_war);
    }

    public function getEmployeeJobFile($emp_id) {
        $query = $this->db->prepare_Stmt('Select *  from tbl_hrm_employee_document where Document_Type = 0 and Employee_ID = ?', [&$emp_id]);
        $row = $this->db->assoc($query);
        return $row;
    }

    public function deleteJobFilePerEmp($entry_id) {
        $query = $this->db->prepare_Stmt('select Document_File_Name from tbl_hrm_employee_document where Entry_ID = ?', [&$entry_id]);
        $row = $this->db->assoc($query)['Document_File_Name'];
        //        $uploaddir = dirname(__FILE__) . '/../../uploads/Documents/';
        //        unlink($uploaddir.$row);
        $this->db->prepare_Stmt('Delete from tbl_hrm_employee_document where Entry_ID = ?', [&$entry_id]);
        $error = $this->db->error();
        return $error;
    }

    public function addEmployeeTrnsfDay() {
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('INSERT INTO 
                                        tbl_employee_transfer_day (
                                            employee_id,
                                            employee_name,
                                            transfer_day_number,
                                            transfer_date,
                                            transfer_day_note

                                        )
                                        VALUES(?,?,?,?,?)
                ', [&$this->info['Employee_ID'], &$this->info['Employee_Name'], &$this->info['transfer_day_number'], &$this->info['transfer_date'], &$this->info['transfer_day_note']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function updateEmployeeTrnsfDay($entry_id) {
        $this->db->query('START TRANSACTION');
        $data = [
            &$this->info['Employee_ID'],
            &$this->info['Employee_Name'],
            &$this->info['transfer_day_number'],
            &$this->info['transfer_date'],
            &$this->info['transfer_day_note'],
            &$entry_id
        ];


        $remaining_days = WorkingTimes::getAvailableTrsDays($this->info['Employee_ID']);
        $query = $this->db->prepare_Stmt('select transfer_day_number from tbl_employee_transfer_day where entry_id = ?', [&$entry_id]);
        $row = $this->db->assoc($query);

        $is_update = $remaining_days['remain'] - $row['transfer_day_number'] + $this->info['transfer_day_number'];

        if ($is_update <= 0) {
            $error = $this->db->error();
            $error[0] = CommonLanguage::getMessage('not_allowed_edit');
            ;
            return $error;
        }

        $this->db->prepare_Stmt('UPDATE `tbl_employee_transfer_day` SET 
                `employee_id`= ?,
                `employee_name`= ?,
                `transfer_day_number`= ?,
                `transfer_date`= ?,
                `transfer_day_note`= ? where entry_id = ?', $data);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function getEmpTransferDay($entry_id) {
        $query = $this->db->prepare_Stmt('Select *  from tbl_employee_transfer_day where entry_id = ?', [&$entry_id]);
        $row = $this->db->assoc($query);
        return $row;
    }

    public function deleteEmployeeTrnsfDay($entry_id) {

        $query = $this->db->prepare_Stmt('select employee_id,transfer_day_number from tbl_employee_transfer_day where entry_id = ?', [&$entry_id]);
        $row = $this->db->assoc($query);
        $remaining_days = WorkingTimes::getAvailableTrsDays($row['employee_id']);


        $is_update = $remaining_days['remain'] - $row['transfer_day_number'];

        if ($is_update < 0) {
            $error = $this->db->error();
            $error[0] = CommonLanguage::getMessage('not_allowed_delete');
            return $error;
        }
        $this->db->prepare_Stmt('delete from tbl_employee_transfer_day where entry_id = ?', [&$entry_id]);
        $error = $this->db->error();
        return $error;
    }

    public function getCountEmpTransferDay($emp_id) {

        $query = $this->db->prepare_Stmt('Select sum(transfer_day_number) as trs_dys from tbl_employee_transfer_day where employee_id = ?', [&$emp_id]);
        $row = $this->db->assoc($query)['trs_dys'];
        return $row;
    }

    public function getCountAbsenceDysPerEmp($emp_id) {
        $day = 0;
        $query_absence = $this->db->query('select (DATEDIFF(Absence_To_Date,Absence_From_Date)+1) as day_absence
            from
                                            tbl_hrm_absence 
                                   where
                                          Employee_ID = "' . $emp_id . '"
                                   and 
                                   is_approved = 1 ');

        while ($row = $this->db->assoc($query_absence)) {

            $day += $row['day_absence'];
        }

        return $day;
    }

    public function addVolunteerTeam() {
        $date_time = date('Y-m-d h:i:s');
        $Is_Edit = $this->info['Is_Edit'];
        $data = [
            &$this->info['volunteer_team_name'],
            &$date_time,
            &$this->info[0]
        ];
        if ($Is_Edit == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_volunteer`(`volunteer_team_name`, `date_time`, `logging_id`) VALUES (?,?,?)', $data);
        } else {
            $data[] = &$this->info['volunteer_team_id'];
            $this->db->prepare_Stmt('update `tbl_volunteer` set `volunteer_team_name` = ? , `date_time` = ? , `logging_id` = ? where volunteer_team_id = ? ', $data);
        }
        $error = $this->db->error();
        return $error;
    }

    public function addVolunteerSkills() {
        $date_time = date('Y-m-d h:i:s');
        $Is_Edit = $this->info['Is_Edit'];
        $data = [
            &$this->info['skills_name'],
            &$date_time,
            &$this->info[0]
        ];
        if ($Is_Edit == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_hrm_skills`(`skills_name`, `date_time`, `logging_id`) VALUES (?,?,?)', $data);
        } else {
            $data[] = &$this->info['skills_id'];
            $this->db->prepare_Stmt('update `tbl_hrm_skills` set `skills_name` = ? , `date_time` = ? , `logging_id` = ? where skills_id = ? ', $data);
        }
        $error = $this->db->error();
        return $error;
    }

    public function addVolunteerNotice() {
        $date_time = date('Y-m-d h:i:s');
        $Is_Edit = $this->info['Is_Edit'];
        $data = [
            &$this->info['notice_name'],
            &$date_time,
            &$this->info[0]
        ];
        if ($Is_Edit == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_volunteer_notice`(`notice_name`, `date_time`, `logging_id`) VALUES (?,?,?)', $data);
        } else {
            $data[] = &$this->info['notice_id'];
            $this->db->prepare_Stmt('update `tbl_volunteer_notice` set `notice_name` = ? , `date_time` = ? , `logging_id` = ? where notice_id = ? ', $data);
        }
        $error = $this->db->error();
        return $error;
    }

    public function deleteVolunteer($volunteer_id) {

        $this->db->prepare_Stmt('delete from `tbl_hrm_volunteer` where volunteer_id = ? ', [&$volunteer_id]);
        $error = $this->db->error();

        return $error;
    }

    public function deleteVolunteerTeam($volunteer_id) {
        $error = array();
        $query = $this->db->prepare_Stmt("SELECT *  FROM `tbl_hrm_volunteer` WHERE concat(',',`volunteer_team_id`,',') like '%," . intval($volunteer_id) . ",%' limit 1");
        $row = $this->db->rowsno($query);
        if ($row != 0) {
            $error[] = CommonLanguage::getMessage('child_row');
        } else {
            $this->db->prepare_Stmt('delete from `tbl_volunteer` where volunteer_team_id = ? ', [&$volunteer_id]);
            $error = $this->db->error();
        }
        return $error;
    }

    public function deleteVolunteerSkills($skills_id) {
        $error = array();
        $query = $this->db->prepare_Stmt("SELECT *  FROM `tbl_hrm_volunteer` WHERE concat(',',`skills_id`,',') like '%," . intval($skills_id) . ",%' limit 1");
        $row = $this->db->rowsno($query);
        if ($row != 0) {
            $error[] = CommonLanguage::getMessage('child_row');
        } else {
            $this->db->prepare_Stmt('delete from `tbl_hrm_skills` where skills_id = ? ', [&$skills_id]);
            $error = $this->db->error();
        }
        return $error;
    }

    public function deleteVolunteerNotice($notice_id) {
        $error = array();
        $query = $this->db->prepare_Stmt("SELECT *  FROM `tbl_hrm_volunteer` WHERE concat(',',`volunteer_notice_id`,',') like '%," . intval($notice_id) . ",%' limit 1");
        $row = $this->db->rowsno($query);
        if ($row != 0) {
            $error[] = CommonLanguage::getMessage('child_row');
        } else {
            $this->db->prepare_Stmt('delete from `tbl_volunteer_notice` where notice_id = ? ', [&$notice_id]);
            $error = $this->db->error();
        }
        return $error;
    }

    public function createVolunteer() {
        $this->db->query('START TRANSACTION');
        $email = !empty($this->info['Volunteer_Email']) ? $this->info['Volunteer_Email'] : null;
        $work_id_number = $this->info['Volunteer_Work_ID'];
        $data = [&$this->info['Volunteer_FirstName'],
            &$this->info['Volunteer_MiddleName'],
            &$this->info['Volunteer_LastName'],
            &$this->info['Volunteer_Gender'],
            &$this->info['Volunteer_DOB'],
            &$this->info['Volunteer_RecordNumber'],
            &$this->info['Volunteer_BloodGroup'],
            &$this->info['Volunteer_HomePhone'],
            &$this->info['Volunteer_MobilePhone'],
            &$work_id_number,
            &$email,
            &$this->info['Merital_Status_ID'],
            &$this->info['Volunteer_StartDate'],
            &$this->info['Volunteer_StreetAddress1'],
            &$this->info['Volunteer_City'],
            &$this->info['Volunteer_Country'],
            &$this->info[0],
            &$this->info['Volunteer_Nationality'],
            &$this->info['Volunteer_Major'],
            &$this->info['Volunteer_Education'],
            &$this->info['Volunteer_Problem'],
            &$this->info['skills_id'],
            &$this->info['volunteer_notice_id'],
            &$this->info['volunteer_team_id'],
            &$this->info['vlounteer_day_left'],
            &$this->info['working_hours']
        ];


        $this->db->prepare_Stmt("INSERT INTO tbl_hrm_volunteer (
				Volunteer_FirstName,
				Volunteer_MiddleName,
				Volunteer_LastName,
				Volunteer_Gender,
				Volunteer_DOB,
				Volunteer_RecordNumber,
				Volunteer_BloodGroup,
				Volunteer_HomePhone,
				Volunteer_MobilePhone,
				Volunteer_Work_ID,
				Volunteer_Email,
				Merital_Status_ID,
				Volunteer_StartDate,
				Volunteer_StreetAddress1,
				Volunteer_City,
				Volunteer_Country,
                                logged_id,
                                Volunteer_Nationality,
                                Volunteer_Major,
                                Volunteer_Education,
                                Volunteer_Problem,
                                skills_id,
                                volunteer_notice_id,
                                volunteer_team_id,
                                vlounteer_day_left,
                                working_hours
			)
			VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", $data);



        $error = $this->db->error();
        if (isset($_FILES["image_volunteer_File"]) && empty($error)) {
            $id = self::getLastEmployeeID();
            $uploaddir = upload_path . 'volunteer/';
            $this->SaveUploadedPhoto("image_volunteer_File", $uploaddir . $id . '.jpg');
        }

        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function getMaxVolunteerWrkID() {

        $query1 = $this->db->query('select max(Volunteer_Work_ID) as work_id from tbl_hrm_volunteer ');
        $row1 = $this->db->assoc($query1);

        if (empty($row1['work_id'])) {
            $work_id_number = '1';
        } else {

            $work_id_number = $row1['work_id'] + 1;
        }
        $work_id_number = 'V' . str_pad($work_id_number, 3, '0', STR_PAD_LEFT);

        return ($work_id_number);
    }

    public function viewVolunteer($volunteer_id) {
        $data = array();
        $query = $this->db->prepare_Stmt('select * from view_volunteers where volunteer_id = ?', [&$volunteer_id]);
        $row = $this->db->assoc($query);
        $data[] = $row;
        return $data;
    }

    public function viewVolunteerDetails($volunteer_id) {
        $data = array();
        $query = $this->db->prepare_Stmt('select * from view_volunteers  where volunteer_id = ? ', [&$volunteer_id]);
        $row = $this->db->assoc($query);
        //

        $query_pro = $this->db->prepare_Stmt('SELECT * FROM `tbl_pro_vlnt` join `tbl_pro_vlnt_dtls` using (pro_id) WHERE vlnt_id = ?', [&$volunteer_id]);
        while ($row_dtl = $this->db->assoc($query_pro)) {
            $row['details'][] = $row_dtl;
        }
        $row['skills_name_arr'] = array();
        if (!empty($row['skills_id'])) {
            $query_dtls = $this->db->prepare_Stmt('select skills_id, skills_name from tbl_hrm_skills  where skills_id in (' . $row['skills_id'] . ')');
            while ($row_skill = $this->db->assoc($query_dtls))
                $row['skills_name_arr'][] = $row_skill['skills_name'];
        }
        //
        $row['volunteer_notice_arr'] = array();
        if (!empty($row['volunteer_notice_id'])) {
            $query_dtls_not = $this->db->prepare_Stmt('select notice_id, notice_name from tbl_volunteer_notice  where notice_id in (' . $row['volunteer_notice_id'] . ')');
            while ($row_notice = $this->db->assoc($query_dtls_not))
                $row['volunteer_notice_arr'][] = $row_notice['notice_name'];
        }
        //
        $row['volunteer_team_arr'] = array();
        if (!empty($row['volunteer_team_id'])) {
            $query_dtls_team = $this->db->prepare_Stmt('select volunteer_team_id, volunteer_team_name from tbl_volunteer  where volunteer_team_id in (' . $row['volunteer_team_id'] . ')');
            while ($row_team = $this->db->assoc($query_dtls_team))
                $row['volunteer_team_arr'][] = $row_team['volunteer_team_name'];
        }
        $data[] = $row;
        return $data;
    }

    public function updateVolunteer() {
        $this->db->query('START TRANSACTION');
        $email = !empty($this->info['Volunteer_Email']) ? $this->info['Volunteer_Email'] : null;
        $work_id_number = $this->info['Volunteer_Work_ID'];
        $data = [&$this->info['Volunteer_FirstName'],
            &$this->info['Volunteer_MiddleName'],
            &$this->info['Volunteer_LastName'],
            &$this->info['Volunteer_Gender'],
            &$this->info['Volunteer_DOB'],
            &$this->info['Volunteer_RecordNumber'],
            &$this->info['Volunteer_BloodGroup'],
            &$this->info['Volunteer_HomePhone'],
            &$this->info['Volunteer_MobilePhone'],
            &$work_id_number,
            &$email,
            &$this->info['Merital_Status_ID'],
            &$this->info['Volunteer_StartDate'],
            &$this->info['Volunteer_StreetAddress1'],
            &$this->info['Volunteer_City'],
            &$this->info['Volunteer_Country'],
            &$this->info[0],
            &$this->info['Volunteer_Nationality'],
            &$this->info['Volunteer_Major'],
            &$this->info['Volunteer_Education'],
            &$this->info['Volunteer_Problem'],
            &$this->info['skills_id'],
            &$this->info['volunteer_notice_id'],
            &$this->info['volunteer_team_id'],
            &$this->info['vlounteer_day_left'],
            &$this->info['working_hours'],
            &$this->info['Volunteer_ID']
        ];
        $this->db->prepare_Stmt("UPDATE
                                        tbl_hrm_volunteer
                                    SET  
                                        Volunteer_FirstName = ?,
                                        Volunteer_MiddleName = ?,
                                        Volunteer_LastName = ?,
                                        Volunteer_Gender = ?,
                                        Volunteer_DOB = ?,
                                        Volunteer_RecordNumber = ?,
                                        Volunteer_BloodGroup = ?,
                                        Volunteer_HomePhone = ?,
                                        Volunteer_MobilePhone = ?,
                                        Volunteer_Work_ID = ?,
                                        Volunteer_Email = ?,
                                        Merital_Status_ID = ?,
                                        Volunteer_StartDate = ?,
                                        Volunteer_StreetAddress1 = ?,
                                        Volunteer_City = ?,
                                        Volunteer_Country = ?,
                                        Logged_ID = ?,
                                        Volunteer_Nationality = ?,
                                        Volunteer_Major = ?,
                                        Volunteer_Education = ?,
                                        Volunteer_Problem = ?,
                                        skills_id = ?,
                                        volunteer_notice_id = ?,
                                        volunteer_team_id = ?,
                                        vlounteer_day_left = ?,
                                        working_hours = ?


                                    WHERE
                                        volunteer_id = ?
                    ", $data);
        $error = $this->db->error();
        if (isset($_FILES["image_volunteer_File"]) && empty($error)) {
            $uploaddir = upload_path . 'volunteer/';
            $this->SaveUploadedPhoto("image_volunteer_File", $uploaddir . $this->info['Volunteer_ID'] . '.jpg');
        }
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function addProVlnt() {
        $this->db->query('START TRANSACTION');
        $date_time = date('Y-m-d h:i:s');

        $data = [
            &$this->info['pro_name'],
            &$this->info['pro_date'],
            &$this->info['pro_desc'],
            &$this->info['pro_loc'],
            &$this->info['pro_link'],
            &$date_time,
            &$this->info[0]
        ];
        $Is_Edit = $this->info['IsEdit'];

        if ($Is_Edit == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_pro_vlnt`(`pro_name`, `pro_date`, `pro_desc`, `pro_loc`, `pro_link`, `date_time`, `logged_id`) VALUES (?,?,?,?,?,?,?)', $data);
            $pro_id = $this->db->id();
        } else {
            $pro_id = $this->info['pro_id'];
            $data[] = &$pro_id;
            $this->db->prepare_Stmt('update  `tbl_pro_vlnt` set `pro_name` = ?, `pro_date`=?, `pro_desc` = ?, `pro_loc` = ?, `pro_link`=?, `date_time`=?, `logged_id` = ? where pro_id = ?', $data);
            $this->db->prepare_Stmt('delete from tbl_pro_vlnt_dtls where pro_id = ? ', [&$pro_id]);
        }
        if (isset($this->info['pro_dtl'])) {
            foreach ($this->info['pro_dtl'] as $item) {
                $item_data = [
                    &$pro_id,
                    &$item['vlnt_id'],
                    &$item['vlnt_hours']
                ];
                $this->db->prepare_Stmt('INSERT INTO `tbl_pro_vlnt_dtls`(`pro_id`, `vlnt_id`, `vlnt_hours`) VALUES (?,?,?)', $item_data);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function viewProVlnt($pro_id) {
        $query = $this->db->prepare_Stmt('select * from tbl_pro_vlnt where pro_id = ? ', [&$pro_id]);
        $row = $this->db->assoc($query);
        $query_dtl = $this->db->prepare_Stmt('select tbl_pro_vlnt_dtls.*,Volunteer_Name from tbl_pro_vlnt_dtls join view_volunteers on (vlnt_id = volunteer_id) where pro_id = ? ', [&$pro_id]);
        while ($row_dtl = $this->db->assoc($query_dtl)) {
            $row['details'][] = $row_dtl;
        }
        return $row;
    }

    public function deleteproList($pro_id) {
        $this->db->prepare_Stmt('delete from `tbl_pro_vlnt` where pro_id = ? ', [&$pro_id]);
        $error = $this->db->error();
        return $error;
    }

    public function submitPieceService() {
        $id = $this->info['piece_service_id'];
        if ($id == 0) {
            $this->db->prepare_Stmt('INSERT INTO  tbl_piece_service (piece_service_name,piece_service_price,currency_id) 
                                                              VALUES (?,?,?)', [&$this->info['piece_service_name'],
                &$this->info['piece_service_price'],
                &$this->info['currency_id']]);
        } else {
            $this->db->prepare_Stmt('update tbl_piece_service set piece_service_name = ?,
                                                                  piece_service_price = ?,
                                                                  currency_id = ?
                                     where piece_service_id = ?', [&$this->info['piece_service_name'],
                &$this->info['piece_service_price'],
                &$this->info['currency_id'],
                &$id]);
        }

        $error = $this->db->error();
        return $error;
    }

    public function submitEmployeePiecePrice() {
        $check = CommonFunctions::check_lock_data(['type' => 'piece_payment', 'start_date' => $this->info['piece_price_date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->info['branch_id'] = !empty($this->info['branch_id']) ? $this->info['branch_id'] : ( empty(smc_session()['user_accounts']['branch_id']) ? smc_session()['default_setting']['default_branch_id'] : smc_session()['user_accounts']['branch_id']);

        $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $this->info['branch_id'] . '"');
        $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
        $prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        $auto_number = self::getMaxPiecePrice($prefix, $this->info['is_actual']);

        $check_query = $this->db->query('select * from tbl_piece_price where  branch_id ="' . $this->info['branch_id'] . '" and is_actual="' . $this->info['is_actual'] . '" and piece_price_date ="' . $this->info['piece_price_date'] . '"');
        $rows_number = $this->db->rowsno($check_query);
        $this->db->query('START TRANSACTION');
        if ($rows_number > 0) {
            $data = $this->db->assoc($check_query);
            $piece_price_id = $data['auto_id'];
            $this->db->prepare_Stmt('Update tbl_piece_price set 
                                                            branch_id = ?
                                                            where auto_id = ?', [&$this->info['branch_id'],
                &$piece_price_id]);
        } else {
            $this->db->prepare_Stmt('INSERT INTO  tbl_piece_price (piece_price_date,auto_number,
                                                                    piece_price_prefix,branch_id,
                                                              current_year_id,is_actual) 
                                                              VALUES (?,?,?,?,?,?)', [&$this->info['piece_price_date'],
                &$auto_number,
                &$prefix, &$this->info['branch_id'],
                &$this->year_id, &$this->info['is_actual']]);
            $piece_price_id = $this->db->id();
        }
        $this->db->prepare_Stmt('delete from tbl_piece_price_details where auto_id = ? and is_paid = 0', [&$piece_price_id]);
        $piece_price_arr = json_decode($this->info['piece_price_arr'], true);
        foreach ($piece_price_arr as $details) {
            $rate = CurrencyFactory::getLastExRate($details['currency_id']);
            $rows_check = self::getEmployeePicePriceDetails($piece_price_id, $details['employee_id'], $details['piece_service_id']);
            if ($rows_check[0] == 0) {
                $this->db->prepare_Stmt('INSERT INTO  tbl_piece_price_details (auto_id,employee_id,piece_service_id,
            piece_price,current_rate,currency_id,piece_quantity,total_price) 
                                                              VALUES (?,?,?,?,?,?,?,?)', [&$piece_price_id,
                    &$details['employee_id'],
                    &$details['piece_service_id'],
                    &$details['piece_price'],
                    &$rate,
                    &$details['currency_id'],
                    &$details['piece_quantity'],
                    &$details['total_price']]);
            } else {
                $data = $rows_check[1];
                $this->db->prepare_Stmt('update tbl_piece_price_details set piece_quantity = ? + ?,total_price = ? + ?
                                                              where entery_id = ?', [&$data['piece_quantity'], &$details['piece_quantity'],
                    &$data['total_price'], &$details['total_price'],
                    &$data['entery_id']]);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function submitPaymentEmployeePiece() {
        $this->info['branch_id'] = !empty($this->info['branch_id']) ? $this->info['branch_id'] : ( empty(smc_session()['user_accounts']['branch_id']) ? smc_session()['default_setting']['default_branch_id'] : smc_session()['user_accounts']['branch_id']);

        $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $this->info['branch_id'] . '"');
        $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
        $prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        $auto_number = self::getMaxPaymentPiece($prefix, $this->info['is_actual']);
        $today = date('Y-m-d');
        $desc = "piece employee payroll";
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('INSERT INTO  tbl_piece_payment (piece_payment_auto_number,from_date,to_date,
            piece_payment_prefix,branch_id,current_year_id,is_actual) 
                                                              VALUES (?,?,?,?,?,?,?)', [
            &$auto_number,
            &$this->info['start_date'],
            &$this->info['end_date'],
            &$prefix,
            &$this->info['branch_id'],
            &$this->year_id,
            &$this->info['is_actual']]);
        $payment_piece_id = $this->db->id();
        $jv = JV::returnJV($this->info['is_actual']);
        foreach ($this->info['payment'] as $details) {
            $this->db->prepare_Stmt('INSERT INTO  tbl_piece_payment_details 
          (piece_payment_auto_id,employee_id,amount,paid_amount) 
                                                              VALUES (?,?,?,?)', [
                &$payment_piece_id,
                &$details['employee_id'],
                &$details['total_amount'],
                &$details['paid_amount']]);
            $accounts = self::viewEmployeeAccount($details['employee_id']);
            $data_1 = ["JV_ID" => $jv,
                "Transaction_Reference" => $auto_number,
                "Transaction_Account" => $accounts['exp'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $details['total_amount'],
                "Transaction_Date" => $today,
                "Currency_ID" => smc_session()['local_currency'],
                "Logged_ID" => smc_session()['user_logged_id'],
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => 1,
                "main_id" => $payment_piece_id,
                "main_type" => "pep",
                "main_nb" => $auto_number,
                "prefix" => $prefix,
                "due_date" => $today
            ];
            JV::addTransaction($data_1, $this->info['is_actual']);

            $data_2 = ["JV_ID" => $jv,
                "Transaction_Reference" => $auto_number,
                "Transaction_Account" => $accounts['inc'],
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $details['total_amount'],
                "Transaction_Date" => $today,
                "Currency_ID" => smc_session()['local_currency'],
                "Logged_ID" => smc_session()['user_logged_id'],
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => 1,
                "main_id" => $payment_piece_id,
                "main_type" => "pep",
                "main_nb" => $auto_number,
                "prefix" => $prefix,
                "due_date" => $today
            ];
            JV::addTransaction($data_2, $this->info['is_actual']);

            $data_3 = ["JV_ID" => $jv,
                "Transaction_Reference" => $auto_number,
                "Transaction_Account" => $accounts['inc'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $details['paid_amount'],
                "Transaction_Date" => $today,
                "Currency_ID" => smc_session()['local_currency'],
                "Logged_ID" => smc_session()['user_logged_id'],
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => 1,
                "main_id" => $payment_piece_id,
                "main_type" => "pep",
                "main_nb" => $auto_number,
                "prefix" => $prefix,
                "due_date" => $today
            ];
            JV::addTransaction($data_3, $this->info['is_actual']);

            $data_4 = ["JV_ID" => $jv,
                "Transaction_Reference" => $auto_number,
                "Transaction_Account" => $this->info['payment_type'],
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $details['paid_amount'],
                "Transaction_Date" => $today,
                "Currency_ID" => smc_session()['local_currency'],
                "Logged_ID" => smc_session()['user_logged_id'],
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => 1,
                "main_id" => $payment_piece_id,
                "main_type" => "pep",
                "main_nb" => $auto_number,
                "prefix" => $prefix,
                "due_date" => $today
            ];
            JV::addTransaction($data_4, $this->info['is_actual']);
        }
        $this->db->prepare_Stmt('update
                                     tbl_piece_price_details details  
                                     inner join tbl_piece_price main using(auto_id) 
                                    set details.is_paid = 1 
                                    where main.piece_price_date  between ? and ? 
                                    and main.branch_id = ? 
                                    and main.is_actual = ? 
                                    and main.current_year_id = ? ', [&$this->info['start_date'], &$this->info['end_date'], &$this->info['branch_id'], &$this->info['is_actual'], &$this->year_id]);




        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function getEmployeePicePriceDetails($auto_id, $employee_id, $piece_service_id) {
        $query = $this->db->query('select * from tbl_piece_price_details 
        where employee_id ="' . $employee_id . '" 
        and piece_service_id= "' . $piece_service_id . '"
        and auto_id = "' . $auto_id . '" and is_paid = 0');
        $rows_nb = $this->db->rowsno($query);
        if ($rows_nb == 0) {
            return [$rows_nb, []];
        }
        $row = $this->db->assoc($query);
        return [$rows_nb, $row];
    }

    public function getEmployeePieceQuantity() {
        $data = array();
        $today = Date::getToday();

        $query = $this->db->query('select concat_ws(" ",employee.Employee_FirstName, employee.Employee_MiddleName,employee.Employee_LastName) as employee_full_name,
        sum(total_price * current_rate) as total_price,0 as old_balance,"" as input,details.employee_id as entry 
                                   from tbl_piece_price_details details
                                   inner join tbl_piece_price main using (auto_id)
                                   inner join tbl_hrm_employee employee
                                   on employee.Employee_ID  = details.employee_id
                                   where main.is_actual = "' . $this->info['is_actual'] . '"
                                   and main.piece_price_date between "' . $this->info['start_date'] . '" and "' . $this->info['end_date'] . '"
                                   and main.current_year_id = "' . $this->year_id . '"
                                   and main.is_actual = "' . $this->info['is_actual'] . '"
                                   and details.is_paid = 0
                                   group by details.employee_id
                                   ');
        while ($row = $this->db->assoc($query)) {
            $row['input'] = '<input name="paid_amount" value="0" data-id="' . $row['entry'] . '" type="number" step="any" class="form-control" onchange="changePaidAmount(this)" onkeyup="changePaidAmount(this)"/>';
            $row['old_balance'] = $this->getEmployeePieceBalance($row['entry']);
            $row['Balance'] = $row['total_price'] + $row['old_balance'];
            unset($row['entry']);
            $data[] = array_values($row);
        }
        return $data;
    }

    public function getEmployeePieceBalance($employee_id) {
        $query = $this->db->query('select SUM(amount) - SUM(paid_amount) as Balance
                                   from tbl_piece_payment_details details
                                   inner join tbl_piece_payment main using(piece_payment_auto_id)
                                   where  main.current_year_id = "' . $this->year_id . '"
                                   and main.is_actual = "' . $this->info['is_actual'] . '"
                                   and details.employee_id = "' . $employee_id . '"
                                    ');
        $row = $this->db->assoc($query);
        return !empty($row['Balance']) ? $row['Balance'] : 0;
    }

    public function getEmployeePieceData() {
        $data = array();
        $payment_service_query = $this->db->query('select * from tbl_piece_payment where "' . $this->info['piece_price_date'] . '" between from_date and to_date and is_actual =0 and current_year_id = "' . $this->year_id . '" ');
        $payment_piece_check = $this->db->rowsno($payment_service_query);
        if ($payment_piece_check != 0)
            return ['payment_piece' => $payment_piece_check];
        $this->info['branch_id'] = !empty($this->info['branch_id']) ? $this->info['branch_id'] : ( empty(smc_session()['user_accounts']['branch_id']) ? smc_session()['default_setting']['default_branch_id'] : smc_session()['user_accounts']['branch_id']);
        $main_data_query = $this->db->query('select main.*,branch.branch_name
        from tbl_piece_price main 
        inner join tbl_organization_branches branch using(branch_id)
        where branch_id = "' . $this->info['branch_id'] . '" and is_actual = "' . $this->info['is_actual'] . '" 
        and piece_price_date = "' . $this->info['piece_price_date'] . '" ');
        $rows_count = $this->db->rowsno($main_data_query);
        if ($rows_count > 0) {
            $current_row = $this->db->assoc($main_data_query);
            $data['main'] = ['piece_price_date' => $current_row['piece_price_date'],
                'branch_id' => $current_row['branch_id'], 'branch_name' => $current_row['branch_name']];
            $details_query = $this->db->query('select details.*,
            concat_ws(" ",emp.Employee_FirstName, emp.Employee_MiddleName,emp.Employee_LastName) as employee_full_name
            from tbl_piece_price_details details 
            inner join tbl_hrm_employee emp on emp.Employee_ID = details.employee_id
            where auto_id = "' . $current_row['auto_id'] . '" and details.is_paid = 0');
            while ($row = $this->db->assoc($details_query)) {
                $data['details'][] = ['employee_id' => $row['employee_id'], 'employee_name' => $row['employee_full_name'],
                    'piece_price' => $row['piece_price'], 'piece_service_id' => $row['piece_service_id'],
                    'piece_quantity' => $row['piece_quantity'], 'total_price' => $row['total_price']];
            }
        }
        return $data;
    }

    public function getMaxPiecePrice($prefix, $is_actual) {
        $query = $this->db->query('SELECT MAX(auto_number) AS Entry_ID 
            FROM tbl_piece_price where current_year_id = "' . $this->year_id . '" '
                . 'and piece_price_prefix = "' . $prefix . '" and is_actual = "' . $is_actual . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $Invoice = $row['Entry_ID'] + 1;
        else
            $Invoice = 1;

        return $Invoice;
    }

    public function getMaxPaymentPiece($prefix, $is_actual) {
        $query = $this->db->query('SELECT MAX(piece_payment_auto_number) AS Entry_ID 
            FROM tbl_piece_payment where current_year_id = "' . $this->year_id . '" '
                . 'and piece_payment_prefix = "' . $prefix . '" and is_actual = "' . $is_actual . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $Invoice = $row['Entry_ID'] + 1;
        else
            $Invoice = 1;

        return $Invoice;
    }

    public function get_basic_salary_emp($emp_id, $from_date, $to_date, $month = null) {
        $basic_salary = 0;
        $currency_id = 0;
        $shift_arr = array();
        $query = $this->db->query('select e.Shift_ID,p.pay_period,
             if(e.basic_salary = 0, pg.Paygrade_Value,e.basic_salary) AS basic_salary,
             if(e.basic_salary = 0, pg.Paygrade_Currency,e.basic_salary_currency) AS basic_salary_currency,
            s.shift_type,p.unit_time,s.unit_time as shift_unit_time ,e.holiday_is_fixed,p.bs_worked_hrs
            from tbl_hrm_employee e join tbl_hrm_shifts s on s.Shift_ID = e.Shift_ID
            join tbl_hrm_basic_salary_periods p on p.period_id = e.period_id
            left join tbl_hrm_paygrade pg on e.Paygrade_ID = pg.Paygrade_ID
            where e.Employee_ID = "' . $emp_id . '"');
        $row = $this->db->assoc($query);

        $holiday_arr = array();

        // calculate number of hours to work from shifts based on range date and exclude holidays
        $total_hrs = 0;
        $total_days = 0;
        $avg_hrs = 0;
        $avg_days = 0;
        if ($row['bs_worked_hrs'] == 0) {
            if ($row['shift_type'] == 'fx') {
                $query_shift = $this->db->query('Select * from tbl_hrm_shifts where Shift_ID ="' . $row['Shift_ID'] . '"');
                while ($row_shift = $this->db->assoc($query_shift)) {
                    $row_shift['Working_Days_Arr'] = array();
                    $row_shift['Working_Day_ID'] = json_decode($row_shift['Working_Day_ID'], true);
                    //                die(print_r($work_hrs));
                    if (!empty($row_shift['Working_Day_ID'])) {
                        foreach ($row_shift['Working_Day_ID'] as $Working_Day_Row) {
                            $row_shift['Working_Days_Arr'][$Working_Day_Row['day_id']] = (strtotime($Working_Day_Row['Shift_To']) - strtotime($Working_Day_Row['Shift_From']));
                        }
                    }
                    $shift_arr = $row_shift;
                }
                //get holidays
                $hquery = $this->db->prepare_Stmt('Select Holiday_From_Date, Holiday_To_Date, Holiday_Name from tbl_hrm_holidays
                where ((Holiday_From_Date between ? and ?) || (Holiday_To_Date between ? and ?)) ', [&$from_date, &$to_date, &$from_date, &$to_date]);
                while ($hrow = $this->db->assoc($hquery)) {
                    if ($hrow['Holiday_From_Date'] <= $hrow['Holiday_To_Date']) {
                        for ($i = $hrow['Holiday_From_Date']; $i <= $hrow['Holiday_To_Date']; $i = date('Y-m-d', strtotime('+1 day', strtotime($i))))
                            $holiday_arr[$i] = 1;
                    }
                }
                //
//                $query_holiday = $this->db->query('select * from tbl_hrm_emp_absence_setting e
//            join tbl_hrm_absence_reasons r on r.Absence_Reason_ID = e.absence_reason_id
//                    where employee_id = "' . $emp_id . '" and is_holiday = 1 ');
//                $emp_fixed_holiday = $this->db->rowsno($query_holiday) > 0 ? 1 : 0;
                //
                if ($from_date <= $to_date) {
                    for ($i = $from_date; $i <= $to_date; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
                        $day_index = date('N', strtotime($i));
                        //
                        if (!empty($shift_arr)) {
                            $shift_days = $shift_arr['Working_Days_Arr'];
                            if (!empty($shift_days[$day_index])) {

                                if (!isset($holiday_arr[$i]) || $row['holiday_is_fixed']) {
                                    $total_hrs += ($shift_days[$day_index] / 3600);
                                    $total_days++;
                                }
                            }
                        }
                    }
                }
            }


            //get number of man hours if monthly or daily shift unit time
            if (($row['unit_time'] == 0 || $row['unit_time'] == 2 ) && $row['shift_type'] == 'hrm') {
                $month_date = explode('-', $from_date)[1];
                $year_date = explode('-', $from_date)[0];
                $mnth_query = $this->db->query('select nb_of_hours,avg_hrs,avg_days,nb_days from tbl_hrm_shifts s left join
            tbl_hrm_shift_hours sh on sh.shift_id = s.Shift_ID and month="' . $month_date . '" 
                and (year =0 or year = "' . $year_date . '")
                where s.Shift_ID = "' . $row['Shift_ID'] . '"  order by year desc limit 1
             ');
                $mnth_row = $this->db->assoc($mnth_query);
                if (!empty($mnth_row['nb_of_hours']))
                    $avg_hrs = $mnth_row['nb_of_hours'];
                else
                    $avg_hrs = $mnth_row['avg_hrs'];
                if (!empty($mnth_row['nb_days']))
                    $avg_days = $mnth_row['nb_days'];
                else
                    $avg_days = $mnth_row['avg_days'];
            }
        }else {
            $att_data = array();
            $data_array = [&$emp_id, &$from_date, &$to_date, &$from_date, &$to_date];
//            die('Select atnd.*,
//            atnd.attendance_id, date(if(isNULL(atnd.date_time_in), atnd.date_time_out, atnd.date_time_in)) as record_date
//            from tbl_employee_attendance_sheet atnd 
//            where employee_id = "'.$emp_id.'" and ((date(date_time_in) between "'.$from_date.'" and "'.$to_date.'" || (isNULL(date_time_in) && date(date_time_out) between "'.$from_date.'" and "'.$to_date.'")))
//                order by  date_time_in asc');
            $query = $this->db->prepare_Stmt('Select atnd.*,
            atnd.attendance_id, date(if(isNULL(atnd.date_time_in), atnd.date_time_out, atnd.date_time_in)) as record_date
            from tbl_employee_attendance_sheet atnd 
            where employee_id = ? and ((date(date_time_in) between ? and ? || (isNULL(date_time_in) && date(date_time_out) between ? and ?)))
                order by  date_time_in asc', $data_array);
            while ($row_att = $this->db->assoc($query)) {
                if (empty($att_data[$row_att['record_date']])) {
                    $att_data[$row_att['record_date']] = $row_att;
                    $total_days++;
                }
                $total_hrs += ((strtotime($row_att['date_time_out']) - strtotime($row_att['date_time_in'])) / 3600);
            }
            $avg_hrs = $total_hrs;
            $avg_days = $total_days;
        }
//echo $row['pay_period'].'==';die();
//        echo $total_hrs.'=='.$total_days;die();
        //                echo"<pre>";
        //                die(print_r($row));
        //salary paid monthly
        if ($row['pay_period'] == 1) {
            // fixed shift
            if ($row['shift_type'] == 'fx') {
                //salary unit time is hour
                if ($row['unit_time'] == 0) {
                    $basic_salary = $row['basic_salary'] * $total_hrs;
                    $currency_id = $row['basic_salary_currency'];
                    //salary unit time is month
                } elseif ($row['unit_time'] == 1) {
                    $basic_salary = $row['basic_salary'];
                    $currency_id = $row['basic_salary_currency'];
                    // salary unit time daily
                } elseif ($row['unit_time'] == 2) {
                    $basic_salary = $row['basic_salary'] * $total_days;
                    $currency_id = $row['basic_salary_currency'];
                }
//                echo $basic_salary.'===';die();
                //man hours
            } elseif ($row['shift_type'] == 'hrm') {
                //monthly man hours
                if ($row['shift_unit_time'] == 1) {
                    //salary unit time is hour
                    if ($row['unit_time'] == 0) {
                        $basic_salary = $row['basic_salary'] * $avg_hrs;
                        $currency_id = $row['basic_salary_currency'];
                        //salary unit time is month
                    } elseif ($row['unit_time'] == 1) {
                        $basic_salary = $row['basic_salary'];
                        $currency_id = $row['basic_salary_currency'];
                        //salary unit time is daily
                    } elseif ($row['unit_time'] == 2) {
//                        echo $row['basic_salary'].'=='.$avg_days;die();
                        $basic_salary = $row['basic_salary'] * $avg_days;
                        $currency_id = $row['basic_salary_currency'];
                    }
                    //weekly man hours
                } elseif ($row['shift_unit_time'] == 2) {
                    //salary unit time is month
                    if ($row['unit_time'] == 1) {
                        $basic_salary = $row['basic_salary'];
                        $currency_id = $row['basic_salary_currency'];
                    }
                }
            }
            //salary paid weekly
        } elseif ($row['pay_period'] == 0) {
            // fixed shift
            if ($row['shift_type'] == 'fx') {
                //weekly man hours
                //                if ($row['shift_unit_time'] == 2) {
                //salary unit time is hour
                if ($row['unit_time'] == 0) {
                    $basic_salary = $row['basic_salary'] * $total_hrs;
                    $currency_id = $row['basic_salary_currency'];
                    // salary unit time is daily
                } else if ($row['unit_time'] == 2) {
                    $basic_salary = $row['basic_salary'] * $total_days;
                    $currency_id = $row['basic_salary_currency'];
                    //                        echo $basic_salary.'===';die();
                }
                //                }
                //man hours  
            } elseif ($row['shift_type'] == 'hrm') {
                //weekly man hours
                if ($row['shift_unit_time'] == 2) {
                    //salary unit time is hour
                    if ($row['unit_time'] == 0) {
                        $basic_salary = $row['basic_salary'] * $avg_hrs;
                        $currency_id = $row['basic_salary_currency'];
                    } else if ($row['unit_time'] == 2) {
                        $basic_salary = $row['basic_salary'] * $avg_days;
                        $currency_id = $row['basic_salary_currency'];
                    }
                }
            }
        }
        $basic_salary = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($currency_id, smc_session()['local_currency'], $basic_salary);
        return $basic_salary;
    }

    public function getEmployeeDuePayments() {
        $data = array();
        $emp_arr = array();
        $shift_arr = array();
        $total_hrs_wrks = 0.0;
        $total_hrs_arr = array();
        $is_actual = $this->info['is_actual'];
        $emp_id = $this->info['employee_id'];
        $from_date = $this->info['start_date'];
        $to_date = $this->info['end_date'];
        $cond = '';
        $data_array = [&$from_date, &$to_date, &$from_date, &$to_date];
        if (!empty($is_actual)) {
            $cond .= ' and emp.Is_Hidden = ?';
            $data_array[] = $is_actual;
        }
        if (!empty($emp_id)) {
            $cond .= ' and emp.Employee_ID = ?';
            $data_array[] = $emp_id;
        }
        $query = $this->db->prepare_Stmt('Select atnd.*,emp.Employee_ID, emp.Shift_ID, concat(Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) as Employee_Name
            , atnd.attendance_id, date(if(isNULL(atnd.date_time_in), atnd.date_time_out, atnd.date_time_in)) as record_date, atnd.date_time_in, atnd.date_time_out,emp.basic_salary from tbl_hrm_employee emp
                left join tbl_employee_attendance_sheet atnd on (emp.Employee_ID = atnd.employee_id and (date(date_time_in) between ? and ? || (isNULL(date_time_in) && date(date_time_out) between ? and ?)))
                inner join tbl_hrm_basic_salary_periods periods on (periods.period_id = emp.period_id and periods.unit_time = 0)
            where  true ' . $cond . ' order by Employee_Name, date_time_in asc', $data_array);
        while ($row = $this->db->assoc($query)) {
            $emp_arr[$row['Employee_ID']] = array('Employee_ID' => $row['Employee_ID'], 'Employee_Name' => $row['Employee_Name'], 'Shift_ID' => $row['Shift_ID'], 'basic_salary' => $row['basic_salary']);
            if (!empty($row['attendance_id'])) {
                if (empty($data[$row['Employee_ID']][$row['record_date']]))
                    $data[$row['Employee_ID']][$row['record_date']] = 0;
                $data[$row['Employee_ID']][$row['record_date']] += (strtotime($row['date_time_out']) - strtotime($row['date_time_in']));
            }
            if (!empty($row['Shift_ID']))
                $shift_arr[$row['Shift_ID']] = null;
        }
        $emp_ids = (empty($emp_arr) ? null : implode(',', array_keys($emp_arr)));

        if ($from_date <= $to_date) {
            for ($i = $from_date; $i <= $to_date; $i = date('Y-m-d', strtotime('+1 day', strtotime($i)))) {
                $day_index = date('N', strtotime($i));
                foreach ($emp_arr as $tmp_emp_id => $emp_row) {
                    if (!isset($total_hrs_arr[$tmp_emp_id]))
                        $total_hrs_arr[$tmp_emp_id] = array('emp_name' => $emp_row['Employee_Name'], 'real_work' => 0, 'basic_salary' => $emp_row['basic_salary'], 'total' => 0);
                    $emp_hrs = &$total_hrs_arr[$tmp_emp_id];
                    if (!empty($data[$tmp_emp_id][$i])) {
                        $emp_hrs['real_work'] += ($data[$tmp_emp_id][$i] / 3600);
                        $emp_hrs['total'] = 0;
                    }
                }
            }
        }
        //        echo '';
        //        print_r($total_hrs_arr);
        $current_data = array();
        foreach ($total_hrs_arr as $row_details) {
            $row_details['total'] = $row_details['basic_salary'] * $row_details['real_work'];
            $current_data[] = array_values($row_details);
        }

        return $current_data;
    }

    public function add_edit_emp_location() {
        $this->db->query('START TRANSACTION');
        if ($this->info['is_edit'] == 0) {
            $data = [
                &$this->info['location_code'],
                &$this->info['location_name']
            ];
            $this->db->prepare_Stmt('INSERT INTO `tbl_employee_location`( `location_code`, `Location_Name`)
                values (?,?)', $data);
        } else {
            $data = [
                &$this->info['location_code'],
                &$this->info['location_name'],
                &$this->info['location_id']
            ];
            $this->db->prepare_Stmt('update `tbl_employee_location`
                set `location_code`=?, `Location_Name`=? where location_id =?', $data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public static function get_emp_name_by_id($emp_id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('select 
            CONCAT(Employee_FirstName, " ", Employee_MiddleName, " ", Employee_LastName) AS Employee_Name
            from tbl_hrm_employee where Employee_ID = "' . $emp_id . '"');
        $row = $db->assoc($query);
        return $row['Employee_Name'];
    }

    public function add_edit_employee_banks() {
        $this->db->query('START TRANSACTION');
        if ($this->info['is_edit'] == 0) {
            $data = [
                &$this->info['bank_name'],
                &$this->info['bank_branch'],
                &$this->info['bank_code']
            ];
            $this->db->prepare_Stmt('INSERT INTO `tbl_employee_banks`( `bank_name`, `bank_branch`,bank_code) 
                values (?,?,?)', $data);
        } else {
            $data = [
                &$this->info['bank_name'],
                &$this->info['bank_branch'],
                &$this->info['bank_code'],
                &$this->info['bank_id']
            ];
            $this->db->prepare_Stmt('update `tbl_employee_banks`
                set `bank_name`=?, `bank_branch`=?,bank_code=? where bank_id =?', $data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_emp_banks_by_id($id) {
        $data = array();
        $query = $this->db->query('select * from tbl_employee_banks where bank_id = "' . $id . '"');
        $data = $this->db->assoc($query);
        return $data;
    }

    public function search_emp_by_pay_period($term = null, $page, $limit, $is_actual = 0, $pay_period = null) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND (Employee_FirstName LIKE ? or Employee_MiddleName LIKE ?  or Employee_LastName LIKE ? ) ';
            $data_filter[] = "%" . $term . "%";
        }
        if ($is_actual == 0) {
            $cond .= ' and Is_Hidden = 0 ';
        }
        if (!is_null($pay_period)) {
            $cond .= ' and p.pay_period = ? ';
            $data_filter[] = $pay_period;
        }
        $query = $this->db->prepare_Stmt('select 
                                    Employee_ID as id,
                                    concat(Employee_FirstName," ",Employee_MiddleName," ",Employee_LastName) as text
                                from 
                                    tbl_hrm_employee
                                    join tbl_hrm_basic_salary_periods p using(period_id)
                                where Status_ID = 1 
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function get_emp_bank_info() {
        $data = array();
        $cond = ' ';
        if (!empty($this->info['emp_id']))
            $cond .= ' and p.Employee_ID = "' . $this->info['emp_id'] . '"  ';
        if (!empty($this->info['dep_id']))
            $cond .= ' and e.Branch_ID = "' . $this->info['dep_id'] . '"  ';
        if (isset($this->info['bank_id']) && $this->info['bank_id'] != '') {

            if ($this->info['bank_id'] == 'with_bank') {
                $cond .= ' and (e.bank_id <> 0 and !isnull(e.bank_id)) ';
            } else {
                if ($this->info['bank_id'] == 0)
                    $cond .= ' and (e.bank_id = 0 or isnull(e.bank_id)) ';
                else
                    $cond .= ' and e.bank_id = "' . $this->info['bank_id'] . '"  ';
            }
        }
        //        $cond.=' and e.bank_id = "' . $this->info['bank_id'] . '"  ';
        if (isset($this->info['is_nssf']) && $this->info['is_nssf'] != '')
            $cond .= ' and e.is_nssf = "' . $this->info['is_nssf'] . '"  ';
        //        $paid_amount = 0;
        $query = $this->db->query('select CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,e.second_lang_name,
            concat_ws(" - ",b.bank_name,b.bank_code) as bank_name_code,b.bank_branch,e.bank_account,p.Amount_Paid from tbl_hrm_payroll p
            join tbl_hrm_employee e on e.Employee_ID = p.Employee_ID
            join tbl_hrm_basic_salary_periods pe on pe.period_id = e.period_id
            left join tbl_employee_banks b on b.bank_id = e.bank_id
            where pe.pay_period = "' . $this->info['pay_period'] . '" 
            and p.from_date = "' . $this->info['from_date'] . '" and p.Payroll_Date = "' . $this->info['to_date'] . '" ' . $cond);
        while ($row = $this->db->assoc($query)) {
            $data[] = array_values($row);
        }
        return $data;
    }

    public function deleteEmpFile($id) {
        $query = $this->db->query('SELECT `Document_File_Name` FROM `tbl_hrm_employee_document` WHERE  Entry_ID = "' . $id . '"');
        $row = $this->db->assoc($query);

        $filename = $row['Document_File_Name'];
        $this->db->prepare_Stmt('delete from tbl_hrm_employee_document where Entry_ID = "' . $id . '"');
        $error = $this->db->error();
        if (empty($error)) {
            //            echo upload_current_path . '/patient_doc/' . $pid . '/' . $filename;
            //            chmod(upload_current_path . '/patient_doc/' . $pid . '/' . $filename, 0777);
            unlink(upload_path . 'Documents/' . $filename);
        }
        return $error;
    }

    public function payroll_report($from_date, $to_date, $dep_id, $shift_type, $is_nssf, $is_paid, $bank_filter, $is_working, $emp_type, $is_actual = 0, $actual_only = 0, $early_payroll = '') {
        //        $data[] = $this->viewPayroll(26, $from_date, $to_date);
        //        viewPayroll($employee_id = null, $from_date, $to_date, $month = null) 
        $data = array();
        $cond = ' ';
        //        $cond = ' and (leave_date is null or leave_date ="" or leave_date="0000-00-00")';
        if (!empty($dep_id)) {
            $cond .= ' and emp.Branch_ID = "' . $dep_id . '"';
        }
        if (empty($is_actual)) {
            $cond .= ' and emp.Is_Hidden = "' . $is_actual . '"';
        } else if (!empty($actual_only)) {
            $cond .= ' and emp.Is_Hidden = 1';
        }

        if (!empty($emp_type)) {
            $cond .= ' and emp.IS_Employee = "' . $emp_type . '"';
        }
        if ($early_payroll != '') {
            $cond .= ' and emp.early_payroll = "' . $early_payroll . '"';
        }
        if (isset($is_nssf) && $is_nssf != '') {
            $cond .= ' and emp.is_nssf = "' . $is_nssf . '"';
        }
        if (isset($bank_filter) && $bank_filter != '') {
            if ($bank_filter == 'with_bank') {
                $cond .= ' and (emp.bank_id <> 0 and !isnull(bank_id)) ';
            } else {
                if ($bank_filter == 0)
                    $cond .= ' and (emp.bank_id = 0 or isnull(bank_id)) ';
                else
                    $cond .= ' and emp.bank_id = "' . $bank_filter . '"  ';
            }
        }
        if (isset($is_working) && $is_working != '') {
            if ($is_working == 1)
                $cond .= ' and  (leave_date is null or leave_date ="" or leave_date="0000-00-00")';
            else
                $cond .= ' and  (leave_date is not null && leave_date <> "" && leave_date <> "0000-00-00")';
        }
        $totals_array = array("basic_salary_total" => 0, "transportation_total" => 0, "overtime_total" => 0, "bonus_total" => 0,
            "deduction_total" => 0, "loan_total" => 0, "allownance_total" => 0, "ssnf_total" => 0, "tax_total" => 0,
            "advance_payment_total" => 0, "launch_total" => 0, "phone_recharge_total" => 0,
            "other_charges_total" => 0, "net_total" => 0, "paid_total" => 0, "paid_amount" => 0,
            "emp_charge1_total" => 0, "emp_charge2_total" => 0, "emp_charge3_total" => 0, "emp_charge4_total" => 0);
        if (!is_null($shift_type)) {
            if ($shift_type == 0)
                $cond .= ' and s.shift_type = "fx"';
            elseif ($shift_type == 1)
                $cond .= ' and s.unit_time = 2 and s.shift_type = "hrm"';
            elseif ($shift_type == 2)
                $cond .= ' and s.unit_time = 1 and s.shift_type = "hrm"';
        }
        $report = new Report();
        $query = $this->db->query('select * from tbl_hrm_employee emp 
            left join tbl_hrm_shifts s on s.Shift_ID = emp.Shift_ID
            where  Employee_StartDate <= "' . $to_date . '"  ' . $cond);
        while ($row = $this->db->assoc($query)) {
            $pay_arr = $this->viewPayroll($row['Employee_ID'], $from_date, $to_date, null, $is_paid);

            if (empty($pay_arr['error']) && !empty($pay_arr['main']) && (($is_paid == 1 && $pay_arr['is_paid'] == 1) || ($is_paid == 2 && $pay_arr['is_paid'] == 0) || $is_paid == 0) && !empty($pay_arr['details'])) {
                if (smc_session()['default_setting']['view_net_overall_balance_multipayroll'] == 1) {
                    $pay_arr['emp_balance'] = $report->returnEmployeeExactBalance($row['Employee_ID'], smc_session()["local_currency"], smc_session()['start_date'], $to_date, 0, $is_actual)['Balance'] * -1;
                    $pay_arr['net_balance'] = $pay_arr['emp_balance'] + (!empty($pay_arr['main']['Net_Pay']) ? $pay_arr['main']['Net_Pay'] : 0);
                } else {
                    $pay_arr['emp_balance'] = 0;
                    $pay_arr['net_balance'] = !empty($pay_arr['main']['Net_Pay']) ? $pay_arr['main']['Net_Pay'] : 0;
                }

                $data[] = $pay_arr;
//                if($pay_arr['main']['Employee_ID']==72){
//                                echo"<pre>";
//                                print_r($pay_arr);die();
//                }
                $totals_array['basic_salary_total'] += $pay_arr['details']['basic_salary'];
                $totals_array['transportation_total'] += $pay_arr['details']['transportation'];
                $totals_array['overtime_total'] += $pay_arr['details']['overtime'];
                $totals_array['bonus_total'] += $pay_arr['details']['bonus'];
                $totals_array['deduction_total'] += $pay_arr['details']['deduction'];
                $totals_array['loan_total'] += $pay_arr['details']['loan_payment'];
                $totals_array['allownance_total'] += $pay_arr['details']['allowance'];
                $totals_array['ssnf_total'] += $pay_arr['details']['ssnf'];
                $totals_array['tax_total'] += $pay_arr['details']['tax'];
                $totals_array['advance_payment_total'] += $pay_arr['details']['advance_payment'];
                $totals_array['launch_total'] += $pay_arr['details']['Lunch'];
                $totals_array['phone_recharge_total'] += $pay_arr['details']['Phone'];
                $totals_array['other_charges_total'] += $pay_arr['details']['other_charges'];
                $totals_array['emp_charge1_total'] += (!empty($pay_arr['details']['emp_charge1']) ? $pay_arr['details']['emp_charge1'] : 0);
                $totals_array['emp_charge2_total'] += (!empty($pay_arr['details']['emp_charge2']) ? $pay_arr['details']['emp_charge2'] : 0);
                $totals_array['emp_charge3_total'] += (!empty($pay_arr['details']['emp_charge3']) ? $pay_arr['details']['emp_charge3'] : 0);
                $totals_array['emp_charge4_total'] += (!empty($pay_arr['details']['emp_charge4']) ? $pay_arr['details']['emp_charge4'] : 0);
//                $totals_array['net_total'] += $pay_arr['main']['Net_Pay'];
                $totals_array['net_total'] += $pay_arr['net_balance'];
                $totals_array['paid_total'] += ($pay_arr['is_paid'] == 1 ? $pay_arr['Amount_Paid'] : 0);
                $totals_array['paid_amount'] += ($pay_arr['is_paid'] == 1 ? $pay_arr['Amount_Paid'] : 0);
            }
        }
//        echo"<pre>";
//        die(print_r($data));
        return [$data, $totals_array];
    }

    public function multi_advance_payment_emp() {
        $data = array();
        $query = $this->db->query('select Employee_ID as emp,Employee_Work_ID,
            concat(Employee_FirstName," ",Employee_MiddleName," ",Employee_LastName) as Employee_Name,
            Employee_ID 
            from tbl_hrm_employee where 1=1' . ($this->info['is_actual'] == 0 ? ' and Is_Hidden=0' : ''));
        while ($row = $this->db->assoc($query)) {
            $row['emp'] = '<input type="checkbox" data-id="' . $row['Employee_ID'] . '" class="advance_payment_check"/>';
            $row['Employee_ID'] = '<input type="number" data-id="' . $row['Employee_ID'] . '" class="form-control amount" step="any" min="0"/>';
            $data[] = array_values($row);
        }
        return $data;
    }

    public function searchBanks($term = null, $page, $limit, $add_no_bank = false) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = $add_no_bank ? array(array('id' => '0', 'text' => CommonLanguage::getMessage('no_bank')), array('id' => 'with_bank', 'text' => CommonLanguage::getMessage('with_bank'))) : array();
        $cond = '';
        if (!is_null($term)) {
            $cond .= ' and (bank_name LIKE "%' . $term . '%"  or bank_code LIKE "%' . $term . '%")';
        }
        $query = $this->db->query('SELECT `bank_id` as id, 
                                           concat_ws(" - ",`bank_name`,bank_code) as text 
                                    FROM `tbl_employee_banks` where 1=1
        ' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1));

        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function submitEmpOpening() {
        $entry_id = $this->info['entry_id'];
        $currency_id = smc_session()['local_currency'];
        if ($entry_id == 0) {
            $this->db->prepare_Stmt('Insert into tbl_hrm_employee_opening (employee_id,to_date,total_salary,total_allowance,total_tax,Currency_ID,current_rate) values (?,?,?,?,?,?,1)', [
                &$this->info['employee_id'], &$this->info['to_date'],
                &$this->info['total_salary'], &$this->info['total_allowance'], &$this->info['total_tax'], &$currency_id]);
        } else {
            $this->db->prepare_Stmt('Update  tbl_hrm_employee_opening set employee_id=?,to_date=?,total_salary=?,total_allowance=?,total_tax=?,Currency_ID=?,current_rate=1 where entry_id = ?', [
                &$this->info['employee_id'], &$this->info['to_date'],
                &$this->info['total_salary'], &$this->info['total_allowance'], &$this->info['total_tax'], &$currency_id, &$entry_id]);
        }
        $error = $this->db->error();
        return $error;
    }

    public function deleteEmpOpening() {
        $entry_id = $this->info['entry_id'];
        $this->db->prepare_Stmt('delete from tbl_hrm_employee_opening  where entry_id = ?', [&$entry_id]);
        $error = $this->db->error();
        return $error;
    }

    public function update_multi_emp_leave_setting() {
        $this->db->query('START TRANSACTION');
        foreach ($this->info['emp_data'] as $emp) {
            $data = [$emp['leave'],
                $emp['previous'], $emp['remaining'], $emp['Employee_ID']];
            $this->db->prepare_Stmt('update tbl_hrm_employee set leave_days_nb = ?,
                previous_days_nb=?,remaining_days = ? where Employee_ID = ?', $data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function delete_emp_location($id) {
        $error_msg = array();
        $query = $this->db->query('select * from tbl_hrm_employee where Employee_Location = "' . $id . '"');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            $error_msg[0] = CommonLanguage::getMessage('location_used_emp');
            return $error_msg;
        }
        //
        $query = $this->db->query('select * from tbl_employee_attendance_sheet where location_id = "' . $id . '"');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            $error_msg[0] = CommonLanguage::getMessage('location_used_att');
            return $error_msg;
        }
        //
        $query = $this->db->query('select * from tbl_checkinout where location_id = "' . $id . '"');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            $error_msg[0] = CommonLanguage::getMessage('location_used_att');
            return $error_msg;
        }
        $this->db->prepare_Stmt('delete from tbl_employee_location where Location_ID = ?', [&$id]);
        $error = $this->db->error();
        return $error;
    }

    public function search_shift_note($term) {
        $data = array();
        $query = $this->db->query('select distinct shift_note , shift_note as text 
            from tbl_hrm_employee where shift_note like "%' . smc_escape($term) . '%"  
                and shift_note <> "" limit 30');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function search_location_address($term) {
        $data = array();
        $query = $this->db->query('select distinct location_address , location_address as text 
            from tbl_hrm_employee where location_address like "%' . smc_escape($term) . '%"  and location_address <> "" limit 30');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function get_interior_ministry_report($options) {
        $cond = ' ';
        if (isset($options['type'])) {
            if ($options['type'] == 0)
                $cond .= ' and isnull(leave_date) and Employee_StartDate <= "' . $options['to_date'] . '"';
            if ($options['type'] == 1)
                $cond .= ' and Employee_StartDate between "' . $options['from_date'] . '" and "' . $options['to_date'] . '"';
            if ($options['type'] == 2)
                $cond .= ' and leave_date between "' . $options['from_date'] . '" and "' . $options['to_date'] . '"';
        }
        if (!empty($options['dep_id']))
            $cond .= ' and Branch_ID = "' . $options['dep_id'] . '"';
        if (isset($options['is_nssf']) && $options['is_nssf'] != '')
            $cond .= ' and is_nssf = "' . $options['is_nssf'] . '"';
        $data = array();
        $query = $this->db->query('select second_lang_name,location_address,Employee_StartDate,Job_Name,
            shift_note,Employee_StreetAddress1,Employee_MobilePhone from view_employees where 1=1 ' . $cond);
        while ($row = $this->db->assoc($query)) {
            $temp = array_values($row);
            $temp[] = '<button class="delete btn btn-success" >' . CommonLanguage::getMessage('Delete') . '</button>';
            $data[] = array_values($temp);
        }
        return $data;
    }

    public function save_interior_ministry_report() {

        $this->db->query('START TRANSACTION');
        $date_time = date("Y-m-d H:i:s");
        $user_id = smc_session()['user_logged_id'];
        $this->db->prepare_Stmt('insert into tbl_interior_ministry_log
                (date_time,user_id,from_date,to_date,title) values(?,?,?,?,?)', [&$date_time, &$user_id, &$this->info['from_date'],
            &$this->info['to_date'], &$this->info['title']]);
        $error = $this->db->error();
        //            die(print_r($error));
        $entry_id = $this->db->id();
        //            echo 
        foreach ($this->info['emp_data'] as $row) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_interior_ministry_log_details`( `entry_id`,
                    `employee_name`, `location_address`, `start_date`, `job`, `shift_note`, `employee_address`,
                    `mobile_number`) values(?,?,?,?,?,?,?,?)', [&$entry_id, &$row['employee_name'],
                &$row['location_address'], &$row['start_date'], &$row['job'], &$row['shift_note'],
                &$row['employee_address'], &$row['mobile_phone']]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function ministry_report_search($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND title LIKE ?  ';
            $data_filter[] = "%" . $term . "%";
        }

        $query = $this->db->prepare_Stmt('select 
                                    l.entry_id as id,
                                    concat_ws(" - ",u.User_Name,date_time,l.title) as text
                                from 
                                    tbl_interior_ministry_log l
                                    join tbl_common_users u on u.User_ID = l.user_id
                                where 1 = 1 
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function get_interior_ministry_saved_report($id) {

        $data = array();
        $query = $this->db->query('SELECT  `employee_name`, `location_address`,
            `start_date`, `job`, `shift_note`, `employee_address`, `mobile_number`
            FROM `tbl_interior_ministry_log_details` where entry_id="' . $id . '" ');
        while ($row = $this->db->assoc($query)) {
            $temp = array_values($row);
            $temp[] = '<button class="delete btn btn-success" >' . CommonLanguage::getMessage('Delete') . '</button>';
            $data[] = array_values($temp);
        }
        return $data;
    }

    public function edit_payroll_tax_data() {
        $this->db->query('START TRANSACTION');
        $query_payroll = $this->db->query('select Payroll_ID,other_prices from tbl_hrm_payroll  ');
        while ($pay_row = $this->db->assoc($query_payroll)) {
            $data_pay = json_decode($pay_row['other_prices'], true);
            //            echo '==---'.$data_pay['details']['basic_salary_tax'].'--==';
            //            $total_salary+=$data_pay['details']['basic_salary_tax'];
            $trans = ($data_pay['details']['transportation'] > 216000 ? ($data_pay['details']['transportation'] - 216000) : 0);
            $data_pay['details']['basic_salary_tax'] = ($data_pay['details']['basic_salary'] + $data_pay['details']['bonus'] + $data_pay['details']['overtime'] + $trans);
            $other_price = json_encode($data_pay);
            $this->db->prepare_Stmt('update tbl_hrm_payroll set other_prices = ?
                where Payroll_ID = "' . $pay_row['Payroll_ID'] . '"', array(&$other_price));
            //            echo $total_salary.'==';
            //             echo 'month count:'.$month_count.'==';
        }
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        die(print_r($error));
    }

    public function submitCommissionSetting() {
        $this->db->query('START TRANSACTION');
        $commission_id = $this->info['commission_id'];
        if ($commission_id == 0) {
            $this->db->prepare_Stmt('insert into  tbl_commission_type (commission_name) Values(?)', array(&$this->info['commission_name']));
            $commission_id = $this->db->id();
        } else {
            $this->db->prepare_Stmt('update tbl_commission_type set commission_name = ? where commission_id = ?', array(&$this->info['commission_name'], &$commission_id));
            $this->db->prepare_Stmt('delete from tbl_commission_type_details  where commission_id = ? ', array(&$commission_id));
        }
        $this->info['details'] = json_decode($this->info['details'], true);
        foreach ($this->info['details'] as $item) {
            $item['condition_operation'] = empty($item['condition_operation']) ? 0 : $item['condition_operation'];
            $item['condition_value'] = empty($item['condition_value']) ? 0 : $item['condition_value'];
            $item['condition_value_to'] = empty($item['condition_value_to']) ? 0 : $item['condition_value_to'];
            $this->db->prepare_Stmt('insert into  tbl_commission_type_details 
                            (commission_id,commission_option,condition_operation,condition_value,condition_value_to,
                            commission_type,commission_amount) Values(?,?,?,?,?,?,?)', array(&$commission_id, &$item['commission_option'],
                &$item['condition_operation'], &$item['condition_value'], &$item['condition_value_to'],
                &$item['commission_type'], &$item['commission_amount']));
        }

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function deleteCommissionSetting() {
        $this->db->prepare_Stmt('delete from tbl_commission_type  where commission_id = ? ', array(&$this->info['commission_id']));
        $error = $this->db->error();
        return $error;
    }

    public function get_payroll_to_print($emp_id, $from_date, $to_date, $is_actual = 0) {
        $data = array();
        //
        $payroll_data = $this->viewPayroll($emp_id, $from_date, $to_date);
        $data = $payroll_data['main'];
        //
        $emp_data = array();
        $emp_query = $this->db->query('select * from tbl_hrm_employee where Employee_ID = "' . $emp_id . '"');
        $emp_data = $this->db->assoc($emp_query);
        //
        $trans_date = empty($emp_data['early_payroll']) ? $to_date : date('Y-m-d', strtotime($to_date . "+1 days"));
        $data['payroll_date'] = $trans_date;
        $data['year_month'] = date('Y-F', strtotime($trans_date));
        //
        $report = new Report;
        $data['emp_balance'] = CommonFunctions::formatNumber($report->returnEmployeeExactBalance($emp_id, smc_session()["local_currency"], smc_session()['start_date'], date('Y-m-d'), 0, $is_actual)['Balance']);
        $data['local_currency_symbol'] = smc_session()['local_currency_symbol'];
        $data['paid_amount'] = CommonFunctions::formatNumber($payroll_data['Amount_Paid']);
        $data['from_date'] = $from_date;
        $data['to_date'] = $to_date;
        foreach ($payroll_data['details'] as $title => $amount) {
            $data['detail_' . $title . '_title'] = CommonLanguage::getMessage($title);
            $data['detail_' . $title . '_value'] = is_numeric($amount) ? CommonFunctions::formatNumber($amount) : $amount;
            $data['detail_' . $title . '_class'] = $amount > 0 ? '' : 'hidden';
            $data['details'][] = array('title' => CommonLanguage::getMessage($title), 'value' => CommonFunctions::formatNumber($amount), 'class' => ($amount > 0 ? '' : 'hidden'));
        }
        if (!empty($payroll_data['att_data'])) {
            foreach ($payroll_data['att_data'] as $title => $amount) {
                $data['att_' . $title . '_title'] = CommonLanguage::getMessage($title);
                $data['att_' . $title . '_value'] = is_numeric($amount) ? CommonFunctions::formatNumber($amount) : $amount;
                $data['att_' . $title . '_class'] = $amount > 0 ? '' : 'hidden';
            }
        }
        $data['net_without_tax'] = CommonFunctions::formatNumber($data['Net_Pay'] - $data['detail_tax_value']);
        $data['Net_Pay'] = CommonFunctions::formatNumber($data['Net_Pay']);
//
//        echo"<pre>";
//        die(print_r($data));
        return $data;
    }

}
