<?php 
if (!defined('BASEPATH')) exit('No direct script access allowed');
class m_template_tanda_terima_akta_cerai_agama extends CI_Model {

	function getdata($perkara_id){
		try{
			if(!is_numeric($perkara_id)){ return '';	}
			$sql = " SELECT 
						nomor_akta_cerai AS no_ac
						,(SELECT VALUE FROM sys_config WHERE id=62) AS nama_satker
						,ac.`tgl_akta_cerai` AS tgl_ac
						,IF(
							perk.`jenis_perkara_id`=347,
							'Putusan',
							'Penetapan'
						)AS jenis_putusan
						,perk.`nomor_perkara` AS nomor_perkara
						,IF(
							perk.`jenis_perkara_id`=347,
							put.`tanggal_putusan`,
							ikrar.tgl_ikrar_talak
						)AS tanggal_putusan
						,pihak1.`nama` AS nama_p
						,get_umur(pihak1.`tanggal_lahir`,ac.`tgl_akta_cerai`) AS umur_p
						,pihak1.`agama` AS agama_p
						,pihak1.`pekerjaan` AS pekerjaan_p
						,pihak1.`alamat` AS alamat_p
						,pihak2.`nama` AS nama_t
						,get_umur(pihak2.`tanggal_lahir`,ac.`tgl_akta_cerai`) AS umur_t
						,pihak2.`agama` AS agama_t
						,pihak2.`pekerjaan` AS pekerjaan_t
						,pihak2.`alamat` AS alamat_t
						,perk.jenis_perkara_nama AS jenis_perkara
						,ac.`perceraian_ke`
						,IF(
							perk.`jenis_perkara_id`=347,
							'Penggugat',
							'Termohon'
						)AS istri_sebagai
						,IF(
							ac.`qobla_bada`=0
							,'qabla dukhul'
							,'ba''da dukhul'
						) AS qobla_bada
						,ac.`keadaan_istri`
						,nikah.kua_tempat_nikah
						,nikah.no_kutipan_akta_nikah
						,nikah.tgl_kutipan_akta_nikah
						,(SELECT VALUE FROM sys_config WHERE id=68) AS nama_pan
						,ubah_nip((SELECT VALUE FROM sys_config WHERE id=69)) AS nip_pan
					FROM perkara_akta_cerai 	AS ac
					JOIN perkara 			AS perk 	ON ac.`perkara_id` = perk.`perkara_id`
					JOIN perkara_putusan 		AS put 		ON ac.`perkara_id` = put.`perkara_id`
					LEFT JOIN perkara_ikrar_talak 	AS ikrar 	ON ac.`perkara_id` = ikrar.`perkara_id`
					JOIN 
						(SELECT 
							pp.perkara_id
							,p.`nama`
							,p.`tanggal_lahir`
							,p.`agama_nama` AS agama
							,p.`pekerjaan`
							,pp.`alamat`
						FROM perkara_pihak1 AS pp
						JOIN pihak AS p ON pp.`pihak_id` = p.id
						WHERE pp.perkara_id = ".$perkara_id." AND pp.`urutan` = 1)
									AS pihak1 	ON ac.`perkara_id` = pihak1.`perkara_id`
					JOIN 
						((SELECT 
							pp.perkara_id
							,p.`nama`
							,p.`tanggal_lahir`
							,p.`agama_nama` AS agama
							,p.`pekerjaan`
							,pp.`alamat`
						FROM perkara_pihak2 AS pp
						JOIN pihak AS p ON pp.`pihak_id` = p.id
						WHERE pp.perkara_id = ".$perkara_id." AND pp.`urutan` = 1))
									AS pihak2 	ON ac.`perkara_id` = pihak2.`perkara_id`
					LEFT JOIN perkara_data_pernikahan AS nikah ON ac.perkara_id = nikah.perkara_id
					WHERE ac.`perkara_id` = ".$perkara_id;
			return $this->db->query($sql);
		}catch(Exception $e){ return false; }
	}
	function getAktaCerai($idperkara){
		try{
			$sql = "SELECT * FROM perkara_akta_cerai WHERE perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataIkrar($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM perkara_ikrar_talak WHERE perkara_id =$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function get_data_pihak_loop($idperkara,$jenispihak){
		try {
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query('SELECT b.*,  a.*,c.nama AS kewarganegaraan, d.nama as agama,
											group_concat(e.pengacara_id) as pengacara_id 
											FROM pihak AS a
											LEFT JOIN
											(SELECT * FROM perkara_pihak'.$jenispihak.') AS b
											ON a.id=b.pihak_id
											LEFT JOIN
											(SELECT id,nama FROM negara) AS c
											ON a.warga_negara_id=c.id
											LEFT JOIN
											(SELECT id,nama FROM agama) AS d
											ON a.agama_id=d.id
											LEFT JOIN
											(SELECT pengacara_id, pihak_id, perkara_id FROM perkara_pengacara) AS e
											ON b.pihak_id=e.pihak_id and e.perkara_id=b.perkara_id
											WHERE b.perkara_id='.$idperkara.' GROUP BY b.id ORDER BY b.id ASC');
			} catch (Exception $e) {
				return '';
			}
	}
	function getDataPerkara($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT nomor_perkara,nomor_surat,tanggal_surat, tanggal_pendaftaran, dakwaan 
					FROM perkara 
					WHERE perkara_id =$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataPernikahan($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM perkara_data_pernikahan WHERE perkara_id =$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataPA(){
		try{
			$sql = "SELECT * FROM 
					(SELECT value AS NamaPN FROM sys_config WHERE id = 62) AS T1,
					(SELECT value AS KetuaPNNama FROM sys_config WHERE id = 64) AS T2,
					(SELECT value AS AlamatPN FROM sys_config WHERE id = 63) AS T3,
					(SELECT value AS WakilKetuaPNNama FROM sys_config WHERE id = 66) AS T4,
					(SELECT value AS PansekPNNama FROM sys_config WHERE id = 68) AS T5,
					(SELECT value AS PansekNip FROM sys_config WHERE id = 69) AS T9,
					(SELECT value AS WaPanNama FROM sys_config WHERE id = 70) AS T6, 
					(SELECT value AS ZonaWaktu FROM sys_config WHERE id = 75) AS T7,
					(SELECT value AS KodePN FROM sys_config WHERE id = 61) AS T8
					";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function getDataPutusan($idperkara){
			try {
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query('SELECT tanggal_putusan,
											sumber_hukum_id,
											tanggal_cabut,
											tanggal_gugur,
											amar_putusan,
											tanggal_bht,
											tanggal_minutasi FROM perkara_putusan 
											WHERE perkara_id='.$idperkara);
				} catch (Exception $e) {
				return '';
			}
	}
	function update_blangko($perkara_id){
		try{
			if(intval($id)>1){ return '';	}
			$sql = "UPDATE perkara_akta_cerai SET blangko_akta_cerai = 1 WHERE perkara_id =".$perkara_id;
			return $this->db->query($sql);
		}catch(Exception $e){ return false; }
	}

}
?>