What are the steps to generate schedule a report and send exported (csv) results to specific email ids?
What are the steps to generate schedule a report and send exported (csv) results to specific email ids?
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
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