<?php

namespace App\Http\Controllers\laporan;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use DB;
use Excel;
use Illuminate\Http\Request;
use App\LokasiProduksi;
use App\Perkiraan;
use Illuminate\Support\Facades\Session;
use Illuminate\Support\Collection;
use Yajra\Datatables\Datatables;

class LaporanController extends Controller{

	public function __construct(){    	
    	$this->middleware(function ($request, $next) {
    	$url = request()->segment(1);
    	$kode_group = Session::get('kode_group');
    	$d_menu = DB::table("tb_menu as a")->join("tb_role as b","a.kode_menu","b.kode_menu")->where([['a.link_menu',$url],['b.kode_group',$kode_group],['b.status_role','1'],['a.active','1']])->orderBy('a.sort_menu','asc')->count();
    	if(Session::get('decode') <> 1)
         {
         	Session::flash('message', 'Silahkan login dengan username dan password anda.!!');
             return redirect()->to('login');
         }
         if($d_menu == 0){
         	Session::flush();
         	Session::flash('message', 'Anda Tidak Punya Akses ke Halaman ini.!!');
             return redirect()->to('login');
         }
        return $next($request);	
        });
    }

	function index(){
		return view('laporan.LaporanList2');
	}

	function index2(){
		return view('laporan.LaporanList');
	}

	function jurnalUmum(Request $request){
		$input = $request->all();
		$data['jurnal_umum'] = [];
		// $data['button_excel']='';
		// if(sizeof($input) > 0){
		// 	$tanggal_awal = implode("-", array_reverse(explode("-", $input['tanggal_awal'])));
		// 	$tanggal_akhir = implode("-", array_reverse(explode("-", $input['tanggal_akhir'])));
		// 	$data['jurnal_umum'] = $this->_set_data_jurnal($tanggal_awal, $tanggal_akhir);
		// 	$data['button_excel'] = url("laporan/jurnal_umum/excel?tanggal_awal=".$input['tanggal_awal']."&tanggal_akhir=".$input['tanggal_akhir']);
		// }
		// dd($data['jurnal_umum']);
		// return view('laporan.LaporanJurnalUmum', $data);
		return view('laporan.LaporanJurnalUmum2', $data);
	}

	function jurnalUmumPdf(Request $request){
		$input = $request->all();
		$data['jurnal_umum'] = [];
		if(sizeof($input) > 0){
			$tanggal_awal = date_db($input['tanggal_awal'], false, 2);
			$tanggal_akhir = date_db($input['tanggal_akhir'], false, 2);
			$data['jurnal_umum'] = $this->_set_data_jurnal($tanggal_awal, $tanggal_akhir);
		}

		return view('laporan.LaporanJurnalUmum', $data);
	}

	function jurnalUmumExcel(Request $request) {
		$input = $request->all();
		$data['jurnal_umum'] = [];
		if(sizeof($input) > 0){
			// $tanggal_awal = implode("-", array_reverse(explode("-", $input['tanggal_awal'])));
			// $tanggal_akhir = implode("-", array_reverse(explode("-", $input['tanggal_akhir'])));
			$tanggal_awal = date_db($input['tanggal_awal'], false, 2);
			$tanggal_akhir = date_db($input['tanggal_akhir'], false, 2);
			$data['jurnal_umum'] = $this->_set_data_jurnal($tanggal_awal, $tanggal_akhir);
			$data['periode'] = $input['tanggal_awal'].' s/d '.$input['tanggal_akhir'];
			$page = 'LaporanJurnalUmumExcel2';

			Excel::create("Jurnal Umum", function($excel) use ($page, $data){
			    $excel->sheet("Sheet 1", function($sheet) use ($page, $data){
			        $sheet->loadView("laporan.".$page, $data);
			    });
			})->export("xlsx");
			// return view('laporan.'.$page, $data);
		} else {
			return redirect()->back();
		}
	}

	public function get_data_jurnal(Request $request)
	{
		$input = $request->all();
		$data = [];
		if(sizeof($input) > 0 && isset($input['tanggal_awal'])){
			$tanggal_awal = date_db($input['tanggal_awal'], false, 2);
			$tanggal_akhir = date_db($input['tanggal_akhir'], false, 2);
			$data = $this->_set_data_jurnal($tanggal_awal, $tanggal_akhir);
		}

		$data = new Collection($data);
		return Datatables::of($data)
			->addColumn('tanggal', function ($data) {
                return date('d-m-Y', strtotime($data['tanggal']));
            })
			->make(true);
	}

	private function _set_data_jurnal($tanggal_awal, $tanggal_akhir)
	{
		// $jurnal_umum = DB::table("jurnal_umum_1")->orderByRaw("tanggal_ref, id_ref, rows, id_perkiraan")->whereBetween("tanggal_ref", [$tanggal_awal, $tanggal_akhir])->get();

		// $jurnal_umum = DB::table(DB::raw("(".jurnal_umum_1()->toSql().") AS ju"))->orderByRaw("ju.tanggal_ref, ju.id_ref, ju.rows, ju.id_perkiraan")->whereBetween("ju.tanggal_ref", [$tanggal_awal, $tanggal_akhir])->get();

		// ini dari helper => opsi 1
		$jurnal_umum = arr_jurnal_umum_1([$tanggal_awal, $tanggal_akhir]);

		// ini dari view table => opsi 2
		// $jurnal_umum = DB::table('jurnal_umum')->whereBetween('tanggal_ref', [$tanggal_awal, $tanggal_akhir])->get()->toArray();
		
		$data_jurnal_umum = array_reduce($jurnal_umum, function($carry, $item){
			if($item->nominal!=0){

				if(!isset($carry[ $item->keterangan_lain."|".$item->nomor_ref."|".$item->tanggal_ref ]['tanggal'])){
					$carry[ $item->keterangan_lain."|".$item->nomor_ref."|".$item->tanggal_ref ]['key'] = $item->nomor_ref;
					$carry[ $item->keterangan_lain."|".$item->nomor_ref."|".$item->tanggal_ref ]['tanggal'] = $item->tanggal_ref;
					$carry[ $item->keterangan_lain."|".$item->nomor_ref."|".$item->tanggal_ref ]['D'] = 0;
					$carry[ $item->keterangan_lain."|".$item->nomor_ref."|".$item->tanggal_ref ]['K'] = 0;
				}
				
				$carry[ $item->keterangan_lain."|".$item->nomor_ref."|".$item->tanggal_ref ][$item->tipe_transaksi] += $item->nominal;
				
				$carry[ $item->keterangan_lain."|".$item->nomor_ref."|".$item->tanggal_ref ]['detail'][] = $item;
				// $carry = $setCarry;
			}
			return $carry;
		});
	return $data_jurnal_umum;
	}

	function kas_harian(){
		return view('laporan.LaporanList');
	}

	/* Buku Besar */
	public function buku_besar($value='')
	{
		$data['akun_perkiraan'] = Perkiraan::where([ ['used','=', 1] ])
							->orderByRaw('kode_perkiraan, kd_rek_1')
							->get();

		return view('laporan.LaporanBukuBesar', $data);
	}

	public function get_data_buku_besar(Request $request)
	{
		$input = $request->all();
		$data = [];
		if(sizeof($input) > 0 && isset($input['tanggal_awal'])){
			$tanggal_awal = date_db($input['tanggal_awal'], false, 2);
			$tanggal_akhir = date_db($input['tanggal_akhir'], false, 2);
			if($input['id_akun']==0){
				$paramAkun = [];
			}else{
				$paramAkun = [ ['id_perkiraan', '=', $input['id_akun']] ];
			}

			$data = $this->set_data_buku_besar($input, $tanggal_awal, $tanggal_akhir, $paramAkun);
			// $data['jurnal_umum'] = arr_jurnal_umum_1([$tanggal_awal, $tanggal_akhir], $paramAkun);
		}
		// dd($data);
		$data = new Collection($data);
		return Datatables::of($data)
			->make(true);
	}

	function set_data_buku_besar($input, $tanggal_awal, $tanggal_akhir, $paramAkun)
	{
		$paramInputSaldoAwal	= $paramAkun;
		$paramInputSaldoAwal[]	= ['tanggal_ref', '<=', $tanggal_awal];
		$inputSaldoAwal 		= arr_saldo_awal([], $paramInputSaldoAwal);

		$paramSaldoAwal 	= $paramAkun;
		$paramSaldoAwal[] 	= ['tanggal_ref', '<', $tanggal_awal];
		$saldoAwal 			= arr_jurnal_umum_1([], array_merge($paramSaldoAwal));
		
		$saldoAwal = array_merge($inputSaldoAwal, $saldoAwal);
		$nominal_saldo = 0;
		$saldoAwalAkun = array_reduce($saldoAwal, function($carry, $item) use (&$nominal_saldo){
			if($item->tipe_transaksi==$item->saldo_normal){
				$nominal_saldo += $item->nominal;
			}else{
				$nominal_saldo -= $item->nominal;
			}
			$carry = [
					'tanggal_ref'	=> '',
					'keterangan'	=> "Saldo Awal",
					'debet'			=> 0,
					'kredit'		=> 0,
					'saldo'			=> $nominal_saldo,
				];
			return $carry;
		});

		if(sizeof($saldoAwalAkun) < 1){
			$saldoAwalAkun = [
					'tanggal_ref'	=> '',
					'keterangan'	=> "Saldo Awal",
					'debet'			=> 0,
					'kredit'		=> 0,
					'saldo'			=> 0,
				];
		}
		// dd($saldoAwalAkun);

		$q_akun = DB::table('ms_perkiraan');
		if(sizeof($paramAkun) > 0){
			$q_akun = $q_akun->where([ ['id', '=', $input['id_akun']] ]);
		}
		$akun = $q_akun->get();

		$data = array_reduce($akun->toArray(), function($carry, $item) use ($tanggal_awal, $tanggal_akhir, $paramAkun, $saldoAwalAkun){
			$item->saldo_awal = $saldoAwalAkun;
			$item->child = arr_jurnal_umum_1([$tanggal_awal, $tanggal_akhir], $paramAkun);
			$item->perkiraan = format_akun_perkiraan($item);
			$carry[] = $item;
			return $carry;
		});

		return $data;
	}

	function bukuBesarExcel(Request $request) {
		$input = $request->all();
		$data['buku_besar'] = [];
		if(sizeof($input) > 0){
			$tanggal_awal = implode("-", array_reverse(explode("-", $input['tanggal_awal'])));
			$tanggal_akhir = implode("-", array_reverse(explode("-", $input['tanggal_akhir'])));
			$data['buku_besar'] = $this->set_data_buku_besar($input, $tanggal_awal, $tanggal_akhir, [['id_perkiraan', '=', $input['id_akun']]]);
			$data['periode'] = $input['tanggal_awal'].' s/d '.$input['tanggal_akhir'];
			$page = 'LaporanBukuBesarExcel';
			Excel::create("Buku Besar", function($excel) use ($page, $data){
			    $excel->sheet("Sheet 1", function($sheet) use ($page, $data){
			        $sheet->loadView("laporan.".$page, $data);
			    });
			})->export("xlsx");
			// return view('laporan.'.$page, $data);
		} else {
			return redirect()->back();
		}
	}
	/* end : Buku Besar */

	/* Neraca Saldo */
	public function neraca_saldo(Request $request)
	{
		return view('laporan.LaporanNeracaSaldo');
	}

	public function get_data_neraca_saldo(Request $request)
	{
		$input = $request->all();
		$data = $this->_set_data_neraca_saldo($input);

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

	public function _set_data_neraca_saldo($input)
	{
		$tanggal_awal 	= date_db($input['tanggal_awal'], false, 2);
		$tanggal_akhir 	= date_db($input['tanggal_akhir'], false, 2);

		$akun = Perkiraan::orderByRaw("kd_rek_1, kode_perkiraan")->get();
		$data = array_reduce($akun->toArray(), function($carry, $item){
			$item['D'] = 0;
			$item['K'] = 0;
			$carry[$item['id']] = $item;
			return $carry;
		});

		$input_saldo_awal = arr_saldo_awal([], [ ['tanggal_ref', '<=', $tanggal_awal] ]);
		foreach ($input_saldo_awal as $key => $value) {
			if($value->saldo_normal == $value->tipe_transaksi){
				$data[$value->id_perkiraan][$value->saldo_normal] += $value->nominal;
			}else{
				$data[$value->id_perkiraan][$value->saldo_normal] -= $value->nominal;
			}
		}

		$saldo_awal = arr_jurnal_umum_1([], [ ['tanggal_ref', '<', $tanggal_awal] ]);
		foreach ($saldo_awal as $key => $value) {
			if($value->saldo_normal == $value->tipe_transaksi){
				$data[$value->id_perkiraan][$value->saldo_normal] += $value->nominal;
			}else{
				$data[$value->id_perkiraan][$value->saldo_normal] -= $value->nominal;
			}
		}

		$jurnal_umum = arr_jurnal_umum_1([$tanggal_awal, $tanggal_akhir]);
		foreach ($jurnal_umum as $key => $value) {
			if($value->saldo_normal == $value->tipe_transaksi){
				$data[$value->id_perkiraan][$value->saldo_normal] += $value->nominal;
			}else{
				$data[$value->id_perkiraan][$value->saldo_normal] -= $value->nominal;
			}
		}

		return $data;
	}

	public function neracaSaldoExcel(Request $request)
	{
		$input = $request->all();
		$data['neraca_saldo'] = [];
		if(sizeof($input) > 0){
			$data['neraca_saldo'] = $this->_set_data_neraca_saldo($input);
			$data['periode'] = $input['tanggal_awal'].' s/d '.$input['tanggal_akhir'];
			$page = 'LaporanNeracaSaldoExcel';
			Excel::create("Neraca Saldo", function($excel) use ($page, $data){
			    $excel->sheet("Sheet 1", function($sheet) use ($page, $data){
			        $sheet->loadView("laporan.".$page, $data);
			    });
			})->export("xlsx");
			// return view('laporan.'.$page, $data);
		} else {
			return redirect()->back();
		}
	}
	/* end : Neraca Saldo */

	/* Neraca */
	public function neracaOld(Request $request)
	{
		$input = $request->all();
		// $data = sizeof($input) > 0 ? $this->_set_data_neraca($input) : [];
		$data = [];
		// dd($data);
		return view('laporan.LaporanNeraca', $data);
	}

	public function neraca(Request $request)
	{
		return view('laporan.LaporanNeracaNew');
	}

	public function get_data_neraca(Request $request)
	{
		$input = $request->all();
		$data_neraca = $this->dataNeraca($input);
		$data = $this->setDataNeraca($data_neraca);

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

	public function getNeraca(Request $request)
	{
		$input = $request->all();
		// $data = $this->_set_data_neraca($input);
		$data = [];
		return response()->json($data);
	}

	public function neracaExcelOld(Request $request)
	{
		$input = $request->all();
		
		if(sizeof($input) > 0){
			$data = sizeof($input) > 0 ? $this->_set_data_neraca($input) : [];
			$data['periode'] = $input['tanggal_awal'].' s/d '.$input['tanggal_akhir'];
			$page = 'LaporanNeracaExcel';
			Excel::create("Neraca", function($excel) use ($page, $data){
			    $excel->sheet("Sheet 1", function($sheet) use ($page, $data){
			        $sheet->loadView("laporan.".$page, $data);
			    });
			})->export("xlsx");
		} else {
			return redirect()->back();
		}
		// return view('laporan.LaporanNeracaExcel', $data);
	}

	public function neracaExcel(Request $request)
	{
		$input = $request->all();
		
		if(sizeof($input) > 0){
			$data_neraca = $this->dataNeraca($input);
			$data['neraca'] = $this->setDataNeraca($data_neraca);
			$data['periode'] = $input['tanggal_awal'].' s/d '.$input['tanggal_akhir'];
			$page = 'LaporanNeracaExcelNew';
			Excel::create("Neraca", function($excel) use ($page, $data){
			    $excel->sheet("Sheet 1", function($sheet) use ($page, $data){
			        $sheet->loadView("laporan.".$page, $data);
			    });
			})->export("xlsx");
		} else {
			return redirect()->back();
		}
	}

	function saldoAwal($tanggal_awal, $id_perkiraan, $saldo_awal)
	{
		$input_saldo_awal = arr_saldo_awal([], [ ['tanggal_ref', '<=', $tanggal_awal] ]);
		$input_saldo = 0;
		if (count($input_saldo_awal) > 0) {
			foreach ($input_saldo_awal as $key => $value) {
				if ($value->id_perkiraan == $id_perkiraan) {
					if($value->saldo_normal == $value->tipe_transaksi){
						$input_saldo += $value->nominal;
					}else{
						$input_saldo -= $value->nominal;
					}
				}
			}
		}

		// $saldo_awal = arr_jurnal_umum_1([], [ ['tanggal_ref', '<', $tanggal_awal] ]);
		$saldo = 0;
		if (count($saldo_awal) > 0) {
			foreach ($saldo_awal as $key => $value) {
				if ($value->id_perkiraan == $id_perkiraan) {
					if($value->saldo_normal == $value->tipe_transaksi){
						$saldo += $value->nominal;
					}else{
						$saldo -= $value->nominal;
					}
				}
			}
		}
			
		return $input_saldo+$saldo;
	}

	public function setDataNeraca(Array $neraca)
	{
		$data = [];
		if (count($neraca) > 0) {
			foreach ($neraca as $key => $value) {
				$data[] = (object) array(
					'kode_perkiraan' 	=> $value['kode_perkiraan'],
					'nama_perkiraan'	=> $value['nama_perkiraan'],
					'saldo'				=> ($value['saldo_normal'] == "D") ? $value['D']-$value['K'] : $value['K']-$value['D']
				);
			}
		}
		return $data;
	}

	private function dataNeraca($input)
	{
		$tanggal_awal 	= date_db($input['tanggal_awal'], false, 2);
		$tanggal_akhir 	= date_db($input['tanggal_akhir'], false, 2);

		$akun = Perkiraan::orderByRaw("kd_rek_1, kode_perkiraan")->get();
		$data = array_reduce($akun->toArray(), function($carry, $item){
			$item['D'] = 0;
			$item['K'] = 0;
			$carry[$item['id']] = $item;
			return $carry;
		});

		$input_saldo_awal = arr_saldo_awal([], [ ['tanggal_ref', '<=', $tanggal_awal] ]);
		foreach ($input_saldo_awal as $key => $value) {
			if($value->saldo_normal == $value->tipe_transaksi){
				$data[$value->id_perkiraan][$value->saldo_normal] += $value->nominal;
			}else{
				$data[$value->id_perkiraan][$value->saldo_normal] -= $value->nominal;
			}
		}

		$saldo_awal = arr_jurnal_umum_1([], [ ['tanggal_ref', '<', $tanggal_awal] ]);
		foreach ($saldo_awal as $key => $value) {
			if($value->saldo_normal == $value->tipe_transaksi){
				$data[$value->id_perkiraan][$value->saldo_normal] += $value->nominal;
			}else{
				$data[$value->id_perkiraan][$value->saldo_normal] -= $value->nominal;
			}
		}

		$jurnal_umum = arr_jurnal_umum_1([$tanggal_awal, $tanggal_akhir]);
		foreach ($jurnal_umum as $key => $value) {
			if($value->saldo_normal == $value->tipe_transaksi){
				$data[$value->id_perkiraan][$value->saldo_normal] += $value->nominal;
			}else{
				$data[$value->id_perkiraan][$value->saldo_normal] -= $value->nominal;
			}
		}

		return $data;
	}

	public function _set_data_neraca_old($input)
	{
		// $input = $request->all();
		$tanggal_awal 	= date_db($input['tanggal_awal'], false, 2);
		$tanggal_akhir 	= date_db($input['tanggal_akhir'], false, 2);

		/* data transaksi akun dari jurnal umum */
		$arr_jurnal_umum = arr_jurnal_umum_1([$tanggal_awal, $tanggal_akhir]);
		// print_r($arr_jurnal_umum[0]->id_perkiraan); exit();
		$jurnal_umum = [];
		if (count($arr_jurnal_umum) > 0) {
			foreach ($arr_jurnal_umum as $key => $value) {
				if(!isset($jurnal_umum[$value->id_perkiraan])){
					$jurnal_umum[$value->id_perkiraan] = $value;
					$jurnal_umum[$value->id_perkiraan]->saldo = 0;

					if($value->tipe_transaksi == $value->saldo_normal){
						$jurnal_umum[$value->id_perkiraan]->saldo += $value->nominal;
					}else{
						$jurnal_umum[$value->id_perkiraan]->saldo -= $value->nominal;
					}

				}else{

					if($value->tipe_transaksi == $value->saldo_normal){
						$jurnal_umum[$value->id_perkiraan]->saldo += $value->nominal;
					}else{
						$jurnal_umum[$value->id_perkiraan]->saldo -= $value->nominal;
					}

				}
			}
		}

		$input_saldo_awal = arr_saldo_awal([], [ ['tanggal_ref', '<=', $tanggal_awal] ]);
		if (count($input_saldo_awal) > 0) {
			foreach ($input_saldo_awal as $key => $value) {
				if (isset($jurnal_umum[$value->id_perkiraan])) {
					if($value->saldo_normal == $value->tipe_transaksi){
						$jurnal_umum[$value->id_perkiraan]->saldo += $value->nominal;
					}else{
						$jurnal_umum[$value->id_perkiraan]->saldo -= $value->nominal;
					}
				}
			}
		}

		$saldo_awal = arr_jurnal_umum_1([], [ ['tanggal_ref', '<', $tanggal_awal] ]);
		if (count($saldo_awal) > 0) {
			foreach ($saldo_awal as $key => $value) {
				if (isset($jurnal_umum[$value->id_perkiraan])) {
					if($value->saldo_normal == $value->tipe_transaksi){
						$jurnal_umum[$value->id_perkiraan]->saldo += $value->nominal;
					}else{
						$jurnal_umum[$value->id_perkiraan]->saldo -= $value->nominal;
					}
				}
			}
		}

		$perkiraan = Perkiraan::orderByRaw("kd_rek_1, level, kode_perkiraan, parent");

		$qNeraca = clone $perkiraan;
		$akun_neraca = $qNeraca->whereIn('kd_rek_1', [1])->get();
		$akun_neraca_aktiva = array_reduce($akun_neraca->toArray(), function($carry, $item) use ($jurnal_umum, $tanggal_awal, $saldo_awal){
			$item['saldo'] = isset($jurnal_umum[$item['id']]) ? $jurnal_umum[$item['id']]->saldo : $this->saldoAwal($tanggal_awal, $item['id'], $saldo_awal);
			$carry[$item['id']] = $item;
			return $carry;
		});

		$qNeraca = clone $perkiraan;
		$akun_neraca = $qNeraca->whereIn('kd_rek_1', [2])->get();
		$akun_neraca_pasiva = array_reduce($akun_neraca->toArray(), function($carry, $item) use ($jurnal_umum, $tanggal_awal, $saldo_awal){
			$item['saldo'] = isset($jurnal_umum[$item['id']]) ? $jurnal_umum[$item['id']]->saldo : $this->saldoAwal($tanggal_awal, $item['id'], $saldo_awal);
			$carry[$item['id']] = $item;
			return $carry;
		});

		$qNeraca = clone $perkiraan;
		$akun_neraca = $qNeraca->whereIn('kd_rek_1', [3])->get();
		$akun_neraca_ekuitas = array_reduce($akun_neraca->toArray(), function($carry, $item) use ($jurnal_umum, $tanggal_awal, $saldo_awal){
			$item['saldo'] = isset($jurnal_umum[$item['id']]) ? $jurnal_umum[$item['id']]->saldo : $this->saldoAwal($tanggal_awal, $item['id'], $saldo_awal);
			$carry[$item['id']] = $item;
			return $carry;
		});

		// $data['akun_neraca_aktiva'] 	= $akun_neraca_aktiva;
		// $data['akun_neraca_pasiva'] 	= $akun_neraca_pasiva;
		// $data['akun_neraca_ekuitas'] 	= $akun_neraca_ekuitas;
		$data['periode'] 				= "Periode : ".$input['tanggal_awal']." s/d ".$input['tanggal_akhir'];

		$kas 					= [];
		$bank 					= [];
		$piutang 				= [];
		$piutang_pi 			= [];
		$persediaan 			= [];
		$uang_muka 				= [];
		$aktiva_tetap 			= [];
		$aktiva_tetap_lain 		= [];
		$aktiva_tidak_berwujud 	= [];
		if (count($akun_neraca_aktiva) > 0) {
			foreach ($akun_neraca_aktiva as $key => $value) {
				if ($value['parent'] == 3) {
					$kas[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_aktiva as $key => $value) {
				if ($value['parent'] == 7) {
					$bank[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_aktiva as $key => $value) {
				if ($value['parent'] == 20) {
					$piutang[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_aktiva as $key => $value) {
				if ($value['parent'] == 23) {
					$piutang_pi[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_aktiva as $key => $value) {
				if ($value['parent'] == 25) {
					$persediaan[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_aktiva as $key => $value) {
				if ($value['parent'] == 31) {
					$uang_muka[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_aktiva as $key => $value) {
				if ($value['parent'] == 39) {
					$aktiva_tetap[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_aktiva as $key => $value) {
				if ($value['parent'] == 49) {
					$aktiva_tetap_lain[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_aktiva as $key => $value) {
				if ($value['parent'] == 51) {
					$aktiva_tidak_berwujud[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}
		}

		$hutang_biaya 			= [];
		$uang_muka_penjualan 	= [];
		$hutang_usaha 			= [];
		$hutang_pajak 			= [];
		$hutang_ppn 			= [];
		$hutang_lain 			= [];
		$hutang_pi 				= [];
		$hutang_bank 			= [];
		$kewajiban_jp 			= [];
		if (count($akun_neraca_pasiva) > 0) {
			foreach ($akun_neraca_pasiva as $key => $value) {
				if ($value['parent'] == 73) {
					$hutang_biaya[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_pasiva as $key => $value) {
				if ($value['parent'] == 77) {
					$uang_muka_penjualan[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_pasiva as $key => $value) {
				if ($value['parent'] == 205) {
					$hutang_usaha[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_pasiva as $key => $value) {
				if ($value['parent'] == 63) {
					$hutang_pajak[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_pasiva as $key => $value) {
				if ($value['parent'] == 64) {
					$hutang_ppn[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_pasiva as $key => $value) {
				if ($value['parent'] == 60) {
					$hutang_lain[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_pasiva as $key => $value) {
				if ($value['parent'] == 61) {
					$hutang_pi[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_pasiva as $key => $value) {
				if ($value['parent'] == 59) {
					$hutang_bank[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}

			foreach ($akun_neraca_pasiva as $key => $value) {
				if ($value['parent'] == 79) {
					$kewajiban_jp[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}
		}			

		$ekuitas = [];
		if (count($akun_neraca_ekuitas) > 0) {
			foreach ($akun_neraca_ekuitas as $key => $value) {
				if ($value['parent'] == 83) {
					$ekuitas[] = array(
						'kode_perkiraan' 	=> $value['kode_perkiraan'],
						'nama_perkiraan' 	=> $value['nama_perkiraan'],
						'saldo_normal' 		=> $value['saldo_normal'],
						'saldo' 			=> $value['saldo']
					);
				} else {
					continue;
				}
			}
		}

		// Aktiva
		$data['kas'] 					= $kas;
		$data['bank'] 					= $bank;
		$data['piutang']				= $piutang;
		$data['piutang_pi']				= $piutang_pi;
		$data['persediaan']				= $persediaan;
		$data['uang_muka']				= $uang_muka;
		$data['aktiva_tetap']			= $aktiva_tetap;
		$data['aktiva_tetap_lain']		= $aktiva_tetap_lain;
		$data['aktiva_tidak_berwujud']	= $aktiva_tidak_berwujud;

		// Kewajiban
		$data['hutang_biaya'] 			= $hutang_biaya;
		$data['uang_muka_penjualan']	= $uang_muka_penjualan;
		$data['hutang_usaha'] 			= $hutang_usaha;
		$data['hutang_pajak'] 			= $hutang_pajak;
		$data['hutang_ppn'] 			= $hutang_ppn;
		$data['hutang_lain'] 			= $hutang_lain;
		$data['hutang_pi'] 				= $hutang_pi;
		$data['hutang_bank'] 			= $hutang_bank;
		$data['kewajiban_jp'] 			= $kewajiban_jp;

		// Ekuitas
		$data['ekuitas'] 				= $ekuitas;

		return $data;
	}

	function buildTree( $data, $pid = 0, $jurnal_umum) {
	    $op = array();
	    foreach( $data as $item ) {
	        if( $item['parent'] == $pid ) {
	            $op[$item['id']] = $item;
	            $op[$item['id']]['saldo'] = isset($jurnal_umum[$item['id']]) ? $jurnal_umum[$item['id']]->saldo : 0;

	            // using recursion
	            $children =  $this->buildTree( $data, $item['id'], $jurnal_umum );
	            if( $children ) {
	                $op[$item['id']]['children'] = $children;
	            }else{
	                $op[$item['id']]['children'] = [];	            	
	            }
	        }
	    }
	    return $op;
	}
	/* end : Neraca */

	/* Laba-Rugi */
	public function laba_rugi(Request $request)
	{
		$input = $request->all();

		if(sizeof($input) > 0){
			$laporan['pendapatan'] 	= $this->_set_laba_rugi_pendapatan($input);
			$laporan['hpp'] 		= $this->_set_laba_rugi_hpp($input);
			$laporan['beban'] 		= $this->_set_laba_rugi_beban($input);
		}else{
			$laporan['pendapatan'] 	= [];
			$laporan['hpp'] 		= [];
			$laporan['beban'] 		= [];
		}

		return view('laporan.LaporanLabaRugiNew', $laporan);
	}

	public function laba_rugi_excel(Request $request)
	{
		$input = $request->all();
		$data['periode'] = $input['tanggal_awal'].' s/d '.$input['tanggal_akhir'];

		if(sizeof($input) > 0){
			$data['pendapatan']	= $this->_set_laba_rugi_pendapatan($input);
			$data['hpp'] 		= $this->_set_laba_rugi_hpp($input);
			$data['beban'] 		= $this->_set_laba_rugi_beban($input);
			$page = 'LaporanLabaRugiExcelNew';
			Excel::create("Laba (Rugi)", function($excel) use ($page, $data){
			    $excel->sheet("Sheet 1", function($sheet) use ($page, $data){
			        $sheet->loadView("laporan.".$page, $data);
			    });
			})->export("xlsx");
		} else {
			return redirect()->back();
		}
	}

	function _set_laba_rugi_pendapatanOLD($input)
	{
		/* Pendapatan */
		// $qParent = Perkiraan::where([ ['kd_rek_1', '=', 4], ['used', '=', 0] ])->get();
		$qParent = Perkiraan::where([ ['kd_rek_1', '=', 4] ])->get();
		$parent = array_reduce($qParent->toArray(), function($carry, $item){
			$item['child'] = [];
			$carry[$item['id']] = $item;
			return $carry;
		});

		// $akun_pendapatan = Perkiraan::where([ ['kd_rek_1', '=', 4], ['used', '<>', '0'] ])->get();
		$akun_pendapatan = Perkiraan::where([ ['kd_rek_1', '=', 4] ])->get();
		if(sizeof($akun_pendapatan) > 0){
			foreach ($akun_pendapatan->toArray() as $key => $value) {
				$value['total_saldo'] = 0;
				$parent[$value['parent']]['child'][$value['id']] = $value;
			}
		}

		$jurnal_pendapatan = $this->_set_laba_rugi($input, [ ['kd_rek_1', '=', 4] ]);
		// dd($jurnal_pendapatan);

		if(sizeof($jurnal_pendapatan) > 0){
			foreach ($jurnal_pendapatan as $key => $value) {
				$arr_temp = $parent[$value->parent]['child'][$value->id_perkiraan];
				if($value->tipe_transaksi == $arr_temp['saldo_normal']){
					$parent[$value->parent]['child'][$value->id_perkiraan]['total_saldo'] += $value->nominal;

				}else{
					$parent[$value->parent]['child'][$value->id_perkiraan]['total_saldo'] -= $value->nominal;

				}
			}
		}
		/* end : Pendapatan */

		return $parent;
	}

	function _set_laba_rugi_pendapatan($input)
	{
		/* Pendapatan */

		$tanggal_awal 	= date_db($input['tanggal_awal'], false, 2);
		$tanggal_akhir 	= date_db($input['tanggal_akhir'], false, 2);

		$akun = Perkiraan::where('kd_rek_1', 4)->orderByRaw("kode_perkiraan")->get();
		$data = array_reduce($akun->toArray(), function($carry, $item){
			$item['total_saldo'] = 0;
			$carry[$item['id']] = $item;
			return $carry;
		});

		$input_saldo_awal = arr_saldo_awal([], [ ['tanggal_ref', '<=', $tanggal_awal], ['kd_rek_1', '=', 4] ]);
		foreach ($input_saldo_awal as $key => $value) {
			if($data[$value->id_perkiraan]['saldo_normal'] == $value->tipe_transaksi){
				$data[$value->id_perkiraan]['total_saldo'] += $value->nominal;
			}else{
				$data[$value->id_perkiraan]['total_saldo'] -= $value->nominal;
			}
		}

		$saldo_awal = arr_jurnal_umum_1([], [ ['tanggal_ref', '<', $tanggal_awal], ['kd_rek_1', '=', 4] ]);
		foreach ($saldo_awal as $key => $value) {
			if($data[$value->id_perkiraan]['saldo_normal'] == $value->tipe_transaksi){
				$data[$value->id_perkiraan]['total_saldo'] += $value->nominal;
			}else{
				$data[$value->id_perkiraan]['total_saldo'] -= $value->nominal;
			}
		}

		$jurnal_umum = arr_jurnal_umum_1([$tanggal_awal, $tanggal_akhir], [ ['kd_rek_1', '=', 4] ]);
		foreach ($jurnal_umum as $key => $value) {
			if($data[$value->id_perkiraan]['saldo_normal'] == $value->tipe_transaksi){
				$data[$value->id_perkiraan]['total_saldo'] += $value->nominal;
			}else{
				$data[$value->id_perkiraan]['total_saldo'] -= $value->nominal;
			}
		}

		return $data;
		/* end : Pendapatan */
	}

	function _set_laba_rugi_hppOLD($input)
	{
		/* HPP */
		// $qParent = Perkiraan::where([ ['kd_rek_1', '=', 5], ['used', '=', 0] ])->get();
		$qParent = Perkiraan::where([ ['kd_rek_1', '=', 5] ])->get();
		$parent = array_reduce($qParent->toArray(), function($carry, $item){
			$item['child'] = [];
			$carry[$item['id']] = $item;
			return $carry;
		});

		// $akun_beban = Perkiraan::where([ ['kd_rek_1', '=', 5], ['used', '<>', '0'] ])->get();
		$akun_beban = Perkiraan::where([ ['kd_rek_1', '=', 5] ])->get();
		if(sizeof($akun_beban) > 0){
			foreach ($akun_beban->toArray() as $key => $value) {
				$value['total_saldo'] = 0;
				$parent[$value['parent']]['child'][$value['id']] = $value;
			}
		}

		$jurnal_beban = $this->_set_laba_rugi($input, [ ['kd_rek_1', '=', 5] ]);
		// dd($jurnal_beban);

		if(sizeof($jurnal_beban) > 0){
			foreach ($jurnal_beban as $key => $value) {
				$arr_temp = $parent[$value->parent]['child'][$value->id_perkiraan];
				if($value->tipe_transaksi == $arr_temp['saldo_normal']){
					$parent[$value->parent]['child'][$value->id_perkiraan]['total_saldo'] += $value->nominal;

				}else{
					$parent[$value->parent]['child'][$value->id_perkiraan]['total_saldo'] -= $value->nominal;

				}
			}
		}
		/* end : HPP */

		return $parent;
	}

	function _set_laba_rugi_hpp($input)
	{
		/* HPP */
		$tanggal_awal 	= date_db($input['tanggal_awal'], false, 2);
		$tanggal_akhir 	= date_db($input['tanggal_akhir'], false, 2);

		$akun = Perkiraan::where('kd_rek_1', 5)->orderByRaw("kode_perkiraan")->get();
		$data = array_reduce($akun->toArray(), function($carry, $item){
			$item['total_saldo'] = 0;
			$carry[$item['id']] = $item;
			return $carry;
		});

		$input_saldo_awal = arr_saldo_awal([], [ ['tanggal_ref', '<=', $tanggal_awal], ['kd_rek_1', '=', 5] ]);
		foreach ($input_saldo_awal as $key => $value) {
			if($data[$value->id_perkiraan]['saldo_normal'] == $value->tipe_transaksi){
				$data[$value->id_perkiraan]['total_saldo'] += $value->nominal;
			}else{
				$data[$value->id_perkiraan]['total_saldo'] -= $value->nominal;
			}
		}

		$saldo_awal = arr_jurnal_umum_1([], [ ['tanggal_ref', '<', $tanggal_awal], ['kd_rek_1', '=', 5] ]);
		foreach ($saldo_awal as $key => $value) {
			if($data[$value->id_perkiraan]['saldo_normal'] == $value->tipe_transaksi){
				$data[$value->id_perkiraan]['total_saldo'] += $value->nominal;
			}else{
				$data[$value->id_perkiraan]['total_saldo'] -= $value->nominal;
			}
		}

		$jurnal_umum = arr_jurnal_umum_1([$tanggal_awal, $tanggal_akhir], [ ['kd_rek_1', '=', 5] ]);
		foreach ($jurnal_umum as $key => $value) {
			if($data[$value->id_perkiraan]['saldo_normal'] == $value->tipe_transaksi){
				$data[$value->id_perkiraan]['total_saldo'] += $value->nominal;
			}else{
				$data[$value->id_perkiraan]['total_saldo'] -= $value->nominal;
			}
		}

		return $data;
		/* end : HPP */
	}

	function _set_laba_rugi_bebanOLD($input)
	{
		/* Beban */
		// $qParent = Perkiraan::where([ ['kd_rek_1', '=', 6], ['used', '=', 0] ])->get();
		$qParent = Perkiraan::where([ ['kd_rek_1', '=', 6] ])->get();
		$parent = array_reduce($qParent->toArray(), function($carry, $item){
			$item['child'] = [];
			$carry[$item['id']] = $item;
			return $carry;
		});

		// $akun_beban = Perkiraan::where([ ['kd_rek_1', '=', 6], ['used', '<>', '0'] ])->get();
		$akun_beban = Perkiraan::where([ ['kd_rek_1', '=', 6] ])->get();
		if(sizeof($akun_beban) > 0){
			foreach ($akun_beban->toArray() as $key => $value) {
				$value['total_saldo'] = 0;
				$parent[$value['parent']]['child'][$value['id']] = $value;
			}
		}

		$jurnal_beban = $this->_set_laba_rugi($input, [ ['kd_rek_1', '=', 6] ]);
		// dd($jurnal_beban);

		if(sizeof($jurnal_beban) > 0){
			foreach ($jurnal_beban as $key => $value) {
				$arr_temp = $parent[$value->parent]['child'][$value->id_perkiraan];
				if($value->tipe_transaksi == $arr_temp['saldo_normal']){
					$parent[$value->parent]['child'][$value->id_perkiraan]['total_saldo'] += $value->nominal;

				}else{
					$parent[$value->parent]['child'][$value->id_perkiraan]['total_saldo'] -= $value->nominal;

				}
			}
		}
		/* end : Beban */

		return $parent;
	}

	function _set_laba_rugi_beban($input)
	{
		/* Beban */
		$tanggal_awal 	= date_db($input['tanggal_awal'], false, 2);
		$tanggal_akhir 	= date_db($input['tanggal_akhir'], false, 2);

		$akun = Perkiraan::where('kd_rek_1', 6)->orderByRaw("kode_perkiraan")->get();
		$data = array_reduce($akun->toArray(), function($carry, $item){
			$item['total_saldo'] = 0;
			$carry[$item['id']] = $item;
			return $carry;
		});

		$input_saldo_awal = arr_saldo_awal([], [ ['tanggal_ref', '<=', $tanggal_awal], ['kd_rek_1', '=', 6] ]);
		foreach ($input_saldo_awal as $key => $value) {
			if($data[$value->id_perkiraan]['saldo_normal'] == $value->tipe_transaksi){
				$data[$value->id_perkiraan]['total_saldo'] += $value->nominal;
			}else{
				$data[$value->id_perkiraan]['total_saldo'] -= $value->nominal;
			}
		}

		$saldo_awal = arr_jurnal_umum_1([], [ ['tanggal_ref', '<', $tanggal_awal], ['kd_rek_1', '=', 6] ]);
		foreach ($saldo_awal as $key => $value) {
			if($data[$value->id_perkiraan]['saldo_normal'] == $value->tipe_transaksi){
				$data[$value->id_perkiraan]['total_saldo'] += $value->nominal;
			}else{
				$data[$value->id_perkiraan]['total_saldo'] -= $value->nominal;
			}
		}

		$jurnal_umum = arr_jurnal_umum_1([$tanggal_awal, $tanggal_akhir], [ ['kd_rek_1', '=', 6] ]);
		foreach ($jurnal_umum as $key => $value) {
			if($data[$value->id_perkiraan]['saldo_normal'] == $value->tipe_transaksi){
				$data[$value->id_perkiraan]['total_saldo'] += $value->nominal;
			}else{
				$data[$value->id_perkiraan]['total_saldo'] -= $value->nominal;
			}
		}

		return $data;
		/* end : Beban */
	}

	function _set_laba_rugi($input, $paramJurnal)
	{
		$tanggal_awal	= date_db($input['tanggal_awal'], false, 2);
		$tanggal_akhir	= date_db($input['tanggal_akhir'], false, 2);

		$jurnal_umum = arr_jurnal_umum_1([$tanggal_awal, $tanggal_akhir], $paramJurnal);
		$data = $jurnal_umum;

		return $data;
	}
	/* end : Laba-Rugi */
}