Implementing automated compliance reporting for custom knowledge base document categories ledgers using FPDF customized scripts

Automating Compliance Reporting for Custom Knowledge Base Ledgers with FPDF

Maintaining auditable ledgers for custom knowledge base document categories is a critical compliance requirement for many organizations. This post details a robust, automated solution using PHP and the FPDF library to generate compliant PDF reports directly from your WordPress database. We’ll focus on a scenario where you need to track document creation, modification, and access permissions for specific categories, ensuring a clear audit trail.

Database Schema for Document Category Ledgers

Before generating reports, we need a structured way to store ledger information. Assuming you have a custom post type (e.g., ‘kb_document’) and a taxonomy for categories (e.g., ‘kb_category’), we’ll augment this with a custom table to log ledger events. This table will store details about each significant action related to documents within specific categories.

Here’s a sample SQL schema for the ledger table:

CREATE TABLE wp_kb_ledger (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    document_id BIGINT(20) UNSIGNED NOT NULL,
    category_id BIGINT(20) UNSIGNED NOT NULL,
    user_id BIGINT(20) UNSIGNED NULL,
    action VARCHAR(50) NOT NULL,
    timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    details TEXT NULL,
    PRIMARY KEY (id),
    KEY document_id (document_id),
    KEY category_id (category_id),
    KEY user_id (user_id),
    KEY action (action),
    KEY timestamp (timestamp)
);

The action field can store values like ‘created’, ‘modified’, ‘viewed’, ‘permission_changed’, etc. The details field can capture specific changes or user roles involved.

FPDF Script for Report Generation

We’ll create a PHP script that queries the ledger table and formats the data into a PDF report. This script can be triggered via a cron job or a custom WordPress admin page.

First, ensure you have the FPDF library installed. You can download it from the official FPDF website or use Composer:

composer require setasign/fpdf

Now, let’s craft the PHP script. This example assumes you’re running this within a WordPress environment to access $wpdb.

<?php
/**
 * Generates a compliance report for KB document category ledgers.
 *
 * This script queries the wp_kb_ledger table and outputs a PDF report.
 * It can be adapted for specific date ranges, categories, or users.
 */

// Ensure WordPress environment is loaded if running outside WP context
// require_once( $_SERVER['DOCUMENT_ROOT'] . '/wp-load.php' );

// Include FPDF library
require_once( 'path/to/fpdf/fpdf.php' ); // Adjust path as necessary

class PDF_KB_Compliance_Report extends FPDF {
    private $reportTitle = 'Knowledge Base Document Category Ledger Report';
    private $categoryName = 'All Categories';
    private $startDate = null;
    private $endDate = null;

    public function setReportTitle($title) {
        $this->reportTitle = $title;
    }

    public function setCategoryName($name) {
        $this->categoryName = $name;
    }

    public function setDateRange($start, $end) {
        $this->startDate = $start;
        $this->endDate = $end;
    }

    // Page header
    function Header() {
        // Logo
        // $this->Image('path/to/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,$this->reportTitle,0,0,'C');
        // Line break
        $this->Ln(20);

        // Report Details
        $this->SetFont('Arial','B',10);
        $this->Cell(0,10,"Category: " . $this->categoryName,0,1);
        if ($this->startDate && $this->endDate) {
            $this->Cell(0,10,"Date Range: " . $this->startDate . " to " . $this->endDate,0,1);
        }
        $this->Ln(10);

        // Table header
        $this->SetFont('Arial','B',8);
        $this->SetFillColor(220,220,220);
        $this->Cell(30,10,'Timestamp',1,0,'C',true);
        $this->Cell(40,10,'Document ID',1,0,'C',true);
        $this->Cell(50,10,'Action',1,0,'C',true);
        $this->Cell(40,10,'User ID',1,0,'C',true);
        $this->Cell(30,10,'Category ID',1,0,'C',true);
        $this->Ln();
    }

    // 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');
    }

    // Load data
    function LoadData($file) {
        // Read file lines
        $lines = file($file);
        $data = array();
        foreach($lines as $line)
            $data[] = explode(';',trim($line));
        return $data;
    }

    // Simple table
    function BasicTable($header, $data) {
        // Header
        $this->SetFont('Arial','B',8);
        $this->SetFillColor(220,220,220);
        foreach($header as $col)
            $this->Cell(40,7,$col,1,0,'C',true);
        $this->Ln();
        // Data
        $this->SetFont('Arial','',8);
        foreach($data as $row) {
            foreach($row as $col)
                $this->Cell(40,6,$col,1);
            $this->Ln();
        }
    }

    // More detailed table with ledger data
    function LedgerTable($ledger_data) {
        $this->SetFont('Arial','',8);
        $fill = false;
        foreach($ledger_data as $row) {
            $this->Cell(30,10,$row->'timestamp',1,0,'L',$fill);
            $this->Cell(40,10,$row->'document_id',1,0,'L',$fill);
            $this->Cell(50,10,$row->'action',1,0,'L',$fill);
            $this->Cell(40,10,($row->'user_id'] ? $row->'user_id' : 'N/A'),1,0,'L',$fill);
            $this->Cell(30,10,$row->'category_id',1,0,'L',$fill);
            $this->Ln();
            $fill = !$fill;
        }
    }
}

// --- Report Generation Logic ---

global $wpdb;
$ledger_table = $wpdb->prefix . 'kb_ledger';

// Parameters for filtering (example: get last 7 days)
$endDate = date('Y-m-d H:i:s');
$startDate = date('Y-m-d H:i:s', strtotime('-7 days'));
$targetCategoryID = null; // Set to a specific category ID if needed
$targetCategoryName = 'All Categories';

// Fetch ledger data
$query = "SELECT * FROM {$ledger_table} WHERE 1=1";
if ($startDate) {
    $query .= $wpdb->prepare(" AND timestamp >= %s", $startDate);
}
if ($endDate) {
    $query .= $wpdb->prepare(" AND timestamp <= %s", $endDate);
}
if ($targetCategoryID !== null) {
    $query .= $wpdb->prepare(" AND category_id = %d", $targetCategoryID);
    $category = get_term($targetCategoryID, 'kb_category'); // Assuming 'kb_category' is your taxonomy
    if ($category && !is_wp_error($category)) {
        $targetCategoryName = $category->name;
    }
}
$query .= " ORDER BY timestamp DESC";

$ledger_data = $wpdb->get_results($query);

// Create PDF
$pdf = new PDF_KB_Compliance_Report();
$pdf->AliasNbPages();
$pdf->SetTitle($targetCategoryName . " Ledger Report");
$pdf->setReportTitle($targetCategoryName . " Ledger Report");
$pdf->setCategoryName($targetCategoryName);
$pdf->setDateRange($startDate, $endDate);
$pdf->AddPage();

// Add ledger table
$pdf->LedgerTable($ledger_data);

// Output PDF
$pdf->Output('kb_compliance_report_' . date('YmdHis') . '.pdf', 'I'); // 'I' for inline display, 'D' for download

?>

Integrating with WordPress

To make this script accessible and manageable within WordPress, consider these integration strategies:

• Custom Admin Page: Create a new menu item under your WordPress admin dashboard. This page can host a form to select date ranges, categories, and users, then trigger the PDF generation. Use WordPress hooks like admin_menu and admin_init.
• Cron Jobs (WP-Cron): Schedule the script to run automatically at regular intervals (e.g., daily, weekly). You can use wp_schedule_event to hook into WP-Cron and then use wp_remote_get or a direct include to execute your report generation script. The generated PDF can be saved to the uploads directory or emailed.
• Shortcode: For on-demand generation, a shortcode could be placed on a specific page, allowing authorized users to click a link to download the report.

Enhancing the Report

The provided script is a foundation. For a production-ready compliance report, consider these enhancements:

• User Information: Instead of just user_id, fetch and display the username or role for better readability. You can use get_user_by('id', $row->user_id).
• Document Titles: Include the document title (e.g., from get_the_title($row->document_id)) for more context.
• Action Details: Parse and display the details field more intelligently. For ‘permission_changed’, list the old and new permissions.
• Filtering Options: Implement robust filtering for specific users, document types, or actions.
• Error Handling: Add comprehensive error checking for database queries and file operations.
• Security: Ensure only authorized users can access and generate reports. Use WordPress nonces and capability checks.
• Internationalization: If your site supports multiple languages, use WordPress internationalization functions (__(), _e()) for all translatable strings.
• Styling: Customize the FPDF output with custom fonts, colors, and more sophisticated table layouts to meet specific branding or compliance guidelines.

Example: Adding Usernames and Document Titles

Let’s modify the LedgerTable method to include more context:

    // More detailed table with ledger data
    function LedgerTable($ledger_data) {
        global $wpdb; // Access $wpdb for user and post queries

        $this->SetFont('Arial','',8);
        $fill = false;
        foreach($ledger_data as $row) {
            // Fetch user display name
            $user_info = $row->user_id ? get_user_by('id', $row->user_id) : null;
            $user_display_name = $user_info ? $user_info->display_name : 'N/A';

            // Fetch document title
            $document_title = $row->document_id ? get_the_title($row->document_id) : 'N/A';
            if (is_wp_error($document_title)) { // Handle potential errors from get_the_title
                $document_title = 'Error fetching title';
            }

            $this->Cell(30,10,$row->'timestamp',1,0,'L',$fill);
            $this->Cell(40,10,$row->'document_id' . ' (' . substr($document_title, 0, 20) . '...)',1,0,'L',$fill); // Truncate title for brevity
            $this->Cell(50,10,$row->'action',1,0,'L',$fill);
            $this->Cell(40,10,$user_display_name,1,0,'L',$fill);
            $this->Cell(30,10,$row->'category_id',1,0,'L',$fill);
            $this->Ln();
            $fill = !$fill;
        }
    }

This enhanced version provides richer context within the PDF, making it significantly more useful for compliance audits. Remember to adjust the path to the FPDF library and integrate the script securely within your WordPress workflow.