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

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

    public function nmer_lot_fg($tgl)
    {
        return $this->db->query("call nomer_lot('$tgl');")->result_array();
    }

    public function get_pengajuan_lpbj()
    {
        $id = $this->session->id_user;
        return $this->db->query("select distinct A.kd_lpbj,C.req,A.token,B.sts_urgent  
        from tb_apprv A
        Left join(select kd_lpbj,cr_id,tgl_lpbj,sts_urgent from lpbj_h) B on B.kd_lpbj = A.kd_lpbj
        Left join(select emp_id,FullName as req from employee) C on C.emp_id = B.cr_id
        where A.emp_id = '$id' and A.apprv_sts = 1;")->result_array();
    }

    public function info_kirim()
    {
        return $this->db->query("SELECT A.sku,B.barang,COUNT(A.kode_fg) as kirim
        FROM tb_fg_gudang A
        LEFT JOIN tb_fg B ON B.sku = A.sku
        WHERE date(A.tgl_kirim) BETWEEN DATE(now() - interval (day(now())-1) DAY) 
        AND LAST_DAY(NOW())
        GROUP BY A.sku ORDER BY B.barang;")->result_array();
    }

    public function recv_monitor_fanny()
    {
        return $this->db->query("SELECT sku,barang,SUM(IT) AS tf,SUM(Recv) AS terima,(SUM(IT)-SUM(Recv)) AS balance
        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 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 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))))
        ) AS tb_recv 
        WHERE balance > 0 AND Recv > 0
        GROUP BY sku,barang;")->result_array();
    }

    public function detail_recv_monitor_fanny($kode)
    {
        return $this->db->query("SELECT doc_tf,tgl,sku,barang,IT,Recv,balance
        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 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 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))))
        ) AS tb_recv 
        WHERE balance > 0 AND Recv > 0 AND sku = '$kode' ORDER BY tgl,sku;")->result_array();
    }

    public function tb_stok()
    {
        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(A.tgl_kirim) <= last_day(curdate() - interval 1 MONTH)
                        GROUP BY A.sku ORDER BY A.sku
                    ) C ON C.sku = A.sku
                    WHERE DATE(A.tgl_diterima) <= last_day(curdate() - interval 1 MONTH)
                    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 '2024-11-01' AND '2024-11-30'
			  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 '2024-11-01' AND '2024-11-30'
			  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 ORDER BY sku;")->result_array();
    }


}
