A few years ago, WHMCS didn't have client statements. Not sure if it does now. So I wrote this code and uploaded it to one of my servers. It's a set of custom mysql queries to calculate the amount owed, amount paid and amount of credits. Kind of like a simple bank statement - amount in and amount out, with balance. It takes all the information and uses FPDF to generate a downloadable PDF document.
I then made an email template in WHMCS that included the link to the page on my server that served my script.
I created MD5 hash from some of the user information, to check against, so that clients wouldn't have to log in when opening the link.
When the link is openend, the user gets a download prompt from their browser, asking if they want to download or open the PDF document. The filename is "Statement_YYYY-mm-dd". All these variables can be changed easily, of course, including the date range.
I'm attaching the code and a screenshot of what the statement looks like.
It's definitely not perfect and probably has some flaws, but maybe it can be modified to work with Blesta.
<?php
ini_set( 'display_errors', 1 );
## Path to logo
$logo = 'logo_vector_2.jpg';
## Company/organistion information
# Name
$org_name = 'Acme';
# Address
$org_addr_1 = "Address 1";
$org_addr_2 = "Address 2";
$org_addr_3 = "Ireland";
# Contact information 1
$org_contact_1 = 'Phone: +353 xxxx';
# Tax ID
$org_tax_id = "VAT ID: 1234567M";
## Database information
$dbhost = "example.com";
$dbuser = "xxx";
$dbpass = "xxx";
$dbname = "xxx";
$uid = isset( $_REQUEST['uid'] ) ? (int)$_REQUEST['uid'] : NULL;
$key = isset( $_REQUEST['key'] ) ? $_REQUEST['key'] : NULL;
if ( empty( $uid ) || empty( $key ) ) {
die( 'Error: no user ID or access key supplied. The link you entered may have expired.' );
}
$mysqli = new mysqli( $dbhost, $dbuser, $dbpass, $dbname );
if ( $mysqli->connect_errno ) {
echo "Failed to connect to MySQL: ( " . $mysqli->connect_errno . " ) " . $mysqli->connect_error;
exit;
}
$result = $mysqli->query( "SELECT *, CONCAT( firstname, ' ', lastname, ' (', companyname, ')' ) AS client FROM tblclients WHERE id='$uid' LIMIT 1" );
if ( $result->num_rows !== 1 ) {
die( 'Error: we could not access any account data with the information that was provided.' );
}
$d = $result->fetch_assoc();
$result->free_result();
$md5 = md5( $d['id'] . ' : ' . $d['client'] . ' : ' . $d['email'] );
if ( $key !== $md5 ) {
die( 'Error: the access key provided could not be validated. The link you followed may have expired.' );
}
$date = date( 'Y-m-d', mktime( 0, 0, 0, date( 'm' ), date( 'd' ), date( 'Y' ) -6 ) );
$result = $mysqli->query( "SELECT SUM(total) AS total FROM tblinvoices WHERE userid = '$uid' AND DATE_FORMAT(`date`, '%Y-%m-%d') < '$date' AND status != 'Cancelled'" );
$balforward = $result->fetch_array()[0];
$result->free_result();
$result = $mysqli->query( "SELECT SUM(amountin) FROM tblaccounts WHERE userid='$uid' AND DATE_FORMAT(`date`, '%Y-%m-%d') < '$date'" );
$balforward -= $result->fetch_array()[0];
$result->free_result();
$result = $mysqli->query( "SELECT SUM(amount) FROM tblcredit WHERE clientid='$uid' AND DATE_FORMAT(`date`, '%Y-%m-%d') < '$date'" );
$balforward += $result->fetch_array()[0];
$result->free_result();
$result = $mysqli->query( "
(SELECT id, ( subtotal + tax ) AS `amount`, DATE_FORMAT(`date`, '%Y-%m-%d') AS `date`, 'Invoice' AS txtype
FROM tblinvoices
WHERE userid='$uid'
AND `date` >= '$date'
AND status != 'Cancelled'
AND ( subtotal + tax ) != 0)
UNION
(SELECT id, amountin AS `amount`, DATE_FORMAT(`date`, '%Y-%m-%d') AS `date`, 'Payment' AS txtype
FROM tblaccounts
WHERE userid='$uid'
AND `date` >= '$date')
UNION
(SELECT id, amount AS `amount`, DATE_FORMAT(`date`, '%Y-%m-%d') AS `date`, 'Credit' AS txtype
FROM tblcredit
WHERE clientid='$uid'
AND `date` >= '$date')
ORDER BY `date` ASC
" );
$i = array();
$n = 0;
$balance = $balforward;
while( $r = $result->fetch_assoc() ) {
$i[$n] = $r;
if ( $r['txtype'] === 'Invoice' ) {
$balance += $r['amount'];
}
else {
$balance -= $r['amount'];
}
$i[$n]['balance'] = number_format( $balance, 2, '.', ',' );
$n++;
}
$result->free_result();
$mysqli->close();
define( 'FPDF_FONTPATH', '../includes/font/' );
require( '../includes/fpdf.php' );
$pdf = new FPDF();
//Column titles
$header = array( 'Date', 'Type', 'ID', 'Amount', 'Balance' );
//Data loading
//$data=$pdf->LoadData($i);
//$pdf->AddPage();
//$pdf->BasicTable($header,$data);
//$pdf->AddPage();
//$pdf->ImprovedTable($header,$data);
$pdf->AddPage();
$pdf->Image( $logo, 0, 0 );
$pdf->SetFont( 'Arial', 'B', 10 );
$pdf->Cell( 120, 6, '', 0, 0, 'L' );
$pdf->Cell( 0, 6, $org_name, 0, 0, 'L' );
$pdf->Ln();
$pdf->SetFont( 'Arial', '', 10 );
$pdf->Cell( 120, 6, '', 0, 0, 'L' );
$pdf->Cell( 0, 6, $org_addr_1, 0, 0, 'L' );
$pdf->Ln();
$pdf->Cell( 120, 6, '', 0, 0, 'L' );
$pdf->Cell( 0, 6, $org_addr_2, 0, 0, 'L' );
$pdf->Ln();
$pdf->Cell( 120, 6, '', 0, 0, 'L' );
$pdf->Cell( 0, 6, $org_addr_3, 0, 0, 'L' );
$pdf->Ln( 8 );
$pdf->Cell( 120, 6, '', 0, 0, 'L' );
$pdf->Cell( 0, 6, $org_contact_1, 0, 0, 'L' );
$pdf->Ln();
$pdf->Cell( 120, 6, '', 0, 0, 'L' );
$pdf->Cell( 0, 6, $org_tax_id, 0, 0, 'L' );
$pdf->Ln( 18 );
$pdf->SetFont( 'Arial', '', 14 );
$pdf->Cell( 80, 6, 'Account Statement', 0, 0, 'L' );
$pdf->Cell( 80, 6, gmdate( 'Y-m-d'), 0, 0, 'R' );
$pdf->Ln(10);
$pdf->SetFont( 'Arial', 'B', 10 );
$pdf->Cell( 0, 6, $d['client'] );
$pdf->Ln();
$pdf->SetFont( 'Arial', '', 10 );
$pdf->Cell( 0, 6, $d['address1'] . ', '. $d['city'] . ', ' . $d['state'] );
$pdf->Ln( 20 );
//$pdf->FancyTable($header,$data);
$data = $i;
//Colors, line width and bold font
$pdf->SetFillColor( 90, 135, 190 );
$pdf->SetTextColor( 255 );
$pdf->SetDrawColor( 128, 0, 0 );
$pdf->SetLineWidth( .3 );
$pdf->SetFont( '', 'B', 11 );
//Header
$w = array( 30, 40, 30, 40, 40 );
for ( $i = 0; $i < count( $header ); $i++ )
$pdf->Cell( $w[$i], 7, $header[$i], 1, 0, 'C', true );
$pdf->Ln();
//Color and font restoration
$pdf->SetFillColor( 224, 235, 255 );
$pdf->SetTextColor( 0 );
$pdf->SetFont( '' );
//Data
$fill = false;
$pdf->Cell( 30, 6, $date, 0, 0, 'L', $fill );
$pdf->Cell( 40, 6, 'Balance Forward', 0, 0, 'C', $fill );
$pdf->Cell( 30, 6, '-', 0, 0, 'L', $fill );
$pdf->Cell( 40, 6, number_format( $balforward, 2, '.', ',' ), 0, 0, 'R', $fill );
$pdf->Cell( 40, 6, number_format( $balforward, 2, '.', ',' ), 0, 0, 'R', $fill );
$pdf->Ln();
foreach( $data as $row )
{
$pdf->Cell( 30, 6, $row['date'], 0, 0, 'L', $fill );
$pdf->Cell (40, 6, $row['txtype'], 0, 0, 'C', $fill );
$pdf->Cell( 30, 6, $row['id'], 0, 0, 'L', $fill );
if ( $row['txtype'] === 'Payment' || $row['txtype'] === 'Credit' ) {
$pdf->Cell( 40, 6, '-' . $row['amount'], 0, 0, 'R', $fill );
}
else {
$pdf->Cell( 40, 6, $row['amount'], 0, 0, 'R', $fill );
}
$pdf->Cell( 40, 6, $row['balance'], 0, 0, 'R', $fill );
$pdf->Ln();
$fill =! $fill;
}
$pdf->Cell( array_sum( $w ), 0, '', 'T' );
$pdf->Ln( 20 );
$pdf->Cell( 80, 6, 'Your current account balance is:', 0, 0, 'L' );
$pdf->SetFont( 'Arial', 'B', 13 );
$pdf->Cell( 80, 6, number_format( $balance, 2, '.', ',' ), 0, 0, 'L' );
$pdf->Ln( 8 );
$pdf->SetFont( 'Arial', '', 8 );
$pdf->MultiCell( 150, 6, 'Please note this is not an official invoice. Errors and omissions excepted. Please refer to your invoice for transaction details and tax information. Please contact us with any questions.' );
$pdf->Ln(5);
$pdf->Ln(1);
header( "Cache-Control: no-cache, must-revalidate" ); // HTTP/1.1
header( "Expires: Sat, 26 Jul 1997 05:00:00 GMT" ); // Date in the past
header( "Content-type: application/pdf" );
header( 'Content-Disposition: attachment; filename="Statement_' . gmdate( 'Y_m_d' ) . '.pdf"' );
$pdf->Output( 'Statement_' .gmdate( 'Y_m_d' ) . '.pdf', 'D' );
