Cacher is the code snippet organizer for pro developers

new RemoteAdminTimeTrackingUtils().updateBurnedHours();

new RemoteAdminTimeTrackingUtils().generateTimeTracking();

var RemoteAdminTimeTrackingUtils = Class.create();
RemoteAdminTimeTrackingUtils.prototype = {
	
	initialize: function() {
	},
	
	//Query daily time cards for the current months and update burned hours
	updateBurnedHours: function() {
		// Query all remote admin projects
		var projectSysID = this.findRemoteAdminProject();
		
		// Loop through all remote admin project and update burned hours on the time tracking record.
		for (var i = 0; i < projectSysID.length; i++) {
			var burned = this.countBurnedHours(projectSysID[i]);
			var burnedMS = this.convertHoursToMilliSec(burned);
			
			this.updateTimeTrackingRecord(projectSysID[i], burnedMS);
		}
	},
	
	// Query all remote admin project type and return an array of project sysid
	findRemoteAdminProject: function() {
		
		var projectSysID = [];
		
		var gr = new GlideRecord('tsp2_project');
		gr.addQuery('project_type', 'Remote Admin');
		gr.addActiveQuery();
		gr.query();
		
		while (gr.next()) {
			projectSysID.push(gr.getUniqueValue().toString());
		}
		return projectSysID;
	},
	
	// Query the daily time card from the sysid and return the SUM of hours
	countBurnedHours: function(sysID) {
		var sum;
		
		var gr = new GlideAggregate('u_jamis_time_cards');
		gr.addQuery('u_date', '>=', gs.beginningOfThisMonth());
		gr.addQuery('u_date', '<=', gs.endOfThisMonth());
		gr.addEncodedQuery('u_jamis_project_nameNOT LIKENO BILL^u_jamis_project_nameNOT LIKENOBILL');
		gr.addQuery('u_cellar_project_number', sysID);
		gr.addAggregate('SUM', 'u_hours');
		gr.setGroup(false); // It won't return the SUM if the setGroup is not included
		gr.query();
		
		if (gr.next()) {
			sum = gr.getAggregate('SUM', 'u_hours');
		}
		
		//Return 0 if there's no timecard for the current month
		if (sum == '') {
			sum = 0;
		}
		
		return sum;
	},
	
	//Update time tracking record given the proejectID and burned hours
	updateTimeTrackingRecord: function(projectID, burned) {
		
		var gr = new GlideRecord('u_remote_admin_time_tracking');
		gr.addQuery('u_project', projectID);
		gr.addQuery('u_month', this.getCurrentMonth());
		gr.addQuery('u_year', this.getCurrentYear());
		gr.query();
		
		if (gr.next()) {
			gr.u_burned.setDateNumericValue(burned);
			gr.update();
		}
	},
	
	//Get the remaining hours from last month
	getRollOverLastMonth: function(projectID) {
		var gr = new GlideRecord('u_remote_admin_time_tracking');
		gr.addQuery('u_project', projectID);
		gr.addQuery('u_month', this.getPreviousMonth());
		
		if (this.getCurrentMonth() == 1) {
			gr.addQuery('u_year', this.getPreviousYear());
		} else {
			gr.addQuery('u_year', this.getCurrentYear());
		}
		
		gr.query();
		
		if (gr.next()) {
			return gr.u_remaining;
		}
	},
	
	//Convert hours to milliseconds
	convertHoursToMilliSec: function(hours) {
		return hours * 60 * 60 * 1000;
	},
	
	//Genarate time tracking record at the beginning of the month
	//Used in the scheduled job
	generateTimeTracking: function() {
		var projectSysID = this.findRemoteAdminProject();
		
		// Create a time tracking record if not found
		
		for (var i = 0; i < projectSysID.length; i++) {
			if (!this.findCurrentTimeTrackingRecord(projectSysID[i])) {
				var prj = new GlideRecord('tsp2_project');
				prj.get(projectSysID[i]);
				
				var monthlyCommit = this.convertHoursToMilliSec(prj.u_offering.u_monthly_commit);
				
				var gr = new GlideRecord('u_remote_admin_time_tracking');
				gr.u_project = projectSysID[i];
				gr.u_company = prj.company;
				gr.u_month = this.getCurrentMonth();
				gr.u_year = this.getCurrentYear();
				gr.u_allotted.setDateNumericValue(monthlyCommit);
				gr.u_comp.setDateNumericValue(0);
				gr.u_burned.setDateNumericValue(0);
				
				//Rollover if subscribe to
				if (prj.u_rollover == 1) {
					gr.u_rollover = this.getRollOverLastMonth(projectSysID[i]);
				} else {
					gr.u_rollover.setDateNumericValue(0);
				}
				
				gr.insert();
			}
		}
	},
	
	// Check to see if a time tracking record exist for the current month
	findCurrentTimeTrackingRecord: function(projectID) {
		var gr = new GlideRecord('u_remote_admin_time_tracking');
		gr.addQuery('u_project', projectID);
		gr.addQuery('u_month', this.getCurrentMonth());
		gr.addQuery('u_year', this.getCurrentYear());
		gr.query();
		
		if (gr.next()) {
			//If found, update the record
			var prj = new GlideRecord('tsp2_project');
			prj.get(projectID);
			
			var monthlyCommit = this.convertHoursToMilliSec(prj.u_offering.u_monthly_commit);
			
			gr.u_allotted.setDateNumericValue(monthlyCommit);
			
			//Rollover if subscribe to
			if (prj.u_rollover == 1) {
				gr.u_rollover = this.getRollOverLastMonth(projectID);
			} else {
				gr.u_rollover.setDateNumericValue(0);
			}
			
			gr.update();
			return true;
		} else {
			return false;
		}
	},
	
	getCurrentMonth: function() {
		var gdt = new GlideDateTime();
		return gdt.getMonthLocalTime();
	},
	
	getPreviousMonth: function() {
		var gdt = new GlideDateTime();
		gdt.addMonthsLocalTime(-1);
		return gdt.getMonthLocalTime();
	},
	
	getCurrentYear: function() {
		var gdt = new GlideDateTime();
		return gdt.getYearLocalTime();
	},
	
	getPreviousYear: function() {
		var gdt = new GlideDateTime();
		gdt.addYearsLocalTime(-1);
		return gdt.getYearLocalTime();
	},
	
	type: 'RemoteAdminTimeTrackingUtils'
};