I’ve noticed a bug in the dashboard invoice overview paid row and the corresponding “paid” invoice page.
I had an invoice that was 2 months overdue, and just paid. It does not show up under this month’s paid total in the overview, nor does it show near the top in the pad invoice page. These 2 seem to be calculated on the invoice date, not the payment date which makes the totals incorrect.
The payments page shows the correct information, sorting the most recent payments to the top. But this amount is not reflected in the dashboard overview of paid invoices.
Can you please direct me to the code correction for this? Our version has some modifications so I need to edit the code to correct this error.
Fixes this for anyone else interested. application\modules\invoices\models\Mdl_invoice_amounts.php
Change this:
case 'this-month':
$results = $this->db->query("
SELECT ip_invoices.invoice_status_id, (CASE ip_invoices.invoice_status_id WHEN 4 THEN SUM(ip_invoice_amounts.invoice_paid) ELSE SUM(ip_invoice_amounts.invoice_balance) END) AS sum_total, COUNT(*) AS num_total
FROM ip_invoice_amounts
JOIN ip_invoices ON ip_invoices.invoice_id = ip_invoice_amounts.invoice_id
AND MONTH(ip_invoices.invoice_date_created) = MONTH(NOW())
AND YEAR(ip_invoices.invoice_date_created) = YEAR(NOW())
GROUP BY ip_invoices.invoice_status_id")->result_array();
break;
…
to this
case 'this-month': //CJC edited to include payments by date not just invoice date
$results = $this->db->query("
SELECT ip_invoices.invoice_status_id, (CASE ip_invoices.invoice_status_id WHEN 4 THEN SUM(ip_invoice_amounts.invoice_paid) ELSE SUM(ip_invoice_amounts.invoice_balance) END) AS sum_total, COUNT(*) AS num_total
FROM ip_invoice_amounts
JOIN ip_invoices ON ip_invoices.invoice_id = ip_invoice_amounts.invoice_id
JOIN
ip_payments ON ip_payments.invoice_id = ip_invoices.invoice_id
WHERE
MONTH(ip_payments.payment_date) = MONTH(NOW())
AND YEAR(ip_payments.payment_date) = YEAR(NOW())
GROUP BY ip_invoices.invoice_status_id")->result_array();
break;
The original does not actually show paid invoices for this month, it shows paid invoices that were created in the date of this month. The new version includes invoices that were actually paid in this month.
Thank you. Yes, actually one of the reports is off also - good catch.
Reports - Payment History correctly shows the paid invoices and total for the time frame chosen
Reports - Sales by Date does not show invoices that were created outside of the time frame, but paid for inside the time frame. IMO that is the same issue as the dashboard overview.
For example, if the company received payments in January of $1000 from 2 invoices of $500 each, but one of the invoices was created in November then the report for January will only show a total for the one January created invoice - $500, even though the company actually received $1000 total payments/sales in January.
It looks like the logic is in \application\modules\reports\models\Mdl_reports.php - public function sales_by_year There are quite a number of select statements in there.
At first glance I think all of them likely need modification as they all select the invoices based on the invoice creation date rather than the payment/sale date and will thus miss all the actual sales/payments for the time frame if the invoice was created outside of the time frame.
Appreciate you all, will watch this thread for updates.
there are actually quite a number of issues with the reports. If a discount is given on an invoice, the totals shown on reports do not reflect the discount and thus show amounts much higher than actual sales.