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

class m_template_mediasi extends CI_Model{
	
	function get_data_pn(){
		try {
			return $this->db->query ("SELECT * FROM 
					(SELECT value AS NamaPN FROM sys_config WHERE id = 62) AS T1,
					(SELECT value AS Panses FROM sys_config WHERE id = 68) AS T2,
					(SELECT value AS AlamatPN FROM sys_config WHERE id = 63) AS T3,
					(SELECT value AS Wapan FROM sys_config WHERE id = 70) AS T4,
					(SELECT value AS ZonaWaktu FROM sys_config WHERE id = 75) AS T5,
					(SELECT value AS NamaPT FROM sys_config WHERE id = 76) AS T6,
					(SELECT value AS Ketua FROM sys_config WHERE id = 64) AS T7,
					(SELECT value AS Wakilketua FROM sys_config WHERE id = 66) AS T8;");
			} catch(PDOException $e) {
			}
	}

	function is_verzet($perkara_id){
		try{
			$sql=$this->db->query ("SELECT COUNT(perkara_id) AS is_verzet FROM perkara_putusan WHERE perkara_id=".$perkara_id." AND putusan_verstek='Y'");
			return $sql->result()[0]->is_verzet;
		}catch(PDOException $e) {
		}
	}

	function is_mediasi($perkara_id){
		try{
			$sql=$this->db->query ("SELECT COUNT(perkara_id) AS is_mediasi FROM perkara_mediasi WHERE perkara_id=".$perkara_id);
			return $sql->result()[0]->is_mediasi;
		}catch(PDOException $e) {
		}
	}

	function get_mediasi_id($perkara_id){
		try{
			$sql=$this->db->query ("SELECT mediasi_id FROM perkara_mediasi WHERE perkara_id=".$perkara_id);
			return $sql->result()[0]->mediasi_id;
		}catch(PDOException $e) {
		}
	}
	function get_file($kode=NULL){
		try {
			$this->db->where('alur_perkara_id','0');
			if ($kode!=NULL){
				$this->db->where('kode',$kode);
			}
			$result = $this->db->get('template_dokumen');
			return $result;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function get_perkara($perkara_id=NULL,$is_verzet=NULL){
		try {
			if ($is_verzet==1) {
				$sql ='SELECT perkara.nomor_perkara, verzet.* from perkara_verzet as verzet LEFT JOIN perkara as perkara on verzet.perkara_id=perkara.perkara_id WHERE verzet.perkara_id='.$perkara_id;
			}else{
				$sql ='SELECT * from perkara WHERE perkara_id='.$perkara_id;
			}
			return $this->db->query($sql);
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_perkara_putusan($perkara_id=NULL,$is_verzet=NULL){
		try {
			if ($is_verzet==1) {
				$sql ='SELECT putusan_verzet as tanggal_putusan from perkara_verzet WHERE perkara_id='.$perkara_id;
			}else{
				$sql ='SELECT * from perkara_putusan WHERE perkara_id='.$perkara_id;
			}
			return $this->db->query($sql);
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_pmh($perkara_id,$is_verzet=NULL){
			try {
				if(!is_numeric($perkara_id)){
					return '';
				}
				if ($is_verzet==1){
					$sql = ("SELECT id,status_hakim_id,nip,nama,nama_gelar as hakim_nama,aktif,
							(SELECT penetapan_majelis_hakim FROM perkara_verzet WHERE perkara_id=".$perkara_id.") AS penetapan_majelis,
							(SELECT penetapan_panitera_pengganti FROM perkara_verzet WHERE perkara_id=".$perkara_id.") AS penetapan_pp
							FROM hakim_pn 
							WHERE id IN
							(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(pv.majelis_hakim_id, ',', numbers.n), ',', -1) id
							FROM 
							(SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers INNER JOIN perkara_verzet AS pv
							ON CHAR_LENGTH(pv.majelis_hakim_id) -CHAR_LENGTH(REPLACE(pv.majelis_hakim_id, ',', ''))>=numbers.n-1 WHERE pv.perkara_id=".$perkara_id.")");
				}else{
					$sql = ('SELECT tanggal_penetapan  AS penetapan_majelis,
				    		nomor_sk_penetapan AS nomor_sk_penetapan_majelis,
							urutan AS urutan_hakim,
							jabatan_hakim_nama,jabatan_hakim_id,
							hakim_nip,
							hakim_nama 
							FROM perkara_hakim_pn WHERE aktif="Y" AND tahapan_id="10" AND perkara_id='.$perkara_id);

				}
				return $this->db->query($sql);
			} catch (Exception $e) {
				return '';
			}
	}

	function get_phs($perkara_id,$is_mediasi){
		try{
			if(!is_numeric($perkara_id)){ 
				return '';	
			}
			if ($is_mediasi==1){
				$sql = "SELECT tanggal_penetapan_sidang_verzet as penetapan_hari_sidang from perkara_verzet where perkara_id=".$perkara_id;	
			}else{
				$sql = "SELECT a.penetapan_hari_sidang, a.sidang_pertama, b.tanggal_sidang,  b.jam_sidang
					FROM perkara_penetapan AS a, perkara_jadwal_sidang AS b
					WHERE a.perkara_id=b.perkara_id AND b.urutan=1 AND a.perkara_id=$perkara_id";

			}
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e){ return false; }
	}

	function get_penetapan_pp($perkara_id){
		try{
			if(!is_numeric($perkara_id)) { return ''; }
			$sql = "SELECT * FROM 
						(SELECT perkara_id, urutan,panitera_id, tanggal_penetapan 
							FROM perkara_panitera_pn 
							WHERE perkara_id = $perkara_id AND aktif='Y'
							GROUP BY panitera_id) AS ph
					LEFT JOIN
						(SELECT id, nama_gelar FROM panitera_pn) AS h
					ON ph.panitera_id = h.id";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function get_data_pihak_loop($idperkara,$jenispihak){
		try {
			if(!is_numeric($idperkara)){
				return '';
			}
			$sql="SELECT a.nomor_perkara, b.urutan, b.pihak_id as id_pihak,c.pengacara_id as id_kuasa,
					d.nama, (year(now()) - year(d.tanggal_lahir)) as umur, e.nama as agama, 
					d.pekerjaan, f.nama as pendidikan, d.alamat, c.nama as nama_kuasa, c.alamat as alamat_kuasa, c.aktif_mulai as tgl_kuasa
					FROM perkara a 
					left join perkara_pihak".$jenispihak." b on a.perkara_id = b.perkara_id
					left join perkara_pengacara c on a.perkara_id = c.perkara_id and b.pihak_id = c.pihak_id
					left join pihak d on b.pihak_id = d.id
					left join agama e on d.agama_id = e.id
					left join tingkat_pendidikan f on d.pendidikan_id = f.id
					where a.perkara_id = ".$idperkara." 
					order by b.urutan";
				
			return $this->db->query($sql);
			} catch (Exception $e) {
				return '';
			}
	}

	function get_data_pengacara($idperkara,$jenispihak){
		try {
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query ("SELECT a.id,
						GROUP_CONCAT(a.pihak_id SEPARATOR ';') AS pihak_id,
						a.urutan,a.nama,a.alamat,
						a.aktif_mulai,'Indonesia' AS kewarganegaraan,
						b.pekerjaan,b.jenis_pihak_id
					FROM perkara_pengacara AS a
										LEFT JOIN
										(SELECT * FROM pihak ) AS b
										ON b.id=a.pengacara_id
										WHERE a.pihak_id IN 
										(SELECT pihak_id FROM perkara_pihak".$jenispihak." WHERE perkara_id=".$idperkara.")
										AND perkara_id=".$idperkara." 
										GROUP BY a.pengacara_id
										ORDER BY urutan ASC");
			} catch (Exception $e) {
				return '';
			}
	}

	function getJadwalMediasi($idjadwalmediasi=NULL){
		if ($idjadwalmediasi!=NULL){
			$where=" WHERE id=$idjadwalmediasi";
		}
		try {
			$sql='SELECT tanggal_mediasi,tempat, dihadiri_oleh, ditunda FROM perkara_jadwal_mediasi '.$where;
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}


	function get_mediasi_terakhir($idmediasi=NULL){
		if ($idmediasi!=NULL){
			$where=" WHERE mediasi_id=$idmediasi";
		}
		try {
			$sql='SELECT id,mediasi_id,urutan, MAX(tanggal_mediasi) as tanggal_mediasi,jam_mediasi,tempat,dihadiri_oleh FROM perkara_jadwal_mediasi '.$where;
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}

		function get_mediasi_awal($idmediasi=NULL){
		if ($idmediasi!=NULL){
			$where=" WHERE mediasi_id=$idmediasi";
		}
		try {
			$sql='SELECT id,mediasi_id,urutan, MIN(tanggal_mediasi) as tanggal_mediasi,jam_mediasi,tempat,dihadiri_oleh FROM perkara_jadwal_mediasi '.$where;
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}
	function get_data_mediator($idperkara=NULL,$jenis_mediasi=NULL){
		if ($idperkara!=NULL){
			$where=" WHERE pm.perkara_id=$idperkara";
		}
		try {
			if ($jenis_mediasi==2){
				$sql='SELECT *, mediator_text as nama_gelar,status_mediator FROM perkara_mediasi AS pm '.$where.' AND pm.jenis_mediasi=2';
			}else{
				
				$sql='SELECT md.* FROM perkara_mediator AS pm
					LEFT JOIN mediator AS md
					ON pm.mediator_id=md.id '.$where;
				
			}
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}

	function get_data_mediasi($idperkara=NULL,$verzet=NULL){
		if ($idperkara!=NULL){
			$where=" WHERE perkara_id=$idperkara";
		}
		try {
			if ($verzet!=NULL){
				$sql='SELECT * FROM perkara_verzet'.$where;
			}else{
				$sql='SELECT * FROM perkara_mediasi '.$where;
			}
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}

	function get_jadwal_sidang($idjadwal=NULL){
		if ($idjadwal!=NULL){
			$where=" WHERE id=$idjadwal";
		}
		try {
			$sql='SELECT * FROM perkara_jadwal_sidang '.$where;
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}
	
	function get_catatan_mediasi($idmediasi,$kode){
		try {
			if ($idmediasi!=NULL) {
				$where=" WHERE mediasi_id=".$idmediasi.' AND kode_template='.'"'.$kode.'"';
			}else{
				$where='';
			}
			$sql='SELECT * FROM perkara_data_dukung_mediasi '.$where;
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}

	function get_biaya_perkara($idperkara){
		try {
			$sql='SELECT jb.nama,pb.jenis_biaya_id,jb.kategori_id,pb.tanggal_transaksi,pb.jumlah 
				  FROM perkara_biaya AS pb, jenis_biaya AS jb
				  WHERE pb.perkara_id='.$idperkara.' AND pb.jenis_biaya_id=jb.id';
			
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}
	
	function get_penanggung_biaya_mediasi($idperkara){
		if(!intval($idperkara)){
			show_404();
			exit();
		}
		try{
			return $this->db->query("SELECT biaya.pihak_id AS pihak_id,
									pihak.nama AS nama_pihak,
									(case when (biaya.pihak_ke = 1) then 'Penggugat' else 'Tergugat' end) as status_pihak
									FROM perkara_biaya as biaya
										LEFT JOIN pihak as pihak
											on biaya.pihak_id=pihak.id
									WHERE biaya.perkara_id=$idperkara and biaya.jenis_biaya_id in (380,381)");
		}catch(Exception $e){
			log_message('error', $e);
		}
	}
	
	function get_data_dukung_mediasi($idperkara,$kode){
		try {
			$sql='SELECT pm.mediasi_id,pm.hasil_mediasi,dd.* FROM perkara_mediasi AS pm
				LEFT JOIN perkara_data_dukung_mediasi AS dd
				ON pm.mediasi_id=dd.mediasi_id
				WHERE pm.perkara_id='.$idperkara.' AND dd.kode_template="'.$kode.'"';
			
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}
}

?>
