<?php

namespace App\Http\Controllers\pembelian;

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

class LaporanpembelianController 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.PembelianLaporan', $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, 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_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 Pembelian */
    public function cetak_rekap_pembelian(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':
                $data['judul']   = "Laporan Pembelian Keseluruhan";
                $pembelian = DB::table("view_pembelian2 AS pd")
                        ->selectRaw("pd.*, CONCAT(no_faktur, sufix_no_faktur) AS nomor_faktur")
                        ->whereBetween('tanggal', [$tgl_awal, $tgl_akhir])
                        ->orderByRaw("CAST(no_faktur AS UNSIGNED) ASC, sufix_no_faktur ASC, tanggal_faktur ASC");
                if(strtolower($input['format'])=="pdf"){
                    $htmlView = 'pembelian.LaporanPembelianRekap';

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

                }

                break;

            case '1':
                $data['judul']   = "Laporan Pembelian Keseluruhan Detail";
                $pembelian = DB::table('view_laporan_pembelian AS pd')
                            ->selectRaw("pd.*, p.nominal_netto, p.total_bayar")
                            ->join("view_pembelian2 AS p", "p.id_pembelian", "=", "pd.id_pembelian")
                            ->whereBetween('pd.tanggal', [$tgl_awal, $tgl_akhir])
                            ->orderByRaw("CAST(pd.no_faktur AS UNSIGNED) ASC, pd.sufix_no_faktur ASC, pd.tanggal_faktur ASC");
                if(strtolower($input['format'])=="pdf"){
                    $htmlView = 'pembelian.LaporanPembelianRekapDetail';

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

                }

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

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

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

        $pembelian = $pembelian->get();

        $arr_pembelian = array_reduce($pembelian->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);
            $item->sisa_tagihan = $tagihan-$pembayaran;

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

    public function cetak_rekap_pembelian2(Request $request)
    {
        $input  = $request->all();
        $session = Session::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']);
        $status_bayar = $input['status_bayar']; // 0=>semua
        $jenis      = $input['jenis_laporan']; // 0=>semua

        $data['judul']   = "Laporan Pembelian Keseluruhan";
        $pembelian = DB::table("view_pembelian2")
                ->selectRaw("view_pembelian2.*, CONCAT(no_faktur, sufix_no_faktur) AS nomor_faktur")
                ->whereBetween('tanggal', [$tgl_awal, $tgl_akhir])
                ->orderByRaw("CAST(no_faktur AS UNSIGNED) ASC, sufix_no_faktur ASC, tanggal_faktur ASC");
        $htmlView = 'pembelian.LaporanPembelianRekap';

        if($jenis==1){
            $data['judul']   = "Laporan Pembelian Keseluruhan Detail";
            $pembelianDetail = DB::table('view_laporan_pembelian AS pd')
                        ->selectRaw("pd.*, p.nominal_netto, p.total_bayar")
                        ->join("view_pembelian2 AS p", "p.id_pembelian", "=", "pd.id_pembelian")
                        ->whereBetween('pd.tanggal', [$tgl_awal, $tgl_akhir])
                        ->orderByRaw("CAST(pd.no_faktur AS UNSIGNED) ASC, pd.sufix_no_faktur ASC, pd.tanggal_faktur ASC");
            $htmlView = 'pembelian.LaporanPembelianRekapDetail';
        }

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

            if($jenis==1){
                $pembelianDetail->where("pd.lokasi", $lokasi);
            }
        }

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

            if($jenis==1){
                $pembelianDetail->where("pd.cv", $label);
            }
        }

        $pembelian = $pembelian->get();

        if($jenis==1){
            $pembelianDetail = $pembelianDetail->get();
            $arr_pembelianDetail = array_reduce($pembelianDetail->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($status_bayar==0){
                    $carry[] = $item;
                }else if(($status_bayar-1)==$bayar){
                    $carry[] = $item;
                }
                    return $carry;
            });

            $data['pembelianDetail'] = $arr_pembelianDetail;
        }

        $arr_pembelian = array_reduce($pembelian->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);
            $item->sisa_tagihan = $tagihan-$pembayaran;

            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']);

        require('fpdf181/Mc_table.php');
        $html = view($htmlView, $data);
        return response($html)->header('Content-Type', 'application/pdf');
    }
    /* end : Cetak Rekap Pembelian */

    function cetak_detail(Request $request){
    	require('fpdf181/Mc_table.php');
        $input = $request->all();
        $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 Pembelian Keseluruhan";

        $jenis = $request->get('jenis');
        switch ($jenis) {
            case '0':
                $data['judul'] = "Rekapitulasi Pembelian Keseluruhan";
                $laporan = DB::table('view_laporan_pembelian')->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(total) total_ttl"))->groupBy(array('id_barang', 'kode_barang'));
                if ($output == "print") {
                    $page = 'Laporanbelirekap';
                } else {
                    $page = 'Laporanbelirekapexcel';
                }
                
                break;
            case '1':
                $data['judul'] = "Laporan Pembelian Keseluruhan";
                $laporan = DB::table('view_laporan_pembelian')->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->orderByRaw("tanggal_faktur, id_pembelian");
                if ($output == "print") {
                    $page = 'Laporanbelidetail';
                } else {
                    $page = 'Laporanbelidetailexcel';
                }
                
                break;
            default:
                # code...
                break;
        }

        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 Pembelian Keseluruhan ".date_db($tgl_awal, true).'-'.date_db($tgl_akhir, true):"(Detail) Laporan 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");
        }
    }

    /* Laporan Jatuh Tempo */
    public function cetak_lap_jatuh_tempo(Request $request)
    {
        # code...
        require('fpdf181/Mc_table.php');
        $input = $request->all();
        // dd($input);
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua

        $data['judul'] = "Laporan Jatuh Tempo Hutang";
        $periode = explode("-", $input['tanggal_tempo']);
        $data['periode'] = bln_tostr($periode[0])." ".$periode[1];
        $laporan = DB::table("view_pembelian_bayar")->orderByRaw("tanggal_tempo")->whereRaw("MONTH(tanggal_tempo) = '$periode[0]' AND YEAR(tanggal_tempo) = '$periode[1]'");

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

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

        $data['data'] = $laporan->get();
        $page = "LaporanJatuhTempo";
        $html = view('pembelian.'.$page)->with('data',$data);
        return response($html)->header('Content-Type', 'application/pdf');
    }
    /* end : Laporan Jatuh Tempo */

    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 Pembelian Per-Kategori";

        $laporan=DB::table('view_laporan_pembelian')->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 = "CetakLaporanPerKategori";
            $html = view('pembelian.'.$page)->with('data',$data);
            return response($html)->header('Content-Type', 'application/pdf');
        } else if ($output == "excel") {
            $page = "CetakLaporanPerKategoriExcel";
            Excel::create("Laporan 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 = $request->get('barang');

        // DB::enableQueryLog();
        $data['judul'] = "Laporan Pembelian per-Barang";
        $laporan = DB::table('view_laporan_pembelian')->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->where('id_barang', $barang)->select(DB::raw("*"));

        // dd(DB::getQueryLog());
        // dd($data['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.Laporanbelibarang')->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']);
        $tgl_akhir = date_db($input['tanggal_akhir_penyedia']);
        $id_penyedia = $input['penyedia'];

        // DB::enableQueryLog();
        $penyedia = DB::table("ms_penyedia")->where(["id_penyedia"=>$id_penyedia])->first();
        $data['judul'] = "Laporan Pembelian Per-Penyedia ( $penyedia->nama )";
        $laporan = DB::table('view_laporan_pembelian')->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->where('id_penyedia', $id_penyedia)->select(DB::raw("*"));

        // dd(DB::getQueryLog());

        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.Laporanbelipenyedia')->with('data',$data);
        return response($html)->header('Content-Type', 'application/pdf');
    }
}