USP_DATAFORMTEMPLATE_VIEW_EVENT_MICROSITE

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EVENT_MICROSITE
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NAME nvarchar(512) = null output,
                    @URL nvarchar(max) = null output,
                    @ISACTIVE bit = null output, 
                    @ISONLINE bit = null output,
                    @ISLIVE bit = null output,
                    @CAPACITY integer = null output,
                    @AVAILABLECAPACITY integer =null output,
                    @STARTDATE datetime = null output,
                    @ENDDATE datetime = null output,
                    @ISEXPIRED bit = null output, --Is after the end of the on-sale window
                    @EVENTTYPECODE tinyint = null output,
                    @ISSALESMETHODSET bit = null output,
                    @ONSALEDATETIME datetime = null output,
                    @OFFSALEDATETIME datetime = null output,
                    @ISONSALE bit = null output --Is after the beginning of the on-sale window
                )
                as
                begin
                    set nocount on;

                    set @DATALOADED = 0;
                    declare @CAPACITYRESERVED integer = 0
                    declare @ENDTIME dbo.UDT_HOURMINUTE = null

                    declare @SALESMETHODID uniqueidentifier = (select [ID] from dbo.[SALESMETHOD] where [TYPECODE] = 2)
                    declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1);

                    select 
                        @DATALOADED = 1,
                        @NAME = [EVENT].[NAME],
                        @URL = [ONLINEINFO].[URL],
                        @ISACTIVE = case
                            when [PROGRAM].[ID] is null then [EVENT].[ISACTIVE]
                            else [PROGRAM].[ISACTIVE]
                        end,
                        @ISONLINE = isnull([ONLINEINFO].[ISONLINE],0),
                        @CAPACITY = [EVENT].[CAPACITY],
                        @STARTDATE = dbo.UFN_DATE_ADDHOURMINUTE([EVENT].[STARTDATE], [EVENT].[STARTTIME]),
                        @ENDDATE = dbo.UFN_DATE_ADDHOURMINUTE([EVENT].[ENDDATE], [EVENT].[ENDTIME]),
                        @ENDTIME = isnull([EVENT].[ENDTIME],''),
                        @EVENTTYPECODE = case when [EVENT].[PROGRAMID] is null then 0 else 1 end, --Preregistered: 1, Special: 0
                        @ISSALESMETHODSET = case when [EVENTSALESMETHOD].[ID] is null then 0 else 1 end,
                        @ONSALEDATETIME = [EVENTSALESMETHOD].[ONSALEDATETIME],
                        @OFFSALEDATETIME = [EVENTOFFSALESDATETIME].[TIME],
                        @ISONSALE = case
                            when 
                                    [PROGRAM].[ID] is null or --Special event
                                    @CURRENTDATETIMEOFFSET >= [EVENTSALESMETHOD].[ONSALEDATETIMEWITHOFFSET] --Preregistered event
                                then 1
                            else 0
                        end
                    from dbo.[EVENT]
                    outer apply (
                        select 
                            case when MICROSITEPAGE.SITEPAGESID is not null then 1 else 0 end as [ISONLINE],
                            dbo.UFN_BBNC_URL() + [VanityURL].[VanityURL] as [URL]
                        from dbo.[MICROSITEPAGE]
                        inner join dbo.VanityURL on
                            MICROSITEPAGE.SITEPAGESID = VanityURL.PageID
                        where MICROSITEPAGE.OBJECTID = EVENT.ID and MICROSITEPAGE.EXCLUDED = 0
                    ) as [ONLINEINFO]
                    left join dbo.EVENTSALESMETHOD 
                        on 
                            [EVENT].ID = EVENTSALESMETHOD.EVENTID and 
                            [EVENTSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
                    left join dbo.[PROGRAM]
                        on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                    left join dbo.PROGRAMSALESMETHOD 
                        on 
                            [PROGRAM].[ID] = PROGRAMSALESMETHOD.PROGRAMID and 
                            [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
                    outer apply (
                        select
                            case 
                                when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] is not null then
                                    case [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] 
                                        when 0 then [EVENT].[STARTDATETIME]
                                        when 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                        when 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                        when 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIME])
                                    end
                                else [EVENT].[STARTDATETIME]
                            end as [TIME]
                    ) [EVENTOFFSALESDATETIME]
                    where [EVENT].[ID] = @ID;

                    set @ISLIVE = case when @ISONLINE = 1 and @ISACTIVE = 1 then 1 else 0 end

                    declare @EXPIRATIONDATE datetime = null
                    if @EVENTTYPECODE = 0
                    begin
                        select @CAPACITYRESERVED = count(REGISTRANT.ID)
                        from dbo.[REGISTRANT]
                        where 
                            [REGISTRANT].[EVENTID] = @ID and
                            [REGISTRANT].[ISCANCELLED] = 0 and
                            [REGISTRANT].[WILLNOTATTEND] = 0

                        set @AVAILABLECAPACITY = @CAPACITY - @CAPACITYRESERVED

                        --Calculate expiration from end of start date
                        if @STARTDATE > @ENDDATE
                        begin
                            set @ENDDATE = @STARTDATE
                            set @EXPIRATIONDATE = dbo.UFN_DATE_GETLATESTTIME(@STARTDATE)
                        end
                        else if @ENDTIME = ''
                            set @EXPIRATIONDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)
                        else
                            set @EXPIRATIONDATE = @ENDDATE
                    end
                    else if @EVENTTYPECODE = 1
                    begin
                        select @AVAILABLECAPACITY = AVAILABILITY
                        from dbo.V_PROGRAMEVENT_TICKETCOUNTS
                        where ID = @ID

                        set @EXPIRATIONDATE = @OFFSALEDATETIME
                    end

                    if @AVAILABLECAPACITY < 0
                        set @AVAILABLECAPACITY = 0

                    declare @EXPIRATIONDATEWITHOFFSET datetimeoffset = null
                    set @EXPIRATIONDATEWITHOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@EXPIRATIONDATE, 0);

                    set @ISEXPIRED = case when @EXPIRATIONDATEWITHOFFSET < @CURRENTDATETIMEOFFSET then 1 else 0 end

                    return 0;
                end