<?php

namespace App\Http\Controllers\keuangan;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

use DB;
use App\LokasiProduksi;
use App\Penyedia;
use Illuminate\Support\Facades\Session;

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

    public function index()
    {
    	$data['pegawai'] = DB::table('ms_pegawai')->join('ms_departement', 'ms_departement.id_departement', 'ms_pegawai.id_departement')->select('ms_pegawai.id_pegawai', 'ms_pegawai.nomor_pegawai', 'ms_pegawai.nama_pegawai', 'ms_pegawai.id_akun_piutang', 'ms_departement.nama as nama_departement')->get();
    	return view('keuangan.kasbon.index', $data);
    }

    public function cetakKasbon(Request $request)
    {
    	$id_pegawai = explode('_', $request->pegawai)[0];
    	$id_akun_piutang = explode('_', $request->pegawai)[1];
    	$tgl_awal = date_db($request->tanggal_awal);
    	$tgl_akhir = date_db($request->tanggal_akhir);

    	$pegawai 		    = DB::table('ms_pegawai')
    							->join('ms_departement', 'ms_departement.id_departement', 'ms_pegawai.id_departement')
    							->select('ms_pegawai.id_pegawai', 'ms_pegawai.nomor_pegawai', 'ms_pegawai.nama_pegawai', 'ms_pegawai.id_akun_piutang', 'ms_departement.nama as nama_departement')
    							->where('ms_pegawai.id_pegawai', $id_pegawai)
        						->first();
        $data['pegawai']  = $pegawai;
        $data['periode'] = $request->tanggal_awal.' s/d '.$request->tanggal_akhir;
        $data['judul'] 	    = "Laporan Kas Bon Pegawai";

    	$kasbon_awal = DB::table('keu_sumber_dana_detail as ksdd')
    					->join('keu_sumber_dana as ksd', 'ksdd.id_keu_sumber_dana', 'ksd.id')
    					->leftJoin('ms_pegawai as mp', 'mp.id_akun_piutang', 'ksdd.ke_akun')
    					->where('mp.id_pegawai', $id_pegawai)
                        ->where('ksdd.ke_akun', '!=', 22)
    					->where('ksd.tanggal_transaksi', '<', $tgl_awal)
    					->sum('ksdd.nominal_transaksi');

    	// $kasbon_awal_bayar = DB::table('pro_upah_harian_detail as uhd')
    	// 					->join('pro_upah_harian_akun as uha', 'uha.id_upah_harian', 'uhd.id_upah_harian')
    	// 					->join('pro_upah_harian as uh', 'uh.id_upah_harian', 'uhd.id_upah_harian')
    	// 					->where('uhd.potongan_kas_bon', '!=', 0)
    	// 					->where('uhd.id_pegawai', $id_pegawai)
    	// 					->where('uh.tanggal_transaksi', '<', $tgl_awal)
    	// 					->sum('uhd.potongan_kas_bon');

        $kasbon_awal_bayar = DB::table('view_pegawai_bayar_kasbon')
                            ->where('id_pegawai', $id_pegawai)
                            ->where('tanggal_transaksi', '<', $tgl_awal)
                            ->sum('bayar_kasbon');

    	$data['saldo_awal'] = (float)$kasbon_awal-(float)$kasbon_awal_bayar;

    	$kasbon = DB::table('keu_sumber_dana_detail as ksdd')
    					->join('keu_sumber_dana as ksd', 'ksdd.id_keu_sumber_dana', 'ksd.id')
    					->leftJoin('ms_pegawai as mp', 'mp.id_akun_piutang', 'ksdd.ke_akun')
    					->where('mp.id_pegawai', $id_pegawai)
                        ->where('ksdd.ke_akun', '!=', 22)
    					->whereBetween('ksd.tanggal_transaksi', [$tgl_awal, $tgl_akhir])
    					->select('ksd.tanggal_transaksi as tanggal_transaksi', 'ksdd.keterangan as keterangan', 'ksdd.nominal_transaksi as nominal', 'ksdd.tipe_id_akun as tipe_akun')
    					->orderBy('ksd.tanggal_transaksi', 'asc');

    	// $kasbon_bayar = DB::table('pro_upah_harian_detail as uhd')
    	// 					->join('pro_upah_harian_akun as uha', 'uha.id_upah_harian', 'uhd.id_upah_harian')
    	// 					->join('pro_upah_harian as uh', 'uh.id_upah_harian', 'uhd.id_upah_harian')
    	// 					->where('uhd.potongan_kas_bon', '!=', 0)
    	// 					->where('uhd.id_pegawai', $id_pegawai)
    	// 					->whereBetween('uh.tanggal_transaksi', [$tgl_awal, $tgl_akhir])
    	// 					->selectRaw("uh.tanggal_transaksi as tanggal_transaksi, 'Bayar Kas Bon' as `keterangan`, uhd.potongan_kas_bon as nominal, uha.tipe_akun_potongan_kas_bon as tipe_akun")
    	// 					->orderBy('uh.tanggal_transaksi', 'asc');

        $kasbon_bayar = DB::table('view_pegawai_bayar_kasbon')
                        ->where('id_pegawai', $id_pegawai)
                        ->whereBetween('tanggal_transaksi', [$tgl_awal, $tgl_akhir])
                        ->selectRaw('tanggal_transaksi, keterangan, bayar_kasbon AS nominal, tipe_dari_akun AS tipe_akun')
                        ->orderBy('tanggal_transaksi', 'asc')

    	require('fpdf181/Mc_table.php');

    	$data['data'] = $kasbon->union($kasbon_bayar)->get();

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

    public function cetakKasbonRekap(Request $request)
    {
        $tgl_awal = date_db($request->tanggal_awal);
        $tgl_akhir = date_db($request->tanggal_akhir);

        $data['periode'] = $request->tanggal_awal.' s/d '.$request->tanggal_akhir;
        $data['judul']      = "Laporan Kas Bon Pegawai (Rekap)";

        $kasbon_awal = DB::table('keu_sumber_dana_detail as ksdd')
                        ->join('keu_sumber_dana as ksd', 'ksdd.id_keu_sumber_dana', 'ksd.id')
                        ->rightJoin('ms_pegawai as mp', 'mp.id_akun_piutang', 'ksdd.ke_akun')
                        ->join('ms_departement as md', 'md.id_departement', 'mp.id_departement')
                        // ->where('mp.id_akun_piutang', '!=', 22)
                        ->whereNotIn('mp.id_akun_piutang', [22, 24])
                        ->where('ksd.tanggal_transaksi', '<', $tgl_awal)
                        ->selectRaw('
                            mp.id_pegawai, 
                            mp.nomor_pegawai, 
                            mp.nama_pegawai, 
                            md.nama as nama_departement, 
                            SUM(ksdd.nominal_transaksi) as kasbon,
                            0 as bayar')
                        ->groupBy('ksdd.ke_akun');

        // $kasbon_awal_bayar = DB::table('pro_upah_harian_detail as uhd')
        //                     ->join('pro_upah_harian_akun as uha', 'uha.id_upah_harian', 'uhd.id_upah_harian')
        //                     ->join('pro_upah_harian as uh', 'uh.id_upah_harian', 'uhd.id_upah_harian')
        //                     ->rightJoin('ms_pegawai as mp', 'mp.id_pegawai', 'uhd.id_pegawai')
        //                     ->join('ms_departement as md', 'md.id_departement', 'mp.id_departement')
        //                     ->where('uhd.potongan_kas_bon', '!=', 0)
        //                     ->where('uh.tanggal_transaksi', '<', $tgl_awal)
        //                     ->selectRaw('
        //                         mp.id_pegawai, 
        //                         mp.nomor_pegawai, 
        //                         mp.nama_pegawai, 
        //                         md.nama as nama_departement,
        //                         0 as kasbon, 
        //                         SUM(uhd.potongan_kas_bon) as bayar')
        //                     ->groupBy('uhd.id_pegawai');

        $kasbon_awal_bayar = DB::table('view_pegawai_bayar_kasbon AS pbk')
                            ->rightJoin('ms_pegawai as mp', 'mp.id_pegawai', 'pbk.id_pegawai')
                            ->join('ms_departement as md', 'md.id_departement', 'mp.id_departement')
                            ->where('pbk.tanggal_transaksi', '<', $tgl_awal)
                            ->selectRaw('
                                mp.id_pegawai, 
                                mp.nomor_pegawai, 
                                mp.nama_pegawai, 
                                md.nama as nama_departement,
                                0 as kasbon, 
                                SUM(pbk.bayar_kasbon) as bayar')
                            ->groupBy('pbk.id_pegawai');

        $kasbon_saldoawal = $kasbon_awal->union($kasbon_awal_bayar)->get();

        $rekap_kasbon = [];
        if (count($kasbon_saldoawal) > 0) {
            foreach ($kasbon_saldoawal as $key => $value) {
                if (!isset($rekap_kasbon[$value->id_pegawai])) {
                    $rekap_kasbon[$value->id_pegawai] = array(
                        'id_pegawai' => $value->id_pegawai,
                        'nomor_pegawai' => $value->nomor_pegawai,
                        'nama_pegawai' => $value->nama_pegawai,
                        'departement' => $value->nama_departement,
                        'saldo_awal' => 0,
                        'kasbon' => 0,
                        'bayar' => 0
                    );
                }

                $saldoawal = $value->kasbon-$value->bayar;

                $rekap_kasbon[$value->id_pegawai]['saldo_awal'] += $saldoawal;
            }
        }

        // $kasbon_bayar = DB::table('pro_upah_harian_detail as uhd')
        //                     ->join('pro_upah_harian_akun as uha', 'uha.id_upah_harian', 'uhd.id_upah_harian')
        //                     ->join('pro_upah_harian as uh', 'uh.id_upah_harian', 'uhd.id_upah_harian')
        //                     ->rightJoin('ms_pegawai as mp', 'mp.id_pegawai', 'uhd.id_pegawai')
        //                     ->join('ms_departement as md', 'md.id_departement', 'mp.id_departement')
        //                     ->where('uhd.potongan_kas_bon', '!=', 0)
        //                     ->whereBetween('uh.tanggal_transaksi', [$tgl_awal, $tgl_akhir])
        //                     ->selectRaw('
        //                         mp.id_pegawai, 
        //                         mp.nomor_pegawai, 
        //                         mp.nama_pegawai, 
        //                         md.nama as nama_departement,
        //                         0 as kasbon, 
        //                         SUM(uhd.potongan_kas_bon) as bayar')
        //                     ->groupBy('uhd.id_pegawai');

        $kasbon_bayar = DB::table('view_pegawai_bayar_kasbon AS pbk')
                            ->rightJoin('ms_pegawai as mp', 'mp.id_pegawai', 'pbk.id_pegawai')
                            ->join('ms_departement as md', 'md.id_departement', 'mp.id_departement')
                            ->whereBetween('pbk.tanggal_transaksi', [$tgl_awal, $tgl_akhir])
                            ->selectRaw('
                                mp.id_pegawai, 
                                mp.nomor_pegawai, 
                                mp.nama_pegawai, 
                                md.nama as nama_departement,
                                0 as kasbon, 
                                SUM(pbk.bayar_kasbon) as bayar')
                            ->groupBy('pbk.id_pegawai');

        $kasbon = DB::table('keu_sumber_dana_detail as ksdd')
                        ->join('keu_sumber_dana as ksd', 'ksdd.id_keu_sumber_dana', 'ksd.id')
                        ->rightJoin('ms_pegawai as mp', 'mp.id_akun_piutang', 'ksdd.ke_akun')
                        ->join('ms_departement as md', 'md.id_departement', 'mp.id_departement')
                        // ->where('mp.id_akun_piutang', '!=', 22)
                        ->whereNotIn('mp.id_akun_piutang', [22, 24])
                        ->whereBetween('ksd.tanggal_transaksi', [$tgl_awal, $tgl_akhir])
                        ->selectRaw('
                            mp.id_pegawai, 
                            mp.nomor_pegawai, 
                            mp.nama_pegawai, 
                            md.nama as nama_departement, 
                            SUM(ksdd.nominal_transaksi) as kasbon,
                            0 as bayar')
                        ->groupBy('ksdd.ke_akun')
                        ->union($kasbon_bayar)
                        ->get();

        if (count($kasbon) > 0) {
            foreach ($kasbon as $key => $val) {
                if (!isset($rekap_kasbon[$val->id_pegawai])) {
                    $rekap_kasbon[$val->id_pegawai] = array(
                        'id_pegawai' => $val->id_pegawai,
                        'nomor_pegawai' => $val->nomor_pegawai,
                        'nama_pegawai' => $val->nama_pegawai,
                        'departement' => $val->nama_departement,
                        'saldo_awal' => 0,
                        'kasbon' => 0,
                        'bayar' => 0
                    );
                }

                $rekap_kasbon[$val->id_pegawai]['kasbon'] += $val->kasbon;
                $rekap_kasbon[$val->id_pegawai]['bayar'] += $val->bayar;
            }
        }

        $data['data'] = $rekap_kasbon;

        require('fpdf181/Mc_table.php');
        $html = view('keuangan.kasbon.laporanKasBonRekap')->with('data',$data);
        return response($html)->header('Content-Type', 'application/pdf');
    }
}
