<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Register_diversi_korban_saksi_m extends CI_Model {
	function parseOrderby($col){
		if($col==1){
			return "b.nomor_perkara"; 
		}else if($col==6){
			return "b.tanggal_surat";
		}else{
			return "b.tanggal_pendaftaran";
		}
	}

	function get_register_anak_saksi_list($col=2,$asc_desc='ASC',$begin=0,$key=''){
		$orderby = $this->parseOrderby($col);
		$where = '';
		
		if(!empty($key)){
			$where .= ' WHERE (p.nomor_perkara LIKE "%'.$key.'%" OR p.jenis_perkara_nama LIKE "%'.$key.'%" OR pk.nama LIKE "%'.$key.'%" OR h.nama LIKE "%'.$key.'%" OR pp5.nama LIKE "%'.$key.'%" OR ppos.nama LIKE "%'.$key.'%" OR p.pihak2_text LIKE "%'.$key.'%" OR p.pasal_dakwaan LIKE "%'.$key.'%") ';
			$orderby = $this->parseOrderby($col);
		}
		try {
			return $this->db->query("SELECT yy.*, GROUP_CONCAT(xx.nama_korban SEPARATOR '<br>') AS nama_korban,GROUP_CONCAT(ww.saksi SEPARATOR '<br>') AS nama_saksi, b.perkara_id,b.nomor_perkara, b.jenis_perkara_nama,b.pihak2_text AS terdakwa,
									b.tanggal_pendaftaran,b.tanggal_surat,b.pasal_dakwaan
									
								FROM
								(SELECT a.perkara_id,a.urutan,a.pihak_id,a.nama, CONCAT('Anak Korban : ',a.nama) AS pihak
									FROM perkara_pihak_korban AS a
									LEFT JOIN perkara_pihak5 AS b ON a.perkara_id=b.perkara_id AND a.pihak_id=b.pihak_id
									GROUP BY a.perkara_id
								UNION ALL
								SELECT a.perkara_id,a.urutan,a.pihak_id,a.nama,CONCAT('Saksi Anak : ',a.nama)  
									FROM perkara_pihak5 AS a 
									LEFT JOIN perkara_pihak_orang_tua AS b ON a.perkara_id=b.perkara_id AND a.pihak_id=b.id_anak
									LEFT JOIN pihak AS c ON id_orang_tua=c.id
									WHERE jenis_saksi=3) AS yy
								LEFT JOIN perkara AS b ON yy.perkara_id=b.perkara_id
								LEFT JOIN (SELECT a.perkara_id ,a.urutan,a.pihak_id,a.nama AS nama_korban
									FROM perkara_pihak_korban AS a
									LEFT JOIN perkara_pihak5 AS b ON a.perkara_id=b.perkara_id AND a.pihak_id=b.pihak_id
									GROUP BY a.perkara_id) AS xx ON yy.perkara_id=xx.perkara_id AND yy.pihak_id=xx.pihak_id
								LEFT JOIN (SELECT a.perkara_id,a.urutan,a.pihak_id,a.nama AS saksi ,c.nama
									FROM perkara_pihak5 AS a 
									LEFT JOIN perkara_pihak_orang_tua AS b ON a.perkara_id=b.perkara_id AND a.pihak_id=b.id_anak
									LEFT JOIN pihak AS c ON id_orang_tua=c.id
									WHERE jenis_saksi=3) AS ww ON yy.perkara_id=ww.perkara_id AND yy.pihak_id=ww.pihak_id
								GROUP BY yy.perkara_id ".$where."
									ORDER BY ".$orderby." ".$asc_desc." LIMIT ".$begin.",50;");
		} catch (Exception $e) {
			
		}
	}	
	
	function fecth_data_num(){
		$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;
    	}
	}	

}
?>