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

class Pelanggan_m extends CI_Model
{
    // var $table = 'sales_pelanggan';
    var $table = 'sales_pipeline_trx';

    function get_datatables($tahun="0000", $bulan="00", $id_group_m="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m);
        if ($_GET['length'] != -1) {
            $this->db->limit($_GET['length'], $_GET['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }
    
    function count_filtered($tahun="0000", $bulan="00", $id_group_m="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m);
        $query = $this->db->get();
        return $query->num_rows();
    }
    
    public function count_all($tahun="0000", $bulan="00", $id_group_m="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m);
        return $this->db->count_all_results();
    }

    private function _get_datatables_query($tahun="0000", $bulan="00", $id_group_m="all"){
        $this->db->select('
            sptx.nama_perusahaan,
            COUNT(sptx.jumlah_layanan) AS jumlah,
            SUM(sptx.nominal_layanan) AS nominal
        ');
        $this->db->from('sales_pipeline_trx AS sptx');
        $this->db->where('sptx.id_company', $this->id_company);
        $this->db->where('sptx.tahun', $tahun);
        $this->db->where('sptx.jenis_tahapan', "wont");
        $this->db->group_by('sptx.nama_perusahaan');

        if($bulan != "all"){
            $this->db->where('sptx.bulan', sprintf('%02d', $bulan));
        }

        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));
            }
        }
        
        // COLUMN SEARCH
        $column_search = array('sptx.nama_perusahaan');
        $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
        $column_order = array('', 'sptx.nama_perusahaan', 'COUNT(sptx.jumlah_layanan)', 'SUM(sptx.nominal_layanan)');
        if (isset($_GET['order'])) {
            $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        } else {
            $order_by = array("MAX(sptx.tgl_approve)" => "DESC");
            $this->db->order_by(key($order_by), $order_by[key($order_by)]);
        }
    }

    public function get_total_jumlah_dan_nominal($tahun="0000", $bulan="00", $id_group_m="all"){
        $this->db->select('
            sptx.nama_perusahaan,
            COUNT(sptx.jumlah_layanan) AS jumlah,
            SUM(sptx.nominal_layanan) AS nominal
        ');
        $this->db->from('sales_pipeline_trx AS sptx');
        $this->db->where('sptx.id_company', $this->id_company);
        $this->db->where('sptx.tahun', $tahun);
        $this->db->where('sptx.jenis_tahapan', "wont");
        $this->db->group_by('sptx.id_company');

        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));
            }
        }

        if($bulan != "all"){
            $this->db->where('sptx.bulan', sprintf('%02d', $bulan));
        }

        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));
            }
        }

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

    // private function _get_datatables_query($tahun="", $bulan=""){
    //     $this->db->select('
    //         sp.id_pelanggan,
    //         sp.nama_perusahaan,
    //         COUNT(spm.id_pipeline_main) AS jumlah,
    //         SUM(spl.harga_total) AS nominal
    //     ');

    //     $this->db->from('sales_pelanggan AS sp');
    //     $this->db->join('sales_pipeline_main AS spm', 'spm.id_pelanggan = sp.id_pelanggan');
    //     $this->db->join('sales_pipeline_layanan AS spl', 'spl.id_pipeline_main = spm.id_pipeline_main');
    //     $this->db->join('sales_tahapan_trx AS stt', 'stt.id_tahapan_m = spm.id_tahapan_m 
    //                         AND stt.tahun = spm.tahun 
    //                         AND stt.bulan = spm.bulan 
    //                         AND stt.jenis = "wont"');        
    //     $this->db->where('sp.id_company', $this->id_company);
    //     $this->db->where('sp.jenis', '1');
    //     $this->db->where('spm.status', "approve");
    //     $this->db->where('spm.tahun', $tahun);
    //     $this->db->group_by('sp.id_pelanggan');

    //     if($bulan != "all"){
    //         $this->db->where('spm.bulan', sprintf('%02d', $bulan));
    //     }
    
    //     $column_search = array('sp.nama_perusahaan');
    //     $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('', 'sp.nama_perusahaan', 'COUNT(spm.id_pipeline_main)', 'SUM(spl.harga_total)');
    //     if (isset($_GET['order'])) {
    //         $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
    //     } else {
    //         $order_by = array("MAX(spm.tgl_update)" => "DESC");
    //         $this->db->order_by(key($order_by), $order_by[key($order_by)]);
    //     }
    // }

    // public function get_total_jumlah_dan_nominal($tahun="", $bulan=""){
    //     $this->db->select('
    //         COUNT(spm.id_pipeline_main) AS jumlah, 
    //         SUM(spl.harga_total) AS nominal
    //     ');
        
    //     $this->db->from('sales_pelanggan AS sp');
    //     $this->db->join('sales_pipeline_main AS spm', 'spm.id_pelanggan = sp.id_pelanggan');
    //     $this->db->join('sales_pipeline_layanan AS spl', 'spl.id_pipeline_main = spm.id_pipeline_main');
    //     $this->db->join('sales_tahapan_trx AS stt', 'stt.id_tahapan_m = spm.id_tahapan_m 
    //                         AND stt.tahun = spm.tahun 
    //                         AND stt.bulan = spm.bulan 
    //                         AND stt.jenis = "wont"');        
    //     $this->db->where('sp.id_company', $this->id_company);
    //     $this->db->where('sp.jenis', '1');
    //     $this->db->where('spm.status', "approve");
    //     $this->db->where('spm.tahun', $tahun);
    //     $this->db->group_by('sp.id_company');

    //     if($bulan != "all"){
    //         $this->db->where('spm.bulan', sprintf('%02d', $bulan));
    //     }

    //     return $this->db->get()->row();
    // }
}