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

class Crontab extends MY_Controller {
    public function __construct(){
		parent::__construct();

		$this->tgl_kemarin = date('Y-m-d',strtotime('-1 Days'));
		$this->tgl_kemarin_lusa = date('Y-m-d',strtotime('-2 Days'));
	}
    public function index(){
	}

    public function reguler(){

        $sql = "INSERT IGNORE INTO r_absensi
                                    (id_karyawan,
                                    nik,
                                    nama_karyawan,
                                    id_cabang,
                                    kode_cabang,
                                    nama_cabang,
                                    id_departemen,
                                    kode_departemen,
                                    nama_departemen,
                                    id_jabatan,
                                    kode_jabatan,
                                    nama_jabatan,
                                    supervisi,
                                    nama_supervisi,
                                    tgl_absen,
                                    jenis_absen,
                                    kode_absensi,
                                    ket_kode,
                                    jam_masuk_kerja,
                                    absen_masuk,
                                    terlambat,
                                    lokasi_masuk,
                                    timezone_masuk,
                                    gmt_masuk,
                                    lat_masuk,
                                    long_masuk,
                                    ket_absen_masuk,
                                    foto_masuk,
                                    absen_ist_mulai,
                                    lokasi_ist_mulai,
                                    timezone_ist_mulai,
                                    gmt_ist_mulai,
                                    lat_ist_mulai,
                                    long_ist_mulai,
                                    ket_ist_mulai,
                                    foto_ist_mulai,
                                    absen_ist_selesai,
                                    lokasi_ist_selesai,
                                    timezone_ist_selesai,
                                    gmt_ist_selesai,
                                    lat_ist_selesai,
                                    long_ist_selesai,
                                    ket_ist_selesai,
                                    foto_ist_selesai,
                                    jam_pulang_kerja,
                                    absen_pulang,
                                    pulang_cepat,
                                    lokasi_pulang,
                                    timezone_pulang,
                                    gmt_pulang,
                                    lat_pulang,
                                    long_pulang,
                                    ket_absen_pulang,
                                    foto_absen_pulang,
                                    id_company)
                            SELECT absensi_masuk.id_karyawan,
                                data_karyawan.nik,
                                data_karyawan.nama_lengkap AS nama_karyawan,
                                master_cabang.id_cabang,
                                master_cabang.kode AS kode_cabang,
                                master_cabang.nama AS nama_cabang,
                                IF(master_departemen.id_departemen IS NOT NULL AND master_departemen.id_departemen != '' AND master_departemen.id_departemen != 'null',master_departemen.id_departemen,'') AS id_departemen,
                                IF(master_departemen.kode IS NOT NULL AND master_departemen.kode !='' AND master_departemen.kode !='null',master_departemen.kode,'') AS kode_departemen,
                                IF(master_departemen.nama IS NOT NULL AND master_departemen.nama !='' AND master_departemen.nama !='null',master_departemen.nama,'') AS nama_departemen,
                                master_jabatan.id_jabatan,
                                master_jabatan.kode_jabatan,
                                master_jabatan.nama AS nama_jabatan,
                                data_karyawan.supervisi,
                                IF(data_karyawan.supervisi IS NOT NULL AND data_karyawan.supervisi != '' AND data_karyawan.supervisi != 'null',(SELECT nama_lengkap FROM data_karyawan WHERE id_karyawan = data_karyawan.supervisi),'') AS nama_supervisi,
                                absensi_masuk.tgl_absen,
                                absensi_masuk.jenis_absen,
                                IF((absensi_pulang.pulang_cepat > 0),CONCAT(absensi_masuk.kode_absen,',(PC)'),
                                    IF((absensi_pulang.jam_absen IS NULL),CONCAT(absensi_masuk.kode_absen,',(TAP)'),absensi_masuk.kode_absen)) AS kode_absensi,
                                'Reguler' AS ket_kode,
                                IF(absensi_masuk.jam_kerja= '00:00:00','',absensi_masuk.jam_kerja) AS jam_masuk_kerja,
                                absensi_masuk.jam_absen AS absen_masuk,
                                absensi_masuk.terlambat,
                                absensi_masuk.lokasi_absen AS lokasi_masuk,
                                absensi_masuk.timezone AS timezone_masuk,
                                absensi_masuk.gmt AS gmt_masuk,
                                absensi_masuk.latitude AS lat_masuk,
                                absensi_masuk.longitude AS long_masuk,
                                absensi_masuk.keterangan AS ket_masuk,
                                absensi_masuk.foto AS foto_masuk,
                                IF(istirahat_mulai.jam_absen IS NULL,'',istirahat_mulai.jam_absen) AS ist_mulai,
                                IF(istirahat_mulai.lokasi_absen IS NULL,'',istirahat_mulai.lokasi_absen) AS lokasi_ist_mulai,
                                IF(istirahat_mulai.timezone IS NULL,'',istirahat_mulai.timezone) AS timezone_ist_mulai,
                                IF(istirahat_mulai.gmt IS NULL,'',istirahat_mulai.gmt) AS gmt_ist_mulai,
                                IF(istirahat_mulai.latitude IS NULL,'',istirahat_mulai.latitude) AS lat_ist_mulai,
                                IF(istirahat_mulai.longitude IS NULL,'',istirahat_mulai.longitude) AS long_ist_mulai,
                                IF(istirahat_mulai.keterangan IS NULL,'',istirahat_mulai.keterangan) AS ket_ist_mulai,
                                IF(istirahat_mulai.foto IS NULL,'',istirahat_mulai.foto) AS foto_ist_mulai,
                                IF(istirahat_selesai.jam_absen IS NULL,'',istirahat_selesai.jam_absen) AS ist_selesai,
                                IF(istirahat_selesai.lokasi_absen IS NULL,'',istirahat_selesai.lokasi_absen) AS lokasi_ist_selesai,
                                IF(istirahat_selesai.timezone IS NULL,'',istirahat_selesai.timezone) AS timezone_ist_selesai,
                                IF(istirahat_selesai.gmt IS NULL,'',istirahat_selesai.gmt) AS gmt_ist_selesai,
                                IF(istirahat_selesai.latitude IS NULL,'',istirahat_selesai.latitude) AS lat_ist_selesai,
                                IF(istirahat_selesai.longitude IS NULL,'',istirahat_selesai.longitude) AS long_ist_selesai,
                                IF(istirahat_selesai.keterangan IS NULL,'',istirahat_selesai.keterangan) AS ket_ist_selesai,
                                IF(istirahat_selesai.foto IS NULL,'',istirahat_selesai.foto) AS foto_ist_selesai,
                                IF(absensi_pulang.jam_kerja IS NULL,'',absensi_pulang.jam_kerja) AS jadwal_pulang,
                                IF(absensi_pulang.jam_absen IS NULL,'',absensi_pulang.jam_absen) AS absen_pulang,
                                IF(absensi_pulang.pulang_cepat IS NULL,'',absensi_pulang.pulang_cepat) AS pulang_cepat,
                                IF(absensi_pulang.lokasi_absen IS NULL,'',absensi_pulang.lokasi_absen) AS lokasi_pulang,
                                IF(absensi_pulang.timezone IS NULL,'',absensi_pulang.timezone) AS timezone_pulang,
                                IF(absensi_pulang.gmt IS NULL,'',absensi_pulang.gmt) AS gmt_pulang,
                                IF(absensi_pulang.latitude IS NULL,'',absensi_pulang.latitude) AS lat_pulang,
                                IF(absensi_pulang.longitude IS NULL,'',absensi_pulang.longitude) AS long_pulang,
                                IF(absensi_pulang.keterangan IS NULL,'',absensi_pulang.keterangan) AS ket_pulang,
                                IF(absensi_pulang.foto IS NULL,'',absensi_pulang.foto) AS foto_pulang,
                                data_karyawan.id_company
                            FROM absensi_masuk
                            LEFT JOIN istirahat_mulai ON istirahat_mulai.id_absensi_masuk = absensi_masuk.id_absensi_masuk
                            LEFT JOIN istirahat_selesai ON istirahat_selesai.id_absensi_masuk = absensi_masuk.id_absensi_masuk
                            LEFT JOIN absensi_pulang ON absensi_pulang.id_masuk = absensi_masuk.id_absensi_masuk
                            LEFT JOIN data_karyawan ON data_karyawan.id_karyawan = absensi_masuk.id_karyawan
                            LEFT JOIN master_cabang ON master_cabang.id_cabang = data_karyawan.id_cabang
                            LEFT JOIN master_departemen ON master_departemen.id_departemen = data_karyawan.id_departemen
                            LEFT JOIN master_jabatan ON master_jabatan.id_jabatan = data_karyawan.id_jabatan
                            WHERE absensi_masuk.jenis_absen = 'reguler'
                            AND absensi_masuk.tgl_absen = '$this->tgl_kemarin' ";

        return $this->db->query($sql);

    }

    public function alpha(){ # KONDISI TIDAK MASUK DAN JAM REGULER MASUK
        $tgl_kemarin  = date('Y-m-d',strtotime('-1 Days'));
        $hari         = date_format(date_create($tgl_kemarin),'N');
        $tgl_berakhir = date('Y-m-d', strtotime('-7 Days', strtotime($tgl_kemarin)));

        $sql = "INSERT IGNORE INTO r_absensi (id_karyawan,
                                        nik,
                                        nama_karyawan,
                                        id_cabang,
                                        kode_cabang,
                                        nama_cabang,
                                        id_departemen,
                                        kode_departemen,
                                        nama_departemen,
                                        id_jabatan,
                                        kode_jabatan,
                                        nama_jabatan,
                                        supervisi,
                                        nama_supervisi,
                                        tgl_absen,
                                        jenis_absen,
                                        kode_absensi,
                                        ket_kode,
                                        id_company)
                                SELECT 	dk.id_karyawan,
                                    dk.nik,
                                    dk.nama_lengkap,
                                    dk.id_cabang,
                                    mc.kode AS kode_cabang,
                                    mc.nama AS nama_cabang,
                                    IF(dk.id_departemen IS NOT NULL AND dk.id_departemen != '' AND dk.id_departemen != 'null',dk.id_departemen,'') AS id_departemen,
                                    IF(md.kode IS NOT NULL AND md.kode !='' AND md.kode !='null',md.kode,'') AS kode_departemen,
                                    IF(md.nama IS NOT NULL AND md.nama !='' AND md.nama !='null',md.nama,'') AS nama_departemen,
                                    dk.id_jabatan,
                                    mj.kode_jabatan,
                                    mj.nama AS nama_jabatan,
                                    dk.supervisi,
                                    IF(dk.supervisi IS NOT NULL AND dk.supervisi != '' AND dk.supervisi != 'null',(SELECT nama_lengkap FROM data_karyawan WHERE id_karyawan = dk.supervisi),'') AS nama_supervisi,
                                    '$tgl_kemarin' AS tgl_absen,
                                    'alpha' AS jenis_absen,
                                    'A' AS kode_absensi,
                                    'Alpha' AS ket_kode,
                                    dk.id_company
                                FROM (select dk1.*,mp.tgl_berakhir from data_karyawan as dk1
                                     join master_plan as mp on mp.id_company = dk1.id_company
                                     where mp.tgl_berakhir >= '$tgl_berakhir') AS dk
                                JOIN jam_kerja AS jk ON jk.id_cabang = dk.id_cabang AND jk.id_company = dk.id_company
                                LEFT JOIN master_cabang AS mc ON mc.id_cabang = dk.id_cabang AND mc.id_company = dk.id_company
                                LEFT JOIN master_departemen AS md ON md.id_departemen = dk.id_departemen AND md.id_company = dk.id_company
                                LEFT JOIN master_jabatan AS mj ON mj.id_jabatan = dk.id_jabatan AND mj.id_company = dk.id_company
                                WHERE dk.status IN('1','2','3','4')
                                AND mj.level_user NOT IN ('1','6')
                                AND jk.hari = '$hari'
                                AND jk.libur = '0'
                                AND dk.id_karyawan NOT IN (SELECT id_karyawan FROM absensi_masuk WHERE tgl_absen = '$tgl_kemarin')
                                AND dk.id_karyawan NOT IN (SELECT dsk.id_karyawan
                                                FROM data_shift_karyawan AS dsk
                                                JOIN master_shift AS ms ON ms.id_master_shift = dsk.id_master_shift
                                                WHERE ms.libur = '1'
                                                AND dsk.tanggal = '$tgl_kemarin')
                                AND dk.id_company NOT IN (SELECT id_company FROM holidays WHERE holidays.tanggal = '$tgl_kemarin')";
        return $this->db->query($sql);
    }

    public function alpha2(){ # KONDISI TIDAK MASUK SHIFT DAN JAM REGULER LIBUR
        $tgl_kemarin = date('Y-m-d',strtotime('-1 Days'));
        $hari = date_format(date_create($tgl_kemarin),'N');
        $tgl_berakhir = date('Y-m-d', strtotime('-7 Days', strtotime($tgl_kemarin)));

        $sql = "INSERT IGNORE INTO r_absensi (id_karyawan,
                                        nik,
                                        nama_karyawan,
                                        id_cabang,
                                        kode_cabang,
                                        nama_cabang,
                                        id_departemen,
                                        kode_departemen,
                                        nama_departemen,
                                        id_jabatan,
                                        kode_jabatan,
                                        nama_jabatan,
                                        supervisi,
                                        nama_supervisi,
                                        tgl_absen,
                                        jenis_absen,
                                        kode_absensi,
                                        ket_kode,
                                        id_company)
                                SELECT 	dk.id_karyawan,
                                    dk.nik,
                                    dk.nama_lengkap,
                                    dk.id_cabang,
                                    mc.kode AS kode_cabang,
                                    mc.nama AS nama_cabang,
                                    IF(dk.id_departemen IS NOT NULL AND dk.id_departemen != '' AND dk.id_departemen != 'null',dk.id_departemen,'') AS id_departemen,
                                    IF(md.kode IS NOT NULL AND md.kode !='' AND md.kode !='null',md.kode,'') AS kode_departemen,
                                    IF(md.nama IS NOT NULL AND md.nama !='' AND md.nama !='null',md.nama,'') AS nama_departemen,
                                    dk.id_jabatan,
                                    mj.kode_jabatan,
                                    mj.nama AS nama_jabatan,
                                    dk.supervisi,
                                    IF(dk.supervisi IS NOT NULL AND dk.supervisi != '' AND dk.supervisi != 'null',(SELECT nama_lengkap FROM data_karyawan WHERE id_karyawan = dk.supervisi),'') AS nama_supervisi,
                                    '$tgl_kemarin' AS tgl_absen,
                                    'alpha' AS jenis_absen,
                                    'A' AS kode_absensi,
                                    'Alpha' AS ket_kode,
                                    dk.id_company
                                FROM (select dk1.*,mp.tgl_berakhir from data_karyawan as dk1
                                     join master_plan as mp on mp.id_company = dk1.id_company
                                     where mp.tgl_berakhir >= '$tgl_berakhir') AS dk
                                LEFT JOIN master_cabang AS mc ON mc.id_cabang = dk.id_cabang AND mc.id_company = dk.id_company
                                LEFT JOIN master_departemen AS md ON md.id_departemen = dk.id_departemen AND md.id_company = dk.id_company
                                LEFT JOIN master_jabatan AS mj ON mj.id_jabatan = dk.id_jabatan AND mj.id_company = dk.id_company
                                WHERE dk.status IN('1','2','3','4')
                                AND mj.level_user NOT IN ('1','6')
                                AND dk.id_karyawan NOT IN (SELECT id_karyawan FROM absensi_masuk WHERE tgl_absen = '$tgl_kemarin')
                                AND dk.id_karyawan IN (SELECT dsk.id_karyawan
                                                FROM data_shift_karyawan AS dsk
                                                JOIN master_shift AS ms ON ms.id_master_shift = dsk.id_master_shift
                                                WHERE ms.libur = '0'
                                                AND dsk.tanggal = '$tgl_kemarin')";
        return $this->db->query($sql);
    }

    public function libur(){
        $tgl_kemarin = date('Y-m-d',strtotime('-1 Days'));
        $hari = date_format(date_create($tgl_kemarin),'N');
        $tgl_berakhir = date('Y-m-d', strtotime('-7 Days', strtotime($tgl_kemarin)));

        $sql = "INSERT IGNORE INTO r_absensi (id_karyawan,
                                        nik,
                                        nama_karyawan,
                                        id_cabang,
                                        kode_cabang,
                                        nama_cabang,
                                        id_departemen,
                                        kode_departemen,
                                        nama_departemen,
                                        id_jabatan,
                                        kode_jabatan,
                                        nama_jabatan,
                                        supervisi,
                                        nama_supervisi,
                                        tgl_absen,
                                        jenis_absen,
                                        kode_absensi,
                                        ket_kode,
                                        id_company)
                                SELECT 	dk.id_karyawan,
                                    dk.nik,
                                    dk.nama_lengkap,
                                    dk.id_cabang,
                                    mc.kode AS kode_cabang,
                                    mc.nama AS nama_cabang,
                                    IF(dk.id_departemen IS NOT NULL AND dk.id_departemen != '' AND dk.id_departemen != 'null',dk.id_departemen,'') AS id_departemen,
                                    IF(md.kode IS NOT NULL AND md.kode !='' AND md.kode !='null',md.kode,'') AS kode_departemen,
                                    IF(md.nama IS NOT NULL AND md.nama !='' AND md.nama !='null',md.nama,'') AS nama_departemen,
                                    dk.id_jabatan,
                                    mj.kode_jabatan,
                                    mj.nama AS nama_jabatan,
                                    dk.supervisi,
                                    IF(dk.supervisi IS NOT NULL AND dk.supervisi != '' AND dk.supervisi != 'null',(SELECT nama_lengkap FROM data_karyawan WHERE id_karyawan = dk.supervisi),'') AS nama_supervisi,
                                    '$tgl_kemarin' AS tgl_absen,
                                    'libur' AS jenis_absen,
                                    'L' AS kode_absensi,
                                    'Libur' AS ket_kode,
                                    dk.id_company
                                FROM (select dk1.*,mp.tgl_berakhir from data_karyawan as dk1
                                     join master_plan as mp on mp.id_company = dk1.id_company
                                     where mp.tgl_berakhir >= '$tgl_berakhir') AS dk
                                JOIN jam_kerja AS jk ON jk.id_cabang = dk.id_cabang AND jk.id_company = dk.id_company
                                LEFT JOIN master_cabang AS mc ON mc.id_cabang = dk.id_cabang AND mc.id_company = dk.id_company
                                LEFT JOIN master_departemen AS md ON md.id_departemen = dk.id_departemen AND md.id_company = dk.id_company
                                LEFT JOIN master_jabatan AS mj ON mj.id_jabatan = dk.id_jabatan AND mj.id_company = dk.id_company
                                WHERE dk.status IN('1','2','3','4')
                                AND mj.level_user NOT IN ('1','6')
                                AND jk.hari = '$hari'
                                AND jk.libur = '1'
                                AND dk.id_karyawan NOT IN (SELECT id_karyawan FROM absensi_masuk WHERE tgl_absen = '$tgl_kemarin')
                                AND dk.id_karyawan NOT IN (SELECT dsk.id_karyawan FROM data_shift_karyawan AS dsk WHERE dsk.tanggal = '$tgl_kemarin')";
        return $this->db->query($sql);
    }


    public function libur_nasional(){
        $tgl_kemarin = date('Y-m-d',strtotime('-1 Days'));
        $tgl_berakhir = date('Y-m-d', strtotime('-7 Days', strtotime($tgl_kemarin)));

        $sql = "INSERT IGNORE INTO r_absensi (id_karyawan,
                                        nik,
                                        nama_karyawan,
                                        id_cabang,
                                        kode_cabang,
                                        nama_cabang,
                                        id_departemen,
                                        kode_departemen,
                                        nama_departemen,
                                        id_jabatan,
                                        kode_jabatan,
                                        nama_jabatan,
                                        supervisi,
                                        nama_supervisi,
                                        tgl_absen,
                                        jenis_absen,
                                        kode_absensi,
                                        ket_kode,
                                        id_company)
                                SELECT 	dk.id_karyawan,
                                    dk.nik,
                                    dk.nama_lengkap,
                                    dk.id_cabang,
                                    mc.kode AS kode_cabang,
                                    mc.nama AS nama_cabang,
                                    IF(dk.id_departemen IS NOT NULL AND dk.id_departemen != '' AND dk.id_departemen != 'null',dk.id_departemen,'') AS id_departemen,
                                    IF(md.kode IS NOT NULL AND md.kode !='' AND md.kode !='null',md.kode,'') AS kode_departemen,
                                    IF(md.nama IS NOT NULL AND md.nama !='' AND md.nama !='null',md.nama,'') AS nama_departemen,
                                    dk.id_jabatan,
                                    mj.kode_jabatan,
                                    mj.nama AS nama_jabatan,
                                    dk.supervisi,
                                    IF(dk.supervisi IS NOT NULL AND dk.supervisi != '' AND dk.supervisi != 'null',(SELECT nama_lengkap FROM data_karyawan WHERE id_karyawan = dk.supervisi),'') AS nama_supervisi,
                                    '$tgl_kemarin' AS tgl_absen,
                                    'libur' AS jenis_absen,
                                    'L' AS kode_absensi,
                                    'Libur' AS ket_kode,
                                    dk.id_company
                                FROM (select dk1.*,mp.tgl_berakhir from data_karyawan as dk1
                                     join master_plan as mp on mp.id_company = dk1.id_company
                                     where mp.tgl_berakhir >= '$tgl_berakhir') AS dk
                                LEFT JOIN master_cabang AS mc ON mc.id_cabang = dk.id_cabang AND mc.id_company = dk.id_company
                                LEFT JOIN master_departemen AS md ON md.id_departemen = dk.id_departemen AND md.id_company = dk.id_company
                                LEFT JOIN master_jabatan AS mj ON mj.id_jabatan = dk.id_jabatan AND mj.id_company = dk.id_company
                                WHERE dk.status IN('1','2','3','4')
                                AND mj.level_user NOT IN ('1','6')
                                AND dk.id_karyawan NOT IN (SELECT id_karyawan FROM absensi_masuk WHERE tgl_absen = '$tgl_kemarin')
                                AND dk.id_karyawan NOT IN (SELECT dsk.id_karyawan FROM data_shift_karyawan AS dsk WHERE dsk.tanggal = '$tgl_kemarin')
                                AND dk.id_company IN (SELECT id_company FROM holidays WHERE holidays.tanggal = '$tgl_kemarin')";
        return $this->db->query($sql);
    }

    public function shift(){
        $tgl_kemarin = date('Y-m-d',strtotime('-1 Days'));

        $sql = "INSERT IGNORE INTO r_absensi
                                    (id_karyawan,
                                    nik,
                                    nama_karyawan,
                                    id_cabang,
                                    kode_cabang,
                                    nama_cabang,
                                    id_departemen,
                                    kode_departemen,
                                    nama_departemen,
                                    id_jabatan,
                                    kode_jabatan,
                                    nama_jabatan,
                                    supervisi,
                                    nama_supervisi,
                                    tgl_absen,
                                    jenis_absen,
                                    kode_absensi,
                                    ket_kode,
                                    jam_masuk_kerja,
                                    absen_masuk,
                                    terlambat,
                                    lokasi_masuk,
                                    timezone_masuk,
                                    gmt_masuk,
                                    lat_masuk,
                                    long_masuk,
                                    ket_absen_masuk,
                                    foto_masuk,
                                    absen_ist_mulai,
                                    lokasi_ist_mulai,
                                    timezone_ist_mulai,
                                    gmt_ist_mulai,
                                    lat_ist_mulai,
                                    long_ist_mulai,
                                    ket_ist_mulai,
                                    foto_ist_mulai,
                                    absen_ist_selesai,
                                    lokasi_ist_selesai,
                                    timezone_ist_selesai,
                                    gmt_ist_selesai,
                                    lat_ist_selesai,
                                    long_ist_selesai,
                                    ket_ist_selesai,
                                    foto_ist_selesai,
                                    jam_pulang_kerja,
                                    absen_pulang,
                                    pulang_cepat,
                                    lokasi_pulang,
                                    timezone_pulang,
                                    gmt_pulang,
                                    lat_pulang,
                                    long_pulang,
                                    ket_absen_pulang,
                                    foto_absen_pulang,
                                    id_company)
                                    SELECT absensi_masuk.id_karyawan,
                                            data_karyawan.nik,
                                            data_karyawan.nama_lengkap AS nama_karyawan,
                                            master_cabang.id_cabang,
                                            master_cabang.kode AS kode_cabang,
                                            master_cabang.nama AS nama_cabang,
                                            IF(master_departemen.id_departemen IS NOT NULL AND master_departemen.id_departemen != '' AND master_departemen.id_departemen != 'null',master_departemen.id_departemen,'') AS id_departemen,
                                            IF(master_departemen.kode IS NOT NULL AND master_departemen.kode !='' AND master_departemen.kode !='null',master_departemen.kode,'') AS kode_departemen,
                                            IF(master_departemen.nama IS NOT NULL AND master_departemen.nama !='' AND master_departemen.nama !='null',master_departemen.nama,'') AS nama_departemen,
                                            master_jabatan.id_jabatan,
                                            master_jabatan.kode_jabatan,
                                            master_jabatan.nama AS nama_jabatan,
                                            data_karyawan.supervisi,
                                            IF(data_karyawan.supervisi IS NOT NULL AND data_karyawan.supervisi != '' AND data_karyawan.supervisi != 'null',(SELECT nama_lengkap FROM data_karyawan WHERE id_karyawan = data_karyawan.supervisi),'') AS nama_supervisi,
                                            absensi_masuk.tgl_absen,
                                            absensi_masuk.jenis_absen,
                                            IF((absensi_pulang.pulang_cepat > 0),CONCAT(absensi_masuk.kode_absen,',(PC)'),
                                                IF((absensi_pulang.jam_absen IS NULL),CONCAT(absensi_masuk.kode_absen,',(TAP)'),absensi_masuk.kode_absen)) AS kode_absensi,
                                            'Shift' AS ket_kode,
                                            IF(absensi_masuk.jam_kerja= '00:00:00','',absensi_masuk.jam_kerja) AS jam_masuk_kerja,
                                            absensi_masuk.jam_absen AS absen_masuk,
                                            absensi_masuk.terlambat,
                                            absensi_masuk.lokasi_absen AS lokasi_masuk,
                                            absensi_masuk.timezone AS timezone_masuk,
                                            absensi_masuk.gmt AS gmt_masuk,
                                            absensi_masuk.latitude AS lat_masuk,
                                            absensi_masuk.longitude AS long_masuk,
                                            absensi_masuk.keterangan AS ket_masuk,
                                            absensi_masuk.foto AS foto_masuk,
                                            IF(istirahat_mulai.jam_absen IS NULL,'',istirahat_mulai.jam_absen) AS ist_mulai,
                                            IF(istirahat_mulai.lokasi_absen IS NULL,'',istirahat_mulai.lokasi_absen) AS lokasi_ist_mulai,
                                            IF(istirahat_mulai.timezone IS NULL,'',istirahat_mulai.timezone) AS timezone_ist_mulai,
                                            IF(istirahat_mulai.gmt IS NULL,'',istirahat_mulai.gmt) AS gmt_ist_mulai,
                                            IF(istirahat_mulai.latitude IS NULL,'',istirahat_mulai.latitude) AS lat_ist_mulai,
                                            IF(istirahat_mulai.longitude IS NULL,'',istirahat_mulai.longitude) AS long_ist_mulai,
                                            IF(istirahat_mulai.keterangan IS NULL,'',istirahat_mulai.keterangan) AS ket_ist_mulai,
                                            IF(istirahat_mulai.foto IS NULL,'',istirahat_mulai.foto) AS foto_ist_mulai,
                                            IF(istirahat_selesai.jam_absen IS NULL,'',istirahat_selesai.jam_absen) AS ist_selesai,
                                            IF(istirahat_selesai.lokasi_absen IS NULL,'',istirahat_selesai.lokasi_absen) AS lokasi_ist_selesai,
                                            IF(istirahat_selesai.timezone IS NULL,'',istirahat_selesai.timezone) AS timezone_ist_selesai,
                                            IF(istirahat_selesai.gmt IS NULL,'',istirahat_selesai.gmt) AS gmt_ist_selesai,
                                            IF(istirahat_selesai.latitude IS NULL,'',istirahat_selesai.latitude) AS lat_ist_selesai,
                                            IF(istirahat_selesai.longitude IS NULL,'',istirahat_selesai.longitude) AS long_ist_selesai,
                                            IF(istirahat_selesai.keterangan IS NULL,'',istirahat_selesai.keterangan) AS ket_ist_selesai,
                                            IF(istirahat_selesai.foto IS NULL,'',istirahat_selesai.foto) AS foto_ist_selesai,
                                            IF(absensi_pulang.jam_kerja IS NULL,'',absensi_pulang.jam_kerja) AS jadwal_pulang,
                                            IF(absensi_pulang.jam_absen IS NULL,'',absensi_pulang.jam_absen) AS absen_pulang,
                                            IF(absensi_pulang.pulang_cepat IS NULL,'',absensi_pulang.pulang_cepat) AS pulang_cepat,
                                            IF(absensi_pulang.lokasi_absen IS NULL,'',absensi_pulang.lokasi_absen) AS lokasi_pulang,
                                            IF(absensi_pulang.timezone IS NULL,'',absensi_pulang.timezone) AS timezone_pulang,
                                            IF(absensi_pulang.gmt IS NULL,'',absensi_pulang.gmt) AS gmt_pulang,
                                            IF(absensi_pulang.latitude IS NULL,'',absensi_pulang.latitude) AS lat_pulang,
                                            IF(absensi_pulang.longitude IS NULL,'',absensi_pulang.longitude) AS long_pulang,
                                            IF(absensi_pulang.keterangan IS NULL,'',absensi_pulang.keterangan) AS ket_pulang,
                                            IF(absensi_pulang.foto IS NULL,'',absensi_pulang.foto) AS foto_pulang,
                                            data_karyawan.id_company
                                FROM absensi_masuk
                                LEFT JOIN istirahat_mulai ON istirahat_mulai.id_absensi_masuk = absensi_masuk.id_absensi_masuk
                                LEFT JOIN istirahat_selesai ON istirahat_selesai.id_absensi_masuk = absensi_masuk.id_absensi_masuk
                                LEFT JOIN absensi_pulang ON absensi_pulang.id_masuk = absensi_masuk.id_absensi_masuk
                                LEFT JOIN data_karyawan ON data_karyawan.id_karyawan = absensi_masuk.id_karyawan
                                LEFT JOIN master_cabang ON master_cabang.id_cabang = data_karyawan.id_cabang
                                LEFT JOIN master_departemen ON master_departemen.id_departemen = data_karyawan.id_departemen
                                LEFT JOIN master_jabatan ON master_jabatan.id_jabatan = data_karyawan.id_jabatan
                                JOIN data_shift_karyawan AS dsk ON dsk.id_karyawan = absensi_masuk.id_karyawan
                                JOIN master_shift AS ms ON ms.id_master_shift = dsk.id_master_shift
                                WHERE absensi_masuk.jenis_absen = 'shift'
                                AND dsk.tanggal = '$tgl_kemarin'
                                AND ms.flag_batas_absen_pulang = '0'
                                AND ms.flag_jam_pulang = '0'
                                AND absensi_masuk.tgl_absen = '$tgl_kemarin' ";

        return $this->db->query($sql);
    }

    public function shift_lewat_hari1(){ # BATAS ABSEN ABSENSI PULANG LEWAT HARI

        $sql = "INSERT IGNORE INTO r_absensi
                                    (id_karyawan,
                                    nik,
                                    nama_karyawan,
                                    id_cabang,
                                    kode_cabang,
                                    nama_cabang,
                                    id_departemen,
                                    kode_departemen,
                                    nama_departemen,
                                    id_jabatan,
                                    kode_jabatan,
                                    nama_jabatan,
                                    supervisi,
                                    nama_supervisi,
                                    tgl_absen,
                                    jenis_absen,
                                    kode_absensi,
                                    ket_kode,
                                    jam_masuk_kerja,
                                    absen_masuk,
                                    terlambat,
                                    lokasi_masuk,
                                    timezone_masuk,
                                    gmt_masuk,
                                    lat_masuk,
                                    long_masuk,
                                    ket_absen_masuk,
                                    foto_masuk,
                                    absen_ist_mulai,
                                    lokasi_ist_mulai,
                                    timezone_ist_mulai,
                                    gmt_ist_mulai,
                                    lat_ist_mulai,
                                    long_ist_mulai,
                                    ket_ist_mulai,
                                    foto_ist_mulai,
                                    absen_ist_selesai,
                                    lokasi_ist_selesai,
                                    timezone_ist_selesai,
                                    gmt_ist_selesai,
                                    lat_ist_selesai,
                                    long_ist_selesai,
                                    ket_ist_selesai,
                                    foto_ist_selesai,
                                    jam_pulang_kerja,
                                    absen_pulang,
                                    pulang_cepat,
                                    lokasi_pulang,
                                    timezone_pulang,
                                    gmt_pulang,
                                    lat_pulang,
                                    long_pulang,
                                    ket_absen_pulang,
                                    foto_absen_pulang,
                                    id_company)
                                    SELECT absensi_masuk.id_karyawan,
                                            data_karyawan.nik,
                                            data_karyawan.nama_lengkap AS nama_karyawan,
                                            master_cabang.id_cabang,
                                            master_cabang.kode AS kode_cabang,
                                            master_cabang.nama AS nama_cabang,
                                            IF(master_departemen.id_departemen IS NOT NULL AND master_departemen.id_departemen != '' AND master_departemen.id_departemen != 'null',master_departemen.id_departemen,'') AS id_departemen,
                                            IF(master_departemen.kode IS NOT NULL AND master_departemen.kode !='' AND master_departemen.kode !='null',master_departemen.kode,'') AS kode_departemen,
                                            IF(master_departemen.nama IS NOT NULL AND master_departemen.nama !='' AND master_departemen.nama !='null',master_departemen.nama,'') AS nama_departemen,
                                            master_jabatan.id_jabatan,
                                            master_jabatan.kode_jabatan,
                                            master_jabatan.nama AS nama_jabatan,
                                            data_karyawan.supervisi,
                                            IF(data_karyawan.supervisi IS NOT NULL AND data_karyawan.supervisi != '' AND data_karyawan.supervisi != 'null',(SELECT nama_lengkap FROM data_karyawan WHERE id_karyawan = data_karyawan.supervisi),'') AS nama_supervisi,
                                            absensi_masuk.tgl_absen,
                                            absensi_masuk.jenis_absen,
                                            IF((absensi_pulang.pulang_cepat > 0),CONCAT(absensi_masuk.kode_absen,',(PC)'),
                                                IF((absensi_pulang.jam_absen IS NULL),CONCAT(absensi_masuk.kode_absen,',(TAP)'),absensi_masuk.kode_absen)) AS kode_absensi,
                                            'Shift' AS ket_kode,
                                            IF(absensi_masuk.jam_kerja= '00:00:00','',absensi_masuk.jam_kerja) AS jam_masuk_kerja,
                                            absensi_masuk.jam_absen AS absen_masuk,
                                            absensi_masuk.terlambat,
                                            absensi_masuk.lokasi_absen AS lokasi_masuk,
                                            absensi_masuk.timezone AS timezone_masuk,
                                            absensi_masuk.gmt AS gmt_masuk,
                                            absensi_masuk.latitude AS lat_masuk,
                                            absensi_masuk.longitude AS long_masuk,
                                            absensi_masuk.keterangan AS ket_masuk,
                                            absensi_masuk.foto AS foto_masuk,
                                            IF(istirahat_mulai.jam_absen IS NULL,'',istirahat_mulai.jam_absen) AS ist_mulai,
                                            IF(istirahat_mulai.lokasi_absen IS NULL,'',istirahat_mulai.lokasi_absen) AS lokasi_ist_mulai,
                                            IF(istirahat_mulai.timezone IS NULL,'',istirahat_mulai.timezone) AS timezone_ist_mulai,
                                            IF(istirahat_mulai.gmt IS NULL,'',istirahat_mulai.gmt) AS gmt_ist_mulai,
                                            IF(istirahat_mulai.latitude IS NULL,'',istirahat_mulai.latitude) AS lat_ist_mulai,
                                            IF(istirahat_mulai.longitude IS NULL,'',istirahat_mulai.longitude) AS long_ist_mulai,
                                            IF(istirahat_mulai.keterangan IS NULL,'',istirahat_mulai.keterangan) AS ket_ist_mulai,
                                            IF(istirahat_mulai.foto IS NULL,'',istirahat_mulai.foto) AS foto_ist_mulai,
                                            IF(istirahat_selesai.jam_absen IS NULL,'',istirahat_selesai.jam_absen) AS ist_selesai,
                                            IF(istirahat_selesai.lokasi_absen IS NULL,'',istirahat_selesai.lokasi_absen) AS lokasi_ist_selesai,
                                            IF(istirahat_selesai.timezone IS NULL,'',istirahat_selesai.timezone) AS timezone_ist_selesai,
                                            IF(istirahat_selesai.gmt IS NULL,'',istirahat_selesai.gmt) AS gmt_ist_selesai,
                                            IF(istirahat_selesai.latitude IS NULL,'',istirahat_selesai.latitude) AS lat_ist_selesai,
                                            IF(istirahat_selesai.longitude IS NULL,'',istirahat_selesai.longitude) AS long_ist_selesai,
                                            IF(istirahat_selesai.keterangan IS NULL,'',istirahat_selesai.keterangan) AS ket_ist_selesai,
                                            IF(istirahat_selesai.foto IS NULL,'',istirahat_selesai.foto) AS foto_ist_selesai,
                                            IF(absensi_pulang.jam_kerja IS NULL,'',absensi_pulang.jam_kerja) AS jadwal_pulang,
                                            IF(absensi_pulang.jam_absen IS NULL,'',absensi_pulang.jam_absen) AS absen_pulang,
                                            IF(absensi_pulang.pulang_cepat IS NULL,'',absensi_pulang.pulang_cepat) AS pulang_cepat,
                                            IF(absensi_pulang.lokasi_absen IS NULL,'',absensi_pulang.lokasi_absen) AS lokasi_pulang,
                                            IF(absensi_pulang.timezone IS NULL,'',absensi_pulang.timezone) AS timezone_pulang,
                                            IF(absensi_pulang.gmt IS NULL,'',absensi_pulang.gmt) AS gmt_pulang,
                                            IF(absensi_pulang.latitude IS NULL,'',absensi_pulang.latitude) AS lat_pulang,
                                            IF(absensi_pulang.longitude IS NULL,'',absensi_pulang.longitude) AS long_pulang,
                                            IF(absensi_pulang.keterangan IS NULL,'',absensi_pulang.keterangan) AS ket_pulang,
                                            IF(absensi_pulang.foto IS NULL,'',absensi_pulang.foto) AS foto_pulang,
                                            data_karyawan.id_company
                                FROM absensi_masuk
                                LEFT JOIN istirahat_mulai ON istirahat_mulai.id_absensi_masuk = absensi_masuk.id_absensi_masuk
                                LEFT JOIN istirahat_selesai ON istirahat_selesai.id_absensi_masuk = absensi_masuk.id_absensi_masuk
                                LEFT JOIN absensi_pulang ON absensi_pulang.id_masuk = absensi_masuk.id_absensi_masuk
                                LEFT JOIN data_karyawan ON data_karyawan.id_karyawan = absensi_masuk.id_karyawan
                                LEFT JOIN master_cabang ON master_cabang.id_cabang = data_karyawan.id_cabang
                                LEFT JOIN master_departemen ON master_departemen.id_departemen = data_karyawan.id_departemen
                                LEFT JOIN master_jabatan ON master_jabatan.id_jabatan = data_karyawan.id_jabatan
                                JOIN data_shift_karyawan AS dsk ON dsk.id_karyawan = absensi_masuk.id_karyawan
                                JOIN master_shift AS ms ON ms.id_master_shift = dsk.id_master_shift
                                WHERE absensi_masuk.jenis_absen = 'shift'
                                AND dsk.tanggal = '$this->tgl_kemarin_lusa'
                                AND ms.flag_batas_absen_pulang = '1'
                                AND ms.flag_jam_pulang = '0'
                                AND absensi_masuk.tgl_absen = '$this->tgl_kemarin_lusa' ";

        return $this->db->query($sql);
    }

    public function shift_lewat_hari2(){ # ABSENSI PULANG LEWAT HARI

        $sql = "INSERT IGNORE INTO r_absensi
                                    (id_karyawan,
                                    nik,
                                    nama_karyawan,
                                    id_cabang,
                                    kode_cabang,
                                    nama_cabang,
                                    id_departemen,
                                    kode_departemen,
                                    nama_departemen,
                                    id_jabatan,
                                    kode_jabatan,
                                    nama_jabatan,
                                    supervisi,
                                    nama_supervisi,
                                    tgl_absen,
                                    jenis_absen,
                                    kode_absensi,
                                    ket_kode,
                                    jam_masuk_kerja,
                                    absen_masuk,
                                    terlambat,
                                    lokasi_masuk,
                                    timezone_masuk,
                                    gmt_masuk,
                                    lat_masuk,
                                    long_masuk,
                                    ket_absen_masuk,
                                    foto_masuk,
                                    absen_ist_mulai,
                                    lokasi_ist_mulai,
                                    timezone_ist_mulai,
                                    gmt_ist_mulai,
                                    lat_ist_mulai,
                                    long_ist_mulai,
                                    ket_ist_mulai,
                                    foto_ist_mulai,
                                    absen_ist_selesai,
                                    lokasi_ist_selesai,
                                    timezone_ist_selesai,
                                    gmt_ist_selesai,
                                    lat_ist_selesai,
                                    long_ist_selesai,
                                    ket_ist_selesai,
                                    foto_ist_selesai,
                                    jam_pulang_kerja,
                                    absen_pulang,
                                    pulang_cepat,
                                    lokasi_pulang,
                                    timezone_pulang,
                                    gmt_pulang,
                                    lat_pulang,
                                    long_pulang,
                                    ket_absen_pulang,
                                    foto_absen_pulang,
                                    id_company)
                                    SELECT absensi_masuk.id_karyawan,
                                            data_karyawan.nik,
                                            data_karyawan.nama_lengkap AS nama_karyawan,
                                            master_cabang.id_cabang,
                                            master_cabang.kode AS kode_cabang,
                                            master_cabang.nama AS nama_cabang,
                                            IF(master_departemen.id_departemen IS NOT NULL AND master_departemen.id_departemen != '' AND master_departemen.id_departemen != 'null',master_departemen.id_departemen,'') AS id_departemen,
                                            IF(master_departemen.kode IS NOT NULL AND master_departemen.kode !='' AND master_departemen.kode !='null',master_departemen.kode,'') AS kode_departemen,
                                            IF(master_departemen.nama IS NOT NULL AND master_departemen.nama !='' AND master_departemen.nama !='null',master_departemen.nama,'') AS nama_departemen,
                                            master_jabatan.id_jabatan,
                                            master_jabatan.kode_jabatan,
                                            master_jabatan.nama AS nama_jabatan,
                                            data_karyawan.supervisi,
                                            IF(data_karyawan.supervisi IS NOT NULL AND data_karyawan.supervisi != '' AND data_karyawan.supervisi != 'null',(SELECT nama_lengkap FROM data_karyawan WHERE id_karyawan = data_karyawan.supervisi),'') AS nama_supervisi,
                                            absensi_masuk.tgl_absen,
                                            absensi_masuk.jenis_absen,
                                            IF((absensi_pulang.pulang_cepat > 0),CONCAT(absensi_masuk.kode_absen,',(PC)'),
                                                IF((absensi_pulang.jam_absen IS NULL),CONCAT(absensi_masuk.kode_absen,',(TAP)'),absensi_masuk.kode_absen)) AS kode_absensi,
                                            'Shift' AS ket_kode,
                                            IF(absensi_masuk.jam_kerja= '00:00:00','',absensi_masuk.jam_kerja) AS jam_masuk_kerja,
                                            absensi_masuk.jam_absen AS absen_masuk,
                                            absensi_masuk.terlambat,
                                            absensi_masuk.lokasi_absen AS lokasi_masuk,
                                            absensi_masuk.timezone AS timezone_masuk,
                                            absensi_masuk.gmt AS gmt_masuk,
                                            absensi_masuk.latitude AS lat_masuk,
                                            absensi_masuk.longitude AS long_masuk,
                                            absensi_masuk.keterangan AS ket_masuk,
                                            absensi_masuk.foto AS foto_masuk,
                                            IF(istirahat_mulai.jam_absen IS NULL,'',istirahat_mulai.jam_absen) AS ist_mulai,
                                            IF(istirahat_mulai.lokasi_absen IS NULL,'',istirahat_mulai.lokasi_absen) AS lokasi_ist_mulai,
                                            IF(istirahat_mulai.timezone IS NULL,'',istirahat_mulai.timezone) AS timezone_ist_mulai,
                                            IF(istirahat_mulai.gmt IS NULL,'',istirahat_mulai.gmt) AS gmt_ist_mulai,
                                            IF(istirahat_mulai.latitude IS NULL,'',istirahat_mulai.latitude) AS lat_ist_mulai,
                                            IF(istirahat_mulai.longitude IS NULL,'',istirahat_mulai.longitude) AS long_ist_mulai,
                                            IF(istirahat_mulai.keterangan IS NULL,'',istirahat_mulai.keterangan) AS ket_ist_mulai,
                                            IF(istirahat_mulai.foto IS NULL,'',istirahat_mulai.foto) AS foto_ist_mulai,
                                            IF(istirahat_selesai.jam_absen IS NULL,'',istirahat_selesai.jam_absen) AS ist_selesai,
                                            IF(istirahat_selesai.lokasi_absen IS NULL,'',istirahat_selesai.lokasi_absen) AS lokasi_ist_selesai,
                                            IF(istirahat_selesai.timezone IS NULL,'',istirahat_selesai.timezone) AS timezone_ist_selesai,
                                            IF(istirahat_selesai.gmt IS NULL,'',istirahat_selesai.gmt) AS gmt_ist_selesai,
                                            IF(istirahat_selesai.latitude IS NULL,'',istirahat_selesai.latitude) AS lat_ist_selesai,
                                            IF(istirahat_selesai.longitude IS NULL,'',istirahat_selesai.longitude) AS long_ist_selesai,
                                            IF(istirahat_selesai.keterangan IS NULL,'',istirahat_selesai.keterangan) AS ket_ist_selesai,
                                            IF(istirahat_selesai.foto IS NULL,'',istirahat_selesai.foto) AS foto_ist_selesai,
                                            IF(absensi_pulang.jam_kerja IS NULL,'',absensi_pulang.jam_kerja) AS jadwal_pulang,
                                            IF(absensi_pulang.jam_absen IS NULL,'',absensi_pulang.jam_absen) AS absen_pulang,
                                            IF(absensi_pulang.pulang_cepat IS NULL,'',absensi_pulang.pulang_cepat) AS pulang_cepat,
                                            IF(absensi_pulang.lokasi_absen IS NULL,'',absensi_pulang.lokasi_absen) AS lokasi_pulang,
                                            IF(absensi_pulang.timezone IS NULL,'',absensi_pulang.timezone) AS timezone_pulang,
                                            IF(absensi_pulang.gmt IS NULL,'',absensi_pulang.gmt) AS gmt_pulang,
                                            IF(absensi_pulang.latitude IS NULL,'',absensi_pulang.latitude) AS lat_pulang,
                                            IF(absensi_pulang.longitude IS NULL,'',absensi_pulang.longitude) AS long_pulang,
                                            IF(absensi_pulang.keterangan IS NULL,'',absensi_pulang.keterangan) AS ket_pulang,
                                            IF(absensi_pulang.foto IS NULL,'',absensi_pulang.foto) AS foto_pulang,
                                            data_karyawan.id_company
                                FROM absensi_masuk
                                LEFT JOIN istirahat_mulai ON istirahat_mulai.id_absensi_masuk = absensi_masuk.id_absensi_masuk
                                LEFT JOIN istirahat_selesai ON istirahat_selesai.id_absensi_masuk = absensi_masuk.id_absensi_masuk
                                LEFT JOIN absensi_pulang ON absensi_pulang.id_masuk = absensi_masuk.id_absensi_masuk
                                LEFT JOIN data_karyawan ON data_karyawan.id_karyawan = absensi_masuk.id_karyawan
                                LEFT JOIN master_cabang ON master_cabang.id_cabang = data_karyawan.id_cabang
                                LEFT JOIN master_departemen ON master_departemen.id_departemen = data_karyawan.id_departemen
                                LEFT JOIN master_jabatan ON master_jabatan.id_jabatan = data_karyawan.id_jabatan
                                JOIN data_shift_karyawan AS dsk ON dsk.id_karyawan = absensi_masuk.id_karyawan
                                JOIN master_shift AS ms ON ms.id_master_shift = dsk.id_master_shift
                                WHERE absensi_masuk.jenis_absen = 'shift'
                                AND dsk.tanggal = '$this->tgl_kemarin_lusa'
                                AND ms.flag_batas_absen_pulang = '1'
                                AND ms.flag_jam_pulang = '1'
                                AND absensi_masuk.tgl_absen = '$this->tgl_kemarin_lusa' ";

        return $this->db->query($sql);
    }

    public function shift_off(){
        $sql ="INSERT IGNORE INTO r_absensi (id_karyawan,
                                        nik,
                                        nama_karyawan,
                                        id_cabang,
                                        kode_cabang,
                                        nama_cabang,
                                        id_departemen,
                                        kode_departemen,
                                        nama_departemen,
                                        id_jabatan,
                                        kode_jabatan,
                                        nama_jabatan,
                                        supervisi,
                                        nama_supervisi,
                                        tgl_absen,
                                        jenis_absen,
                                        kode_absensi,
                                        ket_kode,
                                        id_company)
                        SELECT 	dk.id_karyawan,
                                dk.nik,
                                dk.nama_lengkap,
                                dk.id_cabang,
                                mc.kode AS kode_cabang,
                                mc.nama AS nama_cabang,
                                IF(dk.id_departemen IS NOT NULL AND dk.id_departemen != '' AND dk.id_departemen != 'null',dk.id_departemen,'') AS id_departemen,
                                IF(md.kode IS NOT NULL AND md.kode !='' AND md.kode !='null',md.kode,'') AS kode_departemen,
                                IF(md.nama IS NOT NULL AND md.nama !='' AND md.nama !='null',md.nama,'') AS nama_departemen,
                                dk.id_jabatan,
                                mj.kode_jabatan,
                                mj.nama AS nama_jabatan,
                                dk.supervisi,
                                IF(dk.supervisi IS NOT NULL AND dk.supervisi != '' AND dk.supervisi != 'null',(SELECT nama_lengkap FROM data_karyawan WHERE id_karyawan = dk.supervisi),'') AS nama_supervisi,
                                '".$this->tgl_kemarin."' AS tgl_absen,
                                'libur' AS jenis_absen,
                                ms.kode_shift AS kode_absensi,
                                ms.nama_shift AS ket_kode,
                                dk.id_company
                            FROM data_karyawan AS dk
                            JOIN data_shift_karyawan AS dsk ON dsk.id_karyawan = dk.id_karyawan
                            JOIN master_shift AS ms ON ms.id_master_shift = dsk.id_master_shift
                            LEFT JOIN master_cabang AS mc ON mc.id_cabang = dk.id_cabang AND mc.id_company = dk.id_company
                            LEFT JOIN master_departemen AS md ON md.id_departemen = dk.id_departemen AND md.id_company = dk.id_company
                            LEFT JOIN master_jabatan AS mj ON mj.id_jabatan = dk.id_jabatan AND mj.id_company = dk.id_company
                            WHERE dk.status IN('1','2','3','4')
                            AND mj.level_user NOT IN ('1','6')
                            AND ms.libur = '1'
                            AND dsk.tanggal = '".$this->tgl_kemarin."'";

        return $this->db->query($sql);
    }

    function pensiun() {
        $tanggal = "2022-05-01";
        $tanggal_sekarang = date('Y-m-d');
        if($tanggal_sekarang <= $tanggal) {
            $sql_update = "update data_karyawan join nonaktif on data_karyawan.nik = nonaktif.nik and data_karyawan.id_company = nonaktif.id_company set data_karyawan.status = nonaktif.status";
            $this->db->query($sql_update);
        }
    }

    function hapus_absensi_masuk(){
        $sql =  "DELETE FROM absensi_masuk WHERE tgl_absen <= CURDATE() - INTERVAL 40 DAY";
        $this->db->query($sql);
    }

    function hapus_absensi_istirahat_mulai(){
        $sql =  "DELETE FROM istirahat_mulai WHERE tgl_absen <= CURDATE() - INTERVAL 40 DAY";
        $this->db->query($sql);
    }

    function hapus_absensi_istirahat_selesai(){
        $sql =  "DELETE FROM istirahat_selesai WHERE tgl_absen <= CURDATE() - INTERVAL 40 DAY";
        $this->db->query($sql);
    }

    function hapus_absensi_pulang(){
        $sql =  "DELETE FROM absensi_pulang WHERE tgl_absen <= CURDATE() - INTERVAL 40 DAY";
        $this->db->query($sql);
    }
}
