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;
use Maatwebsite\Excel\Facades\Excel;
use Mpdf\Mpdf;

class AdminReportController extends Controller
{
    // UI साठी main action
    public function containerReport(Request $request)
    {
        $reports = $this->buildContainerReportQuery($request)->get();

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

    // ही common query — filters accept करते
    protected function buildContainerReportQuery(Request $request = null)
    {
        $query = DB::table('invoices')
            ->join('containers', 'containers.id', '=', 'invoices.container_id')
            ->leftJoinSub(
                DB::table('invoice_installments')
                    ->select('invoice_id', DB::raw('COALESCE(SUM(amount), 0) as total_paid'))
                    ->groupBy('invoice_id'),
                'inst',
                'inst.invoice_id',
                '=',
                'invoices.id'
            )
            ->select(
                'containers.id as container_id',
                'containers.container_number',
                'containers.container_date',
                'containers.container_name',

                DB::raw('COUNT(DISTINCT invoices.mark_no) as total_mark_nos'),
                DB::raw('COUNT(DISTINCT invoices.customer_id) as total_customers'),
                DB::raw('COUNT(invoices.id) as total_invoices'),

                DB::raw('COALESCE(SUM(invoices.charge_groups_total), 0) as total_invoice_amount'),
                DB::raw('COALESCE(SUM(invoices.gst_amount), 0) as total_gst_amount'),
                DB::raw('COALESCE(SUM(invoices.grand_total_with_charges), 0) as total_payable'),
                DB::raw('COALESCE(SUM(inst.total_paid), 0) as total_paid'),
                DB::raw('GREATEST(0, COALESCE(SUM(invoices.grand_total_with_charges), 0) - COALESCE(SUM(inst.total_paid), 0)) as total_remaining'),

                DB::raw("
                    CASE
                        WHEN COUNT(invoices.id) > 0
                         AND SUM(CASE WHEN invoices.status != 'paid' THEN 1 ELSE 0 END) = 0
                            THEN 'paid'
                        WHEN SUM(CASE WHEN invoices.status = 'overdue' THEN 1 ELSE 0 END) > 0
                            THEN 'overdue'
                        WHEN SUM(CASE WHEN invoices.status = 'paying' THEN 1 ELSE 0 END) > 0
                            THEN 'paying'
                        ELSE 'pending'
                    END as container_status
                ")
            )
            ->groupBy(
                'containers.id',
                'containers.container_number',
                'containers.container_date',
                'containers.container_name'
            )
            ->orderBy('containers.container_date', 'desc')
            ->orderBy('containers.id', 'desc');

        // ── Filters ──────────────────────────────────────────────────────
        if ($request) {
            if ($request->filled('from_date')) {
                $query->whereDate('containers.container_date', '>=', $request->from_date);
            }

            if ($request->filled('to_date')) {
                $query->whereDate('containers.container_date', '<=', $request->to_date);
            }

            if ($request->filled('status')) {
                // container_status हे aggregate expression आहे,
                // त्यामुळे HAVING clause वापरतो
                $query->havingRaw("
                    CASE
                        WHEN COUNT(invoices.id) > 0
                         AND SUM(CASE WHEN invoices.status != 'paid' THEN 1 ELSE 0 END) = 0
                            THEN 'paid'
                        WHEN SUM(CASE WHEN invoices.status = 'overdue' THEN 1 ELSE 0 END) > 0
                            THEN 'overdue'
                        WHEN SUM(CASE WHEN invoices.status = 'paying' THEN 1 ELSE 0 END) > 0
                            THEN 'paying'
                        ELSE 'pending'
                    END = ?
                ", [$request->status]);
            }
        }

        return $query;
    }

    // ---- Excel export ----
    public function containerReportExcel(Request $request)
    {
        $reports = $this->buildContainerReportQuery($request)->get();

        $headings = [
            'Container No',
            'Container Date',
            'Total Mark Nos',
            'Total Customers',
            'Total Invoices',
            'Invoice Amount (Before GST)',
            'GST Amount',
            'Payable Amount (Incl. GST)',
            'Paid Amount',
            'Remaining Amount',
            'Container Status',
        ];

        $rows = $reports->map(function ($r) {
            return [
                $r->container_number,
                $r->container_date,
                $r->total_mark_nos,
                $r->total_customers,
                $r->total_invoices,
                $r->total_invoice_amount,
                $r->total_gst_amount,
                $r->total_payable,
                $r->total_paid,
                $r->total_remaining,
                $r->container_status,
            ];
        })->toArray();

        $export = new class($headings, $rows) implements
            \Maatwebsite\Excel\Concerns\FromArray,
            \Maatwebsite\Excel\Concerns\WithHeadings
        {
            private $headings;
            private $rows;

            public function __construct($headings, $rows)
            {
                $this->headings = $headings;
                $this->rows     = $rows;
            }

            public function array(): array
            {
                return $this->rows;
            }

            public function headings(): array
            {
                return $this->headings;
            }
        };

        return Excel::download(
            $export,
            'container-report-' . now()->format('Ymd-His') . '.xlsx'
        );
    }

    // ---- PDF export ----
    public function containerReportPdf(Request $request)
    {
        $reports = $this->buildContainerReportQuery($request)->get();

        $html = view('admin.reports', [
            'reports' => $reports,
            'isPdf'   => true,
        ])->render();

        $mpdf = new \Mpdf\Mpdf([
            'mode'          => 'utf-8',
            'format'        => 'A4-L',
            'default_font'  => 'dejavusans',
            'margin_top'    => 8,
            'margin_right'  => 8,
            'margin_bottom' => 10,
            'margin_left'   => 8,
        ]);

        $mpdf->SetHTMLHeader('');
        $mpdf->SetHTMLFooter('');

        $mpdf->WriteHTML($html);

        $fileName = 'container-report-' . now()->format('Ymd-His') . '.pdf';

        return response($mpdf->Output($fileName, 'S'), 200, [
            'Content-Type'        => 'application/pdf',
            'Content-Disposition' => 'attachment; filename="' . $fileName . '"',
        ]);
    }
}