All Kent Code Updated

app/Http/Controllers/Admin/AdminReportController.php

@@ -3,7 +3,6 @@
 namespace App\Http\Controllers\Admin;
 
 use App\Http\Controllers\Controller;
-use App\Models\Order;
 use Illuminate\Http\Request;
 use Illuminate\Support\Facades\DB;
 
@@ -12,45 +11,98 @@ 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
-        // -------------------------------
+    // 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(
-                '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',
-
+                // INVOICE
+                'invoices.id as invoicepk',
                 'invoices.invoice_number',
                 'invoices.invoice_date',
                 'invoices.final_amount',
-                'invoices.status as invoice_status',
-
-                'mark_list.company_name',
-                'mark_list.customer_name'
+                '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')
 
-            ->orderBy('shipments.shipment_date', 'desc')
             ->get();
-
+    
         return view('admin.reports', compact('reports'));
     }
+    
 }