UFN_COMBINATION_PROGRAMSAVAILABLE_DATETIMEOFFSET

CREATE function [dbo].[UFN_COMBINATION_PROGRAMSAVAILABLE_DATETIMEOFFSET]
(
    @COMBINATIONID uniqueidentifier,
    @SALESMETHODID uniqueidentifier,
    @DATESELECTTYPE tinyint,
    @STARTDATETIMEWITHOFFSET datetimeoffset,
    @ENDDATETIMEWITHOFFSET datetimeoffset,
    @SHOWPAST bit
)
returns bit
with execute as caller
as begin

-- if there is no program groups in the combination, return 0

if not exists (
  select PROGRAMGROUP.ID 
  from dbo.PROGRAMGROUP 
  where PROGRAMGROUP.COMBINATIONID = @COMBINATIONID)
  return 0;

declare @SAMEDAYEVENTS bit = 0;
declare @CURRENTDATETIMEWITHOFFSET datetimeoffset = sysdatetimeoffset();

select @SAMEDAYEVENTS = EVENTSSAMEDAY
from dbo.COMBINATION where ID = @COMBINATIONID

-- Program groups that have only scheduled events

-- These are the program groups that need to be checked

-- if the available events are on the same day

declare @PROGRAMGROUP table (
  ID uniqueidentifier
)

insert into @PROGRAMGROUP
select PROGRAMGROUP.ID
from PROGRAMGROUP where PROGRAMGROUP.COMBINATIONID = @COMBINATIONID
and NOT exists (
    select PROGRAMGROUP.ID 
    from dbo.PROGRAMGROUPPROGRAM
        inner join dbo.PROGRAM on PROGRAMGROUPPROGRAM.PROGRAMID = PROGRAM.ID
    where PROGRAMGROUPPROGRAM.PROGRAMGROUPID = PROGRAMGROUP.ID 
        and PROGRAM.ISDAILYADMISSION = 1
)
and NOT exists (
    select PROGRAMGROUP.ID 
    from dbo.UFN_IDSETREADER_GETRESULTS_GUID(PROGRAMGROUP.IDSETREGISTERID) as PROGRAMGROUPPROGRAM
        inner join dbo.PROGRAM on PROGRAMGROUPPROGRAM.ID = PROGRAM.ID
    where PROGRAM.ISDAILYADMISSION = 1
)

-- Number of events that occur on the same day

declare @PROGRAMGROUPCOUNTS int
if @SAMEDAYEVENTS = 1
    select @PROGRAMGROUPCOUNTS = isnull(count(ID), 0)
    from @PROGRAMGROUP
else
    set @PROGRAMGROUPCOUNTS = 0

-- table variable to save the programs from the selection

declare    @AVAILABLEPROGRAMS table (
    PROGRAMGROUPID uniqueidentifier,
    PROGRAMID uniqueidentifier
)

insert into @AVAILABLEPROGRAMS
select PROGRAMGROUP.ID, SELECTION.ID
from dbo.PROGRAMGROUP
cross apply dbo.UFN_IDSETREADER_GETRESULTS_GUID(PROGRAMGROUP.IDSETREGISTERID) AS SELECTION

-- Check if selection returns anything

if exists (select PROGRAMGROUP.ID
  from dbo.PROGRAMGROUP
  where (PROGRAMGROUP.IDSETREGISTERID is not null) and 
        PROGRAMGROUP.ID not in (select PROGRAMGROUPID from @AVAILABLEPROGRAMS) and 
        PROGRAMGROUP.COMBINATIONID = @COMBINATIONID
  )
  return 0;

insert into @AVAILABLEPROGRAMS
select PROGRAMGROUP.ID as PROGRAMGROUPID, PROGRAMGROUPPROGRAM.PROGRAMID as PROGRAMID
from dbo.PROGRAMGROUP
    inner join dbo.PROGRAMGROUPPROGRAM on PROGRAMGROUP.ID = PROGRAMGROUPPROGRAM.PROGRAMGROUPID 
where PROGRAMGROUP.COMBINATIONID = @COMBINATIONID and (PROGRAMGROUP.IDSETREGISTERID is null);

-- Check availability of the programs in the combination

if exists(
    select ID 
    from @PROGRAMGROUP 
    where ID not in 
        (
            select PROGRAMGROUP.ID 
            from dbo.PROGRAMGROUP 
            where PROGRAMGROUP.COMBINATIONID = @COMBINATIONID and
            (
                exists 
                (
                    select EVENTDATE, count(PROGRAMGROUPID)
                    from
                    (
                        select distinct EVENT.STARTDATE as EVENTDATE, PROGRAMGROUPID
                        from @AVAILABLEPROGRAMS as AVAILABLEPROGRAMS
                            inner join dbo.PROGRAM on AVAILABLEPROGRAMS.PROGRAMID = PROGRAM.ID
                            inner join dbo.[EVENT] on [EVENT].[PROGRAMID] = AVAILABLEPROGRAMS.[PROGRAMID]
                            inner join dbo.UFN_EVENT_GETAVAILABILITYWITHEVENTS_BYDATE(
                                @DATESELECTTYPE,
                                @STARTDATETIMEWITHOFFSET,
                                @ENDDATETIMEWITHOFFSET,
                                @CURRENTDATETIMEWITHOFFSET,
                                @SALESMETHODID,@SHOWPAST,
                                cast(@STARTDATETIMEWITHOFFSET as date), 
                                cast(@ENDDATETIMEWITHOFFSET as date),
                                cast(@CURRENTDATETIMEWITHOFFSET as datetime)
                            ) as AVAILABLE on EVENT.ID = AVAILABLE.EVENTID
                        where 
                            [PROGRAM].[ISACTIVE] = 1 and
                            [EVENT].[ISACTIVE] = 1
                    ) as PROGRAMGROUPAVAILABLEDATES
                where EVENTDATE is not null
                    group by (EVENTDATE)
                    having count(PROGRAMGROUPID) >= @PROGRAMGROUPCOUNTS
                ) 
            )
        )
)
    return 0;

-- Check availability of daily admission only program group

if not exists(
            select PROGRAMGROUPID
            from @AVAILABLEPROGRAMS as AVAILABLEPROGRAMS
            inner join dbo.PROGRAM on AVAILABLEPROGRAMS.PROGRAMID = PROGRAM.ID
            inner join dbo.[PROGRAMSALESMETHOD] on [PROGRAMSALESMETHOD].[PROGRAMID] = [PROGRAM].[ID]
            where 
                [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID and
                [PROGRAM].[ISDAILYADMISSION] = 1 and
                [PROGRAM].[ISACTIVE] = 1 and
                AVAILABLEPROGRAMS.PROGRAMGROUPID not in (
                            select PROGRAMGROUPID
                            from @AVAILABLEPROGRAMS as PROGRAMS
                              inner join dbo.PROGRAM on PROGRAMS.PROGRAMID = PROGRAM.ID
                            where [PROGRAM].[ISDAILYADMISSION] = 0
              ) and
                    (
                      @DATESELECTTYPE = 0 or
                      @STARTDATETIMEWITHOFFSET <= @ENDDATETIMEWITHOFFSET
                    )
) and exists (
            select PROGRAMGROUPID
            from @AVAILABLEPROGRAMS as AVAILABLEPROGRAMS
                inner join dbo.PROGRAM on AVAILABLEPROGRAMS.PROGRAMID = PROGRAM.ID
            where 
                [PROGRAM].[ISDAILYADMISSION] = 1 and
                AVAILABLEPROGRAMS.PROGRAMGROUPID not in (
                            select PROGRAMGROUPID
                            from @AVAILABLEPROGRAMS as PROGRAMS
                              inner join dbo.PROGRAM on PROGRAMS.PROGRAMID = PROGRAM.ID
                            where [PROGRAM].[ISDAILYADMISSION] = 0
              )
)
  return 0;

return 1;

end