USP_DATAFORMTEMPLATE_VIEW_CALENDAR

The load procedure used by the view dataform template "Calendar View Form"
Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(73)	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CALENDARID	uniqueidentifier	INOUT	CALENDARID
@CALENDARCONTEXTID	uniqueidentifier	INOUT	CALENDARCONTEXTID
@CALENDARSPECXML	xml	INOUT	CALENDARSPECXML
@DATASOURCES	xml	INOUT	DATASOURCES
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DEFAULTTIMEZONENAME	nvarchar(200)	INOUT
Definition

Copy

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CALENDAR
(
    @ID nvarchar(73),
    @DATALOADED bit = 0 output,
    @CALENDARID uniqueidentifier = null output,
    @CALENDARCONTEXTID uniqueidentifier = null output,
    @CALENDARSPECXML xml = null output,
    @DATASOURCES xml = null output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @DEFAULTTIMEZONENAME nvarchar(200) = null output
)
as
    set nocount on;

    select 
        @DATALOADED = 1,
        @CALENDARID = case when len(@ID) >= 36 then substring(@ID,1,36)
                             end,
        @CALENDARCONTEXTID = case len(@ID)
                                when 73 then substring(@ID,38,36)
                             end,
        @CALENDARSPECXML = CALENDARSPECXML
    from dbo.CALENDARCATALOG
    where ID = substring(@ID,1,36)
        and 1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
        (
            CALENDARCATALOG.CALENDARSPECXML.query
                (
                    'declare namespace common="bb_appfx_commontypes";
                    /*/common:InstalledProductList'
                )
        )

    declare @DATALIST_USERSETTINGS as xml
    select @DATALIST_USERSETTINGS = DATALISTS
    from dbo.USERSETTINGSCALENDAR
    where USERSETTINGSCALENDAR.APPUSERID = @CURRENTAPPUSERID
        and USERSETTINGSCALENDAR.CALENDARCATALOGID = @CALENDARID

    if @DATALOADED = 1
    begin

        select @DEFAULTTIMEZONENAME = NAME from dbo.TIMEZONEENTRY where ID = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULT();

        declare @CALENDARSOURCES table
        (
            ROW int identity(1,1),  -- Explicitly preserve the order of the datasources.
            ID nvarchar(36),
            COLORSTYLE nvarchar(20),
            ENABLED nvarchar(10)
        )

        insert into @CALENDARSOURCES
        (
            ID,
            COLORSTYLE,
            ENABLED
        )
        select
            cast(item.query('data(@DataListID)') as nvarchar(36)),
            cast(item.query('data(@ColorStyle)') as nvarchar(20)),
            cast(item.query('data(@Enabled)') as nvarchar(10))
        from @CALENDARSPECXML.nodes('//*:CalendarDataSource') as DataSources(item)

        -- Calculate the catalog visual styles
        set @DATASOURCES = (
            select
                CALENDARSOURCES.ID,
                coalesce(
                    CALENDARDATALISTCOLOR.COLORSTYLECODE, 
                    dbo.UFN_CALENDARCATALOGCOLOR_GETID(CALENDARSOURCES.COLORSTYLE)
                ) as COLORSTYLE,
                coalesce(DATALIST_USERSETTINGS.ENABLED, 
                    case when len(CALENDARSOURCES.ENABLED) = 0
                        then 1
                        else CONVERT(bit, CALENDARSOURCES.ENABLED)
                    end
                ) as ENABLED
            from
                @CALENDARSOURCES as CALENDARSOURCES
                left join dbo.CALENDARDATALISTCOLOR on 
                    CALENDARSOURCES.ID = CALENDARDATALISTCOLOR.DATALISTID and
                    @CALENDARID = CALENDARDATALISTCOLOR.CALENDARCATALOGID
                inner join dbo.DATALISTCATALOG on 
                    CALENDARSOURCES.ID = DATALISTCATALOG.ID
                left join (
                    select
                        T.c.value('(ID)[1]','uniqueidentifier') AS [ID],
                        T.c.value('(ENABLED)[1]','bit') as [ENABLED]
                    from @DATALIST_USERSETTINGS.nodes('/DATALISTS/ITEM') T(c)
                ) DATALIST_USERSETTINGS on DATALISTCATALOG.ID = DATALIST_USERSETTINGS.ID
            where
                -- Check security
                (
                    (@CURRENTAPPUSERID is null)
                    or (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1)
                    or (dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID, DATALISTCATALOG.ID) = 1)
                )
                -- Check product flags
                and (
                    dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(
                        DATALISTCATALOG.DATALISTSPEC.query(
                            'declare namespace common="bb_appfx_commontypes";
                            /*/common:InstalledProductList'
                        )
                    ) = 1
                )
            order by CALENDARSOURCES.ROW
            for xml raw('ITEM'),type,elements,root('DATASOURCES'),binary base64
        )
    end
    return 0;