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

class m_template_berita_acara extends CI_Model {

	function data_perkara($perkara_id){
		try{
				$this->db->where('perkara_id', $perkara_id);
				$query = $this->db->get('v_perkara');
				return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}

	function list_kurator(){
		try{
			$query = $this->db->get('kurator');
			return $query;
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}

	function get_kurator($kurator_id){
		try{
			$this->db->select('pihak.alamat,pihak.nama');
			$this->db->from('kurator');
			$this->db->join('pihak', 'pihak.id=kurator.pihak_id');
			$this->db->where('kurator.id', $kurator_id);
			$query = $this->db->get();
			return $query;
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}

	function list_template($alur_perkara_id){
		try{
			$this->db->select('kode,nama');
			$this->db->where('alur_perkara_id', $alur_perkara_id);
			$this->db->where('tahapan_id','14');
			$this->db->where('proses_id','200');
			$this->db->like('nama','Berita Acara','both');  
			$query = $this->db->get('template_dokumen');
			return $query;
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}


	function get_pihak($perkara_id,$alur_perkara_id){
		try{
			$this->db->select('pihak.nama,pihak.id,pihak.alamat,pihak.pekerjaan');
			if($alur_perkara_id==1){
				$this->db->order_by("urutan", "desc"); 
				$this->db->from('perkara_pihak1');
				$this->db->join('pihak', 'pihak.id=perkara_pihak1.pihak_id');
				$this->db->where('perkara_pihak1.perkara_id', $perkara_id);
			} else{
				$this->db->order_by("urutan", "desc"); 
				$this->db->from('perkara_pihak2');
				$this->db->join('pihak', 'pihak.id=perkara_pihak2.pihak_id');
				$this->db->where('perkara_pihak2.perkara_id', $perkara_id);
			}
			$query = $this->db->get();
			return $query;
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}

	function jadwal_sidang($perkara_id){
		try{
			$this->db->where('perkara_id', $perkara_id);
			$this->db->order_by("urutan", "asc"); 
			$query = $this->db->get('perkara_jadwal_sidang');
			return $query;
		} catch (Exception $e) {
           	log_message('error', $e);
		}
	}

	function get_detil_sidang($sidang_id){
		try{
			$this->db->where('id', $sidang_id); 
			$query = $this->db->get('perkara_jadwal_sidang');
			return $query;
		} catch (Exception $e) {
           	log_message('error', $e);
		}
	}

	function get_detil_pihak($pihak_id){
		try{
			$this->db->select('nama,id,alamat,pekerjaan');
			$this->db->from('pihak');
			$this->db->where('id', $pihak_id);
			$query = $this->db->get();
			return $query;
		} catch (Exception $e) {
           	log_message('error', $e);
		}
	}

	function get_template($kode){
		try{
			$this->db->select('nama');
			$this->db->where('kode', $kode);
			$query = $this->db->get('template_dokumen');
			return $query;
		} catch (Exception $e) {
           	log_message('error', $e);
		}
	}


	function panitera($idperkara){
		try{
			$sql = "SELECT
		        panitera_pn.nama_gelar as nama
		        FROM perkara_panitera_pn
		        LEFT JOIN panitera_pn ON panitera_pn.id=perkara_panitera_pn.panitera_id
		        WHERE perkara_panitera_pn.aktif='Y'
		        AND perkara_panitera_pn.perkara_id='$idperkara'
		        ORDER BY perkara_panitera_pn.urutan ASC";
            return $this->db->query($sql);
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}

	function hakim($idperkara){
		try{
			$sql = "SELECT
		        hakim_pn.nama_gelar as nama
		        FROM perkara_hakim_pn
		        LEFT JOIN hakim_pn ON hakim_pn.id=perkara_hakim_pn.hakim_id
		        WHERE perkara_hakim_pn.aktif='Y'
		        AND perkara_hakim_pn.perkara_id='$idperkara'
		        ORDER BY perkara_hakim_pn.urutan ASC";
            return $this->db->query($sql);
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}

	function getRefJenisTahanan(){
		try{
			return $this->db->query( "SELECT * FROM jenis_tahanan");
		} catch(Exception $e) { 
			log_message('error', $e);; 
		}
	}

	function getJenisPerkara($idperkara){
    	try {
    		if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT nama 
				FROM jenis_perkara 
				WHERE id IN (SELECT jenis_perkara_id 
							FROM perkara 
							WHERE perkara_id=$idperkara)";
			$query=$this->db->query($sql);
			return $query;
		} catch(Exception $e) { return false;	}
	}

	function getDataPenahananTerdakwa($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT jp.nama AS oleh, sp.nama AS status_penahanan, jt.nama AS jenis_tahanan, 
	            	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 dataPenahanan($idperkara,$idpihak){
    	try {
    		if(!is_numeric($idperkara) OR !is_numeric($idpihak)){
				return '';
			}
			$sql = "SELECT 
            	penahanan_terdakwa.perkara_id AS perkaraid,
            	penahanan_terdakwa.jenis_penahanan_id AS penahanan_id,
            	jenis_penahanan.nama AS jenis_penahanan,
            	jenis_tahanan.nama AS jenis_tahanan,
            	penahanan_terdakwa.tanggal_surat AS tgl_surat_penahanan,
            	penahanan_terdakwa.nomor_surat AS nomor_surat_penahanan,
            	penahanan_terdakwa.mulai AS penahanan_mulai,
            	penahanan_terdakwa.sampai AS penahanan_sampai,
            	penahanan_terdakwa.lama_penahanan AS penahanan_lama
            	FROM penahanan_terdakwa
            	LEFT JOIN jenis_penahanan ON jenis_penahanan.id=penahanan_terdakwa.jenis_penahanan_id
            	LEFT JOIN jenis_tahanan ON jenis_tahanan.id=penahanan_terdakwa.jenis_tahanan_id
            	WHERE penahanan_terdakwa.perkara_id=$idperkara AND penahanan_terdakwa.pihak_id=$pihakid";
		$query=$this->db->query($sql);
			return $query;
     	} catch (Exception $e) { return false; }
	}



	function cetak_ba_prapid($idperkara){
		try{
			$sql = "SELECT
				perkara.perkara_id AS perkaraId,
				perkara.nomor_perkara AS nomorPerkara,
				perkara.tanggal_pendaftaran AS tanggalPendaftaran,
				perkara.dakwaan,
				perkara.jenis_perkara_nama AS jenisPerkara,
				perkara_penetapan.penetapan_majelis_hakim AS tanggalPenetapanHakim,
				(CASE WHEN perkara.tanggal_surat IS NOT NULL THEN perkara.tanggal_surat ELSE '..........' END) AS tanggalSurat,
				(CASE WHEN perkara_putusan.amar_putusan IS NOT NULL THEN perkara_putusan.amar_putusan ELSE '..........' END) AS amarPutusan,
				(CASE WHEN perkara_putusan.tanggal_putusan IS NOT NULL THEN perkara_putusan.tanggal_putusan ELSE '..........' END) AS tanggalPutusan,
				(CASE WHEN (SELECT tanggal_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				IS NOT NULL THEN (SELECT tanggal_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				ELSE '..........' END) AS tanggalSidang1,
				(CASE WHEN (SELECT agenda FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				IS NOT NULL THEN (SELECT agenda FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				ELSE '..........' END) AS agendaSidang1,
				(CASE WHEN (SELECT jam_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				IS NOT NULL THEN (SELECT LEFT(jam_sidang,5) FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				ELSE '..........' END) AS jamSidang1,
				(CASE WHEN (SELECT tanggal_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				IS NOT NULL THEN (SELECT tanggal_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				ELSE '..........' END) AS tanggalSidang2,
				(CASE WHEN (SELECT agenda FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				IS NOT NULL THEN (SELECT agenda FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				ELSE '..........' END) AS agendaSidang2,
				(CASE WHEN (SELECT jam_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				IS NOT NULL THEN (SELECT LEFT(jam_sidang,5) FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				ELSE '..........' END) AS jamSidang2,
				(SELECT hakim_pn.nama_gelar FROM perkara_hakim_pn
					LEFT JOIN hakim_pn ON hakim_pn.id=perkara_hakim_pn.hakim_id
					WHERE perkara_hakim_pn.aktif='Y' AND perkara_hakim_pn.perkara_id=perkara.perkara_id AND perkara_hakim_pn.urutan='1') AS namaHakim1,
				(SELECT panitera_pn.nama_gelar FROM perkara_panitera_pn
					LEFT JOIN panitera_pn ON panitera_pn.id=perkara_panitera_pn.panitera_id
					WHERE perkara_panitera_pn.aktif='Y' AND perkara_panitera_pn.perkara_id=perkara.perkara_id AND perkara_panitera_pn.urutan='1') AS namaPanitera1,
				(CASE WHEN (SELECT COUNT(*) FROM perkara_hakim_pn WHERE perkara_id=perkara.perkara_id)>1 THEN 'Hakim Ketua' ELSE 'Hakim' END) AS jenisHakim1,
				(CASE WHEN (SELECT COUNT(*) FROM perkara_hakim_pn WHERE perkara_id=perkara.perkara_id)>1 THEN 'Majelis Hakim' ELSE 'Hakim' END) AS jenisHakim2,
				(SELECT value FROM sys_config WHERE id = '62') AS namaPN,
				(SELECT value FROM sys_config WHERE id = '63') AS alamatPN,
				(SELECT value FROM sys_config WHERE id = '75') AS zonaWaktu,
				(SELECT value FROM sys_config WHERE id = '68') AS PanSekNama,
				(SELECT value FROM sys_config WHERE id = '69') AS PanSekNIP,
				(SELECT value FROM sys_config WHERE id = '70') AS WaPanNama,
				(SELECT value FROM sys_config WHERE id = '71') AS WaPanNIP,
				d.*, 
				IF(d.pengacara_idP IS NOT NULL,CONCAT('Berkedudukan di  ',d.alamatP,'dalam hal ini memberikan kuasa kepada ',d.namaPengacaraP,' beralamat di ',d.alamatPengacaraP,' berdasarkan Surat Kuasa Khusus tanggal  ',IF(perkara.tanggal_surat IS NULL,'.....',perkara.tanggal_surat),' sebagai '),
				CONCAT('Bertempat tinggal di ',d.`alamatP`,' , Sebagai ')) AS statusP,
				j.*,
				IF(j.pengacara_idT IS NOT NULL,CONCAT('Berkedudukan di  ',j.alamatT,'dalam hal ini memberikan kuasa kepada ',j.namaPengacaraT,' beralamat di ',j.alamatPengacaraT,' berdasarkan Surat Kuasa Khusus tanggal  ',IF(perkara.tanggal_surat IS NULL,'.....',perkara.tanggal_surat),' sebagai '),
				CONCAT('Bertempat tinggal di ',j.`alamatT`,' , Sebagai ')) AS statusT
				FROM perkara
				LEFT JOIN perkara_putusan ON perkara_putusan.perkara_id=perkara.perkara_id
				LEFT JOIN perkara_penetapan ON perkara_penetapan.perkara_id=perkara.perkara_id
				LEFT JOIN (SELECT c.pengacara_id AS pengacara_idP, 
					a.perkara_id AS perkara_idP,
					b.id AS pihak_idP, 
					b.nama AS namaP, 
					b.alamat AS alamatP,
					c.nama AS namaPengacaraP,
					c.alamat AS alamatPengacaraP, 
					c.pihak_id AS pihak_idPC
					FROM 	perkara AS a
					LEFT JOIN perkara_pihak1 AS z ON a.perkara_id=z.perkara_id
					LEFT JOIN pihak AS b ON b.id=z.pihak_id
					LEFT JOIN perkara_pengacara AS c ON a.perkara_id=c.perkara_id AND FIND_IN_SET(z.pihak_id,c.pihak_id)) AS d ON d.perkara_idP=$idperkara            
				LEFT JOIN (SELECT c.pengacara_id AS pengacara_idT, a.perkara_id AS perkara_idT, 
					b.id AS pihak_idT,
					b.nama AS namaT,
					b.alamat AS alamatT,
					c.nama AS namaPengacaraT,
					c.alamat AS alamatPengacaraT, 
					c.pihak_id AS pihak_idTC
					FROM perkara AS a
					LEFT JOIN perkara_pihak2 AS h ON a.perkara_id=h.perkara_id 
					LEFT JOIN pihak AS b ON b.id=h.pihak_id
					LEFT JOIN perkara_pengacara AS c ON a.perkara_id=c.perkara_id AND  
					FIND_IN_SET(h.pihak_id,c.pihak_id)) AS j ON j.perkara_idT=$idperkara
				WHERE perkara.perkara_id=$idperkara";
            return $this->db->query($sql);
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}
	

	function cetak_ba_perdata($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return 'dd';
			}
			return $this->db->query("SELECT
				perkara.perkara_id AS perkaraId,
				perkara.nomor_perkara AS nomorPerkara,
				perkara.tanggal_pendaftaran AS tanggalPendaftaran,
				perkara.petitum,
				perkara.jenis_perkara_nama AS jenisPerkara,
				perkara_penetapan.penetapan_majelis_hakim AS tanggalPenetapanHakim,
				(CASE WHEN perkara.tanggal_surat IS NOT NULL THEN perkara.tanggal_surat ELSE '..........' END) AS tanggalSurat,
				(CASE WHEN perkara_putusan.amar_putusan IS NOT NULL THEN perkara_putusan.amar_putusan ELSE '..........' END) AS amarPutusan,
				(CASE WHEN perkara_putusan.tanggal_putusan IS NOT NULL THEN perkara_putusan.tanggal_putusan ELSE '..........' END) AS tanggalPutusan,
				(CASE WHEN (SELECT nama_gelar FROM mediator WHERE id=perkara_mediasi.mediator_id) IS NOT NULL THEN 
				(SELECT nama_gelar FROM mediator WHERE id=perkara_mediasi.mediator_id) ELSE '..........' END) AS namaMediator,
				perkara_mediasi.mediasi_gagal  AS tanggalLaporanMediasi,
				(CASE WHEN (SELECT tanggal_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				IS NOT NULL THEN (SELECT tanggal_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				ELSE '..........' END) AS tanggalSidang1,
				(CASE WHEN (SELECT agenda FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				IS NOT NULL THEN (SELECT agenda FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				ELSE '..........' END) AS agendaSidang1,
				(CASE WHEN (SELECT jam_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				IS NOT NULL THEN (SELECT LEFT(jam_sidang,5) FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='1')
				ELSE '..........' END) AS jamSidang1,
				(CASE WHEN (SELECT tanggal_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				IS NOT NULL THEN (SELECT tanggal_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				ELSE '..........' END) AS tanggalSidang2,
				(CASE WHEN (SELECT agenda FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				IS NOT NULL THEN (SELECT agenda FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				ELSE '..........' END) AS agendaSidang2,
				(CASE WHEN (SELECT jam_sidang FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				IS NOT NULL THEN (SELECT LEFT(jam_sidang,5) FROM perkara_jadwal_sidang WHERE perkara_id=perkara.perkara_id AND urutan='2')
				ELSE '..........' END) AS jamSidang2,
				(SELECT hakim_pn.nama_gelar FROM perkara_hakim_pn
				LEFT JOIN hakim_pn ON hakim_pn.id=perkara_hakim_pn.hakim_id
				WHERE perkara_hakim_pn.aktif='Y' AND perkara_hakim_pn.perkara_id=perkara.perkara_id AND perkara_hakim_pn.urutan='1') AS namaHakim1,
				(SELECT panitera_pn.nama_gelar FROM perkara_panitera_pn
				LEFT JOIN panitera_pn ON panitera_pn.id=perkara_panitera_pn.panitera_id
				WHERE perkara_panitera_pn.aktif='Y' AND perkara_panitera_pn.perkara_id=perkara.perkara_id AND perkara_panitera_pn.urutan='1') AS namaPanitera1,
				(CASE WHEN (SELECT COUNT(*) FROM perkara_hakim_pn WHERE perkara_id=perkara.perkara_id)>1 THEN 'Hakim Ketua' ELSE 'Hakim' END) AS jenisHakim1,
				(CASE WHEN (SELECT COUNT(*) FROM perkara_hakim_pn WHERE perkara_id=perkara.perkara_id)>1 THEN 'Majelis Hakim' ELSE 'Hakim' END) AS jenisHakim2,
				(SELECT value FROM sys_config WHERE id = '62') AS namaPN,
				(SELECT value FROM sys_config WHERE id = '63') AS alamatPN,
				(SELECT value FROM sys_config WHERE id = '75') AS zonaWaktu,
				(SELECT value FROM sys_config WHERE id = '68') AS PanSekNama,
				(SELECT value FROM sys_config WHERE id = '69') AS PanSekNIP,
				(SELECT value FROM sys_config WHERE id = '70') AS WaPanNama,
				(SELECT value FROM sys_config WHERE id = '71') AS WaPanNIP,
				d.*, 
				IF(d.pengacara_idP IS NOT NULL,CONCAT('Berkedudukan di  ',d.alamatP,'dalam hal ini memberikan kuasa kepada ',d.namaPengacaraP,' beralamat di ',d.alamatPengacaraP,' berdasarkan Surat Kuasa Khusus tanggal  ',IF(perkara.tanggal_surat IS NULL,'.....',perkara.tanggal_surat),' sebagai '),
				CONCAT('Bertempat tinggal di ',d.`alamatP`,' , Sebagai ')) AS statusP,
				j.*,
				IF(j.pengacara_idT IS NOT NULL,CONCAT('Berkedudukan di  ',j.alamatT,'dalam hal ini memberikan kuasa kepada ',j.namaPengacaraT,' beralamat di ',j.alamatPengacaraT,' berdasarkan Surat Kuasa Khusus tanggal  ',IF(perkara.tanggal_surat IS NULL,'.....',perkara.tanggal_surat),' sebagai '),
				CONCAT('Bertempat tinggal di ',j.`alamatT`,' , Sebagai ')) AS statusT
				FROM perkara
				LEFT JOIN perkara_mediasi ON perkara_mediasi.perkara_id=perkara.perkara_id
				LEFT JOIN perkara_putusan ON perkara_putusan.perkara_id=perkara.perkara_id
				LEFT JOIN perkara_penetapan ON perkara_penetapan.perkara_id=perkara.perkara_id
				LEFT JOIN (SELECT c.pengacara_id AS pengacara_idP, 
					a.perkara_id AS perkara_idP,
					b.id AS pihak_idP, 
					b.nama AS namaP, 
					b.alamat AS alamatP,
					c.nama AS namaPengacaraP,
					c.alamat AS alamatPengacaraP, 
					c.pihak_id AS pihak_idPC
					FROM 	perkara AS a
					LEFT JOIN perkara_pihak1 AS z ON a.perkara_id=z.perkara_id
					LEFT JOIN pihak AS b ON b.id=z.pihak_id
					LEFT JOIN perkara_pengacara AS c ON a.perkara_id=c.perkara_id AND FIND_IN_SET(z.pihak_id,c.pihak_id)) AS d ON d.perkara_idP=$idperkara            
				LEFT JOIN (SELECT c.pengacara_id AS pengacara_idT, a.perkara_id AS perkara_idT, 
					b.id AS pihak_idT,
					b.nama AS namaT,
					b.alamat AS alamatT,
					c.nama AS namaPengacaraT,
					c.alamat AS alamatPengacaraT, 
					c.pihak_id AS pihak_idTC
					FROM perkara AS a
					LEFT JOIN perkara_pihak2 AS h ON a.perkara_id=h.perkara_id
					LEFT JOIN pihak AS b ON b.id=h.pihak_id
					LEFT JOIN perkara_pengacara AS c ON a.perkara_id=c.perkara_id AND  
					FIND_IN_SET(h.pihak_id,c.pihak_id)) AS j ON j.perkara_idT=$idperkara
				WHERE perkara.perkara_id=$idperkara");
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}


	function cetak_ba_pidana($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return 'dd';
			}
			return $this->db->query("SELECT
				perkara.perkara_id AS perkaraId,
				perkara.nomor_perkara AS nomorPerkara,
				(CASE WHEN perkara.nomor_surat IS NOT NULL THEN perkara.nomor_surat ELSE '..........' END) AS nomorSurat,
				perkara.tanggal_pendaftaran AS tanggalPendaftaran,
				perkara.dakwaan AS dakwaan,
				(CASE WHEN perkara.tanggal_surat IS NOT NULL THEN perkara.tanggal_surat ELSE '..........' END) AS tanggalSurat,
				penetapan.penetapan_majelis_hakim AS tanggalPenetapanHakim,
				penetapan.penetapan_panitera_pengganti AS tanggalPenetapanPP,
				pihak1.nama AS namaJaksa,
				pihak2.id AS pihakIdTerdakwa,
				pihak2.nama AS namaTerdakwa,
				(CASE WHEN pihak2.alamat IS NOT NULL THEN pihak2.alamat ELSE '..........' END) AS alamatTerdakwa,
				pihak2.agama_nama AS agamaTerdakwa,
				(CASE WHEN pihak2.jenis_kelamin='L' THEN 'Laki - Laki' ELSE 'Perempuan' END) AS kelaminTerdakwa,
				(CASE WHEN pihak2.pekerjaan IS NOT NULL THEN pihak2.pekerjaan ELSE '..........' END) AS pekerjaanTerdakwa,
				pihak2.warga_negara AS negaraTerdakwa,
				#(case when pihak2.umur is not null then pihak2.umur else '..........' end) as umurTerdakwa,
				pihak2.pendidikan AS pendidikanTerdakwa,
				(CASE WHEN pihak2.tanggal_lahir IS NOT NULL THEN pihak2.tanggal_lahir ELSE '..........' END) AS tanggalLahirTerdakwa,
				(CASE WHEN pihak2.tempat_lahir IS NOT NULL THEN pihak2.tempat_lahir ELSE '..........' END) AS tempatLahirTerdakwa,
				(CASE WHEN perkara.tanggal_surat IS NOT NULL THEN perkara.tanggal_surat ELSE '..........' END)  AS tanggalSurat,
				(CASE WHEN perkara.nomor_surat IS NOT NULL THEN perkara.nomor_surat ELSE '..........' END) AS nomorSurat,
				(CASE WHEN perkara_putusan.amar_putusan IS NOT NULL THEN perkara_putusan.amar_putusan ELSE '..........' END) AS amarPutusan,
				(CASE WHEN perkara_putusan.tanggal_putusan IS NOT NULL THEN perkara_putusan.tanggal_putusan ELSE '..........' END) AS tanggalPutusan,
				(CASE WHEN jadwal1.tanggal_sidang IS NOT NULL THEN jadwal1.tanggal_sidang ELSE '..........' END) AS tanggalSidang1,
				(CASE WHEN jadwal1.jam_sidang IS NOT NULL THEN LEFT(jadwal1.jam_sidang,5) ELSE '..........' END) AS jamSidang1,
				(CASE WHEN jadwal1.agenda IS NOT NULL THEN jadwal1.agenda ELSE '..........' END) AS agendaSidang1,
				(CASE WHEN jadwal2.tanggal_sidang IS NOT NULL THEN jadwal2.tanggal_sidang ELSE '..........' END) AS tanggalSidang2,
				(CASE WHEN jadwal2.jam_sidang IS NOT NULL THEN LEFT(jadwal2.jam_sidang,5) ELSE '..........' END) AS jamSidang2,
				(CASE WHEN jadwal2.agenda IS NOT NULL THEN jadwal2.agenda ELSE '..........' END) AS agendaSidang2,
				(SELECT value FROM sys_config WHERE id = 62) AS namaPN,
				(SELECT value FROM sys_config WHERE id = 64) AS ketuaPN,
				(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 = 68) AS namaPansek,
				(SELECT value FROM sys_config WHERE id = 75) AS zonaWaktu,
				penahanan.pihak_id,
				penahanan.jenis_penahanan_id,
				penahanan.jenis_tahanan_id,
				(CASE WHEN penahanan.tanggal_surat IS NOT NULL THEN penahanan.tanggal_surat ELSE '..........' END) AS tanggal_surat,
				(CASE WHEN  penahanan.nomor_surat IS NOT NULL THEN penahanan.nomor_surat ELSE '..........' END) AS nomor_surat,
				penahanan.mulai,
				penahanan.sampai,
				penahanan.lama_penahanan,
				status_penahanan.nama AS statusPenahanan,
				jenis_penahanan.nama AS jenisPenahanan,
				jenis_tahanan.nama AS jenisTahanan,
				(SELECT hakim_pn.nama_gelar FROM perkara_hakim_pn
					LEFT JOIN hakim_pn ON hakim_pn.id=perkara_hakim_pn.hakim_id
					WHERE perkara_hakim_pn.aktif='Y' AND perkara_hakim_pn.perkara_id=perkara.perkara_id AND perkara_hakim_pn.urutan='1') AS namaHakim1,
				(SELECT panitera_pn.nama_gelar FROM perkara_panitera_pn
					LEFT JOIN panitera_pn ON panitera_pn.id=perkara_panitera_pn.panitera_id
					WHERE perkara_panitera_pn.aktif='Y' AND perkara_panitera_pn.perkara_id=perkara.perkara_id AND perkara_panitera_pn.urutan='1') AS namaPanitera1 
				FROM perkara
				LEFT JOIN perkara_putusan ON perkara_putusan.perkara_id=perkara.perkara_id
				LEFT JOIN perkara_pihak1 ON perkara_pihak1.perkara_id=perkara.perkara_id
				LEFT JOIN perkara_pihak2 ON perkara_pihak2.perkara_id=perkara.perkara_id AND perkara_pihak2.`pihak_id` IN (SELECT pihak_id FROM penahanan_terdakwa WHERE perkara_id=perkara.`perkara_id`)
				LEFT JOIN pihak AS pihak1 ON pihak1.id=perkara_pihak1.pihak_id
				LEFT JOIN pihak AS pihak2 ON pihak2.id=perkara_pihak2.pihak_id
				LEFT JOIN perkara_penetapan AS penetapan ON penetapan.perkara_id=perkara.perkara_id
				LEFT JOIN penahanan_terdakwa AS penahanan ON penahanan.perkara_id=perkara.perkara_id AND penahanan.pihak_id=perkara_pihak2.pihak_id
				LEFT JOIN status_penahanan ON status_penahanan.id=penahanan.status_penahanan_id
				LEFT JOIN jenis_penahanan ON jenis_penahanan.id=penahanan.jenis_penahanan_id
				LEFT JOIN jenis_tahanan ON jenis_tahanan.id=penahanan.jenis_tahanan_id
				LEFT JOIN (SELECT * FROM perkara_jadwal_sidang WHERE urutan='1') AS jadwal1 ON jadwal1.perkara_id=perkara.perkara_id
				LEFT JOIN (SELECT * FROM perkara_jadwal_sidang WHERE urutan='2') AS jadwal2 ON jadwal2.perkara_id=perkara.perkara_id
				WHERE perkara.perkara_id=$idperkara
				ORDER BY jenis_penahanan_id ASC");
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}

	function cetak_ba_pk($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return 'dd';
			}
			return $this->db->query("SELECT
				perkara_pk.nomor_perkara_pn AS nomorPerkaraPN,
				perkara_pk.putusan_pn AS tanggalPutusanPN,
				perkara_pk.permohonan_pk AS tanggalPermohonanPK,
				(CASE WHEN perkara_pk.nomor_perkara_banding IS NOT NULL THEN perkara_pk.nomor_perkara_banding ELSE '..........' END) AS nomorPerkaraBanding,
				(CASE WHEN perkara_pk.putusan_banding IS NOT NULL THEN perkara_pk.putusan_banding ELSE '..........' END) AS tanggalPutusanBanding,
				(CASE WHEN perkara_pk.nomor_perkara_kasasi IS NOT NULL THEN perkara_pk.nomor_perkara_kasasi ELSE '..........' END) AS nomorPerkaraKasasi,
				(CASE WHEN perkara_pk.putusan_kasasi IS NOT NULL THEN perkara_pk.putusan_kasasi ELSE '..........' END) AS tanggalPutusanKasasi,
				(CASE WHEN perkara_pk.isi_pendapat_jaksa IS NOT NULL THEN perkara_pk.isi_pendapat_jaksa ELSE '..........' END) AS pendapatJaksa,
				(CASE WHEN perkara_pk.isi_pendapat_hakim IS NOT NULL THEN perkara_pk.isi_pendapat_hakim ELSE '..........' END) AS pendapatHakim,
				(CASE WHEN perkara_pk.amar_putusan_pk IS NOT NULL THEN perkara_pk.amar_putusan_pk ELSE '..........' END) AS amarPutusanPK,
				(CASE WHEN perkara_pk.hakim1_pk IS NOT NULL THEN perkara_pk.hakim1_pk ELSE '..........' END) AS hakim1_pk,
				(CASE WHEN perkara_pk.hakim2_pk IS NOT NULL THEN perkara_pk.hakim2_pk ELSE '..........' END) AS hakim2_pk,
				(CASE WHEN perkara_pk.hakim3_pk IS NOT NULL THEN perkara_pk.hakim3_pk ELSE '..........' END) AS hakim3_pk,
				(CASE WHEN perkara_pk.hakim4_pk IS NOT NULL THEN perkara_pk.hakim4_pk ELSE '..........' END) AS hakim4_pk,
				pk_detil.pihak_diwakili AS statusDiwakili,
				(CASE WHEN pk_detil.pemohon_tanggal_surat IS NOT NULL THEN pk_detil.pemohon_tanggal_surat ELSE '..........' END) AS tanggalSuratPemohon,
				(CASE WHEN pk_detil.pemohon_nomor_surat IS NOT NULL THEN pk_detil.pemohon_nomor_surat ELSE '..........' END) AS nomorSuratPemohon,
				(CASE WHEN pk_detil.alasan_pk IS NOT NULL THEN pk_detil.alasan_pk ELSE '..........' END) AS alasanPK,
				(CASE WHEN pihak.nama IS NOT NULL THEN pihak.nama ELSE '..........' END) AS namaPihak,
				(CASE WHEN pihak.agama_nama IS NOT NULL THEN pihak.agama_nama ELSE '..........' END) AS agamaPihak,
				(CASE WHEN pihak.alamat IS NOT NULL THEN pihak.alamat ELSE '..........' END) AS alamatPihak,
				(CASE WHEN pihak.jenis_kelamin='L' THEN 'Laki-Laki' ELSE 'Perempuan' END) AS kelaminPihak,
				(CASE WHEN pihak.pekerjaan IS NOT NULL THEN pihak.pekerjaan ELSE '..........' END) AS pekerjaanPihak,
				(CASE WHEN pihak.pendidikan IS NOT NULL THEN pihak.pendidikan ELSE '..........' END) AS pendidikanPihak,
				(CASE WHEN pihak.tanggal_lahir IS NOT NULL THEN pihak.tanggal_lahir ELSE '..........' END) AS tanggalLahirPihak,
				(CASE WHEN pihak.tempat_lahir IS NOT NULL THEN pihak.tempat_lahir ELSE '..........' END) AS tempatLahirPihak,
				(CASE WHEN pihak.warga_negara IS NOT NULL THEN pihak.warga_negara ELSE '..........' END) AS negaraPihak,
				(SELECT nama FROM perkara_pihak1 WHERE perkara_id=pk_detil.perkara_id AND urutan='1') AS namaJaksa,
				(CASE WHEN COUNT(pk_detil.pihak_id)='1' THEN 'Pemohon' ELSE 'Para Pemohon' END) AS jenisPemohon,
				(SELECT value FROM sys_config WHERE id='62') AS namaPN,
				(SELECT value FROM sys_config WHERE id='64') AS ketuaPN,
				(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='68') AS namaPansek,
				(SELECT value FROM sys_config WHERE id='75') AS zonaWaktu
				FROM perkara_pk_detil AS pk_detil
				LEFT JOIN perkara_pk ON perkara_pk.perkara_id=pk_detil.perkara_id
				LEFT JOIN pihak AS pihak ON pihak.id=pk_detil.pemohon_id
				WHERE pk_detil.perkara_id=$idperkara AND pk_detil.status_pihak_id='1'");
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}
	

	

}