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

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

    public function Brg($cari, $offset, $items_per_page)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("Select nmr,kd_brg,brg,uom1,uom2,uom3,ratio2,ratio3,hitung_ID 
        from (
            SELECT row_number() over (order by A.kd_brg) as nmr,
            A.kd_brg, 
            A.brg, 
            A.uom1, 
            A.uom2, 
            A.uom3, 
            A.ratio2, 
            A.ratio3,
            ifnull(B.hitung_ID,0) as hitung_ID
            FROM tb_brg A
            Left join(select kd_brg,count(kd_brg) as hitung_ID from lpbj_d group by kd_brg)B on B.kd_brg = A.kd_brg
            WHERE A.sts > 0 and A.brg like '%" . $cari . "%'
        ) as tb_item LIMIT " . $offset . "," . $items_per_page . ";")->result_array();
    }

    public function jmldatabrg($cari)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("SELECT COUNT(A.kd_brg) as total FROM tb_brg A WHERE A.sts > 0 AND A.brg LIKE '%" . $cari . "%'")->row_array();
    }

    public function simpan_brg($kodebrg, $deskripsibrg, $unit1brg, $unit2brg, $ratio2brg, $unit3brg, $ratio3brg)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $tpcdb->db_debug = false;
        $user = $this->session->strEmail;
        $query = "insert into tb_brg(
                kd_brg,
                brg,
                uom1,
                uom2,
                ratio2,
                uom3,
                ratio3,            
                sts,
                dtCreate,
                usrCreate  
                ) values ('$kodebrg','$deskripsibrg','$unit1brg','$unit2brg',$ratio2brg, 
                '$unit3brg',$ratio3brg,1,now(),'" . $user . "');";
        if (!@$this->db->query($query)) {
            $error = $this->db->error();
            echo "0";
        } else {
            echo "1";
        }
    }

    public function unduh($cari)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        $query = "SELECT 
        A.kd_brg, 
        A.brg, 
        A.uom1, 
        A.uom2, 
        A.uom3, 
        A.ratio2, 
        A.ratio3,
        ifnull(B.hitung_ID,0) as hitung_ID 
        FROM tb_brg A
        Left join(select kd_brg,count(kd_brg) as hitung_ID from lpbj_d group by kd_brg)B on B.kd_brg = A.kd_brg
        WHERE A.sts > 0 and A.brg like '%" . $cari . "%' order by A.kd_brg;";
        return $tpcdb->query($query)->result_array();
    }

    public function tb_unit($kd)
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("select A.unit
        from tb_unit A 
        left join (select id_unit from tb_brg_unit where kd_brg = '$kd') B on A.unit = B.id_unit
        where B.id_unit is null;")->result_array();
    }
    
    public function get_team($id)
    {
        return $this->db->query("WITH RECURSIVE aprv AS (
            SELECT emp_id, ApprovalLine
            FROM employee
            WHERE emp_id = '$id'
            UNION ALL
            SELECT a.emp_id, a.ApprovalLine
            FROM employee a
            INNER JOIN aprv ah ON a.ApprovalLine = ah.emp_id
        )
        SELECT A.emp_id,B.FullName,B.Division,case when B.Level = 2 then 'MANAGER'
        when B.Level = 3 then 'SPV'
        when B.Level = 4 then 'ADMIN'
        end Level
        ,B.Email,B.strJobPosition,B.stsAktif,'$id' as dir,C.FullName as atasan,B.Level as Lvl,B.strPassword,B.ApprovalLine
        FROM aprv A
        Left join employee B on B.emp_id = A.emp_id 
        Left join employee C on C.emp_id = B.ApprovalLine
        where B.Level > 1
        Order by B.Level;")->result_array();
    }

    public function get_apprv($lvl)
    {
        return $this->db->query("select emp_id,Fullname,Level,
        case 
        when Level = 1 then 'DIREKTUR'
        when Level = 2 then 'MANAGER' 
        when Level = 3 then 'SPV'
        when Level = 4 then 'ADMIN'
        end lvl
        from employee where Level < $lvl order by Level;")->result_array();
    }

}
