<?php

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

class M_grafik extends CI_Model {

    protected $idGroup = null;
    protected $iduser = null;

    public function __construct() {
        parent::__construct();
        $this->id_group = $this->session->userdata("id_group");
        $this->id_user = $userId = $this->session->userdata('id_user');
    }

    function get_jenis_layanan(){
        $jenis_layanan =  $this->db->query("SELECT id, nama FROM jenis_layanan ORDER BY id ASC")->result_array();
        $color_chart = array("3e95cd", "8e5ea2", "3cba9f", "e8c3b9", "c45850", "66cc00",  );

        $tb = (int) date('m');
        $cb = intval($tb-5+1);

        $result = array();
        $x=0;
        foreach($jenis_layanan as $r){
            $result['jl_'.$r['id']]['nama_layanan'] = $r['nama'];
            $result['jl_'.$r['id']]['color'] = $color_chart[$x];
            for($i=$cb;$i<=$tb;$i++){
                $param = array();
                $param[] = $r['id'];
                $param[] = date('Y-m-01', strtotime(date('Y').'-'.(int)date('m', strtotime('+'.$i.' months',date('m'))).'-'.date('d')));
                $param[] = date('Y-m-t', strtotime(date('Y').'-'.(int)date('m', strtotime('+'.$i.' months',date('m'))).'-'.date('d')));
                $query = "SELECT COUNT(id) as total_uji
                    FROM permintaan_uji
                    WHERE id_layanan = ? AND tgl_pukul_registrasi BETWEEN ? AND ? AND is_active = '1'
                ";
                $result['jl_'.$r['id']]['data_jl']['bl_jl_'.date('m', strtotime('+'.$i.' months',date('m')))] = $this->db->query($query, $param)->row_array()['total_uji'];
            }
            $x++;
        }
        //print("<pre>".print_r($result, true)."</pre>"); die();
        return $result;
    }

    function get_pad(){

        for($i=1; $i<=12;$i++){
            $param = array();
            $param[] = date("Y");
            $param[] = $i;
            $query =  "SELECT SUM(nominal) as total_nominal, bulan FROM data_pad WHERE tahun = ? AND bulan = ? AND is_active = '1'";

            $result[$i] =  $this->db->query($query, $param)->row_array();

        }

        return $result;
    }

    function get_uji_sampel(){
        for($i=1; $i<=12;$i++){
            $param = array();
            $param[] = date('Y-m-01', strtotime(date('Y').'-'.$i.'-'.date('d')));
            $param[] = date('Y-m-t', strtotime(date('Y').'-'.$i.'-'.date('d')));
            $query =  "SELECT count(id) as total_uji FROM permintaan_uji WHERE tgl_pukul_registrasi BETWEEN ? AND ? AND is_active = '1'";

            $result[$i] =  $this->db->query($query, $param)->row_array();

        }

        return $result;
    }

    function get_jenis_pengujian() {
        $jenis_layanan =  $this->db->query("SELECT id, nama FROM parameter_uji WHERE is_active = '1' AND id between 1 and 16 ORDER BY id ASC")->result_array();
        $color_chart = array( "3366cc", "00ccff", "ffccff", "333300", "ffff00", "ff3300", "66ff33", "ff3399", "990099", "666699", "006600", "00e6e6", "", "3566cc", "6666cc", "6666dd");

        $tb = (int) date('m');
        $cb = intval($tb-5+1);

        $result = array();
        $x=0;
        foreach($jenis_layanan as $r){
            $result['jl_'.$r['id']]['nama_parameter'] = $r['nama'];
            $result['jl_'.$r['id']]['color'] = $color_chart[$x];
            for($i=$cb;$i<=$tb;$i++){
                $param = array();
                $param[] = '%'.$r['id'].'|%';
                $param[] = date('Y-m-01', strtotime(date('Y').'-'.(int)date('m', strtotime('+'.$i.' months',date('m'))).'-'.date('d')));
                $param[] = date('Y-m-t', strtotime(date('Y').'-'.(int)date('m', strtotime('+'.$i.' months',date('m'))).'-'.date('d')));
                $query = "SELECT COUNT(ds.id) as total_sampel
                    FROM data_sampel ds
                    JOIN permintaan_uji pu ON pu.id = ds.id_uji
                    WHERE ds.jenis_pengujian LIKE ? AND pu.tgl_pukul_registrasi BETWEEN ? AND ? AND ds.is_active = '1'
                ";
                $result['jl_'.$r['id']]['data_jl']['bl_jl_'.date('m', strtotime('+'.$i.' months',date('m')))] = $this->db->query($query, $param)->row_array()['total_sampel'];
            }
            $x++;
        }
        //print("<pre>".print_r($result, true)."</pre>"); die();
        return $result;
    }

}
