CRM 2011 - Set/Retrieve work hours programmatically
- by Philip Rich
I am attempting to retrieve a resources work hours to perform some logic I require. I understand that the CRM scheduling engine is a little clunky around such things, but I assumed that I would be able to find out how the working hours were stored in the DB eventually...
So a resource has associated calendars and those calendars have associated calendar rules and inner calendars etc. It is possible to look at the start/end and frequency of aforementioned calendar rules and query their codes to work out whether a resource is 'working' during a given period. However, I have not been able to find the actual working hours, the 9-5 shall we say in any field in the DB.
I even tried some SQL profiling while I was creating a new schedule for a resource via the UI, but the results don't show any work hours passing to SQL. For those with the patience the intercepted SQL statement is below:-
EXEC Sp_executesql
N'update [CalendarRuleBase] set [ModifiedBy]=@ModifiedBy0, [EffectiveIntervalEnd]=@EffectiveIntervalEnd0, [Description]=@Description0, [ModifiedOn]=@ModifiedOn0, [GroupDesignator]=@GroupDesignator0, [IsSelected]=@IsSelected0, [InnerCalendarId]=@InnerCalendarId0, [TimeZoneCode]=@TimeZoneCode0, [CalendarId]=@CalendarId0, [IsVaried]=@IsVaried0, [Rank]=@Rank0, [ModifiedOnBehalfBy]=NULL, [Duration]=@Duration0, [StartTime]=@StartTime0, [Pattern]=@Pattern0 where ([CalendarRuleId] = @CalendarRuleId0)',
N'@ModifiedBy0 uniqueidentifier,@EffectiveIntervalEnd0 datetime,@Description0 ntext,@ModifiedOn0 datetime,@GroupDesignator0 ntext,@IsSelected0 bit,@InnerCalendarId0 uniqueidentifier,@TimeZoneCode0 int,@CalendarId0 uniqueidentifier,@IsVaried0 bit,@Rank0 int,@Duration0 int,@StartTime0 datetime,@Pattern0 ntext,@CalendarRuleId0 uniqueidentifier',
@ModifiedBy0='EB04662A-5B38-E111-9889-00155D79A113',
@EffectiveIntervalEnd0='2012-01-13 00:00:00',
@Description0=N'Weekly Single Rule',
@ModifiedOn0='2012-03-12 16:02:08',
@GroupDesignator0=N'FC5769FC-4DE9-445d-8F4E-6E9869E60857',
@IsSelected0=1,
@InnerCalendarId0='3C806E79-7A49-4E8D-B97E-5ED26700EB14',
@TimeZoneCode0=85,
@CalendarId0='E48B1ABF-329F-425F-85DA-3FFCBB77F885',
@IsVaried0=0,
@Rank0=2,
@Duration0=1440,
@StartTime0='2000-01-01 00:00:00',
@Pattern0=N'FREQ=WEEKLY;INTERVAL=1;BYDAY=SU,MO,TU,WE,TH,FR,SA',
@CalendarRuleId0='0A00DFCF-7D0A-4EE3-91B3-DADFCC33781D'
The key parts in the statement are the setting of the pattern:-
@Pattern0=N'FREQ=WEEKLY;INTERVAL=1;BYDAY=SU,MO,TU,WE,TH,FR,SA'
However, as mentioned, no indication of the work hours set.
Am I thinking about this incorrectly or is CRM doing something interesting around these work hours?
Any thoughts greatly appreciated, thanks.
