Register custom section in the portal

Prerequisites

The section objects should be accessible for portal users.

Open access to custom objects in portal

1. On dev environment run script to create [int_RegisterSectionInPortal] stored procedure.

Note. Procedure uses string_split function which is available only under compatibility level 130 and above (see details here).

int_RegisterSectionInPortal stored procedure 

create or alter proc [int_RegisterSectionInPortal] (@EntityName NVARCHAR(MAX), @PortalId uniqueidentifier = 'C8565240-1DA3-4A68-BD4E-280F17B0D32E')
as
begin transaction;
set nocount on;
set transaction isolation level read uncommitted;
set xact_abort on;
begin try
if @EntityName = '' OR @EntityName IS NULL
throw 50001, 'Parameter @EntityName is empty', 1;
declare @PortalSysModuleEntity table (Id uniqueidentifier, BaseRecordId uniqueidentifier, TypeColumnUId uniqueidentifier, SysEntitySchemaUId uniqueidentifier);
declare @SchemaName table (Name nvarchar(250) NOT NULL);
declare @temp table (Id uniqueidentifier);
insert into @SchemaName
select value 
from string_split(@EntityName, ',')
where rtrim(value) <> '';
if (select COUNT(*) from @SchemaName) < 1
throw 50002, 'Parameter @EntityName has wrong format. It should contain entities comma separated names.', 1;
insert into @PortalSysModuleEntity (Id, BaseRecordId, TypeColumnUId, SysEntitySchemaUId)
select NEWID(), e.Id, TypeColumnUId, e.SysEntitySchemaUId
from SysModuleEntity e
where e.SysEntitySchemaUId in (
    select [uid]
    from SysSchema sh
        inner join @SchemaName n on n.Name = sh.Name
    where ExtendParent = 0
)
insert into SysModuleEntity(Id, TypeColumnUId, SysEntitySchemaUId, CreatedOn)
select Id, TypeColumnUId, SysEntitySchemaUId, GETUTCDATE()
from @PortalSysModuleEntity
select Id as SysModuleEntity
from @PortalSysModuleEntity;
insert into SysModule (
            [CreatedOn]
           ,[Caption]
           ,[SysModuleEntityId]
           ,[Image16]
           ,[Image20]
           ,[FolderModeId]
           ,[GlobalSearchAvailable]
           ,[HasAnalytics]
           ,[HasActions]
           ,[HasRecent]
           ,[Code]
           ,[HelpContextId]
           ,[ProcessListeners]
           ,[SysPageSchemaUId]
           ,[ModuleHeader]
           ,[Attribute]
           ,[CardSchemaUId]
           ,[SectionModuleSchemaUId]
           ,[SectionSchemaUId]
           ,[CardModuleUId]
           ,[TypeColumnValue]
           ,[Image32Id]
           ,[LogoId])
output inserted.Id into @temp
select      GETUTCDATE()
           ,s.[Caption] + ' Portal'
           ,p.Id
           ,s.[Image16]
           ,s.[Image20]
           ,s.[FolderModeId]
           ,s.[GlobalSearchAvailable]
           ,s.[HasAnalytics]
           ,s.[HasActions]
           ,s.[HasRecent]
           ,s.[Code]
           ,s.[HelpContextId]
           ,s.[ProcessListeners]
           ,s.[SysPageSchemaUId]
           ,s.[ModuleHeader]
           ,s.[Attribute]
           ,s.[CardSchemaUId]
           ,s.[SectionModuleSchemaUId]
           ,s.[SectionSchemaUId]
           ,s.[CardModuleUId]
           ,s.[TypeColumnValue]
           ,s.[Image32Id]
           ,s.[LogoId]
from SysModule
    inner join SysModuleEntity sme on sme.Id = SysModule.SysModuleEntityId
    inner join @PortalSysModuleEntity p on p.BaseRecordId = sme.Id
    inner join SysModule s on s.SysModuleEntityId = p.BaseRecordId
select Id as SysModule
from @temp;
delete from @temp;
insert into SysModuleEdit (ActionKindCaption, ActionKindName, CardSchemaUId, HelpContextId, MiniPageSchemaUId, PageCaption, SearchRowSchemaUId, SysModuleEntityId, SysPageSchemaUId, TypeColumnValue, UseModuleDetails, CreatedOn)
output inserted.Id into @temp
select e.ActionKindCaption, e.ActionKindName, e.CardSchemaUId, e.HelpContextId, e.MiniPageSchemaUId, e.PageCaption, e.SearchRowSchemaUId, p.Id, e.SysPageSchemaUId, e.TypeColumnValue, e.UseModuleDetails, GETUTCDATE() CreatedOn
from SysModuleEdit e
    inner join @PortalSysModuleEntity p on p.BaseRecordId = e.SysModuleEntityId
where e.SysModuleEntityId in (
select Id
from SysModuleEntity e
where e.SysEntitySchemaUId in (
    select [uid]
    from SysSchema sh
        inner join @SchemaName n on n.Name = sh.Name
    where ExtendParent = 0
))
select Id as SysModuleEdit
from @temp;
delete from @temp;
insert into SysModuleEntityInPortal(SysPortalId, SysModuleEntityId, CreatedOn)
output inserted.Id into @temp
select @PortalId, p.Id, GETUTCDATE()
from @PortalSysModuleEntity p
select Id as SysModuleEntityInPortal
from @temp;
delete from @temp;
commit transaction
end try
begin catch
    select ERROR_NUMBER() as ErrorNumber 
        ,ERROR_MESSAGE() as ErrorMessage;
    if @@TRANCOUNT > 0
        rollback transaction;
end catch;
if @@TRANCOUNT > 0
    rollback transaction;

2. On dev environment execute a stored procedure to register sections in the portal.

Stored procedure [int_RegisterSectionInPortal] takes as an argument comma separated list of section table names.

Example

exec dbo.[int_RegisterSectionInPortal] 'lcBwJob,lcBwLine,lcBwVeh,lcBwCc,lcBwDevice'

3. Add sections to the portal workplace (see figure 0)

4. Bind new data to a package with a filter by ids from output(see figure 1-2)  or by CreatedOn = Today (see figure 3) for the following system tables:

• SysModuleEntity
• SysModule
• SysModuleEdit
• SysModuleEntityInPortal

Figure 0. Add sections to portal workplace

Figure 1. Retrieve new records ids from the output

Figure 2. Bind data by ids from output

Figure 3. Bind data by date