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

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

    public function tb_akses($id)
    {
        return $this->db->query("select 
        A.id_sub,
        A.id_grup,
        A.submenu,
        A.icon,
        A.url,
        A.sts,
        ifnull(B.akses,0) as akses,
        ifnull(B.dml,0) as dml,
        ifnull(B.unduh,0) as unduh
        from tb_sub_menu A
        Left join (select id_menu,akses,dml,unduh from tb_akses where id_user = '$id') 
        B on B.id_menu = A.id_sub;")->result_array();
    }

    public function cari_akses($id, $menu)
    {
        return $this->db->query("SELECT IF ( 
           EXISTS (
                   select akses from tb_akses where id_user = '$id' and id_menu = $menu
                   ),1,0) as cari_akses;")->row_array();
    }

    public function cari_dml($id, $menu)
    {
        return $this->db->query("SELECT IF ( 
           EXISTS (
                   select dml from tb_akses where id_user = '$id' and id_menu = $menu
                   ),1,0) as cari_dml;")->row_array();
    }

    public function cari_unduh($id, $menu)
    {
        return $this->db->query("SELECT IF ( 
           EXISTS (
                   select unduh from tb_akses where id_user = '$id' and id_menu = $menu
                   ),1,0) as cari_unduh; ")->row_array();
    }

    public function iplist()
    {
        return $this->db->query("SELECT ID,strIPv4,strNmApp,case when stsIP = 1 then 'Public' else 'Private' end stsIP,
        case when stsAktif = 0 then 'Offline' else 'Online' end stsAktif
        FROM tpc_ip_allow;")->result_array();
    }
    
    // tpc
    public function iplisttpc()
    {
        $tpcdb = $this->load->database('tpc', TRUE);
        return $tpcdb->query("SELECT ID,strIPv4,strNmApp,case when stsIP = 1 then 'Public' else 'Private' end stsIP,
        case when stsAktif = 0 then 'Offline' else 'Online' end stsAktif
        FROM tpc_ip_allow;")->result_array();
    }
    
    public function tb_menu()
    {
        return $this->db->query("select id_sub,id_grup,submenu,icon,url,sts,akses,dml,unduh,tipe 
        from (
        SELECT id_sub,id_grup,submenu,icon,url,sts,akses,dml,unduh,1 as tipe 
        FROM tb_sub_menu WHERE SUBMENU NOT LIKE '%LAPORAN%'
        Union ALL
        SELECT id_sub,id_grup,submenu,icon,url,sts,akses,dml,unduh,2 as tipe
        FROM tb_sub_menu WHERE SUBMENU LIKE '%LAPORAN%'
        ) as tb_menu order by id_grup,tipe,id_sub;")->result_array();
    }

    public function get_unit($emp)
    {
        return $this->db->query("SELECT A.unit
        FROM tb_unit A
        LEFT JOIN (SELECT unit FROM tb_emp_unit WHERE emp_id = '$emp') B ON B.unit = A.unit
        WHERE B.unit IS NULL;")->result_array();
    }

    public function tb_emp($emp)
    {
        return $this->db->query("SELECT 
        emp_id,
        FullName,
        Division,
        Email,
        strPassword,
        LEVEL,
        ApprovalLine,
        strKdLoc,
        strJobPosition,
        stsAktif,
        dtCreate,
        usrCreate
        FROM employee WHERE emp_id LIKE '%$emp%' OR FullName LIKE'%$emp%';")->result_array();
    }
    
    public function get_approvalline($level)
    {
        return $this->db->query("SELECT emp_id,FullName FROM employee WHERE LEVEL < $level ORDER BY LEVEL;")->result_array();
    }
}
