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

class m_template_bas_eksekusi_agama extends CI_Model {

	function getDataPerkara($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT *
					FROM perkara_eksekusi 
					WHERE perkara_id =$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPerkaraPertama($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT a.perkara_id,a.tanggal_pendaftaran,b.penetapan_majelis_hakim AS tgl_pmh
					FROM perkara a
					LEFT JOIN perkara_penetapan b ON a.perkara_id=b.perkara_id
					WHERE a.perkara_id =$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPenetapanSidang($idperkara){
		try{
			if(!is_numeric($idperkara)){ return '';	}
			$sql = "SELECT a.penetapan_hari_sidang, a.sidang_pertama, b.tanggal_sidang,  b.jam_sidang, b.urutan
					FROM perkara_penetapan AS a, perkara_jadwal_sidang AS b
					WHERE a.perkara_id=b.perkara_id AND a.perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e){ return false; }
	}
	function getDataPMH($idperkara){
		try{
			if(!is_numeric($idperkara)){ return '';	}
			$sql = "SELECT penetapan_majelis_hakim AS tgl_pmh
					FROM perkara_penetapan
					WHERE perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e){ return false; }
	}

	function getDataPenetapanPanitera($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM 
						(SELECT perkara_id, panitera_id, tanggal_penetapan 
							FROM perkara_panitera_pn 
							WHERE perkara_id = $idperkara 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 getDataSidang($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT id,urutan, tanggal_sidang, agenda
					FROM perkara_jadwal_sidang
					WHERE perkara_id =$idperkara ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataMediator($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT mediator_text,tgl_laporan_mediator,hasil_mediasi
					FROM perkara_mediasi
					WHERE perkara_id =$idperkara AND tahapan_id=10 AND jenis_mediasi=1";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getJenisPerkara($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT nama 
					FROM jenis_perkara 
					WHERE id=(SELECT jenis_perkara_id 
								FROM perkara 
								WHERE perkara_id=$idperkara)";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getPihakPemohonEksekusi($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query('SELECT b.*,  a.*,c.nama AS kewarganegaraan, d.nama AS agama,f.nama AS pendidikan,
											GROUP_CONCAT(e.pengacara_id) AS pengacara_id, (YEAR(CURRENT_DATE()) - YEAR(a.tanggal_lahir)) AS umur
											FROM pihak AS a
											LEFT JOIN
											(SELECT * FROM perkara_eksekusi_detil WHERE status_pihak_id=1) AS b
											ON a.id=b.status_pihak_id
											LEFT JOIN
											(SELECT id,nama FROM negara) AS c
											ON a.warga_negara_id=c.id
											LEFT JOIN
											(SELECT id,nama FROM tingkat_pendidikan) AS f
											ON a.pendidikan_id=f.id
											LEFT JOIN
											(SELECT id,nama FROM agama) AS d
											ON a.agama_id=d.id
											LEFT JOIN
											(SELECT pengacara_id, pihak_id, perkara_id FROM perkara_pengacara) AS e
											ON b.pihak_id=e.pihak_id AND e.perkara_id=b.perkara_id
											WHERE b.perkara_id='.$idperkara.' GROUP BY b.id ORDER BY b.id ASC');
			} catch (Exception $e) {
				return '';
			}
	}
	function getPihakAllEksekusi($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query('SELECT b.*,  a.*,c.nama AS kewarganegaraan, d.nama AS agama,f.nama AS pendidikan,
											GROUP_CONCAT(e.pengacara_id) AS pengacara_id, (YEAR(CURRENT_DATE()) - YEAR(a.tanggal_lahir)) AS umur
											FROM pihak AS a
											LEFT JOIN
											(SELECT * FROM perkara_eksekusi_detil) AS b
											ON a.id=b.status_pihak_id
											LEFT JOIN
											(SELECT id,nama FROM negara) AS c
											ON a.warga_negara_id=c.id
											LEFT JOIN
											(SELECT id,nama FROM tingkat_pendidikan) AS f
											ON a.pendidikan_id=f.id
											LEFT JOIN
											(SELECT id,nama FROM agama) AS d
											ON a.agama_id=d.id
											LEFT JOIN
											(SELECT pengacara_id, pihak_id, perkara_id FROM perkara_pengacara) AS e
											ON b.pihak_id=e.pihak_id AND e.perkara_id=b.perkara_id
											WHERE b.perkara_id='.$idperkara.' GROUP BY b.id ORDER BY b.id ASC');
			} catch (Exception $e) {
				return '';
			}
	}
	function getPihakTermohonEksekusi($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query('SELECT b.*,  a.*,c.nama AS kewarganegaraan, d.nama AS agama,f.nama AS pendidikan,
											GROUP_CONCAT(e.pengacara_id) AS pengacara_id, (YEAR(CURRENT_DATE()) - YEAR(a.tanggal_lahir)) AS umur
											FROM pihak AS a
											LEFT JOIN
											(SELECT * FROM perkara_eksekusi_detil WHERE status_pihak_id=2) AS b
											ON a.id=b.status_pihak_id
											LEFT JOIN
											(SELECT id,nama FROM negara) AS c
											ON a.warga_negara_id=c.id
											LEFT JOIN
											(SELECT id,nama FROM tingkat_pendidikan) AS f
											ON a.pendidikan_id=f.id
											LEFT JOIN
											(SELECT id,nama FROM agama) AS d
											ON a.agama_id=d.id
											LEFT JOIN
											(SELECT pengacara_id, pihak_id, perkara_id FROM perkara_pengacara) AS e
											ON b.pihak_id=e.pihak_id AND e.perkara_id=b.perkara_id
											WHERE b.perkara_id='.$idperkara.' GROUP BY b.id ORDER BY b.id ASC');
			} catch (Exception $e) {
				return '';
			}
	}

	function get_data_pihak_loop($idperkara,$jenispihak){
		try {
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query('SELECT b.*,  a.*,c.nama AS kewarganegaraan, d.nama as agama,f.nama as pendidikan,
											group_concat(e.pengacara_id) as pengacara_id, (YEAR(CURRENT_DATE()) - YEAR(a.tanggal_lahir)) AS umur
											FROM pihak AS a
											LEFT JOIN
											(SELECT * FROM perkara_pihak'.$jenispihak.') AS b
											ON a.id=b.pihak_id
											LEFT JOIN
											(SELECT id,nama FROM negara) AS c
											ON a.warga_negara_id=c.id
											LEFT JOIN
											(SELECT id,nama FROM tingkat_pendidikan) AS f
											ON a.pendidikan_id=f.id
											LEFT JOIN
											(SELECT id,nama FROM agama) AS d
											ON a.agama_id=d.id
											LEFT JOIN
											(SELECT pengacara_id, pihak_id, perkara_id FROM perkara_pengacara) AS e
											ON b.pihak_id=e.pihak_id and e.perkara_id=b.perkara_id
											WHERE b.perkara_id='.$idperkara.' GROUP BY b.id ORDER BY b.id ASC');
			} 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 getDataPA(){
		try{
			$sql = "SELECT * FROM 
					(SELECT value AS NamaPN FROM sys_config WHERE id = 62) AS T1,
					(SELECT value AS KetuaPNNama FROM sys_config WHERE id = 64) AS T2,
					(SELECT value AS AlamatPN FROM sys_config WHERE id = 63) AS T3,
					(SELECT value AS WakilKetuaPNNama FROM sys_config WHERE id = 66) AS T4,
					(SELECT value AS PansekPNNama FROM sys_config WHERE id = 68) AS T5,
					(SELECT value AS WaPanNama FROM sys_config WHERE id = 70) AS T6, 
					(SELECT value AS ZonaWaktu FROM sys_config WHERE id = 75) AS T7";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getAllHakim(){
		try{
			$sql = "SELECT * FROM hakim_pn";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPihak($pihak_id){
		try{
			if(!is_numeric($pihak_id)) { return ''; }
			$sql = "SELECT * 
					FROM pihak 
					WHERE id=$pihak_id";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataPihak_eksekusi($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM perkara_eksekusi_detil AS A 
					WHERE A.perkara_id='$idperkara'";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPanitera($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT DISTINCT perkara_id,urutan,panitera_id,panitera_nama,tanggal_penetapan 
					FROM perkara_panitera_pn 
					WHERE perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}		

	function getDataPenetapanHakim($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * 
					FROM (SELECT perkara_id,urutan,jabatan_hakim_id, jabatan_hakim_nama, hakim_nama, hakim_id,tanggal_penetapan,aktif
							FROM perkara_hakim_pn 
							WHERE perkara_id = $idperkara AND aktif='Y'
							GROUP BY hakim_id) AS ph
					LEFT JOIN (SELECT id, nama_gelar FROM hakim_pn) AS h
					ON ph.hakim_id = h.id
					ORDER BY urutan ASC";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
/* 	function getDataJurusita($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * 
					FROM (SELECT *
							FROM perkara_jurusita
							WHERE perkara_id = $idperkara AND aktif='Y'
							GROUP BY jurusita_id) AS ph
					LEFT JOIN (SELECT id, nama_gelar FROM jurusita) AS h
					ON ph.jurusita_id = h.id
					ORDER BY urutan ASC";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	} */
	function getDataJurusita($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT B.nama_gelar,A.tanggal_penetapan FROM perkara_jurusita AS A LEFT JOIN jurusita AS B ON A.jurusita_id=B.id WHERE A.perkara_id='$idperkara'";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataPP($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT B.nama_gelar,A.tanggal_penetapan FROM perkara_panitera_pn AS A LEFT JOIN panitera_pn AS B ON A.panitera_id=B.id WHERE A.perkara_id='$idperkara'";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataPutusanVerstek($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT amar_putusan 
					FROM perkara_putusan
					WHERE perkara_id =$idperkara AND putusan_verstek='Y' ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataAmariKRAR($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT amar_ikrar_talak,tgl_ikrar_talak 
					FROM perkara_ikrar_talak
					WHERE perkara_id =$idperkara ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataPutusan($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT tanggal_putusan,amar_putusan 
					FROM perkara_putusan
					WHERE perkara_id =$idperkara ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataPutusanSela($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT tanggal_putusan_sela,amar_sela 
					FROM perkara_putusan_sela
					WHERE perkara_id =$idperkara ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function get_data_pihak_pemohon($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query('SELECT b.*,  a.*,c.nama AS kewarganegaraan, d.nama as agama,f.nama as pendidikan,
											group_concat(e.pengacara_id) as pengacara_id, (YEAR(CURRENT_DATE()) - YEAR(a.tanggal_lahir)) AS umur
											FROM pihak AS a
											LEFT JOIN
											(SELECT * FROM perkara_eksekusi_detil WHERE status_pihak_id=1) AS b
											ON a.id=b.pihak_id
											LEFT JOIN
											(SELECT id,nama FROM negara) AS c
											ON a.warga_negara_id=c.id
											LEFT JOIN
											(SELECT id,nama FROM tingkat_pendidikan) AS f
											ON a.pendidikan_id=f.id
											LEFT JOIN
											(SELECT id,nama FROM agama) AS d
											ON a.agama_id=d.id
											LEFT JOIN
											(SELECT pengacara_id, pihak_id, perkara_id FROM perkara_pengacara) AS e
											ON b.pihak_id=e.pihak_id and e.perkara_id=b.perkara_id
											WHERE b.perkara_id='.$idperkara.' GROUP BY b.id ORDER BY b.id ASC');
			} catch (Exception $e) {
				return '';
			}
	}
	function get_data_pihak_termohon($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query('SELECT b.*,  a.*,c.nama AS kewarganegaraan, d.nama as agama,f.nama as pendidikan,
											group_concat(e.pengacara_id) as pengacara_id, (YEAR(CURRENT_DATE()) - YEAR(a.tanggal_lahir)) AS umur
											FROM pihak AS a
											LEFT JOIN
											(SELECT * FROM perkara_eksekusi_detil WHERE status_pihak_id=2) AS b
											ON a.id=b.pihak_id
											LEFT JOIN
											(SELECT id,nama FROM negara) AS c
											ON a.warga_negara_id=c.id
											LEFT JOIN
											(SELECT id,nama FROM tingkat_pendidikan) AS f
											ON a.pendidikan_id=f.id
											LEFT JOIN
											(SELECT id,nama FROM agama) AS d
											ON a.agama_id=d.id
											LEFT JOIN
											(SELECT pengacara_id, pihak_id, perkara_id FROM perkara_pengacara) AS e
											ON b.pihak_id=e.pihak_id and e.perkara_id=b.perkara_id
											WHERE b.perkara_id='.$idperkara.' GROUP BY b.id ORDER BY b.id ASC');
			} catch (Exception $e) {
				return '';
			}
	}
//PERADILAN AGAMAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

	function blangko_bas_eksekusi(){
		try{
			$sql = "SELECT kode,nama FROM template_dokumen WHERE 
			(alur_perkara_id='15' AND tahapan_id='50'  AND proses_id='615' AND nomor >= '100' AND nomor <= '200')
			OR
			(alur_perkara_id='15' AND tahapan_id='50'  AND proses_id='622' AND nomor >= '100' AND nomor <= '200')
			 order by kode ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	//PERADILAN AGAMAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
}

