3 * This file is used to create all tables used by WebCalendar and
4 * initialize some of those tables with the required data.
6 * The comments in the table definitions will be parsed to
7 * generate a document (in HTML) that describes these tables.
10 * 21-Oct-2002 Added this file header and additional comments
15 * Defines a WebCalendar user.
17 CREATE TABLE webcal_user (
18 /* the unique user login */
19 cal_login VARCHAR(25) NOT NULL,
20 /* the user's password. (not used for http or ldap authentication) */
21 cal_passwd VARCHAR(32),
22 /* user's last name */
23 cal_lastname VARCHAR(25),
24 /* user's first name */
25 cal_firstname VARCHAR(25),
26 /* is the user a WebCalendar administrator ('Y' = yes, 'N' = no) */
27 cal_is_admin CHAR(1) DEFAULT 'N',
28 /* user's email address */
29 cal_email VARCHAR(75) NULL,
30 PRIMARY KEY ( cal_login )
33 # create a default admin user
34 INSERT INTO webcal_user ( cal_login, cal_passwd, cal_lastname, cal_firstname, cal_is_admin ) VALUES ( 'admin', '21232f297a57a5a743894a0e4a801fc3', 'Administrator', 'Default', 'Y' );
37 * Defines a calendar event. Each event in the system has one entry
38 * in this table unless the event starts before midnight and ends
39 * after midnight. In that case a secondary event will be created with
40 * cal_ext_for_id set to the cal_id of the original entry.
41 * The following tables contain additional information about each
43 * <li><a href="#webcal_entry_user">webcal_entry_user</a> -
44 * lists participants in the event and specifies the status (accepted,
45 * rejected) and category of each participant.</li>
46 * <li><a href="#webcal_entry_repeats">webcal_entry_repeats</a> -
47 * contains information if the event repeats.</li>
48 * <li><a href="#webcal_entry_repeats_not">webcal_entry_repeats_not</a> -
49 * specifies which dates the repeating event does not repeat (because
50 * they were deleted or modified for just that date by the user)</li>
51 * <li><a href="#webcal_entry_log">webcal_entry_log</a> -
52 * provides a history of changes to this event.</li>
53 * <li><a href="#webcal_site_extras">webcal_site_extras</a> -
54 * stores event data as defined in site_extras.php (such as reminders and
55 * other custom event fields).</li>
58 CREATE TABLE webcal_entry (
59 /* cal_id is unique integer id for event */
61 /* cal_group_id: the parent event id if this event is overriding an */
62 /* occurrence of a repeating event */
63 cal_group_id INT NULL,
64 /* used when an event goes past midnight into the */
65 /* next day, in which case an additional entry in this table */
66 /* will use this field to indicate the original event cal_id */
67 cal_ext_for_id INT NULL,
68 /* user login of user that created the event */
69 cal_create_by VARCHAR(25) NOT NULL,
70 /* date of event (in YYYYMMDD format) */
71 cal_date INT NOT NULL,
72 /* event time (in HHMMSS format) */
74 /* date the event was last modified (in YYYYMMDD format) */
76 /* time the event was last modified (in HHMMSS format) */
78 /* duration of event in minutes */
79 cal_duration INT NOT NULL,
80 /* event priority: 1=Low, 2=Med, 3=High */
81 cal_priority INT DEFAULT 2,
82 /* 'E' = Event, 'M' = Repeating event */
83 cal_type CHAR(1) DEFAULT 'E',
85 /* 'R' = Confidential (others can see time allocated but not what it is) */
86 cal_access CHAR(1) DEFAULT 'P',
87 /* brief description of event */
88 cal_name VARCHAR(80) NOT NULL,
89 /* full description of event */
91 PRIMARY KEY ( cal_id )
95 * Defines repeating info about an event.
96 * The event is defined in <a href="#webcal_entry">webcal_entry</a>.
98 CREATE TABLE webcal_entry_repeats (
100 cal_id INT DEFAULT 0 NOT NULL,
101 /* type of repeating:<ul> */
102 /* <li>daily - repeats daily</li> */
103 /* <li>monthlyByDate - repeats on same day of the month</li> */
104 /* <li>monthlyByDayR - repeats on same weekday of the month */
105 /* (counting weeks from the end of the month is in last Monday)</li> */
106 /* <li>monthlyByDay - repeats on specified weekday (2nd Monday, for example)</li> */
107 /* <li>weekly - repeats every week</li> */
108 /* <li>yearly - repeats on same date every year</li> */
109 cal_type VARCHAR(20),
110 /* end date for repeating event (in YYYYMMDD format) */
112 /* frequency of repeat: 1 = every, 2 = every other, 3 = every 3rd, etc. */
113 cal_frequency INT DEFAULT 1,
114 /* which days of the week does it repeat on (only applies when cal_type = 'weekly' */
120 * This table specifies which dates in a repeating
121 * event have either been deleted or replaced with
122 * a replacement event for that day. When replaced, the cal_group_id
123 * (I know... not the best name, but it was not being used) column will
124 * be set to the original event. That way the user can delete the original
125 * event and (at the same time) delete any exception events.
127 CREATE TABLE webcal_entry_repeats_not (
128 /* event id of repeating event */
130 /* cal_date: date event should not repeat (in YYYYMMDD format) */
131 cal_date INT NOT NULL,
132 PRIMARY KEY ( cal_id, cal_date )
136 * This table associates one or more users with an event by the event id.
137 * The event can be found in
138 * <a href="#webcal_entry">webcal_entry</a>.
140 CREATE TABLE webcal_entry_user (
142 cal_id INT DEFAULT 0 NOT NULL,
143 /* participant in the event */
144 cal_login VARCHAR(25) NOT NULL,
145 /* status of event for this user: <ul> */
146 /* <li>A=Accepted</li> */
147 /* <li>R=Rejected</li> */
148 /* <li>W=Waiting</li> </ul>*/
149 cal_status CHAR(1) DEFAULT 'A',
150 /* category of the event for this user */
151 cal_category INT DEFAULT NULL,
152 PRIMARY KEY ( cal_id, cal_login )
156 * This table associates one or more external users (people who do not
157 * have a WebCalendar login) with an event by the event id.
158 * An event must still have at least one WebCalendar user associated
159 * with it. This table is not used unless external users are enabled
160 * in system settings.
161 * The event can be found in
162 * <a href="#webcal_entry">webcal_entry</a>.
164 CREATE TABLE webcal_entry_ext_user (
166 cal_id INT DEFAULT 0 NOT NULL,
167 /* external user fill name */
168 cal_fullname VARCHAR(50) NOT NULL,
169 /* external user email (for sending a reminder) */
170 cal_email VARCHAR(75) NULL,
171 PRIMARY KEY ( cal_id, cal_fullname )
175 * Specify preferences for a user.
176 * Most preferences are set via pref.php.
177 * Values in this table are loaded after system settings
178 * found in <a href="#webcal_config">webcal_config</a>.
180 CREATE TABLE webcal_user_pref (
182 cal_login VARCHAR(25) NOT NULL,
184 cal_setting VARCHAR(25) NOT NULL,
186 cal_value VARCHAR(100) NULL,
187 PRIMARY KEY ( cal_login, cal_setting )
191 * Define layers for a user.
193 CREATE TABLE webcal_user_layers (
194 /* unique layer id */
195 cal_layerid INT DEFAULT 0 NOT NULL,
196 /* login of owner of this layer */
197 cal_login VARCHAR(25) NOT NULL,
198 /* login name of user that this layer represents */
199 cal_layeruser VARCHAR(25) NOT NULL,
200 /* color to display this layer in */
201 cal_color VARCHAR(25) NULL,
202 /* show duplicates ('N' or 'Y') */
203 cal_dups CHAR(1) DEFAULT 'N',
204 PRIMARY KEY ( cal_login, cal_layeruser )
208 * This table holds data for site extra fields
209 * (customized in site_extra.php).
211 CREATE TABLE webcal_site_extras (
213 cal_id INT DEFAULT 0 NOT NULL,
214 /* the brief name of this type (first field in $site_extra array) */
215 cal_name VARCHAR(25) NOT NULL,
216 /* $EXTRA_URL, $EXTRA_DATE, etc. */
217 cal_type INT NOT NULL,
218 /* only used for $EXTRA_DATE type fields (in YYYYMMDD format) */
219 cal_date INT DEFAULT 0,
220 /* how many minutes before event should a reminder be sent */
221 cal_remind INT DEFAULT 0,
222 /* used to store text data */
224 PRIMARY KEY ( cal_id, cal_name, cal_type )
228 * This table keeps a history of when reminders get sent.
230 CREATE TABLE webcal_reminder_log (
232 cal_id INT DEFAULT 0 NOT NULL,
233 /* extra type (see site_extras.php) */
234 cal_name VARCHAR(25) NOT NULL,
235 /* the event date we are sending reminder for (in YYYYMMDD format) */
236 cal_event_date INT NOT NULL DEFAULT 0,
237 /* the date/time we last sent a reminder (in UNIX time format) */
238 cal_last_sent INT NOT NULL DEFAULT 0,
239 PRIMARY KEY ( cal_id, cal_name, cal_event_date )
243 * Define a group. Group members can be found in
244 * <a href="#webcal_group_user">webcal_group_user</a>.
246 CREATE TABLE webcal_group (
247 /* unique group id */
248 cal_group_id INT NOT NULL,
249 /* user login of user that created this group */
250 cal_owner VARCHAR(25) NULL,
251 /* name of the group */
252 cal_name VARCHAR(50) NOT NULL,
253 /* date last updated (in YYYYMMDD format) */
254 cal_last_update INT NOT NULL,
255 PRIMARY KEY ( cal_group_id )
259 * Specify users in a group. The group is defined in
260 * <a href="#webcal_group">webcal_group</a>.
262 CREATE TABLE webcal_group_user (
264 cal_group_id INT NOT NULL,
266 cal_login VARCHAR(25) NOT NULL,
267 PRIMARY KEY ( cal_group_id, cal_login )
271 * A "view" allows a user to put the calendars of multiple users all on
272 * one page. A "view" is valid only for the owner (cal_owner) of the
273 * view. Users for the view are in
274 * <a href="#webcal_view_user">webcal_view_user</a>.
276 CREATE TABLE webcal_view (
278 cal_view_id INT NOT NULL,
279 /* login name of owner of this view */
280 cal_owner VARCHAR(25) NOT NULL,
282 cal_name VARCHAR(50) NOT NULL,
283 /* "W" for week view, "D" for day view, "M" for month view */
284 cal_view_type CHAR(1),
285 /* is this a global view (can it be accessed by other users) ('Y' or 'N') */
286 cal_is_global CHAR(1) DEFAULT 'N' NOT NULL,
287 PRIMARY KEY ( cal_view_id )
291 * Specify users in a view. See <a href="#webcal_view">webcal_view</a>.
293 CREATE TABLE webcal_view_user (
295 cal_view_id INT NOT NULL,
296 /* a user in the view */
297 cal_login VARCHAR(25) NOT NULL,
298 PRIMARY KEY ( cal_view_id, cal_login )
302 * System settings (set by the admin interface in admin.php)
304 CREATE TABLE webcal_config (
306 cal_setting VARCHAR(50) NOT NULL,
308 cal_value VARCHAR(100) NULL,
309 PRIMARY KEY ( cal_setting )
313 INSERT INTO webcal_config ( cal_setting, cal_value )
314 VALUES ( 'application_name', 'WebCalendar' );
315 INSERT INTO webcal_config ( cal_setting, cal_value )
316 VALUES ( 'LANGUAGE', 'Browser-defined' );
317 INSERT INTO webcal_config ( cal_setting, cal_value )
318 VALUES ( 'demo_mode', 'N' );
319 INSERT INTO webcal_config ( cal_setting, cal_value )
320 VALUES ( 'require_approvals', 'Y' );
321 INSERT INTO webcal_config ( cal_setting, cal_value )
322 VALUES ( 'groups_enabled', 'N' );
323 INSERT INTO webcal_config ( cal_setting, cal_value )
324 VALUES ( 'user_sees_only_his_groups', 'N' );
325 INSERT INTO webcal_config ( cal_setting, cal_value )
326 VALUES ( 'categories_enabled', 'N' );
327 INSERT INTO webcal_config ( cal_setting, cal_value )
328 VALUES ( 'allow_conflicts', 'N' );
329 INSERT INTO webcal_config ( cal_setting, cal_value )
330 VALUES ( 'conflict_repeat_months', '6' );
331 INSERT INTO webcal_config ( cal_setting, cal_value )
332 VALUES ( 'disable_priority_field', 'N' );
333 INSERT INTO webcal_config ( cal_setting, cal_value )
334 VALUES ( 'disable_access_field', 'N' );
335 INSERT INTO webcal_config ( cal_setting, cal_value )
336 VALUES ( 'disable_participants_field', 'N' );
337 INSERT INTO webcal_config ( cal_setting, cal_value )
338 VALUES ( 'disable_repeating_field', 'N' );
339 INSERT INTO webcal_config ( cal_setting, cal_value )
340 VALUES ( 'allow_view_other', 'Y' );
341 INSERT INTO webcal_config ( cal_setting, cal_value )
342 VALUES ( 'email_fallback_from', 'youremailhere' );
343 INSERT INTO webcal_config ( cal_setting, cal_value )
344 VALUES ( 'remember_last_login', 'Y' );
345 INSERT INTO webcal_config ( cal_setting, cal_value )
346 VALUES ( 'allow_color_customization', 'Y' );
347 INSERT INTO webcal_config ( cal_setting, cal_value )
348 VALUES ('BGCOLOR','#FFFFFF');
349 INSERT INTO webcal_config ( cal_setting, cal_value )
350 VALUES ('TEXTCOLOR','#000000');
351 INSERT INTO webcal_config ( cal_setting, cal_value )
352 VALUES ('H2COLOR','#000000');
353 INSERT INTO webcal_config ( cal_setting, cal_value )
354 VALUES ('CELLBG','#C0C0C0');
355 INSERT INTO webcal_config ( cal_setting, cal_value )
356 VALUES ('WEEKENDBG','#D0D0D0');
357 INSERT INTO webcal_config ( cal_setting, cal_value )
358 VALUES ('TABLEBG','#000000');
359 INSERT INTO webcal_config ( cal_setting, cal_value )
360 VALUES ('THBG','#FFFFFF');
361 INSERT INTO webcal_config ( cal_setting, cal_value )
362 VALUES ('THFG','#000000');
363 INSERT INTO webcal_config ( cal_setting, cal_value )
364 VALUES ('POPUP_FG','#000000');
365 INSERT INTO webcal_config ( cal_setting, cal_value )
366 VALUES ('POPUP_BG','#FFFFFF');
367 INSERT INTO webcal_config ( cal_setting, cal_value )
368 VALUES ('TODAYCELLBG','#FFFF33');
369 INSERT INTO webcal_config ( cal_setting, cal_value )
370 VALUES ( 'STARTVIEW', 'week.php' );
371 INSERT INTO webcal_config ( cal_setting, cal_value )
372 VALUES ( 'WEEK_START', '0' );
373 INSERT INTO webcal_config ( cal_setting, cal_value )
374 VALUES ( 'TIME_FORMAT', '12' );
375 INSERT INTO webcal_config ( cal_setting, cal_value )
376 VALUES ( 'DISPLAY_UNAPPROVED', 'Y' );
377 INSERT INTO webcal_config ( cal_setting, cal_value )
378 VALUES ( 'DISPLAY_WEEKNUMBER', 'Y' );
379 INSERT INTO webcal_config ( cal_setting, cal_value )
380 VALUES ( 'WORK_DAY_START_HOUR', '8' );
381 INSERT INTO webcal_config ( cal_setting, cal_value )
382 VALUES ( 'WORK_DAY_END_HOUR', '17' );
383 INSERT INTO webcal_config ( cal_setting, cal_value )
384 VALUES ( 'send_email', 'N' );
385 INSERT INTO webcal_config ( cal_setting, cal_value )
386 VALUES ( 'EMAIL_REMINDER', 'Y' );
387 INSERT INTO webcal_config ( cal_setting, cal_value )
388 VALUES ( 'EMAIL_EVENT_ADDED', 'Y' );
389 INSERT INTO webcal_config ( cal_setting, cal_value )
390 VALUES ( 'EMAIL_EVENT_UPDATED', 'Y' );
391 INSERT INTO webcal_config ( cal_setting, cal_value )
392 VALUES ( 'EMAIL_EVENT_DELETED', 'Y' );
393 INSERT INTO webcal_config ( cal_setting, cal_value )
394 VALUES ( 'EMAIL_EVENT_REJECTED', 'Y' );
395 INSERT INTO webcal_config ( cal_setting, cal_value )
396 VALUES ('auto_refresh', 'N');
397 INSERT INTO webcal_config ( cal_setting, cal_value )
398 VALUES ('nonuser_enabled', 'N');
399 INSERT INTO webcal_config ( cal_setting, cal_value )
400 VALUES ('allow_html_description', 'N');
401 INSERT INTO webcal_config ( cal_setting, cal_value )
402 VALUES ('reports_enabled', 'N');
403 INSERT INTO webcal_config ( cal_setting, cal_value )
404 VALUES ('DISPLAY_WEEKENDS', 'Y');
405 INSERT INTO webcal_config ( cal_setting, cal_value )
406 VALUES ('DISPLAY_DESC_PRINT_DAY', 'N');
407 INSERT INTO webcal_config ( cal_setting, cal_value )
408 VALUES ('DATE_FORMAT', '__month__ __dd__, __yyyy__');
409 INSERT INTO webcal_config ( cal_setting, cal_value )
410 VALUES ('TIME_SLOTS', '12');
411 INSERT INTO webcal_config ( cal_setting, cal_value )
412 VALUES ('TIMED_EVT_LEN', 'D');
413 INSERT INTO webcal_config ( cal_setting, cal_value )
414 VALUES ('PUBLISH_ENABLED', 'N');
415 INSERT INTO webcal_config ( cal_setting, cal_value )
416 VALUES ('DATE_FORMAT_MY', '__month__ __yyyy__');
417 INSERT INTO webcal_config ( cal_setting, cal_value )
418 VALUES ('DATE_FORMAT_MD', '__month__ __dd__');
419 INSERT INTO webcal_config ( cal_setting, cal_value )
420 VALUES ('CUSTOM_SCRIPT', 'N');
421 INSERT INTO webcal_config ( cal_setting, cal_value )
422 VALUES ('CUSTOM_HEADER', 'N');
423 INSERT INTO webcal_config ( cal_setting, cal_value )
424 VALUES ('CUSTOM_TRAILER', 'N');
425 INSERT INTO webcal_config ( cal_setting, cal_value )
426 VALUES ('bold_days_in_year', 'Y');
427 INSERT INTO webcal_config ( cal_setting, cal_value )
428 VALUES ('site_extras_in_popup', 'N');
429 INSERT INTO webcal_config ( cal_setting, cal_value )
430 VALUES ('add_link_in_views', 'Y');
431 INSERT INTO webcal_config ( cal_setting, cal_value )
432 VALUES ('allow_conflict_override', 'Y');
433 INSERT INTO webcal_config ( cal_setting, cal_value )
434 VALUES ('limit_appts', 'N');
435 INSERT INTO webcal_config ( cal_setting, cal_value )
436 VALUES ('limit_appts_number', '6');
437 INSERT INTO webcal_config ( cal_setting, cal_value )
438 VALUES ('public_access', 'N');
439 INSERT INTO webcal_config ( cal_setting, cal_value )
440 VALUES ('public_access_default_visible', 'N');
441 INSERT INTO webcal_config ( cal_setting, cal_value )
442 VALUES ('public_access_default_selected', 'N');
443 INSERT INTO webcal_config ( cal_setting, cal_value )
444 VALUES ('public_access_others', 'N');
445 INSERT INTO webcal_config ( cal_setting, cal_value )
446 VALUES ('public_access_can_add', 'N');
447 INSERT INTO webcal_config ( cal_setting, cal_value )
448 VALUES ('public_access_add_needs_approval', 'Y');
449 INSERT INTO webcal_config ( cal_setting, cal_value )
450 VALUES ('public_access_view_part', 'N');
451 INSERT INTO webcal_config ( cal_setting, cal_value )
452 VALUES ('nonuser_at_top', 'Y');
453 INSERT INTO webcal_config ( cal_setting, cal_value )
454 VALUES ('allow_external_users', 'N');
455 INSERT INTO webcal_config ( cal_setting, cal_value )
456 VALUES ('external_notifications', 'N');
457 INSERT INTO webcal_config ( cal_setting, cal_value )
458 VALUES ('external_reminders', 'N');
459 INSERT INTO webcal_config ( cal_setting, cal_value )
460 VALUES ('enable_gradients', 'N');
464 * Activity log for an event.
466 CREATE TABLE webcal_entry_log (
467 /* unique id of this log entry */
468 cal_log_id INT NOT NULL,
470 cal_entry_id INT NOT NULL,
471 /* user who performed this action */
472 cal_login VARCHAR(25) NOT NULL,
473 /* user of calendar affected */
474 cal_user_cal VARCHAR(25) NULL,
475 /* log types: <ul> */
476 /* <li>C: Created</li> */
477 /* <li>A: Approved/Confirmed by user</li> */
478 /* <li>R: Rejected by user</li> */
479 /* <li>U: Updated by user</li> */
480 /* <li>M: Mail Notification sent</li> */
481 /* <li>E: Reminder sent</li> </ul>*/
482 cal_type CHAR(1) NOT NULL,
483 /* date in YYYYMMDD format */
484 cal_date INT NOT NULL,
485 /* time in HHMMSS format */
489 PRIMARY KEY ( cal_log_id )
493 * Defines user categories.
494 * Categories can be specific to a user or global. When a category is global,
495 * the cat_owner field will be NULL. (Only an admin user can create
496 * a global category.)
498 CREATE TABLE webcal_categories (
499 /* unique category id */
501 /* user login of category owner. */
502 /* If this is NULL, then it is a global category */
503 cat_owner VARCHAR(25) NULL,
505 cat_name VARCHAR(80) NOT NULL,
506 PRIMARY KEY ( cat_id )
510 * Define assitant/boss relationship.
512 CREATE TABLE webcal_asst (
513 /* user login of boss */
514 cal_boss VARCHAR(25) NOT NULL,
515 /* user login of assistant */
516 cal_assistant VARCHAR(25) NOT NULL,
517 PRIMARY KEY ( cal_boss, cal_assistant )
521 * Defines non-user calendars.
523 CREATE TABLE webcal_nonuser_cals (
524 /* the unique id for the calendar */
525 cal_login VARCHAR(25) NOT NULL,
526 /* calendar's last name */
527 cal_lastname VARCHAR(25) NULL,
528 /* calendar's first name */
529 cal_firstname VARCHAR(25) NULL,
530 /* who is the calendar administrator */
531 cal_admin VARCHAR(25) NOT NULL,
532 PRIMARY KEY ( cal_login )
536 * Used to track import data (one row per import)
538 CREATE TABLE webcal_import (
539 /* unique id for import */
540 cal_import_id INT NOT NULL,
541 /* name of import (optional) */
542 cal_name VARCHAR(50) NULL,
543 /* date of import (YYYYMMDD format) */
544 cal_date INT NOT NULL,
545 /* type of import (ical, vcal, palm) */
546 cal_type VARCHAR(10) NOT NULL,
547 /* user who performed the import */
548 cal_login VARCHAR(25) NULL,
549 PRIMARY KEY ( cal_import_id )
553 * Used to track import data (one row per event)
555 CREATE TABLE webcal_import_data (
556 /* import id (from webcal_import table) */
557 cal_import_id INT NOT NULL,
558 /* event id in WebCalendar */
561 cal_login VARCHAR(25) NOT NULL,
562 /* type of import: 'palm', 'vcal', 'ical' or 'publish' */
563 cal_import_type VARCHAR(15) NOT NULL,
564 /* external id used in external calendar system (for example, UID in iCal) */
565 cal_external_id VARCHAR(200) NULL,
566 PRIMARY KEY ( cal_id, cal_login )
571 * Defines a custom report created by a user.
573 CREATE TABLE webcal_report (
574 /* creator of report */
575 cal_login VARCHAR(25) NOT NULL,
576 /* unique id of this report */
577 cal_report_id INT NOT NULL,
578 /* is this a global report (can it be accessed by other users) ('Y' or 'N') */
579 cal_is_global CHAR(1) DEFAULT 'N' NOT NULL,
580 /* format of report (html, plain or csv) */
581 cal_report_type VARCHAR(20) NOT NULL,
582 /* if cal_report_type is 'html', should the default HTML header and */
583 /* trailer be included? ('Y' or 'N') */
584 cal_include_header CHAR(1) DEFAULT 'Y' NOT NULL,
585 /* name of the report */
586 cal_report_name VARCHAR(50) NOT NULL,
587 /* time range for report: <ul> */
588 /* <li>0 = tomorrow</li> */
589 /* <li>1 = today</li> */
590 /* <li>2 = yesterday</li> */
591 /* <li>3 = day before yesterday</li> */
592 /* <li>10 = next week</li> */
593 /* <li>11 = current week</li> */
594 /* <li>12 = last week</li> */
595 /* <li>13 = week before last</li> */
596 /* <li>20 = next week and week after</li> */
597 /* <li>21 = current week and next week</li> */
598 /* <li>22 = last week and this week</li> */
599 /* <li>23 = last two weeks</li> */
600 /* <li>30 = next month</li> */
601 /* <li>31 = current month</li> */
602 /* <li>32 = last month</li> */
603 /* <li>33 = month before last</li> */
604 /* <li>40 = next year</li> */
605 /* <li>41 = current year</li> */
606 /* <li>42 = last year</li> */
607 /* <li>43 = year before last</li> */
609 cal_time_range INT NOT NULL,
610 /* user calendar to display (NULL indicates current user) */
611 cal_user VARCHAR(25) NULL,
612 /* allow user to navigate to different dates with next/previous ('Y' or 'N') */
613 cal_allow_nav CHAR(1) DEFAULT 'Y',
614 /* category to filter on (optional) */
616 /* include empty dates in report ('Y' or 'N') */
617 cal_include_empty CHAR(1) DEFAULT 'N',
618 /* include a link for this report in the "Go to" section of the navigation */
619 /* in the page trailer ('Y' or 'N') */
620 cal_show_in_trailer CHAR(1) DEFAULT 'N',
621 /* date created or last updated (in YYYYMMDD format) */
622 cal_update_date INT NOT NULL,
623 PRIMARY KEY ( cal_report_id )
627 * Defines one of the templates used for a report.
628 * Each report has three templates:
630 * <li>Page template - Defines the entire page (except for header and
631 * footer). The following variables can be defined:
633 * <li>${days}<sup>*</sup> - the HTML of all dates (generated from the Date template)</li>
635 * <li>Date template - Defines events for one day. If the report
636 * is for a week or month, then the results of each day will be
637 * concatenated and used as the ${days} variable in the Page template.
638 * The following variables can be defined:
640 * <li>${events}<sup>*</sup> - the HTML of all events
641 * for the data (generated from the Event template)</li>
642 * <li>${date} - the date</li>
643 * <li>${fulldate} - date (includes weekday)</li>
645 * <li>Event template - Defines a single event.
646 * The following variables can be defined:
648 * <li>${name}<sup>*</sup> - Brief Description of event</li>
649 * <li>${description} - Full Description of event</li>
650 * <li>${date} - Date of event</li>
651 * <li>${fulldate} - Date of event (includes weekday)</li>
652 * <li>${time} - Time of event (4:00pm - 4:30pm)</li>
653 * <li>${starttime} - Start time of event</li>
654 * <li>${endtime} - End time of event</li>
655 * <li>${duration} - Duration of event (in minutes)</li>
656 * <li>${priority} - Priority of event</li>
657 * <li>${href} - URL to view event details</li>
660 * <sup>*</sup> denotes a required template variable
662 CREATE TABLE webcal_report_template (
663 /* report id (in webcal_report table) */
664 cal_report_id INT NOT NULL,
665 /* type of template: <ul> */
666 /* <li>'P': page template represents entire document</li> */
667 /* <li>'D': date template represents a single day of events</li> */
668 /* <li>'E': event template represents a single event</li> */
670 cal_template_type CHAR(1) NOT NULL,
671 /* text of template */
672 cal_template_text TEXT,
673 PRIMARY KEY ( cal_report_id, cal_template_type )