function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Kenneth KimbrellKenneth Kimbrell 

Can someone show me a better way to achieve a nested table output. I have working logic, but performance wise it is not great, and I feel there is a better way to achieve my result.

public pageReference getData(){
    	date sd = date.parse(sDate).toStartOfWeek();
    	date ed = date.parse(eDate);
    	hList = new list<Hours__c>([SELECT Name,Employee__r.Id,Date_worked__c,Employee__r.Offshore__c,Employee__r.Name,Employee__r.Department__c,Employee_Name__c,hours__c,Minutes__c,Hours_Decimal__c FROM Hours__c WHERE Type_of_Hours__c = 'Work' AND Date_worked__c >= :sd AND Date_worked__c <= :ed AND Employee__r.Department__c IN :selected ORDER BY Employee__r.Department__c,Date_worked__c,Employee__r.Name LIMIT 3000]);
    	wrap = new list<wrapper>();
    	string tempDepartment;
    	string tempEmpName;
    	date tempWorkDate;
    	date tempStart = sd;
    	decimal offshore = 0;
    	decimal onshore = 0;
    	decimal totalOffshore = 0;
    	decimal totalOnShore = 0;
    	decimal dateCount = 0;
    	decimal aggreagteTotal = 0;
    	integer hitLimit = 3000;
    	integer rpwp = 0;
    	integer compareRPWP = 0;
    	boolean fired = false;
    	boolean dateChanged = false;
    	boolean dateCountis13 = false;
    	map<date,integer> mapDateCount = new map<date,integer>();
    	if(hList.size() > 0 && hList.size() < hitLimit){
        	noResults = false;
    		for(Hours__c h: hList){
	    		//initiate a new wrapper for each department as a primary header
	    		if( (h.Employee__r.Department__c != null && ((tempDepartment != null && tempDepartment != h.Employee__r.Department__c) || ( tempDepartment == null)) ) ){
	    			fired = true;
	    			tempDepartment = h.Employee__r.Department__c;
	    			wrap.add(new wrapper(null,null,null,null,null,tempDepartment,'','','',null,'',''));
	    		}else{
	    			fired = false;
	    		}
	    		if(fired || dateCountis13){
    				dateCountis13 = false;
    				dateCount = 0;
    				totalOffshore = 0;
    				totalOnShore = 0;
    			}
	    		//initiate a new wrapper for each new week period, total employee hours for each week period for each department
	    		if(fired || (h.Date_worked__c != null && ((tempWorkDate != null && tempWorkDate != h.Date_worked__c) || ( tempWorkDate == null)) ) || (h.Employee__r.Department__c != null && ((tempDepartment != null && tempDepartment != h.Employee__r.Department__c) || ( tempDepartment == null)) ) ){
	    			dateChanged = true;
	    			tempWorkDate = h.Date_worked__c;
	    			offshore = 0;
	    			onshore = 0;
	    			rpwp = 0;
	    			dateCount++;
	    			for(Hours__c h1: hList){
	    				//get count of each record that is inside a given week period
	    				if(h1.Date_worked__c == tempWorkDate && h1.Employee__r.Department__c == tempDepartment){
	    					rpwp++;
	    				}
	    				//logic for totaling offshore hours by date and department
	    				if(h1.Date_worked__c == tempWorkDate && h1.Employee__r.Offshore__c && h1.Employee__r.Department__c == tempDepartment){
	    					offshore += h1.Hours_Decimal__c != null ? h1.Hours_Decimal__c : 0;
	    				}
	    				//logic for totaling onshore hours by date and department
	    				if(h1.Date_worked__c == tempWorkDate && h1.Employee__r.Offshore__c == false && h1.Employee__r.Department__c == tempDepartment){
	    					onshore += h1.Hours_Decimal__c != null ? h1.Hours_Decimal__c : 0;
	    				}
	    			}
	    			system.debug('rpwp: ' + rpwp);
					wrap.add(new wrapper(h.Date_worked__c.toStartOfWeek(),h.Date_worked__c,onshore,offshore,null,'total','','','',null,'',''));
					totalOffshore += offshore;
				    totalOnShore += onshore;
				    system.debug('OffShore: ' +  offshore + '  ' + tempWorkDate.format());
				    system.debug('OnShore: ' + onshore + '  ' + tempWorkDate.format());
    			}else{
    				dateChanged = false;
    			}
	    		//initiate a new wrapper for each record and give the ability to open and collapse all records under a given total week period
	    		// functionality now does an aggregate total of employee's that have multiple hour entrees for each week period.
	    		if( dateChanged || (h.Employee__r.Name != null && ((tempEmpName != null && tempEmpName != h.Employee__r.Name) || ( tempEmpName == null)) ) ){
	    			tempEmpName = h.Employee__r.Name;
	    			aggreagteTotal = 0;
	    			for(Hours__c h2: hList){
	    				if(tempEmpName == h2.Employee__r.Name && tempWorkDate == h2.Date_worked__c){
	    					aggreagteTotal += h2.Hours_Decimal__c;
	    				}
	    			}
	    			if(h.Employee__r.Offshore__c){
						wrap.add(new wrapper(h.Date_worked__c.toStartOfWeek(),h.Date_worked__c,0,h.hours__c,aggreagteTotal,'collapse',h.Employee__r.Name,h.Employee__r.Id,h.Name,h.Employee__r.Offshore__c,h.Employee__r.Department__c,h.Date_worked__c.format() ));
					}else{
						wrap.add(new wrapper(h.Date_worked__c.toStartOfWeek(),h.Date_worked__c,h.hours__c,0,aggreagteTotal,'collapse',h.Employee__r.Name,h.Employee__r.Id,h.Name,h.Employee__r.Offshore__c,h.Employee__r.Department__c,h.Date_worked__c.format() ));
					}
	    		}
				//compare record count on the outside loop to record count on the inner loop and set compare iterator to 0 if the date changes
				if(dateChanged){
					compareRPWP = 0;
				}
				compareRPWP++;
				system.debug('compareRPWP: ' +  compareRPWP );
				//if on the 13th week -- initiate a new wrapper and get quarterly totals -- compare record count so that inner loop matches outer loop
	    		if( (dateCount == 13 && compareRPWP == rpwp) ){
	    			system.debug('totalOffshore: ' +  totalOffshore );
		    		system.debug('totalOnShore: ' + totalOnShore );
	    			dateCountis13 = true;
	    			wrap.add(new wrapper(null,null,totalOnShore,totalOffshore,null,'quarter',tempDepartment,'','',null,'',''));
				}
	    	}
    	}else if(hList.size() <= 0){
    		noResults = true;
    		bannerMessage = 'Oops! No record found.';
    	}else if(hList.size() >= hitLimit){
    		noResults = true;
    		bannerMessage = 'Oops! 3000 Record limit hit.';
    	}else{
    		noResults = true;
    		bannerMessage = 'Oops! 50000 record limit hit.';
    	}
    	
    	system.debug('wrap: ' + wrap);
    	system.debug('hList: ' + hList);
    	return null;
    }


Here is my working method inside my class. Essentially I am doing a query on a custom object Hours__c. What I want to do is have the output on the table be first nested by each employee department. Then Nested by each work period, with all hours worked by that department for each work period. I essentially achieved this from the start by ordering the query that way. Now it was about how to make the logic grab it at the time I needed. But it also needed to aggregate the total hours if employees had more than one hour record per week period. I also finally after every 13 weeks output a total of hours for that quarter.

Here is a screencast of my custom report if you are interested in seeing what I achieved on the front-end but what I want to achieve on the backend and in a more effecient manner. The problem with my logic, is while it works, if there is a lot of data being queried. This logic is pretty slow. Because first it loops the query results once. but inside the first for loop I loop the same query 2 different times... ::slaps head:: ...I know it is terrible. 

So I want to see maybe if there is a more effecient way of achieving this with potentially a map or another wrapper class? Because it is all in one wrapper class too. I use front end logic to sort out the table based on results in the wrapper class. I just hide and show rows. Like i said, it works just not as effecient as I would like my logic to run.

here is the screen cast:

https://screencast-o-matic.com/watch/cF60cwYAiB