<?php

class Absence {

    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 create() {
          $check = CommonFunctions::check_lock_data(['type' => 'absence', 'start_date' => $this->info['Absence_From_Date'],'end_date' => $this->info['Absence_To_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $query = $this->db->query('select * from
                                            tbl_hrm_absence
                                        where 
                                        (
                                            (
                                                "' . $this->info['Absence_From_Date'] . '" >= Absence_From_Date
                                            and
                                                 "' . $this->info['Absence_From_Date'] . '" < Absence_To_Date
                                            )
                                        or
                                            (
                                                "' . $this->info['Absence_To_Date'] . '" > Absence_From_Date
                                            and
                                                 "' . $this->info['Absence_To_Date'] . '" <= Absence_To_Date
                                            )
                                            )
                                        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['Absence_From_Date'] . '" and "' . $this->info['Absence_To_Date'] . '"
        or Payroll_Date between "' . $this->info['Absence_From_Date'] . '" and "' . $this->info['Absence_To_Date'] . '"
        or from_date = "' . $this->info['Absence_From_Date'] . '" or from_date = "' . $this->info['Absence_To_Date'] . '"
        or Payroll_Date = "' . $this->info['Absence_From_Date'] . '" or Payroll_Date = "' . $this->info['Absence_To_Date'] . '" 
        or  "' . $this->info['Absence_From_Date'] . '" between from_date and  Payroll_Date
        or  "' . $this->info['Absence_To_Date'] . '" between from_date and  Payroll_Date )');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            return [CommonLanguage::getMessage('payroll_date_check')];
        }

        $check_absence = self::checkAbsenceDay($this->info['Employee_ID'], $this->info['Absence_Reason']);
//        die(print_r($check_absence));
        if (!empty($check_absence)) {
            return [$check_absence[0]];
        }


        //        echo"<pre>";
        //        die(print_r($_FILES));
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $fid = '';
        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 = '7_ ' . $time . '__' . $this->info['Employee_ID'] . '.' . $Extension;

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

        $data = [&$this->info['Absence_From_Date'],
            &$this->info['Absence_To_Date'],
            &$this->info['Absence_Reason'],
            &$this->info['Employee_ID'],
            &$fid,
            &$this->info[0]
        ];
        $this->db->prepare_Stmt('INSERT INTO 
                                            tbl_hrm_absence (
                                                            Absence_From_Date,
                                                            Absence_To_Date,
                                                            Absence_Reason,
                                                            Employee_ID,
                                                            doc_id,
                                                            Logged_ID
                                                    )VALUES (?,?,?,?,?,?)', $data);
        //        $Fileextension = $_FILES['Document_File_Name']['name'];
        //        $SaveAsExtention = explode(".", $Fileextension);
        $absence_id = $this->db->id();
        $emp_arr = CommonFunctions::get_emp_absence_leave_per('ApproveLeave');
        $title = 'Absence 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['Absence_From_Date'];
            $task_arr['main_type'] = "absence";
            $task_arr['main_id'] = &$absence_id;
            $task_arr['task_type'] = "absence";
            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 array_unique($error);
    }

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

    public function viewAbsenceReasons($id = null) {

        $conditions = '';

        if (!is_null($id)) {

            $conditions .= ' WHERE   Absence_Reason_ID = "' . $id . '" ';
        }

        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_hrm_absence_reasons' . $conditions);
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function addAbsenceReasons() {
        foreach ($this->info['Absence_Reason'] as $item) {
            $data = [&$item['Absence_Reason_Name'], &$item['Absence_Reason_Days'],
                &$item['exceed_days'], &$item['is_holiday']];
            $this->db->prepare_Stmt('INSERT INTO tbl_hrm_absence_reasons ( Absence_Reason_Name,
                Absence_Reason_Days,exceed_days,is_holiday) VALUES(?,?,?,?)', $data);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function updateAbsenceReason() {
        $data = [&$this->info['Absence_Reason'][0]['Absence_Reason_Name'],
            &$this->info['Absence_Reason'][0]['Absence_Reason_Days'],
            &$this->info['Absence_Reason'][0]['exceed_days'],
            &$this->info['Absence_Reason'][0]['is_holiday'],
            &$this->info['Absence_Reason_ID']];
        $this->db->prepare_Stmt('UPDATE 
                                        tbl_hrm_absence_reasons 
                                    SET
                                        Absence_Reason_Name = ?,
                                        Absence_Reason_Days = ?,
                                        exceed_days = ?,
                                        is_holiday = ?
                                    WHERE 
                                        Absence_Reason_ID = ?
                    ', $data);
        $error = $this->db->error();
        array_unique($error);
    }

    public function deleteAbsenceReason($id) {
        $data = [&$id];
        $this->db->prepare_Stmt('DELETE  FROM 
                                        tbl_hrm_absence_reasons 

                                    WHERE 
                                        Absence_Reason_ID = ?
                    ', $data);
        $error = $this->db->error();
        return array_unique($error);
    }

    public function approveAbsenceRequest($other = false) {
        //        echo"<pre>";die(print_r($this->info));
        if (!$other)
            $this->db->query("START TRANSACTION");
        $this->db->prepare_Stmt('update tbl_hrm_absence set is_approved = ? 
            where Absence_ID = ?', [&$this->info['status'], &$this->info['id']]);
        CommonFunctions::update_task_status($this->info['id'], 'absence');
        $error = $this->db->error();
        if (!$other) {
            if (empty($error)) {
                $this->db->query('COMMIT');
            } else {
                $this->db->query('ROLLBACK');
            }
        }
        return $error;
    }

    public function submitMultiApproveAbsence() {
        $this->db->query("START TRANSACTION");
        $status_id = $this->info['status_id'];
        foreach ($this->info['employee_absence'] as $row) {
            //             echo"<pre>";die(print_r($this->info));
            $data = ['status' => $status_id, 'id' => $row];
            $this->setData($data)->approveAbsenceRequest(true);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function approveRejectOvertime() {
        $this->db->query("START TRANSACTION");
        $this->db->prepare_Stmt('update tbl_hrm_employee_overtime_hours set is_approved = ? where Overtime_ID = ?', [&$this->info['status'], &$this->info['id']]);
        CommonFunctions::update_task_status($this->info['id'], $this->info['type']);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    /**
     * 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 viewAbsence($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,r.Absence_Reason_Name,
            a.Absence_From_Date AS start ,a.Absence_To_Date AS end,a.Absence_Reason,
            a.Employee_ID,a.is_approved as color,
            concat(e.Employee_FirstName," ",e.Employee_MiddleName," ",e.Employee_LastName," <-> ",r.Absence_Reason_Name) as title
            FROM tbl_hrm_absence a left join tbl_hrm_absence_reasons r on a.Absence_Reason = r.Absence_Reason_ID 
            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 checkAbsenceDay($emp_id, $res_id) {
        $day = 0;
        $query_absence = $this->db->query('select DATEDIFF(Absence_To_Date,Absence_From_Date) as day_absence
            from
                                            tbl_hrm_absence 
                                   where
                                          Employee_ID = "' . $emp_id . '"
                                   and 
                                          Absence_Reason = "' . $res_id . '" 
                                   and  
                                          is_approved = 1 ');

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

            $day += $row['day_absence'];
        }
        $query_emp = $this->db->query('select absence_type,Job_ID from tbl_hrm_employee where Employee_ID = "' . $emp_id . '" ');
        $row_emp = $this->db->assoc($query_emp);
        $absence_type = $row_emp['absence_type'];
        $job_id = $row_emp['Job_ID'];
        $type_id = $absence_type == 2 ? $emp_id : ($absence_type == 3 ? $job_id : '');
        $query_check = self::get_absence_by_type($absence_type, $type_id, $res_id);
//        echo"<pre>";die(print_r($query_check));
        $absence_days = $query_check[0]['nb_days'];
//echo $absence_days.'=='.$day;
        if ($day >= $absence_days && $query_check[0]['exceed_days'] == 0) {
            $error = $this->db->error();
            $error[0] = CommonLanguage::getMessage('warning_absence') . ' (' . CommonLanguage::getMessage('day_number') . ' ' . $day . ')';
            return $error;
        }
        $error = $this->db->error();
        return $error;
    }

    public function view_absence_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_absence a join tbl_hrm_employee e
           on e.Employee_ID = a.Employee_ID where a.Absence_ID = "' . $id . '" ');
        $data = $this->db->assoc($query);
        return $data;
    }

    public function edit_absence() {
          $check = CommonFunctions::check_lock_data(['type' => 'absence', 'start_date' => $this->info['Absence_From_Date'],'end_date' => $this->info['Absence_To_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $query = $this->db->query('select * from
                                            tbl_hrm_absence
                                        where 
                                        (
                                            (
                                                "' . $this->info['Absence_From_Date'] . '" >= Absence_From_Date
                                            and
                                                 "' . $this->info['Absence_From_Date'] . '" < Absence_To_Date
                                            )
                                        or
                                            (
                                                "' . $this->info['Absence_To_Date'] . '" > Absence_From_Date
                                            and
                                                 "' . $this->info['Absence_To_Date'] . '" <= Absence_To_Date
                                            )
                                            )
                                        and 
                                            Employee_ID = "' . $this->info['Employee_ID'] . '" and Absence_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['Absence_From_Date'] . '" and "' . $this->info['Absence_To_Date'] . '"
        or Payroll_Date between "' . $this->info['Absence_From_Date'] . '" and "' . $this->info['Absence_To_Date'] . '"
        or from_date = "' . $this->info['Absence_From_Date'] . '" or from_date = "' . $this->info['Absence_To_Date'] . '"
        or Payroll_Date = "' . $this->info['Absence_From_Date'] . '" or Payroll_Date = "' . $this->info['Absence_To_Date'] . '" 
        or  "' . $this->info['Absence_From_Date'] . '" between from_date and  Payroll_Date
        or  "' . $this->info['Absence_To_Date'] . '" between from_date and  Payroll_Date )');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            return [CommonLanguage::getMessage('payroll_date_check')];
        }

        //        echo"<pre>";
        //        die(print_r($_FILES));
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');


        $data = [&$this->info['Absence_From_Date'],
            &$this->info['Absence_To_Date'],
            &$this->info['Absence_Reason'],
            &$this->info['Employee_ID'],
            &$this->info['id'],
        ];
        $this->db->prepare_Stmt('update 
                                            tbl_hrm_absence set
                                                            Absence_From_Date=?,
                                                            Absence_To_Date=?,
                                                            Absence_Reason=?,
                                                            Employee_ID=?
                                                    where Absence_ID = ?', $data);
        $fid = '';
        if ($_FILES['Document_File_Name']['name'] !== '') {
            $old_query = $this->db->query('select * from tbl_hrm_absence a left join
            tbl_hrm_employee_document d on d.Entry_ID = a.doc_id
            where Absence_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 = '7_ ' . $time . '__' . $this->info['Employee_ID'] . '.' . $Extension;
            $logged_id = smc_session()['user_logged_id'];
            $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   (?,7,?,?,?,?) ', $data1);
                $fid = $this->db->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 array_unique($error);
    }

    public function employee_leave_absence_report($from_date, $to_date,$reason_ids) {
        $emp_data = array();
        $data = array();
        $cond='';
        $cond_abs='';
        if(!empty($reason_ids)){
            $cond.=' and Absence_Reason_ID in ('.implode(',',$reason_ids).') ';
            $cond_abs.=' and Absence_Reason in ('.implode(',',$reason_ids).') ';
        }
        $query = $this->db->query('select * from tbl_hrm_absence_reasons 
            where 1=1 '.$cond.' order by Absence_Reason_ID asc');
        while ($row = $this->db->assoc($query))
            $abs_arr[$row['Absence_Reason_ID']] = $row['Absence_Reason_Name'];
        //
        $query_emp = $this->db->query('select Employee_ID,
            concat(Employee_FirstName," ",Employee_MiddleName," ",Employee_LastName) as Employee_Name 
            from tbl_hrm_employee where Status_ID = 1 
            and (leave_date is null or leave_date ="" or leave_date="0000-00-00")');
        while ($row = $this->db->assoc($query_emp)) {
            $emp_data[$row['Employee_ID']] = array("Employee_Name" => $row['Employee_Name'],
                "total_leave" => 0, "total_holiday" => 0);
            foreach ($abs_arr as $abs_id => $abs_name) {
                $emp_data[$row['Employee_ID']][$abs_name] = 0;
            }
            $emp_data[$row['Employee_ID']]['total_absence'] = 0;
            $emp_data[$row['Employee_ID']]['total'] = 0;
        }
//        echo "<pre>";die(print_r($emp_data));
        $query_leave = $this->db->query('SELECT `Date_From`,`Date_To`,`Employee_ID`,is_holiday
            FROM `tbl_hrm_taken_accrued` WHERE `is_approved`=1 and
            ( 
                Date_From between "' . $from_date . '" and "' . $to_date . '"
            or  Date_To between "' . $from_date . '" and "' . $to_date . '"
            or  "' . $from_date . '" between Date_From and Date_To
            or  "' . $to_date . '" between Date_From and Date_To
            )
            and Employee_ID in (' . implode(',', array_keys($emp_data)) . ')');
        while ($row = $this->db->assoc($query_leave)) {
            if ($row['Date_From'] <= $from_date && $row['Date_To'] >= $from_date && $row['Date_To'] <= $to_date) {
                $date_diff = (CommonFunctions::calc_date_diff($from_date, $row['Date_To']) + 1);
//                $date_diff = date_diff(date_create($row['Date_To']), date_create($from_date));
            } else if ($row['Date_From'] <= $to_date && $row['Date_From'] >= $from_date && $row['Date_To'] >= $to_date) {
//                $date_diff = date_diff(date_create($to_date), date_create($row['Date_From']));
                $date_diff = (CommonFunctions::calc_date_diff($row['Date_From'], $to_date) + 1);
            } else if ($row['Date_From'] >= $from_date && $row['Date_From'] <= $to_date && $row['Date_To'] >= $from_date && $row['Date_To'] <= $to_date) {
//                $date_diff = date_diff(date_create($row['Date_To']), date_create($row['Date_From']));
                $date_diff = (CommonFunctions::calc_date_diff($row['Date_From'], $row['Date_To']) + 1);
            } else if ($row['Date_From'] <= $from_date && $row['Date_To'] >= $to_date) {
//                $date_diff = date_diff(date_create($to_date), date_create($from_date));
                $date_diff = (CommonFunctions::calc_date_diff($from_date, $to_date) + 1);
            }
//            $date_diff = intval($date_diff->format("%d"))+1;
            $emp_data[$row['Employee_ID']][$row['is_holiday'] == 1 ? 'total_holiday' : 'total_leave']+=$date_diff;
            $emp_data[$row['Employee_ID']]['total']+=$date_diff;
        }
//        echo'heree';
        $query_absence = $this->db->query('SELECT Absence_From_Date as `Date_From`,Absence_To_Date as `Date_To`,
            `Employee_ID`,Absence_Reason
            FROM `tbl_hrm_absence` WHERE `is_approved`=1 and
            ( 
                Absence_From_Date between "' . $from_date . '" and "' . $to_date . '"
            or  Absence_To_Date between "' . $from_date . '" and "' . $to_date . '"
            or  "' . $from_date . '" between Absence_From_Date and Absence_To_Date
            or  "' . $to_date . '" between Absence_From_Date and Absence_To_Date
            )
            and Employee_ID in (' . implode(',', array_keys($emp_data)) . ') '.$cond_abs);
        while ($row = $this->db->assoc($query_absence)) {
            if ($row['Date_From'] <= $from_date && $row['Date_To'] >= $from_date && $row['Date_To'] <= $to_date) {
//                $date_diff = date_diff(date_create($row['Date_To']), date_create($from_date));
                $date_diff = (CommonFunctions::calc_date_diff($from_date, $row['Date_To']) + 1);
            } else if ($row['Date_From'] <= $to_date && $row['Date_From'] >= $from_date && $row['Date_To'] >= $to_date) {
//                $date_diff = date_diff(date_create($to_date), date_create($row['Date_From']));
                $date_diff = (CommonFunctions::calc_date_diff($row['Date_From'], $to_date) + 1);
            } else if ($row['Date_From'] >= $from_date && $row['Date_From'] <= $to_date && $row['Date_To'] >= $from_date && $row['Date_To'] <= $to_date) {
//                $date_diff = date_diff(date_create($row['Date_To']), date_create($row['Date_From']));
                $date_diff = (CommonFunctions::calc_date_diff($row['Date_From'], $row['Date_To']) + 1);
            } else if ($row['Date_From'] <= $from_date && $row['Date_To'] >= $to_date) {
//                $date_diff = date_diff(date_create($to_date), date_create($from_date));
                $date_diff = (CommonFunctions::calc_date_diff($from_date, $to_date) + 1);
            }
//            $date_diff = intval($date_diff->format("%d"))+1;
            $emp_data[$row['Employee_ID']]['total_absence']+=$date_diff;
//            if (empty($emp_data[$row['Employee_ID']][$row['Absence_Reason']]))
//                $emp_data[$row['Employee_ID']][$row['Absence_Reason']] = 0;
            $emp_data[$row['Employee_ID']][$abs_arr[$row['Absence_Reason']]]+=$date_diff;
            $emp_data[$row['Employee_ID']]['total']+=$date_diff;
        }
//        echo"<pre>";die(print_r($emp_data));
        foreach ($emp_data as $emp) {
            $data[] = array_values($emp);
        }
        return $data;
    }

    public static function get_absence_by_type($type, $type_id = null, $reason_id = null) {
//        echo $type.'==='.$type_id.'==='.$reason_id;
        $db = DBConnectorFactory::loadDriver();
        $data = array();
        $cond = ' where 1=1 ';
        if (!is_null($reason_id))
            $cond.=' and a.Absence_Reason_ID = "' . $reason_id . '" ';
        if ($type == 1) {
            $query = $db->query('select Absence_Reason_ID,Absence_Reason_Name,Absence_Reason_Days as nb_days,
                exceed_days,is_holiday
                    from tbl_hrm_absence_reasons a ' . $cond);
        } else if ($type == 2) {
            $query = $db->query('select a.Absence_Reason_ID,a.Absence_Reason_Name,
                 ifnull(j.nb_days,0) as nb_days,exceed_days,is_holiday
                    from tbl_hrm_absence_reasons a
                    left join tbl_hrm_emp_absence_setting j on j.absence_reason_id = a.Absence_Reason_ID
                    and employee_id ="' . $type_id . '" ' . $cond);
        } else if ($type == 3) {
            if (!is_null($type_id)) {
                $query = $db->query('select a.Absence_Reason_ID,a.Absence_Reason_Name,ifnull(j.nb_days,0) as nb_days,
                    exceed_days,is_holiday
                    from tbl_hrm_absence_reasons a
                    left join tbl_hrm_job_absence_setting j on j.absence_reason_id = a.Absence_Reason_ID
                    and job_id ="' . $type_id . '" ' . $cond);
            } else {
                $query = $db->query('select Absence_Reason_ID,Absence_Reason_Name,0 as nb_days,exceed_days,is_holiday
                    from tbl_hrm_absence_reasons a ' . $cond);
            }
        }
        while ($row = $db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function update_emp_absence() {
        $this->db->query('START TRANSACTION');
        if (!empty($this->info['emp_arr'])) {
            foreach ($this->info['emp_arr'] as $emp) {
                $data = [$emp['employee_id'], $emp['reason_id'], $emp['nb_days']];
                $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 = values(nb_days)', $data);
            }
        }
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function get_absence_reasons() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_hrm_absence_reasons order by Absence_Reason_ID asc');
        while ($row = $this->db->assoc($query))
            $data[$row['Absence_Reason_ID']] = $row;
        return $data;
    }

}
