NewCalendarTableCreator
For some data preparations and analyses it is relevant to have an IDEA calendar database. A calendar database is an IDEA database which contains all dates between two given date values.
- Instantiation:
Use MacroCommand function NewCalendarTableCreator to get a reference to the builder object.
ISS code:
Dim calendarTableCreator As Object
Set calendarTableCreator = SmartContext.MacroCommands.NewCalendarTableCreator
NewCalendarTableCreator returns an object that implements the CalendarTableCreator interface.
-
ICalendarTableCreator
The interface has the following properties and methods:
-
Property WeekNumbering As Long
Gets/sets the week numbering to be used.
The following values are defined:
'Default: In case the first day of the week is Monday and the first week of a year is the week
'containing the year's first Thursday the calendar week will be calculated as an
'ISO-8601 compliant calendar week value.
'In all other cases the rules of the current user's culture will be used.
const WN_Default As Long = 0
'Instructs the calendar creator to use the ISO-8601 week numbering
const WN_Iso8601 As Long = 1
Default property value = 0 (WN_Default)
-
Property UseShortDayNames As Boolean
Gets/sets a Boolean that specifies whether the column WEEKDAY will contain the short day names.
Default value: False (WEEKDAY contains the full day names)
-
Property IncludeZeroPeriod As Boolean
Gets/sets a Boolean that specifies whether the first row of the calendar database is the zero-period row corresponding to IDEA's zero-date.
Default value: False (the calendar does not include the zero-period row)
-
Property WeekendsOption As Long
Gets/sets a value that specifies whether to include the weekends in the result or not.
The following values are defined:
'The weekends will be included in the calendar.
'Note: This is the default value.
const WEO_Include As Long = 0
'The weekends on IDEA's weekend list will not be included in the calendar.
const WEO_ExcludeIdeaWeekends As Long = 1
'The weekends on the caller-supplied weekend list (calendar creator property WeekendsMask) will not be included in the calendar.
'A weekends mask is a bit pattern specifying the weekends (bit 0: Sunday, bit 1: Monday ... bit 6: Saturday)
const WEO_ExcludeCustomWeekends As Long = 2
Default value = 0 (WEO_Include)
-
Property WeekendsMask As Long
Gets/sets a bit pattern specifying the weekends (bit 0: Sunday, bit 1: Monday ... bit 6: Saturday)
This property is ignored unless WeekendsOption is set to WEO_ExcludeCustomWeekends (2).
-
Sub IncludeColumn(ByVal ColumnId As Long)
Instructs the creator to include the column with the specified ID in the calendar database (DATE will automatically be included).
Parameters:
columnId: A Long that contains the ID of the calendar column to be included.
By default, only the DATE column is included. This column will always be included by the calendar creator in the result.
Note: The order of calls does not influence the order of the columns in the result. The calendar uses a fixed order.
The following calendar column IDs are defined:
'The date column
'Note: Will always be included by the calendar creator in the result.
const CCI_Date As Long = 0
'The language-dependent short or full name of the week
const CCI_Weekday As Long = 1
'Ordinal number of the day in the year
const CCI_DayOfYear As Long = 2
'Calendar week formatted as yyyy-Www
const CCI_CalendarWeek As Long = 4
'Start date of the calendar week
const CCI_CalendarWeekStart As Long = 8
'End date of the calendar week
const CCI_CalendarWeekEnd As Long = 16
The following parameters of this sub are implemented for IDEA version 10.1
'Identifier for public holiday (1 = yes, 0 = no)
const CCI_IsPublicHoliday As Long = 32
'Name of public holiday
const CCI_Holiday As Long = 64
'Identifier of vacation (1 = yes, 0 = no)
const_IsSchoolVacation As Long = 128
'Name of public vacation
const CCI_Vacation As Long = 256
'Specifies whether the date value is on the custom list of days off (1 = yes, 0 = no)
const CCI_IsDayOff As Long = 512
-
Property StartDate As Date
Gets/sets the Gregorian calendar start date of the result table.
-
Property EndDate As Date
Gets/sets the Gregorian calendar end date of the result table.
-
Property TableName As String
Gets/sets a string containing the name of the result table.
-
Property GenerateUniqueTableName As Boolean
Gets/sets a Boolean that specifies whether the generator shall try to create a unique result table name starting from the suggested name.
Default value: False (the generator does not try to create a unique table name)
-
Function PerformTask As Object
Generates the calendar database and returns a reference to an object that holds the result of the execution.
PerformTask raises an error if TableName is empty or if it consists of only white spaces and GenerateUniqueTableName is False. It also raises an error if StartDate or EndDate is not in the valid range of the current user's default calendar.
The returned object implements the interface ICalendarTableCreatorResult.
The following properties as well as NewSpecialDayFilter and the interface SpecialDayFilter were implemented for IDEA version 10.1.
-
Property PublicHolidayFilter As Object
Gets/sets an optional object that implements the interface ISpecialDayFilter and that the creator uses to determine the value of the columns IS_PUBLIC_HOLIDAY and HOLIDAY.
Default value: Nothing (meaning that IS_PUBLIC_HOLIDAY is false and HOLIDAY is empty). A filter object can be created using the NewSpecialDayFilter macro command.
-
Property SchoolVacationFilter As Object
Gets/sets an optional object that implements the interface ISpecialDayFilter and that the creator uses to determine the value of the columns IS_SCHOOL_VACATION and VACATION.
Default value: Nothing (meaning that IS_SCHOOL_VACATION is false and VACATION is empty). A filter object can be created using the NewSpecialDayFilter macro command.
-
Property DayOffFilter As Object
Gets/sets an optional object that implements the interface ISpecialDayFilter and that the creator uses to determine the value of the column IS_DAY_OFF.
Default value: Nothing (meaning that IS_DAY_OFF is false. A filter object can be created using the NewSpecialDayFilter macro command.
-
-
NewSpecialDayFilter
A macro command was implemented that allows creating special day filters for the calendar database.
Function NewSpecialDayFilter (ByVal values As Variant) As Object
The method returns a reference to an object that implements the interface ISpecialDayFilter.
Values - a Variant that specifies the data or data provider for the filter object.
Accepted types:
- String arrays containing the IDEA string representation (format yyyymmdd) of the dates which must be valid Gregorian calendar dates.
- The content of single or from-to-date lists.
- Arrays of date values.
-
Arrays of arrays of min length 2, having at index 0 the IDEA string representation of a valid Gregorian calendar date and at index 1 the name of the special day. Note: This is the format to which a global parameter like ac.global.PublicHolidays_Hamburg can be converted by using the simple command StringList2Array .
-
SpecialDayFilter
The interface defines a special day (e.g. public holiday, vacation or day off) filter for the calendar database.
The interface has the following properties and methods:
-
Property SpecialDayNameMaxLength As Long
Gets the max length of a special day name in the filter.
Note: The value can be zero. Example: Anonymous special day filters like the day off filter.
-
Function IsSpecialDay(ByVal value As Date, ByRef specialDayName As String) As Boolean
Parameters:
Value - The date value to be checked
specialDayName - Receives the name, if available, of the special day.
Returns True if value is a special day, otherwise it returns False.
-
-
ICalendarTableCreatorResult
The interface has the following properties and methods:
-
Property Name As String
Gets the name of the calendar database.
-
Function GetColumnName(ByVal columnID As Long) As String
Gets the name of the calendar column with the specified ID at the time the database was created.
The method returns the name of the column with the specified ID, if the column was generated, otherwise an empty string.
For the list of available column IDs, please refer to 2f (method: IncludeColumn).
-
-
Notes on the location of column names and descriptions
The calendar creator uses the string table of the routine in context to retrieve the names and descriptions of the calendar columns. If the routine in context does not define a calendar column resource, its default value will be taken from the GlobalResources assembly.
The following calendar column resources are defined:
Calendar Column IDEA version Column Name Resource ID Column Description Resource ID DATE 10.0 ICT_N_DATE ICT_D_DATE WEEKDAY 10.0 ICT_N_WEEKDAY ICT_D_WEEKDAY DAY_OF_THE_YEAR 10.0 ICT_N_DAY_OF_THE_YEAR ICT_D_DAY_OF_THE_YEAR CALENDAR_WEEK 10.0 ICT_N_CALENDAR_WEEK ICT_D_CALENDAR_WEEK CALENDAR_WEEK_START 10.0 ICT_N_CALENDAR_WEEK_START ICT_D_CALENDAR_WEEK_START CALENDAR_WEEK_END 10.0 ICT_N_CALENDAR_WEEK_END ICT_D_CALENDAR_WEEK_END IS_PUBLIC_HOLIDAY 10.1 ICT_N_IS_PUBLIC_HOLIDAY ICT_D_IS_PUBLIC_HOLIDAY HOLIDAY 10.1 ICT_N_HOLIDAY ICT_D_HOLIDAY IS_SCHOOL_VACATION 10.1 ICT_N_IS_SCHOOL_VACATION ICT_D_IS_SCHOOL_VACATION VACATION 10.1 ICT_N_VACATION ICT_D_VACATION IS_DAY_OFF 10.1 ICT_N_IS_DAY_OFF ICT_D_IS_DAY_OFF