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

class Tab_status_perkara extends CI_Controller {
	function index(){
		
		$data['namaPN'] = 'Not Configure';
		$data['nama_bulan']=array('', 'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni', 'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember');
		$this->load->model('dashboard/data_dashboard','indikator');
		$data['status_perkara']=$this->indikator->get_status_perkara_all();
		$result=array();
		
		foreach($data['status_perkara']->result() as $data){
			$all=$this->input->post('all');
			$id = $data->id;
			$nama_alur_perkara = $data->nama;
			$alur_perkara_id = $data->alur_perkara_id;
			$proses_id = $data->proses_id;
			$id_proses_alur_perkara = $data->id_proses_alur_perkara;
			$proses_nama= $data->proses_nama;
			$proses_judul = 'Perkara Yang Belum '.$proses_nama;
			$batas_waktu =$data->batas_waktu ;
			$satuan =$data->satuan ;
			$satuan_text = $satuan==1 ? 'Hari Kerja' : ($satuan==2 ? 'Hari Kalender':'Bulan');
			$dari_proses_nama =$data->dari_proses_nama ;
			$batas_waktu_normal = $batas_waktu.' '.$satuan_text.' Dari '.$dari_proses_nama;
			$nama_tabel_dari=$data->nama_tabel_dari;
			$nama_kolom_dari=$data->nama_kolom_dari;
			$nama_tabel_ke=$data->nama_tabel_ke;
			$nama_kolom_ke=$data->nama_kolom_ke;
			$tahapan_id = $data->tahapan_id;
			$all = $this->input->post('all');
			$idpanitera = $this->session->userdata('panitera_id');
			$idhakim = $this->session->userdata('hakim_id');
			$idjurusita = $this->session->userdata('jurusita_id');
			//echo "idhakim = ".$idhakim."idpanitera = ".$idpanitera."idjurusita = ".$idjurusita."<br><br>";
			if($all!='Y'){
				$addand = " AND (( perkara_penetapan.majelis_hakim_id is not null and 
				concat(',',perkara_penetapan.majelis_hakim_id,',') like '%,".$idhakim.",%'  and '".$idhakim."'<>'')
				or 
				(
				perkara_penetapan.panitera_pengganti_id is not null and 
				concat(',',perkara_penetapan.panitera_pengganti_id,',') like '%,".$idpanitera.",%' and '".$idpanitera."'<>'')
				or 
				(perkara_penetapan.jurusita_id is not null and 
				concat(',',perkara_penetapan.jurusita_id,',') like '%,".$idjurusita.",%' and '".$idjurusita."'<>''))";
			}else{
				$addand = "";
			}
			

			$check_sql1 = "select count(perkara.perkara_id) as '".$proses_nama."' from perkara ";
			if($nama_tabel_dari != 'perkara'){
				$check_sql1 .= " left outer join ".$nama_tabel_dari." on perkara.perkara_id=".$nama_tabel_dari.".perkara_id ";
			}
			if($nama_tabel_dari != $nama_tabel_ke ){ 
				if($nama_tabel_ke!='perkara_jadwal_musyawarah_diversi'){
					$check_sql1 .= " left outer join ".$nama_tabel_ke." on ".$nama_tabel_dari.".perkara_id=".$nama_tabel_ke.".perkara_id ";
				}else{
					$check_sql1 .= " left outer join ".$nama_tabel_ke." on ".$nama_tabel_dari.".id=".$nama_tabel_ke.".diversi_id ";
				}
			}
			
			if($nama_tabel_ke!='perkara_penetapan' AND ($alur_perkara_id!=8 and $alur_perkara_id!=17) ){
					$check_sql1 .= " left outer join perkara_penetapan on ".$nama_tabel_dari.".perkara_id=perkara_penetapan.perkara_id ";
			}
			if(($alur_perkara_id==8 OR $alur_perkara_id==17)  AND $all!='Y' AND $proses_id>81 AND $proses_id!=210){
					$check_sql1 .= " left outer join perkara_penetapan on ".$nama_tabel_dari.".perkara_id=perkara_penetapan.perkara_id ";
			}	
			$check_sql1 .= " where perkara.proses_terakhir_id != 211 AND perkara.alur_perkara_id='".$alur_perkara_id."' "
			." and ".$nama_tabel_dari.".".$nama_kolom_dari." is not null " 
			." and ".$nama_tabel_ke.".".$nama_kolom_ke." is null " ;
			if($nama_tabel_dari =='perkara_banding'){
	      	$check_sql1 .= " AND perkara_banding.status_putusan_banding_text <> 'Dicabut' AND perkara.proses_terakhir_id != 391 ";
	    	}
			if($satuan==1)
			{ 
				$check_sql1 .= " and GetHariKerja(".$nama_tabel_dari.".".$nama_kolom_dari.",'".date("Y-m-d")."') <= $batas_waktu ".$addand."";
			}
			else if($satuan==2)
			{ 
				$check_sql1 .= " and DATEDIFF('".date("Y-m-d")."',".$nama_tabel_dari.".".$nama_kolom_dari.") <= $batas_waktu ".$addand."";
			}
			else if($satuan==3)
			{ 
				$check_sql1 .= " and (DATEDIFF('".date("Y-m-d")."',".$nama_tabel_dari.".".$nama_kolom_dari.")/30) <= $batas_waktu ".$addand." ";
			}

			if(($alur_perkara_id=='118' && $proses_id=='80') || ($alur_perkara_id=='118' && $proses_id=='210')){
				$check_sql1 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_diversi WHERE tgl_penetapan_kesepakatan_diversi <> '0000-00-00' AND tgl_minutasi <> '0000-00-00' AND hasil_diversi=1) ";
			}
			if($alur_perkara_id=='118' && $proses_id=='213'){
				$check_sql1 .= " AND perkara.perkara_id IN (SELECT perkara_id FROM perkara_diversi WHERE hasil_diversi=1 AND hasil_lap_pembimbing_masyarakat=1) ";
			}
			if($proses_id=='390'){
				$check_sql1 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_banding WHERE tanggal_cabut IS NOT NULL) ";
			}
			if(($proses_id=='80' OR $proses_id=='81') && $alur_perkara_id=='8'){//dismissal
				$check_sql1 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_putusan WHERE tanggal_putusan IS NOT NULL AND status_putusan_id=28) ";
			}

      		$this->load->model('dashboard/data_dashboard','indikator');
		    $check_sql2 = "select count(perkara.perkara_id) as asterlambat , '".$proses_nama."', '".$proses_judul."' as proses_judul,
			'".$id_proses_alur_perkara."' as id_proses_alur_perkara, 
			'".$proses_id."' as proses_id,
			'0' as status,
			'".$nama_alur_perkara."' as nama_alur_perkara,
			".$alur_perkara_id." as alur_perkara_id ,
			(".$check_sql1.") as normal
			from perkara ";
			if($nama_tabel_dari != 'perkara'){
					$check_sql2 .= " left outer join ".$nama_tabel_dari." on perkara.perkara_id=".$nama_tabel_dari.".perkara_id ";
			}if($nama_tabel_dari != $nama_tabel_ke ){ 
				if($nama_tabel_ke!='perkara_jadwal_musyawarah_diversi'){
					$check_sql2 .= " left outer join ".$nama_tabel_ke." on ".$nama_tabel_dari.".perkara_id=".$nama_tabel_ke.".perkara_id ";
				}else{
					$check_sql2 .= " left outer join ".$nama_tabel_ke." on ".$nama_tabel_dari.".id=".$nama_tabel_ke.".diversi_id ";
				}
			}if($nama_tabel_ke!='perkara_penetapan' AND ($alur_perkara_id!=8 AND $alur_perkara_id!=17)){
					$check_sql2 .= " left outer join perkara_penetapan on ".$nama_tabel_dari.".perkara_id=perkara_penetapan.perkara_id ";
			}
			if(($alur_perkara_id==8 OR $alur_perkara_id==17) AND $all!='Y' AND $proses_id>81 AND $proses_id!=210){
					$check_sql2 .= " left outer join perkara_penetapan on ".$nama_tabel_dari.".perkara_id=perkara_penetapan.perkara_id ";
			}
			$check_sql2 .= " where perkara.proses_terakhir_id != 211 AND perkara.alur_perkara_id='".$alur_perkara_id."' "
				." and ".$nama_tabel_dari.".".$nama_kolom_dari." is not null " 
				." and ".$nama_tabel_ke.".".$nama_kolom_ke." is null " ;
				
			if($nama_tabel_dari =='perkara_banding'){
		      	$check_sql2 .= " AND perkara_banding.status_putusan_banding_text <> 'Dicabut' AND perkara.proses_terakhir_id != 391 ";
		   	}
		    if($satuan==1){ 
					$check_sql2 .= " and GetHariKerja(".$nama_tabel_dari.".".$nama_kolom_dari.",'".date("Y-m-d")."') > $batas_waktu ".$addand." ";
			}else if($satuan==2){ 
					$check_sql2 .= " and DATEDIFF('".date("Y-m-d")."',".$nama_tabel_dari.".".$nama_kolom_dari.") > $batas_waktu ".$addand." ";
			}else if($satuan==3){ 
					$check_sql2 .= " and (DATEDIFF('".date("Y-m-d")."',".$nama_tabel_dari.".".$nama_kolom_dari.")/30) > $batas_waktu ".$addand." ";
			}
			if(($alur_perkara_id=='118' && $proses_id=='80') || ($alur_perkara_id=='118' && $proses_id=='210')){
				$check_sql2 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_diversi WHERE tgl_penetapan_kesepakatan_diversi <> '0000-00-00' AND tgl_minutasi <> '0000-00-00' AND hasil_diversi=1) ";
			}
			if($proses_id=='20' || $proses_id=='30' || $proses_id=='40' || $proses_id=='80'){
				$check_sql2 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_putusan WHERE tanggal_cabut IS NOT NULL) ";
			}
			if($proses_id=='20' && ($alur_perkara_id=='9' || $alur_perkara_id=='10' || $alur_perkara_id=='11')){
				$check_sql2 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_putusan WHERE tanggal_minutasi IS NOT NULL) ";
			}

			if($proses_id=='301' || $proses_id=='400'){
			$check_sql2 .= " AND perkara_banding.perkara_id NOT IN (SELECT perkara_id FROM perkara_banding_detil WHERE status_pihak_id <> '1' AND pemberitahuan_permohonan_banding IS NOT NULL) ";
			}
			if($proses_id=='440'){
				$check_sql2 .= " AND perkara_kasasi.minutasi_kasasi IS NULL AND perkara_kasasi.perkara_id NOT IN (SELECT perkara_id FROM perkara_kasasi_detil WHERE status_pihak_id = '1' AND tidak_memenuhi_syarat IS NOT NULL) ";
			}
			if($proses_id=='490'){
				$check_sql2 .= " AND perkara_kasasi.perkara_id NOT IN (SELECT perkara_id FROM perkara_kasasi WHERE status_kasasi_id IN ('491','492')) ";
			}
			if($proses_id=='500'){
			$check_sql2 .= " AND perkara_kasasi.perkara_id NOT IN (SELECT perkara_id FROM perkara_kasasi_detil WHERE status_pihak_id <> '1' AND pemberitahuan_putusan_kasasi IS NOT NULL) ";
			}
			//perbaikan status cabut pengiriman kasasi dan TMSF
			if($proses_id=='470'){
			$check_sql2 .= " AND perkara_kasasi.perkara_id NOT IN (SELECT perkara_id FROM perkara_kasasi WHERE tanggal_cabut IS NOT NULL) ";
			}
			if($proses_id=='470'){
			$check_sql2 .= " AND perkara_kasasi.perkara_id NOT IN (SELECT perkara_id FROM perkara_kasasi WHERE status_kasasi_text='Perkara Tidak Memenuhi Syarat Formil') ";
			}
			//end 
						
			if($proses_id=='571'){
			$check_sql2 .= " AND perkara_pk.perkara_id NOT IN (SELECT perkara_id FROM perkara_pk_detil WHERE status_pihak_id <> '1' AND pemberitahuan_putusan_pk IS NOT NULL) ";
			}
			if($alur_perkara_id=='118' && $proses_id=='70'){
				$check_sql2 .= " AND perkara_diversi.kesepakatan_melaksanakan_diversi=1";
			}
			if($alur_perkara_id=='118' && $proses_id=='80'){
				$check_sql2 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_diversi)";
			}
			if($alur_perkara_id=='118' && $proses_id=='213'){
				$check_sql2 .= " AND perkara.perkara_id IN (SELECT perkara_id FROM perkara_diversi WHERE hasil_diversi=1 AND hasil_lap_pembimbing_masyarakat=1) ";
			}
			if($proses_id=='390'){
				$check_sql2 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_banding WHERE tanggal_cabut IS NOT NULL) ";
			}			
			if($proses_id=='74'){
				$check_sql2 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_diversi WHERE tgl_minutasi IS NOT NULL) ";
			}			
			if($proses_id=='20' && ($alur_perkara_id=='9' || $alur_perkara_id=='10' || $alur_perkara_id=='11')){
				$check_sql2 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_verzet WHERE status_putusan_verzet_id <> '34') ";
			}
			if(($proses_id=='80' OR $proses_id=='81') && $alur_perkara_id=='8'){//dismissal
				$check_sql2 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_putusan WHERE tanggal_putusan IS NOT NULL AND status_putusan_id=28) ";
			}
			if($alur_perkara_id=='118' && $proses_id=='71'){
				$check_sql2 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_diversi WHERE hasil_diversi='0')";
			}
			if($alur_perkara_id=='118' && $proses_id=='71'){
				$check_sql2 .= " AND perkara.perkara_id NOT IN (SELECT perkara_id FROM perkara_diversi WHERE tgl_penetapan_musyawarah<'2017-12-11')";
			}
			if($proses_id==43){
				$check_sql2 .= " AND perkara.jenis_perkara_id=625 ";
			}
			//echo $check_sql2."<br><br>";
			$this->load->model('dashboard/data_dashboard','indikator');
		    $result[] = $this->indikator->getTotal($check_sql2);
		}
		
		$datas['result'] = $result;
		$this->load->vars($data);
		$this->load->vars($datas);
		$this->load->view('dashboard/status_perkara');
		$this->load->view('footer');
	}

}
