<?php

namespace App\Http\Controllers\pembelian;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use Yajra\Datatables\Datatables;
use Illuminate\Support\Collection;
use App\Pembelian;
use App\Perkiraan;
use App\PembelianBayar;
use App\PembelianBayarTransaksi;
use App\Orderbeli;
use App\LokasiProduksi;
use App\Penyedia;
use App\Tempatpenyerahan;
use App\HutangAwalBayar;
use App\HutangAwal;
use Illuminate\Support\Facades\Session;

class PembelianController extends Controller
{

    private $id_akun;
    
    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); 
        });

        $this->id_akun = [
            "hutang_usaha"=>101,
            "potongan_pembelian"=>102,
            "ppn_masukan"=>103,
        ];
    }

    public function index(){
        // dd(Session::all());
    	return view('pembelian.PembelianList');
    }

    public function get_data()
    {
        // $pembelian = Pembelian::with(["dataPenyedia", "dataPembelianBarang", "dataPembelianBayar"])->get();
        $session = Session::all();
        $pembelian = DB::table("view_pembelian_bayar AS p")
            ->selectRaw("
                    p.*,
                    CONCAT(p.no_faktur, p.sufix_no_faktur) AS nomor_faktur,
                    DATE_FORMAT(p.tanggal_faktur, '%d-%m-%Y') AS tanggal_faktur_s
                ")
            ->orderByRaw('tanggal DESC, tanggal_faktur DESC, no_faktur DESC, id_pembelian ASC');
        
        $arr_kode_group = [5, 8, 9, 3, 12, 20];
        if(!in_array($session['kode_group'], $arr_kode_group)){
            $pembelian->where('lokasi', $session['id_lokasi']);
        }

        $pembelian = $pembelian->get();

        $tagihan = 0;
        $pembayaran = 0;
        $status_bayar = 0;

        return Datatables::of($pembelian)
            ->editColumn("tanggal_faktur", function($pembelian)
            {
                return tgl_full($pembelian->tanggal_faktur, 98);
            })
            ->editColumn("no_faktur", function($pembelian)
            {
                return $pembelian->no_faktur."".$pembelian->sufix_no_faktur;
            })
            ->editColumn("status", function($pembelian) use ($tagihan, $pembayaran, $status_bayar)
            {
                $bayar = $this->set_status_bayar($pembelian);
                return $this->status_beli($pembelian->status)."".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) use ($status_bayar)
            {
                $status_bayar = $this->set_status_bayar($pembelian);

                $button = "<div class='btn-group'>";
    
                    if($pembelian->status != 0 || $status_bayar != 0){
                        $button .= '<a class="btn  btn-xs bg-info-400" type="button" href="'.url('pembelian/detail/'.$pembelian->id_pembelian).'"><i class="icon-eye"></i></a>';
                        if(in_array($status_bayar, [0, 1, 3])){
                            $button .= '<a class="btn  btn-xs bg-warning-400" type="button" href="'.url('pembelian/edit_harga/'.$pembelian->id_pembelian).'"><i class="icon-pencil"></i></a>';
                        }
                    }else{
                        $button .= '<a class="btn  btn-xs bg-info-400" type="button" href="'.url('pembelian/detail/'.$pembelian->id_pembelian).'"><i class="icon-eye"></i></a>';
                        
                        $button .= '<a class="btn  btn-xs bg-warning-400" type="button" href="'.url('pembelian/edit/'.$pembelian->id_pembelian).'"><i class="icon-pencil"></i></a>';

                        $button .= '<button class="btn btn-xs bg-danger-800" type="button" onclick="hapus('.$pembelian->id_pembelian.')"><i class="icon-eraser"></i></button>';
                    }
                    
                    /*$button .= '<a class="btn bg-success btn-xs" type="button" href="'.url('pembelian/cetak/'.$pembelian->id_pembelian).'" target="_blank"><i class="icon-printer"></i></a> ';*/
                    $button .= '<a href="#" class="btn bg-success btn-xs" type="button" data-href="'.url('pembelian/cetak/'.$pembelian->id_pembelian).'" onclick="btnCetak($(this))"><i class="icon-printer"></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' => $this->status_beli($d->status).' '.status_bayar($bayar),
                                        'netto' => number_format($d->nominal_netto,2,",","."),
                                        'total_bayar' => number_format($total_bayar,2,",","."),
                                        'nama' => $d->nama,
                                        'aksi' => $aksi,
                                        'rownum' => $d->rownum);
            }
        }else{
            $data['data'] = array();
        }

        $data = new Collection($data['data']);
        return Datatables::of($data)
                ->make(true);
    }

    public function formtambah(){
    	$data['supplier'] = DB::table('ms_penyedia')->orderBy('nama', 'asc')->get();
        $perkiraan = DB::table('ms_perkiraan')->whereIn('parent',['3', '7'])->orderBy('kode_perkiraan', 'asc')->get();
        $arr_perkiraan = array_reduce($perkiraan->toArray(), function($carry, $item){
            $carry[ $item->id ] = $item->kode_perkiraan." ".$item->nama_perkiraan;
            return $carry;
        });
        $data['perkiraan'] = [null=>"--- Pilih Akun ---"] + $arr_perkiraan;

        $tempat_penyerahan = Tempatpenyerahan::with(['dataLokasi'])->orderByRaw("id_lokasi, nama")->get();
        $data['tempat_penyerahan'] = array_reduce($tempat_penyerahan->toArray(), function($carry, $item){
            $carry[ $item['id'] ] = $item['nama']." - ".$item['data_lokasi']['nama_lokasi'];
            return $carry;
        });

    	$data['cara'] = DB::table('ms_carabayar as mc')->leftJoin('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());
        $data['lokasi'] = LokasiProduksi::get();
        $data['satuan'] = DB::table('ms_satuan')->orderBy('nama_satuan','asc')->get();
    	return view('pembelian.PembelianTambah')->with('data', $data);
    }

    function get_barang(Request $request){
        $input = $request->all();
        // $akun = DB::table("ms_transaksi")->where([["id_transaksi", "=", $input['id_transaksi']]])->first();
        $d_data = DB::table('view_ms_barang_full 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')
            // ->where([['mb.id_akun', '=', $akun->id]])
            ->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);
    }

    public function get_nomor(Request $request)
    {
        $input = $request->all();
        $tanggal = explode("/", $input['tanggal']);
        if($input['id_pembelian']!=""){
            $pembelian = Pembelian::find($input['id_pembelian']);
            $result = [
                    "no_faktur"=>$pembelian->no_faktur,
                    "sufix_no_faktur"=>$pembelian->sufix_no_faktur,
                ];
        }else{
            $pembelian = Pembelian::whereRaw("YEAR(tanggal_faktur)=$tanggal[2] AND MONTH(tanggal_faktur)=$tanggal[1]")->max("no_faktur");
            $result = [
                    "no_faktur"=>$pembelian+1,
                    "sufix_no_faktur"=>"/".$tanggal[1]."/".$tanggal[2],
                ];

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

    function edit($id){
        $d_data = DB::table('pem_pembelian')->where('id_pembelian', '=', $id)->first();
        if($d_data->status != 0){
            return redirect(url("pembelian"));
        }
        $data['supplier'] = DB::table('ms_penyedia')->orderBy('nama', 'asc')->get();
        $perkiraan = DB::table('ms_perkiraan')->whereIn('parent',['3', '7'])->orderBy('kode_perkiraan', 'asc')->get();
        $arr_perkiraan = array_reduce($perkiraan->toArray(), function($carry, $item){
            $carry[ $item->id ] = $item->kode_perkiraan." ".$item->nama_perkiraan;
            return $carry;
        });
        $data['perkiraan'] = [null=>"--- Pilih Akun ---"] + $arr_perkiraan;
        $transaksi = DB::table('ms_transaksi')->whereIn('id_transaksi',['9', '15'])->orderBy('nama_transaksi', 'asc')->get();
        $arr_transaksi = array_reduce($transaksi->toArray(), function($carry, $item){
            $carry[ $item->id_transaksi ] = $item->nama_transaksi;
            return $carry;
        });

        $tempat_penyerahan = Tempatpenyerahan::with(['dataLokasi'])->orderByRaw("id_lokasi, nama")->get();
        $data['tempat_penyerahan'] = array_reduce($tempat_penyerahan->toArray(), function($carry, $item){
            $carry[ $item['id'] ] = $item['nama']." - ".$item['data_lokasi']['nama_lokasi'];
            return $carry;
        });

        $data['transaksi'] = [null=>"--- Pilih Transaksi ---"] + $arr_transaksi;
        $data['cara'] = DB::table('ms_carabayar as mc')->leftJoin('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);
        $data['lokasi'] = LokasiProduksi::get();
        $data['satuan'] = DB::table('ms_satuan')->orderBy('nama_satuan','asc')->get();
        return view('pembelian.PembelianTambah')->with('data', $data);
    }

    function edit_harga($id){
        $d_data = DB::table('pem_pembelian')->where('id_pembelian', '=', $id)->first();
        $data['supplier'] = DB::table('ms_penyedia')->orderBy('nama', 'asc')->get();
        $perkiraan = DB::table('ms_perkiraan')->whereIn('parent',['3', '7'])->orderBy('kode_perkiraan', 'asc')->get();
        $arr_perkiraan = array_reduce($perkiraan->toArray(), function($carry, $item){
            $carry[ $item->id ] = $item->kode_perkiraan." ".$item->nama_perkiraan;
            return $carry;
        });
        $data['perkiraan'] = [null=>"--- Pilih Akun ---"] + $arr_perkiraan;
        $transaksi = DB::table('ms_transaksi')->whereIn('id_transaksi',['9', '15'])->orderBy('nama_transaksi', 'asc')->get();
        $arr_transaksi = array_reduce($transaksi->toArray(), function($carry, $item){
            $carry[ $item->id_transaksi ] = $item->nama_transaksi;
            return $carry;
        });

        $tempat_penyerahan = Tempatpenyerahan::with(['dataLokasi'])->orderByRaw("id_lokasi, nama")->get();
        $data['tempat_penyerahan'] = array_reduce($tempat_penyerahan->toArray(), function($carry, $item){
            $carry[ $item['id'] ] = $item['nama']." - ".$item['data_lokasi']['nama_lokasi'];
            return $carry;
        });

        $data['transaksi'] = [null=>"--- Pilih Transaksi ---"] + $arr_transaksi;
        $data['cara'] = DB::table('ms_carabayar as mc')->leftJoin('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);
        $data['lokasi'] = LokasiProduksi::get();
        $data['satuan'] = DB::table('ms_satuan')->orderBy('nama_satuan','asc')->get();
        return view('pembelian.PembelianEditHarga')->with('data', $data);
    }

    function lihat($id){
        $d_data = DB::table('pem_pembelian')->where('id_pembelian', '=', $id)->first();
        $data['supplier'] = DB::table('ms_penyedia')->orderBy('nama', 'asc')->get();
        $data['transaksi'] = DB::table('ms_transaksi')->orderBy('nama_transaksi', 'asc')->get();
        $data['cara'] = DB::table('ms_carabayar')->orderBy('cara', 'asc')->get();
        $data['data'] = $this->data($d_data);

        return view('pembelian.PembelianLihat')->with('data', $data);
    }

    function detail($id){
        $d_data = DB::table('pem_pembelian')->where('id_pembelian', '=', $id)->first();
        $data['supplier'] = DB::table('ms_penyedia')->orderBy('nama', 'asc')->get();
        $perkiraan = DB::table('ms_perkiraan')->whereIn('parent',['3', '7'])->orderBy('kode_perkiraan', 'asc')->get();
        $arr_perkiraan = array_reduce($perkiraan->toArray(), function($carry, $item){
            $carry[ $item->id ] = $item->kode_perkiraan." ".$item->nama_perkiraan;
            return $carry;
        });
        $data['perkiraan'] = [null=>"--- Pilih Akun ---"] + $arr_perkiraan;
        $transaksi = DB::table('ms_transaksi')->whereIn('id_transaksi',['9', '15'])->orderBy('nama_transaksi', 'asc')->get();
        $arr_transaksi = array_reduce($transaksi->toArray(), function($carry, $item){
            $carry[ $item->id_transaksi ] = $item->nama_transaksi;
            return $carry;
        });
        $data['transaksi'] = [null=>"--- Pilih Transaksi ---"] + $arr_transaksi;
        $data['cara'] = DB::table('ms_carabayar as mc')->leftJoin('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);
        $data['lokasi'] = LokasiProduksi::get();
        return view('pembelian.PembelianDetail')->with('data', $data);
    }

    function simpan(Request $request){
        // dd($request->all());
        $session = Session::all();
    	$id_pembelian = $request->get('id_pembelian');
    	// $data['tanggal'] = date('Y-m-d', strtotime($request->get('tanggal')));
        $data['tanggal'] = date_db($request->get('tanggal'));
        $data['tanggal_faktur'] = date_db($request->get('tanggal_faktur'));
    	$data['tanggal_tempo'] = date_db($request->get('tanggal_tempo'));

        $data['no_faktur'] = $request->get('no_faktur');
    	$data['sufix_no_faktur'] = $request->get('sufix_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['termasuk_pajak'] = $request->get('termasuk_pajak') ? $request->get('termasuk_pajak') : '0';
        $data['pajak'] = $request->get('pajak');
        $data['no_faktur_pajak'] = $request->get('no_faktur_pajak');
        // $data['nominal_pajak'] = $request->get('td_pajak');
        $data['nominal_ongkir'] = $request->get('td_ongkir');
    	// $data['nominal_netto'] = $request->get('td_netto');
        // $data['id_carabayar'] = $request->get('cara_bayar');
        // $data['nominal_bayar'] = $request->get('td_nominal_bayar');

        // $cek_carabayar = DB::table("ms_carabayar")->where([["id_carabayar", "=", $data['id_carabayar']]])->first();
        // if($cek_carabayar->kode_perkiraan==0){
        //     $data['id_akun_carabayar'] = $request->get('id_akun_carabayar');
        // }else{
        //     $data['id_akun_carabayar'] = $cek_carabayar->kode_perkiraan;
        // }

        $data['syarat_bayar'] = $request->get('syarat_bayar');
        $data['tempat_serah'] = $request->get('tempat_serah');
        $data['tanggal_serah'] = date_db($request->get('tanggal_serah'));
        $data['penerima'] = date_db($request->get('penerima'));

        $data['id_akun_hutang'] = $this->id_akun['hutang_usaha'];
        $data['id_akun_potongan'] = $this->id_akun['potongan_pembelian'];
        $data['id_akun_pajak'] = $this->id_akun['ppn_masukan'];
        $data['cv'] = $request->get('cv') ? $request->get('cv') : 0;
        $data['lokasi'] = $request->get('lokasi');

    	$tabel_id = $request->get('tabel_id');
    	$tabel_idbarang = $request->get('tabel_idbarang');
    	$tabel_jumlah = $request->get('tabel_jumlah');
        $tabel_jumlah_konv = $request->get('tabel_jumlah_konv');
    	$tabel_harga = $request->get('tabel_harga');
    	$tabel_total = $request->get('tabel_total');
        $tabel_iob = $request->get('tabel_idorderbarang');
        $tabel_idsatuan = $request->get('tabel_idsatuan');
       // print_r($tabel_id);

        DB::beginTransaction();
        try {

    	if($id_pembelian == ''){
            $data['id_penyedia'] = $request->get('penyedia');
            $data['user_id_create'] = $session['kode_user'];
            $data['status'] = '0';
    		$id = DB::table('pem_pembelian')->insertGetId($data);
    		
    		for($i = 0; $i < count($tabel_id); $i++){
    			$barang['id_pembelian'] = $id;
    			$barang['id_barang'] = $tabel_idbarang[$i];
    			$barang['jml_barang'] = $tabel_jumlah[$i];
                $barang['jml_barang_konv'] = $tabel_jumlah_konv[$i];
    			$barang['harga_satuan'] = $tabel_harga[$i];
    			$barang['total'] = $tabel_total[$i];
                $barang['id_orderbeli_barang'] = $tabel_iob[$i];
                $barang['id_satuan'] = $tabel_idsatuan[$i];

    			DB::table('pem_pembelian_barang')->insert($barang);
    		}
    	}else{
            unset($data['id_transaksi']);
            unset($data['id_penyedia']);
            $data['user_id_update'] = $session['kode_user'];
            DB::table('pem_pembelian')->where(array('id_pembelian' => $id_pembelian))->update($data);
            $id_del = array();
            $id_store = array();
            $d_barang = DB::table('pem_pembelian_barang')->where('id_pembelian', '=', $id_pembelian);
            foreach($d_barang->get() as $d){
                $id_store[] = $d->id_beli_barang;
            }
            foreach($id_store as $d){
                if(count($tabel_id) > 0){
                    if(!in_array($d, $tabel_id)){
                        $id_del[] = $d;
                    }
                }else{
                    $id_del[] = $d;
                }
            }

            if(count($id_del) > 0){
                DB::table('pem_pembelian_barang')->whereIn('id_beli_barang', $id_del)->delete();
            }

            for($i = 0; $i < count($tabel_id); $i++){
                $barang['id_pembelian'] = $id_pembelian;
                $barang['id_barang'] = $tabel_idbarang[$i];
                $barang['jml_barang'] = $tabel_jumlah[$i];
                $barang['jml_barang_konv'] = $tabel_jumlah_konv[$i];
                $barang['harga_satuan'] = $tabel_harga[$i];
                $barang['total'] = $tabel_total[$i];
                $barang['id_orderbeli_barang'] = $tabel_iob[$i];
                $barang['id_satuan'] = $tabel_idsatuan[$i];

                if($tabel_id[$i] == ''){
                    DB::table('pem_pembelian_barang')->insert($barang);
                }else if($tabel_id != ''){
                    $where = 
                    DB::table('pem_pembelian_barang')->where(array('id_beli_barang' => $tabel_id[$i]))->update($barang);
                }
            }
    	}

            DB::commit();

        } catch (Exception $e) {
            DB::rollBack();

        }

    	return redirect('pembelian');
    }

    function simpan_harga(Request $request){
        // dd($request->all());
        $session = Session::all();
        $id_pembelian = $request->get('id_pembelian');

        $tabel_id = $request->get('tabel_id');
        $tabel_harga = $request->get('tabel_harga');
        $tabel_total = $request->get('tabel_total');

        DB::beginTransaction();
        try {

            $data['user_id_update'] = $session['kode_user'];
            DB::table('pem_pembelian')->where(array('id_pembelian' => $id_pembelian))->update($data);            

            for($i = 0; $i < count($tabel_id); $i++){
                $barang['harga_satuan'] = $tabel_harga[$i];
                $barang['total'] = $tabel_total[$i];

                DB::table('pem_pembelian_barang')->where(array('id_beli_barang' => $tabel_id[$i]))->update($barang);
            }

            DB::commit();

        } catch (Exception $e) {
            DB::rollBack();

        }

        return redirect('pembelian');
    }

    function get_edit(Request $request){
        $id = request()->get('id');
        $d_data = DB::table('pem_pembelian_barang AS ppb')->join('ms_barang AS mb', 'ppb.id_barang', 'mb.id')->leftJoin('ms_satuan as ms', 'ms.id_satuan', 'ppb.id_satuan')->where(array('id_pembelian' => $id, 'id_orderbeli_barang' => 0));
        if($d_data->count() > 0){
            foreach($d_data->get() as $d){
                $arr[] = array('id_beli_barang' => $d->id_beli_barang,
                                'id_barang' => $d->id_barang,
                                'kode_barang' => $d->kode_barang,
                                'nama_barang' => $d->nama_barang,
                                'jml_barang' => $d->jml_barang,
                                'jml_barang_konv' => $d->jml_barang_konv,
                                'harga_satuan' => $d->harga_satuan,
                                'id_satuan' => $d->id_satuan,
                                'nama_satuan' => $d->nama_satuan,
                                'total' => $d->total);
            }
        }else{
            $arr = array();
        }

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

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

        $d_data = DB::table('ms_konversi AS mk')->join('ms_satuan AS ms', 'mk.satuan_awal', 'ms.id_satuan')->join('ms_satuan AS ms2', 'mk.satuan_akhir', 'ms2.id_satuan')->select(DB::raw('mk.* , ms.nama_satuan ns_awal, ms.satuan s_awal, ms2.nama_satuan ns_akhir, ms2.satuan s_akhir'))->where('kode', '=', $id);
        if($d_data->count() > 0){
            foreach($d_data->get() as $d){
                $arr[] = array('id_konversi' => $d->id_konversi,
                            'satuan_awal' => $d->ns_awal,
                            'satuan_akhir' => $d->ns_akhir,
                            'nilai_awal' => $d->nilai_awal,
                            'nilai_akhir' => $d->nilai_akhir);
            }
        }else{
            $d_data = DB::table('ms_barang AS mb')->join('ms_satuan AS ms', 'mb.id_satuan', 'ms.id_satuan')->where('mb.id', $id)->select(DB::raw('mb.*, ms.nama_satuan AS satuan'));
            if($d_data->count() > 0){
                $arr = array('satuan_awal' => $d_data->first()->satuan);
            }else{
                $arr = array('satuan_awal' => '');
            }
        }

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

    //Get order

    function get_order(Request $request){
        if($_POST){
            $id = $request->get('id_penyedia');
            $trx = $request->get('id_transaksi');
         //   $d_data = DB::table('view_orderbeli')->where('id_penyedia', $id)->groupBy('id_penyedia');
            $id_o = '';
            if($request->get('id_orderbeli') != ''){
                $id_o = $request->get('id_orderbeli');
            }

            $d_data = $this->func_order($id, $id_o);

        }else{
            $id = $request->id;
            // $d_data = DB::table('view_orderbeli')->where('id_penyedia', $id)->groupBy('id_penyedia');
            $d_data = $this->func_order($id, '');
        }

        $arr = [];
        if(count($d_data) > 0){
            foreach($d_data as $d){
                if($d['sisa'] > 0){
                    $arr[] = array('id_penyedia' => $d['id_penyedia'],
                                    'no_order' => $d['no_faktur']."$d[sufix_no_order]",
                                    'no_faktur' => $d['no_faktur']."$d[sufix_no_order]",
                                    'nama_penyedia' => $d['nama'],
                                    'id_orderbeli_barang' => $d['id_orderbeli_barang'],
                                    'termasuk_pajak' => $d['termasuk_pajak'],
                                    'id_barang' => $d['id_barang'],
                                    'kode_barang' => $d['kode_barang'],
                                    'nama_barang' => $d['nama_barang'],
                                    'harga_satuan' => $d['harga_satuan']+$d['nilai_ppn'],
                                    'id_satuan' =>$d['id_satuan'],
                                    'nama_satuan' => $d['nama_satuan'],
                                    'beli' => $d['beli'],
                                    'ambil' => $d['ambil'],
                                    'sisa' => $d['sisa']);
                }else{
                    
                }
            }
        }else{
            $arr = array();
        }

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

     function get_editorder(Request $request){
        $id = request()->get('id');
        $d_data = DB::table('pem_pembelian_barang as ppb')->leftjoin('ms_satuan as ms', 'ms.id_satuan', 'ppb.id_satuan')->where(array('id_pembelian' => $id))->where('id_orderbeli_barang', '!=', '0');
        if($d_data->count() > 0){
            foreach($d_data->get() as $d){
                $barang = DB::table('ms_barang')->where('id', '=', $d->id_barang)->first();
                $termasuk_pajak = 0;
                if($d->id_orderbeli_barang != 0){
                    $orderbelibarang = DB::table("pem_orderbeli_barang AS ob")
                            ->selectRaw("ob.*, o.termasuk_pajak")
                            ->join("pem_orderbeli AS o", "o.id_orderbeli", "=", "ob.id_orderbeli")
                            ->where(['ob.id_orderbeli_barang'=>$d->id_orderbeli_barang])
                            ->first();
                    $termasuk_pajak = $orderbelibarang->termasuk_pajak;
                }
                $arr[] = array('id_beli_barang' => $d->id_beli_barang,
                                'id_barang' => $d->id_barang,
                                'kode_barang' => $barang->kode_barang,
                                'termasuk_pajak' => $termasuk_pajak,
                                'nama_barang' => $barang->nama_barang,
                                'jml_barang' => $d->jml_barang,
                                'jml_barang_konv' => $d->jml_barang_konv,
                                'harga_satuan' => $d->harga_satuan,
                                'total' => $d->total,
                                'id_obb' => $d->id_orderbeli_barang,
                                'id_satuan' => $d->id_satuan,
                                'nama_satuan' => $d->nama_satuan);
            }
        }else{
            $arr = array();
        }

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

    function get_stokdb(Request $request){

    }

    public function hapus(Request $request){
        $id = $request->get('id');
        DB::beginTransaction();
        try {
            DB::table('pem_pembelian')->where(array('id_pembelian' => $id))->delete();
            DB::table('pem_pembelian_barang')->where(array('id_pembelian' => $id))->delete();
            DB::table('pem_penerimaan')->where(array('id_pembelian' => $id))->delete();

            // HAPUS PEMBAYARAN
            $detailBayar = PembelianBayar::where(["id_pembelian"=>$id]);
            if ($detailBayar->count() > 0) {
                $id_transaksi_bayar = $detailBayar->first()->id_transaksi_bayar;
                
                $detailBayar->delete();

                $detailBayar2 = PembelianBayar::where(["id_transaksi_bayar"=>$id_transaksi_bayar])->count();
                if($detailBayar2 < 1){
                    PembelianBayarTransaksi::find($id_transaksi_bayar)->delete();
                }
            }
            // end : HAPUS PEMBAYARAN

            DB::commit();
        } catch (Exception $e) {
            DB::rollBack();
        }
    }

    function status_beli($id){
        switch ($id) {
            case '0':
                $d = '<span class="label label-danger">Belum Diterima</span>';
                break;
            case '1':
                $d = '<span class="label label-success">Diterima</span>';
            break;
            case '2':
                $d = '<span class="label label-warning">Diterima Sebagian</span>';
            break;
            default:
                $d = '<span class="label label-default"></span>';
                break;
        }

        return $d;
    }

    function data($data = array()){
        if(count($data) > 0){
            $store['id_pembelian'] = $data->id_pembelian;
            $store['lokasi'] = $data->lokasi;
            $store['cv'] = $data->cv;
            $store['tanggal'] = date('d/m/Y', strtotime($data->tanggal));
            $store['tanggal_faktur'] = date('d/m/Y', strtotime($data->tanggal_faktur));
            $store['tanggal_tempo'] = date('d/m/Y', strtotime($data->tanggal_tempo));
            $store['no_faktur'] = $data->no_faktur;
            $store['sufix_no_faktur'] = $data->sufix_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['termasuk_pajak'] = $data->termasuk_pajak;
            $store['pajak'] = $data->pajak;
            $store['no_faktur_pajak'] = $data->no_faktur_pajak;
            $store['nominal_potongan'] = $data->nominal_potongan;
            $store['nominal_ongkir'] = $data->nominal_ongkir;
            // $store['nominal_pajak'] = $data->nominal_pajak;
            // $store['nominal_netto'] = $data->nominal_netto;
            // $store['nominal_ongkir']= $data->nominal_ongkir;
            // $store['nominal_total'] = $data->nominal_total;
            // $store['nominal_bayar'] = $data->nominal_bayar;
            // $store['id_akun_carabayar'] = $data->id_akun_carabayar;
            $store['syarat_bayar'] = $data->syarat_bayar;
            $store['tempat_serah'] = $data->tempat_serah;
            $store['tanggal_serah'] = date('d/m/Y', strtotime($data->tanggal_serah));
            $store['penerima'] = $data->penerima;

        }else{
            $store['id_pembelian'] = '';
            $store['lokasi'] = Session::get("id_lokasi");
            $store['cv'] = true;
            $store['tanggal'] = date('d/m/Y');
            $store['tanggal_faktur'] = date('d/m/Y');
            $store['tanggal_tempo'] = date('d/m/Y');
            $store['no_faktur'] = '';
            $store['sufix_no_faktur'] = '';
            // $store['id_transaksi'] = '';
            // $store['id_carabayar'] = '';
            $store['id_penyedia'] = '';
            $store['keterangan'] = '';
            $store['termasuk_pajak'] = '0';
            $store['pajak'] = '0';
            $store['no_faktur_pajak'] = '-';
            $store['nominal_potongan'] = '0';
            $store['nominal_ongkir'] = '0';
            // $store['nominal_pajak'] = '0';
            // $store['nominal_netto'] = '0';
            // $store['nominal_ongkir'] = '0';
            // $store['nominal_total'] = '0';
            // $store['nominal_bayar'] = 0;
            // $store['id_akun_carabayar'] = '';
            $store['syarat_bayar'] = '';
            $store['tempat_serah'] = '';
            $store['tanggal_serah'] = '';
            $store['penerima'] = 'Owner';
        }

        return $store;
    }

    function func_order($id_penyedia = '', $id_order = ''){
        $where = '';
        if($id_order != ''){
            $where = "AND id_orderbeli_barang = '$id_order'";
        }
        $PDO = DB::connection('mysql')->getPdo();

        $billingStmt = $PDO->prepare("SELECT A.*, sum(beli-ambil) AS sisa FROM ( SELECT * FROM view_orderbeli WHERE id_penyedia = '$id_penyedia' $where) A GROUP BY A.id_orderbeli_barang, A.kode_barang, A.id_penyedia ORDER BY A.nama, A.kode_barang, A.nama_barang");
        $billingStmt->execute();
        $d_data = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));

        return $d_data;
    }

    /*function cetak($id){
        // require('fpdf181/fpdf.php');   
        require('fpdf181/Mc_table.php');
        // $data['data'] = DB::table('pem_pembelian')->where('id_pembelian', '=', $id)->first();
        $data['data'] = Pembelian::with(['dataPenyedia'])->find($id);
        $data['detail'] = [];
        // $data['detail'] = DB::table('pem_pembelian_barang AS ppb')->join('ms_barang AS mb', 'ppb.id_barang', 'mb.id')->join('ms_satuan as ms','mb.id_satuan','ms.id_satuan')->where(array('id_pembelian' => $id))->get();
        $html = view('pembelian.PembelianCetak')->with('data',$data);
        return response($html)->header('Content-Type', 'application/pdf');
    }*/

    function cetak(Request $request, $id){
        $input = $request->all();
        require('fpdf181/Mc_table.php');
        $data['data'] = DB::table("view_pembelian_2")->where(["id_pembelian"=>$id])->first();
        $data['detail'] = DB::table("view_pembelian_barang")->where('id_pembelian', '=', $id)->get();
        $data['tampil_harga'] = isset($input['tampil_harga']) ? $input['tampil_harga'] : "false";
        $data['tanggal_cetak']= isset($input['tanggal_cetak']) ? tgl_full($input['tanggal_cetak'], 0) : tgl_full(date("d-m-Y"), 0);
        // dd($data);
        if($input['uk_kertas']==1){
            $html = view('pembelian.PembelianCetakMini')->with('data',$data);

        }else if($input['uk_kertas']==2){
            $html = view('pembelian.PembelianCetakSedang')->with('data',$data);

        }
        return response($html)->header('Content-Type', 'application/pdf');
    }



    //bayar
    function bayar($id){
        $data['id'] = $id;
        $perkiraan = Perkiraan::whereIn('parent',['3', '7'])->orderBy('kode_perkiraan', 'asc')->get();
        $arr_perkiraan = array_reduce($perkiraan->toArray(), function($carry, $item){
            $carry[ $item['id'] ] = format_akun_perkiraan($item);
            return $carry;
        });
        $data['sumber_dana'] = [null=>"--- Pilih Akun ---"] + $arr_perkiraan;
        
        // $data['cara_bayar'] = DB::table('ms_carabayar as mc')->leftJoin('ms_perkiraan as mp','mc.kode_perkiraan','mp.id')->where('tipe', 'K')->orderByRaw('jenis_bayar asc, id_carabayar asc')->get();
        
        return view('pembelian.PembelianBayar')->with('data', $data);   
    }

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

    function get_detailbayar($id){
        $d_data = DB::table('view_pembelian_bayar')->where('id_pembelian', $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_pembelian_bayar')->where('id_pembelian', $id);
        $d_bayar = DB::table('pem_pembelian_bayar as ppb')
                ->leftjoin('ms_perkiraan AS mp', 'ppb.id_akun', 'mp.id')
                ->select(DB::raw('ppb.*,mp.nama_perkiraan,mp.kode_perkiraan'))->where('ppb.id_pembelian', $id);
        $data['bayar'] = array();
        // if($d_awal_bayar->count() > 0){
        //     $bayar_awal  = $d_awal_bayar->first();
            
        //     $data['bayar'][] = array(
        //                         'id_belibayar' => 'nominal_bayar',
        //                         'id_pembelian' => $bayar_awal->id_pembelian,
        //                         '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_belibayar' => $d->id_belibayar,
                                    'id_pembelian' => $d->id_pembelian,
                                    '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_belibayar = $request->get('id_belibayar');
        $data['id_pembelian'] = $request->get('id_beli');
        $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');

        DB::beginTransaction();
        try {
            if($id_belibayar == ''){
                DB::table('pem_pembelian_bayar')->insert($data);
            }else{
                DB::table('pem_pembelian_bayar')->where('id_belibayar', $id_belibayar)->update($data);
            }

            DB::commit();
        } catch (Exception $e) {
            DB::rollBack();
        }

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

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

        DB::table('pem_pembelian_bayar')->where(array('id_belibayar' => $id))->delete();

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

    /* bayar tagihan */
    public function bayar_tagihan(Request $request)
    {
        $input = $request->all();

        if(sizeof($input)==0){
            return redirect(url("hutang"));
        }else if(!isset($input['id']) && !isset($input['as'])){
            return redirect(url("hutang"));
        }

        $kode_sebagai = $this->get_index_arr_sebagai($input['as']);

        $data['pembelianById'] = DB::table("view_pembelian2")
                ->where([["id_pembelian", "=", $input['id']], ["kode_sebagai", "=", $kode_sebagai]])
                ->first();
        $data['pembelianBySupplier'] = DB::table("view_pembelian2")
                ->where([['id_penyedia', '=', $data['pembelianById']->id_penyedia]])
                ->where(function($query) use ($input, $kode_sebagai){
                    $query->where([ ["id_pembelian", "<>", $input['id']], ["kode_sebagai", "=", $kode_sebagai] ]);
                    $query->orWhere([ ["kode_sebagai", "<>", $kode_sebagai] ]);

                })
                ->orderByRaw("CAST(no_faktur AS UNSIGNED), tanggal_faktur")
                ->get();
                
        // $akunKas = dataAkunKasBank();
        $akunKas = dataAkun();
        $data['akunBayar'] = array_reduce($akunKas->toArray(), function($carry, $item){
            $carry[$item['id']] = format_akun_perkiraan($item);
            return $carry;
        });

        return view("pembelian.PembelianBayarTagihan", $data);
    }

    private function get_index_arr_sebagai($value='')
    {
        $arr_sebagai = ["", "hutang_awal", "hutang_pembelian"];
        return array_search($value, $arr_sebagai);
    }

    public function simpan_bayar_tagihan(Request $request)
    {
        $input = $request->all();
        $session = Session::all();
        $this->validate($request, [
            'no_transaksi_bayar'=>"required|not_in:0",
        ]);

        DB::beginTransaction();
        $dataTransaksiBayar = [
                "user_id_create"=>$session['kode_user'],
                "user_id_update"=>$session['kode_user'],
                "lokasi"=>1,
                "no_transaksi_bayar"=>$input['no_transaksi_bayar'],
                "tanggal_bayar"=>date_db($input['tgl_bayar']),
            ];
        $idTransaksi = DB::table("pem_pembelian_transaksi_bayar")->insertGetId($dataTransaksiBayar);
        try {
            foreach ($input['jml_bayar'] as $key => $value) {
                if($value==0) continue;
                $dataBayar = [
                    'id_transaksi_bayar' => $idTransaksi,
                    'id_akun' => $input['id_akun'],
                    'tipe_id_akun' => "K",
                    'tgl_bayar' => date_db($input['tgl_bayar']),
                    'jum_bayar' => $value,
                ];

                if($input['sebagai'][$key]=='hutang_awal'){
                    $dataBayar['id_hutangawal'] = $key;
                    DB::table("pem_hutangawal_bayar")->insert($dataBayar);

                }else if($input['sebagai'][$key]=='hutang_pembelian'){
                    $dataBayar['id_pembelian'] = $key;
                    PembelianBayar::insert($dataBayar);
                }
            }
            DB::commit();
            $message = '<span class="text-semibold">Berhasil!</span> Data berhasil disimpan.';
            return redirect('hutang')->with('flash_message_success', $message);
        } catch (Exception $e) {
            DB::rollBack();
            $message = '<span class="text-semibold">ERROR!</span> Terjadi kesalahan pada sistem.';
            return redirect('hutang')->with('flash_message_error', $message);
        }
        // dd($input);
        // return redirect(url('hutang'));
    }
    /* end : bayar tagihan */ 

    // daftar tagihan bayar pembelian
    public function daftar_bayar_tagihan(Request $request, $id_pembelian)
    {
        if($request->get("as")==NULL){
            return redirect(url("hutang"));
        }

        $kode_sebagai = $this->get_index_arr_sebagai($request->get("as"));

        $data['pembelian'] = DB::table('view_pembelian2')->where([ ['id_pembelian', '=', $id_pembelian], ['kode_sebagai', '=', $kode_sebagai] ])->first();
        // dd($data['pembelian']);

        return view("pembelian.PembelianDaftarBayar", $data);
    }

    public function tabel_daftar_bayar($sebagai, $id)
    {
        if($sebagai=="hutang_awal"){
            $data = HutangAwalBayar::with(['dataTransaksiBayar', 'dataAkunPerkiraan'])->where([ ['id_hutangawal', '=', $id] ])->get();

        }else{
            $data = PembelianBayar::with(['dataTransaksiBayar', 'dataAkunPerkiraan'])->where([ ['id_pembelian', '=', $id] ])->get();

        }

        return Datatables::of($data)
                ->addColumn("akun_bayar", function($data){
                    return format_akun_perkiraan($data->dataAkunPerkiraan);
                })
                ->addColumn("tgl_bayar", function($data){
                    return tgl_full($data->dataTransaksiBayar->tanggal_bayar, "");
                })
                ->addColumn("aksi", function($data) use ($sebagai, $id){
                    if ($sebagai=="hutang_awal") {
                        $button = "<div class='btn-group'>";
                            $button .= "<button type='button' class='btn btn-xs btn-danger' onclick='btnHapusDetailBayar($(this))'><i class='fa fa-trash'></i></button>";
                            $button .= "<button type='button' class='btn btn-xs btn-success' onclick='btnCetakBuktiBayar($(this))'><i class='fa fa-print'></i></button>";
                        $button .= "</div>";
                    } else {
                        $button = "<div class='btn-group'>";
                            $button .= "<button type='button' class='btn btn-xs btn-danger' onclick='btnHapusDetailBayar($(this))'><i class='fa fa-trash'></i></button>";
                            $button .= "<button type='button' class='btn btn-xs btn-success' onclick='btnCetakBuktiBayar($(this))'><i class='fa fa-print'></i></button>";
                            $button .= "<a href='".url('pembelian/pembayaran/edit/'.$data->id_belibayar.'/'.$id)."' class='btn btn-xs btn-warning'><i class='fa fa-pencil'></i></a>";
                        $button .= "</div>";
                    }
                    return $button;
                })
                ->make(true);
    }

    public function editPembayaran($id_belibayar = null, $id_pembelian = null)
    {
        $data['id_pembelian'] = $id_pembelian;
        $data['id_belibayar'] = $id_belibayar;

        $data['pembelian'] = DB::table("view_pembelian2 as pb")
                            ->join("ms_penyedia as pd", "pd.id_penyedia", "=", "pb.id_penyedia")
                            ->where("pb.id_pembelian", $id_pembelian)
                            ->select('pb.cv', 'pd.nama as nama_penyedia', 'pb.tanggal', 'pb.tanggal_faktur', 'pb.no_faktur', 'pb.sufix_no_faktur', 'pb.keterangan', 'pb.total_bayar', 'pb.nominal_netto')
                            ->first();

        $akunKas = dataAkunKasBank();
        $data['akunBayar'] = array_reduce($akunKas->toArray(), function($carry, $item){
            $carry[$item['id']] = format_akun_perkiraan($item);
            return $carry;
        });

        $data['akun_bayar'] = DB::table("pem_pembelian_bayar as pb")
                                ->join('pem_pembelian_transaksi_bayar as ptb', 'ptb.id', '=', 'pb.id_transaksi_bayar')
                                ->where('id_belibayar', $id_belibayar)
                                ->select('pb.tgl_bayar', 'pb.id_akun', 'pb.jum_bayar', 'ptb.no_transaksi_bayar', 'ptb.tanggal_bayar')
                                ->first();

        return view("pembelian.PembelianBayarEdit", $data);
    }

    public function updatePembayaran($id_belibayar = null, $id_pembelian = null, Request $request)
    {
        $count = DB::table('pem_pembelian_bayar')->where('id_belibayar', $id_belibayar)->count();
        if ($count > 0) {
            DB::beginTransaction();
            try{
                $data = array(
                    'id_akun'    => $request->get('akun_bayar'),
                    'tgl_bayar'  => date_db($request->get('tanggal_bayar')),
                    'jum_bayar'  => $request->get('jum_bayar'),
                    'updated_at' => date('Y-m-d H:i:s')
                );

                DB::table('pem_pembelian_bayar')->where('id_belibayar', $id_belibayar)->update($data);
                $id_transaksi_bayar = DB::table('pem_pembelian_bayar')->where('id_belibayar', $id_belibayar)->first()->id_transaksi_bayar;
                $data_tr = array('tanggal_bayar'=>date_db($request->get('tanggal_bayar')), 'no_transaksi_bayar'=>$request->get('no_transaksi_bayar'));
                DB::table('pem_pembelian_transaksi_bayar')->where('id', $id_transaksi_bayar)->update($data_tr);
                DB::commit();
                return redirect('pembelian/daftar_bayar_tagihan/'.$id_pembelian.'?as=hutang_pembelian')->with('flash_message_success', 'Data berhasil diperbarui!');
            } catch(Exception $e) {
                DB::rollBack();
                return redirect('pembelian/daftar_bayar_tagihan/'.$id_pembelian.'?as=hutang_pembelian')->with('flash_message_error', 'Data gagal diperbarui! COBA LAGI...');
            }
        } else {
            return redirect('pembelian/daftar_bayar_tagihan/'.$id_pembelian.'?as=hutang_pembelian')->with('flash_message_error', 'Data tidak ditemukan!');
        }
    }

    public function delete_detail_bayar(Request $request)
    {
        $input = $request->all();
        DB::beginTransaction();
        try {
            
            if($request->get("sebagai")=="hutang_awal"){
                $detailBayar = HutangAwalBayar::where(["id"=>$input['id']]);

            }else{
                $detailBayar = PembelianBayar::where(["id_belibayar"=>$input['id']]);

            }

            $id_transaksi_bayar = $detailBayar->first()->id_transaksi_bayar;
            
            $detailBayar->delete();

            $q_pembelian_bayar = DB::table("pem_pembelian_bayar");
            $q_hutangawal_bayar = DB::table("pem_hutangawal_bayar");

            $detailBayar2 = $q_pembelian_bayar->unionAll($q_hutangawal_bayar)->where(["id_transaksi_bayar"=>$id_transaksi_bayar])->get();

            // $detailBayar2 = PembelianBayar::where(["id_transaksi_bayar"=>$id_transaksi_bayar])->get();

            if(sizeof($detailBayar2->toArray())<1){
                PembelianBayarTransaksi::find($id_transaksi_bayar)->delete();
            }

            DB::commit();
        } catch (Exception $e) {
            DB::rollBack();
        }
    }

    public function cetak_bukti_bayar(Request $request)
    {
        $input = $request->all();

        if(!isset($input['as'])){
            return redirect(url("hutang"));
        }

        if($input['as']=="hutang_awal"){
            $data['detail_data'] = HutangAwalBayar::with(['dataTransaksiBayar', 'dataAkunPerkiraan'])->find($input['id']); // data berdasarkan id bayar yg dipilih

            $data['pembelian'] = HutangAwal::with(['dataPenyedia'])->find($data['detail_data']->id_hutangawal);

            $q_akun_debet = "
                    SELECT
                        b.*,
                        SUM(IFNULL(jum_bayar, 0)) as jumlah,
                        'D' AS tipe_id_akun_hutang,
                        a.id,
                        a.kd_rek_1,
                        a.kode_perkiraan,
                        a.nama_perkiraan,
                        a.level,
                        a.saldo_normal,
                        a.used,
                        a.parent,
                        a.locked
                    FROM
                        pem_hutangawal_bayar b
                    JOIN pem_hutangawal pb ON pb.id = b.id_hutangawal
                    JOIN ms_penyedia s ON s.id_penyedia = pb.id_penyedia
                    JOIN ms_perkiraan a ON a.id = s.id_akun_hutang
                    WHERE b.id_transaksi_bayar = ?
                    GROUP BY b.id_transaksi_bayar, a.id
                ";
            
            $q_akun_kredit = "
                    SELECT
                        b.*,
                        SUM(IFNULL(jum_bayar, 0)) as jumlah,
                        a.id,
                        a.kd_rek_1,
                        a.kode_perkiraan,
                        a.nama_perkiraan,
                        a.level,
                        a.saldo_normal,
                        a.used,
                        a.parent,
                        a.locked
                    FROM
                        pem_hutangawal_bayar b
                    JOIN pem_hutangawal pb ON pb.id = b.id_hutangawal
                    JOIN ms_penyedia s ON s.id_penyedia = pb.id_penyedia
                    JOIN ms_perkiraan a ON a.id = b.id_akun
                    WHERE b.id_transaksi_bayar = ?
                    GROUP BY b.id_transaksi_bayar, b.id_akun
                ";

            $data['transaksi'] = HutangAwalBayar::where('id_transaksi_bayar', '=', $data['detail_data']->id_transaksi_bayar)->get();
        }else{
            $data['detail_data'] = PembelianBayar::with(['dataTransaksiBayar', 'dataAkunPerkiraan'])->find($input['id']); // data berdasarkan id bayar yg dipilih
            
            $data['pembelian'] = Pembelian::with(['dataPenyedia'])->find($data['detail_data']->id_pembelian);

            $q_akun_debet = "
                    SELECT
                        b.*,
                        SUM(IFNULL(jum_bayar, 0)) as jumlah,
                        'D' AS tipe_id_akun_hutang,
                        a.id,
                        a.kd_rek_1,
                        a.kode_perkiraan,
                        a.nama_perkiraan,
                        a.level,
                        a.saldo_normal,
                        a.used,
                        a.parent,
                        a.locked
                    FROM
                        pem_pembelian_bayar b
                    JOIN pem_pembelian pb ON pb.id_pembelian = b.id_pembelian
                    JOIN ms_penyedia s ON s.id_penyedia = pb.id_penyedia
                    JOIN ms_perkiraan a ON a.id = s.id_akun_hutang
                    WHERE b.id_transaksi_bayar = ?
                    GROUP BY b.id_transaksi_bayar, a.id
                ";
            
            $q_akun_kredit = "
                    SELECT
                        b.*,
                        SUM(IFNULL(jum_bayar, 0)) as jumlah,
                        a.id,
                        a.kd_rek_1,
                        a.kode_perkiraan,
                        a.nama_perkiraan,
                        a.level,
                        a.saldo_normal,
                        a.used,
                        a.parent,
                        a.locked
                    FROM
                        pem_pembelian_bayar b
                    JOIN pem_pembelian pb ON pb.id_pembelian = b.id_pembelian
                    JOIN ms_penyedia s ON s.id_penyedia = pb.id_penyedia
                    JOIN ms_perkiraan a ON a.id = b.id_akun
                    WHERE b.id_transaksi_bayar = ?
                    GROUP BY b.id_transaksi_bayar, b.id_akun
                ";
            $data['transaksi'] = PembelianBayarTransaksi::with(['pembelianBayar'])->find($data['detail_data']->id_transaksi_bayar);
        }

        $akun_debet = DB::select($q_akun_debet, [$data['detail_data']->id_transaksi_bayar]);
        $akun_kredit = DB::select($q_akun_kredit, [$data['detail_data']->id_transaksi_bayar]);

        $data['posting'] = [
                "row_debet"=>$akun_debet,
                "row_kredit"=>$akun_kredit,
            ];

        $data['label_cv'] = isset($input['label_cv']) ? $input['label_cv'] : "false";
        $data['tanggal_cetak']= isset($input['tanggal_cetak']) ? tgl_full($input['tanggal_cetak'], 98) : tgl_full(date("d-m-Y"), 98);
        $data['bukti'] = 'KELUAR';
        
        if($input['uk_kertas']==1){
            $html = view('pembelian.CetakBuktiBayarKecil')->with('data',$data);
        }else if($input['uk_kertas']==2){
            $html = view('pembelian.CetakBuktiBayarSedang')->with('data',$data);
        }

        require('fpdf181/Mc_table.php');
        return response($html)->header('Content-Type', 'application/pdf');
        // return view('pembelian.CetakBuktiBayar', $data);
    }
    // end : daftar tagihan bayar pembelian

    public function set_status_bayar($data)
    {
        $tagihan = $data->nominal_netto;
        $pembayaran = $data->total_bayar;

        $total_bayar = $pembayaran;
        if($total_bayar == 0){
            $bayar = 0; // belum bayar
        }else if($total_bayar < $tagihan){
            $bayar = 1; // proses bayar
        }else if($total_bayar > $tagihan){
            $bayar = 3; // pembayaran berlebih
        }else{
            $bayar = 2; // lunas
        }

        return $bayar;
    }
}

