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

class Dashboard_m extends CI_Model
{
    function data_sales_summary($periode = "00-0000", $id_group_m = "all")
    {   
        $this->db->select('
            stt.nama AS nama_tahapan,
            stt.warna AS warna_tahapan,
            stt.warna_text AS warna_text_tahapan,
            stt.no_urut AS no_urut_tahapan,
            IFNULL(SUM(spl.`harga_total`), 0) AS total_nominal,
	        IFNULL(COUNT(DISTINCT spm.`id_pipeline_main`), 0) AS total_layanan
        ');
        $this->db->from('sales_tahapan_trx stt');

        $this->db->where('stt.id_company', $this->id_company);
        $this->db->where('CONCAT(stt.bulan,"-",stt.tahun)', $periode);
        $this->db->group_by('stt.id_tahapan_m');
        $this->db->order_by('stt.no_urut');

        $where = "spm.id_company = stt.id_company
            AND spm.bulan = stt.bulan
            AND spm.tahun = stt.tahun
            AND spm.id_tahapan_m = stt.id_tahapan_m
            AND spm.status != 'tolak'";

        if($id_group_m != "all"){
            $where .= " AND (spm.id_group_m = ".$id_group_m. " OR spm.id_group_m = '')";
        }

        if($this->is_sales_admin == true){
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                $where .= "AND (spm.id_group_m IN ('" . str_replace(',', "', '", $this->sales_admin_group) . "') OR spm.id_group_m = '')";
            }
        }

        $this->db->join(
            'sales_pipeline_main spm',
            $where,
            'LEFT'
        );

        $this->db->join(
            'sales_pipeline_layanan spl',
            'spl.id_company = stt.id_company
                AND spl.id_pipeline_main = spm.id_pipeline_main',
            'LEFT'
        );

        return $this->db->get()->result();
    }

    function data_po_per_sales($periode = "00-0000", $id_group_m = "all")
    {   
        // Untuk data_karyawan.join_date
        $binding[] = DateTime::createFromFormat('m-Y', $periode)->format('Y-m-t');

        $where = "";
        if($id_group_m != "all"){
            $where .= " AND id_group_m  = ?";
            $binding[] = $id_group_m;
            $binding[] = $id_group_m;
        }

        if($this->is_sales_admin == true){
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                $where .= " AND id_group_m IN ('" . str_replace(',', "', '", $this->sales_admin_group) . "')";
            }
        }
        
        // Untuk (trx.tahun,trx.bulan)
        $binding[] = $periode;
        
        $sql = "SELECT
                    id_karyawan,
                    nik,
                    nama_lengkap,
                    SUM(nominal_realisasi) AS total_nominal,
                    SUM(jumlah_po) AS total_po
                FROM(
                    SELECT 	
                        dk.id_karyawan,
                        dk.nik,
                        dk.nama_lengkap,
                        sa.id_group_m,
                        '0' AS nominal_realisasi,
                        '0' AS jumlah_po
                    FROM sales_approver AS sa
                    JOIN data_karyawan AS dk
                        ON sa.id_karyawan = dk.id_karyawan
                    WHERE dk.id_company = ".$this->id_company."
                    AND dk.join_date <= ?
                    ".$where."
                    AND sa.status = '1'

                    UNION ALL
                    
                    SELECT
                        id_karyawan,
                        nik,
                        nama_karyawan AS nama_lengkap,
                        id_group_m,
                        nominal_realisasi,
                        jumlah_po
                    FROM sales_target_personil_r
                    WHERE id_company = ".$this->id_company."
                    ".$where."
                    AND CONCAT(bulan,'-',tahun) = ?
                ) AS combined
                GROUP BY combined.id_karyawan
                ORDER BY combined.nama_lengkap ASC
            ";
            
        $query = $this->db->query($sql, $binding)->result();

        $data  = array();
        foreach ($query as $key => $value) {
            $data['nama_karyawan'][] = substr($value->nama_lengkap, 0, 25);
            $data['total_po'][]      = $value->total_po;
            $data['total_nominal'][] = $value->total_nominal;
        }

        return $data;
    }

    function data_po_per_product($periode = "00-0000", $id_group_m = "all", $by = 'product')
    {
        if ($by === 'product') {
            $this->db->select('product.nama');
        } else {
            $this->db->select('group_product.nama');
        }
        $this->db->from('sales_pipeline_trx AS sptx');
        $this->db->join('sales_pipeline_layanan AS spl', 'spl.id_pipeline_main = sptx.id_pipeline_main');
        $this->db->join('sales_product_m AS product', 'product.id_product_m = spl.id_product_m AND product.id_company = spl.id_company');
        $this->db->join('sales_product_group_m AS group_product', 'group_product.id_product_group_m = product.id_product_group_m');
        $this->db->where('sptx.id_company', $this->id_company);
        $this->db->where('sptx.jenis_tahapan', "wont");
        $this->db->where('CONCAT(sptx.bulan,"-",sptx.tahun)', $periode);

        if($id_group_m != "all"){
            $this->db->where('sptx.id_group_m', $id_group_m);
        }

        if($this->is_sales_admin == true){
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                $this->db->where_in('sptx.id_group_m', explode(',', $this->sales_admin_group));
            }
        }

        $query = $this->db->get()->result_array();

        $nama_product_list  = array_column($query, 'nama');
        $jumlah_per_product = array_count_values($nama_product_list);
        $total              = count($nama_product_list);

        $data_presentase = [];
        foreach ($jumlah_per_product as $nama_product => $jumlah) {
            $persentase = ($jumlah / $total) * 100;
            $bulat      = round($persentase, 2);

            $data_presentase[] = [
                'nama_product' => $nama_product,
                'presentase'   => $bulat
            ];
        }

        $presentase_column = array_column($data_presentase, 'presentase');
        array_multisort($presentase_column, SORT_DESC, $data_presentase);

        return $data_presentase;
    }

    function data_activity($periode = "00-0000", $id_group_m = "all")
    {
        $array_warna = [
            "#3288bd",
            "#66c2a5",
            "#abdda4",
            "#9e0142",
            "#d53e4f",
            "#f46d43",
            "#fdae61",
            "#fee08b",
            "#5e4fa2",
            "#01a5e4",
            "#00cdac",
            "#c6fac5",
            "#d967c9",
            "#f27ca6",
            "#ffa23a",
            "#ffd871",
            "#ea57a4",
            "#72a2f4"
        ];

        $binding[] = $periode;
        
        if($id_group_m != "all"){
            $where = " AND id_group_m  = ?";
            $binding[] = $id_group_m;
        }else{
            $where = "";
        }

        if($this->is_sales_admin == true){
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                $where .= " AND id_group_m IN ('" . str_replace(',', "', '", $this->sales_admin_group) . "')";
            }
        }

        $sql = "
            SELECT
                u.jenis_activity,
                SUM(u.total_activity) AS total_activity
            FROM (
                SELECT
                    nama AS jenis_activity,
                    0 AS total_activity
                FROM sales_activity_m
                WHERE id_company = '$this->id_company'

                UNION ALL

                SELECT
                    jenis_activity,
                    1 AS total_activity
                FROM sales_pipeline_activity
                WHERE id_company = '$this->id_company'
                AND DATE_FORMAT(tgl_activity, '%m-%Y') = ?
                ".$where."
            ) AS u
            GROUP BY u.jenis_activity
            ORDER BY SUM(u.total_activity) DESC;
        ";

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

        $i = 0;
        $total_semua_activity = 0;
        $data = [];

        // array untuk keterangan html
        foreach ($query as $value) {
            $data['data'][] = array(
                'jenis_activity' => $value->jenis_activity,
                'total_activity' => $value->total_activity,
                'warna'          => $array_warna[$i]
            );

            $data['jenis_activity'][]  = $value->jenis_activity;
            $data['total_activity'][]  = $value->total_activity;
            $data['warna'][]           = $array_warna[$i];
            $total_semua_activity     += $value->total_activity;
            $i++;
        }

        $data['total_semua_activity'] = $total_semua_activity;
        return $data;
    }

    function data_target($jenis = "tahunan", $tahun = "0000", $id_group_m = "all")
    {
        // nama tabel
        if ($jenis == 'tahunan') {
            $table = 'sales_target_tahunan_p';
        } else if ($jenis == 'kuartal') {
            $table = 'sales_target_kuartal_p';
        } else if ($jenis == 'bulanan') {
            $table = 'sales_target_bulanan_p';
        } else if ($jenis == 'realisasi') {
            $table = 'sales_target_personil_r';
        } else {
            echo 'error';
        }

        // jika realisasi
        if ($jenis == 'realisasi') {
            $this->db->select('SUM(nominal_realisasi) AS nominal');
        } else {
            $this->db->select('SUM(nominal) AS nominal');
        }

        $this->db->select('tahun, bulan, id_company');
        $this->db->from($table);
        $this->db->where('id_company', $this->id_company);
        $this->db->where('tahun', $tahun);
        $this->db->group_by('tahun, bulan');

        if($id_group_m != "all"){
            $this->db->where('id_group_m', $id_group_m);
        }

        if($this->is_sales_admin == true){
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                $this->db->where_in('id_group_m', explode(',', $this->sales_admin_group));
            }
        }

        $query = $this->db->get()->result_array();

        $array_bulan = array();
        for ($i = 1; $i <= 12; $i++) {
            $array_bulan[] = str_pad($i, 2, "0", STR_PAD_LEFT);
        }

        $data  = array();
        $total = 0;
        foreach ($array_bulan as $bulan) {
            $res      = array_search($bulan, array_column($query, 'bulan'));
            $res      = ($res !== false) ? $query[$res] : null;
            $nominal  = !empty($res['nominal']) ? $res['nominal'] : '0';
            $total   += $nominal;
            $data[]  = $nominal;
        }

        $data_target = array(
            'data'  => $data,
            'total' => number_format($total, 0, ",", ".")
        );

        return $data_target;
    }

    // DROPDOWN GROUP UNTUK PERIODE
    public function dropdown_group_periode($periode = "00-000", $search=""){
        // Binding Periode
        $binding[] = $periode;

        // Binding Search
        if(!empty($search)){
            $binding[]    = "%".$search."%";
            $where_search = "WHERE combined.nama_group LIKE ?";
        }else{
            $where_search = "";
        }
        
        // Query jika admin sales
        if($this->is_sales_admin == true){
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                if(!empty($where_search)){
                    $where_adm = "AND combined.id_group_m IN ('" . str_replace(',', "', '", $this->sales_admin_group) . "')";
                }else{
                    $where_adm = "WHERE combined.id_group_m IN ('" . str_replace(',', "', '", $this->sales_admin_group) . "')";
                }
            }
        }else{
            $where_adm = "";
        }

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

                UNION ALL

                SELECT 
                    id_group_m,
                    nama_group 
                FROM sales_pipeline_trx
                WHERE id_company = '" . $this->id_company . "'
                AND CONCAT(bulan,'-',tahun) = ?
                AND id_group_m != ''
                GROUP BY id_group_m
            ) AS combined
            ".$where_search."
            ".$where_adm."
            GROUP BY combined.id_group_m
            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,
            ];
        }

        echo json_encode($json);
    }

    // DROPDOWN GROUP UNTUK TAHUN
    public function dropdown_group_tahun($tahun = "0000", $search=""){
        // Binding Tahun
        $binding[] = $tahun;

        // Binding Search
        if(!empty($search)){
            $binding[]    = "%".$search."%";
            $where_search = "WHERE combined.nama_group LIKE ?";
        }else{
            $where_search = "";
        }
        
        // Query jika admin sales
        if($this->is_sales_admin == true){
            if (!in_array($this->sales_admin_group, ["all", ""])) {
                if(!empty($where_search)){
                    $where_adm = "AND combined.id_group_m IN ('" . str_replace(',', "', '", $this->sales_admin_group) . "')";
                }else{
                    $where_adm = "WHERE combined.id_group_m IN ('" . str_replace(',', "', '", $this->sales_admin_group) . "')";
                }
            }
        }else{
            $where_adm = "";
        }

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

                UNION ALL

                SELECT 
                    id_group_m,
                    nama_group 
                FROM sales_target_personil_r
                WHERE id_company = '" . $this->id_company . "'
                AND tahun = ?
                AND id_group_m != ''
                GROUP BY id_group_m
            ) AS combined
            ".$where_search."
            ".$where_adm."
            GROUP BY combined.id_group_m
            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,
            ];
        }

        echo json_encode($json);
    }
}
