<?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 PersediaanController extends Controller
{
    private $session;
    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'));
    }
    
    public function index(){

        $data['gudang'] = DB::table('ms_gudang')->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')->where([['mp.nama_proses','<>','Pengemasan Satuan'],['mp.nama_proses','<>','Pengemasan Sepuluh'],['mp.nama_proses','<>','Pengemasan Seratus'],['mp.nama_proses','<>','Seri'],['mp.nama_proses','<>','Plat'],['mp.nama_proses','<>','Packing Karung']])->orderBy('mp.id_proses','ASC')->get();
        $data['workorder'] = DB::table('pem_workorder_barang')->get();

    	return view('inventori.PersediaanList')->with('data',$data);

    //    print_r($arr);
    }

    function detail($id){
        $data['id'] = $id;        
        return view('inventori.PersediaanDetail')->with('data', $data);   
    }

    function get_barang(){
        /*$id_gudang = Session::get('id_gudang');*/
        DB::statement(DB::raw('set @rownum=0'));
        $PDO = DB::connection('mysql')->getPdo();

        $billingStmt = $PDO->prepare("SELECT C.*, CASE WHEN C.id_proses = '' AND C.id_satuan_awal != '' THEN FORMAT(C.stok/(C.nilai_akhir/C.nilai_awal),2) ELSE '' END AS stok_satuan_awal
            FROM (
                SELECT B.*, 
                CASE WHEN B.id_proses != '' THEN '' WHEN mst.id_satuan IS NULL THEN '' ELSE mst.id_satuan END AS id_satuan_awal,
                CASE WHEN B.id_proses != '' THEN '' WHEN mst.id_satuan IS NULL THEN '' ELSE mst.nama_satuan END AS nama_satuan_awal,
                CASE WHEN mk.nilai_awal IS NULL THEN 1 ELSE mk.nilai_awal END AS nilai_awal,
                CASE WHEN mk.nilai_akhir IS NULL THEN 1 ELSE mk.nilai_akhir END AS nilai_akhir,
                CASE WHEN B.id_perubahansatuan != '' THEN stok*jumlah_rencana ELSE '' END AS jumlah_rencana_akhir
                FROM ( ".base_stok_user($this->session['id_gudang'])." 
                ) B 
                JOIN ms_barang as mb ON B.id_barang = mb.id
                LEFT JOIN ms_konversi as mk ON mb.id = mk.kode
                LEFT JOIN ms_satuan as mst ON mk.satuan_awal = mst.id_satuan
                WHERE B.stok > '0'
            ) C  ");
        $billingStmt->execute();
        $usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));

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

    function get_barang_cetak(Request $request){
        $input = $request->all();
        // $akun = DB::table("ms_transaksi")->where([["id_transaksi", "=", $input['id_transaksi']]])->first();
        $d_data = DB::table('view_ms_barang_full 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')
            // ->where([['mb.id_akun', '=', $akun->id]])
            ->select(DB::raw('mb.*, mkb2.id_kb AS id_kategori, mkb2.nama nama_kategori, mkb.id_kb id_sub, mkb.nama nama_sub, mkb.id_kelompok, 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[] = array('id_barang' => $d->id,
                                'kode_barang' => $d->kode_barang,
                                'nama_barang' => $d->nama_barang,
                                'id_kelompok' => $d->id_kelompok,
                                '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 = array();
        }
        
        return response()->json($arr);
    }

    public function rekanan(){
        return view('inventori.PersediaanRekananList');
    }

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

        $d_data = DB::table("view_rekanan")->where('id_proses', '!=', '37');
        

        $d_tenun = DB::table("view_rekanan as vr")->leftJoin('pro_barangmasuk as pbm', 'vr.id_barangkeluar', 'pbm.id_barangkeluar')->groupBy('id_proses', 'id_workorder', 'id_workorder_barang');

        $d_kemas = DB::table("view_rekanan_kemasan");


        if($id_proses != 0){
            if(in_array('37', [$id_proses])){
                $d_tenun->where('id_proses', '=', '37');
            }else{
                $d_tenun->where('id_proses', '=', '1000000');
            }

            $d_kemas->whereIn('id_proses', explode(",", $id_proses));
            $d_data->whereIn('id_proses', explode(",", $id_proses));
        }else{
            $d_tenun->where('id_proses', '=', '37');
        }

        $arr = array();
        if($d_data->count() > 0){
            foreach($d_data->get() as $u){
                $pekerja = '';
                $stat_pekerja = '';
                $workorder_barang = '';

                if($u->id_rekanan != '' && $u->id_rekanan != '0'){
                    $pekerja = $u->nama_rekanan;
                    $stat_pekerja = 'Rekanan';
                }else{
                    $pekerja = $u->nama_pegawai;
                    $stat_pekerja = 'Pegawai';
                }

                if($u->id_workorder_barang != 0){
                    $workorder_barang = $u->no_workorder;
                }

                 $arr[] = array('tanggal' => date('d-m-Y', strtotime($u->tanggal)),
                                'tanggal_p' => date('d-m-Y', strtotime($u->tanggal_perkiraan)),
                                'workorder_barang' => $workorder_barang,
                                'nama_lokasi' => $u->nama_lokasi,
                                'pekerja' => $pekerja,
                                'stat_pekerja' => $stat_pekerja,
                                'nama_proses' => $u->nama_proses,
                                'posisi' => $pekerja.' ('.$stat_pekerja.')',
                                'aksi' => '<button class="btn bg-info-400" type="button" onclick="detail('.$u->id_barangkeluar.', 0)"><i class="icon-eye"></i></button>');
            }
        }else{
            
        }

        if($d_tenun->count() > 0){
            foreach($d_tenun->get() as $u){
                $pekerja = '';
                $stat_pekerja = '';
                $workorder_barang = '';

                if($u->id_rekanan != '' && $u->id_rekanan != '0'){
                    $pekerja = $u->nama_rekanan;
                    $stat_pekerja = 'Rekanan';
                }else{
                    $pekerja = $u->nama_pegawai;
                    $stat_pekerja = 'Pegawai';
                }

                if($u->id_workorder_barang != 0){
                    $workorder_barang = $u->no_workorder;
                }

                 $arr[] = array('tanggal' => date('d-m-Y', strtotime($u->tanggal)),
                                'tanggal_p' => date('d-m-Y', strtotime($u->tanggal_perkiraan)),
                                'workorder_barang' => $workorder_barang,
                                'nama_lokasi' => $u->nama_lokasi,
                                'pekerja' => $pekerja,
                                'stat_pekerja' => $stat_pekerja,
                                'nama_proses' => $u->nama_proses,
                                'posisi' => $pekerja.' ('.$stat_pekerja.')',
                                'aksi' => '<button class="btn bg-info-400" type="button" onclick="detail('.$u->id_barangkeluar.', 0)"><i class="icon-eye"></i></button>');
            }
        }else{
            
        }

        if($d_kemas->count() > 0){
            foreach($d_kemas->get() as $u){
                $pekerja = '';
                $stat_pekerja = '';
                $workorder_barang = '';

                if($u->id_rekanan != '' && $u->id_rekanan != '0'){
                    $pekerja = $u->nama_rekanan;
                    $stat_pekerja = 'Rekanan';
                }else{
                    $pekerja = $u->nama_pegawai;
                    $stat_pekerja = 'Pegawai';
                }

                 $arr[] = array('tanggal' => date('d-m-Y', strtotime($u->tanggal)),
                                'tanggal_p' => date('d-m-Y', strtotime($u->tanggal_perkiraan)),
                                'workorder_barang' => '',
                                'nama_lokasi' => $u->nama_lokasi,
                                'pekerja' => $pekerja,
                                'stat_pekerja' => $stat_pekerja,
                                'nama_proses' => $u->nama_proses,
                                'posisi' => $pekerja.' ('.$stat_pekerja.')',
                                'aksi' => '<button class="btn bg-info-400" type="button" onclick="detail('.$u->id_barangkeluar_kemasan.', 1)"><i class="icon-eye"></i></button>');
            }
        }else{
            
        }

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

    }

    function get_rekanan_detail(Request $request){
        $id = $request->get('id');
        $jenis = $request->get('jenis');

        if($jenis == '0'){
            $d_barangkeluar = DB::table('pro_barangkeluar')->where('id_barangkeluar', $id)->first();
            $where['pb.id_proses'] = $d_barangkeluar->id_proses;
            $where['pb.id_workorder_barang'] = $d_barangkeluar->id_workorder_barang;
            $where['pb.id_rekanan'] = $d_barangkeluar->id_rekanan;
            $where['pb.id_pegawai'] = $d_barangkeluar->id_pegawai;

            if($d_barangkeluar->id_proses != '37'){
                $d_data = DB::table('view_keluar')->where('id', $id);
            }else{
            //    print_r($where);
                $d_data = get_barangkeluar_byorder()->where($where);

                
            }
        }else if($jenis == '1'){
            $d_data = DB::table('view_keluarkemasan')->where('id', $id);
        }else{
            $d_data = array();
        }

        
        if($d_data->count() > 0){            
            $arr['data'] = $d_data->get();
            $arr['status'] = array('1');
        }else{
            $arr['status'] = array('0');
        }

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

    function get_rekanan_masuk(Request $request){
        $id = $request->get('id');
        $jenis = $request->get('jenis');

        if($jenis == '0'){
            $id_barangmasuk = DB::table('pro_barangmasuk')->where('id_barangkeluar', $id)->get();
            $id_masuk = array();
            foreach($id_barangmasuk as $i){
                $id_masuk[] = $i->id_barangmasuk;
            }

            $d_data = DB::table('view_masuk')->whereIn('id', $id_masuk);
        }else if($jenis == '1'){
            $id_barangmasuk = DB::table('pro_barangmasuk_kemasan')->where('id_barangkeluar_kemasan', $id)->get();
            $id_masuk = array();
            foreach($id_barangmasuk as $i){
                $id_masuk[] = $i->id_barangmasuk_kemasan;
            }

            $d_data = DB::table('view_masukkemasan')->whereIn('id', $id_masuk);
        }else{
            $d_data = array();
        }

        if($d_data->count() > 0){            
            $arr['data'] = $d_data->get();
            $arr['status'] = array('1');
        }else{
            $arr['status'] = array('0');
        }

        return response()->json($arr);
        // print_r($id_masuk);
        // print_r("\n");
        // print_r($d_data->get());
    }

    function get_sisa(Request $request){
        $req = $request->all();

        $id = $req['id'];
        $jenis = $req['jenis'];

        if($jenis == 0){
            $d_keluar = DB::table('pro_barangkeluar')->where('id_barangkeluar', $id)->first();

            $except = array(24, 25);

            if($d_keluar->id_proses == '37'){
                $d_barangkeluar = DB::table('pro_barangkeluar')->where('id_barangkeluar', $id)->first();
                $where['pb.id_proses'] = $d_barangkeluar->id_proses;
                $where['pb.id_workorder_barang'] = $d_barangkeluar->id_workorder_barang;
                $where['pb.id_rekanan'] = $d_barangkeluar->id_rekanan;
                $where['pb.id_pegawai'] = $d_barangkeluar->id_pegawai;

                $d_data = get_barangkeluar_byorder()->where($where)->where('vk.id_motif', '!=', '');

                //print_r($d_data->get());
                
                if($d_data->count() > 0){
                     foreach($d_data->get() as $d){

                        $jum = 0;
                        $jum = $d->jumlah * $d->jumlah_rencana;
                        

                        $where = "WHERE vm.id_proses = ".$where['pb.id_proses']." AND vm.id_workorder_barang = ".$where['pb.id_workorder_barang']." AND pkk.id_rekanan = ".$where['pb.id_rekanan']." AND pkk.id_pegawai = ".$where['pb.id_pegawai']."";
                        $tenun = "SELECT A.*, CASE WHEN sum(A.masuk - A.keluar) % 1 = 0 THEN sum(A.masuk - A.keluar) ELSE ROUND(sum(A.masuk - A.keluar), 2) END AS stok
                                FROM (
                                ".base_stok_tenun($where)."
                                ) A";

                        //print_r(get_rawquery($tenun));
                        foreach(get_rawquery($tenun) as $t){
                            $arr[] = array('kode_barang' => $t['kode_barang'],
                                           'nama_barang' => $t['nama_barang'],
                                           'jumlah' => $jum - $t['stok'],
                                           'nama_satuan' => $t['nama_satuan'],
                                           'jumlah_rencana' => '',
                                           'nama_perubahansatuan' => '',
                                           'nama_gudang' => '',
                                           'nama_komposisi' => '',
                                           'nama_corak' => '',
                                           'nama_motif' => $t['nama_motif'],
                                           'nama_kualitas' => '',
                                           'kualitas_dudul' => '',
                                           'nama_label' => '',
                                           'nama_cap' => '',
                                           'nama_kemasan' => '',
                                           'nama_kemasansatuan' => '',
                                           'nama_kemasansepuluh' => '',
                                           'nama_kemasanseratus' => '');

                        }

                    $result['data'] = $arr;
                    $result['status'] = '1';    
                     }
                }else{
                    $result['status'] = '0';    
                }

                
            }
            else if(!in_array($d_keluar->id_proses, $except)){
                $query = "SELECT B.*
                    FROM (
                    ".base_stok_masuk()."
                    ) B WHERE B.id_barangkeluar = $id AND B.stok > 0";

                $result['data'] = get_rawquery($query);
                $result['status'] = '1';
            }else{
                $result['status'] = '0';
            }
        }else if ($jenis == 1){
            $d_keluar = DB::table('pro_barangkeluar_kemasan')->where('id_barangkeluar_kemasan', $id)->first();

            $query = "SELECT B.*
                    FROM (
                    ".base_stok_masuk_multi()."
                    ) B WHERE B.id_barangkeluar_kemasan = $id AND B.stok > 0";

            $result['data'] = get_rawquery($query);
            $result['status'] = '1';
        }

        return $result;
        

    }

    

    function cetak_persediaan(Request $request){
        $jenis = request()->get('popup_id');
        $id_gudang = request()->get('kartu_inv_gudang');
        $id_barang = request()->get('popup_barang');
        $id_proses = request()->get('kartu_inv_proses');
        $id_order = request()->get('kartu_inv_order');
        $tgl_awal = tgl_full(request()->get('tgl_awal'),'99');
        $tgl_akhir = tgl_full(request()->get('tgl_akhir'),'99');
        $id_barang_mutasi = request()->get('popup_barang_mutasi');

        $where = '';
        $data['header'] = '';
        $data['sub_header'] = '';
        $data['tanggal'] = '';
        $view_laporan  = '';
        switch ($jenis) {
            case '0':
                $data['header'] = "Gudang";
                $data['sub_header'] = DB::table('ms_gudang')->where('id_gudang', $id_gudang)->first()->nama_gudang;
                $where = "AND B.id_gudang = '$id_gudang'";
                break;
            case '1':
                $data['header'] = "Barang";
                $data['sub_header'] = DB::table('ms_barang')->where('id', $id_barang)->first()->nama_barang;
                $where = "AND B.id_barang = '$id_barang'";
                break;
            case '2':
                $data['header'] = "Proses";
                $data['sub_header'] = DB::table('ms_proses')->where('id_proses', $id_proses)->first()->nama_proses;
                $where = "AND B.id_proses = '$id_proses'";
                break;
            case '3':
                $data['header'] = "Order";
                $data['sub_header'] = DB::table('pem_workorder_barang')->where('id_workorder_barang', $id_order)->first()->no_workorder;
                $where = "AND B.id_workorder_barang = '$id_order'";
                break;
            case '4':
                $data['header'] = "Mutasi Persediaan";
                $data['sub_header'] = DB::table('ms_barang')->where('id', $id_barang_mutasi)->first()->nama_barang;
                $where = "AND B.id_barang = '$id_barang_mutasi'";
                $where_awal = "B.id_barang = '$id_barang_mutasi' AND B.tanggal Between '$tgl_awal' AND '$tgl_akhir'";
                $whereAwal = "B.id_barang = '$id_barang_mutasi' AND B.tanggal < '$tgl_awal'";
                $data['tanggal'] = tgl_full($tgl_awal, '').' s/d '.tgl_full($tgl_akhir, '');
                break;
            default:
                
                break;
        }

        date_default_timezone_set('Asia/Jakarta');
        DB::statement(DB::raw('set @rownum=0'));
        $PDO = DB::connection('mysql')->getPdo();
        
        $billingStmt = $PDO->prepare("SELECT *,  @rownum  := @rownum  + 1 AS rownum 
                    FROM ( ".base_stok()."
                     ) B WHERE B.stok > '0' $where");
        $billingStmt->execute();
        $usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));
                
        $data['detail'] = $usersBills;
        
        //----- Persediaan Awal
        if($jenis == 4){
         /*$billingStmt_2 = $PDO->prepare("SELECT *,  @rownum  := @rownum  + 1 AS rownum 
                    FROM ( ".base_stok_awal()."
                     ) B WHERE $where_awal");
        
        $billingStmt_2->execute();
        $usersBills_2 = $billingStmt_2->fetchAll((\PDO::FETCH_ASSOC));
        $billingStmt_Awal = $PDO->prepare("SELECT *,  @rownum  := @rownum  + 1 AS rownum 
                    FROM ( ".base_stok_awal2()."
                     ) B WHERE $whereAwal");
        $billingStmt_Awal->execute();
        $usersBills_Awal = $billingStmt_Awal->fetchAll((\PDO::FETCH_ASSOC));*/

        $query_mutasi['saldo_awal'] = "SELECT *,  @rownum  := @rownum  + 1 AS rownum 
                    FROM ( ".base_stok_awal()."
                     ) B WHERE $whereAwal";
        $query_mutasi['transaksi'] = "SELECT *,  @rownum  := @rownum  + 1 AS rownum 
                    FROM ( ".base_stok_awal2()."
                     ) B WHERE $where_awal";
        $query_mutasi_union = implode(" UNION ALL ", $query_mutasi);
        $query_laporan_mutasi = "SELECT 
                                A.*
                            FROM ($query_mutasi_union) AS A
                            ";

        

        
        }
        require('fpdf181/Mc_table.php');

        switch ($jenis) {
            case '0':
            case '1':
            case '2':
            case '3':
                $view_laporan = view('inventori.cetak.PersediaanCetak')->with('data', $data);
            break;
            case '4':
            /*$data['detail2'] = $usersBills_2;
            $data['awal'] = $usersBills_Awal;*/
            $data['detail3'] = DB::select($query_laporan_mutasi);
                $view_laporan = view('inventori.cetak.MutasiCetak')->with('data', $data);
                 /*$view_laporan = view('inventori.cetak.PersediaanCetak')->with('data', $data);*/
                /*print_r($data['detail3']);*/
            break;
            default:
            
            break;
        }

        $html = $view_laporan;
        return response($html)->header('Content-Type', 'application/pdf');
    }
}

