Implementing automated compliance reporting for custom real estate agent listings ledgers using custom PHP-Spreadsheet exports
/**
* Fetches listing data from a custom table.
*
* @param string $start_date
* @param string $end_date
* @return array
*/
private function get_listing_data_from_custom_table( $start_date, $end_date ) {
global $wpdb;
$table_name = $wpdb->prefix . 'real_estate_listings'; // Your custom table name
$query = $wpdb->prepare(
"SELECT
listing_id, agent_id, client_name, property_address, transaction_date,
sale_price, commission_amount, status, compliance_flags
FROM {$table_name}
WHERE transaction_date BETWEEN %s AND %s",
$start_date,
$end_date
);
$results = $wpdb->get_results( $query, ARRAY_A );
return $results ?: array();
}
Similarly, the formatting of data within the spreadsheet can be customized. For instance, you might want to format currency fields or parse complex `compliance_flags` into a more readable format. PHP-Spreadsheet offers extensive formatting options:
// Inside generate_compliance_report, after fetching data
$row_num = 2;
foreach ( $listing_data as $row_data ) {
// Example: Format currency
if ( isset( $row_data['sale_price'] ) && is_numeric( $row_data['sale_price'] ) ) {
$sheet->setCellValue( 'F' . $row_num, $row_data['sale_price'] );
$sheet->getStyle('F' . $row_num)->getNumberFormat()->setFormatCode('$#,##0.00'); // US Dollar format
} else {
$sheet->setCellValue( 'F' . $row_num, $row_data['sale_price'] ); // Keep as is if not numeric
}
// Example: Parse compliance flags if they are a serialized array
$compliance_text = '';
if ( ! empty( $row_data['compliance_flags'] ) ) {
$flags = maybe_unserialize( $row_data['compliance_flags'] ); // Use WordPress function for safety
if ( is_array( $flags ) ) {
$compliance_text = implode( ', ', $flags );
} else {
$compliance_text = $row_data['compliance_flags']; // Fallback
}
}
$sheet->setCellValue( 'I' . $row_num, $compliance_text );
// Set other cells
$sheet->setCellValue( 'A' . $row_num, $row_data['listing_id'] );
$sheet->setCellValue( 'B' . $row_num, $row_data['agent_id'] );
$sheet->setCellValue( 'C' . $row_num, $row_data['client_name'] );
$sheet->setCellValue( 'D' . $row_num, $row_data['property_address'] );
$sheet->setCellValue( 'E' . $row_num, $row_data['transaction_date'] );
$sheet->setCellValue( 'G' . $row_num, $row_data['commission_amount'] );
$sheet->setCellValue( 'H' . $row_num, $row_data['status'] );
$row_num++;
}
Security and Best Practices
When implementing this, always prioritize security:
- Nonce Verification: The example includes `wp_nonce_field` and `check_admin_referer` to prevent CSRF attacks.
- Input Sanitization: Use WordPress sanitization functions like `sanitize_text_field`, `sanitize_date`, etc., for all user inputs.
- Capability Checks: Ensure that only users with appropriate roles (e.g., ‘manage_options’) can access the reporting page and generate reports.
- Error Handling: Implement robust error handling for database queries, file operations, and Composer dependency issues.
- Large Datasets: For very large datasets, consider implementing pagination or background processing (e.g., using WP-Cron or a dedicated queue system) to avoid timeouts. PHP-Spreadsheet can consume significant memory.
- File Permissions: Ensure your web server has the necessary permissions to write temporary files if needed by the library, though `php://output` generally bypasses this.
By integrating PHP-Spreadsheet into your WordPress plugin, you can provide a powerful, automated solution for generating essential compliance reports, saving significant time and reducing the risk of errors for real estate agencies.
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
class RealEstate_Compliance_Reporter {
public function __construct() {
// Hook into admin menu to add a reporting page or link
add_action( 'admin_menu', array( $this, 'add_admin_menu' ) );
}
/**
* Add admin menu page for reporting.
*/
public function add_admin_menu() {
add_submenu_page(
'edit.php?post_type=agent_listing', // Parent slug (assuming 'agent_listing' post type)
__( 'Compliance Reports', 'real-estate-reporter' ),
__( 'Compliance Reports', 'real-estate-reporter' ),
'manage_options',
'compliance-reports',
array( $this, 'render_report_page' )
);
}
/**
* Render the reporting page UI.
*/
public function render_report_page() {
?>
<div class="wrap">
<h1><?php _e( 'Generate Compliance Reports', 'real-estate-reporter' ); ?></h1>
<form method="post" action="">
<input type="hidden" name="generate_report" value="1" />
<?php wp_nonce_field( 'generate_compliance_report_nonce' ); ?>
<table class="form-table">
<tr>
<th><label for="start_date"><?php _e( 'Start Date', 'real-estate-reporter' ); ?></label></th>
<td><input type="date" id="start_date" name="start_date" class="regular-text" /></td>
</tr>
<tr>
<th><label for="end_date"><?php _e( 'End Date', 'real-estate-reporter' ); ?></label></th>
<td><input type="date" id="end_date" name="end_date" class="regular-text" /></td>
</tr>
<tr>
<th><label for="report_format"><?php _e( 'Report Format', 'real-estate-reporter' ); ?></label></th>
<td>
<select id="report_format" name="report_format">
<option value="xlsx"><?php _e( 'Excel (XLSX)', 'real-estate-reporter' ); ?></option>
<option value="csv"><?php _e( 'CSV', 'real-estate-reporter' ); ?></option>
</select>
</td>
</tr>
</table>
<?php submit_button( __( 'Generate Report', 'real-estate-reporter' ) ); ?>
</form>
</div>
generate_compliance_report( $start_date, $end_date, $format );
}
}
/**
* Fetches listing data based on date range.
*
* @param string $start_date
* @param string $end_date
* @return array
*/
private function get_listing_data( $start_date, $end_date ) {
$args = array(
'post_type' => 'agent_listing', // Your custom post type
'posts_per_page' => -1, // Get all posts
'meta_query' => array(
'relation' => 'AND',
array(
'key' => 'transaction_date', // Custom field for transaction date
'value' => array( $start_date, $end_date ),
'type' => 'DATE',
'compare' => 'BETWEEN',
),
// Add other meta queries for filtering if necessary
),
);
$listings = get_posts( $args );
$data = array();
if ( ! empty( $listings ) ) {
foreach ( $listings as $listing ) {
$data[] = array(
'listing_id' => $listing->ID,
'agent_id' => get_post_meta( $listing->ID, 'agent_id', true ),
'client_name' => get_post_meta( $listing->ID, 'client_name', true ),
'property_address' => get_post_meta( $listing->ID, 'property_address', true ),
'transaction_date' => get_post_meta( $listing->ID, 'transaction_date', true ),
'sale_price' => get_post_meta( $listing->ID, 'sale_price', true ),
'commission_amount' => get_post_meta( $listing->ID, 'commission_amount', true ),
'status' => get_post_meta( $listing->ID, 'status', true ),
'compliance_flags' => get_post_meta( $listing->ID, 'compliance_flags', true ), // Assuming this is a serialized array or comma-separated string
);
}
}
return $data;
}
/**
* Generates and downloads the compliance report.
*
* @param string $start_date
* @param string $end_date
* @param string $format
*/
public function generate_compliance_report( $start_date, $end_date, $format = 'xlsx' ) {
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Set header row
$header = array(
'Listing ID',
'Agent ID',
'Client Name',
'Property Address',
'Transaction Date',
'Sale Price',
'Commission Amount',
'Status',
'Compliance Flags',
);
$sheet->fromArray( $header, NULL, 'A1' );
// Fetch and populate data
$listing_data = $this->get_listing_data( $start_date, $end_date );
if ( ! empty( $listing_data ) ) {
$row_num = 2; // Start from the second row
foreach ( $listing_data as $row_data ) {
// Ensure data is in the correct order as per header
$sheet->fromArray( array_values( $row_data ), NULL, 'A' . $row_num );
$row_num++;
}
}
// Apply some basic styling (optional)
$sheet->getStyle('A1:' . $sheet->getHighestColumn() . '1')->getFont()->setBold(true);
$sheet->getColumnDimension('A')->setWidth(10);
$sheet->getColumnDimension('B')->setWidth(10);
$sheet->getColumnDimension('C')->setWidth(30);
$sheet->getColumnDimension('D')->setWidth(50);
$sheet->getColumnDimension('E')->setWidth(15);
$sheet->getColumnDimension('F')->setWidth(15);
$sheet->getColumnDimension('G')->setWidth(15);
$sheet->getColumnDimension('H')->setWidth(15);
$sheet->getColumnDimension('I')->setWidth(40);
// Set filename
$filename_base = 'compliance_report_' . date('Ymd');
if ( ! empty( $start_date ) && ! empty( $end_date ) ) {
$filename_base .= '_' . $start_date . '_' . $end_date;
}
// Determine writer based on format
if ( 'csv' === strtolower( $format ) ) {
$writer = new Csv( $spreadsheet );
$filename = $filename_base . '.csv';
header( 'Content-Type: text/csv' );
header( 'Content-Disposition: attachment;filename="' . $filename . '"' );
} else { // Default to XLSX
$writer = new Xlsx( $spreadsheet );
$filename = $filename_base . '.xlsx';
header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
header( 'Content-Disposition: attachment;filename="' . $filename . '"' );
}
header('Cache-Control: max-age=0');
// Output the file
$writer->save( 'php://output' );
exit; // Important to exit after sending the file
}
}
// Instantiate the reporter class
new RealEstate_Compliance_Reporter();
?>
Customizing Data Fetching and Formatting
The `get_listing_data` method is where you’ll tailor the data retrieval to your specific needs. If your data is stored in a custom table rather than custom fields of a post type, you’ll need to use `global $wpdb;` and construct SQL queries. For example:
/**
* Fetches listing data from a custom table.
*
* @param string $start_date
* @param string $end_date
* @return array
*/
private function get_listing_data_from_custom_table( $start_date, $end_date ) {
global $wpdb;
$table_name = $wpdb->prefix . 'real_estate_listings'; // Your custom table name
$query = $wpdb->prepare(
"SELECT
listing_id, agent_id, client_name, property_address, transaction_date,
sale_price, commission_amount, status, compliance_flags
FROM {$table_name}
WHERE transaction_date BETWEEN %s AND %s",
$start_date,
$end_date
);
$results = $wpdb->get_results( $query, ARRAY_A );
return $results ?: array();
}
Similarly, the formatting of data within the spreadsheet can be customized. For instance, you might want to format currency fields or parse complex `compliance_flags` into a more readable format. PHP-Spreadsheet offers extensive formatting options:
// Inside generate_compliance_report, after fetching data
$row_num = 2;
foreach ( $listing_data as $row_data ) {
// Example: Format currency
if ( isset( $row_data['sale_price'] ) && is_numeric( $row_data['sale_price'] ) ) {
$sheet->setCellValue( 'F' . $row_num, $row_data['sale_price'] );
$sheet->getStyle('F' . $row_num)->getNumberFormat()->setFormatCode('$#,##0.00'); // US Dollar format
} else {
$sheet->setCellValue( 'F' . $row_num, $row_data['sale_price'] ); // Keep as is if not numeric
}
// Example: Parse compliance flags if they are a serialized array
$compliance_text = '';
if ( ! empty( $row_data['compliance_flags'] ) ) {
$flags = maybe_unserialize( $row_data['compliance_flags'] ); // Use WordPress function for safety
if ( is_array( $flags ) ) {
$compliance_text = implode( ', ', $flags );
} else {
$compliance_text = $row_data['compliance_flags']; // Fallback
}
}
$sheet->setCellValue( 'I' . $row_num, $compliance_text );
// Set other cells
$sheet->setCellValue( 'A' . $row_num, $row_data['listing_id'] );
$sheet->setCellValue( 'B' . $row_num, $row_data['agent_id'] );
$sheet->setCellValue( 'C' . $row_num, $row_data['client_name'] );
$sheet->setCellValue( 'D' . $row_num, $row_data['property_address'] );
$sheet->setCellValue( 'E' . $row_num, $row_data['transaction_date'] );
$sheet->setCellValue( 'G' . $row_num, $row_data['commission_amount'] );
$sheet->setCellValue( 'H' . $row_num, $row_data['status'] );
$row_num++;
}
Security and Best Practices
When implementing this, always prioritize security:
- Nonce Verification: The example includes `wp_nonce_field` and `check_admin_referer` to prevent CSRF attacks.
- Input Sanitization: Use WordPress sanitization functions like `sanitize_text_field`, `sanitize_date`, etc., for all user inputs.
- Capability Checks: Ensure that only users with appropriate roles (e.g., ‘manage_options’) can access the reporting page and generate reports.
- Error Handling: Implement robust error handling for database queries, file operations, and Composer dependency issues.
- Large Datasets: For very large datasets, consider implementing pagination or background processing (e.g., using WP-Cron or a dedicated queue system) to avoid timeouts. PHP-Spreadsheet can consume significant memory.
- File Permissions: Ensure your web server has the necessary permissions to write temporary files if needed by the library, though `php://output` generally bypasses this.
By integrating PHP-Spreadsheet into your WordPress plugin, you can provide a powerful, automated solution for generating essential compliance reports, saving significant time and reducing the risk of errors for real estate agencies.
<?php
/**
* Plugin Name: Real Estate Compliance Reporter
* Description: Automates compliance reporting for agent listings.
* Version: 1.0
* Author: Your Name
*/
// Ensure Composer autoloader is included
if ( file_exists( plugin_dir_path( __FILE__ ) . 'vendor/autoload.php' ) ) {
require_once plugin_dir_path( __FILE__ ) . 'vendor/autoload.php';
} else {
// Handle error: Composer dependencies not installed
add_action( 'admin_notices', function() {
echo '<div class="notice notice-error"><p>Error: Composer dependencies for Real Estate Compliance Reporter are not installed. Please run "composer install" in the plugin directory.</p></div>';
});
return;
}
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
// ... rest of your plugin code
?>
Implementing the Export Functionality
We’ll create a dedicated class or function to handle the data retrieval and spreadsheet generation. This will typically be hooked into an admin menu action to provide a downloadable link or button.
Let’s define a class, say `RealEstate_Compliance_Reporter`, within your plugin. This class will contain methods for fetching data and generating the report.
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
class RealEstate_Compliance_Reporter {
public function __construct() {
// Hook into admin menu to add a reporting page or link
add_action( 'admin_menu', array( $this, 'add_admin_menu' ) );
}
/**
* Add admin menu page for reporting.
*/
public function add_admin_menu() {
add_submenu_page(
'edit.php?post_type=agent_listing', // Parent slug (assuming 'agent_listing' post type)
__( 'Compliance Reports', 'real-estate-reporter' ),
__( 'Compliance Reports', 'real-estate-reporter' ),
'manage_options',
'compliance-reports',
array( $this, 'render_report_page' )
);
}
/**
* Render the reporting page UI.
*/
public function render_report_page() {
?>
<div class="wrap">
<h1><?php _e( 'Generate Compliance Reports', 'real-estate-reporter' ); ?></h1>
<form method="post" action="">
<input type="hidden" name="generate_report" value="1" />
<?php wp_nonce_field( 'generate_compliance_report_nonce' ); ?>
<table class="form-table">
<tr>
<th><label for="start_date"><?php _e( 'Start Date', 'real-estate-reporter' ); ?></label></th>
<td><input type="date" id="start_date" name="start_date" class="regular-text" /></td>
</tr>
<tr>
<th><label for="end_date"><?php _e( 'End Date', 'real-estate-reporter' ); ?></label></th>
<td><input type="date" id="end_date" name="end_date" class="regular-text" /></td>
</tr>
<tr>
<th><label for="report_format"><?php _e( 'Report Format', 'real-estate-reporter' ); ?></label></th>
<td>
<select id="report_format" name="report_format">
<option value="xlsx"><?php _e( 'Excel (XLSX)', 'real-estate-reporter' ); ?></option>
<option value="csv"><?php _e( 'CSV', 'real-estate-reporter' ); ?></option>
</select>
</td>
</tr>
</table>
<?php submit_button( __( 'Generate Report', 'real-estate-reporter' ) ); ?>
</form>
</div>
generate_compliance_report( $start_date, $end_date, $format );
}
}
/**
* Fetches listing data based on date range.
*
* @param string $start_date
* @param string $end_date
* @return array
*/
private function get_listing_data( $start_date, $end_date ) {
$args = array(
'post_type' => 'agent_listing', // Your custom post type
'posts_per_page' => -1, // Get all posts
'meta_query' => array(
'relation' => 'AND',
array(
'key' => 'transaction_date', // Custom field for transaction date
'value' => array( $start_date, $end_date ),
'type' => 'DATE',
'compare' => 'BETWEEN',
),
// Add other meta queries for filtering if necessary
),
);
$listings = get_posts( $args );
$data = array();
if ( ! empty( $listings ) ) {
foreach ( $listings as $listing ) {
$data[] = array(
'listing_id' => $listing->ID,
'agent_id' => get_post_meta( $listing->ID, 'agent_id', true ),
'client_name' => get_post_meta( $listing->ID, 'client_name', true ),
'property_address' => get_post_meta( $listing->ID, 'property_address', true ),
'transaction_date' => get_post_meta( $listing->ID, 'transaction_date', true ),
'sale_price' => get_post_meta( $listing->ID, 'sale_price', true ),
'commission_amount' => get_post_meta( $listing->ID, 'commission_amount', true ),
'status' => get_post_meta( $listing->ID, 'status', true ),
'compliance_flags' => get_post_meta( $listing->ID, 'compliance_flags', true ), // Assuming this is a serialized array or comma-separated string
);
}
}
return $data;
}
/**
* Generates and downloads the compliance report.
*
* @param string $start_date
* @param string $end_date
* @param string $format
*/
public function generate_compliance_report( $start_date, $end_date, $format = 'xlsx' ) {
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Set header row
$header = array(
'Listing ID',
'Agent ID',
'Client Name',
'Property Address',
'Transaction Date',
'Sale Price',
'Commission Amount',
'Status',
'Compliance Flags',
);
$sheet->fromArray( $header, NULL, 'A1' );
// Fetch and populate data
$listing_data = $this->get_listing_data( $start_date, $end_date );
if ( ! empty( $listing_data ) ) {
$row_num = 2; // Start from the second row
foreach ( $listing_data as $row_data ) {
// Ensure data is in the correct order as per header
$sheet->fromArray( array_values( $row_data ), NULL, 'A' . $row_num );
$row_num++;
}
}
// Apply some basic styling (optional)
$sheet->getStyle('A1:' . $sheet->getHighestColumn() . '1')->getFont()->setBold(true);
$sheet->getColumnDimension('A')->setWidth(10);
$sheet->getColumnDimension('B')->setWidth(10);
$sheet->getColumnDimension('C')->setWidth(30);
$sheet->getColumnDimension('D')->setWidth(50);
$sheet->getColumnDimension('E')->setWidth(15);
$sheet->getColumnDimension('F')->setWidth(15);
$sheet->getColumnDimension('G')->setWidth(15);
$sheet->getColumnDimension('H')->setWidth(15);
$sheet->getColumnDimension('I')->setWidth(40);
// Set filename
$filename_base = 'compliance_report_' . date('Ymd');
if ( ! empty( $start_date ) && ! empty( $end_date ) ) {
$filename_base .= '_' . $start_date . '_' . $end_date;
}
// Determine writer based on format
if ( 'csv' === strtolower( $format ) ) {
$writer = new Csv( $spreadsheet );
$filename = $filename_base . '.csv';
header( 'Content-Type: text/csv' );
header( 'Content-Disposition: attachment;filename="' . $filename . '"' );
} else { // Default to XLSX
$writer = new Xlsx( $spreadsheet );
$filename = $filename_base . '.xlsx';
header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
header( 'Content-Disposition: attachment;filename="' . $filename . '"' );
}
header('Cache-Control: max-age=0');
// Output the file
$writer->save( 'php://output' );
exit; // Important to exit after sending the file
}
}
// Instantiate the reporter class
new RealEstate_Compliance_Reporter();
?>
Customizing Data Fetching and Formatting
The `get_listing_data` method is where you’ll tailor the data retrieval to your specific needs. If your data is stored in a custom table rather than custom fields of a post type, you’ll need to use `global $wpdb;` and construct SQL queries. For example:
/**
* Fetches listing data from a custom table.
*
* @param string $start_date
* @param string $end_date
* @return array
*/
private function get_listing_data_from_custom_table( $start_date, $end_date ) {
global $wpdb;
$table_name = $wpdb->prefix . 'real_estate_listings'; // Your custom table name
$query = $wpdb->prepare(
"SELECT
listing_id, agent_id, client_name, property_address, transaction_date,
sale_price, commission_amount, status, compliance_flags
FROM {$table_name}
WHERE transaction_date BETWEEN %s AND %s",
$start_date,
$end_date
);
$results = $wpdb->get_results( $query, ARRAY_A );
return $results ?: array();
}
Similarly, the formatting of data within the spreadsheet can be customized. For instance, you might want to format currency fields or parse complex `compliance_flags` into a more readable format. PHP-Spreadsheet offers extensive formatting options:
// Inside generate_compliance_report, after fetching data
$row_num = 2;
foreach ( $listing_data as $row_data ) {
// Example: Format currency
if ( isset( $row_data['sale_price'] ) && is_numeric( $row_data['sale_price'] ) ) {
$sheet->setCellValue( 'F' . $row_num, $row_data['sale_price'] );
$sheet->getStyle('F' . $row_num)->getNumberFormat()->setFormatCode('$#,##0.00'); // US Dollar format
} else {
$sheet->setCellValue( 'F' . $row_num, $row_data['sale_price'] ); // Keep as is if not numeric
}
// Example: Parse compliance flags if they are a serialized array
$compliance_text = '';
if ( ! empty( $row_data['compliance_flags'] ) ) {
$flags = maybe_unserialize( $row_data['compliance_flags'] ); // Use WordPress function for safety
if ( is_array( $flags ) ) {
$compliance_text = implode( ', ', $flags );
} else {
$compliance_text = $row_data['compliance_flags']; // Fallback
}
}
$sheet->setCellValue( 'I' . $row_num, $compliance_text );
// Set other cells
$sheet->setCellValue( 'A' . $row_num, $row_data['listing_id'] );
$sheet->setCellValue( 'B' . $row_num, $row_data['agent_id'] );
$sheet->setCellValue( 'C' . $row_num, $row_data['client_name'] );
$sheet->setCellValue( 'D' . $row_num, $row_data['property_address'] );
$sheet->setCellValue( 'E' . $row_num, $row_data['transaction_date'] );
$sheet->setCellValue( 'G' . $row_num, $row_data['commission_amount'] );
$sheet->setCellValue( 'H' . $row_num, $row_data['status'] );
$row_num++;
}
Security and Best Practices
When implementing this, always prioritize security:
- Nonce Verification: The example includes `wp_nonce_field` and `check_admin_referer` to prevent CSRF attacks.
- Input Sanitization: Use WordPress sanitization functions like `sanitize_text_field`, `sanitize_date`, etc., for all user inputs.
- Capability Checks: Ensure that only users with appropriate roles (e.g., ‘manage_options’) can access the reporting page and generate reports.
- Error Handling: Implement robust error handling for database queries, file operations, and Composer dependency issues.
- Large Datasets: For very large datasets, consider implementing pagination or background processing (e.g., using WP-Cron or a dedicated queue system) to avoid timeouts. PHP-Spreadsheet can consume significant memory.
- File Permissions: Ensure your web server has the necessary permissions to write temporary files if needed by the library, though `php://output` generally bypasses this.
By integrating PHP-Spreadsheet into your WordPress plugin, you can provide a powerful, automated solution for generating essential compliance reports, saving significant time and reducing the risk of errors for real estate agencies.
composer require phpoffice/phpspreadsheet
This will download the library and its dependencies into a `vendor` directory within your plugin. You’ll need to include the Composer autoloader in your plugin’s main file or within the specific script that handles the export:
<?php
/**
* Plugin Name: Real Estate Compliance Reporter
* Description: Automates compliance reporting for agent listings.
* Version: 1.0
* Author: Your Name
*/
// Ensure Composer autoloader is included
if ( file_exists( plugin_dir_path( __FILE__ ) . 'vendor/autoload.php' ) ) {
require_once plugin_dir_path( __FILE__ ) . 'vendor/autoload.php';
} else {
// Handle error: Composer dependencies not installed
add_action( 'admin_notices', function() {
echo '<div class="notice notice-error"><p>Error: Composer dependencies for Real Estate Compliance Reporter are not installed. Please run "composer install" in the plugin directory.</p></div>';
});
return;
}
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
// ... rest of your plugin code
?>
Implementing the Export Functionality
We’ll create a dedicated class or function to handle the data retrieval and spreadsheet generation. This will typically be hooked into an admin menu action to provide a downloadable link or button.
Let’s define a class, say `RealEstate_Compliance_Reporter`, within your plugin. This class will contain methods for fetching data and generating the report.
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
class RealEstate_Compliance_Reporter {
public function __construct() {
// Hook into admin menu to add a reporting page or link
add_action( 'admin_menu', array( $this, 'add_admin_menu' ) );
}
/**
* Add admin menu page for reporting.
*/
public function add_admin_menu() {
add_submenu_page(
'edit.php?post_type=agent_listing', // Parent slug (assuming 'agent_listing' post type)
__( 'Compliance Reports', 'real-estate-reporter' ),
__( 'Compliance Reports', 'real-estate-reporter' ),
'manage_options',
'compliance-reports',
array( $this, 'render_report_page' )
);
}
/**
* Render the reporting page UI.
*/
public function render_report_page() {
?>
<div class="wrap">
<h1><?php _e( 'Generate Compliance Reports', 'real-estate-reporter' ); ?></h1>
<form method="post" action="">
<input type="hidden" name="generate_report" value="1" />
<?php wp_nonce_field( 'generate_compliance_report_nonce' ); ?>
<table class="form-table">
<tr>
<th><label for="start_date"><?php _e( 'Start Date', 'real-estate-reporter' ); ?></label></th>
<td><input type="date" id="start_date" name="start_date" class="regular-text" /></td>
</tr>
<tr>
<th><label for="end_date"><?php _e( 'End Date', 'real-estate-reporter' ); ?></label></th>
<td><input type="date" id="end_date" name="end_date" class="regular-text" /></td>
</tr>
<tr>
<th><label for="report_format"><?php _e( 'Report Format', 'real-estate-reporter' ); ?></label></th>
<td>
<select id="report_format" name="report_format">
<option value="xlsx"><?php _e( 'Excel (XLSX)', 'real-estate-reporter' ); ?></option>
<option value="csv"><?php _e( 'CSV', 'real-estate-reporter' ); ?></option>
</select>
</td>
</tr>
</table>
<?php submit_button( __( 'Generate Report', 'real-estate-reporter' ) ); ?>
</form>
</div>
generate_compliance_report( $start_date, $end_date, $format );
}
}
/**
* Fetches listing data based on date range.
*
* @param string $start_date
* @param string $end_date
* @return array
*/
private function get_listing_data( $start_date, $end_date ) {
$args = array(
'post_type' => 'agent_listing', // Your custom post type
'posts_per_page' => -1, // Get all posts
'meta_query' => array(
'relation' => 'AND',
array(
'key' => 'transaction_date', // Custom field for transaction date
'value' => array( $start_date, $end_date ),
'type' => 'DATE',
'compare' => 'BETWEEN',
),
// Add other meta queries for filtering if necessary
),
);
$listings = get_posts( $args );
$data = array();
if ( ! empty( $listings ) ) {
foreach ( $listings as $listing ) {
$data[] = array(
'listing_id' => $listing->ID,
'agent_id' => get_post_meta( $listing->ID, 'agent_id', true ),
'client_name' => get_post_meta( $listing->ID, 'client_name', true ),
'property_address' => get_post_meta( $listing->ID, 'property_address', true ),
'transaction_date' => get_post_meta( $listing->ID, 'transaction_date', true ),
'sale_price' => get_post_meta( $listing->ID, 'sale_price', true ),
'commission_amount' => get_post_meta( $listing->ID, 'commission_amount', true ),
'status' => get_post_meta( $listing->ID, 'status', true ),
'compliance_flags' => get_post_meta( $listing->ID, 'compliance_flags', true ), // Assuming this is a serialized array or comma-separated string
);
}
}
return $data;
}
/**
* Generates and downloads the compliance report.
*
* @param string $start_date
* @param string $end_date
* @param string $format
*/
public function generate_compliance_report( $start_date, $end_date, $format = 'xlsx' ) {
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Set header row
$header = array(
'Listing ID',
'Agent ID',
'Client Name',
'Property Address',
'Transaction Date',
'Sale Price',
'Commission Amount',
'Status',
'Compliance Flags',
);
$sheet->fromArray( $header, NULL, 'A1' );
// Fetch and populate data
$listing_data = $this->get_listing_data( $start_date, $end_date );
if ( ! empty( $listing_data ) ) {
$row_num = 2; // Start from the second row
foreach ( $listing_data as $row_data ) {
// Ensure data is in the correct order as per header
$sheet->fromArray( array_values( $row_data ), NULL, 'A' . $row_num );
$row_num++;
}
}
// Apply some basic styling (optional)
$sheet->getStyle('A1:' . $sheet->getHighestColumn() . '1')->getFont()->setBold(true);
$sheet->getColumnDimension('A')->setWidth(10);
$sheet->getColumnDimension('B')->setWidth(10);
$sheet->getColumnDimension('C')->setWidth(30);
$sheet->getColumnDimension('D')->setWidth(50);
$sheet->getColumnDimension('E')->setWidth(15);
$sheet->getColumnDimension('F')->setWidth(15);
$sheet->getColumnDimension('G')->setWidth(15);
$sheet->getColumnDimension('H')->setWidth(15);
$sheet->getColumnDimension('I')->setWidth(40);
// Set filename
$filename_base = 'compliance_report_' . date('Ymd');
if ( ! empty( $start_date ) && ! empty( $end_date ) ) {
$filename_base .= '_' . $start_date . '_' . $end_date;
}
// Determine writer based on format
if ( 'csv' === strtolower( $format ) ) {
$writer = new Csv( $spreadsheet );
$filename = $filename_base . '.csv';
header( 'Content-Type: text/csv' );
header( 'Content-Disposition: attachment;filename="' . $filename . '"' );
} else { // Default to XLSX
$writer = new Xlsx( $spreadsheet );
$filename = $filename_base . '.xlsx';
header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
header( 'Content-Disposition: attachment;filename="' . $filename . '"' );
}
header('Cache-Control: max-age=0');
// Output the file
$writer->save( 'php://output' );
exit; // Important to exit after sending the file
}
}
// Instantiate the reporter class
new RealEstate_Compliance_Reporter();
?>
Customizing Data Fetching and Formatting
The `get_listing_data` method is where you’ll tailor the data retrieval to your specific needs. If your data is stored in a custom table rather than custom fields of a post type, you’ll need to use `global $wpdb;` and construct SQL queries. For example:
/**
* Fetches listing data from a custom table.
*
* @param string $start_date
* @param string $end_date
* @return array
*/
private function get_listing_data_from_custom_table( $start_date, $end_date ) {
global $wpdb;
$table_name = $wpdb->prefix . 'real_estate_listings'; // Your custom table name
$query = $wpdb->prepare(
"SELECT
listing_id, agent_id, client_name, property_address, transaction_date,
sale_price, commission_amount, status, compliance_flags
FROM {$table_name}
WHERE transaction_date BETWEEN %s AND %s",
$start_date,
$end_date
);
$results = $wpdb->get_results( $query, ARRAY_A );
return $results ?: array();
}
Similarly, the formatting of data within the spreadsheet can be customized. For instance, you might want to format currency fields or parse complex `compliance_flags` into a more readable format. PHP-Spreadsheet offers extensive formatting options:
// Inside generate_compliance_report, after fetching data
$row_num = 2;
foreach ( $listing_data as $row_data ) {
// Example: Format currency
if ( isset( $row_data['sale_price'] ) && is_numeric( $row_data['sale_price'] ) ) {
$sheet->setCellValue( 'F' . $row_num, $row_data['sale_price'] );
$sheet->getStyle('F' . $row_num)->getNumberFormat()->setFormatCode('$#,##0.00'); // US Dollar format
} else {
$sheet->setCellValue( 'F' . $row_num, $row_data['sale_price'] ); // Keep as is if not numeric
}
// Example: Parse compliance flags if they are a serialized array
$compliance_text = '';
if ( ! empty( $row_data['compliance_flags'] ) ) {
$flags = maybe_unserialize( $row_data['compliance_flags'] ); // Use WordPress function for safety
if ( is_array( $flags ) ) {
$compliance_text = implode( ', ', $flags );
} else {
$compliance_text = $row_data['compliance_flags']; // Fallback
}
}
$sheet->setCellValue( 'I' . $row_num, $compliance_text );
// Set other cells
$sheet->setCellValue( 'A' . $row_num, $row_data['listing_id'] );
$sheet->setCellValue( 'B' . $row_num, $row_data['agent_id'] );
$sheet->setCellValue( 'C' . $row_num, $row_data['client_name'] );
$sheet->setCellValue( 'D' . $row_num, $row_data['property_address'] );
$sheet->setCellValue( 'E' . $row_num, $row_data['transaction_date'] );
$sheet->setCellValue( 'G' . $row_num, $row_data['commission_amount'] );
$sheet->setCellValue( 'H' . $row_num, $row_data['status'] );
$row_num++;
}
Security and Best Practices
When implementing this, always prioritize security:
- Nonce Verification: The example includes `wp_nonce_field` and `check_admin_referer` to prevent CSRF attacks.
- Input Sanitization: Use WordPress sanitization functions like `sanitize_text_field`, `sanitize_date`, etc., for all user inputs.
- Capability Checks: Ensure that only users with appropriate roles (e.g., ‘manage_options’) can access the reporting page and generate reports.
- Error Handling: Implement robust error handling for database queries, file operations, and Composer dependency issues.
- Large Datasets: For very large datasets, consider implementing pagination or background processing (e.g., using WP-Cron or a dedicated queue system) to avoid timeouts. PHP-Spreadsheet can consume significant memory.
- File Permissions: Ensure your web server has the necessary permissions to write temporary files if needed by the library, though `php://output` generally bypasses this.
By integrating PHP-Spreadsheet into your WordPress plugin, you can provide a powerful, automated solution for generating essential compliance reports, saving significant time and reducing the risk of errors for real estate agencies.
Leveraging PHP-Spreadsheet for Automated Real Estate Ledger Compliance Exports
For real estate agencies operating under various regulatory frameworks, maintaining accurate and auditable ledgers of agent transactions is paramount. This often involves generating periodic compliance reports. Manually compiling these reports is not only time-consuming but also prone to human error. This post details a robust solution for automating these compliance exports directly from a custom WordPress plugin, utilizing the powerful PHP-Spreadsheet library to generate downloadable Excel or CSV files.
Plugin Architecture and Data Model
Our solution assumes a custom post type (e.g., ‘agent_listing’) or a custom database table storing essential listing data. Key fields for compliance reporting typically include:
- Listing ID (Unique identifier)
- Agent ID (Associated agent)
- Client Name
- Property Address
- Transaction Date
- Sale Price
- Commission Amount
- Status (e.g., ‘Sold’, ‘Pending’, ‘Expired’)
- Compliance Flags (e.g., ‘Disclosure Signed’, ‘Inspection Completed’)
The WordPress plugin will provide an administrative interface to trigger these exports, allowing users to specify date ranges and potentially filter by agent. The core logic will query the relevant data, process it, and then generate the spreadsheet.
Integrating PHP-Spreadsheet
The PHP-Spreadsheet library (a fork of the widely-used PHPExcel) is an excellent choice for generating spreadsheet files programmatically. It supports various formats, including XLSX, ODS, CSV, and HTML. We’ll integrate it into our WordPress plugin via Composer.
First, ensure you have Composer installed. Navigate to your WordPress plugin’s root directory in your terminal and run:
composer require phpoffice/phpspreadsheet
This will download the library and its dependencies into a `vendor` directory within your plugin. You’ll need to include the Composer autoloader in your plugin’s main file or within the specific script that handles the export:
<?php
/**
* Plugin Name: Real Estate Compliance Reporter
* Description: Automates compliance reporting for agent listings.
* Version: 1.0
* Author: Your Name
*/
// Ensure Composer autoloader is included
if ( file_exists( plugin_dir_path( __FILE__ ) . 'vendor/autoload.php' ) ) {
require_once plugin_dir_path( __FILE__ ) . 'vendor/autoload.php';
} else {
// Handle error: Composer dependencies not installed
add_action( 'admin_notices', function() {
echo '<div class="notice notice-error"><p>Error: Composer dependencies for Real Estate Compliance Reporter are not installed. Please run "composer install" in the plugin directory.</p></div>';
});
return;
}
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
// ... rest of your plugin code
?>
Implementing the Export Functionality
We’ll create a dedicated class or function to handle the data retrieval and spreadsheet generation. This will typically be hooked into an admin menu action to provide a downloadable link or button.
Let’s define a class, say `RealEstate_Compliance_Reporter`, within your plugin. This class will contain methods for fetching data and generating the report.
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
class RealEstate_Compliance_Reporter {
public function __construct() {
// Hook into admin menu to add a reporting page or link
add_action( 'admin_menu', array( $this, 'add_admin_menu' ) );
}
/**
* Add admin menu page for reporting.
*/
public function add_admin_menu() {
add_submenu_page(
'edit.php?post_type=agent_listing', // Parent slug (assuming 'agent_listing' post type)
__( 'Compliance Reports', 'real-estate-reporter' ),
__( 'Compliance Reports', 'real-estate-reporter' ),
'manage_options',
'compliance-reports',
array( $this, 'render_report_page' )
);
}
/**
* Render the reporting page UI.
*/
public function render_report_page() {
?>
<div class="wrap">
<h1><?php _e( 'Generate Compliance Reports', 'real-estate-reporter' ); ?></h1>
<form method="post" action="">
<input type="hidden" name="generate_report" value="1" />
<?php wp_nonce_field( 'generate_compliance_report_nonce' ); ?>
<table class="form-table">
<tr>
<th><label for="start_date"><?php _e( 'Start Date', 'real-estate-reporter' ); ?></label></th>
<td><input type="date" id="start_date" name="start_date" class="regular-text" /></td>
</tr>
<tr>
<th><label for="end_date"><?php _e( 'End Date', 'real-estate-reporter' ); ?></label></th>
<td><input type="date" id="end_date" name="end_date" class="regular-text" /></td>
</tr>
<tr>
<th><label for="report_format"><?php _e( 'Report Format', 'real-estate-reporter' ); ?></label></th>
<td>
<select id="report_format" name="report_format">
<option value="xlsx"><?php _e( 'Excel (XLSX)', 'real-estate-reporter' ); ?></option>
<option value="csv"><?php _e( 'CSV', 'real-estate-reporter' ); ?></option>
</select>
</td>
</tr>
</table>
<?php submit_button( __( 'Generate Report', 'real-estate-reporter' ) ); ?>
</form>
</div>
generate_compliance_report( $start_date, $end_date, $format );
}
}
/**
* Fetches listing data based on date range.
*
* @param string $start_date
* @param string $end_date
* @return array
*/
private function get_listing_data( $start_date, $end_date ) {
$args = array(
'post_type' => 'agent_listing', // Your custom post type
'posts_per_page' => -1, // Get all posts
'meta_query' => array(
'relation' => 'AND',
array(
'key' => 'transaction_date', // Custom field for transaction date
'value' => array( $start_date, $end_date ),
'type' => 'DATE',
'compare' => 'BETWEEN',
),
// Add other meta queries for filtering if necessary
),
);
$listings = get_posts( $args );
$data = array();
if ( ! empty( $listings ) ) {
foreach ( $listings as $listing ) {
$data[] = array(
'listing_id' => $listing->ID,
'agent_id' => get_post_meta( $listing->ID, 'agent_id', true ),
'client_name' => get_post_meta( $listing->ID, 'client_name', true ),
'property_address' => get_post_meta( $listing->ID, 'property_address', true ),
'transaction_date' => get_post_meta( $listing->ID, 'transaction_date', true ),
'sale_price' => get_post_meta( $listing->ID, 'sale_price', true ),
'commission_amount' => get_post_meta( $listing->ID, 'commission_amount', true ),
'status' => get_post_meta( $listing->ID, 'status', true ),
'compliance_flags' => get_post_meta( $listing->ID, 'compliance_flags', true ), // Assuming this is a serialized array or comma-separated string
);
}
}
return $data;
}
/**
* Generates and downloads the compliance report.
*
* @param string $start_date
* @param string $end_date
* @param string $format
*/
public function generate_compliance_report( $start_date, $end_date, $format = 'xlsx' ) {
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Set header row
$header = array(
'Listing ID',
'Agent ID',
'Client Name',
'Property Address',
'Transaction Date',
'Sale Price',
'Commission Amount',
'Status',
'Compliance Flags',
);
$sheet->fromArray( $header, NULL, 'A1' );
// Fetch and populate data
$listing_data = $this->get_listing_data( $start_date, $end_date );
if ( ! empty( $listing_data ) ) {
$row_num = 2; // Start from the second row
foreach ( $listing_data as $row_data ) {
// Ensure data is in the correct order as per header
$sheet->fromArray( array_values( $row_data ), NULL, 'A' . $row_num );
$row_num++;
}
}
// Apply some basic styling (optional)
$sheet->getStyle('A1:' . $sheet->getHighestColumn() . '1')->getFont()->setBold(true);
$sheet->getColumnDimension('A')->setWidth(10);
$sheet->getColumnDimension('B')->setWidth(10);
$sheet->getColumnDimension('C')->setWidth(30);
$sheet->getColumnDimension('D')->setWidth(50);
$sheet->getColumnDimension('E')->setWidth(15);
$sheet->getColumnDimension('F')->setWidth(15);
$sheet->getColumnDimension('G')->setWidth(15);
$sheet->getColumnDimension('H')->setWidth(15);
$sheet->getColumnDimension('I')->setWidth(40);
// Set filename
$filename_base = 'compliance_report_' . date('Ymd');
if ( ! empty( $start_date ) && ! empty( $end_date ) ) {
$filename_base .= '_' . $start_date . '_' . $end_date;
}
// Determine writer based on format
if ( 'csv' === strtolower( $format ) ) {
$writer = new Csv( $spreadsheet );
$filename = $filename_base . '.csv';
header( 'Content-Type: text/csv' );
header( 'Content-Disposition: attachment;filename="' . $filename . '"' );
} else { // Default to XLSX
$writer = new Xlsx( $spreadsheet );
$filename = $filename_base . '.xlsx';
header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
header( 'Content-Disposition: attachment;filename="' . $filename . '"' );
}
header('Cache-Control: max-age=0');
// Output the file
$writer->save( 'php://output' );
exit; // Important to exit after sending the file
}
}
// Instantiate the reporter class
new RealEstate_Compliance_Reporter();
?>
Customizing Data Fetching and Formatting
The `get_listing_data` method is where you’ll tailor the data retrieval to your specific needs. If your data is stored in a custom table rather than custom fields of a post type, you’ll need to use `global $wpdb;` and construct SQL queries. For example:
/**
* Fetches listing data from a custom table.
*
* @param string $start_date
* @param string $end_date
* @return array
*/
private function get_listing_data_from_custom_table( $start_date, $end_date ) {
global $wpdb;
$table_name = $wpdb->prefix . 'real_estate_listings'; // Your custom table name
$query = $wpdb->prepare(
"SELECT
listing_id, agent_id, client_name, property_address, transaction_date,
sale_price, commission_amount, status, compliance_flags
FROM {$table_name}
WHERE transaction_date BETWEEN %s AND %s",
$start_date,
$end_date
);
$results = $wpdb->get_results( $query, ARRAY_A );
return $results ?: array();
}
Similarly, the formatting of data within the spreadsheet can be customized. For instance, you might want to format currency fields or parse complex `compliance_flags` into a more readable format. PHP-Spreadsheet offers extensive formatting options:
// Inside generate_compliance_report, after fetching data
$row_num = 2;
foreach ( $listing_data as $row_data ) {
// Example: Format currency
if ( isset( $row_data['sale_price'] ) && is_numeric( $row_data['sale_price'] ) ) {
$sheet->setCellValue( 'F' . $row_num, $row_data['sale_price'] );
$sheet->getStyle('F' . $row_num)->getNumberFormat()->setFormatCode('$#,##0.00'); // US Dollar format
} else {
$sheet->setCellValue( 'F' . $row_num, $row_data['sale_price'] ); // Keep as is if not numeric
}
// Example: Parse compliance flags if they are a serialized array
$compliance_text = '';
if ( ! empty( $row_data['compliance_flags'] ) ) {
$flags = maybe_unserialize( $row_data['compliance_flags'] ); // Use WordPress function for safety
if ( is_array( $flags ) ) {
$compliance_text = implode( ', ', $flags );
} else {
$compliance_text = $row_data['compliance_flags']; // Fallback
}
}
$sheet->setCellValue( 'I' . $row_num, $compliance_text );
// Set other cells
$sheet->setCellValue( 'A' . $row_num, $row_data['listing_id'] );
$sheet->setCellValue( 'B' . $row_num, $row_data['agent_id'] );
$sheet->setCellValue( 'C' . $row_num, $row_data['client_name'] );
$sheet->setCellValue( 'D' . $row_num, $row_data['property_address'] );
$sheet->setCellValue( 'E' . $row_num, $row_data['transaction_date'] );
$sheet->setCellValue( 'G' . $row_num, $row_data['commission_amount'] );
$sheet->setCellValue( 'H' . $row_num, $row_data['status'] );
$row_num++;
}
Security and Best Practices
When implementing this, always prioritize security:
- Nonce Verification: The example includes `wp_nonce_field` and `check_admin_referer` to prevent CSRF attacks.
- Input Sanitization: Use WordPress sanitization functions like `sanitize_text_field`, `sanitize_date`, etc., for all user inputs.
- Capability Checks: Ensure that only users with appropriate roles (e.g., ‘manage_options’) can access the reporting page and generate reports.
- Error Handling: Implement robust error handling for database queries, file operations, and Composer dependency issues.
- Large Datasets: For very large datasets, consider implementing pagination or background processing (e.g., using WP-Cron or a dedicated queue system) to avoid timeouts. PHP-Spreadsheet can consume significant memory.
- File Permissions: Ensure your web server has the necessary permissions to write temporary files if needed by the library, though `php://output` generally bypasses this.
By integrating PHP-Spreadsheet into your WordPress plugin, you can provide a powerful, automated solution for generating essential compliance reports, saving significant time and reducing the risk of errors for real estate agencies.