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:

'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 IDs 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'.

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 data report should include, at a minimum, the start date, end date, and the text to be displayed in the calendar entry. Additionally, optional description data is included by default along with the text. This description data accommodates iCal data export with a separate description field.

Columns that are available but not shown by default in the iCalendar file are the location and the URL. You can display them by using the location.hide and url.hide options.

Additional columns can be utilized for styling, creating linked reports or URLs for calendar entries, returning IDs, and more. When the iCal option is enabled, users can download the iCal file for the entries. The iCal file can include additional fields such as location and description, which can be defined using specific options.

Entries are considered full-day entries (without displaying times in the calendar) if both the start and end datetimes have the time part set to '00:00:00', or if 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 as needed when 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 using the dbr.calendar.report command.

Calendar-level options and commands are specified in the main report. These include settings such as height, weekends, weekNumbers, and others.

          
              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 individual entries. For example, the "Birthday party" entry can be styled with 'background:red;color:white'.

All commands and options that reference the result set's columns or are specific to calendar entries (such as dbr.report, dbr.url, editable-option, event.editable-option) need to be placed in the dynamic data report of the calendar, not in the main report.

To enable downloading of the iCal file from the entry, use the ical-option. When using iCal, additional fields such as location and description can be passed 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

If you have a limited date range and do not need to fetch additional data, you can create a calendar using a single report. This approach is suitable when only a limited number of entries are involved. However, for scenarios requiring browsing of an unlimited date range and fetching data dynamically, it is recommended to use dynamic data fetching.

          
              create procedure sp_DBR_calendar( in_calendar_id int)
begin
 
select 'dbr.calendar', 800;
 
-- Override the header set in <code class="code">defaults.php</code> 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

Using the 'editable' option, you can enable editing of calendar events. By default, users can adjust the start time by dragging events and change the duration by resizing them. You can further control user actions by setting the 'editing_type' parameter to 'all' (for both start and duration), '' (for both), 'start' (for start time only), or 'duration' (for duration only). Additionally, you can specify individual entry editing capabilities 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 automatically be 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 parameters: 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';.