handleImportStart(); return; } if ($action === 'process-chunk' || $action === 'import-status') { $this->handleImportChunk(); return; } if ($action === 'clear') { $this->handleClear(); return; } } $action = (string) ($_GET['action'] ?? ''); if ($action === 'grid-data' || $action === 'datatable' || $action === 'table-data') { $this->handleTableData(); return; } if ($action === 'export-run') { $this->handleExportRun(); return; } if ($action === 'export-status') { $this->handleExportStatus(); return; } if ($action === 'export-download') { $this->handleExportDownload(); return; } $view = (string) ($_GET['view'] ?? ''); if ($view === 'table') { $this->renderTable(); return; } if ($view === 'export') { $this->renderExport(); return; } $this->renderIndex(); } private function handleImportStart(): void { try { $upload = $this->getUpload('spreadsheet'); $token = bin2hex(random_bytes(16)); $targetDir = sys_get_temp_dir() . '/warner-imports'; if (!is_dir($targetDir) && !mkdir($targetDir, 0777, true) && !is_dir($targetDir)) { throw new RuntimeException('Unable to create a temporary upload directory.'); } $extension = strtolower(pathinfo($upload['name'], PATHINFO_EXTENSION)); $targetPath = rtrim($targetDir, '/\\') . '/' . $token . '.' . $extension; if (!move_uploaded_file($upload['tmp_name'], $targetPath)) { throw new RuntimeException('Unable to store the uploaded file for chunked import.'); } $this->jobStore->save($token, [ 'token' => $token, 'status' => 'ready', 'filePath' => $targetPath, 'sourceFilename' => $upload['name'], 'sourceExtension' => strtolower(pathinfo($upload['name'], PATHINFO_EXTENSION)), 'importedRows' => 0, 'warningsCount' => 0, 'warnings' => [], 'processedRows' => 0, 'totalRows' => 0, 'startedAt' => time(), ]); $message = 'Upload received. Processing will continue in chunks.'; if ($this->isAjaxRequest()) { $this->respondJson([ 'ok' => true, 'message' => $message, 'token' => $token, 'offset' => 0, 'total' => 0, 'done' => false, 'chunkSize' => $this->importService->chunkSize(), ], 200); return; } $this->flash('success', $message); $this->redirectHome(); } catch (Throwable $throwable) { if ($this->isAjaxRequest()) { $this->respondJson([ 'ok' => false, 'message' => $throwable->getMessage(), ], 400); return; } $this->flash('danger', $throwable->getMessage()); $this->redirectHome(); } } private function handleImportChunk(): void { try { @set_time_limit(0); $token = (string) ($_POST['token'] ?? $_GET['token'] ?? ''); if ($token === '') { throw new RuntimeException('Missing import token.'); } $state = $this->jobStore->get($token); if ($state === null) { $this->respondJson([ 'ok' => false, 'done' => true, 'status' => 'missing', 'message' => 'Import job not found or already finished.', ], 404); return; } $state = $this->importService->processJobState($state); $this->jobStore->save($token, $state); if (in_array((string) ($state['status'] ?? ''), ['completed', 'error'], true)) { $this->cleanupImportJob($state); } $progress = $this->calculateProgress($state); $message = (string) ($state['message'] ?? 'Processing import chunk.'); if (($state['status'] ?? '') === 'ready') { $message = 'Import is ready to process.'; } elseif (($state['status'] ?? '') === 'cached') { $message = sprintf( 'Workbook cached. %s row(s) detected and ready for import.', number_format((int) ($state['totalRows'] ?? 0)) ); } elseif (($state['status'] ?? '') === 'running') { $message = sprintf( 'Processed %d row(s) so far. Imported %d row(s).', (int) ($state['processedRows'] ?? 0), (int) ($state['importedRows'] ?? 0) ); } $totalRows = (int) ($state['totalRows'] ?? 0); $processedRows = (int) ($state['processedRows'] ?? 0); if ($this->isAjaxRequest()) { $this->respondJson([ 'ok' => ($state['status'] ?? '') !== 'error', 'done' => ($state['status'] ?? '') === 'completed', 'status' => $state['status'] ?? 'unknown', 'message' => $message, 'token' => $token, 'importedRows' => (int) ($state['importedRows'] ?? 0), 'warningsCount' => (int) ($state['warningsCount'] ?? 0), 'warnings' => array_values(array_slice(is_array($state['warnings'] ?? null) ? $state['warnings'] : [], 0, 20)), 'processedRows' => $processedRows, 'totalRows' => $totalRows, 'offset' => min($processedRows, $totalRows), 'total' => $totalRows, 'processed' => (int) ($state['importedRows'] ?? 0), 'skipped' => (int) ($state['warningsCount'] ?? 0), 'progressPercent' => $progress, 'chunkSize' => $this->importService->chunkSize(), ], ($state['status'] ?? '') === 'error' ? 400 : 200); return; } $this->flash(($state['status'] ?? '') === 'error' ? 'danger' : 'success', $message); $this->redirectHome(); } catch (Throwable $throwable) { if ($this->isAjaxRequest()) { $this->respondJson([ 'ok' => false, 'done' => true, 'message' => $throwable->getMessage(), ], 400); return; } $this->flash('danger', $throwable->getMessage()); $this->redirectHome(); } } private function handleClear(): void { try { $this->importService->clear(); if ($this->isAjaxRequest()) { $this->respondJson([ 'ok' => true, 'message' => 'Imported data cleared.', ], 200); return; } $this->flash('success', 'Imported data cleared.'); $this->redirectHome(); } catch (Throwable $throwable) { if ($this->isAjaxRequest()) { $this->respondJson([ 'ok' => false, 'message' => $throwable->getMessage(), ], 400); return; } $this->flash('danger', $throwable->getMessage()); $this->redirectHome(); } } private function renderIndex(): void { $latestBatch = $this->importService->latestBatch(); $previewLimit = 20; $rows = []; $tableHeaders = []; $latestBatchRowCount = 0; if ($latestBatch !== null) { $batchId = (int) $latestBatch['id']; $latestBatchRowCount = $this->importService->countRowsForBatch($batchId); $rows = $this->importService->getPreview($batchId, $previewLimit, 0); $tableHeaders = array_merge($latestBatch['original_headers'], $latestBatch['calculated_headers']); } $flash = $this->consumeFlash(); echo $this->view->render('index', [ 'appName' => app_config('app.name', 'Spreadsheet Importer'), 'flash' => $flash, 'latestBatch' => $latestBatch, 'latestBatchRowCount' => $latestBatchRowCount, 'rows' => $rows, 'tableHeaders' => $tableHeaders, 'previewLimit' => $previewLimit, 'requiredColumns' => $this->importService->requiredColumns(), 'currentView' => 'preview', 'homeUrl' => '?view=preview', 'tableUrl' => '?view=table', 'exportUrl' => '?view=export', ]); } private function renderTable(): void { $latestBatch = $this->importService->latestBatch(); $tableHeaders = []; $latestBatchRowCount = 0; if ($latestBatch !== null) { $batchId = (int) $latestBatch['id']; $latestBatchRowCount = $this->importService->countRowsForBatch($batchId); $tableHeaders = array_merge($latestBatch['original_headers'], $latestBatch['calculated_headers']); } $flash = $this->consumeFlash(); echo $this->view->render('table', [ 'appName' => app_config('app.name', 'Spreadsheet Importer'), 'flash' => $flash, 'latestBatch' => $latestBatch, 'latestBatchRowCount' => $latestBatchRowCount, 'tableHeaders' => $tableHeaders, 'requiredColumns' => $this->importService->requiredColumns(), 'currentView' => 'table', 'homeUrl' => '?view=preview', 'tableUrl' => '?view=table', 'exportUrl' => '?view=export', ]); } private function renderExport(): void { $latestBatch = $this->importService->latestBatch(); $latestBatchRowCount = $latestBatch !== null ? $this->importService->countRowsForBatch((int) $latestBatch['id']) : 0; $flash = $this->consumeFlash(); echo $this->view->render('export', [ 'appName' => app_config('app.name', 'Spreadsheet Importer'), 'flash' => $flash, 'latestBatch' => $latestBatch, 'latestBatchRowCount' => $latestBatchRowCount, 'currentView' => 'export', 'homeUrl' => '?view=preview', 'tableUrl' => '?view=table', 'exportUrl' => '?view=export', 'exportRunUrl' => '?action=export-run', 'exportStatusUrl' => '?action=export-status', 'exportDownloadUrl' => '?action=export-download', ]); } private const EXPORT_FONT_NAME = 'Verdana'; private const EXPORT_FONT_SIZE = 8; private const EXPORT_ROW_HEIGHT = 16.0; private const EXPORT_COLUMN_WIDTH = 10.0; private const EXPORT_HEADER_TEXT_COLOR = '1D4072'; private const EXPORT_HEADER_ACCENT_BG = 'EFD6ED'; private const EXPORT_HEADER_DEFAULT_BG = 'D6E1EF'; private const EXPORT_CURRENCY_HEADER = 'Net Value USD'; private const EXPORT_CURRENCY_FORMAT = '#,##0.00;(#,##0.00)'; private const EXPORT_ACCENT_HEADERS = [ 'US/ex-US sale', 'Actual/Accrual', 'Contracting Party Filtered', 'String', '2110 Applicable X201', 'Class', ]; /** * @return array */ private function resolveExportHeaders(array $tableHeaders): array { $headers = array_values(array_filter($tableHeaders, static fn (string $header): bool => $header !== 'x')); foreach (['US/ex-US sale', 'Actual/Accrual'] as $header) { $fromIndex = array_search($header, $headers, true); if ($fromIndex === false) { continue; } array_splice($headers, $fromIndex, 1); $dspIndex = array_search('DSP', $headers, true); array_splice($headers, $dspIndex === false ? count($headers) : $dspIndex, 0, [$header]); } return $headers; } private function buildExportHeaderStyle(string $backgroundColor): Style { $style = new Style(); $style->setBackgroundColor($backgroundColor); $style->setFontColor(self::EXPORT_HEADER_TEXT_COLOR); $style->setFontBold(); $style->setFontName(self::EXPORT_FONT_NAME); $style->setFontSize(self::EXPORT_FONT_SIZE); return $style; } private function buildExportDataStyle(): Style { $style = new Style(); $style->setFontName(self::EXPORT_FONT_NAME); $style->setFontSize(self::EXPORT_FONT_SIZE); return $style; } private function buildExportCurrencyCellStyle(): Style { $style = new Style(); $style->setFormat(self::EXPORT_CURRENCY_FORMAT); return $style; } private function exportFilePath(string $token): string { $safeToken = preg_replace('/[^a-zA-Z0-9_-]/', '', $token); if ($safeToken === '') { throw new RuntimeException('Invalid export token.'); } $dir = sys_get_temp_dir() . '/warner-exports'; if (!is_dir($dir) && !mkdir($dir, 0777, true) && !is_dir($dir)) { throw new RuntimeException('Unable to create the export storage directory.'); } return $dir . '/' . $safeToken . '.xlsx'; } private function handleExportRun(): void { $token = (string) ($_GET['token'] ?? ''); // Release the session file lock immediately: this request runs for // the whole export, and PHP's default session handler otherwise // blocks every other request (including the status polls) from the // same browser session until this one finishes. if (session_status() === PHP_SESSION_ACTIVE) { session_write_close(); } try { if ($token === '') { throw new RuntimeException('Missing export token.'); } $latestBatch = $this->importService->latestBatch(); if ($latestBatch === null) { throw new RuntimeException('There is no imported data to export yet.'); } $batchId = (int) $latestBatch['id']; $totalRows = $this->importService->countRowsForBatch($batchId); $tableHeaders = $this->resolveExportHeaders( array_merge($latestBatch['original_headers'], $latestBatch['calculated_headers']) ); $this->exportJobStore->save($token, [ 'status' => 'running', 'processedRows' => 0, 'totalRows' => $totalRows, 'message' => 'Starting export…', ]); @set_time_limit(0); @ignore_user_abort(true); $filePath = $this->exportFilePath($token); $writer = new Writer(); $writer->getOptions()->DEFAULT_ROW_HEIGHT = self::EXPORT_ROW_HEIGHT; $writer->getOptions()->DEFAULT_COLUMN_WIDTH = self::EXPORT_COLUMN_WIDTH; $writer->openToFile($filePath); // Leave the first two rows blank before the header row and data. $writer->addRow(Row::fromValues([])); $writer->addRow(Row::fromValues([])); $accentHeaderStyle = $this->buildExportHeaderStyle(self::EXPORT_HEADER_ACCENT_BG); $defaultHeaderStyle = $this->buildExportHeaderStyle(self::EXPORT_HEADER_DEFAULT_BG); $headerColumnStyles = array_map( static fn (string $header): Style => in_array($header, self::EXPORT_ACCENT_HEADERS, true) ? $accentHeaderStyle : $defaultHeaderStyle, $tableHeaders ); $writer->addRow(Row::fromValuesWithStyles($tableHeaders, null, $headerColumnStyles)); $dataStyle = $this->buildExportDataStyle(); $currencyIndex = array_search(self::EXPORT_CURRENCY_HEADER, $tableHeaders, true); $dataColumnStyles = $currencyIndex === false ? [] : [$currencyIndex => $this->buildExportCurrencyCellStyle()]; $chunkSize = 2000; $offset = 0; do { $rows = $this->importService->getExportRows($batchId, $chunkSize, $offset); foreach ($rows as $row) { $values = array_map( function (string $header) use ($row): string|float { $value = $row[$header] ?? ''; if ($header === self::EXPORT_CURRENCY_HEADER && is_numeric($value)) { return (float) $value; } return (string) $value; }, $tableHeaders ); $writer->addRow(Row::fromValuesWithStyles($values, $dataStyle, $dataColumnStyles)); } $offset += $chunkSize; $this->exportJobStore->save($token, [ 'status' => 'running', 'processedRows' => min($offset, $totalRows), 'totalRows' => $totalRows, 'message' => sprintf('Exported %d of %d row(s)…', min($offset, $totalRows), $totalRows), ]); } while (count($rows) === $chunkSize); $writer->close(); $this->exportJobStore->save($token, [ 'status' => 'completed', 'processedRows' => $totalRows, 'totalRows' => $totalRows, 'message' => 'Export complete.', ]); $this->respondJson(['ok' => true], 200); } catch (Throwable $throwable) { if ($token !== '') { $this->exportJobStore->save($token, [ 'status' => 'error', 'processedRows' => 0, 'totalRows' => 0, 'message' => $throwable->getMessage(), ]); } $this->respondJson(['ok' => false, 'message' => $throwable->getMessage()], 400); } } private function handleExportStatus(): void { if (session_status() === PHP_SESSION_ACTIVE) { session_write_close(); } $token = (string) ($_GET['token'] ?? ''); $state = $token !== '' ? $this->exportJobStore->get($token) : null; if ($state === null) { $this->respondJson(['status' => 'unknown'], 404); return; } $status = (string) ($state['status'] ?? 'unknown'); $totalRows = (int) ($state['totalRows'] ?? 0); $processedRows = (int) ($state['processedRows'] ?? 0); $progressPercent = $status === 'completed' ? 100 : ($totalRows > 0 ? (int) min(99, round(($processedRows / $totalRows) * 100)) : 0); $this->respondJson([ 'status' => $status, 'processedRows' => $processedRows, 'totalRows' => $totalRows, 'progressPercent' => $progressPercent, 'message' => (string) ($state['message'] ?? ''), 'downloadUrl' => $status === 'completed' ? '?action=export-download&token=' . rawurlencode($token) : null, ], 200); } private function handleExportDownload(): void { if (session_status() === PHP_SESSION_ACTIVE) { session_write_close(); } $token = (string) ($_GET['token'] ?? ''); $state = $token !== '' ? $this->exportJobStore->get($token) : null; if ($state === null || ($state['status'] ?? '') !== 'completed') { http_response_code(404); echo 'Export not found or not ready yet.'; return; } $filePath = $this->exportFilePath($token); if (!is_file($filePath)) { http_response_code(404); echo 'Export file is no longer available.'; return; } $filename = 'warner-export-' . date('Y-m-d-His') . '.xlsx'; header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment; filename="' . $filename . '"'); header('Content-Length: ' . filesize($filePath)); header('Cache-Control: max-age=0'); readfile($filePath); @unlink($filePath); $this->exportJobStore->delete($token); } private function handleTableData(): void { try { $latestBatch = $this->importService->latestBatch(); if ($latestBatch === null) { if (array_key_exists('distinctColumn', $_GET)) { $this->respondJson([ 'values' => [], ], 200); return; } $this->respondJson([ 'rows' => [], 'lastRow' => 0, ], 200); return; } $batchId = (int) $latestBatch['id']; $tableHeaders = array_merge($latestBatch['original_headers'], $latestBatch['calculated_headers']); $distinctColumn = trim((string) ($_GET['distinctColumn'] ?? '')); if ($distinctColumn !== '' || array_key_exists('distinctColumn', $_GET)) { $distinctSearch = trim((string) ($_GET['distinctSearch'] ?? '')); $this->respondJson([ 'values' => $this->importService->getDistinctValuesForBatchGrid( $batchId, $tableHeaders, $distinctColumn, $distinctSearch, 500 ), ], 200); return; } $start = max(0, (int) ($_GET['startRow'] ?? $_GET['start'] ?? 0)); $end = max($start + 1, (int) ($_GET['endRow'] ?? ($start + 100))); $length = min(200, max(1, $end - $start)); $search = trim((string) ($_GET['search'] ?? '')); $sortModel = $this->decodeGridPayload((string) ($_GET['sortModel'] ?? '[]')); $filterModel = $this->decodeGridPayload((string) ($_GET['filterModel'] ?? '{}')); $recordsTotal = $this->importService->countRowsForBatch($batchId); $recordsFiltered = ($search !== '' || $filterModel !== []) ? $this->importService->countGridRows($batchId, $search, $tableHeaders, $filterModel) : $recordsTotal; $pageRows = $this->importService->getGridRows( $batchId, $length, $start, $search, $tableHeaders, $filterModel, $sortModel ); $this->respondJson([ 'rows' => $pageRows, 'lastRow' => $recordsFiltered, ], 200); } catch (Throwable $throwable) { $this->respondJson([ 'rows' => [], 'lastRow' => 0, 'error' => $throwable->getMessage(), ], 500); } } private function getUpload(string $fieldName): array { if (!isset($_FILES[$fieldName])) { throw new RuntimeException('Please choose an Excel file to upload.'); } $upload = $_FILES[$fieldName]; if (!is_array($upload) || ($upload['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) { throw new RuntimeException('Please choose an Excel file to upload.'); } $name = (string) $upload['name']; $tmpName = (string) $upload['tmp_name']; $extension = strtolower(pathinfo($name, PATHINFO_EXTENSION)); if (!in_array($extension, ['xls', 'xlsx'], true)) { throw new RuntimeException('The uploaded file must be an .xls or .xlsx Excel workbook.'); } if (!is_uploaded_file($tmpName)) { throw new RuntimeException('The uploaded file could not be verified.'); } return [ 'name' => $name, 'tmp_name' => $tmpName, ]; } private function respondJson(array $payload, int $statusCode): void { http_response_code($statusCode); header('Content-Type: application/json; charset=utf-8'); echo json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES); } private function isAjaxRequest(): bool { return strtolower((string) ($_SERVER['HTTP_X_REQUESTED_WITH'] ?? '')) === 'xmlhttprequest'; } private function flash(string $type, string $message): void { $_SESSION['flash'] = [ 'type' => $type, 'message' => $message, ]; } private function consumeFlash(): ?array { if (!isset($_SESSION['flash'])) { return null; } $flash = $_SESSION['flash']; unset($_SESSION['flash']); return is_array($flash) ? $flash : null; } private function calculateProgress(array $state): int { $totalRows = (int) ($state['totalRows'] ?? 0); if ($totalRows <= 0) { return ($state['status'] ?? '') === 'completed' ? 100 : 0; } $processedRows = (int) ($state['processedRows'] ?? 0); $progress = (int) round(($processedRows / $totalRows) * 100); if (($state['status'] ?? '') === 'completed') { return 100; } return max(0, min(99, $progress)); } private function escapeTableValue(mixed $value): string { return htmlspecialchars((string) ($value ?? ''), ENT_QUOTES, 'UTF-8'); } private function decodeGridPayload(string $json): array { $decoded = json_decode($json, true); return is_array($decoded) ? $decoded : []; } private function cleanupImportJob(array $state): void { $filePath = (string) ($state['filePath'] ?? ''); if ($filePath !== '' && is_file($filePath)) { @unlink($filePath); } $cacheBasePath = (string) ($state['cacheBasePath'] ?? ''); if ($cacheBasePath !== '') { $this->importService->deleteCache($cacheBasePath); } $token = (string) ($state['token'] ?? ''); if ($token !== '') { $this->jobStore->delete($token); } } private function redirectHome(): never { header('Location: ' . rtrim((string) app_config('app.url', 'http://127.0.0.1:8000'), '/')); exit; } }