The calendar and allows you to create a fully featured calendar from your data. myDBR utilizes FullCalendar v5 for this. Calendar data can be fetched dynamically via Ajax or directly within the main report.
dbr.calendar
- Create a calendar from a result set or dynamically via Ajaxdbr.calendar.report
- Define the Ajax report for calendar datadbr.calendar.options
- Set calendar options
select 'dbr.calendar', [max_width [, default_date]]
select 'dbr.calendar.report', 'sp_DBR_calendar_ajax' [, param1[, param2..]]
select 'dbr.calendar.options', option, value
The 'option' can be:
'event.style', 'css style'
- default CSS style for event (set in the main report).
'editable', editing_type , 'sp_DBR_editing_report', 'ColumnRef'[, 'ColumnRef'...]
- Set entries editable, possible editing types 'all/""' | 'start' | 'duration' (set in calendar data report when data is fetched via Ajax)
'new', 'new_buttons_class', number_of_datetime_parameters
- Connect day click to "New calendar entry"-button (set in the main report)
'ical', 1
- Allows user to download event's iCal-file
'selectable', 1
- Allows to create calendar events by clicking on dates or painting the days/hours. Is connected to "New calendar entry"-button (set in the main report).
'event.editable', ColumnRef
- Override the 'editing type'. Possible values from the columb 'all', 'start', 'duration' and ''. In 'all' both start and duration can be edited, if '' no editing is allowed (set in calendar data report when data is fetched via Ajax)
'event.id', ColumnRef
- Define events ID's column. Can be used with FullCalendar JavaScript API (set in calendar data report when data is fetched via Ajax)
'event.description', ColumnRef
- Define events description column. This is an additional field which will be included into iCal-file when available.
'event.location', ColumnRef
- Define events location column. This is an additional field which will be included into iCal-file when available.
'event.url', ColumnRef
- Define events url column. This is an additional field which will be included into iCal-file when available.
'description.hide', 1
- Determines if the calenadar shows the description. By default it does.
'location.hide', 0
- Determines if the calenadar shows the location. By default it does not.
'url.hide', 0
- Determines if the calenadar shows the URL. By default it does not.
'event.location', ColumnRef
- Define events location column. The location is an additional field which will be included into iCal-file when available.
'tooltip', ColumnRef
- Show data on tooltip
'height', pixels
- (FullCalendar) height of the Calendar in pixels (set in the main report).
'weekends', 1 | 0
- (FullCalendar) show / hide weekends (set in the main report).
'weekNumbers', 1 | 0
- (FullCalendar) show / hide weeknumbers (set in the main report).
'firstDay', 0-7
- (FullCalendar) Start of the week (the default is determined by the locale) (set in the main report).
'defaultView', viewname
- (FullCalendar) one from the available views. Defaults: month, week, day and listWeek
'slotDuration', duration
- (FullCalendar) The time steps in the calendar. Default "00:30:00" (30 minutes)
'eventDisplay', 'block' | 'auto' | 'list-item' | 'background' | 'inverse-background' | 'none'
- (FullCalendar) Controls which preset rendering style events use. myDBR defaults to 'block'.
Following columns will be used in the calendar and included in the iCalendar file:
Column | Data from | Shown by default in the calendar |
---|---|---|
datetime_from | First column | Yes |
datetime_to | Second column | Yes |
Entry | Third column | Yes |
Description | event.description | Yes |
Location | event.location | No (use 'location.hide', 0 to show it) |
URL | event.url | No (use 'url.hide', 0 to show it) |
The calendar's data report should return at minimum the start date, end date and the text to be shown in the calendar entry. Separate optional description data is shown by default with the text. The description data accomodates iCal data export with separate description field.
Columns that are included into the iCalendar file, but are not shown by default are the location and the URL. You can show them by using the location.hide
/url.hide
options.
Additional columns can be used to styling, create linked reports / URL's for the calendar entry, returning ID's etc. When the ical-option is enabled, user can download the iCal-file for the entries. iCal file can include additional fields (location description) which can be defined with the options.
Entries are considered to be full day entries (do not show the times in the calendar) if both start and end datetimes have time part set to '00:00:00' or the start has time part set to '00:00:00' and end is null.
Fetching data dynamically (via Ajax) allows for browsing unlimited date range. The calendar content is fetched when needed as the user navigates the calendar. The report consists of the main report introducing the calendar and the report responsible for fetching the data. The report fetching the data is defined via dbr.calendar.report
-command.
Calendar level options/commands are put in the main report. These include for example height, weekends, weekNumbers, etc.
create procedure sp_DBR_calendar( in_calendar_id int) begin select 'dbr.calendar', 800; -- Define the calendar data report select 'dbr.calendar.report', 'sp_DBR_calendar_data', in_calendar_id; -- Show the calendar with result set, content does not matter select 'dummy'; end;
dbr.cellstyle and dbr.cellclass can be used to style the individual entry. For example, the "Birthday party" entry is styled with 'background:red;color:white'.
All commands/options that refer to the result set's columns or are calendar entry level commands (like dbr.report, dbr.url, editable-option event.editable-option), needs to be put in the calendar's dynamic data report, not into the main report.
One can add the ability to download the iCal-file from the entry by adding the ical-option. With iCal, one can pass additional location and description fields to the iCal-file.
create procedure sp_DBR_calendar_data( in_from date, in_to date, in_calendar_id int ) begin select 'dbr.cellstyle', 'entry', 'style'; select 'dbr.report', 'sp_DBR_entry_actions', '[entry]', 'in_id=id', "popup"; -- Optional iCal-file option with optional description and location fields select 'dbr.calendar.options', 'ical', 1; select 'dbr.calendar.options', 'event.description', 'description'; select 'dbr.calendar.options', 'event.location', 'location'; -- Select all entries which are inside the current range. Optional criteria (like in_calendar_id) can be used. -- datetime_from, datetime_to, entry are the mandatory columns, rest are additional select datetime_from, datetime_to, entry, style, id, description, location from mydb.calendar where calendar_id=in_calendar_id and cast(datetime_from as date) <= in_to and ifnull(cast(datetime_to as date),in_from) >= in_from; end
You can also create a calendar with a single report if you have limited date range (no additional data is fetched). This can be used when an only limited number of entries are fetched. Otherwise, the recommended way is to use the dynamic data fetching.
create procedure sp_DBR_calendar( in_calendar_id int) begin select 'dbr.calendar', 800; -- Override the header set in defaults.php to remove the prev/next month buttons select 'dbr.calendar.options', 'header', '{"left": "today","center":"title","right":"month,agendaWeek,agendaDay,listMonth"}'; select datetime_from, datetime_to, entry, style from mydb.calendar where calendar_id=in_calendar_id and cast(datetime_from as date) <= in_to and ifnull(cast(datetime_to as date),in_from) >= in_from; end
By using the 'editable'-option, you can make the calendar events editable. User can by default adjust the start time (drag and move events) and adjust the duration (resize the end time). You can define what user can do, by setting the editing_type parameter ('all' or '' for both start end duration, 'start' for just starttime change and 'duration' for end time editing). You can also define the editing capability for each individual entries by using the 'event.editable'-option.
Calendar event adjust duration:
Calendar event adjust the start time:
In order to create new events or delete existing ones, use a linked report.
The calendar editing report will automatically add starttime and endtime as last parameters. Other parameters can be defined in 'editable'-option after the editing report name.
create procedure sp_DBR_calendar( in_calendar_id int) begin select 'dbr.calendar', 800; select 'dbr.calendar.options', 'editable', '', 'sp_DBR_calendar_edit', 'id'; select 'dbr.calendar.report', 'sp_DBR_calendar_editable_data', in_calendar_id; -- Show the calendar with result set, content does not matter select 'dummy'; end
The data report can override the default editable for each entry via event.editable option. The column referenced can contain '' for no editing, 'start' to allow start time to be edited, 'duration' to edit the event duration and 'all' to enable both start time and duration editing.
create procedure sp_DBR_calendar_editable_data(in_from date, in_to date, in_choose int) begin -- All options that refer to columns need to be placed into the data report select 'dbr.cellstyle', 'entry', 'style'; select 'dbr.calendar.options', 'editable', '', 'sp_DBR_calendar_edit', 'id', 'datetime_from', 'datetime_to'; -- Set editing capability per event select 'dbr.calendar.options', 'event.editable', 'editable'; -- Optionally set the id which can be used with the FullCalendar JavaScript API select 'dbr.calendar.options', 'event.id', 'id'; -- Add capability to delete an event (misc[] is a dynamically ([]-notation) created embedded elemennt where the result from the report is put) select 'dbr.report', 'sp_DBR_calendar_event_delete','[entry]', 'misc[]', 'in_id=id', '"dbr.html:<span style="color:red">Delete event</span>"'; -- Update a calendar entry text select 'dbr.report', 'sp_DBR_calendar_event_update','[entry]', 'misc[]', 'in_id=id', 'in_entry<=entry'; select datetime_from, datetime_to, entry, style, editable, id from mydb.calendar where calendar_id=in_calendar_id and cast(datetime_from as date) <= in_to and ifnull(cast(datetime_to as date),in_from) >= in_from; end
The sp_DBR_calendar_edit report would then have three paramerer: id, start time, and end time.
create procedure sp_DBR_calendar_edit( in_id int, in_from datetime, in_to datetime ) begin update mydb.calendar set datetime_from = in_from, datetime_to = in_to where id=in_id; end
You can also reject the editing inside the editing routine and/or pass text that will be shown to the user. If the text starts with 'reject:', the UI will undo the editing and move/resize the event back to the original position. If a text without 'reject:' is selected, the text will be shown to the user and the event will be accepted by the UI.
create procedure sp_DBR_calendar_edit( in_id int, in_from datetime, in_to datetime ) begin declare v_min_time datetime; select min_time into v_min_time from mydb.open_slots; if (in_from < v_min_time) then select 'reject:The selected timeslot is closed'; else update mydb.calendar set datetime_from = in_from, datetime_to = in_to where id=in_id end if; select 'Event succesfully moved'; end if; end
You can add new events to the calendar by using a linked report. Add a button to the main report whose target is a dynamic div (as example's 'misc').
select 'dbr.button', 'New'; select 'dbr.report', 'sp_DBR_calendar_event_new', 'misc[]'; select 'Dummy';
The 'sp_DBR_calendar_event_new' linked report asks the datetimes and the entry text as a parameter and after insert refreshes the Ajax-calendar.
create procedure sp_DBR_calendar_event_new( in_from datetime, in_to datetime, in_entry varchar(255) ) begin insert into mydb.calendar ( datetime_from, datetime_to, entry ) values ( in_from, in_to, in_entry ); select 'dbr.javascript', "$('.mycal').fullCalendar( 'refetchEvents' )"; end
create procedure sp_DBR_calendar_event_delete( in_id int ) begin delete from mydb.calendar where id=in_id; select 'dbr.javascript', "$('.mycal').fullCalendar( 'refetchEvents' )"; end
create procedure sp_DBR_calendar_event_update( in_id int, in_entry varchar(255) ) begin update mydb.calendar set entry=in_entry where id=in_id; select 'dbr.javascript', "$('.mycal').fullCalendar( 'refetchEvents' )"; end
When you add a "New calendar entry"-button to the report, you can also automatically connect calendar internal actions to this button. These internal actions include:
Create the main report and set calendar date to be fecthed dynamically. Add
create procedure sp_DBR_calendar( in_calendar_id int) begin select 'dbr.javascript',' function confirmdel(obj) { return confirm("Are you sure you want to delete the entry?"); }'; select 'dbr.calendar', 800; -- Define the calendar data report select 'dbr.calendar.report', 'sp_DBR_calendar_data', in_calendar_id; -- Bind to 'newentry' button and tell that the new calendar entry report has two datetime parameters select 'dbr.calendar.options', 'new', 'newentry', 2; -- Allow user to create new calendar entries by painting (click and drag with mouse) select 'dbr.calendar.options', 'selectable', 1; -- Show the calendar with result set, content does not matter select 'dummy'; -- Create the "New calendar entry"-button with newentry-class (and add plus-character to the button) select 'dbr.button', '<i class="fa fa-plus" aria-hidden="true"></i> New calendar entry', 'newentry button'; select 'dbr.report', 'sp_DBR_calendar_event_new', 'misc[]'; select 1; end;
create procedure sp_DBR_calendar_data(in_from date, in_to date, in_id) begin select 'dbr.cellstyle', 'entry', 'style'; select 'dbr.calendar.options', 'editable', '', 'sp_DBR_calendar_edit', 'id', 'datetime_from', 'datetime_to'; select 'dbr.calendar.options', 'event.editable', 'editable'; select 'dbr.report', 'sp_DBR_calendar_delete','[entry]', 'in_id=id', "misc[]", 'callbefore=confirmdel', '"dbr.html:<span style="color:red">Delete event</span>"'; select 'dbr.calendar.options', 'event.id', 'id'; select datetime_from, datetime_to, entry, style, editable, id from mydb.calendar where calendar_id=in_id cast(datetime_from as date) <= in_to and cast(datetime_to as date) >= in_from;
To add functionality for downloading all events in a view to an iCal file, one can use the following code:
select 'dbr.html', '<button>Download calendar events</button>';
If you have multiple calendars in a report, add a resultclass to the calendar and pass the class name to the Calendar.iCalAll() as a parameter so myDBR knows from which calendar the download should be done.
The default style for the calendar itself is myDBR's own style. You can revert back to FullCalendar native style by adding a resultclass fullcalendar
to the result set. You can also make the change permanent by setting default $mydbr_defaults['calendar']['default_class'] = 'fullcalendar'
.
The default style for the calendar entries is block
. FullCalendar also offers an another dot-look, which you can choose by setting select 'dbr.calendar.options', 'eventDisplay', 'auto';
.