You need to sign in to do that
Don't have an account?
Tatu Laakso
AggregateResult WEEK_IN_YEAR changes week number between wednesday and thursday, why?
Hi All
I am new to the apex and trying to link Sobjects Task Activities to a Sobject Weekly Utilization with the same week number and summarize activities' durations. I made a trigger which uses aggregateresult to do this, and it works just fine except it thinks, that the same week's wednesday and thursday have different week numbers. Could anyone help me with this?
Code:
Tatu
I am new to the apex and trying to link Sobjects Task Activities to a Sobject Weekly Utilization with the same week number and summarize activities' durations. I made a trigger which uses aggregateresult to do this, and it works just fine except it thinks, that the same week's wednesday and thursday have different week numbers. Could anyone help me with this?
Code:
trigger WeeklyUtilizationTrigger on Weekly_Utilization__c (after insert, after update) { if((!UtilizerClass.inUtiSync) && (!UtilizerClass.inTaskSync)){ UtilizerClass.inUtiSync = true; Map<Id,SFDC_Resource__c> resot = new Map<Id,SFDC_Resource__c>(); Map<Id,Task_Activity__c> tasksToUpdate = new Map<Id,Task_Activity__c>(); List<Weekly_Utilization__c> newUtiz = new List<Weekly_Utilization__c>(); List<Task_Activity__c> tsksToUpdate = new List<Task_Activity__c>(); List<Weekly_Utilization__c> utizToUpdate = new List<Weekly_Utilization__c>(); Map<Id,Task_Activity__c> acts = new Map<Id,Task_Activity__c>(); List<Date> dlist = new List<Date>(); Set<Id> tskIds = new Set<Id>(); Set<Id> utiIds = new Set<Id>(); Set<Id> resIds = new Set<Id>(); Map<Id,Task_Activity__c> taskIdsToTask = new Map<Id,Task_Activity__c>(); Map<Id,Id> wuResIds = new Map<Id,Id>(); Map<Id,Weekly_Utilization__c> utiz = new Map<Id,Weekly_Utilization__c>(); for(Weekly_Utilization__c uti : Trigger.New){ dlist.add(uti.Start_Date__c); resIDs.add(uti.Resource__c); utiz.put(uti.Id,uti); resot.put(uti.Resource__c,null); wuResIDs.put(uti.Id,uti.Resource__c); utiIds.add(uti.Id); } //Collecting dates and using the smallest one as a filter dlist.sort(); Date PVM_A = dlist[0]; //Quering Task Activities for(Task_Activity__c tsk :[select Id, Resourcee__c, Billable__c, Project_Task__c, Duration_in_minutes__c, Start__c, End__c, Week_Number__c from Task_Activity__c where Resourcee__c in :resIds and End__c>=:PVM_A and End__c <:PVM_A+7 and Billable__c=true]){ acts.put(tsk.Id,tsk); taskIdsToTask.put(tsk.Id,tsk); } system.debug('taskIdsToTask values: '+taskIdsToTask.keySet()); for(Task_Activity__c tsk :[select Id, Resourcee__c, Billable__c, Project_Task__c, Duration_in_minutes__c, Start__c, End__c, Week_Number__c from Task_Activity__c where Resourcee__c in :resIds and End__c>=:PVM_A and End__c <:PVM_A+7 and Weekly_Utilization__c in :utiIds]){ tskIds.add(tsk.Id); } Map<String,List<Id>> tskDates = new Map<String,List<Id>>(); Map<Id,Map<String,Decimal>> durations = new Map<Id,Map<String,Decimal>>(); //AggregateResult for Task Activities for(AggregateResult ar : [select Id, CALENDAR_YEAR(End__c) year, WEEK_IN_YEAR(End__c) week, SUM(Duration_in_minutes__c) duration from Task_Activity__c where End__c>=:PVM_A and End__c <:PVM_A+7 and Billable__c=true and Resourcee__c in :resIds group by rollup (CALENDAR_YEAR(End__c), WEEK_IN_YEAR(End__c), Id)]){ if(ar.get('year') != null && ar.get('week') != null && ar.get('Id') != null){ Task_Activity__c tsk = taskIdsToTask.get((Id)ar.get('Id')); if(tsk!=null){ system.debug('AGGREGATE RESULT TASK ID: '+(Id)ar.get('Id')); system.debug('TSK RECORD: '+tsk); system.debug('TSK RECORD WEEK: '+String.valueOf(ar.get('week'))); system.debug('TSK RECORD WEEK: '+(Long)ar.get('week')); String yearAndWeek = String.valueOf(ar.get('year')) + '-' + String.valueOf(ar.get('week')); if(tskDates.containsKey(yearAndWeek + '-' + tsk.Resourcee__c)){ tskDates.get(yearAndWeek + '-' + tsk.Resourcee__c).add(tsk.Id); }else{ tskDates.put(yearAndWeek + '-' + tsk.Resourcee__c, new List<Id>{tsk.Id}); } if(durations.containsKey(tsk.Resourcee__c)){ if(durations.get(tsk.Resourcee__c).containsKey(yearAndWeek)){ durations.get(tsk.Resourcee__c).put(yearAndWeek,durations.get(tsk.Resourcee__c).get(yearAndWeek) + (Decimal)ar.get('duration')); }else{ durations.get(tsk.Resourcee__c).put(yearAndWeek,(Decimal)ar.get('duration')); } }else{ durations.put(tsk.Resourcee__c, new Map<String,Decimal>{String.valueOf(ar.get('year'))+'-'+String.valueOf(ar.get('week')) => (Decimal)ar.get('duration')}); } } } } //AggrefateResult for Weekly Utilization system.debug('TASKDATES: '+tskDates); for(AggregateResult ar : [select Id, CALENDAR_YEAR(Start_Date__c) year, WEEK_IN_YEAR(Start_Date__c) week, SUM(Duration_in_minutes__c) duration from Weekly_Utilization__c where Id in :Trigger.New group by rollup (CALENDAR_YEAR(Start_Date__c), WEEK_IN_YEAR(Start_Date__c), Id)]){ Id resourceId = wuResIds.get((Id)ar.get('Id')); String yearAndWeek = String.valueOf(ar.get('year')) + '-' + String.valueOf(ar.get('week')); if(durations.containsKey(resourceId)){ if(durations.get(resourceId).containsKey(yearAndWeek)){ utizToUpdate.add(new Weekly_Utilization__c( Id=(Id)ar.get('Id'), Duration_in_minutes__c = (Decimal)durations.get(resourceId).get(yearAndWeek) )); } } if(ar.get('year') != null && ar.get('week') != null && ar.get('Id') != null){ if(tskDates.containsKey(yearAndWeek+'-'+resourceId)){ for(Id i2 : tskDates.get(yearAndWeek+'-'+resourceId)){ system.debug('TASK TO UPDATE: '+i2); tsksToUpdate.add(new Task_Activity__c( Id=i2, Weekly_Utilization__c=(Id)ar.get('Id') )); } } } } for(Task_Activity__c tsk :[select Id from Task_Activity__c where Id in :tskIds and Id not in :tsksToUpdate]){ system.debug('Task To Update: '+tsk); tsksToUpdate.add(new Task_Activity__c( Id=tsk.Id, Weekly_Utilization__c=null )); } system.debug('Tasks To Update Size: '+tsksToUpdate.size()); update utizToUpdate; update tsksToUpdate; } }Thanks
Tatu
Eric Pepin
I don't think it uses the calendar (Sunday-Saturday) to determine week number. I believe that WEEK_IN_YEAR reports the week # by taking the number of days elapsed in the year and dividing that by 7. So January 1-7 will always be Week 1, January 8-14 will always be Week 2, etc. Regardless of the day of the week that they fall on.