+ Start a Discussion
bohemianguy100bohemianguy100 

help calculating event start and end dates for events

I have a trigger that is creating recurring events based on the number of hours specificed for a specific month.  For example, the trigger fires and queries a record that indicates there are 40 hours in January.  My trigger logic would create a recurring event for 5 days because there are 8 hours in a day and 5 working days X 8 hours is 40 hours.

 

The trick is I can only create the events on actual workdays (not weekends Saturday and Sunday) and lets I am in December 2010, but the record entry is for January 2011.  I need to obtain the 2011 year and not 2010.

 

Can anyone provide some help on how I can correctly calculate the recurrencestartdatetime for the event and the RecurrenceEndDateOnly?

 

My trigger is creating the recurring events, but the startDates are off and the endDate is not correct for the series.  I'm not clear on how to properly calculate these dates.

 

Additionally, all I have to work with from the record is the month index...i.e. Jan = 1, Feb = 2, March = 3, etc.

 

Here is my code:

 

private static void doCreateResourceEvents(List<ProductManagement__c> inNew, Map<Id, ProductManagement__c> inOld)
    {
    	RecordType rt = [Select Id From RecordType Where Name = 'Service Delivery Event' and sObjectType = 'Event'];
    	
    	List<Scheduling__c> schedulingNew = [Select Hours__c, Month_Index__c, Resource__r.Id, Resource__r.User_Id__c, ProductManagement__r.Id From Scheduling__c Where ProductManagement__c in: inNew];
				
		List<Event> evs = new List<Event>();
	
		for (Scheduling__c s : schedulingNew) {
						
			if (s.Hours__c != null && s.Resource__r.User_Id__c != null 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res1__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res2__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res3__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res4__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res5__c) {
				Integer recurrenceInterval = math.round(s.Hours__c / 8);
				Integer mnth = math.round(s.Month_Index__c);
				Integer endDay = system.today().day() + recurrenceInterval;
				Datetime startDate = Datetime.newInstance(system.today().year(), mnth, system.today().day(), 8, 0, 0);
				Date endDate = Date.newInstance(system.today().year(), mnth, endDay);
				

				Event e = new Event();
				e.IsRecurrence = true;
				e.RecurrenceDayOfWeekMask = 62;
				e.RecurrenceType = 'RecursEveryWeekday';
				e.OwnerId = s.Resource__r.User_Id__c;
				e.RecordTypeId = rt.Id;
				e.Subject = 'Other';
				e.RecurrenceStartDateTime = startDate;
				e.RecurrenceEndDateOnly = endDate;
				e.DurationInMinutes = 60;
				evs.add(e);
				
			}
			
		}
    	insert evs;
    }	

 

Thanks for any help.

Best Answer chosen by Admin (Salesforce Developers) 
mikefmikef

 

endDate = endDate.addDays(recurrenceInterval + skipToNextBusinessDay(endDate));

 

 

All Answers

mikefmikef

I think what you can do to fix this is create you end date like this.

 

 

Integer recurrenceInterval = math.round(s.Hours__c / 8);
				Integer mnth = math.round(s.Month_Index__c);
				Integer endDay = system.today().day() + recurrenceInterval;
				Datetime startDate = Datetime.newInstance(system.today().year(), mnth, system.today().day(), 8, 0, 0);
				Date endDate = System.today().addDays(recurrenceInterval);

 

 

the addDays method will take care of your issue with the year and if your fall to close to the start of a new month.

 

So what if the recurrence ends on a weekend? do you want it to go to the next business day? Or is it ok to just fall on a weekend?

 

bohemianguy100bohemianguy100

Hi Mike,

 

Thanks for replying to my post.  I made some updates and have the year calculating correctly and I can get the first day of the month.  However, I still have two additional fixes I need to make to get the start and end dates correct.  As you pointed out, if the recurrence ends on a weekend, I want it to go to the next business day.  I can't figure that out??

 

Also, instead of setting the start date to begin on the first of the month, I need it to be on the first business day of the month.  So, if the first of the month started on Saturday or Sunday, I would want the event to start on Monday.

 

Here is my modified code: (I created a couple of helper methods to do some conversions, etc.)

 

private static void doCreateResourceEvents(List<ProductManagement__c> inNew, Map<Id, ProductManagement__c> inOld)
    {
    	RecordType rt = [Select Id From RecordType Where Name = 'Service Delivery Event' and sObjectType = 'Event'];
    	
    	List<Scheduling__c> schedulingNew = [Select Hours__c, Month_Index__c, Resource__r.Id, Resource__r.User_Id__c, ProductManagement__r.Id From Scheduling__c Where ProductManagement__c in: inNew];
		
		List<ProductManagement__c> PM = new List<ProductManagement__c>([Select Pricing__r.Opportunity__c From ProductManagement__c Where Id in: inNew]);
		Set<Id> opIds = new Set<Id>();
		for (ProductManagement__c cs: PM) {
			opIds.add(cs.Pricing__r.Opportunity__c);
		}
		
		List<Project_Request__c> PR = new List<Project_Request__c>([Select Id From Project_Request__c Where Opportunity__c in: opIds]);
				
		List<Event> evs = new List<Event>();
	
		for (Scheduling__c s : schedulingNew) {
						
			if (s.Hours__c != null && s.Resource__r.User_Id__c != null 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res1__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res2__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res3__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res4__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res5__c) {
					
				Integer recurrenceInterval = math.round(s.Hours__c / 8);
				Integer hours = math.round(s.Hours__c);
				Integer remainder = math.mod(hours,8);
				if (remainder > 0) {
					recurrenceInterval = recurrenceInterval + 1;
				}
				Integer mnth = doConvertMonthIndex(s.Month_Index__c);
				Integer year = doGetYearFromMonth(mnth);
				
				Datetime startDate = Datetime.newInstance(year, mnth, system.today().day(), 8, 0, 0);
				Date firstDayOfMonth = doConvertDateTimeToDate(startDate);
				firstDayOfMonth = firstDayOfMonth.toStartOfMonth();
				Date endDate = doConvertDateTimeToDate(startDate);
				endDate = endDate.addDays(recurrenceInterval);
				endDate = endDate.addDays(-1);

				Event e = new Event();
				e.IsRecurrence = true;
				e.RecurrenceDayOfWeekMask = 62;
				e.RecurrenceType = 'RecursEveryWeekday';
				e.OwnerId = s.Resource__r.User_Id__c;
				e.RecordTypeId = rt.Id;
				e.Subject = 'Other';
				e.RecurrenceStartDateTime = startDate;
				e.RecurrenceEndDateOnly = endDate;
				e.DurationInMinutes = 60;
				evs.add(e);
				
			}
			
		}
    	//insert evs;
    }	
	
	private static integer doConvertMonthIndex(Double dbl) {
		
		Integer retval;
		
		if (dbl == 1.0) { retval = 12; }
		if (dbl == 2.0) { retval = 1; }
		if (dbl == 3.0) { retval = 2; }
		if (dbl == 4.0) { retval = 3; }
		if (dbl == 5.0) { retval = 4; }
		if (dbl == 6.0) { retval = 5; }
		if (dbl == 7.0) { retval = 6; }
		if (dbl == 8.0) { retval = 7; }
		if (dbl == 9.0) { retval = 8; }
		if (dbl == 10.0) { retval = 9; }
		if (dbl == 11.0) { retval = 10; }
		if (dbl == 12.0) { retval = 11; }
		
		return retval;
	}
	
	private static integer doGetYearFromMonth(Integer m) {
		
		Integer retval;
		
		Integer currentMonth = system.today().month();
		
		if (m >= currentMonth) {
			retval = system.today().year();
		}
		else {
			Date nextYear = system.today().addYears(1);
			retval = nextYear.year();
		}
		
		return retval;
	}
	
	private static date doConvertDateTimeToDate(Datetime dt) {

		Integer year = dt.year();
		Integer month = dt.month();
		Integer day = dt.day();
		Date retval = Date.newInstance(year, month, day);
		return retval;
	}

 

Any ideas on how I can make the recurring event that ends on a weekend, go to the next business day?  And, how to set the first occurrence of an event to start on the first business day of the month, unless of course it is the current month, then it should just start on that day.

 

Thanks for the help.  I appreciate it.

mikefmikef

So you can do this to get the day of the week.

 

 

 public static Integer skipToNextBusinessDay(DateTime pDate){
   Integer returnInt = 0;
   Long dayOfTheWeek = Math.mod(pDate.getTime(),7);
   
   if(dayOfTheWeek == 0){
      returnInt = 1;
   }
   if(dayOfTheWeek == 6){
      returnInt = 2;
   }

   return returnInt;
}

 and if you want to pass in the date then add a bit of logic to create a datetime with the same date just 00:00:00.0 timel

 

 

bohemianguy100bohemianguy100

Hi Mike,

 

The formula to calculate the day of the week seems to be off.

 

I've been running a system debug and the day of the week seems to change by seconds and minutes.  Everytime I re-run it, the value changes?  Here is the method I'm using that I copied from your example:

 

private static Integer skipToNextBusinessDay(Datetime dt){
		
   		Integer retval = 0;
   		
   		Long dayOfTheWeek = Math.mod(dt.getTime(),7);

   		if(dayOfTheWeek == 0){
      		retval = 1;
   		}
   		if(dayOfTheWeek == 6){
      		retval = 2;
   		}
   		return retval;
	}

 

Any ideas of what might be wrong with this?

Thanks.

mikefmikef

Sorry you are correct there is a bug with this method.

 

 

    public static Integer skipToNextBusinessDay(DateTime pDate){
	   Datetime tmpDateTime = Datetime.newInstance(1985,6,24);
	   Integer returnInt = 0;
	   Long dayOfTheWeek = Math.mod(pDate.getTime() - tmpDateTime.getTime(),7);
	   
	   if(dayOfTheWeek == 0){
	      returnInt = 1;
	   }
	   if(dayOfTheWeek == 6){
	      returnInt = 2;
	   }

       return returnInt;
    }

 It was late at night when I sent it. This new method should work just fine.

 

bohemianguy100bohemianguy100

Thanks Mike,

 

I'm running into one small glitch.  When the events get created, I'm missing how to add the missed days for the weekends.  For example, if January has 80 hours, that would mean 10 event records should be created.  What is happening is that 8 event records are created because it passes over Saturday and Sunday, but those days are still counted in the recurrence.  I'm probably just missing something simple in my code.

 

Here is the trigger method with all associated helper methods:

 

private static void doCreateResourceEvents(List<ProductManagement__c> inNew, Map<Id, ProductManagement__c> inOld)
    {  	

		RecordType rt = [Select Id From RecordType Where Name = 'Service Delivery Event' and sObjectType = 'Event'];
    	
    	List<Scheduling__c> schedulingNew = [Select Hours__c, Month_Index__c, Resource__r.Id, Resource__r.User_Id__c, ProductManagement__r.Id From Scheduling__c Where ProductManagement__c in: inNew];
		
		List<ProductManagement__c> PM = new List<ProductManagement__c>([Select Pricing__r.Opportunity__c From ProductManagement__c Where Id in: inNew]);
		Set<Id> opIds = new Set<Id>();
		for (ProductManagement__c cs: PM) {
			opIds.add(cs.Pricing__r.Opportunity__c);
		}
		
		List<Project_Request__c> PR = new List<Project_Request__c>([Select Id From Project_Request__c Where Opportunity__c in: opIds]);
		
		List<Event> evs = new List<Event>();
	
		for (Scheduling__c s : schedulingNew) {
			
			if (s.Hours__c != null && s.Resource__r.User_Id__c != null 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res1__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res2__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res3__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res4__c 
				&& s.Resource__r.Id != inOld.get(s.ProductManagement__r.Id).Res5__c) {
					
				Integer recurrenceInterval = math.round((s.Hours__c / 8)-1);

				Integer hours = math.round(s.Hours__c);

				Integer remainder = math.mod(hours,8);

				if (remainder > 0) {
					recurrenceInterval = recurrenceInterval + 1;
				}

				Integer mnth = doConvertMonthIndex(s.Month_Index__c);
				
				Integer year = doGetYearFromMonth(mnth);
				
				Integer currentMonth = system.today().month();
				
				Datetime startDate = Datetime.newInstance(year, mnth, system.today().day(), 8, 0, 0);
				
				Date firstDayOfMonth = doConvertDateTimeToDate(startDate);
				
				if (currentMonth > mnth) {
					firstDayOfMonth = firstDayOfMonth.toStartOfMonth();
				}
				
				Datetime firstWorkDayOfMonth = doConvertDateToDateTime(firstDayOfMonth, 8, 0, 0);
				
				Integer intStart = skipToNextBusinessDay(firstWorkDayOfMonth);
				
				if (intStart == 1) {
					firstWorkDayOfMonth = firstWorkDayOfMonth.addDays(1);
				}
				else if (intStart == 2) {
					firstWorkDayOfMonth = firstWorkDayOfMonth.addDays(2);
				}
				

				Date endDate = doConvertDateTimeToDate(firstWorkDayOfMonth);
				endDate = endDate.addDays(recurrenceInterval);
				
				Integer intEnd = skipToNextBusinessDay(endDate);

				if (intEnd == 1) {
					endDate = endDate.addDays(1);
				}
				else if (intEnd == 2) {
					endDate = endDate.addDays(2);
				}

				Event e = new Event();
				e.IsRecurrence = true;
				e.RecurrenceDayOfWeekMask = 62;
				e.RecurrenceType = 'RecursEveryWeekday';
				e.OwnerId = s.Resource__r.User_Id__c;
				e.RecordTypeId = rt.Id;
				e.Subject = 'Other';
				e.RecurrenceStartDateTime = firstWorkDayOfMonth;
				e.RecurrenceEndDateOnly = endDate;
				e.DurationInMinutes = 60;
				evs.add(e);
				
			}
			
		}
    	insert evs;
    }	
	
	private static Integer doConvertMonthIndex(Double dbl) {
		
		Integer retval = 0;
		
		if (dbl == 1.0) { retval = 12; }
		if (dbl == 2.0) { retval = 1; }
		if (dbl == 3.0) { retval = 2; }
		if (dbl == 4.0) { retval = 3; }
		if (dbl == 5.0) { retval = 4; }
		if (dbl == 6.0) { retval = 5; }
		if (dbl == 7.0) { retval = 6; }
		if (dbl == 8.0) { retval = 7; }
		if (dbl == 9.0) { retval = 8; }
		if (dbl == 10.0) { retval = 9; }
		if (dbl == 11.0) { retval = 10; }
		if (dbl == 12.0) { retval = 11; }
		
		return retval;
	}
	
	private static Integer doWeekDayConversion(Datetime dt) {
		
		Integer retval;
		String weekday = dt.format('EEEE'); 
		
		if (weekday == 'Sunday') { retval = 0; }
		if (weekday == 'Monday') { retval = 1; }
		if (weekday == 'Tuesday') { retval = 2; }
		if (weekday == 'Wenesday') { retval = 3; }
		if (weekday == 'Thursday') { retval = 4; }
		if (weekday == 'Friday') { retval = 5; }
		if (weekday == 'Saturday') { retval = 6; }
		
		return retval;
	}
	
	private static Integer doGetYearFromMonth(Integer m) {
		
		Integer retval = 0;
		
		Integer currentMonth = system.today().month();
		
		if (m >= currentMonth) {
			retval = system.today().year();
		}
		else {
			Date nextYear = system.today().addYears(1);
			retval = nextYear.year();
		}
		
		return retval;
	}
	
	private static Date doConvertDateTimeToDate(Datetime dt) {

		Integer year = dt.year();
		Integer month = dt.month();
		Integer day = dt.day();
		Date retval = Date.newInstance(year, month, day);
		return retval;
	}
	
	private static Datetime doConvertDateToDateTime(Date dt, Integer hh, Integer mm, Integer ss) {
		
		Integer year = dt.year();
		Integer month = dt.month();
		Integer day = dt.day();
		Datetime retval = Datetime.newInstance(year, month, day, hh, mm, ss);
		return retval;
	}
	
	private static Integer skipToNextBusinessDay(Datetime dt){
		
   		Integer retval = 0;
   		
   		Integer dayOfTheWeek = doWeekDayConversion(dt);
		
   		if(dayOfTheWeek == 0){
      		retval = 1;
   		}
   		if(dayOfTheWeek == 6){
      		retval = 2;
   		}
   		return retval;
	}

 

 

Do you see what I'm missing?

mikefmikef

I am not sure what you are missing but the method that I provided to you is set up to add a 0, or a 1 or a 2 to the interval.

So if the end date is on a Saturday then my method will return a 2. You would add that 2 to the addDays() calculation.

bohemianguy100bohemianguy100

Mike,

 

Do you have an example of setting the event field values by chance using your skipToNextBusinessDay method?  I'm using your method and it is creating the correct number of events, but it is creating them over the weekend.  My knee jerk reaction is that I'm improperly setting the field values somehow.

mikefmikef

 

endDate = endDate.addDays(recurrenceInterval + skipToNextBusinessDay(endDate));

 

 

This was selected as the best answer