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

class Report_panitera 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 
				".$year." AS year
				,pp.panitera_id AS panitera_id
				,UPPER(pp.panitera_nama) AS panitera_nama
				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-01' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-01' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-01' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-01' ))) AND CONCAT('".$year."','-01') <= LEFT(CURRENT_DATE(),7)
				    ,1,0)) AS BelumMinutasi01
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-01' AND CONCAT('".$year."','-01') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus01
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-01' AND CONCAT('".$year."','-01') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi01
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-01' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-01' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-01' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-01' ))) AND CONCAT('".$year."','-01') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) 
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-01' AND CONCAT('".$year."','-01') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) 
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-01' AND CONCAT('".$year."','-01') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) ) AS Jumlah01    
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-01' AND CONCAT('".$year."','-01') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi01
				      
				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-02' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-02' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-02' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-02' ))) AND CONCAT('".$year."','-02') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS BelumMinutasi02
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-02' AND CONCAT('".$year."','-02') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus02
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-02' AND CONCAT('".$year."','-02') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi02
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-02' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-02' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-02' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-02' ))) AND CONCAT('".$year."','-02') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) 
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-02' AND CONCAT('".$year."','-02') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) 
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-02' AND CONCAT('".$year."','-02') <= LEFT(CURRENT_DATE(),7) 
					,1,0))) AS Jumlah02
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-02' AND CONCAT('".$year."','-02') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi02

				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-03' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-03' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-03' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-03' ))) AND CONCAT('".$year."','-03') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS BelumMinutasi03
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-03' AND CONCAT('".$year."','-03') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus03
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-03' AND CONCAT('".$year."','-03') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi03
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-03' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-03' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-03' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-03' ))) AND CONCAT('".$year."','-03') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) 
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-03' AND CONCAT('".$year."','-03') <= LEFT(CURRENT_DATE(),7) 
					,1,0))
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-03' AND CONCAT('".$year."','-03') <= LEFT(CURRENT_DATE(),7) 
					,1,0))) AS Jumlah03
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-03' AND CONCAT('".$year."','-03') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi03
				    
				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-04' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-04' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-04' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-04' ))) AND CONCAT('".$year."','-04') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS BelumMinutasi04
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-04' AND CONCAT('".$year."','-04') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus04
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-04' AND CONCAT('".$year."','-04') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi04
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-04' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-04' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-04' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-04' ))) AND CONCAT('".$year."','-04') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) 
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-04' AND CONCAT('".$year."','-04') <= LEFT(CURRENT_DATE(),7) 
					,1,0))
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-04' AND CONCAT('".$year."','-04') <= LEFT(CURRENT_DATE(),7) 
					,1,0))) AS Jumlah04
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-04' AND CONCAT('".$year."','-04') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi04
				 
				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-05' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-05' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-05' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-05' ))) AND CONCAT('".$year."','-05') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS BelumMinutasi05
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-05' AND CONCAT('".$year."','-05') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus05
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-05' AND CONCAT('".$year."','-05') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi05
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-05' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-05' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-05' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-05' ))) AND CONCAT('".$year."','-05') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) 
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-05' AND CONCAT('".$year."','-05') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) 
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-05' AND CONCAT('".$year."','-05') <= LEFT(CURRENT_DATE(),7) 
					,1,0))) AS Jumlah05
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-05' AND CONCAT('".$year."','-05') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi05
				    
				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-06' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-06' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-06' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-06' ))) AND CONCAT('".$year."','-06') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS BelumMinutasi06
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-06' AND CONCAT('".$year."','-06') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus06
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-06' AND CONCAT('".$year."','-06') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi06
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-06' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-06' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-06' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-06' ))) AND CONCAT('".$year."','-06') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0))
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-06' AND CONCAT('".$year."','-06') <= LEFT(CURRENT_DATE(),7) 
					,1,0))
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-06' AND CONCAT('".$year."','-06') <= LEFT(CURRENT_DATE(),7) 
					,1,0))) AS Jumlah06
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-06' AND CONCAT('".$year."','-06') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi06
				    
				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-07' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-07' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-07' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-07' ))) AND CONCAT('".$year."','-07') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS BelumMinutasi07
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-07' AND CONCAT('".$year."','-07') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus07
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-07' AND CONCAT('".$year."','-07') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi07
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-07' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-07' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-07' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-07' ))) AND CONCAT('".$year."','-07') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0))
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-07' AND CONCAT('".$year."','-07') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) 
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-07' AND CONCAT('".$year."','-07') <= LEFT(CURRENT_DATE(),7) 
					,1,0))) AS Jumlah07
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-07' AND CONCAT('".$year."','-07') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi07

				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-08' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-08' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-08' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-08' ))) AND CONCAT('".$year."','-08') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS BelumMinutasi08
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-08' AND CONCAT('".$year."','-08') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus08
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-08' AND CONCAT('".$year."','-08') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi08
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-08' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-08' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-08' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-08' ))) AND CONCAT('".$year."','-08') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0))
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-08' AND CONCAT('".$year."','-08') <= LEFT(CURRENT_DATE(),7) 
					,1,0))
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-08' AND CONCAT('".$year."','-08') <= LEFT(CURRENT_DATE(),7) 
					,1,0))) AS Jumlah08
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-08' AND CONCAT('".$year."','-08') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi08

				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-09' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-09' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-09' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-09' ))) AND CONCAT('".$year."','-09') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS BelumMinutasi09
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-09' AND CONCAT('".$year."','-09') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus09
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-09' AND CONCAT('".$year."','-09') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi09
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-09' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-09' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-09' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-09' ))) AND CONCAT('".$year."','-09') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0))
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-09' AND CONCAT('".$year."','-09') <= LEFT(CURRENT_DATE(),7) 
					,1,0))
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-09' AND CONCAT('".$year."','-09') <= LEFT(CURRENT_DATE(),7) 
					,1,0))) AS Jumlah09
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-09' AND CONCAT('".$year."','-09') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi09

				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-10' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-10' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-10' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-10' ))) AND CONCAT('".$year."','-10') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS BelumMinutasi10
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-10' AND CONCAT('".$year."','-10') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus10
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-10' AND CONCAT('".$year."','-10') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi10
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-10' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-10' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-10' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-10' ))) AND CONCAT('".$year."','-10') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0))
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-10' AND CONCAT('".$year."','-10') <= LEFT(CURRENT_DATE(),7) 
					,1,0))
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-10' AND CONCAT('".$year."','-10') <= LEFT(CURRENT_DATE(),7) 
					,1,0))) AS Jumlah10
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-10' AND CONCAT('".$year."','-10') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi10

				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-11' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-11' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-11' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-11' ))) AND CONCAT('".$year."','-11') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS BelumMinutasi11
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-11' AND CONCAT('".$year."','-11') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus11
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-11' AND CONCAT('".$year."','-11') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi11
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-11' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-11' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-11' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-11' ))) AND CONCAT('".$year."','-11') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0))
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-11' AND CONCAT('".$year."','-11') <= LEFT(CURRENT_DATE(),7) 
					,1,0))
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-11' AND CONCAT('".$year."','-11') <= LEFT(CURRENT_DATE(),7) 
					,1,0))) AS Jumlah11
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-11' AND CONCAT('".$year."','-11') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi11

				,SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-12' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-12' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-12' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-12' ))) AND CONCAT('".$year."','-12') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS BelumMinutasi12
				,SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-12' AND CONCAT('".$year."','-12') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Putus12
				,SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-12' AND CONCAT('".$year."','-12') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) AS Mediasi12
				,(SUM(IF( ((LEFT(p.tanggal_putusan,7) < '".$year."-12' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-12' ))
				     OR (LEFT(p.mediasi_berhasil,7) < '".$year."-12' AND (p.tanggal_minutasi IS NULL OR LEFT(p.tanggal_minutasi,7) >= '".$year."-12' ))) AND CONCAT('".$year."','-12') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0))
				 + SUM(IF(p.mediasi_berhasil IS NULL AND LEFT(p.tanggal_putusan,7) ='".$year."-12' AND CONCAT('".$year."','-12') <= LEFT(CURRENT_DATE(),7) 
					,1,0)) 
				 + SUM(IF(LEFT(p.mediasi_berhasil,7) ='".$year."-12' AND CONCAT('".$year."','-12') <= LEFT(CURRENT_DATE(),7) 
					,1,0))) AS Jumlah12
				,SUM(IF(LEFT(p.tanggal_minutasi,7) <= '".$year."-12' AND CONCAT('".$year."','-12') <= LEFT(CURRENT_DATE(),7) 
				    ,1,0)) AS Minutasi12

				,SUM(IF((YEAR(tanggal_putusan) < ".$year." AND (tanggal_minutasi IS NULL OR YEAR(tanggal_minutasi) >= ".$year." ))
						OR(YEAR(mediasi_berhasil) < ".$year." AND (tanggal_minutasi IS NULL OR YEAR(tanggal_minutasi) >= ".$year." )
				    ),1,0)) AS BelumMinutasiTahunLalu
				,SUM(IF(mediasi_berhasil IS NULL AND YEAR(tanggal_putusan) =".$year." 
					,1,0)) AS PutusTahunIni
				,SUM(IF(YEAR(p.mediasi_berhasil) = ".$year." 
					,1,0)) AS MediasiTahunIni
				,(SUM(IF((YEAR(tanggal_putusan) < ".$year." AND (tanggal_minutasi IS NULL OR YEAR(tanggal_minutasi) >= ".$year." ))
						OR(YEAR(mediasi_berhasil) < ".$year." AND (tanggal_minutasi IS NULL OR YEAR(tanggal_minutasi) >= ".$year." )
				    ),1,0))
				 + SUM(IF(mediasi_berhasil IS NULL AND YEAR(tanggal_putusan) =".$year." 
					,1,0)) 
				 + SUM(IF(YEAR(p.mediasi_berhasil) = ".$year." 
					,1,0))) AS JumlahTahunIni
				,SUM(IF(YEAR(p.tanggal_minutasi) = ".$year." 
				    ,1,0)) AS MinutasiTahunIni
				,SUM(IF((YEAR(tanggal_putusan) <= ".$year." AND (tanggal_minutasi IS NULL OR YEAR(tanggal_minutasi) > ".$year." ))
						OR(YEAR(mediasi_berhasil) <= ".$year." AND (tanggal_minutasi IS NULL OR YEAR(tanggal_minutasi) > ".$year." )
				    ),1,0)) AS SisaAkhir
				    
				FROM 
				perkara_panitera_pn AS pp 
				JOIN v_perkara AS p ON  pp.perkara_id= p.perkara_id AND pp.aktif='Y'
				WHERE 
				alur_perkara_id IN (".$idalurperkara.") AND 
				( 
					( YEAR(p.tanggal_pendaftaran) < ".$year." 
					 AND 
						( 
							(YEAR(p.tanggal_putusan)< ".$year."  AND (p.tanggal_minutasi IS NULL OR YEAR(p.tanggal_minutasi)>= ".$year." ))
							OR
							(YEAR(mediasi_berhasil) < ".$year." AND (tanggal_minutasi IS NULL OR YEAR(tanggal_minutasi) >= ".$year."))
				    	) 
					) 
					OR 
					(YEAR(p.tanggal_pendaftaran) = ".$year." AND (YEAR(p.tanggal_putusan)=".$year." OR YEAR(p.mediasi_berhasil)=".$year." )) 
				)
				GROUP BY 
				pp.panitera_id
				,pp.panitera_nama 
				ORDER BY pp.panitera_nama");
		} catch (Exception $e) {
			
		}
	}
}