Untitled

<?php

namespace App\Http\Controllers\Finance;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Style\Color;
use Symfony\Component\HttpFoundation\StreamedResponse;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Model\Journal;
use App\Model\JournalDetail;
use PDF;

ini_set('max_execution_time', 6000);

class CashFlowController extends Controller
{
    public function CashFlowList(){
        return view('Finance.cashflow');
    }

    private function setFontStyle($bold, $alignment, $size, $name) {

        $alignments = [Alignment::HORIZONTAL_LEFT, Alignment::HORIZONTAL_CENTER, Alignment::HORIZONTAL_RIGHT];

        $headerStyleArray = [
            'font' => [
                'bold' => $bold,
                'size' => $size,
                'name' => $name
            ],
            'alignment' => [
                'horizontal' => $alignments[$alignment],
            ],
        ];
        return $headerStyleArray;
    }

    private function setDefaults($sheet, $startDate, $endDate){
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(45);
        $sheet->getColumnDimension('C')->setWidth(25);


        $sheet->getStyle('A1:C3')->applyFromArray($this->setFontStyle(true, 1, 12, "Calibri"));

        $sheet->mergeCells('A1:C1');
        $sheet->mergeCells('A2:C2');
        $sheet->mergeCells('A3:C3');


        $sd = date_format(date_create($startDate), 'd M Y');
        $ed = date_format(date_create($endDate), 'd M Y');

        $sheet->setCellValue('A1', 'Metta Trans Perkasa');
        $sheet->setCellValue('A2', 'Laporan Arus Kas');
        $sheet->setCellValue('A3', 'Per '.$sd.' - '.$ed);
    }

    private function setBody($sheet, $incoming, $outgoing, $prev){
        $rowIndex = 7;
        $total_payments_from_customers = 0;
        $total_payments_to_suppliers = 0;
        $total_payments_to_employees = 0;
        $total_wheel_payments = 0;
        $total_oil_payments = 0;
        $total_battery_payments = 0;
        $total_water_bills = 0;
        $total_phone_internet_bills = 0;
        $total_electricity_payments = 0;
        $total_office_bills = 0;
        $total_misc_office_payments = 0;
        $total_insurance_costs = 0;
        $total_payments_for_administration = 0;
        $total_interest_costs = 0;
        $total_misc_payments = 0;
        $total_misc_income = 0;
        $total_wheel_maintenance_payments = 0;
        $total_electronic_money_payments = 0;
        $total_consumption_payments = 0;
        $operational_total = 0;

        $total_vehicular_payments = 0;
        $investing_total = 0;

        $total_capital_income = 0;
        $total_leasing_cost = 0;
        $financial_total = 0;

        foreach($incoming as $i) {
            if($i->reference_id == 3) {
                $total_payments_from_customers += $i->journal_value;
                $operational_total += $i->journal_value;
            }
            else if($i->reference_id == 52) {
                $total_payments_from_customers += $i->journal_value;
                $operational_total += $i->journal_value;
            }
            else if($i->reference_id == 15) {
                $total_vehicular_payments -= $i->journal_value;
                $investing_total += $i->journal_value;
            }
            else if($i->reference_id == 22) {
                $total_capital_income += $i->journal_value;
                $financial_total += $i->journal_value;
            }
            else {
                $isRevise = false;
                if($i->reference_id == 16) {
                    $total_payments_to_suppliers -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 31) {
                    $total_payments_to_employees -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 5) {
                    $total_wheel_payments -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 6) {
                    $total_battery_payments -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 7) {
                    $total_oil_payments -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 36) {
                    $total_water_bills -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 37) {
                    $total_electricity_payments -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 38) {
                    $total_phone_internet_bills -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 35) {
                    $total_office_bills -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 43) {
                    $total_misc_office_payments -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 63) {
                    $total_wheel_maintenance_payments -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 64) {
                    $total_electronic_money_payments -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 65) {
                    $total_consumption_payments -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif(($i->reference_id == 54) || ($i->reference_id == 44)) {
                    $total_payments_for_administration -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 10) {
                    $total_insurance_costs -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 9) {
                    $total_insurance_costs -= $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 15) {
                    $total_leasing_cost -= $i->journal_value;
                    $financial_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 11) {
                    $total_vehicular_payments +=  $i->journal_value;
                    $investing_total += $i->journal_value;
                    $isRevise = true;
                }
                elseif($i->reference_id == 47) {
                    $total_interest_costs +=  $i->journal_value;
                    $operational_total += $i->journal_value;
                    $isRevise = true;
                }
                if(!$isRevise) {
                    $total_misc_income += $i->journal_value;
                    $operational_total += $i->journal_value;
                }
            }

        }

        foreach($outgoing as $o) {
            if($o->reference_id == 16) {
                $total_payments_to_suppliers += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 31) {
                $total_payments_to_employees += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 5) {
                $total_wheel_payments += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 6) {
                $total_battery_payments += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 7) {
                $total_oil_payments += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 36) {
                $total_water_bills += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 37) {
                $total_electricity_payments += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 38) {
                $total_phone_internet_bills += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 35) {
                $total_office_bills += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 43) {
                $total_misc_office_payments += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 63) {
                $total_wheel_maintenance_payments += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 64) {
                $total_electronic_money_payments += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 65) {
                $total_consumption_payments += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif(($o->reference_id == 54) || ($o->reference_id == 44)) {
                $total_payments_for_administration += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 10) {
                $total_insurance_costs += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 9) {
                $total_insurance_costs += $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            elseif($o->reference_id == 15) {
                $total_leasing_cost += $o->journal_value;
                $financial_total -= $o->journal_value;
            }
            elseif($o->reference_id == 11) {
                $total_vehicular_payments +=  $o->journal_value;
                $investing_total -= $o->journal_value;
            }
            elseif($o->reference_id == 47) {
                $total_interest_costs +=  $o->journal_value;
                $operational_total -= $o->journal_value;
            }
            else {
                $isRevise = false;
                if($o->reference_id == 3) {
                    $total_payments_from_customers -= $o->journal_value;
                    $operational_total -= $o->journal_value;
                    $isRevise = true;
                }
                else if($o->reference_id == 52) {
                    $total_payments_from_customers -= $o->journal_value;
                    $operational_total -= $o->journal_value;
                    $isRevise = true;
                }
                else if($o->reference_id == 15) {
                    $total_vehicular_payments -= $o->journal_value;
                    $investing_total -= $o->journal_value;
                    $isRevise = true;
                }
                else if($o->reference_id == 22) {
                    $total_capital_income -= $o->journal_value;
                    $financial_total -= $o->journal_value;
                    $isRevise = true;
                }
                if(!$isRevise) {
                    $total_misc_payments += $o->journal_value;
                    $operational_total -= $o->journal_value;
                }
            }
        }
        $sheet->mergeCells('A6:C6');
        $sheet->getStyle('A6:C6')->applyFromArray($this->setFontStyle(false, 0, 12, "Calibri"));
        $sheet->getStyle('C7:C26')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle('B26')->applyFromArray($this->setFontStyle(true, 0, 11, "Calibri"));
        $sheet->getStyle('C26')->applyFromArray($this->setFontStyle(true, 2, 11, "Calibri"));

        $sheet->setCellValue('A6', 'Arus kas dari kegiatan operasi:');
        $sheet->setCellValue("B".$rowIndex, 'Kas diterima dari pelanggan');
        $sheet->setCellValue("C".$rowIndex, $total_payments_from_customers);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar ke suplier');
        $sheet->setCellValue("C".$rowIndex, $total_payments_to_suppliers);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk gaji');
        $sheet->setCellValue("C".$rowIndex, $total_payments_to_employees);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk pembelian ban');
        $sheet->setCellValue("C".$rowIndex, $total_wheel_payments);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk pembelian oli');
        $sheet->setCellValue("C".$rowIndex, $total_oil_payments);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk pembelian aki');
        $sheet->setCellValue("C".$rowIndex, $total_battery_payments);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk biaya air');
        $sheet->setCellValue("C".$rowIndex, $total_water_bills);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk biaya listrik');
        $sheet->setCellValue("C".$rowIndex, $total_electricity_payments);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk biaya telepon & internet');
        $sheet->setCellValue("C".$rowIndex, $total_phone_internet_bills);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk biaya perlengkapan kantor');
        $sheet->setCellValue("C".$rowIndex, $total_office_bills);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk biaya perlengkapan kantor lain-lain');
        $sheet->setCellValue("C".$rowIndex, $total_misc_office_payments);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk biaya reparasi ban');
        $sheet->setCellValue("C".$rowIndex, $total_wheel_maintenance_payments);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk biaya uang elektronik');
        $sheet->setCellValue("C".$rowIndex, $total_electronic_money_payments);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk biaya konsumsi');
        $sheet->setCellValue("C".$rowIndex, $total_consumption_payments);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk pembayaran asuransi');
        $sheet->setCellValue("C".$rowIndex, $total_insurance_costs);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk pembayaran administrasi');
        $sheet->setCellValue("C".$rowIndex, $total_payments_for_administration);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk pembayaran bunga');
        $sheet->setCellValue("C".$rowIndex, $total_interest_costs);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk kegiatan lain-lain');
        $sheet->setCellValue("C".$rowIndex, $total_misc_payments);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Kas masuk dari kegiatan lain-lain');
        $sheet->setCellValue("C".$rowIndex, $total_misc_income);
        $rowIndex++;

        $sheet->setCellValue("B".$rowIndex, 'Total arus kas dari kegiatan operasional');
        $sheet->setCellValue("C".$rowIndex, $operational_total);
        $rowIndex++;
        $rowIndex++;

        $sheet->mergeCells("A".$rowIndex.":C".$rowIndex);

        $sheet->getStyle("A".$rowIndex.":C".$rowIndex)->applyFromArray($this->setFontStyle(false, 0, 12, "Calibri"));
        $sheet->getStyle("C".($rowIndex+1).":C".($rowIndex+2))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle("B".($rowIndex+2))->applyFromArray($this->setFontStyle(true, 0, 11, "Calibri"));
        $sheet->getStyle("C".($rowIndex+2))->applyFromArray($this->setFontStyle(true, 2, 11, "Calibri"));


        $sheet->setCellValue("A".$rowIndex."", 'Arus kas dari kegiatan investasi:');
        $rowIndex++;
        $sheet->setCellValue("B".$rowIndex, 'Pembayaran uang muka pembelian aset tetap');
        $sheet->setCellValue("C".$rowIndex, $total_vehicular_payments);
        $rowIndex++;
        $sheet->setCellValue("B".$rowIndex, 'Total arus kas dari kegiatan investasi');
        $sheet->setCellValue("C".$rowIndex, $investing_total);
        $rowIndex++;
        $rowIndex++;

        $sheet->mergeCells("A".$rowIndex.":C".$rowIndex);
        $sheet->getStyle("A".$rowIndex.":C".$rowIndex)->applyFromArray($this->setFontStyle(false, 0, 12, "Calibri"));
        $sheet->getStyle("C".($rowIndex+1).":C".($rowIndex+3))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle("B".($rowIndex+3))->applyFromArray($this->setFontStyle(true, 0, 11, "Calibri"));
        $sheet->getStyle("C".($rowIndex+3))->applyFromArray($this->setFontStyle(true, 2, 11, "Calibri"));

        $sheet->setCellValue("A".$rowIndex."", 'Arus kas dari kegiatan pendanaan:');
        $rowIndex++;
        $sheet->setCellValue("B".$rowIndex, 'Kas masuk dari modal disetor');
        $sheet->setCellValue("C".$rowIndex, $total_capital_income);
        $rowIndex++;
        $sheet->setCellValue("B".$rowIndex, 'Kas keluar untuk pembayaran leasing');
        $sheet->setCellValue("C".$rowIndex, $total_leasing_cost);
        $rowIndex++;
        $sheet->setCellValue("B".$rowIndex, 'Total arus kas dari kegiatan pendanaan');
        $sheet->setCellValue("C".$rowIndex, $financial_total);
        $rowIndex++;
        $rowIndex++;



        $sheet->mergeCells("A".$rowIndex.":B".$rowIndex);
        $sheet->mergeCells("A".($rowIndex+1).":B".($rowIndex+1));
        $sheet->mergeCells("A".($rowIndex+2).":B".($rowIndex+2));
        $sheet->getStyle("A".($rowIndex).":A".($rowIndex+2))->applyFromArray($this->setFontStyle(false, 0, 12, "Calibri"));
        $sheet->getStyle("C".($rowIndex).":C".($rowIndex+2))->applyFromArray($this->setFontStyle(false, 2, 12, "Calibri"));
        $sheet->getStyle("C".($rowIndex).":C".($rowIndex+2))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);


        if($operational_total + $investing_total + $financial_total <= 0) {
            $sheet->getStyle("C".($rowIndex))->getFont()->getColor()->setARGB(Color::COLOR_RED);
        }
        else {
            $sheet->getStyle("C".($rowIndex))->getFont()->getColor()->setARGB(Color::COLOR_GREEN);
        }

        if($prev <= 0) {
            $sheet->getStyle("C".($rowIndex+1))->getFont()->getColor()->setARGB(Color::COLOR_RED);
        }
        else {
            $sheet->getStyle("C".($rowIndex+1))->getFont()->getColor()->setARGB(Color::COLOR_GREEN);
        }

        if($prev + $operational_total + $investing_total + $financial_total <= 0) {
            $sheet->getStyle("C".($rowIndex+2))->getFont()->getColor()->setARGB(Color::COLOR_RED);
        }
        else {
            $sheet->getStyle("C".($rowIndex+2))->getFont()->getColor()->setARGB(Color::COLOR_GREEN);
        }

        $sheet->setCellValue("A".($rowIndex), 'Penambahan Kas');
        $sheet->setCellValue("A".($rowIndex+1), 'Kas Awal');
        $sheet->setCellValue("A".($rowIndex+2), 'Kas Akhir');

        $sheet->setCellValue("C".($rowIndex), $operational_total + $investing_total + $financial_total);
        $sheet->setCellValue("C".($rowIndex+1), $prev);
        $sheet->setCellValue("C".($rowIndex+2), $prev + $operational_total + $investing_total + $financial_total);
    }

    public function CashFlowExcel($startDate, $endDate){

        $journals = Journal::join('journal_detail','journal_detail.journal_id','=','journal.journal_id')
                                ->whereBetween('journal.journal_date', [$startDate." 00:00:00", $endDate." 23:59:59"])
                                ->where(function ($query) {
                                    $query->where('journal_detail.reference_id', '1')
                                        ->orWhere('journal_detail.reference_id', '2');
                                })
                                ->get();

        $prevSumDebit = JournalDetail::join('journal','journal_detail.journal_id','=','journal.journal_id')
                                ->where('journal_type', '0')
                                ->where('journal_date', '<', $startDate)
                                ->where(function ($query) {
                                    $query->where('reference_id', '1')
                                        ->orWhere('reference_id', '2');
                                })
                                ->get()
                                ->sum('journal_value');

        $prevSumCredit = JournalDetail::join('journal','journal_detail.journal_id','=','journal.journal_id')
                                ->where('journal_type', '1')
                                ->where('journal_date', '<', $startDate)
                                ->where(function ($query) {
                                    $query->where('reference_id', '1')
                                    ->orWhere('reference_id', '2');
                                })
                                ->get()
                                ->sum('journal_value');

        $previous = $prevSumDebit-$prevSumCredit;

        $incoming = [];
        $outgoing = [];
        foreach($journals as $j) {
            foreach ($j->detail as $d) {

                if($d->reference_id != 1 && $d->reference_id != 2) {
                    if($d->journal_type == 0){
                        array_push($outgoing, $d);
                    }
                    else {
                        array_push($incoming, $d);
                    }
                }
            }
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $this->setDefaults($sheet, $startDate, $endDate);
        $this->setBody($sheet, $incoming, $outgoing, $previous);

        $writer = new Xlsx($spreadsheet);

        $response =  new StreamedResponse(
            function () use ($writer) {
                $writer->save('php://output');
            }
        );
        $start = date_format(date_create($startDate), 'd M Y');
        $end = date_format(date_create($endDate), 'd M Y');

        $response->headers->set('Content-Type', 'application/vnd.ms-excel');
        $response->headers->set('Content-Disposition', 'attachment;filename="Laporan Arus Kas Per '.$start.' - '. $end .'.xlsx"');
        $response->headers->set('Cache-Control','max-age=0');
        return $response;
    }

    public function CashFlowPDF($startDate, $endDate){

        $journals = Journal::join('journal_detail','journal_detail.journal_id','=','journal.journal_id')
                                ->whereBetween('journal.journal_date', [$startDate." 00:00:00", $endDate." 23:59:59"])
                                ->where(function ($query) {
                                    $query->where('journal_detail.reference_id', '1')
                                        ->orWhere('journal_detail.reference_id', '2');
                                })
                                ->get();

        $prevSumDebit = JournalDetail::join('journal','journal_detail.journal_id','=','journal.journal_id')
                                ->where('journal_type', '0')
                                ->where('journal_date', '<', $startDate)
                                ->where(function ($query) {
                                    $query->where('reference_id', '1')
                                        ->orWhere('reference_id', '2');
                                })
                                ->get()
                                ->sum('journal_value');

        $prevSumCredit = JournalDetail::join('journal','journal_detail.journal_id','=','journal.journal_id')
                                ->where('journal_type', '1')
                                ->where('journal_date', '<', $startDate)
                                ->where(function ($query) {
                                    $query->where('reference_id', '1')
                                    ->orWhere('reference_id', '2');
                                })
                                ->get()
                                ->sum('journal_value');

        $previous = $prevSumDebit-$prevSumCredit;

        $incoming = [];
        $outgoing = [];
        foreach($journals as $j) {
            // $is_cash = false;
            // $temp_detail = [];
            foreach ($j->detail as $d) {
                // if($d->reference->sub->minor->code == 1 && $d->reference->sub->minor->major->code == 1 && ($d->reference->sub->code == 1 || $d->reference->sub->code == 2)) {
                //     $is_cash = true;
                // }
                // else {

                if($d->reference_id != 1 && $d->reference_id != 2) {
                    if($d->journal_type == 0){
                        array_push($outgoing, $d);
                    }
                    else {
                        array_push($incoming, $d);
                    }
                }
                // }
            }
            // if($is_cash == true) {
            //     foreach($temp_detail as $td) {
            //         array_push($details, $td);
            //     }
            // }
        }

        // foreach($details as $d) {
        //     if($d->journal_type == 0){
        //         array_push($outgoing, $d);
        //     }
        //     else {
        //         array_push($incoming, $d);
        //     }
        // }

        $start = date_format(date_create($startDate), 'd M Y');
        $end = date_format(date_create($endDate), 'd M Y');
        $title = "Laporan Arus Kas " . $start . " - " . $end . ".pdf";

        $pdf = PDF::loadView('PDF.cashflowpdf', compact('incoming', 'outgoing', 'previous', 'start', 'end'))->setPaper('a4', 'portrait');
        return $pdf->download($title);
    }

    public function CashFlowDetail($startDate, $endDate){

        //EXPLAIN SELECT SUM(`journal_value`) FROM `journal_detail` JOIN `journal` ON `journal`.`journal_id` = `journal_detail`.`journal_id` WHERE `reference_id` = 1 AND `journal_type` = 0 AND `journal`.`journal_date` < '2020-12-12'
        // UNION
        // SELECT SUM(`journal_value`) FROM `journal_detail` JOIN `journal` ON `journal`.`journal_id` = `journal_detail`.`journal_id` WHERE `reference_id` = 1 AND `journal_type` = 1 AND `journal`.`journal_date` < '2020-12-12'

        $journals = Journal::join('journal_detail','journal_detail.journal_id','=','journal.journal_id')
                                ->whereBetween('journal.journal_date', [$startDate." 00:00:00", $endDate." 23:59:59"])
                                ->where(function ($query) {
                                    $query->where('journal_detail.reference_id', '1')
                                        ->orWhere('journal_detail.reference_id', '2');
                                })
                                ->get();

        $prevSumDebit = JournalDetail::join('journal','journal_detail.journal_id','=','journal.journal_id')
                                ->where('journal_type', '0')
                                ->where('journal_date', '<', $startDate)
                                ->where(function ($query) {
                                    $query->where('reference_id', '1')
                                        ->orWhere('reference_id', '2');
                                })
                                ->get()
                                ->sum('journal_value');

        $prevSumCredit = JournalDetail::join('journal','journal_detail.journal_id','=','journal.journal_id')
                                ->where('journal_type', '1')
                                ->where('journal_date', '<', $startDate)
                                ->where(function ($query) {
                                    $query->where('reference_id', '1')
                                    ->orWhere('reference_id', '2');
                                })
                                ->get()
                                ->sum('journal_value');

        $previous = $prevSumDebit-$prevSumCredit;

        $incoming = [];
        $outgoing = [];
        foreach($journals as $j) {
            // $is_cash = false;
            // $temp_detail = [];
            foreach ($j->detail as $d) {
                // if($d->reference->sub->minor->code == 1 && $d->reference->sub->minor->major->code == 1 && ($d->reference->sub->code == 1 || $d->reference->sub->code == 2)) {
                //     $is_cash = true;
                // }
                // else {

                if($d->reference_id != 1 && $d->reference_id != 2) {
                    if($d->journal_type == 0){
                        array_push($outgoing, $d);
                    }
                    else {
                        array_push($incoming, $d);
                    }
                }
                // }
            }
            // if($is_cash == true) {
            //     foreach($temp_detail as $td) {
            //         array_push($details, $td);
            //     }
            // }
        }

        // foreach($details as $d) {
        //     if($d->journal_type == 0){
        //         array_push($outgoing, $d);
        //     }
        //     else {
        //         array_push($incoming, $d);
        //     }
        // }

        return response()->json([
            'incoming' => $incoming,
            'outgoing' => $outgoing,
            'previous' => $previous
        ]);
    }

}