<?php

namespace App\Http\Controllers\keuangan;

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

class ReturTagihanPiutangController 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.returtagihan.index');
    }

    public function getData()
    {
    	$d_data = DB::table('retur_tagihan_penjualan as rtp')
    			->join('ms_matauang', 'ms_matauang.id_matauang', '=', 'rtp.mata_uang')
    			->join('pen_penjualan as pj', 'pj.id_penjualan', 'rtp.id_penjualan')
    			->select('rtp.id as id_rtp', 'rtp.tanggal_retur', 'rtp.no_retur', 'rtp.sufix_no_retur', 'pj.no_faktur', 'pj.sufix_no_faktur', 'rtp.nominal_retur', 'ms_matauang.*');

    	return Datatables::of($d_data)
    			->addColumn('no_retur', function($d_data){
    				return $d_data->no_retur.$d_data->sufix_no_retur;
    			})
    			->addColumn('no_faktur', function($d_data){
    				return $d_data->no_faktur.$d_data->sufix_no_faktur;
    			})
    			->addColumn("mata_uang", function($data){
                    return $data->simbol;
                })
    			->addColumn('nominal_retur', function($d_data){
    				return $d_data->nominal_retur;
    			})
    			->addColumn('aksi', function($d_data){
    				$button = "<div class='btn-group'>";

                    $button .= '<a class="btn btn-xs bg-warning-400" href="'.url('retur-tagihan-piutang/edit/'. $d_data->id_rtp).'"><i class="fa fa-pencil"></i></a>';
                    $button .='<button type="button" onclick="deleteRetur('.$d_data->id_rtp.')" class="btn btn-xs bg-danger-400"><i class="fa fa-trash"></i></button>';

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

    public function create(Request $request)
    {
    	if ($request->isMethod('get')) {
    		$data['lokasi'] = DB::table("ms_lokasi_produksi")->get();
    		$data['mata_uang'] = DB::table('ms_matauang')->get();
    		$no_faktur = [];

    		$d_nofaktur = DB::table('view_penjualan2')
                ->join('ms_matauang', 'ms_matauang.id_matauang', '=', 'view_penjualan2.mata_uang')
                ->leftjoin('retur_tagihan_penjualan as rtp', 'rtp.id_penjualan', 'view_penjualan2.id_penjualan')
                ->orderBy('tanggal_faktur', 'ASC')
                ->orderBy('no_faktur', 'ASC')
                ->selectRaw('@rownum  := @rownum  + 1 AS rownum, view_penjualan2.*, (case when rtp.nominal_retur is null then 0 else sum(rtp.nominal_retur) end) AS nominal_retur, rtp.mata_uang as rtpmatauang, rtp.kurs, CONCAT(no_faktur, sufix_no_faktur) AS nomor_faktur,ms_matauang.*')
                ->whereNotIn("tanggal_faktur", ['0000-00-00'])
                ->where('kode_sebagai', 2)
                ->groupBy('view_penjualan2.id_penjualan')
                ->get();

            foreach ($d_nofaktur as $key => $value) {
            	$total_bayar = $value->total_bayar-$value->nominal_retur;
            	$retur = DB::table('a_pen_penjualan_retur_groupby_penjualan')->where('id_penjualan', $value->id_penjualan);

            	if ($retur->count() > 0) {
            	    $tagihan = $value->nominal_netto-$retur->first()->nominal_total;
            	} else {
            	    $tagihan = $value->nominal_netto;
            	}

            	if($total_bayar == 0){
            	    $bayar = 0;//Belum Bayar
            	}else if($total_bayar < $tagihan){
            	    $bayar = 1;//Proses Bayar
            	}else if($total_bayar > $tagihan){
            	    $bayar = 3;//Kelebihan Bayar
            	    $sisa = $total_bayar - $tagihan;
            	}else{
            	    $bayar = 2;//Lunas
            	}

            	if ($bayar == 3) {
            		$no_faktur[] = array(
            			"id" 		=> $value->id_penjualan.'_'.$value->id_pelanggan.'_'.$value->cv.'_'.$sisa,
            			"no_faktur" => $value->no_faktur.$value->sufix_no_faktur,
            			"nama_pelanggan" => $value->nama_pelanggan
            		);
            	}
            }

            $data['no_faktur'] = $no_faktur;
            $akun = DB::table("ms_perkiraan")->whereIn("parent", [3, 7])->orderByRaw("kd_rek_1, kode_perkiraan")->get();
            $data['akun'] = array_reduce($akun->toArray(), function($carry, $item){
            	$carry[ $item->id ] = format_akun_perkiraan($item);
            	return $carry;
            });

    		return view('keuangan.returtagihan.create', $data);
    	} else {
    		// id_penjualan = id_penjualan_id_pelanggan_cv_sisa
    		// dd($request->all());
    		DB::beginTransaction();
    		try{
    			$explode = explode("_", $request->get('id_penjualan'));

    			$d_data = array(
    				'lokasi' 			=> $request->get('lokasi'),
    				'user_id_create' 	=> Session::get('kode_user'),
    				'user_id_update' 	=> Session::get('kode_user'),
    				'cv' 				=> $explode[2],
    				'no_retur' 			=> $request->get('no_retur'),
    				'sufix_no_retur' 	=> $request->get('sufix_no_retur'),
    				'tanggal' 			=> date('Y-m-d'),
    				'tanggal_retur'		=> date_db($request->get('tanggal_retur')),
    				'tanggal_full'		=> date('Y-m-d H:i:s'),
    				'id_pelanggan'		=> $explode[1],
    				'id_penjualan'		=> $explode[0],
    				'mata_uang'			=> $request->get('currency'),
    				'kurs'				=> $request->get('kurs'),
    				'nominal_retur'		=> $request->get('nominal') == "" ? 0 : $request->get('nominal'),
    				'keterangan'		=> $request->get('keterangan'),
    				'id_akun'			=> $request->get('id_akun'),
    				'tipe_id_akun'		=> "K"
    			);

    			DB::table('retur_tagihan_penjualan')->insert($d_data);
    			DB::commit();
    			return redirect()->back()->with('flash_message_success', 'Data berhasil disimpan!');
    		}catch(Exception $e){
    			DB::rollback();
    			return redirect()->back()->with('flash_message_error', 'Data gagal disimpan!');
    		}
    	}
    }

    public function edit(Request $request, $id = null)
    {
    	if ($request->isMethod('get')) {
    		$check = DB::table('retur_tagihan_penjualan')->where('id', $id)->count();
    		if ($check > 0) {
				$data['lokasi'] 	= DB::table("ms_lokasi_produksi")->get();
				$data['mata_uang'] 	= DB::table('ms_matauang')->get();
				
		        $akun = DB::table("ms_perkiraan")->whereIn("parent", [3, 7])->orderByRaw("kd_rek_1, kode_perkiraan")->get();
		        $data['akun'] = array_reduce($akun->toArray(), function($carry, $item){
		        	$carry[ $item->id ] = format_akun_perkiraan($item);
		        	return $carry;
		        });

    			$data['id'] = $id;

	    		$data['data'] = DB::table('retur_tagihan_penjualan as rtp')
	    						->join('pen_penjualan as pj', 'pj.id_penjualan', 'rtp.id_penjualan')
	    						->join('ms_pelanggan as pl', 'pl.id_pelanggan', 'pj.id_pelanggan')
	    						->where('rtp.id', $id)
	    						->selectRaw('rtp.*, CONCAT_WS(" ", CONCAT("", pj.no_faktur, pj.sufix_no_faktur), CONCAT_WS("", "(", pl.nama, ")")) as no_faktur')
	    						->first();

	    		return view('keuangan.returtagihan.edit', $data);
    		} else {
    			return redirect()->back()->with('flash_message_error', 'Data tidak ditemukan');
    		}
    	} else {
    		$check = DB::table('retur_tagihan_penjualan')->where('id', $id)->count();
    		if ($check > 0) {
    			DB::beginTransaction();
	    		try{
	    			$d_data = array(
	    				'lokasi' 			=> $request->get('lokasi'),
	    				'user_id_update' 	=> Session::get('kode_user'),
	    				'no_retur' 			=> $request->get('no_retur'),
	    				'sufix_no_retur' 	=> $request->get('sufix_no_retur'),
	    				'tanggal_retur'		=> date_db($request->get('tanggal_retur')),
	    				'mata_uang'			=> $request->get('currency'),
	    				'kurs'				=> $request->get('kurs'),
	    				'nominal_retur'		=> $request->get('nominal') == "" ? 0 : $request->get('nominal'),
	    				'keterangan'		=> $request->get('keterangan'),
	    				'id_akun'			=> $request->get('id_akun'),
	    				'updated_at'		=> date('Y-m-d H:i:s')
	    			);

	    			DB::table('retur_tagihan_penjualan')->where('id', $id)->update($d_data);
	    			DB::commit();
	    			return redirect('retur-tagihan-piutang')->with('flash_message_success', 'Data berhasil diperbarui!');
	    		}catch(Exception $e){
	    			DB::rollback();
	    			return redirect('retur-tagihan-piutang')->with('flash_message_error', 'Data gagal diperbarui!');
	    		}
    		} else {
    			return redirect('retur-tagihan-piutang')->with('flash_message_error', 'Data tidak ditemukan!');
    		}
    	}
    }

    public function getNomor(Request $request)
    {
        $input = $request->all();
        $tanggal = explode("/", $input['tanggal']);
        
        $data = DB::table('retur_tagihan_penjualan')
        		->whereYear('tanggal_retur', $tanggal[2])
        		->whereMonth('tanggal_retur', $tanggal[1])
        		->max('no_retur');

        if($data == 0){
            $data = substr($tanggal[2], -2).$tanggal[1].sprintf("%03d", 0);
        }
        $result = [
                "no_retur"=>$data+1,
                "sufix_no_faktur"=>"/RTP",
            ];

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

    public function delete($id = null)
    {
    	DB::beginTransaction();
    	try{
    		$rtp = DB::table('retur_tagihan_penjualan')->where('id', $id);

    		if ($rtp->count() > 0) {
    			$rtp->delete();
    			DB::commit();
    			return response()->json(['status'=>"Success"]);
    		} else {
    			return response()->json(['status'=>"Failed"]);
    		}
    	}catch(Exception $e){
    		DB::rollback();
    		return response()->json(['status'=>"Failed"]);
    	}
    }

    // ====================================================

    public function returPiutang($id_penjualan = null)
    {
        $check = DB::table('view_penjualan2 as pj')
                ->where('pj.id_penjualan', $id_penjualan)
                ->where('pj.kode_sebagai', 2)
                ->count();

        if ($check > 0) {
            $data['lokasi'] = DB::table("ms_lokasi_produksi")->get();
            $data['mata_uang'] = DB::table('ms_matauang')->get();
            $akun = DB::table("ms_perkiraan")->whereIn("parent", [3, 7])->orderByRaw("kd_rek_1, kode_perkiraan")->get();
            $data['akun'] = array_reduce($akun->toArray(), function($carry, $item){
                $carry[ $item->id ] = format_akun_perkiraan($item);
                return $carry;
            });
            $data['data'] = DB::table('view_penjualan2 as pj')
                            ->leftjoin('retur_tagihan_penjualan as rtp', 'rtp.id_penjualan', 'pj.id_penjualan')
                            ->where('pj.id_penjualan', $id_penjualan)
                            ->where('pj.kode_sebagai', 2)
                            ->selectRaw('pj.*, (case when rtp.nominal_retur is null then 0 else sum(rtp.nominal_retur) end) AS nominal_retur')
                            ->groupBy('pj.id_penjualan')
                            ->first();

            $total_bayar = $data['data']->total_bayar-$data['data']->nominal_retur;
            $retur = DB::table('a_pen_penjualan_retur_groupby_penjualan')->where('id_penjualan', $id_penjualan);

            if ($retur->count() > 0) {
                $tagihan = $data['data']->nominal_netto-$retur->first()->nominal_total;
            } else {
                $tagihan = $data['data']->nominal_netto;
            }

            $data['sisa'] = $total_bayar - $tagihan;

            return view('keuangan.returtagihan.returPiutangCreate', $data);
        } else {
            return redirect()->back()->with('flash_message_error', 'Data tidak ditemukan!');
        }
    }

    public function createPiutang(Request $request)
    {
        DB::beginTransaction();
        try{
            $d_data = array(
                'lokasi'            => $request->get('lokasi'),
                'user_id_create'    => Session::get('kode_user'),
                'user_id_update'    => Session::get('kode_user'),
                'cv'                => $request->get('cv'),
                'no_retur'          => $request->get('no_retur'),
                'sufix_no_retur'    => $request->get('sufix_no_retur'),
                'tanggal'           => date('Y-m-d'),
                'tanggal_retur'     => date_db($request->get('tanggal_retur')),
                'tanggal_full'      => date('Y-m-d H:i:s'),
                'id_pelanggan'      => $request->get('id_pelanggan'),
                'id_penjualan'      => $request->get('id_penjualan'),
                'mata_uang'         => $request->get('currency'),
                'kurs'              => $request->get('kurs'),
                'nominal_retur'     => $request->get('nominal') == "" ? 0 : $request->get('nominal'),
                'keterangan'        => $request->get('keterangan'),
                'id_akun'           => $request->get('id_akun'),
                'tipe_id_akun'      => "K"
            );

            DB::table('retur_tagihan_penjualan')->insert($d_data);
            DB::commit();
            return redirect('piutang')->with('flash_message_success', 'Data berhasil disimpan!');
        }catch(Exception $e){
            DB::rollback();
            return redirect('piutang')->with('flash_message_error', 'Data gagal disimpan!');
        }
    }

    // ===================================================
    // Piutang Awal

    public function indexPiutangAwal()
    {
        return view('keuangan.returtagihan.indexPiutangAwal');
    }

    public function getDataPiutangAwal()
    {
        $d_data = DB::table('retur_tagihan_piutangawal as rtp')
                ->join('ms_matauang', 'ms_matauang.id_matauang', '=', 'rtp.mata_uang')
                ->join('view_penjualan2 as pj', 'pj.id_penjualan', 'rtp.id_piutangawal')
                ->where('pj.kode_sebagai', 1)
                ->select('rtp.id as id_rtp', 'rtp.tanggal_retur', 'rtp.no_retur', 'rtp.sufix_no_retur', 'pj.no_faktur', 'pj.sufix_no_faktur', 'rtp.nominal_retur', 'ms_matauang.*');

        return Datatables::of($d_data)
                ->addColumn('no_retur', function($d_data){
                    return $d_data->no_retur.$d_data->sufix_no_retur;
                })
                ->addColumn('no_faktur', function($d_data){
                    return $d_data->no_faktur.$d_data->sufix_no_faktur;
                })
                ->addColumn("mata_uang", function($data){
                    return $data->simbol;
                })
                ->addColumn('nominal_retur', function($d_data){
                    return $d_data->nominal_retur;
                })
                ->addColumn('aksi', function($d_data){
                    $button = "<div class='btn-group'>";

                    $button .= '<a class="btn btn-xs bg-warning-400" href="'.url('retur-tagihan-piutang-awal/edit/'. $d_data->id_rtp).'"><i class="fa fa-pencil"></i></a>';
                    $button .='<button type="button" onclick="deleteRetur('.$d_data->id_rtp.')" class="btn btn-xs bg-danger-400"><i class="fa fa-trash"></i></button>';

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

    public function addPiutangAwal(Request $request)
    {
        if ($request->isMethod('get')) {
            $data['lokasi'] = DB::table("ms_lokasi_produksi")->get();
            $data['mata_uang'] = DB::table('ms_matauang')->get();
            $no_faktur = [];

            $d_nofaktur = DB::table('view_penjualan2')
                ->join('ms_matauang', 'ms_matauang.id_matauang', '=', 'view_penjualan2.mata_uang')
                ->leftjoin('retur_tagihan_piutangawal as rtp', 'rtp.id_piutangawal', 'view_penjualan2.id_penjualan')
                ->orderBy('tanggal_faktur', 'ASC')
                ->orderBy('no_faktur', 'ASC')
                ->selectRaw('@rownum  := @rownum  + 1 AS rownum, view_penjualan2.*, (case when rtp.nominal_retur is null then 0 else sum(rtp.nominal_retur) end) AS nominal_retur, rtp.mata_uang as rtpmatauang, rtp.kurs, CONCAT(no_faktur, sufix_no_faktur) AS nomor_faktur,ms_matauang.*')
                ->whereNotIn("tanggal_faktur", ['0000-00-00'])
                ->where('kode_sebagai', 1)
                ->groupBy('view_penjualan2.id_penjualan')
                ->get();

            foreach ($d_nofaktur as $key => $value) {
                $total_bayar = $value->total_bayar-$value->nominal_retur;
                $tagihan = $value->nominal_netto;

                if($total_bayar == 0){
                    $bayar = 0;//Belum Bayar
                }else if($total_bayar < $tagihan){
                    $bayar = 1;//Proses Bayar
                }else if($total_bayar > $tagihan){
                    $bayar = 3;//Kelebihan Bayar
                    $sisa = $total_bayar - $tagihan;
                }else{
                    $bayar = 2;//Lunas
                }

                if ($bayar == 3) {
                    $no_faktur[] = array(
                        "id"        => $value->id_penjualan.'_'.$value->id_pelanggan.'_'.$value->cv.'_'.$sisa,
                        "no_faktur" => $value->no_faktur.$value->sufix_no_faktur,
                        "nama_pelanggan" => $value->nama_pelanggan
                    );
                }
            }

            $data['no_faktur'] = $no_faktur;
            $akun = DB::table("ms_perkiraan")->whereIn("parent", [3, 7])->orderByRaw("kd_rek_1, kode_perkiraan")->get();
            $data['akun'] = array_reduce($akun->toArray(), function($carry, $item){
                $carry[ $item->id ] = format_akun_perkiraan($item);
                return $carry;
            });

            return view('keuangan.returtagihan.createPiutangAwal', $data);
        } else {
            // id_penjualan = id_penjualan_id_pelanggan_cv_sisa
            // dd($request->all());
            DB::beginTransaction();
            try{
                $explode = explode("_", $request->get('id_penjualan'));

                $d_data = array(
                    'lokasi'            => $request->get('lokasi'),
                    'user_id_create'    => Session::get('kode_user'),
                    'user_id_update'    => Session::get('kode_user'),
                    'cv'                => $explode[2],
                    'no_retur'          => $request->get('no_retur'),
                    'sufix_no_retur'    => $request->get('sufix_no_retur'),
                    'tanggal'           => date('Y-m-d'),
                    'tanggal_retur'     => date_db($request->get('tanggal_retur')),
                    'tanggal_full'      => date('Y-m-d H:i:s'),
                    'id_pelanggan'      => $explode[1],
                    'id_piutangawal'      => $explode[0],
                    'mata_uang'         => $request->get('currency'),
                    'kurs'              => $request->get('kurs'),
                    'nominal_retur'     => $request->get('nominal') == "" ? 0 : $request->get('nominal'),
                    'keterangan'        => $request->get('keterangan'),
                    'id_akun'           => $request->get('id_akun'),
                    'tipe_id_akun'      => "K"
                );

                DB::table('retur_tagihan_piutangawal')->insert($d_data);
                DB::commit();
                return redirect()->back()->with('flash_message_success', 'Data berhasil disimpan!');
            }catch(Exception $e){
                DB::rollback();
                return redirect()->back()->with('flash_message_error', 'Data gagal disimpan!');
            }
        }
    }

    public function editPiutangAwal(Request $request, $id = null)
    {
        if ($request->isMethod('get')) {
            $check = DB::table('retur_tagihan_piutangawal')->where('id', $id)->count();
            if ($check > 0) {
                $data['lokasi']     = DB::table("ms_lokasi_produksi")->get();
                $data['mata_uang']  = DB::table('ms_matauang')->get();
                
                $akun = DB::table("ms_perkiraan")->whereIn("parent", [3, 7])->orderByRaw("kd_rek_1, kode_perkiraan")->get();
                $data['akun'] = array_reduce($akun->toArray(), function($carry, $item){
                    $carry[ $item->id ] = format_akun_perkiraan($item);
                    return $carry;
                });

                $data['id'] = $id;

                $data['data'] = DB::table('retur_tagihan_piutangawal as rtp')
                                ->join('view_penjualan2 as pj', 'pj.id_penjualan', 'rtp.id_piutangawal')
                                ->join('ms_pelanggan as pl', 'pl.id_pelanggan', 'pj.id_pelanggan')
                                ->where('rtp.id', $id)
                                ->where('pj.kode_sebagai', 1)
                                ->selectRaw('rtp.*, CONCAT_WS(" ", CONCAT("", pj.no_faktur, pj.sufix_no_faktur), CONCAT_WS("", "(", pl.nama, ")")) as no_faktur')
                                ->first();

                return view('keuangan.returtagihan.editPiutangAwal', $data);
            } else {
                return redirect()->back()->with('flash_message_error', 'Data tidak ditemukan');
            }
        } else {
            $check = DB::table('retur_tagihan_piutangawal')->where('id', $id)->count();
            if ($check > 0) {
                DB::beginTransaction();
                try{
                    $d_data = array(
                        'lokasi'            => $request->get('lokasi'),
                        'user_id_update'    => Session::get('kode_user'),
                        'no_retur'          => $request->get('no_retur'),
                        'sufix_no_retur'    => $request->get('sufix_no_retur'),
                        'tanggal_retur'     => date_db($request->get('tanggal_retur')),
                        'mata_uang'         => $request->get('currency'),
                        'kurs'              => $request->get('kurs'),
                        'nominal_retur'     => $request->get('nominal') == "" ? 0 : $request->get('nominal'),
                        'keterangan'        => $request->get('keterangan'),
                        'id_akun'           => $request->get('id_akun'),
                        'updated_at'        => date('Y-m-d H:i:s')
                    );

                    DB::table('retur_tagihan_piutangawal')->where('id', $id)->update($d_data);
                    DB::commit();
                    return redirect('retur-tagihan-piutang-awal')->with('flash_message_success', 'Data berhasil diperbarui!');
                }catch(Exception $e){
                    DB::rollback();
                    return redirect('retur-tagihan-piutang-awal')->with('flash_message_error', 'Data gagal diperbarui!');
                }
            } else {
                return redirect('retur-tagihan-piutang-awal')->with('flash_message_error', 'Data tidak ditemukan!');
            }
        }
    }

    public function getNomorPiutangAwal(Request $request)
    {
        $input = $request->all();
        $tanggal = explode("/", $input['tanggal']);
        
        $data = DB::table('retur_tagihan_piutangawal')
                ->whereYear('tanggal_retur', $tanggal[2])
                ->whereMonth('tanggal_retur', $tanggal[1])
                ->max('no_retur');

        if($data == 0){
            $data = substr($tanggal[2], -2).$tanggal[1].sprintf("%03d", 0);
        }
        $result = [
                "no_retur"=>$data+1,
                "sufix_no_faktur"=>"/RTPA",
            ];

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

    public function deletePiutangAwal($id = null)
    {
        DB::beginTransaction();
        try{
            $rtp = DB::table('retur_tagihan_piutangawal')->where('id', $id);

            if ($rtp->count() > 0) {
                $rtp->delete();
                DB::commit();
                return response()->json(['status'=>"Success"]);
            } else {
                return response()->json(['status'=>"Failed"]);
            }
        }catch(Exception $e){
            DB::rollback();
            return response()->json(['status'=>"Failed"]);
        }
    }

    // ====================================================

    public function returPiutangAwal($id_piutangawal = null)
    {
        $check = DB::table('view_penjualan2 as pj')
                ->where('pj.id_penjualan', $id_piutangawal)
                ->where('pj.kode_sebagai', 1)
                ->count();

        if ($check > 0) {
            $data['lokasi'] = DB::table("ms_lokasi_produksi")->get();
            $data['mata_uang'] = DB::table('ms_matauang')->get();
            $akun = DB::table("ms_perkiraan")->whereIn("parent", [3, 7])->orderByRaw("kd_rek_1, kode_perkiraan")->get();
            $data['akun'] = array_reduce($akun->toArray(), function($carry, $item){
                $carry[ $item->id ] = format_akun_perkiraan($item);
                return $carry;
            });
            $data['data'] = DB::table('view_penjualan2 as pj')
                            ->leftjoin('retur_tagihan_piutangawal as rtp', 'rtp.id_piutangawal', 'pj.id_penjualan')
                            ->where('pj.id_penjualan', $id_piutangawal)
                            ->where('pj.kode_sebagai', 1)
                            ->selectRaw('pj.*, (case when rtp.nominal_retur is null then 0 else sum(rtp.nominal_retur) end) AS nominal_retur')
                            ->groupBy('pj.id_penjualan')
                            ->first();

            $total_bayar = $data['data']->total_bayar-$data['data']->nominal_retur;
            $tagihan = $data['data']->nominal_netto;

            $data['sisa'] = $total_bayar - $tagihan;

            return view('keuangan.returtagihan.returPiutangAwalCreate', $data);
        } else {
            return redirect()->back()->with('flash_message_error', 'Data tidak ditemukan!');
        }
    }

    public function createPiutangAwal(Request $request)
    {
        DB::beginTransaction();
        try{
            $d_data = array(
                'lokasi'            => $request->get('lokasi'),
                'user_id_create'    => Session::get('kode_user'),
                'user_id_update'    => Session::get('kode_user'),
                'cv'                => $request->get('cv'),
                'no_retur'          => $request->get('no_retur'),
                'sufix_no_retur'    => $request->get('sufix_no_retur'),
                'tanggal'           => date('Y-m-d'),
                'tanggal_retur'     => date_db($request->get('tanggal_retur')),
                'tanggal_full'      => date('Y-m-d H:i:s'),
                'id_pelanggan'      => $request->get('id_pelanggan'),
                'id_piutangawal'    => $request->get('id_piutangawal'),
                'mata_uang'         => $request->get('currency'),
                'kurs'              => $request->get('kurs'),
                'nominal_retur'     => $request->get('nominal') == "" ? 0 : $request->get('nominal'),
                'keterangan'        => $request->get('keterangan'),
                'id_akun'           => $request->get('id_akun'),
                'tipe_id_akun'      => "K"
            );

            DB::table('retur_tagihan_piutangawal')->insert($d_data);
            DB::commit();
            return redirect('piutangawal')->with('flash_message_success', 'Data berhasil disimpan!');
        }catch(Exception $e){
            DB::rollback();
            return redirect('piutangawal')->with('flash_message_error', 'Data gagal disimpan!');
        }
    }
}
