function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Michael MMichael M 

How to organize soql results based on task.subject

Hello, I have written some apex code that currently does 3 things:
1) fetches the results of EmailStatuses for listemails 
2) puts that into a csv
3) emails that csv

I need to do a 4th thing: I need to summarize the results on the body of the email itself. In other words, I need to organize the queried data by "emailstatus.task.subject", and for each different subject, I need to show a summary of: 
1. number of contacts it was sent to
2. number of contacts who opened the email

Is this possible, and can you please show me how to do so. Here is my code so far:

// fetch Data using SOQL from Salesforce
List<Contact> contList = [Select id, name, CreatedDate, lastModifiedDate, email,
                           (SELECT  id, taskId, createdDate, firstOpenDate, lastOpenDate, timesOpened, emailTemplateName, task.subject, task.TaskSubtype
                          FROM EmailStatuses where createddate = last_month and task.tasksubtype = 'ListEmail')
                          from Contact where email != null ];
//Set Header values of the file
string csvHeader = 'Contact Name, Contact Email    , Subject, Date Sent, Times Opened, Task Subtype \n';
string mainContent = csvHeader;
for (Contact cnt: contList){
    for (emailstatus es : cnt.EmailStatuses){
        integer month = es.createddate.month();
        integer year = es.createddate.year();
    integer day = es.createddate.day();
        if (month == 12 && year == 2020 && day == 8){
      //Adding records in a string
       string recordString = cnt.name+','+cnt.email +','+es.task.subject +','+es.CreatedDate+','+es.timesOpened +','+es.task.tasksubtype +'\n';
       mainContent += recordString;
        }}}
Messaging.EmailFileAttachment csvAttcmnt = new Messaging.EmailFileAttachment ();

//Create CSV file using Blob
blob csvBlob = Blob.valueOf (mainContent);
string csvname= 'Contact.csv';
csvAttcmnt.setFileName (csvname);
csvAttcmnt.setBody (csvBlob);
Messaging.SingleEmailMessage singEmail = new Messaging.SingleEmailMessage ();
String [] toAddresses = new list<string> {'test@test.org'};
//Set recipient list
singEmail.setToAddresses (toAddresses);
String subject ='Contact CSV';
singEmail.setSubject (subject);
singEmail.setPlainTextBody ('Contact CSV');

//Set blob as CSV file attachment
singEmail.setFileAttachments (new Messaging.EmailFileAttachment []{csvAttcmnt});
Messaging.SendEmailResult [] r = Messaging.sendEmail (new Messaging.SingleEmailMessage [] {singEmail});
Best Answer chosen by Michael M
AbhishekAbhishek (Salesforce Developers) 
Actually, It's a tricky one and I have seen this scenario yet.

https://salesforce.stackexchange.com/questions/244253/soql-query-description-field-on-task-object-contain-any-string-from-a-list-of-st

But you can check the above once.