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

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

    public function scan_fg($fg)
    {
        return $this->db->query("select count(lot) as cari from(
        select concat(shift,date_format(tgl,'%y'),date_format(tgl,'%m'),date_format(tgl,'%d'),LPAD(kd_mesin, 2, '0')) as lot
        from tb_lot) as tb_scan
        where tb_scan.lot in (select left('$fg',9));")->row_array();
    }

    public function cari_fg_gudang($fg)
    {
        return $this->db->query("select count(kode_fg) as cari_bj_gudang from tb_fg_gudang where kode_fg = $fg;")->row_array();
    }

    public function cari_fg_gudang2($fg)
    {
        return $this->db->query("select count(kode_fg) as cari_bj_gudang from tb_fg_gudang where kode_fg = '$fg' and tgl_diterima is not null;")->row_array();
    }

    public function fg_diterima_gudang($fg)
    {
        $this->db->query("update tb_fg_gudang set tgl_diterima = now() where kode_fg = " . $fg . ";");
    }

    public function fg_list()
    {
        return $this->db->query("select 
        row_number() over (order by A.tgl_diterima desc) as nmr,
        A.sku,
        B.barang,
        A.kode_fg,
        date_format(A.tgl_diterima,'%d %b %Y') as tgl,
        date_format(A.tgl_diterima,'%T') as jam
        from tb_fg_gudang A
        left join tb_fg B on B.sku = A.sku
        where date_format(A.tgl_diterima,'%Y-%m-%d') = current_date();")->result_array();
    }

    public function laporan_penerimaan_fg($mulai, $hingga)
    {
        return $this->db->query("select A.kode_fg,A.id_produksi,A.sku,B.barang,
        date_format(A.tgl_transfer,'%d %b %Y') as tgl_kirim,A.tgl_diterima,
        date_format(A.tgl_diterima,'%d %b %Y') as tgl_scan
        from tb_fg_gudang A
        Left join tb_fg B on B.sku = A.sku 
        where date_format(A.tgl_transfer,'%Y-%m-%d') between '$mulai' and '$hingga' order by A.kode_fg;")->result_array();
    }

    public function cek_pengiriman($kendaraan, $no_sj)
    {
        return $this->db->query("select A.kode_fg,A.sku,B.barang,C.no_pol,C.merk,
        date_format(A.tgl_kirim,'%d %b %Y') as tgl_kirim,
        date_format(A.tgl_kirim,'%H:%m:%s') as jam_kirim 
        from tb_fg_gudang A
        Left join tb_fg B on B.sku = A.sku
        left join tb_kendaraan C on C.id_kendaraan = A.id_kendaraan
        where A.id_kendaraan = $kendaraan and A.no_sj = '$no_sj' order by A.tgl_kirim;")->result_array();
    }

    public function stok_gudang($mulai, $hingga)
    {
        if ($mulai === '' && $hingga === '') {
            $query = "select A.sku,
            A.barang as barang_diterima,
            A.warna,ifnull(B.qty_terima,0) as qty_diterima,
            ifnull(C.qty_kirim,0) as qty_kirim
            ,(ifnull(B.qty_terima,0) - ifnull(C.qty_kirim,0)) as stok
            from tb_fg A
            Left join(SELECT A.sku,count(A.sku) as qty_terima,B.barang as barang_diterima
                        FROM tb_fg_gudang A
                        LEFT JOIN tb_fg B ON B.sku = A.sku        
                        where A.tgl_diterima is not null
                        GROUP BY A.sku ORDER BY A.sku
            )B on B.sku = A.sku
            Left join(
            SELECT A.sku,count(A.sku) as qty_kirim,B.barang as barang_dikirim
            FROM tb_fg_gudang A
            LEFT JOIN tb_fg B ON B.sku = A.sku        
            where A.tgl_kirim is not null
            GROUP BY A.sku ORDER BY A.sku
            )C on C.sku = A.sku
            where B.qty_terima is not null;";
        } else {
            $query = "SELECT 
            A.sku,
            B.barang as barang_diterima,
            count(A.sku) as qty_diterima,
            ifnull(C.qty_kirim,0) as qty_kirim,
            (count(A.sku) - ifnull(C.qty_kirim,0)) as stok
            FROM tb_fg_gudang A
            LEFT JOIN tb_fg B ON B.sku = A.sku
            LEFT JOIN (
                SELECT 
                    A.sku,
                    count(A.sku) as qty_kirim,
                    B.barang as barang_dikirim
                FROM tb_fg_gudang A
                LEFT JOIN tb_fg B ON B.sku = A.sku
                WHERE date_format(A.tgl_kirim, '%Y-%m-%d') BETWEEN '$mulai' AND '$hingga'
                GROUP BY A.sku
                ORDER BY A.sku
            ) C ON C.sku = A.sku
            WHERE date_format(A.tgl_diterima, '%Y-%m-%d') BETWEEN '$mulai' AND '$hingga'
            GROUP BY A.sku ORDER BY A.sku";
        }
        return $this->db->query($query)->result_array();
    }

    public function trace_stok($kd, $gudang)
    {
        return $this->db->query("SELECT sku,tanggal,tgl,qty_in,qty_out,stok 
       FROM (
       SELECT DISTINCT tb_stok.sku,tb_stok.tgl AS tanggal,DATE_FORMAT(tb_stok.tgl, '%d %b %Y') as tgl,IFNULL(B.qty_in,0) as qty_in,IFNULL(C.qty_out,0) as qty_out,D.stok
        FROM(
        SELECT sku,DATE(tgl_diterima) as tgl
        FROM tb_fg_gudang WHERE sku = '$kd' and tgl_diterima IS NOT NULL AND id_gudang = $gudang
        GROUP BY DATE(tgl_diterima)
        UNION ALL
        SELECT sku,DATE(tgl_kirim) as tgl
        FROM tb_fg_gudang WHERE sku = '$kd' and tgl_kirim IS NOT NULL AND id_gudang = $gudang
        GROUP BY DATE(tgl_kirim)
        ) as tb_stok
        LEFT JOIN(SELECT sku,DATE(tgl_diterima) as tgl,
            count(sku) as qty_in
            FROM tb_fg_gudang WHERE sku = '$kd' and tgl_diterima IS NOT NULL AND id_gudang = $gudang
            GROUP BY DATE(tgl_diterima)
        )B on B.sku = tb_stok.sku and B.tgl = tb_stok.tgl
        LEFT JOIN(SELECT sku,DATE(tgl_kirim) as tgl,
            count(sku) as qty_out
            FROM tb_fg_gudang WHERE sku = '$kd' and tgl_kirim IS NOT NULL AND id_gudang = $gudang
            GROUP BY DATE(tgl_kirim)
        )C on C.sku = tb_stok.sku and C.tgl = tb_stok.tgl
        LEFT JOIN(
            SELECT DISTINCT sku,tgl,
            (IFNULL(sum(qty_in) over (ORDER BY tgl),0) - IFNULL(sum(qty_out) over (ORDER BY tgl),0)) as stok 
            FROM(
                SELECT sku,
                tgl,
                CASE WHEN ket = 'in' then qty end qty_in,
                CASE WHEN ket = 'out' then qty end qty_out
                FROM(
                    SELECT sku,DATE(tgl_diterima) as tgl,count(sku) as qty
                    ,'in' as ket
                    FROM tb_fg_gudang 
                    WHERE sku = '$kd' and tgl_diterima IS NOT NULL AND id_gudang = $gudang
                    GROUP BY DATE(tgl_diterima)
                    UNION ALL
                    SELECT sku,DATE(tgl_kirim) as tgl,count(sku) as qty
                    ,'out' as ket
                    FROM tb_fg_gudang 
                    WHERE sku = '$kd' and tgl_kirim IS NOT NULL AND id_gudang = $gudang
                    GROUP BY DATE(tgl_kirim)
                ) as tb_stok ORDER BY tgl
            ) as tb_trace ORDER BY tgl
        )D on D.sku = tb_stok.sku and D.tgl = tb_stok.tgl        
        UNION ALL
        SELECT 
            DISTINCT tb_sku.sku,
            tb_sku.tgl AS tanggal,
            DATE_FORMAT(tb_sku.tgl, '%d %b %Y') as tgl,
            IFNULL(B.qty_in,0) AS qty_in,
            IFNULL(C.qty_out,0) AS qty_out,
            SUM(IFNULL(B.qty_in,0)-IFNULL(C.qty_out,0)) OVER (ORDER BY tb_sku.tgl) AS stok
            FROM (
                SELECT B.sku,DATE(A.scan_time) AS tgl
                FROM tb_scan_mutasi A
                LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
                WHERE B.sku = '$kd' AND A.gudang_asal = $gudang
                UNION ALL 
                SELECT B.sku,DATE(A.scan_out) AS tgl
                FROM tb_scan_mutasi A
                LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
                WHERE A.scan_out IS NOT NULL AND B.sku = '$kd' AND A.gudang_asal = $gudang
            ) AS tb_sku 
            LEFT JOIN(
                SELECT B.sku,A.scan_time AS tgl,COUNT(A.no_lot) AS qty_in
                FROM (SELECT no_lot,DATE(scan_time) AS scan_time FROM tb_scan_mutasi WHERE gudang_asal = $gudang) AS A
                LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
                WHERE B.sku = '$kd' 
                GROUP BY B.sku,A.scan_time
            )B ON B.tgl = tb_sku.tgl
            LEFT JOIN(
                SELECT B.sku,A.scan_out AS tgl,COUNT(A.no_lot) AS qty_out
                FROM (SELECT no_lot,DATE(scan_out) AS scan_out FROM tb_scan_mutasi WHERE gudang_asal = $gudang) AS A
                LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
                WHERE B.sku = '$kd' AND A.scan_out IS NOT NULL 
                GROUP BY B.sku,A.scan_out
            )C ON C.tgl = tb_sku.tgl
            GROUP BY tb_sku.sku,tb_sku.tgl,B.qty_in,C.qty_out
        ) AS tb_trace ORDER BY tanggal;")->result_array();
    }

    public function monitor_stok($mulai, $hingga, $kode)
    {
       return $this->db->query("SELECT tgl_tr,tgl,doc_tf,qty_tf,qty_in,qty_out,balance,ket,keterangan 
        FROM (
            select date_format(tgl_tr, '%Y-%m-%d') as tgl_tr,tgl,doc_tf,qty_tf,qty_in,qty_out,
                (sum(qty_in) over (order by tgl_tr,doc_tf,ket) -
                sum(qty_out) over (order by tgl_tr,doc_tf,ket)) as balance,ket,keterangan
                from ( 
                    select tgl_tr,tgl,doc_tf,count(kode_fg) as qty_tf,qty_in,qty_out,ket,keterangan
                        from(
                            select A.kode_fg,DATE_FORMAT(A.tgl_transfer,'%Y-%m-%d') as tgl_tr,date_format(A.tgl_transfer,'%d %b %Y') as tgl,
                            ifnull(A.doc_tf,concat('IT-',UNIX_TIMESTAMP(DATE_FORMAT(A.tgl_transfer,'%Y-%m-%d')))) as doc_tf
                            ,0 as qty_in,0 as qty_out,'1' as ket,'Hasil Produksi' AS keterangan
                            from tb_fg_gudang A where A.sku = '$kode'
                        ) as tb_tf
                        Group by doc_tf,tgl_tr,tgl,keterangan
                        Union ALL
                        select tgl_tr,tgl,doc_tf,qty_tf,count(kode_fg) as qty_in,qty_out,ket,keterangan 
                        from(
                            select A.kode_fg,DATE_FORMAT(A.tgl_transfer,'%Y-%m-%d') as tgl_tr,date_format(A.tgl_transfer,'%d %b %Y') as tgl,
                            ifnull(A.doc_tf,concat('IT-',UNIX_TIMESTAMP(DATE_FORMAT(A.tgl_transfer,'%Y-%m-%d')))) as doc_tf
                            ,0 as qty_tf,0 as qty_out,'2' as ket,'Item Transfer IN' AS keterangan
                            from tb_fg_gudang A where A.sku = '$kode' and A.tgl_diterima is not null
                        ) as tb_in
                        Group by doc_tf,tgl_tr,tgl,keterangan
                        Union ALL
                        select tgl_tr,tgl,doc_tf,qty_tf,qty_in,count(kode_fg) as qty_out,ket,keterangan 
                        from(
                        SELECT DATE_FORMAT(A.tgl_kirim,'%Y-%m-%d') as tgl_tr,date_format(A.tgl_kirim,'%d %b %Y') as tgl,
                        A.no_sj as doc_tf,
                        0 as qty_tf,0 as qty_in,A.kode_fg,'3' as ket,'Delivery' AS keterangan
                        from tb_fg_gudang A
                        where A.sku = '$kode' and A.no_sj is not null
                        ) as tb_out
                        group by tgl_tr,tgl,doc_tf,keterangan
                        Union All
                        select tgl,tgl_tr,doc_tf,(count(no_lot)*-1) as qty_koreksi,0 as qty_in,0 as qty_out,'4' as ket,keterangan
                        from(
                            select no_lot,date_format(tgl, '%d %b %Y') as tgl,'Koreksi' as doc_tf,
                            date_format(tgl, '%Y-%m-%d') as tgl_tr,
                            0 as qty_in,0 as qty_out,'4' as ket,'Adjustment IN' AS keterangan
                            from tb_void_lot where sts = 2 and sku = '$kode'
                        ) as tb_void_lot
                        group by tgl,tgl_tr,keterangan
                ) as tb_stok
                group by tgl_tr,tgl,doc_tf,qty_tf,qty_in,qty_out,ket,keterangan 
            order by sum(qty_in) over (order by tgl_tr,doc_tf,ket),tgl_tr,doc_tf,ket
        ) AS tb_calc_stock WHERE tgl_tr BETWEEN '$mulai' AND '$hingga';")->result_array();
    }

    public function saldo_stok($thn, $bln)
    {
        if ($bln == 1) {
            $blnsebelum = 12;
            $thnsebelum = $thn - 1;
        } else {
            $blnsebelum = $bln - 1;
            $thnsebelum = $thn;
        }
        return $this->db->query("SELECT sku,barang,stok_awal,qty_diterima,qty_kirim,stok_akhir FROM (
        SELECT A.sku,A.barang,IFNULL(B.stok_awal,0) as stok_awal,
                IFNULL(C.qty_diterima,0) as qty_diterima,IFNULL(C.qty_kirim,0) as qty_kirim,
                ((IFNULL(B.stok_awal,0)+IFNULL(C.qty_diterima,0))-IFNULL(C.qty_kirim,0)) as stok_akhir
                FROM tb_fg A
                LEFT JOIN (SELECT 
                        A.sku,
                        (count(A.sku) - IFNULL(C.qty_kirim,0)) as stok_awal
                        FROM tb_fg_gudang A
                        LEFT JOIN (
                            SELECT 
                            A.sku,
                            count(A.sku) as qty_kirim,
                            B.barang as barang_dikirim
                            FROM tb_fg_gudang A
                            LEFT JOIN tb_fg B ON B.sku = A.sku
                            WHERE DATE_FORMAT(A.tgl_kirim, '%Y-%m-%d') <= LAST_DAY(concat($thnsebelum,'-',$blnsebelum,'-01'))
                            GROUP BY A.sku ORDER BY A.sku
                        ) C ON C.sku = A.sku
                        WHERE DATE_FORMAT(A.tgl_diterima, '%Y-%m-%d') <= LAST_DAY(concat($thnsebelum,'-',$blnsebelum,'-01'))
                        GROUP BY A.sku,C.qty_kirim ORDER BY A.sku
                )B on B.sku = A.sku
                LEFT JOIN(
                    SELECT A.sku,
                    IFNULL(B.qty_diterima,0) AS qty_diterima,
                    IFNULL(C.qty_kirim,0) AS qty_kirim,
                    (IFNULL(B.qty_diterima,0)-IFNULL(C.qty_kirim,0)) AS stok_akhir
                    FROM tb_fg A
                    LEFT JOIN (
                    SELECT 
                    A.sku,
                    count(A.sku) as qty_diterima
                    FROM tb_fg_gudang A
                    WHERE DATE(A.tgl_diterima) BETWEEN concat($thn,'-',$bln,'-01') AND LAST_DAY(concat($thn,'-',$bln,'-01'))
                    GROUP BY A.sku
                    ) B ON B.sku = A.sku
                    LEFT JOIN (
                    SELECT 
                    A.sku,
                    count(A.sku) as qty_kirim
                    FROM tb_fg_gudang A
                    WHERE DATE(A.tgl_kirim) BETWEEN concat($thn,'-',$bln,'-01') AND LAST_DAY(concat($thn,'-',$bln,'-01'))
                    GROUP BY A.sku
                    ) C ON C.sku = A.sku
                    WHERE B.qty_diterima IS NOT NULL OR C.qty_kirim IS NOT NULL
                )C on C.sku = A.sku
        ) AS tb_stok WHERE stok_akhir > 0;")->result_array();
    }
    
    public function tb_recv($mulai, $hingga)
    {
        return $this->db->query("SELECT * FROM (
	select distinct 
	ifnull(doc_tf,concat('IT-',UNIX_TIMESTAMP(DATE(A.tgl_transfer)))) as doc_tf,
	DATE(A.tgl_transfer) as tgl,
	A.sku,
	B.barang,
	ifnull(C.IT,0) as IT,
	ifnull(D.Recv,0) as Recv,
	(ifnull(C.IT,0)-ifnull(D.Recv,0)) as balance
	from tb_fg_gudang A
	Left join tb_fg B on B.sku = A.sku
	Left join (
	       SELECT doc_tf_it,sku,count(sku) as IT,tgl from(
		       SELECT ifnull(doc_tf,concat('IT-',UNIX_TIMESTAMP(DATE(tgl_transfer)))) as doc_tf_it
						,sku,DATE(tgl_transfer) as tgl 
						from tb_fg_gudang
	       ) as tb_it where tgl between '$mulai' and '$hingga' group by sku,tgl,doc_tf_it
	)C on C.sku = A.sku and C.tgl = DATE(A.tgl_transfer) AND C.doc_tf_it = IFNULL(A.doc_tf,concat('IT-',UNIX_TIMESTAMP(DATE(A.tgl_transfer))))
	Left Join(
	       SELECT doc_tf_recv,sku,count(kode_fg) as Recv,tgl from(
		   select ifnull(doc_tf,concat('IT-',UNIX_TIMESTAMP(DATE(tgl_transfer)))) as doc_tf_recv,kode_fg,sku,DATE(tgl_transfer) as tgl 
				  from tb_fg_gudang 
				  where tgl_diterima is not null
	       ) as tb_it where tgl between '$mulai' and '$hingga' group by sku,tgl,doc_tf_recv
	)D on D.sku = A.sku and D.tgl = DATE(A.tgl_transfer) AND D.doc_tf_recv = IFNULL(A.doc_tf,concat('IT-',UNIX_TIMESTAMP(DATE(A.tgl_transfer))))
	where DATE(A.tgl_transfer) BETWEEN '$mulai' and '$hingga'
	) AS tb_recv ORDER BY tgl;")->result_array();
    }

    public function cari_sku_opname($lot)
    {
        return $this->db->query("select B.sku,C.barang,count(A.no_lot) as hitung
        from tb_audit A
        Left join tb_fg_gudang B on B.kode_fg = A.no_lot
        Left join tb_fg C on C.sku = B.sku
        where B.sku in(select B.sku 
        from tb_audit A
        left join tb_fg_gudang B on B.kode_fg = A.no_lot
        where A.no_lot = $lot)
        group by B.sku,C.barang;")->row_array();
    }
    
    public function cancel_load($kd)
    {
        return $this->db->query("select count(kode_fg) as kode_fg from tb_fg_gudang where kode_fg = '$kd' and no_sj is not null;")->row_array();
    }

    public function cancel_loading($kd)
    {
        $id = $this->session->id_user;
        $this->db->query("call cancel_load('$kd','$id');");
    }

    public function cancel_report($mulai, $hingga)
    {
        return $this->db->query("select 
        A.no_lot,
        A.tgl_cancel,
        DATE_FORMAT(A.tgl_cancel,'%d %b %Y') as tgl,
        B.no_pol,
        A.no_sj,
        C.FullName as cancel_by
        from tb_cancel_loading A
        Left join tb_kendaraan B on B.id_kendaraan = A.id_kendaraan
        Left join employee C on C.emp_id = A.cancel_by
        where date_format(A.tgl_cancel,'%Y-%m-%d') between '$mulai' and '$hingga'
        order by A.tgl_cancel,A.no_lot;")->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;")->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
        where A.strJobPosition = 'SOPIR' and B.id_sopir IS NULL;")->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 = 'KM';")->result_array();
    }

    public function hitung_fg_nopol($nopol)
    {
        return $this->db->query("select A.sku,B.barang,count(A.sku) as hitung
        from tb_fg_gudang A
        Left join tb_fg B on B.sku = A.sku
        where A.no_sj = '$nopol' group by A.sku,B.barang;")->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 hapus_sj_all($id)
    {
        $this->db->query("UPDATE tb_fg_gudang set tgl_kirim = NULL,no_sj = NULL, id_kendaraan = NULL 
        where no_sj in(select doc_sj from tb_sj where id_rit = $id);");
    }

    public function tb_ritase($s, $e)
    {
        return $this->db->query("select distinct
        B.emp_id,
        B.sopir,
        C.id_kendaraan,
        C.no_pol,
        D.hitung_hari_kerja,
        ifnull((ifnull(C.size_1,0)*ifnull(C.size_2,0)*ifnull(C.size_3,0)),0) as kubikasi,
        E.dk,
        E.lk,
        ifnull((ifnull(E.dk,0) + ifnull(E.lk,0)),0) as total_ritase,
        F.total_sj,
        ifnull(G.fg_kubikasi,0) as fg_kubikasi,
        ifnull(((E.dk + E.lk)/D.hitung_hari_kerja),0) as ritase,
        ifnull(((ifnull(G.fg_kubikasi,0)/(ifnull(C.size_1,0)*ifnull(C.size_2,0)*ifnull(C.size_3,0))/(ifnull(E.dk,0) + ifnull(E.lk,0)))*100),0) as efektif
        from tb_ritase A
        Left join(select emp_id,FullName as sopir from employee where strJobPosition = 'SOPIR')B on B.emp_id = A.id_sopir
        Left join tb_kendaraan C on C.id_kendaraan = A.id_kendaraan
        Left join(select id_sopir,id_kendaraan,count(hari_kerja) as hitung_hari_kerja 
                from(
                select distinct id_sopir,id_kendaraan,date_format(tgl_kembali,'%Y-%m-%d') as hari_kerja
                from tb_ritase where tgl_kembali is not null 
                and date_format(tgl_pergi,'%Y-%m-%d') between '$s' and '$e'
                ) as tb_hari group by id_sopir,id_kendaraan
        )D on D.id_sopir = A.id_sopir and D.id_kendaraan = A.id_kendaraan
        Left join(select A.id_sopir,A.id_kendaraan,count(B.lk) as lk,count(C.dk) as dk
                from tb_ritase A
                Left join(select distinct id_rit,dk_lk as lk from tb_sj where dk_lk = 'LK')B on B.id_rit = A.id_rit
                Left join(select distinct id_rit,dk_lk as dk from tb_sj where dk_lk = 'DK')C on C.id_rit = A.id_rit
                where date_format(A.tgl_pergi,'%Y-%m-%d') between '$s' and '$e'
                and A.tgl_kembali is not null group by A.id_sopir,A.id_kendaraan
        )E on E.id_sopir = A.id_sopir and E.id_kendaraan = A.id_kendaraan
        Left join(select A.id_sopir,A.id_kendaraan,count(doc_sj) as total_sj 
                from tb_ritase A
                Left join tb_sj B on B.id_rit = A.id_rit
                where date_format(A.tgl_pergi,'%Y-%m-%d') between '$s' and '$e'
                and A.tgl_kembali is not null group by A.id_sopir,A.id_kendaraan
        )F on F.id_sopir = A.id_sopir and F.id_kendaraan = A.id_kendaraan
        Left Join(
            select id_sopir,id_kendaraan,sum(fg_kubikasi) as fg_kubikasi 
            from(
                select A.id_sopir,A.id_kendaraan,B.doc_sj,C.sku,count(C.sku) as hitung_sku,
                ifnull(D.fg_kubikasi,0) as kubikasi_brg,
                (count(C.sku)*(ifnull(D.fg_kubikasi,0))/1000000000) as fg_kubikasi
                from tb_ritase A
                Left join tb_sj B on B.id_rit = A.id_rit
                left join tb_fg_gudang C on C.no_sj = B.doc_sj
                left join (select sku,(size_1*size_2*size_3) as fg_kubikasi from tb_fg) D on D.sku = C.sku
                where date_format(A.tgl_pergi,'%Y-%m-%d') between '$s' and '$e'
                and A.tgl_kembali is not null group by A.id_sopir,A.id_kendaraan,B.doc_sj,C.sku
            ) as tb_fg_kubikasi group by id_sopir,id_kendaraan
        )G on G.id_sopir = A.id_sopir and G.id_kendaraan = A.id_kendaraan
        where date_format(A.tgl_pergi,'%Y-%m-%d') between '$s' and '$e'
        and A.tgl_kembali is not null;")->result_array();
    }

    public function tb_do($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.unit = 'KM';")->result_array();
    }
    
    public function tb_do_detail($s, $e, $sopir, $kendaraan)
    {
        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_sopir = '$sopir' and A.id_kendaraan = $kendaraan;")->result_array();
    }
    
    public function tb_pengiriman($s, $e)
    {
        return $this->db->query("select sku,barang,tgl_kirim,count(sku) as qty_kirim,date_format(tgl_kirim, '%d %b %Y') AS tanggal_kirim 
        from(
            select A.sku,B.barang,date(A.tgl_kirim) as tgl_kirim,
            DATE_FORMAT(A.tgl_kirim,'%d %b %Y') as tanggal_kirim
            from tb_fg_gudang A
            Left join tb_fg B on B.sku = A.sku
            where A.tgl_kirim between '$s' and '$e'
        ) as tb_kirim
        group by sku,tgl_kirim,barang order by tgl_kirim,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 = 'KM';")->result_array();
    }
    
    public function get_info_lot($lot)
    {
        return $this->db->query("select A.kode_fg, A.sku,C.barang,
        date_format(A.tgl_transfer, '%d %b %Y') as tgl_transfer,A.doc_tf,
        date_format(A.tgl_diterima, '%d %b %Y') as tgl_diterima,
        date_format(A.tgl_kirim, '%d %b %Y') as tgl_kirim,
        A.no_sj,B.no_pol
        from tb_fg_gudang A
        Left Join tb_kendaraan B on B.id_kendaraan = A.id_kendaraan
        Left join tb_fg C on C.sku =  A.sku
        where A.kode_fg = '$lot';")->result_array();
    }

    public function cancel_no_lot($kd)
    {
        return $this->db->query("select count(no_lot) as no_lot 
        from tb_cancel_loading 
        where no_lot = '$kd';")->row_array();
    }

    public function cancel_no_lot_kirim($kd)
    {
        return $this->db->query("select count(A.id_rit) as id_rit
        from tb_ritase A
        left join tb_sj B on B.id_rit = A.id_rit
        left join tb_fg_gudang C on C.no_sj = B.doc_sj
        where A.sts_pengiriman = 1 and C.kode_fg = '$kd';")->row_array();
    }

    public function tb_expedisi($s, $e)
    {
        return $this->db->query("select 
        id_rit, 
        no_pol,
        tgl_pergi,
        date_format(tgl_pergi, '%d %b %Y') as tanggal_pergi,
        no_resi,
        customer,keterangan,sts_pengiriman
        from tb_ritase 
        where no_pol is not null or expedisi is not null 
        and date(tgl_pergi) between '$s' and '$e'
        and sts_pengiriman = 0 AND unit = 'KM' order by tgl_pergi,no_pol;")->result_array();
    }
    
    
    public function sj_head($sj)
    {
        return $this->db->query("select 
        A.id_customer,
        case when A.no_sj is null then A.doc_sj else A.no_sj end no_sj,
        A.keterangan,
        B.kota,
        date_format(C.tgl_pergi, '%d %b %Y') as tgl_sj
        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
        where A.doc_sj = $sj;")->row_array();
    }

	public function scan_lot($kode)
    {
        return $this->db->query("SELECT 
        A.kode_fg,
        A.sku,
        B.barang,
        date_format(A.tgl_transfer,'%d %b %Y') As tgl_transfer,
        date_format(A.tgl_diterima,'%d %b %Y') AS tgl_diterima,
        TIME(tgl_diterima) AS jam_diterima,
        date_format(A.tgl_kirim,'%d %b %Y') AS tgl_kirim,
        A.id_kendaraan,
        C.no_pol,
        A.no_sj,
        A.doc_tf,
        E.no_sj_retur,
        date_format(E.tgl_retur,'%d %b %Y') AS tgl_retur,
        E.no_pol AS no_pol_retur,
        E.id_customer,
        E.keterangan
        FROM tb_fg_gudang A
        LEFT JOIN tb_fg B ON B.sku = A.sku
        LEFT JOIN tb_kendaraan C ON C.id_kendaraan = A.id_kendaraan
        LEFT JOIN (SELECT no_lot,id_retur FROM tb_retur)D ON D.no_lot = A.kode_fg
        LEFT JOIN (SELECT id_retur,no_sj_retur,no_pol,id_customer,tgl AS tgl_retur,keterangan FROM tb_retur_head)E ON E.id_retur = D.id_retur
        WHERE A.kode_fg = '$kode';")->row_array();
    }

    public function tb_stock_opname()
    {
        return $this->db->query("SELECT B.sku,C.barang,COUNT(A.no_lot) AS hitung
        FROM tb_audit A
        LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
        LEFT JOIN tb_fg C ON C.sku = B.sku
        group by B.sku,C.barang ORDER BY C.barang;")->result_array();
    }

    public function get_sj($s, $e)
    {
        return $this->db->query("SELECT A.doc_sj,A.no_sj,A.keterangan,B.kota,A.dk_lk,
        date_format(C.tgl_pergi,'%d %b %Y') as tgl 
        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
        WHERE DATE(C.tgl_pergi) BETWEEN '$s' AND '$e'
        ORDER BY C.tgl_pergi;")->result_array();
    }

    public function get_detail_sj($doc_sj)
    {
        return $this->db->query("SELECT A.sku,B.barang,COUNT(kode_fg) AS qty
        FROM tb_fg_gudang A
        LEFT JOIN tb_fg B ON B.sku = A.sku
        WHERE A.no_sj = $doc_sj GROUP BY A.sku,B.barang;")->result_array();
    }

    public function get_sj_available()
    {
        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;")->result_array();
    }

    public function sj_tujuan($sj)
    {
        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 A.doc_sj = $sj;")->result_array();
    }

    public function cari_kode_sj($kode, $sj)
    {
        return $this->db->query("SELECT COUNT(kode_fg) AS cari
        FROM tb_fg_gudang WHERE kode_fg = '$kode' and no_sj = $sj;")->row_array();
    }

    public function tb_spb($s, $e, $noso)
    {
        return $this->db->query("SELECT id_spb,tgl_spb,no_so,customer,shipto,remark,qty,qty_s,ifnull(qty_spb,0) as qty_spb
        FROM tb_spb
        WHERE tgl_spb BETWEEN '$s' AND '$e' and no_so in($noso) ORDER BY tgl_spb;")->result_array();
    }

    public function get_so($s, $e)
    {
        return $this->db->query("SELECT distinct no_so FROM tb_spb
        WHERE tgl_spb BETWEEN '$s' AND '$e' ORDER BY no_so;")->result_array();
    }

    public function get_count_penerimaan($lot)
    {
        return $this->db->query("SELECT B.barang,COUNT(A.kode_fg) AS penerimaan
        FROM tb_fg_gudang A
        LEFT JOIN (SELECT sku,barang FROM tb_fg) B ON B.sku = A.sku
        WHERE A.sku IN(
            SELECT sku FROM tb_fg_gudang WHERE kode_fg = '$lot'
        ) AND DATE(A.tgl_diterima) = DATE(NOW()) GROUP BY B.barang;")->row_array();
    }

    public function void_gudang($lot, $usr, $keterangan, $apprv)
    {
        $this->db->query("INSERT INTO tb_void_lot(no_lot,sku,id_produksi,tgl,usr,keterangan,sts,aprv_date,id_apprv) 
        SELECT kode_fg,sku,id_produksi,NOW(),'$usr','$keterangan',2,NOW(),'$apprv'
        FROM tb_fg_gudang WHERE kode_fg IN('$lot');
        DELETE FROM tb_fg_gudang WHERE kode_fg IN('$lot');");
    }
/*
    public function get_gudang($sku, $thn, $bln)
    {
        $id = $this->session->id_user;
        return $this->db->query("SELECT DISTINCT A.sku,A.id_gudang,B.gudang
        FROM tb_fg_gudang A
        LEFT JOIN tb_gudang B ON B.id_gudang COLLATE utf8mb4_general_ci = A.id_gudang
        WHERE A.sku COLLATE utf8mb4_general_ci = '$sku' and B.lokasi COLLATE utf8mb4_general_ci in(select unit FROM tb_emp_unit WHERE emp_id COLLATE utf8mb4_general_ci = '$id')
        AND A.tgl_diterima COLLATE utf8mb4_general_ci <= LAST_DAY(concat($thn,'-',$bln,'-01'))
        ORDER BY A.sku,A.id_gudang;")->result_array();
    }
*/

    public function get_gudang($sku, $thn, $bln)
    {
        $id = $this->session->id_user;
        return $this->db->query("SELECT DISTINCT A.sku,A.id_gudang,B.gudang
        FROM tb_fg_gudang A
        LEFT JOIN tb_gudang B ON B.id_gudang COLLATE utf8mb4_general_ci = A.id_gudang
        WHERE A.sku COLLATE utf8mb4_general_ci = '$sku' and B.lokasi COLLATE utf8mb4_general_ci in(select unit FROM tb_emp_unit WHERE emp_id COLLATE utf8mb4_general_ci = '$id')
        AND A.tgl_diterima COLLATE utf8mb4_general_ci <= LAST_DAY(concat($thn,'-',$bln,'-01'))
        UNION All
        SELECT DISTINCT B.sku,A.gudang_asal,C.gudang
        FROM tb_scan_mutasi A
        LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
        LEFT JOIN tb_gudang C ON C.id_gudang = A.gudang_asal
        WHERE DATE(A.scan_time) COLLATE utf8mb4_general_ci <= LAST_DAY(concat($thn,'-',$bln,'-01')) AND B.sku = '$sku'
        AND C.lokasi COLLATE utf8mb4_general_ci in(select unit FROM tb_emp_unit WHERE emp_id = '$id');")->result_array();
    }

    public function saldo_stok2($thn, $bln, $sku, $gudang)
    {
        if ($bln == 1) {
            $blnsebelum = 12;
            $thnsebelum = $thn - 1;
        } else {
            $blnsebelum = $bln - 1;
            $thnsebelum = $thn;
        }

        return $this->db->query("SELECT sku,
        SUM(stok_awal) AS stok_awal,
        SUM(qty_diterima) AS qty_diterima,
        SUM(qty_kirim) AS qty_kirim,
        SUM(stok_akhir) AS stok_akhir
        FROM(
            SELECT sku,stok_awal,qty_diterima,qty_kirim,stok_akhir FROM (
                    SELECT A.sku,A.barang,IFNULL(B.stok_awal,0) as stok_awal,
                            IFNULL(C.qty_diterima,0) as qty_diterima,IFNULL(C.qty_kirim,0) as qty_kirim,
                            ((IFNULL(B.stok_awal,0)+IFNULL(C.qty_diterima,0))-IFNULL(C.qty_kirim,0)) as stok_akhir
                            FROM tb_fg A
                            LEFT JOIN(SELECT 
                                    A.sku,
                                    (count(A.sku) - IFNULL(C.qty_kirim,0)) as stok_awal
                                    FROM tb_fg_gudang A
                                    LEFT JOIN (
                                        SELECT 
                                        A.sku,
                                        count(A.sku) as qty_kirim,
                                        B.barang as barang_dikirim
                                        FROM tb_fg_gudang A
                                        LEFT JOIN tb_fg B ON B.sku = A.sku
                                        WHERE DATE_FORMAT(A.tgl_kirim, '%Y-%m-%d') <= LAST_DAY(concat($thnsebelum,'-',$blnsebelum,'-01')) AND id_gudang = $gudang AND A.sku = '$sku'
                                        GROUP BY A.sku ORDER BY A.sku
                                    ) C ON C.sku = A.sku
                                    WHERE DATE_FORMAT(A.tgl_diterima, '%Y-%m-%d') <= LAST_DAY(concat($thnsebelum,'-',$blnsebelum,'-01')) AND id_gudang = $gudang AND A.sku = '$sku'
                                    GROUP BY A.sku,C.qty_kirim ORDER BY A.sku
                            )B on B.sku = A.sku
                            LEFT JOIN(
                                SELECT A.sku,
                                IFNULL(B.qty_diterima,0) AS qty_diterima,
                                IFNULL(C.qty_kirim,0) AS qty_kirim,
                                (IFNULL(B.qty_diterima,0)-IFNULL(C.qty_kirim,0)) AS stok_akhir
                                FROM tb_fg A
                                LEFT JOIN (
                                    SELECT 
                                    A.sku,
                                    count(A.sku) as qty_diterima
                                    FROM tb_fg_gudang A
                                    WHERE DATE(A.tgl_diterima) BETWEEN concat($thn,'-',$bln,'-01') AND LAST_DAY(concat($thn,'-',$bln,'-01')) AND id_gudang = $gudang AND A.sku = '$sku'
                                    GROUP BY A.sku
                                ) B ON B.sku = A.sku
                                LEFT JOIN (
                                    SELECT 
                                    A.sku,
                                    count(A.sku) as qty_kirim
                                    FROM tb_fg_gudang A
                                    WHERE DATE(A.tgl_kirim) BETWEEN concat($thn,'-',$bln,'-01') AND LAST_DAY(concat($thn,'-',$bln,'-01')) AND id_gudang = $gudang AND A.sku = '$sku'
                                    GROUP BY A.sku
                                ) C ON C.sku = A.sku
                                WHERE B.qty_diterima IS NOT NULL OR C.qty_kirim IS NOT NULL
                            )C on C.sku = A.sku
                    ) AS tb_stok WHERE sku = '$sku'
            UNION ALL 
            SELECT A.sku,
            IFNULL(B.stok_awal,0) AS stok_awal,
            IFNULL(C.qty_diterima,0) AS qty_diterima,
            IFNULL(D.qty_kirim,0) AS qty_kirim,
	    ((IFNULL(B.stok_awal,0)+IFNULL(C.qty_diterima,0))-IFNULL(D.qty_kirim,0)) AS stok_akhir
            FROM tb_fg A
            LEFT JOIN (SELECT B.sku,(COUNT(A.no_lot)-IFNULL(C.stok_keluar,0)) AS stok_awal
                        FROM tb_scan_mutasi A
                        LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
                        LEFT JOIN(
                            SELECT B.sku,COUNT(A.no_lot) AS stok_keluar
                            FROM tb_scan_mutasi A
                            LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
                            WHERE DATE(A.scan_out) <= LAST_DAY(concat($thnsebelum,'-',$blnsebelum,'-01')) AND B.sku = '$sku' AND A.gudang_asal = $gudang
                            GROUP BY B.sku
                        )C ON C.sku = B.sku
                        WHERE DATE(A.scan_time) <= LAST_DAY(concat($thnsebelum,'-',$blnsebelum,'-01')) AND B.sku = '$sku' AND A.gudang_asal = $gudang
                        GROUP BY B.sku,C.stok_keluar
            )B ON B.sku = A.sku
            LEFT JOIN(SELECT B.sku,COUNT(A.no_lot) AS qty_diterima
                            FROM tb_scan_mutasi A
                            LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
                            WHERE DATE(A.scan_time) BETWEEN concat($thn,'-',$bln,'-01') AND LAST_DAY(concat($thn,'-',$bln,'-01')) AND B.sku = '$sku' AND A.gudang_asal = $gudang
                            GROUP BY B.sku
            )C ON C.sku = A.sku
            LEFT JOIN(SELECT B.sku,COUNT(A.no_lot) AS qty_kirim
                            FROM tb_scan_mutasi A
                            LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
                            WHERE DATE(A.scan_out) BETWEEN concat($thn,'-',$bln,'-01') AND LAST_DAY(concat($thn,'-',$bln,'-01')) AND B.sku = '$sku' AND A.gudang_asal = $gudang
                            GROUP BY B.sku
            )D ON D.sku = A.sku
            WHERE A.sku = '$sku'
        ) AS tb_stok_all GROUP BY sku;")->result_array();
    }

    public function trace_stok2($kd)
    {
        return $this->db->query("SELECT DISTINCT tb_stok.sku,tb_stok.tgl AS tanggal,DATE_FORMAT(tb_stok.tgl, '%d %b %Y') as tgl
        FROM(
        SELECT sku,DATE(tgl_diterima) as tgl
        FROM tb_fg_gudang WHERE sku = '$kd' and tgl_diterima IS NOT NULL
        GROUP BY DATE(tgl_diterima)
        UNION ALL
        SELECT sku,DATE(tgl_kirim) as tgl
        FROM tb_fg_gudang WHERE sku = '$kd' and tgl_kirim IS NOT NULL
        GROUP BY DATE(tgl_kirim)
        ) as tb_stok
        ORDER BY tb_stok.tgl;")->result_array();
    }

    public function get_gudang_trace($sku, $tgl)
    {
        return $this->db->query("SELECT distinct id_gudang,gudang from(
        SELECT DISTINCT A.id_gudang,B.gudang
        FROM tb_fg_gudang A
        LEFT JOIN tb_gudang B ON B.id_gudang = A.id_gudang
        WHERE A.sku= '$sku' AND DATE(A.tgl_diterima) = '$tgl'
        Union All
        SELECT DISTINCT A.id_gudang,B.gudang
        FROM tb_fg_gudang A
        LEFT JOIN tb_gudang B ON B.id_gudang = A.id_gudang
        WHERE A.sku= '$sku' AND DATE(A.tgl_kirim) = '$tgl'
        )as tb_gudang order by id_gudang;")->result_array();
    }

    public function get_stok_trace($sku, $gudang, $tgl)
    {
        return $this->db->query("SELECT DISTINCT tb_stok.sku,tb_stok.tgl AS tanggal,IFNULL(B.qty_in,0) as qty_in,IFNULL(C.qty_out,0) as qty_out,D.stok
        FROM(
        SELECT sku,DATE(tgl_diterima) as tgl
        FROM tb_fg_gudang WHERE sku = '$sku' and tgl_diterima IS NOT NULL AND id_gudang = $gudang
        GROUP BY DATE(tgl_diterima)
        UNION ALL
        SELECT sku,DATE(tgl_kirim) as tgl
        FROM tb_fg_gudang WHERE sku = '$sku' and tgl_kirim IS NOT NULL AND id_gudang = $gudang
        GROUP BY DATE(tgl_kirim)
        ) as tb_stok
        LEFT JOIN(SELECT sku,DATE(tgl_diterima) as tgl,
            count(sku) as qty_in
            FROM tb_fg_gudang WHERE sku = '$sku' and tgl_diterima IS NOT NULL AND id_gudang = $gudang
            GROUP BY DATE(tgl_diterima)
        )B on B.sku = tb_stok.sku and B.tgl = tb_stok.tgl
        LEFT JOIN(SELECT sku,DATE(tgl_kirim) as tgl,
            count(sku) as qty_out
            FROM tb_fg_gudang WHERE sku = '$sku' and tgl_kirim IS NOT NULL AND id_gudang = $gudang
            GROUP BY DATE(tgl_kirim)
        )C on C.sku = tb_stok.sku and C.tgl = tb_stok.tgl
        LEFT JOIN(
            SELECT DISTINCT sku,tgl,
            (IFNULL(sum(qty_in) over (ORDER BY tgl),0) - IFNULL(sum(qty_out) over (ORDER BY tgl),0)) as stok 
            FROM(
                SELECT sku,
                tgl,
                CASE WHEN ket = 'in' then qty end qty_in,
                CASE WHEN ket = 'out' then qty end qty_out
                FROM(
                    SELECT sku,DATE(tgl_diterima) as tgl,count(sku) as qty
                    ,'in' as ket
                    FROM tb_fg_gudang 
                    WHERE sku = '$sku' and tgl_diterima IS NOT NULL AND id_gudang = $gudang
                    GROUP BY DATE(tgl_diterima)
                    UNION ALL
                    SELECT sku,DATE(tgl_kirim) as tgl,count(sku) as qty
                    ,'out' as ket
                    FROM tb_fg_gudang 
                    WHERE sku = '$sku' and tgl_kirim IS NOT NULL AND id_gudang = $gudang
                    GROUP BY DATE(tgl_kirim)
                ) as tb_stok ORDER BY tgl
            ) as tb_trace ORDER BY tgl
        )D on D.sku = tb_stok.sku and D.tgl = tb_stok.tgl
        WHERE tb_stok.tgl = '$tgl'
        ORDER BY tb_stok.tgl;")->result_array();
    }

    public function karantina_lot($sku)
    {
        $this->db->query("INSERT INTO tb_karantina_lot
        SELECT 
        A.kode_fg,
        A.id_produksi,
        A.sku,
        A.tgl_transfer,
        A.tgl_diterima,
        A.gudang,
        A.tgl_kirim,
        A.id_kendaraan,
        A.no_sj,
        A.doc_tf,
        A.sts,
        A.id_gudang,
        A.keterangan
        FROM tb_fg_gudang A
        LEFT join tb_audit B ON B.no_lot = A.kode_fg
        WHERE A.sku = '$sku' AND B.no_lot IS NULL;");
    }

    public function adj_stock($sku)
    {
        $this->db->query("DELETE tb_fg_gudang FROM tb_fg_gudang
        LEFT join tb_audit B ON B.no_lot = tb_fg_gudang.kode_fg
        WHERE tb_fg_gudang.sku = '$sku' 
        AND B.no_lot IS NOT NULL 
        AND A.tgl_kirim IS NULL;");
    }

    public function hapus_stock_opname()
    {
        $this->db->query("TRUNCATE table tb_audit;");
    }

    public function get_gudang_trace_grup()
    {
        $id = $this->session->id_user;
        return $this->db->query("SELECT A.id_gudang,A.gudang
        FROM tb_gudang A
        LEFT JOIN tb_emp_unit B ON B.unit COLLATE utf8mb4_general_ci= A.lokasi
        WHERE B.emp_id COLLATE utf8mb4_general_ci= '$id' order by A.id_gudang;")->result_array();
    }

    public function get_brg($tgl)
    {
        return $this->db->query("SELECT A.sku,B.barang,COUNT(A.kode_fg) AS qty
        FROM tb_fg_gudang A
        LEFT JOIN tb_fg B ON B.sku = A.sku
        GROUP BY A.sku,B.barang ORDER BY A.sku;")->result_array();
    }

    public function trace_stok_harian($kd, $gudang, $tgl)
    {
        return $this->db->query("SELECT A.sku,(COUNT(A.tgl_diterima)-COUNT(A.tgl_kirim)) AS stok
        FROM tb_fg_gudang A        
        WHERE A.sku = '$kd' AND DATE(A.tgl_diterima) <= '$tgl'
        AND A.id_gudang = $gudang GROUP BY A.sku
        UNION ALL 
        SELECT B.sku,(COUNT(A.scan_time)-COUNT(A.scan_out)) AS stok
	FROM tb_scan_mutasi A
	LEFT JOIN (SELECT kode_fg,sku FROM tb_fg_gudang) B ON B.kode_fg = A.no_lot        
	WHERE DATE(A.scan_time) <= '$tgl' AND A.gudang_asal = $gudang AND B.sku = '$kd'
	GROUP BY B.sku")->result_array();
    }

    public function tb_fg_gudang($tgl1, $tgl2, $lot)
    {
        return $this->db->query("SELECT 
        A.kode_fg,
        B.tgl,
        B.sku,
        C.barang,
        B.shift,
        B.kd_mesin,
        D.mesin,
        A.id_produksi,
        A.tgl_transfer,
        A.tgl_diterima,
        A.tgl_kirim,
        A.id_kendaraan,
        E.no_pol,
        IFNULL(F.no_sj,A.no_sj) AS no_sj,
        A.doc_tf,
        A.id_gudang,
        G.gudang
        FROM tb_fg_gudang A
        LEFT JOIN tb_lot B ON B.id = A.id_produksi
        LEFT JOIN tb_fg C ON C.sku = B.sku
        LEFT JOIN tb_mesin D ON D.kd_mesin = B.kd_mesin
        LEFT JOIN tb_kendaraan E ON E.id_kendaraan = A.id_kendaraan
        LEFT JOIN tb_sj F ON F.doc_sj = A.no_sj
        LEFT JOIN tb_gudang G ON G.id_gudang = A.id_gudang
        WHERE B.tgl BETWEEN '$tgl1' AND '$tgl2' AND A.kode_fg LIKE '%$lot%'
        ORDER BY A.kode_fg;")->result_array();
    }

    public function tb_scan_cari()
    {
        return $this->db->query("SELECT 
        A.no_lot,
        B.id_produksi,
        B.kode_fg,
        B.sku,
        C.barang,
        D.gudang,
        A.scan_time,
        A.crid
        FROM tb_cari A
        LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
        LEFT JOIN tb_fg C ON C.sku = B.sku
        LEFT JOIN tb_gudang D ON D.id_gudang = B.id_gudang
        ORDER BY A.no_lot;")->result_array();
    }

}
