PHP-MVC-TERRITORY/app/Services/ExportService.php

<?php

declare(strict_types=1);

namespace App\Services;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Dompdf\Dompdf;
use Dompdf\Options;

class ExportService
{
    /**
     * Build a ZIP archive containing XLSX + PDF for each territory.
     *
     * @param list<array<string,mixed>> $territories
     * @param list<array<string,mixed>> $allHouseholds  All household rows (with territory_id)
     * @return string  Raw ZIP binary content
     */
    public function buildZip(array $territories, array $allHouseholds): string
    {
        $byTerritory = [];
        foreach ($allHouseholds as $h) {
            $tid = (int) $h['territory_id'];
            $byTerritory[$tid][] = $h;
        }

        $zipFile = tempnam(sys_get_temp_dir(), 'territory_export_');
        $zip     = new \ZipArchive();
        $zip->open($zipFile, \ZipArchive::CREATE | \ZipArchive::OVERWRITE);

        foreach ($territories as $territory) {
            $tid        = (int) $territory['id'];
            $households = $byTerritory[$tid] ?? [];
            $slug       = $this->slug($territory['name']);

            $xlsx = $this->buildXlsx($territory, $households);
            $zip->addFromString("{$slug}.xlsx", $xlsx);

            $pdf = $this->buildPdf($territory, $households);
            $zip->addFromString("{$slug}.pdf", $pdf);
        }

        $zip->close();
        $content = (string) file_get_contents($zipFile);
        unlink($zipFile);

        return $content;
    }

    /** @param list<array<string,mixed>> $households */
    private function buildXlsx(array $territory, array $households): string
    {
        $spreadsheet = new Spreadsheet();
        $spreadsheet->removeSheetByIndex(0);

        $byStreet = $this->groupByStreet($households);

        if (empty($byStreet)) {
            $sheet = $spreadsheet->createSheet();
            $sheet->setTitle('No Data');
            $sheet->setCellValue('A1', 'No households found for this territory.');
        }

        foreach ($byStreet as $streetName => $streetHouseholds) {
            $sheet = $spreadsheet->createSheet();
            $sheet->setTitle(substr((string) $streetName, 0, 31));

            [$even, $odd] = $this->splitEvenOdd($streetHouseholds);

            $headerStyle = [
                'font'    => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
                'fill'    => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '1D7A6D']],
                'borders' => ['bottom' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]],
            ];

            $evenHeaders = ['#', 'Address (Even)', 'Bus.', 'DNC', 'DNC Date'];
            $oddHeaders  = ['#', 'Address (Odd)',  'Bus.', 'DNC', 'DNC Date'];

            foreach ($evenHeaders as $col => $header) {
                $cell = $this->colLetter($col + 1) . '1';
                $sheet->setCellValue($cell, $header);
                $sheet->getStyle($cell)->applyFromArray($headerStyle);
            }

            $sheet->setCellValue('G1', '');

            foreach ($oddHeaders as $col => $header) {
                $cell = $this->colLetter($col + 8) . '1';
                $sheet->setCellValue($cell, $header);
                $sheet->getStyle($cell)->applyFromArray($headerStyle);
            }

            $row = 2;
            foreach ($even as $h) {
                $sheet->setCellValue("A{$row}", $h['street_number'] ?? '');
                $sheet->setCellValue("B{$row}", $h['address'] ?? '');
                $sheet->setCellValue("C{$row}", $h['is_business'] ? 'Yes' : '');
                $sheet->setCellValue("D{$row}", $h['do_not_call'] ? 'Yes' : '');
                $sheet->setCellValue("E{$row}", $h['do_not_call_date'] ?? '');
                $row++;
            }

            $row = 2;
            foreach ($odd as $h) {
                $sheet->setCellValue("H{$row}", $h['street_number'] ?? '');
                $sheet->setCellValue("I{$row}", $h['address'] ?? '');
                $sheet->setCellValue("J{$row}", $h['is_business'] ? 'Yes' : '');
                $sheet->setCellValue("K{$row}", $h['do_not_call'] ? 'Yes' : '');
                $sheet->setCellValue("L{$row}", $h['do_not_call_date'] ?? '');
                $row++;
            }

            foreach (range('A', 'L') as $col) {
                $sheet->getColumnDimension($col)->setAutoSize(true);
            }
        }

        $writer  = new Xlsx($spreadsheet);
        $tmpFile = tempnam(sys_get_temp_dir(), 'territory_xlsx_');
        $writer->save($tmpFile);
        $content = (string) file_get_contents($tmpFile);
        unlink($tmpFile);

        return $content;
    }

    /** @param list<array<string,mixed>> $households */
    private function buildPdf(array $territory, array $households): string
    {
        $options = new Options();
        $options->set('defaultFont', 'DejaVu Sans');
        $options->set('isRemoteEnabled', false);

        $dompdf = new Dompdf($options);
        $dompdf->loadHtml($this->buildPdfHtml($territory, $households));
        $dompdf->setPaper('A4', 'landscape');
        $dompdf->render();

        return (string) $dompdf->output();
    }

    /** @param list<array<string,mixed>> $households */
    private function buildPdfHtml(array $territory, array $households): string
    {
        $title    = htmlspecialchars($territory['name'], ENT_QUOTES, 'UTF-8');
        $byStreet = $this->groupByStreet($households);

        $html = '<!DOCTYPE html><html><head><meta charset="UTF-8">
        <style>
            body { font-family: DejaVu Sans, sans-serif; font-size: 9pt; margin: 10mm; }
            h1   { font-size: 14pt; margin-bottom: 4px; }
            h2   { font-size: 11pt; margin: 12px 0 4px; border-bottom: 1px solid #555; padding-bottom: 2px; }
            table { width: 100%; border-collapse: collapse; margin-bottom: 8px; }
            th   { background: #1d7a6d; color: #fff; padding: 4px 6px; font-size: 8pt; text-align: left; }
            td   { padding: 3px 6px; border-bottom: 1px solid #ddd; font-size: 8pt; vertical-align: top; }
            .dnc { color: #c0392b; font-weight: bold; }
            .sep { width: 6px; }
        </style>
        </head><body>';

        $html .= "<h1>Territory: {$title}</h1>";

        if (empty($byStreet)) {
            $html .= '<p>No households found.</p>';
        }

        foreach ($byStreet as $streetName => $streetHouseholds) {
            $escapedStreet = htmlspecialchars((string) $streetName, ENT_QUOTES, 'UTF-8');
            [$even, $odd]  = $this->splitEvenOdd($streetHouseholds);

            $html .= "<h2>{$escapedStreet}</h2>";
            $html .= '<table><tr>
                <th>#</th><th>Address (Even)</th><th>DNC</th>
                <th class="sep"></th>
                <th>#</th><th>Address (Odd)</th><th>DNC</th>
            </tr>';

            $max      = max(count($even), count($odd), 1);
            $evenVals = array_values($even);
            $oddVals  = array_values($odd);

            for ($i = 0; $i < $max; $i++) {
                $html .= '<tr>';
                if (isset($evenVals[$i])) {
                    $h    = $evenVals[$i];
                    $addr = htmlspecialchars($h['address'] ?? '', ENT_QUOTES, 'UTF-8');
                    $dnc  = $h['do_not_call'] ? '<span class="dnc">DNC</span>' : '';
                    $html .= "<td>{$h['street_number']}</td><td>{$addr}</td><td>{$dnc}</td>";
                } else {
                    $html .= '<td></td><td></td><td></td>';
                }
                $html .= '<td class="sep"></td>';
                if (isset($oddVals[$i])) {
                    $h    = $oddVals[$i];
                    $addr = htmlspecialchars($h['address'] ?? '', ENT_QUOTES, 'UTF-8');
                    $dnc  = $h['do_not_call'] ? '<span class="dnc">DNC</span>' : '';
                    $html .= "<td>{$h['street_number']}</td><td>{$addr}</td><td>{$dnc}</td>";
                } else {
                    $html .= '<td></td><td></td><td></td>';
                }
                $html .= '</tr>';
            }

            $html .= '</table>';
        }

        $html .= '</body></html>';
        return $html;
    }

    /**
     * @param  list<array<string,mixed>> $households
     * @return array<string, list<array<string,mixed>>>
     */
    private function groupByStreet(array $households): array
    {
        $byStreet = [];
        foreach ($households as $h) {
            $street = trim((string) ($h['street_name'] ?? ''));
            if ($street === '') {
                $street = 'Unknown Street';
            }
            $byStreet[$street][] = $h;
        }

        ksort($byStreet);
        return $byStreet;
    }

    /**
     * @param  list<array<string,mixed>> $households
     * @return array{list<array<string,mixed>>, list<array<string,mixed>>}
     */
    private function splitEvenOdd(array $households): array
    {
        $even = [];
        $odd  = [];

        foreach ($households as $h) {
            $num = (int) ($h['street_number'] ?? 0);
            if ($num % 2 === 0) {
                $even[] = $h;
            } else {
                $odd[] = $h;
            }
        }

        usort($even, fn($a, $b) => (int) ($a['street_number'] ?? 0) <=> (int) ($b['street_number'] ?? 0));
        usort($odd, fn($a, $b) => (int) ($a['street_number'] ?? 0) <=> (int) ($b['street_number'] ?? 0));

        return [$even, $odd];
    }

    private function colLetter(int $n): string
    {
        $letter = '';
        while ($n > 0) {
            $n--;
            $letter = chr(65 + ($n % 26)) . $letter;
            $n      = (int) ($n / 26);
        }
        return $letter;
    }

    private function slug(string $name): string
    {
        $slug = strtolower(preg_replace('/[^a-zA-Z0-9]+/', '_', $name) ?? $name);
        return trim($slug, '_') ?: 'territory';
    }
}