USP_DATALIST_VOLUNTEERMATCHES_CALENDAR

CREATE procedure dbo.USP_DATALIST_VOLUNTEERMATCHES_CALENDAR 
(
    @VOLUNTEERID uniqueidentifier = null, 
    @JOBOCCURENCEID uniqueidentifier = null,
    @LOCATIONID uniqueidentifier = null,
    @STARTDATE date = null, 
    @ENDDATE date = null, 
    @INCLUDEINACTIVE bit = 0,
    @CURRENTAPPUSERID uniqueidentifier = null
)
as
    set nocount on;

    declare @TODAY datetime;

    /*CONSTANTS FOR MATCHING*/
    set @TODAY = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    /*TABLE CLEANUP */
    if not object_id('tempdb..#AVAILABLE') is null  
        drop table #AVAILABLE
    if not object_id('tempdb..#TRAITS') is null  
        drop table #TRAITS

    declare @DAYLIST nvarchar(max) = '';
    declare @JOB_STARTDATE datetime
    declare @JOB_ENDDATE datetime;
    declare @STARTTIME dbo.UDT_HOURMINUTE = '';
    declare @ENDTIME dbo.UDT_HOURMINUTE = '';

    select 
        @DAYLIST = stuff((select convert(varchar, DATEADD(d,NUM,@STARTDATE), 112) 
            from 
                dbo.NUMBERS 
            where 
                DATEADD(d,NUM,@STARTDATE) <= @ENDDATE
            for xml path(''), type).value('.', 'nvarchar(max)'), 1, 0, '')

    if len(@DAYLIST) = 0 or @DAYLIST = '00000000'
        return;

    /*FIND AVAILABLE*/
    select 
        VACANCIES.OCCURRENCEID, 
        JOBOCCURRENCE.JOBID, 
        AVAILABLE.AVAILABLEDATE STARTDATE
    into #AVAILABLE
    from dbo.[UFN_JOBOCCURRENCE_VACANTSPECIFICDAYS](@DAYLIST,null,null) VACANCIES 
    inner join dbo.JOBOCCURRENCE 
        on JOBOCCURRENCE.ID = VACANCIES.OCCURRENCEID
    inner join dbo.JOB
        on JOBOCCURRENCE.JOBID = JOB.ID
    inner join dbo.[UFN_VOLUNTEER_AVAILABLESPECIFICDAYS](@DAYLIST, @VOLUNTEERID, 0) AVAILABLE
        on AVAILABLE.AVAILABLEDATE = VACANCIES.ASSIGNMENTDATE
    left outer join dbo.VOLUNTEERASSIGNMENT 
        on VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = JOBOCCURRENCE.ID 
            and VOLUNTEERASSIGNMENT.DATE = AVAILABLE.AVAILABLEDATE
    left outer join dbo.VOLUNTEERVOLUNTEERTYPE VOLTYPE
        on VOLTYPE.VOLUNTEERID = @VOLUNTEERID AND VOLTYPE.STATUSCODE = 1
    left outer join dbo.VOLUNTEERASSIGNMENT VAS
        on AVAILABLE.VOLUNTEERID = VAS.VOLUNTEERID
            and VAS.DATE = VACANCIES.ASSIGNMENTDATE
    left outer join dbo.JOBOCCURRENCE ASSIGNEDJO
        on ASSIGNEDJO.ID = VAS.JOBOCCURRENCEID and
           (ASSIGNEDJO.STARTTIME between JOBOCCURRENCE.STARTTIME and JOBOCCURRENCE.ENDTIME
            or
           ASSIGNEDJO.ENDTIME between JOBOCCURRENCE.STARTTIME and JOBOCCURRENCE.ENDTIME
           or ASSIGNEDJO.STARTTIME = '' or ASSIGNEDJO.ENDTIME = ''
           or JOBOCCURRENCE.STARTTIME = '' or JOBOCCURRENCE.ENDTIME  = '')
    left outer join    dbo.VOLUNTEERVOLUNTEERLOCATION
        on AVAILABLE.VOLUNTEERID = VOLUNTEERVOLUNTEERLOCATION.VOLUNTEERID
    where 
        (@JOBOCCURENCEID is null or JOBOCCURRENCE.ID = @JOBOCCURENCEID) and
        (JOB.VOLUNTEERTYPEID is null or VOLTYPE.VOLUNTEERTYPEID = JOB.VOLUNTEERTYPEID) and
        (AVAILABLE.STARTTIME <= JOBOCCURRENCE.STARTTIME or JOBOCCURRENCE.STARTTIME = '')
        and
        (AVAILABLE.ENDTIME >= JOBOCCURRENCE.ENDTIME or AVAILABLE.ENDTIME = '')
        and 
        (@LOCATIONID is null or JOBOCCURRENCE.LOCATIONCODEID = @LOCATIONID)
        and
        (JOBOCCURRENCE.LOCATIONCODEID is null -- no location

            or VOLUNTEERVOLUNTEERLOCATION.ID is null --none

            or VOLUNTEERVOLUNTEERLOCATION.VOLUNTEERLOCATIONCODEID = JOBOCCURRENCE.LOCATIONCODEID)
        and AVAILABLE.AVAILABLEDATE >= @TODAY 
        and (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOBOCCURRENCE.SITEID) = 1)
        and (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOB.SITEID) = 1)
        and (@INCLUDEINACTIVE = 1 or JOB.ISACTIVE = 1)
    group by 
        VACANCIES.OCCURRENCEID, 
        JOBOCCURRENCE.JOBID, 
        AVAILABLE.AVAILABLEDATE
    having 
        max(case when ASSIGNEDJO.ID is null then 0 else 1 end) = 0;

    create index AVAILABLE_OCCURRENCEIDID on #AVAILABLE(OCCURRENCEID);

    /*Remove jobs that do not have all of the required special needs*/
    delete from #AVAILABLE
    where JOBID in 
        (select AVAIL.JOBID
         from #AVAILABLE AVAIL
         inner join VOLUNTEERSPECIALNEED VSN
            on VSN.VOLUNTEERID = @VOLUNTEERID
         left outer join dbo.JOBSPECIALNEED JSN
            on JSN.JOBID = AVAIL.JOBID and JSN.SPECIALNEEDCODEID = VSN.SPECIALNEEDCODEID
         where JSN.ID is null)

    /*FIND TRAITS*/
    select    
        AVAIL.OCCURRENCEID,
        AVAIL.JOBID,
        AVAIL.STARTDATE,
        cast((select count(*) from dbo.[UFN_JOB_TRAITS](AVAIL.JOBID) where TRAITTYPEID <> 7) as numeric(13,2)) AS TRAITCOUNT,
        count(distinct(VOLUNTEERADMINISTRATIVE.ID)) 
            + count(distinct(VOLUNTEERCERTIFICATION.ID)) 
            + count(distinct(VOLUNTEERCOURSE.ID))
            + count(distinct(VOLUNTEERINTEREST.ID)) 
            + count(distinct(VOLUNTEERLICENSE.ID))
            + count(distinct(VOLUNTEERMEDICAL.ID)) 
            + count(distinct(VOLUNTEERSKILLLEVEL.ID)) TRAITHIT,
        sum(
                case 
                    when JOBADMINISTRATIVE.ISREQUIRED = 1 and VOLUNTEERADMINISTRATIVE.ID is null then 1
                    when JOBCERTIFICATION.ISREQUIRED = 1 and VOLUNTEERCERTIFICATION.ID is null then 1
                    when JOBCOURSE.ISREQUIRED = 1 and VOLUNTEERCOURSE.ID is null then 1
                    when JOBINTEREST.ISREQUIRED = 1 and VOLUNTEERINTEREST.ID is null then 1
                    when JOBLICENSE.ISREQUIRED = 1 and VOLUNTEERLICENSE.ID is null then 1
                    when JOBMEDICAL.ISREQUIRED = 1 and VOLUNTEERMEDICAL.ID is null then 1
                    when JOBSKILL.ISREQUIRED = 1 and VOLUNTEERSKILLLEVEL.ID is null then 1
                    else 0
                end
            ) as MISSINGREQUIREDTRAITCOUNT
    into #TRAITS
    from #AVAILABLE AVAIL
    left outer join dbo.JOBADMINISTRATIVE
        on JOBADMINISTRATIVE.JOBID = AVAIL.JOBID 
    left outer join dbo.VOLUNTEERADMINISTRATIVE
        on VOLUNTEERADMINISTRATIVE.VOLUNTEERID = @VOLUNTEERID 
                and VOLUNTEERADMINISTRATIVE.ADMINISTRATIVECODEID = JOBADMINISTRATIVE.ADMINISTRATIVECODEID 
                and VOLUNTEERADMINISTRATIVE.VERIFIED = 1 
                and (VOLUNTEERADMINISTRATIVE.EXPIRESON is null 
                     or 
                    VOLUNTEERADMINISTRATIVE.EXPIRESON >= @TODAY)
    left outer join dbo.JOBCERTIFICATION
        on JOBCERTIFICATION.JOBID = AVAIL.JOBID 
    left outer join dbo.VOLUNTEERCERTIFICATION
        on VOLUNTEERCERTIFICATION.VOLUNTEERID = @VOLUNTEERID 
                and VOLUNTEERCERTIFICATION.CERTIFICATIONCODEID = JOBCERTIFICATION.CERTIFICATIONCODEID 
                and VOLUNTEERCERTIFICATION.VERIFIED = 1 
                and (VOLUNTEERCERTIFICATION.EXPIRESON is null 
                     or 
                    VOLUNTEERCERTIFICATION.EXPIRESON >= @TODAY)
    left outer join dbo.JOBCOURSE
        on JOBCOURSE.JOBID = AVAIL.JOBID 
    left outer join dbo.VOLUNTEERCOURSE
        on VOLUNTEERCOURSE.VOLUNTEERID = @VOLUNTEERID 
                and VOLUNTEERCOURSE.COURSECODEID = JOBCOURSE.COURSECODEID 
                and VOLUNTEERCOURSE.VERIFIED = 1 
                and (VOLUNTEERCOURSE.EXPIRESON is null 
                     or 
                    VOLUNTEERCOURSE.EXPIRESON >= @TODAY)
    left outer join dbo.JOBINTEREST
        on JOBINTEREST.JOBID = AVAIL.JOBID 
    left outer join dbo.VOLUNTEERINTEREST
        on VOLUNTEERINTEREST.VOLUNTEERID = @VOLUNTEERID 
                and VOLUNTEERINTEREST.VOLUNTEERINTERESTCODEID = JOBINTEREST.VOLUNTEERINTERESTCODEID 
    left outer join dbo.JOBLICENSE
        on JOBLICENSE.JOBID = AVAIL.JOBID 
    left outer join dbo.VOLUNTEERLICENSE
        on VOLUNTEERLICENSE.VOLUNTEERID = @VOLUNTEERID 
                and VOLUNTEERLICENSE.LICENSECODEID = JOBLICENSE.LICENSECODEID 
                and VOLUNTEERLICENSE.VERIFIED = 1 
                and (VOLUNTEERLICENSE.EXPIRESON is null 
                     or 
                    VOLUNTEERLICENSE.EXPIRESON >= @TODAY)
    left outer join dbo.JOBMEDICAL
        on JOBMEDICAL.JOBID = AVAIL.JOBID 
    left outer join dbo.VOLUNTEERMEDICAL
        on VOLUNTEERMEDICAL.VOLUNTEERID = @VOLUNTEERID 
                and VOLUNTEERMEDICAL.MEDICALCODEID = JOBMEDICAL.MEDICALCODEID 
                and VOLUNTEERMEDICAL.VERIFIED = 1 
                and (VOLUNTEERMEDICAL.EXPIRESON is null 
                     or 
                    VOLUNTEERMEDICAL.EXPIRESON >= @TODAY)
    left outer join dbo.JOBSKILL
        on JOBSKILL.JOBID = AVAIL.JOBID 
    left outer join dbo.VOLUNTEERSKILLLEVEL as [JOBSKILLLEVEL]
        on JOBSKILLLEVEL.ID = JOBSKILL.SKILLLEVELID
    left outer join dbo.VOLUNTEERSKILL
        on VOLUNTEERSKILL.VOLUNTEERID = @VOLUNTEERID 
                and VOLUNTEERSKILL.SKILLCODEID = JOBSKILL.SKILLCODEID
    left outer join dbo.VOLUNTEERSKILLLEVEL
        on VOLUNTEERSKILLLEVEL.ID = VOLUNTEERSKILL.SKILLLEVELID
                and VOLUNTEERSKILLLEVEL.SEQUENCE >= JOBSKILLLEVEL.SEQUENCE
    group by AVAIL.OCCURRENCEID,
            AVAIL.JOBID,
            AVAIL.STARTDATE

    create index TRAITS_JOBID on #TRAITS(JOBID);

    /*PUT IT ALL TOGETHER */
    select  
        TRAITS.JOBID,
        TRAITS.OCCURRENCEID,
        (cast(TRAITS.OCCURRENCEID as nvarchar(36)) + cast(@VOLUNTEERID as nvarchar(36)) + convert(nvarchar, TRAITS.STARTDATE, 112)) ASSIGN_ACTION,
        (cast(@VOLUNTEERID as nvarchar(36)) + cast(TRAITS.OCCURRENCEID as nvarchar(36))) ADD_MULTIPLE_ACTION,
        JOB.NAME NAME,
        '(' + cast(cast(round(
            case 
                when TRAITCOUNT = 0 then 1
                else TRAITHIT/TRAITCOUNT end * 100, 0) 
        as int) as varchar) + '% Match)' DESCRIPTION,
        TRAITS.STARTDATE STARTDATE,
        TRAITS.STARTDATE ENDDATE,
        JOBOCCURRENCE.STARTTIME,
        JOBOCCURRENCE.ENDTIME
    from #TRAITS TRAITS
    inner join dbo.JOBOCCURRENCE
        on TRAITS.OCCURRENCEID = JOBOCCURRENCE.ID
    inner join dbo.JOB 
        on TRAITS.JOBID = JOB.ID
    where
        coalesce(TRAITS.MISSINGREQUIREDTRAITCOUNT, 0) = 0
    order by NAME asc;

    /*TABLE CLEANUP */
    if not object_id('tempdb..#AVAILABLE') is null
        drop table #AVAILABLE
    if not object_id('tempdb..#TRAITS') is null
        drop table #TRAITS