Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
0.00% |
0 / 428 |
|
0.00% |
0 / 17 |
CRAP | |
0.00% |
0 / 1 |
| EventReportController | |
0.00% |
0 / 428 |
|
0.00% |
0 / 17 |
5852 | |
0.00% |
0 / 1 |
| __construct | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
2 | |||
| checkReportAuth | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
20 | |||
| getFullReport | |
0.00% |
0 / 23 |
|
0.00% |
0 / 1 |
20 | |||
| getSalesReport | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
20 | |||
| getMarketingReport | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
20 | |||
| getInventoryReport | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
20 | |||
| getComebackCashReport | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
20 | |||
| exportReport | |
0.00% |
0 / 23 |
|
0.00% |
0 / 1 |
20 | |||
| getSalesMetrics | |
0.00% |
0 / 65 |
|
0.00% |
0 / 1 |
110 | |||
| getMarketingMetrics | |
0.00% |
0 / 60 |
|
0.00% |
0 / 1 |
132 | |||
| getInventoryMetrics | |
0.00% |
0 / 42 |
|
0.00% |
0 / 1 |
30 | |||
| getComebackCashMetrics | |
0.00% |
0 / 57 |
|
0.00% |
0 / 1 |
110 | |||
| generateCsvReport | |
0.00% |
0 / 66 |
|
0.00% |
0 / 1 |
30 | |||
| getEventById | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
6 | |||
| setJsonContentType | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| sendJsonResponse | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| sendErrorResponse | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
6 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace BuyerKiosk\EventManagement\Controllers; |
| 4 | |
| 5 | use Exception; |
| 6 | use PDO; |
| 7 | use DateTime; |
| 8 | use BuyerKiosk\EventManagement\Models\Event; |
| 9 | use BuyerKiosk\EventManagement\Services\EventService; |
| 10 | |
| 11 | /** |
| 12 | * EventReportController - REST API for Event Reports |
| 13 | * |
| 14 | * Provides endpoints for retrieving event performance reports including: |
| 15 | * |
| 16 | * Report Endpoints: |
| 17 | * 1. GET /api/:typeNum/events/:eventId/report - Full event report (all metrics) |
| 18 | * 2. GET /api/:typeNum/events/:eventId/report/sales - Sales metrics only |
| 19 | * 3. GET /api/:typeNum/events/:eventId/report/marketing - Marketing metrics only |
| 20 | * 4. GET /api/:typeNum/events/:eventId/report/inventory - Inventory metrics only |
| 21 | * 5. GET /api/:typeNum/events/:eventId/report/comebackcash - Comeback Cash metrics only |
| 22 | * 6. GET /api/:typeNum/events/:eventId/report/export - CSV export of full report |
| 23 | * |
| 24 | * Authentication: All endpoints require session authentication + uri_events_reports permission |
| 25 | * |
| 26 | * @package BuyerKiosk\EventManagement\Controllers |
| 27 | */ |
| 28 | class EventReportController |
| 29 | { |
| 30 | /** |
| 31 | * @var \Slim\Slim Slim application instance |
| 32 | */ |
| 33 | private $app; |
| 34 | |
| 35 | /** |
| 36 | * @var \Store Store object |
| 37 | */ |
| 38 | private $store; |
| 39 | |
| 40 | /** |
| 41 | * @var string Store type number |
| 42 | */ |
| 43 | private string $typeNum; |
| 44 | |
| 45 | /** |
| 46 | * @var PDO Database connection |
| 47 | */ |
| 48 | private PDO $db; |
| 49 | |
| 50 | /** |
| 51 | * Constructor |
| 52 | * |
| 53 | * @param \Slim\Slim $app Slim application instance |
| 54 | * @param \Store $store Store object (validated) |
| 55 | */ |
| 56 | public function __construct($app, \Store $store) |
| 57 | { |
| 58 | $this->app = $app; |
| 59 | $this->store = $store; |
| 60 | $this->typeNum = $store->getTypeNum(); |
| 61 | $this->db = dbConnectByName($store->getDbName()); |
| 62 | } |
| 63 | |
| 64 | // ========================================================================= |
| 65 | // AUTHENTICATION & PERMISSION CHECKS |
| 66 | // ========================================================================= |
| 67 | |
| 68 | /** |
| 69 | * Check session authentication and uri_events_reports permission |
| 70 | * |
| 71 | * @return bool True if authorized, false otherwise (response already sent) |
| 72 | */ |
| 73 | private function checkReportAuth(): bool |
| 74 | { |
| 75 | if (!isset($this->app->user) || !$this->app->user) { |
| 76 | $this->sendErrorResponse('Authentication required', 401, 'UNAUTHORIZED'); |
| 77 | return false; |
| 78 | } |
| 79 | |
| 80 | if (!$this->app->user->checkAccess('uri_events_reports')) { |
| 81 | $this->sendErrorResponse('Access denied. Requires uri_events_reports permission', 403, 'FORBIDDEN'); |
| 82 | return false; |
| 83 | } |
| 84 | |
| 85 | return true; |
| 86 | } |
| 87 | |
| 88 | // ========================================================================= |
| 89 | // REPORT ENDPOINTS |
| 90 | // ========================================================================= |
| 91 | |
| 92 | /** |
| 93 | * GET /api/:typeNum/events/:eventId/report |
| 94 | * |
| 95 | * Returns full event report with all metrics combined. |
| 96 | * |
| 97 | * @param int $eventId Event ID |
| 98 | * |
| 99 | * Response: |
| 100 | * { |
| 101 | * "success": true, |
| 102 | * "report": { |
| 103 | * "event": {...}, |
| 104 | * "sales": {...}, |
| 105 | * "marketing": {...}, |
| 106 | * "inventory": {...}, |
| 107 | * "comebackCash": {...}, |
| 108 | * "generatedAt": "2024-12-05T10:30:00Z" |
| 109 | * } |
| 110 | * } |
| 111 | */ |
| 112 | public function getFullReport(int $eventId): void |
| 113 | { |
| 114 | $this->setJsonContentType(); |
| 115 | |
| 116 | if (!$this->checkReportAuth()) { |
| 117 | return; |
| 118 | } |
| 119 | |
| 120 | try { |
| 121 | $event = $this->getEventById($eventId); |
| 122 | if (!$event) { |
| 123 | $this->sendErrorResponse('Event not found', 404, 'NOT_FOUND'); |
| 124 | return; |
| 125 | } |
| 126 | |
| 127 | $report = [ |
| 128 | 'event' => $event->toArray(), |
| 129 | 'sales' => $this->getSalesMetrics($event), |
| 130 | 'marketing' => $this->getMarketingMetrics($event), |
| 131 | 'inventory' => $this->getInventoryMetrics($event), |
| 132 | 'comebackCash' => $this->getComebackCashMetrics($event), |
| 133 | 'generatedAt' => (new DateTime())->format('c'), |
| 134 | ]; |
| 135 | |
| 136 | $response = [ |
| 137 | 'success' => true, |
| 138 | 'report' => $report, |
| 139 | ]; |
| 140 | |
| 141 | $this->sendJsonResponse($response); |
| 142 | |
| 143 | } catch (Exception $e) { |
| 144 | error_log("EventReportController::getFullReport error: " . $e->getMessage()); |
| 145 | $this->sendErrorResponse('Failed to generate report', 500); |
| 146 | } |
| 147 | } |
| 148 | |
| 149 | /** |
| 150 | * GET /api/:typeNum/events/:eventId/report/sales |
| 151 | * |
| 152 | * Returns sales metrics only for the event. |
| 153 | * |
| 154 | * @param int $eventId Event ID |
| 155 | * |
| 156 | * Response: |
| 157 | * { |
| 158 | * "success": true, |
| 159 | * "metrics": { |
| 160 | * "revenue": 15000.00, |
| 161 | * "transactions": 125, |
| 162 | * "avgTicket": 120.00, |
| 163 | * "baseline": { ... }, |
| 164 | * "lift": { ... } |
| 165 | * } |
| 166 | * } |
| 167 | */ |
| 168 | public function getSalesReport(int $eventId): void |
| 169 | { |
| 170 | $this->setJsonContentType(); |
| 171 | |
| 172 | if (!$this->checkReportAuth()) { |
| 173 | return; |
| 174 | } |
| 175 | |
| 176 | try { |
| 177 | $event = $this->getEventById($eventId); |
| 178 | if (!$event) { |
| 179 | $this->sendErrorResponse('Event not found', 404, 'NOT_FOUND'); |
| 180 | return; |
| 181 | } |
| 182 | |
| 183 | $metrics = $this->getSalesMetrics($event); |
| 184 | |
| 185 | $response = [ |
| 186 | 'success' => true, |
| 187 | 'metrics' => $metrics, |
| 188 | ]; |
| 189 | |
| 190 | $this->sendJsonResponse($response); |
| 191 | |
| 192 | } catch (Exception $e) { |
| 193 | error_log("EventReportController::getSalesReport error: " . $e->getMessage()); |
| 194 | $this->sendErrorResponse('Failed to retrieve sales metrics', 500); |
| 195 | } |
| 196 | } |
| 197 | |
| 198 | /** |
| 199 | * GET /api/:typeNum/events/:eventId/report/marketing |
| 200 | * |
| 201 | * Returns marketing metrics only for the event. |
| 202 | * |
| 203 | * @param int $eventId Event ID |
| 204 | * |
| 205 | * Response: |
| 206 | * { |
| 207 | * "success": true, |
| 208 | * "metrics": { |
| 209 | * "smsSent": 500, |
| 210 | * "smsDelivered": 485, |
| 211 | * "smsClicked": 120, |
| 212 | * "deliveryRate": 0.97, |
| 213 | * "clickRate": 0.247, |
| 214 | * "campaigns": [...] |
| 215 | * } |
| 216 | * } |
| 217 | */ |
| 218 | public function getMarketingReport(int $eventId): void |
| 219 | { |
| 220 | $this->setJsonContentType(); |
| 221 | |
| 222 | if (!$this->checkReportAuth()) { |
| 223 | return; |
| 224 | } |
| 225 | |
| 226 | try { |
| 227 | $event = $this->getEventById($eventId); |
| 228 | if (!$event) { |
| 229 | $this->sendErrorResponse('Event not found', 404, 'NOT_FOUND'); |
| 230 | return; |
| 231 | } |
| 232 | |
| 233 | $metrics = $this->getMarketingMetrics($event); |
| 234 | |
| 235 | $response = [ |
| 236 | 'success' => true, |
| 237 | 'metrics' => $metrics, |
| 238 | ]; |
| 239 | |
| 240 | $this->sendJsonResponse($response); |
| 241 | |
| 242 | } catch (Exception $e) { |
| 243 | error_log("EventReportController::getMarketingReport error: " . $e->getMessage()); |
| 244 | $this->sendErrorResponse('Failed to retrieve marketing metrics', 500); |
| 245 | } |
| 246 | } |
| 247 | |
| 248 | /** |
| 249 | * GET /api/:typeNum/events/:eventId/report/inventory |
| 250 | * |
| 251 | * Returns inventory metrics only for the event. |
| 252 | * |
| 253 | * @param int $eventId Event ID |
| 254 | * |
| 255 | * Response: |
| 256 | * { |
| 257 | * "success": true, |
| 258 | * "metrics": { |
| 259 | * "itemsSold": 350, |
| 260 | * "uniqueSkus": 45, |
| 261 | * "topItems": [...], |
| 262 | * "stockMovements": {...} |
| 263 | * } |
| 264 | * } |
| 265 | */ |
| 266 | public function getInventoryReport(int $eventId): void |
| 267 | { |
| 268 | $this->setJsonContentType(); |
| 269 | |
| 270 | if (!$this->checkReportAuth()) { |
| 271 | return; |
| 272 | } |
| 273 | |
| 274 | try { |
| 275 | $event = $this->getEventById($eventId); |
| 276 | if (!$event) { |
| 277 | $this->sendErrorResponse('Event not found', 404, 'NOT_FOUND'); |
| 278 | return; |
| 279 | } |
| 280 | |
| 281 | $metrics = $this->getInventoryMetrics($event); |
| 282 | |
| 283 | $response = [ |
| 284 | 'success' => true, |
| 285 | 'metrics' => $metrics, |
| 286 | ]; |
| 287 | |
| 288 | $this->sendJsonResponse($response); |
| 289 | |
| 290 | } catch (Exception $e) { |
| 291 | error_log("EventReportController::getInventoryReport error: " . $e->getMessage()); |
| 292 | $this->sendErrorResponse('Failed to retrieve inventory metrics', 500); |
| 293 | } |
| 294 | } |
| 295 | |
| 296 | /** |
| 297 | * GET /api/:typeNum/events/:eventId/report/comebackcash |
| 298 | * |
| 299 | * Returns Comeback Cash metrics only for the event. |
| 300 | * |
| 301 | * @param int $eventId Event ID |
| 302 | * |
| 303 | * Response: |
| 304 | * { |
| 305 | * "success": true, |
| 306 | * "metrics": { |
| 307 | * "couponsIssued": 200, |
| 308 | * "couponsRedeemed": 85, |
| 309 | * "redemptionRate": 0.425, |
| 310 | * "totalValue": 1700.00, |
| 311 | * "avgCouponValue": 20.00, |
| 312 | * "revenueFromRedemptions": 4250.00 |
| 313 | * } |
| 314 | * } |
| 315 | */ |
| 316 | public function getComebackCashReport(int $eventId): void |
| 317 | { |
| 318 | $this->setJsonContentType(); |
| 319 | |
| 320 | if (!$this->checkReportAuth()) { |
| 321 | return; |
| 322 | } |
| 323 | |
| 324 | try { |
| 325 | $event = $this->getEventById($eventId); |
| 326 | if (!$event) { |
| 327 | $this->sendErrorResponse('Event not found', 404, 'NOT_FOUND'); |
| 328 | return; |
| 329 | } |
| 330 | |
| 331 | $metrics = $this->getComebackCashMetrics($event); |
| 332 | |
| 333 | $response = [ |
| 334 | 'success' => true, |
| 335 | 'metrics' => $metrics, |
| 336 | ]; |
| 337 | |
| 338 | $this->sendJsonResponse($response); |
| 339 | |
| 340 | } catch (Exception $e) { |
| 341 | error_log("EventReportController::getComebackCashReport error: " . $e->getMessage()); |
| 342 | $this->sendErrorResponse('Failed to retrieve Comeback Cash metrics', 500); |
| 343 | } |
| 344 | } |
| 345 | |
| 346 | /** |
| 347 | * GET /api/:typeNum/events/:eventId/report/export |
| 348 | * |
| 349 | * Returns CSV download of the full event report. |
| 350 | * |
| 351 | * @param int $eventId Event ID |
| 352 | * |
| 353 | * Response: CSV file download |
| 354 | * Content-Type: text/csv |
| 355 | * Content-Disposition: attachment; filename="event-report-{id}.csv" |
| 356 | */ |
| 357 | public function exportReport(int $eventId): void |
| 358 | { |
| 359 | if (!$this->checkReportAuth()) { |
| 360 | return; |
| 361 | } |
| 362 | |
| 363 | try { |
| 364 | $event = $this->getEventById($eventId); |
| 365 | if (!$event) { |
| 366 | $this->setJsonContentType(); |
| 367 | $this->sendErrorResponse('Event not found', 404, 'NOT_FOUND'); |
| 368 | return; |
| 369 | } |
| 370 | |
| 371 | // Gather all metrics |
| 372 | $salesMetrics = $this->getSalesMetrics($event); |
| 373 | $marketingMetrics = $this->getMarketingMetrics($event); |
| 374 | $inventoryMetrics = $this->getInventoryMetrics($event); |
| 375 | $comebackCashMetrics = $this->getComebackCashMetrics($event); |
| 376 | |
| 377 | // Generate CSV content |
| 378 | $csv = $this->generateCsvReport($event, $salesMetrics, $marketingMetrics, $inventoryMetrics, $comebackCashMetrics); |
| 379 | |
| 380 | // Set CSV headers |
| 381 | $filename = "event-report-{$eventId}.csv"; |
| 382 | $this->app->response->headers->set('Content-Type', 'text/csv; charset=utf-8'); |
| 383 | $this->app->response->headers->set('Content-Disposition', "attachment; filename=\"{$filename}\""); |
| 384 | $this->app->response->headers->set('Cache-Control', 'no-cache, no-store, must-revalidate'); |
| 385 | $this->app->response->headers->set('Pragma', 'no-cache'); |
| 386 | $this->app->response->headers->set('Expires', '0'); |
| 387 | |
| 388 | $this->app->response->setBody($csv); |
| 389 | |
| 390 | } catch (Exception $e) { |
| 391 | error_log("EventReportController::exportReport error: " . $e->getMessage()); |
| 392 | $this->setJsonContentType(); |
| 393 | $this->sendErrorResponse('Failed to export report', 500); |
| 394 | } |
| 395 | } |
| 396 | |
| 397 | // ========================================================================= |
| 398 | // METRICS CALCULATION METHODS |
| 399 | // ========================================================================= |
| 400 | |
| 401 | /** |
| 402 | * Get sales metrics for an event |
| 403 | * |
| 404 | * @param Event $event Event object |
| 405 | * @return array Sales metrics |
| 406 | */ |
| 407 | private function getSalesMetrics(Event $event): array |
| 408 | { |
| 409 | // Initialize with defaults |
| 410 | $metrics = [ |
| 411 | 'revenue' => 0.0, |
| 412 | 'transactions' => 0, |
| 413 | 'avgTicket' => 0.0, |
| 414 | 'itemsSold' => 0, |
| 415 | 'baseline' => [ |
| 416 | 'revenue' => 0.0, |
| 417 | 'transactions' => 0, |
| 418 | 'avgTicket' => 0.0, |
| 419 | 'period' => null, |
| 420 | ], |
| 421 | 'lift' => [ |
| 422 | 'revenue' => 0.0, |
| 423 | 'revenuePercent' => 0.0, |
| 424 | 'transactions' => 0, |
| 425 | 'transactionsPercent' => 0.0, |
| 426 | ], |
| 427 | ]; |
| 428 | |
| 429 | // Only calculate if event has valid dates |
| 430 | if (!$event->startDate || !$event->endDate) { |
| 431 | return $metrics; |
| 432 | } |
| 433 | |
| 434 | $startDate = $event->startDate->format('Y-m-d'); |
| 435 | $endDate = $event->endDate->format('Y-m-d'); |
| 436 | |
| 437 | // Get sales data from buys table during event period |
| 438 | try { |
| 439 | $sql = "SELECT |
| 440 | COALESCE(SUM(total), 0) as revenue, |
| 441 | COUNT(DISTINCT id) as transactions, |
| 442 | COALESCE(SUM(itemQty), 0) as items_sold |
| 443 | FROM buys |
| 444 | WHERE DATE(buyDate) BETWEEN :startDate AND :endDate |
| 445 | AND status IN ('paid', 'complete', 'completed')"; |
| 446 | |
| 447 | $stmt = $this->db->prepare($sql); |
| 448 | $stmt->execute([ |
| 449 | ':startDate' => $startDate, |
| 450 | ':endDate' => $endDate, |
| 451 | ]); |
| 452 | |
| 453 | $row = $stmt->fetch(PDO::FETCH_ASSOC); |
| 454 | if ($row) { |
| 455 | $metrics['revenue'] = (float) $row['revenue']; |
| 456 | $metrics['transactions'] = (int) $row['transactions']; |
| 457 | $metrics['itemsSold'] = (int) $row['items_sold']; |
| 458 | $metrics['avgTicket'] = $metrics['transactions'] > 0 |
| 459 | ? round($metrics['revenue'] / $metrics['transactions'], 2) |
| 460 | : 0.0; |
| 461 | } |
| 462 | |
| 463 | // Calculate baseline from same period previous year |
| 464 | $baselineStart = (new DateTime($startDate))->modify('-1 year')->format('Y-m-d'); |
| 465 | $baselineEnd = (new DateTime($endDate))->modify('-1 year')->format('Y-m-d'); |
| 466 | |
| 467 | $sql = "SELECT |
| 468 | COALESCE(SUM(total), 0) as revenue, |
| 469 | COUNT(DISTINCT id) as transactions |
| 470 | FROM buys |
| 471 | WHERE DATE(buyDate) BETWEEN :startDate AND :endDate |
| 472 | AND status IN ('paid', 'complete', 'completed')"; |
| 473 | |
| 474 | $stmt = $this->db->prepare($sql); |
| 475 | $stmt->execute([ |
| 476 | ':startDate' => $baselineStart, |
| 477 | ':endDate' => $baselineEnd, |
| 478 | ]); |
| 479 | |
| 480 | $baselineRow = $stmt->fetch(PDO::FETCH_ASSOC); |
| 481 | if ($baselineRow) { |
| 482 | $metrics['baseline']['revenue'] = (float) $baselineRow['revenue']; |
| 483 | $metrics['baseline']['transactions'] = (int) $baselineRow['transactions']; |
| 484 | $metrics['baseline']['avgTicket'] = $metrics['baseline']['transactions'] > 0 |
| 485 | ? round($metrics['baseline']['revenue'] / $metrics['baseline']['transactions'], 2) |
| 486 | : 0.0; |
| 487 | $metrics['baseline']['period'] = "{$baselineStart} to {$baselineEnd}"; |
| 488 | |
| 489 | // Calculate lift |
| 490 | $metrics['lift']['revenue'] = $metrics['revenue'] - $metrics['baseline']['revenue']; |
| 491 | $metrics['lift']['revenuePercent'] = $metrics['baseline']['revenue'] > 0 |
| 492 | ? round(($metrics['lift']['revenue'] / $metrics['baseline']['revenue']) * 100, 2) |
| 493 | : 0.0; |
| 494 | $metrics['lift']['transactions'] = $metrics['transactions'] - $metrics['baseline']['transactions']; |
| 495 | $metrics['lift']['transactionsPercent'] = $metrics['baseline']['transactions'] > 0 |
| 496 | ? round(($metrics['lift']['transactions'] / $metrics['baseline']['transactions']) * 100, 2) |
| 497 | : 0.0; |
| 498 | } |
| 499 | |
| 500 | } catch (Exception $e) { |
| 501 | error_log("EventReportController::getSalesMetrics error: " . $e->getMessage()); |
| 502 | } |
| 503 | |
| 504 | return $metrics; |
| 505 | } |
| 506 | |
| 507 | /** |
| 508 | * Get marketing metrics for an event |
| 509 | * |
| 510 | * @param Event $event Event object |
| 511 | * @return array Marketing metrics |
| 512 | */ |
| 513 | private function getMarketingMetrics(Event $event): array |
| 514 | { |
| 515 | $metrics = [ |
| 516 | 'smsSent' => 0, |
| 517 | 'smsDelivered' => 0, |
| 518 | 'smsFailed' => 0, |
| 519 | 'smsClicked' => 0, |
| 520 | 'deliveryRate' => 0.0, |
| 521 | 'clickRate' => 0.0, |
| 522 | 'campaigns' => [], |
| 523 | ]; |
| 524 | |
| 525 | // Only calculate if event has valid dates |
| 526 | if (!$event->startDate || !$event->endDate) { |
| 527 | return $metrics; |
| 528 | } |
| 529 | |
| 530 | $startDate = $event->startDate->format('Y-m-d'); |
| 531 | $endDate = $event->endDate->format('Y-m-d'); |
| 532 | |
| 533 | try { |
| 534 | // Check if sms_blasts table exists and get blast data linked to event |
| 535 | // First check event_integrations for SMS campaign links |
| 536 | $sql = "SELECT foreign_id FROM event_integrations |
| 537 | WHERE event_id = :eventId |
| 538 | AND integration_type = 'sms_blast' |
| 539 | AND status != 'failed'"; |
| 540 | |
| 541 | $stmt = $this->db->prepare($sql); |
| 542 | $stmt->execute([':eventId' => $event->id]); |
| 543 | $blastIds = $stmt->fetchAll(PDO::FETCH_COLUMN); |
| 544 | |
| 545 | if (!empty($blastIds)) { |
| 546 | // Get SMS stats from linked blasts |
| 547 | $placeholders = implode(',', array_fill(0, count($blastIds), '?')); |
| 548 | $sql = "SELECT |
| 549 | COUNT(*) as sent, |
| 550 | SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) as delivered, |
| 551 | SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed, |
| 552 | SUM(CASE WHEN clicked = 1 THEN 1 ELSE 0 END) as clicked |
| 553 | FROM sms_log |
| 554 | WHERE blast_id IN ({$placeholders})"; |
| 555 | |
| 556 | $stmt = $this->db->prepare($sql); |
| 557 | $stmt->execute($blastIds); |
| 558 | $row = $stmt->fetch(PDO::FETCH_ASSOC); |
| 559 | |
| 560 | if ($row) { |
| 561 | $metrics['smsSent'] = (int) $row['sent']; |
| 562 | $metrics['smsDelivered'] = (int) $row['delivered']; |
| 563 | $metrics['smsFailed'] = (int) $row['failed']; |
| 564 | $metrics['smsClicked'] = (int) $row['clicked']; |
| 565 | } |
| 566 | } |
| 567 | |
| 568 | // Also get any SMS sent during the event period (for non-linked campaigns) |
| 569 | $sql = "SELECT |
| 570 | COUNT(*) as sent, |
| 571 | SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) as delivered, |
| 572 | SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed, |
| 573 | SUM(CASE WHEN clicked = 1 THEN 1 ELSE 0 END) as clicked |
| 574 | FROM sms_log |
| 575 | WHERE DATE(created) BETWEEN :startDate AND :endDate |
| 576 | AND (blast_id IS NOT NULL OR trigger_id IS NOT NULL)"; |
| 577 | |
| 578 | $stmt = $this->db->prepare($sql); |
| 579 | $stmt->execute([ |
| 580 | ':startDate' => $startDate, |
| 581 | ':endDate' => $endDate, |
| 582 | ]); |
| 583 | |
| 584 | $periodRow = $stmt->fetch(PDO::FETCH_ASSOC); |
| 585 | if ($periodRow && (int) $periodRow['sent'] > $metrics['smsSent']) { |
| 586 | // Use period stats if they're higher (more inclusive) |
| 587 | $metrics['smsSent'] = max($metrics['smsSent'], (int) $periodRow['sent']); |
| 588 | $metrics['smsDelivered'] = max($metrics['smsDelivered'], (int) $periodRow['delivered']); |
| 589 | $metrics['smsFailed'] = max($metrics['smsFailed'], (int) $periodRow['failed']); |
| 590 | $metrics['smsClicked'] = max($metrics['smsClicked'], (int) $periodRow['clicked']); |
| 591 | } |
| 592 | |
| 593 | // Calculate rates |
| 594 | $metrics['deliveryRate'] = $metrics['smsSent'] > 0 |
| 595 | ? round($metrics['smsDelivered'] / $metrics['smsSent'], 3) |
| 596 | : 0.0; |
| 597 | $metrics['clickRate'] = $metrics['smsDelivered'] > 0 |
| 598 | ? round($metrics['smsClicked'] / $metrics['smsDelivered'], 3) |
| 599 | : 0.0; |
| 600 | |
| 601 | // Get campaign details |
| 602 | if (!empty($blastIds)) { |
| 603 | $placeholders = implode(',', array_fill(0, count($blastIds), '?')); |
| 604 | $sql = "SELECT id, name, sent_count, delivered_count, click_count, scheduled_at |
| 605 | FROM sms_blasts |
| 606 | WHERE id IN ({$placeholders}) |
| 607 | ORDER BY scheduled_at DESC"; |
| 608 | |
| 609 | $stmt = $this->db->prepare($sql); |
| 610 | $stmt->execute($blastIds); |
| 611 | $metrics['campaigns'] = $stmt->fetchAll(PDO::FETCH_ASSOC); |
| 612 | } |
| 613 | |
| 614 | } catch (Exception $e) { |
| 615 | error_log("EventReportController::getMarketingMetrics error: " . $e->getMessage()); |
| 616 | } |
| 617 | |
| 618 | return $metrics; |
| 619 | } |
| 620 | |
| 621 | /** |
| 622 | * Get inventory metrics for an event |
| 623 | * |
| 624 | * @param Event $event Event object |
| 625 | * @return array Inventory metrics |
| 626 | */ |
| 627 | private function getInventoryMetrics(Event $event): array |
| 628 | { |
| 629 | $metrics = [ |
| 630 | 'itemsSold' => 0, |
| 631 | 'uniqueSkus' => 0, |
| 632 | 'totalQuantity' => 0, |
| 633 | 'topItems' => [], |
| 634 | 'categoryBreakdown' => [], |
| 635 | ]; |
| 636 | |
| 637 | // Only calculate if event has valid dates |
| 638 | if (!$event->startDate || !$event->endDate) { |
| 639 | return $metrics; |
| 640 | } |
| 641 | |
| 642 | $startDate = $event->startDate->format('Y-m-d'); |
| 643 | $endDate = $event->endDate->format('Y-m-d'); |
| 644 | |
| 645 | try { |
| 646 | // Get items sold during event period from buy_details or similar table |
| 647 | $sql = "SELECT |
| 648 | COUNT(DISTINCT bd.id) as items_sold, |
| 649 | COUNT(DISTINCT bd.sku) as unique_skus, |
| 650 | COALESCE(SUM(bd.qty), 0) as total_qty |
| 651 | FROM buy_details bd |
| 652 | INNER JOIN buys b ON bd.buy_id = b.id |
| 653 | WHERE DATE(b.buyDate) BETWEEN :startDate AND :endDate |
| 654 | AND b.status IN ('paid', 'complete', 'completed')"; |
| 655 | |
| 656 | $stmt = $this->db->prepare($sql); |
| 657 | $stmt->execute([ |
| 658 | ':startDate' => $startDate, |
| 659 | ':endDate' => $endDate, |
| 660 | ]); |
| 661 | |
| 662 | $row = $stmt->fetch(PDO::FETCH_ASSOC); |
| 663 | if ($row) { |
| 664 | $metrics['itemsSold'] = (int) $row['items_sold']; |
| 665 | $metrics['uniqueSkus'] = (int) $row['unique_skus']; |
| 666 | $metrics['totalQuantity'] = (int) $row['total_qty']; |
| 667 | } |
| 668 | |
| 669 | // Get top selling items |
| 670 | $sql = "SELECT |
| 671 | bd.sku, |
| 672 | bd.description, |
| 673 | SUM(bd.qty) as quantity_sold, |
| 674 | SUM(bd.price * bd.qty) as revenue |
| 675 | FROM buy_details bd |
| 676 | INNER JOIN buys b ON bd.buy_id = b.id |
| 677 | WHERE DATE(b.buyDate) BETWEEN :startDate AND :endDate |
| 678 | AND b.status IN ('paid', 'complete', 'completed') |
| 679 | GROUP BY bd.sku, bd.description |
| 680 | ORDER BY quantity_sold DESC |
| 681 | LIMIT 10"; |
| 682 | |
| 683 | $stmt = $this->db->prepare($sql); |
| 684 | $stmt->execute([ |
| 685 | ':startDate' => $startDate, |
| 686 | ':endDate' => $endDate, |
| 687 | ]); |
| 688 | |
| 689 | $metrics['topItems'] = $stmt->fetchAll(PDO::FETCH_ASSOC); |
| 690 | |
| 691 | // Get category breakdown if category data is available |
| 692 | $sql = "SELECT |
| 693 | COALESCE(bd.category, 'Uncategorized') as category, |
| 694 | SUM(bd.qty) as quantity_sold, |
| 695 | SUM(bd.price * bd.qty) as revenue |
| 696 | FROM buy_details bd |
| 697 | INNER JOIN buys b ON bd.buy_id = b.id |
| 698 | WHERE DATE(b.buyDate) BETWEEN :startDate AND :endDate |
| 699 | AND b.status IN ('paid', 'complete', 'completed') |
| 700 | GROUP BY bd.category |
| 701 | ORDER BY revenue DESC"; |
| 702 | |
| 703 | $stmt = $this->db->prepare($sql); |
| 704 | $stmt->execute([ |
| 705 | ':startDate' => $startDate, |
| 706 | ':endDate' => $endDate, |
| 707 | ]); |
| 708 | |
| 709 | $metrics['categoryBreakdown'] = $stmt->fetchAll(PDO::FETCH_ASSOC); |
| 710 | |
| 711 | } catch (Exception $e) { |
| 712 | error_log("EventReportController::getInventoryMetrics error: " . $e->getMessage()); |
| 713 | } |
| 714 | |
| 715 | return $metrics; |
| 716 | } |
| 717 | |
| 718 | /** |
| 719 | * Get Comeback Cash metrics for an event |
| 720 | * |
| 721 | * @param Event $event Event object |
| 722 | * @return array Comeback Cash metrics |
| 723 | */ |
| 724 | private function getComebackCashMetrics(Event $event): array |
| 725 | { |
| 726 | $metrics = [ |
| 727 | 'couponsIssued' => 0, |
| 728 | 'couponsRedeemed' => 0, |
| 729 | 'couponsExpired' => 0, |
| 730 | 'couponsPending' => 0, |
| 731 | 'redemptionRate' => 0.0, |
| 732 | 'totalValueIssued' => 0.0, |
| 733 | 'totalValueRedeemed' => 0.0, |
| 734 | 'avgCouponValue' => 0.0, |
| 735 | 'revenueFromRedemptions' => 0.0, |
| 736 | ]; |
| 737 | |
| 738 | try { |
| 739 | // Get Comeback Cash event integration |
| 740 | $sql = "SELECT foreign_id FROM event_integrations |
| 741 | WHERE event_id = :eventId |
| 742 | AND integration_type = 'comeback_cash' |
| 743 | AND status != 'failed'"; |
| 744 | |
| 745 | $stmt = $this->db->prepare($sql); |
| 746 | $stmt->execute([':eventId' => $event->id]); |
| 747 | $ccEventIds = $stmt->fetchAll(PDO::FETCH_COLUMN); |
| 748 | |
| 749 | if (empty($ccEventIds)) { |
| 750 | // No linked Comeback Cash events, try to find by date range |
| 751 | if ($event->startDate && $event->endDate) { |
| 752 | $sql = "SELECT id FROM ccevents |
| 753 | WHERE startDate <= :endDate |
| 754 | AND endDate >= :startDate |
| 755 | LIMIT 5"; |
| 756 | |
| 757 | $stmt = $this->db->prepare($sql); |
| 758 | $stmt->execute([ |
| 759 | ':startDate' => $event->startDate->format('Y-m-d'), |
| 760 | ':endDate' => $event->endDate->format('Y-m-d'), |
| 761 | ]); |
| 762 | $ccEventIds = $stmt->fetchAll(PDO::FETCH_COLUMN); |
| 763 | } |
| 764 | } |
| 765 | |
| 766 | if (!empty($ccEventIds)) { |
| 767 | $placeholders = implode(',', array_fill(0, count($ccEventIds), '?')); |
| 768 | |
| 769 | // Get coupon statistics |
| 770 | $sql = "SELECT |
| 771 | COUNT(*) as issued, |
| 772 | SUM(CASE WHEN redeemed = 1 THEN 1 ELSE 0 END) as redeemed, |
| 773 | SUM(CASE WHEN expired = 1 THEN 1 ELSE 0 END) as expired, |
| 774 | SUM(CASE WHEN redeemed = 0 AND expired = 0 THEN 1 ELSE 0 END) as pending, |
| 775 | COALESCE(SUM(amount), 0) as total_issued, |
| 776 | COALESCE(SUM(CASE WHEN redeemed = 1 THEN amount ELSE 0 END), 0) as total_redeemed |
| 777 | FROM cccoupons |
| 778 | WHERE ccevent_id IN ({$placeholders})"; |
| 779 | |
| 780 | $stmt = $this->db->prepare($sql); |
| 781 | $stmt->execute($ccEventIds); |
| 782 | $row = $stmt->fetch(PDO::FETCH_ASSOC); |
| 783 | |
| 784 | if ($row) { |
| 785 | $metrics['couponsIssued'] = (int) $row['issued']; |
| 786 | $metrics['couponsRedeemed'] = (int) $row['redeemed']; |
| 787 | $metrics['couponsExpired'] = (int) $row['expired']; |
| 788 | $metrics['couponsPending'] = (int) $row['pending']; |
| 789 | $metrics['totalValueIssued'] = (float) $row['total_issued']; |
| 790 | $metrics['totalValueRedeemed'] = (float) $row['total_redeemed']; |
| 791 | |
| 792 | $metrics['avgCouponValue'] = $metrics['couponsIssued'] > 0 |
| 793 | ? round($metrics['totalValueIssued'] / $metrics['couponsIssued'], 2) |
| 794 | : 0.0; |
| 795 | |
| 796 | $metrics['redemptionRate'] = $metrics['couponsIssued'] > 0 |
| 797 | ? round($metrics['couponsRedeemed'] / $metrics['couponsIssued'], 3) |
| 798 | : 0.0; |
| 799 | } |
| 800 | |
| 801 | // Get revenue from transactions with redeemed coupons |
| 802 | $sql = "SELECT COALESCE(SUM(b.total), 0) as revenue |
| 803 | FROM buys b |
| 804 | INNER JOIN cccoupons c ON c.redeem_buy_id = b.id |
| 805 | WHERE c.ccevent_id IN ({$placeholders}) |
| 806 | AND c.redeemed = 1"; |
| 807 | |
| 808 | $stmt = $this->db->prepare($sql); |
| 809 | $stmt->execute($ccEventIds); |
| 810 | $revenueRow = $stmt->fetch(PDO::FETCH_ASSOC); |
| 811 | |
| 812 | if ($revenueRow) { |
| 813 | $metrics['revenueFromRedemptions'] = (float) $revenueRow['revenue']; |
| 814 | } |
| 815 | } |
| 816 | |
| 817 | } catch (Exception $e) { |
| 818 | error_log("EventReportController::getComebackCashMetrics error: " . $e->getMessage()); |
| 819 | } |
| 820 | |
| 821 | return $metrics; |
| 822 | } |
| 823 | |
| 824 | // ========================================================================= |
| 825 | // CSV GENERATION |
| 826 | // ========================================================================= |
| 827 | |
| 828 | /** |
| 829 | * Generate CSV report content |
| 830 | * |
| 831 | * @param Event $event Event object |
| 832 | * @param array $salesMetrics Sales metrics |
| 833 | * @param array $marketingMetrics Marketing metrics |
| 834 | * @param array $inventoryMetrics Inventory metrics |
| 835 | * @param array $comebackCashMetrics Comeback Cash metrics |
| 836 | * @return string CSV content |
| 837 | */ |
| 838 | private function generateCsvReport( |
| 839 | Event $event, |
| 840 | array $salesMetrics, |
| 841 | array $marketingMetrics, |
| 842 | array $inventoryMetrics, |
| 843 | array $comebackCashMetrics |
| 844 | ): string { |
| 845 | $output = fopen('php://temp', 'r+'); |
| 846 | |
| 847 | // Add UTF-8 BOM for Excel compatibility |
| 848 | fwrite($output, "\xEF\xBB\xBF"); |
| 849 | |
| 850 | // Event Information Section |
| 851 | fputcsv($output, ['Event Report']); |
| 852 | fputcsv($output, ['Generated', (new DateTime())->format('Y-m-d H:i:s')]); |
| 853 | fputcsv($output, []); |
| 854 | fputcsv($output, ['Event Information']); |
| 855 | fputcsv($output, ['Name', $event->name]); |
| 856 | fputcsv($output, ['Type', $event->eventType]); |
| 857 | fputcsv($output, ['Status', $event->status]); |
| 858 | fputcsv($output, ['Start Date', $event->startDate ? $event->startDate->format('Y-m-d') : 'N/A']); |
| 859 | fputcsv($output, ['End Date', $event->endDate ? $event->endDate->format('Y-m-d') : 'N/A']); |
| 860 | fputcsv($output, []); |
| 861 | |
| 862 | // Sales Metrics Section |
| 863 | fputcsv($output, ['Sales Metrics']); |
| 864 | fputcsv($output, ['Metric', 'Value']); |
| 865 | fputcsv($output, ['Revenue', number_format($salesMetrics['revenue'], 2)]); |
| 866 | fputcsv($output, ['Transactions', $salesMetrics['transactions']]); |
| 867 | fputcsv($output, ['Average Ticket', number_format($salesMetrics['avgTicket'], 2)]); |
| 868 | fputcsv($output, ['Items Sold', $salesMetrics['itemsSold']]); |
| 869 | fputcsv($output, []); |
| 870 | fputcsv($output, ['Baseline Comparison (Previous Year)']); |
| 871 | fputcsv($output, ['Baseline Revenue', number_format($salesMetrics['baseline']['revenue'], 2)]); |
| 872 | fputcsv($output, ['Baseline Transactions', $salesMetrics['baseline']['transactions']]); |
| 873 | fputcsv($output, ['Revenue Lift', number_format($salesMetrics['lift']['revenue'], 2)]); |
| 874 | fputcsv($output, ['Revenue Lift %', $salesMetrics['lift']['revenuePercent'] . '%']); |
| 875 | fputcsv($output, []); |
| 876 | |
| 877 | // Marketing Metrics Section |
| 878 | fputcsv($output, ['Marketing Metrics']); |
| 879 | fputcsv($output, ['Metric', 'Value']); |
| 880 | fputcsv($output, ['SMS Sent', $marketingMetrics['smsSent']]); |
| 881 | fputcsv($output, ['SMS Delivered', $marketingMetrics['smsDelivered']]); |
| 882 | fputcsv($output, ['SMS Failed', $marketingMetrics['smsFailed']]); |
| 883 | fputcsv($output, ['SMS Clicked', $marketingMetrics['smsClicked']]); |
| 884 | fputcsv($output, ['Delivery Rate', ($marketingMetrics['deliveryRate'] * 100) . '%']); |
| 885 | fputcsv($output, ['Click Rate', ($marketingMetrics['clickRate'] * 100) . '%']); |
| 886 | fputcsv($output, []); |
| 887 | |
| 888 | // Inventory Metrics Section |
| 889 | fputcsv($output, ['Inventory Metrics']); |
| 890 | fputcsv($output, ['Metric', 'Value']); |
| 891 | fputcsv($output, ['Items Sold', $inventoryMetrics['itemsSold']]); |
| 892 | fputcsv($output, ['Unique SKUs', $inventoryMetrics['uniqueSkus']]); |
| 893 | fputcsv($output, ['Total Quantity', $inventoryMetrics['totalQuantity']]); |
| 894 | fputcsv($output, []); |
| 895 | |
| 896 | // Top Items |
| 897 | if (!empty($inventoryMetrics['topItems'])) { |
| 898 | fputcsv($output, ['Top Selling Items']); |
| 899 | fputcsv($output, ['SKU', 'Description', 'Quantity Sold', 'Revenue']); |
| 900 | foreach ($inventoryMetrics['topItems'] as $item) { |
| 901 | fputcsv($output, [ |
| 902 | $item['sku'] ?? '', |
| 903 | $item['description'] ?? '', |
| 904 | $item['quantity_sold'] ?? 0, |
| 905 | number_format((float) ($item['revenue'] ?? 0), 2), |
| 906 | ]); |
| 907 | } |
| 908 | fputcsv($output, []); |
| 909 | } |
| 910 | |
| 911 | // Comeback Cash Metrics Section |
| 912 | fputcsv($output, ['Comeback Cash Metrics']); |
| 913 | fputcsv($output, ['Metric', 'Value']); |
| 914 | fputcsv($output, ['Coupons Issued', $comebackCashMetrics['couponsIssued']]); |
| 915 | fputcsv($output, ['Coupons Redeemed', $comebackCashMetrics['couponsRedeemed']]); |
| 916 | fputcsv($output, ['Coupons Expired', $comebackCashMetrics['couponsExpired']]); |
| 917 | fputcsv($output, ['Coupons Pending', $comebackCashMetrics['couponsPending']]); |
| 918 | fputcsv($output, ['Redemption Rate', ($comebackCashMetrics['redemptionRate'] * 100) . '%']); |
| 919 | fputcsv($output, ['Total Value Issued', number_format($comebackCashMetrics['totalValueIssued'], 2)]); |
| 920 | fputcsv($output, ['Total Value Redeemed', number_format($comebackCashMetrics['totalValueRedeemed'], 2)]); |
| 921 | fputcsv($output, ['Average Coupon Value', number_format($comebackCashMetrics['avgCouponValue'], 2)]); |
| 922 | fputcsv($output, ['Revenue from Redemptions', number_format($comebackCashMetrics['revenueFromRedemptions'], 2)]); |
| 923 | |
| 924 | rewind($output); |
| 925 | $csv = stream_get_contents($output); |
| 926 | fclose($output); |
| 927 | |
| 928 | return $csv; |
| 929 | } |
| 930 | |
| 931 | // ========================================================================= |
| 932 | // DATABASE HELPERS |
| 933 | // ========================================================================= |
| 934 | |
| 935 | /** |
| 936 | * Get a single event by ID |
| 937 | * |
| 938 | * @param int $eventId Event ID |
| 939 | * @return Event|null Event object or null if not found |
| 940 | */ |
| 941 | private function getEventById(int $eventId): ?Event |
| 942 | { |
| 943 | $sql = "SELECT * FROM events WHERE id = :id"; |
| 944 | $stmt = $this->db->prepare($sql); |
| 945 | $stmt->execute([':id' => $eventId]); |
| 946 | $row = $stmt->fetch(PDO::FETCH_ASSOC); |
| 947 | |
| 948 | if (!$row) { |
| 949 | return null; |
| 950 | } |
| 951 | |
| 952 | return Event::fromRow($row); |
| 953 | } |
| 954 | |
| 955 | // ========================================================================= |
| 956 | // RESPONSE HELPERS |
| 957 | // ========================================================================= |
| 958 | |
| 959 | /** |
| 960 | * Set JSON content type header |
| 961 | */ |
| 962 | private function setJsonContentType(): void |
| 963 | { |
| 964 | $this->app->response->headers->set('Content-Type', 'application/json'); |
| 965 | } |
| 966 | |
| 967 | /** |
| 968 | * Send JSON response body |
| 969 | * |
| 970 | * @param array $data Response data |
| 971 | */ |
| 972 | private function sendJsonResponse(array $data): void |
| 973 | { |
| 974 | $this->app->response->setBody(json_encode($data)); |
| 975 | } |
| 976 | |
| 977 | /** |
| 978 | * Send error response |
| 979 | * |
| 980 | * @param string $message Error message |
| 981 | * @param int $httpStatus HTTP status code |
| 982 | * @param string|null $errorCode Application error code |
| 983 | */ |
| 984 | private function sendErrorResponse(string $message, int $httpStatus, ?string $errorCode = null): void |
| 985 | { |
| 986 | $response = [ |
| 987 | 'success' => false, |
| 988 | 'error' => $message, |
| 989 | ]; |
| 990 | |
| 991 | if ($errorCode !== null) { |
| 992 | $response['code'] = $errorCode; |
| 993 | } |
| 994 | |
| 995 | $this->app->response->setStatus($httpStatus); |
| 996 | $this->sendJsonResponse($response); |
| 997 | } |
| 998 | } |