function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

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

use input text value into SOQL

I have a Visualforce page that takes a request and then sends its result by email.
At present, the request is fixed, but I want to use any request.
So goes the question: how do this part to be dynamic:

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

Thank you!

The codes:

PAGE VISUALFORCE::::
<apex:page controller="AAAACSV">
    <style type="text/css">
        .classeRequete { width: 600px; }
        .classeMail { width: 400px; }
    </style>   
  <apex:form >
  <apex:PageBlock >
    Request :&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>


CLASS APEX::::
global class AAAACSV implements System.Schedulable {
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;

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

String lignes = contenuCSV;
List<String> parts = lignes.split('\n');
integer lineNumber = parts.size();

Messaging.EmailFileAttachment csvPJ = new Messaging.EmailFileAttachment();
blob csvBlob = Blob.valueOf(contenuCSV);
nomFichierCSV = 'Closed_'+Date.today().format()+'.csv';
csvPJ.setFileName(nomFichierCSV);
csvPJ.setBody(csvBlob);
Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
String[] adressMail = new list<string> {'mail@mail.com'};
String subject = 'Rreports';
email.setSubject(subject);
email.setToAddresses(adressMail);

email.setPlainTextBody('Mail....);
                        
email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvPJ});
Messaging.SendEmailResult [] envoyer = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email}); 

}
}

bob_buzzardbob_buzzard
I don't understand your question I'm afraid - the code you have posted is dynamic - it takes the results of the query and concatenates them.  The query comes from the page, so that is also dynamic.  Which part of this do you consider static?
marys pindamarys pinda
Hi...
Thank you for your help!

This part is static:

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

It works only with Cases requests!

Have a nice day!
Roman VasetskiyRoman Vasetskiy
Hello, Marys
Do I understand correctly ? Do you want this query to work with any kind of sObjects?
If so, You've just change 
  Case a: (List<Case>)database.query code part to sObject a : Database.query (...)

Later you can check for exact collection type using getSObjectType() method and to whatever you want depends on type
 
marys pindamarys pinda
Hello!!!

The User Shashank_SFDC helped me make the dynamic code, but now this code sends me the results in a CSV out of order. how can I solve this?

THE CODE:
global class AAABCTest 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 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';
    }
    
    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});
    }
}
}

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 .

Thank you,
Roman VasetskiyRoman Vasetskiy
In that case you need to form CSV file manually within foreach, like this.

List<String> scv = new List<String>();

for(sObject a: queryResult)
    {
         scv.add (a.get ('CaseNumber)'     + ', '
                      + a.get ('Subject')                        + ', '
                      + a.get ('Contact').get ('Name) + ', '
                      + a.get ('Status') 
                      + '\n'
         );
    }
After that, you will have array of strings where each strings are SCV-formatted one, ended with \n.
marys pindamarys pinda
But I woult like to work with any request, non ONLY select casenumber,subject,Contact.Name,status from Case where ClosedDate=TODAY

But any requests:
select Name from Contact order by Name asc
select Name, Email from Contact ....

Thank you
Roman VasetskiyRoman Vasetskiy
Not a problem at all. 
Since you have dynamically generated query, you cac store requested field in a collection.

Now you set a query as a whole string and pass it via setter. It's not so flexible and convinient. 
The better option is to pass data via constructor and
1. Pass fields as String[] 
2. Pass object from where to select as sObjectType, to minimize error.

Using this way you could have something like this

global class AAABCTest implements System.Schedulable {
    private final String[] field;
    private final String sObjectName;
    public string caseTT {get; set;}
    public final String query;

    global void execute(SchedulableContext sc) {
        .....
    }

    /**
     * fields - list of fields. Like  - new List<String> {'Subject', 'Name', 'whatever'}
     * type - sOBjectType. For Case it would be Case.sObjectType
     * tail - is the 'where' clause. Like 'where CloseDate = Today()'
    */
    public AAABCTest (List<String> fields, SObjectType type, String tail) {
        query = 'select ';
        Boolean isFirst = true;
        for (String field: fields) {
            query += isFirst ? '' : ', ' + field;

            isFirst = false;
        }

        query += ' from ' + type.getDescribe().getName() + ' ' + tail;
    }
    // return result in scv format
    private List<String> getSCVArray () {
        List<String> scv = new List<String>();

        for(sObject a: queryResult) {
            String  scvItem;
            Boolean isFirst = true;
            for (String field: fields)     {
                Object objValue =
                    !field.contains('.')
                        ? a.get (field)
                        : a.get (field.substring(0, field.indexOf ('.') - 1)) +
                          a.get (field.substring(field.indexOf ('.') + 1);

                scvItem += isFirst ? '' : ', ' + String.valueOf (objValue);
                isFirst = false;
            }

            scv.add (scvItem + '\n');
        }

        return scv;
    }
}
marys pindamarys pinda
Thank you!!!
But my first code works with many problems to order the fields...
Roman VasetskiyRoman Vasetskiy
Take a look to the code please, it totally depends on the order you'd fill the field list.
Order of fields in CVS will be absolutely the same as in the query - in the both cases fields will appear in the order they inserted to String<List> fields.

So, if you'd add fields in list like this,

List <String> fields = new List<String> ('Name', 'Status', 'SomeField1', 'SomeField2') then
query will be generated with the same order, namely - 'select Name, Status, SomeField1, SomeField2....'
and cvs will have the fields appear the same way - name, status, somefield1, somefield2
marys pindamarys pinda
Thank you
Have a nice day my friends!