57 lines
1.7 KiB
PHP
57 lines
1.7 KiB
PHP
|
<?php
|
||
|
|
|
||
|
|
namespace App\Http\Controllers\Admin;
|
||
|
|
|
||
|
|
use App\Http\Controllers\Controller;
|
||
|
|
use App\Models\Order;
|
||
|
|
use Illuminate\Http\Request;
|
||
|
|
use Illuminate\Support\Facades\DB;
|
||
|
|
|
||
|
|
class AdminReportController extends Controller
|
||
|
|
{
|
||
|
|
/**
|
||
|
|
* Display the reports page with joined data
|
||
|
|
*/
|
||
|
|
public function index(Request $request)
|
||
|
|
{
|
||
|
|
// -------------------------------
|
||
|
|
// FETCH REPORT DATA
|
||
|
|
// ONLY orders that have BOTH:
|
||
|
|
// 1. Invoice
|
||
|
|
// 2. Shipment
|
||
|
|
// -------------------------------
|
||
|
|
$reports = DB::table('orders')
|
||
|
|
->join('shipment_items', 'shipment_items.order_id', '=', 'orders.id')
|
||
|
|
->join('shipments', 'shipments.id', '=', 'shipment_items.shipment_id')
|
||
|
|
->join('invoices', 'invoices.order_id', '=', 'orders.id')
|
||
|
|
->leftJoin('mark_list', 'mark_list.mark_no', '=', 'orders.mark_no')
|
||
|
|
->leftJoin('users', 'users.customer_id', '=', 'mark_list.customer_id')
|
||
|
|
|
||
|
|
->select(
|
||
|
|
'orders.id as order_pk',
|
||
|
|
'orders.order_id',
|
||
|
|
'orders.mark_no',
|
||
|
|
'orders.origin',
|
||
|
|
'orders.destination',
|
||
|
|
|
||
|
|
'shipments.id as shipment_pk',
|
||
|
|
'shipments.shipment_id',
|
||
|
|
'shipments.status as shipment_status',
|
||
|
|
'shipments.shipment_date',
|
||
|
|
|
||
|
|
'invoices.invoice_number',
|
||
|
|
'invoices.invoice_date',
|
||
|
|
'invoices.final_amount',
|
||
|
|
'invoices.status as invoice_status',
|
||
|
|
|
||
|
|
'mark_list.company_name',
|
||
|
|
'mark_list.customer_name'
|
||
|
|
)
|
||
|
|
|
||
|
|
->orderBy('shipments.shipment_date', 'desc')
|
||
|
|
->get();
|
||
|
|
|
||
|
|
return view('admin.reports', compact('reports'));
|
||
|
|
}
|
||
|
|
}
|