public function getReport(Request $request) { $currentYear = CommonHelper::get_current_financial_year(); $type = $request->statement_for; $person_id = $request->person_id; $balanceDueBeforeCurrentYear = CommonHelper::customer_balance_due_before_date($person_id, $type, strtotime('01-01-' . $currentYear)); // Statement Codes $salesOrder = DB::table('sales_order')->where(['customer_type' => $type, 'customer_id' => $person_id])->select('id', 'date', DB::raw('SUM(total) as sum_total'), DB::raw('SUM(transport_cost) as sum_transport_cost'), DB::raw('SUM(labour_cost) as sum_labour_cost'), DB::raw('SUM(paid) as sum_paid'))->groupBy('date')->where('date', '>', strtotime('01-01-' . $currentYear))->get(); $arrangedSalesOrder = []; foreach ($salesOrder as $order) { $arrangedSalesOrder[$order->date]['Sales Order']['sum_total'] = $order->sum_total + $order->sum_transport_cost + $order->sum_labour_cost; $arrangedSalesOrder[$order->date]['Sales Order']['sum_paid'] = $order->sum_paid; $arrangedSalesOrder[$order->date]['Sales Order']['memo_no'] = $order->id; } $salesReturn = DB::table('sales_return')->where(['customer_type' => $type, 'customer_id' => $person_id])->select('id', 'date', DB::raw('SUM(total_amount) as sum_total_amount'), DB::raw('SUM(due_paid) as sum_due_paid'))->groupBy('date')->where('date', '>', strtotime('01-01-' . $currentYear))->get(); $arrangedSalesReturn = []; foreach ($salesReturn as $return) { $arrangedSalesReturn[$return->date]['Sales Return']['sum_total'] = $return->sum_total_amount; $arrangedSalesReturn[$return->date]['Sales Return']['sum_paid'] = $return->sum_due_paid; $arrangedSalesReturn[$return->date]['Sales Return']['memo_no'] = $return->id; } $defects = DB::table('defects')->where(['customer_type' => $type, 'customer_id' => $person_id])->select('id', 'date', DB::raw('SUM(cash) as sum_cash'), DB::raw('SUM(replacement) as sum_replacement'), DB::raw('SUM(total) as sum_total'), DB::raw('SUM(due) as sum_due'))->groupBy('date')->where('date', '>', strtotime('01-01-' . $currentYear))->get(); $arrangedDefects = []; foreach ($defects as $defect) { $arrangedDefects[$defect->date]['Defects']['sum_cash'] = $defect->sum_cash; $arrangedDefects[$defect->date]['Defects']['sum_replacement'] = $defect->sum_replacement; $arrangedDefects[$defect->date]['Defects']['sum_total'] = $defect->sum_total; $arrangedDefects[$defect->date]['Defects']['sum_due'] = $defect->sum_due; $arrangedDefects[$defect->date]['Defects']['memo_no'] = $defect->id; } $receivePayments = DB::table('payments')->where(['from_whom_type' => $type, 'from_whom' => $person_id])->select('id', 'voucher_no', 'date', DB::raw('SUM(amount) as sum_amount'), DB::raw('SUM(total_amount) as sum_total_amount'))->groupBy('date')->where('date', '>', strtotime('01-01-' . $currentYear))->get(); $arrangedReceivePayments = []; foreach ($receivePayments as $receivePayment) { $arrangedReceivePayments[$receivePayment->date]['Receive Payment']['sum_paid'] = $receivePayment->sum_amount; $arrangedReceivePayments[$receivePayment->date]['Receive Payment']['sum_total'] = $receivePayment->sum_total_amount; $arrangedReceivePayments[$receivePayment->date]['Receive Payment']['memo_no'] = $receivePayment->id; $arrangedReceivePayments[$receivePayment->date]['Receive Payment']['voucher_no'] = $receivePayment->id; } $makePayments = DB::table('payments')->where(['to_whom_type' => $type, 'to_whom' => $person_id])->select('id', 'voucher_no', 'date', DB::raw('SUM(amount) as sum_amount'), DB::raw('SUM(total_amount) as sum_total_amount'))->groupBy('date')->where('date', '>', strtotime('01-01-' . $currentYear))->get(); $arrangedMakePayments = []; foreach ($makePayments as $makePayment) { $arrangedMakePayments[$makePayment->date]['Make Payment']['sum_paid'] = $makePayment->sum_amount; $arrangedMakePayments[$makePayment->date]['Make Payment']['sum_total'] = $makePayment->sum_total_amount; $arrangedMakePayments[$makePayment->date]['Make Payment']['memo_no'] = $makePayment->id; $arrangedMakePayments[$makePayment->date]['Make Payment']['voucher_no'] = $makePayment->id; } $newArray = []; foreach ($arrangedSalesOrder as $key => $arr) { if (isset($arrangedSalesReturn[$key])) { $newArray[$key] = array_merge_recursive($arrangedSalesReturn[$key], $arr); } else { $newArray[$key] = $arr; } } $newArray2 = []; foreach ($newArray as $key => $arr) { if (isset($arrangedDefects[$key])) { $newArray2[$key] = array_merge_recursive($arrangedDefects[$key], $arr); } else { $newArray2[$key] = $arr; } } $newArray3 = []; foreach ($newArray2 as $key => $arr) { if (isset($arrangedReceivePayments[$key])) { $newArray3[$key] = array_merge_recursive($arrangedReceivePayments[$key], $arr); } else { $newArray3[$key] = $arr; } } $newArray4 = []; foreach ($newArray3 as $key => $arr) { if (isset($arrangedMakePayments[$key])) { $newArray4[$key] = array_merge_recursive($arrangedMakePayments[$key], $arr); } else { $newArray4[$key] = $arr; } } $statements = json_decode(json_encode($newArray4), true); $balanceDueBeforeCurrentYear = json_decode(json_encode($balanceDueBeforeCurrentYear), true); $ajaxView = view('reports.transactionStatements.view', compact('statements', 'balanceDueBeforeCurrentYear', 'type', 'person_id'))->render(); return response()->json($ajaxView); }