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

class Realisasi_won_m extends CI_Model
{

    public function dropdown_group($periode_mulai="0000-00-00", $periode_selesai="0000-00-00", $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_mulai;
        $binding[] = $periode_selesai;

        $periode_bulan_mulai   = date_format(date_create($periode_mulai),"Ym");
        $periode_bulan_selesai = date_format(date_create($periode_selesai),"Ym");

        if($periode_bulan_mulai < date('Ym') && $periode_bulan_selesai < date('Ym')){

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

            $sql = "SELECT g.id_group_m,
                            g.nama_group
                    FROM sales_pipeline_trx AS g
                    WHERE g.id_company = '" . $this->id_company . "'
                    AND (DATE_FORMAT(g.tgl_approve,'%Y-%m-%d') >= ? AND DATE_FORMAT(g.tgl_approve,'%Y-%m-%d') <= ?)
                    AND g.id_group_m != ''
                    AND g.nama_group != ''
                    ".$where_group."
                    ".$where_search."
                    GROUP BY g.nama_group
                    ORDER BY g.nama_group ASC";
        }else{

            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_pipeline_trx AS g
                        WHERE g.id_company = '" . $this->id_company . "'
                        AND (DATE_FORMAT(g.tgl_approve,'%Y-%m-%d') >= ? AND DATE_FORMAT(g.tgl_approve,'%Y-%m-%d') <= ?)
                        AND g.id_group_m != ''
                        AND g.nama_group != ''
                        ".$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_mulai="0000-00-00", $periode_selesai="0000-00-00", $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_mulai;
        $binding[] = $periode_selesai;

        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_pipeline_trx as sgm
                            WHERE sgm.id_company = '".$this->id_company."'
                            ".$where_group."
                            AND (sgm.tgl_approve >= ? AND sgm.tgl_approve <= ?)
                            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);
    }


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

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

    public function count_all($periode_mulai="0000-00-00", $periode_selesai="0000-00-00", $id_group="all", $id_karyawan="all"){
        $this->_get_datatables_query($periode_mulai, $periode_selesai, $id_group, $id_karyawan);
        return $this->db->count_all_results();
    }
    private function _get_datatables_query($periode_mulai="0000-00-00", $periode_selesai="0000-00-00", $id_group="all", $id_karyawan="all"){
         $subquery = $this->db->select('id_pipeline_main, MAX(tgl_input) AS tgl_po')
                            ->from('sales_pipeline_main_log')
                            ->where("id_company", $this->id_company)
                            ->having("DATE_FORMAT(tgl_po,'%Y-%m-%d') >=", $periode_mulai)
                            ->having("DATE_FORMAT(tgl_po,'%Y-%m-%d') <=", $periode_selesai)
                            ->group_by('id_pipeline_main')
                            ->get_compiled_select();
        $this->db->select("
                spt.nik,
                spt.nama_karyawan,
                spt.nominal_layanan,
                spt.nama_perusahaan,
                spt.tgl_incoming AS tgl_input_main,
                spt.nama_lead_source AS nama_lead,
                spt.nama_jenis_perusahaan AS jenis_perusahaan,
                spt.title,
                sp.kategori_pelanggan,
                GROUP_CONCAT(DISTINCT product.nama ORDER BY product.nama SEPARATOR ', ') AS nama_product,
                spm_log_max.tgl_po AS tgl_approve
            ")
            ->from('sales_pipeline_trx AS spt')
            ->join(
                'sales_tahapan_trx AS stt',
                'stt.id_tahapan_m = spt.id_tahapan
                    AND stt.bulan = spt.bulan
                    AND stt.tahun = spt.tahun
                '
            )
            ->join('sales_pipeline_main AS spm', 'spm.id_pipeline_main = spt.id_pipeline_main')
            ->join("($subquery) AS spm_log_max", 'spm_log_max.id_pipeline_main = spt.id_pipeline_main')
            ->join('sales_pelanggan AS sp', 'sp.id_pelanggan = spm.id_pelanggan')
            ->join('sales_pipeline_layanan AS spl', 'spl.id_pipeline_main = spm.id_pipeline_main')
            ->join('sales_product_m AS product', 'product.id_product_m = spl.id_product_m')
            ->where("spt.id_company", $this->id_company)
            ->where("stt.jenis", "wont")
            ->group_by("spt.id_karyawan, spt.tgl_approve, spt.nama_perusahaan, spt.nominal_layanan");

        # 1. filter tanggal
        $this->db->group_start();
        $this->db->where("DATE_FORMAT(spm_log_max.tgl_po,'%Y-%m-%d') >=", $periode_mulai);
        $this->db->where("DATE_FORMAT(spm_log_max.tgl_po,'%Y-%m-%d') <=", $periode_selesai);
        $this->db->group_end();

        # 2. filter karyawan
        if($id_karyawan != "all"){
            $this->db->where('spt.id_karyawan', $id_karyawan);
        }else{
            $check_sa = $this->fungsi->check_atasan_get_bawahan_sa();
            if($check_sa['is_atasan'] == true){
                $this->db->where_in('spt.id_karyawan', $check_sa['list_bawahan']);
            }else{
                $id_karyawan_arr = $this->_get_id_karyawan_union($periode_mulai, $periode_selesai, $id_group);
                $id_karyawan_arr = !empty($id_karyawan_arr) ? $id_karyawan_arr : [""];
                $this->db->where_in('spt.id_karyawan', $id_karyawan_arr);
            }
        }

        # 3. filter group
        if($id_group != "all"){
            $this->db->where('spt.id_group_m', $id_group);
        }

        $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("spt.tgl_approve" => "DESC");
            $this->db->order_by(key($order_by), $order_by[key($order_by)]);
        }
    }

    private function _get_id_karyawan_union($periode_mulai="0000-00-00", $periode_selesai="0000-00-00", $id_group="all"){
        $periode_bulan_mulai   = date_format(date_create($periode_mulai),"Ym");
        $periode_bulan_selesai = date_format(date_create($periode_selesai),"Ym");

        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 = ? ";

            if($periode_bulan_mulai < date('Ym') && $periode_bulan_selesai < date('Ym')){
                $binding[] = $id_group;
            }else{
                $binding[] = $id_group;
                $binding[] = $id_group;
            }
        }

        $binding[] = $periode_mulai;
        $binding[] = $periode_selesai;

        if($periode_bulan_mulai < date('Ym') && $periode_bulan_selesai < date('Ym')){
            $select = "SELECT id_karyawan
                        FROM sales_pipeline_trx AS sales
                        WHERE id_company = '".$this->id_company."'
                        ".$where_group."
                        AND (DATE_FORMAT(sales.tgl_approve,'%Y-%m-%d') >= ? AND DATE_FORMAT(sales.tgl_approve,'%Y-%m-%d') <= ?)
                        GROUP BY id_karyawan
                    ";

        }else{
            $select = "SELECT id_karyawan
                                FROM (
                                        SELECT id_karyawan
                                        FROM sales_approver AS sales
                                        WHERE id_company = '".$this->id_company."'
                                        ".$where_group."
                                        AND status = '1'

                                        UNION ALL

                                        SELECT id_karyawan
                                        FROM sales_pipeline_trx AS sales
                                        WHERE id_company = '".$this->id_company."'
                                        ".$where_group."
                                        AND (DATE_FORMAT(sales.tgl_approve,'%Y-%m-%d') >= ? AND DATE_FORMAT(sales.tgl_approve,'%Y-%m-%d') <= ?)
                                        GROUP BY id_karyawan
                                    ) AS combined
                            GROUP BY combined.id_karyawan
                        ";
        }

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

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

        return $data;
    }
}