Missing Income Lookup

[rickybsb]

 

 Hi folks. 

I had some cases that invoices were not generated.  When that happened,  we deleted orders and clients record and made new identical ones, so apparently problem has gone.

BUT we really needed a tool to help us identify any monthly missing invoices.

I wrote the following SQL, running once a month and mailing me details, so I have peace of mind.

I´d appreciate opinions from who´s in the same boat.

select tblclients.companyname, tblclients.email, tblhosting.orderid, tblhosting.amount, tblhosting.domain, tblhosting.domainstatus, tblhosting.billingcycle, tblhosting.notes from tblhosting INNER JOIN tblclients ON tblclients.id = tblhosting.userid where tblhosting.domainstatus='Active' and tblhosting.billingcycle='Monthly' and tblhosting.id not in 
(select a.relid from tblinvoiceitems a, tblinvoices b where a.invoiceid=b.id and a.type='Hosting' and YEAR(b.date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(b.date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) ) ;