Custom Report - Services with Configurable Option

[John]

Hey All,

I'm looking to write a custom report that will show me all the services that have a certain configurable option/addon. Specifically, I need to find all domains that have bought privacy. The privacy configurable option does not call back to our domain registrar, so there is a need to audit the domains that have this option and make sure they are getting what they paid for.

Could anyone help me write the query for this custom report? I'm extremely new to Blesta's custom reporting system.

It would need to include all domain products, find the services that the addon is active with, and list them.

Thank you 

[Blesta Addons]

wich registrar module you use ?

 

[John]

44 minutes ago, naja7host said:

wich registrar module you use ?

 

Ah, good question. LogicBoxes.

[Blesta Addons]

first you need to get the module_row id for the logicbox .
 then create a custom request to database with this code

    public function searchServiceFields($module_id) {
        $fields = array("services.*",'service_options.*');
        
        return $this->Record->select($fields)->
            from("services")->
            innerJoin("module_rows", "module_rows.id", "=", "services.module_row_id", false)->
            innerJoin("service_options", "service_options.service_id", "=", "services.id", false)->    
            where("services.module_row_id", "=", $module_id)->
            group("services.id")->fetchAll();
    }

you can use the function like

$services = $this->searchServiceFields(7); // 7 is the module row id
print_r($services);

this custom code will return just the service that use the module "XXXX" that has configoption .