<?php

namespace App\Http\Controllers\inventori;

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

class SaldoawalController 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); 
        });

        $this->session = array('id_gudang'=>Session::get('id_gudang'),'id_proses'=>Session::get('id_proses'),'id_lokasi'=>Session::get('id_lokasi'), 'kode_group' => Session::get('kode_group'), 'kode_user' => Session::get('kode_user'));
    }

    public function index(){
        $id_proses = $this->session['id_proses'];
        $data['warna'] = DB::table('ms_warnakomposisi')->OrderBy('nama', 'asc')->get();
        $data['proses'] = DB::table('ms_proses as mp')->leftJoin('ms_proses as mp2','mp.parent','mp2.id_proses')->select('mp.nama_proses as nama_proses','mp2.nama_proses as nama_proses2','mp.id_proses')->orderBy('mp.id_proses');
        if($id_proses != 0){
            // $data['proses']->whereIn('mp.id_proses', explode(",", $id_proses));
        }
        $data['proses'] = $data['proses']->get();

    	return view('inventori/SaldoawalList')->with('data',$data);
    }

    function get_barang(){  
        $id_gudang = $this->session['id_gudang'];

        $arr = array();

        $d_data = DB::table('ms_barang AS mb')->join('ms_kategori_barang AS mkb', 'mb.id_kb', 'mkb.id_kb')->join('ms_kategori_barang AS mkb2', 'mkb.induk_kb', 'mkb2.id_kb')->join('ms_satuan AS ms', 'mb.id_satuan', 'ms.id_satuan')->select(DB::raw('mb.*, mkb2.id_kb AS id_kategori, mkb2.nama nama_kategori, mkb.id_kb id_sub, mkb.nama nama_sub, ms.id_satuan, ms.nama_satuan'));

        if($d_data->count() > 0){
            foreach($d_data->orderBy('nama_kategori', 'asc')->orderBy('nama_sub', 'asc')->orderBy('kode_barang', 'asc')->orderBy('nama_barang', 'asc')->get() as $d){
                $arr['barang'][] = array('id_barang' => $d->id,
                                'kode_barang' => $d->kode_barang,
                                'nama_barang' => $d->nama_barang,
                                'id_kategori' => $d->id_kategori,
                                'kategori' => $d->nama_kategori,
                                'id_sub' => $d->id_sub,
                                'nama_sub' => $d->nama_sub,
                                'satuan' => $d->nama_satuan);
            }
        }else{
            $arr['barang'] = array();
        }

        $d_data = DB::table('ms_gudang');
        
        if($id_gudang != 0){
            $d_data->whereIn('id_gudang', explode(",", $id_gudang));
        }

        if($d_data->count() > 0){
            foreach($d_data->orderBy('nama_gudang', 'asc')->get() as $d){
                $arr['gudang'][] = array('id_gudang' => $d->id_gudang,
                                'nama_gudang' => $d->nama_gudang);
            }
        }else{
            $arr['gudang'] = array();
        }

        $d_data = DB::table('ms_motif');

        if($d_data->count() > 0){
            foreach($d_data->orderBy('nama_motif', 'asc')->get() as $d){
                $arr['motif'][] = array('id_motif' => $d->id_motif,
                                'kode_motif' => $d->kode_motif,
                                'nama_motif' => $d->nama_motif);
            }
        }else{
            $arr['motif'] = array();
        }

        $d_data = DB::table('ms_motif as mm')->join('ms_motif_barang as mmb', 'mm.id_motif', 'mmb.id_motif')->select(DB::raw('mm.id_motif, mm.kode_motif, mm.nama_motif, mmb.id_motif_barang, mmb.motif_keterangan'));

        if($d_data->count() > 0){
            foreach($d_data->orderBy('nama_motif', 'asc')->get() as $d){
                $arr['corak'][] = array('id_corak' => $d->id_motif,
                                'kode_motif' => $d->kode_motif,
                                'nama_motif' => $d->nama_motif,
                                'id_motif_barang' => $d->id_motif_barang,
                                'motif_keterangan' => $d->motif_keterangan);
            }
        }else{
            $arr['corak'] = array();
        }

        $d_data = DB::table('ms_barang as mb')->where('mb.id_kb', '32')->where('mb.nama_barang', 'like', '%plastik%');

        if($d_data->count() > 0){
            foreach($d_data->orderBy('mb.nama_barang', 'asc')->get() as $d){
                $arr['kemasan'][] = array('id' => $d->id,
                                'kode_barang' => $d->kode_barang,
                                'nama_barang' => $d->nama_barang);
            }
        }else{
            $arr['kemasan'] = array();
        }

        $d_data = DB::table("ms_barang")->where("id_kb", '30');
        if($d_data->count() > 0){
            foreach ($d_data->get() as $d) {
                $arr['kemasansatuan'][] = array('id_barang' => $d->id,
                                'kode_barang' => $d->kode_barang,
                                'nama_barang' => $d->nama_barang);
            }

        }else{
            $arr['kemasansatuan'] = array();
        }

        $d_data = DB::table('ms_barang as mb')->where('mb.id_kb', '30');

        if($d_data->count() > 0){
            foreach($d_data->orderBy('mb.kode_barang', 'asc')->get() as $d){
                $arr['kemasansepuluh'][] = array('id' => $d->id,
                                'kode_barang' => $d->kode_barang,
                                'nama_barang' => $d->nama_barang);
            }
        }else{
            $arr['kemasansepuluh'] = array();
        }

        $d_data = DB::table('ms_barang as mb')->where('mb.id_kb', '36');

        if($d_data->count() > 0){
            foreach($d_data->orderBy('mb.kode_barang', 'asc')->get() as $d){
                $arr['kemasanseratus'][] = array('id' => $d->id,
                                'kode_barang' => $d->kode_barang,
                                'nama_barang' => $d->nama_barang);
            }
        }else{
            $arr['kemasanseratus'] = array();
        }

        $d_data = DB::table('ms_barang as mb')->where('mb.id_kb', '38');

        if($d_data->count() > 0){
            foreach($d_data->orderBy('mb.kode_barang', 'asc')->get() as $d){
                $arr['cap'][] = array('id' => $d->id,
                                'kode_barang' => $d->kode_barang,
                                'nama_barang' => $d->nama_barang);
            }
        }else{
            $arr['cap'] = array();
        }

        $d_data = DB::table('ms_barang as mb')->where('mb.id_kb', '31');

        if($d_data->count() > 0){
            foreach($d_data->orderBy('mb.kode_barang', 'asc')->get() as $d){
                $arr['labell'][] = array('id' => $d->id,
                                'kode_barang' => $d->kode_barang,
                                'nama_barang' => $d->nama_barang);
            }
        }else{
            $arr['labell'] = array();
        }

        $d_data = DB::table('ms_satuan');

        if($d_data->count() > 0){
            foreach($d_data->orderBy('nama_satuan', 'asc')->get() as $d){
                $arr['satuan'][] = array('id_satuan' => $d->id_satuan,
                                'nama_satuan' => $d->nama_satuan);
            }
        }else{
            $arr['satuan'] = array();
        }

        $d_data = DB::table('ms_kualitas')->where('jenis', '1');

        if($d_data->count() > 0){
            foreach($d_data->orderBy('nama_kualitas', 'asc')->get() as $d){
                $arr['kualitas'][] = array('id_kualitas' => $d->id_kualitas,
                                'nama_kualitas' => $d->nama_kualitas);
            }
        }else{
            $arr['kualitas'] = array();
        }


        $d_data = DB::table('ms_kualitas')->where('jenis', '2');

        if($d_data->count() > 0){
            foreach($d_data->orderBy('nama_kualitas', 'asc')->get() as $d){
                $arr['kualitas_proses'][] = array('id_kualitas' => $d->id_kualitas,
                                'nama_kualitas' => $d->nama_kualitas);
            }
        }else{
            $arr['kualitas_proses'] = array();
        }

        $d_data = DB::table('ms_kualitas')->where('jenis', '3');

        if($d_data->count() > 0){
            foreach($d_data->orderBy('nama_kualitas', 'asc')->get() as $d){
                $arr['kualitas_periksa'][] = array('id_kualitas' => $d->id_kualitas,
                                'nama_kualitas' => $d->nama_kualitas);
            }
        }else{
            $arr['kualitas_periksa'] = array();
        }

        $arr['workorder'] = $this->get_spk(2);


        return response()->json($arr);
    }    

    function get_data(){
        $id_gudang = $this->session['id_gudang'];
        $id_proses = $this->session['id_proses'];

        DB::statement(DB::raw('set @rownum=0'));
        $d_data = DB::table('view_inv_saldoawal_sub');
        if($id_gudang != 0){
            // $d_data->whereIn('id_gudang', explode(",", $id_gudang));
        }

        // if($id_proses != 0){
        //     $d_data->whereIn('id_proses', explode(",", $id_proses));
        // }


        return Datatables::of($d_data)
                ->addColumn('aksi', function($d_data){
                    $btnAction = '<button class="btn bg-warning-400" onclick="edit('.$d_data->id.')"><i class="icon-pencil"></i> </button>
                        <button class="btn bg-danger-800" type="button" onclick="hapus('.$d_data->id.')"><i class="icon-eraser"></i></button>'.
                        '<input type="hidden" id="table_id'.$d_data->id.'" value="'.$d_data->id.'">'.
                        '<input type="hidden" id="table_idbarang'.$d_data->id.'" value="'.$d_data->id_barang.'">'.
                        '<input type="hidden" id="table_idgudang'.$d_data->id.'" value="'.$d_data->id_gudang.'">'.
                        '<input type="hidden" id="table_idproses'.$d_data->id.'" value="'.$d_data->id_proses.'">'.
                        '<input type="hidden" id="table_idsatuan'.$d_data->id.'" value="'.$d_data->id_satuan.'">'.
                        '<input type="hidden" id="table_idsatuanperubahan'.$d_data->id.'" value="'.$d_data->id_perubahansatuan.'">'.
                        '<input type="hidden" id="table_jumlah'.$d_data->id.'" value="'.$d_data->jumlah_saldoawal.'">'.
                        '<input type="hidden" id="table_jumlahrencana'.$d_data->id.'" value="'.$d_data->jumlah_rencana.'">'.
                        '<input type="hidden" id="table_idkualitas'.$d_data->id.'" value="'.$d_data->id_kualitas.'">'.
                        '<input type="hidden" id="table_idkomposisi'.$d_data->id.'" value="'.$d_data->id_komposisi.'">'.
                        '<input type="hidden" id="table_idmotif'.$d_data->id.'" value="'.$d_data->id_motif.'">'.
                        '<input type="hidden" id="table_idcorak'.$d_data->id.'" value="'.$d_data->id_corak.'">'.
                        '<input type="hidden" id="table_idlabel'.$d_data->id.'" value="'.$d_data->id_label.'">'.
                        '<input type="hidden" id="table_idcap'.$d_data->id.'" value="'.$d_data->id_cap.'">'.
                        '<input type="hidden" id="table_idkemasan'.$d_data->id.'" value="'.$d_data->id_kemasan.'">'.
                        '<input type="hidden" id="table_idkemasansatuan'.$d_data->id.'" value="'.$d_data->id_kemasansatuan.'">'.
                        '<input type="hidden" id="table_idkemasansepuluh'.$d_data->id.'" value="'.$d_data->id_kemasansepuluh.'">'.
                        '<input type="hidden" id="table_idkemasanseratus'.$d_data->id.'" value="'.$d_data->id_kemasanseratus.'">'.
                        '<input type="hidden" id="table_idkualitas_dudul'.$d_data->id.'" value="'.$d_data->id_kualitas_dudul.'">'.
                        '<input type="hidden" id="table_idkualitas_proses'.$d_data->id.'" value="'.$d_data->id_kualitas_proses.'">'.
                        '<input type="hidden" id="table_idkualitas_periksa'.$d_data->id.'" value="'.$d_data->id_kualitas_periksa.'">'.
                        '<input type="hidden" id="table_tanggal'.$d_data->id.'" value="'.tgl_full($d_data->tanggal,'').'">'.
                        '<input type="hidden" id="table_keterangan'.$d_data->id.'" value="'.$d_data->keterangan.'">'.
                        '<input type="hidden" id="table_workorderbarang'.$d_data->id.'" value="'.$d_data->id_workorder_barang.'">'
                        ;
                    return $btnAction;
                })->addColumn('aksi2', function($d_data){
                    // $jum_aksi = $this->get_barangrubah($d_data->id);
                    // $jum = array_sum($jum_aksi);
                    $jum = 0;
                    if($jum == 0){
                       $btnAction2 = '<button class="btn bg-warning-400" onclick="edit('.$d_data->id.')"><i class="icon-pencil"></i> </button>
                         <button class="btn bg-danger-800" type="button" onclick="hapus('.$d_data->id.')"><i class="icon-eraser"></i></button>'; 
                    }else{
                        $btnAction2 = '';
                    }
                     
                    return $btnAction2;
                })
                ->addColumn('tanggal_data', function($d_data){
                    $tanggal = tgl_full($d_data->tanggal,'');
                    
                     
                    return $tanggal;
                })
                ->make(true);

        return response()->json($arr);
    }


    function simpan(Request $request){
        $id = $request->get("popup_id_table");
        $data['id_barang'] = $request->get("popup_barang");
        $data['id_gudang'] = $request->get("popup_gudang");
        $data['id_proses'] = $request->get("popup_proses");
        $data['id_satuan'] = $request->get("popup_satuan");
        $data['id_perubahansatuan'] = $request->get("popup_satuanmenghasilkan");
        $data['jumlah_saldoawal'] = $request->get("popup_jumlah");
        $data['jumlah_rencana'] = $request->get("popup_menghasilkan");
        $data['id_kualitas'] = $request->get("popup_kual");
        $data['id_komposisi'] = $request->get("popup_komposisi");
        $data['id_motif'] = $request->get("popup_motif");
        $data['id_motif_barang'] = $request->get("popup_corak");
        $data['id_label'] = $request->get("popup_label");
        $data['id_cap'] = $request->get("popup_cap");
        $data['id_kemasan'] = $request->get("popup_kemasan");
        $data['id_kemasansatuan'] = $request->get("popup_kemasansatuan");
        $data['id_kemasansepuluh'] = $request->get("popup_kemasansepuluh");
        $data['id_kemasanseratus'] = $request->get("popup_kemasanseratus");
        $data['id_kualitas_periksa'] = $request->get("popup_periksa");
        $data['id_kualitas_proses'] = $request->get("popup_kualitas");
        $data['id_kualitas_dudul'] = $request->get("popup_dudul");
        $data['tanggal'] = !is_null($request->get('popup_tanggal')) ? tgl_full($request->get('popup_tanggal'), 2):tgl_full(date('d-m-Y'), 2);
        $data['keterangan'] = $request->get('popup_ket');

        $data['tanggal_full'] = !is_null($request->get('popup_tanggal')) ? tgl_full($request->get('popup_tanggal'), 5):tgl_full(date('d-m-Y'), 5);
        $data['id_workorder_barang'] = $request->get("popup_order");
        $data['id_workorder'] = "0";
        if($data['id_workorder_barang'] != 0 || $data['id_workorder_barang'] != ''){
            $d_data = DB::table('pem_workorder_barang as pwb')->leftJoin("pem_workorder as pw", "pwb.id_workorder", "pw.id_workorder")->where("pwb.id_workorder_barang", $data['id_workorder_barang'])->select(DB::raw("pw.id_workorder, pw.nomor, pwb.id_workorder_barang, pwb.no_workorder"));
            if($d_data->count() > 0){
                $data['id_workorder'] = $d_data->first()->id_workorder;
            }
        }

        if($id == ''){
            DB::table('inv_saldoawal')->insert($data);
        }else{
            DB::table('inv_saldoawal')->where('id',$id)->update($data);
        }

        return response()->json(array('status' => '1'));
    }

    function hapus(Request $request){
        $data['id'] = $request->get('id');
        DB::table('inv_saldoawal')->where($data)->delete();
    }

    function get_barangrubah($id){
        $d_rubah = DB::table('view_inv_saldoawal_sub')->where('id', $id);
        $jum = array();

        if($d_rubah->count() > 0){
            foreach($d_rubah->get() as $d){

                $jumlah = $d->jumlah_saldoawal;

                $where = "WHERE B.id_gudang = '$d->id_gudang' AND B.id_barang = '$d->id_barang' AND B.id_komposisi = '$d->id_komposisi' AND B.id_corak ='$d->id_corak' AND B.id_motif ='$d->id_motif' AND B.id_kualitas = '$d->id_kualitas' AND B.id_kualitas_dudul = '$d->id_kualitas_dudul' AND B.id_label = '$d->id_label' AND B.id_kemasan = '$d->id_kemasan' AND B.id_kemasansatuan = '$d->id_kemasansatuan' AND B.id_kemasansepuluh = '$d->id_kemasansepuluh' AND B.id_kemasanseratus = '$d->id_kemasanseratus' AND B.id_proses = '$d->id_proses' AND B.id_cap = '$d->id_cap' AND B.id_kualitas_periksa = '$d->id_kualitas_periksa' AND B.id_kualitas_proses = '$d->id_kualitas_proses' AND B.id_satuan = '$d->id_satuan'";

                $PDO = DB::connection('mysql')->getPdo();
                $billingStmt = $PDO->prepare("SELECT B.*
                                             FROM (
                                                 ".base_stok()."
                                             ) B
                                             $where ");
            
                $billingStmt->execute();
                $usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));

                if($usersBills[0]['stok'] < $jumlah){
                    $jum[] = 1;
                }else{
                    $jum[] = 0;
                }
            }
        }else{
            $jum[] = 1;
            return 'kosong';
        }
        return $jum;
    }

    function get_spk($jenis = ''){
        $arr = array();
        $arr2 = array();
        $d_list;
        $id_lokasi = $this->session['id_lokasi'];

        if($jenis == '1'){
            $d_order = DB::table('pro_barangkeluar as pb')->join('pem_workorder_barang as pwk', 'pb.id_workorder_barang', 'pwk.id_workorder_barang')->where('pb.status_beem', '!=', '')->groupBy('pwk.id_workorder_barang')->select(DB::raw('pwk.id_workorder_barang'));
             foreach ($d_order->get() as $d) {
                $arr[] = $d->id_workorder_barang;
            }

            $d_list = DB::table('pem_workorder_barang as pwb')->whereNotIn('pwb.id_workorder_barang', $arr)->join('pem_workorder as pw', 'pw.id_workorder', 'pwb.id_workorder')->orderBy("pwb.no_workorder"); //->where('pw.id_lokasi', '1');

            if($id_lokasi != 0){
                $d_list->where('pw.id_lokasi', $id_lokasi);
            }
        }else{
            $d_order = DB::table('pro_barangkeluar as pb')->join('pem_workorder_barang as pwk', 'pb.id_workorder_barang', 'pwk.id_workorder_barang')->where('pb.status_beem', '1')->groupBy('pwk.id_workorder_barang')->select(DB::raw('pwk.id_workorder_barang'));
             foreach ($d_order->get() as $d) {
                $arr[] = $d->id_workorder_barang;
            }

            $d_list = DB::table('pem_workorder_barang as pwb')->whereNotIn('pwb.id_workorder_barang', $arr)->join('pem_workorder as pw', 'pw.id_workorder', 'pwb.id_workorder')->orderBy("pwb.no_workorder"); //->where('pw.id_lokasi', '1');

            if($id_lokasi != 0){
                $d_list->where('pw.id_lokasi', $id_lokasi);
            }

        }

        return response()->json($d_list->get());        
    }
}
