Kent-logistics-Laravel/app/Http/Controllers/Admin/AdminReportController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
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)
    // {
        /*********************************************************
         * OLD FLOW (Order + Shipment + Invoice)
         * फक्त reference साठी ठेवलेला, वापरत नाही.
         *********************************************************/

        /*
        $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(...)
            ->orderBy('shipments.shipment_date', 'desc')
            ->get();
        */

        /*********************************************************
         * NEW FLOW (Container + Invoice + MarkList)
         *********************************************************/

    //     $reports = DB::table('invoices')
    //         ->join('containers', 'containers.id', '=', 'invoices.containerid')
    //         ->leftJoin('mark_list', 'mark_list.markno', '=', 'invoices.markno')
    //         ->select(
    //             'invoices.id as invoicepk',
    //             'invoices.invoicenumber',
    //             'invoices.invoicedate',
    //             'invoices.finalamount',
    //             'invoices.finalamountwithgst',
    //             'invoices.gstpercent',
    //             'invoices.gstamount',
    //             'invoices.status as invoicestatus',
    //             'invoices.markno',

    //             'containers.id as containerpk',
    //             'containers.containernumber',
    //             'containers.containerdate',
    //             'containers.containername',

    //             'mark_list.companyname',
    //             'mark_list.customername'
    //         )
    //         ->orderBy('containers.containerdate', 'desc')
    //         ->get();

    //     return view('admin.reports', compact('reports'));
    // }


    
    public function index(Request $request)
    {
        $reports = DB::table('invoices')
            ->join('containers', 'containers.id', '=', 'invoices.container_id')
            ->leftJoin('mark_list', 'mark_list.mark_no', '=', 'invoices.mark_no')
            ->select(
                // INVOICE
                'invoices.id as invoicepk',
                'invoices.invoice_number',
                'invoices.invoice_date',
                'invoices.final_amount',
                'invoices.final_amount_with_gst',
                'invoices.gst_percent',
                'invoices.gst_amount',
                'invoices.status as invoicestatus',
                'invoices.mark_no',
    
                // CONTAINER
                'containers.id as containerpk',
                'containers.container_number',
                'containers.container_date',
                'containers.container_name',
    
                // RAW FIELDS (for reference/debug if needed)
                'invoices.company_name as inv_company_name',
                'invoices.customer_name as inv_customer_name',
                'mark_list.company_name as ml_company_name',
                'mark_list.customer_name as ml_customer_name',
    
                // FINAL FIELDS (automatically pick invoice first, else mark_list)
                DB::raw('COALESCE(invoices.company_name, mark_list.company_name) as company_name'),
                DB::raw('COALESCE(invoices.customer_name, mark_list.customer_name) as customer_name')
            )
            ->orderBy('invoices.invoice_date', 'desc')
            ->orderBy('invoices.id', 'desc')

            ->get();
    
        return view('admin.reports', compact('reports'));
    }
    
}