<?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(id_uji) AS jumlah";
        else
            $query ="SELECT pu.*, jl.nama_jenis as jenis_layanan, ss.nama_jenis as status_sampel, ss.detail as detail_status";

        $query .= " FROM permintaan_uji pu";

        if(!$is_count)
			$query .= " LEFT JOIN jenis_layanan jl ON jl.id_jenis = pu.id_jenis_layanan
                LEFT JOIN status_sampel ss ON ss.id_jenis = 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_uji DESC";

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

        $this->is_notifikasi();

        $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(isset($data['id_uji'])){
            $query .= " AND pu.id_uji LIKE ?";
            $param[] = '%'.$data['id_uji'].'%';
        }

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

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

        return $result;
    }

    function is_notifikasi(){

    }

    function getSampel($id_uji, $type = false){
        if($type){
            $query = "SELECT COUNT(ds.id_sampel) as c FROM data_sampel ds
                WHERE ds.id_uji = ?";

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

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

    function setUji($data){
        $data['id_jenis_layanan'] = $this->db->query("SELECT id_jenis FROM jenis_layanan WHERE singkatan = ?", $data['id_jenis_layanan'])->row_array()['id_jenis'];

        $param_list = array("id_jenis_layanan", "registrasi_laboratorium", "tgl_pukul_pengambilan", "tgl_pukul_registrasi", "instansi_umum", "alamat", "nomor_telepon", "email_pemohon", "nama_pemohon", "sumber_dana", "perkiraan_estimasi_selesai_lhp");

        $this->db->trans_begin();
        $param = array();

        $data['tgl_pukul_pengambilan'] = isset($data['tgl_pukul_pengambilan']) && $data['tgl_pukul_pengambilan'] != '' ? date('Y-m-d H:i:s', strtotime(str_replace('/', '-', $data['tgl_pukul_pengambilan']))) : null;
        $data['tgl_pukul_registrasi'] = isset($data['tgl_pukul_registrasi']) && $data['tgl_pukul_registrasi'] != '' ? date('Y-m-d H:i:s', strtotime(str_replace('/', '-', $data['tgl_pukul_registrasi']))) : null;
        $data['perkiraan_estimasi_selesai_lhp'] = isset($data['perkiraan_estimasi_selesai_lhp']) && $data['perkiraan_estimasi_selesai_lhp'] != '' ? date('Y-m-d H:i:s', strtotime(str_replace('/', '-', $data['perkiraan_estimasi_selesai_lhp']))) : null;

        foreach($param_list as $r){
            $param[] = $data[$r];
        }
        $param[] = $this->session->userdata("r_userId");
        $param[] = 1;

        if(isset($data['id_uji_hidden']) && $data['id_uji_hidden'] != ''){
            $param[] = $data['id_uji_hidden'];
            $query = "UPDATE permintaan_uji SET id_jenis_layanan = ?, registrasi_laboratorium = ?, tgl_pukul_pengambilan = ?, tgl_pukul_registrasi = ?, instansi_umum = ?, alamat = ?, nomor_telepon = ?, email_pemohon = ?, nama_pemohon = ?, sumber_dana = ?, perkiraan_estimasi_selesai_lhp = ?, last_modified_user = ?, id_status = ?
                WHERE id_uji = ?";
        }else{
            $query = "INSERT INTO permintaan_uji (id_jenis_layanan, registrasi_laboratorium, tgl_pukul_pengambilan, tgl_pukul_registrasi, instansi_umum, alamat, nomor_telepon, email_pemohon, nama_pemohon, sumber_dana, perkiraan_estimasi_selesai_lhp, last_modified_user, id_status) VALUES
                (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        }

        $result = $this->db->query($query, $param);
        if ($data['id_uji_hidden'] !== '') $id = $data['id_uji_hidden'];
        else $id = $this->db->insert_id();

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return FALSE;
        }else{
            $this->db->trans_commit();
            return TRUE;
        }
    }

    function setDaftar($data){
        $param_list = array("id_uji", "asal_sampel", "klasifikasi", "id_keterangan", "id_jenis_sampel", "kualitas", "bentuk_jenis", "label_pakan", "petugas_pengambil_sampel", "jenis_pengujian", "merek_dagang");

        $disposisi_list = array("id_penanganan_sampel", "berat_sampel", "satuan_sampel", "kemasan", "id_sampel_kerja", "id_sampel_arsip");

        $param = array();
        foreach($param_list as $r){
            if(strpos($data[$r], 'AM') || strpos($data[$r], 'PM')){
                if(strpos($data[$r], 'PM')){
                    $data[$r] = str_replace('PM','',$data[$r]);
                    $data[$r] = date('Y-m-d H:i:s',strtotime('+12 hour',strtotime($data[$r])));
                }else{
                    $data[$r] = str_replace('AM','',$data[$r]);
                    $data[$r] = date('Y-m-d H:i:s',strtotime($data[$r]));
                }
            }
            $param[] = $data[$r];
        }

        $param[] = $this->session->userdata("r_userId");



        $this->db->trans_begin();
        if(isset($data['id_sampel_hidden']) && $data['id_sampel_hidden'] != ''){
            $param[] = $id = $data['id_sampel_hidden'];

            $query = "UPDATE data_sampel SET id_uji = ?, asal_sampel = ?, klasifikasi = ?, id_keterangan = ?, id_jenis_sampel = ?, kualitas = ?, bentuk_jenis = ?, label_pakan = ?, petugas_pengambil_sampel = ?, jenis_pengujian = ?, merek_dagang = ?, last_modified_user = ?
            WHERE id_sampel = ?";
        }else{
            $query = "INSERT INTO data_sampel (id_uji, asal_sampel, klasifikasi, id_keterangan, id_jenis_sampel, kualitas, bentuk_jenis, label_pakan, petugas_pengambil_sampel, jenis_pengujian, merek_dagang, last_modified_user)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
        }

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

        if(empty($id)) $id = $this->db->insert_id();

        if(isset($id)){
            $param = array();
            foreach($disposisi_list as $r){
                $param[] = $data[$r];
            }

            $param[] = $this->session->userdata("r_userId");
            $param[] = $id;
            $param[] = '0';

            if($data['id_disposisi_hidden'] && $data['id_disposisi_hidden'] != ''){
                $param[] = $id = $data['id_disposisi_hidden'];

                $query = "UPDATE disposisi SET  id_penanganan_sampel = ?,berat_sampel = ?,satuan_berat = ?,kemasan = ?,id_sampel_kerja = ?,id_sampel_arsip = ?, last_modified_user = ?, id_sampel = ?, is_active = ?
                WHERE id_disposisi = ?";
            }else {
                $query = "INSERT INTO disposisi (id_penanganan_sampel, berat_sampel, satuan_berat, kemasan, id_sampel_kerja, id_sampel_arsip, last_modified_user, id_sampel, is_active)
                VALUES (?,?,?,?,?,?,?,?,?)";
            }
            $this->db->query($query, $param);
        }

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return FALSE;
        }else{
            $this->db->trans_commit();
            if (isset($data['id_sampel_hidden']) && $data['id_sampel_hidden'] !== '') $status = 'updete';
            else $status = 'insert';
            return $status;
        }
    }

    function getNpps($id_uji){
        $query = "SELECT ds.*, js.nama_jenis as jenis_sampel, d.* FROM data_sampel ds
        LEFT JOIN jenis_sampel js ON js.id_jenis = ds.id_jenis_sampel
        LEFT JOIN disposisi d ON d.id_sampel = ds.id_sampel
        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_jenis as jenis_layanan, ss.nama_jenis 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_jenis = pu.id_jenis_layanan
            LEFT JOIN status_sampel ss ON ss.id_jenis = pu.id_status
            LEFT JOIN jenis_layanan js ON js.id_jenis = pu.id_jenis_layanan
            WHERE pu.is_active = '1' AND pu.id_uji = ?
        ", $id_uji)->row_array();

        return $query;
    }

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

        return $query;
    }

    function getJenisSampel($data) {
        $query = $this->db->query("SELECT id_jenis, nama_jenis FROM jenis_sampel WHERE is_active = '1' AND id_klasifikasi = ? AND id_gol_sampel = ?", $data)->result_array();

        $result = '<option></option>';
        foreach($query as $r) {
            $result .= "<option value='".$r['id_jenis']."'>".$r['nama_jenis']."[".$r['kode_jenis']."]"."</option>";
        }

        return $result;
    }

    function getJenisPengujian($id){
      $data = "<option><option>";
      $param = array();
      if($id == 'kosong'){
        $query = "SELECT id_jenis, nama_jenis 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_jenis, nama_jenis FROM parameter_uji WHERE id_jenis 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_jenis']."'>".$r['nama_jenis']."<option>";
      }

      return $data;
    }
}
