USP_DATAFORMTEMPLATE_VIEW_COMBINATIONONLINE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_COMBINATIONONLINE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(100) = null output,
    @PUBLICDESCRIPTION nvarchar(max) = null output,
    @EVENTSSAMEDAY bit = null output,
    @ISACTIVE bit = null output,
    @CURRENTLYAVAILABLEFORSALEONLINE bit = null output,
    @PROGRAMGROUPS xml = null output,
    @ACKNOWLEDGEMENTEMAILTEMPLATEID integer = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    -- Online sales method
    declare @SALESMETHODID uniqueidentifier = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(2);

    declare @ONSALEHORIZON int;
    select 
        @ONSALEHORIZON = ONSALEHORIZON
    from dbo.PROGRAMTICKETPARTCONTENT;

    select 
        @DATALOADED = 1,
        @NAME = COMBINATION.NAME,
        @PUBLICDESCRIPTION = COMBINATION.PUBLICDESCRIPTION,
        @EVENTSSAMEDAY = COMBINATION.EVENTSSAMEDAY,
        @ISACTIVE = COMBINATION.ISACTIVE,
        @CURRENTLYAVAILABLEFORSALEONLINE = dbo.UFN_COMBINATION_AVAILABLEFORORDER(@ID, @SALESMETHODID),
        @ACKNOWLEDGEMENTEMAILTEMPLATEID = COMBINATION_MICROSITEEMAILTEMPLATE.EMAILTEMPLATEID
    from dbo.COMBINATION
    left outer join dbo.COMBINATION_MICROSITEEMAILTEMPLATE
        on COMBINATION.ID = COMBINATION_MICROSITEEMAILTEMPLATE.COMBINATIONID
        and COMBINATION_MICROSITEEMAILTEMPLATE.ACTIVE = 1
    where COMBINATION.ID = @ID;

    declare @CURRENTDATETIMEWITHOFFSET datetimeoffset = sysdatetimeoffset();    
    declare @ENDDATE datetime = dbo.UFN_DATE_GETLATESTTIME(DATEADD(dd, @ONSALEHORIZON, getDate()));
    declare @ENDDATETIMEWITHOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@ENDDATE, 0);

    declare @PROGRAMGROUP table (
        ID uniqueidentifier primary key clustered,
        NAME nvarchar(100),
        PUBLICDESCRIPTION nvarchar(max),
        IDSETREGISTERID uniqueidentifier,
        ALLOWSCONFLICTS bit
    );

    insert into @PROGRAMGROUP
        (ID, NAME, PUBLICDESCRIPTION, IDSETREGISTERID, ALLOWSCONFLICTS)
    select
        PROGRAMGROUP.ID,
        PROGRAMGROUP.NAME,
        PROGRAMGROUP.PUBLICDESCRIPTION,
        PROGRAMGROUP.IDSETREGISTERID,
        PROGRAMGROUP.ALLOWSCONFLICTS
    from dbo.PROGRAMGROUP
    where PROGRAMGROUP.COMBINATIONID = @ID;

    declare @PROGRAMGROUPPROGRAMS table (
        ID uniqueidentifier,
        PROGRAMGROUPID uniqueidentifier,
        PROGRAMID uniqueidentifier,
        PROGRAMISACTIVE bit,
        PROGRAMISPREREGISTERED bit,
        ISDAILYADMISSION bit,
        EVENTID uniqueidentifier,
        NAME nvarchar(100),
        PUBLICDESCRIPTION nvarchar(max),
        DATEADDED datetime
    );

    insert into @PROGRAMGROUPPROGRAMS
        (PROGRAMGROUPID, PROGRAMID, PROGRAMISACTIVE, PROGRAMISPREREGISTERED, ISDAILYADMISSION, NAME, PUBLICDESCRIPTION, DATEADDED)
    select
        PROGRAMGROUPPROGRAM.PROGRAMGROUPID,
        PROGRAMGROUPPROGRAM.PROGRAMID,
        PROGRAM.ISACTIVE as PROGRAMISACTIVE,
        PROGRAM.ISPREREGISTERED as PROGRAMISPREREGISTERED,
        PROGRAM.ISDAILYADMISSION,
        PROGRAM.NAME,
        PROGRAM.PUBLICDESCRIPTIONHTML as PUBLICDESCRIPTION,        
        PROGRAMGROUPPROGRAM.DATEADDED
    from dbo.PROGRAMGROUP
    inner join dbo.PROGRAMGROUPPROGRAM
        on PROGRAMGROUP.ID = PROGRAMGROUPPROGRAM.PROGRAMGROUPID
    inner join dbo.PROGRAM 
        on PROGRAMGROUPPROGRAM.PROGRAMID = PROGRAM.ID        
    where PROGRAMGROUP.COMBINATIONID = @ID
        and PROGRAMGROUP.IDSETREGISTERID is null
        and (
            -- Scheduled events        
            (
                PROGRAM.ISDAILYADMISSION = 0 
                and PROGRAM.ISPREREGISTERED = 0 
            ) or
            -- Daily admissions
            (
                [PROGRAM].[ISDAILYADMISSION] = 1 
                and exists 
                    ( --Can be sold today and with this sales method
                        select 
                            [PROGRAMSALESMETHOD].[PROGRAMID]
                        from 
                            dbo.[PROGRAMSALESMETHOD]
                        where
                            [PROGRAMSALESMETHOD].[PROGRAMID] =  [PROGRAM].[ID] and
                            [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID 
                    )                             
            )
        );


    insert into @PROGRAMGROUPPROGRAMS
        (PROGRAMGROUPID, PROGRAMID, PROGRAMISACTIVE, PROGRAMISPREREGISTERED, ISDAILYADMISSION, NAME, PUBLICDESCRIPTION, DATEADDED)
    select
        PROGRAMGROUPPROGRAM.PROGRAMGROUPID,
        PROGRAMGROUPPROGRAM.PROGRAMID,
        PROGRAM.ISACTIVE as PROGRAMISACTIVE,
        PROGRAM.ISPREREGISTERED as PROGRAMISPREREGISTERED,
        PROGRAM.ISDAILYADMISSION,
        PROGRAM.NAME,
        PROGRAM.PUBLICDESCRIPTIONHTML as PUBLICDESCRIPTION,
        PROGRAMGROUPPROGRAM.DATEADDED
    from dbo.PROGRAMGROUP
    inner join dbo.PROGRAMGROUPPROGRAM
        on PROGRAMGROUP.ID = PROGRAMGROUPPROGRAM.PROGRAMGROUPID
    cross apply dbo.UFN_IDSETREADER_GETRESULTS_GUID(PROGRAMGROUP.IDSETREGISTERID) as AVAILABLEPROGRAMS
    inner join dbo.PROGRAM 
        on AVAILABLEPROGRAMS.ID = PROGRAM.ID
    where PROGRAMGROUP.COMBINATIONID = @ID
        and PROGRAMGROUP.IDSETREGISTERID is not null
        -- Scheduled events
        and (
            (
                [PROGRAM].[ISDAILYADMISSION] = 0 
                and [PROGRAM].[ISPREREGISTERED] = 0 
            ) 
            -- Daily admissions
            or (
                [PROGRAM].[ISDAILYADMISSION] = 1 
                and exists 
                    ( --Can be sold today and with this sales method
                        select 
                            [PROGRAMSALESMETHOD].[PROGRAMID]
                        from 
                            dbo.[PROGRAMSALESMETHOD]
                        where
                            [PROGRAMSALESMETHOD].[PROGRAMID] =  [PROGRAM].[ID] and
                            [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID 
                    )                 
            )
        );

    -- If you have one or more scheduled programs
    --  ensure a day exists from now until the OnSaleHorizon
    --  where at least one event exists for each scheduled program with Availability > 0
    --  that can be sold today
    if exists(select 1 from @PROGRAMGROUPPROGRAMS where ISDAILYADMISSION = 0)
        begin
            declare @EVENTS table (
                ID uniqueidentifier,
                PROGRAMID uniqueidentifier,
                NAME nvarchar(100),
                SUPERCEDESPROGRAMPUBLICDESCRIPTION bit,
                PUBLICDESCRIPTIONHTML nvarchar(max),            
                EVENTDATE date,
                STARTTIME dbo.UDT_HOURMINUTE,
                ENDTIME dbo.UDT_HOURMINUTE,
                CAPACITY integer,
                AVAILABILITY integer
            );

            insert into @EVENTS
                (ID, PROGRAMID, NAME, SUPERCEDESPROGRAMPUBLICDESCRIPTION, PUBLICDESCRIPTIONHTML, EVENTDATE, STARTTIME, ENDTIME, CAPACITY, AVAILABILITY)
                select
                    [EVENT].ID,
                    [EVENT].PROGRAMID,
                    [EVENT].NAME,
                    [EVENT].SUPERCEDESPROGRAMPUBLICDESCRIPTION,
                    [EVENT].PUBLICDESCRIPTIONHTML,            
                    [EVENT].STARTDATE as EVENTDATE,
                    [EVENT].STARTTIME,
                    [EVENT].ENDTIME,             
                    coalesce([EVENT].CAPACITY, 0) as CAPACITY,
                    EVENTAVAILABILITY.QUANTITY as AVAILABILITY
                from dbo.UFN_EVENT_GETAVAILABILITYWITHEVENTS_BYDATE(4, @CURRENTDATETIMEWITHOFFSET, @ENDDATETIMEWITHOFFSET, @CURRENTDATETIMEWITHOFFSET, @SALESMETHODID, 0, convert(date, @CURRENTDATETIMEWITHOFFSET), convert(date, @ENDDATETIMEWITHOFFSET), getDate()) EVENTAVAILABILITY
                inner join dbo.[EVENT]
                    on EVENTAVAILABILITY.EVENTID = [EVENT].ID
                where [EVENT].ISACTIVE = 1
                    and [EVENT].PROGRAMID is not null
                    and EVENTAVAILABILITY.QUANTITY > 0
                    and EVENTAVAILABILITY.AVAILABLEFORSALE = 1;

           create table #PROGRAMGROUPPROGRAMEVENTS_CTE (
                PROGRAMGROUPID uniqueidentifier,
                ISDAILYADMISSION bit,
                ID uniqueidentifier,
                EVENTDATE date,
                AVAILABILITY integer,
                unique clustered (PROGRAMGROUPID,ID)
            );

            -- Find first date that all program groups have at least 1 event with ISDAILYADMISSION = 1 or availability > 0
            declare @FIRSTAVAILABLEDATE datetime = null;  

            insert into #PROGRAMGROUPPROGRAMEVENTS_CTE
            select
                PROGRAMGROUPPROGRAM.PROGRAMGROUPID,
             PROGRAMGROUPPROGRAM.ISDAILYADMISSION,            
                [EVENTS].ID,
                [EVENTS].EVENTDATE,
                [EVENTS].AVAILABILITY
            from @PROGRAMGROUPPROGRAMS PROGRAMGROUPPROGRAM
            left outer join @EVENTS [EVENTS] 
                on PROGRAMGROUPPROGRAM.PROGRAMID = [EVENTS].PROGRAMID 
            left join dbo.[EVENT_MICROSITEEMAILTEMPLATE] on
                ([EVENTS].ID = EVENT_MICROSITEEMAILTEMPLATE.EVENTID) and (EVENT_MICROSITEEMAILTEMPLATE.ACTIVE = 1)

            select
                @FIRSTAVAILABLEDATE = min(EVENTDATE)
            from #PROGRAMGROUPPROGRAMEVENTS_CTE PROGRAMGROUPPROGRAMEVENTS
            where not exists(
                select 1
                from @PROGRAMGROUP PROGRAMGROUPS
                left outer join #PROGRAMGROUPPROGRAMEVENTS_CTE PROGRAMGROUPPROGRAMEVENTS2
                    on PROGRAMGROUPS.ID = PROGRAMGROUPPROGRAMEVENTS2.PROGRAMGROUPID
                    and (
                        PROGRAMGROUPPROGRAMEVENTS2.ISDAILYADMISSION = 1 
                        or (
                            PROGRAMGROUPPROGRAMEVENTS2.EVENTDATE = PROGRAMGROUPPROGRAMEVENTS.EVENTDATE
                            and PROGRAMGROUPPROGRAMEVENTS2.AVAILABILITY > 0)
                    )
                where PROGRAMGROUPPROGRAMEVENTS2.PROGRAMGROUPID is null
            );

            -- No date with the OnSaleHorizon where combination is available for sale
            if @CURRENTLYAVAILABLEFORSALEONLINE = 1 and @FIRSTAVAILABLEDATE is null
                set @CURRENTLYAVAILABLEFORSALEONLINE = 0;

       end

    -- Assumes only 1 program per program group
    set @PROGRAMGROUPS = (
        select
            PROGRAMGROUPS.ID, 
            PROGRAMGROUPS.NAME, 
            PROGRAMGROUPS.PUBLICDESCRIPTION, 
            PROGRAMGROUPS.IDSETREGISTERID, 
            PROGRAMGROUPS.ALLOWSCONFLICTS,
            (
                select top 1
                    PROGRAMGROUPPROGRAMS.ID, 
                    PROGRAMGROUPPROGRAMS.PROGRAMID, 
                    PROGRAMGROUPPROGRAMS.PROGRAMISACTIVE,
                    PROGRAMGROUPPROGRAMS.PROGRAMISPREREGISTERED,
                    PROGRAMGROUPPROGRAMS.NAME,
                    PROGRAMGROUPPROGRAMS.PUBLICDESCRIPTION,
                    PROGRAMGROUPPROGRAMS.ISDAILYADMISSION                    
                from @PROGRAMGROUPPROGRAMS PROGRAMGROUPPROGRAMS                
                where PROGRAMGROUPPROGRAMS.PROGRAMGROUPID = PROGRAMGROUPS.ID                    
                order by PROGRAMGROUPPROGRAMS.DATEADDED asc
                for xml raw ('ITEM'), type, elements, root('PROGRAMGROUPPROGRAMS'), BINARY BASE64
            )    
        from @PROGRAMGROUP PROGRAMGROUPS
        for xml raw ('ITEM'), type, elements, root('PROGRAMGROUPS'), BINARY BASE64
    )
    return 0;