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

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

    public function nmr()
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $lokasi = $this->session->strKdLokasi;
        return $tpcdb->query("CALL get_next_number2('$lokasi', @next_number);")->row_array();
    }

    public function satuan($brg)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("SELECT uom1 FROM tb_brg WHERE kd_brg  = '$brg' 
        Union All
        SELECT uom2 FROM tb_brg WHERE kd_brg  = '$brg' 
        Union All
        SELECT uom3 FROM tb_brg WHERE kd_brg  = '$brg';")->result_array();
    }

    public function get_user($id)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $this->db->query("select 
        A.emp_id,
        A.FullName,
        A.Email,A.Level,
        case 
        when A.Level = 4 then 'Admin' 
        when A.Level = 3 then 'SPV'
        when A.Level = 2 then 'Manager'
        when A.Level = 1 then 'Direktur'
        end Level_posisi,
        case 
        when A.Level = 4 then A.ApprovalLine
        when A.Level = 3 then A.emp_id 
        when A.Level = 2 then A.emp_id 
        when A.Level = 1 then A.emp_id 
        end id_spv,
        case 
        when A.Level = 4 then B.Fullname
        when A.Level = 3 then A.Fullname
        when A.Level = 2 then A.Fullname 
        when A.Level = 1 then A.Fullname 
        end nama_spv,
        case 
        when A.Level = 4 then B.Level
        when A.Level = 3 then A.Level
        when A.Level = 2 then A.Level 
        when A.Level = 1 then A.Level  
        end level_spv,
        case 
        when A.Level = 4 then B.ApprovalLine
        when A.Level = 3 then B.emp_id 
        when A.Level = 2 then A.emp_id 
        when A.Level = 1 then A.emp_id 
        end id_manager,
        case 
        when A.Level = 4 then C.Fullname
        when A.Level = 3 then B.Fullname
        when A.Level = 2 then A.Fullname 
        when A.Level = 1 then A.Fullname 
        end nama_manager, 
        case 
        when A.Level = 4 then C.Level
        when A.Level = 3 then B.Level
        when A.Level = 2 then A.Level 
        when A.Level = 1 then A.Level 
        end level_manager,
        case 
        when A.Level = 4 then D.emp_id
        when A.Level = 3 then C.emp_id 
        when A.Level = 2 then B.emp_id 
        when A.Level = 1 then A.emp_id 
        end id_direktur,
        case 
        when A.Level = 4 then D.FullName
        when A.Level = 3 then C.FullName 
        when A.Level = 2 then B.FullName 
        when A.Level = 1 then A.FullName 
        end nama_direktur
        from employee A
        Left join(select emp_id,FullName,ApprovalLine,Level from employee)B on B.emp_id = A.ApprovalLine
        Left join(select emp_id,FullName,ApprovalLine,Level from employee)C on C.emp_id = B.ApprovalLine
        Left join(select emp_id,FullName,ApprovalLine,Level from employee)D on D.emp_id = C.ApprovalLine
        where A.emp_id = '$id' and A.stsAktif = 1;")->row_array();
    }

    public function get_number($kd)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("select count(kd_lpbj) as kd_lpbj from lpbj_h where kd_lpbj = '$kd';")->row_array();
    }

    public function ajukan_approve($kd_lpbj, $id, $sts, $token)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $idusr = $this->session->id_user;
        $tpcdb->query("call ajukan_aprove('$kd_lpbj','$idusr',$sts,'$token');");
    }

    public function lpbj_list($kd)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $id = $this->session->id_user;
        return $tpcdb->query("select 
        A.kd_lpbj,
        A.kd_brg,
        B.brg as nama_brg,
        A.qty_req,
        A.uom,
        A.stok,
        A.keterangan,
        A.dept,
        C.strNmDept,
        C.strKdLokasi,
        A.tgl,
        date_format(A.tgl,'%d %b %Y') as tanggal,
        A.gambar,
        D.sts_lpbj 
        from lpbj_d A
        Left join tb_brg B on B.kd_brg = A.kd_brg
        Left join tpc_dept C on C.id = A.dept
        Left join lpbj_h D on D.kd_lpbj = A.kd_lpbj
        where A.kd_lpbj = '$kd' and D.cr_id ='$id';")->result_array();
    }

    public function sts_lpbj($kd)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("
        SELECT IF ( 
           EXISTS (
                   select kd_lpbj from lpbj_h where kd_lpbj = '$kd' and sts_lpbj = 2
                   ),1,0) as sts_lpbj;")->row_array();
    }

    public function hitung_lpbj($kd)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("select count(kd_lpbj) as item_lpbj from lpbj_d where kd_lpbj = '$kd';")->row_array();
    }

    public function laporan_lpbj($mulai, $hingga)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $id = $this->session->id_user;
        return $tpcdb->query("select distinct
        A.kd_lpbj,
        H.req_by,
        A.tgl_lpbj,
        date_format(A.tgl_lpbj,'%d %b %Y') as tanggal_lpbj,
        date_format(A.tgl_lpbj,'%H:%m:%s') as jam_lpbj,
        A.cr_id,
        A.sts_urgent,
        A.unit,
        A.sts_lpbj,
        C.spv,
        /*
        ifnull(B.apprv_sts,'Pending') as spv_aprv,
        ifnull(B.apprv_date,'-') as spv_aprv_date,
        ifnull(B.keterangan,'-') as spv_aprv_ket,
        */
        E.mgr,
        /*
        ifnull(D.apprv_sts,'Pending') as mgr_aprv,
        ifnull(D.apprv_date,'-') as mgr_aprv_date,
        ifnull(D.keterangan,'-') as mgr_aprv_ket,
        */
        G.dir
        /*
        ifnull(F.apprv_sts,'Pending') as dir_aprv,
        ifnull(F.apprv_date,'-') as dir_aprv_date,
        ifnull(F.keterangan,'-') as dir_aprv_ket
        */
        from lpbj_h A
        Left join(select kd_lpbj,kd_brg,ID,apprv_sts,apprv_date,keterangan from tb_apprv where Level = 3) B on B.kd_lpbj = A.kd_lpbj
        Left join(select ID,Fullname as spv from employee) C on C.ID = B.ID
        Left join(select kd_lpbj,kd_brg,ID,apprv_sts,apprv_date,keterangan from tb_apprv where Level = 2) D on D.kd_lpbj = A.kd_lpbj
        Left join(select ID,Fullname as mgr from employee) E on E.ID = D.ID
        Left join(select kd_lpbj,kd_brg,ID,apprv_sts,apprv_date,keterangan from tb_apprv where Level = 1) F on F.kd_lpbj = A.kd_lpbj
        Left join(select ID,Fullname as dir from employee) G on G.ID = F.ID
        Left join(select ID,Fullname as req_by from employee) H on H.ID = A.cr_id
        where date_format(A.tgl_lpbj,'%Y-%m-%d') between '$mulai' and '$hingga' 
        and A.cr_id in (
            WITH RECURSIVE aprv AS (
                SELECT ID, ApprovalLine
                FROM employee
                WHERE ID = '$id'
                UNION ALL
                SELECT a.ID, a.ApprovalLine
                FROM employee a
                INNER JOIN aprv ah ON a.ApprovalLine = ah.ID
            )
            SELECT ID FROM aprv
        );")->result_array();
    }

    public function preview_lpbj($kd)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("select A.doc_id,A.kd_lpbj,A.kd_brg,B.brg as barang,
        C.strNmDept as nama_dept,date_format(A.tgl,'%d %b %Y') as tanggal_lpbj,
        A.qty_req,A.uom,A.stok,A.tgl,A.gambar,A.keterangan 
        from lpbj_d A
        Left join tb_brg B on B.kd_brg = A.kd_brg
        Left join tpc_dept C on C.ID = A.dept
        where A.kd_lpbj = '$kd';")->result_array();
    }

    public function sts_aprv_lpbj($kd, $brg)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("select 
        A.kd_lpbj,
        A.ID,
        A.kd_brg,
        case 
        when A.level = 3 then concat('SPV : ',A.keterangan) 
        when A.level = 2 then concat('MGR : ',A.keterangan) 
        when A.level = 1 then concat('DIR : ',A.keterangan) 
        end keterangan
        from tb_apprv A
        where A.kd_lpbj = '$kd' 
        and A.doc_id = '$brg'
        Union All
        select kd_lpbj,check_by,kd_brg,concat('Fnl Chk : ',keterangan_sts) as keterangan
        from tb_pr 
        where kd_lpbj = '$kd' 
        and kd_brg = '$brg' 
        and sts in(2,0)
        Union All
        select kd_lpbj,check_by,kd_brg,concat('Purch : ',keterangan_purchase) as keterangan
        from tb_pr 
        where kd_lpbj = '$kd' 
        and kd_brg = '$brg' 
        and sts_purchase in(2,0);")->result_array();
    }

    public function sts_aprv_lpbj_2($kd, $brg)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("select kd_lpbj,id_purchasing,kd_brg,sts_purchase as apprv_sts,ket from (
        select kd_lpbj,id_purchasing,kd_brg,sts_purchase,'purch' as ket 
        from tb_pr
        where kd_lpbj = '$kd' 
        and doc_id = '$brg'
        Union All
        select kd_lpbj,check_by,kd_brg,sts,'fc' as ket 
        from tb_pr
        where kd_lpbj = '$kd' 
        and doc_id = '$brg'
        Union All
        select kd_lpbj,ID,kd_brg,apprv_sts,'dir' as ket 
        from (
            select 
            A.kd_lpbj,
            A.ID,
            A.kd_brg,
            A.apprv_sts
            from tb_apprv A
            where A.kd_lpbj = '$kd' 
            and A.doc_id = '$brg'  order by A.level limit 1
        ) as tb_apprv_dir
    ) as tb_info_apprv where id_purchasing is not null limit 1;")->result_array();
    }

    public function lpbj_list_aprv($key)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        //$id = $this->session->id_user;
        return $tpcdb->query("select distinct
        A.doc_id,
        A.kd_lpbj,
        A.kd_brg,
        B.brg as nama_brg,
        C.qty_req,
        C.uom,
        C.stok,
        C.keterangan,
        D.strNmDept,
        C.tgl,
        C.gambar,
        A.apprv_sts,
        A.token,
        date_format(E.tgl_lpbj,'%d %b %Y') as tanggal
        from tb_apprv A
        Left join tb_brg B on B.kd_brg = A.kd_brg
        Left join lpbj_d C on C.doc_id = A.doc_id
        Left join tpc_dept D on D.ID = C.dept
        Left join lpbj_h E on E.kd_lpbj = A.kd_lpbj
        where A.apprv_sts = 1
        and A.token = '$key';")->result_array();
    }

    public function approve($kd, $brg, $emp, $email, $level, $token)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $tpcdb->query("call aprove('$kd','$emp','$email','$brg',$level,'$token');");
    }

    public function tb_pr1($kd_lpbj)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $id = $this->session->id_user;
        return $tpcdb->query("
        select A.doc_id,A.kd_lpbj,
        A.kd_brg,
        B.brg as nama_brg,
        C.qty_req,
        C.uom,
        C.stok,
        C.keterangan,
        D.strNmDept,
        C.tgl,
        C.gambar,
        A.apprv_sts,
        A.token,
        date_format(E.tgl_lpbj,'%d %b %Y') as tanggal,E.unit
        from tb_apprv A
        Left join tb_brg B on B.kd_brg = A.kd_brg
        Left join lpbj_d C on C.doc_id = A.doc_id
        Left join tpc_dept D on D.ID = C.dept
        Left join lpbj_h E on E.kd_lpbj = A.kd_lpbj
        where A.kd_lpbj = '$kd_lpbj' and A.Level = 1 and A.apprv_sts = 2
        and E.unit in(select fc from tb_emp_unit where emp_id = '$id');")->result_array();
    }

    public function add_tb_pr($kd, $brg, $sts, $keterangan)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $id = $this->session->id_user;
        $tpcdb->query("insert into tb_pr(doc_id,kd_lpbj,kd_brg,qty_req_pr,uom,stok,dept,tgl,gambar,keterangan,sts,keterangan_sts,dt_final,check_by)
        select A.doc_id,A.kd_lpbj,A.kd_brg,B.qty_req,B.uom,B.stok,B.dept,B.tgl,B.gambar,B.keterangan,'$sts','$keterangan',now(),'$id' 
        from tb_apprv A 
        Left join lpbj_d B on B.doc_id = A.doc_id
        where A.level = 1 and A.apprv_sts = 2 and A.kd_lpbj = '$kd' and A.doc_id = '$brg';");
    }

    public function tb_apprv_h()
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $id = $this->session->id_user;
        return $tpcdb->query("select distinct A.kd_lpbj,date_format(B.tgl_lpbj,'%d %b %Y') as tgl_lpbj,C.FullName as req_name,B.unit
        from tb_apprv A 
        Left join lpbj_h B on B.kd_lpbj = A.kd_lpbj
        Left join employee C on C.ID = B.cr_id
        Left join tb_pr D on D.kd_lpbj = A.kd_lpbj
        where A.level = 1 and A.apprv_sts = 2 and B.unit in(select fc from tb_emp_unit where emp_id = '$id') 
        and D.kd_lpbj is null;")->result_array();
    }

    public function tb_icon_apprv($kd, $emp)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("select distinct A.ID,A.kd_lpbj
        from tb_apprv A
        where A.kd_lpbj = '$kd' and A.ID = '$emp'
        and apprv_sts = 2 order by A.level limit 1;")->row_array();
    }

    public function tb_brg()
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $id = $this->session->id_user;
        return $tpcdb->query("select A.kd_brg,A.brg,B.id_unit
        from tb_brg A
        left join tb_brg_unit B on B.kd_brg = A.kd_brg
        where B.id_unit in(select unit from tb_emp_unit where emp_id = '$id');")->result_array();
    }

    public function dept()
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $id = $this->session->id_user;
        return $tpcdb->query("select ID,strNmDept,strKdLokasi 
        from tb_dept 
        where strKdLokasi in(select unit from tb_emp_unit where emp_id = '$id');")->result_array();
    }

    public function laporan_lpbj_usr($mulai, $hingga)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $id = $this->session->id_user;
        return $tpcdb->query("select distinct
        A.kd_lpbj,
        H.req_by,
        A.tgl_lpbj,
        date_format(A.tgl_lpbj,'%d %b %Y') as tanggal_lpbj,
        date_format(A.tgl_lpbj,'%H:%m:%s') as jam_lpbj,
        A.cr_id,
        A.sts_urgent,
        A.unit,
        A.sts_lpbj,
        C.spv,
        E.mgr,
        G.dir
        from lpbj_h A
        Left join(select kd_lpbj,kd_brg,ID,apprv_sts,apprv_date,keterangan from tb_apprv where Level = 3 and apprv_sts <> 0) B on B.kd_lpbj = A.kd_lpbj
        Left join(select ID,Fullname as spv from employee) C on C.ID = B.ID
        Left join(select kd_lpbj,kd_brg,ID,apprv_sts,apprv_date,keterangan from tb_apprv where Level = 2  and apprv_sts <> 0) D on D.kd_lpbj = A.kd_lpbj
        Left join(select ID,Fullname as mgr from employee) E on E.ID = D.ID
        Left join(select kd_lpbj,kd_brg,ID,apprv_sts,apprv_date,keterangan from tb_apprv where Level = 1  and apprv_sts <> 0) F on F.kd_lpbj = A.kd_lpbj
        Left join(select ID,Fullname as dir from employee) G on G.ID = F.ID
        Left join(select ID,Fullname as req_by from employee) H on H.ID = A.cr_id
        where date_format(A.tgl_lpbj,'%Y-%m-%d') between '$mulai' and '$hingga' 
        and A.cr_id = '$id';")->result_array();
    }
    
   
    
}
