<?php

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

class M_download_dok extends CI_Model {

    protected $idGroup = null;

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

    function get_disposisi($data){
        $query = "SELECT ds.*, dp.* FROM data_sampel ds
            LEFT JOIN disposisi dp ON dp.id_sampel = ds.id_sampel
            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_uji = ? AND is_active = '1'", $id_uji)->row_array();
    }

    function getAnalis($data){
        $query = $this->db->query("SELECT ds.*, ds.id_sampel as 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,
         gj.detail as gol_jenis_sampel, js.*, t.*, jl.nama_jenis as jenis_layanan
         FROM data_sampel ds
         LEFT JOIN lhu l ON l.id_sampel = ds.id_sampel AND l.is_active = '1'
         LEFT JOIN jenis_sampel js ON js.id_jenis = ds.id_jenis_sampel
         LEFT JOIN gol_jenis_sampel gj ON gj.id_jenis = js.id_gol_sampel
         LEFT JOIN tdn t ON t.id_sampel = ds.id_sampel
         LEFT JOIN permintaan_uji pu ON pu.id_uji = ds.id_uji
         LEFT JOIN jenis_layanan jl ON jl.id_jenis = pu.id_jenis_layanan
         WHERE ds.id_uji = ? AND ds.is_active ='1'", $data['id_uji']);
        return $query->result_array();
    }

    function get_npps($data, $method = null) {
        $param = array();
        $param[] = $data['id_uji'];

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

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

        return $result;
    }

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

      $query = "SELECT ds.*, pu.*
        FROM data_sampel ds
        LEFT JOIN permintaan_uji pu ON pu.id_uji = ds.id_uji
        WHERE ds.id_uji = ? AND ds.is_active = '1'";
      $result = $this->db->query($query, $param)->result_array();

      $get_parameter = $this->db->query("SELECT id_jenis, nama_jenis
        FROM parameter_uji WHERE is_active = '1'
        ORDER BY id_jenis ASC")->result_array();

      $parameter_uji = array();
      $harga_uji = array(1=>25000, 25000, 75000, 75000, 75000, 75000, 75000, 30000, 75000, 400000, 75000);

      foreach($get_parameter as $r){
        foreach($result as $j){
          $x=0;
          $parameter_uji[$r['id_jenis']]['id_jenis'] = $r['id_jenis'];
          $parameter_uji[$r['id_jenis']]['nama_jenis'] = $r['nama_jenis'];
          if(strpos($j['jenis_pengujian'], $r['id_jenis'].'|') !== false){
            $parameter_uji[$r['id_jenis']]['total_sampel'][] = $j['id_sampel'];
            $x++;
          }
        }
        if(!empty($parameter_uji[$r['id_jenis']]['total_sampel'])){
          $parameter_uji[$r['id_jenis']]['total'] = count($parameter_uji[$r['id_jenis']]['total_sampel']);
        }
        if(!empty($harga_uji[$r['id_jenis']])){
          $parameter_uji[$r['id_jenis']]['harga_uji'] = $harga_uji[$r['id_jenis']];
        }else {
          $parameter_uji[$r['id_jenis']]['harga_uji'] = 0;
        }

        if(!empty($parameter_uji[$r['id_jenis']]['total_sampel']) && !empty($harga_uji[$r['id_jenis']])){
          $parameter_uji[$r['id_jenis']]['total_harga_uji'] = intval($parameter_uji[$r['id_jenis']]['total'] * $parameter_uji[$r['id_jenis']]['harga_uji']);
        }else{
          $parameter_uji[$r['id_jenis']]['total_harga_uji'] = 0;
        }
      }

      return array($result,$parameter_uji);
    }

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

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

    function get_parameter($id){

    }

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

    function get_tdn($data){
      $param = array();
      $query = "SELECT t.*, ds.id_sampel as nomor_sampel, l.*, pu.registrasi_laboratorium as nomor_lab FROM data_sampel ds
        LEFT JOIN tdn t ON ds.id_sampel = t.id_sampel
        LEFT JOIN lhu l ON ds.id_sampel = l.id_sampel
        LEFT JOIN permintaan_uji pu ON pu.id_uji = 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.*, ds.id_sampel as nomor_sampel, l.*, pu.*, 
        js.kadar_air as sni_air,
        js.kadar_abu as sni_abu,
        js.protein_kasar as sni_pk,
        js.lemak_kasar as sni_lk,
        js.serat_kasar as sni_sk,
        js.kalsium as sni_ca,
        js.fosfor as sni_p,
        js.aflatoksin as sni_af,
        js.lisin as sni_lisin,
        js.metionin as sni_metionin,
        js.metionin_sistin as sni_metionin_sistin,
        js.treonin as sni_treonin,
        js.triptofan as sni_triptofan
        FROM data_sampel ds
        LEFT JOIN tdn t ON ds.id_sampel = t.id_sampel
        LEFT JOIN lhu l ON ds.id_sampel = l.id_sampel
        LEFT JOIN jenis_sampel js ON js.id_jenis = ds.id_jenis_sampel
        LEFT JOIN permintaan_uji pu ON pu.id_uji = ds.id_uji
        WHERE ds.id_sampel = ? 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){
      
    }

}
