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

class m_template_tun_akta_kasasi extends CI_Model {

function getDataTerdakwa($idperkara){
		try {
			return $this->db->query("SELECT pihak.perkara_id, pihak.pihak_id, pihak.nama,a.pemohon_nama,a.pihak_diwakili, a.perkara_id, a.pihak_id
                FROM perkara_pk_detil AS a 
                LEFT JOIN (SELECT perkara_id,pihak_id, nama FROM perkara_pihak1 WHERE perkara_id=$idperkara  
                            UNION 
                            SELECT perkara_id,pihak_id, nama FROM perkara_pihak2 WHERE perkara_id=$idperkara) AS pihak ON pihak.perkara_id = $idperkara
                WHERE a.perkara_id=$idperkara AND a.status_pihak_id =1 AND a.pihak_id=pihak.pihak_id");
		} catch (Exception $e) {
		}
	}

#terdakwa yang tidak diwakili
function getTerdakwaKasasi($idperkara){
		try {
			return $this->db->query("SELECT DISTINCT pemohon_id AS pemohonId, pemohon_nama AS pemohonNama, pihak_nama AS pihak2_nama FROM perkara_kasasi_detil WHERE perkara_id=$idperkara AND pihak_diwakili='T' and pihak_asal='2'");
		} catch (Exception $e) {
		}
	}
#terdakwa yang tidak diwakili
function getPengacaraTerdakwaKasasi($idperkara){
		try {
			return $this->db->query("SELECT DISTINCT pemohon_id AS pemohonId, pemohon_nama AS pemohonNama, pihak_nama AS pihak2_nama 
				FROM perkara_kasasi_detil WHERE perkara_id=$idperkara AND pihak_diwakili='Y' and pihak_asal='2'");
		} catch (Exception $e) {
		}
	}
##penasihat hukum yang mewakili terdakwa
function getDataPH($idperkara){
		try {
			return $this->db->query("SELECT DISTINCT pemohon_id AS pemohonId, pemohon_nama AS pemohonNama, pihak_nama AS pihak2_nama FROM perkara_kasasi_detil WHERE perkara_id=$idperkara AND pihak_diwakili='Y'");
		} catch (Exception $e) {
		}
	}
#getDataPejabat
function getDataPejabat(){
		try {
			return $this->db->query("SELECT b.id,a.namaPengadilan,b.id,IF (b.nama = 'PanSekNama','PanSek','WaPan' ) AS pejabat,b.pw
                FROM    (SELECT id,value AS namaPengadilan FROM sys_config WHERE id=62) AS a,
                        (SELECT id,value AS pw, name AS nama FROM sys_config WHERE id IN (68,70)) AS b");
		} catch (Exception $e) {
		}
	}

function getDataPutusan($idperkara){
		try {
			return $this->db->query("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) AS tahapan 
                WHERE tahapan.perkara_id=$idperkara limit 2");
		} catch (Exception $e) {
		}
	}

function pihakKasasi($idperkara){
		try {
			return $this->db->query("SELECT 	
				pihak_id AS idPihak, 
				IF(pihak_diwakili = 'Y',CONCAT(pihak_nama,' diwakili oleh ',pemohon_nama),pihak_nama) AS pihak
			FROM perkara_kasasi_detil WHERE perkara_id=$idperkara AND pihak_asal='2' #AND status_pihak_id IN (4,5)");
		} catch (Exception $e) {
		}
	}

function cetak_akta_tidak_kasasi($idperkara,$idpembuatakta,$idput){
		try {
			return $this->db->query("SELECT f.nomor_perkara,
						d.pw, IF (d.jabatan ='PanSekNama','Panitera','Wakil Panitera') AS jabatan,
						e.value AS nama_pengadilan, put.idPut, put.tahapan, put.nomorPerkara , put.putusan
				FROM perkara AS f
				LEFT JOIN perkara_putusan AS g ON f.`perkara_id`=g.`perkara_id`
				LEFT JOIN perkara_banding AS a ON f.perkara_id = a.perkara_id
				LEFT JOIN (SELECT id,name AS jabatan,value AS pw FROM sys_config) AS d ON d.id IN (68,70)  
				LEFT JOIN (SELECT id,value  FROM sys_config) AS e ON e.id=62
				LEFT JOIN 	(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 putusan, perkara_id 
							FROM perkara_putusan AS a 
							WHERE a.perkara_id=$idperkara
							UNION ALL
							SELECT '2' AS idPut,(SELECT value FROM sys_config WHERE id = 76) AS tahapan,
							nomor_perkara_banding AS nomorPerkara,
							putusan_banding AS putusan,	perkara_id 
							FROM perkara_banding AS a 
							WHERE a.perkara_id=$idperkara) AS put ON put.perkara_id=$idperkara
				WHERE f.perkara_id=$idperkara AND d.id=$idpembuatakta AND put.idput=$idput");
		} catch (Exception $e) {
		}
	}

function cetak_AktaPermohonanKasasiTerdakwa($idperkara,$idpembuatakta,$pihakid,$idput){
		try {
			$qry="SELECT a.pihak_id,z.nomor_perkara,a.permohonan_kasasi,
				c.nama AS namaPihak,
				c.alamat AS alamatPihak,
				c.tempat_lahir AS tempatLahirPihak,
				c.tanggal_lahir  AS tanggalLahirPihak,
				c.jenis_kelamin AS kelaminPihak,
				c.agama_nama  AS agamaPihak,
				c.pekerjaan  AS pekerjaanPihak,
				c.pendidikan  AS pendidikanPihak,
				c.warga_negara  AS negaraPihak,
				put.*, info.*
		FROM perkara_kasasi_detil AS a
		LEFT JOIN perkara AS z ON a.perkara_id=z.perkara_id
		LEFT JOIN perkara_pihak2  AS b ON a.pihak_id=b.pihak_id
		LEFT JOIN pihak AS c ON b.pihak_id=c.id
		LEFT JOIN (SELECT '1' AS idPut,(SELECT value FROM sys_config WHERE id = 62) AS tahapan,
				(SELECT nomor_perkara FROM perkara WHERE perkara_id=$idperkara ) AS nomorPerkaraTahapan,
				tanggal_putusan AS putusan, perkara_id 
				FROM perkara_putusan AS a 
				WHERE a.perkara_id=$idperkara
			UNION ALL
				SELECT '2' AS idPut,(SELECT value FROM sys_config WHERE id = 76) AS tahapan,
				nomor_perkara_banding AS nomorPerkaraTahapan,
				putusan_banding AS putusan,perkara_id 
				FROM perkara_banding AS a 
				WHERE a.perkara_id=$idperkara) AS put ON put.idPut IN ('1','2')
		LEFT JOIN (SELECT 	b.id,a.namaPengadilan,
			IF (b.nama = 'PanSekNama','Panitera Sekretaris','Wakil Panitera' ) AS pejabat,b.pw
			FROM    (SELECT id,value AS namaPengadilan FROM sys_config WHERE id=62) AS a,
		        (SELECT id,value AS pw, name AS nama FROM sys_config WHERE id IN (68,70)) AS b) AS info ON info.id IN (68,70)
		WHERE a.perkara_id=$idperkara AND a.pihak_id=$pihakid  AND info.id=$idpembuatakta AND put.idput=$idput AND a.pihak_diwakili ='T' AND a.status_pihak_id='1' ";
		// echo $qry;
		return $this->db->query($qry);
		} catch (Exception $e) {
		}
	}

	function cetak_AktaPermohonanKasasiTerdakwaTidakMenghadap($idperkara,$idpembuatakta,$idput){
		try {
			return $this->db->query("SELECT a.pihak_id,z.nomor_perkara,a.permohonan_kasasi,
				c.nama AS namaPihak,
				c.alamat AS alamatPihak,
				c.tempat_lahir AS tempatLahirPihak,
				c.tanggal_lahir  AS tanggalLahirPihak,
				c.jenis_kelamin AS kelaminPihak,
				c.agama_nama  AS agamaPihak,
				c.pekerjaan  AS pekerjaanPihak,
				c.pendidikan  AS pendidikanPihak,
				c.warga_negara  AS negaraPihak,
				b.umur,
				put.*, info.*
		FROM perkara_kasasi_detil AS a
		LEFT JOIN perkara AS z ON a.perkara_id=z.perkara_id
		LEFT JOIN perkara_pihak2  AS b ON a.pihak_id=b.pihak_id
		LEFT JOIN pihak AS c ON b.pihak_id=c.id
		LEFT JOIN (SELECT '1' AS idPut,(SELECT value FROM sys_config WHERE id = 62) AS tahapan,
				(SELECT nomor_perkara FROM perkara WHERE perkara_id=$idperkara ) AS nomorPerkaraTahapan,
				tanggal_putusan AS putusan, perkara_id 
				FROM perkara_putusan AS a 
				WHERE a.perkara_id=$idperkara
			UNION ALL
				SELECT '2' AS idPut,(SELECT value FROM sys_config WHERE id = 76) AS tahapan,
				nomor_perkara_banding AS nomorPerkaraTahapan,
				putusan_banding AS putusan,perkara_id 
				FROM perkara_banding AS a 
				WHERE a.perkara_id=$idperkara) AS put ON put.idPut IN ('1','2')
		LEFT JOIN (SELECT 	b.id,a.namaPengadilan,
			IF (b.nama = 'PanSekNama','Panitera Sekretaris','Wakil Panitera' ) AS pejabat,b.pw
			FROM    (SELECT id,value AS namaPengadilan FROM sys_config WHERE id=62) AS a,
		        (SELECT id,value AS pw, name AS nama FROM sys_config WHERE id IN (68,70)) AS b) AS info ON info.id IN (68,70)
		WHERE a.perkara_id=$idperkara AND info.id=$idpembuatakta AND put.idput=$idput AND a.pihak_diwakili ='T' AND a.pihak_asal !=1  ");
		} catch (Exception $e) {
		}
	}

		function cetak_AktaPermohonanKasasiPenuntutUmum($idperkara,$idpembuatakta,$idput){
		try {
			return $this->db->query("SELECT a.pihak_id,
				z.nomor_perkara,a.permohonan_kasasi,
				b.pihak_id,d.*,
				c.nama AS namaPihak,
				c.alamat AS alamatPihak,
				c.tempat_lahir AS tempatLahirPihak,
				c.tanggal_lahir  AS tanggalLahirPihak,
				c.jenis_kelamin AS kelaminPihak,
				c.agama_nama  AS agamaPihak,
				c.pekerjaan  AS pekerjaanPihak,
				c.pendidikan  AS pendidikanPihak,
				c.warga_negara  AS negaraPihak,
				b.umur,
				put.*, info.*
			FROM perkara_kasasi_detil AS a
			LEFT JOIN perkara AS z ON a.perkara_id=z.perkara_id
			LEFT JOIN perkara_pihak1  AS b ON a.pihak_id=b.pihak_id AND a.perkara_id=b.perkara_id
			LEFT JOIN pihak AS c ON b.pihak_id=c.id
			LEFT JOIN pihak_jaksa AS d ON d.pihak_id=c.id
			LEFT JOIN (SELECT '1' AS idPut,(SELECT value FROM sys_config WHERE id = 62) AS tahapan,
						(SELECT nomor_perkara FROM perkara WHERE perkara_id=$idperkara ) AS nomorPerkaraTahapan,
					tanggal_putusan AS putusan, perkara_id 
					FROM perkara_putusan AS a 
					WHERE a.perkara_id=$idperkara
				UNION ALL
					SELECT '2' AS idPut,(SELECT value FROM sys_config WHERE id = 76) AS tahapan,
					nomor_perkara_banding AS nomorPerkaraTahapan,
					putusan_banding AS putusan,perkara_id 
					FROM perkara_banding AS a 
					WHERE a.perkara_id=$idperkara) AS put ON put.idPut IN ('1','2')
			LEFT JOIN (SELECT 	b.id,a.namaPengadilan,
			IF (b.nama = 'PanSekNama','Panitera Sekretaris','Wakil Panitera' ) AS pejabat,b.pw
			FROM    (SELECT id,value AS namaPengadilan FROM sys_config WHERE id=62) AS a,
		        (SELECT id,value AS pw, name AS nama FROM sys_config WHERE id IN (68,70)) AS b) AS info ON info.id IN (68,70)
			WHERE a.perkara_id=$idperkara AND info.id='$idpembuatakta' AND put.idput='$idput' AND a.pihak_diwakili ='T' AND a.pihak_asal =1 ");
		} catch (Exception $e) {
		}
	}

	function infoDataTerdakwa($idperkara){
		try {
			return $this->db->query("SELECT pihak_id,perkara_id, 
				status_pihak_id, 
				permohonan_pk AS tglPemohonPK, 
				permohonan_pk,
				pihak_asal, 
				pihak_nama, 
				pihak_diwakili, 
				pemohon_id AS pengacara
			FROM 	perkara_pk_detil 
			WHERE 	permohonan_pk IS NOT NULL AND 
					pihak_asal=2 AND pihak_diwakili='T' AND perkara_id=$idperkara ");
		} catch (Exception $e) {
		}
	}

function getDataJaksaKasasi($idperkara){
		try {
			$sql="	SELECT 	q.pihak_id, 
							b.nama, 
							IF (b.pekerjaan IS NULL ,'#isi dulu di aplikasi#',b.pekerjaan ) AS pekerjaan,
							IF (r.NIP IS NULL ,'#isi dulu di aplikasi#',r.NIP ) AS NIP,
							IF (r.kejaksaan IS NULL ,'#isi dulu di aplikasi#',r.kejaksaan ) AS kejaksaan,
							IF (r.alamat_kantor IS NULL ,'#isi dulu di aplikasi#',r.alamat_kantor ) AS alamat_kantor
					FROM 	pihak b, 
							perkara_pk_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
					WHERE 	q.pihak_id=b.id AND
							p.pihak_asal='1' AND 
							p.pihak_id=q.pihak_id AND p.perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		} catch (Exception $e) {
		}
	}

function infoDataPHKasasi($idperkara){
		try {
			return $this->db->query("SELECT pihak_id,perkara_id, 
				status_pihak_id, 
				permohonan_pk AS tglPemohonPK, 
				pihak_asal, 
				pihak_nama, 
				pihak_diwakili, 
				pemohon_id AS pengacara_id,
				pemohon_nama AS namaPengacara
			FROM 	perkara_pk_detil 
			WHERE 	permohonan_pk IS NOT NULL AND pihak_asal='2' AND pihak_diwakili='Y' AND
					perkara_id=$idperkara");
		} catch (Exception $e) {
		}
	}
	
	function getInfo($idperkara){
		try {
				$qry="SELECT 
				CONCAT(pihak_nama,' / ',status_pihak_text) AS pihak_nama,
				status_pihak_text,
				IF(pihak_diwakili='Y', CONCAT(pemohon_nama), 'Tidak Diwakili') AS diwakili, pihak_id
				FROM perkara_pk_detil WHERE perkara_id=$idperkara AND permohonan_pk IS NOT NULL AND status_pihak_id=1 ";
		echo $qry;
		return $this->db->query($qry);
		} catch (Exception $e) {
		}
	}

//------------untuk looping penggugat di template---------//
	function getPihakPenggugat($idperkara){
		try{
			if(!is_numeric($idperkara)){
						return '';
					}
			return $this->db->query ("SELECT pihak1.pihak_id, pihak1.urutan, pihak1.nama, pihak1.alamat, pihak1.jenis_pihak_id AS info, p.pekerjaan, IF(p.warga_negara IS NULL,'Indonesia','') AS warga_negara
									FROM perkara_pihak1 AS pihak1
									LEFT JOIN pihak AS p ON pihak1.pihak_id=p.id
									WHERE perkara_id =".$idperkara." ORDER BY urutan ASC");
			}catch(PDOException $e) {
		}
	}

	function getPihakTergugat($idperkara){
		try{
			if(!is_numeric($idperkara)){
						return '';
					}
			return $this->db->query ("SELECT pihak2.pihak_id, pihak2.urutan, pihak2.nama, pihak2.alamat, pihak2.jenis_pihak_id AS info, p.pekerjaan, IF(p.warga_negara IS NULL,'Indonesia','') AS warga_negara
									FROM perkara_pihak2 AS pihak2
									LEFT JOIN pihak AS p ON pihak2.pihak_id=p.id
									WHERE perkara_id =".$idperkara." ORDER BY urutan ASC");
			}catch(PDOException $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 getDataPihakKasasi($idperkara,$idpemohon){
		try {
			if(!is_numeric($idperkara) AND !is_numeric($idpemohon)){
					return '';
			}
			return $this->db->query('SELECT pemohon_id AS IDPihak, pemohon_nama AS nama,permohonan_kasasi AS tglPemberitahuan,tanggal_cabut FROM perkara_kasasi_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 pihak_nama AS nama, pihak_id AS IDPihak, penerimaan_memori_banding AS tglmemori, penerimaan_kontra_banding AS tglkontramemori FROM perkara_kasasi_detil WHERE perkara_id ='.$idperkara.' AND pihak_id ='.$idpemohon);
		} 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_kasasi AS tglPemberitahuan, penerimaan_memori_kasasi AS tglMemori FROM perkara_kasasi_detil WHERE perkara_id='.$idperkara.' AND pemohon_id='.$idpemohon);
		} 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 getDataKuasaHukum($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_kasasi AS tglPemberitahuan, pemohon_tanggal_surat AS tglKuasa, pemohon_nomor_surat AS nomorKuasa
                    FROM perkara_kasasi_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) {		
		}
	}
}


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