Hi all,

I've created an sObject which summarizes task activity hours of a resource for a specific week. I need to make a batch job that creates these Weekly Utilization records for every week for every resource, even if there are no task activities for some weeks. I am new to apex and have difficulties to get started with this, so any help will be appreciated.

Thanks in advance
Tatu

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:

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