Fetching data dynamically (via Ajax) allows for browsing unlimited date range. The calendar content is fetched when needed as user navigates the calendar. The report consists of a main report introducing the calendar and the report responsible of fetching the data.

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;

The calendar dynamic data report consist of a report which has startdate and enddate as first two parameters (additional parameters can be passed). The returning result set contains the event's starting datetime, ending datatime and the calendar text entry. Additional columns can be used to create linked reports / URL's for the calendar entry.

Entries do not show times if both start and end datetimes have time part '00:00:00' or start has time part '00:00:00' and end is null. These are considered full day entries.

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.

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";

-- Select all entries which are inside the current range. Optional criteria (like in_calendar_id) can be used.
select datetime_from, datetime_to, entry, style, 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

You can also create a calendar with single report if you have limited date range (no additional data is fetched). This can be used when 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
select 'dbr.report', 'sp_DBR_calendar_delete','[entry]', 'in_id=id', "misc[]", '"dbr.html:<span style="color:red">Delete event</span>"';

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 show to user. If the text starts with 'reject:', the UI will undo the editing and move/resize the event back to 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 '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 parameter and after insert refreshes the Ajax-calendar.

create procedure sp_DBR_calendar_event_new(
in_from datetime,
in_to datetime,
in_entry varchar(120)
)
begin

insert into mydb.calendar ( datetime_from, datetime_to, entry )
values ( in_from, in_to, in_entry );

select 'dbr.javascript', "$('.mycal').fullCalendar( 'refetchEvents' )";

end

Calendar events have an ID you can set using the event.id-option which you define when the events are fetched.

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 parameter and after insert refreshes the Ajax-calendar.

create procedure sp_DBR_calendar_event_delete(
in_entry varchar(255)
)
begin

delete 
from mydb.calendar
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;