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

class Form_batubara_kirim_model extends CI_Model
{
  public $table = 'form_batubara_kirim';
  public $id    = 'id_batubara_kirim';
  public $order = 'DESC';

  var $column = array(
    'id_batubara_kirim',
    'nomor_form_batubara_kirim'
  );

  private function _get_datatables_query()
  {
    $this->db->from($this->table);
    //$this->db->join('unit_batubara_kirim', 'unit_batubara_kirim.id_unit_batubara_kirim = form_batubara_kirim.unit_batubara_kirim');
    $this->db->join('stockpile_batubara_masuk', 'stockpile_batubara_masuk.id_stockpile_batubara_masuk = form_batubara_kirim.asal_batubara_kirim');
    $this->db->join('tujuan_batubara_kirim', 'tujuan_batubara_kirim.id_tujuan_batubara_kirim = form_batubara_kirim.tujuan_batubara_kirim');
    $this->db->join('kode_batubara_kirim', 'kode_batubara_kirim.id_kode_batubara_kirim = form_batubara_kirim.kode_batubara_kirim');
    $this->db->join('checker', 'checker.id_checker = form_batubara_kirim.checker');
    $this->db->join('checker_batubara_kirim', 'checker_batubara_kirim.id_checker_batubara_kirim = form_batubara_kirim.checker_batubara_kirim');
    //$this->db->join('checker_batubara_kirim_jetty', 'checker_batubara_kirim_jetty.id_checker_batubara_kirim_jetty = form_batubara_kirim.checker_batubara_kirim_jetty');

    $i = 0;

    foreach ($this->column as $item) // loop column
    {
      if ($_POST['search']['value']) // if datatable send POST for search
      {

        if ($i === 0) // first loop
        {
          $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
          $this->db->like($item, $_POST['search']['value']);
        } else {
          $this->db->or_like($item, $_POST['search']['value']);
        }

        if (count($this->column) - 1 == $i) //last loop
          $this->db->group_end(); //close bracket
      }
      $column[$i] = $item; // set column array variable to order processing
      $i++;
    }

    if (isset($_POST['order'])) // here order processing
    {
      $this->db->order_by($column[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
    } else if (isset($this->order)) {
      $order = $this->order;
      $this->db->order_by(key($order), $order[key($order)]);
    }
  }

  public function get_datatables()
  {
    $this->_get_datatables_query();
    if ($_POST['length'] != -1)
      $this->db->limit($_POST['length'], $_POST['start']);
    $query = $this->db->get();
    return $query->result();
  }

  function count_filtered()
  {
    $this->_get_datatables_query();
    $query = $this->db->get();
    return $query->num_rows();
  }

  public function count_all()
  {
    $this->db->from($this->table);
    return $this->db->count_all_results();
  }


  function get_by_id($id)
  {
    //$this->db->join('unit_batubara_kirim', 'unit_batubara_kirim.id_unit_batubara_kirim = form_batubara_kirim.id_unit_batubara_kirim');
    $this->db->join('stockpile_batubara_masuk', 'stockpile_batubara_masuk.id_stockpile_batubara_masuk = form_batubara_kirim.id_asal_batubara_kirim');
    $this->db->join('tujuan_batubara_kirim', 'tujuan_batubara_kirim.id_tujuan_batubara_kirim = form_batubara_kirim.id_tujuan_batubara_kirim');
    $this->db->join('kode_batubara_kirim', 'kode_batubara_kirim.id_kode_batubara_kirim = form_batubara_kirim.id_kode_batubara_kirim');
    $this->db->join('checker', 'checker.id_checker = form_batubara_kirim.id_checker');

    $this->db->join('checker_batubara_kirim', 'checker_batubara_kirim.id_checker_batubara_kirim = form_batubara_kirim.id_checker_batubara_kirim');
    $this->db->where($this->id, $id);
    return $this->db->get($this->table)->row();
  }


  // get total rows
  function total_rows()
  {
    return $this->db->get($this->table)->num_rows();
  }

  // insert data
  function insert($data)
  {
    $this->db->insert($this->table, $data);
    return $this->db->insert_id();
  }

  function insert_cek_list($data)
  {
    $this->db->insert('form_batubara_kirim_cek_list', $data);
    //    return $this->db->insert_id();
  }


  // update data
  function update($id, $data)
  {
    $this->db->where($this->id, $id);
    $this->db->update($this->table, $data);
  }

  // delete data
  function delete($id)
  {
    $this->db->where($this->id, $id);
    $this->db->delete($this->table);
  }
  // delete data
  function delete_cek_list($id)
  {
    $this->db->where('id_batubara_kirim', $id);
    $this->db->delete('form_batubara_kirim_cek_list');
  }


  function get_all()
  {

    $this->db->join('stockpile_batubara_masuk', 'stockpile_batubara_masuk.id_stockpile_batubara_masuk = form_batubara_kirim.id_asal_batubara_kirim');
    $this->db->join('tujuan_batubara_kirim', 'tujuan_batubara_kirim.id_tujuan_batubara_kirim = form_batubara_kirim.id_tujuan_batubara_kirim');
    $this->db->join('kode_batubara_kirim', 'kode_batubara_kirim.id_kode_batubara_kirim = form_batubara_kirim.id_kode_batubara_kirim');
    $this->db->join('checker', 'checker.id_checker = form_batubara_kirim.id_checker');

    $this->db->join('checker_batubara_kirim', 'checker_batubara_kirim.id_checker_batubara_kirim = form_batubara_kirim.id_checker_batubara_kirim');


    $this->db->order_by('tanggal_batubara_kirim', 'DESC');
    $this->db->order_by($this->id, 'DESC');
    return $this->db->get($this->table)->result();
  }

  function get_dropdown_form_batubara_kirim()
  {
    return $this->db->get($this->table)->result();
  }

  function count_check($id_item_cek_list_batubara_kirim, $id_batubara_kirim)
  {
    $this->db->where('id_batubara_kirim', $id_batubara_kirim);
    $this->db->where('id_item_cek_list_batubara_kirim', $id_item_cek_list_batubara_kirim);
    $this->db->from('form_batubara_kirim_cek_list');
    return $this->db->count_all_results();
  }

  function get_data_report($tanggal_batubara_awal, $tanggal_batubara_akhir)
  {
    $this->db->where('tanggal_batubara_kirim <=', $tanggal_batubara_akhir);
    $this->db->where('tanggal_batubara_kirim >=', $tanggal_batubara_awal);
    //$this->db->join('unit_batubara_kirim', 'unit_batubara_kirim.id_unit_batubara_kirim = form_batubara_kirim.id_unit_batubara_kirim');
    $this->db->join('kode_batubara_kirim', 'kode_batubara_kirim.id_kode_batubara_kirim = form_batubara_kirim.id_kode_batubara_kirim');
    $this->db->join('stockpile_batubara_masuk', 'stockpile_batubara_masuk.id_stockpile_batubara_masuk = form_batubara_kirim.id_asal_batubara_kirim');
    $this->db->join('tujuan_batubara_kirim', 'tujuan_batubara_kirim.id_tujuan_batubara_kirim = form_batubara_kirim.id_tujuan_batubara_kirim');
    $this->db->order_by('tanggal_batubara_kirim', 'ASC');
    $this->db->order_by('jam_batubara_kirim', 'ASC');
    return $this->db->get($this->table)->result();
  }
  
  function get_data_report_kode($tanggal_batubara_awal, $tanggal_batubara_akhir, $id_kode_batubara_kirim)
  {
    $this->db->where('tanggal_batubara_kirim <=', $tanggal_batubara_akhir);
    $this->db->where('tanggal_batubara_kirim >=', $tanggal_batubara_awal);
    $this->db->where('form_batubara_kirim.id_kode_batubara_kirim', $id_kode_batubara_kirim);
    $this->db->join('kode_batubara_kirim', 'kode_batubara_kirim.id_kode_batubara_kirim = form_batubara_kirim.id_kode_batubara_kirim');
    $this->db->join('stockpile_batubara_masuk', 'stockpile_batubara_masuk.id_stockpile_batubara_masuk = form_batubara_kirim.id_asal_batubara_kirim');
    $this->db->join('tujuan_batubara_kirim', 'tujuan_batubara_kirim.id_tujuan_batubara_kirim = form_batubara_kirim.id_tujuan_batubara_kirim');
    $this->db->order_by('tanggal_batubara_kirim', 'ASC');
    $this->db->order_by('jam_batubara_kirim', 'ASC');
    return $this->db->get($this->table)->result();
  }

  function get_nomor_max($tanggal)
  {
    $this->db->select_max('nomor_urut_batubara_kirim');
    $this->db->where('tanggal_batubara_kirim', $tanggal);
    return $this->db->get($this->table)->row();
  }


  // get total rows
  function total_rows_by_date($tgl_awal, $tgl_akhir)
  {
    $this->db->from($this->table);
    $array = array('tanggal_batubara_kirim >=' => $tgl_awal, 'tanggal_batubara_kirim <=' => $tgl_akhir);
    $this->db->where($array);
    return $this->db->get()->num_rows();
  }


  public function get_data_table_by_id($id, $tgl_awal, $tgl_akhir)
  {
    $this->db->select('bruto_batubara_kirim as bruto, tarra_batubara_kirim as tarra');
    $this->db->from($this->table);
    $array = array('id_asal_batubara_kirim' => $id, 'tanggal_batubara_kirim >=' => $tgl_awal, 'tanggal_batubara_kirim <=' => $tgl_akhir);
    $this->db->where($array);
    $this->db->where_in('approve', 1);
    return $this->db->get()->result_array();
  }

  public function sum_tonase_by_table($tgl_awal, $tgl_akhir)
  {
    $this->db->select('SUM(bruto_batubara_kirim - tarra_batubara_kirim) as total_tonase');
    $this->db->from($this->table);
    $array = array('tanggal_batubara_kirim >=' => $tgl_awal, 'tanggal_batubara_kirim <=' => $tgl_akhir);
    $this->db->where($array);
    $this->db->where_in('approve', 1);
    return $this->db->get()->row_array();
  }

  public function get_row_table_by_id($table, $fieldname, $id, $tgl_awal, $tgl_akhir)
  {
    $this->db->from($table);
    $array = array($fieldname => $id, 'tanggal_batubara_kirim >=' => $tgl_awal, 'tanggal_batubara_kirim <=' => $tgl_akhir);
    $this->db->where($array);
    $this->db->where_in('approve', 1);
    return $this->db->count_all_results();
  }
}
