<?php

namespace App\Http\Controllers\inventori;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use Yajra\Datatables\Datatables;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Session;

class MonitoringController extends Controller
{
    //
    public function __construct(){      
        $this->middleware(function ($request, $next) {
        $url = request()->segment(1);
        $kode_group = Session::get('kode_group');
        $d_menu = DB::table("tb_menu as a")->join("tb_role as b","a.kode_menu","b.kode_menu")->where([['a.link_menu',$url],['b.kode_group',$kode_group],['b.status_role','1'],['a.active','1']])->orderBy('a.sort_menu','asc')->count();
        if(Session::get('decode') <> 1)
         {
            Session::flash('message', 'Silahkan login dengan username dan password anda.!!');
             return redirect()->to('login');
         }
         if($d_menu == 0){
            Session::flush();
            Session::flash('message', 'Anda Tidak Punya Akses ke Halaman ini.!!');
             return redirect()->to('login');
         }
        return $next($request); 
        });
    }
    
    public function index(){
    	return view('inventori.MonitoringList');
    }

    function get_data(){
    	DB::statement(DB::raw('set @rownum=0'));
        $PDO = DB::connection('mysql')->getPdo();

        $billingStmt = $PDO->prepare("SELECT B.*, @rownum  := @rownum  + 1 AS rownum,
        	case when B.stok <= 0 AND B.stok <= mb.min_stok then 2 
            when B.stok <= mb.min_stok then 1 else 0 end as status_min FROM (
			".base_stok()."
                    ) B
                    JOIN ms_barang mb on B.id_barang=mb.id
                    where B.id_proses = ''
                    ");
        $billingStmt->execute();
        $usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));

        if(count($usersBills) > 0){
            foreach($usersBills as $u){
                $min = "";
                if($u['status_min'] == 2){
                    $min = '<label class="label bg-danger-800">Stok Habis</label>';
                }elseif($u['status_min'] == 1){
                    $min = '<label class="label bg-warning-400">Stok Hampir Habis</label>';
                }else{
                	$min = '<label class="label bg-success-800">Stok Masih Ada</label>';
                }


                $arr[] = array('kode_barang' => $u['kode_barang'],
                                'nama_barang' => $u['nama_barang'],
                                'stok' => $u['stok'],
                                'nama_gudang' => $u['nama_gudang'],	
                            	'status_min' => $min,
                                'id_statusmin' => $u['status_min']);
            }
        }else{
            $arr = array();
        }

        $data = new Collection($arr);
        return Datatables::of($data)
                ->make(true);
    }

    function get_notif(Request $request){
    	DB::statement(DB::raw('set @rownum=0'));
        $PDO = DB::connection('mysql')->getPdo();

        $billingStmt = $PDO->prepare("SELECT * FROM(SELECT B.*, @rownum  := @rownum  + 1 AS rownum,
        	case when B.stok <= 0 AND B.stok <= mb.min_stok then 2 
            when B.stok <= mb.min_stok then 1 else 0 end as status_min FROM (
            SELECT A.*, sum(masuk - keluar) as stok
                    FROM (
                        SELECT * FROM view_penerimaan
                        UNION ALL
                        SELECT * FROM view_transfer 
                        UNION ALL
                        SELECT * FROM view_keluar
                        UNION ALL
                        SELECT * FROM view_masuk
                        UNION ALL
                        SELECT * FROM view_penjualan
                        UNION ALL
                        SELECT * FROM view_opname
                    ) A GROUP BY A.id_barang, A.id_gudang, A.id_proses, A.id_komposisi, A.id_corak, A.id_motif, A.id_workorder, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap
                    ) B
                    JOIN ms_barang mb on B.id_barang=mb.id
                    where B.id_proses = '') AS C where C.status_min='1' OR C.status_min='2'
                    ");
        $billingStmt->execute();
        $usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));


        if(count($usersBills) > 0){            
            $jum = count($usersBills);
            foreach($usersBills as $u){
                $min = "";
                if($u['status_min'] == 2){
                    $min = '<label class="label bg-danger-800">Stok Habis Terlalu Banyak</label>';
                }elseif($u['status_min'] == 1){
                    $min = '<label class="label bg-warning-400">Stok Hampir Habis</label>';
                }else{
                    $min = '<label class="label bg-danger-800">Stok Habis</label>';
                }


                $arr[] = array('kode_barang' => $u['kode_barang'],
                                'nama_barang' => $u['nama_barang'],
                                'jumlah' => $jum,
                                'stok' => $u['stok'],
                                'nama_gudang' => $u['nama_gudang'], 
                                'status_min' => $min,
                                'status' => $u['status_min']);
            }
        }else{
            $arr = array();
        }
		return response()->json($arr);
        
    }


    public function proses(){
        return view('inventori.ProsesMonitoringList');
    }

    function get_data_proses(){
        DB::statement(DB::raw('set @rownum=0'));
        $PDO = DB::connection('mysql')->getPdo();

        /*$billingStmt = $PDO->prepare("SELECT A.* from(SELECT pw.nomor,pw.id_workorder,pwb.id_workorder_barang, pbk.id_barangkeluar,  pwb.hb as tanggal, 
            pwb.jumlah_beem as jumlah, pbk.id_proses,pbk2.id_proses as id_proses2
            FROM pem_workorder as pw
            JOIN pem_workorder_barang as pwb ON pw.id_workorder = pwb.id_workorder
            left JOIN pro_barangkeluar pbk ON pw.id_workorder = pbk.id_workorder
            left Join pro_barangkeluar pbk2 ON pwb.id_workorder_barang = pbk2.id_workorder_barang
            group by pw.id_workorder, pbk2.id_proses
            union all
            select pwb.no_workorder as nomor, pw.id_workorder as id_workorder, pwb.id_workorder_barang as id_workorder_barang, pbk.id_barangkeluar as id_barangkeluar, pwb.hb as tangal,
            pwb.jumlah_beem as jumlah, pbk.id_proses as id_proses, pbk2.id_proses as id_proses2
            from pem_workorder as pw
            join pem_workorder_barang as pwb ON pw.id_workorder = pwb.id_workorder
            left JOIN pro_barangkeluar pbk ON pw.id_workorder = pbk.id_workorder
            left Join pro_barangkeluar pbk2 ON pwb.id_workorder_barang = pbk2.id_workorder_barang
            group by pwb.id_workorder_barang, pbk2.id_proses ) A");*/
        $billingStmt = $PDO->prepare("SELECT pw.nomor, pw.id_workorder as id_workorder, pwb.id_workorder_barang, pbk.id_barangkeluar as id_barangkeluar, pbk.id_proses as id_proses, pbk.tanggal as hb
            from pem_workorder as pw
            JOIN pem_workorder_barang as pwb ON pw.id_workorder = pwb.id_workorder
            LEFT JOIN pro_barangkeluar pbk ON pw.id_workorder = pbk.id_workorder
            ");

        $billingStmt->execute();
        $usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));

        if(count($usersBills) > 0){
            foreach($usersBills as $u){
                
                $id = $u['id_workorder'];
               // $id_workorder_barang = '';
                $id_workorder_barang = $u['id_workorder_barang'];
                $no_workorder = '';
                $tgl = '';
                $tgl_corak = '';
                $tgl_motif = '';
                $tgl_tenun = '';
                if($u['id_proses']=='33'){
                    $tgl_corak = tgl_full($u['hb'],'');
                }
                $billingStmt2 = $PDO->prepare("SELECT pwb.no_workorder as nomor, pw.id_workorder as id_workorder, pwb.id_workorder_barang as id_workorder_barang, pbk2.id_barangkeluar as id_barangkeluar, pwb.hb as tangal,
                    pwb.jumlah_beem as jumlah, pbk.id_proses as id_proses, pbk2.id_proses as id_proses2, '1' as jumlah_beem, mm.nama_motif, pbk2.tanggal as tanggal_motif
                    from pem_workorder as pw
                    join pem_workorder_barang as pwb ON pw.id_workorder = pwb.id_workorder
                    left JOIN pro_barangkeluar pbk ON pw.id_workorder = pbk.id_workorder
                    left Join pro_barangkeluar pbk2 ON pwb.id_workorder_barang = pbk2.id_workorder_barang
                    left join ms_motif mm ON pwb.motif=mm.id_motif 
                    where pwb.id_workorder_barang='$id_workorder_barang'
                    group by pwb.no_workorder, pw.id_workorder, pwb.id_workorder_barang, id_proses2");
                $billingStmt2->execute();
                $usersBills2 = $billingStmt2->fetchAll((\PDO::FETCH_ASSOC));
                foreach($usersBills2 as $b){
                    
                    if($b['id_proses2'] == '37'){
                        $tgl_tenun = tgl_full($b['tanggal_motif'],'');
                        $cek_hb = DB::table('pro_barangkeluar')->where('id_barangkeluar','=',$b['id_barangkeluar'])->first();
                        if($cek_hb->status_beem == 1){
                            $tgl = tgl_full($cek_hb->tanggal_beem,'');
                        }
                    
                    }

                    if($b['id_proses2'] == 24 && $b['tanggal_motif'] != ''){
                        $tgl_motif = tgl_full($b['tanggal_motif'],'');
                    }
                    
                    
                //    $id_workorder_barang = $b['id_workorder_barang'];
                    $no_workorder = $b['nomor'];

                    
                    }

                $arr[] = array('id_workorder' => $id,
                                'nomor' => $u['nomor'],
                                'id_workorder_barang' => $id_workorder_barang,
                                'no_workorder' => $no_workorder,
                                'tanggal'   => $tgl,
                                'corak'     => $tgl_corak,
                                'motif'     => $tgl_motif, 
                                'tenun'     => $tgl_tenun);
                
                /*array_intersect_key($arr, array_unique(array_map("serialize", $arr)));*/
                //array_unique($arr, SORT_REGULAR);
                //$arr = array_map("unserialize", array_unique(array_map("serialize", $arr)));

                
                }
             array_multisort(array_column($arr, 'nomor'), array_column($arr, 'no_workorder'));
             $arr = array_column($arr, null, 'id_workorder_barang');
        }else{
            $arr = array();
        }

    //    print_r($arr);
        $data = new Collection($arr);
        return Datatables::of($data)
                ->make(true);
    }
}
