USP_SEARCHLIST_EVENTSWORKCENTEREVENT

CREATE procedure dbo.USP_SEARCHLIST_EVENTSWORKCENTEREVENT
(
    @NAME nvarchar(100) = null,
    @FROMDATE datetime = null,
    @TODATE datetime = null,
    @LOCATION nvarchar(100) = null,
    @CATEGORYCODEID uniqueidentifier = null,
    @MAXROWS smallint = 500,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null
)
as

    set nocount on;

    --set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE)

    --set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE)


    select distinct top(@MAXROWS)
        EVENT.ID,
        EVENT.NAME,
        EVENT.LOOKUPID,
        EVENT.STARTDATE,
        EVENT.ENDDATE,
        NF.NAME as CONTACT,
        coalesce((select dbo.UDA_BUILDLIST(SITE.NAME) from dbo.SITE inner join dbo.EVENTSITE on EVENTSITE.SITEID = SITE.ID where EVENTSITE.EVENTID = EVENT.ID),'') as SITE,
        case
            when EVENT.PROGRAMID is not null then dbo.UFN_PROGRAM_GETLOCATIONNAME(EVENT.PROGRAMID)
            else dbo.UFN_EVENTLOCATION_GETNAME(EVENT.EVENTLOCATIONID)
        end as LOCATION,
        case
            when PROGRAM.ID is not null then dbo.UFN_PROGRAMCATEGORYCODE_GETDESCRIPTION(PROGRAM.PROGRAMCATEGORYCODEID)
            else dbo.UFN_EVENTCATEGORYCODE_GETDESCRIPTION(EVENT.EVENTCATEGORYCODEID)
        end as CATEGORYCODE,
        case 
            when EVENTAUCTION.ID is not null then 5 -- Auction

            when EVENT.PROGRAMID is not null then 10 -- Program event

            else 1 -- Event

        end as EVENTTYPECODE,
        case
            when (select count(INNEREVENT.ID) from dbo.EVENT as INNEREVENT where INNEREVENT.MAINEVENTID = EVENT.ID) > 0 or (select count(EVENTHIERARCHY.ID) from dbo.EVENTHIERARCHY inner join dbo.EVENT as INNEREVENT on INNEREVENT.ID = EVENTHIERARCHY.ID where EVENTHIERARCHY.ID = EVENT.ID and INNEREVENT.MAINEVENTID is null) > 0
            then 1 else 0 end as ISMULTIPART --Taken from USP_DATAFORMTEMPLATE_LOAD_EVENTISMULTIPART

    from dbo.EVENT 
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EVENT.EVENTLOCATIONCONTACTID) NF
        left outer join dbo.EVENTLOCATION on EVENT.EVENTLOCATIONID = EVENTLOCATION.ID
        left outer join dbo.EVENTAUCTION on EVENT.ID = EVENTAUCTION.ID
        left outer join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
    where
        (    
            (@NAME is null)
            or
            (EVENT.NAME like ('%' + @NAME + '%'))
        )
        and
        ( 
            (@FROMDATE is null and @TODATE is null)
            or
            (not (EVENT.STARTDATE > @TODATE or EVENT.ENDDATE < @FROMDATE))
        )                        
        and
        (
            (@LOCATION is null)
            or
            (EVENTLOCATION.NAME like ('%' + @LOCATION + '%'))
            or
            (EVENT.PROGRAMID is not null and dbo.UFN_PROGRAM_GETLOCATIONNAME(EVENT.PROGRAMID) like ('%' + @LOCATION + '%'))
        ) 
        and
        (
            (@CATEGORYCODEID is null)
            or
            (@CATEGORYCODEID = EVENT.EVENTCATEGORYCODEID)
            or
            (@CATEGORYCODEID = PROGRAM.PROGRAMCATEGORYCODEID)
        )
        and
        (

            (dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1)
        )
        and
        (
            @SITEFILTERMODE = 0
            or 
            (exists (select 1 from dbo.EVENTSITE with (nolock)
                    inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) 
                        on UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = EVENTSITE.SITEID  
                    where EVENTSITE.EVENTID = EVENT.ID))
        )

        union all

    select distinct top(@MAXROWS)
        PROGRAM.ID,
        PROGRAM.NAME,
        null as LOOKUPID,
        null as STARTDATE,
        null as ENDDATE,
        null as CONTACT,
        null as SITE,
        dbo.UFN_EVENTLOCATION_GETNAME(PROGRAMLOCATION.EVENTLOCATIONID) as LOCATION,
        dbo.UFN_PROGRAMCATEGORYCODE_GETDESCRIPTION(PROGRAM.PROGRAMCATEGORYCODEID)as CATEGORYCODE,
        0 as EVENTTYPECODE, -- Daily admission

        0 as ISMULTIPART
    from dbo.PROGRAM 
        left outer join dbo.PROGRAMLOCATION on PROGRAMLOCATION.PROGRAMID = PROGRAM.ID
        left outer join dbo.EVENTLOCATION on PROGRAMLOCATION.EVENTLOCATIONID = EVENTLOCATION.ID
    where
        PROGRAM.ISDAILYADMISSION = 1
        and
        (    
            (@NAME is null)
            or
            (PROGRAM.NAME like ('%' + @NAME + '%'))
        )
        and
        (
            (@LOCATION is null)
            or
            (EVENTLOCATION.NAME like ('%' + @LOCATION + '%'))
        ) 
        and
        (
            (@CATEGORYCODEID is null)
            or
            (@CATEGORYCODEID = PROGRAM.PROGRAMCATEGORYCODEID)
        )
        and
        (
            @SITEFILTERMODE = 0
        )

        order by EVENTTYPECODE, STARTDATE;