Subject Area Name
|
Definition
|
140 - Workspace Calendar and Operation Schedule
|
The tables used to define the Workspace Calendar aka the Work Schedule. The work schedule is stored as an iCal availability item, and it can be shared across workspaces. These tables simplify the data for reporting.
|
|
ER Diagram Name
|
Physical Display Level
|
|
Logical
|
Column
|
|
Physical
|
Column
|
|
Physical Name
|
Entity/Table Name
|
Logical Only
|
Do Not Generate
|
Definition
|
|
|
agent
|
Agent
|
true
|
true
|
a user authorized to use the FootPrints agent functions, which enable the agent to provide services to the customer. These include the ability to edit, escalate, and close tickets.
|
|
|
Physical Name
|
Attribute/Column Name
|
Definition
|
Physical Data Type
|
|
|
lic_type_id
|
License Type Id
|
A Foreign Key to the License Type table.
|
bigint
|
|
|
app_user_id
|
Application User Id
|
A Foreign Key to the Application User table.
|
bigint
|
|
cal_item
|
Calendar Item
|
|
false
|
The main calendar item table for the calender service. This table is populated and maintained by the Calendar Service API. It is not very suitable for database query outside of the API. The calendar item can be a personal or workspace calendar, or an appointment within the calendar. The calendar item can be synched using the iCal format.
|
|
|
Physical Name
|
Attribute/Column Name
|
Definition
|
Physical Data Type
|
|
|
item_type
|
item type
|
item type.
|
varchar(16)
|
|
|
cal_item_id
|
Calendar Item Id
|
The automatically generated primary key of the cal_item table.
|
bigint
|
|
|
create_date
|
create date
|
create date, stored as a long integer to avoid using date time classes that do not handle timezone well.
|
int
|
|
|
etag
|
etag
|
etag.
|
varchar(255)
|
|
|
mdfy_date
|
modify date
|
modify date, stored as a long integer to avoid using date time classes that do not handle timezone well.
|
int
|
|
|
client_create_date
|
client create date
|
client create date, stored as a long integer to avoid using date time classes that do not handle timezone well.
|
int
|
|
|
client_mod_date
|
client modified date
|
client modified date, stored as a long integer to avoid using date time classes that do not handle timezone well.
|
int
|
|
|
display_name
|
display name
|
display name.
|
nvarchar(4000)
|
|
|
item_name
|
item name
|
item name.
|
nvarchar(4000)
|
|
|
intrnl_id
|
Internal Id
|
Internal Id.
|
varchar(255)
|
|
|
last_modf
|
last modification
|
The value of a Calendar Item, stored as a date.
|
int
|
|
|
last_mod_by
|
last modified by
|
last modified by.
|
nvarchar(4000)
|
|
|
needs_reply
|
needs reply
|
Indicates if needs reply.
|
bit
|
|
|
sent
|
sent
|
Indicates if sent.
|
bit
|
|
|
ical_uid
|
ical uid
|
ical uid.
|
varchar(255)
|
|
|
content_encoding
|
content encoding
|
content encoding.
|
varchar(32)
|
|
|
content_language
|
content language
|
content language.
|
varchar(32)
|
|
|
content_length
|
content length
|
The value of a Calendar Item stored as Long.
|
bigint
|
|
|
content_type
|
content type
|
content type.
|
varchar(64)
|
|
|
has_modfs
|
has modifications
|
Indicates if has modifications.
|
bit
|
|
|
cal_content_data_id
|
Calendar Content Data Id
|
A Foreign Key to the Calendar Content Data table.
|
bigint
|
|
|
app_user_id
|
Application User Id
|
A Foreign Key to the Application User table.
|
bigint
|
|
|
modifies_cal_item_id
|
Modifies Calendar Item Id
|
A Foreign Key to the Calendar Item table.
|
bigint
|
|
|
delete_date
|
Delete Date
|
The value of a Calendar Item, stored as a date.
|
datetime
|
|
cal_schd
|
Calendar Schedule
|
false
|
false
|
The association between the FootPrints calendar instance, and the implementation of the calendar schedule and appointments. This enables the reuse of a schedule between workspaces, but it does not require it.
|
|
|
Physical Name
|
Attribute/Column Name
|
Definition
|
Physical Data Type
|
|
|
schd_id
|
Schedule Id
|
A Foreign Key to the Schedule table.
|
bigint
|
|
|
cal_schd_id
|
Calendar Schedule Id
|
The automatically generated primary key of the cal_schd table.
|
bigint
|
|
|
cal_item_id
|
Calendar Item Id
|
A Foreign Key to the Calendar Item table.
|
bigint
|
|
cal_type
|
Calendar Type
|
|
false
|
The type of Calendar, Workspace or Personal
|
|
|
Physical Name
|
Attribute/Column Name
|
Definition
|
Physical Data Type
|
|
|
cal_type_name
|
Calendar Type Name
|
The name of the Calendar Type.
|
varchar(255)
|
|
|
cal_type_id
|
Calendar Type Id
|
The automatically generated primary key of the cal_type table.
|
int
|
|
schd
|
Schedule
|
false
|
false
|
A personal or workspace calendar in Footprints. It is used to provide easy access to work hours and settings for appointment scheduling and metric reporting. The Calendar Service maintains the system of record tables for the schedule, but those tables are too difficult and time consuming to query for trend reporting.
|
|
|
Physical Name
|
Attribute/Column Name
|
Definition
|
Physical Data Type
|
|
|
schd_id
|
Schedule Id
|
The automatically generated primary key of the schd table.
|
bigint
|
|
|
schd_name
|
Schedule Name
|
The name of the Schedule.
|
varchar(255)
|
|
|
max_date_val
|
Maximum Date Value
|
The time spans are forecast out into the future as CalendarActualPeriod. This is the maximum date for which those periods are generated.
|
datetime
|
|
|
time_zone_region_name
|
Time Zone Region Name
|
The name of the Time Zone Region for the calendar, which derives from or overrides the value used for the system or workspace.
|
varchar(255)
|
|
|
cal_type_id
|
Calendar Type Id
|
A Foreign Key to the Calendar Type table.
|
int
|
|
|
app_user_id
|
Application User Id
|
A Foreign Key to the Application User table.
|
bigint
|
|
|
use_local_time
|
Use Local Time
|
Indicates that the workschedule is base on local time for the time zone region. If you want UTC time, use that as the region.
|
bit
|
|
schd_actual_period
|
Schedule Actual Period
|
|
false
|
Given the definitions of the standard work periods and holidays, these are the work periods. If overlapping shift work periods are defined, the time ranges here represent all shifts. This makes it easier to do math against working hours. The data in this table is time zone accurate for the schedule in the time zone of the workspace. Each minute within the begin and end date of the actual period must be a work minute. The master copy of the schedule is an iCal availability item stored in the Calendar service Calendar Item tables via the API. The data is redundantly and more conveniently stored so that the dashboard can use the information in the calculation of work hours elapsed.
|
|
|
Physical Name
|
Attribute/Column Name
|
Definition
|
Physical Data Type
|
|
|
schd_actual_period_id
|
Schedule Actual Period Id
|
The automatically generated primary key of the schd_actual_period table.
|
bigint
|
|
|
begin_date
|
Begin Date
|
Begin Date.
|
datetime
|
|
|
end_date
|
End Date
|
End Date.
|
datetime
|
|
|
work_durn_in_mins
|
Work Duration In Minutes
|
Work Time duration in minutes. This is the number of work minutes within the begin and end date of the period, which is equal to the end date minus the begin date, because the work periods can be sparsely populated. This value can be summed to get the total work minutes for a date range. If a date marker requested falls within the begin and end date of the actual period, then the minutes from the marker date and the begin/end point must be subtracted from this number.
|
int
|
|
|
schd_id
|
Schedule Id
|
A Foreign Key to the Schedule table.
|
bigint
|
|
|
cum_work_durn_in_mins
|
Cumulative Work Duration In Minutes
|
Cumulative Work Time duration in minutes. This is the number of work minutes from the actual first period to the end of the current actual period as calculated by a running total or cumulative sum of the Work Duration field. Because the work periods will be sparsely populated, the hard dates cannot be subtracted to yield Work Duration Difference. However, this Cumulative Work Duration can be subtracted to get the Work Duration difference. If a date marker requested falls within the begin and end date of the actual period, then the minutes from the marker date and the begin/end point must be subtracted from this number.
|
int
|
|
|
begin_date_utc_s
|
Begin Date UTC S
|
A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries.
|
varchar(23)
|
|
|
end_date_utc_s
|
End Date UTC S
|
A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries.
|
varchar(23)
|
|
time_zone_local_hour
|
Time Zone Local Hour
|
|
false
|
A bridge table that provides the UTC begin and End Hour datetimes for a local hour in an Olson timezone region. This table is used in Trend Reporting to give a high performance date calculation so that local hours and days can be used on graph axes and report heading with calculated totals. It is also used to load the summary tables using the local time dimension. High speed correct database-side time zone conversions are made by joining to this table.
|
|
|
Physical Name
|
Attribute/Column Name
|
Definition
|
Physical Data Type
|
|
|
begin_date_hour_utc
|
Begin Date Hour UTC
|
A date whole hour in the local timezone for the Begin Date Hour UTC. The Minutes portion of the date will always be 00. It is not stated whether daylight savings is in effect.
|
datetime
|
|
|
end_date_hour_utc
|
End Date Hour UTC
|
A date whole hour in the local timezone for the End Date Hour UTC. The Minutes portion of the date will always be 00. It is not stated whether daylight savings is in effect.
|
datetime
|
|
|
begin_date_hour_local
|
Begin Date Hour Local
|
A date time in UTC that cooresponds to a whole hour date in the local timezone for the Begin Date Hour Local. The Minutes portion of the date will usually be 00 but can be 30, 15, 45 in the case of half hour or quarter hour time zones. UTC never has daylight savings is in effect,
|
datetime
|
|
|
end_date_hour_local
|
End Date Hour Local
|
A date time in UTC that cooresponds to a whole hour date in the local timezone for the End Date Hour Local. The Minutes portion of the date will usually be 00 but can be 30, 15, 45 in the case of half hour or quarter hour time zones. UTC never has daylight savings is in effect,
|
datetime
|
|
|
begin_is_daylight_savings
|
Begin Is Daylight Savings
|
Flag indicating if daylight savings is in effect for the begin time. This helps identify the special hours that are missing or duplicated during daylight savings days.
|
bit
|
|
|
time_zone_region_id
|
Time Zone Region Id
|
An alternate Key to the Time Zone Region table, which is arbitrarily calculated and assigned by FootPrints. The reason for this is performance of the Foreign Key join to large numbers of rows. There is no suitable short data length key available in a published standard data source.
|
bigint
|
|
|
time_zone_local_hour_id
|
Time Zone Local Hour Id
|
The automatically generated primary key of the time_zone_local_hour table.
|
bigint
|
|
|
begin_date_hour_local_s
|
Begin Date Hour Local S
|
A string version of the local date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries.
|
varchar(23)
|
|
|
end_date_hour_local_s
|
End Date Hour Local S
|
A string version of the local date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries.
|
varchar(23)
|
|
|
begin_date_hour_utc_s
|
Begin Date Hour UTC S
|
A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries.
|
varchar(23)
|
|
|
end_date_hour_utc_s
|
End Date Hour UTC S
|
A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries.
|
varchar(23)
|
|
|
end_is_daylight_savings
|
End Is Daylight Savings
|
Flag indicating if daylight savings is in effect for the end time. This helps identify the special hours that are missing or duplicated during daylight savings days.
|
bit
|
|
time_zone_region
|
Time Zone Region
|
|
false
|
A region of the world defined in the Olson time zone database. Only the entries actually used by a FootPrints instance will be stored. The localized name of the region is provided by the Java.Util.Timezone getDisplayName() method. Only the java class can be used to determine the time zone offset from GMT, which varies historically and in the future. See the Time Zone Local Hour table. Take aspirin as needed.
|
|
|
Physical Name
|
Attribute/Column Name
|
Definition
|
Physical Data Type
|
|
|
time_zone_region_name
|
Time Zone Region Name
|
The name of the Time Zone Region. This is 2. The 'primary key' of the java timezone is the string region identifier in English, example 'America/Phoenix'. There is no numeric identifier. As the JRE is updated, or specific classes are updated, the list could change to include new entries, and the detail data behind the regions could change.
|
varchar(255)
|
|
|
time_zone_region_abbr
|
Time Zone Region Abbreviation
|
A well-known string identifier used as a code to identify the Time Zone Region. This could be provided by user entry, but it is not available from the Timezone class. It should probably not be used.
|
varchar(255)
|
|
|
time_zone_region_id
|
Time Zone Region Id
|
An alternate Key to the Time Zone Region table, which is arbitrarily calculated and assigned by FootPrints. The reason for this is performance of the Foreign Key join to large numbers of rows. There is no suitable short data length key available in a published standard data source.
|
bigint
|
|
wksp_cntnr
|
Workspace Container
|
true
|
true
|
A workspace is the principle architectural structure in Numara FootPrints for storing and tracking information. A Workspace is a separate sub-database or work area within the system with its own fields, forms, options, workflow, settings and users. Most of the behaviour of the Workspace comes from the Container Abstract class, and only things that are specific to the Workspace are implemented here. For Example, Current FootPrints allows a single CMDB to be referenced from a Workspace, and a single Address book, yet these are listed as features for improvement. So convenience methods that get and set Container Group Relationship might be appropriate.
|
|
|
Physical Name
|
Attribute/Column Name
|
Definition
|
Physical Data Type
|
|
|
time_zone_region_name
|
Time Zone Region Name
|
The name of the Time Zone Region. This is 2. The 'primary key' of the java timezone is the string region identifier in English, example 'America/Phoenix'. There is no numeric identifier. As the JRE is updated, or specific classes are updated, the list could change to include new entries, and the detail data behind the regions could change.
|
varchar(255)
|
|
|
cntnr_type_id
|
Container Type Id
|
A Foreign Key to the Container Type table.
|
int
|
|
|
cntnr_id
|
Container Id
|
A Foreign Key to the Container table.
|
bigint
|