<?php

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

class Data_kelengkapan_file_pengajuan_m extends CI_Model {
    var $table = 'data_kelengkapan_file_p';

    function total_pengajuan() {
        $this->db->where('id_company', $this->session->userdata('id_company'));
        $this->db->where_in('status_pengajuan', array('1','3','4'));
        return $this->db->get($this->table);
    }

    function get_data_pengajuan($id_karyawan_md5) {
        $this->db->where('md5(id_karyawan)', $id_karyawan_md5);
        $this->db->where('id_company', $this->session->userdata('id_company'));
        $this->db->where_in('status_pengajuan', array('1','3','4'));
        return $this->db->get($this->table);
    }

    public function getListData($id_karyawan=null){
        $select = $this->db->select("id_kelengkapan_file, id_karyawan,nama_dokumen,file, status_pengajuan, action, ket_revisi")
                            ->from($this->table)
                            ->where("MD5(id_karyawan)",$id_karyawan)
                            ->get();
        return $select;
    }
    
    public function approve($id, $status, $keterangan = '') {
        $this->db->set('status_pengajuan', $status);
        if($keterangan != '') {
          $this->db->set('ket_revisi', $keterangan);
        }
        $this->db->where('md5(id_kelengkapan_file)', $id);
        $this->db->where('id_company', $this->session->userdata('id_company'));
        return $this->db->update($this->table);
    }

    function cek_id($id) {
        $this->db->where('md5(id_kelengkapan_file)', $id);
        return $this->db->get('data_kelengkapan_file')->num_rows();
    }

    public function getDataById($id_kelengkapan_file=null){
        $select = $this->db->select("id, id_kelengkapan_file,
                                      id_karyawan,
                                    nama_dokumen,
                                    action,
                                    file")
                            ->from($this->table)
                            ->where("MD5(id_kelengkapan_file)",$id_kelengkapan_file)
                            ->get()
                            ->row();
        return $select;
    }

    function insert_master($id, $folder_master) {
        $sql = "INSERT INTO data_kelengkapan_file (
                              id_kelengkapan_file,
                              id_karyawan,
                              nama_dokumen,
                              file,
                              id_company
                                          )
                      SELECT id_kelengkapan_file,
                      id_karyawan,
                      nama_dokumen,
                      '{$folder_master}',
                      id_company
                FROM data_kelengkapan_file_p WHERE md5(id_kelengkapan_file) = ? AND id_company = ?";
        return $this->db->query($sql, array($id, $this->session->userdata('id_company')));
    }

    function insert_history($id, $folder_history) {
        $sql = "INSERT INTO data_kelengkapan_file_h (id_kelengkapan_file,
                                          id_karyawan,
                                          nama_dokumen,
                                          file,
                                          action,
                                          id_company
                                          )
                                   SELECT data_kelengkapan_file.id_kelengkapan_file,
                                          data_kelengkapan_file.id_karyawan,
                                          data_kelengkapan_file.nama_dokumen,
                                          '{$folder_history}',
                                          data_kelengkapan_file_p.action,
                                          data_kelengkapan_file.id_company
                FROM data_kelengkapan_file 
                JOIN data_kelengkapan_file_p on data_kelengkapan_file_p.id_kelengkapan_file = data_kelengkapan_file.id_kelengkapan_file
                WHERE md5(data_kelengkapan_file.id_kelengkapan_file) = ? 
                    AND data_kelengkapan_file.id_company = ?";
        return $this->db->query($sql, array($id, $this->session->userdata('id_company')));
    }

    function update_data($id_kelengkapan_file, $folder_master) {
        $sql = "UPDATE data_kelengkapan_file a
                JOIN data_kelengkapan_file_p b ON b.id_kelengkapan_file = a.id_kelengkapan_file AND a.id_karyawan = b.id_karyawan AND a.id_company = b.id_company
                SET a.nama_dokumen = b.nama_dokumen,
                    a.file = '{$folder_master}'
                WHERE md5(a.id_kelengkapan_file) = ? AND a.id_company = ?";
        return $this->db->query($sql, array($id_kelengkapan_file, $this->session->userdata('id_company')));
    }

    function delete($id) {
        $this->db->where('md5(id_kelengkapan_file)', $id);
        $this->db->where('id_company', $this->session->userdata('id_company'));
        return $this->db->delete($this->table);
    }

    function hapus_data($id) {
        $this->db->where('md5(id_kelengkapan_file)', $id);
        $this->db->where('id_company', $this->session->userdata('id_company'));
        return $this->db->delete('data_kelengkapan_file');
    }

    function update_path_foto_history($id, $folder_history) 
    {
        // update path foto yang sama dengan master diisi path foto yang sudah dipindah ke history
        $sql = "UPDATE data_kelengkapan_file_h history 
            JOIN data_kelengkapan_file master 
                on master.id_kelengkapan_file = history.id_kelengkapan_file 
                    and master.file = history.file 
            SET history.file = ? 
            WHERE md5(history.id_kelengkapan_file) = ?";
        $this->db->query($sql, [$folder_history, $id]);
    }

}

/* End of file Data_kelengkapan_file_pengajuan_m.php */
