Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
marys pindamarys pinda 

request SOQL be dynamic?

Hi..
I would like to make bold part be dynamic.
It's simple: take any request  informed in input text and uses it correctly.
I have to make the input text receiving any request and work.

I want not just Cases, I want Leads, Opportunity, Contacts request ...
Thank you!

CONTROLLER:
global class AAAACSV implements System.Schedulable {
public String mailSouhaite {get; set;}
public string caseTT {get; set;}
  
global void execute(SchedulableContext sc) {
planifier();
}

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

//BE DYNAMIQUE??? IS IT POSSIBLE???
for(Case a: (List<case>)database.query(caseTT))
{
   string contenu = a.casenumber + ',' + a.subject+ ',' +a.Contact.Name+ ',' + a.status +'\n';
   contenuCSV = contenuCSV + contenu ;
}

Messaging.EmailFileAttachment csvPJ = new Messaging.EmailFileAttachment();
blob csvBlob = Blob.valueOf(contenuCSV);
string csvNom = 'cases_closed_'+Date.today().format()+'.csv';
csvPJ.setFileName(csvNom);
csvPJ.setBody(csvBlob);
Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
String[] adressMail = new list<string> {mailSouhaite};
String subject = 'Report - '+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="AAAACSV">
    <style type="text/css">
        .classeRequete { width: 600px; }
        .classeMail { width: 400px; }
    </style>
  <apex:form >
  <apex:PageBlock >
    :::::::::: PARAMETRAGE :::::::::: <br /><br />
    Mail..........:&nbsp;<apex:inputText styleClass="classeMail" value="{!mailSouhaite}"/><br /><br />
    Requête :&nbsp;&nbsp;<apex:inputText styleClass="classeRequete" value="{!caseTT}"/><br /><br />
    <apex:commandButton value="Valider" action="{!planifier}"/> <br/><br/>
    <apex:commandButton value="Effacer"/> <br/><br/>
  </apex:PageBlock>
  </apex:form>
</apex:page>
Best Answer chosen by marys pinda
ShashForceShashForce
I tried this different approach, which is the only one I could think of. This involves converting the whole query result into a string and creating a CSV string using it. This is not perfect though, as it causes problems when a field is blank or contains commas (,). But it was worth a try:

global class AAABCTest implements System.Schedulable {
    public String mailSouhaite {get; set;}
    public string caseTT {get; set;}
    public string nomFichierCSV {get; set;} 
      
    global void execute(SchedulableContext sc) {
        planifier();
    }
    
    public void planifier(){
    String query = caseTT;
    String premier=query.substringAfter('select');   
    premier=  premier.substringBefore('from');
      
    string titre= premier+'\n';
    string contenuCSV = titre;
   
    //I changed from here
    string queryResultStr = '';
    list<sObject> queryResult = (List<sObject>)database.query(caseTT);
    for(sObject a: queryResult)
    {
        queryResultStr = queryResultStr + string.valueof(a);
    }
    
    list<string> queryRows = queryResultStr.split('}');
    for(string s:queryRows){
        list<string> queryfields = s.split(',');
        for(string st:queryfields){
            contenuCSV = contenuCSV + st.substringAfter('=') + ',';
        }
        contenuCSV = contenuCSV.substringBeforeLast(',') + '\n';
    }
    //I changed until here
    
    String lignes = contenuCSV;
    List<String> parts = lignes.split('\n');
    integer lineNumber = queryResult.size();

    integer numLinesPerAttach = 4;

    for(integer i=0;i<lineNumber;i++){
      string splitfile = parts[0] + '\n';
      if(math.mod(i,numLinesPerAttach)<>0) continue;
      if((lineNumber-i)<numLinesPerAttach){
        for(integer j=1;j<=(lineNumber-i);j++){
            splitfile = splitfile + parts[i+j] + '\n';
        }
      }
      if((lineNumber-i)>=numLinesPerAttach){
        for(integer j=1;j<=numLinesPerAttach;j++){
            splitfile = splitfile + parts[i+j] + '\n';
        }
      }
      Messaging.EmailFileAttachment csvPJ = new Messaging.EmailFileAttachment();
      blob csvBlob = Blob.valueOf(splitfile);
      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> {mailSouhaite};
      String 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});
    }
}
}

If this answers your question, please mark this as the Best Answer for this post, so that others can benefit from this post.

Thanks,
Shashank

All Answers

ShashForceShashForce
Hi,

Please try something like this using the generic sObject:

for(sObject a: (List<sObject>)database.query(caseTT))
{
   string contenu = a.casenumber + ',' + a.subject+ ',' +a.Contact.Name+ ',' + a.status +'\n';
   contenuCSV = contenuCSV + contenu ;
}

If this answers your question, please mark this as the Best Answer for this post, so that others can benefit from this post.

Thanks,
Shashank
marys pindamarys pinda
Thank you,

But it does not work ...
If I inform in  Visualforce page EG:

select firstName from Contact, does not work!

How to do it: string = contenu a.casenumber + ',' + a.subject + ',' + a.Contact.Name + ',' + a.status + '\ n';

Be dynamic and take the firstName informed?

Thank you...
marys pindamarys pinda
I have a error with generic SObject:

Error: Compile Error: Field expression not allowed for generic SObject

Why?
Thank you!
ShashForceShashForce
In that case, we have to get the field names and values from the query string and use ths sObject instance methods getObject() and get() to get the field values.

e.g.: 
sObject obj = sObject.get('object name');
string fieldValue = acc.get('field name');

It can get pretty complicated, as you need to traverse through the query string to find the object name and field names using string methods like substring() and split()
marys pindamarys pinda
Thank you...
I tried to do so, see if you can help me:
You an error on the bold = s

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

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

String conteudo = premier;
List<String> conteudoVirgula = conteudo .split(',');
integer taille = conteudoVirgula.size();  
string contenu;
   
for(Case a: (List<Case>)database.query(caseTT))
{
    for(Integer i = 0; i <= conteudoVirgula.size(); i++){
     string separe = conteudoVirgula[i];
        contenu = a.separe. +'\n';
    }
    contenuCSV = contenuCSV + contenu ;
}

//Date.valueOf(System.now()) -- Date et heure
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> {mailSouhaite};
String subject = 'Rapport Cases Fermés 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});
}
}
marys pindamarys pinda
Another tip?
ShashForceShashForce
I tried this different approach, which is the only one I could think of. This involves converting the whole query result into a string and creating a CSV string using it. This is not perfect though, as it causes problems when a field is blank or contains commas (,). But it was worth a try:

global class AAABCTest implements System.Schedulable {
    public String mailSouhaite {get; set;}
    public string caseTT {get; set;}
    public string nomFichierCSV {get; set;} 
      
    global void execute(SchedulableContext sc) {
        planifier();
    }
    
    public void planifier(){
    String query = caseTT;
    String premier=query.substringAfter('select');   
    premier=  premier.substringBefore('from');
      
    string titre= premier+'\n';
    string contenuCSV = titre;
   
    //I changed from here
    string queryResultStr = '';
    list<sObject> queryResult = (List<sObject>)database.query(caseTT);
    for(sObject a: queryResult)
    {
        queryResultStr = queryResultStr + string.valueof(a);
    }
    
    list<string> queryRows = queryResultStr.split('}');
    for(string s:queryRows){
        list<string> queryfields = s.split(',');
        for(string st:queryfields){
            contenuCSV = contenuCSV + st.substringAfter('=') + ',';
        }
        contenuCSV = contenuCSV.substringBeforeLast(',') + '\n';
    }
    //I changed until here
    
    String lignes = contenuCSV;
    List<String> parts = lignes.split('\n');
    integer lineNumber = queryResult.size();

    integer numLinesPerAttach = 4;

    for(integer i=0;i<lineNumber;i++){
      string splitfile = parts[0] + '\n';
      if(math.mod(i,numLinesPerAttach)<>0) continue;
      if((lineNumber-i)<numLinesPerAttach){
        for(integer j=1;j<=(lineNumber-i);j++){
            splitfile = splitfile + parts[i+j] + '\n';
        }
      }
      if((lineNumber-i)>=numLinesPerAttach){
        for(integer j=1;j<=numLinesPerAttach;j++){
            splitfile = splitfile + parts[i+j] + '\n';
        }
      }
      Messaging.EmailFileAttachment csvPJ = new Messaging.EmailFileAttachment();
      blob csvBlob = Blob.valueOf(splitfile);
      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> {mailSouhaite};
      String 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});
    }
}
}

If this answers your question, please mark this as the Best Answer for this post, so that others can benefit from this post.

Thanks,
Shashank
This was selected as the best answer
marys pindamarys pinda
Very good!
Can you help me understand?can you comment this part please:

string queryResultStr = '';
    list<sObject> queryResult = (List<sObject>)database.query(caseTT);
    for(sObject a: queryResult)
    {
        queryResultStr = queryResultStr + string.valueof(a);
    }
   
    list<string> queryRows = queryResultStr.split('}');
    for(string s:queryRows){
        list<string> queryfields = s.split(',');
        for(string st:queryfields){
            contenuCSV = contenuCSV + st.substringAfter('=') + ',';
        }
        contenuCSV = contenuCSV.substringBeforeLast(',') + '\n'

The CSV has an information PLUS:

Name

Christian Armando,0032000001Cu4q7AAB
Mary Scott,0032000001Cu4lfAAB

What is this?
Thank you
Have a nice day!!!
marys pindamarys pinda
The request used: select Name from contact;
ShashForceShashForce
The extra information you are seeing is because a soql query will always return the Id of a record though the Id field is not included in the result.

Please change from:

contenuCSV = contenuCSV.substringBeforeLast(',') + '\n';

to:

contenuCSV = contenuCSV.substringBeforeLast(',').substringBeforeLast(',') + '\n';

Regarding the comments on the code:

string queryResultStr = '';
//converting the query result into a single list.
    list<sObject> queryResult = (List<sObject>)database.query(caseTT);
//convert the list into a single string
    for(sObject a: queryResult)
    {
        queryResultStr = queryResultStr + string.valueof(a);
    }
  //traverse through the string and buld the string for our csv file. Thing to note here is the format in which a query result is returnen when converted into a string
//please use system.debug to check how the query result string looks.
    list<string> queryRows = queryResultStr.split('}');
    for(string s:queryRows){
        list<string> queryfields = s.split(',');
        for(string st:queryfields){
            contenuCSV = contenuCSV + st.substringAfter('=') + ',';
        }
//removing the Id part of the query result and adding a line break..
    contenuCSV = contenuCSV.substringBeforeLast(',').substringBeforeLast(',') + '\n';
marys pindamarys pinda
Thank you so much!!!
You are very good at Apex.
Congratulations!!!
vanessa veronvanessa veron
Hello!
I used your code but I have a problem:

REQUEST: select casenumber,subject,Contact.Name,status from Case where ClosedDate=TODAY

RESULTAT INTO CSV:
casenumber,subject,Contact.Name,status
Closed,case02,0032000001D8WWsAAN,5002000000arWuwAAE
Closed,case01,0032000001D8WWsAAN,5002000000arWuOAAU

The code generates the results to the contrary and the author name and ID case are encoded .
How do I solve this?

Thank you
marys pindamarys pinda
It's true,
the code generates the results to the contrary and the author name and ID case are encoded .
How do I solve this?

Thank you,