Is there any way to build a CSV report scheduler?

Hi all.

A client of mine is asking for the utility to setup scheduled reports in sugarCRM, but for them to come as .csv rather than a PDF file.

Has anyone done this yet or has seen it done in the past? The only references I can find are to older versions that are no longer used.

Thanks!

Dan Hill 

  • We have implemented precisely this same feature for a customer and it works like a charm.

    It implies a lots of development tasks, some of them quite complex. Anyway, all of them are upgrade safe and packageScan safe.

    Let me know if you would like to hire such feature, we can provide a working installer specific for your SugarCRM version.

    Regards

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • 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

  • This functionality is now available in the recent Q1 2022 release. you can read more about this functionality in the Application Guide.