Gecko [ v3.cesa.co.za ]

Name	Size	Permission	Date
.htaccess	237 B	-r-xr-xr-x	2026-08-17 03:12
MarketingAttendeeManager.php	11.91 KB	-rwxrwxr-x	2026-04-02 11:48
SchoolAttendeeManager.php	10.2 KB	-rwxrwxr-x	2026-04-02 11:48
SchoolWeeksCoursesDashboardService.php	19 KB	-rw-r--r--	2026-08-14 12:33

Rename

<?php

namespace App\EventBundle\Services;

use Doctrine\DBAL\Connection;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * SCE Weeks/Courses dashboard rows — outer joins so missing related data never drops an event.
 */
final class SchoolWeeksCoursesDashboardService
{
    public const DEFAULT_PER_PAGE = 40;

/** @var list<int> */
    public const PER_PAGE_OPTIONS = [20, 40, 80, 160];

public const DEFAULT_SORT = 'StartDateReport';

public const DEFAULT_DIR = 'asc';

/**
     * Allow-listed sort keys => SQL ORDER BY expression.
     *
     * @var array<string, string>
     */
    public const SORT_COLUMNS = [
        'EventName' => 'se.EventName',
        'CourseType' => 'se.CourseType',
        'StartDateReport' => 'se.StartDate',
        'EndDateReport' => 'se.EndDate',
        'AttendeeCount' => 'AttendeeCount',
        'ProviderName' => 'tp.CompanyName',
        'PresenterName' => 'sp.PresenterName',
        'Category' => 'sc.Category',
        'Cancelled' => 'se.Cancelled',
        'CostPerPerson' => 'se.CostPerPerson',
        'RatePerDay' => 'tpr.RatePerDay',
        'Days' => 'Days',
        'TotalTPRate' => 'TotalTPRate',
        'TotalIncome' => 'TotalIncome',
        'Profit' => 'ProfitRaw',
        'Invoice' => 'se.TrainerInvRecv',
        'Venue' => 'se.Venue',
    ];

public function __construct(
        private readonly Connection $connection,
    ) {
    }

/**
     * Resolve filters: when both from and to are empty, apply default window
     * (today − 14 days … today + 4 months).
     *
     * @param array{from?: string|null, to?: string|null, q?: string|null, page?: int|string|null, per_page?: int|string|null, sort?: string|null, dir?: string|null} $filters
     * @return array{
     *     from: string,
     *     to: string,
     *     q: string|null,
     *     page: int,
     *     per_page: int,
     *     sort: string,
     *     dir: string,
     *     defaults_applied: bool
     * }
     */
    public function resolveFilters(array $filters = []): array
    {
        $from = $this->normalizeDateInput($filters['from'] ?? null);
        $to = $this->normalizeDateInput($filters['to'] ?? null);
        $q = isset($filters['q']) ? trim((string) $filters['q']) : '';
        $q = $q !== '' ? $q : null;

$defaultsApplied = false;
        if ($from === null && $to === null) {
            $today = new \DateTimeImmutable('today');
            $from = $today->modify('-14 days')->format('Y-m-d');
            $to = $today->modify('+4 months')->format('Y-m-d');
            $defaultsApplied = true;
        }

$perPage = (int) ($filters['per_page'] ?? self::DEFAULT_PER_PAGE);
        if (!in_array($perPage, self::PER_PAGE_OPTIONS, true)) {
            $perPage = self::DEFAULT_PER_PAGE;
        }

$page = max(1, (int) ($filters['page'] ?? 1));

$sort = trim((string) ($filters['sort'] ?? self::DEFAULT_SORT));
        if (!isset(self::SORT_COLUMNS[$sort])) {
            $sort = self::DEFAULT_SORT;
        }

$dir = strtolower(trim((string) ($filters['dir'] ?? self::DEFAULT_DIR)));
        if (!in_array($dir, ['asc', 'desc'], true)) {
            $dir = self::DEFAULT_DIR;
        }

return [
            'from' => (string) $from,
            'to' => (string) $to,
            'q' => $q,
            'page' => $page,
            'per_page' => $perPage,
            'sort' => $sort,
            'dir' => $dir,
            'defaults_applied' => $defaultsApplied,
        ];
    }

/**
     * @param array{from?: string|null, to?: string|null, q?: string|null, page?: int|string|null, per_page?: int|string|null, sort?: string|null, dir?: string|null} $filters
     * @return array{
     *     rows: list<array<string, mixed>>,
     *     total: int,
     *     page: int,
     *     per_page: int,
     *     pages: int,
     *     filters: array{from: string, to: string, q: string|null, page: int, per_page: int, sort: string, dir: string, defaults_applied: bool}
     * }
     */
    public function getDashboardPage(array $filters = []): array
    {
        $resolved = $this->resolveFilters($filters);
        [$whereSql, $params] = $this->buildWhere($resolved);

$countSql = 'SELECT COUNT(*) FROM SchoolEvents se'
            . ' LEFT JOIN SchoolCourses sc ON sc.SchoolCourseID = se.SchoolCourseID'
            . ' LEFT JOIN SchoolWebCategories swc ON swc.WebCategoryID = se.WebCategory'
            . ' WHERE ' . $whereSql;

$total = (int) $this->connection->fetchOne($countSql, $params);
        $pages = $total > 0 ? (int) ceil($total / $resolved['per_page']) : 1;
        if ($resolved['page'] > $pages) {
            $resolved['page'] = $pages;
        }

$offset = ($resolved['page'] - 1) * $resolved['per_page'];
        $sql = str_replace('/*WHERE*/', 'WHERE ' . $whereSql, $this->buildSql());
        $sql .= ' ' . $this->buildOrderBy($resolved['sort'], $resolved['dir']);
        $sql .= ' LIMIT ' . (int) $resolved['per_page'] . ' OFFSET ' . (int) $offset;

$rows = $this->connection->fetchAllAssociative($sql, $params);
        if (!is_array($rows)) {
            $rows = [];
        }

return [
            'rows' => array_map([$this, 'normalizeRow'], $rows),
            'total' => $total,
            'page' => $resolved['page'],
            'per_page' => $resolved['per_page'],
            'pages' => $pages,
            'filters' => $resolved,
        ];
    }

/**
     * All rows matching filters (no pagination) for Excel export.
     *
     * @param array{from?: string|null, to?: string|null, q?: string|null, sort?: string|null, dir?: string|null} $filters
     * @return list<array<string, mixed>>
     */
    public function getDashboardExportRows(array $filters = []): array
    {
        $resolved = $this->resolveFilters(array_merge($filters, [
            'page' => 1,
            'per_page' => self::DEFAULT_PER_PAGE,
        ]));
        [$whereSql, $params] = $this->buildWhere($resolved);

$sql = str_replace('/*WHERE*/', 'WHERE ' . $whereSql, $this->buildSql());
        $sql .= ' ' . $this->buildOrderBy($resolved['sort'], $resolved['dir']);

$rows = $this->connection->fetchAllAssociative($sql, $params);
        if (!is_array($rows)) {
            return [];
        }

return array_map([$this, 'normalizeRow'], $rows);
    }

/**
     * @param array{from?: string|null, to?: string|null, q?: string|null, sort?: string|null, dir?: string|null} $filters
     */
    public function buildExcelSpreadsheet(array $filters = []): Spreadsheet
    {
        $rows = $this->getDashboardExportRows($filters);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Weeks Courses');

$headers = [
            'Event Name',
            'Course Type',
            'Start Date',
            'End Date',
            'Del',
            'Provider',
            'Presenter',
            'Category',
            'X',
            'Cost / Person',
            'Rate / Day',
            'Days',
            'Total TP Rate',
            'Total Income',
            'Profit',
            'Invoice',
            'Venue',
        ];
        foreach ($headers as $col => $label) {
            $sheet->setCellValueByColumnAndRow($col + 1, 1, $label);
        }
        $sheet->getStyle('A1:Q1')->getFont()->setBold(true);

$r = 2;
        foreach ($rows as $row) {
            $sheet->setCellValueByColumnAndRow(1, $r, (string) ($row['EventName'] ?? ''));
            $sheet->setCellValueByColumnAndRow(2, $r, (string) ($row['CourseType'] ?? ''));
            $sheet->setCellValueByColumnAndRow(3, $r, (string) ($row['StartDateReport'] ?? ''));
            $sheet->setCellValueByColumnAndRow(4, $r, (string) ($row['EndDateReport'] ?? ''));
            $sheet->setCellValueByColumnAndRow(5, $r, (string) ($row['AttendeeCount'] ?? ''));
            $sheet->setCellValueByColumnAndRow(6, $r, (string) ($row['ProviderName'] ?? ''));
            $sheet->setCellValueByColumnAndRow(7, $r, (string) ($row['PresenterName'] ?? ''));
            $sheet->setCellValueByColumnAndRow(8, $r, (string) ($row['Category'] ?? ''));
            $sheet->setCellValueByColumnAndRow(9, $r, !empty($row['Cancelled']) ? 'Y' : 'N');
            $sheet->setCellValueByColumnAndRow(10, $r, (string) ($row['CostPerPerson'] ?? ''));
            $sheet->setCellValueByColumnAndRow(11, $r, (string) ($row['RatePerDay'] ?? ''));
            $sheet->setCellValueByColumnAndRow(12, $r, (string) ($row['Days'] ?? ''));
            $sheet->setCellValueByColumnAndRow(13, $r, (string) ($row['TotalTPRate'] ?? ''));
            $sheet->setCellValueByColumnAndRow(14, $r, (string) ($row['TotalIncome'] ?? ''));
            $sheet->setCellValueByColumnAndRow(15, $r, (string) ($row['Profit'] ?? ''));
            $sheet->setCellValueByColumnAndRow(16, $r, (string) ($row['Invoice'] ?? 'No'));
            $sheet->setCellValueByColumnAndRow(17, $r, (string) ($row['Venue'] ?? ''));
            ++$r;
        }

return $spreadsheet;
    }

/**
     * @param array{from?: string|null, to?: string|null, q?: string|null, sort?: string|null, dir?: string|null} $filters
     */
    public function buildExcelBinary(array $filters = []): string
    {
        $spreadsheet = $this->buildExcelSpreadsheet($filters);
        $writer = new Xlsx($spreadsheet);
        $tmp = tmpfile();
        if ($tmp === false) {
            throw new \RuntimeException('Could not create temporary file for Excel export.');
        }
        $meta = stream_get_meta_data($tmp);
        $path = $meta['uri'] ?? null;
        if (!is_string($path) || $path === '') {
            fclose($tmp);
            throw new \RuntimeException('Could not resolve temporary Excel path.');
        }
        $writer->save($path);
        rewind($tmp);
        $binary = stream_get_contents($tmp);
        fclose($tmp);
        if ($binary === false) {
            throw new \RuntimeException('Could not read Excel export binary.');
        }

return $binary;
    }

/**
     * @param array{from?: string|null, to?: string|null, q?: string|null} $filters
     * @return list<array<string, mixed>>
     */
    public function getDashboardRows(array $filters = []): array
    {
        return $this->getDashboardPage(array_merge($filters, [
            'page' => 1,
            'per_page' => self::DEFAULT_PER_PAGE,
        ]))['rows'];
    }

/**
     * Next sort direction when clicking a column header.
     */
    public function nextSortDir(string $currentSort, string $currentDir, string $clickedSort): string
    {
        if ($clickedSort === $currentSort && strtolower($currentDir) === 'asc') {
            return 'desc';
        }

return 'asc';
    }

/**
     * @param array{from: string, to: string, q: string|null} $filters
     * @return array{0: string, 1: array<string, mixed>}
     */
    private function buildWhere(array $filters): array
    {
        $where = ['1=1'];
        $params = [];

if ($filters['from'] !== '') {
            $where[] = 'se.StartDate >= :fromDate';
            $params['fromDate'] = $filters['from'] . ' 00:00:00';
        }
        if ($filters['to'] !== '') {
            $where[] = 'se.StartDate <= :toDate';
            $params['toDate'] = $filters['to'] . ' 23:59:59';
        }
        if (!empty($filters['q'])) {
            $where[] = '(se.EventName LIKE :q OR se.Venue LIKE :q OR sc.Category LIKE :q OR swc.WebCategoryName LIKE :q)';
            $params['q'] = '%' . $filters['q'] . '%';
        }

return [implode(' AND ', $where), $params];
    }

private function buildOrderBy(string $sort, string $dir): string
    {
        $expr = self::SORT_COLUMNS[$sort] ?? self::SORT_COLUMNS[self::DEFAULT_SORT];
        $dirSql = strtoupper($dir) === 'DESC' ? 'DESC' : 'ASC';

return 'ORDER BY ' . $expr . ' ' . $dirSql . ', se.EventID ASC';
    }

private function normalizeDateInput(mixed $value): ?string
    {
        if ($value === null) {
            return null;
        }
        $raw = trim((string) $value);
        if ($raw === '') {
            return null;
        }

$raw = str_replace('/', '-', $raw);
        $ts = strtotime($raw);
        if ($ts === false) {
            return null;
        }

return date('Y-m-d', $ts);
    }

private function buildSql(): string
    {
        // Days / TP / Profit use schedule-day counts only (no calendar DATEDIFF fallback).
        return <<<SQL
SELECT
    se.EventID AS EventID,
    se.EventName AS EventName,
    se.CourseType AS CourseType,
    se.CostPerPerson AS CostPerPerson,
    se.Venue AS Venue,
    se.StartDate AS StartDateReport,
    se.EndDate AS EndDateReport,
    se.TrainerInvRecv AS TrainerInvRecv,
    se.TrainerInvRecv AS TrainerInvRecvSE,
    se.Cancelled AS Cancelled,
    tp.CompanyName AS ProviderName,
    sp.PresenterName AS PresenterName,
    tpr.RateDesc AS RateDesc,
    tpr.RatePerDay AS RatePerDay,
    CASE
        WHEN tpr.RatePerDay IS NULL OR IFNULL(sch.ScheduleDays, 0) = 0 THEN NULL
        ELSE tpr.RatePerDay * sch.ScheduleDays
    END AS TotalTPRate,
    IFNULL(att.AttendeeCount, 0) AS AttendeeCount,
    CASE
        WHEN se.CostPerPerson IS NULL THEN NULL
        ELSE IFNULL(att.AttendeeCount, 0) * se.CostPerPerson
    END AS TotalIncome,
    CASE
        WHEN IFNULL(se.Cancelled, 0) = 1 OR IFNULL(att.AttendeeCount, 0) = 0 THEN NULL
        WHEN se.CostPerPerson IS NULL
            OR tpr.RatePerDay IS NULL
            OR IFNULL(sch.ScheduleDays, 0) = 0
        THEN NULL
        ELSE (IFNULL(att.AttendeeCount, 0) * se.CostPerPerson)
            - (tpr.RatePerDay * sch.ScheduleDays)
    END AS ProfitRaw,
    sch.ScheduleDays AS Days,
    sc.Category AS Category,
    swc.WebCategoryName AS WebCategoryName
FROM SchoolEvents se
LEFT JOIN SchoolCourses sc ON sc.SchoolCourseID = se.SchoolCourseID
LEFT JOIN SchoolWebCategories swc ON swc.WebCategoryID = se.WebCategory
LEFT JOIN SchoolPresenters sp ON sp.PresenterID = se.PresenterID
LEFT JOIN TrainingProviders tp ON tp.ProviderID = sc.TrainingProviderID
LEFT JOIN (
    SELECT EventID, COUNT(AttendeeID) AS AttendeeCount
    FROM SchoolAttendees
    WHERE Cancelled = 0 OR Cancelled IS NULL
    GROUP BY EventID
) att ON att.EventID = se.EventID
LEFT JOIN (
    SELECT EventID, COUNT(DISTINCT ScheduleDate) AS ScheduleDays
    FROM EventSchedules
    WHERE ScheduleDate IS NOT NULL
    GROUP BY EventID
) sch ON sch.EventID = se.EventID
LEFT JOIN (
    SELECT ProviderID, MIN(RateDesc) AS RateDesc, MIN(RatePerDay) AS RatePerDay
    FROM TrainingProviderRates
    WHERE RatePerDay IS NOT NULL
    GROUP BY ProviderID
) tpr ON tpr.ProviderID = sc.TrainingProviderID
/*WHERE*/
SQL;
    }

/**
     * @param array<string, mixed> $row
     * @return array<string, mixed>
     */
    private function normalizeRow(array $row): array
    {
        $display = static function ($value): string {
            if ($value === null || $value === '') {
                return '';
            }

return (string) $value;
        };

$money = static function ($value): string {
            if ($value === null || $value === '') {
                return '';
            }

return number_format((float) $value, 2, '.', ' ');
        };

$attendeeCount = isset($row['AttendeeCount']) ? (int) $row['AttendeeCount'] : 0;
        $cancelled = $this->isEventCancelled($row['Cancelled'] ?? null);
        $profitNotApplicable = $cancelled || $attendeeCount === 0;

$profitRaw = $row['ProfitRaw'] ?? $row['Profit'] ?? null;
        if (!$profitNotApplicable && ($profitRaw === null || $profitRaw === '')) {
            // Fallback if SQL omitted ProfitRaw but income/cost components are present.
            $income = $row['TotalIncome'] ?? null;
            $tpRate = $row['TotalTPRate'] ?? null;
            if ($income !== null && $income !== '' && $tpRate !== null && $tpRate !== '') {
                $profitRaw = (float) $income - (float) $tpRate;
            }
        }

$profitDisplay = $profitNotApplicable ? '-' : $money($profitRaw);
        $profitIsNegative = !$profitNotApplicable
            && $profitRaw !== null
            && $profitRaw !== ''
            && (float) $profitRaw < 0;

return [
            'EventID' => isset($row['EventID']) ? (int) $row['EventID'] : 0,
            'EventName' => $display($row['EventName'] ?? null),
            'CourseType' => $display($row['CourseType'] ?? null),
            'CostPerPerson' => $money($row['CostPerPerson'] ?? null),
            'Venue' => $display($row['Venue'] ?? null),
            'StartDateReport' => $this->formatDate($row['StartDateReport'] ?? null),
            'EndDateReport' => $this->formatDate($row['EndDateReport'] ?? null),
            'TrainerInvRecv' => $display($row['TrainerInvRecv'] ?? null),
            'TrainerInvRecvSE' => $display($row['TrainerInvRecvSE'] ?? null),
            'Invoice' => $this->formatYesNo($row['TrainerInvRecv'] ?? $row['TrainerInvRecvSE'] ?? null),
            'ProviderName' => $display($row['ProviderName'] ?? null),
            'PresenterName' => $display($row['PresenterName'] ?? null),
            'RateDesc' => $display($row['RateDesc'] ?? null),
            'RatePerDay' => $money($row['RatePerDay'] ?? null),
            'TotalTPRate' => $money($row['TotalTPRate'] ?? null),
            'AttendeeCount' => (string) $attendeeCount,
            'TotalIncome' => $money($row['TotalIncome'] ?? null),
            'Profit' => $profitDisplay,
            'ProfitIsNegative' => $profitIsNegative,
            'Days' => $display($row['Days'] ?? null),
            'Category' => $display($row['Category'] ?? null),
            'WebCategoryName' => $display($row['WebCategoryName'] ?? null),
            'Cancelled' => $cancelled,
        ];
    }

private function formatYesNo(mixed $value): string
    {
        if ($value === null || $value === '') {
            return 'No';
        }
        if (is_bool($value)) {
            return $value ? 'Yes' : 'No';
        }
        if (is_numeric($value)) {
            return ((int) $value) === 1 ? 'Yes' : 'No';
        }

$normalized = strtolower(trim((string) $value));
        if (in_array($normalized, ['1', 'yes', 'y', 'true'], true)) {
            return 'Yes';
        }
        if (in_array($normalized, ['0', 'no', 'n', 'false'], true)) {
            return 'No';
        }

return 'No';
    }

private function isEventCancelled(mixed $value): bool
    {
        if ($value === null || $value === '') {
            return false;
        }
        if (is_bool($value)) {
            return $value;
        }
        if (is_numeric($value)) {
            return (int) $value === 1;
        }

$normalized = strtolower(trim((string) $value));

return in_array($normalized, ['1', 'yes', 'y', 'true'], true);
    }

private function formatDate(mixed $value): string
    {
        if ($value === null || $value === '') {
            return '';
        }

if ($value instanceof \DateTimeInterface) {
            return $value->format('Y/m/d');
        }

$raw = (string) $value;
        $ts = strtotime($raw);
        if ($ts === false) {
            return $raw;
        }

return date('Y/m/d', $ts);
    }
}