<?php

class Job {

    private $db;
    private $info;

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

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

    public function create() {
        $this->db->query('START TRANSACTION');
        $data = [&$this->info['Job_Name'],
            &$this->info['Job_Description'],
            &$this->info[0]];
        $this->db->prepare_Stmt('INSERT INTO 
                                    tbl_hrm_jobs	
                                    (
                                            Job_Name,
                                            Job_Description,
                                            Logged_ID
                                    )
                                    VALUES ( ?,?,? )', $data);
        $job_id = $this->db->id();
        if (!empty($this->info['PayGrade'])) {
            self::addPaygrade($job_id);
        }
        if (!empty($this->info['absences'])) {
            foreach ($this->info['absences'] as $ab) {
                $this->db->prepare_Stmt('INSERT INTO `tbl_hrm_job_absence_setting`( `job_id`, `absence_reason_id`,
                    `nb_days`) values(?,?,?) ', [$job_id, $ab['absence_reason_id'], $ab['nb_days']]);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function updateJob() {
        $this->db->query('START TRANSACTION');
        $data = [&$this->info['Job_Name'], &$this->info['Job_Description'], &$this->info[0], &$this->info['Job_ID']];
        $this->db->prepare_Stmt("
			UPDATE  tbl_hrm_jobs SET  
				Job_Name = ?,
				Job_Description= ?,
                                Logged_ID = ?
                        WHERE Job_ID = ?", $data);
        if (!empty($this->info['PayGrade'])) {
            foreach ($this->info['PayGrade'] as $item) {
                if ($item['Paygrade_ID'] != 0) {
                    $item_update = [&$item['Paygrade_Name'],
                        &$item['Paygrade_Value'],
                        &$item['Paygrade_Currency'],
                        &$item['Paygrade_ID']];
                    $this->db->prepare_Stmt('UPDATE tbl_hrm_paygrade
                                         SET
                                            Paygrade_Name = ?,
                                            Paygrade_Value = ?,
                                            Paygrade_Currency= ?
                                         WHERE
                                            Paygrade_ID = ?', $item_update);
                } else {
                    $item_data = [&$item['Paygrade_Name'],
                        &$item['Paygrade_Value'],
                        &$item['Paygrade_Currency'],
                        &$this->info['Job_ID'],
                        &$this->info[0]];
                    $this->db->prepare_Stmt('INSERT INTO
                                    tbl_hrm_paygrade (
                                         Paygrade_Name,
                                         Paygrade_Value,
                                         Paygrade_Currency,
                                         Job_ID ,
                                         Logged_ID
                                    ) 
                                    VALUES (?,?,?,?,?)', $item_data);
                }
            }
        }
        $this->db->prepare_Stmt('delete from tbl_hrm_job_absence_setting where job_id = ?', [$this->info['Job_ID']]);
        if (!empty($this->info['absences'])) {
            foreach ($this->info['absences'] as $ab) {
                $this->db->prepare_Stmt('INSERT INTO `tbl_hrm_job_absence_setting`( `job_id`, `absence_reason_id`,
                    `nb_days`) values(?,?,?) ', [$this->info['Job_ID'], $ab['absence_reason_id'], $ab['nb_days']]);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function addPaygrade($id) {
        foreach ($this->info['PayGrade'] as $item) {
            $item_data = [&$item['Paygrade_Name'],
                &$item['Paygrade_Value'],
                &$item['Paygrade_Currency'],
                &$id,
                &$this->info[0]];
            $this->db->prepare_Stmt('INSERT INTO
                                    tbl_hrm_paygrade (
                                         Paygrade_Name,
                                         Paygrade_Value,
                                         Paygrade_Currency,
                                         Job_ID ,
                                         Logged_ID
                                    ) 
                                    VALUES (?,?,?,?,?)', $item_data);
        }
    }

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

    public function getIdByName($name) {
        $query = '
			SELECT 
				Job_ID 
			FROM 
				tbl_hrm_jobs
			WHERE
				Job_Name	=	"' . $name . '"
            and
                Status_ID = 1
		';
        $result = $this->db->query($query);
        $row = $this->db->assoc($result);
        return $row['Job_ID'];
    }

    public function getJobsWithPaygrades() {
        $data = array();
        $query = '
			SELECT
				*
			FROM
				tbl_hrm_jobs
			LEFT JOIN
				tbl_hrm_paygrade
			ON
				tbl_hrm_paygrade.Job_ID		=	tbl_hrm_jobs.Job_ID
            WHERE
                tbl_hrm_jobs.Status_ID = 1
		';
        $result = $this->db->query($query);
        while ($row = $this->db->assoc($result)) {
            $keys = array_keys($data);
            $job_name = $row['Job_Name'];
            if (!in_array($job_name, $keys)) {
                $data[$row['Job_Name']] = array();
            }
            array_push($data[$row['Job_Name']], array(
                'Paygrade_ID' => $row['Paygrade_ID'],
                'Paygrade_Name' => $row['Paygrade_Name'],
                'Paygrade_Value' => $row['Paygrade_Value'],
                'Paygrade_Currency' => $row['Paygrade_Currency']
            ));
        }
        return $data;
    }

    public function getJobsDetails($id) {
        $data = array();
        $query = $this->db->query('SELECT * 
                                    FROM 
                                        tbl_hrm_jobs 
                                    inner JOIN 
                                        tbl_hrm_paygrade 
                                    ON 
                                        tbl_hrm_paygrade.Job_ID = tbl_hrm_jobs.Job_ID 
                                    LEFT JOIN 
                                        tbl_common_currency 
                                    ON 
                                        tbl_common_currency.Currency_ID = tbl_hrm_paygrade.Paygrade_Currency 
                                    WHERE 
                                        tbl_hrm_jobs.Job_ID ="' . $id . '"
                                        AND 

									 tbl_hrm_jobs.Status_ID = 1


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

    public function getJobsByID($id) {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_hrm_jobs 
            WHERE Job_ID ="' . $id . '" AND Status_ID = 1');
        $data = $this->db->assoc($query);
        $query_paygrage = $this->db->query('select * from tbl_hrm_paygrade p
            LEFT JOIN tbl_common_currency c ON c.Currency_ID = p.Paygrade_Currency where p.Job_ID = "' . $id . '"');
        $data['paygrade'] = array();
        while ($row = $this->db->assoc($query_paygrage))
            $data['paygrade'][] = $row;
        return $data;
    }

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

    public function getJobID() {
        $query = $this->db->query('SELECT MAX(Job_ID) AS Job_ID FROM tbl_hrm_jobs');
        $row = $this->db->assoc($query);
        return $row['Job_ID'];
    }

    public function getPaygradeID() {
        $query = $this->db->query('SELECT MAX(Paygrade_ID) AS Paygrade_ID FROM tbl_hrm_paygrade');
        $row = $this->db->assoc($query);
        return $row['Paygrade_ID'];
    }

    public function viewExistingJob($name) {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_hrm_jobs WHERE Job_Name = "' . $name . '" AND Status_ID =1');
        $row = $this->db->assoc($query);
        if (!empty($row))
            return false;
        else
            return true;
    }

    public function deleteJobs($job_id, $logged_id) {
        $this->db->prepare_Stmt('DELETE FROM tbl_hrm_jobs WHERE Job_ID = ?', [&$job_id]);
        $error = $this->db->error();
        return $error;
    }

    public function deletePaygrayde($paygrayd_id, $logged_id) {
        $this->db->prepare_Stmt('DELETE FROM tbl_hrm_paygrade WHERE Paygrade_ID = ?', [&$paygrayd_id]);
        $error = $this->db->error();
        return $error;
    }

    public function viewPayGradesByJob() {
        $data = array();
        //    $arr_job = array();
        $jids = array();
        $jobs = $this->db->query('SELECT 
                                                *
                                        FROM 
                                                tbl_hrm_jobs
                                        WHERE 
                                                Status_ID = 1
				');
        while ($job = $this->db->assoc($jobs)) {
            $data[$job['Job_ID']]['Job_Name'] = $job['Job_Name'];
            $jids[] = $job['Job_ID'];
        }
        $jobs_str = implode(',', $jids);
        $paygrades = $this->db->query('SELECT 
                                                    p.*,
                                                    c.Currency_Symbol
                                            FROM 
                                                    tbl_hrm_paygrade p
                                            LEFT JOIN 
                                                    tbl_common_currency c 
                                            ON 
                                                    p.Paygrade_Currency = c.Currency_ID
                                            WHERE 
                                                    Job_ID in (' . $jobs_str . ')
                                            AND 
                                                    p.Status_ID = 1
                                        ');
        while ($paygrade = $this->db->assoc($paygrades)) {
            //				$paygrade['Job_Name'] = $arr_job[$paygrade['Job_ID']]['Job_Name'];
            $data[$paygrade['Job_ID']]['details'][] = $paygrade;
        }


        ////                echo $i;
        //                echo"<pre>";
        //                die(print_r($data));
        return $data;
    }

    public function jobSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .=' AND Job_Name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->query('select Job_ID as id, Job_Name as text
          from tbl_hrm_jobs 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 paygradeSearch($term = null, $page, $limit, $job_id = null) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .=' AND Paygrade_Name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        if (!is_null($job_id)) {
            $cond .=' AND p.Job_ID = "' . $job_id . '"';
        }

        $query = $this->db->prepare_Stmt('select Paygrade_ID as id, 
            concat(j.Job_Name,">>",p.Paygrade_Name,">>",p.Paygrade_Value," ",c.Currency_Symbol) as text
            from tbl_hrm_paygrade p JOIN tbl_hrm_jobs j ON p.Job_ID = j.Job_ID
            join tbl_common_currency c on c.Currency_ID = p.Paygrade_Currency 
            where p.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;
    }

}
