<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Data_sertifikat_pengajuan_m extends CI_Model {
    var $table = 'data_sertifikat_p';

    public function list_data($id_karyawan = null){
        $id_company = $this->session->userdata("id_company");
        $query = "SELECT id_sertifikat,
                  nama_sertifikat,
                  penyelenggara,
                  tgl_berlaku,
                  tgl_berakhir,
                  deskripsi,
                  file,
                  status_pengajuan,
                  action,
                  ket_revisi
                FROM {$this->table}
                WHERE md5(id_karyawan) = ?
                AND id_company = ?
                ORDER BY tgl_input DESC";
        return $this->db->query($query, array($id_karyawan,$id_company))->result();
    }

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

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

    function get_data($id_karyawan_md5) {
      $this->db->where('md5(id_karyawan)', $id_karyawan_md5);
      $this->db->where('id_company', $this->session->userdata('id_company'));
      return $this->db->get($this->table);
    }

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

    function approve($id, $status, $keterangan = '') {

      $this->db->set('status_pengajuan', $status);
      if($keterangan != '') {
        $this->db->set('ket_revisi', $keterangan);
      }
      $this->db->where('md5(id_sertifikat)', $id);
      $this->db->where('id_company', $this->session->userdata('id_company'));
      return $this->db->update($this->table);
    }

    function insert_history($id, $folder_history) {
      $sql = "INSERT INTO data_sertifikat_h (id_sertifikat,
                                        id_karyawan,
                                        nama_sertifikat,
                                        penyelenggara,
                                        tgl_berlaku,
                                        tgl_berakhir,
                                        deskripsi,
                                        file,
                                        action,
                                        id_company
                                        )
                                 SELECT data_sertifikat.id_sertifikat,
                                        data_sertifikat.id_karyawan,
                                        data_sertifikat.nama_sertifikat,
                                        data_sertifikat.penyelenggara,
                                        data_sertifikat.tgl_berlaku,
                                        data_sertifikat.tgl_berakhir,
                                        data_sertifikat.deskripsi,
                                        '{$folder_history}',
                                        data_sertifikat_p.action,
                                        data_sertifikat.id_company
              FROM data_sertifikat 
              JOIN data_sertifikat_p ON data_sertifikat.id_sertifikat = data_sertifikat_p.id_sertifikat 
              WHERE md5(data_sertifikat.id_sertifikat) = ? AND data_sertifikat.id_company = ?";
      return $this->db->query($sql, array($id, $this->session->userdata('id_company')));
    }

    function update_data($id_keluarga, $folder_master) {
      $sql = "UPDATE data_sertifikat a
              JOIN data_sertifikat_p b ON b.id_sertifikat = a.id_sertifikat AND a.id_karyawan = b.id_karyawan AND a.id_company = b.id_company
              SET a.nama_sertifikat = b.nama_sertifikat,
                  a.penyelenggara = b.penyelenggara,
                  a.tgl_berlaku = b.tgl_berlaku,
                  a.tgl_berakhir = b.tgl_berakhir,
                  a.deskripsi = b.deskripsi,
                  a.file = '{$folder_master}'
              WHERE md5(a.id_sertifikat) = ? AND a.id_company = ?";
      return $this->db->query($sql, array($id_keluarga, $this->session->userdata('id_company')));
    }

    function insert_master($id, $folder_master) {
      $sql = "INSERT INTO data_sertifikat (
                            id_sertifikat,
                            id_karyawan,
                            nama_sertifikat,
                            penyelenggara,
                            tgl_berlaku,
                            tgl_berakhir,
                            deskripsi,
                            file,
                            id_company
                                        )
                    SELECT id_sertifikat,
                    id_karyawan,
                    nama_sertifikat,
                    penyelenggara,
                    tgl_berlaku,
                    tgl_berakhir,
                    deskripsi,
                    '{$folder_master}',
                    id_company
              FROM data_sertifikat_p WHERE md5(id_sertifikat) = ? AND id_company = ?";
      return $this->db->query($sql, array($id, $this->session->userdata('id_company')));
    }

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

    public function getDataById($id_sertifikat=null){
        $select = $this->db->select("id_sertifikat,
                                      id_karyawan,
                                    nama_sertifikat,
                                    penyelenggara,
                                    tgl_berlaku,
                                    tgl_berakhir,
                                    deskripsi,
                                    action,
                                    file")
                            ->from($this->table)
                            ->where("MD5(id_sertifikat)",$id_sertifikat)
                            ->get()
                            ->row();
        return $select;
    }

    function hapus_data($id) {
      $this->db->where('md5(id_sertifikat)', $id);
      return $this->db->delete('data_sertifikat');
    }

    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_sertifikat_h history 
            JOIN data_sertifikat master 
                on master.id_sertifikat = history.id_sertifikat 
                    and master.file = history.file 
            SET history.file = ? 
            WHERE md5(history.id_sertifikat) = ?";
        $this->db->query($sql, [$folder_history, $id]);
    }
}
