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

class List_Harta_Pailit extends CI_Model{
	function parseOrderby($col){
		if($col==1){
			return "pp.tanggal_pendaftaran"; 
		}else if($col==2){
			return "p.nomor_perkara"; 
		}else if($col==3){
			return "p.para_pihak"; 
		}else if($col==4){
			return "ppp.tanggal_putusan"; 
		}else if($col==5){
			return "h.nama_gelar"; 
		}else if($col==6){
			return "pi.nama_gelar"; 
		}else if($col==7){
			return "k.nama"; 
		}else if($col==8){
			return "nama_kreditur"; 
		}else{
			return "pp.tanggal_pendaftaran";
		}
	}

	function getListPailit($col=9,$type='DESC',$begin=0,$idalurperkara,$key=''){
		try {
			$orderby = $this->parseOrderby($col);
			$where = '';
			if(!empty($key)){
				$where .= ' WHERE (pp.tanggal_pendaftaran_actio LIKE "%'.$key.'%" OR 
					p.nomor_perkara LIKE "%'.$key.'%" OR p.para_pihak LIKE "%'.$key.'%" OR
					h.nama_gelar LIKE "%'.$key.'%" OR pi.nama_gelar LIKE "%'.$key.'%"
					OR k.nama LIKE "%'.$key.'%" OR ppp.tanggal_putusan LIKE "%'.$key.'%") ';
			}
			return $this->db->query("SELECT SQL_CALC_FOUND_ROWS p.perkara_id,
				pp.tanggal_pendaftaran AS tanggal_pendaftaran_harta,p.nomor_perkara,
				p.para_pihak,ppp.tanggal_putusan,h.nama_gelar AS hakim_pengawas,k.nama AS nama_kurator,
				pi.nama_gelar AS panitera,nama_kreditur
				FROM perkara_harta_pailit AS pp
				LEFT JOIN perkara AS p
				ON pp.perkara_id = p.perkara_id
				LEFT JOIN perkara_putusan AS ppp
					ON p.perkara_id = ppp.perkara_id
				LEFT JOIN hakim_pn AS h
					ON h.id = pp.hakim_pengawas_id
				LEFT JOIN panitera_pn AS pi
					ON pi.id = pp.panitera_id
				LEFT JOIN kurator AS k
					ON k.pihak_id = pp.kurator_id
				
				".$where." ORDER BY ".$orderby." ".$type." LIMIT ".$begin.",30;");
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}

	function getNomorPerkara($idalurperkara){
		try {
			return $this->db->query('SELECT perkara_id,nomor_perkara FROM perkara WHERE alur_perkara_id IN ('.$idalurperkara.');');
		} catch (Exception $e) {
			return '';
		}
	}

	function getTglPendaftaranKepailitan($idperkara){
		try {
			$result = $this->db->query('SELECT tanggal_pendaftaran FROM perkara WHERE perkara_id = '.$idperkara);
			if($result->num_rows>0){
				return $result->row()->tanggal_pendaftaran;
			}else{
				return '';
			}
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}

	function getInfoPerkara($idperkara){
		try {
			return $this->db->query('SELECT p.perkara_id,nomor_perkara,tanggal_pendaftaran,ppp.tanggal_putusan,
				ppp.tanggal_minutasi,para_pihak,kurator_id,hakim_pengawas_id,panitera_pengganti_id,jurusita_id,
				penetapan_hakim_pengawas
				FROM v_perkara AS p
				LEFT JOIN perkara_putusan AS ppp
					ON p.perkara_id = ppp.perkara_id
				WHERE p.perkara_id = '.$idperkara);
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}

	function isPerkaraExist($idperkara){
		try {
			$result = $this->db->query('SELECT perkara_id FROM perkara_actio_pauliana WHERE perkara_id = '.$idperkara);
			if($result->num_rows>0){
				return TRUE;
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			log_message('error', $e);
			return '-1';
		}
	}

	function getInfoPailit($idperkara){
		try {
			return $this->db->query('SELECT p.perkara_id,p.nomor_perkara,p.tanggal_pendaftaran,
				ppp.tanggal_putusan,ppp.tanggal_minutasi,para_pihak,ph.kurator_id,ph.hakim_pengawas_id,
				ph.panitera_id,ph.jurusita_id,ph.tanggal_penetapan_hakim_pengawas,ph.amar_penetapan_hakim_pengawas,
				ph.tanggal_insolvensi,tanggal_pelelangan,tanggal_pembagian,nama_kreditur,jumlah_pembagian,
				catatan_harta_pailit,ph.tanggal_pendaftaran AS tanggal_pendaftaran_harta
				FROM perkara_harta_pailit as ph
				LEFT JOIN v_perkara AS p
					ON ph.perkara_id = p.perkara_id
				LEFT JOIN perkara_putusan AS ppp
					ON p.perkara_id = ppp.perkara_id
				WHERE p.perkara_id = '.$idperkara);
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}
	function getDetilPerkara($idperkara){
		try {
			return $this->db->query('SELECT p.perkara_id,p.nomor_perkara,p.tanggal_pendaftaran,
				ppp.tanggal_putusan,ppp.tanggal_minutasi,para_pihak,k.nama AS kurator,h.nama_gelar AS hakim,
				pan.nama_gelar AS panitera,j.nama_gelar AS jurusita,ph.tanggal_penetapan_hakim_pengawas,
				ph.amar_penetapan_hakim_pengawas,ph.tanggal_insolvensi,tanggal_pelelangan,
				tanggal_pembagian,nama_kreditur,jumlah_pembagian,
				catatan_harta_pailit,ph.tanggal_pendaftaran AS tanggal_pendaftaran_harta
				FROM perkara_harta_pailit as ph
				LEFT JOIN v_perkara AS p
					ON ph.perkara_id = p.perkara_id
				LEFT JOIN perkara_putusan AS ppp
					ON p.perkara_id = ppp.perkara_id
				LEFT JOIN kurator as k
					ON k.pihak_id = ph.kurator_id
				LEFT JOIN panitera_pn as pan
					ON pan.id = ph.panitera_id
				LEFT JOIN hakim_pn as h
					ON h.id = ph.hakim_pengawas_id
				LEFT JOIN jurusita as j
					ON j.id = ph.jurusita_id
				WHERE p.perkara_id = '.$idperkara);
		} catch (Exception $e) {
			return '';
		}
	}

	function fecth_data_num(){
		try {
			$query = $this->db->query("SELECT FOUND_ROWS() as cnt;");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->cnt;
				}
	    		return $query->num_rows;
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			
		}
	}

	function getTanggalPutusan($idperkara){
		try {
			$result = $this->db->query('SELECT tanggal_putusan FROM perkara_putusan WHERE perkara_id = '.$idperkara);
			if($result->num_rows>0){
				return $result->row()->tanggal_putusan;
			}else{
				return '';
			}
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}

	function getListHakim(){
		try {
			return $this->db->query('SELECT id,nama_gelar FROM hakim_pn WHERE aktif="Y" ORDER BY nama ASC;');
		} catch (Exception $e) {
			
		}
	}

	function getListKurator(){
		try {
			return $this->db->query('SELECT pihak_id,nama FROM kurator ORDER BY nama ASC;');
		} catch (Exception $e) {
			
		}
	}

	function getListPanitera(){
		try {
			return $this->db->query('SELECT id,nama_gelar FROM panitera_pn WHERE aktif="Y" ORDER BY nama ASC;');
		} catch (Exception $e) {
			
		}
	}

	function getListJurusita(){
		try {
			return $this->db->query('SELECT id,nama_gelar FROM jurusita WHERE aktif="Y" ORDER BY nama ASC;');
		} catch (Exception $e) {
			
		}
	}
	function fetch_description($title,$data){
		$descrip = '<br><table style="vertical-align:top" cellspacing="0" cellpadding="1" border="1">';
		$descrip .= '<tr><th>Nama Kolom</th><th>Nilai</th></tr>';
		foreach ($data as $key => $value) {
			$descrip .= '<tr>';
			$descrip .= '<td>'.$key.'</td>';
			$descrip .= '<td>'.$value.'</td>';
			$descrip .= '</tr>';
		}
		$descrip .= '</table>';
		return $descrip;
	}

	function add_audittrail($action,$title,$descrip,$tablename){
		try {
			$data = array(
				'datetime' => date("Y-m-d H:i:s"),
				'ipaddress' => $this->input->ip_address(),
				'username' => $this->session->userdata('username'),
				'tablename' => $tablename,
				'formname' => 'Add Referensi Pihak',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
			
		}
	}

	function add_data($data){
		try {
			$this->db->insert('perkara_harta_pailit', $data);
			$title = "Tambah Register Pemberesan Harta Pailit [perkara_id=<b>".$data['perkara_id']."</b>]<br />Tambah table <b>perkara_harta_pailit</b> dari halaman <b>REGISTER PEMBERESAN HARTA PAILIT</b> dengan Primary Key [id=".$data['perkara_id']."]";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("INSERT",$title,$descrip,'perkara_harta_pailit');
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_data($data,$idperkara){
		try {
			if($idperkara>0){
				$this->db->where('perkara_id', $idperkara);
				$this->db->update('perkara_harta_pailit', $data); 
				$title = "Ubah Data Register Pemberesan Harta Pailit [perkara_id=<b>".$data['perkara_id']."</b>]<br />Update table <b>perkara_harta_pailit</b> dari halaman <b>REGISTER PEMBERESAN HARTA PAILIT</b> dengan Primary Key [id=".$data['perkara_id']."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("UPDATE",$title,$descrip,'perkara_harta_pailit');
				return TRUE;
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function del_data($idperkara){
		try {
			$this->db->query("DELETE FROM perkara_harta_pailit WHERE perkara_id =".$idperkara);
			$title = "Delete Data Register Pemberesan Harta Pailit <br />dengan id [perkara_id=".$idperkara."] Oleh = ".$this->session->userdata('username');
			$this->add_audittrail("DELETE",$title,'DELETE DATA','perkara_harta_pailit');
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

}