Vengala Vinay

Having 12+ Years of Experience in Software Development

Home » Implementing automated compliance reporting for custom event ticket registers ledgers using FPDF customized scripts

Implementing automated compliance reporting for custom event ticket registers ledgers using FPDF customized scripts

Understanding the Core Problem: Event Ticket Register Auditing

Many WordPress sites, particularly those handling events, ticketing, or registrations, generate a wealth of data that requires regular auditing for compliance. This data often resides in custom database tables or is generated through specific plugins. The challenge lies in transforming this raw event data into a structured, human-readable, and auditable report format. Manually generating these reports is time-consuming, error-prone, and impractical for frequent compliance checks. This post outlines a robust solution using PHP and the FPDF library to automate the generation of these compliance reports from custom event ticket registers.

Prerequisites and Setup

Before diving into the code, ensure you have the following:

  • A WordPress installation with a custom table or a plugin that stores event ticket data. For this example, we’ll assume a custom table named wp_event_tickets with columns like ticket_id, event_name, attendee_name, registration_date, payment_status, and ticket_price.
  • PHP 7.4+ installed on your server.
  • Composer installed for dependency management.
  • The FPDF library. We’ll install this via Composer.

Navigate to your WordPress theme’s directory (or a custom plugin directory) and run the following Composer command to install FPDF:

composer require setasign/fpdf

This will create a vendor directory and an autoload.php file. You’ll need to include this file in your PHP script to load the FPDF library.

Database Schema Assumption

Our example assumes a simplified custom table structure for event tickets. In a real-world scenario, this might be more complex, involving relationships with other tables (e.g., users, events). The key is to adapt the SQL query to fetch the necessary data for your specific compliance report.

CREATE TABLE wp_event_tickets (
    ticket_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(255) NOT NULL,
    attendee_name VARCHAR(255) NOT NULL,
    registration_date DATETIME NOT NULL,
    payment_status VARCHAR(50) NOT NULL,
    ticket_price DECIMAL(10, 2) NOT NULL
);

Core Reporting Logic: The PHP Script

We’ll create a PHP script that connects to the WordPress database, fetches the relevant ticket data, and then uses FPDF to generate a PDF report. This script can be triggered via a cron job or a custom WordPress admin page.

1. Setting up the PDF Generator Class

First, let’s define a custom PDF class that extends FPDF. This allows us to define headers and footers that will appear on every page, which is crucial for compliance reports.

<?php
require_once 'vendor/autoload.php'; // Load Composer's autoloader

use setasign\Fpdf\Fpdf;

class ComplianceReportPDF extends Fpdf {
    // Page header
    function Header() {
        // Logo
        // $this->Image('path/to/your/logo.png', 10, 6, 30);
        // Arial bold 15
        $this->SetFont('Arial', 'B', 15);
        // Move to the right
        $this->Cell(80);
        // Title
        $this->Cell(30, 10, 'Event Ticket Compliance Report', 0, 0, 'C');
        // Line break
        $this->Ln(20);
    }

    // Page footer
    function Footer() {
        // Position at 1.5 cm from bottom
        $this->SetY(-15);
        // Arial italic 8
        $this->SetFont('Arial', 'I', 8);
        // Page number
        $this->Cell(0, 10, 'Page ' . $this->PageNo() . '/{nb}', 0, 0, 'C');
        // Report generation timestamp
        $this->SetX(-50); // Position to the right
        $this->Cell(0, 10, 'Generated: ' . date('Y-m-d H:i:s'), 0, 0, 'R');
    }

    // Custom method to add table header
    function HeaderTable() {
        $this->SetFont('Arial', 'B', 10);
        $this->SetFillColor(200, 220, 200); // Light green background
        $this->Cell(30, 10, 'Ticket ID', 1, 0, 'C', true);
        $this->Cell(50, 10, 'Event Name', 1, 0, 'C', true);
        $this->Cell(50, 10, 'Attendee Name', 1, 0, 'C', true);
        $this->Cell(30, 10, 'Reg. Date', 1, 0, 'C', true);
        $this->Cell(30, 10, 'Payment', 1, 0, 'C', true);
        $this->Cell(20, 10, 'Price', 1, 0, 'C', true);
        $this->Ln();
    }

    // Custom method to add table rows
    function AddDataRow($data) {
        $this->SetFont('Arial', '', 10);
        $this->Cell(30, 10, $data['ticket_id'], 1, 0, 'C');
        $this->Cell(50, 10, $data['event_name'], 1, 0, 'L');
        $this->Cell(50, 10, $data['attendee_name'], 1, 0, 'L');
        $this->Cell(30, 10, $data['registration_date'], 1, 0, 'C');
        $this->Cell(30, 10, $data['payment_status'], 1, 0, 'C');
        $this->Cell(20, 10, number_format($data['ticket_price'], 2), 1, 0, 'R');
        $this->Ln();
    }
}
?>

2. Fetching Data from WordPress Database

We need to access the WordPress database. The global $wpdb object is available within the WordPress environment. If running this script outside of the WordPress context (e.g., a standalone cron job), you’ll need to include WordPress core files and initialize it.

<?php
// Assuming this script is run within the WordPress environment (e.g., via a WP-CLI command or a hook)
global $wpdb;
$table_name = $wpdb->prefix . 'event_tickets'; // Dynamically get table name with prefix

// Define date range for the report (e.g., last 30 days)
$end_date = date('Y-m-d H:i:s');
$start_date = date('Y-m-d H:i:s', strtotime('-30 days'));

// Construct the SQL query
// IMPORTANT: Sanitize inputs if they come from user-provided data.
// For a fixed date range, this is generally safe.
$sql = $wpdb->prepare(
    "SELECT ticket_id, event_name, attendee_name, registration_date, payment_status, ticket_price
     FROM {$table_name}
     WHERE registration_date BETWEEN %s AND %s
     ORDER BY registration_date ASC",
    $start_date,
    $end_date
);

$tickets = $wpdb->get_results($sql);

if (empty($tickets)) {
    echo "No tickets found for the specified period.\n";
    // Optionally, exit or handle this case gracefully
    // exit;
}
?>

3. Generating the PDF Report

Now, we instantiate our custom PDF class, add data, and output the PDF. The output can be sent directly to the browser or saved to a file.

<?php
// Instantiate the PDF class
$pdf = new ComplianceReportPDF('L', 'mm', 'A4'); // Landscape orientation, millimeters, A4 size
$pdf->AliasNbPages(); // Enable page numbering
$pdf->AddPage();
$pdf->SetFont('Arial', '', 12);

// Add the table header
$pdf->HeaderTable();

// Add data rows
$total_revenue = 0;
foreach ($tickets as $ticket) {
    // Format registration date for display
    $registration_date_formatted = date('Y-m-d H:i', strtotime($ticket->registration_date));

    $pdf->AddDataRow([
        'ticket_id'         => $ticket->ticket_id,
        'event_name'        => $ticket->event_name,
        'attendee_name'     => $ticket->attendee_name,
        'registration_date' => $registration_date_formatted,
        'payment_status'    => $ticket->payment_status,
        'ticket_price'      => $ticket->ticket_price,
    ]);
    $total_revenue += $ticket->ticket_price;
}

// Add a summary section
$pdf->Ln(10); // Add some space
$pdf->SetFont('Arial', 'B', 12);
$pdf->Cell(0, 10, 'Report Summary', 0, 1, 'L');
$pdf->SetFont('Arial', '', 12);
$pdf->Cell(0, 10, 'Total Tickets Registered: ' . count($tickets), 0, 1, 'L');
$pdf->Cell(0, 10, 'Total Revenue: $' . number_format($total_revenue, 2), 0, 1, 'L');

// Output the PDF
// Option 1: Send to browser
$pdf->Output('compliance_report_' . date('Ymd') . '.pdf', 'I');

// Option 2: Save to a file on the server
// $pdf->Output('compliance_report_' . date('Ymd') . '.pdf', 'F');
// echo "Report generated and saved to compliance_report_" . date('Ymd') . ".pdf\n";

// Option 3: Send as attachment via email (requires additional mailer setup)
// $pdf->Output($pdf->buffer, 'S'); // Get PDF content as string
// mail('[email protected]', 'Compliance Report', 'Please find attached the compliance report.', "Content-Type: application/pdf\nContent-Disposition: attachment; filename=\"compliance_report_" . date('Ymd') . ".pdf\"\nContent-Transfer-Encoding: base64\n\n" . base64_encode($pdf->buffer));

exit; // Ensure no further output
?>

Integrating with WordPress

To make this script easily manageable within WordPress, consider these integration methods:

1. WP-CLI Command

Create a custom WP-CLI command. This is ideal for running reports manually from the command line or scheduling them with system cron jobs.

// In your theme's functions.php or a custom plugin file
if ( class_exists( 'WP_CLI' ) ) {
    WP_CLI::add_command( 'event-compliance report', function( $args, $assoc_args ) {
        // Paste the entire reporting logic (database query and PDF generation) here.
        // Ensure vendor/autoload.php is correctly included.
        // For example: require_once WP_CONTENT_DIR . '/themes/your-theme/vendor/autoload.php';

        // Example: Add date range as arguments
        $days = isset($assoc_args['days']) ? intval($assoc_args['days']) : 30;
        $end_date = date('Y-m-d H:i:s');
        $start_date = date('Y-m-d H:i:s', strtotime("-{$days} days"));

        // ... (rest of the script from section 3) ...

        // For WP-CLI, outputting to STDOUT is common.
        // If saving to file, indicate success.
        WP_CLI::success( "Compliance report generated for the last {$days} days." );
    });
}
?>

To run this command:

wp event-compliance report --days=60 --output=report.pdf

2. Scheduled Action (WP Cron)

Use WordPress’s built-in cron system (WP Cron) to schedule regular report generation. This requires a function hooked into `wp_cron` and a scheduled event.

// In your theme's functions.php or a custom plugin file

// Hook to schedule the event
register_activation_hook( __FILE__, 'schedule_compliance_report' );
function schedule_compliance_report() {
    if ( ! wp_next_scheduled( 'generate_daily_compliance_report' ) ) {
        wp_schedule_event( time(), 'daily', 'generate_daily_compliance_report' ); // 'daily', 'weekly', 'monthly'
    }
}

// Hook to unschedule the event on deactivation
register_deactivation_hook( __FILE__, 'unschedule_compliance_report' );
function unschedule_compliance_report() {
    wp_clear_scheduled_hook( 'generate_daily_compliance_report' );
}

// The action that runs the report generation
add_action( 'generate_daily_compliance_report', 'run_compliance_report_generation' );
function run_compliance_report_generation() {
    // Paste the entire reporting logic here.
    // Ensure vendor/autoload.php is correctly included.
    // For example: require_once WP_CONTENT_DIR . '/themes/your-theme/vendor/autoload.php';

    // Set the output to save to a file, as there's no browser or WP-CLI context.
    $report_filename = WP_CONTENT_DIR . '/uploads/compliance_reports/compliance_report_' . date('Ymd') . '.pdf';
    // Ensure the uploads directory exists and is writable
    if (!file_exists(dirname($report_filename))) {
        wp_mkdir_p(dirname($report_filename));
    }

    // ... (rest of the script from section 3, using 'F' for output) ...
    // $pdf->Output($report_filename, 'F');

    // Log the generation for debugging
    error_log("Compliance report generated: " . $report_filename);
}
?>

Note: WP Cron is triggered by site visits. For reliable execution, especially on low-traffic sites, consider using a server-level cron job to trigger wp-cron.php.

# Example server cron job (runs daily at 2 AM)
0 2 * * * wget -q -O - https://yourdomain.com/wp-cron.php?doing_wp_cron >/dev/null 2>&1

Advanced Considerations and Customizations

1. Filtering and Parameters

Enhance the script to accept parameters for date ranges, specific events, payment statuses, etc. This can be done via URL parameters if accessed via an admin page, or arguments if using WP-CLI or a custom API endpoint.

// Example: Adding filters to the SQL query based on $_GET parameters
$filter_event = isset($_GET['event']) ? sanitize_text_field($_GET['event']) : null;
$filter_status = isset($_GET['status']) ? sanitize_text_field($_GET['status']) : null;
$date_conditions = [];
$query_params = [];

if ($filter_event) {
    $date_conditions[] = "event_name = %s";
    $query_params[] = $filter_event;
}
if ($filter_status) {
    $date_conditions[] = "payment_status = %s";
    $query_params[] = $filter_status;
}

$where_clause = implode(' AND ', $date_conditions);
if (!empty($where_clause)) {
    $where_clause = " AND " . $where_clause;
}

$sql = "SELECT ... FROM {$table_name} WHERE registration_date BETWEEN %s AND %s {$where_clause} ORDER BY registration_date ASC";
$sql = $wpdb->prepare($sql, $start_date, $end_date, ...$query_params); // Use splat operator for additional params
$tickets = $wpdb->get_results($sql);
?>

2. Data Validation and Error Handling

Implement robust error handling. Check database connection, query results, and file write permissions. Log errors to a file for debugging.

try {
    // ... database query ...
    if ($wpdb->last_error) {
        throw new Exception("Database query failed: " . $wpdb->last_error);
    }
    if (empty($tickets)) {
        // Handle no data found scenario
        error_log("No tickets found for the specified period.");
        // Optionally, generate an empty report or skip generation
        return;
    }
    // ... PDF generation ...
    // For file output:
    $output_path = '...';
    if ($pdf->Output($output_path, 'F') === false) {
         throw new Exception("Failed to save PDF to {$output_path}. Check permissions.");
    }
} catch (Exception $e) {
    error_log("Compliance Report Error: " . $e->getMessage());
    // Handle error appropriately, e.g., notify admin
    // WP_CLI::error("Report generation failed: " . $e->getMessage()); // If using WP-CLI
}
?>

3. Security Considerations

If the report generation is accessible via a URL (e.g., an admin page), ensure proper authentication and authorization checks are in place. Sanitize all user inputs used in database queries. Avoid exposing sensitive data in the report unless strictly necessary and properly secured.

4. Report Content Customization

Tailor the report content to specific compliance requirements. This might involve:

  • Including specific fields (e.g., attendee email, IP address – with privacy considerations).
  • Calculating derived fields (e.g., total revenue per event).
  • Adding conditional formatting (e.g., highlighting overdue payments).
  • Embedding digital signatures or watermarks.

For complex layouts or dynamic content, consider FPDF’s advanced features like multi-cell text wrapping, image embedding, and custom fonts.

Conclusion

Automating compliance reporting for custom event ticket registers in WordPress significantly reduces manual effort and improves accuracy. By leveraging PHP, FPDF, and WordPress integration methods like WP-CLI or WP Cron, you can build a robust and maintainable solution. Remember to adapt the database queries and report structure to your specific needs and always prioritize security and error handling.

Recent Posts

Top Categories

Our Products

Our Services