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

class Pengalaman_kerja_pengajuan_m extends CI_Model{
  var $table = 'riwayat_pekerjaan_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);
  }

  function get_datatables($id_karyawan){
      $this->_get_datatables_query($id_karyawan);
      if($_POST['length'] != -1){
          $this->db->limit($_POST['length'], $_POST['start']);
      }
      $query = $this->db->get();
      return $query->result();
  }

  function count_filtered($id_karyawan = null){
      $this->_get_datatables_query($id_karyawan);
      $query = $this->db->get();
      return $query->num_rows();
  }

  public function count_all($id_karyawan){
      $this->db->select("id_riwayat_pekerjaan")->from($this->table);
      if ($id_karyawan != '') {
          $this->db->where("MD5(id_karyawan)", $id_karyawan);
      }
      return $this->db->count_all_results();
  }
  private function _get_datatables_query($id_karyawan = null){
      $this->db->select("id_riwayat_pekerjaan,nama_perusahaan,jenis_perusahaan,jabatan, status_pengajuan, ket_revisi, action")
              ->from($this->table);
      if ($id_karyawan != '') {
          $this->db->where("MD5(id_karyawan)", $id_karyawan);
      }
      $column_search = array('nama_perusahaan','jenis_perusahaan','jabatan','ket_revisi');
      $i = 0;

      foreach ($column_search as $item){  // looping awal
          if($_POST['search']['value']){ // jika datatable mengirimkan pencarian dengan metode POST

              if($i===0){ // looping awal
                  $this->db->group_start();
                  $this->db->like($item, $_POST['search']['value']);
              }else{
                  $this->db->or_like($item, $_POST['search']['value']);
              }

              if(count($column_search) - 1 == $i){
                  $this->db->group_end();
              }
          }
          $i++;
      }

      $column_order = array('','nama_perusahaan','jenis_perusahaan','jabatan','ket_revisi','status_pengajuan','id_riwayat_pekerjaan');

      if(isset($_POST['order'])){
          $this->db->order_by($column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
      }
      else{
          $order_by = array("id_riwayat_pekerjaan"=>"DESC");
          $this->db->order_by(key($order_by), $order_by[key($order_by)]);
      }
  }

  function getDataById($id) {
    return $this->db->select("riwayat_pekerjaan_p.id_riwayat_pekerjaan,
                                    riwayat_pekerjaan_p.id_karyawan,
                                    riwayat_pekerjaan_p.nama_perusahaan,
                                    riwayat_pekerjaan_p.jenis_perusahaan,
                                    riwayat_pekerjaan_p.jabatan,
                                    master_kota.id_kota,
                                    master_kota.nama AS kota,
                                    master_provinsi.id_prov,
                                    master_provinsi.nama AS provinsi,
                                    riwayat_pekerjaan_p.alamat_perusahaan,
                                    riwayat_pekerjaan_p.tgl_masuk,
                                    riwayat_pekerjaan_p.tgl_keluar,
                                    riwayat_pekerjaan_p.alasan_berhenti,
                                    riwayat_pekerjaan_p.action,
                                    riwayat_pekerjaan_p.file")
                        ->from($this->table)
                        ->join("master_provinsi","master_provinsi.id_prov = riwayat_pekerjaan_p.provinsi","left")
                        ->join("master_kota","master_kota.id_kota = riwayat_pekerjaan_p.kota","left")
                        ->where("MD5(riwayat_pekerjaan_p.id_riwayat_pekerjaan)",$id)
                        ->get()->row();
  }

  function insert_history($id, $folder_history) {
    $sql = "INSERT INTO riwayat_pekerjaan_h (
                          id_riwayat_pekerjaan,
                          id_karyawan,
                          id_company,
                          nama_perusahaan,
                          jenis_perusahaan,
                          jabatan,
                          kota,
                          provinsi,
                          alamat_perusahaan,
                          tgl_masuk,
                          tgl_keluar,
                          alasan_berhenti,
                          file,
                          action
                                      )
                  SELECT riwayat_pekerjaan.id_riwayat_pekerjaan,
                  riwayat_pekerjaan.id_karyawan,
                  riwayat_pekerjaan.id_company,
                  riwayat_pekerjaan.nama_perusahaan,
                  riwayat_pekerjaan.jenis_perusahaan,
                  riwayat_pekerjaan.jabatan,
                  riwayat_pekerjaan.kota,
                  riwayat_pekerjaan.provinsi,
                  riwayat_pekerjaan.alamat_perusahaan,
                  riwayat_pekerjaan.tgl_masuk,
                  riwayat_pekerjaan.tgl_keluar,
                  riwayat_pekerjaan.alasan_berhenti,
                  '{$folder_history}',
                  riwayat_pekerjaan_p.action
            FROM riwayat_pekerjaan 
            JOIN riwayat_pekerjaan_p ON riwayat_pekerjaan_p.id_riwayat_pekerjaan = riwayat_pekerjaan.id_riwayat_pekerjaan 
            WHERE md5(riwayat_pekerjaan.id_riwayat_pekerjaan) = ? AND riwayat_pekerjaan.id_company = ?";
    return $this->db->query($sql, array($id, $this->session->userdata('id_company')));
  }
  function insert_master($id, $folder_master) {
    $sql = "INSERT INTO riwayat_pekerjaan (
                          id_riwayat_pekerjaan,
                          id_karyawan,
                          id_company,
                          nama_perusahaan,
                          jenis_perusahaan,
                          jabatan,
                          kota,
                          provinsi,
                          alamat_perusahaan,
                          tgl_masuk,
                          tgl_keluar,
                          alasan_berhenti,
                          file
                                      )
                  SELECT id_riwayat_pekerjaan,
                  id_karyawan,
                  id_company,
                  nama_perusahaan,
                  jenis_perusahaan,
                  jabatan,
                  kota,
                  provinsi,
                  alamat_perusahaan,
                  tgl_masuk,
                  tgl_keluar,
                  alasan_berhenti,
                  '{$folder_master}'
            FROM riwayat_pekerjaan_p WHERE md5(id_riwayat_pekerjaan) = ? AND id_company = ?";
    return $this->db->query($sql, array($id, $this->session->userdata('id_company')));
  }

  function update_data($id, $folder_master) {
    $sql = "UPDATE riwayat_pekerjaan a
            JOIN {$this->table} b ON b.id_riwayat_pekerjaan = a.id_riwayat_pekerjaan AND a.id_karyawan = b.id_karyawan AND a.id_company = b.id_company
            SET a.nama_perusahaan = b.nama_perusahaan,
                a.jenis_perusahaan = b.jenis_perusahaan,
                a.jabatan = b.jabatan,
                a.kota = b.kota,
                a.provinsi = b.provinsi,
                a.alamat_perusahaan = b.alamat_perusahaan,
                a.tgl_masuk = b.tgl_masuk,
                a.tgl_keluar = b.tgl_keluar,
                a.alasan_berhenti = b.alasan_berhenti,
                a.file = '{$folder_master}'
            WHERE md5(a.id_riwayat_pekerjaan) = ? AND a.id_company = ?";
    return $this->db->query($sql, array($id, $this->session->userdata('id_company')));
  }

  function delete($id) {
    $this->db->where('md5(id_riwayat_pekerjaan)', $id);
    $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_riwayat_pekerjaan)', $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_riwayat_pekerjaan)', $id);
    return $this->db->get('riwayat_pekerjaan')->num_rows();
  }

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

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

  function cek_pengalaman($id) {
    $this->db->from('riwayat_pekerjaan');
    $this->db->where('md5(id_riwayat_pekerjaan)', $id);
    return $this->db->get()->num_rows();
  }
}
