Planner

  • This component is not suitable for users that require assistive technologies. This component is not suitable for users who require assistive technologies.
  • This component is not suitable for use on mobile devies. 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

Component settings

Enabled view options
The options available in the "View" dropdown.
Item height
The number of lines of text available per item.
Item width
The width of an item.
Possible value Description
Small
Medium
Large
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 parameters supplied by the user before any other SQL is run in the component. This call is only made if the component has visible parameters, 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;