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

class Report_panitera_banding extends CI_Model{
	
	function get_info_pn(){
		try {
			$this->db->select('*');
			$this->db->where('id >=', 61); 
			return $this->db->get('sys_config');
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
	function getJenisPerkara($idalurperkara){
		if(empty($idalurperkara)) return '';
		try {
			$this->db->where('id',$idalurperkara);
			return $this->db->get('alur_perkara')->row()->nama;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_rekap($idalurperkara,$year){
		try {
			
			return $this->db->query("
			select 
			pp.`panitera_id` as panitera_id
			,upper(pp.`panitera_nama`) as panitera_nama
			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-01' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-01' )) and CONCAT('".$year."','-01') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi01
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-01' and CONCAT('".$year."','-01') <= left(CURRENT_DATE(),7) 
				,1,0)) as Putus01
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-01' and LEFT(p.minutasi_banding,7) = '".$year."-01' and CONCAT('".$year."','-01') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi01
			      
			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-02' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-02' )) and CONCAT('".$year."','-02') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi02
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-02' and CONCAT('".$year."','-02') <= left(CURRENT_DATE(),7) 
				,1,0)) as Putus02
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-02' and LEFT(p.minutasi_banding,7) = '".$year."-02' and CONCAT('".$year."','-02') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi02

			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-03' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-03' )) and CONCAT('".$year."','-03') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi03
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-03' and CONCAT('".$year."','-03') <= left(CURRENT_DATE(),7) 
				,1,0)) as Putus03
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-03' and LEFT(p.minutasi_banding,7) = '".$year."-03' and CONCAT('".$year."','-03') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi03
			    
			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-04' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-04' )) and CONCAT('".$year."','-04') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi04
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-04' and CONCAT('".$year."','-04') <= left(CURRENT_DATE(),7) 
				,1,0)) as Putus04
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-04' and LEFT(p.minutasi_banding,7) = '".$year."-04' and CONCAT('".$year."','-04') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi04
			 
			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-05' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-05' )) and CONCAT('".$year."','-05') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi05
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-05' and CONCAT('".$year."','-05') <= left(CURRENT_DATE(),7) 
				,1,0)) as Putus05
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-05' and LEFT(p.minutasi_banding,7) = '".$year."-05' and CONCAT('".$year."','-05') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi05
			    
			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-06' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-06' )) and CONCAT('".$year."','-06') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi06
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-06' and CONCAT('".$year."','-06') <= left(CURRENT_DATE(),7) 
				,1,0)) as Putus06
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-06' and LEFT(p.minutasi_banding,7) = '".$year."-06' and CONCAT('".$year."','-06') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi06
			    
			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-07' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-07' )) and CONCAT('".$year."','-07') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi07
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-07' and CONCAT('".$year."','-07') <= left(CURRENT_DATE(),7)
				,1,0)) as Putus07
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-07' and LEFT(p.minutasi_banding,7) = '".$year."-07' and CONCAT('".$year."','-07') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi07
			    
			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-08' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-08' )) and CONCAT('".$year."','-08') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi08
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-08' and CONCAT('".$year."','-08') <= left(CURRENT_DATE(),7) 
				,1,0)) as Putus08
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-08' and LEFT(p.minutasi_banding,7) = '".$year."-08' and CONCAT('".$year."','-08') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi08

			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-09' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-09' )) and CONCAT('".$year."','-09') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi09
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-09' and CONCAT('".$year."','-09') <= left(CURRENT_DATE(),7) 
				,1,0)) as Putus09
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-09' and LEFT(p.minutasi_banding,7) = '".$year."-09' and CONCAT('".$year."','-09') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi09
			         
			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-10' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-10' )) and CONCAT('".$year."','-10') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi10
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-10' and CONCAT('".$year."','-10') <= left(CURRENT_DATE(),7) 
				,1,0)) as Putus10
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-10' and LEFT(p.minutasi_banding,7) = '".$year."-10' and CONCAT('".$year."','-10') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi10

			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-11' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-11' )) and CONCAT('".$year."','-11') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi11
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-11' and CONCAT('".$year."','-11') <= left(CURRENT_DATE(),7) 
				,1,0)) as Putus11
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-11' and LEFT(p.minutasi_banding,7) = '".$year."-11' and CONCAT('".$year."','-11') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi11

			,SUM(if((LEFT(p.putusan_banding,7) < '".$year."-12' and (p.minutasi_banding is null or LEFT(p.minutasi_banding,7) >= '".$year."-12' )) and CONCAT('".$year."','-12') <= left(CURRENT_DATE(),7)
			    ,1,0)) as BelumMinutasi12
			,SUM(if(LEFT(p.putusan_banding,7) ='".$year."-12' and CONCAT('".$year."','-12') <= left(CURRENT_DATE(),7) 
				,1,0)) as Putus12
			,SUM(if(LEFT(p.putusan_banding,7) <='".$year."-12' and LEFT(p.minutasi_banding,7) = '".$year."-12' and CONCAT('".$year."','-12') <= left(CURRENT_DATE(),7) 
			    ,1,0)) as Minutasi12

			,SUM(if((year(putusan_banding) < ".$year." and (minutasi_banding is null or year(minutasi_banding) >= ".$year." )	)
				,1,0)) as BelumMinutasiTahunLalu
			,SUM(if(year(putusan_banding) =".$year." 
				,1,0)) as PutusTahunIni
			,SUM(if(year(putusan_banding) <=".$year." and year(minutasi_banding) = ".$year." 
			    ,1,0)) as MinutasiTahunIni
			,SUM(if((year(putusan_banding) <= ".$year." and (minutasi_banding is null or year(minutasi_banding) > ".$year." )	)
				,1,0)) as SisaAkhir
			                            
			from 
			perkara_panitera_banding as pp 
			join perkara_banding as p on  pp.`perkara_id`= p.`perkara_id` and pp.`aktif`='Y'
			where 
			p.alur_perkara_id in (".$idalurperkara.") and 
			( 
				(
					year(p.putusan_banding)< ".$year." 
			    	and (p.minutasi_banding is null or year(p.minutasi_banding)>= ".$year." ) 
			   	) 
				or 
				year(p.putusan_banding) = ".$year."  
			)
			GROUP BY 
			pp.`panitera_id`
			,pp.`panitera_nama` 
			order by pp.`panitera_nama`;");
		} catch (Exception $e) {
			
		}
	}
}