Schedule and send generated export (csv) report to Users

What are the steps to generate schedule a report and send exported (csv) results to specific email ids?

Parents

0 Francesca Shiekh over 3 years ago

You can also create a custom scheduler, that runs a SugarQuery, generates and output and emails it as a csv attachment.

There ia a fair amount of coding involved, and you would have to code it so it emails by exact email address, by a User's Team (email everyone in a given team) or some other similar criteria.

For example, I have a daily report that runs to report any errors in our Sync with ERP

custom/Extension/modules/Schedulers/Ext/ScheduledTasks/

I created a custom scheduler job that queries some data, gets each result as a row and puts the results in a csv with headers which is then attached to an email and sent

<?php
array_push($job_strings, 'accountSyncErrorReporting');
function accountSyncErrorReporting(){
  include_once('custom/entry_points/Utils/send_group_email.php');

  global $db;
  $send = false; //send only if there is something to report
  
  $query = "select * from ora_sugar_accounts_stg where comments is not null and integration_status = ?";
  $conn = $db->getConnection();
  $stmt = $conn->executeQuery($query, array('sync_failed'));
  
  if(!empty($stmt)){
    $headers = array();
    //the csv goes in a temp directory we have in our sugar cache directory
    if(!is_dir(sugar_cached('temp'))){
      sugar_mkdir(sugar_cached('temp'));
    }
    
    //create csv attachment with results
    $filename = 'AccountSyncErrorReport_'.date("Ymd").'.csv';
    $file = sugar_cached('temp/').$filename;
    $fp = sugar_fopen($file, 'w');
    
    //start creating the attachment
    if($fp){
      header("Content-Type: text/csv");
      header("Content-Disposition: attachment; filename={$filename}");
      header("Pragma: no-cache");
      header("Expires: 0");
      foreach($stmt->fetchAll() as $row){
        $send = true; //we have results
        //format results
        if(empty($headers)){
          $headers = array_keys($row);
          fputcsv($fp, $headers);
        }
        fputcsv($fp, array_values($row),',','"');
      }
      fclose($fp);
    }
    
    $to_email_addresses = array(<a list of email addresses is added here>);
    $subject = "SugarCRM warning: Accounts Failed Oracle Sync";
    $From = '<the from address I want to send this as>';
    $FromName = 'SugarCRM Notice';
    $email_body =<<<BODY
      Please find attached the accounts that failed to sync from Oracle today
BODY;
    if($send) sendGroupEmail($to_email_addresses, $From, $FromName, $subject, $email_body, array(), $file);
    return true;
  }
  return true;
}

The send Email code quite old and probably could use a revision and improvements but you know how it goes, if it works, you never have the time to go back and make it better... ;)

<?php
  //Function to send Email message

function sendGroupEmail($ToEmailAdd, $FromEmailAdd, $FromEmailName, $EmailSubject, $EmailBody, $CcEmailAdd = array(), $attach='') {
  include_once('modules/Mailer/MailerFactory.php');
  global $sugar_config;
  $To = array();
  $Cc = array();

  if(is_array($ToEmailAdd)){
    $To = $ToEmailAdd;
  }else{
    $To = explode(';',$ToEmailAdd);
  }

  if(is_array($CcEmailAdd)){
    $Cc = $CcEmailAdd;
  }else{
    $Cc = explode(';',$CcEmailAdd);
  }
  try{
    $phpMailer = MailerFactory::getSystemDefaultMailer();
    $mailTransmissionProtocol = $phpMailer->getMailTransmissionProtocol();
    $FromEmailIdentity = new EmailIdentity($FromEmailAdd, $FromEmailName);
    $headers = new EmailHeaders();
    $header_array = array(
      'From'=>$FromEmailIdentity,
      'ReplyTo'=>'',
      'Sender'=>$FromEmailIdentity, //mandatory
      'Subject'=>$EmailSubject,
    );
    $headers->buildFromArray($header_array);
    $phpMailer->setHeaders($headers);
    $phpMailer->setTextBody($EmailBody);

    foreach($To as $to_addr){
      $phpMailer->addRecipientsTo(new EmailIdentity($to_addr, $to_addr));
    }
    foreach($Cc as $cc_addr){
      $phpMailer->addRecipientsCc(new EmailIdentity($cc_addr, $cc_addr));
    }
    if(!empty($attach)){
      $phpMailer->addAttachment(new Attachment($attach));
    }
    $phpMailer->send();
  }catch(MailerException $me) {
    $message = $me->getMessage();
    $GLOBALS["log"]->fatal(
      "SendGroupEmail: error sending e-mail (method: {$mailTransmissionProtocol}), (error: {$message})"
    );
  }
  return;
}
?>

Hope this helps you find a solution that works for you!
FrancescaS

Reply