I have a trigger(AI/AU) on Event staging object, which creates Events once the records are inserted into Event Staging.
I am getting System.LimitException: Too many query rows: 50001 exception because there are 4-5 SOQl queries within helper class which fetches more than 50k records.
I need list of all user,leads and contacts email ids in 3 seperate lists(around 65k count). I need to check if the recepeint/Cc fields email ids already exist in that list and process them if so.
How do i make a check of more than 50k records.
I am calling this class from trigger.
TRIGGER::
trigger EventnEventRelationtrigger on Event_Staging__c(after update,after Insert) {
If(trigger.isAfter){
If(Trigger.isInsert){
if(EventnEventRelation.runOnce()){
EventnEventRelation ERI=new EventnEventRelation();
ERI.CreateEvent(Trigger.new);
//ERA.DeleteOldEventStagings();
}
}
If(Trigger.isupdate){
if(EventnEventRelation.runOnce()){
EventnEventRelation ERU=new EventnEventRelation();
ERU.UpdateEvent(Trigger.new);
}
}
}}
CLASS::
Global class EventnEventRelation implements schedulable{
Public EventnEventRelation()
{
}
private static boolean run = true;
public static boolean runOnce(){
if(run){
run=false;
return true;
}else{
return run;
}
}
global void execute(SchedulableContext sc)
{
DeleteOldEventStagings();
}
Public void CreateEvent(List<Event_Staging__c> ESList)
{
Set<Id> successfulEventIds = new Set<Id>();
Set<string> UserEmailset=new set<string>();
Set<string> allUserEmailset=new set<string>();
Set<string> ConEmailset=new set<string>();
Set<string> LeadEmailset=new set<string>();
set<string> allemailset=new set<string>();
//fetch list of all active Users
List<profile> profids=[Select Id,name From profile where Name LIKE '%Chatter%'];
List<user> userlist=[Select id,Name,Email,profileid FROM User where IsActive = TRUE AND profileId not IN:profids ];
List<user> userlistall=[Select id,Name,Email FROM User where IsActive = TRUE ];
List<contact> conlist=[select id,name,email FROM contact];
List<Lead> Leadlist=[select id,name,email FROM Lead];
//Create a map of emailId and ID of users
map<string,id> UserMap=new map<string,id>();
map<string,id> UserMapall=new map<string,id>();
map<string,id> conMap=new map<string,id>();
map<string,id> leadMap=new map<string,id>();
for(user us:userlist)
{
UserMap.put(us.email,us.id);
}
for(user usa:userlistall)
{
allUserEmailset.add(usa.email);
UserMapall.put(usa.email,usa.id);
}
List<Event_Staging__c > Eventstagelist=[Select Event_Created_By__c,Meeting_UID__c,Event_Attendees__c,Event_CC__c FROM Event_Staging__c];
//Create map of meetingUID and eventattendeed of eventstaging
map<string,string> AttendeesMap=new map<string,string>();
for( Event_Staging__c Evs:Eventstagelist)
{
AttendeesMap.put(Evs.Meeting_UID__c,Evs.Event_Attendees__c+';'+Evs.Event_CC__c);
}
List<event> eventlist=new list<event>();
List<eventrelation> eventrelationlist=new list<eventrelation>();
List<eventrelation> maineventrelationlist=new list<eventrelation>();
List<string> ErrormessageList = New List<string>();
for(User Uu:userlist)
{
UserEmailset.add(uu.Email);
//system.debug('User Email--------->'+uu.Email);
}
for(contact cc:conlist)
{
ConEmailset.add(cc.Email);
conmap.put(cc.email,cc.id);
//system.debug('User Email--------->'+cc.Email);
}
for(Lead le:Leadlist)
{
LeadEmailset.add(le.Email);
leadmap.put(le.email,le.id);
//system.debug('User Email--------->'+le.Email);
}
system.debug('users size------->'+UserEmailset.size());
system.debug('contacts size------->'+ConEmailset.size());
system.debug('Leads size------->'+LeadEmailset.size());
system.debug('UserEmailset size>>>>---->>>>'+allemailset.size());
For(Event_Staging__c ES: ESList)
{
If(UserEmailset.contains(ES.Event_Created_By__c) )
{
Event ev = new Event();
Id UserId=UserMap.get(ES.Event_Created_By__c);
ev.ownerId=UserId;
system.debug('Event created By'+UserId);
ev.Subject=ES.Event_Subject__c;
ev.Location=ES.Event_Location__c;
ev.StartDateTime=ES.Event_Start_Date__c;
ev.EndDateTime=ES.Event_End_Date__c;
ev.Description=ES.Meeting_UID__c;
if(ES.Event_Duration_in_Minutes__c!=NULL){
ev.DurationInMinutes=ES.Event_Duration_in_Minutes__c.intvalue(); }
eventlist.add(ev);
}
else
{
Event evt = new Event();
evt.ownerId=UserInfo.getUserId();
evt.Subject=ES.Event_Subject__c;
evt.Location=ES.Event_Location__c;
evt.StartDateTime=ES.Event_Start_Date__c;
evt.EndDateTime=ES.Event_End_Date__c;
evt.Description=ES.Meeting_UID__c;
if(ES.Event_Duration_in_Minutes__c!=NULL){
evt.DurationInMinutes=ES.Event_Duration_in_Minutes__c.intvalue(); }
eventlist.add(evt);
}
}
if(eventlist.size()>0 && eventlist!=NULL)
{
try {
Database.SaveResult[] EventResultList = Database.insert(eventlist,false);
if (EventResultList != null){
for (Database.SaveResult er : EventResultList) {
if (er.isSuccess()) {
successfulEventIds.add(er.getId());
System.debug('Successfully inserted Event. Event ID: ' + er.getId());
}
else {
Database.Error[] errl = er.getErrors();
for(Database.Error err : errl){
ErrormessageList.add(err.getMessage());
System.debug(err.getStatusCode() + ' - ' + err.getMessage());
}
}
}
If(ErrormessageList.size()>0 && ErrormessageList!=NULL)
{
sendmail(ErrormessageList);
}
}
} catch (Exception e) {
System.debug(e.getTypeName() + ' - ' + e.getCause() + ': ' + e.getMessage());
}
}
List<Event> insertedEvents=[select id,description from Event WHERE id IN:successfulEventIds];
set<string> EventMeetingUIDSet=new set<string>();
List<Event_Staging__c> UpdateEventstaging = new list<Event_Staging__c>();
list<Event_Staging__c> Eventsup = new list<Event_Staging__c>();
Map<string,id> EventMap=new Map<string,id>();
for(Event e:insertedEvents){
EventMap.put(e.description,e.id);
}
for(Event ECB : insertedEvents)
{
EventMeetingUIDSet.add(ECB.description);
}
for(Event_Staging__c EVSCB :[select id,Event_Subject__c,Meeting_UID__c FROM Event_Staging__c] )
{
If(EventMeetingUIDSet.contains(EVSCB.Meeting_UID__c))
{
EVSCB.IsEventCreated__c=TRUE;
EVSCB.Event_ID__c =EventMap.get(EVSCB.Meeting_UID__c);
UpdateEventstaging.add(EVSCB);
}
}
If(UpdateEventstaging.size()>0 && UpdateEventstaging!=NULL)
{
update UpdateEventstaging;
}
for(event ev:eventlist)
{
string str=AttendeesMap.get(ev.Description);
system.debug('UserMapall size---------'+UserMapall.size());
system.debug('AttendeesMap size---------'+AttendeesMap.size());
system.debug('string --------'+str);
List<String> ReciepientMail= str.split(';');
system.debug('ReciepientMail size------------' +ReciepientMail.size());
system.debug('ReciepientMail size 1------------' +ReciepientMail[0]);
system.debug('ReciepientMail size 1------------' +ReciepientMail[1]);
system.debug('allUserEmailset>>>>---->>>>----' +allUserEmailset.size());
for(integer i=0;i<ReciepientMail.size();i++)
{
Eventrelation evr=new eventrelation();
If(allUserEmailset.contains(ReciepientMail[i]))
{
evr.relationid=UserMapall.get(ReciepientMail[i]);
}
else If(ConEmailset.contains(ReciepientMail[i]))
{
evr.relationid=conMap.get(ReciepientMail[i]);
}
else if(LeadEmailset.contains(ReciepientMail[i]))
{
evr.relationid=leadMap.get(ReciepientMail[i]);
}
evr.eventid=ev.Id;
evr.status='New';
eventrelationlist.add(evr);
}
maineventrelationlist.addall(eventrelationlist);
eventrelationlist.clear();
}
if(maineventrelationlist.size()>0 && maineventrelationlist!=NULL)
{
try {
Database.SaveResult[] EventRelationResultList = Database.insert(maineventrelationlist,false);
if (EventRelationResultList != null){
for (Database.SaveResult sr : EventRelationResultList) {
if (!sr.isSuccess()) {
Database.Error[] errlist = sr.getErrors();
for(Database.Error errr : errlist)
System.debug(errr.getStatusCode() + ' - ' + errr.getMessage());
}
}
}
} catch (Exception e) {
System.debug(e.getTypeName() + ' - ' + e.getCause() + ': ' + e.getMessage());
}
}
}
Public void UpdateEvent(List<Event_Staging__c> updateESList)
{
//List<Event_Staging__c> ListEventstaging = updateESList;
system.debug('updateESList size--------------'+updateESList.size());
List<string> DelEvents=new List<string>();
for(Event_Staging__c ESdel: updateESList){
DelEvents.add(ESdel.Event_ID__c);
}
system.debug('DelEvents size--------------'+DelEvents.size());
List<Event> DelList=[select id from Event WHERE id IN: DelEvents];
system.debug('DelList size--------------'+DelList.size());
If(DelList.size()>0 && DelList!=NULL)
{
Delete DelList;
}
createEvent(updateESList);
}
Public void sendmail(List<string> ErrorRecords)
{
Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();
email.setSubject('Failed Events');
email.setBccSender(false);
//string[] toaddress = New String[] {UserInfo.getUserEmail()};
string[] toaddress = New String[] {'[email protected]'};
//email.setCcAddresses(new String[] {'[email protected]'});
email.setSaveAsActivity(false);
String s='Hello '+UserInfo.getName()+',';
s += '\n\nThere are '+ ErrorRecords.size() + ' records which did not get created as salesforce Events.\n';
s += 'The following are the reasons for the failures:\n\n';
integer i=0;
for(string st : ErrorRecords){
string ss=ErrorRecords.get(i);
system.debug('Error in record---->>>>'+ss);
i++;
s +='\n'+ String.ValueOf(i) + '. ' + ss ;
}
s+='\n\nRegards,\n\nSalesforce Support Team';
email.setPlainTextBody(s);
email.setToAddresses(toaddress);
Messaging.SendEmailResult [] res =Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});
}
Public void DeleteOldEventStagings()
{
List<Event_Staging__c > DelList=new List<Event_Staging__c>();
DelList=[Select Id,name,Event_Subject__c,IsEventCreated__c from Event_Staging__c where CreatedDate < LAST_N_DAYS:07 AND Event_End_Date__c < TODAY];
If(DelList.size()>0 && DelList!=NULL){
Delete DelList;
}
}
}
Immediate help required. Thanks in advance.