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

class Product_m extends CI_Model
{
    var $table = 'sales_pelanggan';

    function get_datatables($tahun="0000", $bulan="00", $id_group_m="all", $id_product_m="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m, $id_product_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", $id_product_m="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m, $id_product_m);
        $query = $this->db->get();
        return $query->num_rows();
    }
    
    public function count_all($tahun="0000", $bulan="00", $id_group_m="all", $id_product_m="all"){
        $this->_get_datatables_query($tahun, $bulan, $id_group_m, $id_product_m);
        return $this->db->count_all_results();
    }
    
    private function _get_datatables_query($tahun="0000", $bulan="00", $id_group_m="all", $id_product_m="all"){
        $this->db->select('
            product.nama AS nama_product,
            g_product.nama AS nama_product_group,
            COUNT(sptx.jumlah_layanan) AS jumlah,
            SUM(sptx.nominal_layanan) AS nominal
        ');
        $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');
        $this->db->join('sales_product_group_m AS g_product', 'g_product.id_product_group_m = product.id_product_group_m');
        $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('product.id_product_m');

        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($id_product_m != "all"){
            $this->db->where('product.id_product_m', $id_product_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 ORDER
        $column_order = array('', 'g_product.nama', 'COUNT(sptx.jumlah_layanan)', 'SUM(spl.nominal_layanan)');
        if (isset($_GET['order'])) {
            $this->db->order_by($column_order[$_GET['order']['0']['column']], $_GET['order']['0']['dir']);
        } else {
            $this->db->order_by("COUNT(sptx.jumlah_layanan) DESC, g_product.nama ASC");
        }
    }

    public function get_total_jumlah_dan_nominal($tahun="0000", $bulan="00", $id_group_m="all", $id_product_m="all"){
        $this->db->select('
            COUNT(sptx.jumlah_layanan) AS jumlah,
            SUM(sptx.nominal_layanan) AS nominal
        ');
        $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');
        $this->db->join('sales_product_group_m AS g_product', 'g_product.id_product_group_m = product.id_product_group_m');
        $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($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($id_product_m != "all"){
            $this->db->where('product.id_product_m', $id_product_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();
    }
    
    public function dropdown_produk($search = "")
    {
        $this->db->select('id_product_m, nama');
        $this->db->where('id_company', $this->id_company);
        $this->db->order_by('nama', 'ASC');

        if ($search != '') {
            $this->db->like('nama', $search);
        }

        $result = $this->db->get('sales_product_m')->result();
        $json = [];
        foreach ($result as $rows) {
            $json[] = ['id' => $rows->id_product_m, 'text' => $rows->nama];
        }
        echo json_encode($json);
    }
}