We are based in South Africa and we use a customer age analysis to show the outstanding balance at current, 30 days, 60 days, and 120+ days, in order to keep track of overdue payments for each customer.
Are there any modules available that can show that? customer invoice statistics is inadequate at the moment so we are looking for any modules that could show outstanding invoices in that way.
I’m not sure how EU and US based companies keep track of unpaid invoices other than sending reminders but suggestions from people based there would be welcome.
Hi, I am a programmer although I have not learn PHP, mostly worked with C++. But lately, I had to start working with PHP and I did some research and have some client who needed this report as well.
So after some learning, I came up with this report codes, but I am having issues with the numbers in the 60 days column. Most of the engine is here and if someone who is more familiar with PHP can look at the SQL it would be great and I posted a picture of the final output as well. So any help to correct ONLY the SQL code is needed. the Problem is that the remaining balance is not well calculated and I know its on the SQL fetch snippet.
THANKS
<?php
// Load Dolibarr environment
$res = 0;
if (!$res && file_exists("../main.inc.php")) $res = @include '../main.inc.php';
if (!$res && file_exists("../../main.inc.php")) $res = @include '../../main.inc.php';
// Check user rights
if (!$user->rights->societe->lire) accessforbidden();
// Load classes
require_once DOL_DOCUMENT_ROOT . '/core/lib/admin.lib.php';
require_once DOL_DOCUMENT_ROOT . '/societe/class/societe.class.php';
require_once DOL_DOCUMENT_ROOT . '/compta/facture/class/facture.class.php';
// Define aging periods
$aging_periods = [
'0-30' => [0, 30],
'31-60' => [31, 60],
'61-90' => [61, 90],
'90+' => [91, 9999]
];
// Function to determine aging period
function get_aging_period($days)
{
global $aging_periods;
foreach ($aging_periods as $label => $range) {
if ($days >= $range[0] && $days <= $range[1]) {
return $label;
}
}
return 'Unknown';
}
// Handle customer filter
$selected_customer = isset($_POST['customer']) ? $_POST['customer'] : '';
// Fetch customer list for filter dropdown
$sql_customers = "SELECT rowid, nom FROM " . MAIN_DB_PREFIX . "societe WHERE client >= 1 ORDER BY nom ASC";
$resql_customers = $db->query($sql_customers);
$customer_options = [];
if ($resql_customers) {
while ($customer = $db->fetch_object($resql_customers)) {
$customer_options[$customer->rowid] = $customer->nom;
}
}
// SQL query for invoices
$sql = "SELECT
s.rowid AS customer_id,
s.nom AS customer_name,
f.rowid AS invoice_id,
f.total_ttc
- IFNULL((
SELECT SUM(pf.amount)
FROM llx_paiement_facture AS pf
WHERE pf.fk_facture = f.rowid
), 0) AS total_after_payments,
IFNULL((
SELECT SUM(cn.total_ttc)
FROM llx_facture AS cn
WHERE cn.fk_facture_source = f.rowid AND cn.type = 2
), 0) AS total_credit_notes,
(f.total_ttc
- IFNULL((
SELECT SUM(pf.amount)
FROM llx_paiement_facture AS pf
WHERE pf.fk_facture = f.rowid
), 0)
+ IFNULL((
SELECT SUM(cn.total_ttc)
FROM llx_facture AS cn
WHERE cn.fk_facture_source = f.rowid AND cn.type = 2
), 0)) AS balance_due,
f.date_lim_reglement,
f.paye
FROM
llx_societe AS s
JOIN
llx_facture AS f ON s.rowid = f.fk_soc
WHERE
f.type != 2 -- Exclude credit notes from the main invoice list
AND f.paye = 0
AND f.fk_user_valid = 1";
if ($selected_customer) {
$sql .= " AND s.rowid = " . intval($selected_customer);
}
$sql .= " HAVING balance_due > 0";
$resql = $db->query($sql);
if (!$resql) {
die("Error fetching data: " . $db->lasterror());
}
// Prepare report data
$report_data = [];
while ($obj = $db->fetch_object($resql)) {
$customer_id = $obj->customer_id;
$customer_name = $obj->customer_name;
$balance_due = $obj->balance_due;
$due_date = strtotime($obj->date_lim_reglement);
$days_overdue = (time() - $due_date) / (60 * 60 * 24);
$aging_period = get_aging_period($days_overdue);
if (!isset($report_data[$customer_id])) {
$report_data[$customer_id] = [
'customer_name' => $customer_name,
'0-30' => 0,
'31-60' => 0,
'61-90' => 0,
'90+' => 0,
'total' => 0
];
}
$report_data[$customer_id][$aging_period] += $balance_due;
$report_data[$customer_id]['total'] += $balance_due;
}
// Render the report as HTML
?>
Customer Aging Report
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid #dddddd; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
form { margin-bottom: 20px; }
Customer Aging Report
Select Customer:
All Customers
<?php foreach ($customer_options as $id => $name): ?>
>
<?php echo htmlspecialchars($name); ?>
<?php endforeach; ?>
Filter
<?php if (empty($report_data)): ?>
<?php else: ?>
<?php foreach ($report_data as $data): ?>
<?php endforeach; ?>
<?php endif; ?>
