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

class M_sub_margo extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function tb_scan_in($tgl)
    {
        return $this->db->query("SELECT C.barang,COUNT(A.no_lot) AS qty_out
        FROM tb_mutasi A
        LEFT JOIN (SELECT kode_fg,sku FROM tb_fg_gudang) B ON B.kode_fg = A.no_lot 
        LEFT JOIN (SELECT sku,barang FROM tb_fg) C ON C.sku = B.sku
        WHERE DATE(A.scan_time) = '$tgl' AND A.id_gudang = 1
        GROUP BY C.barang;")->result_array();
    }

    public function available_truck()
    {
        return $this->db->query("SELECT A.id_kendaraan,A.no_pol,B.kendaraan_kirim 
        FROM tb_kendaraan A
        LEFT JOIN(SELECT DISTINCT id_kendaraan as kendaraan_kirim FROM tb_ritase WHERE tgl_kembali IS NULL)B on B.kendaraan_kirim = A.id_kendaraan
        WHERE B.kendaraan_kirim IS NULL and A.lokasi = 'KL';")->result_array();
    }

    public function available_driver()
    {
        return $this->db->query("SELECT A.emp_id,A.FullName,B.id_sopir
        FROM employee A
        LEFT JOIN(
            SELECT DISTINCT id_sopir FROM tb_ritase WHERE tgl_kembali IS NULL
        )B on B.id_sopir = A.emp_id
        LEFT JOIN(SELECT emp_id,unit FROM tb_emp_unit)C ON C.emp_id = A.emp_id
        WHERE A.strJobPosition = 'SOPIR' AND C.unit = 'KL' AND B.id_sopir IS NULL;")->result_array();
    }

    public function tb_pengiriman($s, $e)
    {
        return $this->db->query("SELECT 
        A.id_rit,
        A.id_kendaraan,
        B.no_pol,
        A.id_sopir,
        C.sopir,
        A.tgl_pergi,
        DATE_FORMAT(A.tgl_pergi, '%d %b %Y') as tanggal_pergi,
        A.tgl_kembali,
        DATE_FORMAT(A.tgl_kembali, '%d %b %Y') as tanggal_kembali,
        A.km_pergi,
        A.km_kembali,
        (IFNULL(A.km_kembali,0)-IFNULL(A.km_pergi,0)) as jarak_tempuh,        
        A.keterangan,
        A.cr_id,        
        D.cr_name,
        A.cr_date,
        A.sts_pengiriman
        FROM tb_ritase A
        LEFT JOIN tb_kendaraan B on B.id_kendaraan = A.id_kendaraan
        LEFT JOIN (SELECT emp_id,Fullname as sopir FROM employee WHERE strJobPosition = 'SOPIR') C on C.emp_id = A.id_sopir
        LEFT JOIN (SELECT emp_id,Fullname as cr_name FROM employee) D on D.emp_id = A.cr_id
        WHERE DATE_FORMAT(A.tgl_pergi,'%Y-%m-%d') BETWEEN '$s' and '$e' and A.id_kendaraan IS NOT NULL and A.unit = 'MR';")->result_array();
    }

    public function tb_expedisi($s, $e)
    {
        return $this->db->query("SELECT * FROM(
        SELECT 
        id_rit, 
        no_pol,
        tgl_pergi,
        DATE_FORMAT(tgl_pergi, '%d %b %Y') AS tanggal_pergi,
        no_resi,
        customer,
        keterangan,
        sts_pengiriman,
        unit
        FROM tb_ritase 
        WHERE no_pol IS NOT NULL OR expedisi IS NOT NULL 
        AND sts_pengiriman = 0 ORDER BY tgl_pergi,no_pol
        ) AS tb_exp WHERE DATE(tgl_pergi) BETWEEN '$s' AND '$e' AND unit = 'KL';")->result_array();
    }

    public function tb_sj($rit)
    {
        return $this->db->query("SELECT
        A.doc_sj,
        A.id_rit,
        A.no_so,
        A.id_customer,
        CASE WHEN A.no_sj ='' then A.doc_sj else A.no_sj end no_sj,
        B.id_kota,
        B.kota,
        A.dk_lk,
        A.cr_id,
        A.cr_date,
        A.keterangan 
        FROM tb_sj A
        LEFT JOIN tb_kota B on B.id_kota = A.kota
        WHERE A.id_rit = $rit;")->result_array();
    }

    public function hitung_fg_nopol($sj)
    {
        return $this->db->query("SELECT B.sku,C.barang,COUNT(A.no_lot) AS hitung
        FROM tb_mutasi A
        LEFT JOIN(SELECT kode_fg,sku FROM tb_fg_gudang)B ON B.kode_fg = A.no_lot
        LEFT JOIN(SELECT sku,barang FROM tb_fg)C ON C.sku = B.sku
        WHERE A.sj_kirim = $sj GROUP BY B.sku,C.barang;")->result_array();
    }

    public function get_available_sj($sj)
    {
        return $this->db->query("SELECT B.doc_sj,CASE WHEN B.no_sj ='' then B.doc_sj else B.no_sj end no_sj
        FROM tb_ritase A
        LEFT JOIN tb_sj B on B.id_rit = A.id_rit
        WHERE A.sts_pengiriman = 0 and B.doc_sj not in($sj) and A.unit = 'MR';")->result_array();
    }

    public function loading_kirim()
    {
        return $this->db->query("SELECT A.doc_sj,
        CASE WHEN A.no_sj ='' then A.doc_sj else A.no_sj end no_sj,
        B.kota,A.dk_lk,
        A.id_customer,
        C.sts_pengiriman,
        CASE WHEN D.no_pol IS NULL then C.no_pol else D.no_pol end no_pol,
        D.id_kendaraan,
        CASE WHEN E.FullName IS NULL then 'EXPEDISI' else E.FullName end sopir,
        C.no_pol as no_pol_exp,
        C.no_resi
        FROM tb_sj A
        LEFT JOIN tb_kota B on B.id_kota = A.kota
        LEFT JOIN tb_ritase C on C.id_rit = A.id_rit 
        LEFT JOIN tb_kendaraan D on D.id_kendaraan = C.id_kendaraan
        LEFT JOIN employee E on E.emp_id = C.id_sopir
        WHERE C.sts_pengiriman = 0 and C.unit = 'KL';")->result_array();
    }

    public function hapus_sj($sj, $sku)
    {
        $this->db->query("UPDATE tb_mutasi A
        JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
        SET A.sj_kirim = NULL,A.scan_out = NULL
        WHERE A.sj_kirim = $sj AND B.sku = '$sku';");
    }

    public function hapus_sj_all($id)
    {
        $this->db->query("UPDATE tb_mutasi A
        LEFT JOIN tb_sj B ON B.doc_sj = A.sj_kirim
        LEFT JOIN tb_ritase C ON C.id_rit = B.id_rit
        SET A.sj_kirim = NULL,A.scan_out = NULL
        WHERE C.id_rit = $id;");
    }

    public function tb_recv_daily($tgl)
    {
        return $this->db->query("SELECT DATE(A.scan_time) as tgl,B.sku,C.barang,COUNT(A.no_lot) AS qty
        FROM tb_mutasi A
        LEFT JOIN (SELECT kode_fg,sku FROM tb_fg_gudang) B ON B.kode_fg = A.no_lot 
        LEFT JOIN (SELECT sku,barang FROM tb_fg) C ON C.sku = B.sku
        WHERE DATE(A.scan_time) = '$tgl' AND A.gudang_asal = 1 GROUP BY DATE(A.scan_time),B.sku,C.barang;")->result_array();
    }

    public function recv_user($tgl, $sku)
    {
        return $this->db->query("SELECT C.nama,B.sku,COUNT(A.no_lot) AS qty
        FROM tb_mutasi A
        LEFT JOIN (SELECT kode_fg,sku FROM tb_fg_gudang) B ON B.kode_fg = A.no_lot 
        LEFT JOIN (SELECT emp_id,FullName AS nama FROM employee) C ON C.emp_id = A.id_user
        WHERE DATE(A.scan_time) = '$tgl' AND B.sku = '$sku' AND A.gudang_asal = 1
        GROUP BY C.nama,B.sku;")->result_array();
    }
}
