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

class lalulintas extends CI_Model{

	function getPerkaraLLCetak($tanggalPersidangan){
		try {
			$sql="SELECT
				B.nomor_tilang AS NomorTilang,
				DATE_FORMAT(B.tanggal_penindakan,'%d/%m/%Y') AS TanggalPenindakan,
				B.form_tilang AS JenisTilang,
				B.nomor_pembayaran AS NomorPembayaran,
				B.kode_satker_polri AS KodeSatkerPolri,
				B.deskripsi_kantor_polisi AS DeskripsiPolri,
				H.nama AS NamaPelanggar,
				H.alamat AS AlamatPelanggar,
				A.dakwaan AS PasalPelanggaran,
				(CASE 
					WHEN B.bukti_tilang_id='10' THEN '10|SIM C'
					WHEN B.bukti_tilang_id='11' THEN '11|SIM A'
					WHEN B.bukti_tilang_id='12' THEN '12|SIM BI'
					WHEN B.bukti_tilang_id='13' THEN '13|SIM A UMUM'
					WHEN B.bukti_tilang_id='14' THEN '14|SIM BI UMUM'
					WHEN B.bukti_tilang_id='15' THEN '15|SIM BII UMUM'
					WHEN B.bukti_tilang_id='20' THEN '20|STNK'
					WHEN B.bukti_tilang_id='30' THEN '30|SIM & STNK'
					WHEN B.bukti_tilang_id='40' THEN '40|KENDARAAN'
					WHEN B.bukti_tilang_id='50' THEN '50|Buku KIR'
					ELSE '' END
				) AS BuktiTilang,
				(CASE 
					WHEN B.jenis_kendaraan_id='1' THEN '1|Sepeda Motor'
					WHEN B.jenis_kendaraan_id='2' THEN '2|Mobil Barang/Pick Up'
					WHEN B.jenis_kendaraan_id='3' THEN '3|Mobil Penumpang Pribadi'
					WHEN B.jenis_kendaraan_id='4' THEN '4|Mobil Penumpang Umum'
					WHEN B.jenis_kendaraan_id='5' THEN '5|Bus'
					WHEN B.jenis_kendaraan_id='6' THEN '6|Truk'
					WHEN B.jenis_kendaraan_id='7' THEN '7|Truk Gandeng'
					WHEN B.jenis_kendaraan_id='8' THEN '8|Tronton'
					ELSE '99|Lain-Lain' END
				) AS JenisKendaraan,
				B.nomor_polisi AS NomorPolisi,
				B.uang_titipan AS UangTitipan,
				(SELECT VALUE FROM sys_config WHERE id='82') AS KodeSatkerPN,
				A.nomor_perkara AS NomorPerkara,
				C.majelis_hakim_nama AS NamaHakim,
				C.panitera_pengganti_text AS NamaPanitera,
				B.kode_satker_kejaksaan AS KodeSatkerKejaksaan,
				DATE_FORMAT(C.sidang_pertama,'%d/%m/%Y') AS TanggalSidang,
				(CASE WHEN D.putusan_verstek='Y' THEN 'VERSTEK' ELSE 'TIDAK' END) AS StatusKehadiran,
				MAX(CASE WHEN E.status_putusan_id='16' THEN durasi_nominal ELSE '' END) AS DendaTilang,
				MAX(CASE WHEN E.status_putusan_id='24' THEN durasi_nominal ELSE '' END) AS SubsiderKurungan,
				MAX(CASE WHEN E.status_putusan_id='88' THEN durasi_nominal ELSE '' END) AS BiayaPerkara
				FROM perkara AS A
				LEFT JOIN perkara_lalulintas AS B ON B.perkara_id=A.perkara_id
				LEFT JOIN perkara_penetapan AS C ON C.perkara_id=A.perkara_id
				LEFT JOIN perkara_putusan AS D ON D.perkara_id=A.perkara_id
				LEFT JOIN perkara_putusan_terdakwa AS E ON E.perkara_id=A.perkara_id
				LEFT JOIN perkara_pihak2 AS H ON H.perkara_id=A.perkara_id
				WHERE A.alur_perkara_id='114'
				AND C.sidang_pertama='".$tanggalPersidangan."'
				AND H.urutan='1'
				GROUP BY A.perkara_id";
			$query = $this->db->query($sql);
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


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


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


	function getJenisPerkara($idalurperkara){
		if(empty($idalurperkara)) return '';
		try {
			$this->db->where('id',$idalurperkara);
			return $this->db->get('alur_perkara')->row()->nama;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getDataBuktiTilang($id,$kode){
		if(empty($id)) return '';
		try{
			$this->db->where('id', $id);
			$this->db->where('kode', $kode);
			$query = $this->db->get('bukti_tilang');
			return $query;
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}


	function getDataKendaraan($id,$kode){
		if(empty($id)) return '';
		try{
			$this->db->where('id', $id);
			$this->db->where('kode', $kode);
			$query = $this->db->get('jenis_kendaraan');
			return $query;
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}


	function getListHakimPN(){
		try{
			$this->db->where('aktif','Y');
			$this->db->order_by('nama','ASC');
			$query = $this->db->get('hakim_pn');
			return $query;
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}

	function getListPaniteraPN(){
		try{
			$this->db->where('aktif','Y');
			$this->db->order_by('nama','ASC');
			$query = $this->db->get('panitera_pn');
			return $query;
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}

	function getListRuangSidang(){
		try{
			$this->db->where('aktif','Y');
			$this->db->order_by('nama','ASC'); 
			return $this->db->get('ruangan_sidang');
		} catch (Exception $e) {
            log_message('error', $e);
		}
	}

	function getNomorUrutPerkara($idalurperkara,$year){
		if(empty($idalurperkara)) return '';
		if(empty($year)) return '';
		try {
			$query = $this->db->query("SELECT ifnull(max(nomor_urut_perkara),0) + 1 AS nomor FROM perkara WHERE alur_perkara_id='".$idalurperkara."' AND year(tanggal_pendaftaran)='".$year."';");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->nomor;
				}
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getNomorUrutRegister($idalurperkara,$date){
		if(empty($idalurperkara)) return '';
		if(empty($date)) return '';
		try {
			$query = $this->db->query("SELECT (ifnull(max(nomor_urut_register),0)) +1 AS nomor 
				FROM perkara WHERE alur_perkara_id='".$idalurperkara."' AND LEFT(tanggal_pendaftaran,7) = '".$date."';");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->nomor;
				}
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getKlasifikasiPerkara($idalurperkara){
		if(empty($idalurperkara)) return '';
		if($idalurperkara<0) return '';
		try {
			$query = $this->db->query("SELECT alur_perkara_id, jenis_perkara_id AS id,nama_lengkap AS nama
				FROM jenis_alur_perkara AS j, jenis_perkara AS p
				WHERE j.jenis_perkara_id = p.id AND j.alur_perkara_id = ".$idalurperkara."
				ORDER BY nama_lengkap ASC");
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

		
	function getFormatPerkara($idjenisperkara){
		if(empty($idjenisperkara)) return '';
		try {
			$this->db->where('id',$idjenisperkara);
			return $this->db->get('jenis_perkara')->row()->format_nomor;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getKodePN(){
		try {
			$this->db->where('id',61);
			$this->db->select('value');
			return $this->db->get('sys_config')->row()->value;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function getNamaPN(){
		try {
			$this->db->where('id',62);
			$this->db->select('value');
			return $this->db->get('sys_config')->row()->value;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}



	function isNomorPerkaraExist($nomorPerkara){
		try {
			$this->db->where('nomor_perkara',$nomorPerkara);
			$this->db->select('nomor_perkara');
			$query = $this->db->get('perkara');
			if($query->num_rows>0){
				return TRUE;
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			log_message('error', $e);
			return -1;
		}
	}

	function isNomorUrutRegisterExists($tglPendaftaran,$idalurperkara,$noUrutReg){
		$tmp = explode("/", $tglPendaftaran);
		if(count($tmp)==3){
			$date = $tmp[2]."-".$tmp[1];
		}else{
			return -2;
		}
		try {
			$query = $this->db->query("SELECT nomor_urut_register 
				FROM perkara 
				WHERE LEFT(tanggal_pendaftaran,7)='".$date."' 
				AND alur_perkara_id='".$idalurperkara."' AND nomor_urut_register='".$noUrutReg."';");
			if($query->num_rows>0){
				return TRUE;
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			log_message('error', $e);
			return -2;
		}
	}

	function getJenisPerkaraNama($id){
		if(empty($id)) return '';
		try {
			$this->db->where('id',$id);
			$this->db->select('kode,nama,nama_lengkap');
			return $this->db->get('jenis_perkara');
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function getNamaBarangBukti($id){
		if(empty($id)) return '';
		try {
			$this->db->where('id',$id);
			$this->db->select('nama');
			return $this->db->get('bukti_tilang')->row()->nama;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getNamaKendaraan($id){
		if(empty($id)) return '';
		try {
			$this->db->where('id',$id);
			return $this->db->get('jenis_kendaraan')->row()->nama;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getAlurPerkara($id){
		if(empty($id)) return '';
		try {
			$this->db->where('id',$id);
			$this->db->select('kode,nama,keterangan');
			return $this->db->get('alur_perkara');
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
	
}