<?php

Class WorkingTimes {

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

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
    }

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

    public function addWorkingDays() {
        $this->db->query('START TRANSACTION');
        $check = 0;
        $this->db->prepare_Stmt('Update  tbl_hrm_working_days  SET Is_Checked = ? ', [&$check]);
        foreach ($this->info['MyData'] as $val):
            $this->db->prepare_Stmt('Update 
                                                tbl_hrm_working_days
                                        SET 
                                                Is_Checked = 1
                                        WHERE
                                                Working_Day_ID = ?
                    ', [&$val]);
        endforeach;
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function viewUnOrWorkingDays($val = null) {
        $condition = '';
        $data = array();
        if (!is_null($val))
            $condition .= 'WHERE Is_Checked = "' . $val . '"';
        $query = $this->db->query('SELECT
                                            *
                                    FROM 
                                            tbl_hrm_working_days
				' . $condition);
        while ($row = $this->db->assoc($query)) {
            $row['Working_Day_Name'] = CommonLanguage::getMessage($row['Working_Day_Name']);
            $data[] = $row;
        }

        return $data;
    }

    public function addShifts() {
        $this->db->query('START TRANSACTION');
        $logged_id = Session::getUserID();
        $days = $this->info['shift_type'] == 'fx' ? json_encode($this->info['Shift_Data']) : '';
        $avg_hrs = $this->info['shift_type'] == 'hrm' ? $this->info['avg_hrs'] : '';
        $avg_days = $this->info['shift_type'] == 'hrm' ? $this->info['avg_days'] : '';
        $unit_time = $this->info['shift_type'] == 'hrm' ? $this->info['unit_time'] : '';
        $max_hrs_per_day = $this->info['shift_type'] == 'hrm' ? $this->info['max_hrs_per_day'] : '';
        $avg_hrs_per_day = $this->info['shift_type'] == 'hrm' ? $this->info['avg_hrs_per_day'] : '';
        $shiftDetails = [&$this->info["Shift_Name"], &$days, &$logged_id, &$this->info['shift_type'],
            &$avg_hrs, &$avg_days, &$max_hrs_per_day, &$avg_hrs_per_day, &$unit_time, &$this->info['round_bfr_post']];
        $this->db->prepare_Stmt('INSERT INTO tbl_hrm_shifts
                                            (
                                                Shift_Name, 
                                                Working_Day_ID,
                                                Logged_ID,
                                                shift_type,
                                                avg_hrs,
                                                avg_days,
                                                max_hrs_per_day,
                                                avg_hrs_per_day,
                                                unit_time,round_bfr_post
                                            )
                                                VALUES
                                            (?,?,?,?,?,?,?,?,?,?)
                    ', $shiftDetails);
        $shift_id = $this->db->id();
        if (!empty($this->info['rounds'])) {
            $i = 0;
            foreach ($this->info['rounds'] as $round) {
                $day_indx = $this->info['shift_type'] == 'hrm' ? $i : $round['work_day_id'];
                if (!empty($round['time_in'])) {
                    $time_in = date("H:i:s", strtotime($round['time_in']));
                    $data_round = [&$time_in, &$round['round_in'], &$day_indx, &$shift_id];
                    $this->db->prepare_Stmt('INSERT INTO `tbl_hrm_shift_rounds`( `hr`, `round_min`,
                    `round_type`, `day_indx`, `shift_id`) values(?,?,0,?,?)', $data_round);
                }
                //
                if (!empty($round['time_out'])) {
                    $time_out = date("H:i:s", strtotime($round['time_out']));
                    $data_round = [&$time_out, &$round['round_out'], &$day_indx, &$shift_id];
                    $this->db->prepare_Stmt('INSERT INTO `tbl_hrm_shift_rounds`( `hr`, `round_min`,
                    `round_type`, `day_indx`, `shift_id`) values(?,?,1,?,?)', $data_round);
                }
                $i++;
            }
        }

        if ($this->info['shift_type'] == 'hrm') {
            if (!empty($this->info['Shift_Data']) && $this->info['unit_time'] == 1) {
                foreach ($this->info['Shift_Data'] as $shift) {
                    $item = [&$shift_id, &$shift['month'], &$shift['year'], &$shift['nb_days'],
                        &$shift['nb_hrs'], &$shift['avg_hrs_day']];
                    $this->db->prepare_Stmt('INSERT INTO tbl_hrm_shift_hours
                ( shift_id, month,year,nb_days,nb_of_hours,avg_hrs_day) VALUES (?,?,?,?,?,?)', $item);
                }
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

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

        $logged_id = Session::getUserID();
        //        $days = json_encode($this->info['Shift_Data']);
        $days = $this->info['shift_type'] == 'fx' ? json_encode($this->info['Shift_Data']) : '';
        $avg_hrs = $this->info['shift_type'] == 'hrm' ? $this->info['avg_hrs'] : '';
        $avg_days = $this->info['shift_type'] == 'hrm' ? $this->info['avg_days'] : '';
        $unit_time = $this->info['shift_type'] == 'hrm' ? $this->info['unit_time'] : '';
        $max_hrs_per_day = $this->info['shift_type'] == 'hrm' ? $this->info['max_hrs_per_day'] : '';
        $avg_hrs_per_day = $this->info['shift_type'] == 'hrm' ? $this->info['avg_hrs_per_day'] : '';

        $shiftDetails = [&$this->info["Shift_Name"], &$days, &$logged_id, &$avg_hrs, &$avg_days, &$max_hrs_per_day, &$avg_hrs_per_day,
            &$unit_time, &$this->info['shift_type'], &$this->info['round_bfr_post'], &$shift_id];
        $this->db->prepare_Stmt(" UPDATE  tbl_hrm_shifts SET
                                            Shift_Name = ?,
                                            Working_Day_ID           = ?,                       
                                            Logged_ID= ? ,
                                            avg_hrs=?,
                                            avg_days=?,
                                            max_hrs_per_day=?,
                                            avg_hrs_per_day=?,
                                            unit_time=?,
                                            shift_type = ?,round_bfr_post=?
                                        WHERE 
                                            Shift_ID = ?

                                            ", $shiftDetails);

        $this->db->prepare_Stmt('delete from tbl_hrm_shift_hours where shift_id = ?', [&$shift_id]);
        $this->db->prepare_Stmt('delete from tbl_hrm_shift_rounds where shift_id = ?', [&$shift_id]);
        if (!empty($this->info['rounds'])) {
            $i = 0;
            foreach ($this->info['rounds'] as $round) {
                $day_indx = $this->info['shift_type'] == 'hrm' ? $i : $round['work_day_id'];
                if (!empty($round['time_in'])) {
                    $time_in = date("H:i:s", strtotime($round['time_in']));
                    $data_round = [&$time_in, &$round['round_in'], &$day_indx, &$shift_id];
                    $this->db->prepare_Stmt('INSERT INTO `tbl_hrm_shift_rounds`( `hr`, `round_min`,
                    `round_type`, `day_indx`, `shift_id`) values(?,?,0,?,?)', $data_round);
                }
                //
                if (!empty($round['time_out'])) {
                    $time_out = date("H:i:s", strtotime($round['time_out']));
                    $data_round = [&$time_out, &$round['round_out'], &$day_indx, &$shift_id];
                    $this->db->prepare_Stmt('INSERT INTO `tbl_hrm_shift_rounds`( `hr`, `round_min`,
                    `round_type`, `day_indx`, `shift_id`) values(?,?,1,?,?)', $data_round);
                }
                $i++;
            }
        }
        if ($this->info['shift_type'] == 'hrm') {
            if (!empty($this->info['Shift_Data']) && $this->info['unit_time'] == 1) {
                foreach ($this->info['Shift_Data'] as $shift) {
                    $item = [&$shift_id, &$shift['month'], &$shift['year'], &$shift['nb_days'],
                        &$shift['nb_hrs'], $shift['avg_hrs_day']];
                    $this->db->prepare_Stmt('INSERT INTO tbl_hrm_shift_hours
                ( shift_id, month,year,nb_days,nb_of_hours,avg_hrs_day) VALUES (?,?,?,?,?,?)', $item);
                }
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function deleteShift($shift_id) {
        $this->db->query('START TRANSACTION');
        $query = $this->db->query('select Shift_ID from tbl_hrm_employee where Shift_ID = "' . $shift_id . '"');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            $error[0] = CommonLanguage::getMessage('linked_shift_msg');
            return $error;
        }
        $this->db->prepare_Stmt('Delete from tbl_hrm_shifts WHERE Shift_ID = ?', [&$shift_id]);
        $this->db->prepare_Stmt('Delete from tbl_hrm_shift_hours WHERE shift_id = ?', [&$shift_id]);

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

    public function addHoliday() {
        $this->db->prepare_Stmt('INSERT INTO tbl_hrm_holidays
                                            (Holiday_Name,Holiday_Reason,Holiday_From_Date,Holiday_To_Date)
                                                VALUES
                                            (?,?,?,?)
                    ', [&$this->info['Holiday_Name'], &$this->info['Holiday_Reason'], &$this->info['Holiday_From_Date'], &$this->info['Holiday_To_Date']]);
        return $this->db->error();
    }

    public function updateHoliday($holiday_id) {
        $logged_id = Session::getUserID();

        $holidayDetails = [&$this->info["Holiday_Name"], &$this->info['Holiday_Reason'], &$this->info['Holiday_From_Date'], &$this->info['Holiday_To_Date'], &$holiday_id];
        $this->db->prepare_Stmt(" UPDATE  tbl_hrm_holidays SET
                                            Holiday_Name = ?,
                                            Holiday_Reason           = ?,                       
                                            Holiday_From_Date           = ?,
                                            Holiday_To_Date           = ?
                                        WHERE 
                                            Holiday_ID = ?

                                            ", $holidayDetails);
        return $this->db->error();
    }

    public function deleteHoliday($holiday_id) {
        $logged_id = Session::getUserID();
        $this->db->prepare_Stmt('Delete from tbl_hrm_holidays WHERE Holiday_ID = ?'
                , [&$holiday_id]);
        return $this->db->error();
    }

    public function viewDays($id) {
        $data = '';
        $query = $this->db->query('SELECT
                                        *
                                        FROM 
                                                tbl_hrm_working_days
                                        WHERE
                                                Working_Day_ID = "' . $id . '"
				');
        $row = $this->db->assoc($query);
        $data['Working_Day_Name'] = $row['Working_Day_Name'];
        $data['Working_Day_ID'] = $row['Working_Day_ID'];


        return $data;
    }

    public function viewShifts($id = null) {
        $condition = '';
        if (!is_null($id)) {
            $condition .= ' AND Shift_ID = "' . $id . '"';
        }
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_hrm_shifts WHERE Status_ID = 1' . $condition);
        $days = array();
        while ($row = $this->db->assoc($query)) {
            $temp = unserialize($row['Working_Day_ID']);

            for ($i = 0; $i < count($temp); $i++) {
                $days_temp = self::viewDays($temp[$i]);
                $days['Working_Day_Name'][] = CommonLanguage::getMessage($days_temp['Working_Day_Name']);
                $days['Working_Day_ID'][] = $days_temp['Working_Day_ID'];
            }
            $shift_days = implode($days['Working_Day_Name'], "-");
            $shift_daysID = implode($days['Working_Day_ID'], "-");

            $temp = '';
            $days = array();
            $row['Working_Day_Name'] = $shift_days;
            $row['Working_Day_ID'] = $shift_daysID;
            $shift_days = '';
            $data[] = $row;
        }

        return $data;
    }

    public function addTakenAccruedDays() {
        $check = CommonFunctions::check_lock_data(['type' => 'leave', 'start_date' => $this->info['Date_From'], 'end_date' => $this->info['Date_To'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $query = $this->db->query('select * from
                                            tbl_hrm_taken_accrued
                                        where 
                                        (
                                            (
                                                "' . $this->info['Date_From'] . '" >= Date_From
                                            and
                                                 "' . $this->info['Date_From'] . '" < Date_To
                                            )
                                        or
                                            (
                                                "' . $this->info['Date_To'] . '" > Date_From
                                            and
                                                 "' . $this->info['Date_To'] . '" <= Date_To
                                            )
                                        )
                                        and 
                                            Employee_ID = "' . $this->info['Employee_ID'] . '"

                ');
        $row = $this->db->assoc($query);
        if (!empty($row)) {
            $error = $this->db->error();
            $error[0] = CommonLanguage::getMessage('duplicate_entry');
            return $error;
        }
        $query = $this->db->query('select * from tbl_hrm_payroll
        where Employee_ID = "' . $this->info['Employee_ID'] . '" 
        and  ( from_date between "' . $this->info['Date_From'] . '" and "' . $this->info['Date_To'] . '"
        or Payroll_Date between "' . $this->info['Date_From'] . '" and "' . $this->info['Date_To'] . '"
        or from_date = "' . $this->info['Date_From'] . '" or from_date = "' . $this->info['Date_To'] . '"
        or Payroll_Date = "' . $this->info['Date_From'] . '" or Payroll_Date = "' . $this->info['Date_To'] . '" 
        or  "' . $this->info['Date_From'] . '" between from_date and  Payroll_Date
        or  "' . $this->info['Date_To'] . '" between from_date and  Payroll_Date )');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            return [CommonLanguage::getMessage('payroll_date_check')];
        }
//        $from_date = strtotime($this->info['Date_From']);
//        $to_date = strtotime($this->info['Date_To']);
        $days_number = CommonFunctions::calc_date_diff($this->info['Date_From'], $this->info['Date_To']) + 1;
//        echo $days_number.'==';
//        $days_number = (($to_date - $from_date) / (60 * 60 * 24)) + 1;
        $is_trs_type = ($this->info['trs_dys'] == 1 ? 1 : 0);
        if ($is_trs_type == 0)
            $available_days = self::getAvailableAccruedDays($this->info['Employee_ID'])['remain'];
        else
            $available_days = self::getAvailableTrsDays($this->info['Employee_ID'])['remain'];
        //
        if ($days_number == 1 && $this->info['is_half_day'] == 1)
            $days_number = 0.5;
        //
        if ($days_number > $available_days) {
            $error = $this->db->error();
            $error[0] = CommonLanguage::getMessage('warning_absence') . ' ' . ($is_trs_type == 0 ? '' : CommonLanguage::getMessage('delete_trs_dys')) . ' (' . CommonLanguage::getMessage('available_days') . ' ' . $available_days . ')';
            return $error;
        }
        $fid = '';
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $logged_id = Session::getUserID();
        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 = '8_ ' . $time . '__' . $this->info['Employee_ID'] . '.' . $Extension;

            $data1 = [&$this->info['Employee_ID'],
                &$FileName,
                &$today,
                &$FileName,
                &$logged_id];
            $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
				                        )
                                 VALUES   (?,8,?,?,?,?) ', $data1);
                $fid = $this->db->id();
            }
        }


        $this->db->prepare_Stmt('INSERT INTO 
                                        tbl_hrm_taken_accrued (
                                            Date_From,
                                            Date_To,
                                            Days_Number,
                                            doc_id,
                                            Employee_ID,
                                            is_trs_type,is_holiday

                                        )
                                        VALUES(?,?,?,?,?,?,?)
                ', [&$this->info['Date_From'], &$this->info['Date_To'], &$days_number, &$fid, &$this->info['Employee_ID'], &$is_trs_type, &$this->info['is_holiday']]);
        $leave_id = $this->db->id();
        $emp_arr = CommonFunctions::get_emp_absence_leave_per('ApproveLeave');
        $title = 'Accrued Leave for ' . (Employee::get_emp_name_by_id($this->info['Employee_ID']));
        foreach ($emp_arr as $emp_id) {
            $task_arr = array();
            $task_arr['emp_id'] = &$emp_id['User_ID'];
            $task_arr['task_desc'] = &$title;
            $task_arr['due_date'] = &$this->info['Date_From'];
            $task_arr['main_type'] = "leave";
            $task_arr['main_id'] = &$leave_id;
            $task_arr['task_type'] = "leave";
            CommonFunctions::insertEmpTask($task_arr);
        }
        $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 $error;
    }

    public function payAccruedDays() {
        $check = CommonFunctions::check_lock_data(['type' => 'paid_accrued', 'start_date' => $this->info['Paid_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $flag = $this->info['is_actual'];
        $this->db->query('START TRANSACTION');
        $data = [
            &$this->info['Days_Number'],
            &$this->info['Paid_Date'],
            &$this->info['Employee_ID'],
            &$this->info['Paid_Amount'],
            &$this->info['Currency_ID'],
            &$this->info['Payment_Code'],
            &$this->info[0],
            &$flag
        ];
        $this->db->prepare_Stmt("INSERT INTO 
                                        tbl_hrm_paid_accrued (
                                            Days_Number,
                                            Paid_Date,
                                            Employee_ID,
                                            Paid_Amount,
                                            Currency_ID,
                                            Payment_Code,
                                            Logged_ID,
                                            is_actual
                                        )
                                        VALUES(
                                            ?,?,?,?,?,?,?,?
                                        )
                ", $data);
//                $error = $this->db->error();die(print_r(($error)));
        $pid = $this->db->id();
        $employee_account = Employee::viewEmployeeAccount($this->info['Employee_ID']);
        $payment_code = $this->info['Payment_Code'];
        $jv = JV::returnJV($flag);
        $ref = $pid;
        $desc = 'Paid accrued days for :' . $this->info['Employee_Name'];
        $dat_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $employee_account['inc'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Paid_Amount'],
            "Transaction_Date" => $this->info['Paid_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $desc,
            "Current_Ex_Rate" => "0",
            "main_id" => $pid,
            "main_type" => "pal"
        ];
        JV::addTransaction($dat_1, $flag);
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $payment_code,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Paid_Amount'],
            "Transaction_Date" => $this->info['Paid_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $desc,
            "Current_Ex_Rate" => "0",
            "main_id" => $pid,
            "main_type" => "pal"
        ];
        //        echo"<pre>";die(print_r($dat_2));
        JV::addTransaction($dat_2, $flag);
//        $update = [&$this->info['Days_Number'], &$this->info['Employee_ID']];
//        $this->db->query("UPDATE
//                                tbl_hrm_accrued_days
//                            SET
//                                Accrued_Days = Accrued_Days - ?
//                            WHERE 
//                                Employee_ID = ?
//                     ", $update);

        $this->db->prepare_Stmt('update tbl_hrm_employee 
            set remaining_days = remaining_days - ?', [&$this->info['Days_Number']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    private function viewEmployeeAccount($id) {
        $employee_code = $this->db->query('SELECT 
                                                c.Account_Code 
                                            FROM 
                                                tbl_hrm_employees_accounts e
                                            LEFT JOIN 
                                                tbl_chart_of_accounts c
                                            ON 
                                                c.Account_ID = e.Account_ID   
                                            WHERE 
                                                e.Employee_ID = "' . $id . '"');
        $emp_code = $this->db->assoc($employee_code);
        if (count($emp_code) > 0)
            $emp_code_val = $emp_code['Account_Code'];
        else
            $emp_code_val = 6301;
        return $emp_code_val;
    }

    public function getAvailableAccruedDays($employee_id) {
        $today = Date::getToday();
        //employee after one year to allow start taking his vacations, but can't take more than 2 years
        $before_year = date('Y-m-d', strtotime('-1 year'));
        $before_2_years = date('Y-m-d', strtotime('-2 years'));
        $start_calc_date = 'if(Employee_StartDate > "' . $before_2_years . '", Employee_StartDate, "' . $before_2_years . '")';

        $query = $this->db->query('select *,(TIMESTAMPDIFF(MONTH, ' . $start_calc_date . ',"' . $today . '")) as accrued_month
            from tbl_hrm_employee WHERE Employee_ID = "' . $employee_id . '"');
        $row = $this->db->assoc($query);
        $taken = 0;
        $year = date('Y');
        $start_year = smc_session()['default_setting']['emp_accrued_day_start_work'] == 1 ? $row['Employee_StartDate'] : $year . '-01-01';
        $end_year = date("Y-m-d", strtotime($start_year . " +1 year -1 day"));

        //we don't put it in query since if employee '1', and not before 1 year we need to get taken days if there is
        if ($row['IS_Employee'] == '1') {
            // (SUM(DATEDIFF(Date_To, Date_From))+1) AS sum_taken 
            $query2 = $this->db->query('SELECT Date_From,Date_To
                                        FROM  tbl_hrm_taken_accrued WHERE Employee_ID = "' . $employee_id . '" 
                                            and is_approved = "1" and is_trs_type = 0
                                            and (Date_From between "' . $start_year . '" and "' . $end_year . '" 
                                                or Date_To between "' . $start_year . '" and "' . $end_year . '")');
            while ($row2 = $this->db->assoc($query2)) {
                $current_start = $row2['Date_From'];
                $current_end = $row2['Date_To'];
                if ($row2['Date_From'] < $start_year) {
                    $current_start = $start_year;
                }
                if ($row2['Date_To'] > $end_year) {
                    $current_end = $end_year;
                }
                $taken+=(CommonFunctions::calc_date_diff($current_start, $current_end) + 1);
            }
            $query3 = $this->db->query('SELECT SUM(Days_Number) AS sum_paid FROM  tbl_hrm_paid_accrued 
                                        WHERE Employee_ID = "' . $employee_id . '" 
                                            and Paid_Date between "' . $start_year . '" and "' . $end_year . '"');
            $row3 = $this->db->assoc($query3);
            if (!empty($row3['sum_paid']))
                $taken+=$row3['sum_paid'];
        }
        if (smc_session()['default_setting']['activate_dynamic_accrued_days'] == 1) {
            $total_days = !empty($row['leave_days_nb']) ? $row['leave_days_nb'] : smc_session()['default_setting']['default_nb_leave'];
            $total_days += $row['holiday_is_fixed'] == 1 ? $row['holiday_nb'] : 0;
            $total_days+= $row['previous_days_nb'];
            $remaining = $row['remaining_days'];
        } else {
            $total_days = 0;
            if ($start_year <= $before_year) {
                $total_days = (smc_session()['default_setting']['default_nb_leave'] / 12) * $row['accrued_month'];
            }
            $remaining = $total_days - $taken;
        }
        return array('remain' => $remaining, 'total' => $total_days, 'taken' => $taken);
    }

    public static function getAvailableTrsDays($employee_id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->prepare_Stmt('Select sum(transfer_day_number) as trs_dys from tbl_employee_transfer_day where employee_id = ?', [&$employee_id]);
        $row = $db->assoc($query);
        $row['trs_dys'] = (isset($row['trs_dys']) && $row['trs_dys'] > 0 ? $row['trs_dys'] : 0);
        ///    
        $query2 = $db->query('SELECT SUM(Days_Number) AS sum_taken FROM  tbl_hrm_taken_accrued 
                WHERE Employee_ID = "' . $employee_id . '"  and is_approved = "1" and is_trs_type = 1 ');
        $row2 = $db->assoc($query2);
        $row2['sum_taken'] = (isset($row2['sum_taken']) && $row2['sum_taken'] > 0 ? $row2['sum_taken'] : 0);
        //
        $available_trs_days = $row['trs_dys'] - $row2['sum_taken'];
        return array('remain' => $available_trs_days, 'total' => $row['trs_dys'], 'taken' => $row2['sum_taken']);
    }

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

    public function viewShiftsDetails($id) {
        $data = array();
        $query = $this->db->query('SELECT * From tbl_hrm_shifts Where Shift_ID = "' . $id . '"');
        $row = $this->db->assoc($query);
        $data = $row;
        if ($row['shift_type'] == 'fx') {
            $data['Work_Day'] = json_decode($row['Working_Day_ID'], true);
        } else {
            $query_hrs = $this->db->query('select * from tbl_hrm_shift_hours where shift_id = "' . $id . '"');
            while ($row_hrs = $this->db->assoc($query_hrs))
                $data['Work_Day'][] = $row_hrs;
            $query_hrs = $this->db->query('select * from tbl_hrm_shift_rounds
                where shift_id = "' . $id . '" order by entry_id asc');
            while ($row_hrs = $this->db->assoc($query_hrs)) {
                $round_indx = 'round_' . ($row_hrs['round_type'] == 0 ? 'in' : 'out');
                $hr_indx = 'hr_' . ($row_hrs['round_type'] == 0 ? 'in' : 'out');
                $row_hrs[$round_indx] = $row_hrs['round_min'];
                $row_hrs[$hr_indx] = $row_hrs['hr'];
                if (isset($data['rounds'][$row_hrs['day_indx']])) {
                    $data['rounds'][$row_hrs['day_indx']][$round_indx] = $row_hrs[$round_indx];
                    $data['rounds'][$row_hrs['day_indx']][$hr_indx] = $row_hrs[$hr_indx];
                } else
                    $data['rounds'][$row_hrs['day_indx']] = $row_hrs;
            }
        }
        //        echo'<pre>';die(print_r($data));
        //        $data['Shift_ID'] = $row['Shift_ID'];
        //        $data['Shift_Name'] = $row['Shift_Name'];
        //        $array = array_merge($data, $work_day);
        return $data;
    }

    public function searchshift($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND Shift_Name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    Shift_ID as id,
                                    Shift_Name as text
                                from 
                                    tbl_hrm_shifts
                                    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 viewHoliday() {
        $data = array();
        $query = $this->db->query('SELECT Holiday_Name AS title,Holiday_From_Date as start
            ,Holiday_To_Date as end from tbl_hrm_holidays');

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

        return $data;
    }

    public function viewAccruedLeave($id = '', $is_actual = 0) {
        $data = array();
        $cond = '';
        if (!empty($id)) {
            $cond .= ' and e.Employee_ID = "' . smc_escape($id) . '"';
        }
        if ($is_actual == 0) {
            $cond .= ' and e.Is_Hidden = 0 ';
        }

        $query = $this->db->query('SELECT d.Document_File_Name,a.Date_From AS start ,a.Date_To AS end,
            a.Employee_ID,a.is_approved as color,
            concat(e.Employee_FirstName," ",e.Employee_MiddleName," ",e.Employee_LastName," <-> Accrued") as title
            FROM tbl_hrm_taken_accrued a left join tbl_hrm_employee e on a.Employee_ID = e.Employee_ID 
            left join tbl_hrm_employee_document d on d.Entry_ID = a.doc_id  where a.is_approved = 1 ' . $cond);
        while ($row = $this->db->assoc($query)) {
            if ($row['color'] == 1) {
                $row['color'] = "#409127";
            } else if ($row['color'] == 2) {
                $row['color'] = "#f90000";
            } else {
                $row['color'] = "#f9cb00";
            }
            $data[] = $row;
        }
        return $data;
    }

    public function approveAccruedRequest($other = false) {
        if (!$other)
            $this->db->query('START TRANSACTION');
        $this->db->query("START TRANSACTION");
        $status = $this->info['status'];
        if ($status == 1) {
            $query = $this->db->query('select * from tbl_hrm_taken_accrued  where Entry_ID = "' . $this->info['id'] . '"');
            $row = $this->db->assoc($query);
            $is_trs_dys = $row['is_trs_type'];
            if ($is_trs_dys == 0) {
                $available_days = self::getAvailableAccruedDays($row['Employee_ID'])['remain'];
            } else {
                $available_days = self::getAvailableTrsDays($row['Employee_ID'])['remain'];
            }
            $days_number = $row['Days_Number'];
            if ($days_number > $available_days) {
                $error = $this->db->error();
                $error[0] = CommonLanguage::getMessage('warning_absence') . ' ' . ($is_trs_dys == 0 ? '' : CommonLanguage::getMessage('delete_trs_dys')) . ' (' . CommonLanguage::getMessage('available_days') . ' ' . $available_days . ')';
                return $error;
            }
            $this->db->prepare_Stmt('update tbl_hrm_employee set remaining_days = remaining_days - ? where Employee_ID = ?', [&$days_number, &$row['Employee_ID']]);
        }
        $this->db->prepare_Stmt('update tbl_hrm_taken_accrued set is_approved = ? where Entry_ID = ?', [&$status, &$this->info['id']]);
        CommonFunctions::update_task_status($this->info['id'], 'leave');
        $error = $this->db->error();
        if (!$other) {
            if (empty($error)) {
                $this->db->query('COMMIT');
            } else {
                $this->db->query('ROLLBACK');
            }
        }
        return $error;
    }

    public function submitMultiApproveAccrued() {
        $this->db->query("START TRANSACTION");
        foreach ($this->info['employee_accrued'] as $row) {
            $data = ['status' => 1, 'id' => $row];
            $this->setData($data)->approveAccruedRequest(true);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function add_edit_salary_formula() {
        if ($this->info['is_edit'] == 0) {
            $data = [
                &$this->info['formula_name'],
                &$this->info['hr_price_formula'],
                &$this->info['deduction_rate'],
                &$this->info['overtime_rate']
            ];
            $this->db->prepare_Stmt('insert into `tbl_hrm_salary_unit_formulas`
                ( `formula_name`, `hr_price_formula`, `deduction_rate`, `overtime_rate`) 
                values (?,?,?,?)', $data);
        } else {
            $data = [
                &$this->info['formula_name'],
                &$this->info['hr_price_formula'],
                &$this->info['deduction_rate'],
                &$this->info['overtime_rate'],
                &$this->info['formula_id'],
            ];
            $this->db->prepare_Stmt('update `tbl_hrm_salary_unit_formulas`
                set `formula_name`=?, `hr_price_formula`=?, `deduction_rate`=?,
                `overtime_rate`=? where formula_id =?', $data);
        }
        $error = $this->db->error();
        return $error;
    }

    public function add_edit_salary_periods() {
        $this->db->query('START TRANSACTION');
        $bs_worked_hrs = (!empty($this->info['bs_worked_hrs']) && $this->info['bs_worked_hrs'] === 'on') ? 1 : 0;
        if ($this->info['is_edit'] == 0) {
            $data = [
                &$this->info['period_name'],
                &$this->info['pay_period'],
                &$this->info['unit_time'],
                &$this->info['basic_salary'],
                &$this->info['Currency_ID'],
                &$this->info['formula_id'],
                &$bs_worked_hrs
            ];
            $this->db->prepare_Stmt('insert into `tbl_hrm_basic_salary_periods`
                ( `period_name`, `pay_period`, `unit_time`, `basic_salary`,Currency_ID,formula_id,bs_worked_hrs) 
                values (?,?,?,?,?,?,?)', $data);
        } else {
            $data = [
                &$this->info['period_name'],
                &$this->info['pay_period'],
                &$this->info['unit_time'],
                &$this->info['basic_salary'],
                &$this->info['Currency_ID'],
                &$this->info['formula_id'],
                &$bs_worked_hrs,
                &$this->info['period_id']
            ];
            $this->db->prepare_Stmt('update `tbl_hrm_basic_salary_periods`
                set `period_name`=?, `pay_period`=?, `unit_time`=?,
                `basic_salary`=?,Currency_ID = ?,formula_id=?,bs_worked_hrs=? where period_id =?', $data);

            if (!empty($this->info['update_emp']) && $this->info['update_emp'] === 'on') {
                $this->db->prepare_Stmt('update tbl_hrm_employee set basic_salary = ?,basic_salary_currency=?
                    where period_id = ?', [&$this->info['basic_salary'], &$this->info['Currency_ID'], &$this->info['period_id']]);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_default_salary_formula() {
        $data = array();
        $query = $this->db->query('select * from tbl_hrm_salary_unit_formulas where is_default = 1');
        $row = $this->db->assoc($query);
        if (!is_null($row))
            $data = $row;
        return $data;
    }

    public static function get_payperiod_by_id($id) {
        switch ($id) {
            case 0:
                return CommonLanguage::getMessage('weekly');
                break;
            case 1:
                return CommonLanguage::getMessage('monthly');
            case 2:
                return CommonLanguage::getMessage('daily');
    
                break;
        }
    }

    public static function get_unittime_by_id($id) {
        switch ($id) {
            case 0:
                return CommonLanguage::getMessage('hour');
                break;
            case 1:
                return CommonLanguage::getMessage('month');
            case 2:
                return CommonLanguage::getMessage('day');
    
                break;
        }
    }

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

    public function delete_accrued_leave($id) {
        $old_query = $this->db->query('select a.*,d.Document_File_Name,e.Is_Hidden from tbl_hrm_taken_accrued a left join
            tbl_hrm_employee_document d on d.Entry_ID = a.doc_id
            join tbl_hrm_employee e on e.Employee_ID = a.Employee_ID
            where a.Entry_ID = "' . $id . '"');
        $row = $this->db->assoc($old_query);
        $check = CommonFunctions::check_lock_data(['type' => 'leave', 'start_date' => $row['Date_From'], 'end_date' => $row['Date_To'], 'is_actual' => $row['Is_Hidden']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $query = $this->db->query('select * from tbl_hrm_payroll
        where Employee_ID = "' . $row['Employee_ID'] . '" 
        and  ( from_date between "' . $row['Date_From'] . '" and "' . $row['Date_To'] . '"
        or Payroll_Date between "' . $row['Date_From'] . '" and "' . $row['Date_To'] . '"
        or from_date = "' . $row['Date_From'] . '" or from_date = "' . $row['Date_To'] . '"
        or Payroll_Date = "' . $row['Date_From'] . '" or Payroll_Date = "' . $row['Date_To'] . '" 
        or  "' . $row['Date_From'] . '" between from_date and  Payroll_Date
        or  "' . $row['Date_To'] . '" between from_date and  Payroll_Date )');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            return [CommonLanguage::getMessage('payroll_accrued_overlap')];
        }
        if ($row['is_approved'] == 1) {
            $this->db->prepare_Stmt('update tbl_hrm_employee set remaining_days = remaining_days + ? where Employee_ID = ?', [&$row['Days_Number'], &$row['Employee_ID']]);
        }
        if ($row['doc_id'] != 0) {
            $this->db->prepare_Stmt('DELETE FROM tbl_hrm_employee_document where Entry_ID = "' . $row['doc_id'] . '"');
        }
        $this->db->prepare_Stmt('DELETE FROM `tbl_hrm_taken_accrued` where Entry_ID = "' . $id . '"');
        $error = $this->db->error();
        if (empty($error) && $row['doc_id'] != 0) {
            chmod(upload_path . 'Documents/' . $row['Document_File_Name'], 0777);

            unlink(upload_path . 'Documents/' . $row['Document_File_Name']);
        }
        return $error;
    }

    public function delete_absence($id) {

        $old_query = $this->db->query('select a.*,d.*,e.Is_Hidden from tbl_hrm_absence a left join
            tbl_hrm_employee_document d on d.Entry_ID = a.doc_id
            join tbl_hrm_employee e on e.Employee_ID = a.Employee_ID
            where Absence_ID = "' . $id . '"');
        $row = $this->db->assoc($old_query);
        $check = CommonFunctions::check_lock_data(['type' => 'absence', 'start_date' => $row['Absence_From_Date'], 'end_date' => $row['Absence_To_Date'], 'is_actual' => $row['Is_Hidden']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $query = $this->db->query('select * from tbl_hrm_payroll
        where Employee_ID = "' . $row['Employee_ID'] . '" 
        and  ( from_date between "' . $row['Absence_From_Date'] . '" and "' . $row['Absence_To_Date'] . '"
        or Payroll_Date between "' . $row['Absence_From_Date'] . '" and "' . $row['Absence_To_Date'] . '"
        or from_date = "' . $row['Absence_From_Date'] . '" or from_date = "' . $row['Absence_To_Date'] . '"
        or Payroll_Date = "' . $row['Absence_From_Date'] . '" or Payroll_Date = "' . $row['Absence_To_Date'] . '" 
        or  "' . $row['Absence_From_Date'] . '" between from_date and  Payroll_Date
        or  "' . $row['Absence_To_Date'] . '" between from_date and  Payroll_Date )');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            return [CommonLanguage::getMessage('payroll_absence_overlap')];
        }
        if ($row['doc_id'] != 0) {
            $this->db->prepare_Stmt('DELETE FROM tbl_hrm_employee_document where Entry_ID = "' . $row['doc_id'] . '"');
        }
        $this->db->prepare_Stmt('DELETE FROM `tbl_hrm_absence` where Absence_ID = "' . $id . '"');
        $error = $this->db->error();
        if (empty($error) && $row['doc_id'] != 0) {
            chmod(upload_path . 'Documents/' . $row['Document_File_Name'], 0777);

            unlink(upload_path . 'Documents/' . $row['Document_File_Name']);
        }
        return $error;
    }

    public function view_leave_by_id($id) {
        $query = $this->db->query('select a.*, 
            concat(e.Employee_FirstName," ",e.Employee_MiddleName," ",e.Employee_LastName) as Employee_Name 
            from tbl_hrm_taken_accrued a join tbl_hrm_employee e
           on e.Employee_ID = a.Employee_ID where a.Entry_ID = "' . $id . '" ');
        $data = $this->db->assoc($query);
        return $data;
    }

    public function edit_leave() {
        $check = CommonFunctions::check_lock_data(['type' => 'leave', 'start_date' => $this->info['Date_From'], 'end_date' => $this->info['Date_To'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $query = $this->db->query('select * from
                                            tbl_hrm_taken_accrued
                                        where 
                                        (
                                            (
                                                "' . $this->info['Date_From'] . '" >= Date_From
                                            and
                                                 "' . $this->info['Date_From'] . '" < Date_To
                                            )
                                        or
                                            (
                                                "' . $this->info['Date_To'] . '" > Date_From
                                            and
                                                 "' . $this->info['Date_To'] . '" <= Date_To
                                            )
                                        )
                                        and 
                                            Employee_ID = "' . $this->info['Employee_ID'] . '" and Entry_ID <> "' . $this->info['id'] . '"

                ');
        $row = $this->db->assoc($query);
        if (!empty($row)) {
            $error = $this->db->error();
            $error[0] = CommonLanguage::getMessage('duplicate_entry');
            return $error;
        }
        $query = $this->db->query('select * from tbl_hrm_payroll
        where Employee_ID = "' . $this->info['Employee_ID'] . '" 
        and  ( from_date between "' . $this->info['Date_From'] . '" and "' . $this->info['Date_To'] . '"
        or Payroll_Date between "' . $this->info['Date_From'] . '" and "' . $this->info['Date_To'] . '"
        or from_date = "' . $this->info['Date_From'] . '" or from_date = "' . $this->info['Date_To'] . '"
        or Payroll_Date = "' . $this->info['Date_From'] . '" or Payroll_Date = "' . $this->info['Date_To'] . '" 
        or  "' . $this->info['Date_From'] . '" between from_date and  Payroll_Date
        or  "' . $this->info['Date_To'] . '" between from_date and  Payroll_Date )');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            return [CommonLanguage::getMessage('payroll_date_check')];
        }
//        $from_date = strtotime($this->info['Date_From']);
//        $to_date = strtotime($this->info['Date_To']);
        $days_number = CommonFunctions::calc_date_diff($this->info['Date_From'], $this->info['Date_To']) + 1; //(($to_date - $from_date) / (60 * 60 * 24)) + 1;
        $is_trs_type = ($this->info['trs_dys'] == 1 ? 1 : 0);
        if ($is_trs_type == 0)
            $available_days = self::getAvailableAccruedDays($this->info['Employee_ID'])['remain'];
        else
            $available_days = self::getAvailableTrsDays($this->info['Employee_ID'])['remain'];
        //
        if ($days_number == 1 && $this->info['is_half_day'] == 1)
            $days_number = 0.5;
        //
        if ($days_number > $available_days) {
            $error = $this->db->error();
            $error[0] = CommonLanguage::getMessage('warning_absence') . ' ' . ($is_trs_type == 0 ? '' : CommonLanguage::getMessage('delete_trs_dys')) . ' (' . CommonLanguage::getMessage('available_days') . ' ' . $available_days . ')';
            return $error;
        }
        $fid = '';
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $logged_id = Session::getUserID();
        if ($_FILES['Document_File_Name']['name'] !== '') {
            $old_query = $this->db->query('select * from tbl_hrm_taken_accrued a left join
            tbl_hrm_employee_document d on d.Entry_ID = a.doc_id
            where a.Entry_ID = "' . $this->info['id'] . '"');
            $row = $this->db->assoc($old_query);

            if ($row['doc_id'] != 0) {
                $this->db->prepare_Stmt('DELETE FROM tbl_hrm_employee_document where Entry_ID = "' . $row['doc_id'] . '"');
            }
            $error = $this->db->error();
            if (empty($error) && $row['doc_id'] != 0) {
                chmod(upload_path . 'Documents/' . $row['Document_File_Name'], 0777);

                unlink(upload_path . 'Documents/' . $row['Document_File_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 = '8_ ' . $time . '__' . $this->info['Employee_ID'] . '.' . $Extension;

            $data1 = [&$this->info['Employee_ID'],
                &$FileName,
                &$today,
                &$FileName,
                &$logged_id];
            $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
				                        )
                                 VALUES   (?,8,?,?,?,?) ', $data1);
                $fid = $this->db->id();
            }
        }


        $this->db->prepare_Stmt('update 
                                        tbl_hrm_taken_accrued set
                                            Date_From=?,
                                            Date_To=?,
                                            Days_Number=?,
                                            doc_id=?,
                                            Employee_ID=?,
                                            is_trs_type=?,is_holiday=?
                                            where Entry_ID = ?
                ', [&$this->info['Date_From'], &$this->info['Date_To'], &$days_number, &$fid, &$this->info['Employee_ID'], &$is_trs_type, $this->info['is_holiday'], &$this->info['id']]);

        $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 $error;
    }

    public function manage_emp_leaves() {
        $data = array();
        $permissions = new Permissions(Session::getUserID());
        $cond = ' ';
        if (!empty($this->info['dep_id']))
            $cond.= ' and Branch_ID = "' . $this->info['dep_id'] . '" ';
        if (!empty($this->info['job_id']))
            $cond.=' and Job_ID = "' . $this->info['job_id'] . '" ';
        if (!empty($this->info['shift_id']))
            $cond.=' and Shift_ID = "' . $this->info['shift_id'] . '" ';
        $query = $this->db->query('select Employee_ID as emp,Employee_Work_ID,
            concat(Employee_FirstName," ",Employee_MiddleName," ",Employee_LastName) as Employee_Name,
            Employee_ID,leave_days_nb,holiday_nb,holiday_is_fixed,previous_days_nb,remaining_days 
            from tbl_hrm_employee where 1=1 ' . $cond);
        while ($row = $this->db->assoc($query)) {
            if ($permissions->hasPermission('change_fixed_holiday')) {
                $data[] = array(
                    $row['Employee_Name'],
                    '<input type="number" data-id="' . $row['Employee_ID'] . '" class="form-control previous text-center" value="' . $row['previous_days_nb'] . '" step="any" min="0"/>',
                    '<input type="checkbox" data-id="' . $row['Employee_ID'] . '" class="is_fixed" ' . ($row['holiday_is_fixed'] == 1 ? 'checked' : '') . '/>',
                    '<input type="number" data-id="' . $row['Employee_ID'] . '" class="form-control holiday text-center" value="' . $row['holiday_nb'] . '" ' . ($row['holiday_is_fixed'] == 1 ? '' : 'readonly') . ' step="any" min="0"/>',
                    '<input type="number" data-id="' . $row['Employee_ID'] . '" class="form-control leave text-center" value="' . $row['leave_days_nb'] . '" step="any" min="0"/>',
                    '<input type="number" data-id="' . $row['Employee_ID'] . '" class="form-control remaining text-center" readonly value="' . $row['remaining_days'] . '" step="any" min="0"/>',
                );
            } else {
                $data[] = array(
                    $row['Employee_Name'],
                    '<input type="number" data-id="' . $row['Employee_ID'] . '" class="form-control previous text-center" value="' . $row['previous_days_nb'] . '" step="any" min="0"/>',
                    '<input type="number" data-id="' . $row['Employee_ID'] . '" class="form-control leave text-center" value="' . $row['leave_days_nb'] . '" step="any" min="0"/>',
                    '<input type="number" data-id="' . $row['Employee_ID'] . '" class="form-control remaining text-center" readonly value="' . $row['remaining_days'] . '" step="any" min="0"/>',
                );
            }
        }
        return $data;
    }

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

    public function edit_paid_accrued() {
        $check = CommonFunctions::check_lock_data(['type' => 'paid_accrued', 'start_date' => $this->info['Paid_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $flag = $this->info['is_actual'];
        $this->db->query('START TRANSACTION');
        //
        $query = $this->db->query('select JV_ID from tbl_accounting_transactions 
            where main_type="pal" and main_id="' . $this->info['paid_acc_id'] . '"');
        $jv_id = $this->db->assoc($query)['JV_ID'];
        //
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions
            where main_type="pal" and main_id="' . $this->info['paid_acc_id'] . '"');
        $query = $this->db->query('select Days_Number from tbl_hrm_paid_accrued 
            where Paid_Accrued_ID="' . $this->info['paid_acc_id'] . '"');
        $old_days = $this->db->assoc($query)['Days_Number'];
        //
        $logged_id = smc_session()['user_logged_id'];

        $data = [
            &$this->info['Days_Number'],
            &$this->info['Paid_Date'],
            &$this->info['Employee_ID'],
            &$this->info['Paid_Amount'],
            &$this->info['Currency_ID'],
            &$this->info['Payment_Code'],
            &$logged_id,
            &$this->info['paid_acc_id']
        ];
        $this->db->prepare_Stmt("update tbl_hrm_paid_accrued 
                                        set Days_Number=?,
                                            Paid_Date=?,
                                            Employee_ID=?,
                                            Paid_Amount=?,
                                            Currency_ID=?,
                                            Payment_Code=?,
                                            Logged_ID=? where Paid_Accrued_ID = ?
                                        
                ", $data);
        //        $error = $this->db->error();die(print_r(($error)));
        $pid = $this->info['paid_acc_id'];
        $employee_account = Employee::viewEmployeeAccount($this->info['Employee_ID']);
        $payment_code = $this->info['Payment_Code'];
        $jv = empty($jv_id) ? JV::returnJV($flag) : $jv_id;
        $ref = $pid;
        $desc = 'Paid accrued days for :' . $this->info['Employee_Name'];
        $dat_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $employee_account['inc'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Paid_Amount'],
            "Transaction_Date" => $this->info['Paid_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "Transaction_Description" => $desc,
            "Current_Ex_Rate" => "0",
            "main_id" => $pid,
            "main_type" => "pal"
        ];
        JV::addTransaction($dat_1, $flag);
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $payment_code,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Paid_Amount'],
            "Transaction_Date" => $this->info['Paid_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "Transaction_Description" => $desc,
            "Current_Ex_Rate" => "0",
            "main_id" => $pid,
            "main_type" => "pal"
        ];
        //        echo"<pre>";die(print_r($dat_2));
        JV::addTransaction($dat_2, $flag);
//        $update = [&$this->info['Days_Number'], &$this->info['Employee_ID']];
//        $this->db->query("UPDATE
//                                tbl_hrm_accrued_days
//                            SET
//                                Accrued_Days = Accrued_Days - ?
//                            WHERE 
//                                Employee_ID = ?
//                     ", $update);
        $day_nb = $this->info['Days_Number'] - $old_days;
        $this->db->prepare_Stmt('update tbl_hrm_employee 
            set remaining_days = remaining_days - ?', [&$day_nb]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

}
