<?php

namespace App\Http\Controllers\Pembelian;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use DB;
use Excel;
use Illuminate\Support\Facades\Session;

class LaporanOrderbeliController 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(){
        if (Session::get('id_lokasi') == 1 && Session::get('kode_group') == 5 || Session::get('kode_group') == 8 || Session::get('kode_group') == 9) {
            $data['lokasi_produksi'] = DB::table("ms_lokasi_produksi")->get();
            $data['lokasi_semua'] = 'show';
        } else if (Session::get('id_lokasi') != 1 || Session::get('id_lokasi') != 0 || Session::get('id_lokasi') != "" && Session::get('kode_group') == 5 || Session::get('kode_group') == 8 || Session::get('kode_group') == 9) {
            $data['lokasi_produksi'] = DB::table("ms_lokasi_produksi")->where('id_lokasi', Session::get('id_lokasi'))->get();
            $data['lokasi_semua'] = 'hide';
        } else if (Session::get('id_lokasi') != 1 || Session::get('id_lokasi') != 0 || Session::get('id_lokasi') != "" && Session::get('kode_group') != 5 || Session::get('kode_group') != 8 || Session::get('kode_group') != 9) {
            $data['lokasi_produksi'] = DB::table("ms_lokasi_produksi")->where('id_lokasi', Session::get('id_lokasi'))->get();
            $data['lokasi_semua'] = 'hide';
        } else if (Session::get('id_lokasi') == 0 || Session::get('id_lokasi') == "") {
            $data['lokasi_produksi'] = DB::table("ms_lokasi_produksi")->get();
            $data['lokasi_semua'] = 'show';
        }
        
        $data['kategori']=DB::table("ms_kategori_barang")->where('induk_kb','=','0')->get();
        $data['session_lokasi'] = Session::get('id_lokasi');

		return view('pembelian.OrderbeliLaporan', $data);
	}

	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, 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);
    }

    function get_penyedia(){
        $d_data = DB::table('ms_penyedia')->orderBy('nama', 'asc');
        if($d_data->count() > 0){
            foreach($d_data->get() as $d){
                $arr[] = array('id_penyedia' => $d->id_penyedia,
                                'nama' => $d->nama,
                                'pic' => $d->pic,
                                'alamat' => $d->alamat);
            }
        }else{
            $arr = array();
        }

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

    /* Cetak Rekap Order Pembelian */
    public function cetak_rekap_orderbeli(Request $request)
    {
        $input  = $request->all();
        $session = Session::all();

        if(!isset($input['format'])) { return redirect()->back(); }

        // dd($input);
        $lokasi = $input['lokasi']; // 0=>semua
        $label  = $input['label']; // 3=>semua
        $tgl_awal   = date_db($input['tanggal_awal']);
        $tgl_akhir  = date_db($input['tanggal_akhir']);
        $status_bayar = $input['status_bayar']; // 0=>semua
        $jenis      = $input['jenis_laporan']; // 0=>semua

        switch ($jenis) {
            case '0':
                # code...
                $data['judul']   = "Laporan Order Pembelian Keseluruhan";
                $orderPembelian = DB::table('view_orderbeli_barang_st_bayar AS opb')
                            ->selectRaw("opb.*, op.tanggal")
                            ->join("pem_orderbeli AS op", "op.id_orderbeli", "=", "opb.id_orderbeli")
                            ->whereBetween('op.tanggal', [$tgl_awal, $tgl_akhir])
                            ->groupBy("op.id_orderbeli")
                            ->orderByRaw("op.tanggal ASC, CAST(opb.no_faktur AS UNSIGNED) ASC, opb.sufix_no_order ASC");
                if(strtolower($input['format'])=="pdf"){
                    $htmlView = 'pembelian.LaporanOrderbeliRekapStatus';

                }else if(strtolower($input['format'])=="excel"){
                    $htmlView = 'pembelian.LaporanOrderbeliRekapStatusExcel';

                }

                break;

            case '1':
                # code...
                $data['judul']   = "Laporan Order Pembelian Keseluruhan Detail";
                $orderPembelian = DB::table('view_orderbeli_barang_st_bayar AS opb')
                            ->selectRaw("opb.*, op.tanggal")
                            ->join("pem_orderbeli AS op", "op.id_orderbeli", "=", "opb.id_orderbeli")
                            ->whereBetween('op.tanggal', [$tgl_awal, $tgl_akhir])
                            ->orderByRaw("op.tanggal ASC, CAST(opb.no_faktur AS UNSIGNED) ASC, opb.sufix_no_order ASC");
                if(strtolower($input['format'])=="pdf"){
                    $htmlView = 'pembelian.LaporanOrderbeliRekapDetail';

                }else if(strtolower($input['format'])=="excel"){
                    $htmlView = 'pembelian.LaporanOrderbeliRekapDetailExcel';

                }

                break;
            
            default:
                # code...
                break;
        }

        if($lokasi!=0){
            $orderPembelian->where("lokasi", $lokasi);
        }

        if($label!=3){
            $orderPembelian->where("cv", $label);
        }

        // $orderPembelian = $orderPembelian->get();

        $orderPembelian = $orderPembelian->get();
        $arr_pembelian = array_reduce($orderPembelian->toArray(), function($carry, $item) use ($status_bayar){
            $tagihan = $item->nominal_netto;
            $pembayaran = $item->total_bayar;

            $total_bayar = $pembayaran;
            if($total_bayar == 0){
                $bayar = 0;
            }else if($total_bayar < $tagihan){
                $bayar = 1;
            }else if($total_bayar > $tagihan){
                $bayar = 3;
            }else{
                $bayar = 2;
            }

            $item->status_bayar = status_bayar_txt($bayar);

            if($tagihan==0 || $tagihan==null){
                $bayar = 4;
                $item->status_bayar = "Tagihan Belum Dibuat";
            }

            if($status_bayar==0){
                $carry[] = $item;
            }else if(($status_bayar-1)==$bayar){
                $carry[] = $item;
            }
                return $carry;
        });

        $data['periode'] = date_db($tgl_awal, true)." s/d ".date_db($tgl_akhir, true);
        $data['pembelian'] = $arr_pembelian;

        // dd($data['pembelianDetail']);

        if(strtolower($input['format'])=="pdf"){
            require('fpdf181/Mc_table.php');
            $html = view($htmlView, $data);
            return response($html)->header('Content-Type', 'application/pdf');
        }else if(strtolower($input['format'])=='excel'){
            // return view($htmlView, $data);
            Excel::create($data['judul']." Periode : ".$data['periode'], function($excel) use ($htmlView, $data){
                $excel->sheet("Sheet 1", function($sheet) use ($htmlView, $data){
                    $sheet->loadView($htmlView, $data);
                });
            })->export("xlsx");
        }else{
            return redirect()->back();
        }
    }
    /* end : Cetak Rekap Order Pembelian */

    function cetak_detail(Request $request){
    	require('fpdf181/Mc_table.php');
        $input = $request->all();
        // dd($input);
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua
        $tgl_awal = date_db($input['tanggal_awal']);
        $tgl_akhir = date_db($input['tanggal_akhir']);
        $output = $input['page'];
        $data['judul'] = "Laporan Order Pembelian Keseluruhan";

        $jenis = $request->get('jenis');
        switch ($jenis) {
            case '0':
                $data['judul'] = "Rekapitulasi Order Pembelian Keseluruhan";
                $laporan = DB::table('view_laporan_orderbeli')->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->orderByRaw("kode_barang")->select(DB::raw("*, sum(jml_barang) total_barang, sum(jml_barang_konv) total_barang_konv, sum(jml_barang*harga_satuan) total_harga, sum(jml_barang*nilai_ppn) total_ppn, sum(sub_total) total_ttl"))->groupBy(array('id_barang', 'kode_barang'));
                
                if ($output == "print") {
                    $page = 'LaporanOrderbelirekap';
                } else {
                    $page = 'LaporanOrderbelirekapExcel';
                }
                break;
            case '1':
                $data['judul'] = "Detail Order Pembelian Keseluruhan";
                $laporan = DB::table('view_laporan_orderbeli')->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->orderByRaw("tanggal, no_faktur , kode_barang");
                
                if ($output == "print") {
                    $page = 'LaporanOrderbelidetail';
                } else {
                    $page = 'LaporanOrderbelidetailExcel';
                }
                break;
            default:
                # code...
                break;
        }
        // dd($data);

        if($lokasi!=0){
            $laporan->where("lokasi", $lokasi);
        }

        if($label!=3){
            $laporan->where("cv", $label);
        }

        $data['periode'] = date_db($tgl_awal, true)." s/d ".date_db($tgl_akhir, true);
        $data['data'] = $laporan->get();

        if ($output == "print") {
            $html = view('pembelian.'.$page)->with('data',$data);
            return response($html)->header('Content-Type', 'application/pdf');
        } else if ($output == "excel") {
            Excel::create($jenis == '0' ? "(Rekap) Laporan Order Pembelian Keseluruhan ".date_db($tgl_awal, true).'-'.date_db($tgl_akhir, true):"(Detail) Laporan Order Pembelian Keseluruhan ".date_db($tgl_awal, true).'-'.date_db($tgl_akhir, true), function($excel) use ($page, $data){
                $excel->sheet("Sheet 1", function($sheet) use ($page, $data){
                    $sheet->loadView("pembelian.".$page, $data);
                });
            })->export("xlsx");
        }
    }

    function cetak_laporan_kategori(Request $request)
    {
        require('fpdf181/Mc_table.php');
        $input = $request->all();
        // dd($input);
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua
        $kategori = $input['kategori']; // 0=>Tidak ada kategori
        $sub_kategori = $input['sub_kategori']; // 0=>semua
        $tgl_awal = date_db($input['tanggal_awal']);
        $tgl_akhir = date_db($input['tanggal_akhir']);
        $output = $input['page'];

        $data['judul'] = "Laporan Order Pembelian Per-Kategori";

        $laporan=DB::table('view_laporan_orderbeli')->whereBetween("tanggal", [$tgl_awal, $tgl_akhir]);

        if($lokasi!=0){
            $laporan->where("lokasi", $lokasi);
        }

        if($label!=3){
            $laporan->where("cv", $label);
        }

        if($kategori!=0 && $sub_kategori !=0){
            $laporan->where("id_sub_kategori","=",$sub_kategori);
            $kategori = DB::table("ms_kategori_barang")->where(["id_kb"=>$kategori])->first();
            $sub_kategori = DB::table("ms_kategori_barang")->where(["id_kb"=>$sub_kategori])->first();
            $data['judul'] .= " (".$kategori->nama." ".$sub_kategori->nama.")";
        }else{
            $laporan->where("id_kategori","=",$kategori);
            $kategori = DB::table("ms_kategori_barang")->where(["id_kb"=>$kategori])->first();
            $data['judul'] .= " (".$kategori->nama.")";
        }

        $data['periode'] = date_db($tgl_awal, true)." s/d ".date_db($tgl_akhir, true);
        $data['data'] = $laporan->get();

        if ($output == "print") {
            $page = "CetakLaporanOrderbeliPerKategori";
            $html = view('pembelian.'.$page)->with('data',$data);
            return response($html)->header('Content-Type', 'application/pdf');
        } else if ($output == "excel") {
            $page = "CetakLaporanOrderbeliPerKategoriExcel";
            Excel::create("Laporan Order Pembelian Per Kategori"." (".$kategori->nama.") ".date_db($tgl_awal, true).'-'.date_db($tgl_akhir, true), function($excel) use ($page, $data){
                $excel->sheet("Sheet 1", function($sheet) use ($page, $data){
                    $sheet->loadView("pembelian.".$page, $data);
                });
            })->export("xlsx");
        }
    }

    //Laporan Satuan

    function lap_kategori(){
        return view('pembelian.PembelianSatuanLaporan');
    }

    function cetak_kategoribarang(Request $request){
        require('fpdf181/Mc_table.php');
        $input = $request->all();
        // dd($input);
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua
        $tgl_awal = date_db($input['tanggal_awal']);
        $tgl_akhir = date_db($input['tanggal_akhir']);
        $barang = $input['barang'];

        // DB::enableQueryLog();
        $data['barang'] = DB::table("view_ms_barang_full")->find($barang);
        $set_nama_barang = $data['barang']->kategori." ".$data['barang']->sub_kategori." ".$data['barang']->nama_barang;
        if($data['barang']->id_kelompok==4){
            $set_nama_barang = $data['barang']->nama_barang;

        }
        $data['judul'] = "Laporan Order Pembelian Per-Barang ( $set_nama_barang )";

        $laporan = DB::table('view_laporan_orderbeli')->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->where('id_barang', $barang)->select(DB::raw("*"));
        // dd($data);

        if($lokasi!=0){
            $laporan->where("lokasi", $lokasi);
        }

        if($label!=3){
            $laporan->where("cv", $label);
        }

        $data['periode'] = date_db($tgl_awal, true)." s/d ".date_db($tgl_akhir, true);
        $data['data'] = $laporan->get();

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

     function cetak_kategoripenyedia(Request $request){
        require('fpdf181/Mc_table.php');
        $input = $request->all();
        // dd($input);
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua
        $tgl_awal = date_db($input['tanggal_awal_penyedia'], false, 2);
        $tgl_akhir = date_db($input['tanggal_akhir_penyedia'], false, 2);
        $id_penyedia = $input['penyedia'];

        $penyedia = DB::table("ms_penyedia")->where(["id_penyedia"=>$id_penyedia])->first();
        $data['judul'] = "Laporan Order Pembelian Per-Penyedia ( $penyedia->nama )";

        $laporan = DB::table('view_laporan_orderbeli')->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->where('id_penyedia', $id_penyedia)->select(DB::raw("*"));
        // dd($data);

        if($lokasi!=0){
            $laporan->where("lokasi", $lokasi);
        }

        if($label!=3){
            $laporan->where("cv", $label);
        }

        $data['periode'] = date_db($tgl_awal, true)." s/d ".date_db($tgl_akhir, true);
        $data['data'] = $laporan->get();

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