You need to sign in to do that
Don't have an account?

Getting error:-"NewCaseSendEmail: System.LimitException: Too many SOQL queries: 101"
Please help in bulkifying the trigger so that update from datalaoder is possible.
Trigger code
trigger NewCaseSendEmail on Case (after insert, after update) {
system.debug('inside Case after insert update trigger*****');
if(Label.EXECUTE_TRIGGER == 'False')
return ;
//Subhash Garhwal - 12/26/2016 - Added Record Type check to exclude XVELA and Interschalt record types
//Code Modified By Karthikeyan Ramamurthy - WeServetech 21-03-2017
//Included the new xvela record types.
Case[] cases = [select id, AccountId, CaseNumber, type, subject, Product__c, Priority, RecordType.Name from case where id in :Trigger.new
And RecordType.DeveloperName Not IN('XVELA' ,'XVELA_Question','XVELA_Dongle','XVELA_Error','XVELA_Sales','Interschalt')];
CustomEvent__c[] events = [select id,date__c,useralias__c,User_Alias__c, start__c, end__c from customevent__c where
(start__c <= :System.Now()) and (end__c >= :System.Now())];
system.debug('eventsr*****'+events);
System.debug('YYYYY'+cases);
for(Case currentCase :cases){
if(events.size() > 0 && currentCase.AccountId != null)
{
system.debug('currentCase*****'+currentCase);
if(Trigger.isUpdate)
{
system.debug('Trigger.isUpdate'+Trigger.isUpdate);
Case oldCase = Trigger.oldMap.get(currentCase.ID);
if(currentCase.Priority.equals(oldCase.Priority))
{
continue;
}
}
CustomEvent__c currentEvent = events[0];
list<Settings__C> settingsList = [select id,priorities__c from settings__c LIMIT 1];
Settings__C settings;
if(settingsList != null && !settingsList.isEmpty()){
settings = settingsList[0];
}else{
//add error if no settings record present
//trigger.newMap.get(currentCase.Id).addError('No Settings have been created. Please create settings.');
}
list<Account> accList = [select id,priorities__c,site from account where id = :currentCase.AccountId];
Account account;
if(accList != null && ! accList.isEmpty()){
account = accList[0];
}else{
//add error if no settings record present
//trigger.newMap.get(currentCase.Id).addError('No Account associated with Case');
}
String debugForContent = '';
system.debug('account.Id --->' + account);
system.debug('currentEvent.useralias__c*******'+currentEvent.useralias__c);
system.debug('settingsc*******'+settings);
system.debug('accountc*******'+account);
if(settings != null && account != null && currentEvent.User_Alias__c != null)
{
List<String> accountPriorities = new List<String>();
List<String> settingsPriorities= settings.Priorities__c.split(';');
system.debug('settingsPrioritie+++++++++++++++'+settingsPriorities);
if(account.Priorities__c == null)
{
accountPriorities= new List<String>();
}
else
{
accountPriorities = account.Priorities__c.split(';');
}
system.debug('accountPriorities+++++++++++++++'+accountPriorities);
list<Contact> relatedContactList = [select id,phone from contact where accountid = :account.Id];
Contact accountFirstContact;
if(relatedContactList != null && !relatedContactList.isEmpty()){
accountFirstContact = relatedContactList[0];
}
String contactPhone = '';
system.debug('currentEvent.user_alias__c -->' + currentEvent.user_alias__c);
list<User> userList = [select Instant_Messager_ID__c from user where CommunityNickname = :currentEvent.user_alias__c and isActive = true];
User eventAttachedUser;
if(userList != null && !userList.isEmpty()){
eventAttachedUser = userList[0];
}
system.debug('eventAttachedUser-->' + eventAttachedUser);
// List<User> eventAttachedUsers = [select Instant_Messager_ID__c from user where CommunityNickname = :currentEvent.useralias__c];
//If (eventAttachedUsers !=null && eventAttachedUsers.size()>0) {
// User eventAttachedUser =eventAttachedUsers[0];
// System.Debug(eventAttachedUser);
if(accountFirstContact != null)
{
contactPhone = accountFirstContact.Phone;
}
Boolean canContinue = false;
if(accountPriorities != null && settingsPriorities != null)
{
for(String i : accountPriorities){
for(String j : settingsPriorities){
debugForContent = debugForContent + ' '+i+'-' +j;
if(i.equals(j)){
canContinue = true;
break;
}
}
}
}
if(!canContinue) {
if(currentCase.Priority == '1 Critical')
{
canContinue = true;
}
else
{
canContinue = false;
}
}
system.debug('canContinue-->' + canContinue);
if(eventAttachedUser ==null || eventAttachedUser.Instant_Messager_ID__c == null)
{
canContinue = false;
system.debug('$$$$'+canContinue);
}
System.debug('HELLO' + eventAttachedUser + ' with priority' + currentCase.Priority + canContinue);
if(canContinue)
{
system.debug('%%%');
// Messaging.reserveSingleEmailCapacity(2);
Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
String[] toAddresses = new String[] {eventAttachedUser.Instant_Messager_ID__c};
mail.setToAddresses(toAddresses);
mail.setSenderDisplayName('SalesForce Case Trigger');
mail.setSubject('Critical Case Alert: #'+ currentCase.CaseNumber + ' ' + account.Site + ' '
+ currentCase.Type+', Ph ' + contactPhone );
mail.setBccSender(false);
mail.setUseSignature(false);
mail.setPlainTextBody(
'Case: ' + currentCase.CaseNumber
+'\n'
+'Subject: ' + currentCase.Subject
+'\n'
+'Account : ' + account.Site
+'\n'
+'Product : ' + currentCase.Product__c
+'\n'
+'Contact #: ' +contactPhone
);
mail.setHtmlBody(
'Case: ' + currentCase.CaseNumber
+'<br/>'
+'Subject: ' + currentCase.Subject
+'<br/>'
+'Account : ' + account.Site
+'<br/>'
+'Product : ' + currentCase.Product__c
+'<br/>'
+'Contact #: ' +contactPhone
);
Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
}
}
}
}
}
Trigger code
trigger NewCaseSendEmail on Case (after insert, after update) {
system.debug('inside Case after insert update trigger*****');
if(Label.EXECUTE_TRIGGER == 'False')
return ;
//Subhash Garhwal - 12/26/2016 - Added Record Type check to exclude XVELA and Interschalt record types
//Code Modified By Karthikeyan Ramamurthy - WeServetech 21-03-2017
//Included the new xvela record types.
Case[] cases = [select id, AccountId, CaseNumber, type, subject, Product__c, Priority, RecordType.Name from case where id in :Trigger.new
And RecordType.DeveloperName Not IN('XVELA' ,'XVELA_Question','XVELA_Dongle','XVELA_Error','XVELA_Sales','Interschalt')];
CustomEvent__c[] events = [select id,date__c,useralias__c,User_Alias__c, start__c, end__c from customevent__c where
(start__c <= :System.Now()) and (end__c >= :System.Now())];
system.debug('eventsr*****'+events);
System.debug('YYYYY'+cases);
for(Case currentCase :cases){
if(events.size() > 0 && currentCase.AccountId != null)
{
system.debug('currentCase*****'+currentCase);
if(Trigger.isUpdate)
{
system.debug('Trigger.isUpdate'+Trigger.isUpdate);
Case oldCase = Trigger.oldMap.get(currentCase.ID);
if(currentCase.Priority.equals(oldCase.Priority))
{
continue;
}
}
CustomEvent__c currentEvent = events[0];
list<Settings__C> settingsList = [select id,priorities__c from settings__c LIMIT 1];
Settings__C settings;
if(settingsList != null && !settingsList.isEmpty()){
settings = settingsList[0];
}else{
//add error if no settings record present
//trigger.newMap.get(currentCase.Id).addError('No Settings have been created. Please create settings.');
}
list<Account> accList = [select id,priorities__c,site from account where id = :currentCase.AccountId];
Account account;
if(accList != null && ! accList.isEmpty()){
account = accList[0];
}else{
//add error if no settings record present
//trigger.newMap.get(currentCase.Id).addError('No Account associated with Case');
}
String debugForContent = '';
system.debug('account.Id --->' + account);
system.debug('currentEvent.useralias__c*******'+currentEvent.useralias__c);
system.debug('settingsc*******'+settings);
system.debug('accountc*******'+account);
if(settings != null && account != null && currentEvent.User_Alias__c != null)
{
List<String> accountPriorities = new List<String>();
List<String> settingsPriorities= settings.Priorities__c.split(';');
system.debug('settingsPrioritie+++++++++++++++'+settingsPriorities);
if(account.Priorities__c == null)
{
accountPriorities= new List<String>();
}
else
{
accountPriorities = account.Priorities__c.split(';');
}
system.debug('accountPriorities+++++++++++++++'+accountPriorities);
list<Contact> relatedContactList = [select id,phone from contact where accountid = :account.Id];
Contact accountFirstContact;
if(relatedContactList != null && !relatedContactList.isEmpty()){
accountFirstContact = relatedContactList[0];
}
String contactPhone = '';
system.debug('currentEvent.user_alias__c -->' + currentEvent.user_alias__c);
list<User> userList = [select Instant_Messager_ID__c from user where CommunityNickname = :currentEvent.user_alias__c and isActive = true];
User eventAttachedUser;
if(userList != null && !userList.isEmpty()){
eventAttachedUser = userList[0];
}
system.debug('eventAttachedUser-->' + eventAttachedUser);
// List<User> eventAttachedUsers = [select Instant_Messager_ID__c from user where CommunityNickname = :currentEvent.useralias__c];
//If (eventAttachedUsers !=null && eventAttachedUsers.size()>0) {
// User eventAttachedUser =eventAttachedUsers[0];
// System.Debug(eventAttachedUser);
if(accountFirstContact != null)
{
contactPhone = accountFirstContact.Phone;
}
Boolean canContinue = false;
if(accountPriorities != null && settingsPriorities != null)
{
for(String i : accountPriorities){
for(String j : settingsPriorities){
debugForContent = debugForContent + ' '+i+'-' +j;
if(i.equals(j)){
canContinue = true;
break;
}
}
}
}
if(!canContinue) {
if(currentCase.Priority == '1 Critical')
{
canContinue = true;
}
else
{
canContinue = false;
}
}
system.debug('canContinue-->' + canContinue);
if(eventAttachedUser ==null || eventAttachedUser.Instant_Messager_ID__c == null)
{
canContinue = false;
system.debug('$$$$'+canContinue);
}
System.debug('HELLO' + eventAttachedUser + ' with priority' + currentCase.Priority + canContinue);
if(canContinue)
{
system.debug('%%%');
// Messaging.reserveSingleEmailCapacity(2);
Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
String[] toAddresses = new String[] {eventAttachedUser.Instant_Messager_ID__c};
mail.setToAddresses(toAddresses);
mail.setSenderDisplayName('SalesForce Case Trigger');
mail.setSubject('Critical Case Alert: #'+ currentCase.CaseNumber + ' ' + account.Site + ' '
+ currentCase.Type+', Ph ' + contactPhone );
mail.setBccSender(false);
mail.setUseSignature(false);
mail.setPlainTextBody(
'Case: ' + currentCase.CaseNumber
+'\n'
+'Subject: ' + currentCase.Subject
+'\n'
+'Account : ' + account.Site
+'\n'
+'Product : ' + currentCase.Product__c
+'\n'
+'Contact #: ' +contactPhone
);
mail.setHtmlBody(
'Case: ' + currentCase.CaseNumber
+'<br/>'
+'Subject: ' + currentCase.Subject
+'<br/>'
+'Account : ' + account.Site
+'<br/>'
+'Product : ' + currentCase.Product__c
+'<br/>'
+'Contact #: ' +contactPhone
);
Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
}
}
}
}
}