<?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 LaporanController 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);	
        });
    }

	function index(){
		return view('inventori.LaporanList');
	}

	function get_barang(){
        $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[] = 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 = array();
        }

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

    function get_detail(Request $request){
    	DB::statement(DB::raw('set @rownum=0'));
    	$where = '';
    	$id_barang = $request->get('id_barang');
    	if($id_barang != 0){
    		$where = "AND id_barang = '$id_barang'";
    	}

    	$tgl_awal = date('Y-m-d', strtotime($request->get('tgl_awal')));
    	$tgl_akhir = date('Y-m-d', strtotime($request->get('tgl_akhir')));

    	$PDO = DB::connection('mysql')->getPdo();

        $billingStmt = $PDO->prepare("SELECT *, @rownum  := @rownum  + 1 AS rownum
										FROM(
											SELECT vp.* , pp.no_faktur ket_1, mp.nama ket_2, 'Pembelian' ket_3
											FROM view_penerimaan vp
											JOIN pem_pembelian pp ON vp.id = pp.id_pembelian
											JOIN ms_penyedia mp ON pp.id_penyedia = mp.id_penyedia
											UNION ALL
											SELECT vt.*, '' ket_1, vt.nama_gudang ket_2, 'Transfer Keluar' ket_3
											FROM view_transferkeluar vt
											JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
											UNION ALL
											SELECT vt.*, '' ket_1, vt.nama_gudang ket_2, 'Transfer Masuk' ket_3
											FROM view_transfermasuk vt
											JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
											UNION ALL
											SELECT vp.*, pp.no_faktur ket_1, mp.nama ket_2, 'Penjualan' ket_3
											FROM view_penjualan vp
											JOIN pen_penjualan pp ON vp.id = pp.id_penjualan
											JOIN ms_pelanggan mp ON pp.id_pelanggan = mp.id_pelanggan
											UNION ALL
											SELECT vk.*, '' ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN mr.nama
											ELSE mp.nama_pegawai END AS ket_2, 'Bon Bahan' ket_3
											FROM view_keluar vk
											JOIN pro_barangkeluar pbk ON vk.id = pbk.id_barangkeluar
											LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
											LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
											UNION ALL
											SELECT vm.*, '' ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN mr.nama
											ELSE mp.nama_pegawai END AS ket_2, 'Laporan Hasil' ket_3
											FROM view_masuk vm
											JOIN pro_barangmasuk pbm ON vm.id = pbm.id_barangmasuk
											JOIN pro_barangkeluar pbk ON pbm.id_barangkeluar = pbk.id_barangkeluar
											LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
											LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
											UNION ALL
											SELECT *, '' ket_1, vo.nama_gudang ket_2, 'Stok Opname' ket_3
											FROM view_opname vo
										) A
										WHERE A.tanggal BETWEEN '$tgl_awal' AND '$tgl_akhir' $where");
        $billingStmt->execute();
        $usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));
        return response()->json($usersBills);
        // $data = new Collection($usersBills);
        // return Datatables::of($data)
        //         ->make(true);
    }

	function get_detail_group(Request $request){
		DB::statement(DB::raw('set @rownum=0'));

    	$where = '';
    	$id_barang = $request->get('id_barang');
    	if($id_barang != 0){
    		$where = "AND A.id_barang = '$id_barang'";
    	}

    	$tgl_awal = date('Y-m-d', strtotime($request->get('tgl_awal')));
    	$tgl_akhir = date('Y-m-d', strtotime($request->get('tgl_akhir')));

    	$PDO = DB::connection('mysql')->getPdo();

        $billingStmt = $PDO->prepare("SELECT A.*, sum(masuk) as persediaan_masuk, sum(keluar) as persediaan_keluar, sum(masuk-keluar) as persediaan, @rownum  := @rownum  + 1 AS rownum
										FROM(
											SELECT vp.* , pp.no_faktur ket_1, mp.nama ket_2, 'Pembelian' ket_3
											FROM view_penerimaan vp
											JOIN pem_pembelian pp ON vp.id = pp.id_pembelian
											JOIN ms_penyedia mp ON pp.id_penyedia = mp.id_penyedia
											UNION ALL
											SELECT vt.*, '' ket_1, vt.nama_gudang ket_2, 'Transfer Keluar' ket_3
											FROM view_transferkeluar vt
											JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
											UNION ALL
											SELECT vt.*, '' ket_1, vt.nama_gudang ket_2, 'Transfer Masuk' ket_3
											FROM view_transfermasuk vt
											JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
											UNION ALL
											SELECT vp.*, pp.no_faktur ket_1, mp.nama ket_2, 'Penjualan' ket_3
											FROM view_penjualan vp
											JOIN pen_penjualan pp ON vp.id = pp.id_penjualan
											JOIN ms_pelanggan mp ON pp.id_pelanggan = mp.id_pelanggan
											UNION ALL
											SELECT vk.*, '' ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN mr.nama
											ELSE mp.nama_pegawai END AS ket_2, 'Bon Bahan' ket_3
											FROM view_keluar vk
											JOIN pro_barangkeluar pbk ON vk.id = pbk.id_barangkeluar
											LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
											LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
											UNION ALL
											SELECT vm.*, '' ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN mr.nama
											ELSE mp.nama_pegawai END AS ket_2, 'Laporan Hasil' ket_3
											FROM view_masuk vm
											JOIN pro_barangmasuk pbm ON vm.id = pbm.id_barangmasuk
											JOIN pro_barangkeluar pbk ON pbm.id_barangkeluar = pbk.id_barangkeluar
											LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
											LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
											UNION ALL
											SELECT *, '' ket_1, vo.nama_gudang ket_2, 'Stok Opname' ket_3
											FROM view_opname vo
										) A
										WHERE A.tanggal BETWEEN '$tgl_awal' AND '$tgl_akhir' $where GROUP BY A.id_barang, A.id_gudang, A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, 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, A.id_kualitas_proses, A.id_kualitas_periksa");
        $billingStmt->execute();
        $usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));
        return response()->json($usersBills);
        // $data = new Collection($usersBills);
        // return Datatables::of($data)
        //         ->make(true);
    }

	function cetak(Request $request){
		require('fpdf181/Mc_table.php');
		$data = '';
		if($_POST){
			$where = '';
	    	$id_barang = $request->get('barang');
	    	if($id_barang != 0){
	    		$where = "AND id_barang = '$id_barang'";
	    	}

	    	$tgl_awal = date('Y-m-d', strtotime($request->get('tanggal_awal')));
	    	$tgl_akhir = date('Y-m-d', strtotime($request->get('tanggal_akhir')));

	    	$PDO = DB::connection('mysql')->getPdo();

	    	$alur = array();
	    	$groupby = array();

	    	$select1 = "SELECT *";
	    	$select2= "SELECT A.*, sum(masuk) as persediaan_masuk, sum(keluar) as persediaan_keluar, sum(masuk-keluar) as persediaan";
	    	$group = "GROUP BY A.id_barang, A.id_gudang, A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, 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, A.id_kualitas_proses, A.id_kualitas_periksa";
	    	$query1 = "
						FROM(
						SELECT vp.* , pp.no_faktur ket_1, mp.nama ket_2, 'Pembelian' ket_3
							FROM view_penerimaan vp
							JOIN pem_pembelian pp ON vp.id = pp.id_pembelian
							JOIN ms_penyedia mp ON pp.id_penyedia = mp.id_penyedia
							UNION
							SELECT vt.*, '' ket_1, vt.nama_gudang ket_2, 'Transfer Keluar' ket_3
							FROM view_transferkeluar vt
							JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
							UNION
							SELECT vt.*, '' ket_1, vt.nama_gudang ket_2, 'Transfer Masuk' ket_3
							FROM view_transfermasuk vt
							JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
							UNION
							SELECT vp.*, pp.no_faktur ket_1, mp.nama ket_2, 'Penjualan' ket_3
							FROM view_penjualan vp
							JOIN pen_penjualan pp ON vp.id = pp.id_penjualan
							JOIN ms_pelanggan mp ON pp.id_pelanggan = mp.id_pelanggan
							UNION
							SELECT vk.*, '' ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN mr.nama
							ELSE mp.nama_pegawai END AS ket_2, 'Bon Bahan' ket_3
							FROM view_keluar vk
							JOIN pro_barangkeluar pbk ON vk.id = pbk.id_barangkeluar
							LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
							LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
							UNION
							SELECT vm.*, '' ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN mr.nama
							ELSE mp.nama_pegawai END AS ket_2, 'Laporan Hasil' ket_3
							FROM view_masuk vm
							JOIN pro_barangmasuk pbm ON vm.id = pbm.id_barangmasuk
							JOIN pro_barangkeluar pbk ON pbm.id_barangkeluar = pbk.id_barangkeluar
							LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
							LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
							UNION
							SELECT *, '' ket_1, vo.nama_gudang ket_2, 'Stok Opname' ket_3
							FROM view_opname vo
						) A
						WHERE A.tanggal BETWEEN '$tgl_awal' AND '$tgl_akhir' $where";
		    $prep = $PDO->prepare($select1.$query1);
	        $prep->execute();
	        $fetch = $prep->fetchAll((\PDO::FETCH_ASSOC));
	       	$alur = $fetch;

			$preps = $PDO->prepare($select2.$query1.$group);
	        $preps->execute();
	        $fetch2 = $preps->fetchAll((\PDO::FETCH_ASSOC));
	       	$groupby = $fetch2;


	       	$data['barang'] = DB::table('ms_barang as mb')->where('mb.id', $id_barang)->first()->nama_barang;
	       	$data['alur'] = $alur;
	       	$data['groupby'] = $groupby;
	       	$data['periode'] = date_range($tgl_awal, $tgl_akhir);
	       	$data['satuan'] = DB::table('ms_barang as mb')->join('ms_satuan as ms', 'mb.id_satuan', 'ms.id_satuan')->where('mb.id', $id_barang)->first()->nama_satuan;
		}else{
			$data['barang'] = "";
			$data['alur'] = array();
	       	$data['groupby'] = array();
	       	$data['periode'] = "";
	       	$data['satuan'] = "";
		}

		$html = view('inventori.LaporanCetak')->with('data',$data);
		return response($html)->header('Content-Type', 'application/pdf');
	}

	function test(){
		$data = DB::table('ms_barang');
		
		dd($data->toSql());
	}
}