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

class Rekap_absensi_download_m extends CI_Model {
    var $table = 'r_absensi';

    function download($tanggal_mulai, $tanggal_selesai, $cabang, $departemen){
        $this->_get_datatables_query($tanggal_mulai, $tanggal_selesai, $cabang, $departemen);
        $query = $this->db->get();
        return $query->result();
    }

    private function _get_datatables_query($tanggal_mulai, $tanggal_selesai, $cabang, $departemen){        
        $this->db->select("dp.id_karyawan, nama_lengkap");
        $akhir		= date_create($tanggal_selesai);
		$awal 		= date_create($tanggal_mulai);
		$selisih 	= date_diff($akhir, $awal)->format("%a");
		$hasil 		= array();
		if($selisih == 0) {
            $tanggal = date_format($awal, "Y-m-d");
            $this->db->select("(SELECT kode_absensi FROM r_absensi WHERE id_karyawan = dp.id_karyawan 
                AND tgl_absen = ".$this->db->escape($tanggal)." group by id_karyawan) as '".$tanggal."'");
		} else {
			$temp = $awal;
			for($a = 0; $a <= $selisih; $a++) {
                $tanggal = date_format($temp, "Y-m-d");
                $this->db->select("(SELECT kode_absensi FROM r_absensi WHERE id_karyawan = dp.id_karyawan 
                    AND tgl_absen = ".$this->db->escape($tanggal)." group by id_karyawan) as '".$tanggal."'");
				$temp = date_add($temp, date_interval_create_from_date_string("1 days"));
			}
        }
        $this->db->select('(SELECT COUNT(kode_absensi) FROM r_absensi WHERE tgl_absen >= '.$this->db->escape($tanggal_mulai).
            ' AND tgl_absen <= '.$this->db->escape($tanggal_selesai).' AND id_karyawan = dp.id_karyawan 
            AND kode_absensi = "H") as H');
        $this->db->select('(SELECT COUNT(kode_absensi) FROM r_absensi WHERE tgl_absen >= '.$this->db->escape($tanggal_mulai).
            ' AND tgl_absen <= '.$this->db->escape($tanggal_selesai).' AND id_karyawan = dp.id_karyawan 
            AND kode_absensi IN ("H,(T)", "H,(T),(PC)", "H,(T),(TAP)")) as T');
        $this->db->select('(SELECT COUNT(kode_absensi) FROM r_absensi WHERE tgl_absen >= '.$this->db->escape($tanggal_mulai).
            ' AND tgl_absen <= '.$this->db->escape($tanggal_selesai).' AND id_karyawan = dp.id_karyawan 
            AND kode_absensi IN ("H,(PC)", "H,(T),(PC)")) as PC');
        $this->db->select('(SELECT COUNT(kode_absensi) FROM r_absensi WHERE tgl_absen >= '.$this->db->escape($tanggal_mulai).
            ' AND tgl_absen <= '.$this->db->escape($tanggal_selesai).' AND id_karyawan = dp.id_karyawan 
            AND kode_absensi IN ("H,(TAP)", "H,(T),(TAP)")) as AP');
        $this->db->select('(SELECT COUNT(kode_absensi) FROM r_absensi WHERE tgl_absen >= '.$this->db->escape($tanggal_mulai).
            ' AND tgl_absen <= '.$this->db->escape($tanggal_selesai).' AND id_karyawan = dp.id_karyawan 
            AND kode_absensi = "I") as I');
        $this->db->select('(SELECT COUNT(kode_absensi) FROM r_absensi WHERE tgl_absen >= '.$this->db->escape($tanggal_mulai).
            ' AND tgl_absen <= '.$this->db->escape($tanggal_selesai).' AND id_karyawan = dp.id_karyawan 
            AND kode_absensi = "S") as S');
        $this->db->select('(SELECT COUNT(kode_absensi) FROM r_absensi WHERE tgl_absen >= '.$this->db->escape($tanggal_mulai).
            ' AND tgl_absen <= '.$this->db->escape($tanggal_selesai).' AND id_karyawan = dp.id_karyawan 
            AND kode_absensi = "C") as C');
        $this->db->select('(SELECT COUNT(kode_absensi) FROM r_absensi WHERE tgl_absen >= '.$this->db->escape($tanggal_mulai).
            ' AND tgl_absen <= '.$this->db->escape($tanggal_selesai).' AND id_karyawan = dp.id_karyawan 
            AND kode_absensi = "A") as A');
        $this->db->select('(SELECT COUNT(kode_absensi) FROM r_absensi WHERE tgl_absen >= '.$this->db->escape($tanggal_mulai).
            ' AND tgl_absen <= '.$this->db->escape($tanggal_selesai).' AND id_karyawan = dp.id_karyawan 
            AND kode_absensi = "L") as L');
        
        $this->db->from("data_karyawan dp");
        $this->db->join('master_jabatan', 'dp.id_jabatan = master_jabatan.id_jabatan');
        $this->db->where('id_cabang', $cabang);

        if ($departemen != 'null') {
            $this->db->where('id_departemen', $departemen);
        }
         $status = array('1', '2', '3');
        $level = array("1","6");

        $this->db->where_in('dp.status',$status);
        $this->db->where_not_in('master_jabatan.level_user',$level);
        $order_by = array("dp.id_karyawan"=>"ASC");
        $this->db->order_by(key($order_by), $order_by[key($order_by)]);
    }
}