<?php

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

class M_pendaftaran extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    function getDaftar($is_count = false, $limit = null, $start = null, $id = null){
        $param = array();

        if($is_count)
            $query = "SELECT COUNT(pu.id) AS jumlah";
        else
            $query ="SELECT pu.*, jl.nama as jenis_layanan, ss.nama as status_sampel, ss.detail as detail_status";

        $query  .= " FROM permintaan_uji pu";

        $query  .= " LEFT JOIN jenis_layanan jl ON jl.id = pu.id_layanan
                    LEFT JOIN status_sampel ss ON ss.id = pu.id_status";

        $query  .= " WHERE pu.is_active = '1' ";

        if($this->input->post()){
            $data   = $this->input->post();
            $where  = $this->whereHandler($query, $param, $data);

            $query  = $where['query'];
            $param  = $where['param'];
        }

        $query .= " ORDER BY pu.id ASC";

        if($is_count == false) {
            if (isset($limit)) {
                $query .= " LIMIT ?, ?";
                $param[] = intval($start);
                $param[] = intval($limit);
            }
        }

        $result = $this->db->query($query, $param);

        if($is_count){
			return $result->row_array()['jumlah'];
		} else {
			if($id != null)
				return $result->row_array();
			else
				return $result->result_array();
		}
    }

    function whereHandler($query, $param, $data){
        if($data['nomor_uji'] != ''){
            $query  .= " AND pu.nomor_uji = ?";
            $param[] = $data['nomor_uji'];
        }

        if($data['instansi_umum'] != ''){
            $query  .= " AND pu.instansi_umum LIKE ?";
            $param[] = '%'.$data['instansi_umum'].'%';
        }

        if($data['jenis_layanan'] != ''){
            $query  .= " AND jl.nama LIKE ?";
            $param[] = '%'.$data['jenis_layanan'].'%';
        }

        if($data['status'] != ''){
            $query  .= " AND ss.nama LIKE ?";
            $param[] = '%'.$data['status'].'%';
        }

        $result['query'] = $query;
        $result['param'] = $param;

        return $result;
    }

    function get_id_ttpp($id_uji)
    {
        return $this->db->get_where('permintaan_uji', ['id'=>$id_uji])->row_array()['id_ttpp'];
    }

    function getSampel($id_uji, $type = false){
        $id_ttpp = $this->get_id_ttpp($id_uji);
        if($type){
            $query = "SELECT COUNT(ds.id_sampel) as c FROM data_sampel ds
                LEFT JOIN permintaan_uji pu ON pu.id = ds.id_uji
                WHERE pu.id = ?";

            $result = $this->db->query($query, $id_uji)->row_array();
        }else {
            $query = "SELECT pu.*, ds.*, js.id as id_jenis_sampel, js.nama as jenis_sampel, k.nama as klasifikasi, gj.nama as keterangan FROM data_sampel ds
                LEFT JOIN jenis_sampel js ON js.id = ds.id_jenis_sampel
                LEFT JOIN gol_jenis_sampel gj ON gj.id = ds.id_keterangan
                LEFT JOIN klasifikasi k ON k.id = ds.klasifikasi
                LEFT JOIN permintaan_uji pu ON pu.id = ds.id_uji
                WHERE pu.id = ? AND ds.is_active = '1'
                ORDER BY ds.nomor_sampel
                ";

            $result = $this->db->query($query, $id_uji)->result_array();
        }
            return $result;
    }

    function set_uji($data, $id=null){
        if(empty($this->session->userdata('data_uji'))){
            $data['id_layanan'] = $this->db->get_where("jenis_layanan", ['singkatan' => $data['id_layanan']])->row_array()['id'];
            
            $data['tgl_pukul_pengambilan'] = tgl_permintaan_uji($data['tgl_pukul_pengambilan']);
            $data['tgl_pukul_registrasi'] = tgl_permintaan_uji($data['tgl_pukul_registrasi']);
            $data['tanggal_penghapusan'] = tgl_permintaan_uji($data['tanggal_penghapusan']);
            $data['perkiraan_estimasi_selesai_lhp'] = tgl_permintaan_uji($data['perkiraan_estimasi_selesai_lhp']);
            $data['tgl_surat'] = tgl_permintaan_uji($data['tgl_surat']);
        }

        $data['id_status'] = 1;

        if($id){
            $this->db->where('id', $id);
            $this->db->update('permintaan_uji', $data);
            return $id;
        }else{
            $data['tahun'] = date('Y');
            $data['bulan'] = date('m');
            $data['nomor_uji'] = $this->count_permintaan_uji($data['tahun'])+1;
            
            $this->db->insert('permintaan_uji', $data);

            if(empty($data['id_ttpp'])){
                $this->db->where('id', $this->db->insert_id());
                $this->db->update('permintaan_uji', ['id_ttpp' => $this->db->insert_id()]);
            }
            return $this->db->insert_id();
        }
    }

    function get_nomor_uji($id_uji)
    {
        $id_ttpp = $this->db->get_where('permintaan_uji', ['id'=>$id_uji])->row_array()['id_ttpp'];

        $query = $this->db->get_where('permintaan_uji', ['id_ttpp'=>$id_ttpp]);
        $result = $query->result_array();
        return $result;
    }

    function count_permintaan_uji($year = 0, $month=0)
    {   
        $param = [];
        $param['tahun']     = $year;
        if($month){
            $param['bulan'] = $month;
        }
        $query = $this->db->get_where('permintaan_uji', $param)->num_rows();
        return $query;
    }

    function delete($table, $id)
    {
        $this->db->where('id', $id);
        $this->db->update($table, ['is_active'=>'0']);
        return 1;
    }

    function count_data_sampel($year = 0)
    {
        $query = $this->db->get_where('data_sampel', ['tahun'=> $year, 'is_active'=>'1'])->num_rows();

        return $query;
    }

    function cek_sampel($id_uji){
        $this->db->select('id');
        $this->db->order_by('id', 'desc');
        return $this->db->get_where('data_sampel', ['id_uji'=>$id_uji, 'is_active'=>'1'])->result_array();
    }

    function set_nomor_sampel($year=''){
        if($year == ''){
            $year = date('Y');
        }
        $this->db->select('nomor_sampel');
        $this->db->order_by('nomor_sampel', 'desc');
        $query = $this->db->get_where('data_sampel', ['tahun'=>$year])->row_array();

        if(empty($query['nomor_sampel'])){
            $query['nomor_sampel'] = 0;
        }
        return ($query['nomor_sampel'] + 1);
    }

    function set_sampel($data, $id=null){
        $param_list = ["nomor_sampel", "id_uji", "asal_sampel", "klasifikasi", "id_keterangan", "kualitas", "bentuk_jenis", "label_pakan", "petugas_pengambil_sampel", "jenis_pengujian", "merk_dagang", "id_jenis_sampel", "phytase", "tahun"];
        
        $param = [];
        foreach($param_list as $pl){
            $param[$pl] = $data[$pl];
        }
        $param['last_modified_user'] = $this->session->userdata("r_userId");

        if($id){
            $this->db->where('id', $id);
            $this->db->update('data_sampel', $param);
            $id_sampel = $id;
        }else {
            $this->db->insert('data_sampel', $param);
            $id_sampel = $this->db->insert_id();
        }

        $disposisi_list = ["id_penanganan_sampel", "berat_sampel", "kemasan", "id_sampel_kerja", "id_sampel_arsip"];
        $id_disposisi   = $this->db->get_where('disposisi', ['id_sampel' => $id_sampel])->row_array()['id'];

        $param = [];
        foreach($disposisi_list as $dl){
            $param[$dl] = $data[$dl];
        }

        $param['satuan_berat']      = $data['satuan_sampel'];
        $param['last_modified_user']= $this->session->userdata("r_userId");
        $param['id_sampel']         = $id_sampel;
        $param['is_active']         = '0';

        if($id_disposisi){
            $this->db->where('id', $id_disposisi);
            $this->db->update('disposisi', $param);
        }else {
            $this->db->insert('disposisi', $param);
        }

        return 1;
    }

    function get_parameter_uji($id)
    {
        return $this->db->get_where('parameter_uji', ['id'=>$id])->row_array();
    }

    function getNpps($id_uji){
        $query = "SELECT ds.*, js.nama as jenis_sampel, d.*, gj.nama as keterangan, k.nama as nama_klasifikasi
        FROM data_sampel ds
        LEFT JOIN jenis_sampel js ON js.id = ds.id_jenis_sampel
        LEFT JOIN gol_jenis_sampel gj ON gj.id = js.id_gol_sampel
        LEFT JOIN disposisi d ON d.id_sampel = ds.id
        LEFT JOIN klasifikasi k ON ds.klasifikasi = k.id
        WHERE ds.id_uji = ? AND ds.is_active = ?";
        return $this->db->query($query,array($id_uji, '1'))->result_array();

    }

    function detailData($id_uji){
        $query = $this->db->query("SELECT pu.*, jl.nama as jenis_layanan, ss.nama as status_sampel, ss.detail as detail_status, js.singkatan as singkatan_layanan
            FROM permintaan_uji pu
            LEFT JOIN jenis_layanan jl ON jl.id = pu.id_layanan
            LEFT JOIN status_sampel ss ON ss.id = pu.id_status
            LEFT JOIN jenis_layanan js ON js.id = pu.id_layanan
            WHERE pu.is_active = '1' AND pu.id = ?
        ", $id_uji)->row_array();

        return $query;
    }

    function detailSampel($id_sampel){
        $query= $this->db->query("SELECT ds.*, k.id as klasifikasi, gj.nama as keterangan, dp.*
        FROM data_sampel ds
        LEFT JOIN disposisi dp ON dp.id_sampel = ds.id
        LEFT JOIN klasifikasi k ON k.id = ds.klasifikasi
        LEFT JOIN gol_jenis_sampel gj ON gj.id = ds.id_keterangan
        WHERE ds.id = ? AND ds.is_active = '1'", $id_sampel)->row_array();

        return $query;
    }

    function getJenisSampel($data) {
        $query = $this->db->query("SELECT id, nama, kode FROM jenis_sampel WHERE is_active = '1' AND id_klasifikasi = ? AND id_gol_sampel = ?", $data)->result_array();
        $query1 = $this->db->last_query();
        $jenis_sampel = '<option></option>';
        foreach($query as $r) {
            $jenis_sampel .= "<option value='".$r['id']."'>".$r['nama']."[".$r['kode']."]"."</option>";
        }

        $this->db->select("id, nama, detail");
        if($data['id_klasifikasi'] == 3){
            $this->db->like('nama', 'Lain-lain');
        }else{
            $this->db->not_like('nama', 'Lain-lain');
        }
        $this->db->where("is_active", '1');
        $query = $this->db->get("gol_jenis_sampel")->result_array();
        $query2 = $this->db->last_query();
        $gol_sampel = '<option></option>';
        foreach($query as $r) {
            $selected = ($data['id_gol_sampel'] == $r['id']) ? 'selected' : '';
            $gol_sampel .= "<option $selected value='".$r['id']."'>".$r['nama']."</option>";
        }

        $result['gol_sampel'] = $gol_sampel;
        $result['jenis_sampel'] = $jenis_sampel;    
        return json_encode($result);
    }

    function getJenisPengujian($id){
      $data = "<option><option>";
      $param = array();
      if($id == 'kosong'){
        $query = "SELECT id, nama FROM parameter_uji WHERE is_active = '1'";
      }else if($id = '1|2|3|4|5'){
        $data .= "<option value='1|2|3|4|5' selected>Proksimat<option>";
        $query = "SELECT id, nama FROM parameter_uji WHERE id NOT IN ? AND is_active = '1'";
        $param[] = array(1,2,3,4,5);
      }

      $result = $this->db->query($query, $param)->result_array();
      var_dump($id);

      foreach ($result as $r) {
        $data .= "<option value='".$r['id']."'>".$r['nama']."<option>";
      }

      return $data;
    }
}
