<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Model_exam extends CI_Model {

    public function get_exam_list($staff_id = null, $tab_id = null) {
        $this->db->select('a.*, b.name as staff_name, c.name as department_name, d.name as branch_name, f.attachment_name, f.mask_id')
                ->join('user__employment_detail b', 'a.staff_id = b.id', 'left outer')
                ->join('company__department c', 'b.department_id = c.id', 'left outer')
                ->join('company__branch d', 'c.branch_id = d.id', 'left outer')
                ->join('hr__exam_attachment f', 'f.exam_id = a.id', 'left outer')
                ->where('a.is_delete', 0);

        if ($staff_id)
            $this->db->where('a.staff_id', $staff_id);

        if (!empty($tab_id)) {

            if ($tab_id == 1) {

                $this->db->select('e.program as programme');
                $this->db->join('user__pursuing_study e', 'e.id = a.programme_id', 'left outer');
            } else if ($tab_id == 2) {

                $this->db->select('e.program as programme');
                $this->db->join('user__professional_academic e', 'e.id = a.programme_id', 'left outer');
            } else if ($tab_id == 3) {

                $this->db->select('e.program as programme');
                $this->db->join('user__professional_academic e', 'e.id = a.programme_id AND e.type = 1', 'left outer');
            }

            $this->db->where('a.tab_id', $tab_id);
        }

        $query = $this->db->get('hr__exam a');

        return ($query->num_rows() > 0) ? $query->result_array() : null;
    }

    public function get_exam_list_admin() {
        $query = $this->db->query("SELECT a.*, b.name as staff_name, c.name as department_name, d.name as branch_name, e.program as programme, f.attachment_name, f.mask_id, 'Education in Progress' as tab 
									FROM hr__exam a 
									LEFT OUTER JOIN user__employment_detail b ON a.staff_id = b.id 
									LEFT OUTER JOIN company__department c ON b.department_id = c.id 
									LEFT OUTER JOIN company__branch d ON c.branch_id = d.id 
									LEFT OUTER JOIN user__pursuing_study e ON e.id = a.programme_id 
                                    LEFT OUTER JOIN hr__exam_attachment f ON f.exam_id = a.id
									WHERE a.is_delete = 0 
									AND a.tab_id = 1
									-- AND a.request_sl_status = 1
									UNION
									SELECT a.*, b.name as staff_name, c.name as department_name, d.name as branch_name, e.program as programme, f.attachment_name, f.mask_id, 'Professional Education' as tab 
									FROM hr__exam a 
									LEFT OUTER JOIN user__employment_detail b ON a.staff_id = b.id 
									LEFT OUTER JOIN company__department c ON b.department_id = c.id 
									LEFT OUTER JOIN company__branch d ON c.branch_id = d.id 
									LEFT OUTER JOIN user__professional_academic e ON e.id = a.programme_id 
                                    LEFT OUTER JOIN hr__exam_attachment f ON f.exam_id = a.id
									WHERE a.is_delete = 0 
									AND a.tab_id = 2
									-- AND a.request_sl_status = 1
									UNION
									SELECT a.*, b.name as staff_name, c.name as department_name, d.name as branch_name, e.program as programme, f.attachment_name, f.mask_id, 'Professional Certificate' as tab 
									FROM hr__exam a 
									LEFT OUTER JOIN user__employment_detail b ON a.staff_id = b.id 
									LEFT OUTER JOIN company__department c ON b.department_id = c.id 
									LEFT OUTER JOIN company__branch d ON c.branch_id = d.id 
									LEFT OUTER JOIN user__professional_academic e ON e.id = a.programme_id AND e.type = 1
                                    LEFT OUTER JOIN hr__exam_attachment f ON f.exam_id = a.id
									WHERE a.is_delete = 0 
									AND a.tab_id = 3
									-- AND a.request_sl_status = 1
									ORDER BY staff_name ASC, tab ASC");

        return ($query->num_rows() > 0) ? $query->result_array() : null;
    }

    public function save_exam($data) {
        $add_exam = $this->db->insert('hr__exam', $data);

        if ($add_exam) {
            return $this->db->insert_id();
        } else {
            return false;
        }
    }

    public function update_exam($sets = array(), $where = array()) {
        foreach ($sets as $column => $value) {
            $this->db->set($column, $value);
        }

        foreach ($where as $column => $value) {
            $this->db->where($column, $value);
        }

        $this->db->update('hr__exam');

        if ($this->db->affected_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }

    public function get_valid_exam_list_by_user($user_id) {
        $this->db->select('a.*, (SELECT count(c.id) FROM hr__leave_application c WHERE c.exam_id = a.id AND c.is_delete = 0 AND c.status IN (1,2)) as `count_leave_application`')
                ->join('view_count_exam b', '(b.paper_code = a.paper_code AND b.staff_id = a.staff_id AND (b.count_failed IS NULL OR b.count_failed <= 2))')
                ->where('a.staff_id', $user_id)
                ->where('a.is_delete', 0)
                ->where('a.result IS NULL')
                ->having('count_leave_application = 0');

        $query = $this->db->get('hr__exam a');

        return ($query->num_rows() > 0) ? $query->result_array() : null;
    }

    public function getExamListByID($id) {
        $this->db->select('a.*, b.name as staff_name')
                ->join('user__employment_detail b', 'b.id = a.staff_id', 'LEFT OUTER')
                ->where('a.is_delete', 0)
                ->where('a.id', $id);

        $query = $this->db->get('hr__exam a');

        return ($query->num_rows() > 0) ? $query->result() : null;
    }

    public function get_study_leave($exam_id) {
        $this->db->select('a.*')
                ->where('a.exam_id', $exam_id)
                ->where('a.status IN (1,2)')
                ->where('a.is_delete', 0);

        $query = $this->db->get('hr__leave_application a');

        return ($query->num_rows() > 0) ? $query->result_array() : null;
    }

    public function get_count_paper_code($paper_code, $staff_id) {
        $this->db->select('count(id) as count')
                ->where('staff_id', $staff_id)
                ->where('paper_code', $paper_code)
                ->where('is_delete', 0);

        $query = $this->db->get('hr__exam');

        return $query->result();
    }

    public function get_paper_date($paper_id) {
        $this->db->select('exam_date')
                ->where('id', $paper_id)
                ->where('is_delete', 0);

        $query = $this->db->get('hr__exam');

        return $query->result();
    }

    public function check_existing_study_leave_allocation($staff_id, $year) {
        $this->db->select('*')
                ->where('user_id', $staff_id)
                ->where('year', $year)
                ->where('type_id', 7)
                ->where('is_delete', 0);

        $query = $this->db->get('hr__leave_allocation');

        return ($query->num_rows() > 0) ? $query->result_array() : null;
    }

    public function update_study_leave_allocation($sets = array(), $where = array()) {
        foreach ($sets as $column => $value) {
            $this->db->set($column, $value);
        }

        foreach ($where as $column => $value) {
            $this->db->where($column, $value);
        }

        $this->db->update('hr__leave_allocation');

        if ($this->db->affected_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }

    public function insert_study_leave_allocation($data) {
        $add_allocation = $this->db->insert('hr__leave_allocation', $data);

        if ($add_allocation) {
            return $this->db->insert_id();
        } else {
            return false;
        }
    }

    public function exam__filter($table, $select = array('*'), $where = array(), $join = array(), $order = array(), $group = array(), $having = array(), $or_having = array(), $where_not_in = array(), $where_in = array(), $or_where = array(), $like = array()) {
        if (!$table)
            return NULL;

        if ($select)
            foreach ($select as $key => $value)
                $this->db->select($value, false);
        if ($where)
            foreach ($where as $key => $value)
                $this->db->where($key, $value);
        if ($join)
            foreach ($join as $key => $value)
                $this->db->join($value[0], $value[1], $value[2], false);
        if ($group)
            foreach ($group as $key => $value)
                $this->db->group_by($value);
        if ($having)
            foreach ($having as $key => $value)
                $this->db->having($key, $value);
        if ($or_having)
            foreach ($or_having as $key => $value)
                $this->db->or_having($value);
        if ($order)
            foreach ($order as $key => $value)
                $this->db->order_by($key, $value);
        if ($where_not_in)
            foreach ($where_not_in as $key => $value)
                $this->db->where_not_in($key, $value);
        if ($where_in)
            foreach ($where_in as $key => $value)
                $this->db->where_in($key, $value);
        if ($or_where)
            foreach ($or_where as $key => $value)
                $this->db->or_where($key, $value);
        if ($like)
            foreach ($like as $key => $value)
                $this->db->like($key, $value);


        $query = $this->db->get($table);

        return ($query->num_rows() > 0) ? $query->result_array() : null;
    }

    // sn
    public function getValidExamListByUser($userId) {
//        $this->db->select('a.*, (SELECT count(c.id) FROM hr__leave_application c WHERE c.exam_id = a.id AND c.is_delete = 0 AND c.status IN (1,2)) as `count_leave_application`')
//                ->where('a.staff_id', $userId)
//                ->where('a.is_delete', 0)
//                // ->where('a.result IS NULL')
//                ->having('count_leave_application = 0');
        $this->db->select('a.*')
                ->where('a.staff_id', $userId)
                ->where('a.is_delete', 0);

        $query = $this->db->get('hr__exam a');

        log_message('error', $this->db->last_query());
        return ($query->num_rows() > 0) ? $query->result_array() : array();
    }

    public function createAttachment_exam($input)
    {
        $this->db->insert('hr__exam_attachment',$input);

        return $this->db->insert_id();
    }

    public function editAttachment_exam($input, $id = null, $exam_id = null)
    {
        if($exam_id) $this->db->where('exam_id',$exam_id);
        if($id) $this->db->where('id',$id);
        $this->db->set($input);
        $this->db->update('hr__exam_attachment');
    }

    public function fetchAttachment($staff_id = null, $mask_id = null)
    {
        
        $this->db->select('a.*');
        if($staff_id)$this->db->where('user_id',$staff_id);
        if($mask_id)$this->db->where('mask_id',$mask_id);
        $query = $this->db->get('hr__exam_attachment a');

        return ($query->num_rows() > 0)?$query->result() : null;
    }

}

?>