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

class m_template_agama_pbt extends CI_Model {

	function getListPenggugat($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return 'dd';
			}
			return $this->db->query("SELECT urutan, pihak_id, nama FROM perkara_pihak1 WHERE perkara_id=$idperkara ORDER BY urutan ASC");
		} catch (Exception $e) {
		}
	}

	function getListJurusita($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return 'dd';
			}
			$sql = "SELECT nama_gelar, jabatan, jurusita_id as id
					FROM (SELECT *
							FROM perkara_jurusita
							WHERE perkara_id = $idperkara AND aktif='Y'
							GROUP BY jurusita_id) AS ph
					LEFT JOIN (SELECT id, nama_gelar, jabatan FROM jurusita) AS h
					ON ph.jurusita_id = h.id
					ORDER BY urutan ASC";
			$query=$this->db->query($sql);
			return $query;
		} catch (Exception $e) {
		}
	}
	
	function getBiayaPerkara($idperkara){
		try {
			if(!is_numeric($idperkara)){
				return 'dd';
			}
			return $this->db->query("SELECT a.perkara_id, a.kategori_id, a.tahapan_id, a.jumlah
                FROM 
                perkara_biaya AS a 
                WHERE a.perkara_id=$idperkara AND a.kategori_id IN ('4','6') 
                GROUP BY a.jumlah");
		} catch (Exception $e) {
		}
	}

	function blangko_pbt_t(){
		try{
			$sql = "SELECT kode,nama FROM template_dokumen WHERE 
			(alur_perkara_id='15' AND tahapan_id='15'  AND proses_id='230' AND nomor >= '201' AND nomor <= '210')";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function blangko_pbt_t_delegasi(){
		try{
			$sql = "SELECT kode,nama FROM template_dokumen WHERE alur_perkara_id='15' AND tahapan_id='15'  AND proses_id='231' AND nomor = '202' ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

			function getAlurPerkaraId($idperkara){
		try {
			return $this->db->query('SELECT alur_perkara_id FROM perkara WHERE perkara_id = '.$idperkara.';')->row()->alur_perkara_id;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function blangko_pbt_p($alur_perkara_id){
		try{
			//$sql = "SELECT kode,nama FROM template_dokumen WHERE alur_perkara_id='15' AND tahapan_id='15'  AND proses_id='230' AND nomor >= '211' AND nomor <= '220'";
			$sql = "SELECT kode,nama FROM template_dokumen WHERE 
				(alur_perkara_id=$alur_perkara_id AND tahapan_id='15'  AND proses_id='230' AND nomor >= '211' AND nomor <= '220')";

			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function blangko_pbt_p_delegasi(){
		try{
			$sql = "SELECT kode,nama FROM template_dokumen WHERE alur_perkara_id='15' AND tahapan_id='15'  AND proses_id='231' AND nomor = '201'";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function identitas_pihak($idperkara,$jenispihak){
		try{
			$sql = "SELECT c.nama AS nama, YEAR(a.tanggal_pendaftaran) - YEAR(c.tanggal_lahir) AS umur, d.nama AS agama, c.pekerjaan AS pekerjaan, e.nama AS pendidikan, c.alamat AS alamat, f.nama AS kuasa, a.nomor_perkara as nomor_perkara
					FROM perkara a 
					LEFT JOIN (SELECT * FROM perkara_pihak".$jenispihak.") b ON a.perkara_id = b.perkara_id
					LEFT JOIN pihak c ON b.pihak_id = c.id
					LEFT JOIN agama d ON c.agama_id = d.id
					LEFT JOIN tingkat_pendidikan e ON c.pendidikan_id = e.id
					LEFT JOIN perkara_pengacara f ON a.perkara_id = f.perkara_id
					WHERE a.perkara_id = $idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	
	function cetak_pemberitahuan_putusan_pdt($idperkara,$idpihak,$jursit_id,$urutan_t){
		try {
			if(!is_numeric($idperkara) OR !is_numeric($idpihak) OR !is_numeric($jursit_id)){
				return 'dd';
			}
			// $sql="SELECT 	a.`perkara_id` , 
			// 			a.`nomor_perkara` AS noPerkara ,  
			// 			b.jurusita_id,
			// 			b.jurusita_nama AS jurusita, 
			// 			IF(jumlahP.jmlP >= 2,CONCAT(c.`nama`,' .dkk'),c.`nama`) AS namaPihakP,
			// 			IF(jumlahT.jmlT >= 2,CONCAT(e.`nama`,' .dkk'),e.`nama`) AS namaPihakT,
			// 			d.`tanggal_putusan` AS tglPutusan, 
			// 			d.`amar_putusan` AS amarPutusan,	
			// 			f.nama AS namaPihak,
			// 			f.pekerjaan AS pekerjaanPihak,
			// 			f.alamat AS alamatPihak,
			// 			h.value AS namaPengadilan,
			// 			jumlahP.jmlP, jumlahT.jmlT
			// 	FROM perkara AS a
			// 	LEFT JOIN perkara_jurusita AS b  ON a.perkara_id=b.perkara_id and b.jurusita_id=$jursit_id
			// 	LEFT JOIN perkara_pihak1 AS c ON c.`perkara_id`=a.`perkara_id`
			// 	LEFT JOIN perkara_putusan AS d ON a.`perkara_id`=d.`perkara_id`
			// 	LEFT JOIN perkara_pihak2 AS e ON a.`perkara_id`=e.`perkara_id`
			// 	LEFT JOIN (SELECT pihak_id,nama, pekerjaan,alamat FROM perkara_pihak1 ) AS f ON f.pihak_id = $idpihak
			// 	LEFT JOIN (SELECT id, VALUE FROM sys_config) AS h ON h.id=62
			// 	LEFT JOIN (SELECT COUNT(pihak_id) AS jmlP, perkara_id FROM perkara_pihak1 WHERE perkara_id=$idperkara ) AS jumlahP ON  jumlahP.perkara_id=a.perkara_id
			// 	LEFT JOIN (SELECT COUNT(pihak_id) AS jmlT, perkara_id FROM perkara_pihak2 WHERE perkara_id=$idperkara ) AS jumlahT ON  jumlahT.perkara_id=a.perkara_id
			// 	WHERE  a.perkara_id=$idperkara and c.urutan=1 AND e.urutan=1 ";
			$sql="SELECT 	a.`perkara_id` , 
						a.`nomor_perkara` AS noPerkara ,  
						a.`tanggal_pendaftaran` AS tgldaftar ,  
						b.jurusita_id,
						b.jurusita_nama AS jurusita, 
						IF(jumlahP.jmlP >= 2,CONCAT(c.`nama`,' .dkk'),c.`nama`) AS namaPihakP,
						IF(jumlahT.jmlT >= 2,CONCAT(e.`nama`,' .dkk'),e.`nama`) AS namaPihakT,
						d.`tanggal_putusan` AS tglPutusan, 
						d.`amar_putusan` AS amarPutusan,	
						f.nama AS namaPihak,
						f.pekerjaan AS pekerjaanPihak,
						f.alamat AS alamatPihak,
						h.value AS namaPengadilan,
						jumlahP.jmlP, jumlahT.jmlT, z.nama as jenis_perkara,
						year(a.tanggal_pendaftaran) -  year(f.tanggal_lahir) as umur, ag.nama as agama, tp.nama as pendidikan
				FROM perkara AS a
				LEFT JOIN perkara_jurusita AS b  ON a.perkara_id=b.perkara_id and b.jurusita_id=$jursit_id
				LEFT JOIN perkara_pihak1 AS c ON c.`perkara_id`=a.`perkara_id`
				LEFT JOIN perkara_putusan AS d ON a.`perkara_id`=d.`perkara_id`
				LEFT JOIN perkara_pihak2 AS e ON a.`perkara_id`=e.`perkara_id`
				LEFT JOIN (SELECT pihak_id, y.nama, y.pekerjaan, y.alamat, y.tanggal_lahir, y.agama_id, y.pendidikan_id 
					FROM perkara_pihak1 x, pihak y WHERE x.pihak_id=y.id ) AS f ON f.pihak_id = $idpihak
				LEFT JOIN (SELECT id, VALUE FROM sys_config) AS h ON h.id=62
				LEFT JOIN (SELECT COUNT(pihak_id) AS jmlP, perkara_id FROM perkara_pihak1 WHERE perkara_id=$idperkara ) AS jumlahP ON  jumlahP.perkara_id=a.perkara_id
				LEFT JOIN (SELECT COUNT(pihak_id) AS jmlT, perkara_id FROM perkara_pihak2 WHERE perkara_id=$idperkara ) AS jumlahT ON  jumlahT.perkara_id=a.perkara_id
				left join jenis_perkara z on a.jenis_perkara_id = z.id
				left join agama ag on f.agama_id = ag.id
				left join tingkat_pendidikan tp on f.pendidikan_id = tp.id
				WHERE  a.perkara_id=$idperkara and c.urutan=1 AND e.urutan".$urutan_t;
				$query=$this->db->query($sql);
			return $query;
		} catch (Exception $e) {
		}
	}

}

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