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

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

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

    public function by_limit($offset, $limit){

        $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' LIMIT $offset, $limit";

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

    }
}
