Implementing automated compliance reporting for custom customer support tickets ledgers using custom PhpSpreadsheet components

Leveraging PhpSpreadsheet for Automated Customer Support Ticket Compliance Reporting

For e-commerce businesses, maintaining an auditable ledger of customer support interactions is not merely a best practice; it’s a critical compliance requirement. This ledger, often a complex dataset derived from various support channels, needs to be readily accessible and presentable in a standardized format for regulatory bodies or internal audits. Manually generating these reports is time-consuming, error-prone, and scales poorly. This document outlines a robust, automated solution using PhpSpreadsheet to generate compliance-ready Excel reports directly from your customer support ticket data.

Data Source and Preprocessing

Our primary data source will be a relational database (e.g., MySQL, PostgreSQL) storing ticket information. For this example, we’ll assume a table named support_tickets with columns such as ticket_id, customer_id, timestamp_created, timestamp_resolved, category, priority, status, and agent_id. Before generating the report, we need to extract and potentially transform this data. A common requirement is to filter tickets within a specific date range and categorize them according to compliance mandates.

We’ll use PHP for this process, interacting with the database via PDO for broad compatibility and security. The core logic will involve fetching relevant ticket data and preparing it for export. For demonstration purposes, let’s define a function to retrieve tickets within a given month and year.

Database Query Example (SQL)

SELECT
    t.ticket_id,
    c.email AS customer_email,
    t.timestamp_created,
    t.timestamp_resolved,
    t.category,
    t.priority,
    t.status,
    a.name AS agent_name
FROM
    support_tickets t
JOIN
    customers c ON t.customer_id = c.customer_id
LEFT JOIN
    agents a ON t.agent_id = a.agent_id
WHERE
    YEAR(t.timestamp_created) = :year
    AND MONTH(t.timestamp_created) = :month
ORDER BY
    t.timestamp_created ASC;

PHP Data Fetching Function

This function establishes a PDO connection and executes the SQL query, returning an array of ticket data.

<?php
require 'vendor/autoload.php'; // Assuming PhpSpreadsheet is installed via Composer

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PDO;
use PDOException;

/**
 * Fetches support ticket data for a specific month and year.
 *
 * @param PDO $pdo The PDO database connection instance.
 * @param int $year The year to filter tickets.
 * @param int $month The month to filter tickets.
 * @return array An array of ticket data, or an empty array on failure.
 */
function getTicketsForMonth(PDO $pdo, int $year, int $month): array
{
    $sql = "SELECT
                t.ticket_id,
                c.email AS customer_email,
                t.timestamp_created,
                t.timestamp_resolved,
                t.category,
                t.priority,
                t.status,
                a.name AS agent_name
            FROM
                support_tickets t
            JOIN
                customers c ON t.customer_id = c.customer_id
            LEFT JOIN
                agents a ON t.agent_id = a.agent_id
            WHERE
                YEAR(t.timestamp_created) = :year
                AND MONTH(t.timestamp_created) = :month
            ORDER BY
                t.timestamp_created ASC;";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->bindParam(':year', $year, PDO::PARAM_INT);
        $stmt->bindParam(':month', $month, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        error_log("Database error fetching tickets: " . $e->getMessage());
        return [];
    }
}

/**
 * Generates a compliance report in XLSX format.
 *
 * @param array $ticketData The array of ticket data.
 * @param int $year The year of the report.
 * @param int $month The month of the report.
 * @return string|false The path to the generated file, or false on failure.
 */
function generateComplianceReport(array $ticketData, int $year, int $month): string|false
{
    if (empty($ticketData)) {
        error_log("No ticket data provided for report generation.");
        return false;
    }

    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    // Define headers
    $headers = [
        'Ticket ID',
        'Customer Email',
        'Created At',
        'Resolved At',
        'Category',
        'Priority',
        'Status',
        'Agent Name',
        'Resolution Time (Hours)' // Calculated field
    ];
    $sheet->fromArray([$headers], null, 'A1');

    // Apply header styling
    $headerStyle = [
        'font' => ['bold' => true],
        'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFD3D3D3']],
        'borders' => ['bottom' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]]
    ];
    $sheet->getStyle('A1:' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($headers)) . '1')->applyFromArray($headerStyle);

    // Populate data rows
    $rowNum = 2;
    foreach ($ticketData as $ticket) {
        $createdAt = new DateTime($ticket['timestamp_created']);
        $resolvedAt = $ticket['timestamp_resolved'] ? new DateTime($ticket['timestamp_resolved']) : null;
        $resolutionTime = 'N/A';

        if ($resolvedAt && $resolvedAt >= $createdAt) {
            $interval = $createdAt->diff($resolvedAt);
            $hours = ($interval->days * 24) + $interval->h + ($interval->i / 60);
            $resolutionTime = round($hours, 2);
        }

        $rowData = [
            $ticket['ticket_id'],
            $ticket['customer_email'],
            $ticket['timestamp_created'],
            $ticket['timestamp_resolved'] ?? '', // Handle null resolved time
            $ticket['category'],
            $ticket['priority'],
            $ticket['status'],
            $ticket['agent_name'] ?? 'Unassigned', // Handle null agent name
            $resolutionTime
        ];
        $sheet->fromArray([$rowData], null, 'A' . $rowNum);
        $rowNum++;
    }

    // Auto-size columns for better readability
    foreach (range('A', $sheet->getHighestColumn()) as $columnID) {
        $sheet->getColumnDimension($columnID)->setAutoSize(true);
    }

    // Set column format for dates and resolution time
    $sheet->getStyle('C2:D' . ($rowNum - 1))->getNumberFormat()->setFormatCode('yyyy-mm-dd hh:mm:ss');
    $sheet->getStyle('I2:I' . ($rowNum - 1))->getNumberFormat()->setFormatCode('#,##0.00'); // Format resolution time as number

    // Add a title and date range to the report
    $reportTitle = "Customer Support Ticket Compliance Report - " . date('F Y', mktime(0, 0, 0, $month, 1, $year));
    $sheet->prependRow(1, [$reportTitle]);
    $sheet->mergeCells('A1:' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($headers)) . '1');
    $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
    $sheet->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

    // Adjust row number for data after adding title
    $dataStartRow = 3;
    $sheet->getStyle('A' . $dataStartRow . ':' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($headers)) . ($rowNum - 1))->applyFromArray($headerStyle); // Re-apply header style to actual headers

    // Generate filename
    $filename = sprintf('compliance_report_%d_%02d.xlsx', $year, $month);
    $writer = new Xlsx($spreadsheet);
    $filePath = sys_get_temp_dir() . DIRECTORY_SEPARATOR . $filename; // Save to temp directory
    $writer->save($filePath);

    return $filePath;
}

// --- Example Usage ---
/*
$dbHost = 'localhost';
$dbName = 'support_db';
$dbUser = 'db_user';
$dbPass = 'db_password';
$year = 2023;
$month = 10; // October

try {
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    $tickets = getTicketsForMonth($pdo, $year, $month);

    if (!empty($tickets)) {
        $reportPath = generateComplianceReport($tickets, $year, $month);
        if ($reportPath) {
            echo "Report generated successfully: " . $reportPath . "\n";
            // Here you would typically move the file, email it, or make it available for download.
            // For example, to force download:
            // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            // header('Content-Disposition: attachment;filename="' . basename($reportPath) . '"');
            // header('Expires: 0');
            // header('Cache-Control: must-revalidate');
            // header('Pragma: public');
            // header('Content-Length: ' . filesize($reportPath));
            // readfile($reportPath);
            // unlink($reportPath); // Clean up temp file
            // exit;
        } else {
            echo "Failed to generate report.\n";
        }
    } else {
        echo "No tickets found for the specified period.\n";
    }

} catch (PDOException $e) {
    error_log("Database connection failed: " . $e->getMessage());
    echo "Database connection error.\n";
}
*/
?>

Customizing PhpSpreadsheet Components for Advanced Reporting

PhpSpreadsheet offers extensive customization options beyond basic data population. For compliance reporting, we often need to:

• Apply Specific Formatting: Ensure dates, numbers, and currency adhere to required standards.
• Add Calculated Fields: Compute metrics like resolution time, first response time, or SLA adherence directly within the spreadsheet.
• Implement Conditional Formatting: Highlight tickets that violate SLAs or require immediate attention.
• Add Charts and Graphs: Visualize trends in ticket volume, resolution times, or category distribution.
• Protect Sheets/Workbooks: Prevent accidental modification of sensitive compliance data.
• Add Metadata: Include report generation timestamps, data sources, and versioning information.

Calculating Resolution Time

The example above already includes a calculation for resolution time in hours. This involves comparing timestamp_created and timestamp_resolved. We use PHP’s DateTime objects for accurate interval calculations. The result is rounded to two decimal places and formatted as a number in the spreadsheet.

Implementing Conditional Formatting

Conditional formatting is crucial for flagging non-compliant or critical tickets. Let’s say we want to highlight tickets that took longer than 48 hours to resolve. We can achieve this by adding a conditional formatting rule to the ‘Resolution Time (Hours)’ column.

<?php
// ... inside generateComplianceReport function, after populating data ...

// Add conditional formatting for resolution time exceeding 48 hours
$conditionalStyleArray = [
    'font' => [
        'color' => ['argb' => \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE],
        'bold' => true,
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'startColor' => ['argb' => \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED],
    ],
];

$conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_GREATERTHAN);
$conditional1->addCondition('48'); // Value to compare against

$conditional1->getStyle()->applyFromArray($conditionalStyleArray);

// Apply conditional formatting to the resolution time column (Column I)
// Assuming data starts from row 3 after the title and headers
$sheet->getStyle('I3:I' . ($rowNum - 1))->setConditionalStyles([$conditional1]);

// ... rest of the function ...
?>

Adding Report Metadata

Including metadata ensures the report’s integrity and context. We can add a dedicated section or use spreadsheet properties.

<?php
// ... inside generateComplianceReport function ...

// Add metadata section at the bottom
$metadataStartRow = $rowNum + 2; // Start after the data and a blank row
$sheet->setCellValue('A' . $metadataStartRow, 'Report Generated On:');
$sheet->setCellValue('B' . $metadataStartRow, (new DateTime())->format('Y-m-d H:i:s'));
$sheet->getStyle('A' . $metadataStartRow)->getFont()->setBold(true);

$sheet->setCellValue('A' . ($metadataStartRow + 1), 'Data Source:');
$sheet->setCellValue('B' . ($metadataStartRow + 1), 'support_db.support_tickets');
$sheet->getStyle('A' . ($metadataStartRow + 1))->getFont()->setBold(true);

$sheet->setCellValue('A' . ($metadataStartRow + 2), 'Report Period:');
$sheet->setCellValue('B' . ($metadataStartRow + 2), date('F Y', mktime(0, 0, 0, $month, 1, $year)));
$sheet->getStyle('A' . ($metadataStartRow + 2))->getFont()->setBold(true);

// Set document properties
$spreadsheet->getProperties()
    ->setCreator("Your Company Name")
    ->setLastModifiedBy("Automated Report Generator")
    ->setTitle("Compliance Report - " . date('F Y', mktime(0, 0, 0, $month, 1, $year)))
    ->setSubject("Customer Support Ticket Compliance")
    ->setDescription("Automated compliance report for customer support tickets.")
    ->setKeywords("compliance, support, tickets, report, e-commerce")
    ->setCategory("Compliance");

// ... rest of the function ...
?>

Automating the Report Generation Workflow

To make this truly automated, the PHP script should be triggered on a schedule. This can be achieved using cron jobs on Linux/macOS or Task Scheduler on Windows.

Cron Job Example

To run the report generation script daily at 2 AM for the previous month, you would add the following line to your crontab:

0 2 1 * * /usr/bin/php /path/to/your/report_generator.php --generate-previous-month

The report_generator.php script would contain the database connection logic, call getTicketsForMonth with the appropriate year and month (calculated based on the current date or command-line arguments), and then call generateComplianceReport. The generated file can then be moved to a secure, accessible location, uploaded to cloud storage, or emailed to relevant stakeholders.

Error Handling and Notifications

Robust error handling is paramount. The script should log database connection errors, query failures, and file generation issues. For critical failures, an email notification system (e.g., using PHPMailer) should alert the technical team immediately. This ensures that any breakdown in the reporting process is quickly identified and rectified, maintaining continuous compliance.

Security Considerations

When handling customer data and generating compliance reports, security must be a top priority:

• Database Credentials: Store database credentials securely, preferably outside the webroot and not hardcoded in the script. Use environment variables or a dedicated configuration management system.
• File Permissions: Ensure that generated report files have appropriate read/write permissions. Avoid world-writable directories.
• Data Sensitivity: If reports contain Personally Identifiable Information (PII), consider encrypting the generated files or restricting access to them.
• Secure Transport: If reports are emailed or uploaded, use secure protocols (TLS/SSL for email, SFTP/HTTPS for uploads).
• Input Validation: If the script accepts parameters (like year/month), always validate and sanitize them to prevent injection attacks.

Conclusion

By integrating PhpSpreadsheet into your support workflow, you can transform a manual, compliance-burdening task into an automated, efficient process. The ability to customize output, apply specific formatting, and embed metadata ensures that generated reports meet stringent compliance requirements. Coupled with a robust scheduling and error-handling mechanism, this solution provides a scalable and reliable method for maintaining an auditable trail of customer support interactions, crucial for any e-commerce business operating in regulated environments.