SQL Query to pull free domains without active packages

[sahostking]

Hi guys,

Is there any module, addon or SQL query I could use to pull all domains that are active and set to free(0.00) but have no active packages set towards them that are set on annual,biannual or triannual.

 

Thanks

[brian!]

5 hours ago, sahostking said:

Is there any module, addon or SQL query I could use to pull all domains that are active and set to free(0.00) but have no active packages set towards them that are set on annual,biennial or triennial.

the usual suspect would be ModulesGarden's Report Generator - but that's probably overkill for something like this (assuming the module could even do it)....

that last part of your question could be interpreted in a number of ways though... e.g can they have inactive packages... or do you want a list of domains with no packages at all - knowing which would make a fundamental difference to any SQL query.

[sahostking]

Well I'm looking for a SQL query that can check all domains that have a recurring amount of 0.00 but have a package linked to it that is suspended, cancelled, terminated.

Those domains should be cancelled if customer cancelled package. 

Reason is customers that pay annually for their packages and get a free domain then decide to cancel their package the following year. The domain would stay active on our whmcs system but then auto renew possibly the year thereafter.

[brian!]

10 minutes ago, sahostking said:

Well I'm looking for a SQL query that can check all domains that have a recurring amount of 0.00 but have a package linked to it that is suspended, cancelled, terminated. 

aaah... that's clearer!

SELECT
tbldomains.domain,
tblhosting.domainstatus
FROM
tbldomains
INNER JOIN tblhosting ON tbldomains.domain = tblhosting.domain
WHERE
tbldomains.recurringamount = '0' AND
tblhosting.domainstatus IN ('Suspended', 'Cancelled', 'Terminated')
GROUP BY
tbldomains.domain

you might need to play with the select if you want other fields.

15 minutes ago, sahostking said:

Those domains should be cancelled if customer cancelled package. 

Reason is customers that pay annually for their packages and get a free domain then decide to cancel their package the following year. The domain would stay active on our whmcs system but then auto renew possibly the year thereafter.

a couple of addons that may be worth looking at...

• Free Domains Manager (free, but don't know if still works).

  Quote

  Client ordered a yearly hosting plan and received a free domain with it. After couple of months, client cancelled the hosting account, but domain is still listed in WHMCS as a free domain and might be sent to the registrar for renewal ! This hook will look for these criteria (with some more verifications) and will notify you about it.

   

• Cancel 0.00 Domains ($20 per year - but stated as working on v7.5)

  Quote

  An Action Hook that checks for free ($0.00) domains associated with a non-Active hosting account (runs during Daily Cron Job) and sets any to Cancelled. Also hooks into the termination functions so that a free ($0.00) domain is not accidentally renewed if the customer does not wish to continue their hosting service.