<?php

namespace App\Http\Controllers\keuangan;

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 App\Pembelian;
use Illuminate\Support\Facades\Session;

class HutangController 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(){
        return view('keuangan.HutangList');

    }

	function hutang_tambah()
	{
		$data['supplier'] = DB::table('ms_penyedia')->orderBy('nama', 'asc')->get();
        $data['transaksi'] = DB::table('ms_transaksi')->where('id_transaksi','9')->orderBy('nama_transaksi', 'asc')->get();
        $data['cara'] = DB::table('ms_carabayar as mc')->join('ms_perkiraan as mp','mc.kode_perkiraan','mp.id')->where('tipe', 'K')->orderByRaw('jenis_bayar asc, id_carabayar asc')->get();
        $data['data'] = $this->data(array());
		
        
        return view('keuangan.hutangAwalTambah')->with('data', $data);
	}

    function data($data = array()){
        if(count($data) > 0){
            $store['id_hutang'] = $data->id_hutang;
            $store['tanggal'] = date('d-m-Y', strtotime($data->tanggal));
            $store['tanggal_faktur'] = date('d-m-Y', strtotime($data->tanggal_faktur));
            $store['no_faktur'] = $data->no_faktur;
            $store['id_transaksi'] = $data->id_transaksi;
            $store['id_carabayar'] = $data->id_carabayar;
            $store['id_penyedia'] = $data->id_penyedia;
            $store['keterangan'] = $data->keterangan;
            $store['pajak'] = $data->pajak;
            $store['nominal_potongan'] = $data->nominal_potongan;
            $store['nominal_pajak'] = $data->nominal_pajak;
            $store['nominal_netto'] = $data->nominal_netto;
            $store['nominal_total'] = $data->nominal_total;
            $store['nominal_bayar'] = $data->nominal_bayar;

        }else{
            $store['id_hutang'] = '';
            $store['tanggal'] = '';
            $store['tanggal_faktur'] = '';
            $store['no_faktur'] = '';
            $store['id_transaksi'] = '';
            $store['id_carabayar'] = '';
            $store['id_penyedia'] = '';
            $store['keterangan'] = '';
            $store['pajak'] = '0';
            $store['nominal_potongan'] = '0';
            $store['nominal_pajak'] = '0';
            $store['nominal_netto'] = '0';
            $store['nominal_total'] = '0';
            $store['nominal_bayar'] = 0;
        }

        return $store;
    }
    function simpan(Request $request){
        $id_hutang = $request->get('id_hutang');
        $data['tanggal'] = date('Y-m-d', strtotime($request->get('tanggal')));
        $data['tanggal_faktur'] = date('Y-m-d', strtotime($request->get('tanggal_faktur')));

        $data['no_faktur'] = strtoupper($request->get('no_faktur'));
        $data['id_transaksi'] = $request->get('transaksi');
        
        $data['keterangan'] = $request->get('keterangan');
        $data['nominal_total'] = $request->get('td_total');
        $data['nominal_potongan'] = $request->get('td_potongan');
        $data['pajak'] = $request->get('pajak');
        $data['nominal_pajak'] = $request->get('td_pajak');
        
        $data['nominal_netto'] = $request->get('td_netto');
        $data['id_carabayar'] = $request->get('cara_bayar');
        $data['nominal_bayar'] = $request->get('td_nominal_bayar');

        if($id_hutang == ''){
            $data['id_penyedia'] = $request->get('penyedia');
            $data['status'] = '0';
            $id = DB::table('keu_hutang')->insertGetId($data);
            
        }else{
            DB::table('keu_hutang')->where(array('id_hutang' => $id_hutang))->update($data);
        }

        return redirect('hutang');
    }

    public function get_data()
    {
        // $pembelian = Pembelian::with(["dataPenyedia", "dataPembelianBarang", "dataPembelianBayar"])->get();
        $session = Session::all();
        $pembelian = DB::table("view_pembelian2")->selectRaw("view_pembelian2.*")->orderByRaw("CAST(no_faktur AS UNSIGNED) ASC, tanggal_faktur ASC");
        
        $arr_kode_group = [5, 8, 9, 12];
        if(!in_array($session['kode_group'], $arr_kode_group)){
            $pembelian->where('lokasi', $session['id_lokasi']);
        }

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

        $tagihan = 0;
        $pembayaran = 0;

        return Datatables::of($pembelian)
            ->editColumn("no_faktur", function($pembelian)
            {
                return "<a href='".url('pembelian/daftar_bayar_tagihan/'.$pembelian->id_pembelian."?as=".$pembelian->sebagai)."'>".nomor_pembelian($pembelian)."</a>";
            })
            ->editColumn("status", function($pembelian) use ($tagihan, $pembayaran)
            {
                $tagihan = $pembelian->nominal_netto;
                $pembayaran = $pembelian->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;
                }
                return status_bayar($bayar);
            })
            ->addColumn("tagihan", function($pembelian) use ($tagihan)
            {
                // $pajak = $pembelian->pajak;
                // $sub_total = $pembelian->dataPembelianBarang->sum('total');
                // $nominal_pajak = 0;
                // if($pajak==1){
                //     $nominal_pajak = $sub_total/10;
                // }
                $sub_total = $pembelian->nominal_total;
                $nominal_pajak = $pembelian->nominal_pajak;
                $nominal_ongkir = $pembelian->nominal_ongkir;

                $tagihan = $sub_total+$nominal_pajak-$pembelian->nominal_potongan+$nominal_ongkir;
                return $tagihan;
            })
            ->addColumn("pembayaran", function($pembelian) use ($pembayaran)
            {
                // $pembayaran = $pembelian->dataPembelianBayar->sum("jum_bayar");
                $pembayaran = $pembelian->total_bayar;
                return $pembayaran;
            })
            ->addColumn("nama_penyedia", function($pembelian){
                return $pembelian->nama_penyedia;
            })
            ->addColumn("aksi", function($pembelian)
            {
                $button = "<div class='btn-group'>";

                    // $button .='<a class="btn  btn-xs bg-success-400" type="button" href="'.url('pembelian/bayar/'.$pembelian->id_pembelian).'"><i class="icon-cash2"></i></a>';
                    $button .='<a class="btn btn-xs bg-success-400" type="button" href="'.url('pembelian/bayar_tagihan?id='.$pembelian->id_pembelian.'&as='.$pembelian->sebagai).'"><i class="icon-cash2"></i></a>';

                $button .= "</div>";
                return $button;
            })
            ->make(true);
    }

    public function get_data_service(Request $request)
    {
        $session = Session::all();
        $orderService = DB::table('view_orderservice_bayar')->selectRaw("view_orderservice_bayar.*, DATE_FORMAT(tanggal_nota, '%d-%m-%Y') tanggal_nota_formated")->orderByRaw("tanggal_nota DESC, no_nota ASC");
        
        $arr_kode_group = [5, 8, 9, 12];
        if(!in_array($session['kode_group'], $arr_kode_group)){
            $orderService->where('lokasi', $session['id_lokasi']);
        }

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

        $tagihan = 0;
        $pembayaran = 0;

        return Datatables::of($orderService)
                ->addColumn("no_nota", function($orderService){
                    return "<a href='".url('order_service/daftar_bayar_tagihan/'.$orderService->id)."'>".$orderService->no_nota."".$orderService->sufix_no_nota."</a>";
                })
                ->addColumn("status", function($orderService) use ($tagihan, $pembayaran)
                {
                    $tagihan = $orderService->nominal_netto;
                    $pembayaran = $orderService->nominal_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;
                    }
                    
                    return status_bayar($bayar);
                })
                ->addColumn("tagihan", function($orderService){
                    return $orderService->nominal_netto;
                })
                ->addColumn("pembayaran", function($orderService){
                    return $orderService->nominal_bayar;
                })
                ->addColumn("aksi", function($orderService){
                    $button = "<div class='btn-group'>";
                        $button .='<a class="btn  btn-xs bg-success-400" type="button" href="'.url('order_service/bayar_tagihan_service?id='.$orderService->id).'"><i class="icon-cash2"></i></a>';
                    $button .= "</div>";
                    return $button;
                })
                ->make(true);
    }

    public function get_data_sewa(Request $request)
    {
        $session = Session::all();
        $orderSewa = DB::table('view_sewa_bayar')->selectRaw("view_sewa_bayar.*, DATE_FORMAT(tanggal_faktur, '%d-%m-%Y') tanggal_faktur_formated")->orderByRaw("tanggal_faktur DESC, no_faktur ASC");
        
        $arr_kode_group = [5, 8, 9, 12];
        if(!in_array($session['kode_group'], $arr_kode_group)){
            $orderSewa->where('id_lokasi', $session['id_lokasi']);
        }

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

        $tagihan = 0;
        $pembayaran = 0;

        return Datatables::of($orderSewa)
                ->addColumn("no_faktur", function($orderSewa){
                    return "<a href='".url('sewa/daftar_bayar_tagihan/'.$orderSewa->id_sewa)."'>".$orderSewa->no_faktur."</a>";
                })
                ->addColumn("status", function($orderSewa) use ($tagihan, $pembayaran)
                {
                    $tagihan = $orderSewa->nominal_total;
                    $pembayaran = $orderSewa->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;
                    }
                    
                    return status_bayar($bayar);
                })
                ->addColumn("tagihan", function($orderSewa){
                    return $orderSewa->nominal_total;
                })
                ->addColumn("pembayaran", function($orderSewa){
                    return $orderSewa->total_bayar;
                })
                ->addColumn("aksi", function($orderSewa){
                    $button = "<div class='btn-group'>";
                        $button .='<a class="btn  btn-xs bg-success-400" type="button" href="'.url('sewa/bayar_tagihan_sewa?id='.$orderSewa->id_sewa).'"><i class="icon-cash2"></i></a>';
                    $button .= "</div>";
                    return $button;
                })
                ->make(true);
    }

    function get_data2(){

        DB::statement(DB::raw('set @rownum=0'));
        $d_data = DB::table('view_pembelian_bayar')->join('ms_penyedia','view_pembelian_bayar.id_penyedia','ms_penyedia.id_penyedia')->select('view_pembelian_bayar.*','ms_penyedia.nama', DB::raw('@rownum  := @rownum  + 1 AS rownum'));
        if($d_data->count() > 0){
            foreach($d_data->get() as $d){
                $aksi = '';
                $bayar = 0;
                $total_beli = 0;
                $total_beli=$d->nominal_netto;
                $total_bayar=$d->total_bayar;

               if($d->status == '0'){
                 $aksi = '<a class="btn  btn-xs bg-warning-400" type="button" href="'.url('pembelian/edit/'.$d->id_pembelian).'"><i class="icon-pencil"></i> </a> <button class="btn btn-xs bg-danger-800" type="button" onclick="hapus('.$d->id_pembelian.')"><i class="icon-eraser"></i></button> <a class="btn  btn-xs bg-success-400" type="button" href="'.url('pembelian/bayar/'.$d->id_pembelian).'"><i class="icon-cash2"></i></a>';
               }else{
                 $aksi = '<a class="btn  btn-xs bg-warning-400" type="button" href="'.url('pembelian/edit/'.$d->id_pembelian).'"><i class="icon-pencil"></i> </a> <a class="btn btn-xs bg-success-400" type="button" href="'.url('pembelian/bayar/'.$d->id_pembelian).'"><i class="icon-cash2"></i></a>';
               }

               if($total_bayar == 0){
                $bayar = 0;
               }else if($total_bayar < $total_beli){
                $bayar = 1;
               }else{
                $bayar = 2;
               }

                $data['data'][] = array('id_pembelian' => $d->id_pembelian,
                                        'no_faktur' => '<b>'.$d->no_faktur.'</b>',
                                        'tanggal' => date('d-m-Y', strtotime($d->tanggal)),
                                        'tanggal_faktur' => date('d-m-Y', strtotime($d->tanggal_faktur)),
                                        'id_penyedia' => $d->id_penyedia,
                                        'id_transaksi' => $d->id_transaksi,
                                        'id_carabayar' => $d->id_carabayar,
                                        'status_id' => $d->status,
                                        'status' => status_bayar($bayar),
                                        'netto' => number_format($d->nominal_netto,2,",","."),
                                        'total_bayar' => number_format($total_bayar,2,",","."),
                                        'nama' => $d->nama,
                                        'aksi' => "&nbsp;",
                                        'rownum' => $d->rownum);
            }
        }else{
            $data['data'] = array();
        }

        $data = new Collection($data['data']);
        return Datatables::of($data)
                ->make(true);
    }
    function edit($id){
         $d_data = DB::table('keu_hutang')->where('id_hutang', '=', $id)->first();
        $data['supplier'] = DB::table('ms_penyedia')->orderBy('nama', 'asc')->get();
        $data['transaksi'] = DB::table('ms_transaksi')->where('id_transaksi','9')->orderBy('nama_transaksi', 'asc')->get();
        $data['cara'] = DB::table('ms_carabayar as mc')->join('ms_perkiraan as mp','mc.kode_perkiraan','mp.id')->where('tipe', 'K')->orderByRaw('jenis_bayar asc, id_carabayar asc')->get();
        $data['data'] = $this->data($d_data);
        return view('keuangan.hutangAwalTambah')->with('data', $data);
    }

    //bayar
    function bayar($id){
        $data['id'] = $id;
        $data['cara_bayar'] = DB::table('ms_carabayar as mc')->join('ms_perkiraan as mp','mc.kode_perkiraan','mp.id')->where('tipe', 'K')->orderByRaw('jenis_bayar asc, id_carabayar asc')->get();
        $data['sumber_dana'] = DB::table('ms_perkiraan')->where([ ["kode_perkiraan", "like", "1-101%"], [DB::raw("SUBSTR(kode_perkiraan,6,1)"), "<>", "0"] ])->get();
        return view('keuangan.HutangBayar')->with('data', $data);   
    }

    function bayar_lihat($id){
        $data['id'] = $id;
        return view('keuangan.HutangBayarLihat')->with('data', $data);   
    }

    function get_detailbayar($id){
        $d_data = DB::table('view_hutang_bayar')->where('id_hutang', $id);
        $data['detail'] = array();
        if($d_data->count() > 0){
            $data['detail'] = $d_data->first();
            $data['detail']->status = '1';
        }else{
            $data['detail'] = array('status' => '0');
        }

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

    function get_bayar($id){
        $d_awal_bayar = DB::table('view_hutang_bayar')->where('id_hutang', $id)->whereIn('jenis_bayar', [1,3]);
        $d_bayar = DB::table('keu_hutang_bayar as ppb')
                ->leftjoin('ms_perkiraan AS mp', 'ppb.id_akun', 'mp.id')
                ->leftjoin('ms_carabayar AS mc', 'ppb.id_carabayar', 'mc.id_carabayar')
                ->select(DB::raw('ppb.*,mp.nama_perkiraan,mp.kode_perkiraan,mc.cara,mc.tipe'))->where('ppb.id_hutang', $id);
        $data['bayar'] = array();
        if($d_awal_bayar->count() > 0){
            $bayar_awal  = $d_awal_bayar->first();
            
            $data['bayar'][] = array(
                                'id_hutangbayar' => 'nominal_bayar',
                                'id_hutang' => $bayar_awal->id_hutang,
                                'id_akun' => '',
                                'id_carabayar' => $bayar_awal->id_carabayar,
                                'kode_perkiraan' =>  $bayar_awal->kode_perkiraan.' - '.$bayar_awal->nama_perkiraan,
                                'tgl_bayar' => tgl_full($bayar_awal->tanggal, ''),
                                'jenis' => $bayar_awal->cara_bayar.' - '.$bayar_awal->tipe_bayar,
                                'jum_bayar' => (float) $bayar_awal->nominal_bayar,
                                'aksi' => ''
            );
        }
        foreach($d_bayar->get() as $d){
            $data['bayar'][] = array('id_hutangbayar' => $d->id_hutangbayar,
                                    'id_hutang' => $d->id_hutang,
                                    'id_akun' => $d->id_akun,
                                    'id_carabayar' => $d->id_carabayar,
                                    'kode_perkiraan' => $d->kode_perkiraan.' - '.$d->nama_perkiraan,
                                    'tgl_bayar' => tgl_full($d->tgl_bayar, ''),
                                    'jenis' => $d->cara.' - '.$d->tipe,
                                    'jum_bayar' => $d->jum_bayar,
                                    'aksi' => '');
        }

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

    function simpan_bayar(Request $request){
        $id_hutangbayar = $request->get('id_hutangbayar');
        $data['id_hutang'] = $request->get('id_hutang');
        $data['tgl_bayar'] = tgl_full($request->get('tanggal'), '2');
        $data['jum_bayar'] = $request->get('jum_bayar');
        $data['id_akun'] = $request->get('id_akun');
        $data['id_carabayar'] = $request->get('id_carabayar');

        if($id_hutangbayar == ''){
            DB::table('keu_hutang_bayar')->insert($data);
        }else{
            DB::table('keu_hutang_bayar')->where('id_hutangbayar', $id_hutangbayar)->update($data);
        }

        return response()->json(array('status' => '1'));
    }

    public function hapus_bayar(Request $request){
        $id = $request->get('id');

        DB::table('keu_hutang_bayar')->where(array('id_hutangbayar' => $id))->delete();

        return response()->json(array('status' => '1'));
    }
     public function hapus(Request $request){
        $id = $request->get('id');
        DB::table('keu_hutang')->where(array('id_hutang' => $id))->delete();
        DB::table('keu_hutang_bayar')->where(array('id_hutang' => $id))->delete();
    }

}
