Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
89.95% |
340 / 378 |
|
55.00% |
11 / 20 |
CRAP | |
0.00% |
0 / 1 |
| KanbanBoardQueryService | |
89.92% |
339 / 377 |
|
55.00% |
11 / 20 |
118.72 | |
0.00% |
0 / 1 |
| __construct | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| fetchBoardData | |
100.00% |
14 / 14 |
|
100.00% |
1 / 1 |
2 | |||
| resolveModuleConfig | |
100.00% |
14 / 14 |
|
100.00% |
1 / 1 |
6 | |||
| buildConfigFromModuleRow | |
92.00% |
23 / 25 |
|
0.00% |
0 / 1 |
5.01 | |||
| detectNameColumn | |
77.78% |
7 / 9 |
|
0.00% |
0 / 1 |
4.18 | |||
| resolveSummaryFields | |
100.00% |
15 / 15 |
|
100.00% |
1 / 1 |
5 | |||
| getDefaultSummaryFields | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| filterExcludedSummaryFields | |
100.00% |
31 / 31 |
|
100.00% |
1 / 1 |
1 | |||
| fetchFirstFilterVisibleFields | |
95.83% |
23 / 24 |
|
0.00% |
0 / 1 |
6 | |||
| fetchPicklistColumns | |
100.00% |
11 / 11 |
|
100.00% |
1 / 1 |
2 | |||
| detectPriorityPicklistId | |
70.00% |
7 / 10 |
|
0.00% |
0 / 1 |
4.43 | |||
| loadPicklistValuesMap | |
59.46% |
22 / 37 |
|
0.00% |
0 / 1 |
8.40 | |||
| loadRelationLabelsMap | |
88.89% |
8 / 9 |
|
0.00% |
0 / 1 |
5.03 | |||
| fetchFieldRelationLabels | |
96.30% |
26 / 27 |
|
0.00% |
0 / 1 |
12 | |||
| resolvePriorityMeta | |
100.00% |
10 / 10 |
|
100.00% |
1 / 1 |
4 | |||
| resolveRelationIcon | |
100.00% |
13 / 13 |
|
100.00% |
1 / 1 |
12 | |||
| fetchModuleRecords | |
80.33% |
49 / 61 |
|
0.00% |
0 / 1 |
13.10 | |||
| extractDueDate | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
5 | |||
| buildSummaryBadges | |
100.00% |
36 / 36 |
|
100.00% |
1 / 1 |
10 | |||
| groupRecordsIntoColumns | |
100.00% |
25 / 25 |
|
100.00% |
1 / 1 |
4 | |||
| 1 | <?php |
| 2 | |
| 3 | declare(strict_types=1); |
| 4 | |
| 5 | /** @license For full copyright and license information, please see the LICENSE.md file. */ |
| 6 | |
| 7 | namespace App\Core\Engine\Application\Service; |
| 8 | |
| 9 | defined('AMMONLY_APP') || exit('Direct script access is forbidden.'); |
| 10 | |
| 11 | use App\Core\Engine\Infrastructure\Schema\SqlTableSchemaHelper; |
| 12 | use PDO; |
| 13 | use PDOException; |
| 14 | |
| 15 | /** |
| 16 | * Universal Kanban Board Query and Card Aggregation Service. |
| 17 | * |
| 18 | * Resolves module schema configurations, picklist columns, card summary fields, |
| 19 | * and groups module records into respective Kanban status columns. |
| 20 | * |
| 21 | * @package App\Core\Engine\Application\Service |
| 22 | */ |
| 23 | final readonly class KanbanBoardQueryService |
| 24 | { |
| 25 | /** @var list<string> Statuses that mark a record as finalized (not overdue). */ |
| 26 | public const array CLOSED_STATUSES = [ |
| 27 | 'completed', 'closed', 'resolved', 'cancelled', 'canceled', 'rejected', |
| 28 | ]; |
| 29 | |
| 30 | private const string IDENTIFIER_PATTERN = '/^\w+$/'; |
| 31 | |
| 32 | /** @var array<string, array{table: string, status_col: string, name_col: string, picklist_id: int, mod_id: int}> */ |
| 33 | public const array DEFAULT_MODULE_MAP = [ |
| 34 | 'tickets' => [ |
| 35 | 'table' => 'c_mod_tickets_records', |
| 36 | 'status_col' => 'ticket_status', |
| 37 | 'name_col' => 'subject', |
| 38 | 'picklist_id' => 61, |
| 39 | 'mod_id' => 50, |
| 40 | ], |
| 41 | 'projects' => [ |
| 42 | 'table' => 'c_mod_projects_records', |
| 43 | 'status_col' => 'project_status', |
| 44 | 'name_col' => 'project_name', |
| 45 | 'picklist_id' => 110, |
| 46 | 'mod_id' => 75, |
| 47 | ], |
| 48 | 'project_stages' => [ |
| 49 | 'table' => 'c_mod_project_stages_records', |
| 50 | 'status_col' => 'stage_status', |
| 51 | 'name_col' => 'stage_name', |
| 52 | 'picklist_id' => 113, |
| 53 | 'mod_id' => 76, |
| 54 | ], |
| 55 | 'project_tasks' => [ |
| 56 | 'table' => 'c_mod_project_tasks_records', |
| 57 | 'status_col' => 'task_status', |
| 58 | 'name_col' => 'task_name', |
| 59 | 'picklist_id' => 116, |
| 60 | 'mod_id' => 77, |
| 61 | ], |
| 62 | ]; |
| 63 | |
| 64 | /** |
| 65 | * KanbanBoardQueryService constructor. |
| 66 | * |
| 67 | * @param PDO $pdo Active database PDO connection handle. |
| 68 | */ |
| 69 | public function __construct( |
| 70 | private PDO $pdo |
| 71 | ) { |
| 72 | } |
| 73 | |
| 74 | /** |
| 75 | * Fetches complete Kanban board payload for a given module. |
| 76 | * |
| 77 | * @param string $module Module machine name. |
| 78 | * @return array<string, mixed>|null Board data dictionary or null if unsupported. |
| 79 | */ |
| 80 | public function fetchBoardData(string $module): ?array |
| 81 | { |
| 82 | $config = $this->resolveModuleConfig($module); |
| 83 | if ($config === null) { |
| 84 | return null; |
| 85 | } |
| 86 | |
| 87 | $columns = $this->fetchPicklistColumns($config['picklist_id']); |
| 88 | $summaryFields = $this->resolveSummaryFields($config); |
| 89 | $records = $this->fetchModuleRecords($config, $summaryFields); |
| 90 | $groupedColumns = $this->groupRecordsIntoColumns($columns, $records); |
| 91 | |
| 92 | return [ |
| 93 | 'module' => $module, |
| 94 | 'status_field' => $config['status_col'], |
| 95 | 'summary_fields' => $summaryFields, |
| 96 | 'columns' => $groupedColumns, |
| 97 | 'total_cards' => count($records), |
| 98 | ]; |
| 99 | } |
| 100 | |
| 101 | /** |
| 102 | * Resolves status configuration and table schema for module. |
| 103 | * |
| 104 | * @param string $module Module machine name. |
| 105 | * @return array{table: string, status_col: string, name_col: string, picklist_id: int, mod_id: int}|null |
| 106 | */ |
| 107 | public function resolveModuleConfig(string $module): ?array |
| 108 | { |
| 109 | try { |
| 110 | $sql = "SELECT `id`, `name`, `table_name`, `kanban_field_name`, `has_kanban` |
| 111 | FROM `a_core_module_records` |
| 112 | WHERE `name` = :mod AND `is_active` = 1 |
| 113 | LIMIT 1"; |
| 114 | $stmt = $this->pdo->prepare($sql); |
| 115 | $stmt->execute([':mod' => $module]); |
| 116 | $modRow = $stmt->fetch(PDO::FETCH_ASSOC); |
| 117 | |
| 118 | if (is_array($modRow)) { |
| 119 | $hasKanban = isset($modRow['has_kanban']) ? (int) $modRow['has_kanban'] : 1; |
| 120 | if ($hasKanban === 0) { |
| 121 | return null; |
| 122 | } |
| 123 | |
| 124 | $cfg = $this->buildConfigFromModuleRow($modRow, $module); |
| 125 | if ($cfg !== null) { |
| 126 | return $cfg; |
| 127 | } |
| 128 | } |
| 129 | } catch (PDOException) { |
| 130 | // Fallback when metadata tables are missing (e.g., in unit tests). |
| 131 | } |
| 132 | |
| 133 | return self::DEFAULT_MODULE_MAP[$module] ?? null; |
| 134 | } |
| 135 | |
| 136 | /** |
| 137 | * Builds module configuration from database module row and field metadata. |
| 138 | * |
| 139 | * @param array<string, mixed> $modRow Database row from a_core_module_records. |
| 140 | * @param string $module Module name. |
| 141 | * @return array{table: string, status_col: string, name_col: string, picklist_id: int, mod_id: int}|null |
| 142 | */ |
| 143 | private function buildConfigFromModuleRow(array $modRow, string $module): ?array |
| 144 | { |
| 145 | $moduleId = (int) $modRow['id']; |
| 146 | $tableName = (string) ($modRow['table_name'] ?? ''); |
| 147 | $statusCol = (string) ($modRow['kanban_field_name'] ?? ''); |
| 148 | |
| 149 | if ($statusCol === '') { |
| 150 | $default = self::DEFAULT_MODULE_MAP[$module] ?? null; |
| 151 | $statusCol = $default['status_col'] ?? 'status'; |
| 152 | } |
| 153 | |
| 154 | $fieldSql = "SELECT `picklist_id` FROM `a_core_field_records` |
| 155 | WHERE `module_id` = :mid AND `field_key` = :fkey |
| 156 | LIMIT 1"; |
| 157 | $fStmt = $this->pdo->prepare($fieldSql); |
| 158 | $fStmt->execute([':mid' => $moduleId, ':fkey' => $statusCol]); |
| 159 | $picklistId = (int) $fStmt->fetchColumn(); |
| 160 | |
| 161 | if ($picklistId === 0) { |
| 162 | $default = self::DEFAULT_MODULE_MAP[$module] ?? null; |
| 163 | $picklistId = $default['picklist_id'] ?? 0; |
| 164 | } |
| 165 | |
| 166 | if ($picklistId === 0) { |
| 167 | return null; |
| 168 | } |
| 169 | |
| 170 | $default = self::DEFAULT_MODULE_MAP[$module] ?? null; |
| 171 | $nameCol = $default['name_col'] ?? $this->detectNameColumn($moduleId); |
| 172 | |
| 173 | return [ |
| 174 | 'table' => $tableName !== '' ? $tableName : ($default['table'] ?? ''), |
| 175 | 'status_col' => $statusCol, |
| 176 | 'name_col' => $nameCol, |
| 177 | 'picklist_id' => $picklistId, |
| 178 | 'mod_id' => $moduleId, |
| 179 | ]; |
| 180 | } |
| 181 | |
| 182 | /** |
| 183 | * Detects primary title field key for a module. |
| 184 | * |
| 185 | * @param int $moduleId Module identifier. |
| 186 | * @return string Name column key. |
| 187 | */ |
| 188 | private function detectNameColumn(int $moduleId): string |
| 189 | { |
| 190 | try { |
| 191 | $sql = "SELECT `field_key` FROM `a_core_field_records` |
| 192 | WHERE `module_id` = :mid |
| 193 | AND `field_key` IN ('name', 'subject', 'title', 'task_name', 'project_name') |
| 194 | ORDER BY `sort_order` ASC |
| 195 | LIMIT 1"; |
| 196 | $stmt = $this->pdo->prepare($sql); |
| 197 | $stmt->execute([':mid' => $moduleId]); |
| 198 | $col = $stmt->fetchColumn(); |
| 199 | |
| 200 | if (is_string($col) && $col !== '') { |
| 201 | return $col; |
| 202 | } |
| 203 | } catch (PDOException) { |
| 204 | // Ignore exception in isolated test runs. |
| 205 | } |
| 206 | |
| 207 | return 'name'; |
| 208 | } |
| 209 | |
| 210 | /** |
| 211 | * Resolves summary fields to display on Kanban cards. |
| 212 | * |
| 213 | * @param array<string, mixed> $config Module configuration. |
| 214 | * @return list<array<string, mixed>> Summary field definitions. |
| 215 | */ |
| 216 | public function resolveSummaryFields(array $config): array |
| 217 | { |
| 218 | $moduleId = (int) ($config['mod_id'] ?? 0); |
| 219 | $statusCol = (string) $config['status_col']; |
| 220 | $nameCol = (string) $config['name_col']; |
| 221 | |
| 222 | if ($moduleId <= 0) { |
| 223 | return $this->getDefaultSummaryFields($statusCol); |
| 224 | } |
| 225 | |
| 226 | try { |
| 227 | // 1. Fetch fields explicitly marked with is_summary = 1 |
| 228 | $sql = "SELECT f.`field_key`, f.`label`, COALESCE(u.`name`, 'string_input') AS `uitype_name`, |
| 229 | f.`picklist_id`, f.`relation_module`, f.`relation_table`, |
| 230 | f.`relation_key`, f.`relation_label` |
| 231 | FROM `a_core_field_records` f |
| 232 | LEFT JOIN `a_core_uitype_records` u ON u.`id` = f.`uitype_id` |
| 233 | WHERE f.`module_id` = :mid AND f.`is_summary` = 1 AND f.`special_access` = 1 |
| 234 | ORDER BY f.`sort_order` ASC, f.`id` ASC"; |
| 235 | $stmt = $this->pdo->prepare($sql); |
| 236 | $stmt->execute([':mid' => $moduleId]); |
| 237 | $fields = $stmt->fetchAll(PDO::FETCH_ASSOC); |
| 238 | $selected = !empty($fields) ? $fields : $this->fetchFirstFilterVisibleFields($moduleId, $statusCol); |
| 239 | if (!empty($selected)) { |
| 240 | return $this->filterExcludedSummaryFields($selected, $nameCol, $statusCol); |
| 241 | } |
| 242 | } catch (PDOException) { |
| 243 | // Fallback for tests or setups without metadata tables |
| 244 | } |
| 245 | |
| 246 | return $this->getDefaultSummaryFields(); |
| 247 | } |
| 248 | |
| 249 | /** |
| 250 | * Default summary fields fallback when metadata tables are unavailable. |
| 251 | * |
| 252 | * @return list<array<string, mixed>> |
| 253 | */ |
| 254 | private function getDefaultSummaryFields(): array |
| 255 | { |
| 256 | return []; |
| 257 | } |
| 258 | |
| 259 | /** |
| 260 | * Filters out fields that already have dedicated display zones on the Kanban card. |
| 261 | * |
| 262 | * @param list<array<string, mixed>> $fields Candidate fields. |
| 263 | * @param string $nameCol Name column. |
| 264 | * @param string $statusCol Status column. |
| 265 | * @return list<array<string, mixed>> Filtered summary fields. |
| 266 | */ |
| 267 | private function filterExcludedSummaryFields(array $fields, string $nameCol, string $statusCol): array |
| 268 | { |
| 269 | $excluded = [ |
| 270 | $nameCol, |
| 271 | $statusCol, |
| 272 | 'id', |
| 273 | 'priority', |
| 274 | 'owner', |
| 275 | 'assigned_to', |
| 276 | 'user_id', |
| 277 | 'progress', |
| 278 | 'resolution_due', |
| 279 | 'target_end_date', |
| 280 | 'end_date', |
| 281 | 'due_date', |
| 282 | 'actual_end_date', |
| 283 | 'start_date', |
| 284 | 'ticket_no', |
| 285 | 'project_no', |
| 286 | 'task_no', |
| 287 | 'created_by', |
| 288 | 'created_at', |
| 289 | 'updated_at', |
| 290 | 'special_access', |
| 291 | 'record_status', |
| 292 | 'is_favorite', |
| 293 | 'is_pinned', |
| 294 | 'is_active', |
| 295 | 'co_owners', |
| 296 | ]; |
| 297 | |
| 298 | return array_values(array_filter($fields, static function (array $f) use ($excluded): bool { |
| 299 | return !in_array((string) ($f['field_key'] ?? ''), $excluded, true); |
| 300 | })); |
| 301 | } |
| 302 | |
| 303 | /** |
| 304 | * Fetches first filter visible fields as fallback summary fields. |
| 305 | * |
| 306 | * @param int $moduleId Module ID. |
| 307 | * @param string $statusCol Status column key to exclude. |
| 308 | * @return list<array<string, mixed>> Filtered field definitions. |
| 309 | */ |
| 310 | private function fetchFirstFilterVisibleFields(int $moduleId, string $statusCol): array |
| 311 | { |
| 312 | $sql = "SELECT `visible_fields` FROM `a_core_filter_records` |
| 313 | WHERE `module_id` = :mid |
| 314 | ORDER BY `is_default` DESC, `id` ASC |
| 315 | LIMIT 1"; |
| 316 | $stmt = $this->pdo->prepare($sql); |
| 317 | $stmt->execute([':mid' => $moduleId]); |
| 318 | $raw = $stmt->fetchColumn(); |
| 319 | |
| 320 | $keys = (is_string($raw) && $raw !== '') ? json_decode($raw, true) : null; |
| 321 | if (!is_array($keys)) { |
| 322 | return []; |
| 323 | } |
| 324 | |
| 325 | $excluded = [ |
| 326 | 'id', 'special_access', 'record_status', 'is_favorite', 'is_pinned', 'created_by', 'created_at', |
| 327 | 'updated_at', 'co_owners', $statusCol, |
| 328 | ]; |
| 329 | $targetKeys = array_values(array_diff($keys, $excluded)); |
| 330 | $slice = array_slice($targetKeys, 0, 5); |
| 331 | |
| 332 | if (empty($slice)) { |
| 333 | return []; |
| 334 | } |
| 335 | |
| 336 | $inClause = implode(',', array_fill(0, count($slice), '?')); |
| 337 | $fSql = "SELECT f.`field_key`, f.`label`, COALESCE(u.`name`, 'string_input') AS `uitype_name`, |
| 338 | f.`picklist_id`, f.`relation_module`, f.`relation_table`, |
| 339 | f.`relation_key`, f.`relation_label` |
| 340 | FROM `a_core_field_records` f |
| 341 | LEFT JOIN `a_core_uitype_records` u ON u.`id` = f.`uitype_id` |
| 342 | WHERE f.`module_id` = ? AND f.`field_key` IN ({$inClause}) AND f.`special_access` = 1 |
| 343 | ORDER BY f.`sort_order` ASC, f.`id` ASC"; |
| 344 | $stmtF = $this->pdo->prepare($fSql); |
| 345 | $stmtF->execute(array_merge([$moduleId], $slice)); |
| 346 | $fields = $stmtF->fetchAll(PDO::FETCH_ASSOC); |
| 347 | |
| 348 | return is_array($fields) ? $fields : []; |
| 349 | } |
| 350 | |
| 351 | /** |
| 352 | * Fetches picklist values representing Kanban columns. |
| 353 | * |
| 354 | * @param int $picklistId Picklist identifier. |
| 355 | * @return list<array<string, mixed>> List of column definitions. |
| 356 | */ |
| 357 | public function fetchPicklistColumns(int $picklistId): array |
| 358 | { |
| 359 | try { |
| 360 | $sql = "SELECT `value` AS `value_key`, `label`, |
| 361 | COALESCE(`color`, 'secondary') AS `badge_color`, `icon_class` |
| 362 | FROM `a_core_picklist_value_records` |
| 363 | WHERE `picklist_id` = :pid AND `is_active` = 1 |
| 364 | ORDER BY `sort_order` ASC, `id` ASC"; |
| 365 | |
| 366 | $stmt = $this->pdo->prepare($sql); |
| 367 | $stmt->execute([':pid' => $picklistId]); |
| 368 | |
| 369 | return $stmt->fetchAll(PDO::FETCH_ASSOC); |
| 370 | } catch (PDOException) { |
| 371 | $sqlLegacy = "SELECT `value_key`, `label`, `badge_color`, `icon_class` |
| 372 | FROM `a_core_picklist_values_records` |
| 373 | WHERE `picklist_id` = :pid AND `is_active` = 1 |
| 374 | ORDER BY `sort_order` ASC, `id` ASC"; |
| 375 | |
| 376 | $stmtLegacy = $this->pdo->prepare($sqlLegacy); |
| 377 | $stmtLegacy->execute([':pid' => $picklistId]); |
| 378 | |
| 379 | return $stmtLegacy->fetchAll(PDO::FETCH_ASSOC); |
| 380 | } |
| 381 | } |
| 382 | |
| 383 | /** |
| 384 | * Detects picklist ID for the module priority field if defined. |
| 385 | * |
| 386 | * @param int $moduleId Module ID. |
| 387 | * @return int Picklist ID or 0. |
| 388 | */ |
| 389 | private function detectPriorityPicklistId(int $moduleId): int |
| 390 | { |
| 391 | if ($moduleId <= 0) { |
| 392 | return 0; |
| 393 | } |
| 394 | |
| 395 | try { |
| 396 | $sql = "SELECT `picklist_id` FROM `a_core_field_records` |
| 397 | WHERE `module_id` = :mid AND `field_key` = 'priority' AND `special_access` = 1 |
| 398 | LIMIT 1"; |
| 399 | $stmt = $this->pdo->prepare($sql); |
| 400 | $stmt->execute([':mid' => $moduleId]); |
| 401 | $val = $stmt->fetchColumn(); |
| 402 | |
| 403 | return is_numeric($val) ? (int) $val : 0; |
| 404 | } catch (PDOException) { |
| 405 | return 0; |
| 406 | } |
| 407 | } |
| 408 | |
| 409 | /** |
| 410 | * Loads picklist definitions for summary badge and priority translation. |
| 411 | * |
| 412 | * @param list<int> $picklistIds List of picklist IDs. |
| 413 | * @return array<int, array<string, array{label: string, color: string, icon_class: string}>> |
| 414 | */ |
| 415 | private function loadPicklistValuesMap(array $picklistIds): array |
| 416 | { |
| 417 | $uniqueIds = array_values(array_unique(array_filter( |
| 418 | $picklistIds, |
| 419 | static fn(int $id): bool => $id > 0 |
| 420 | ))); |
| 421 | if (empty($uniqueIds)) { |
| 422 | return []; |
| 423 | } |
| 424 | |
| 425 | $inClause = implode(',', array_fill(0, count($uniqueIds), '?')); |
| 426 | $map = []; |
| 427 | |
| 428 | try { |
| 429 | $sql = "SELECT `picklist_id`, `value`, `label`, |
| 430 | COALESCE(`color`, 'secondary') AS `color`, |
| 431 | COALESCE(`icon_class`, 'bi bi-tag') AS `icon_class` |
| 432 | FROM `a_core_picklist_value_records` |
| 433 | WHERE `picklist_id` IN ({$inClause}) AND `is_active` = 1 |
| 434 | ORDER BY `sort_order` ASC, `id` ASC"; |
| 435 | $stmt = $this->pdo->prepare($sql); |
| 436 | $stmt->execute($uniqueIds); |
| 437 | while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { |
| 438 | $pid = (int) $row['picklist_id']; |
| 439 | $val = (string) $row['value']; |
| 440 | $map[$pid][$val] = [ |
| 441 | 'label' => (string) $row['label'], |
| 442 | 'color' => (string) $row['color'], |
| 443 | 'icon_class' => (string) $row['icon_class'], |
| 444 | ]; |
| 445 | } |
| 446 | } catch (PDOException) { |
| 447 | try { |
| 448 | $sqlLegacy = "SELECT `picklist_id`, `value_key` AS `value`, `label`, |
| 449 | COALESCE(`badge_color`, 'secondary') AS `color`, |
| 450 | COALESCE(`icon_class`, 'bi bi-tag') AS `icon_class` |
| 451 | FROM `a_core_picklist_values_records` |
| 452 | WHERE `picklist_id` IN ({$inClause}) AND `is_active` = 1 |
| 453 | ORDER BY `sort_order` ASC, `id` ASC"; |
| 454 | $stmt = $this->pdo->prepare($sqlLegacy); |
| 455 | $stmt->execute($uniqueIds); |
| 456 | while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { |
| 457 | $pid = (int) $row['picklist_id']; |
| 458 | $val = (string) $row['value']; |
| 459 | $map[$pid][$val] = [ |
| 460 | 'label' => (string) $row['label'], |
| 461 | 'color' => (string) $row['color'], |
| 462 | 'icon_class' => (string) $row['icon_class'], |
| 463 | ]; |
| 464 | } |
| 465 | } catch (PDOException) { |
| 466 | // Return empty map on error |
| 467 | } |
| 468 | } |
| 469 | |
| 470 | return $map; |
| 471 | } |
| 472 | |
| 473 | /** |
| 474 | * Loads referenced entity display labels for foreign key fields. |
| 475 | * |
| 476 | * @param list<array<string, mixed>> $summaryFields Summary fields list. |
| 477 | * @param list<array<string, mixed>> $rows Record rows. |
| 478 | * @return array<string, array<string, string>> Map of [table => [id => label]]. |
| 479 | */ |
| 480 | private function loadRelationLabelsMap(array $summaryFields, array $rows): array |
| 481 | { |
| 482 | $map = []; |
| 483 | if (empty($rows)) { |
| 484 | return $map; |
| 485 | } |
| 486 | |
| 487 | foreach ($summaryFields as $field) { |
| 488 | $table = (string) ($field['relation_table'] ?? ''); |
| 489 | $labels = $this->fetchFieldRelationLabels($field, $rows); |
| 490 | if (!empty($labels) && $table !== '') { |
| 491 | $map[$table] = array_replace($map[$table] ?? [], $labels); |
| 492 | } |
| 493 | } |
| 494 | |
| 495 | return $map; |
| 496 | } |
| 497 | |
| 498 | /** |
| 499 | * Fetches relation labels for a single summary relation field. |
| 500 | * |
| 501 | * @param array<string, mixed> $field Field definition. |
| 502 | * @param list<array<string, mixed>> $rows Data rows. |
| 503 | * @return array<string, string> Map of [id => label]. |
| 504 | */ |
| 505 | private function fetchFieldRelationLabels(array $field, array $rows): array |
| 506 | { |
| 507 | $table = (string) ($field['relation_table'] ?? ''); |
| 508 | $keyCol = (string) ($field['relation_key'] ?? 'id'); |
| 509 | $labelCol = (string) ($field['relation_label'] ?? 'name'); |
| 510 | $fieldKey = (string) ($field['field_key'] ?? ''); |
| 511 | |
| 512 | if ($table === '' || $fieldKey === '' |
| 513 | || !preg_match(self::IDENTIFIER_PATTERN, $table) |
| 514 | || !preg_match(self::IDENTIFIER_PATTERN, $keyCol) |
| 515 | || !preg_match(self::IDENTIFIER_PATTERN, $labelCol)) { |
| 516 | return []; |
| 517 | } |
| 518 | |
| 519 | $rawIds = []; |
| 520 | foreach ($rows as $r) { |
| 521 | if (isset($r[$fieldKey]) && $r[$fieldKey] !== '') { |
| 522 | $rawIds[] = $r[$fieldKey]; |
| 523 | } |
| 524 | } |
| 525 | $uniqueIds = array_values(array_unique($rawIds)); |
| 526 | if (empty($uniqueIds)) { |
| 527 | return []; |
| 528 | } |
| 529 | |
| 530 | $inClause = implode(',', array_fill(0, count($uniqueIds), '?')); |
| 531 | $sql = "SELECT `{$keyCol}` AS `rel_id`, `{$labelCol}` AS `rel_label` |
| 532 | FROM `{$table}` |
| 533 | WHERE `{$keyCol}` IN ({$inClause})"; |
| 534 | |
| 535 | $labels = []; |
| 536 | try { |
| 537 | $stmt = $this->pdo->prepare($sql); |
| 538 | $stmt->execute($uniqueIds); |
| 539 | while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { |
| 540 | $labels[(string) $row['rel_id']] = (string) $row['rel_label']; |
| 541 | } |
| 542 | } catch (PDOException) { |
| 543 | // Silently skip if table or column is missing |
| 544 | } |
| 545 | |
| 546 | return $labels; |
| 547 | } |
| 548 | |
| 549 | /** |
| 550 | * Resolves human label and theme color for record priority. |
| 551 | * |
| 552 | * @param string|null $priority Raw priority code. |
| 553 | * @param array<string, array{label: string, color: string, icon_class: string}> $picklist Priority picklist. |
| 554 | * @return array{label: string, color: string} |
| 555 | */ |
| 556 | private function resolvePriorityMeta(?string $priority, array $picklist): array |
| 557 | { |
| 558 | if ($priority === null || $priority === '') { |
| 559 | return ['label' => 'Normalny', 'color' => 'secondary']; |
| 560 | } |
| 561 | |
| 562 | if (isset($picklist[$priority])) { |
| 563 | return [ |
| 564 | 'label' => (string) $picklist[$priority]['label'], |
| 565 | 'color' => (string) ($picklist[$priority]['color'] ?? 'secondary'), |
| 566 | ]; |
| 567 | } |
| 568 | |
| 569 | $map = [ |
| 570 | 'p1_critical' => ['label' => 'Critical (P1)', 'color' => 'danger'], |
| 571 | 'p2_high' => ['label' => 'High (P2)', 'color' => 'orange'], |
| 572 | 'p3_medium' => ['label' => 'Medium (P3)', 'color' => 'yellow'], |
| 573 | 'p4_low' => ['label' => 'Low (P4)', 'color' => 'azure'], |
| 574 | 'critical' => ['label' => 'Critical', 'color' => 'danger'], |
| 575 | 'urgent' => ['label' => 'Urgent', 'color' => 'danger'], |
| 576 | 'high' => ['label' => 'High', 'color' => 'orange'], |
| 577 | 'normal' => ['label' => 'Normal', 'color' => 'azure'], |
| 578 | 'medium' => ['label' => 'Medium', 'color' => 'yellow'], |
| 579 | 'low' => ['label' => 'Low', 'color' => 'secondary'], |
| 580 | ]; |
| 581 | |
| 582 | return $map[strtolower($priority)] ?? [ |
| 583 | 'label' => ucfirst(str_replace('_', ' ', $priority)), |
| 584 | 'color' => 'secondary', |
| 585 | ]; |
| 586 | } |
| 587 | |
| 588 | /** |
| 589 | * Resolves semantic Bootstrap Icon for related module entity. |
| 590 | * |
| 591 | * @param string $module Related module name. |
| 592 | * @return string Icon class name. |
| 593 | */ |
| 594 | private function resolveRelationIcon(string $module): string |
| 595 | { |
| 596 | return match ($module) { |
| 597 | 'companies', 'accounts' => 'bi bi-building', |
| 598 | 'contacts', 'users', 'system_users' => 'bi bi-person', |
| 599 | 'system_administrators' => 'bi bi-shield-lock', |
| 600 | 'tickets' => 'bi bi-ticket-detailed', |
| 601 | 'contracts' => 'bi bi-file-earmark-text', |
| 602 | 'projects' => 'bi bi-kanban', |
| 603 | 'tasks', 'project_tasks' => 'bi bi-check2-square', |
| 604 | 'products' => 'bi bi-box-seam', |
| 605 | 'services' => 'bi bi-tools', |
| 606 | 'documents' => 'bi bi-file-earmark-arrow-down', |
| 607 | default => 'bi bi-link-45deg', |
| 608 | }; |
| 609 | } |
| 610 | |
| 611 | /** |
| 612 | * Fetches active records for Kanban board cards. |
| 613 | * |
| 614 | * @param array<string, mixed> $cfg Module configuration. |
| 615 | * @param list<array<string, mixed>> $summaryFields Resolved card summary fields. |
| 616 | * @return list<array<string, mixed>> Records list. |
| 617 | */ |
| 618 | public function fetchModuleRecords(array $cfg, array $summaryFields): array |
| 619 | { |
| 620 | $table = (string) $cfg['table']; |
| 621 | $statusCol = (string) $cfg['status_col']; |
| 622 | $nameCol = (string) $cfg['name_col']; |
| 623 | $moduleId = (int) ($cfg['mod_id'] ?? 0); |
| 624 | |
| 625 | $tableCols = SqlTableSchemaHelper::getTableColumns($this->pdo, $table); |
| 626 | $colNames = $tableCols !== [] ? array_keys($tableCols) : ['id', 'special_access', 'owner']; |
| 627 | $colSelect = implode(', ', array_map(static fn(string $c): string => "r.`{$c}`", $colNames)); |
| 628 | |
| 629 | $usersTable = str_starts_with($table, 'c_') ? 'c_mod_users_records' : 'a_mod_users_records'; |
| 630 | $sql = "SELECT {$colSelect}, |
| 631 | COALESCE(u.`c_cn`, u.`username`, 'Brak') AS `owner_name`, |
| 632 | COALESCE(u.`avatar_url`, '') AS `owner_avatar` |
| 633 | FROM `{$table}` r |
| 634 | LEFT JOIN `{$usersTable}` u ON u.`id` = r.`owner` |
| 635 | ORDER BY r.`id` DESC |
| 636 | LIMIT 300"; |
| 637 | |
| 638 | try { |
| 639 | $stmt = $this->pdo->query($sql); |
| 640 | $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); |
| 641 | } catch (PDOException) { |
| 642 | try { |
| 643 | $sqlLegacy = "SELECT {$colSelect}, |
| 644 | COALESCE(u.`username`, 'Brak') AS `owner_name`, |
| 645 | COALESCE(u.`avatar`, '') AS `owner_avatar` |
| 646 | FROM `{$table}` r |
| 647 | LEFT JOIN `{$usersTable}` u ON u.`id` = r.`owner` |
| 648 | ORDER BY r.`id` DESC |
| 649 | LIMIT 300"; |
| 650 | $stmt = $this->pdo->query($sqlLegacy); |
| 651 | $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); |
| 652 | } catch (PDOException) { |
| 653 | // Simplified fallback for basic SQLite unit tests |
| 654 | $sqlSimple = "SELECT {$colSelect} FROM `{$table}` r " |
| 655 | . "ORDER BY r.`id` DESC LIMIT 300"; |
| 656 | $stmt = $this->pdo->query($sqlSimple); |
| 657 | $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); |
| 658 | } |
| 659 | } |
| 660 | |
| 661 | $priorityPicklistId = $this->detectPriorityPicklistId($moduleId); |
| 662 | $allPicklistIds = array_filter(array_map( |
| 663 | static fn(array $f): int => (int) ($f['picklist_id'] ?? 0), |
| 664 | $summaryFields |
| 665 | )); |
| 666 | if ($priorityPicklistId > 0) { |
| 667 | $allPicklistIds[] = $priorityPicklistId; |
| 668 | } |
| 669 | |
| 670 | $picklistMap = $this->loadPicklistValuesMap($allPicklistIds); |
| 671 | $relationMap = $this->loadRelationLabelsMap($summaryFields, $rows); |
| 672 | $priorityPicklist = $priorityPicklistId > 0 ? ($picklistMap[$priorityPicklistId] ?? []) : []; |
| 673 | |
| 674 | $result = []; |
| 675 | $now = date('Y-m-d H:i:s'); |
| 676 | |
| 677 | foreach ($rows as $r) { |
| 678 | $status = (string) ($r[$statusCol] ?? 'planned'); |
| 679 | $dueDate = $this->extractDueDate($r); |
| 680 | $isOverdue = $dueDate !== null && $dueDate < $now && !in_array($status, self::CLOSED_STATUSES, true); |
| 681 | $prioRaw = isset($r['priority']) ? (string) $r['priority'] : null; |
| 682 | $prioMeta = $this->resolvePriorityMeta($prioRaw, $priorityPicklist); |
| 683 | |
| 684 | $card = [ |
| 685 | 'id' => (int) $r['id'], |
| 686 | 'name' => (string) ($r[$nameCol] ?? ('#' . $r['id'])), |
| 687 | 'status' => $status, |
| 688 | 'number' => (string) ($r['ticket_no'] ?? ($r['project_no'] ?? ('#' . $r['id']))), |
| 689 | 'priority' => $prioRaw, |
| 690 | 'priority_label' => $prioMeta['label'], |
| 691 | 'priority_color' => $prioMeta['color'], |
| 692 | 'progress' => isset($r['progress']) ? (int) $r['progress'] : null, |
| 693 | 'owner_name' => (string) ($r['owner_name'] ?? 'Brak'), |
| 694 | 'owner_avatar' => (string) ($r['owner_avatar'] ?? ''), |
| 695 | 'due_date' => $dueDate, |
| 696 | 'is_overdue' => $isOverdue, |
| 697 | 'summary_badges' => $this->buildSummaryBadges($r, $summaryFields, $picklistMap, $relationMap), |
| 698 | ]; |
| 699 | |
| 700 | $result[] = $card; |
| 701 | } |
| 702 | |
| 703 | return $result; |
| 704 | } |
| 705 | |
| 706 | /** |
| 707 | * Extracts record due date from available date columns. |
| 708 | * |
| 709 | * @param array<string, mixed> $row Database row. |
| 710 | * @return string|null Due date string or null. |
| 711 | */ |
| 712 | private function extractDueDate(array $row): ?string |
| 713 | { |
| 714 | $candidates = ['resolution_due', 'target_end_date', 'end_date', 'due_date']; |
| 715 | foreach ($candidates as $col) { |
| 716 | if (isset($row[$col]) && is_string($row[$col]) && $row[$col] !== '') { |
| 717 | return $row[$col]; |
| 718 | } |
| 719 | } |
| 720 | |
| 721 | return null; |
| 722 | } |
| 723 | |
| 724 | /** |
| 725 | * Builds summary badges displayed on the card. |
| 726 | * |
| 727 | * @param array<string, mixed> $row Database row. |
| 728 | * @param list<array<string, mixed>> $summaryFields Field defs. |
| 729 | * @param array<int, array<string, array{label: string, color: string, icon_class: string}>> $picklistMap Picklists. |
| 730 | * @param array<string, array<string, string>> $relationMap Relations. |
| 731 | * @return list<array<string, string>> Summary badges list. |
| 732 | */ |
| 733 | private function buildSummaryBadges( |
| 734 | array $row, |
| 735 | array $summaryFields, |
| 736 | array $picklistMap, |
| 737 | array $relationMap |
| 738 | ): array { |
| 739 | $badges = []; |
| 740 | foreach ($summaryFields as $field) { |
| 741 | $key = (string) $field['field_key']; |
| 742 | if (!array_key_exists($key, $row)) { |
| 743 | continue; |
| 744 | } |
| 745 | |
| 746 | $val = $row[$key]; |
| 747 | if ($val === null || $val === '') { |
| 748 | continue; |
| 749 | } |
| 750 | |
| 751 | $uitype = (string) ($field['uitype_name'] ?? 'string_input'); |
| 752 | $label = (string) $field['label']; |
| 753 | $pid = (int) ($field['picklist_id'] ?? 0); |
| 754 | $relTable = (string) ($field['relation_table'] ?? ''); |
| 755 | $relModule = (string) ($field['relation_module'] ?? ''); |
| 756 | |
| 757 | $displayVal = (string) $val; |
| 758 | $color = 'secondary'; |
| 759 | $icon = 'bi bi-tag'; |
| 760 | |
| 761 | if ($pid > 0 && isset($picklistMap[$pid][(string) $val])) { |
| 762 | $pMeta = $picklistMap[$pid][(string) $val]; |
| 763 | $displayVal = (string) $pMeta['label']; |
| 764 | $color = (string) ($pMeta['color'] ?? 'secondary'); |
| 765 | $icon = (string) ($pMeta['icon_class'] ?? 'bi bi-tag'); |
| 766 | } elseif ($relTable !== '' && isset($relationMap[$relTable][(string) $val])) { |
| 767 | $displayVal = (string) $relationMap[$relTable][(string) $val]; |
| 768 | $icon = $this->resolveRelationIcon($relModule); |
| 769 | $color = 'azure'; |
| 770 | } elseif (in_array($uitype, ['date_picker', 'datetime_picker'], true)) { |
| 771 | $displayVal = substr((string) $val, 0, 10); |
| 772 | $icon = 'bi bi-calendar3'; |
| 773 | } |
| 774 | |
| 775 | $badges[] = [ |
| 776 | 'key' => $key, |
| 777 | 'label' => $label, |
| 778 | 'value' => $displayVal, |
| 779 | 'raw_value' => (string) $val, |
| 780 | 'icon' => $icon, |
| 781 | 'color' => $color, |
| 782 | 'uitype' => $uitype, |
| 783 | ]; |
| 784 | } |
| 785 | |
| 786 | return $badges; |
| 787 | } |
| 788 | |
| 789 | /** |
| 790 | * Groups raw records into respective status column arrays. |
| 791 | * |
| 792 | * @param list<array<string, mixed>> $columns Column definitions. |
| 793 | * @param list<array<string, mixed>> $records Raw records list. |
| 794 | * @return list<array<string, mixed>> Grouped columns structure. |
| 795 | */ |
| 796 | public function groupRecordsIntoColumns(array $columns, array $records): array |
| 797 | { |
| 798 | $buckets = []; |
| 799 | foreach ($columns as $col) { |
| 800 | $key = (string) $col['value_key']; |
| 801 | $buckets[$key] = [ |
| 802 | 'key' => $key, |
| 803 | 'label' => (string) $col['label'], |
| 804 | 'badge_color' => (string) ($col['badge_color'] ?? 'secondary'), |
| 805 | 'icon_class' => (string) ($col['icon_class'] ?? 'bi bi-kanban'), |
| 806 | 'count' => 0, |
| 807 | 'items' => [], |
| 808 | ]; |
| 809 | } |
| 810 | |
| 811 | foreach ($records as $card) { |
| 812 | $statusKey = (string) ($card['status'] ?? 'planned'); |
| 813 | if (!isset($buckets[$statusKey])) { |
| 814 | $buckets[$statusKey] = [ |
| 815 | 'key' => $statusKey, |
| 816 | 'label' => ucfirst($statusKey), |
| 817 | 'badge_color' => 'secondary', |
| 818 | 'icon_class' => 'bi bi-question-circle', |
| 819 | 'count' => 0, |
| 820 | 'items' => [], |
| 821 | ]; |
| 822 | } |
| 823 | |
| 824 | $buckets[$statusKey]['items'][] = $card; |
| 825 | $buckets[$statusKey]['count']++; |
| 826 | } |
| 827 | |
| 828 | return array_values($buckets); |
| 829 | } |
| 830 | } |