Implementing automated compliance reporting for custom affiliate click tracking logs ledgers using dompdf library

Leveraging DOMPDF for Automated Affiliate Click Log Compliance Reporting

Maintaining auditable logs for affiliate click tracking is paramount for compliance, especially when dealing with financial transactions or regulatory requirements. This post details a robust method for generating automated, PDF-based compliance reports directly from custom affiliate click logs, utilizing the DOMPDF library within a WordPress environment. We’ll focus on a practical PHP implementation that transforms raw log data into a structured, human-readable, and machine-verifiable report.

Prerequisites and Setup

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

A WordPress installation.
Composer installed for PHP dependency management.
An existing system for logging affiliate clicks. For this example, we’ll assume logs are stored in a structured format, perhaps a custom database table or a CSV file.

First, install the DOMPDF library using Composer. It’s best practice to manage external libraries outside of the core WordPress structure, typically in a dedicated plugin or theme directory.

Composer Installation

Navigate to your plugin or theme directory in your terminal and run:

composer require dompdf/dompdf

This will download DOMPDF and its dependencies into a `vendor` directory. You’ll need to include the Composer autoloader in your PHP script.

Affiliate Log Data Structure

For this example, let’s assume our affiliate click logs are stored in a custom WordPress table named wp_affiliate_clicks with the following schema:

CREATE TABLE wp_affiliate_clicks (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    affiliate_id INT(11) NOT NULL,
    campaign_id INT(11) NOT NULL,
    click_timestamp DATETIME NOT NULL,
    user_ip VARCHAR(45) NOT NULL,
    user_agent TEXT NOT NULL,
    PRIMARY KEY (id),
    KEY idx_affiliate_timestamp (affiliate_id, click_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The report will aggregate clicks by affiliate and campaign over a specified date range.

PHP Script for Report Generation

We’ll create a PHP script, perhaps as part of a custom plugin or a standalone script executed via WP-CLI or a cron job, to fetch the data and generate the PDF. This script will need to include the Composer autoloader and instantiate DOMPDF.

Core Report Generation Logic

// Assuming this script is run within a WordPress environment or has access to WP functions
// Include Composer autoloader
require_once 'path/to/your/vendor/autoload.php'; // Adjust path as necessary

use Dompdf\Dompdf;
use Dompdf\Options;

/**
 * Generates a compliance report PDF from affiliate click logs.
 *
 * @param string $start_date The start date for the report (YYYY-MM-DD).
 * @param string $end_date   The end date for the report (YYYY-MM-DD).
 * @return string|false The PDF content as a string, or false on failure.
 */
function generate_affiliate_compliance_report(string $start_date, string $end_date): string|false
{
    global $wpdb; // Access WordPress database object

    // Validate dates
    if (!strtotime($start_date) || !strtotime($end_date)) {
        error_log("Invalid date format provided for report generation.");
        return false;
    }

    $start_datetime = date('Y-m-d 00:00:00', strtotime($start_date));
    $end_datetime = date('Y-m-d 23:59:59', strtotime($end_date));

    // Fetch aggregated click data
    $table_name = $wpdb->prefix . 'affiliate_clicks';
    $sql = $wpdb->prepare(
        "SELECT
            affiliate_id,
            campaign_id,
            COUNT(*) as total_clicks,
            MIN(click_timestamp) as first_click,
            MAX(click_timestamp) as last_click
        FROM {$table_name}
        WHERE click_timestamp BETWEEN %s AND %s
        GROUP BY affiliate_id, campaign_id
        ORDER BY affiliate_id, campaign_id, first_click",
        $start_datetime,
        $end_datetime
    );

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

    if (!$results) {
        // Handle case with no data, perhaps generate an empty report or return false
        error_log("No affiliate click data found for the period: {$start_date} to {$end_date}");
        // Optionally, generate a report indicating no data
        // return generate_empty_report($start_date, $end_date);
        return false;
    }

    // Configure DOMPDF
    $options = new Options();
    $options->set('isHtml5ParserEnabled', true);
    $options->set('isRemoteEnabled', false); // Security: disable remote resource loading

    $dompdf = new Dompdf($options);

    // Build HTML content for the PDF
    $html = '
    
    
    
        
        Affiliate Click Compliance Report
        
    
    
        Affiliate Click Compliance Report
        
            Report Period: ' . esc_html($start_date) . ' to ' . esc_html($end_date) . '
        
        ';

    foreach ($results as $row) {
        $html .= '
                ';
    }

    $html .= '
            
            
                
                    Affiliate ID
                    Campaign ID
                    Total Clicks
                    First Click
                    Last Click
                
            
            
                    ' . esc_html($row->affiliate_id) . '
                    ' . esc_html($row->campaign_id) . '
                    ' . esc_html($row->total_clicks) . '
                    ' . esc_html($row->first_click) . '
                    ' . esc_html($row->last_click) . '
                
        
        
            Generated on: ' . date('Y-m-d H:i:s') . '
            This report is for compliance purposes and reflects aggregated click data.
        
    
    ';

    // Load HTML into DOMPDF
    $dompdf->loadHtml($html);

    // (Optional) Set paper size and orientation
    $dompdf->setPaper('A4', 'portrait');

    // Render the HTML as PDF
    $dompdf->render();

    // Output the generated PDF (as a string)
    return $dompdf->output();
}

/**
 * Example of how to use the function and save the PDF.
 * This could be triggered by an admin action, WP-Cron, or WP-CLI.
 */
function trigger_report_generation() {
    $report_start_date = '2023-10-01'; // Example start date
    $report_end_date = '2023-10-31';   // Example end date
    $output_filename = 'affiliate_compliance_report_' . $report_start_date . '_' . $report_end_date . '.pdf';
    $upload_dir = wp_upload_dir(); // Get WordPress upload directory info

    $pdf_content = generate_affiliate_compliance_report($report_start_date, $report_end_date);

    if ($pdf_content) {
        // Save the PDF to the uploads directory
        $file_path = $upload_dir['basedir'] . '/' . $output_filename;
        if (file_put_contents($file_path, $pdf_content) !== false) {
            // Log success or notify admin
            error_log("Affiliate compliance report generated successfully: " . $file_path);
        } else {
            error_log("Failed to save affiliate compliance report to: " . $file_path);
        }
    } else {
        error_log("Affiliate compliance report generation failed for dates {$report_start_date} to {$report_end_date}.");
    }
}

// To test, you could hook this into an admin action or run it via WP-CLI:
// add_action('admin_init', 'trigger_report_generation'); // For testing, remove in production
// Or via WP-CLI:
// wp eval 'trigger_report_generation();'

Affiliate ID	Campaign ID	Total Clicks	First Click	Last Click
' . esc_html($row->affiliate_id) . '	' . esc_html($row->campaign_id) . '	' . esc_html($row->total_clicks) . '	' . esc_html($row->first_click) . '	' . esc_html($row->last_click) . '

Integrating with WordPress

The provided PHP script uses global $wpdb for database access, which is standard within WordPress. To make this report generation process automated and manageable:

Option 1: WP-Cron for Scheduled Reports

You can schedule the report generation using WordPress’s built-in cron system. Define a custom cron event and hook your report generation function to it.

/**
 * Schedule the affiliate report generation event.
 */
function schedule_affiliate_report_event() {
    if ( ! wp_next_scheduled( 'daily_affiliate_report_event' ) ) {
        // Schedule to run daily at 2 AM
        wp_schedule_event( time() + ( 2 * HOUR_IN_SECONDS ), 'daily', 'daily_affiliate_report_event' );
    }
}
add_action( 'wp', 'schedule_affiliate_report_event' );

/**
 * Hook for the daily affiliate report event.
 */
function run_daily_affiliate_report() {
    // Define the date range for the report (e.g., yesterday's data)
    $report_end_date = date('Y-m-d');
    $report_start_date = date('Y-m-d', strtotime('-1 day'));

    // Call the generation function
    $pdf_content = generate_affiliate_compliance_report($report_start_date, $report_end_date);

    if ($pdf_content) {
        $upload_dir = wp_upload_dir();
        $output_filename = 'affiliate_compliance_report_' . $report_start_date . '_' . $report_end_date . '.pdf';
        $file_path = $upload_dir['basedir'] . '/' . $output_filename;

        if (file_put_contents($file_path, $pdf_content) !== false) {
            // Log success
            error_log("Scheduled affiliate compliance report generated: " . $file_path);
            // Optionally, send an email notification to an administrator
            // wp_mail('[email protected]', 'Affiliate Report Generated', 'The daily affiliate compliance report has been generated and saved to ' . $file_path);
        } else {
            error_log("Scheduled affiliate compliance report failed to save to: " . $file_path);
        }
    } else {
        error_log("Scheduled affiliate compliance report generation failed for dates {$report_start_date} to {$report_end_date}.");
    }
}
add_action( 'daily_affiliate_report_event', 'run_daily_affiliate_report' );

// To unschedule:
// wp_clear_scheduled_hook( 'daily_affiliate_report_event' );

Remember that WP-Cron is triggered by site visits. For critical, time-sensitive reporting, a true server cron job that pings your WordPress site’s cron URL is more reliable.

Option 2: WP-CLI for On-Demand Reports

For manual generation or integration into deployment scripts, WP-CLI is an excellent choice. Create a custom WP-CLI command.

if ( class_exists( 'WP_CLI' ) ) {
    /**
     * Command to generate affiliate compliance reports.
     *
     * ## OPTIONS
     *
     * --start_date=
     * : The start date for the report (YYYY-MM-DD).
     *
     * --end_date=
     * : The end date for the report (YYYY-MM-DD).
     *
     * --output_dir=
     * : Directory to save the PDF. Defaults to WordPress uploads.
     *
     * ## EXAMPLES
     *
     * wp affiliate-report generate --start_date=2023-10-01 --end_date=2023-10-31
     * wp affiliate-report generate --start_date=2023-11-01 --end_date=2023-11-30 --output_dir=/tmp/reports
     */
    function generate_affiliate_report_cli( $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_dir = isset( $assoc_args['output_dir'] ) ? $assoc_args['output_dir'] : null;

        if ( ! $start_date || ! $end_date ) {
            WP_CLI::error( 'Both --start_date and --end_date parameters are required.' );
            return;
        }

        if ( ! strtotime( $start_date ) || !strtotime( $end_date ) ) {
            WP_CLI::error( 'Invalid date format. Please use YYYY-MM-DD.' );
            return;
        }

        $pdf_content = generate_affiliate_compliance_report( $start_date, $end_date );

        if ( ! $pdf_content ) {
            WP_CLI::error( 'Failed to generate PDF content. Check logs for details.' );
            return;
        }

        if ( ! $output_dir ) {
            $upload_dir_info = wp_upload_dir();
            $output_dir = $upload_dir_info['basedir'];
        }

        // Ensure output directory exists
        if ( ! wp_mkdir_p( $output_dir ) ) {
             WP_CLI::error( "Could not create or access output directory: {$output_dir}" );
             return;
        }

        $output_filename = 'affiliate_compliance_report_' . $start_date . '_' . $end_date . '.pdf';
        $file_path = trailingslashit( $output_dir ) . $output_filename;

        if ( file_put_contents( $file_path, $pdf_content ) !== false ) {
            WP_CLI::success( "Affiliate compliance report generated and saved to: {$file_path}" );
        } else {
            WP_CLI::error( "Failed to save report to: {$file_path}" );
        }
    }

    WP_CLI::add_command( 'affiliate-report generate', 'generate_affiliate_report_cli', array(
        'shortdesc' => 'Generates an affiliate click compliance report.',
        'synopsis'  => '[--start_date=] [--end_date=] [--output_dir=]',
    ) );
}

To use this, place the code within your plugin’s main file or a dedicated `cli-commands.php` file included by your plugin. Then, you can run commands like:

wp affiliate-report generate --start_date=2023-10-01 --end_date=2023-10-31

Security and Best Practices

When implementing automated reporting, consider these points:

Data Sanitization: Always sanitize data fetched from the database before outputting it into HTML, even if it’s just for a PDF. Use functions like esc_html().
DOMPDF Security: Disable remote file access in DOMPDF options (isRemoteEnabled = false) to prevent potential SSRF vulnerabilities if user-supplied data were ever to influence HTML content directly.
File Permissions: Ensure the directory where reports are saved has appropriate write permissions for the web server or CLI user, but is not world-writable.
Log Rotation/Archiving: Implement a strategy for managing generated PDF reports. Old reports might need to be archived or deleted to prevent disk space issues.
Error Handling: Robust error logging is crucial. Log failures during data fetching, PDF generation, or file saving.
Resource Management: For very large datasets, DOMPDF might consume significant memory and CPU. Consider paginating the data or generating reports for smaller date ranges if performance becomes an issue. You might also need to adjust PHP’s memory_limit and max_execution_time.
Audit Trail: Log when reports are generated, by whom (if manually triggered), and for which period. This adds another layer to your compliance audit.

Conclusion

By integrating DOMPDF into your WordPress workflow, you can automate the creation of professional, compliant PDF reports from your affiliate click tracking logs. Whether scheduled via WP-Cron or generated on-demand via WP-CLI, this approach provides a reliable mechanism for data verification and regulatory adherence, transforming raw logs into actionable compliance documents.