Skip to content

Instantly share code, notes, and snippets.

@mrl22

mrl22/uninvoiced-hosting.sql

Created September 19, 2023 12:40
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mrl22/37cb7e5537462020aa311d4ce94dbae3 to your computer and use it in GitHub Desktop.
Save mrl22/37cb7e5537462020aa311d4ce94dbae3 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
uninvoiced-hosting.sql
SELECT tblhosting.billingcycle, tblhosting.nextduedate, tblhosting.*
FROM tblhosting
LEFT JOIN tblinvoiceitems
ON tblinvoiceitems.`type` IN ('Hosting') AND tblhosting.id = tblinvoiceitems.relid
and (
(tblhosting.billingcycle='Monthly' AND date_add(tblinvoiceitems.duedate, INTERVAL 1 month) >= tblhosting.nextduedate)
OR (tblhosting.billingcycle='Quarterly' AND date_add(tblinvoiceitems.duedate, INTERVAL 3 month) >= tblhosting.nextduedate)
OR (tblhosting.billingcycle='Semi-Annually' AND date_add(tblinvoiceitems.duedate, INTERVAL 6 month) >= tblhosting.nextduedate)
OR (tblhosting.billingcycle='Annually' AND date_add(tblinvoiceitems.duedate, INTERVAL 12 month) >= tblhosting.nextduedate)
OR (tblhosting.billingcycle='Biennially' AND date_add(tblinvoiceitems.duedate, INTERVAL 24 month) >= tblhosting.nextduedate)
OR (tblhosting.billingcycle='Triennially' AND date_add(tblinvoiceitems.duedate, INTERVAL 36 month) >= tblhosting.nextduedate)
)
WHERE
tblhosting.domainstatus = 'Active'
and tblinvoiceitems.id is null
and tblhosting.nextduedate != '0000-00-00'
order by FIELD(tblhosting.billingcycle, 'Monthly','Quarterly','Semi-Annually','Annually','Biennially','Triennially');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment