Calendar

The calendar allows you to create a fully featured calendar from your data. myDBR utilizes FullCalendar for this. Calendar data can be fetched dynamically via Ajax or directly within the main report.

Commands

dbr.calendar - Create a calendar from a result set or dynamically via Ajax
dbr.calendar.report - Define the Ajax report for calendar data
dbr.calendar.options - Set calendar options

Syntax

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:

Data set Returned to the Calendar

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 a 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 accommodates iCal data export with a separate description field.

Columns that are included in 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 for styling, creating linked reports / URLs for the calendar entry, returning IDs, etc. When the iCal option is enabled, users can download the iCal file for the entries. The 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 the time part set to '00:00:00' or the start has the time part set to '00:00:00' and the end is null.

Data Fetched Dynamically via Ajax

Fetching data dynamically (via Ajax) allows for browsing an 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), need to be put in the calendar's dynamic data report, not in 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

Data Fectched Directly

You can also create a calendar with a single report if you have a limited date range (no additional data is fetched). This can be used when only a limited number of entries are fetched. Otherwise, the recommended way is to use 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

Editable Events

By using the 'editable' option, you can make the calendar events editable. Users can by default adjust the start time (drag and move events) and adjust the duration (resize the end time). You can define what the user can do by setting the editing_type parameter ('all' or '' for both start and duration, 'start' for just start time change, and 'duration' for end time editing). You can also define the editing capability for each individual entry by using the 'event.editable' option.

Calendar event adjust duration:

Calendar event adjust the start time:

To add or delete events on the calendar, use a linked report.

When using the calendar editing report, the starttime and endtime will be automatically added as the last parameters. You can define additional parameters in the 'editable' option following 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

In the data report, you can customize the default editable settings for each entry using the 'event.editable' option. The referenced column can contain:

- An empty string ('') to indicate no editing, - 'start' to allow editing of the start time, - 'duration' to edit the event duration, or - '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

Additionally, you have the flexibility to reject the editing within the editing routine and/or provide custom text to be shown to the user. If the text begins with 'reject:', the UI will undo the editing, moving or resizing the event back to its original position. If the text does not start with 'reject:', it will be displayed to the user, and the UI will accept the event changes.

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

Add new Events to Calendar

To incorporate new events into the calendar, utilize a linked report. Integrate a button into the main report, directing it towards a dynamic div (such as the example's 'misc').

select 'dbr.button', 'New';

select 'dbr.report', 'sp_DBR_calendar_event_new', 'misc[]';
select 'Dummy';

The linked report 'sp_DBR_calendar_event_new' prompts for datetimes and entry text parameters. Upon insertion, it triggers a refresh of 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

Remove an Event from the Calendar

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

Update an Event Text in Calendar

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

FullCalendar functionality with connection to the "New calendar entry"-button

Introducing a "New Calendar Entry" button to the report allows for the automatic association of internal calendar actions. These internal actions encompass:

  • Doubleclick on the calendar to create new entry
  • Paint (click and drag with mouse) into the calendar to create new entry

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;

Download all Events in a View to an iCal File

To enhance functionality for downloading all events in a view to an iCal file, consider implementing the following code snippet:

select 'dbr.html', '<button onclick="Calendar.iCalAll();">Download Calendar Events</button>';

If your report features multiple calendars, assign a result class to each calendar and pass the class name as a parameter to Calendar.iCalAll(). This ensures myDBR identifies the specific calendar for the download operation.

Calendar Look and Feel

The default style for the calendar itself adopts myDBR's distinctive style. Should you wish to revert to the FullCalendar native style, simply append a result class, such as fullcalendar, to the result set. Alternatively, you can make this change permanent by setting the default class in the configuration: $mydbr_defaults['calendar']['default_class'] = 'fullcalendar'.

As for the default style for calendar entries, it is set to block. FullCalendar provides an alternative dot-look, which can be activated by specifying: select 'dbr.calendar.options', 'eventDisplay', 'auto';.