<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Data_perkara extends CI_Controller {

	function index(){
	    die();}

	function sidang(){
		$data = $this->panggil_data_sidang();
		if($data){
				echo json_encode($data);
		} else {
			echo json_encode('<center>TIDAK ADA SIDANG<br>HARI INI</center>');}
	    die();}
	

	function statistik($tampil=NULL){
	   $bln = date("m");$tahun = date("Y");
       #$bln = '12'; $tahun = '2022';
	    if($tampil=='nilai'){
	    $array=array(
             (array('label'=>'Januari','y'=>0,'info'=>NULL)),
             (array('label'=>'Februari','y'=>0,'info'=>NULL)),
             (array('label'=>'Maret','y'=>0,'info'=>NULL)),
             (array('label'=>'April','y'=>0,'info'=>NULL)),
             (array('label'=>'Mei','y'=>0,'info'=>NULL)),
             (array('label'=>'Juni','y'=>0,'info'=>NULL)),
             (array('label'=>'Juli','y'=>0,'info'=>NULL)),
             (array('label'=>'Agustus','y'=>0,'info'=>NULL)),
             (array('label'=>'September','y'=>0,'info'=>NULL)),
             (array('label'=>'Oktober','y'=>0,'info'=>NULL)),
             (array('label'=>'November','y'=>0,'info'=>NULL)),
             (array('label'=>'Desember','y'=>0,'info'=>NULL)));
       foreach ($array as $key => $item){
            $num  =  $key +1;
            if($bln>=$num){
                $data = $this->panggil_data_statistik_perbulan(sprintf("%02d", $num),$tahun);
                #print_r($data);die();
                $blm_putus = $data['sisa'] + $data['masuk'] - $data['putus'];
                $array[$key]['y']    = $data['masuk'] ;
                $array[$key]['info'] = strtoupper($array[$key]['label']).":<br>Sisa Sblmnya $data[sisa] Perkara<br>Masuk $data[masuk] Perkara<br>Putus $data[putus] Perkara<br>Belum Putus $blm_putus Perkara";
            }}
        #print_r(json_encode($array));die();
        echo json_encode(json_encode($array,JSON_NUMERIC_CHECK));}
        
        else if($tampil=='judul'){
            $data = $this->panggil_data_statistik_ket($tahun);
            echo json_encode("Sisa Perkara Tahun Lalu = $data[sisa] Perkara &nbsp; | &nbsp; Masuk = $data[masuk] Perkara &nbsp; | &nbsp; Putus = $data[putus] Perkara &nbsp; | &nbsp; Dalam Proses = $data[belum_putus] Perkara<br>
            <p><i>Data Tanggal $data[tgl]</i></p>
            ");}
        die();}





	
#PANGGIL DATA DISINI	
	
	function panggil_data_sidang(){
		return $this->db->query("select a.noPerkara,a.jenisPerkara,b.agenda from jadwalsidangweb b
			join dataumumweb a using(IDPerkara)
			where b.tglSidang=curdate()
			order by a.tglPendaftaran,a.noPerkara;")->result();
	}
	
	function panggil_data_statistik(){
	    return $this->db->query("SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(MONTHNAME(tglPendaftaran),'August','Agustus'),'October','Oktober'),'December','Desember'),'January','Januari'),'February','Februari'),'March','Maret'),'May','Mei'),'June','Juni'),'July','Juli') label, COUNT(tglPendaftaran) y,
	    'Sisa Sblmnya 0 Perkara<br>Masuk 0 Perkara<br>Putus 0 Perkara<br>Dalam Proses 0 Perkara' info
	    FROM dataumumweb WHERE YEAR(tglPendaftaran)=YEAR(CURDATE()) 
	    GROUP BY month(tglPendaftaran) 
	    ORDER BY month(tglPendaftaran);")->result_array();
	}
	
	function panggil_data_statistik_ket($tahun){
	    $query = "SELECT
            DATE_FORMAT(MAX(A.tglPendaftaran),'%d/%m/%Y') tgl,
            SUM(CASE WHEN YEAR(A.tglPendaftaran)<=$tahun-1 AND (YEAR(B.tglMinutasi)>=$tahun OR B.tglMinutasi IS NULL) THEN 1 ELSE 0 END) AS sisa,
            SUM(CASE WHEN YEAR(A.tglPendaftaran)=$tahun THEN 1 ELSE 0 END) AS masuk,
            SUM(CASE WHEN YEAR(B.tglPutusan)=$tahun THEN 1 ELSE 0 END) AS putus,
            SUM(CASE WHEN A.IDProses < 210 THEN 1 ELSE 0 END) AS belum_putus
            FROM dataumumweb AS A LEFT JOIN perkaraputusanweb AS B ON A.IDPerkara=B.IDPerkara WHERE A.klasifikasiPerkara <> 114;";
        #die($query);
	    return $this->db->query($query)->row_array();
            	}
            	
   function panggil_data_statistik_perbulan($bulan,$tahun){
      $time  = $tahun.'-'.$bulan;
      $query = "SELECT (SELECT 
              COUNT(p.IDPerkara) 
            FROM dataumumweb AS p 
              LEFT JOIN perkaraputusanweb AS putus 
            	ON p.IDPerkara = putus.IDPerkara 
            WHERE LEFT(p.tglPendaftaran, 7) < '$time' 
              AND (putus.tglPutusan IS NULL 
            	OR LEFT(putus.tglPutusan, 7) >= '$time')
              AND p.IDPerkara NOT IN (SELECT diversi.perkara_id FROM diversiweb AS diversi WHERE diversi.hasil_diversi = 1 
              AND putus.tglMinutasi IS NOT NULL AND dibuka_kembali=0
              AND (diversi.tgl_penetapan_kesepakatan_diversi <> '0000-00-00' 
            	OR LEFT(diversi.tgl_penetapan_kesepakatan_diversi,7) >= '$time'
              ))) sisa,
              
              
              
            (SELECT 
              COUNT(p.IDPerkara)
            FROM
              dataumumweb AS p 
            WHERE LEFT(p.tglPendaftaran, 7) = '$time') masuk,
            
            
            
            (SELECT 
              COUNT(p.IDPerkara)
            FROM
              perkaraputusanweb AS p 
            WHERE LEFT(p.tglPutusan, 7) = '$time') putus;";

      #die($query);
        return $this->db->query($query)->row_array();
	}
	
}
?>