<?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
]);
}
}