The calendar

View an example of a calendar

Separate mode for the table component.

Allows you to output any data in the calendar.

1. in SELECT 3 GetItems, set ViewType= 'calendar' and configure the CalendarOptions parameter (all calendar settings in a single JSON):

select 'calendar' ViewType,
    '{
    	"defaultView": "timeGridWeek",
        "firstDay":1,
        "defaultDate": "2020-05-28",
        "droppable": true,
        "editable": true,
        "locale": "ru",
        "rerenderDelay": 50,
        "timeZone": "UTC",
         "displayEventTime": true,
         "header": {
            "left": "title",
            "center": "dayGridMonth,timeGridWeek,timeGridDay,list",
            "right": "prev,next today myCustomButton"
          },
          "formCreate": {
            	"itemID":"0",	"code" : "newEvent", "big": 0, "title": "New event", "btnText":"Create", "slideout":1
          },
          "remove":1
    }' calendarOptions,
    1 hideTitleCount

Key calendar settings: 

• formCreate-sets the settings for the event creation form (by clicking on an empty field).
• remove - if 1, the delete button will appear when hovering over the event. 
• dropable - if 1, you can throw special elements on the calendar to add new events. 
• editable - if 1, you can change the event parameters (move or change the duration). 
• timezone-sets the time shift (time zone), UTC, locale, or name of the zone
• locale-sets the calendar language
• defaultDate-sets the date on which the calendar will be opened. 
• defaultView - what default view to output when loading timeGridWeek, list, dayGridMonth
• height (auto, parent, or number), contentHeight, aspectRatio - control the calendar height, width/height ratio, and vertical scrolling.

Description of all JSON calendar parameters: https://fullcalendar.io/docs

2. in SELECT 1 , output event data as the following table: 

	declare   @result TABLE (itemID int, title nvarchar(256), start nvarchar(64), [end] nvarchar(64),
                             color nvarchar(64), textColor nvarchar(64), borderColor nvarchar(64),
                            url nvarchar(256), className nvarchar(128), allDay bit, rendering nvarchar(128),
                             formCode nvarchar(128), formBig bit, formTitle nvarchar(128), formBtnText 
							 nvarchar(128), formSlideout bit, disableModify bit
    )

	insert into @result
    --'2020-05-28T07:19:00'  convert(nvarchar, start, 120)
	select   id itemID, title title, convert(nvarchar, start, 120)  start,  convert(nvarchar, [end],
	120)  [end], '#5f3' color, '' textColor, '' borderColor,
    	url url, '' className, allDay allDay, '' rendering,  'editEvent' formCode, 0 formBig, 'Change 
		the event' formTitle, 'Save' formBtnText, 1 formSlideout, 0 disableModify
    from as_events
    where username = @username
       

Parameters in SELECT 1 (you don't need to create columns in the table via the web interface):

• itemID - ID of the event
  
• title-event title
  
• start-start of the event (2020-05-28T07:19: 00)
  
• end-end of the event (2020-05-28T07:19: 00)
  
• color - the color of the event background
  
• textColor-event text color
  
• borderColor-event border light
  
• url - if specified, clicking on the event will lead to the URL
  
• className-CSS class for the event
  
• allDay - if 1, the event will be for the whole day
  
• rendering-if there is a background, the event will be marked in the background color on the calendar.
  
• formCode-code of the event editing form.
  
• formBig - if 1, the edit form will be large (full screen)
  
• formTitle-form header in the modal window
  
• formBtnText-form button in the modal header
  
• formSlideout - if 1, the form will open attached to the right side of the browser
  
• disableModify - if 1, the event cannot be dragged and changed in duration

How to change events and drag typical events to the calendar

In CalendarOptions, set editable=1 (for changing event dates by dragging and stretching) and dropable=1 (adding a new event by dragging an element from outside). 

Implementing the updateField procedure: 

CREATE PROCEDURE [dbo].[crud_tst-calendar_updateField]
	@itemID int,
	@field nvarchar(64),
	@value nvarchar(max),
	@username nvarchar(64)
AS
BEGIN
-- update the fields of the table
-- for casting types, use try_cast(@value as int), for date try_convert(date, @value, 104)

	declare @start nvarchar(256), @end nvarchar(256), @additionalData nvarchar(256), @startDate datetime, @endDate datetime

      select @start = dbo.str_splitPart(@value, '||', 1)
      select @additionalData = dbo.str_splitPart(@value, '||', 2)

      select @end = dbo.str_splitPart(@additionalData, '||', 1)
      select @additionalData = dbo.str_splitPart(@additionalData, '||', 2)

      select @startDate =cast(CONVERT(datetime2, @start, 126) as datetime)
      select @endDate =cast(CONVERT(datetime2, @end, 126) as datetime)


if(@field='event') begin
	if(@itemID>0) begin
      -- this is the edit date of the element

      update as_events
      set start = @startDate, [end] = @endDate
      where id = @itemID and username=@username
	end else begin
		-- this is adding an element via dragging (additionalData=title||itemID from an external filter. this is not the itemID of an existing event)
        declare @title nvarchar(128) ='', @outerItemID nvarchar(256)
        select @title = dbo.str_splitPart(@additionalData, '||', 1)
		select @outerItemID = dbo.str_splitPart(@additionalData, '||', 2)

        insert into as_events(start, [end], title, [desc], url, allDay, username)
    	values (@startDate, @endDate, @title, '', '', 0, @username)

    end
end else begin
	select 'Invalid property code' Msg, 0 Result
	return
end
select '' Msg, 1 Result

END

Note: 

1. This procedure works both for changing elements and adding new ones (those that were added via drop from outside)
2.  Start||end||additionalData is passed to value. If this is an update, additionalData is empty. If created, additionalData=title||outerItemID. outerItemID comes from an external element (data-itemID parameters) that is transferred to the calendar. 
3. Layout for items that we plan to drag to the calendar: 

<div id="as-calendar-dropCont">
<div class="as-calendar-dropItem my-2 p-2 border" data-itemid= "0" data-event= "{&title& quot;: &Event for 2 hours&, &duration& quot;: &02:00& quot;} " >Add an external typical event</div>
<div class="as-calendar-dropItem my-2 p-2 border" data-itemid="123" data-event="{ &title&quot;: & Event for 5 hours&quot;, &duration& quot;: &05:00&quot;, &color&quot;: & quot;#3F3&quot;}"> another 1 event</div>
</div>

• Container required with id=as-calendar-dropcontent
• Elements have the as-calendar-dropItem and data-itemID classes (this value allows you to pass a link to an external object, such as an Order, to the event saving procedure).
• In data-event, we set the event settings as JSON. Key parameters: title, duration, color.

How to add events to the calendar

To do this, specify the settings for formcreate in CalendarOptions.

Creating a form and processing incoming parameters for creating an event.

An example of the stored procedure:

CREATE PROCEDURE [dbo].[fm_newEvent_saveItem]
@username nvarchar(256),
@itemID nvarchar(256),
@parameters ExtendedDictionaryParameter READONLY
-- or list all fields in the form (@fieldcode, etc.)
AS
BEGIN
	-- saving the form (adding/updating an entity)

    declare @filterItemID nvarchar(128), @dt nvarchar(256), @start datetime -- '2020-05-27T10:00:00+03:00'
     set @filterItemID = dbo.str_splitPart (@itemID, '_', 1)
    set @dt = dbo.str_splitPart (@itemID, '_',2)

	select @start =cast(CONVERT(datetime2, @dt, 126) as datetime)

	declare @ptitle nvarchar(max)
	select @ptitle = Value2 from @parameters where [key]='title'
	declare @pdesc nvarchar(max)
	select @pdesc = Value2 from @parameters where [key]='desc'
	declare @pallDay nvarchar(max)
	select @pallDay = Value2 from @parameters where [key]='allDay'
	declare @phours float, @minutes int
	select @phours = try_cast(replace(Value2, ',', '.') as float) from @parameters where [key]='hours'

    if(@phours is null) set @phours = 1

    set @minutes = cast(@phours*60 as int)
  --  set @pdesc = cast(@minutes as nvarchar) + '  ' + cast(@phours as nvarchar)

    insert into as_events(start, [end], title, [desc], url, allDay, username)
    values (@start, dateadd(minute, @minutes, @start), @ptitle, @pdesc, '', iif(@pallDay='True', 1,0), @username)

	-- SELECT 1 (Result, Msg, SuccessUrl, HideFormAfterSubmit, RefreshContainer)
	select 1 Result, 'Сохранено' Msg, '' SuccessUrl, 1 HideFormAfterSubmit, '.calendarCont' RefreshContainer
END

How to edit calendar events.

To do this, specify the form parameters for editing for the element (these are parameters that start with form).

You can make sure that different events are handled by different editing forms. 

After the update, we update the calendar via RefreshContainer (to do this, we put its snippet in an additional container).

Example of SQL saving an edit form: 

CREATE PROCEDURE [dbo].[fm_editEvent_saveItem]
   @username nvarchar(256),
   @itemID int,
   @parameters ExtendedDictionaryParameter READONLY
   -- либо перечислить все поля в форме (@fieldcode и т.д.)
AS
BEGIN
	declare @ptitle nvarchar(max)
	select @ptitle = Value2 from @parameters where [key]='title'
	declare @pdesc nvarchar(max)
	select @pdesc = Value2 from @parameters where [key]='desc'

	declare @pallDay nvarchar(max)
	select @pallDay = Value2 from @parameters where [key]='allDay'

	update as_events
	set title = @ptitle,
    	[desc] = @pdesc,
        allDay = iif(@pallDay='True', 1, 0)
	where id = @itemID and username=@username

	-- SELECT 1 (Result, Msg, SuccessUrl, HideFormAfterSubmit, RefreshContainer)
	select 1 Result, 'Сохранено' Msg, '' SuccessUrl, 1 HideFormAfterSubmit, '.calendarCont' RefreshContainer

END

Deleting an event from the calendar

To do this, set calendarOptions remove=1

When you hover over the event, the delete icon will appear. 

Implementing the removeItem procedure: 

CREATE PROCEDURE [dbo].[crud_tst-calendar_deleteItem]
	@itemID int,
	@username nvarchar(32)
AS
begin
	SET NOCOUNT off ;
	-- delete of the element
	delete from as_events where id = @itemID and username=@username

	if (@@ROWCOUNT > 0) begin
		select '' Msg, 1 Result
	end else begin
		select 'I couldn't delete the event' Msg, 0 Result
	end
end