UFN_VOLUNTEER_JOBOCCURRENCE_GETMATCH

CREATE function dbo.UFN_VOLUNTEER_JOBOCCURRENCE_GETMATCH
(
    @VOLUNTEERID uniqueidentifier,
    @JOBOCCURRENCEID uniqueidentifier,
    @STARTDATE date,
    @ENDDATE date
)
returns integer

as begin
    declare @TRAITPERCENT int;
    declare @TRAITCOUNT numeric(13,2);
    declare @DAYCOUNT numeric(13,2);
    declare @TODAY datetime;
    declare @REQUIREDTRAITCOUNT int = 0;
    declare @DAYLIST nvarchar(max) = '';

    declare    @LOCATIONID uniqueidentifier;
    declare    @VOLUNTEERTYPEID uniqueidentifier;
    declare    @JOBID uniqueidentifier;

    /* CONSTANTS FOR WEIGHTING */
    select @TRAITPERCENT  = 100;

    /*CONSTANTS FOR MATCHING*/
    set @TODAY = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
    select 
        @TRAITCOUNT = count(*),
        @REQUIREDTRAITCOUNT = coalesce(sum(case when ISREQUIRED = 1 then 1 else 0 end), 0)
    from 
        dbo.[UFN_JOBOCCURRENCE_TRAITIDS](@JOBOCCURRENCEID) 
    where 
        TRAITTYPEID <> 7 --exclude special needs


    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, '')

    set @DAYCOUNT = len(@DAYLIST) / 8;

    select  
        @LOCATIONID = LOCATIONCODEID,
        @VOLUNTEERTYPEID = VOLUNTEERTYPEID,
        @JOBID = JOB.ID
    from dbo.JOBOCCURRENCE 
    inner join dbo.JOB
        on dbo.JOBOCCURRENCE.JOBID = dbo.JOB.ID
    where JOBOCCURRENCE.ID = @JOBOCCURRENCEID

    declare @AVAILABLE bit = 0
    select 
        @AVAILABLE = (case when AVAILABLE.VOLUNTEERID is null then 0 else 1 end)
    from dbo.UFN_JOBOCCURRENCE_SCHEDULE(@STARTDATE, @ENDDATE, @JOBOCCURRENCEID, null) SCHEDULE
    inner join dbo.JOBOCCURRENCE 
        on JOBOCCURRENCE.ID = @JOBOCCURRENCEID
    inner join dbo.[UFN_VOLUNTEER_AVAILABLESPECIFICDAYS](@DAYLIST, @VOLUNTEERID, 1) AVAILABLE
        on AVAILABLE.AVAILABLEDATE = SCHEDULE.ASSIGNMENTDATE
    left outer join dbo.VOLUNTEERVOLUNTEERTYPE VOLTYPE
        on AVAILABLE.VOLUNTEERID = VOLTYPE.VOLUNTEERID AND VOLTYPE.STATUSCODE = 1
    left outer join    dbo.VOLUNTEERVOLUNTEERLOCATION
        on AVAILABLE.VOLUNTEERID = VOLUNTEERVOLUNTEERLOCATION.VOLUNTEERID
    left outer join (
        select 
            OCCURRENCEID,
            VOLUNTEERASSIGNMENT.DATE,
            VOLUNTEERASSIGNMENT.VOLUNTEERID,
            JOBSTARTTIME,
            JOBENDTIME  
        from dbo.VOLUNTEERASSIGNMENT 
        outer apply dbo.UFN_JOBOCCURRENCE_SCHEDULE(@STARTDATE, @ENDDATE, VOLUNTEERASSIGNMENT.JOBOCCURRENCEID, null)) as ASSIGNEDJOB 
            on ASSIGNEDJOB.VOLUNTEERID = AVAILABLE.VOLUNTEERID
                and ASSIGNEDJOB.DATE = SCHEDULE.ASSIGNMENTDATE
                and ((ASSIGNEDJOB.JOBSTARTTIME between JOBOCCURRENCE.STARTTIME and JOBOCCURRENCE.ENDTIME)
                or (ASSIGNEDJOB.JOBENDTIME between JOBOCCURRENCE.STARTTIME and JOBOCCURRENCE.ENDTIME)
                or ASSIGNEDJOB.JOBSTARTTIME = '' or ASSIGNEDJOB.JOBENDTIME = ''
                or JOBOCCURRENCE.STARTTIME = '' or JOBOCCURRENCE.ENDTIME  = '')
    where
        (@VOLUNTEERTYPEID is null or VOLTYPE.VOLUNTEERTYPEID = @VOLUNTEERTYPEID) and
        (AVAILABLE.STARTTIME <= JOBOCCURRENCE.STARTTIME or JOBOCCURRENCE.STARTTIME = '') and
        (AVAILABLE.ENDTIME >= JOBOCCURRENCE.ENDTIME or AVAILABLE.ENDTIME = '') and
        (@LOCATIONID is null -- no location

            or VOLUNTEERVOLUNTEERLOCATION.ID is null --none

            or VOLUNTEERVOLUNTEERLOCATION.VOLUNTEERLOCATIONCODEID = @LOCATIONID)
    group by AVAILABLE.VOLUNTEERID, SCHEDULE.ASSIGNMENTDATE 
    having max(case when ASSIGNEDJOB.OCCURRENCEID is null or ASSIGNEDJOB.OCCURRENCEID = @JOBOCCURRENCEID then 0 else 1 end) = 0;

    /* volunteer not available when special needs are not met. */
    if exists (
        select VSN.VOLUNTEERID
        from VOLUNTEERSPECIALNEED VSN
        left outer join dbo.JOBSPECIALNEED JSN
            on JSN.JOBID = @JOBID and JSN.SPECIALNEEDCODEID = VSN.SPECIALNEEDCODEID
        where JSN.ID is null and VSN.VOLUNTEERID = @VOLUNTEERID)
        set @AVAILABLE = 0

    declare @MATCH integer = 0;

    if @AVAILABLE = 1
    begin
        declare @TRAITS table
        (
            VOLUNTEERID uniqueidentifier, 
            TRAITWEIGHT int, 
            REQUIREDTRAITCOUNT int
        )

        /*FIND TRAITS*/
        insert into @TRAITS
        select    
            @VOLUNTEERID,
            round(CASE when @TRAITCOUNT = 0 
                then 1
                else
                    sum(case when coalesce(
                                VOLUNTEERADMINISTRATIVE.ID,
                                VOLUNTEERCERTIFICATION.ID,
                                VOLUNTEERCOURSE.ID,
                                VOLUNTEERINTEREST.ID,
                                VOLUNTEERLICENSE.ID,
                                VOLUNTEERMEDICAL.ID,
                                VOLUNTEERSKILLLEVEL.ID) is null then 0 else 1 end)
                    /@TRAITCOUNT 
                end * @TRAITPERCENT, 0)
                as TRAITWEIGHT,
                sum(case 
                        when ISREQUIRED = 0 then 0
                        else case when coalesce(
                                VOLUNTEERADMINISTRATIVE.ID,
                                VOLUNTEERCERTIFICATION.ID,
                                VOLUNTEERCOURSE.ID,
                                VOLUNTEERINTEREST.ID,
                                VOLUNTEERLICENSE.ID,
                                VOLUNTEERMEDICAL.ID,
                                VOLUNTEERSKILLLEVEL.ID) is null then 0 else 1 end
                    end) REQUIREDTRAITCOUNT
        from dbo.[UFN_JOBOCCURRENCE_TRAITIDS](@JOBOCCURRENCEID) JT
        left outer join dbo.VOLUNTEERADMINISTRATIVE
            on VOLUNTEERADMINISTRATIVE.VOLUNTEERID = @VOLUNTEERID
                    and VOLUNTEERADMINISTRATIVE.ADMINISTRATIVECODEID = JT.ADMINISTRATIVECODEID 
                    and VOLUNTEERADMINISTRATIVE.VERIFIED = 1 
                    and (VOLUNTEERADMINISTRATIVE.EXPIRESON is null 
                         or 
                        VOLUNTEERADMINISTRATIVE.EXPIRESON >= @TODAY)
        left outer join dbo.VOLUNTEERCERTIFICATION
            on VOLUNTEERCERTIFICATION.VOLUNTEERID = @VOLUNTEERID
                    and VOLUNTEERCERTIFICATION.CERTIFICATIONCODEID = JT.CERTIFICATIONCODEID 
                    and VOLUNTEERCERTIFICATION.VERIFIED = 1
                    and (VOLUNTEERCERTIFICATION.EXPIRESON is null 
                         or 
                        VOLUNTEERCERTIFICATION.EXPIRESON >= @TODAY)
        left outer join dbo.VOLUNTEERCOURSE
            on VOLUNTEERCOURSE.VOLUNTEERID = @VOLUNTEERID
                    and VOLUNTEERCOURSE.COURSECODEID = JT.COURSECODEID 
                    and VOLUNTEERCOURSE.VERIFIED = 1
                    and (VOLUNTEERCOURSE.EXPIRESON is null 
                         or 
                        VOLUNTEERCOURSE.EXPIRESON >= @TODAY)
        left outer join dbo.VOLUNTEERINTEREST
            on VOLUNTEERINTEREST.VOLUNTEERID = @VOLUNTEERID
                    and VOLUNTEERINTEREST.VOLUNTEERINTERESTCODEID = JT.VOLUNTEERINTERESTCODEID
        left outer join dbo.VOLUNTEERLICENSE
            on VOLUNTEERLICENSE.VOLUNTEERID = @VOLUNTEERID 
                    and VOLUNTEERLICENSE.LICENSECODEID = JT.LICENSECODEID 
                    and VOLUNTEERLICENSE.VERIFIED = 1
                    and (VOLUNTEERLICENSE.EXPIRESON is null 
                         or 
                        VOLUNTEERLICENSE.EXPIRESON >= @TODAY)
        left outer join dbo.VOLUNTEERMEDICAL
            on VOLUNTEERMEDICAL.VOLUNTEERID = @VOLUNTEERID 
                    and VOLUNTEERMEDICAL.MEDICALCODEID = JT.MEDICALCODEID 
                    and VOLUNTEERMEDICAL.VERIFIED = 1
                    and (VOLUNTEERMEDICAL.EXPIRESON is null 
                         or 
                        VOLUNTEERMEDICAL.EXPIRESON >= @TODAY)
        left outer join dbo.VOLUNTEERSKILL
            on VOLUNTEERSKILL.VOLUNTEERID = @VOLUNTEERID 
                    and VOLUNTEERSKILL.SKILLCODEID = JT.SKILLCODEID
        left outer join dbo.VOLUNTEERSKILLLEVEL
            on VOLUNTEERSKILLLEVEL.ID = VOLUNTEERSKILL.SKILLLEVELID 
                and VOLUNTEERSKILLLEVEL.SEQUENCE >= JT.SKILLLEVEL
        where TRAITTYPEID is null or TRAITTYPEID <> 7;

        /*PUT IT ALL TOGETHER */
        select 
            @MATCH = coalesce(TRAITWEIGHT, 0)
        from @TRAITS TRAITS
        inner join dbo.CONSTITUENT 
            on TRAITS.VOLUNTEERID = CONSTITUENT.ID
        where
            @REQUIREDTRAITCOUNT = TRAITS.REQUIREDTRAITCOUNT;
    end

    return @MATCH;
end