<?php

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

class M_download_dok extends CI_Model {

    public $id_group = '';

    public function __construct() {
        parent::__construct();
        $this->id_group = $this->session->userdata("id_group");
    }

    function get_disposisi($data){
        $query = "SELECT ds.*, dp.*, pu.registrasi_laboratorium, pu.tgl_pukul_registrasi, pu.perkiraan_estimasi_selesai_lhp, js.nama as ket_jenis_sampel, pu.id_layanan
            FROM data_sampel ds
            JOIN disposisi dp ON dp.id_sampel = ds.id
            JOIN permintaan_uji pu ON pu.id = ds.id_uji
            LEFT JOIN gol_jenis_sampel js ON js.id = ds.id_keterangan
            WHERE ds.id_uji = ? AND ds.is_active = '1'
        ";

        $result = $this->db->query($query, $data['id_uji'])->result_array();

        return $result;
    }

    function dataUji($id_uji) {
        return $this->db->query("SELECT * FROM permintaan_uji WHERE id = ? AND is_active = '1'", $id_uji)->row_array();
    }

    function getAnalis($data){
        $query = $this->db->query("SELECT ds.*, ds.nomor_sampel, pu.*,
        l.*, l.air as l_air, l.abu as l_abu, l.protein_kasar as l_protein_kasar, l.lemak_kasar as l_lemak_kasar, l.serat_kasar as l_serat_kasar,l.kalsium as l_kalsium, l.fosfor as l_fosfor, l.ur as l_ur, l.af as l_af, l.andf as l_andf, l.mbm as l_mbm, l.tdn as l_tdn, l.betn as l_betn, l.ca as l_ca,
        gj.detail as gol_jenis_sampel, js.*, t.*, jl.nama as jenis_layanan
        FROM data_sampel ds
        LEFT JOIN lhu l ON l.id_sampel = ds.id AND l.is_active = '1'
        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 tdn t ON t.id_sampel = ds.id
        LEFT JOIN permintaan_uji pu ON pu.id = ds.id_uji
        LEFT JOIN jenis_layanan jl ON jl.id = pu.id_layanan
        WHERE ds.id_uji = ? AND ds.is_active ='1'", $data['id_uji']);
        return $query->result_array();
    }

    function get_data_lhp($id_uji)
    {
        $this->db->select('ds.*, pu.*, l.*, gj.detail as gol_jenis_sampel, js.nama as jenis_sampel, jl.nama as jenis_layanan');
        $this->db->join('lhp l', 'l.id_sampel = ds.id', 'left');
        $this->db->join('permintaan_uji pu', 'pu.id = ds.id_uji', 'left');
        $this->db->join('jenis_layanan jl', 'jl.id = pu.id_layanan', 'left');
        $this->db->join('jenis_sampel js', 'js.id = ds.id_jenis_sampel', 'left');
        $this->db->join('gol_jenis_sampel gj', 'gj.id = js.id_gol_sampel', 'left');
        $query = $this->db->get_where('data_sampel ds', ['id_uji' => $id_uji]);
        $result = $query->result_array();

        return $result;
        
    }

    function get_metode_uji_by_id($id)
    {
        return $this->db->get_where('metode_uji', ['id_parameter_uji'=>$id])->row_array()['detail'];
    }

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

    function get_npps($id_uji) {
        $param    = [];
        $param[]  = $id_uji;

        $query = "SELECT ds.*, pu.*, d.*, js.nama as jenis_sampel, js.kode as kode_pakan
        FROM data_sampel ds
        LEFT JOIN permintaan_uji pu ON pu.id        = ds.id_uji
        LEFT JOIN disposisi      d  ON d.id_sampel  = ds.id
        LEFT JOIN jenis_sampel   js ON js.id        = ds.id_jenis_sampel
        WHERE ds.id_uji = ? AND ds.is_active = '1'";

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

        return $result->result_array();
    }

    function get_ttpp($id_uji){
        $param = array();
        $param[] = $id_uji;

        $daftar_uji = $this->db->get_where('permintaan_uji', ['id_ttpp'=>$id_uji])->result_array();

        $d_id_uji = [];
        foreach($daftar_uji as $du){
            $d_id_uji[] = $du['id'];
        }

        $this->db->select('ds.*, pu.*');
        $this->db->join('permintaan_uji pu', 'pu.id = ds.id_uji', 'left');
        $this->db->where_in('id_uji', $d_id_uji);
        $this->db->where('ds.is_active', '1');
        $query = $this->db->get('data_sampel ds');
        $result = $query->result_array();
        
        $get_parameter = $this->db->query("SELECT id, nama, harga
            FROM parameter_uji WHERE is_active = '1'
            ORDER BY id ASC")->result_array();

        $parameter_uji = array();

        foreach($get_parameter as $r){
            foreach($result as $j){
                $x=0;
                $parameter_uji[$r['id']]['id'] = $r['id'];
                $parameter_uji[$r['id']]['nama'] = $r['nama'];
                if(in_array($r['id'], json_decode($j['jenis_pengujian'])) !== false){
                    $parameter_uji[$r['id']]['total_sampel'][] = $j['id'];
                    $x++;
                }
            }
            if(!empty($parameter_uji[$r['id']]['total_sampel'])){
                $parameter_uji[$r['id']]['total'] = count($parameter_uji[$r['id']]['total_sampel']);
            }
            
            $parameter_uji[$r['id']]['tarif_analis'] = ($r['harga'] != 0) ? $r['harga'] : '-';

            if(!empty($parameter_uji[$r['id']]['total_sampel'])){
                $parameter_uji[$r['id']]['jumlah_biaya'] = intval((float)$parameter_uji[$r['id']]['total'] * (float)$parameter_uji[$r['id']]['tarif_analis']);
            }else{
            $parameter_uji[$r['id']]['jumlah_biaya'] = 0;
            }
        }

        return array($result,$parameter_uji);
    }

    function get_metode($nama = null) {
        if(!is_null($nama)){
            $query = "SELECT * FROM metode_uji WHERE nama = ? AND is_active = '1'";

            $result = $this->db->query($query, $nama);
            return $result->row_array();
        }else{
            return $this->db->query("SELECT detail FROM metode_uji WHERE is_active = '1'")->result_array();
        }
        }

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

    function get_jabatan($id){
        return $this->db->query("SELECT p.nama, p.nip, p.niptk FROM personil p LEFT JOIN jabatan j ON j.id = p.id_jabatan WHERE p.id_jabatan LIKE ?", '%"'.$id.'"%')->row_array();
    }

    function get_tdn($data){
        $param = array();
        $query = "SELECT t.*, ds.id as nomor_sampel, l.*, pu.registrasi_laboratorium as nomor_lab FROM data_sampel ds
            LEFT JOIN tdn t ON ds.id = t.id_sampel
            LEFT JOIN lhu l ON ds.id = l.id_sampel
            LEFT JOIN permintaan_uji pu ON pu.id = ds.id_uji
            WHERE pu.id_uji = ? AND pu.id_status = ? AND pu.is_active = '1' AND ds.is_active = '1'
        ";

        $param[] = $data['id_uji'];
        $param[] = $data['status'];

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

    function get_sertifikat($id_sampel){
        $param = array();
        $query = "SELECT js.*, t.*, ds.nomor_sampel, lu.*, l.*, pu.*, ds.*
            FROM data_sampel ds
            LEFT JOIN tdn t ON ds.id = t.id_sampel
            LEFT JOIN lhp l ON ds.id = l.id_sampel
            LEFT JOIN lhu lu ON ds.id = lu.id_sampel
            LEFT JOIN jenis_sampel js ON js.id = ds.id_jenis_sampel
            LEFT JOIN permintaan_uji pu ON pu.id = ds.id_uji
            WHERE ds.id = ? AND pu.is_active = '1' AND ds.is_active = '1'
        ";

        $param[] = $id_sampel;

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

    function get_data_amino($id_uji){
        $query = $this->db->query("SELECT * FROM lhp_amino WHERE id_uji = ?", $id_uji);
        return $query->row_array();
    }

    function get_lhp_af($data){
    
    }

    function get_kelengkapan_administrasi($id_uji)
    {
        $this->db->select('pu.*');
        $query = $this->db->get_where('permintaan_uji pu', ['id'=>$id_uji]);
        $result = $query->row_array();

        return $result;
    }

    function get_id_sampel_by_id_uji($id_uji)
    {
        $this->db->select('id');
        $query = $this->db->get_where('data_sampel', ['id_uji'=>$id_uji, 'is_active'=>'1']);
        $result = $query->result_array();

        $char = '';
        if(count($result) > 0){
            foreach($result as $r){
            $char .= $r['id'].', ';
            }
        }
        $char = substr($char, 0, -2);

        return $char;
    }

    function get_mulai_uji($table, $id_sampel)
    {
        if(in_array($table, ['kadar_tdn', 'kadar_mbm'])){
            return null;
        }
        $this->db->select('mulai_uji');
        $query = $this->db->get_where($table, ['id_sampel'=>$id_sampel]);
        $result = $query->result_array();
        if(count($result) > 0){
            return $result[0]['mulai_uji'];
        }
    }

    function get_personil_by_id($id_user = '')
    {
        return $this->db->get_where('personil', ['id_user'=>$id_user])->row_array();
    }

}
