Custom Report: Export Clients matching Packages

[Paul]

Here's a custom report for exporting client data that matches a specific list of comma separated package ID's. This is useful for exporting client data, and importing with the Blesta CSV importer into a new Blesta install. Does not import actual services/packages, which would have to be moved manually.

To create the custom report, go to Billing > Reports, and click on the "Customize" tab, then the [+] button.

1. Give the report a name, like "Client Export from Package IDs"

2. Enter the following query into the Query text area:

SELECT `contacts`.`first_name`, `contacts`.`last_name`, `contacts`.`company`, `contacts`.`title`, `contacts`.`address1`, `contacts`.`address2`, `contacts`.`city`, `contacts`.`country`, `contacts`.`state`, `contacts`.`zip`, `contacts`.`email`, `users`.`username`
FROM `packages`
INNER JOIN `package_pricing` ON `package_pricing`.`package_id` = `packages`.`id`
INNER JOIN `services` ON `services`.`pricing_id` = `package_pricing`.`id` AND `services`.`status` = 'active' 
INNER JOIN `clients` ON `clients`.`id` = `services`.`client_id`
INNER JOIN `users` ON `users`.`id` = `clients`.`user_id`
INNER JOIN `contacts` ON `contacts`.`client_id` = `clients`.`id` AND `contacts`.`contact_type` = 'primary'
WHERE `packages`.`id` IN (:package_ids)
GROUP BY `clients`.`id`;

3. Create a field with a Label of "Package Ids", Name of "package_ids", Type of "Text", and Required: "Yes"

4. Save. The report will now be available under the "Generate" tab. When generating the report, enter package ids as a comma separated list (e.g. 23,24,81). 

NOTE! The Package ID is the internal ID, not the Package ID listed for the package listing. Hover over the Edit link, or click it to observe the actual ID of the package in the URL.