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

class Pengajuan_izin_m extends CI_Model
{
    var $table = 'data_izin';

    function get_datatables($tgl_mulai = "0", $tgl_selesai = "0", $jenis = "SM", $status = "0", $id_karyawan = "0", $id_cabang = "0", $id_departemen = "0")
    {
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $jenis, $status, $id_karyawan, $id_cabang, $id_departemen);
        if ($_GET['length'] != -1) {
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered($tgl_mulai = "0", $tgl_selesai = "0", $jenis = "SM", $status = "0", $id_karyawan = "0", $id_cabang = "0", $id_departemen = "0")
    {
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $jenis, $status, $id_karyawan, $id_cabang, $id_departemen);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all($tgl_mulai = "0", $tgl_selesai = "0", $jenis = "SM", $status = "0", $id_karyawan = "0", $id_cabang = "0", $id_departemen = "0")
    {
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $jenis, $status, $id_karyawan, $id_cabang, $id_departemen);

        return $this->db->count_all_results();
    }
    private function _get_datatables_query($tgl_mulai = "0", $tgl_selesai = "0", $jenis = "SM", $status = "0", $id_karyawan = "0", $id_cabang = "0", $id_departemen = "0")
    {
        $this->db->select("data_izin.id_izin,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            master_jabatan.level_user,
                            data_izin.id_cabang,
                            data_izin.nama_izin,
                            data_izin.tgl_pengajuan,
                            data_izin.tgl_mulai_izin,
                            data_izin.tgl_selesai_izin,
                            data_izin.jml_izin,
                            data_izin.ket_izin,
                            data_izin.approval_spv,
                            data_izin.approval_kedep,
                            data_izin.approval_kacab,
                            data_izin.approval_direksi,
                            data_izin.approval_hrd,
                            data_izin.status,
                            p_approval_izin.level_approval");
        $this->db->from($this->table);
        $this->db->join('data_karyawan', 'data_izin.id_karyawan  = data_karyawan.id_karyawan');
        $this->db->join('master_jabatan', 'master_jabatan.id_jabatan  = data_karyawan.id_jabatan');
        $this->db->join('p_approval_izin', 'p_approval_izin.id_cabang=data_karyawan.id_cabang AND p_approval_izin.level_user=master_jabatan.level_user');
        $this->db->where("data_izin.id_company", $this->id_company);
        $this->db->where("data_izin.status !=", '2');
        $this->db->where("DATE_FORMAT(data_izin.tgl_pengajuan,'%Y-%m-%d') >=", $tgl_mulai);
        $this->db->where("DATE_FORMAT(data_izin.tgl_pengajuan,'%Y-%m-%d') <=", $tgl_selesai);

        if ($jenis != 'SM') {
            $this->db->where("data_izin.kode_izin", $jenis);
        }

        if ($id_karyawan != "0") {
            $this->db->where("data_izin.id_karyawan", $id_karyawan);
        } else {
            if ($this->level_user == "7") {
                $this->db->where("data_karyawan.supervisi", $this->id_karyawan);
            } else if ($this->level_user == "3") {
                $this->db->where("data_karyawan.id_karyawan", $this->id_karyawan);
            }
        }

        if ($id_cabang != "0") {
            $this->db->where('data_karyawan.id_cabang', $id_cabang);
        } else {
            if ($this->level_user == "5") {
                if ($this->multi_cabang == true) {
                    $this->db->where_in('data_karyawan.id_cabang', $this->id_cabang_arr);
                } else {
                    $this->db->where('data_karyawan.id_cabang', $this->id_cabang);
                }
            } else if ($this->level_user == "2") {
                $this->db->where('data_karyawan.id_cabang', $this->id_cabang);
            }
        }

        if ($id_departemen != "0") {
            $this->db->where('data_karyawan.id_departemen', $id_departemen);
        } else {
            if ($this->level_user == "2") {
                if ($this->multi_departemen == true) {
                    $this->db->where_in('data_karyawan.id_departemen', $this->id_departemen_arr);
                } else {
                    $this->db->where('data_karyawan.id_departemen', $this->id_departemen);
                }
            }
        }

        if ($this->level_user == "3") {
            $this->db->where_in('data_izin.status', $status['appv_sts']);
        } else {
            $this->db->group_start();
            if ($this->level_user == '1') {
                $this->db->where_in($status['appv_lvl'], $status['appv_sts']);
                $this->db->or_where_in('data_izin.status', $status['appv_sts']);
                $this->db->or_where('data_izin.id_karyawan', $this->id_karyawan);
            } else {
                $this->db->where_in($status['appv_lvl'], $status['appv_sts']);
                if (!in_array($status['appv_flt'], ['1', '2'])) {
                    if ($status['appv_flt'] == '0') {
                        $status['appv_sts'] = ['4'];
                    }
                    $this->db->or_where_in('data_izin.status', $status['appv_sts']);
                    $this->db->or_where('data_izin.id_karyawan', $this->id_karyawan);
                }
            }
            $this->db->group_end();
        }

        $column_search = array('data_karyawan.nama_lengkap', 'data_izin.nama_izin');
        $i = 0;

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

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

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

        $column_order = array('', 'data_izin.id_karyawan', 'data_izin.jenis_izin', 'data_izin.tgl_pengajuan');

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

    public function getDataById($id = null)
    {
        $select = $this->db->query("SELECT id_izin, izin.kode_izin, izin.nama_izin, izin.status, izin.id_karyawan, izin.ket_izin, izin.file, izin.tgl_mulai_izin, izin.tgl_selesai_izin, dk.nama_lengkap
                                    FROM $this->table AS izin
                                    JOIN data_karyawan AS dk ON dk.id_karyawan = izin.id_karyawan
                                    WHERE MD5(id_izin) = ?
                                    AND izin.id_company = '" . $this->id_company . "'", [$id])->row();
        return $select;
    }

    public function getDetailIzin($id_izin = null)
    {
        $this->db->select("data_izin.id_izin,
                            data_izin.tgl_pengajuan,
                            data_karyawan.nama_lengkap,
                            data_karyawan.nik,
                            data_izin.tgl_mulai_izin,
                            data_izin.tgl_selesai_izin,
                            data_izin.jml_izin,
                            data_izin.nama_izin,
                            data_izin.ket_izin,
                            data_izin.flag,
                            data_izin.status,
                            data_karyawan.jatah_cuti as sisa_cuti");
        $this->db->from("data_izin");
        $this->db->join("data_karyawan", "data_karyawan.id_karyawan = data_izin.id_karyawan");
        $this->db->where("MD5(data_izin.id_izin)", $id_izin);

        $query = $this->db->get();
        return $query->row();
    }

    public function save($data, $id = null)
    {
        $file = $data['file'];
        if ($id != null) {

            $cek_file_izin = $this->db->query("SELECT id_izin FROM file_izin WHERE md5(id_izin) = '$id' ")->num_rows();

            if ($cek_file_izin > 0) {
                if (!empty($file)) {
                    $this->db->set('file', $file);
                    $this->db->where(array('md5(id_izin)'=>$id, 'id_company'=>$this->id_company));
                    $this->db->update('file_izin');
                }
            }else{
                if (!empty($file)) {
                    $cek_id_izin = $this->db->query("SELECT id_izin FROM data_izin WHERE md5(id_izin) = '$id' ")->row()->id_izin;
                    $data_file_izin = array(
                                    'id_izin'    => $cek_id_izin,
                                    'file'       => $file,
                                    'id_company' => $this->id_company
                                );

                    $this->db->insert('file_izin', $data_file_izin);
                }
            }

            $this->db->where(array('md5(id_izin)' => $id, 'id_company' => $this->id_company));
            $update = $this->db->update($this->table, $data);
            $this->logger->create_log('data_izin', 'edit', $this->db->last_query());
            return $update;
        } else {
            $nextId = $this->fungsi->getNextId($this->table, 'id_izin', $this->id_company, 4);
            $id_izin = array('id_izin' => $nextId);
            $insert = $this->db->insert($this->table, array_merge($data, $id_izin));

            if (!empty($file)) {
                $this->insert_file_izin($file);
            }
            return $insert;
        }
    }

    public function insert_file_izin($foto_file)
    {
        $id_izin = $this->db->query("SELECT id_izin FROM data_izin WHERE file = '$foto_file'")->row()->id_izin;
        $data = array(
            'id_izin'    => $id_izin,
            'file'       => $foto_file,
            'id_company' => $this->id_company
        );
        return $this->db->insert('file_izin', $data);
    }

    public function delete($id = null)
    {

        $data_izin = $this->db->query("SELECT data_izin.id_karyawan, data_izin.tgl_mulai_izin, data_izin.tgl_selesai_izin, data_izin.nama_izin, data_izin.ket_izin, data_izin.status, data_izin.flag, data_izin.jml_izin, data_izin.saldo_ct_tahunan, data_izin.saldo_ct_cf, dk.jatah_cuti, dk.carry_forward
            FROM data_izin
            JOIN data_karyawan AS dk ON dk.id_karyawan = data_izin.id_karyawan
            WHERE MD5(data_izin.id_izin) = ?
            AND data_izin.id_company = '" . $this->id_company . "'", [$id])->row();

        $id_karyawan_kirim = $data_izin->id_karyawan;

        $tanggal_sekarang   = date('Y-m-d');

        $this->db->trans_start();

        if ($data_izin->status == '4') {

            if ($data_izin->tgl_mulai_izin >= $tanggal_sekarang) {
                $awal       = date_create($data_izin->tgl_mulai_izin);
                $akhir      = date_create(date('Y-m-d', strtotime('+1 days ' . $data_izin->tgl_selesai_izin)));
                $interval   = DateInterval::createFromDateString('1 day');
                $period     = new DatePeriod($awal, $interval, $akhir);

                foreach ($period as $dt) {
                    $tgg = $dt->format("Y-m-d");

                    // Hapus rekap absensi
                    $this->db->where(array('id_karyawan' => $data_izin->id_karyawan, 'tgl_absen' => $tgg));
                    $this->db->delete('r_absensi');

                    $this->db->where(array('id_company' => $this->id_company ,'id_karyawan' => $data_izin->id_karyawan, 'tgl_absen' => $tgg));
                    $this->db->delete('absensi_masuk');
                }

                if ($data_izin->flag == '1') {
                    // update saldo cuti data karyawan
                    // karyawan yang belum menggunakan carry forward
                    if ($data_izin->saldo_ct_tahunan == 0 && $data_izin->saldo_ct_cf == 0) {
                        $data_ct['jatah_cuti'] = ($data_izin->jatah_cuti + $data_izin->jml_izin);
                    } else {
                        if ($data_izin->saldo_ct_cf > 0) {
                            $data_ct['carry_forward'] = ($data_izin->carry_forward + $data_izin->saldo_ct_cf);
                        }
                        if ($data_izin->saldo_ct_tahunan > 0) {
                            $data_ct['jatah_cuti'] = ($data_izin->jatah_cuti + $data_izin->saldo_ct_tahunan);
                        }
                    }
                    $this->db->where('id_company', $this->id_company);
                    $this->db->where('id_karyawan', $id_karyawan_kirim);
                    $this->db->update('data_karyawan', $data_ct);

                    // insert history cuti
                    $nextId              = $this->fungsi->getNextId('cuti_history', 'id_cuti_history', $this->id_company, 3);
                    $saldo_cuti_karyawan = ($data_izin->jatah_cuti + $data_izin->jml_izin);
                    $data_history_cuti   = array(
                        'id_cuti_history' => $nextId,
                        'tgl_trx'         => $tanggal_sekarang,
                        'tambah'          => $data_izin->jml_izin,
                        'kurang'          => 0,
                        'saldo'           => $saldo_cuti_karyawan,
                        'keterangan'      => $data_izin->nama_izin . '(' . $data_izin->ket_izin . ') tanggal ' . date('d-m-Y', strtotime($data_izin->tgl_mulai_izin)) . ' s/d ' . date('d-m-Y', strtotime($data_izin->tgl_selesai_izin)) . ' telah dihapus.',
                        'id_karyawan'     => $data_izin->id_karyawan,
                        'user_input'      => $this->nik_karyawan . ' - ' . $this->nm_karyawan,
                        'id_company'      => $this->id_company
                    );

                    $this->db->insert('cuti_history', $data_history_cuti);
                }

                // Hapus Data Izin
                $this->db->where(array('md5(id_izin)' => $id));
                $this->db->delete($this->table);

                $this->logger->create_log('data_izin_approve_+_rekap_' . $id_karyawan_kirim, 'delete', $this->db->last_query());
            } else {
                // Hapus Data Izin
                $this->db->where(array('md5(id_izin)' => $id));
                $this->db->delete($this->table);

                $this->logger->create_log('data_izin_approve_' . $id_karyawan_kirim, 'delete', $this->db->last_query());
            }
        } else {
            // Hapus Data Izin
            $this->db->where(array('md5(id_izin)' => $id));
            $this->db->delete($this->table);

            $this->logger->create_log('data_izin_not_approve_' . $id_karyawan_kirim, 'delete', $this->db->last_query());
        }

        $predir_foto = $this->db->select("file")
                                    ->from('file_izin')
                                    ->where("md5(id_izin)",$id)
                                    ->get()
                                    ->row("file");

        $this->fungsi->delete_fileupload($predir_foto);
        $this->db->delete('file_izin', array('md5(id_izin)' => $id));

        $this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return __response_delete(false);
        } else {
            $this->db->trans_commit();
            return __response_delete(true);
        }
    }

    public function get_jenis($id = null, $search = null)
    {
        $id_company = $this->id_company;

        $this->db->select('nama, id_jenis_izin, kode_izin');
        $this->db->where('id_company', $id_company);
        $this->db->group_start();
        $this->db->like('nama', $search);
        $this->db->or_like('kode_izin', $search);
        $this->db->group_end();
        $this->db->order_by('id_jenis_izin', 'ASC');

        $result = $this->db->get('master_jenis_izin')->result();
        $json = [];
        $json[] = ['id' => 'SM', 'text' => 'Semua Jenis'];
        foreach ($result as $rows) {
            $json[] = ['id' => $rows->kode_izin, 'text' => $rows->nama . " (" . $rows->kode_izin . ")"];
        }
        echo json_encode($json);
    }

    public function get_jenis_form($id = null, $search = null)
    {
        $id_company = $this->id_company;

        $this->db->select('nama, id_jenis_izin, kode_izin');
        $this->db->where('id_company', $id_company);
        $this->db->group_start();
        $this->db->like('nama', $search);
        $this->db->or_like('kode_izin', $search);
        $this->db->group_end();
        $this->db->order_by('id_jenis_izin', 'ASC');

        $result = $this->db->get('master_jenis_izin')->result();
        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id' => $rows->kode_izin, 'text' => $rows->nama . " (" . $rows->kode_izin . ")"];
        }
        echo json_encode($json);
    }

    public function userApproval($id_karyawan = null)
    {
        $this->db->select("nik, nama_lengkap");
        $this->db->where("id_karyawan", $id_karyawan);
        $this->db->from("data_karyawan");
        $query = $this->db->get()->row()->nama_lengkap;
        return $query;
    }


    function cek_punya_shift_dan_tidak_libur($id_karyawan, $tanggal, $id_company)
    {
        $where = array(
            "data_shift_karyawan.id_karyawan" => $id_karyawan,
            "data_shift_karyawan.tanggal"     => $tanggal,
            "data_shift_karyawan.id_company"  => $id_company,
            "master_shift.kode_shift NOT IN ('O', 'OFF')" => NULL
        );
        $this->db->select("id_shift_karyawan")
            ->from("data_shift_karyawan")
            ->join("master_shift", "data_shift_karyawan.id_master_shift = master_shift.id_master_shift")
            ->where($where);
        return $this->db->get()->num_rows();
    }

    function cek_jadwal_kerja_libur($id_cabang, $tanggal, $id_company)
    {
        $where = array(
            "id_cabang"   => $id_cabang,
            "id_company"  => $id_company,
            "hari"        => date("w", strtotime($tanggal))
        );
        $this->db->select("libur")
            ->from("jam_kerja")
            ->where($where);
        return $this->db->get()->row()->libur;
    }


    function get_holidays($date_start, $date_end)
    {
        $this->db->select('tanggal');
        $this->db->from('holidays');
        $this->db->where('tanggal >=', $date_start);
        $this->db->where('tanggal <=', $date_end);
        $this->db->where('id_company', $this->id_company);
        return $this->db->get();
    }

    function shift_off($id_karyawan, $date_start, $date_end)
    {
        $this->db->select('tanggal');
        $this->db->from('data_shift_karyawan ds');
        $this->db->join('master_shift ms', 'ms.id_master_shift = ds.id_master_shift');
        $this->db->where('ms.libur', '1');
        $this->db->where('ds.tanggal >=', $date_start);
        $this->db->where('ds.tanggal <=', $date_end);
        $this->db->where('ds.id_karyawan', $id_karyawan);
        return $this->db->get();
    }

    function shift_masuk($id_karyawan, $date_start, $date_end)
    {
        $this->db->select('tanggal');
        $this->db->from('data_shift_karyawan ds');
        $this->db->join('master_shift ms', 'ms.id_master_shift = ds.id_master_shift');
        $this->db->where('ms.libur', '0');
        $this->db->where('ds.tanggal >=', $date_start);
        $this->db->where('ds.tanggal <=', $date_end);
        $this->db->where('ds.id_karyawan', $id_karyawan);
        return $this->db->get();
    }

    function week_holidays($id_karyawan)
    {
        $this->db->select('jk.hari');
        $this->db->from('jam_kerja jk');
        $this->db->join('data_karyawan k', 'k.id_cabang = jk.id_cabang');
        $this->db->where('k.id_karyawan', $id_karyawan);
        $this->db->where('jk.libur', 1);
        return $this->db->get();
    }

    function data_karyawan($id_karyawan)
    {
        $this->db->where('id_karyawan', $id_karyawan);
        return $this->db->get('data_karyawan')->row();
    }

    function detail_karyawan($id_karyawan)
    {
        $this->db->select('k.nik,k.nama_lengkap as nama_karyawan,k.id_cabang,c.kode as kode_cabang,
		c.nama as nama_cabang,k.id_departemen,d.kode as kode_departemen, d.nama as nama_departemen,
		k.id_jabatan,j.kode_jabatan,j.nama as nama_jabatan,k.supervisi,
		spv.nama_lengkap as nama_superivisi,k.id_company');
        $this->db->from('data_karyawan k');
        $this->db->join('master_cabang c', 'c.id_cabang = k.id_cabang', 'left');
        $this->db->join('master_departemen d', 'k.id_departemen = d.id_departemen', 'left');
        $this->db->join('master_jabatan j', 'j.id_jabatan = k.id_jabatan', 'left');
        $this->db->join('data_karyawan spv', 'k.supervisi = spv.id_karyawan', 'left');
        $this->db->where('k.id_karyawan', $id_karyawan);
        return $this->db->get()->row();
    }

    function detail_master_izin($id_jenis)
    {
        $this->db->select('max_izin, flag, flag_kalender');
        $this->db->from('master_jenis_izin');
        $this->db->where('kode_izin', $id_jenis);
        $this->db->where('id_company', $this->id_company);
        return $this->db->get()->row();
    }

    function update_jatah_cuti($id_karyawan, $potongan)
    {
        $this->db->set('jatah_cuti', 'jatah_cuti-' . $potongan, FALSE);
        $this->db->where('id_karyawan', $id_karyawan);
        $this->db->where('id_company', $this->id_company);
        $this->db->update('data_karyawan');
    }

    function update_jatah_cuti_carry_forward($id_karyawan, $saldo_cuti, $carry_forward)
    {
        $set_data = array(
            'jatah_cuti' => $saldo_cuti,
            'carry_forward' => $carry_forward
        );
        $this->db->where('id_karyawan', $id_karyawan);
        $this->db->where('id_company', $this->id_company);
        $this->db->update('data_karyawan', $set_data);
    }

    function hapus_absensi($id_karyawan, $tanggal_arr)
    {
        $this->db->where('id_karyawan', $id_karyawan);
        $this->db->where_in('tgl_absen', $tanggal_arr);
        $this->db->delete('absensi_masuk');

        $this->db->where('id_karyawan', $id_karyawan);
        $this->db->where_in('tgl_absen', $tanggal_arr);
        $this->db->delete('absensi_pulang');

        $this->db->where('id_karyawan', $id_karyawan);
        $this->db->where_in('tgl_absen', $tanggal_arr);
        $this->db->delete('istirahat_mulai');

        $this->db->where('id_karyawan', $id_karyawan);
        $this->db->where_in('tgl_absen', $tanggal_arr);
        $this->db->delete('istirahat_selesai');

        $this->db->where('id_karyawan', $id_karyawan);
        $this->db->where_in('tgl_absen', $tanggal_arr);
        $this->db->delete('r_absensi');
    }

    function download($tgl_mulai="0", $tgl_selesai="0", $jenis="0", $status="0", $id_karyawan="0", $id_cabang="0", $id_departemen="0"){
        $this->_get_datatables_query($tgl_mulai, $tgl_selesai, $jenis, $status, $id_karyawan, $id_cabang, $id_departemen);

        $query = $this->db->get();
        $result = $query->result();
        return $result;
    }
}
