USP_DATALIST_COMBINATIONAVAILABLEDATES

CREATE procedure dbo.USP_DATALIST_COMBINATIONAVAILABLEDATES
(
    @COMBINATIONID uniqueidentifier = null,
    @SALESMETHODTYPECODE tinyint = null,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null
)
as
    set nocount on;

    -- Default to Online sales

    if @SALESMETHODTYPECODE is null
        set @SALESMETHODTYPECODE = 2;

    declare @SALESMETHODID uniqueidentifier;
    set @SALESMETHODID = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(@SALESMETHODTYPECODE);

    declare @CURRENTDATETIMEWITHOFFSET datetimeoffset = sysdatetimeoffset(); 
    declare @STARTDATETIMEWITHOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@STARTDATE, 0);
    declare @ENDDATETIMEWITHOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@ENDDATE, 0);

    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

    declare @HASDELIVERYMETHOD bit;
    declare @HASUNRESTRICTEDDELIVERY bit;
    declare @EARLIESTVALIDEVENTTIMEWITHOFFSET datetimeoffset(3) = null;

    select 
        @HASDELIVERYMETHOD = [HASDELIVERYMETHOD],
        @HASUNRESTRICTEDDELIVERY = [HASUNRESTRICTEDDELIVERY], 
        @EARLIESTVALIDEVENTTIMEWITHOFFSET = [EARLIESTVALIDEVENTDATETIMEWITHOFFSET]
    from dbo.UFN_DELIVERYMETHOD_INFO_BYSALESMETHODID(@SALESMETHODID);

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

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

    declare @EVENTS table (
        ID uniqueidentifier,
        PROGRAMID uniqueidentifier,
        NAME nvarchar(100),
        EVENTDATE date,
        STARTTIME dbo.UDT_HOURMINUTE,
        ENDTIME dbo.UDT_HOURMINUTE,
        STARTDATETIMEWITHOFFSET datetimeoffset,
        ENDDATETIMEWITHOFFSET datetimeoffset,
        CAPACITY integer,
        AVAILABILITY integer
    );

    insert into @EVENTS
        (ID, PROGRAMID, NAME, EVENTDATE, STARTTIME, ENDTIME, STARTDATETIMEWITHOFFSET, ENDDATETIMEWITHOFFSET, CAPACITY, AVAILABILITY)
        select
            [EVENT].ID,
            [EVENT].PROGRAMID,
            [EVENT].NAME,
            [EVENT].STARTDATE as EVENTDATE,
            [EVENT].STARTTIME,
            [EVENT].ENDTIME,
            [EVENT].STARTDATETIMEWITHOFFSET,
            [EVENT].ENDDATETIMEWITHOFFSET,
            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 EVENTAVAILABILITY.QUANTITY > 0
            and (
                @HASDELIVERYMETHOD = 1
                and (
                    @HASUNRESTRICTEDDELIVERY =  1
                    or [EVENT].STARTDATETIMEWITHOFFSET > @EARLIESTVALIDEVENTTIMEWITHOFFSET
                )
            );

    create table #PROGRAMGROUPPROGRAMS (
        ID uniqueidentifier,
        PROGRAMGROUPID uniqueidentifier,
        PROGRAMID uniqueidentifier,
        PROGRAMISACTIVE bit,
        PROGRAMISPREREGISTERED bit,
        ISDAILYADMISSION bit,
        EVENTID uniqueidentifier,
        NAME nvarchar(100) collate DATABASE_DEFAULT,
        EVENTDATE date,
        STARTDATETIMEWITHOFFSET datetimeoffset(3),
        ENDDATETIMEWITHOFFSET datetimeoffset(3),
        CAPACITY integer,        
        AVAILABILITY integer,
        unique clustered (PROGRAMGROUPID,ID,EVENTID)
    );

    insert into #PROGRAMGROUPPROGRAMS
        (ID, PROGRAMGROUPID, PROGRAMID, PROGRAMISACTIVE, PROGRAMISPREREGISTERED, ISDAILYADMISSION, EVENTID, NAME, EVENTDATE, STARTDATETIMEWITHOFFSET, ENDDATETIMEWITHOFFSET, CAPACITY, AVAILABILITY)
    select
        PROGRAMGROUPPROGRAM.ID,
        PROGRAMGROUPPROGRAM.PROGRAMGROUPID,
        PROGRAMGROUPPROGRAM.PROGRAMID,
        PROGRAM.ISACTIVE as PROGRAMISACTIVE,
        PROGRAM.ISPREREGISTERED as PROGRAMISPREREGISTERED,
        PROGRAM.ISDAILYADMISSION,
        [EVENTS].ID as EVENTID,
        coalesce([EVENTS].NAME, PROGRAM.NAME) as NAME,
        [EVENTS].EVENTDATE,
        [EVENTS].STARTDATETIMEWITHOFFSET,
        [EVENTS].ENDDATETIMEWITHOFFSET,
        [EVENTS].CAPACITY,
        [EVENTS].AVAILABILITY
    from dbo.PROGRAMGROUP
    inner join dbo.PROGRAMGROUPPROGRAM
        on PROGRAMGROUP.ID = PROGRAMGROUPPROGRAM.PROGRAMGROUPID
    inner join dbo.PROGRAM 
        on PROGRAMGROUPPROGRAM.PROGRAMID = PROGRAM.ID    
    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)
    left join dbo.[PROGRAM_MICROSITEEMAILTEMPLATE] on
        (PROGRAM.ID = PROGRAM_MICROSITEEMAILTEMPLATE.PROGRAMID) and (PROGRAM_MICROSITEEMAILTEMPLATE.ACTIVE = 1)   
    where PROGRAMGROUP.COMBINATIONID = @COMBINATIONID
        and PROGRAMGROUP.IDSETREGISTERID is null
        and (
            -- Scheduled events        

            (
                PROGRAM.ISDAILYADMISSION = 0 
                and PROGRAM.ISPREREGISTERED = 0 
                and PROGRAM.ISACTIVE = 1 
            ) or
            -- Daily admissions

            (
                [PROGRAM].[ISDAILYADMISSION] = 1 
                and [PROGRAM].[ISACTIVE] = 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 
                    )                 
                and @CURRENTDATETIMEWITHOFFSET <= @ENDDATETIMEWITHOFFSET                
            )
        );


    insert into #PROGRAMGROUPPROGRAMS
        (ID, PROGRAMGROUPID, PROGRAMID, PROGRAMISACTIVE, PROGRAMISPREREGISTERED, ISDAILYADMISSION, EVENTID, NAME, EVENTDATE, STARTDATETIMEWITHOFFSET, ENDDATETIMEWITHOFFSET, CAPACITY, AVAILABILITY)
    select
        PROGRAMGROUPPROGRAM.ID,
        PROGRAMGROUPPROGRAM.PROGRAMGROUPID,
        PROGRAMGROUPPROGRAM.PROGRAMID,
        PROGRAM.ISACTIVE as PROGRAMISACTIVE,
        PROGRAM.ISPREREGISTERED as PROGRAMISPREREGISTERED,
        PROGRAM.ISDAILYADMISSION,
        [EVENTS].ID as EVENTID,
        coalesce([EVENTS].NAME, PROGRAM.NAME) as NAME,
        [EVENTS].EVENTDATE,
        [EVENTS].STARTDATETIMEWITHOFFSET,
        [EVENTS].ENDDATETIMEWITHOFFSET,
        [EVENTS].CAPACITY,
        [EVENTS].AVAILABILITY
    from dbo.PROGRAMGROUP
    inner join dbo.PROGRAMGROUPPROGRAM
        on PROGRAMGROUP.ID = PROGRAMGROUPPROGRAM.PROGRAMGROUPID
    cross apply dbo.UFN_IDSETREADER_GETRESULTS_GUID(PROGRAMGROUP.IDSETREGISTERID) as AVAILABLEPROGRAMS
        --on PROGRAMGROUPPROGRAM.PROGRAMID = AVAILABLEPROGRAMS.ID

    inner join dbo.PROGRAM 
        on AVAILABLEPROGRAMS.ID = PROGRAM.ID
    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
    left join dbo.PROGRAM_MICROSITEEMAILTEMPLATE 
        on PROGRAM.ID = PROGRAM_MICROSITEEMAILTEMPLATE.PROGRAMID 
        and PROGRAM_MICROSITEEMAILTEMPLATE.ACTIVE = 1
    where PROGRAMGROUP.COMBINATIONID = @COMBINATIONID
        and PROGRAMGROUP.IDSETREGISTERID is not null
        -- Scheduled events

        and (
            (
                [PROGRAM].[ISDAILYADMISSION] = 0 
                and [PROGRAM].[ISPREREGISTERED] = 0 
            ) 
            -- Daily admissions

            or (
                [PROGRAM].[ISDAILYADMISSION] = 1 
                and [PROGRAM].[ISACTIVE] = 1 
                and @CURRENTDATETIMEWITHOFFSET <= @ENDDATETIMEWITHOFFSET
                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 
                    )                 
            )
        );

    select distinct
        EVENTDATE as AVIALABLEDATE
    from #PROGRAMGROUPPROGRAMS PROGRAMGROUPPROGRAMS
    where not exists(
        select 1
        from @PROGRAMGROUP PROGRAMGROUPS
        left outer join #PROGRAMGROUPPROGRAMS PROGRAMGROUPPROGRAMS2
            on PROGRAMGROUPS.ID = PROGRAMGROUPPROGRAMS2.PROGRAMGROUPID
            and (
                PROGRAMGROUPPROGRAMS2.ISDAILYADMISSION = 1 
                or (
                    PROGRAMGROUPPROGRAMS2.EVENTDATE = PROGRAMGROUPPROGRAMS.EVENTDATE
                    and PROGRAMGROUPPROGRAMS2.AVAILABILITY > 0)
            )
        where PROGRAMGROUPPROGRAMS2.PROGRAMGROUPID is null
    );