Is there any way to build a CSV report scheduler?

Hi,

I recently encountered a similar issue as you, users wishing to receive .csv reports on a periodic basis automatically. Thankfully, everything that is needed is available from the sugar codebase out of the box.

Writing out a full implementation as a comment would be probably too lengthy, but I can give you a few good pointers.

First of all, Sugar has PHPMailer included into its codebase, so you should leverage this to create a csv attachment. PHPMailer has a method addStringAttachment, and this is what you can use to send the csv files.

So you can create a new custom API endpoint, which could include something like:

//Do some query to fetch the data you want to send out. 
//This likely should be a separate function from the function where you send the mail.
$result = [];
query_result = $GLOBALS['db']->query("your query here");
while ($row = $GLOBALS['db']->fetchByAssoc($query_result)) {
    array_push($result, $row);
}

$file = '';
foreach ($result as $arr) {
    $file = $file . implode(",", $arr) . PHP_EOL;
    //NOTE: this is just a very crude way of building a csv, 
    //and e.g. will encounter problems if your data includes the , character anywhere. 
}
//So now that you have some csv data stored in the $file variable...

/*
Pick an outbound email configuration from the outbound_email table, 
and fill in the relevant details below...
*/
try {
//check the exact settings you need in the outbound_email table, or with your sysadmin
            $phpMailer = new PHPMailer();
            $phpMailer->isSMTP();
            $phpMailer->Host = 'your mail server';
            $phpMailer->SMTPAuth = true;
            $phpMailer->Username = 'an smtp username';
            $phpMailer->Password = 'an smtp password'; 
/*
NOTE: you should NOT hardcode a password here, you can pick an outbound email config
from the outbound_email table, and then e.g. you can use the Blowfish class to decode the password hash from the database table instead
*/
            $phpMailer->Port = 'check your outbound_email table';
            $phpMailer->SMTPSecure = 'likely tls, but check your outbound_email table';

            $phpMailer->setFrom('example@email.com', 'Type friendly name here');
            $phpMailer->addReplyTo('example@email.com', 'Type friendly name here');
            $phpMailer->addAddress($toAddress, '');

            $phpMailer->Subject = $subject;

            $phpMailer->Body = $body; //main message content of email

            $phpMailer->addStringAttachment($file, 'give_a_better_filename.csv', Encoding::Base64, 'text/csv');
            
            $phpMailer->send();

        } catch (phpmailerException $ex) {
            $GLOBALS["log"]->fatal($ex->getMessage());
        }

Include the above block of code in a custom API class, and make a call to your custom API class from the scheduler.

You can then use the sugar scheduler to make the calls using cURL to your sugar API endpoint. Do not forget to get an oauth token before trying to call your custom API endpoint. There is great documentation on it already here.

You might want to consider registering a custom platform for this API action.

It would be then up to you to create an interface that is suitable for yourself to administer more of these reports.

Hi,

I recently encountered a similar issue as you, users wishing to receive .csv reports on a periodic basis automatically. Thankfully, everything that is needed is available from the sugar codebase out of the box.

Writing out a full implementation as a comment would be probably too lengthy, but I can give you a few good pointers.

First of all, Sugar has PHPMailer included into its codebase, so you should leverage this to create a csv attachment. PHPMailer has a method addStringAttachment, and this is what you can use to send the csv files.

So you can create a new custom API endpoint, which could include something like:

//Do some query to fetch the data you want to send out. 
//This likely should be a separate function from the function where you send the mail.
$result = [];
query_result = $GLOBALS['db']->query("your query here");
while ($row = $GLOBALS['db']->fetchByAssoc($query_result)) {
    array_push($result, $row);
}

$file = '';
foreach ($result as $arr) {
    $file = $file . implode(",", $arr) . PHP_EOL;
    //NOTE: this is just a very crude way of building a csv, 
    //and e.g. will encounter problems if your data includes the , character anywhere. 
}
//So now that you have some csv data stored in the $file variable...

/*
Pick an outbound email configuration from the outbound_email table, 
and fill in the relevant details below...
*/
try {
//check the exact settings you need in the outbound_email table, or with your sysadmin
            $phpMailer = new PHPMailer();
            $phpMailer->isSMTP();
            $phpMailer->Host = 'your mail server';
            $phpMailer->SMTPAuth = true;
            $phpMailer->Username = 'an smtp username';
            $phpMailer->Password = 'an smtp password'; 
/*
NOTE: you should NOT hardcode a password here, you can pick an outbound email config
from the outbound_email table, and then e.g. you can use the Blowfish class to decode the password hash from the database table instead
*/
            $phpMailer->Port = 'check your outbound_email table';
            $phpMailer->SMTPSecure = 'likely tls, but check your outbound_email table';

            $phpMailer->setFrom('example@email.com', 'Type friendly name here');
            $phpMailer->addReplyTo('example@email.com', 'Type friendly name here');
            $phpMailer->addAddress($toAddress, '');

            $phpMailer->Subject = $subject;

            $phpMailer->Body = $body; //main message content of email

            $phpMailer->addStringAttachment($file, 'give_a_better_filename.csv', Encoding::Base64, 'text/csv');
            
            $phpMailer->send();

        } catch (phpmailerException $ex) {
            $GLOBALS["log"]->fatal($ex->getMessage());
        }

Include the above block of code in a custom API class, and make a call to your custom API class from the scheduler.

You can then use the sugar scheduler to make the calls using cURL to your sugar API endpoint. Do not forget to get an oauth token before trying to call your custom API endpoint. There is great documentation on it already here.

You might want to consider registering a custom platform for this API action.

It would be then up to you to create an interface that is suitable for yourself to administer more of these reports.

Hi Viktor.

Thanks so much for this, it gives me something to start with at the very least so I appreciate the time you spent on this!

Many Thanks,

Dan