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

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

    public function tb_lotfg($mulai, $hingga, $gudang)
    {
        return $this->db->query("SELECT IFNULL(CONCAT('PROD-',DATE_FORMAT(A.tgl, '%Y%m%d'),'-', LPAD(A.id, 10, '0')),0) AS nmr_prod,
	A.id,
	A.shift,
	A.tgl,
	DATE_FORMAT(A.tgl,'%d %b %Y') as tanggal,
	A.kd_mesin,
	C.mesin,
	A.nomor,
	A.sku,
	B.barang,
	DATE_FORMAT(E.tgl_transfer,'%d %b %Y') as tgl_transfer,
	IFNULL(D.cari_qty_gudang,0) as cari_gudang,
	/*ifnull(E.doc_tf,A.doc_num) AS doc_tf,*/
	A.sts_cetak,
	IFNULL(A.sts,0) AS sts,
	A.keterangan,
	A.kirim,
	A.void,
	IFNULL(F.cari,0) AS doc_tf,
	H.gudang
	FROM tb_lot A 
	LEFT JOIN tb_fg B on B.sku = A.sku
	LEFT JOIN tb_mesin C on C.kd_mesin = A.kd_mesin
	LEFT JOIN(SELECT id_produksi,count(id_produksi) as cari_qty_gudang from tb_fg_gudang group by id_produksi)D on D.id_produksi = A.id 
	LEFT JOIN(SELECT DISTINCT id_produksi,tgl_transfer,doc_tf  from tb_fg_gudang)E on E.id_produksi = A.id
	LEFT JOIN(SELECT id_produksi,COUNT(id_produksi) AS cari from tb_tf_note GROUP BY id_produksi)F ON F.id_produksi = A.id
	LEFT JOIN(SELECT DISTINCT id_produksi,id_gudang FROM tb_fg_gudang)G ON G.id_produksi = A.id        
	LEFT JOIN tb_gudang H ON H.id_gudang = G.id_gudang
	WHERE A.tgl BETWEEN '$mulai' and '$hingga' 
	AND A.kd_mesin IN(SELECT A.kd_mesin
							FROM tb_mesin A
							WHERE A.kd_loc COLLATE utf8mb4_general_ci
							IN(SELECT lokasi FROM tb_gudang 
							WHERE id_gudang COLLATE utf8mb4_general_ci IN($gudang))); ")->result_array();
    }

/*
    public function tb_lotfg($mulai, $hingga)
    {
        return $this->db->query("SELECT 
	IFNULL(CONCAT('PROD-',DATE_FORMAT(A.tgl, '%Y%m%d'),'-', LPAD(A.id, 10, '0')),0) AS nmr_prod,
        A.id,
        A.shift,
        A.tgl,
        date_format(A.tgl,'%d %b %Y') as tanggal,
        A.kd_mesin,
        C.mesin,
        A.nomor,
        A.sku,
        B.barang,
        date_format(E.tgl_transfer,'%d %b %Y') as tgl_transfer,
        ifnull(D.cari_gudang,0) as cari_gudang,
        ifnull(E.doc_tf,A.doc_num) AS doc_tf,
        A.sts_cetak,
        ifnull(A.sts,0) AS sts,
        A.keterangan,
        A.kirim,
        A.void
        FROM tb_lot A 
        Left join tb_fg B on B.sku = A.sku
        Left join tb_mesin C on C.kd_mesin = A.kd_mesin
        Left join(select id_produksi,count(id_produksi) as cari_gudang from tb_fg_gudang group by id_produksi)D on D.id_produksi = A.id 
        Left Join(select distinct id_produksi,tgl_transfer,doc_tf  from tb_fg_gudang)E on E.id_produksi = A.id
        where A.tgl between '$mulai' and '$hingga'
        order by A.tgl,A.shift;")->result_array();
    }
*/

    public function cetaklot($id)
    {
        return $this->db->query("call nomer_lot($id);")->result_array();
    }

    public function lotvoid($id)
    {
        return $this->db->query("call nomer_lot_void($id);")->result_array();
    }

    public function tb_void($token)
    {
        return $this->db->query("select distinct A.sku,B.barang,date_format(A.tgl,'%d %b %Y') as tgl,C.FullName,A.keterangan
        from tb_void_lot A
        left join tb_fg B on B.sku = A.sku
        Left join employee C on C.emp_id = A.usr
        where A.token = '$token';")->row_array();
    }

//Temporary code
    public function tb_reprint()
    {
        return $this->db->query("SELECT A.kode_fg as lotfg,C.barang AS warna
        FROM tb_reprint A
        LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.kode_fg
        LEFT JOIN tb_fg C ON C.sku = B.sku;")->result_array();
    }

    public function get_load_fg()
    {
        return $this->db->query("SELECT id,doc_num,sku,barang,jml_lot,kirim,void
        FROM (
            SELECT 
            A.id,
            A.doc_num,
            A.sku,
            B.barang,
            IFNULL(A.nomor,0) AS jml_lot,
            IFNULL(A.kirim,0) AS kirim,
            IFNULL(A.void,0) AS void,
            (IFNULL(A.nomor,0)-(IFNULL(A.kirim,0)+IFNULL(A.void,0))) AS jml
            FROM tb_lot A
            LEFT JOIN (SELECT sku,barang FROM tb_fg)B ON B.sku = A.sku
            WHERE A.sts IS NOT NULL AND A.doc_num IS NOT NULL ORDER BY A.id
        ) AS tb_lot_monitor WHERE jml > 0;")->result_array();
    }

    public function cetaklot2($id)
    {
        return $this->db->query("SELECT A.kode_fg AS lotfg,B.barang AS warna 
        FROM tb_fg_gudang A
        LEFT JOIN tb_fg B ON B.sku = A.sku
        WHERE A.id_produksi = $id;")->result_array();
    }

    public function tb_tf_note()
    {
        $id = $this->session->id_user;
        return $this->db->query("SELECT id,tgl,sku,gudang,barang,jml_lot,qty_lot,note,jml_note,blc,jml_kirim,jml_void
        FROM (
            SELECT 
            A.id,
            DATE_FORMAT(A.tgl,'%d %b %Y') as tgl,
            I.gudang,
            A.sku,
            D.barang,
            A.nomor AS jml_lot,
            H.qty_lot,
            COUNT(C.doc_id) AS note,
            E.jml_note AS jml_note,
            (IFNULL(H.qty_lot,0) - IFNULL(E.jml_note,0)) as blc,
            IFNULL(F.jml_kirim,0) AS jml_kirim,
            IFNULL(G.jml_void,0) AS jml_void
            FROM tb_lot A
            LEFT JOIN tb_fg_gudang B ON B.id_produksi = A.id
            LEFT JOIN tb_tf_note C ON C.id_produksi = A.id
            LEFT JOIN tb_fg D ON D.sku = A.sku
            LEFT JOIN(SELECT id_produksi,SUM(qty) AS jml_note 
                        FROM tb_tf_note 
                        GROUP BY id_produksi)E ON E.id_produksi = A.id
            LEFT JOIN(SELECT id_produksi,SUM(qty) AS jml_kirim 
                        FROM tb_tf_note WHERE sts = 1
                        GROUP BY id_produksi)F ON F.id_produksi = A.id	
            LEFT JOIN(SELECT id_produksi,SUM(qty) AS jml_void 
                        FROM tb_tf_note WHERE sts = 2
                        GROUP BY id_produksi)G ON G.id_produksi = A.id									
            LEFT JOIN(SELECT id_produksi,COUNT(kode_fg) AS qty_lot 
                    FROM tb_fg_gudang GROUP BY id_produksi)H ON H.id_produksi = A.id 	
                LEFT JOIN(SELECT A.id_gudang,A.lokasi,B.emp_id,A.gudang
                                FROM tb_gudang A
                                LEFT JOIN tb_emp_unit B ON B.unit COLLATE utf8mb4_general_ci = A.lokasi)I ON I.id_gudang COLLATE utf8mb4_general_ci = B.id_gudang			  		
            WHERE B.kode_fg IS NOT NULL AND I.emp_id COLLATE utf8mb4_general_ci = '$id'
            GROUP BY A.id,A.sku,A.nomor,H.qty_lot,I.gudang,E.jml_note,F.jml_kirim,G.jml_void
            HAVING IFNULL(E.jml_note,0) < COUNT(B.kode_fg) ORDER BY A.id
        ) AS tb_panji WHERE blc > 0;")->result_array();
    }

    public function get_mesin()
    {
        $id = $this->session->id_user;
/*
        return $this->db->query("SELECT kd_mesin,mesin,kd_loc
        FROM tb_mesin 
        WHERE kd_loc IN(SELECT strKdLoc FROM employee WHERE emp_id = '$id');")->result_array();
*/
        return $this->db->query("SELECT A.kd_mesin,A.mesin,A.kd_loc
        FROM tb_mesin A
        LEFT JOIN tb_emp_unit B ON B.unit = A.kd_loc
        WHERE B.emp_id = '$id';")->result_array();
    }

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

}
