Implementing automated compliance reporting for custom event ticket registers ledgers using custom PhpSpreadsheet components

Leveraging PhpSpreadsheet for Automated Compliance Reporting of Custom Event Ticket Registers

Many WordPress sites, particularly those handling event registrations, ticketing, or sensitive user interactions, require robust audit trails for compliance. This often involves maintaining a ledger of custom events, such as ticket purchases, cancellations, or access grants. Automating the generation of reports from these ledgers, especially in a spreadsheet format, is crucial for security reviews, financial audits, and regulatory adherence. This post details how to implement such a system using PhpSpreadsheet, a powerful PHP library for reading and writing spreadsheet files.

Designing the Event Ticket Register Ledger

Before generating reports, we need a structured way to log events. For this example, we’ll assume a custom database table or a structured log file. A typical ledger entry might include:

• Timestamp of the event
• User ID (if applicable)
• Event type (e.g., ‘ticket_purchase’, ‘cancellation’, ‘access_granted’)
• Associated data (e.g., ticket ID, amount, recipient email)
• IP Address
• User Agent

For demonstration purposes, we’ll simulate fetching data from a hypothetical WordPress custom table named wp_event_ticket_ledger. In a real-world scenario, you would replace this with your actual data retrieval logic (e.g., using $wpdb).

Setting Up PhpSpreadsheet

First, ensure you have PhpSpreadsheet installed. The recommended method is via Composer:

composer require phpoffice/phpspreadsheet

Once installed, you can include the autoloader in your PHP script:

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Alignment;



Generating the Spreadsheet Report



We'll create a PHP script that fetches ledger data and populates a new Excel (XLSX) file. This script can be triggered manually, via a WP-CLI command, or on a schedule.



Fetching Ledger Data (Simulated)



This function simulates retrieving data from your event ledger. Replace this with your actual database query.



<?php
/**
 * Simulates fetching event ledger data.
 * In a real application, this would query your database.
 *
 * @return array An array of ledger entries.
 */
function get_event_ledger_data() {
    // Replace with your actual database query using $wpdb
    // Example:
    // global $wpdb;
    // $results = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}event_ticket_ledger ORDER BY timestamp DESC", ARRAY_A);
    // return $results;

    // Simulated data for demonstration
    return [
        [
            'timestamp' => '2023-10-27 10:00:00',
            'user_id' => 123,
            'event_type' => 'ticket_purchase',
            'details' => json_encode(['ticket_id' => 'TKT-001', 'amount' => 50.00, 'currency' => 'USD']),
            'ip_address' => '192.168.1.100',
            'user_agent' => 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36',
        ],
        [
            'timestamp' => '2023-10-27 10:05:15',
            'user_id' => 456,
            'event_type' => 'cancellation',
            'details' => json_encode(['ticket_id' => 'TKT-001', 'reason' => 'User request']),
            'ip_address' => '10.0.0.5',
            'user_agent' => 'Mozilla/5.0 (iPhone; CPU iPhone OS 14_0 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.0 Mobile/15E148 Safari/604.1',
        ],
        [
            'timestamp' => '2023-10-27 11:30:00',
            'user_id' => 789,
            'event_type' => 'access_granted',
            'details' => json_encode(['user_email' => '[email protected]', 'event_id' => 'EVT-XYZ']),
            'ip_address' => '203.0.113.42',
            'user_agent' => 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36',
        ],
    ];
}
?>



Creating and Populating the Spreadsheet



This section details the core logic for generating the XLSX file.



<?php
// Include autoloader and other necessary classes
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

// --- Helper function to get data (defined above) ---
function get_event_ledger_data() { /* ... */ }
// --------------------------------------------------

// 1. Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// 2. Fetch the ledger data
$ledger_data = get_event_ledger_data();

// 3. Define headers and apply styling
$headers = ['Timestamp', 'User ID', 'Event Type', 'Details', 'IP Address', 'User Agent'];
$column_count = count($headers);

// Add headers to the first row
$sheet->fromArray([$headers], null, 'A1');

// Apply header styling
$header_style = [
    'font' => [
        'bold' => true,
        'color' => ['argb' => 'FFFFFFFF'], // White text
    ],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['argb' => 'FF4F81BD'], // Blue background
    ],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_CENTER,
    ],
    'borders' => [
        'bottom' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['argb' => 'FF000000']],
    ],
];
$sheet->getStyle('A1:' . chr(64 + $column_count) . '1')->applyFromArray($header_style);

// 4. Populate the spreadsheet with data
$row_num = 2; // Start from the second row
foreach ($ledger_data as $entry) {
    // Decode JSON details for better readability if needed, or keep as string
    $details_decoded = json_decode($entry['details'], true);
    $details_formatted = '';
    if (is_array($details_decoded)) {
        foreach ($details_decoded as $key => $value) {
            $details_formatted .= ucfirst(str_replace('_', ' ', $key)) . ': ' . $value . "\n";
        }
        // Remove trailing newline
        $details_formatted = rtrim($details_formatted);
    } else {
        $details_formatted = $entry['details'];
    }

    $data_row = [
        $entry['timestamp'],
        $entry['user_id'],
        $entry['event_type'],
        $details_formatted, // Use formatted details
        $entry['ip_address'],
        $entry['user_agent'],
    ];
    $sheet->fromArray([$data_row], null, 'A' . $row_num);

    // Apply basic row styling (optional)
    $sheet->getStyle('A' . $row_num . ':' . chr(64 + $column_count) . $row_num)->applyFromArray([
        'borders' => [
            'bottom' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['argb' => 'FFE0E0E0']],
        ],
        'alignment' => [
            'vertical' => Alignment::VERTICAL_TOP,
        ],
    ]);

    $row_num++;
}

// 5. Auto-size columns for better readability
foreach (range('A', chr(64 + $column_count)) as $columnID) {
    $sheet->getColumnDimension($columnID)->setAutoSize(true);
}
// Specifically for the 'Details' column, allow more width if needed
$sheet->getColumnDimension('D')->setWidth(50); // Example: Set width to 50

// 6. Set document properties (optional)
$spreadsheet->getProperties()
    ->setCreator('Your WordPress Site')
    ->setLastModifiedBy('Your WordPress Site')
    ->setTitle('Event Ticket Register Compliance Report')
    ->setSubject('Compliance Report for Event Ticket Ledger')
    ->setDescription('Automated report of all significant event ticket ledger entries.')
    ->setKeywords('compliance report event ticket ledger')
    ->setCategory('Audit');

// 7. Set active sheet title
$sheet->setTitle('Event Ledger');

// 8. Prepare to output the file
$filename = 'event_ticket_ledger_report_' . date('Ymd_His') . '.xlsx';
$writer = new Xlsx($spreadsheet);

// 9. Output the file to the browser for download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');

// If you are using IE9, then the following may be needed
header('Cache-Control: max-age=1');

// If you are running in SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$writer->save('php://output');
exit;
?>



Customizing and Enhancing the Report



The provided script offers a solid foundation. Here are several ways to enhance it for production environments:



Filtering and Date Ranges



For compliance, you often need reports for specific periods. You can add parameters to your script (e.g., via GET requests or WP-CLI arguments) to filter data:



// Example: Adding date range filtering to get_event_ledger_data()
function get_event_ledger_data( $start_date = null, $end_date = null ) {
    global $wpdb;
    $query = "SELECT * FROM {$wpdb->prefix}event_ticket_ledger";
    $where_clauses = [];

    if ( $start_date ) {
        $where_clauses[] = $wpdb->prepare("timestamp >= %s", $start_date);
    }
    if ( $end_date ) {
        $where_clauses[] = $wpdb->prepare("timestamp <= %s", $end_date);
    }

    if ( ! empty( $where_clauses ) ) {
        $query .= " WHERE " . implode(' AND ', $where_clauses);
    }

    $query .= " ORDER BY timestamp DESC";

    $results = $wpdb->get_results($query, ARRAY_A);
    return $results;
}

// In your main script:
// $start_date = $_GET['start_date'] ?? null; // Example: '2023-10-01'
// $end_date = $_GET['end_date'] ?? null;   // Example: '2023-10-31'
// $ledger_data = get_event_ledger_data($start_date, $end_date);




Advanced Styling and Formatting



PhpSpreadsheet allows extensive styling:



Conditional Formatting: Highlight specific event types (e.g., 'error' events in red).
Number Formatting: Ensure currency values are formatted correctly (e.g., `$50.00`).
Date Formatting: Display timestamps in a user-friendly format.
Merged Cells: For titles or summaries.



// Example: Formatting currency and dates
// Inside the loop, after populating $data_row:
$sheet->getStyle('A' . $row_num)->getNumberFormat()->setFormatCode('yyyy-mm-dd hh:mm:ss'); // Timestamp format
$sheet->getStyle('D' . $row_num)->getAlignment()->setWrapText(true); // Enable text wrapping for details

// If 'amount' is part of details and you want to format it specifically
// This requires parsing details more deeply or having dedicated columns
// For simplicity, we'll assume 'details' is a string for now.
// If you had a dedicated 'amount' column:
// $sheet->getStyle('E' . $row_num)->getNumberFormat()->setFormatCode('$#,##0.00'); // Currency format




Error Handling and Logging



Implement robust error handling for database queries and file operations. Log any issues to aid debugging.



try {
    // ... spreadsheet generation code ...
    $writer->save('php://output');
    exit;
} catch (\Exception $e) {
    // Log the error
    error_log("Error generating compliance report: " . $e->getMessage());
    // Optionally display a user-friendly error message
    wp_die("An error occurred while generating the report. Please try again later or contact support.");
}




Security Considerations



Access Control: Ensure that the script generating the report is only accessible by authorized users. This might involve WordPress user roles, nonces, or running the script via WP-CLI with appropriate user context. Never expose sensitive ledger data publicly.



Data Sanitization: While PhpSpreadsheet handles output formatting, ensure that data fetched from your database is clean and doesn't contain malicious content that could be misinterpreted by spreadsheet software (though this is less common with XLSX).



Integration with WordPress



To make this process seamless within WordPress:



WP-CLI Command: Create a custom WP-CLI command to run the report generation script. This is ideal for scheduled tasks or manual execution from the server's command line.
Admin Menu Page: Add a link to an admin page that triggers the script. Use nonces to secure the action.
Scheduled Events (WP Cron): For automated daily/weekly/monthly reports, use WordPress's cron system to schedule the script execution. The generated file could be emailed or saved to a secure location.



Example: WP-CLI Command



Create a file like wp-content/mu-plugins/compliance-reports.php:



<?php
if ( ! class_exists( 'WP_CLI' ) ) {
    return;
}

/**
 * Command to generate event ticket ledger compliance report.
 */
class Compliance_Report_Command {

    /**
     * Generates the event ticket ledger report as an XLSX file.
     *
     * ## OPTIONS
     *
     * --start-date=
     * : The start date for the report (YYYY-MM-DD).
     *
     * --end-date=
     * : The end date for the report (YYYY-MM-DD).
     *
     * --output=
     * : The full path to save the generated XLSX file. If not provided, the file will be downloaded via browser.
     *
     * ## EXAMPLES
     *
     * wp compliance-report generate --start-date=2023-10-01 --end-date=2023-10-31
     * wp compliance-report generate --output=/var/www/html/reports/ledger_oct_2023.xlsx
     *
     * @param array $args       Positional arguments.
     * @param array $assoc_args Associative arguments.
     */
    public function generate( $args, $assoc_args ) {
        $start_date = isset( $assoc_args['start-date'] ) ? $assoc_args['start-date'] : null;
        $end_date   = isset( $assoc_args['end-date'] ) ? $assoc_args['end-date'] : null;
        $output_path = isset( $assoc_args['output'] ) ? $assoc_args['output'] : null;

        // --- Include PhpSpreadsheet and your report generation logic here ---
        // For simplicity, assuming the report generation logic is in a separate file
        // or defined within this scope.
        // Ensure vendor/autoload.php is accessible.
        require_once 'vendor/autoload.php'; // Adjust path as necessary

        // --- Function to get data (must be defined or included) ---
        // Example: Assume get_event_ledger_data is defined globally or in this file
        // function get_event_ledger_data( $start_date = null, $end_date = null ) { ... }

        // --- Main report generation logic (similar to previous example) ---
        try {
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $ledger_data = get_event_ledger_data($start_date, $end_date); // Use filtered data

            // ... (Add headers, populate data, styling, etc.) ...
            $headers = ['Timestamp', 'User ID', 'Event Type', 'Details', 'IP Address', 'User Agent'];
            $column_count = count($headers);
            $sheet->fromArray([$headers], null, 'A1');
            // ... (Apply header styling) ...

            $row_num = 2;
            foreach ($ledger_data as $entry) {
                // ... (Format details, etc.) ...
                $details_decoded = json_decode($entry['details'], true);
                $details_formatted = '';
                if (is_array($details_decoded)) {
                    foreach ($details_decoded as $key => $value) {
                        $details_formatted .= ucfirst(str_replace('_', ' ', $key)) . ': ' . $value . "\n";
                    }
                    $details_formatted = rtrim($details_formatted);
                } else {
                    $details_formatted = $entry['details'];
                }

                $data_row = [
                    $entry['timestamp'],
                    $entry['user_id'],
                    $entry['event_type'],
                    $details_formatted,
                    $entry['ip_address'],
                    $entry['user_agent'],
                ];
                $sheet->fromArray([$data_row], null, 'A' . $row_num);
                // ... (Apply row styling) ...
                $row_num++;
            }

            // ... (Auto-size columns, set properties) ...
            foreach (range('A', chr(64 + $column_count)) as $columnID) {
                $sheet->getColumnDimension($columnID)->setAutoSize(true);
            }
            $sheet->getColumnDimension('D')->setWidth(50);

            $spreadsheet->getProperties()
                ->setTitle('Event Ticket Register Compliance Report')
                ->setSubject('Compliance Report for Event Ticket Ledger');
            $sheet->setTitle('Event Ledger');

            $writer = new Xlsx($spreadsheet);
            $filename = 'event_ticket_ledger_report_' . date('Ymd_His') . '.xlsx';

            if ($output_path) {
                // Save to file
                $writer->save($output_path);
                WP_CLI::success("Report saved to: " . $output_path);
            } else {
                // This scenario is tricky with WP-CLI as it doesn't directly handle browser downloads.
                // For WP-CLI, saving to a file is the standard approach.
                // If a download is strictly required, you'd typically trigger a web request.
                WP_CLI::error("WP-CLI cannot directly trigger browser downloads. Please specify an --output path or run this via a web request.");
            }

        } catch (\Exception $e) {
            WP_CLI::error("Error generating compliance report: " . $e->getMessage());
        }
    }
}

WP_CLI::add_command( 'compliance-report', 'Compliance_Report_Command' );

// --- Ensure get_event_ledger_data is defined somewhere accessible ---
// For this example, let's define it here if it's not globally available
if (!function_exists('get_event_ledger_data')) {
    function get_event_ledger_data( $start_date = null, $end_date = null ) {
        global $wpdb;
        $query = "SELECT * FROM {$wpdb->prefix}event_ticket_ledger";
        $where_clauses = [];

        if ( $start_date ) {
            $where_clauses[] = $wpdb->prepare("timestamp >= %s", $start_date);
        }
        if ( $end_date ) {
            $where_clauses[] = $wpdb->prepare("timestamp <= %s", $end_date);
        }

        if ( ! empty( $where_clauses ) ) {
            $query .= " WHERE " . implode(' AND ', $where_clauses);
        }

        $query .= " ORDER BY timestamp DESC";

        $results = $wpdb->get_results($query, ARRAY_A);
        // Simulate error if no results for testing
        if ($results === null && $wpdb->last_error) {
             throw new Exception("Database error: " . $wpdb->last_error);
        }
        return $results ?: []; // Return empty array if no results
    }
}
?>



After placing this file, you can run commands like:



wp compliance-report generate --start-date=2023-10-01 --end-date=2023-10-31 --output=/path/to/your/secure/reports/ledger_oct_2023.xlsx



This approach provides a secure, scriptable, and auditable method for generating compliance reports from custom event ledgers, significantly reducing manual effort and potential errors.