Viewing File: /home/assersoft/public_html/nationallab/app/Controllers/DashboardController.php

<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use CodeIgniter\HTTP\ResponseInterface;

class DashboardController extends BaseController
{
    public function summary()
    {
        $db = \Config\Database::connect();
        $query1 = $db->query("select
            (select count(*) from users) as total_users,
            (select count(*) from tests) as total_tests,
            (select count(*) from patients) as total_patients,
            (select count(*) from invoices) as total_invoices,
            (select count(*) from reports) as total_reports
        ");
        $counts = $query1->getRowArray();

        $query2 = $db->query("
            select invoices.lab_number,
                patients.name AS patient_name,
                tests.name AS test_name
            from invoices
            join patients on invoices.patient_id = patients.id
            join invoice_tests on invoices.id = invoice_tests.invoice_id
            join tests on invoice_tests.test_id = tests.id
            where invoice_tests.status = 'Pending'
            order by invoices.lab_number DESC
            limit 25;
        ");
        $pending_reports = $query2->getResultArray();

        $data = [
            'counts' => $counts,
            'pending_reports' => $pending_reports
        ];

        return $this->response->setJSON($data);
    }
}