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

class m_template_mil extends CI_Model {

	function getDataPN(){
		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 IDPN FROM sys_config WHERE id = 81) AS T7,
					(SELECT value AS PansekPNNip FROM sys_config WHERE id = 69) AS T8,
					(SELECT value AS PansekPNPangkat FROM sys_config WHERE id = 86) AS T9, 
					(SELECT value AS KodePN FROM sys_config WHERE id = 61) AS T10"; 
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function get_bht($perkara_id){
		try {
			$sql="SELECT @tanggal:=tanggal_bht FROM perkara_putusan WHERE perkara_id = ".$perkara_id;
			$this->db->query($sql);
			return TRUE;
		} catch (Exception $e) {
			return false;
		}
	}

	function get_pn($id_pn){
		try {
			$sql="select * from pengadilan_negeri where id=".$id_pn;
			return $this->db->query($sql);
		} catch (Exception $e) {
			return false;
		}
	}

	function get_perkara_panitera($perkara_id){
		try {
			$sql="SELECT ppn.* 
				FROM perkara_panitera_pn AS pp 
					LEFT JOIN panitera_pn  ppn ON ppn.id=pp.panitera_id 
				WHERE pp.perkara_id=".$perkara_id." AND pp.aktif='Y';";
			return $this->db->query($sql);
		} catch (Exception $e) {
			return false;
		}
	}

	function getDataPemberitahuanPutusanMil($perkara_id, $pihak_ke, $pihak_id){
		try {
			$sql="SELECT per.nomor_perkara, 
						(CASE
						WHEN pmp.pihak=1 THEN CONCAT(IF(pj.pangkat IS NULL,'',pj.pangkat), ' ',pp1.nama, ' ', IF(pj.kesatuan IS NULL, '', pj.kesatuan))
						WHEN pmp.pihak=2 THEN CONCAT(IF(pp2.pangkat IS NULL,'',pp2.pangkat), ' ',pp2.nama, ' ', IF(pp2.kesatuan IS NULL, '', pp2.kesatuan))
						END) AS nama,
						pj.pangkat AS pangkat_1, pj.kesatuan AS kesatuan_1, pj.nip AS nrp_1, pp1.nama as nama_1,
						pp2.pangkat AS pangkat_2, pp2.kesatuan AS kesatuan_2,
						pp2.nrp AS nrp_2, pp2.nama as nama_2,
						pj.kejaksaan,
					pmp.* 
					FROM perkara_putusan_pemberitahuan_putusan pmp 
						LEFT JOIN perkara per ON per.perkara_id=pmp.perkara_id
						LEFT JOIN perkara_pihak1 pp1 ON pp1.perkara_id=pmp.perkara_id AND pp1.id=pmp.pihak_id
						LEFT JOIN pihak p ON p.id=pp1.pihak_id
						LEFT JOIN pihak_jaksa pj ON pj.pihak_id=p.id
						LEFT JOIN perkara_pihak2 pp2 ON pp2.perkara_id=pmp.perkara_id AND pp2.id=pmp.pihak_id
					WHERE pmp.perkara_id=".$perkara_id." 
						AND pmp.pihak=".$pihak_ke." 
						AND pmp.pihak_id=".$pihak_id.";";
			return $this->db->query($sql);
		} catch (Exception $e) {
			return false;
		}
	}

	function getDataPenetapanHakimMil($idperkara,$pembuat){
		try {
			if(!is_numeric($idperkara) OR !is_numeric($pembuat)){
				return 'dd';
			}
				$qry="SELECT 
								  a.tanggal_pendaftaran,a.nomor_perkara, a.nomor_urut_perkara, a.tanggal_surat AS tgl_surat_dakwaan, a.nomor_surat, 
								  b.matra, b.tanggal_skeppera, b.nomor_skeppera,b.pejabat_skeppera, b.tanggal_surat_dakwaan, b.nomor_surat_dakwaan,
								  b.berkas_dari,
								  c.tanggal_sidang, c.jam_sidang, c.agenda, c.ruangan,  
								  p2.*, e.penetapan_majelis_hakim, e.penetapan_panitera_pengganti,
								  f.nama_gelar AS nama_hakim, f.urutan AS urutan_hakim, f.jabatan_hakim_id, f.jabatan_hakim_nama,f.jabatan, f.pangkat,f.nip, pp.nama_gelar AS nama_pp, pp.tanggal_penetapan AS tgl_pen_pp,
								  pp.pangkat as pangkat_pp, pp.jabatan as jabatan_pp, pp.nip as nip_pp,
								  e.penetapan_hari_sidang AS penetapanharisidang, e.sidang_pertama AS sidang_pertama,
								  (SELECT VALUE FROM sys_config WHERE id = '62') AS namaPengadilan,
								  (SELECT VALUE FROM sys_config WHERE id = '63') AS AlamatPN,
								  (SELECT VALUE FROM sys_config WHERE id = '76') AS namaPT,
								  (SELECT VALUE FROM sys_config WHERE id = '64') AS namaketua,  
								  (SELECT VALUE FROM sys_config WHERE id = '64') AS KetuaPNNama,
								  (SELECT VALUE FROM sys_config WHERE id = '65') AS KetuaPNNip,
								  (SELECT VALUE FROM sys_config WHERE id = '84') AS PangkatKetua,
								  (SELECT VALUE FROM sys_config WHERE id = '66') AS WakilKetuaPNNama,
								  (SELECT VALUE FROM sys_config WHERE id = '68') AS PansekPNNama,
								  (SELECT VALUE FROM sys_config WHERE id = '69') AS PansekPNNip,
								  (SELECT VALUE FROM sys_config WHERE id = '75') AS zona_waktu,
								  (SELECT VALUE FROM sys_config WHERE id = '85') AS pangkat_wakil_ketua,
								  (SELECT VALUE FROM sys_config WHERE id = '86') AS pangkat_panitera,
								  (SELECT VALUE FROM sys_config WHERE id = '87') AS pangkat_wakil_panitera,
								  pn.* , info.* 
								FROM
								  perkara AS a 
								  LEFT JOIN perkara_matra AS b ON a.perkara_id = b.perkara_id 
								  LEFT JOIN 
								    (SELECT 
								      p2.pihak_id,'T' AS pihak, p.nama AS namat,p.tempat_lahir AS tlt,p.tanggal_lahir AS tgllhrt,
								      IF(p.jenis_kelamin = 'L','Laki-Laki','Perempuan') AS jkt,
								      p.alamat AS alamatt, p.status_kawin AS kawint, p.pekerjaan AS pekerjaant,
								      p.pendidikan AS pendidikant,p.warga_negara AS wnt, p2.urutan AS urutanT,p2.perkara_id ,p2.nrp,p2.jabatan AS jabatant, p2.kesatuan, p2.pangkat,
								      ag.nama AS agamat
								      FROM
								      perkara_pihak2 AS p2 
								      LEFT JOIN pihak AS p  ON p2.pihak_id = p.id 
								      LEFT JOIN agama AS ag ON p.agama_id=ag.id
								    WHERE p2.perkara_id = $idperkara) AS p2 ON p2.perkara_id = $idperkara 
								  LEFT JOIN perkara_penetapan AS e ON e.perkara_id = a.perkara_id 
								  LEFT JOIN perkara_jadwal_sidang AS c ON c.perkara_id=a.perkara_id AND c.urutan=1
								  LEFT JOIN (SELECT 
									      b.nama_gelar,
									      b.pangkat,
									      b.jabatan,
									      b.nip,
									      a.perkara_id,
									      a.tanggal_penetapan,
									      a.hakim_id,
									      a.urutan,
									      a.jabatan_hakim_id,
									      a.jabatan_hakim_nama 
									FROM
									      perkara_hakim_pn AS a,
									      hakim_pn AS b 
									WHERE a.hakim_id = b.id 
									AND a.perkara_id = $idperkara 
									AND a.aktif = 'y') AS f  ON f.perkara_id = a.`perkara_id` 
								  LEFT JOIN (SELECT a.perkara_id AS ppperkaraid, a.tanggal_penetapan, a.panitera_id, a.urutan, b.nama_gelar, b.jabatan, b.pangkat, b.nip 
									    FROM
									      perkara_panitera_pn AS a,
									      panitera_pn AS b 
									    WHERE a.panitera_id = b.id 
									      AND a.perkara_id = $idperkara) AS pp 
									    ON pp.ppperkaraid = a.perkara_id 
								  LEFT JOIN (SELECT id, nama AS pengadilan,jenis_pengadilan, kode_pn 
										FROM  pengadilan_negeri) AS pn   ON pn.id =  (SELECT  VALUE FROM sys_config WHERE id = '81') 
								  LEFT JOIN (SELECT  sys_config.id, NAME, VALUE AS ditetapkanoleh, (SELECT VALUE FROM sys_config WHERE id = '84') AS pangkat, 'Kepala' as jabatan_ttd
										FROM sys_config WHERE id = 64
										UNION
										SELECT  sys_config.id, NAME, VALUE AS ditetapkanoleh, (SELECT VALUE FROM sys_config WHERE id = '85') AS pangkat, 'Wakil Kepala'
										FROM sys_config WHERE id = 66
										UNION
										SELECT  sys_config.id, NAME, VALUE AS ditetapkanoleh, (SELECT VALUE FROM sys_config WHERE id = '86') AS pangkat, 'Panitera Kepala'
										FROM sys_config  WHERE id = 68
										UNION
										SELECT  sys_config.id, NAME, VALUE AS ditetapkanoleh, (SELECT VALUE FROM sys_config WHERE id = '87') AS pangkat, 'Wakil Panitera'
										FROM sys_config WHERE id = 70) AS info  ON info.id IN ('64','66','68','70') 
								WHERE a.perkara_id = $idperkara
								AND info.id = '".$pembuat."' ;";
							//echo $qry;
			return $this->db->query($qry);
		} catch (Exception $e) {
		}
	}

	function getDataDocumentTemplate($idalurperkara,$idatahapan,$idproses,$notemplate,$keyword){
		if (empty($keyword) or $keyword==''){
		$sql="SELECT a.nama AS jnsperkara,b.nomor AS notenmplate,b.kode AS kdtemplate,b.nama AS namatemplate 
				FROM alur_perkara a,template_dokumen b 
				WHERE a.id=b.alur_perkara_id 	AND a.id=$idalurperkara 
												AND b.tahapan_id =$idatahapan 
												AND b.proses_id=$idproses 
												AND b.nomor=$notemplate";
		}else{
		$sql="SELECT a.nama AS jnsperkara,b.nomor AS notenmplate,b.kode AS kdtemplate,b.nama AS namatemplate 
				FROM alur_perkara a,template_dokumen b 
				WHERE a.id=b.alur_perkara_id 	AND a.id=$idalurperkara 
												AND b.tahapan_id =$idatahapan 
												AND b.proses_id=$idproses 
												AND b.nama LIKE '%$keyword%'";
		}
		$query=$this->db->query($sql);
		return $query;
	}

	function getTglPts($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT tanggal_putusan, amar_putusan FROM perkara_putusan WHERE perkara_id ='.$idperkara);
		} catch(PDOException $e) {		
		}
	}

	function getDataPutusan($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return 'dd';
			}
			$qry="SELECT 
								  a.tanggal_pendaftaran,a.nomor_perkara, a.nomor_urut_perkara, a.tanggal_surat AS tgl_surat_dakwaan, a.nomor_surat, a.dakwaan,
								  b.matra, b.tanggal_skeppera, b.nomor_skeppera,b.tanggal_surat_dakwaan, b.nomor_surat_dakwaan, b.pejabat_skeppera,
								  b.penyidik_mil, b.no_bap_penyidik_mil , b.tgl_penyidik_mil, b.berkas_dari, b.tanggal_kejadian,b.tempat_kejadian,
								  c.tanggal_sidang, c.jam_sidang, c.agenda, c.ruangan,  
								  p2.*, e.penetapan_majelis_hakim, e.penetapan_panitera_pengganti,
								  f.nama_gelar AS nama_hakim, f.urutan AS urutan_hakim, f.jabatan_hakim_id, f.jabatan_hakim_nama, 
								  f.nip as nip_hakim, f.pangkat as pangkat_hakim, f.jabatan as jabatan_hakim,
								  pp.nama_gelar AS nama_pp, pp.tanggal_penetapan AS tgl_pen_pp,
								  pp.pangkat as pangkat_pp, pp.jabatan as jabatan_pp, pp.nip as nip_pp,
								  e.penetapan_hari_sidang AS penetapanharisidang, e.sidang_pertama AS sidang_pertama,
								  (SELECT VALUE FROM sys_config WHERE id = '64') AS namaketua,
								  (SELECT VALUE FROM sys_config WHERE id = 62) AS namaPengadilan,
								  (SELECT VALUE FROM sys_config WHERE id = 64) AS KetuaPNNama,
								  (SELECT VALUE FROM sys_config WHERE id = 63) AS AlamatPN,
								  (SELECT VALUE FROM sys_config WHERE id = 66) AS WakilKetuaPNNama,
								  (SELECT VALUE FROM sys_config WHERE id = 68) AS PansekPNNama,
								  pn.* , tuntut.isi_penuntutan , saksi.*, d.tanggal_putusan, d.amar_putusan, g.jenis_barang_bukti,
								  h.tanggal_putusan_sela, h.amar_sela,
								  pp1.nama AS namaOditur, pj.NIP AS nrpOditur, pj.pangkat AS pangkatOditur, d.tanggal_bht
								FROM
								  perkara AS a 
								  LEFT JOIN perkara_matra AS b ON a.perkara_id = b.perkara_id 
								  LEFT JOIN perkara_pihak1 AS pp1 ON pp1.perkara_id = a.perkara_id
								  LEFT JOIN pihak_jaksa AS pj ON pj.pihak_id = pp1.pihak_id
								  LEFT JOIN 
								    (SELECT 
								      'T' AS pihak, p.nama AS namat,p.tempat_lahir AS tlt,p.tanggal_lahir AS tgllhrt,
								      IF(p.jenis_kelamin = 'L','Laki-Laki','Perempuan') AS jkt,
								      p.alamat AS alamatt, p.agama_nama AS agamat,p.status_kawin AS kawint, p.pekerjaan AS pekerjaant,
								      p.pendidikan AS pendidikant,p.warga_negara AS wnt, p2.urutan AS urutanT,p2.perkara_id, p2.nrp as nrpt, p2.jabatan AS jabatant, p2.kesatuan, p2.pangkat as pangkatt
								      FROM
								      perkara_pihak2 AS p2 
								      LEFT JOIN pihak AS p  ON p2.pihak_id = p.id 
								    WHERE p2.perkara_id = $idperkara) AS p2 ON p2.perkara_id = $idperkara 
								  LEFT JOIN perkara_penetapan AS e ON e.perkara_id = a.perkara_id 
								  LEFT JOIN perkara_jadwal_sidang AS c ON c.perkara_id=a.perkara_id AND c.urutan=1
								  LEFT JOIN (SELECT 
									      b.nama_gelar,b.nip, b.pangkat, b.jabatan,
									      a.perkara_id,
									      a.tanggal_penetapan,
									      a.hakim_id,
									      a.urutan,
									      a.jabatan_hakim_id,
									      a.jabatan_hakim_nama 
									FROM
									      perkara_hakim_pn AS a,
									      hakim_pn AS b 
									WHERE a.hakim_id = b.id 
									AND a.perkara_id = $idperkara 
									AND a.aktif = 'y') AS f  ON f.perkara_id = a.`perkara_id` 
								  LEFT JOIN (SELECT a.perkara_id AS ppperkaraid, a.tanggal_penetapan, a.panitera_id, a.urutan, b.nama_gelar, b.jabatan, b.pangkat, b.nip 
									    FROM
									      perkara_panitera_pn AS a,
									      panitera_pn AS b 
									    WHERE a.panitera_id = b.id 
									      AND a.perkara_id = $idperkara) AS pp 
									    ON pp.ppperkaraid = a.perkara_id 
								  LEFT JOIN (SELECT id, nama AS pengadilan,jenis_pengadilan, kode_pn 
										FROM  pengadilan_negeri) AS pn   ON pn.id =  (SELECT  VALUE FROM sys_config WHERE id = '81') 
								  LEFT JOIN perkara_penuntutan AS tuntut ON a.perkara_id=tuntut.perkara_id
								  LEFT JOIN (SELECT a.perkara_id,a.urutan AS urutan_s, a.pangkat, a.nrp, a.jabatan, a.kesatuan, 
											b.nama as nama_s, b.tempat_lahir as tempat_lahir_s , b.tanggal_lahir as tanggal_lahir_s, IF(b.jenis_kelamin='L','Laki-laki','Perempuan') as jenis_kelamin_s, b.alamat as alamat_s, b.warga_negara_id, c.nama AS agama_s
									 	FROM perkara_pihak5 AS a
									    LEFT JOIN pihak AS b ON a.pihak_id=b.id
									    LEFT JOIN agama AS c ON c.id=b.agama_id WHERE a.perkara_id = $idperkara) AS saksi ON saksi.perkara_id=a.perkara_id
								  LEFT JOIN perkara_putusan as d on d.perkara_id=a.perkara_id
								  LEFT JOIN perkara_barang_bukti as g on g.perkara_id=a.perkara_id
								  LEFT join perkara_putusan_sela  as h on h.perkara_id=a.perkara_id
								WHERE a.perkara_id = $idperkara;";
								//echo $qry;
			return $this->db->query($qry);
		} catch (Exception $e) {
		}
	}

	function getDataTerdakwaMil($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
			}
				$sql="SELECT a.urutan AS urutan, 
							 a.pihak_id AS pihak_id, 
							 a.pangkat,a.jabatan, a.kesatuan,a.nrp, 
							 (SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(VALUE,' ',3),' ',-1) FROM sys_config WHERE id = 62) AS kode_pt, 
							 (SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(VALUE,' ',3),' ',-1) FROM sys_config WHERE id = 76) AS kota_pt, 
							 b.*
							 FROM perkara_pihak2 AS a
							 LEFT JOIN pihak AS b ON a.pihak_id=b.id
							 WHERE a.perkara_id=$idperkara
							 ORDER BY a.urutan ASC";
			//echo $sql;
			$query=$this->db->query($sql);
			return $query;
		} catch(Exception $e) {
			return false;
		}
	}

	function getDataPenahananTerdakwaMil($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT jp.nama AS oleh, sp.nama AS status_penahanan, jt.nama AS jenis_tahanan, 
					 	pt.jenis_penahanan_id,
  						pt.tanggal_surat,
  						pt.nomor_surat,
  						pt.mulai AS sejak, pt.sampai, pt.lama_penahanan, pt.pihak_id, pt.pejabat_penahan
					FROM (SELECT * 
							FROM penahanan_terdakwa 
							WHERE perkara_id=$idperkara  
							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 sejak, pt.jenis_penahanan_id";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getDataPenahananBef($idperkara,$tglMulaiBef){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM penahanan_terdakwa WHERE perkara_id = ".$idperkara." AND mulai = (SELECT MAX(mulai) FROM penahanan_terdakwa WHERE perkara_id = ".$idperkara." AND mulai < '".$tglMulaiBef."') LIMIT 1;";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}


	function getDataOditur($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT a.perkara_id, a.pihak_id,a.nama, b.nip AS nrp, b.pangkat, b.jabatan, b.kesatuan
					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 pihak_Oditur($idperkara,$idOditur){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT a.perkara_id, a.pihak_id,a.nama, b.nip AS nrp, b.pangkat, b.jabatan, b.kesatuan
					FROM 
					perkara_pihak1 AS a 
					LEFT JOIN pihak_jaksa AS b ON a.pihak_id=b.pihak_id
					WHERE a.perkara_id=".$idperkara." AND a.pihak_id=".$idOditur." ";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getDataPengacara($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT a.perkara_id, a.pihak_id, a.nama , b.pangkat,b.jabatan, b.kesatuan,b.nomor_induk AS nrp     
					FROM perkara_pengacara AS a
					LEFT JOIN pihak_pengacara AS b ON a.pengacara_id=b.pihak_id
					WHERE a.perkara_id=".$idperkara."; ";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getDataPemohonBanding($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT 
									  pbd.pihak_id,
									  p.tanggal_pendaftaran,
									  pp.nama,
									  pp.tempat_lahir,  
									  pp.tanggal_lahir,
									  (YEAR(p.tanggal_pendaftaran) - YEAR(pp.tanggal_lahir)) AS umur,
									  pp.warga_negara,
									  pp.alamat,
									  pp.agama_nama,
									  pp.pekerjaan,
									  pp.jenis_kelamin, 
                                      pp2.nrp, 
                                      pp2.kesatuan, 
                                      pp2.pangkat, 
                                      pp2.jabatan, 
                                      pp2.nama, 
                                      p.nomor_urut_register, p.nomor_perkara, ppn.panitera_nip, perkput.tanggal_putusan, 
			        (SELECT  
SUBSTRING_INDEX(SUBSTRING_INDEX(VALUE,' ',3),' ',-1) 
FROM sys_config WHERE id = 62) AS kode_pt, 
(SELECT  
SUBSTRING_INDEX(SUBSTRING_INDEX(VALUE,' ',3),' ',-1) 
FROM sys_config WHERE id = 76) AS kota_pt  
									FROM perkara AS p
									LEFT JOIN perkara_banding_detil AS pbd ON p.perkara_id = pbd.perkara_id
									LEFT JOIN pihak AS pp ON pp.id = pbd.pihak_id
									LEFT JOIN perkara_pihak2 AS pp2 ON pp2.perkara_id = p.perkara_id AND pp2.pihak_id = pp.id 
									LEFT JOIN perkara_panitera_pn  AS ppn ON ppn.perkara_id = pbd.perkara_id 
									LEFT JOIN perkara_putusan AS perkput ON perkput.perkara_id = pbd.perkara_id 
									WHERE pbd.perkara_id = $idperkara AND pbd.pihak_asal = 2 AND pbd.pihak_diwakili = 'Y'; ";

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

//function stiven
function getDataPemohonBandingTer($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT pbd.permohonan_banding, 
SUBSTRING_INDEX(pb.nomor_putusan_banding,'/',1) AS nomor_register,  
(CASE WHEN permat.matra = 1 THEN 'AD' 
WHEN permat.matra = 2 THEN 'AL' 
WHEN permat.matra = 3 THEN 'AU' 
END) AS matra,
SUBSTRING_INDEX(SUBSTRING_INDEX(pbd.permohonan_banding,'-',2),'-',-1) AS bulan, 
SUBSTRING_INDEX(SUBSTRING_INDEX(pbd.permohonan_banding,'-',1),'-',-1) AS tahun, 
(SELECT  
SUBSTRING_INDEX(SUBSTRING_INDEX(VALUE,' ',4),' ',-1) 
FROM sys_config WHERE id = 76) AS kota_pn, 
(SELECT  
SUBSTRING_INDEX(SUBSTRING_INDEX(VALUE,' ',3),' ',-1) 
FROM sys_config WHERE id = 62) AS kodee_pn, 
(SELECT  
SUBSTRING_INDEX(SUBSTRING_INDEX(VALUE,' ',3),' ',-1) 
FROM sys_config WHERE id = 62) AS kode_pt,

pb.nomor_perkara_banding AS nomor_perkara_banding, pb.nomor_putusan_banding AS nomor_putusan_banding, pb.putusan_banding AS tgl_putusan_banding, pb.amar_putusan_banding AS amar_putusan_banding, pbd.pemohon_nama, pp2.pangkat, pp2.jabatan, pp2.nrp, pp2.kesatuan, perk.nomor_urut_register, perk.nomor_perkara, (SELECT VALUE FROM sys_config WHERE id=69) AS panitera_nip, perkput.tanggal_putusan
					FROM perkara_banding_detil AS pbd 
					LEFT JOIN perkara_banding AS pb ON pb.perkara_id = pbd.perkara_id 
					LEFT JOIN perkara_pihak2 AS pp2 ON pp2.perkara_id = pbd.perkara_id 
					LEFT JOIN perkara AS perk ON perk.perkara_id = pbd.perkara_id 
					LEFT JOIN perkara_putusan AS perkput ON perkput.perkara_id = pbd.perkara_id 
					LEFT JOIN perkara_matra AS permat ON permat.perkara_id = pbd.perkara_id 
					WHERE pbd.perkara_id = $idperkara AND pbd.pihak_asal = 2; ";					

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

function infoPihak1($idperkara,$pemohon_id){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT 	p.nama, p.tempat_lahir , p.tanggal_lahir , p.jenis_kelamin , p.alamat, p.agama_nama , p.status_kawin , p.pekerjaan, 
						pp1.id, pp1.perkara_id, pp1.urutan, pp1.pihak_id, pp1.jenis_pihak_id,pp1.nama,
						pj.nip AS nrp, pj.pangkat, pj.jabatan, pj.kesatuan 
					FROM perkara_pihak1 AS pp1
					LEFT JOIN pihak_jaksa AS pj ON pp1.pihak_id = pj.pihak_id
					LEFT JOIN pihak AS p ON pp1.pihak_id=p.id
					WHERE pp1.perkara_id='".$idperkara."' AND pp1.pihak_id='".$pemohon_id."' ;";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

function infoPihak2($idperkara,$pemohon_id){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT 	pp2.perkara_id, pp2.pihak_id, p.nama, p.tempat_lahir , p.tanggal_lahir , p.jenis_kelamin , p.alamat, p.agama_nama , p.status_kawin , p.pekerjaan, 
						pp2.nama, pp2.alamat , pp2.keterangan , pp2.pangkat , pp2.nrp , pp2.jabatan , pp2.kesatuan  
					FROM perkara_pihak2 AS pp2
					LEFT JOIN pihak AS p ON pp2.pihak_id=p.id
					WHERE perkara_id='".$idperkara."' AND pp2.pihak_id='".$pemohon_id."' ;";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

function pk_infotputusan_bef($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT tahapan.* FROM 
					(SELECT '1' AS idPut,
					(SELECT value FROM sys_config WHERE id = 62) AS tahapan,
					(SELECT nomor_perkara FROM perkara WHERE perkara_id=$idperkara) AS nomorPerkara,
					tanggal_putusan AS tglPutus, 
					perkara_id 
					FROM perkara_putusan AS a 
				UNION ALL
                	SELECT '2' AS idPut,( SELECT value  FROM sys_config WHERE id=76 ),
                    nomor_perkara_banding,putusan_banding,perkara_id 
                    FROM perkara_banding AS a
        		UNION ALL SELECT '3' AS idput, 'Mahkamah Agung RI', nomor_perkara_kasasi,putusan_kasasi,perkara_id
		 			FROM perkara_kasasi WHERE perkara_id=$idperkara) AS tahapan 
                	WHERE tahapan.perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

function pk_infoMajelisHakim($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT k.perkara_id, f.nip, f.pangkat , f.jabatan , f.nama , f.nama_gelar  
					FROM 
					perkara_pk AS k 
					LEFT JOIN hakim_pn AS f ON FIND_IN_SET(f.id,k.hakim_id_pk)
					WHERE k.perkara_id='".$idperkara."'";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}



}


/* End of file m_template_mil.php */
/* Location: ./application/models/generate_template/m_template_mil.php */