<?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,
                                master_departemen.id_departemen,
                                master_departemen.kode AS kode_departemen,
                                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)));

        $log_file = 'log_absensi_alpa.log';

        $flexible_jam_kerja = $this->db->query("SELECT mc.id_company, 
                                                        mc.nama AS nama_company, 
                                                        mc.id_tb_paket, 
                                                        tbp.komponen, 
                                                        tbp.komponen_web, 
                                                        tbp.komponen_mobile, 
                                                        mp.tgl_berakhir
                                                    FROM master_company AS mc
                                                    JOIN tb_paket AS tbp ON tbp.id_tb_paket = mc.id_tb_paket
                                                    JOIN master_plan AS mp ON mp.id_company = mc.id_company 
                                                    WHERE mp.tgl_berakhir >= CURDATE()
                                                    ORDER BY mp.tgl_berakhir DESC"
                                                )->result();

        foreach ($flexible_jam_kerja as $row) {

            $paket_arr = json_decode($row->komponen, true);

            if (isset($paket_arr['flexible_jam_kerja'])) {
                $flexible_jam_kerja = $paket_arr['flexible_jam_kerja'];
            }else{
                $flexible_jam_kerja = false;
            }

            $paket_arr['flexible_jam_kerja'] = $flexible_jam_kerja;

            if ($paket_arr['flexible_jam_kerja'] === false) {
                $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,
                                    dk.id_departemen,
                                    md.kode AS kode_departemen,
                                    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.id_company = '$row->id_company' 
                                AND 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 dsk.tanggal = '$tgl_kemarin')
                                AND dk.id_company NOT IN (SELECT id_company FROM holidays WHERE holidays.tanggal = '$tgl_kemarin')";

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

                $log_data = date('Y-m-d H:i:s') . " - " . $row->nama_company . "\n";
                file_put_contents($log_file, $log_data, FILE_APPEND);
            }
        }
    }

    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,
                                    dk.id_departemen,
                                    md.kode AS kode_departemen,
                                    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 ms.flag_jam_pulang = '0'
                                                AND dsk.tanggal = '$tgl_kemarin')";
        return $this->db->query($sql);
    }

    public function alpha_shift_lewat_hari(){ # KONDISI TIDAK MASUK SHIFT DAN JAM REGULER LIBUR
        $tgl_kemarin = date('Y-m-d',strtotime('-2 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,
                                    dk.id_departemen,
                                    md.kode AS kode_departemen,
                                    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 ms.flag_jam_pulang = '1'
                                                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)));

        $log_file = 'log_absensi_libur.log';

        $flexible_jam_kerja = $this->db->query("SELECT mc.id_company, 
                                                        mc.nama AS nama_company, 
                                                        mc.id_tb_paket, 
                                                        tbp.komponen, 
                                                        tbp.komponen_web, 
                                                        tbp.komponen_mobile, 
                                                        mp.tgl_berakhir
                                                    FROM master_company AS mc
                                                    JOIN tb_paket AS tbp ON tbp.id_tb_paket = mc.id_tb_paket
                                                    JOIN master_plan AS mp ON mp.id_company = mc.id_company 
                                                    WHERE mp.tgl_berakhir >= CURDATE()
                                                    ORDER BY mp.tgl_berakhir DESC"
                                                )->result();


         foreach ($flexible_jam_kerja as $row) {
            $paket_arr = json_decode($row->komponen, true);

            if (isset($paket_arr['flexible_jam_kerja'])) {
                $flexible_jam_kerja = $paket_arr['flexible_jam_kerja'];
            }else{
                $flexible_jam_kerja = false;
            }

            $paket_arr['flexible_jam_kerja'] = $flexible_jam_kerja;

            if ($paket_arr['flexible_jam_kerja'] === false) {
                $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,
                                            dk.id_departemen,
                                            md.kode AS kode_departemen,
                                            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.id_company = '$row->id_company' 
                                        AND 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')";
                $this->db->query($sql);

                $log_data = date('Y-m-d H:i:s') . " - " . $row->nama_company . "\n";
                file_put_contents($log_file, $log_data, FILE_APPEND);
            }
        }
    }


    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,
                                    dk.id_departemen,
                                    md.kode AS kode_departemen,
                                    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,
                                            master_departemen.id_departemen,
                                            master_departemen.kode AS kode_departemen,
                                            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,
                                            master_departemen.id_departemen,
                                            master_departemen.kode AS kode_departemen,
                                            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,
                                            master_departemen.id_departemen,
                                            master_departemen.kode AS kode_departemen,
                                            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,
                                dk.id_departemen,
                                md.kode AS kode_departemen,
                                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 60 DAY";
        $this->db->query($sql);
    }

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

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

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

    function hapus_shift_karyawan(){
        $sql =  "DELETE FROM data_shift_karyawan WHERE tanggal <= CURDATE() - INTERVAL 90 DAY";
        $this->db->query($sql);
    }

    public function insert_data_dashbord(){

        $this->db2 = $this->load->database("dashboard",TRUE);

        $query = $this->db->query("SELECT CURDATE() as tgl_trx, 
                        'custom' as jenis, 
                        master_company.nama as nm_company,
                        master_company.id_company as id_company, 
                        tb_paket.nama AS nm_paket, 
                        master_plan.tgl_aktif as tgl_aktif, 
                        master_plan.tgl_berakhir as tgl_berakhir, 
                        tb_paket.max_karyawan as ttl_user_paket,
                        COUNT(*) AS ttl_user_aktif 
                FROM data_karyawan
                JOIN master_company ON master_company.id_company = data_karyawan.id_company
                JOIN master_plan ON master_plan.id_company = master_company.id_company
                JOIN tb_paket ON tb_paket.id_tb_paket = master_company.id_tb_paket
                WHERE master_company.id_company = '202501631' 
                AND data_karyawan.status IN('1','2','3','4')")->row();

        $data = array(
                        'tgl_trx'        => $query->tgl_trx, 
                        'jenis'          => $query->jenis, 
                        'nm_company'     => $query->nm_company, 
                        'id_company'     => $query->id_company, 
                        'nm_paket'       => $query->nm_paket, 
                        'tgl_aktif'      => $query->tgl_aktif, 
                        'tgl_berakhir'   => $query->tgl_berakhir, 
                        'ttl_user_paket' => $query->ttl_user_paket, 
                        'ttl_user_aktif' => $query->ttl_user_aktif 
                    );

        $insert_query = $this->db2->insert_string('ttl_users', $data);
        $insert_query = preg_replace('/INSERT INTO/','INSERT IGNORE INTO',$insert_query,1);
        $this->db2->query($insert_query);
    }
}
