+ Start a Discussion
vanessa veronvanessa veron 

Class Schedulable via code Apex

Hello!!!
How do I:

1 - Report on a Visualforce page a request  to generate a CSV.
2 - Report on a Visualforce page the day of the week and time that I want to execute the request and send an email with the result.
3 - The class Schedulable Apex will contain the code for sending email.

How do I handle this?

I have the Schedulable class but do not know how to continue ..

global class CSVTest implements System.Schedulable {
    public String mailSouhaite {get; set;}
    public string inputText {get; set;}
   
    global void execute(SchedulableContext sc) {
        planifier();
    }

  
public void planifier(){
    String query=inputText;
    String premier=query.substringAfter('select ');
    premier=  premier.substringBefore('from');
   
    string titre= premier+'\n';
    string contenuCSV = titre;

    string queryResultatString = '';
    list<sObject> queryResultat = (List<sObject>)database.query(inputText);
    for(sObject a: queryResultat)
    {

        queryResultatString = queryResultatString + string.valueof(a);
    }

    list<string> queryLignes = queryResultatString.split('}');

    for(string s:queryLignes){
        list<string> queryColonnes = s.split(',');
        for(string st:queryColonnes){
            contenuCSV = contenuCSV + st.substringAfter('=') + ',';
        }
        contenuCSV = contenuCSV.substringBeforeLast(',').substringBeforeLast(',') + '\n';
    }
    
      //Send Mail
      Messaging.EmailFileAttachment csvPJ = new Messaging.EmailFileAttachment();
      blob csvBlob = Blob.valueOf(contenuCSV );
      string csvNom = 'nom_'+Date.today().format()+'.csv';
      csvPJ.setFileName(csvNom);
      csvPJ.setBody(csvBlob);
      Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
      String[] adressMail = new list<string> {mailSouhaite};
      
      String subject;
      subject = 'CSV - '+Date.today().format();
      email.setSubject(subject);
      email.setToAddresses(adressMail);
      email.setPlainTextBody('Mail....');
      email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvPJ});
      Messaging.SendEmailResult [] envoyer = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});
    }
}
}

-------------------------------------------------------------------------------------------------------------------------

PAGE

<apex:page controller="CSVTest">
  <apex:form >
  <apex:PageBlock >
    :::::::::: TEST TEST TEST :::::::::: <br /><br />
    Mail..........:&nbsp;<apex:inputText styleClass="classeMail" value="{!mailSouhaite}"/><br /><br />
    Request  :&nbsp;&nbsp;<apex:inputText styleClass="classeRequete" value="{!inputText}"/><br /><br /> 
    <apex:commandButton value="SEND" action="{!panifier}"/> <br/><br/>
   
  </apex:PageBlock>
  </apex:form>
</apex:page>
Thank you,

Best Answer chosen by vanessa veron
vanessa veronvanessa veron
Hi, I solved this problem:

Thank you

<apex:page standardController="CronTrigger" extensions="BBBB">
<style type="text/css">
        .classeHoraire { width: 60px; }
        .classeBig { width: 600px; }
    </style>
	
  <apex:form >
  <apex:PageBlock >
    :::::::::: TEST TEST TEST :::::::::: <br /><br />  
    Mail..........:&nbsp;<apex:inputText styleClass="classeBig" value="{!mail}"/><br /><br />
    Request..:&nbsp;<apex:inputText styleClass="classeBig" value="{!request}"/><br /><br />
    Job Name.......:&nbsp;<apex:inputText value="{!nmJob}"/><br /><br />
        
    Schedule.....:&nbsp;
    <apex:inputText value="{!schedule}" />
    <br /> <br /><br />
	
    <apex:commandButton value="Schedule" action="{!schedulejob}" /> 
  </apex:PageBlock>
  </apex:form>
</apex:page>

--------------------------------------------------------------------------------------------------------------------------------------------------

global class BBBB implements System.Schedulable {

global String mail{get;set;}
global String nmJob{get;set;}
global String request{get;set;}
global String schedule{get;set;}


global void execute(SchedulableContext sc) {
  newPublier();
}

global BBBB () {}


global BBBB (String mail, String request, String schedule) {
this.mail= mail;
this.request= request;
this.schedule= schedule;

}

global void setMail(String mail) {
    this.mail= mail;
}

global String getMail() {
    return mail;
}

global void setRequete(String request) {
    this.request= request;
}

global String getRequete() {
    return request;
}

global void setSchedule(String schedule) {
    this.schedule= schedule;
}

global String getSchedule() {
    return schedule;
}


global void schedulejob(){      
        String email = getMail();
        String req = getRequete();
        String sch = getSchedule();
                       
        String NomJobSchedulable = nmJob;
        BBBB p = new BBBB(email , req, sch);
        system.schedule(NomJobSchedulable , sch, p);
}

public void newPublier(){
    String query=request;
    String premier=query.substringAfter('select ');   
    premier=  premier.substringBefore('from');
      
    string titre= premier+'\n';
    string contenuCSV = titre;

    string queryResultatString = '';
 
    list<sObject> queryResultat = (List<sObject>)database.query(request);
    for(sObject a: queryResultat)
    {
        queryResultatString = queryResultatString + string.valueof(a);  
    }
   
    list<string> queryLignes = queryResultatString.split('}');

    for(string s:queryLignes){
        list<string> queryColonnes = s.split(',');
        for(string st:queryColonnes){
            contenuCSV = contenuCSV + st.substringAfter('=') + ',';
        }
        contenuCSV = contenuCSV.substringBeforeLast(',').substringBeforeLast(',') + '\n';
    }
      }

      Messaging.EmailFileAttachment csvPJ = new Messaging.EmailFileAttachment();
      blob csvBlob = Blob.valueOf(contenuCSV);
      string csvNom = 'cases_fermes_'+Date.today().format()+'.csv';
      csvPJ.setFileName(csvNom);
      csvPJ.setBody(csvBlob);
      Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
      String[] adressMail = new list<string> {mail};
      String subject = 'CSV - '+Date.today().format();
      email.setSubject(subject);
      email.setToAddresses(adressMail);
      email.setPlainTextBody('mail body.........');   
      email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvPJ});
      Messaging.SendEmailResult [] envoyer = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});
    }
  }   
}

All Answers

kevin Carotherskevin Carothers

I would generate your CSV file from a report.  Generating a CSV file from a query seems like a lot of work -- 
I wrote a blog post a few years back on how to do most of what you need - the link is here:

http://kevindotcar.wix.com/home/apps/blog/how-to-schedule-a-report-to-send

Hope this helps.

vanessa veronvanessa veron
Thank you!

But how can I use the code I've ever made ​​for him to be schedulable when the User inform the day and time he wants?
kevin Carotherskevin Carothers
Hi Vanessa,

so, unless I'm getting something wrong, It looks like you need to have a page that schedules data to be sent to an email addresses that may or may not be a salesforce user in your SFDC instance?

Hmmm -
Well, it would be ideal if it was possible to create a workflow rule from an APEX class - but I don't think that's posible.

What I think you need to do is from your VF page, create a record in a custom object that has the date and email address - something like this;


User-added image

...and then, have a scheduled apex class that runs at, say noon or something like that and that class reads all records that are "not sent" (ie;   date equals "today()")  and send your report to those email addresses saved on the record.

kevin Carotherskevin Carothers
Hi Vanessa,

This is the complete solution (If I got your requirements right);

0.  Create an object called ScheduledReport__c that has the following fields;
        Email__c  (type:  email)
        Processed__c (type:  checkbox)
       ScheduledDate__c  (type;  Date)
 

1.   Create the following class (a VF component controller);
public class CSVStream {
    public static Boolean isTest;
    public static String strEmailAddr;
    public static String strOut;
    public static Boolean restRequested;
    public String strEmail{get;set;}
    public String strRptname{get;set;}
    
    void CSVStream () {
        strOut = '';        
        }
 
   public String getCSVStream() {
        restRequested = System.isFuture() || System.isScheduled();
        executeRpt();
        return strOut;
        }
  
    public void executeRpt() {
        String requestURL;
        requestURL = '/' + strRptname + '?csv=1&exp=1';
        strOut = new PageReference(requestURL).getContent().toString();
        System.debug('CALLING executeRpt:  output= ' + strOut );
    }
 
}

2.   Create the following class; 

public class ScheduleController {

        public String From_Date {get;set;}  // Reseller ID
        public Date ScheduleDate{get;set;}
        public String To_Email{get;set;}
        
        public ScheduleController() {
            }

        public String getFrom_Date() {
            return From_Date;
            }

        public void getFrom_Date(String x) {
            From_Date = x;
            }    
                
        public void setSchedule () {
            //System.debug('Set Schedule to: ' + From_Date);
            callSchedule(From_Date);
            }
        public void SaveRecord() {
            ScheduledReport__c sr = new ScheduledReport__c();
            List<ScheduledReport__c> l =  new List<ScheduledReport__c>();
             try {    
                sr.Email__c = To_Email;
                sr.ScheduledDate__c = callSchedule(From_Date);
                l.add(sr);
                insert l;
                }
            catch(Exception e) {         
                ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,e.getMessage()));
                }    
            }
            
                  
        private Date callSchedule(String myDate) {
            String strCron = '0 12 ';
            String[] myDateOnly = myDate.split(' ');
            String[] strDate = myDateOnly[0].split('/');

            Integer myIntDate = integer.valueOf(strDate[1]);
            Integer myIntMonth = integer.valueOf(strDate[0]);
            Integer myIntYear = integer.valueOf(strDate[2]);
            Date d = Date.newInstance(myIntYear, myIntMonth, myIntDate);
            
            strCron += myIntDate + ' ' + myIntMonth + ' ' + '*';
            return d;
            }

        public String getLongDate(DateTime cDT) {
            String LongDate = cDT.format('MM/dd/yyyy');
            return LongDate;
            }
 }

3.   Create your VisualForce component;
<apex:component controller="CSVStream" access="global">
    <apex:attribute name="xstrRptname" description="report ID" type="String" assignTo="{!strRptname}"/>
    <apex:outputText value="{!CSVStream}" escape="false"/>
</apex:component>



4  Create your VisualForce email template;
...Make sure the "xstrRptname"  variable is set to your report data name!!!
<messaging:emailTemplate subject="Data Export" recipientType="User" >
<messaging:plainTextEmailBody >

Hi,

please find attached the report(s) you have requested...

Kind regards,
{!$Organization.Name}
</messaging:plainTextEmailBody>
    
<messaging:attachment filename="report.csv" >
<c:ReportExportController xstrRptname="00OP0000000Jp3N"/>
</messaging:attachment>
</messaging:emailTemplate>

5.   Create the class for reading your scheduled report records and  processing them;

global class ProcessReport implements Schedulable {
   global void execute(SchedulableContext ctx) {
      System.debug('Entered Cron trigger');
      List<ScheduledReport__c> sr = [SELECT Id, Email__c, Processed__c, ScheduledDate__c FROM ScheduledReport__c
                WHERE Processed__c != True];
      
      Account someAcct = new Account(name='reportacct', Zipx__c='91111');
      insert someAcct;
      
      Id templateId = '00Xi0000000ZliA';   // hard coded
      
      for(ScheduledReport__c s :sr) {
          System.debug('running report: ' + s.Email__c);
          
          Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
          //mail.setSubject('Report Data you requested');
          mail.setTemplateId(templateId);
          mail.setTargetObjectId(UserInfo.getUserId());
          mail.setSaveAsActivity(false);
          mail.setToAddresses( new List<String>{ s.Email__c } );
          Messaging.sendEmail( new List<Messaging.Email>{mail} );
          s.Processed__c = true;
          }
       delete  someAcct;
       update sr;
      }   
}

6.   Create the following VF page for creating report requests;
<apex:page controller="ScheduleController" id="_SchedulePage">
<apex:form id="_scheduleForm">

<script>
<style>
        span.dateInput span.dateFormat{
        display:none;
    }
</style>

function DynamicDatePicker(d_id) {
 //alert('id: ' + d_id);
 DatePicker.pickDate(false,d_id.id,false);
 }
function setFocusOnLoad() {} 
</script>
<apex:messages ></apex:messages>
<apex:pageBlock id="_pb">
  From:
  <apex:inputText value="{!ScheduleDate}" id="f1" 
     onfocus="DatePicker.pickDate(false, '_SchedulePage:_scheduleForm:_pb:f1', true);" required="true"/>
  <br/>

  Email: <apex:inputText value="{!To_Email}" id="f2"  onblur="setFocusOnLoad()" size="100" disabled="false"/>
</apex:pageBlock>
   
</apex:form>
</apex:page>


7.   From the console window, set up the scheduled job;
String s = '1 30 14 1-31 1-12 ? '; 
 ProcessReport abc = new ProcessReport();
 system.schedule('Report Job', s, abc);



...And your report will be sent to the address specified on the  ScheduledReport__c records at (about) 2:30.


vanessa veronvanessa veron
Thank you, but I want the User see the Visualforce page HE IS who will inform the date he wants to receive the email ..
not through the console window ...
How do I do this?

Thank you
vanessa veronvanessa veron
Hi...
I made changes to the code, but still have errors.
The JOB schedulable is created, but the email is not sent.

global class ASchedTeste implements System.Schedulable {
public String str{get;set;}
public String mail{get;set;}
public String nomJob{get;set;}

global void execute(SchedulableContext sc) {
  newPublier();
}

//Cria bem o job na hora informada 0 28 15 * * ?
public void schedulejob(){
        String NomJobSchedulable = nomJob;
        ASchedTeste p = new ASchedTeste();
        String sch = str; 
        system.schedule(NomJobSchedulable , sch, p);    
}

public void newPublier(){

List<case> acclist = [Select casenumber,subject, accountid from case limit 10];
string header = 'Id, Subject,Account, '+'\n';
string finalstr = header ;
for(case a: acclist)
{
   string recordString = a.casenumber + ',' + a.subject+ ',' + a.accountid +'\n';
   finalstr = finalstr + recordString;
}
Messaging.EmailFileAttachment csvAttc = new Messaging.EmailFileAttachment();
blob csvBlob = Blob.valueOf(finalstr);
string csvname= 'cases.csv';
csvAttc.setFileName(csvname);
csvAttc.setBody(csvBlob);
Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
String[] toAddresses = new list<string> {mail};
String subject = 'Report CSV';
email.setSubject(subject);
email.setToAddresses( toAddresses );
email.setPlainTextBody('Informations HERE');
email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvAttc});
Messaging.SendEmailResult [] r = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});
  }   
}

--------------------------------------------------------------------------------------------------------------------

<apex:page controller="ASchedTeste">
  <apex:form >
  <apex:PageBlock >
    :::::::::: TEST TEST TEST :::::::::: <br /><br />  
    Mail..........:&nbsp;<apex:inputText styleClass="classeMail" value="{!mail}"/><br /><br />
    Nom Job.......:&nbsp;<apex:inputText styleClass="classeMail" value="{!nomJob}"/><br /><br />
    Plan.....:<apex:inputText value="{!str}" />
    <apex:commandButton value="Schedule" action="{!schedulejob}" />
   
  </apex:PageBlock>
  </apex:form>
</apex:page>
Help me please!!!
Thank you
kevin Carotherskevin Carothers
Hi Vanessa,

If you put debug in your newPublier()  method, you'll see that the email address (mail) is null.

But if you put debug statements in schedulejob() it's set correctly.

Basically the scheduled class is called in the future, and the email address is null because it's another instance of the class.

If you really want to keep this code structured as it is, I'd put the email address in an object that the job could get to.

vanessa veronvanessa veron
Hi, I solved this problem:

Thank you

<apex:page standardController="CronTrigger" extensions="BBBB">
<style type="text/css">
        .classeHoraire { width: 60px; }
        .classeBig { width: 600px; }
    </style>
	
  <apex:form >
  <apex:PageBlock >
    :::::::::: TEST TEST TEST :::::::::: <br /><br />  
    Mail..........:&nbsp;<apex:inputText styleClass="classeBig" value="{!mail}"/><br /><br />
    Request..:&nbsp;<apex:inputText styleClass="classeBig" value="{!request}"/><br /><br />
    Job Name.......:&nbsp;<apex:inputText value="{!nmJob}"/><br /><br />
        
    Schedule.....:&nbsp;
    <apex:inputText value="{!schedule}" />
    <br /> <br /><br />
	
    <apex:commandButton value="Schedule" action="{!schedulejob}" /> 
  </apex:PageBlock>
  </apex:form>
</apex:page>

--------------------------------------------------------------------------------------------------------------------------------------------------

global class BBBB implements System.Schedulable {

global String mail{get;set;}
global String nmJob{get;set;}
global String request{get;set;}
global String schedule{get;set;}


global void execute(SchedulableContext sc) {
  newPublier();
}

global BBBB () {}


global BBBB (String mail, String request, String schedule) {
this.mail= mail;
this.request= request;
this.schedule= schedule;

}

global void setMail(String mail) {
    this.mail= mail;
}

global String getMail() {
    return mail;
}

global void setRequete(String request) {
    this.request= request;
}

global String getRequete() {
    return request;
}

global void setSchedule(String schedule) {
    this.schedule= schedule;
}

global String getSchedule() {
    return schedule;
}


global void schedulejob(){      
        String email = getMail();
        String req = getRequete();
        String sch = getSchedule();
                       
        String NomJobSchedulable = nmJob;
        BBBB p = new BBBB(email , req, sch);
        system.schedule(NomJobSchedulable , sch, p);
}

public void newPublier(){
    String query=request;
    String premier=query.substringAfter('select ');   
    premier=  premier.substringBefore('from');
      
    string titre= premier+'\n';
    string contenuCSV = titre;

    string queryResultatString = '';
 
    list<sObject> queryResultat = (List<sObject>)database.query(request);
    for(sObject a: queryResultat)
    {
        queryResultatString = queryResultatString + string.valueof(a);  
    }
   
    list<string> queryLignes = queryResultatString.split('}');

    for(string s:queryLignes){
        list<string> queryColonnes = s.split(',');
        for(string st:queryColonnes){
            contenuCSV = contenuCSV + st.substringAfter('=') + ',';
        }
        contenuCSV = contenuCSV.substringBeforeLast(',').substringBeforeLast(',') + '\n';
    }
      }

      Messaging.EmailFileAttachment csvPJ = new Messaging.EmailFileAttachment();
      blob csvBlob = Blob.valueOf(contenuCSV);
      string csvNom = 'cases_fermes_'+Date.today().format()+'.csv';
      csvPJ.setFileName(csvNom);
      csvPJ.setBody(csvBlob);
      Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
      String[] adressMail = new list<string> {mail};
      String subject = 'CSV - '+Date.today().format();
      email.setSubject(subject);
      email.setToAddresses(adressMail);
      email.setPlainTextBody('mail body.........');   
      email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvPJ});
      Messaging.SendEmailResult [] envoyer = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});
    }
  }   
}

This was selected as the best answer