Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#9 SQL error in arinvoicerepcust.php

open
nobody
None
5
2004-04-28
2004-04-28
Peter Kools
No

In arinvoicerepcust.php, there is an SQL error in the
query (line 32) that generates the amount due. The
original query is:

select arinvoice.invoicenumber, arinvoice.invoicedate,
arinvoice.duedat
e, arinvoice.invoicetotal-sum
(arinvoicepaymentdetail.amount) from arinvoice left join
arinvoicepaymentdetail on arinvoic
e.id=arinvoicepaymentdetail.invoiceid where
arinvoice.orderbycompanyid=".sqlprep($companyid)." and
arinvoice.status>=0 g
roup by
arinvoice.invoicenumber,arinvoice.invoicedate,arinvoice.d
uedate,arinvoice.invoicetotal,arinvoice.id

This should be replaced with:

select arinvoice.invoicenumber, arinvoice.invoicedate,
arinvoice.duedat
e, arinvoice.invoicetotal-ifnull(sum
(arinvoicepaymentdetail.amount),0) from arinvoice left
join arinvoicepaymentdetail o
n arinvoice.id=arinvoicepaymentdetail.invoiceid where
arinvoice.orderbycompanyid=".sqlprep($companyid)." and
arinvoice.s
tatus>=0 group by
arinvoice.invoicenumber,arinvoice.invoicedate,arinvoice.d
uedate,arinvoice.invoicetotal,arinvoice.id

The difference is to add an 'ifnull() clause around sum
(arinvoicepaymentdetail.amount) so the subtraction
works properly even if there is no paymentdetail amount.

Discussion