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

class m_template_mil_pidana_akta_banding extends CI_Model {
	function getListPihak($idperkara,$pihakke){
		try {
			if(!is_numeric($idperkara) OR !is_numeric($pihakke)){
				return '';
			}
			return $this->db->query('SELECT pihak_id,nama FROM perkara_pihak'.$pihakke.' WHERE perkara_id='.$idperkara);
		} catch (Exception $e) {
			
		}
	}

	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 = '64') AS namaketua,
								  (SELECT VALUE FROM sys_config WHERE id = 62) AS namaPengadilan,
								  (SELECT VALUE FROM sys_config WHERE id = 76) AS namaPT,
								  (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 = 63) AS AlamatPN,
								  (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 = 84) AS PangkatKetua,
								  (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 
								      '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,
								      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  id, NAME, VALUE as ditetapkanoleh FROM sys_config) 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';
			}
			return $this->db->query("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.tanggal_surat_dakwaan, b.nomor_surat_dakwaan, b.pejabat_skeppera,
								  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, 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
								FROM
								  perkara AS a 
								  LEFT JOIN perkara_matra AS b ON a.perkara_id = b.perkara_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 
								      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,
									      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
								WHERE a.perkara_id = $idperkara;");
		} 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,
							 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";

			$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 
					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";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

		function getDataPemohonBanding($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT pbd.permohonan_banding, pbd.pemohon_nama, pp2.pangkat, pp2.jabatan, pp2.nrp, pp2.kesatuan, perk.nomor_urut_register, perk.nomor_perkara, ppn.panitera_nip, perkput.tanggal_putusan
					FROM perkara_banding_detil AS pbd 
					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_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 = $perkara_id AND pbd.status_pihak_id = 1; ";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getFilename($idalurperkara,$idtahapan){
		try{
			if(!is_numeric($idalurperkara) OR !is_numeric($idtahapan)){
				return '';
		}
		return $this->db->query('SELECT * FROM template_dokumen WHERE alur_perkara_id='.$idalurperkara.' AND tahapan_id='.$idtahapan.' AND nomor<=300;');
	} catch (Exception $e){
		}
	}

	function getDataPerkara($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT nomor_perkara,nomor_surat,tanggal_surat, tanggal_pendaftaran FROM perkara WHERE perkara_id ='.$idperkara);
		} catch(PDOException $e) {		
		}
	}
	function getTanggalPutusan($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT tanggal_putusan FROM perkara_putusan WHERE perkara_id ='.$idperkara);
		} catch(PDOException $e) {		
		}
	}

	function getDataPN(){
		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;");
			} catch(PDOException $e) {
			}
	}
	function getDataBandingPT($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT nomor_perkara_banding, putusan_banding AS tglPutusanBanding, amar_putusan_banding
				FROM perkara_banding WHERE perkara_id='.$idperkara);
		} catch(PDOException $e) {		
		}
	}
	function getDataPihakBanding($idperkara,$idpemohon){
		try {
			if(!is_numeric($idperkara) AND !is_numeric($idpemohon)){
					return '';
				}
			return $this->db->query('SELECT perkara_id as IDPerkara,pihak_nama AS nama,permohonan_banding,permohonan_banding AS tglPemberitahuan, penerimaan_memori_banding AS tglmemori, penerimaan_kontra_banding AS tglkontramemori, pihak_id AS IDPihak, pihak_asal AS pihakke,status_pihak_text, tanggal_cabut,pihak_diwakili FROM perkara_banding_detil WHERE perkara_id ='.$idperkara.' AND pemohon_id ='.$idpemohon);
		} catch(PDOException $e) {		
		}
	}
	function getDataPihak($idperkara,$idpemohon){
		try {
			if(!is_numeric($idperkara) AND !is_numeric($idpemohon)){
					return '';
				}
			return $this->db->query('SELECT perkara_id as IDPerkara,pihak_nama AS nama,permohonan_banding AS tglPemberitahuan, penerimaan_memori_banding AS tglmemori, penerimaan_kontra_banding AS tglkontramemori, pihak_id AS IDPihak, pihak_asal AS pihakke,status_pihak_text FROM perkara_banding_detil WHERE perkara_id ='.$idperkara.' AND pihak_id ='.$idpemohon);
		} catch(PDOException $e) {		
		}
	}
	function getDataTerdakwa($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT pihak_nama AS nama,pihak_id AS IDPihak, pihak_asal AS pihakke, permohonan_banding AS tglPemberitahuan, penerimaan_memori_banding AS tglMemori 
				FROM perkara_banding_detil WHERE perkara_id ='.$idperkara.' AND pihak_asal = 2');
		} catch(PDOException $e) {		
		}
	}
	function infoDataTerdakwa($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT pihak_nama AS nama,pihak_id AS IDPihak, pihak_asal AS pihakke, permohonan_banding AS tglPemberitahuan, penerimaan_memori_banding AS tglMemori 
				FROM perkara_banding_detil WHERE perkara_id ='.$idperkara.' AND pihak_asal = 2 AND pihak_id='.$idpihak );
		} catch(PDOException $e) {		
		}
	}
	function getAllTerdakwa($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT pihak_id AS IDPihak, pihak_nama AS nama 
				FROM perkara_banding_detil WHERE perkara_id ='.$idperkara.' AND pihak_asal=2');
		} catch(PDOException $e) {		
		}
	}
	function getTerdakwaKontraMemori($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT pihak_nama AS nama,pihak_id AS IDPihak, 
				pihak_asal AS pihakke, 
				permohonan_banding AS tglPemberitahuan, 
				penerimaan_memori_banding AS tglMemori, penyerahan_memori_banding AS tglPenyerahanMemori,
				penerimaan_kontra_banding AS tglKontraMemori 
				FROM perkara_banding_detil 
				WHERE perkara_id ='.$idperkara.' AND pihak_asal !=1 AND status_pihak_id IN (4,5)');
		} catch(PDOException $e) {		
		}
	}
	function relaasTerdakwaPembanding($idpihak){
		try {
			if(!is_numeric($idpihak)){
					return '';
				}
			return $this->db->query('SELECT pihak_id, pihak_nama, status_pihak_text, penerimaan_memori_banding, penyerahan_kontra_banding, pemberitahuan_inzage, pemberitahuan_putusan_banding
				FROM perkara_banding_detil 
				WHERE pihak_id ='.$idpihak.' AND pihak_asal !=1 AND status_pihak_id=1');
		} catch(PDOException $e) {		
		}
	}
	function getDataTerdakwaBanding($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara) OR !is_numeric($idpihak)){
					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
									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 
									WHERE pbd.perkara_id ='.$idperkara.' AND pbd.pihak_id ='.$idpihak);
			return $this->db->query($sql);
		} catch(PDOException $e) {		
		}
	}

    //Untuk pihak 1
    function getDataTerdakwaBanding2($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara) OR !is_numeric($idpihak)){
					return '';
				}				
			return $this->db->query('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, 
                                      pj.NIP, 
                                      pj.kesatuan, 
                                      pj.pangkat, 
                                      pj.jabatan, 
                                      pp1.nama
									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_pihak1 AS pp1 ON pp1.perkara_id = p.perkara_id AND pp1.pihak_id = pp.id 
									LEFT JOIN pihak_jaksa AS pj ON pj.pihak_id = pp1.pihak_id 
									WHERE pbd.perkara_id ='.$idperkara.' AND pbd.pihak_id ='.$idpihak);
		} catch(PDOException $e) {		
		}
	}

	function getTanggalPemberitahuan($idperkara,$idstatus){
		try {
			if(!is_numeric($idperkara) OR !is_numeric($idstatus)){
					return '';
				}
			return $this->db->query('SELECT * FROM perkara_banding_detil WHERE perkara_id ='.$idperkara.' AND status_pihak_id ='.$idstatus);
		} catch(PDOException $e) {		
		}
	}
	function getDataPembanding($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT  pemohon_nama AS nama, pemohon_id AS IDPihak, pihak_asal AS pihakke, permohonan_banding AS tglPemberitahuan, pemohon_tanggal_surat AS tglKuasa, pemohon_nomor_surat AS nomorKuasa, penerimaan_memori_banding AS tglMemori 
                    FROM perkara_banding_detil 
                    WHERE pemohon_id NOT IN (SELECT pihak_id FROM perkara_pihak2 WHERE perkara_id ='.$idperkara.')
                    AND perkara_id ='.$idperkara.'
                    AND pihak_asal != 1
                    AND status_pihak_id =1');
		} catch(PDOException $e) {		
		}
	}
	function getPHKontraMemoriBanding($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT  pemohon_nama AS nama, pemohon_id AS IDPihak, 
					pihak_asal AS pihakke, permohonan_banding AS tglPemberitahuan, 
					pemohon_tanggal_surat AS tglKuasa, pemohon_nomor_surat AS nomorKuasa, penerimaan_kontra_banding AS tglKontraMemori 
                    FROM perkara_banding_detil 
                    WHERE pemohon_id NOT IN (SELECT pihak_id FROM perkara_pihak2 WHERE perkara_id ='.$idperkara.')
                    AND perkara_id ='.$idperkara.'
                    AND pihak_asal != 1
                    AND status_pihak_id IN (4,5)');
		} catch(PDOException $e) {		
		}
	}
	function getDataBandingParties($idperkara,$idpemohon){
		try {
			if(!is_numeric($idperkara)OR !is_numeric($idpemohon)){
					return '';
				}
			$sql="SELECT 	a.pemohon_nama as pemohonbanding,
						a.penerimaan_memori_banding as tglPenerimaan,
						a.pemohon_tanggal_surat as tglKuasa,
						a.pemohon_nomor_surat as nomorKuasa,
						b.nama, 	 
						c.jenis_kelamin, 
						c.tempat_lahir,
						c.tanggal_lahir,
						c.pekerjaan,
						c.warga_negara,
						b.alamat,
						c.agama_nama,
						c.jenis_kelamin
				FROM perkara_banding_detil a, perkara_pihak2 b, pihak c
				WHERE a.pihak_id = b.pihak_id
				AND b.pihak_id=c.id				
				AND a.perkara_id ='$idperkara'
				AND a.pemohon_id ='$idpemohon'
				AND a.pihak_asal !=1";
				$query=$this->db->query($sql);
				return $query;
		} catch(PDOException $e) {		
		}
	}
	function getDataBandingParties2($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			$sql="SELECT a.pemohon_nama as pemohonbanding, a.permohonan_banding, b.nama, c.jenis_kelamin, 
						c.tempat_lahir,
						c.tanggal_lahir,
						c.pekerjaan,
						c.warga_negara,
						b.alamat,
						c.agama_nama
				FROM perkara_banding_detil a, perkara_pihak2 b, pihak c
				WHERE a.pihak_id = b.pihak_id
				AND b.pihak_id=c.id				
				AND a.perkara_id ='$idperkara'				
				AND a.pihak_asal !=1";
			$query=$this->db->query($sql);
			return $query;
		} catch(PDOException $e) {		
		}
	}
	function getDataJaksa($idperkara,$idpemohon){
		try {
			if(!is_numeric($idperkara) AND !is_numeric($idpemohon)){
					return '';
				}
			return $this->db->query('SELECT pihak_nama AS nama,pihak_id AS IDPihak, pihak_asal AS pihakke, permohonan_banding AS tglPemberitahuan, penerimaan_memori_banding AS tglMemori FROM perkara_banding_detil WHERE perkara_id='.$idperkara.' AND pemohon_id='.$idpemohon);
		} catch(PDOException $e) {		
		}
	}
	function getInfoPihak($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara) AND !is_numeric($idpihak)){
					return '';
				}
			return $this->db->query('SELECT pihak_nama AS nama, pihak_id AS IDPihak FROM perkara_banding_detil WHERE perkara_id='.$idperkara.' AND pihak_id='.$idpihak);
		} catch(PDOException $e) {		
		}
	}
	function getAllJaksa($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT pihak_id AS IDPihak, pihak_nama AS nama FROM perkara_banding_detil WHERE  perkara_id ='.$idperkara.' AND pihak_asal=1');
		} catch(PDOException $e) {		
		}
	}
	function getJaksaRelaasBanding($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT pihak_id AS IDPihak, pihak_nama AS nama, pemberitahuan_permohonan_banding, penyerahan_memori_banding, penerimaan_kontra_banding, pemberitahuan_inzage, 
				pemberitahuan_putusan_banding
				FROM perkara_banding_detil 
				WHERE  perkara_id ='.$idperkara.'
				AND status_pihak_id IN (4,5)');
		} catch(PDOException $e) {		
		}
	}
	function getJaksaBanding($idperkara,$idpihak){
		try {
			if(!is_numeric($idperkara) OR !is_numeric($idpihak)){				
					return '';
				}
			return $this->db->query('SELECT pihak_id, pihak_nama, status_pihak_text, penerimaan_memori_banding, penyerahan_kontra_banding, pemberitahuan_inzage, pemberitahuan_putusan_banding
				FROM perkara_banding_detil 
				WHERE perkara_id='.$idperkara.' AND pihak_id ='.$idpihak.' AND pihak_asal =1 AND  status_pihak_id=1');
		} catch(PDOException $e) {		
		}
	}
	function getJaksaKontraBanding($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT pihak_nama AS nama,pihak_id AS IDPihak, pihak_asal AS pihakke, 
				permohonan_banding AS tglPemberitahuan, penerimaan_kontra_banding AS tglKontraMemori 
				FROM perkara_banding_detil 
				WHERE perkara_id ='.$idperkara.' AND pihak_asal =1 AND status_pihak_id IN (4,5)');
		} catch(PDOException $e) {		
		}
	}
	function cetakDataJaksa($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT a.permohonan_banding,a.pemohon_nama,b.NIP
					FROM perkara_banding_detil a, pihak_jaksa b
                    WHERE a.pihak_id = b.pihak_id
                    AND perkara_id ='.$idperkara.'
                    AND pihak_asal=1');
		} catch(PDOException $e) {		
		}
	}
	function cetakJaksaKontra($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			return $this->db->query('SELECT a.pihak_nama,b.NIP
					FROM perkara_banding_detil a, pihak_jaksa b
                    WHERE a.pihak_id = b.pihak_id
                    AND perkara_id ='.$idperkara.'
                    AND status_pihak_id IN (4,5)');
		} catch(PDOException $e) {		
		}
	}
	function cetakJaksaParties($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
				}
			$sql="SELECT a.permohonan_banding as tglbandingPu, a.pihak_nama as nama, b.NIP, a.pihak_asal_text as pekerjaan
					FROM perkara_banding_detil a, pihak_jaksa b
                    WHERE a.pihak_id = b.pihak_id AND pihak_asal = 1
                    AND perkara_id ='$idperkara'";
            $query=$this->db->query($sql);
            return $query;
		} catch(PDOException $e) {		
		}
	}
	function pembuatAkta(){
		try {			
			return $this->db->query("SELECT id,CONCAT('PanSek-> [ ',value,' ]') AS namaPanitera FROM sys_config WHERE id = '68' UNION
				SELECT id,CONCAT('WaPan-> [ ',value,' ]') AS namaPanitera FROM sys_config WHERE id = '70' ");
		} catch(PDOException $e) {				
		}
	}
	function getTanggalCabutBanding($idperkara){
		try{
			if(!is_numeric($idperkara)){
					return '';
		}
		return $this->db->query('SELECT * FROM perkara_banding WHERE perkara_id ='.$idperkara);
		} catch(PDOException $e) {		
		}
	}
	function getAllJurusita(){
		try{			
		return $this->db->query("SELECT id, nama_gelar FROM jurusita WHERE aktif!='T' GROUP BY nama_gelar ASC");
		} catch(PDOException $e) {		
		}
	}
	function cetakJurusita($idjurusita){
		try{
			if(!is_numeric($idjurusita)){
					return '';
		}			
		return $this->db->query("SELECT id, nama_gelar FROM jurusita WHERE id=".$idjurusita);
		} catch(PDOException $e) {		
		}
	}	
	function getTanggalPemberitahuanBanding($idperkara,$idpihak){
		try{
			if(!is_numeric($idperkara) OR !is_numeric($idpihak)){
					return '';
		}
		return $this->db->query('SELECT pihak_id, permohonan_banding, status_pihak_text, pemberitahuan_permohonan_banding AS pemberitahuan, penerimaan_memori_banding, penyerahan_memori_banding, 
			penerimaan_kontra_banding, penyerahan_kontra_banding, pemberitahuan_inzage, pelaksanaan_inzage, pemberitahuan_putusan_banding 
			FROM perkara_banding_detil WHERE   perkara_id='.$idperkara.' AND pihak_id='.$idpihak);
		} catch(PDOException $e) {		
		}
	}
	function cetakPemberitahuanBandingPU($idperkara){
		try{
			if(!is_numeric($idperkara)){
					return '';
		}
		return $this->db->query('SELECT pemberitahuan_permohonan_banding AS pemberitahuan FROM perkara_banding_detil WHERE  pihak_asal=1 AND status_pihak_id!=1 AND perkara_id='.$idperkara);
		} catch(PDOException $e) {		
		}
	}	
	function cetakPemberitahuanPU($idperkara){
		try{
			if(!is_numeric($idperkara)){
					return '';
		}
		return $this->db->query('SELECT a.nama, b.NIP FROM perkara_pihak1 a, pihak_jaksa b WHERE a.pihak_id = b.pihak_id AND perkara_id='.$idperkara);
		} catch(PDOException $e) {		
		}
	}
	function getPihakPembanding($idperkara){
		try{
			if(!is_numeric($idperkara)){
					return '';
		}
		return $this->db->query('SELECT pihak_id AS IDPihak, pihak_nama AS nama, penyerahan_kontra_banding FROM perkara_banding_detil WHERE perkara_id='.$idperkara.' AND status_pihak_id IN (1,2)');
		} catch(PDOException $e) {		
		}
	}
	function getPihakTerbanding($idperkara){
		try{
			if(!is_numeric($idperkara)){
					return '';
		}
		return $this->db->query('SELECT pihak_id AS IDPihak, pihak_nama AS nama, penerimaan_kontra_banding FROM perkara_banding_detil WHERE perkara_id='.$idperkara.' AND status_pihak_id IN (3,4,5)');
		} catch(PDOException $e) {		
		}
	}
	function getAllPihakBanding($idperkara,$idpihak){
		try{
			if(!is_numeric($idperkara) OR !is_numeric($idpihak)){
					return '';
		}
		return $this->db->query('SELECT a.pihak_id, p.nama, p.alamat, p.pekerjaan 
			FROM perkara_banding_detil a LEFT JOIN pihak p 
				ON p.id = a.pihak_id
			WHERE a.perkara_id ='.$idperkara.' AND a.pihak_id ='.$idpihak);
		} catch(PDOException $e) {		
		}
	}

	function infoTglBanding($idperkara){
		try{
			if(!is_numeric($idperkara)){
					return '';
		}
		return $this->db->query('SELECT permohonan_banding FROM perkara_banding_detil WHERE perkara_id='.$idperkara.' AND pihak_asal=2');
		} catch(PDOException $e) {		
		}
	}


// Untuk Mendapatkan Oditur 
function getDataOditur($idperkara){
		try {			
			$qry="	SELECT 	q.pihak_id, p.pemohon_id, 
							b.nama, 
							b.pekerjaan,
							r.NIP,
							r.kejaksaan,
							r.alamat_kantor,
							r.pangkat AS pangkat_oditur, 
							z.nomor_perkara, p.permohonan_banding, p.pihak_diwakili, 
							(SELECT VALUE AS namaPengadilan FROM sys_config WHERE id=62) as namaPengadilan, 
							r.kesatuan AS kesatuan_oditur
					FROM pihak b, perkara_banding_detil AS p 
					LEFT JOIN perkara_pihak1 AS q ON p.perkara_id=q.perkara_id 
					LEFT JOIN pihak_jaksa AS r ON q.pihak_id=r.pihak_id 
					LEFT JOIN perkara AS z ON p.perkara_id=z.perkara_id 
					WHERE 	 
							p.pihak_asal='1' AND 
							p.pihak_id=q.pihak_id AND 
							p.perkara_id=$idperkara AND
							q.pihak_id=b.id";
				$query=$this->db->query($qry);

				return $query;
		} catch (Exception $e) {
		}
	}



//ini buat pihak 2 - satu query untuk semua
	function getoditur($idperkara){
      try{
			if(!is_numeric($idperkara)){
					return '';
		}

       $sql="SELECT pp1.nama, pj.jabatan, pj.NIP, pj.pangkat, pj.kesatuan, pp2.nama AS namaterdak, pp2.nrp, pp2.jabatan AS jabatanter, pp2.kesatuan AS kesatuanter,  
(CONCAT_WS(' ', NULLIF(pp2.nama, ' '), ' ', NULLIF(pp2.pangkat, ' '), ' ')) AS namapangkatkorp,  
pj.pangkat AS pangkatkorp, 
(SELECT VALUE FROM sys_config WHERE id=68) AS panitera_nama, 
(SELECT VALUE FROM sys_config WHERE id=86) AS pangkatpan,
(SELECT VALUE FROM sys_config WHERE id=69) AS nippan, 
(CONCAT_WS(' ', NULLIF((SELECT VALUE FROM sys_config WHERE id=68), ' '), NULLIF((SELECT VALUE FROM sys_config WHERE id=86), ' '), ' ')) AS namapangkatkorppan,  
pb.putusan_banding, pb.nomor_putusan_banding, pb.pemberitahuan_putusan_banding, pb.penerimaan_memori_banding, pb.pemberitahuan_permohonan_banding, pb.nomor_perkara_pn AS nomor_putusan_pertama,  
pp2.pangkat AS pangkatkorpter, pp2.pangkat AS pangkatter, perkput.tanggal_putusan AS tanggal_putusan_pertama, pb.penyerahan_memori_banding, pbd.pemberitahuan_putusan_banding AS pemberitahuan_putusan_banding_ter, 
pbd.penerimaan_kontra_banding AS penerimaan_kontra_banding_ter, pbd.permohonan_banding AS permohonan_banding_ter, pbd.penerimaan_kontra_banding AS penerimaan_kontra_banding_ter, pbd.penyerahan_kontra_banding AS penyerahan_kontra_banding_ter, pbd.pemberitahuan_inzage AS pemberitahuan_inzage_ter,
phk.nama AS nama_kuasa_hukum_ter, 
pep2.pangkat AS pangkatkorp_kuasa_hukum, 
pep2.nrp AS nrp_kuasa_hukum, pep2.jabatan AS jabatan_kuasa_hukum, pep2.kesatuan AS kesatuan_kuasa_hukum, 
SUBSTRING_INDEX(pb.nomor_perkara_pn,'/',1) AS nomor_register,  
SUBSTRING_INDEX(SUBSTRING_INDEX(pb.nomor_perkara_pn,'/',3),'/',-1) AS matra, 
SUBSTRING_INDEX(SUBSTRING_INDEX(pb.nomor_perkara_pn,'/',4),'/',-1) AS bulan, 
SUBSTRING_INDEX(SUBSTRING_INDEX(pb.nomor_perkara_pn,'/',5),'/',-1) AS tahun, 
(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, 
(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, 
pbd.permohonan_banding AS permohonan_banding_ter, pbd.tanggal_cabut AS tanggal_cabut_ter, 
v_perkara.tanggal_bht AS tanggal_bht_ter

FROM perkara_pihak1 AS pp1 
LEFT JOIN pihak_jaksa AS pj ON pj.pihak_id = pp1.pihak_id 
LEFT JOIN perkara_pihak2 AS pp2 ON pp2.perkara_id = pp1.perkara_id 
LEFT JOIN perkara_panitera_pn AS ppn ON ppn.perkara_id = pp1.perkara_id 
LEFT JOIN panitera_pn AS pn ON pn.id = ppn.panitera_id 
LEFT JOIN perkara_banding AS pb ON pb.perkara_id = pp2.perkara_id 
LEFT JOIN perkara_putusan AS perkput ON perkput.perkara_id = pp2.perkara_id  
LEFT JOIN perkara_banding_detil AS pbd ON pbd.perkara_id = pp2.perkara_id 
LEFT JOIN pihak AS phk ON phk.id = pbd.pemohon_id
LEFT JOIN perkara_pihak2 AS pep2 ON pep2.pihak_id = pbd.pemohon_id  
LEFT JOIN v_perkara AS v_perkara ON v_perkara.perkara_id = pb.perkara_id
WHERE pp1.perkara_id=$idperkara AND pbd.pihak_asal = 2";


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

	}
//ini buat pihak 1 - satu query untuk semua 
	function getoditur2($idperkara){
      try{
			if(!is_numeric($idperkara)){
					return '';
		}

$sql="SELECT pp1.nama, pj.jabatan, pj.NIP, pj.pangkat, pj.kesatuan AS kode_otmil, pj.kejaksaan AS kode_oditur, pj.pangkat AS pangkatkorpodt,
pp2.nama AS namaterdak, pp2.nrp, pp2.jabatan AS jabatanter, pp2.kesatuan AS kesatuanter,  
(CONCAT_WS(' ', NULLIF(pp2.nama, ' '), ' ', NULLIF(pp2.pangkat, ' '), ' ')) AS namapangkatkorp,  
pj.pangkat AS pangkatkorp, 
(SELECT VALUE FROM sys_config WHERE id=68) AS panitera_nama, 
(SELECT VALUE FROM sys_config WHERE id=86) AS pangkatpan, 
(SELECT VALUE FROM sys_config WHERE id=69) AS nippan, 
(CONCAT_WS(' ', NULLIF((SELECT VALUE FROM sys_config WHERE id=68), ' '), NULLIF((SELECT VALUE FROM sys_config WHERE id=86), ' '), ' ')) AS namapangkatkorppan,  
pb.putusan_banding, pb.nomor_putusan_banding, pb.pemberitahuan_putusan_banding, pb.penerimaan_memori_banding, pb.pemberitahuan_permohonan_banding, pb.nomor_perkara_pn AS nomor_putusan_pertama,  
pp2.pangkat AS pangkatkorpter, pp2.pangkat AS pangkatter, perkput.tanggal_putusan AS tanggal_putusan_pertama, pbd.pemberitahuan_permohonan_banding, pbd.pemberitahuan_putusan_banding AS pemberitahuan_putusan_banding_odt, 
pbd.penerimaan_kontra_banding AS penerimaan_kontra_banding_odt, pbd.permohonan_banding AS permohonan_banding_odt, pbd.penerimaan_kontra_banding AS penerimaan_kontra_banding_odt, pbd.penyerahan_kontra_banding AS penyerahan_kontra_banding_odt, pbd.pemberitahuan_inzage AS pemberitahuan_inzage_odt, pbd.penyerahan_memori_banding AS penyerahan_memori_banding_odt, 
pbd.permohonan_banding AS permohonan_banding_odt, pbd.tanggal_cabut AS tanggal_cabut_odt 
FROM perkara_pihak1 AS pp1 
LEFT JOIN pihak_jaksa AS pj ON pj.pihak_id = pp1.pihak_id 
LEFT JOIN perkara_pihak2 AS pp2 ON pp2.perkara_id = pp1.perkara_id 
LEFT JOIN perkara_panitera_pn AS ppn ON ppn.perkara_id = pp1.perkara_id 
LEFT JOIN panitera_pn AS pn ON pn.id = ppn.panitera_id 
LEFT JOIN perkara_banding AS pb ON pb.perkara_id = pp2.perkara_id 
LEFT JOIN perkara_putusan AS perkput ON perkput.perkara_id = pp2.perkara_id 
LEFT JOIN perkara_banding_detil AS pbd ON pbd.perkara_id = pp1.perkara_id 
WHERE pp1.perkara_id=$idperkara and pbd.pihak_asal = 1";

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

	}

function biaya_tk_banding($idperkara){
      try{
			if(!is_numeric($idperkara)){
					return '';
		}
$sql="SELECT durasi_nominal FROM perkara_putusan_terdakwa WHERE perkara_id = $idperkara AND status_putusan_id = 60";

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

	}

}
	

?>