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

class Data_diri_pengajuan_m extends CI_Model {
  public $table = 'data_karyawan_p';

  function get_data($id_karyawan_md5) {
    $this->db->select('data_karyawan_p.id_karyawan,
      data_karyawan_p.nik,
      data_karyawan_p.nama_lengkap,
      data_karyawan_p.gelar_depan,
      data_karyawan_p.gelar_belakang,
      data_karyawan_p.jenis_kelamin,
      data_karyawan_p.golongan_darah,
      data_karyawan_p.agama,
      data_karyawan_p.tempat_lahir,
      data_karyawan_p.tgl_lahir,
      data_karyawan_p.alamat_domisili,
      data_karyawan_p.rt,
      data_karyawan_p.rw,
      data_karyawan_p.id_kel,
      data_karyawan_p.id_kec,
      data_karyawan_p.id_kab,
      data_karyawan_p.id_prov,
      data_karyawan_p.kode_pos,
      data_karyawan_p.no_ktp,
      data_karyawan_p.no_bpjs_kes,
      data_karyawan_p.no_bpjs_tk,
      data_karyawan_p.telepon,
      data_karyawan_p.email,
      data_karyawan_p.pendidikan_terakhir,
      data_karyawan_p.status,
      data_karyawan_p.ket_revisi,
      data_karyawan_p.action,
      data_karyawan_p.id_company,
      master_kota.id_kota,
      master_kota.nama as nama_kota,
      master_kecamatan.id_kecamatan,
      master_kecamatan.nama as nama_kec,
      master_kelurahan.id_kelurahan,
      master_kelurahan.nama as nama_kel,
      master_provinsi.id_prov,
      master_provinsi.nama as nama_prov');
    $this->db->join("master_provinsi","master_provinsi.id_prov = data_karyawan_p.id_prov","LEFT");
    $this->db->join("master_kota","master_kota.id_kota = data_karyawan_p.id_kab","LEFT");
    $this->db->join("master_kecamatan","master_kecamatan.id_kecamatan = data_karyawan_p.id_kec","LEFT");
    $this->db->join("master_kelurahan","master_kelurahan.id_kelurahan = data_karyawan_p.id_kel","LEFT");
    $this->db->where('md5(data_karyawan_p.id_karyawan)', $id_karyawan_md5);
    $this->db->where('data_karyawan_p.id_company', $this->session->userdata('id_company'));
    return $this->db->get($this->table);
  }

  function total_pengajuan() {
    $this->db->where('id_company', $this->session->userdata('id_company'));
    $this->db->where_in('status', array('1','3'));
    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', array('1','3'));
    return $this->db->get($this->table);
  }

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

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

  function insert_history($id_karyawan_md5) {
    $sql = "INSERT INTO data_karyawan_h (id_karyawan,
                                        nik,
                                        nama_lengkap,
                                        gelar_depan,
                                        gelar_belakang,
                                        jenis_kelamin,
                                        golongan_darah,
                                        agama,
                                        tempat_lahir,
                                        tgl_lahir,
                                        alamat_domisili,
                                        rt,
                                        rw,
                                        id_kel,
                                        id_kec,
                                        id_kab,
                                        id_prov,
                                        kode_pos,
                                        no_ktp,
                                        no_bpjs_kes,
                                        no_bpjs_tk,
                                        telepon,
                                        email,
                                        pendidikan_terakhir,
                                        action,
                                        id_company)
            SELECT id_karyawan,
                    nik,
                    nama_lengkap,
                    gelar_depan,
                    gelar_belakang,
                    jenis_kelamin,
                    golongan_darah,
                    agama,
                    tempat_lahir,
                    tgl_lahir,
                    alamat_domisili,
                    rt,
                    rw,
                    id_kel,
                    id_kec,
                    id_kab,
                    id_prov,
                    kode_pos,
                    no_ktp,
                    no_bpjs_kes,
                    no_bpjs_tk,
                    telepon,
                    email,
                    pendidikan_terakhir,
                    '2',
                    id_company
            FROM data_karyawan WHERE md5(id_karyawan) = ? AND id_company = ?";
    return $this->db->query($sql, array($id_karyawan_md5, $this->session->userdata('id_company')));
  }

  function update_data_diri($id_karyawan_md5) {
    $sql = "UPDATE data_karyawan a
            JOIN data_karyawan_p b ON b.id_karyawan = a.id_karyawan AND a.id_company = b.id_company
            SET a.id_karyawan = b.id_karyawan,
                a.nik = b.nik,
                a.nama_lengkap = b.nama_lengkap,
                a.gelar_depan = b.gelar_depan,
                a.gelar_belakang = b.gelar_belakang,
                a.jenis_kelamin = b.jenis_kelamin,
                a.golongan_darah = b.golongan_darah,
                a.agama = b.agama,
                a.tempat_lahir = b.tempat_lahir,
                a.tgl_lahir = b.tgl_lahir,
                a.alamat_domisili = b.alamat_domisili,
                a.rt = b.rt,
                a.rw = b.rw,
                a.id_kel = b.id_kel,
                a.id_kec = b.id_kec,
                a.id_kab = b.id_kab,
                a.id_prov = b.id_prov,
                a.kode_pos = b.kode_pos,
                a.no_ktp = b.no_ktp,
                a.no_bpjs_kes = b.no_bpjs_kes,
                a.no_bpjs_tk = b.no_bpjs_tk,
                a.telepon = b.telepon,
                a.email = b.email,
                a.pendidikan_terakhir = b.pendidikan_terakhir
            WHERE md5(a.id_karyawan) = ? AND a.id_company = ?";
    return $this->db->query($sql, array($id_karyawan_md5, $this->session->userdata('id_company')));
  }

  function delete($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->delete($this->table);
  }
}
