0

I am working on the Entitlement Process and Milestones. When the case is closed, I need to calculate Total ElaspseTime of all the milestones. I've taken a reference from link : How to calculate the Overall Elapse Time of the SLA? .

Then for sure I need to write logic in after update Trigger.

I have written the below logic and needs some modification, also looking to bulkyfy the code. When Case is closed, Total ElaseTime of each Case should be calculated.

public static void calculateElasTime(List<Id> caseIds, String category, String subCategory){

        Map<Id, CaseMilestone> cmsMap = new Map<Id, CaseMilestone>([SELECT CaseId, Case.Category__c,
                                        CompletionDate,
                                        ElapsedTimeInMins,
                                        IsCompleted,
                                        MilestoneTypeId,
                                        MilestoneType.Name, 
                                        StartDate,Case.IsClosed 
                                    FROM CaseMilestone  
                                    WHERE CaseId IN ('LLLLLL','KKKKKK') 
                                    AND IsCompleted=true AND Case.IsClosed=true
                                    AND Case.Category__c ='Paypal'
                                    AND Case.SubCategory__c IN ('Basic Auth','Ssale')]);

        Map<Id, List<CaseMilestone>> caseIdToMilestone = new Map<Id, List<CaseMilestone>>();
        for(Id mId : cmsMap.keySet()){
            if(caseIdToMilestone.containsKey(cmsMap.get(mId).CaseId)){
                caseIdToMilestone.get(cmsMap.get(mId).CaseId).add(cmsMap.get(mId));
            }
            else{
                List<CaseMilestone> casesList = new List<CaseMilestone>();
                casesList.add(cmsMap.get(mId));
                caseIdToMilestone.put(cmsMap.get(mId).CaseId, casesList);
            }
        }

        Map<Id, Integer> caseIdToElapseTime = new Map<Id, Integer>();
        for(Id cid : caseIdToMilestone.keySet()){
            List<CaseMilestone> mtList = caseIdToMilestone.get(cid);
            Integer sum = 0;
            for(CaseMilestone cm : mtList){
                sum += cm.ElapsedTimeInMins;
                caseIdToElapseTime.put(cid, sum);
            }
        }       
    }

I think, in the Trigger.New we should check isClosed=True, instead of making call to database to check case is closed ? I am facing in updating the result and bulkifying the code.

EDIT:-1

I am getting very wired error:
Error: Invalid Data. 
    Review all error messages below to correct your data.
    Apex trigger CaseTrigger caused an unexpected exception, contact your administrator: CaseTrigger: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 500c0000009ggifAAA; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, CaseTrigger: maximum trigger depth exceeded Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif] Case trigger event AfterUpdate for [500c0000009ggif]: []: Class.CalculateElapseTimeUtil.calculateAllElapseTime: line 33, column 1
0

If I understand your question correctly, you want to calculate Total ElaspseTime of all the milestones for each Case.

Here it is using Aggregate query which is most efficient for this purpose:

public static void calculateElasTime(List<Id> caseIds, String category, String subCategory)
{
    List<Case> lstCase = new List<Case>();

    List<AggregateResult> results = [SELECT CaseId, SUM(ElapsedTimeInMins) elpsTime
                                    FROM CaseMilestone  
                                    WHERE CaseId IN:caseIds 
                                    AND IsCompleted=true AND Case.IsClosed=true
                                    AND Case.Category__c ='Paypal'
                                    AND Case.SubCategory__c IN ('Basic Auth','Ssale')
                                    GROUP BY CaseId]);

    for(AggregateResult result:results)
    {
        Case caseObj = new Case (Id = (Id) result.get('CaseId'), Total_ElapseTime__c = (Integer) result.get('elpsTime'));
        lstCase.add(caseObj);
    }
    update lstCase;
}

Update based on comments

You need to perform this operation in after trigger. Since record is readonly in after trigger context, you have a create a new instance of case object, prepare a list and finally update.

  • yes thanks !, for some category and subCategory I only want to calculate 1st Milestones elapseTime and from that perspective please provide the solution. Also provide solution for how to show data in Number Field Named as Total_ElapseTime__C – user4567570 Aug 15 '17 at 14:52
  • 1
    Thats not your original question. you can ask a separate question upon accepting this – Santanu Boral Aug 15 '17 at 14:54
  • check my updated answer – Santanu Boral Aug 15 '17 at 15:50
  • basically, follow the best practice whatever code is – Santanu Boral Aug 15 '17 at 16:31
  • you can do the conversion well, just google some sample code and fit into that conversion – Santanu Boral Aug 15 '17 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.