report of all active customers/products

[snake]

Hi,

 

I need to get a report/list of all active customers, their products/addons, how much they are paying and billing period.

 

anyone know how to do this ?

[brian!]

using the Services report would be the quickest/simplest way... potentially more thorough way would be via SQL query, but I think all the info you ask for is in the report... with perhaps the clients report for cross-referencing whether they're active or not.

[snake]

yea I checked the services report, but all this gives is a productID

[brian!]

yea I checked the services report, but all this gives is a productID

I was hoping that you only had a handful of products and could work with just that!

 

but if you know the productID, then it's simply a case of using an inner join to lookup the product name in the tblproducts table... it's probably quicker to give you the sql query that you can run in phpmyadmin rather than modifying the report code..

 

SELECT
 CONCAT(firstname,' ',lastname) AS Name,
 tblclients.companyname,
 tblproducts.name AS Product,
 tblhosting.domain,
 tblhosting.paymentmethod,
 tblhosting.billingcycle,
 tblhosting.amount
FROM tblhosting
 INNER JOIN tblproducts
   ON tblhosting.packageid = tblproducts.id
 INNER JOIN tblclients
   ON tblhosting.userid = tblclients.id
WHERE tblclients.status = 'Active'
ORDER BY Name

I think that should tell you everything you want to know!

[snake]

Thanks, the problem is that I need ALL the details for the products as well as just the product name, as I main need this to see all the servers/VPS products, so I need the configurable options so that the report shows how much ram, cpu's etc each server has, as well as any addons and their prices etc. That is where I am having trouble getting this info into the output. I tried using a sub-query but since this results in multiple columns and rows, it won't go into a single alias column. My SQL is rather rusty.

[brian!]

aaah.. you should have said that in the first place!

 

that does add a little complexity to the issue - I know the tables that would need to be queried and their relationships, but for something like this you might be better off posting in Marketplace and paying for a custom report to be written.

 

as there will be multiple config options not used by all the products, it might be simpler to split it into two - so a primary report as per the above query, but hyper-linking the product to a report showing it's config options... otherwise it's going to potentially be one hell of a table if you want to show everything for all products.