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

class Admin_model extends CI_Model
{
    public function get($table, $data = null, $where = null)
    {
        if ($data != null) {
            return $this->db->get_where($table, $data)->row_array();
        } else {
            return $this->db->get_where($table, $where)->result_array();
        }
    }  

    public function update($table, $pk, $id, $data)
    {
        $this->db->where($pk, $id);
        return $this->db->update($table, $data);
    }

    public function insert($table, $data, $batch = false)
    {
        return $batch ? $this->db->insert_batch($table, $data) : $this->db->insert($table, $data);
    }

    public function delete($table, $pk, $id)
    {
        return $this->db->delete($table, [$pk => $id]);
    }

    public function getUsers($id = null)
    {
        /**
         * ID disini adalah untuk data yang tidak ingin ditampilkan. 
         * Maksud saya disini adalah 
         * tidak ingin menampilkan data user yang digunakan, 
         * pada managemen data user
         */
        $this->db->where('id_user !=', $id);
        $this->db->join('departemen b', 'a.id_depart = b.id_depart', 'LEFT');
        return $this->db->get('user a')->result_array();
    }

    public function getUserByRole($role)
    {
        $this->db->join('departemen b', 'a.id_depart = b.id_depart', 'LEFT');
        $this->db->where('a.role', $role);
        return $this->db->get('user a')->result_array();

    }

    public function getBarang()
    {
        $this->db->join('jenis j', 'b.jenis_id = j.id_jenis');
        $this->db->join('satuan s', 'b.satuan_id = s.id_satuan');
        $this->db->order_by('b.id_barang');
        return $this->db->get('barang b')->result_array();
    }

    public function getBarangByCabang($id_cabang = null)
    {
        $this->db->join('jenis j', 'b.jenis_id = j.id_jenis');
        $this->db->join('satuan s', 'b.satuan_id = s.id_satuan');
        $this->db->join('sa_barang sb', 'b.id_barang = sb.barang_id');
        $this->db->order_by('id_barang');

        $this->db->where('sb.cabang_id', $id_cabang);
        return $this->db->get('barang b')->result_array();
    }

    public function getBarangMasuk($cabang = null, $limit = null)
    {
        $this->db->select('
            bmsk.id_barang_masuk, 
            bmsk.tanggal_masuk,
            u.nama as nm_user, 
            up.nama as nm_user_pemilik, 
            MIN(b.nama_barang) as nama_barang, 
            MIN(s.nama_satuan) as nama_satuan, 
            c.nama_cabang');
        $this->db->join('barang_masuk bmsk', 'bmsk.id_barang_masuk = bm.id_barang_masuk');
        $this->db->join('user u', 'bmsk.user_id = u.id_user');
        $this->db->join('user up', 'bmsk.user_id_pemilik = up.id_user');
        $this->db->join('barang b', 'bm.id_barang = b.id_barang');
        $this->db->join('satuan s', 'b.satuan_id = s.id_satuan');
        $this->db->join('cabang c', 'bmsk.cabang_id = c.id_cabang');


        if ($cabang != null) {
            $this->db->where('bmsk.cabang_id', $cabang);
        }

        if ($limit != null) {
            $this->db->limit($limit);
        }

        $this->db->order_by('bmsk.id_barang_masuk', 'DESC');
        $this->db->group_by('bmsk.id_barang_masuk');
        return $this->db->get('detail_barang_masuk bm')->result_array();
    }

    public function getBarangKeluar($cabang = null, $limit = null)
    {
        $this->db->select('
            bmk.id_barang_keluar, bmk.tanggal_keluar,
            u.nama as nm_user, 
            MIN(up.nama) as nm_user_pemilik, 
            MIN(b.nama_barang) as nama_barang, 
            MIN(s.nama_satuan) as nama_satuan, 
            c.nama_cabang');
        $this->db->join('barang_keluar bmk', 'bmk.id_barang_keluar = bk.id_barang_keluar');
        $this->db->join('user u', 'bmk.user_id = u.id_user');
        $this->db->join('user up', 'bk.user_id_pemilik = up.id_user');
        $this->db->join('barang b', 'bk.id_barang = b.id_barang');
        $this->db->join('satuan s', 'b.satuan_id = s.id_satuan');
        $this->db->join('cabang c', 'bmk.cabang_id = c.id_cabang');
        if ($cabang != null) {
            $this->db->where('bmk.cabang_id', $cabang);
        }
        if ($limit != null) {
            $this->db->limit($limit);
        }

        $this->db->order_by('bk.id_barang_keluar', 'DESC');
        $this->db->group_by('bk.id_barang_keluar');
        return $this->db->get('detail_barang_keluar bk')->result_array();
    }
    
    public function getBarangKeluarById($tgl_awal, $tgl_akhir, $cabang = null, $id_barang = null)
    {
        $this->db->select('*');
        $this->db->join('barang_keluar bmk', 'bmk.id_barang_keluar = bk.id_barang_keluar');
        $this->db->join('user u', 'bmk.user_id = u.id_user');
        $where_id = array('bmk.cabang_id' => $cabang, 'bk.id_barang' => $id_barang, 'bmk.tanggal_keluar >=' => $tgl_awal, 'bmk.tanggal_keluar <=' => $tgl_akhir);
        $this->db->where($where_id);
        
        $this->db->order_by('bk.id_barang_keluar', 'DESC');
        // $this->db->group_by('bk.id_barang_keluar');
        return $this->db->get('detail_barang_keluar bk')->result_array();
    }

    public function getRetur($limit = null, $id_barang = null, $range = null)
    {
        $this->db->select('*,c.nama_cabang as asal_cabang, cc.nama_cabang as tujuan_cabang');
        $this->db->join('retur bmk', 'bmk.id_retur = bk.id_retur');
        $this->db->join('user u', 'bmk.id_user = u.id_user');
        $this->db->join('barang b', 'bk.id_barang = b.id_barang');
        $this->db->join('satuan s', 'b.satuan_id = s.id_satuan');
        $this->db->join('cabang c', 'bmk.asal_cabang = c.id_cabang');
        $this->db->join('cabang cc', 'bmk.tujuan_cabang = cc.id_cabang');
        if ($limit != null) {
            $this->db->limit($limit);
        }
        if ($id_barang != null) {
            $this->db->where('id_barang', $id_barang);
        }
        if ($range != null) {
            $this->db->where('tanggal' . ' >=', $range['mulai']);
            $this->db->where('tanggal' . ' <=', $range['akhir']);
        }

        $this->db->order_by('bk.id_retur', 'DESC');
        $this->db->group_by('bk.id_retur');
        return $this->db->get('detail_retur bk')->result_array();
    }

    public function getBarangMasukByCabang($cabang = null, $range = null, $category = null, $id_impor = null)
    {
        $this->db->select('
            bm.*, 
            bmsk.*,
            b.nama_barang,
            s.nama_satuan,
            c.nama_cabang, 
            userp.nama as nm_user_pemilik, userp.jabatan, 
            d.nama_depart,
            u.nama as nm_user,
            j.nama_jenis');
        $this->db->join('barang_masuk bmsk', 'bmsk.id_barang_masuk = bm.id_barang_masuk','left');
        $this->db->join('user u', 'bmsk.user_id = u.id_user','left');
        $this->db->join('user userp', 'bmsk.user_id_pemilik = userp.id_user','left');
        $this->db->join('departemen d', 'userp.id_depart = d.id_depart','left');
        $this->db->join('barang b', 'bm.id_barang = b.id_barang','left');
        $this->db->join('satuan s', 'b.satuan_id = s.id_satuan','left');
        $this->db->join('cabang c', 'bmsk.cabang_id = c.id_cabang','left');
        $this->db->join('jenis j', 'b.jenis_id = j.id_jenis','left');

        if ($cabang != null) {
            $this->db->where('bmsk.cabang_id', $cabang);
        }

        if ($range != null) {
            $this->db->where('bmsk.tanggal_masuk' . ' >=', $range['mulai']);
            $this->db->where('bmsk.tanggal_masuk' . ' <=', $range['akhir']);
        }

        if ($category != null) {
            $this->db->where_in('b.jenis_id', $category);
        }

        if ($id_impor != null) {
            $this->db->where_in('bmsk.id_impor', $id_impor);
        }

        $this->db->order_by('bmsk.id_barang_masuk', 'DESC');
        return $this->db->get('detail_barang_masuk bm')->result_array();
    }

    public function getBarangMasukByCabangImpor($id_impor, $cabang = null, $range = null, $category = null)
    {
        $this->db->select('
            bm.*, 
            bmsk.*,
            b.nama_barang,
            s.nama_satuan,
            c.nama_cabang, 
            userp.nama as nm_user_pemilik, userp.jabatan, 
            d.nama_depart,
            u.nama as nm_user,
            j.nama_jenis');
        $this->db->join('barang_masuk bmsk', 'bmsk.id_barang_masuk = bm.id_barang_masuk','left');
        $this->db->join('user u', 'bmsk.user_id = u.id_user','left');
        $this->db->join('user userp', 'bmsk.user_id_pemilik = userp.id_user','left');
        $this->db->join('departemen d', 'userp.id_depart = d.id_depart','left');
        $this->db->join('barang b', 'bm.id_barang = b.id_barang','left');
        $this->db->join('satuan s', 'b.satuan_id = s.id_satuan','left');
        $this->db->join('cabang c', 'bmsk.cabang_id = c.id_cabang','left');
        $this->db->join('jenis j', 'b.jenis_id = j.id_jenis','left');

        if ($cabang != null) {
            $this->db->where('bmsk.cabang_id', $cabang);
        }

        if ($range != null) {
            $this->db->where('bmsk.tanggal_masuk' . ' >=', $range['mulai']);
            $this->db->where('bmsk.tanggal_masuk' . ' <=', $range['akhir']);
        }

        if ($category != null) {
            $this->db->where_in('b.jenis_id', $category);
        }

        if ($id_impor != null) {
            $this->db->where_in('bmsk.id_impor', $id_impor);
        }

        $this->db->order_by('bmsk.id_barang_masuk', 'DESC');
        return $this->db->get('detail_barang_masuk bm')->result_array();
    }

    public function getBarangKeluarByCabang($cabang = null, $range = null, $category = null, $id_impor = null)
    {
        $this->db->select('
            bk.*, 
            bmk.*, 
            b.nama_barang,
            s.nama_satuan,
            c.nama_cabang, 
            u.nama as nm_user, 
            up.nama as nm_user_pemilik, up.jabatan, 
            d.nama_depart,
            u.nama as nm_user,
            j.nama_jenis,
            dbms.merk, dbms.spesifikasi, dbms.barcode, dbms.sn,
            bm.stts_penggunaan, bm.stts_perolehan');
        $this->db->join('barang_keluar bmk', 'bmk.id_barang_keluar = bk.id_barang_keluar','left');
        $this->db->join('detail_barang_masuk dbms', 'bk.id_detail_masuk = dbms.id_detail','left');
        $this->db->join('barang_masuk bm', 'dbms.id_barang_masuk = bm.id_barang_masuk','left');
        $this->db->join('user u', 'bmk.user_id = u.id_user','left');
        $this->db->join('user up', 'bk.user_id_pemilik = up.id_user','left');
        $this->db->join('departemen d', 'up.id_depart = d.id_depart','left');
        $this->db->join('barang b', 'bk.id_barang = b.id_barang','left');
        $this->db->join('satuan s', 'b.satuan_id = s.id_satuan','left');
        $this->db->join('cabang c', 'bmk.cabang_id = c.id_cabang','left');
        $this->db->join('jenis j', 'b.jenis_id = j.id_jenis','left');

        if ($cabang != null) {
            $this->db->where('bmk.cabang_id', $cabang);
        }

        if ($range != null) {
            $this->db->where('bmk.tanggal_keluar' . ' >=', $range['mulai']);
            $this->db->where('bmk.tanggal_keluar' . ' <=', $range['akhir']);
        }

        if ($category != null) {
            $this->db->where_in('b.jenis_id', $category);
        }

        if ($id_impor != null) {
            $this->db->where_in('bmk.id_impor', $id_impor);
        }
    
        $this->db->order_by('bk.id_barang_keluar', 'DESC');

        return $this->db->get('detail_barang_keluar bk')->result_array();
    }

    public function getMax($table, $field, $kode = null)
    {
        $this->db->select_max($field);
        if ($kode != null) {
            $this->db->like($field, $kode, 'after');
        }
        return $this->db->get($table)->row_array()[$field];
    }

    public function count($table)
    {
        $this->db->from($table);
        return $this->db->count_all_results();
    }

    public function sum($table, $field)
    {
        $this->db->select_sum($field);
        return $this->db->get($table)->row_array()[$field];
    }

    public function min($table, $field, $min)
    {
        $field = $field . ' <=';
        $this->db->where($field, $min);
        return $this->db->get($table)->result_array();
    }

    public function chartBarangMasuk($id_cabang, $bulan)
    {
        $like = 'T-BM-' . date('y') . $bulan;
        $this->db->like('id_barang_masuk', $like, 'after');
        $this->db->where('cabang_id', $id_cabang);
        return count($this->db->get('barang_masuk')->result_array());
    }

    public function chartBarangKeluar($id_cabang, $bulan)
    {
        $like = 'T-BK-' . date('y') . $bulan;
        $this->db->like('id_barang_keluar', $like, 'after');
        $this->db->where('cabang_id', $id_cabang);
        return count($this->db->get('barang_keluar')->result_array());
    }

    public function laporan($table, $mulai, $akhir)
    {
        $tgl = $table == 'barang_masuk' ? 'tanggal_masuk' : 'tanggal_keluar';
        $this->db->where($tgl . ' >=', $mulai);
        $this->db->where($tgl . ' <=', $akhir);
        return $this->db->get($table)->result_array();
    }

    public function cekStok($id, $id_cabang)
    {
        $this->db->join('cabang_detail a', 'b.id_barang=a.id_barang');
        $this->db->join('satuan s', 's.id_satuan=b.satuan_id');
        $data = $this->db->get_where('barang b', ['b.id_barang' => $id, 'a.id_cabang' => $id_cabang])->row_array();
        if ($data == null) {
            $this->db->select('s.nama_satuan,0 stok');
            $this->db->join('satuan s', 's.id_satuan=b.satuan_id');
            $data = $this->db->get_where('barang b', ['b.id_barang' => $id])->row_array();
        }
        return $data;
    }

    public function cekStokTransfer($id, $asal_cabang, $tujuan_cabang)
    {
        $query = "SELECT * FROM cabang_detail WHERE id_barang = '$id' AND id_cabang = '$asal_cabang'";
        $q1 = $this->db->query($query)->row_array();
        $query2 = "SELECT * FROM cabang_detail WHERE id_barang = '$id' AND id_cabang = '$tujuan_cabang'";
        $q2 = $this->db->query($query2)->row_array();

        if ($q1 == null) {
            $q1 = ['stok' => 0];
        }

        if ($q2 == null) {
            $q2 = ['stok' => 0];
        }

        $data = [
            'stok_awal' => $q1['stok'],
            'stok_tujuan' => $q2['stok']
        ];

        return $data;
    }

    public function getDataCabang()
    {
        $this->db->select('*');
        return $this->db->get('cabang')->result_array();
    }

    public function getSaBarangById($id_barang)
    {
        $this->db->where('a.barang_id', $id_barang);
        return $this->db->get('sa_barang a')->result_array();
    }

    public function getDataCabangByUser($id)
    {
        $this->db->select('user.nama, cabang.*');
        $this->db->join('user', 'cabang.id_cabang = user.cabang_id');
        $this->db->where_in('user.cabang_id', $id);
        return $this->db->get('cabang')->row_array();
    }




    // cetak laporan 
    public function cetak_laporan($dt_header11, $dt_all, $stts)
    {
        $this->load->library('pdf');
        $pdf = new PDF('L','mm','A4');
        $pdf->AliasNbPages();

        // set variabel dulu
        $pdf->judul   = $dt_header11['judul'];
        $pdf->periode = $dt_header11['periode'];
        $pdf->profile = $dt_header11['profile'];

        $pdf->AddPage(); // otomatis panggil Header()

        // menyusun
        // isi konten laporan
        $pdf->SetFont('Arial','B',9);
        $pdf->Cell(0,10,'Cabang : '. $dt_header11['cabang']['nama_cabang'],0,1);

        $pdf->setFont('Arial','B',6);
        $pdf->setFillColor(255,255,255);
        $pdf->cell(10,6,'No.',1,0,'C',1);
        $pdf->cell(12,6,'ID Barang',1,0,'C',1);
        $pdf->cell(13,6,'Barcode',1,0,'C',1);
        $pdf->cell(20,6,'Item',1,0,'C',1);
        $pdf->cell(8,6,'Qty',1,0,'C',1);
        $pdf->cell(20,6,'Category',1,0,'C',1);
        $pdf->cell(17,6,'Merk',1,0,'C',1);
        $pdf->cell(23,6,'Spesifikasi',1,0,'C',1);
        $pdf->cell(15,6,'SN',1,0,'C',1);
        $pdf->cell(15,6,'Tanggal',1,0,'C',1);
        $pdf->cell(20,6,'User Pemilik',1,0,'C',1);
        $pdf->cell(19,6,'Jabatan',1,0,'C',1);
        $pdf->cell(17,6,'Departemen',1,0,'C',1);
        $pdf->cell(17,6,'Status',1,0,'C',1);
        $pdf->cell(17,6,'Movement',1,0,'C',1);
        $pdf->cell(17,6,'Keterangan',1,0,'C',1);
        if($stts == 1 || $stts == 2) {
            $pdf->cell(20,6,'Petugas',1,0,'C',1);
        } else {
            $pdf->cell(20,6,'Lokasi Tujuan',1,0,'C',1);
        }

        $pdf->Ln();

        // cetak laporan barang masuk
        if($stts == 1) {
            if($dt_all){
                $pdf->setFont('Arial','',6);
                $pdf->SetWidths(Array(10,12,13,20,8,20,17,23,15,15,20,19,17,17,17,17,20));
                $pdf->SetAligns(Array('C','L','L','L','C','L','L','L','L','L','L','L','L','L','L','L','L',));
                $pdf->SetLineHeight(5);
                $no = 1;
                foreach ($dt_all as $row) {

                    $pdf->Row(Array(
                        $no++,
                        $row['id_barang'],
                        $row['barcode'],
                        $row['nama_barang'],
                        $row['jumlah'],
                        $row['nama_jenis'],
                        $row['merk'],
                        $row['spesifikasi'],
                        $row['sn'],
                        $row['tanggal_masuk'],
                        $row['nm_user_pemilik'],
                        $row['jabatan'],
                        $row['nama_depart'],
                        $row['stts_perolehan'],
                        $row['stts_penggunaan'],
                        $row['ket'],
                        $row['nm_user'],
                    ));
                }

            } else {
                $pdf->setFont('Arial','',9);
                $pdf->cell(190,6,'Tidak Ada Transaksi',1,1,'C',1);
            }
        } else if($stts == 2) {
            // cetak laporan barang keluar
            if($dt_all){
                $pdf->setFont('Arial','',6);
                $pdf->SetWidths(Array(10,12,13,20,8,20,17,23,15,15,20,19,17,17,17,17,20));
                $pdf->SetAligns(Array('C','L','L','L','C','L','L','L','L','L','L','L','L','L','L','L','L',));
                $pdf->SetLineHeight(5);
                $no = 1;
                foreach ($dt_all as $row) {

                    $pdf->Row(Array(
                        $no++,
                        $row['id_barang'],
                        $row['barcode'],
                        $row['nama_barang'],
                        $row['jumlah'],
                        $row['nama_jenis'],
                        $row['merk'],
                        $row['spesifikasi'],
                        $row['sn'],
                        $row['tanggal_keluar'],
                        $row['nm_user_pemilik'],
                        $row['jabatan'],
                        $row['nama_depart'],
                        $row['stts_perolehan'],
                        $row['stts_penggunaan'],
                        $row['ket'],
                        $row['nm_user'],
                    ));
                }

            } else {
                $pdf->setFont('Arial','',9);
                $pdf->cell(190,6,'Tidak Ada Transaksi',1,1,'C',1);
            }

        } else if($stts == 3) {
            // cetak laporan barang transfer
            if($dt_all){
                $pdf->setFont('Arial','',6);
                $pdf->SetWidths(Array(10,12,13,20,8,20,17,23,15,15,20,19,17,17,17,17,20));
                $pdf->SetAligns(Array('C','L','L','L','C','L','L','L','L','L','L','L','L','L','L','L','L',));
                $pdf->SetLineHeight(5);
                $no = 1;
                foreach ($dt_all as $row) {

                    $pdf->Row(Array(
                        $no++,
                        $row['id_barang'],
                        $row['barcode'],
                        $row['nama_barang'],
                        $row['jumlah'],
                        $row['nama_jenis'],
                        $row['merk'],
                        $row['spesifikasi'],
                        $row['sn'],
                        $row['tgl_transfer'],
                        $row['nm_user_pemilik'],
                        $row['jabatan'],
                        $row['nama_depart'],
                        $row['stts_perolehan'],
                        $row['stts_penggunaan'],
                        $row['keterangan'],
                        $row['tujuan'],
                    ));
                }

            } else {
                $pdf->setFont('Arial','',9);
                $pdf->cell(190,6,'Tidak Ada Transaksi',1,1,'C',1);
            }

        } else if($stts == 4) {
            // cetak laporan barang stok opname
            if($dt_all){
                $pdf->setFont('Arial','',6);
                $pdf->SetWidths(Array(10,12,13,20,8,20,17,23,15,15,20,19,17,17,17,17,20));
                $pdf->SetAligns(Array('C','L','L','L','C','L','L','L','L','L','L','L','L','L','L','L','L',));
                $pdf->SetLineHeight(5);
                $no = 1;
                foreach ($dt_all as $row) {

                    $pdf->Row(Array(
                        $no++,
                        $row['id_barang'],
                        $row['barcode'],
                        $row['nama_barang'],
                        $row['jumlah'],
                        $row['nama_jenis'],
                        $row['merk'],
                        $row['spesifikasi'],
                        $row['sn'],
                        $row['tanggal_stock_op_name'],
                        $row['nm_user_pemilik'],
                        $row['jabatan'],
                        $row['nama_depart'],
                        $row['stts_perolehan'],
                        $row['stts_penggunaan'],
                        $row['ket'],
                        $row['nm_user'],
                    ));
                }

            } else {
                $pdf->setFont('Arial','',9);
                $pdf->cell(190,6,'Tidak Ada Transaksi',1,1,'C',1);
            }

        } 

        $pdf->setTitle($dt_header11['judul'],true);
        $pdf->Output('I',$dt_header11['nm_file'].'.pdf');
    }

    // file impor
    public function process_impor($data)
    {
        $this->db->insert('impor', $data);
        $id_impor  = $this->db->insert_id();

        return $id_impor;
    }

}
