version

Planner

This component is not suitable for users who require assistive technologies.
This component is not suitable for use on mobile devices.

Used to assign items to resources and dates using drag and drop.

Component modes: Without time scale | With time scale

Component mode: Without time scale

Appearance

Overview

PlannerOverview

Without timescale

PlannerItems

With time scale

PlannerWithTimescale

Component settings

Default item width

The default width of an item.

Possible value	Description
Medium
Large

Enabled view options

The options available in the "View" dropdown.

Item height

The number of lines of text available per item.

Unbooked area height

How many item are visible horizontally in the unbooked area.

Years after in dropdown

The number of years to add to the year dropdown after the current year.

Years before in dropdown

The number of years to add to the year dropdown before the current year.

SQL

SQL Call: Filters (mandatory)

This is the first call made by the component. It is followed by the 'Get data' call to finish initialization.

Specifies the filter dropdowns to display and their values, using two result sets per filter.

The selected values will be passed to the 'Get data' call.
The selected values are also available for item links and day/resource links.

May modify database: No

Parameters

@Action string

Set to 'GetFilters' for this call.

Resultset: Filter (optional)

Table count: repeated zero or more times

Row count: exactly one row

Columns

DefaultValue optional string

Default value to use.

Default: If omitted, the first value is used.

Filter mandatory string

The name of the filter dropdown.

Resultset: Filter values (optional)

Values to show in the dropdown from the previous resultset.

Table count: repeated zero or more times

Row count: zero or more rows

Columns

<column with ordinal 1> mandatory string

Id value for filter. The column name is used as parameter name.

<column with ordinal 2> mandatory string

Text to display.

SQL Call: Get data (mandatory)

Called initially after the 'Get filters' call. Also called after a 'Move' call has requested a reload.

If @Mode is 'Weeks', then a temp table #WeekDates(FromDate, ToDate) is available with one row per week to show.

May modify database: No

Parameters

@<filter-name> string

One parameter for each filter is set to the current value of that filter.

@Action string

Set to 'GetData' for this call.

@FromDate date

The first date shown.

@Mode string

Possible value	Description
Days	If the view is grouped by days.
Weeks	If the view is grouped by weeks.

@ToDate date

The last date shown.

Resultset: General (optional)

General configuration.

Table count: repeated zero or one time

Row count: exactly one row

Columns

AggregateLabel optional string

The label to show when both the aggregate rows and the aggregate columns are visible.

Default: Defaults to a sum sign.

ResourceFilterLabel optional string

Label to show above the resource filter. The filter is only shown if a label has been given. Note that the filtering is only done client side so if your planner contains a lot of data it might be more suitable to use a filter dropdown.

Title optional string

The title to show in the upper left corner.

UnbookedDayExpanded optional bit

If the area with unbooked items per day should start expanded.

UnbookedDayLabel optional string

The label for the area holding unbooked items per day (i.e. holding items with a start date but not connected to a resource). The area is only shown if a label has been given.

UnbookedExpanded optional bit

If the area with unbooked items not connected to a date should start expanded.

UnbookedLabel optional string

The label for the area holding unbooked items not connected to a date (i.e. holding items without a start date and not connected to a resource). The area is only shown if a label has been given.

Resultset: ColumnInfo (optional)

Additional information for specific columns.

Table count: repeated zero or one time

Row count: zero or more rows

Columns

Aggregate optional string

The aggregate value for the specified date. The row with column aggregate values is only shown if at least one column has an aggregate value.

Date mandatory date

The date the information belongs to. Use the value of the FromDate column from #WeekDates when @Mode is 'Weeks'.

ExtraInfo optional string

Extra info for the specified date, shown below the date.

IsHoliday optional bit

Whether the specified date is a holiday.

Resultset: ResourceInfo

The resources to show. If you want to include filtering of the resources, use the ResourceFilterLabel column from the general table.

Table count: repeated exactly once

Row count: zero or more rows

Columns

Aggregate optional string

The aggregate value for the specified resource.

The column with resource aggregate values is only shown if at least one resource has an aggregate value.

ExtraInfo optional string

Extra info for the specified resource.

Resource mandatory string

The name of the resource.

ResourceId mandatory string

The ID of the resource.

Resultset: Items

The items to show. The component supports at most 2000 items, but that amount of items is difficult to manage. Try adding additional filters if you have many items.

Table count: repeated exactly once

Row count: zero or more rows

Columns

BackgroundColor optional string

The background color for the item. See Colors.

CanDrag optional bit

Determines if the item can be dragged.

Days optional int

The number of days the item spans.

Description optional string

Item description shown below the title.

Icon optional string

The name of a system icon. Multiple icons can be added by having multipe columns with names starting with "Icon".

Use the menu item "Admin > Theme > System icons" to register system icons.

IconColor optional string

Color to use for the corresponding icon. If e.g. the icon column is named IconX, the color column is IconColorX. See Colors.

ItemId mandatory string

The Item ID. Used by the 'Move' call after the user has dropped the item.

ResourceId optional string

The ID of the resource that this item belongs to, if any.

StartDate optional date

The start date of the item, if any. Note that items with a start date before @DateFrom should be included in this result set if they end on or after @DateFrom.

Title mandatory string

The title of the item.

ToolTip optional string

The tooltip of the item.

SQL Call: Move (mandatory)

Called after an item has been moved using drag and drop.

May modify database: Yes

Parameters

@<filter-name> string

One parameter for each filter is set to the current value of that filter.

@Action string

Set to 'Move' for this call.

@Date date

The date that the item was moved to. Not set if the item does not belong to a date.

@FromDate date

The first date shown.

@ItemId string

The ID of the item that was moved.

@Mode string

If @Mode is 'Weeks', then a temp table #WeekDates(FromDate, ToDate) is available with one row per week to show.

Possible value	Description
Days	If the view is grouped by days.
Weeks	If the view is grouped by weeks.

@ResourceId string

The ID of the resource that the item was moved to. Null if the item was moved to an unbooked spot.

@ToDate date

The last date shown.

Resultset: Reload (optional)

Can be used when partial updates are not sufficient.

Can not be combined with any other result sets.

Table count: repeated zero or one time

Row count: exactly one row

Columns

Reload mandatory bit

Set to 1 to trigger a 'Get data' call.

Resultset: Columns to update (optional)

Table count: repeated zero or one time

Row count: zero or more rows

Columns

Aggregate optional string

The new aggregate value for the specified date.

Date mandatory date

The date to update. Use the value of the FromDate column from #WeekDates when @Mode is 'Weeks'.

Resultset: Resources to update (optional)

Table count: repeated zero or one time

Row count: zero or more rows

Columns

Aggregate optional string

The new aggregate value for the specified resource.

ResourceId mandatory string

The ID of the resource to update.

Resultset: Items to update (optional)

Table count: repeated zero or one time

Row count: zero or more rows

Columns

BackgroundColor optional string

The new background color for the item. See Colors.

Description optional string

The description for the item.

Icon optional string

The name of a system icon.

IconColor optional string

Color to use for the corresponding icon. See Colors.

ItemId mandatory string

The ID of the item to update.

Title optional string

The new title for the item.

ToolTip optional string

The tool tip for the item.

Resultset: Messages (optional)

Can not be combined with any other result sets.

Table count: repeated zero or one time

Row count: exactly one row

Columns

admin_force optional string

Message to show.

admin_message optional string

Message to show (preventing the move).

SQL Call: Validate parameters

Allows you to validate the SQL parameters before any other SQL is run in the component. This call is only made if the SQL is a stored procedure and Validate parameters is checked.

May modify database: No

Parameters

@Force bit

Set to 1 if the last call to validate parameters used admin_force and the user clicked OK in the OK/Cancel dialog.

@ValidateParams bit

Set to 1 when this call is made.

Resultset: Messages (optional)

Table count: repeated zero or one time

Row count: zero or one row

Columns

ADMIN_Force optional string

Message asking the end user to confirm their parameters.

ADMIN_Message optional string

Message explaining why the parameters are rejected.

InfoSQL

See the InfoSQL documentation for details.

Custom access control and logging

SQL Call: Custom access control and logging

Use this call to restrict which entries a user is allowed to view and edit, and to log which entries a user views.

Access to a menu item is normally controlled through functions and roles alone but some entities need more fine grained control. For example, a user may have access to the View Member menu item for normal members but not for members with a protected identity.

The menu items a user visits are always logged (in ADMINLogMenuItem) but for sensitive data you may need to log exactly what entries are viewed. Do the logging in this call as the common ways of viewing data (grid and InfoSQL) are not allowed to modify the database.

If you bind a scalar function instead of a stored procedure to this call then its name must end with '_GrantAccess'.

May modify database: Yes

Resultset: Access permissions

Return whether the user is allowed to visit the menu item with the current parameters.

Table count: repeated exactly once

Row count: exactly one row

Columns

GrantAccess mandatory bit

1 if the user is allowed to view the menu item, 0 if the user should not be allowed to view the menu item.

If 0 then an error will be logged as the user should not have been able to reach the menu item with the given parameters in the first place.

Querystring parameters

DisplayMode optional

The default value for the 'View' dropdown.

Possible value	Description
Day	Day
Four-Full-Weeks	4 weeks (day view)
HalfYear-Weeks	Half year Only available in the "Without time scale" mode.
Month	Month (day view)
Month-Weeks	Month (week view) Only available in the "Without time scale" mode.
Quarter-Weeks	Quarter Only available in the "Without time scale" mode.
Two-Full-Weeks	2 weeks (day view)
Week	Full week
WorkWeek	Work week
Year-Weeks	Year Only available in the "Without time scale" mode.

menuitemheading optional

String that replaces the menu item name when the menu item is loaded. It does not replace the name before that (like for example in the navigator). It is ignored if the menu item is displayed as a part in a multipart in which case the name can be set from the multipart procedure.

StartDate optional

Start date. Will be adjusted to the first day of the week or month, depending on the 'View' dropdown.

Examples

Simple planner

A simple planner where tasks start in the Unbooked area and can be dragged to users.

CREATE TABLE Example.Task
(
	TaskId           int identity primary key,
	TaskName         varchar(100) NOT NULL,
	DaysRequired     int NOT NULL,
	UserIdAssignedTo int NULL,
	StartDate        date NULL,
	EndDate          AS DATEADD(day, DaysRequired - 1, StartDate)
);

GO

INSERT Example.Task ( TaskName, DaysRequired )
VALUES
('ThreeDays', 3),
('OneDay', 1),
('TwoWeeks', 14);

GO

CREATE OR ALTER PROCEDURE Example.Task_Planner
	@Action     varchar(50) = NULL,
	@Mode       varchar(50) = NULL,
	@FromDate   date        = NULL,
	@ToDate     date        = NULL,
	@ItemId     int         = NULL,
	@Date       date        = NULL,
	@ResourceId int         = NULL
AS
BEGIN
	IF @Action = 'GetData'
	BEGIN
		-- General.
		SELECT
			'Unplanned' AS UnbookedLabel,
			1           AS UnbookedExpanded;

		-- Resources.
		SELECT
			U.UserId   AS ResourceId,
			U.Username AS Resource
		FROM
			SoftadminApi.[User] U
		WHERE
			U.IsEnabled = 1;

		-- Items.
		SELECT
			T.TaskId           AS ItemId,
			T.TaskName         AS Title,
			T.StartDate        AS StartDate,
			T.DaysRequired     AS Days,
			T.UserIdAssignedTo AS ResourceId
		FROM
			Example.Task T
		WHERE
			T.StartDate IS NULL
			OR
			(
				T.StartDate <= @ToDate AND
				T.EndDate >= @FromDate
			);

		RETURN;
	END;

	IF @Action = 'Move'
	BEGIN
		UPDATE Example.Task SET
			UserIdAssignedTo = @ResourceId,
			StartDate        = @Date
		WHERE
			TaskId = @ItemId;

		RETURN;
	END;

	RETURN;
END;