stagfe2/app/Http/Controllers/ManagementStatsController.php

<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;


class ManagementStatsController extends Controller
{

    public function index(Request $request)
    {
    
        $filters = $request->all();
        $clientsWePaidToAcquire = [
            ['date_trunc' => '2022-01-01 00:00:00', 'count' => 1445]
        ];
        $activeClients = [
            ['date_trunc' => '2022-01-01 00:00:00', 'count' => 1445]
        ];

        $startDate = '2022-01-01';
        $endDate = '2022-02-01';
        $resultMap = [
            "AcquiredClients" => $this->getAcquiredClients($startDate, $endDate),
            "AcquiredClientsCount" => $this->getAcquiredClientsCount($startDate, $endDate),
            "NoteCountByMonth" => $this->getNoteCountByMonth($startDate, $endDate),
            "ActiveClientsByMonth" => $this->getActiveClientsByMonth($startDate, $endDate),
            "ActiveProsByMonth" => $this->getActiveProsByMonth($startDate, $endDate),
            "CptCodesBilled" => $this->getCptCodesBilled($startDate, $endDate),
            "CptCodesBilledByMonth" => $this->getCptCodesBilledByMonth($startDate, $endDate),
            "TotalBilledAmount" => $this->getTotalBilledAmount($startDate, $endDate)
        ];

        
        return view('app.admin.management-stats.index', compact('clientsWePaidToAcquire', 'activeClients', 'filters'));
    }

    private function getAcquiredClients($startDate, $endDate){
        $query = "SELECT id FROM client WHERE created_at::DATE >= ? AND created_at::DATE < ? AND client_engagement_status_category != 'DUMMY'";
        return DB::select( DB::raw($query), array($startDate,$endDate));
    }

    private function getAcquiredClientsCount($startDate, $endDate){
        $query = "SELECT COUNT(*) FROM client WHERE id IN (SELECT id FROM client WHERE created_at::DATE >= ? AND created_at::DATE < ?) AND client_engagement_status_category != 'DUMMY'";
        return DB::select( DB::raw($query), array($startDate,$endDate));
    }

    private function getNoteCountByMonth($startDate, $endDate){
        $query= "
        SELECT COUNT(*), DATE_TRUNC('month', created_at)
		FROM note 
		WHERE (is_signed_by_hcp IS NOT FALSE AND is_bill_closed IS NOT FALSE AND is_claim_closed IS NOT FALSE AND is_cancelled IS NOT TRUE)
		AND client_id IN (SELECT id FROM client WHERE created_at::DATE >= ? AND created_at::DATE < ? AND client_engagement_status_category != 'DUMMY')
		GROUP BY 2
		ORDER BY 2 ASC;
        ";
        return DB::select( DB::raw($query), array($startDate,$endDate));
    }

    private function getActiveClientsByMonth($startDate, $endDate){
        $query= "
        SELECT COUNT(DISTINCT(client_id)), DATE_TRUNC('month', created_at)
		FROM note 
		WHERE (is_signed_by_hcp IS NOT FALSE AND is_bill_closed IS NOT FALSE AND is_claim_closed IS NOT FALSE AND is_cancelled IS NOT TRUE)
		AND client_id IN (SELECT id FROM client WHERE created_at::DATE >= ? AND created_at::DATE < ? AND client_engagement_status_category != 'DUMMY')
		GROUP BY 2
		ORDER BY 2 ASC;
        ";
        return DB::select( DB::raw($query), array($startDate,$endDate));
    }

    private function getActiveProsByMonth($startDate, $endDate){
        $query= "
        SELECT COUNT(DISTINCT(hcp_pro_id)), DATE_TRUNC('month', created_at)
        FROM note
        WHERE (is_signed_by_hcp IS NOT FALSE AND is_bill_closed IS NOT FALSE AND is_claim_closed IS NOT FALSE AND is_cancelled IS NOT TRUE)
        AND client_id IN (SELECT id FROM client WHERE created_at::DATE >= ? AND created_at::DATE < ? AND client_engagement_status_category != 'DUMMY')
        GROUP BY 2
        ORDER BY 2 ASC;
        ";
        return DB::select( DB::raw($query), array($startDate,$endDate));
    }

    private function getCptCodesBilled($startDate, $endDate){
        $query= "
        SELECT COUNT(*), cpt
		FROM claim_line
		JOIN claim ON claim_line.claim_id = claim.id
		JOIN note ON claim.note_id = note.id
		JOIN client ON note.client_id = client.id
		WHERE client.id IN (SELECT id FROM client WHERE created_at::DATE >= ? AND created_at::DATE < ? AND client_engagement_status_category != 'DUMMY')
		GROUP BY cpt ORDER BY cpt ASC;
        ";
        return DB::select( DB::raw($query), array($startDate,$endDate));
    }

    private function getCptCodesBilledByMonth($startDate, $endDate){
        $query= "
        SELECT COUNT(*), cpt, DATE_TRUNC('month', note.effective_dateest)
		FROM claim_line
		JOIN claim ON claim_line.claim_id = claim.id
		JOIN note ON claim.note_id = note.id
		JOIN client ON note.client_id = client.id
		WHERE client.id IN (SELECT id FROM client WHERE created_at::DATE >= ? AND created_at::DATE < ?  AND client_engagement_status_category != 'DUMMY')
		GROUP BY 3, 2;
        " ;
        return DB::select( DB::raw($query), array($startDate,$endDate));
    }

    private function getTotalBilledAmount($startDate, $endDate){
        $query= "
        SELECT SUM(bill.total_paid)
		FROM bill
		JOIN note ON bill.note_id = note.id
		JOIN client ON bill.client_id = client.id
		WHERE client.id IN (SELECT id FROM client WHERE created_at::DATE >= ? AND created_at::DATE < ?  AND client_engagement_status_category != 'DUMMY');
        ";
        return DB::select( DB::raw($query), array($startDate,$endDate));
    }
}