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

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

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

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

    public function tb_retur($s, $e)
    {
        return $this->db->query("SELECT 
        A.id_retur,
        A.no_sj_retur,
        A.no_pol,
        A.id_customer,
        A.tgl,
        DATE_FORMAT(A.tgl,'%d %b %Y') as tanggal,
        A.cr_id,
        A.keterangan,
        B.nmr_outstanding,
        A.sts 
        from tb_retur_head A
        LEFT JOIN (SELECT id_retur,COUNT(no_lot) AS nmr_outstanding 
                    FROM tb_retur
                    WHERE tgl_kirim IS NULL GROUP BY id_retur
                    )B ON B.id_retur = A.id_retur
        where A.tgl between '$s' AND '$e' 
        order by A.tgl;")->result_array();
    }

    public function hitung_scan_retur($id)
    {
        return $this->db->query("SELECT DISTINCT 
        A.id_retur,
        B.sku,
        C.barang,
        IFNULL(E.rework,0) AS rework,
        IFNULL(F.fg_gudang,0) AS fg_gudang,
        IFNULL(G.scrap,0) AS scrap,
        (IFNULL(E.rework,0)-(IFNULL(F.fg_gudang,0)+IFNULL(G.scrap,0))) AS belum_cek,
        H.hitung
        FROM tb_retur 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
        LEFT JOIN (SELECT id_retur,sts FROM tb_retur_head) D on D.id_retur = A.id_retur
        LEFT JOIN (SELECT A.id_retur,B.sku,COUNT(no_lot) AS rework
                FROM tb_retur A
                LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
                WHERE A.tgl_kirim IS NOT NULL 
                GROUP BY A.id_retur,B.sku)E ON E.id_retur = A.id_retur AND E.sku = B.sku
        LEFT JOIN (SELECT A.id_retur,B.sku,COUNT(A.no_lot) AS fg_gudang
                    FROM tb_retur A
                    LEFT JOIN (SELECT kode_fg,sku FROM tb_fg_gudang) B on B.kode_fg = A.no_lot
                    WHERE sts_retur = 2 GROUP BY A.id_retur,B.sku)F ON F.id_retur = A.id_retur AND F.sku = B.sku
        LEFT JOIN (SELECT A.id_retur,B.sku,COUNT(A.no_lot) AS scrap
                    FROM tb_retur A
                    LEFT JOIN (SELECT kode_fg,sku FROM tb_fg_gudang) B on B.kode_fg = A.no_lot
                    WHERE sts_retur = 3 GROUP BY A.id_retur,B.sku)G ON G.id_retur = A.id_retur AND G.sku = B.sku
        LEFT JOIN (SELECT A.id_retur,B.sku,count(A.no_lot) AS hitung
                            FROM tb_retur A
                            LEFT JOIN tb_fg_gudang B ON B.kode_fg = A.no_lot
                            GROUP BY A.id_retur,B.sku)H ON H.id_retur AND H.sku = B.sku
        WHERE A.id_retur = $id;")->result_array();
    }

    public function cek_renumber($id, $sku)
    {
        $this->db->query("call renumber_lot($id,'$sku');");
    }

    public function tb_scan_retur()
    {
        return $this->db->query("select distinct A.id_retur,A.no_sj_retur,A.no_pol,A.id_customer,D.id_retur AS id_retur_lot
        from tb_retur_head A
        LEFT JOIN tb_retur B ON B.id_retur = A.id_retur
        LEFT JOIN tb_fg_gudang C ON C.kode_fg = B.no_lot
        LEFT JOIN tb_lot D ON D.sts = A.id_retur AND D.sku = C.sku
        WHERE D.id_retur IS NULL;")->result_array();
    }

    public function tb_detail_retur($id, $sku)
    {
        return $this->db->query("SELECT A.no_lot,B.no_sj,C.sku,DATE_FORMAT(A.tgl_kirim,'%d %b %Y') as tgl_kirim,time(A.tgl_kirim) as jam
        FROM tb_retur A
        LEFT JOIN tb_sj B ON B.doc_sj = A.no_sj
        LEFT JOIN tb_fg_gudang C ON C.kode_fg = A.no_lot
        WHERE A.id_retur = $id AND C.sku = '$sku';")->result_array();
    }

    public function kode_lot_kirim($id)
    {
        return $this->db->query("SELECT count(no_lot) AS cari 
        FROM tb_retur WHERE no_lot = '$id' and tgl_kirim is null;")->row_array();
    }

    public function tb_kirim_retur()
    {
        return $this->db->query("SELECT A.id_retur_det,A.no_lot,B.sku,C.barang,A.sts_retur
        FROM tb_retur 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 DATE(A.tgl_kirim) = date(NOW()) ORDER BY A.no_lot;")->result_array();
    }

    public function hitung_scan_terima($id)
    {
        return $this->db->query("SELECT A.id_retur,B.sku,C.barang,COUNT(A.no_lot) AS hitung 
        FROM tb_retur 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 A.id_retur = $id
        GROUP BY A.id_retur,B.sku,C.barang;")->result_array();
    }
}
