<?php 
if (!defined('BASEPATH')) exit('No direct script access allowed');
class m_template_penetapan_agama extends CI_Model {
	function get_cek_perubahan_pmh($idperkara){
		try{
			if(!is_numeric($idperkara)){ return '';	}
			$sql = "SELECT count(urutan)cek_perubahan
					FROM perkara_hakim_pn
					WHERE perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e){ return false; }
	}
	function get_cek_perubahan_pp($idperkara){
		try{
			if(!is_numeric($idperkara)){ return '';	}
			$sql = "SELECT perkara_id,aktif
					FROM perkara_panitera_pn
					WHERE aktif='T' AND perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e){ return false; }
	}
	function get_cek_perubahan_pjs($idperkara){
		try{
			if(!is_numeric($idperkara)){ return '';	}
			$sql = "SELECT perkara_id,aktif
					FROM perkara_jurusita
					WHERE aktif='T' AND 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
					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=$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 getRefJenisTahanan(){
		try{
			$sql = "SELECT * 
					FROM jenis_tahanan";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getDataPerkara($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT nomor_perkara,nomor_surat,tanggal_surat, tanggal_pendaftaran, dakwaan 
					FROM perkara 
					WHERE perkara_id =$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPernikahan($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * 
					FROM perkara_data_pernikahan 
					WHERE perkara_id =$idperkara";
			$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 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 PlhKetua FROM sys_config WHERE id = 93) AS T8,
					(SELECT value AS PlhPanitera FROM sys_config WHERE id = 95) AS T9, 	
					(SELECT value AS PltKetua FROM sys_config WHERE id = 97) AS T10,	
					(SELECT value AS PltPanitera FROM sys_config WHERE id = 99) AS T11,
					(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 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 
					join perkara_pihak".$jenispihak." b on a.perkara_id = b.perkara_id
					left join 
						(select 
						case
							when (ca.jumlah = 1) then cb.nama 
							else concat(cb.nama, ' dan kawan-kawan')
							end as nama,
							cb.pengacara_id, cb.alamat, cb.aktif, cb.perkara_id, cb.pihak_id,cb.aktif_mulai
						from 
						(select COUNT(1) AS jumlah, id from perkara_pengacara caa 
						group by caa.pihak_id) ca
						join perkara_pengacara cb on ca.id = cb.id) as c 
					on a.perkara_id = c.perkara_id and b.pihak_id = c.pihak_id
					join pihak d on b.pihak_id = d.id
					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 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 getDatastatus_penetapan_ikrar_talak_id($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT DISTINCT status_penetapan_ikrar_talak_id,amar_ikrar_talak,tanggal_penetapan_sidang_ikrar,amar_ikrar_talak,tgl_ikrar_talak,majelis_hakim_nama AS nama_majelis_ikrar
					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
					FROM perkara_putusan
					WHERE perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

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

	function getDataTerdakwa($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			 $sql="SELECT 		a.*
								,b.tempat_lahir
								,b.tanggal_lahir
								,b.jenis_kelamin
								,d.nama AS warga_negara
								,c.nama AS agama_nama
								,b.pekerjaan
							FROM perkara_pihak2 AS a
							LEFT JOIN pihak AS b
							ON b.id=a.pihak_id
							LEFT JOIN agama AS c
							ON c.id=b.agama_id
							LEFT JOIN negara AS d
							ON d.id=b.warga_negara_id
							
							WHERE a.perkara_id=$idperkara
							ORDER BY a.urutan";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPihakTerdakwa1($idperkara, $idpihak){
		try{
			if(!is_numeric($idperkara) OR !is_numeric($idpihak)) { return ''; }
			$sql = "SELECT 	a.pihak_id, a.urutan, 
						b.nama, b.alamat, b.tempat_lahir, b.tanggal_lahir, b.jenis_kelamin, a.pangkat, a.nrp, a.jabatan, a.kesatuan,
						c.nama AS agama_nama, d.nama AS warga_negara, b.pekerjaan 
					FROM 		perkara_pihak2 a
					LEFT JOIN pihak AS b ON a.pihak_id=b.id
					LEFT JOIN agama AS c ON  b.agama_id=c.id
					LEFT JOIN negara AS d ON b.warga_negara_id=d.id
					WHERE a.perkara_id = $idperkara AND a.pihak_id=$idpihak
					ORDER BY urutan ASC";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPenahananTerdakwa($idperkara,$idpihak){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT 	pihak_id,
							jp.id AS idjns_penahanan,
							jp.nama AS oleh, 
							sp.nama AS status_penahanan, 
							jt.nama AS jenis_tahanan,
							pt.mulai AS sejak, 
							pt.sampai, 
							pt.lama_penahanan,
							pt.tanggal_surat as tglPenetapan,
							pt.id ,
							pt.tanggal_surat, pt.nomor_surat 
					FROM (SELECT * FROM penahanan_terdakwa WHERE perkara_id = $idperkara AND pihak_id = $idpihak ORDER BY jenis_penahanan_id, mulai ASC) pt 
					LEFT JOIN jenis_penahanan jp ON pt.jenis_penahanan_id=jp.id
					LEFT JOIN status_penahanan sp ON pt.status_penahanan_id=sp.id
					LEFT JOIN jenis_tahanan jt ON pt.jenis_tahanan_id=jt.id
					ORDER BY jp.id" ;
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	
	function getDataPenahanan($idpenahanan){
		try{
		$sql= "SELECT 	A.id,
						A.perkara_id,
						A.pihak_id,
						B.nama AS jenis_penahanan,
						C.nama AS status_penahanan,
						D.nama AS jenis_tahanan,
						A.tanggal_surat,
						A.nomor_surat,
						A.mulai,
						A.sampai,
						A.waktu_penahanan,
						A.lama_penahanan,
						A.rutan_id,
						E.nama
				FROM penahanan_terdakwa A 
				LEFT JOIN jenis_penahanan B ON A.jenis_penahanan_id=B.id 
				LEFT JOIN status_penahanan C ON A.status_penahanan_id=C.id
				LEFT JOIN jenis_tahanan D ON A.jenis_tahanan_id=D.id
				LEFT JOIN lembaga_pemasyarakatan AS E ON A.rutan_id = E.id
				WHERE A.id=$idpenahanan ";				
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataHakim($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * 
					FROM (SELECT perkara_id,urutan,jabatan_hakim_id, jabatan_hakim_nama, hakim_id, tanggal_penetapan 
							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 jabatan_hakim_id ASC";		
			$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,keterangan
							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 getDataPenetapanHakimSebelumnya($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 as tgl_penetapan_hakim_sebelumnya,aktif
			// 				FROM perkara_hakim_pn 
			// 				WHERE perkara_id = $idperkara  AND aktif='T'
			// 				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";
			$sql = "SELECT b.nama_gelar, a.tanggal_penetapan as tgl_penetapan_hakim_sebelumnya
					FROM perkara_hakim_pn a 
					JOIN hakim_pn b ON a.hakim_id = b.id
					WHERE a.perkara_id = '$idperkara' AND a.aktif = 'T' AND a.urutan = 1 ORDER BY a.id DESC LIMIT 1";
			$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, B.jabatan 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 getDataJurusitaIkrar($idperkara){
		try {
			return $this->db->query("SELECT p.perkara_id, p.jurusita_id, js.nama_gelar, js.jabatan, p.penetapan_jurusita FROM perkara_ikrar_talak AS p
					LEFT JOIN jurusita AS js
					ON p.jurusita_id = js.id
					WHERE perkara_id = ".$idperkara.";");
		} catch (Exception $e) {
			
		}
	}
	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 getPenahananBefore($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM penahanan_terdakwa WHERE id = ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getKejaksaan($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT IF(a.alamat <> '',a.alamat, b.kejaksaan) AS kejaksaan  FROM 
					perkara_pihak1 AS a
					LEFT JOIN  pihak_jaksa AS b ON a.pihak_id=b.pihak_id
					WHERE a.perkara_id='".$idperkara."' ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	
	function blangko_pmh_pertama(){
		try{
			$sql = "SELECT kode,nama FROM template_dokumen WHERE alur_perkara_id='15' AND tahapan_id='12'  AND proses_id='20'";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	
	function blangko_pppp(){
		try{
			$sql = "SELECT kode,nama FROM template_dokumen WHERE alur_perkara_id='15' AND tahapan_id='12'  AND proses_id='30'";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function blangko_phs($idalurperkara){
		try{
			$sql = "SELECT kode,nama FROM template_dokumen WHERE alur_perkara_id='".$idalurperkara."' AND tahapan_id='12'  AND proses_id='80' ORDER BY nama ASC";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function blangko_delegasi_ikrar(){
		try{
			$sql = "SELECT kode,nama FROM template_dokumen WHERE alur_perkara_id='15' AND tahapan_id='18'  AND proses_id='294' ORDER BY nama ASC";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	
	function blangko_pjs(){
		try{
			$sql = "SELECT kode,nama FROM template_dokumen WHERE alur_perkara_id='15' AND tahapan_id='12'  AND proses_id='40'";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataIkrarTalak($idperkara){
		try{
			$sql = "SELECT a.*,b.nama_gelar as nama_pp_ikrar,c.nama_gelar as nama_js_ikrar 
							from perkara_ikrar_talak a 
							left join panitera_pn b on a.panitera_pengganti_id = b.id 
							left join jurusita c on a.jurusita_id = c.id 
							where perkara_id ='".$idperkara."' ";
			$query=$this->db->query($sql);
			return $query;
			}catch(Exception $e){
				return false;
			}
	}
	function getDataPMHIkrar($idperkara) {
			try{
			$sql = "SELECT a.tanggal_putusan AS tgl_putus, b.majelis_hakim_nama AS majelis_hakim, penetapan_majelis_hakim AS tgl_pmh
					FROM perkara_putusan a
					JOIN perkara_ikrar_talak 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 getDataMajelisIkrarTalak($idperkara){
		try{
			if(intval($idperkara)>0){
				$sql = "SELECT majelis_hakim_nama FROM perkara_ikrar_talak WHERE perkara_id = $idperkara";
				$query = $this->db->query($sql);
				return $query;
			}else{
				return "";
			}
		}catch(Exception $e){
			return FALSE;
		}
	}

	function getDataPaniteraIkrarTalak($idperkara){
		try{
			if(intval($idperkara)>0){
				$sql = "select a.panitera_pengganti_id,b.nama from perkara_ikrar_talak a join panitera_pn b on a.panitera_pengganti_id = b.id where a.perkara_id = $idperkara";
				$query = $this->db->query($sql);
				return $query;
			}else{
				return "";
			}
		}catch(Exception $e){
			return FALSE;
		}
	}

	function getDataPenetapanSidangIkrar($idperkara){
		try{
			if(!is_numeric($idperkara)){ return '';	}
			$sql = "SELECT tanggal_penetapan_sidang_ikrar AS penetapan_hari_sidang, tanggal_sidang_pertama AS sidang_pertama, b.tanggal_sidang,  b.jam_sidang, a.majelis_hakim_nama
					FROM perkara_ikrar_talak AS a, perkara_jadwal_sidang AS b
					WHERE a.perkara_id=b.perkara_id AND a.sidang_id=b.id AND a.perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e){ return false; }
	}

	function getDataPenetapanHakimIkrar($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM perkara_hakim_pn AS ph
					LEFT JOIN hakim_pn AS h
					ON ph.hakim_id = h.id
					WHERE (jabatan_hakim_id = 1 OR jabatan_hakim_id =3) AND ph.perkara_id = ".$idperkara." AND ph.aktif='Y';";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataAmar($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT amar_putusan, tanggal_putusan FROM perkara_putusan
					WHERE perkara_id = ".$idperkara."";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	
}
?>