UFN_COMBINATION_PROGRAMSAVAILABLE

CREATE function [dbo].[UFN_COMBINATION_PROGRAMSAVAILABLE]
(
    @COMBINATIONID uniqueidentifier,
    @SALESMETHODID uniqueidentifier,
    @DATESELECTTYPE tinyint,
    @STARTDATE datetime,
    @ENDDATE datetime,
    @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 @CURRENTDATE datetime = getdate();

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() as AVAILABLE on EVENT.ID = AVAILABLE.EVENTID
                        where 
                            [PROGRAM].[ISDAILYADMISSION] = 0 and
                            [PROGRAM].[ISACTIVE] = 1 and
                            [EVENT].[ISACTIVE] = 1 and
                            ( 
                                ((@DATESELECTTYPE = 0 and [EVENT].[STARTDATETIME] >= @CURRENTDATE) 
                                or (@DATESELECTTYPE <> 0 and 
                                    ([EVENT].[STARTDATETIME] between @STARTDATE and @ENDDATE or 
                                    (@STARTDATE between [EVENT].[STARTDATETIME] and [EVENT].[ENDDATETIME]))))
                            )
                            and exists ( --Can be sold today and with this sales method

                                        select [EVENTSALESMETHOD].[EVENTID]
                                        from dbo.[EVENTSALESMETHOD]
                                        inner join dbo.[EVENT] on [EVENTSALESMETHOD].[EVENTID] = [EVENT].[ID]
                                        inner join dbo.[PROGRAMSALESMETHOD] on [EVENT].[PROGRAMID] = [PROGRAMSALESMETHOD].[PROGRAMID]
                                        where
                                          [EVENTSALESMETHOD].[EVENTID] =  [EVENT].[ID] and
                                          [EVENTSALESMETHOD].[SALESMETHODID] = @SALESMETHODID and
                                          @CURRENTDATE >= [EVENTSALESMETHOD].[ONSALEDATETIME] and
                                          @CURRENTDATE <= 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
                                      ) 
                    ) 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
                      @STARTDATE <= @ENDDATE
                    )
) 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