Implementing automated compliance reporting for custom custom subscription logs ledgers using custom PHP-Spreadsheet exports

Database Schema for Subscription Ledger Data

To implement automated compliance reporting, we first need a robust database schema to store our subscription ledger data. This schema should capture essential details for each transaction, including user identifiers, subscription plan, start/end dates, payment status, and any relevant audit trails. For this example, we’ll assume a MySQL database.

Consider the following table structure:

CREATE TABLE subscription_ledger (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    subscription_plan_id INT UNSIGNED NOT NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NULL,
    renewal_date DATETIME NULL,
    status ENUM('active', 'cancelled', 'expired', 'pending') NOT NULL DEFAULT 'pending',
    payment_status ENUM('paid', 'failed', 'refunded', 'pending') NOT NULL DEFAULT 'pending',
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_subscription_plan_id (subscription_plan_id),
    INDEX idx_start_date (start_date),
    INDEX idx_end_date (end_date),
    INDEX idx_status (status)
);

CREATE TABLE subscription_plans (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    price DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    billing_cycle ENUM('monthly', 'annually', 'quarterly') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ledger_id BIGINT UNSIGNED NULL,
    action VARCHAR(255) NOT NULL,
    details TEXT NULL,
    performed_by VARCHAR(255) NULL,
    performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ledger_id (ledger_id),
    INDEX idx_action (action)
);

PHP Implementation for Data Export

We will leverage the popular PhpSpreadsheet library to generate our compliance reports in various formats, such as XLSX, CSV, or ODS. This library provides a powerful and flexible API for creating and manipulating spreadsheet documents.

First, ensure you have PhpSpreadsheet installed via Composer:

composer require phpoffice/phpspreadsheet

Next, let’s create a PHP script that fetches data from our subscription_ledger and subscription_plans tables and exports it to an XLSX file. This script can be triggered by a cron job or a scheduled task within your WordPress environment.

<?php
require 'vendor/autoload.php'; // Adjust path as necessary

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

// Database connection details (replace with your actual credentials)
$dbHost = 'localhost';
$dbUser = 'your_db_user';
$dbPass = 'your_db_password';
$dbName = 'your_db_name';

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);
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

// Fetch subscription data
$stmt = $pdo->prepare("
    SELECT
        sl.id,
        sl.user_id,
        sp.name AS plan_name,
        sl.start_date,
        sl.end_date,
        sl.renewal_date,
        sl.status,
        sl.payment_status,
        sl.amount,
        sl.currency
    FROM subscription_ledger sl
    JOIN subscription_plans sp ON sl.subscription_plan_id = sp.id
    ORDER BY sl.start_date DESC
");
$stmt->execute();
$subscriptions = $stmt->fetchAll();

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

// Set document properties
$spreadsheet->getProperties()
    ->setCreator('Your Company Name')
    ->setLastModifiedBy('Your Company Name')
    ->setTitle('Subscription Compliance Report')
    ->setSubject('Automated Subscription Ledger Export')
    ->setDescription('Compliance report of subscription transactions.')
    ->setKeywords('compliance report subscription ledger')
    ->setCategory('Reporting');

// Add header row
$header = ['Ledger ID', 'User ID', 'Plan Name', 'Start Date', 'End Date', 'Renewal Date', 'Status', 'Payment Status', 'Amount', 'Currency'];
$sheet->fromArray([$header], NULL, 'A1');

// Apply header styling
$headerStyle = [
    'font' => ['bold' => true],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['rgb' => 'D3D3D3'], // Light gray
    ],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
];
$sheet->getStyle('A1:' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($header)) . '1')->applyFromArray($headerStyle);

// Add data rows
$rowNum = 2;
foreach ($subscriptions as $subscription) {
    $sheet->setCellValue('A' . $rowNum, $subscription['id']);
    $sheet->setCellValue('B' . $rowNum, $subscription['user_id']);
    $sheet->setCellValue('C' . $rowNum, $subscription['plan_name']);
    $sheet->setCellValue('D' . $rowNum, $subscription['start_date']);
    $sheet->setCellValue('E' . $rowNum, $subscription['end_date']);
    $sheet->setCellValue('F' . $rowNum, $subscription['renewal_date']);
    $sheet->setCellValue('G' . $rowNum, $subscription['status']);
    $sheet->setCellValue('H' . $rowNum, $subscription['payment_status']);
    $sheet->setCellValue('I' . $rowNum, $subscription['amount']);
    $sheet->setCellValue('J' . $rowNum, $subscription['currency']);
    $rowNum++;
}

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

// Set date format for date columns
$sheet->getStyle('D2:F' . ($rowNum - 1))->getNumberFormat()->setFormatCode('yyyy-mm-dd hh:mm:ss');

// Set currency format for amount column
$sheet->getStyle('I2:I' . ($rowNum - 1))->getNumberFormat()->setFormatCode('$#,##0.00'); // Example for USD

// Define the output filename
$filename = 'subscription_compliance_report_' . date('Ymd_His') . '.xlsx';

// Save the spreadsheet
$writer = new Xlsx($spreadsheet);
$writer->save($filename);

echo "Compliance report generated successfully: " . $filename;
?>

Automating Report Generation with Cron Jobs

To ensure regular and automated compliance reporting, you can schedule the PHP script using cron jobs. This is a standard Unix utility that allows you to schedule commands or scripts to run automatically at specified intervals.

First, ensure your PHP executable is in your system’s PATH or know its full path. You can find it by running which php in your terminal.

Edit your crontab file using the command:

crontab -e

Add a line to schedule your script. For example, to run the script daily at 2:00 AM:

0 2 * * * /usr/bin/php /path/to/your/script/generate_report.php >> /path/to/your/logs/cron.log 2>&1

Explanation of the cron entry:

• 0 2 * * *: This specifies the schedule: 0 minutes, 2 hours, any day of the month, any month, any day of the week.
• /usr/bin/php: The full path to your PHP executable.
• /path/to/your/script/generate_report.php: The full path to your PHP script that generates the report.
• >> /path/to/your/logs/cron.log 2>&1: This redirects both standard output and standard error to a log file, which is crucial for debugging.

Make sure the output directory for the generated XLSX file is writable by the user running the cron job. You might also want to implement a log rotation strategy for cron.log to prevent it from growing too large.

Integrating with WordPress Hooks (Advanced)

For a more integrated WordPress solution, you can trigger report generation via WordPress actions or scheduled events using WP-Cron. This avoids direct server cron job management and keeps the logic within your WordPress theme’s functions.php or a custom plugin.

First, include the PhpSpreadsheet library in your WordPress environment. The best practice is to include it within a custom plugin or a must-use plugin to avoid conflicts and ensure it’s available.

// In your custom plugin's main file or functions.php
require_once 'vendor/autoload.php'; // Adjust path based on your plugin structure

// Function to generate the report
function generate_subscription_compliance_report() {
    // ... (Database connection and PhpSpreadsheet logic from the previous example) ...

    // Instead of saving to a file directly, you might want to:
    // 1. Store it in the WordPress uploads directory.
    // 2. Email it to a specific recipient.
    // 3. Upload it to a cloud storage service (e.g., S3).

    $upload_dir = wp_upload_dir();
    $filename = 'subscription_compliance_report_' . date('Ymd_His') . '.xlsx';
    $filepath = $upload_dir['basedir'] . '/' . $filename;

    $writer = new Xlsx($spreadsheet);
    $writer->save($filepath);

    // Example: Send an email with the report attached
    $to = '[email protected]';
    $subject = 'Automated Subscription Compliance Report - ' . date('Y-m-d');
    $body = 'Please find the attached subscription compliance report.';

    // Use WordPress mail function
    wp_mail($to, $subject, $body, [], [$filepath]);

    // Clean up the temporary file if not needed after email
    // unlink($filepath);

    return true; // Indicate success
}

// Schedule the report generation using WP-Cron
if (!wp_next_scheduled('daily_compliance_report_event')) {
    wp_schedule_event(time(), 'daily', 'daily_compliance_report_event');
}

// Hook the function to the scheduled event
add_action('daily_compliance_report_event', 'generate_subscription_compliance_report');

// Optional: Add a manual trigger for testing
add_action('admin_post_generate_report', 'handle_manual_report_generation');
function handle_manual_report_generation() {
    if (current_user_can('manage_options')) { // Ensure user has permissions
        generate_subscription_compliance_report();
        wp_redirect(admin_url('admin.php?page=your-plugin-page&message=report_generated')); // Redirect with a message
        exit;
    }
}

// Add a menu item for manual trigger (example for a plugin)
add_action('admin_menu', function() {
    add_menu_page(
        'Compliance Reports',
        'Compliance Reports',
        'manage_options',
        'compliance-reports',
        'render_compliance_report_page',
        'dashicons-chart-bar',
        80
    );
});

function render_compliance_report_page() {
    ?>
    <div class="wrap">
        <h1>Compliance Reports</h1>
        <p>This section allows you to manage automated compliance reports.</p>
        <p>
            <a href="" class="button button-primary">Generate Report Now</a>
        </p>
        
            <div class="notice notice-success is-dismissible"><p>Report generated and emailed successfully.</p></div>
        
    </div>
    



Key considerations for the WordPress integration:



WP-Cron Limitations: WP-Cron is triggered by website traffic. If your site has low traffic, the scheduled event might not run precisely on time. For critical, time-sensitive reports, a server-level cron job is more reliable.
Security: Ensure that the script is only accessible by authorized users and that database credentials are kept secure. Use WordPress's built-in security functions like nonces for manual triggers.
Error Handling and Logging: Implement robust error handling and logging within your PHP script. For WP-Cron, you can use WordPress's built-in logging mechanisms or a dedicated logging plugin.
File Management: Decide on a strategy for managing generated report files. This could involve storing them in the WordPress uploads directory, deleting old reports, or archiving them.



Advanced Reporting Features and Considerations



Beyond basic data export, consider these advanced features for more comprehensive compliance reporting:



Filtering and Date Ranges: Allow users to specify date ranges or filter by subscription status, payment status, or user ID when generating reports. This can be implemented via GET parameters in the URL for manual triggers or as form inputs in an admin page.
Audit Trail Integration: Include relevant data from the audit_log table in your reports. This provides a historical record of changes and actions related to subscriptions, which is vital for compliance.
Data Validation: Implement checks to ensure data integrity before export. For example, verify that amounts are positive, dates are logical, and required fields are populated.
Different Export Formats: Offer options to export in CSV, ODS, or PDF formats by using different writers provided by PhpSpreadsheet (e.g., \PhpOffice\PhpSpreadsheet\Writer\Csv, \PhpOffice\PhpSpreadsheet\Writer\Ods, \PhpOffice\PhpSpreadsheet\Writer\Pdf\DomPDF).
Secure Storage and Distribution: For sensitive data, consider encrypting reports before storage or transmission. Integrate with secure cloud storage solutions (e.g., AWS S3, Google Cloud Storage) or secure file transfer protocols (SFTP).
Version Control for Reports: Implement a system for versioning your reports, especially if they are used for long-term archival or regulatory purposes.



By combining a well-structured database schema with the power of PhpSpreadsheet and a reliable scheduling mechanism (either server cron or WP-Cron), you can build a robust automated compliance reporting system tailored to your custom subscription ledger data.