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

class Poin_bulanan_m extends CI_Model
{
    var $table = 'data_karyawan';

    function get_datatables($periode="0000", $id_group="all", $id_karyawan="all"){
        $this->_get_datatables_query($periode, $id_group, $id_karyawan);
        if ($_GET['length'] != -1) {
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        // echo $this->db->last_query();
        return $query->result();
        }

    function count_filtered($periode="0000", $id_group="all", $id_karyawan="all"){
        $this->_get_datatables_query($periode, $id_group, $id_karyawan);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all($periode="0000", $id_group="all", $id_karyawan="all"){
        $this->_get_datatables_query($periode, $id_group, $id_karyawan);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query($periode="0000", $id_group="all", $id_karyawan="all")
    {
        $this->db->select("
            trx.id_karyawan,
            trx.nik,
            trx.nama_karyawan"
        );
        $this->db->from('sales_target_poin_trx AS trx');
        $this->db->where("trx.id_company", $this->id_company);
        $this->db->where("DATE_FORMAT(trx.tgl_trx,'%Y')", $periode);

        if($id_karyawan != "all"){
            $this->db->where('trx.id_karyawan', $id_karyawan);
        }else{

            $check_sa = $this->fungsi->check_atasan_get_bawahan_sa();
            if($check_sa['is_atasan'] == true){
                $this->db->where_in('trx.id_karyawan', $check_sa['list_bawahan']);
            }else{
                $id_karyawan_arr = $this->_get_id_karyawan_union($periode, $id_group);
                $id_karyawan_arr = !empty($id_karyawan_arr) ? $id_karyawan_arr : [""];
                $this->db->where_in('trx.id_karyawan', $id_karyawan_arr);
            }
        }

        $column_search = array('nik', 'nama_karyawan');
        $i = 0;
        foreach ($column_search as $item) {  // looping awal
            if ($_GET['search']['value']) { // jika datatable mengirimkan pencarian dengan metode POST

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

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

        // $column_order = array('', '');
        if (isset($_GET['order'])) {
            // $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        } else {
            $order_by = array("trx.nama_karyawan" => "ASC");
            $this->db->order_by(key($order_by), $order_by[key($order_by)]);
        }
        $this->db->group_by("trx.id_karyawan");
    }

    public function get_poin_realisasi($periode="0000", $id_group="all", $id_karyawan="all")
    {
        $this->db->select('
            id_karyawan,
            DATE_FORMAT(tgl_activity, "%Y-%m"),
            SUM(poin) AS total_poin,
            CONCAT(DATE_FORMAT(tgl_activity, "%Y-%m"),id_karyawan) AS key_poin
        ');

        $this->db->from('sales_pipeline_activity');
        $this->db->where("id_company", $this->id_company);
        $this->db->where("status", 'approve');

        # 1. filter tanggal
        $this->db->where('DATE_FORMAT(tgl_activity, "%Y") =', $periode);

        # 2. Filter Group
        if($id_group != "all"){
            $this->db->where('id_group_m', $id_group);
        }

        # 3. filter karyawan
        if ($id_karyawan != "all") {
            $this->db->where('id_karyawan', $id_karyawan);
        }

        $this->db->group_by(['id_karyawan', 'DATE_FORMAT(tgl_activity, "%Y-%m")']);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_poin_target($periode="0000", $id_group="all", $id_karyawan="all")
    {
        $this->db->select('
            stpt.id_karyawan,
            DATE_FORMAT(stpt.tgl_trx, "%Y-%m"),
            SUM(stpt.poin) AS target_poin,
            CONCAT(DATE_FORMAT(stpt.tgl_trx, "%Y-%m"), stpt.id_karyawan) AS key_poin
        ');
        $this->db->from('sales_target_poin_trx AS stpt');
        $this->db->where("stpt.id_company", $this->id_company);

        # 1. filter tanggal
        $this->db->where('DATE_FORMAT(stpt.tgl_trx, "%Y") =', $periode);

        # 2. Filter Group
        if($id_group != "all"){
            $this->db->where('stpt.id_group_m', $id_group);
        }

        # 3. filter karyawan
        if($id_karyawan != "all") {
            $id_karyawan_arr = [$id_karyawan];
            $this->db->where('stpt.id_karyawan', $id_karyawan);

        }else{
            $check_sa = $this->fungsi->check_atasan_get_bawahan_sa();

            if($check_sa['is_atasan'] == true){
                $id_karyawan_arr = $check_sa['list_bawahan'];
                $this->db->where_in('stpt.id_karyawan', $id_karyawan_arr);

            }else{
                $id_karyawan_arr  = $this->_get_id_karyawan_union($periode);
                $this->db->where_in('stpt.id_karyawan', $id_karyawan_arr);

            }
        }


        # 4. filter absensi
        $tahun_now =  date('Y');
        if($tahun_now == $periode){
            $karyawan_str = implode("','", array_map(function($val){
                return addslashes($val);
            }, $id_karyawan_arr));

            $jenis_absen_str = "'reguler','shift','alpha'";
            $id_company_esc  = $this->db->escape($this->id_company);

            $subquery = "(
                SELECT id_karyawan, tgl_absen, jenis_absen
                FROM absensi_masuk
                WHERE id_company = $id_company_esc
                AND DATE_FORMAT(tgl_absen, '%Y') = '$periode'
                AND id_karyawan IN ('$karyawan_str')
                AND jenis_absen IN ($jenis_absen_str)

                UNION

                SELECT id_karyawan, tgl_absen, jenis_absen
                FROM r_absensi
                WHERE id_company = $id_company_esc
                AND DATE_FORMAT(tgl_absen, '%Y') = '$periode'
                AND id_karyawan IN ('$karyawan_str')
                AND jenis_absen IN ($jenis_absen_str)
            ) AS absen";

            $this->db->join($subquery, 'absen.id_karyawan = stpt.id_karyawan AND absen.tgl_absen = stpt.tgl_trx');
            
        }else{
            $this->db->join('r_absensi AS ra', 'ra.tgl_absen = stpt.tgl_trx AND ra.id_karyawan = stpt.id_karyawan');
            $this->db->where_in("ra.jenis_absen", ['reguler', 'shift', 'alpha']);
        }

        $this->db->group_by(['stpt.id_karyawan', 'DATE_FORMAT(stpt.tgl_trx, "%Y-%m")']);
        $query = $this->db->get();
        return $query->result_array();
    }

    private function _get_id_karyawan_union($periode="0000", $id_group="all"){
        $binding[]   = $periode;

        if($id_group == 'all'){
            if($this->is_sales_admin == TRUE && !in_array($this->sales_admin_group, ["all", ""])){
                $where_group = " AND sales.id_group_m IN ('".str_replace(",","','",$this->sales_admin_group)."') ";
            }else{
                $where_group = " ";
            }
        }else{
            $where_group = " AND sales.id_group_m = ? ";
            $binding[]   = $id_group;
        }


            $select = "SELECT id_karyawan
                        FROM sales_target_poin_trx AS sales
                        WHERE id_company = '".$this->id_company."'
                        AND DATE_FORMAT(sales.tgl_trx,'%Y') = ?
                        ".$where_group."
                        GROUP BY id_karyawan";
        $query = $this->db->query($select, $binding)->result();

        $data = array();
        foreach ($query as $value) {
            $data[] = $value->id_karyawan;
        }

        return $data;
    }


    public function dropdown_group($periode="0000", $search=""){
        $where_group = "";
        if($this->is_sales_admin == TRUE && !in_array($this->sales_admin_group, ["all", ""])){
            $where_group = " AND g.id_group_m IN ('".str_replace(",","','",$this->sales_admin_group)."')";
        }

        $binding[] = $periode;

        if(!empty($search)){
            $binding[] = "%".$search."%";
            $where_search = " WHERE combined.nama_group LIKE ? ";
        }else{
            $where_search = " ";
        }

        $sql = "SELECT * FROM (
                    SELECT g.id_group_m,
                            CONCAT(g.kode, ' - ',g.nama) AS nama_group
                    FROM sales_group_m AS g
                    WHERE g.id_company = '" . $this->id_company . "'
                    ".$where_group."

                    UNION ALL

                    SELECT g.id_group_m,
                            g.nama_group
                    FROM sales_target_poin_trx AS g
                    WHERE g.id_company = '" . $this->id_company . "'
                    AND DATE_FORMAT(g.tgl_trx,'%Y') >= ?
                    ".$where_group."
                    GROUP BY g.nama_group
                ) AS combined
                ".$where_search."
                GROUP BY combined.nama_group
                ORDER BY combined.nama_group ASC
            ";

        $result = $this->db->query($sql, $binding)->result();

        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id' => $rows->id_group_m, 'text' => $rows->nama_group, 'nm-group'=>$rows->nama_group];
        }
        echo json_encode($json);
    }

    public function dropdown_karyawan($periode="00-0000", $id_group="all", $search=""){
        $check_sa = $this->fungsi->check_atasan_get_bawahan_sa();
        if($check_sa['is_atasan'] == true){
            $this->db->where_in('data_karyawan.id_karyawan', $check_sa['list_bawahan']);
        }

        if($id_group == 'all'){
            if($this->is_sales_admin == TRUE && !in_array($this->sales_admin_group, ["all", ""])){
                $where_group = " AND sgm.id_group_m IN ('".str_replace(",","','",$this->sales_admin_group)."')";
            }else{
                $where_group = " ";
            }
        }else{
            $where_group = " AND sgm.id_group_m = ? ";
            $binding[]   = $id_group;
            $binding[]   = $id_group;

        }

        $binding[] = $periode;

        if(!empty($search)){
            $where_search = " WHERE (combined.nik LIKE ? OR combined.nm_karyawan LIKE ?) ";
            $binding[] = "%".$search."%";
            $binding[] = "%".$search."%";
        }

        $sql = "SELECT combined.id_karyawan,
                            combined.nik,
                            combined.nm_karyawan
                    FROM (
                            SELECT dk.id_karyawan,
                                    dk.nik,
                                    dk.nama_lengkap as nm_karyawan
                            FROM sales_approver as sa
                            JOIN data_karyawan as dk on dk.id_karyawan = sa.id_karyawan
                            JOIN sales_group_m as sgm on sgm.id_group_m = sa.id_group_m
                            WHERE sa.id_company = '".$this->id_company."'
                            ".$where_group."
                            AND sa.status = '1'

                            UNION ALL

                            SELECT sgm.id_karyawan,
                                    sgm.nik,
                                    sgm.nama_karyawan as nm_karyawan
                            FROM sales_target_poin_trx as sgm
                            WHERE sgm.id_company = '".$this->id_company."'
                            ".$where_group."
                            AND DATE_FORMAT(sgm.tgl_trx,'%m-%Y') >= ?
                            GROUP BY sgm.id_karyawan
                        ) AS combined
                    ".$where_search."
                    GROUP BY combined.id_karyawan
                    ORDER BY combined.nm_karyawan ASC";

        $result = $this->db->query($sql, $binding)->result();
        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id' => $rows->id_karyawan,
                        'text'        => $rows->nik." - ".$rows->nm_karyawan,
                        'nm-karyawan' => $rows->nik." - ".$rows->nm_karyawan];
        }
        echo json_encode($json);
    }

}
