USP_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS

CREATE procedure dbo.USP_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS 
(
    @JOBOCCURRENCEID uniqueidentifier,
    @STARTDATE date,
    @ENDDATE date,
    @CURRENTAPPUSERID uniqueidentifier = null
)
with execute as owner
as

    set nocount on;

    declare @ISADMIN bit;
    declare @APPUSER_IN_NONRACROLE bit;
    declare @APPUSER_IN_NOSECGROUPROLE bit;
    declare @APPUSER_IN_NONSITEROLE bit;
    declare @APPUSER_IN_NOSITEROLE bit;

    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

    declare    @LOCATIONID uniqueidentifier;
    declare    @VOLUNTEERTYPEID uniqueidentifier;
    declare    @JOBID uniqueidentifier;
    declare @TRAITPERCENT int;
    declare @TRAITCOUNT numeric(13,2);
    declare @TODAY datetime;
    declare @REQUIREDTRAITCOUNT int = 0;

    /* 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


    declare @DAYCOUNT int = 0;

    if @STARTDATE is not null and @ENDDATE is not null and @STARTDATE <= @ENDDATE
        set @DAYCOUNT = datediff(dd, @STARTDATE, @ENDDATE) + 1
    else
        return;      

    declare @DAYSXML xml;
    set @DAYSXML = (
        select 
            convert(varchar, DATEADD(d,NUM,@STARTDATE), 112) [DATE]
        from 
            dbo.NUMBERS 
        where 
            DATEADD(d,NUM,@STARTDATE) <= @ENDDATE
        for xml path('ITEM'), type)

    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 @TRAITSCTE nvarchar(max) = ''
    declare @REQUIREDTRAITID uniqueidentifier;

    declare REQUIREDJOBADMINISTRATIVECURSOR cursor local fast_forward for
        select 
            JOBADMINISTRATIVE.ADMINISTRATIVECODEID
        from dbo.JOBADMINISTRATIVE 
        where 
            JOBADMINISTRATIVE.JOBID = @JOBID 
            and JOBADMINISTRATIVE.ISREQUIRED = 1

    open REQUIREDJOBADMINISTRATIVECURSOR;
    fetch next from REQUIREDJOBADMINISTRATIVECURSOR into @REQUIREDTRAITID;

    while (@@FETCH_STATUS = 0)
    begin
        if len(@TRAITSCTE) > 0
            set @TRAITSCTE = @TRAITSCTE + 'and '

        set @TRAITSCTE = @TRAITSCTE + '
            exists (
                select 1
                from 
                    dbo.VOLUNTEERADMINISTRATIVE
                where 
                    VOLUNTEERADMINISTRATIVE.ADMINISTRATIVECODEID = ''' + cast(@REQUIREDTRAITID as nvarchar(36)) + '''
                    and VOLUNTEERADMINISTRATIVE.VERIFIED = 1
                    and (VOLUNTEERADMINISTRATIVE.EXPIRESON is null or VOLUNTEERADMINISTRATIVE.EXPIRESON >= @TODAY)
                    and VOLUNTEERADMINISTRATIVE.VOLUNTEERID = VOLUNTEER.ID
            ) '

        fetch next from REQUIREDJOBADMINISTRATIVECURSOR into @REQUIREDTRAITID;
    end

    close REQUIREDJOBADMINISTRATIVECURSOR;
    deallocate REQUIREDJOBADMINISTRATIVECURSOR;

    declare REQUIREDJOBCERTIFICATIONCURSOR cursor local fast_forward for
    select 
        JOBCERTIFICATION.CERTIFICATIONCODEID
    from dbo.JOBCERTIFICATION 
    where 
        JOBCERTIFICATION.JOBID = @JOBID 
        and JOBCERTIFICATION.ISREQUIRED = 1

    open REQUIREDJOBCERTIFICATIONCURSOR;
    fetch next from REQUIREDJOBCERTIFICATIONCURSOR into @REQUIREDTRAITID;

    while (@@FETCH_STATUS = 0)
    begin
        if len(@TRAITSCTE) > 0
            set @TRAITSCTE = @TRAITSCTE + 'and '

        set @TRAITSCTE = @TRAITSCTE + '
            exists (
                select 1
                from 
                    dbo.VOLUNTEERCERTIFICATION
                where 
                    VOLUNTEERCERTIFICATION.CERTIFICATIONCODEID = ''' + cast(@REQUIREDTRAITID as nvarchar(36)) + '''
                    and VOLUNTEERCERTIFICATION.VERIFIED = 1
                    and (VOLUNTEERCERTIFICATION.EXPIRESON is null or VOLUNTEERCERTIFICATION.EXPIRESON >= @TODAY)
                    and VOLUNTEERCERTIFICATION.VOLUNTEERID = VOLUNTEER.ID
            ) '

        fetch next from REQUIREDJOBCERTIFICATIONCURSOR into @REQUIREDTRAITID;
    end

    close REQUIREDJOBCERTIFICATIONCURSOR;
    deallocate REQUIREDJOBCERTIFICATIONCURSOR;

    declare REQUIREDJOBCOURSECURSOR cursor local fast_forward for
    select 
        JOBCOURSE.COURSECODEID
    from dbo.JOBCOURSE 
    where 
        JOBCOURSE.JOBID = @JOBID 
        and JOBCOURSE.ISREQUIRED = 1

    open REQUIREDJOBCOURSECURSOR;
    fetch next from REQUIREDJOBCOURSECURSOR into @REQUIREDTRAITID;

    while (@@FETCH_STATUS = 0)
    begin
        if len(@TRAITSCTE) > 0
            set @TRAITSCTE = @TRAITSCTE + 'and '

        set @TRAITSCTE = @TRAITSCTE + '
            exists (
                select 1
                from 
                    dbo.VOLUNTEERCOURSE
                where 
                    VOLUNTEERCOURSE.COURSECODEID = ''' + cast(@REQUIREDTRAITID as nvarchar(36)) + '''
                    and VOLUNTEERCOURSE.VERIFIED = 1
                    and (VOLUNTEERCOURSE.EXPIRESON is null or VOLUNTEERCOURSE.EXPIRESON >= @TODAY)
                    and VOLUNTEERCOURSE.VOLUNTEERID = VOLUNTEER.ID
            ) '

        fetch next from REQUIREDJOBCOURSECURSOR into @REQUIREDTRAITID;
    end

    close REQUIREDJOBCOURSECURSOR;
    deallocate REQUIREDJOBCOURSECURSOR;

    declare REQUIREDJOBINTERESTCURSOR cursor local fast_forward for
    select 
        JOBINTEREST.VOLUNTEERINTERESTCODEID
    from dbo.JOBINTEREST 
    where 
        JOBINTEREST.JOBID = @JOBID 
        and JOBINTEREST.ISREQUIRED = 1

    open REQUIREDJOBINTERESTCURSOR;
    fetch next from REQUIREDJOBINTERESTCURSOR into @REQUIREDTRAITID;

    while (@@FETCH_STATUS = 0)
    begin
        if len(@TRAITSCTE) > 0
            set @TRAITSCTE = @TRAITSCTE + 'and '

        set @TRAITSCTE = @TRAITSCTE + '
            exists (
                select 1
                from 
                    dbo.VOLUNTEERINTEREST
                where 
                    VOLUNTEERINTEREST.VOLUNTEERINTERESTCODEID = ''' + cast(@REQUIREDTRAITID as nvarchar(36)) + '''
                    and VOLUNTEERINTEREST.VOLUNTEERID = VOLUNTEER.ID
            ) '

        fetch next from REQUIREDJOBINTERESTCURSOR into @REQUIREDTRAITID;
    end

    close REQUIREDJOBINTERESTCURSOR;
    deallocate REQUIREDJOBINTERESTCURSOR;

    declare REQUIREDJOBLICENSECURSOR cursor local fast_forward for
    select 
        JOBLICENSE.LICENSECODEID
    from dbo.JOBLICENSE 
    where 
        JOBLICENSE.JOBID = @JOBID 
        and JOBLICENSE.ISREQUIRED = 1

    open REQUIREDJOBLICENSECURSOR;
    fetch next from REQUIREDJOBLICENSECURSOR into @REQUIREDTRAITID;

    while (@@FETCH_STATUS = 0)
    begin
        if len(@TRAITSCTE) > 0
            set @TRAITSCTE = @TRAITSCTE + 'and '

        set @TRAITSCTE = @TRAITSCTE + '
            exists (
                select 1
                from 
                    dbo.VOLUNTEERLICENSE
                where 
                    VOLUNTEERLICENSE.LICENSECODEID = ''' + cast(@REQUIREDTRAITID as nvarchar(36)) + '''
                    and VOLUNTEERLICENSE.VERIFIED = 1
                    and (VOLUNTEERLICENSE.EXPIRESON is null or VOLUNTEERLICENSE.EXPIRESON >= @TODAY)
                    and VOLUNTEERLICENSE.VOLUNTEERID = VOLUNTEER.ID
            ) '

        fetch next from REQUIREDJOBLICENSECURSOR into @REQUIREDTRAITID;
    end

    close REQUIREDJOBLICENSECURSOR;
    deallocate REQUIREDJOBLICENSECURSOR;

    declare REQUIREDJOBMEDICALCURSOR cursor local fast_forward for
    select 
        JOBMEDICAL.MEDICALCODEID
    from dbo.JOBMEDICAL 
    where 
        JOBMEDICAL.JOBID = @JOBID 
        and JOBMEDICAL.ISREQUIRED = 1

    open REQUIREDJOBMEDICALCURSOR;
    fetch next from REQUIREDJOBMEDICALCURSOR into @REQUIREDTRAITID;

    while (@@FETCH_STATUS = 0)
    begin
        if len(@TRAITSCTE) > 0
            set @TRAITSCTE = @TRAITSCTE + 'and '

        set @TRAITSCTE = @TRAITSCTE + '
            exists (
                select 1
                from 
                    dbo.VOLUNTEERMEDICAL
                where 
                    VOLUNTEERMEDICAL.MEDICALCODEID = ''' + cast(@REQUIREDTRAITID as nvarchar(36)) + '''
                    and VOLUNTEERMEDICAL.VERIFIED = 1
                    and (VOLUNTEERMEDICAL.EXPIRESON is null or VOLUNTEERMEDICAL.EXPIRESON >= @TODAY)
                    and VOLUNTEERMEDICAL.VOLUNTEERID = VOLUNTEER.ID
            ) '

        fetch next from REQUIREDJOBMEDICALCURSOR into @REQUIREDTRAITID;
    end

    close REQUIREDJOBMEDICALCURSOR;
    deallocate REQUIREDJOBMEDICALCURSOR;

    declare @SKILLLEVELSEQUENCE int;
    declare REQUIREDJOBSKILLCURSOR cursor local fast_forward for
    select 
        JOBSKILL.SKILLCODEID,
        VOLUNTEERSKILLLEVEL.SEQUENCE
    from dbo.JOBSKILL 
        inner join dbo.VOLUNTEERSKILLLEVEL on  VOLUNTEERSKILLLEVEL.ID = JOBSKILL.SKILLLEVELID
    where 
        JOBSKILL.JOBID = @JOBID 
        and JOBSKILL.ISREQUIRED = 1

    open REQUIREDJOBSKILLCURSOR;
    fetch next from REQUIREDJOBSKILLCURSOR into @REQUIREDTRAITID, @SKILLLEVELSEQUENCE;

    while (@@FETCH_STATUS = 0)
    begin
        if len(@TRAITSCTE) > 0
            set @TRAITSCTE = @TRAITSCTE + 'and '

        set @TRAITSCTE = @TRAITSCTE + '
            exists (
                select 1
                from 
                    dbo.VOLUNTEERSKILL
                    inner join dbo.VOLUNTEERSKILLLEVEL on VOLUNTEERSKILLLEVEL.ID = VOLUNTEERSKILL.SKILLLEVELID 
                where 
                    VOLUNTEERSKILL.SKILLCODEID = ''' + cast(@REQUIREDTRAITID as nvarchar(36)) + '''
                    and VOLUNTEERSKILLLEVEL.SEQUENCE >= ' + cast(@SKILLLEVELSEQUENCE as nvarchar(20)) + '
                    and VOLUNTEERSKILL.VOLUNTEERID = VOLUNTEER.ID
            ) '

        fetch next from REQUIREDJOBSKILLCURSOR into @REQUIREDTRAITID, @SKILLLEVELSEQUENCE;
    end

    close REQUIREDJOBSKILLCURSOR;
    deallocate REQUIREDJOBSKILLCURSOR;

    if object_id('tempdb..#TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEIDS') is not null
        drop table #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEIDS;

    create table #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEIDS
    (  
        VOLUNTEERID uniqueidentifier
    );    

    CREATE INDEX IDX_AVAILABLEIDS_VOLUNTEERID
    ON #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEIDS (VOLUNTEERID)

    if object_id('tempdb..#TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES') is not null
        drop table #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES;

    create table #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES
    (  
        AVAILABLEDATE date,
        VOLUNTEERID uniqueidentifier,
        STARTTIME varchar(4) collate DATABASE_DEFAULT,
        ENDTIME varchar(4) collate DATABASE_DEFAULT
    );    

    if object_id('tempdb..#TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE') is not null
        drop table #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE;

    create table #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE
    (
        ASSIGNMENTDATE datetime, 
        OCCURRENCEID uniqueidentifier, 
        JOBID uniqueidentifier, 
        JOBSTARTTIME varchar(4) collate DATABASE_DEFAULT, 
        JOBENDTIME varchar(4) collate DATABASE_DEFAULT, 
        VOLUNTEERSNEEDED int, 
        OPENINGS int
    )

    CREATE INDEX IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES_AVAILABLEDATE
    ON #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES (AVAILABLEDATE)

    CREATE INDEX IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES_STARTTIME
    ON #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES (STARTTIME)

    CREATE INDEX IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES_ENDTIME
    ON #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES (ENDTIME)

    CREATE INDEX IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE_ASSIGNMENTDATE
    ON #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE (ASSIGNMENTDATE)

    CREATE INDEX IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE_OCCURRENCEID
    ON #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE (OCCURRENCEID)

    CREATE INDEX IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE_JOBSTARTTIME
    ON #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE (JOBSTARTTIME)

    CREATE INDEX IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE_JOBENDTIME
    ON #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE (JOBENDTIME)

    insert into #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES
    select 
        AVAILABILITY.AVAILABLEDATE, 
        AVAILABILITY.VOLUNTEERID, 
        AVAILABILITY.STARTTIME, 
        AVAILABILITY.ENDTIME
    from 
        dbo.[UFN_VOLUNTEER_AVAILABLESPECIFICDAYS_2](@DAYSXML, null, @TODAY) AVAILABILITY
    where
        AVAILABILITY.VOLUNTEERID in (
            select VOLUNTEERID
            from dbo.[UFN_VOLUNTEER_AVAILABLESPECIFICDAYS_2](@DAYSXML, null, @TODAY) AVAILABILITY
            group by VOLUNTEERID
            having count(*) >= @DAYCOUNT)

    insert into #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE
    select * from dbo.UFN_JOBOCCURRENCE_SCHEDULE(@STARTDATE, @ENDDATE, @JOBOCCURRENCEID, null)

    /*FIND AVAILABLE*/
    declare @SQL nvarchar(max) = '';

    if len(@TRAITSCTE) > 0
    begin
        set @SQL = '
            with TRAITS_CTE as (
                select 
                    VOLUNTEER.ID
                from
                    dbo.VOLUNTEER
                where ' + @TRAITSCTE + ') '
    end

    set @SQL = @SQL + '
        insert into #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEIDS
        select 
            distinct AVAILABLE.VOLUNTEERID
        from #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE SCHEDULE
        inner join dbo.JOBOCCURRENCE 
            on JOBOCCURRENCE.ID = @JOBOCCURRENCEID
        inner join #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES AVAILABLE
            on AVAILABLE.AVAILABLEDATE = SCHEDULE.ASSIGNMENTDATE '

    if len(@TRAITSCTE) > 0
    begin
        set @SQL = @SQL + '
        inner join TRAITS_CTE on TRAITS_CTE.ID = AVAILABLE.VOLUNTEERID '
    end    

    set @SQL = @SQL + '
        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 
            inner join #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES  VOLUNTEERS
                on VOLUNTEERS.VOLUNTEERID = VOLUNTEERASSIGNMENT.VOLUNTEERID 
            outer apply dbo.UFN_JOBOCCURRENCE_SCHEDULE(@STARTDATE, @ENDDATE, VOLUNTEERASSIGNMENT.JOBOCCURRENCEID, null)
            where VOLUNTEERASSIGNMENT.DATE between @STARTDATE and @ENDDATE) 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 (ASSIGNEDJOB.OCCURRENCEID is null) and
            (AVAILABLE.STARTTIME <= JOBOCCURRENCE.STARTTIME or JOBOCCURRENCE.STARTTIME = '''') and
            (AVAILABLE.ENDTIME >= JOBOCCURRENCE.ENDTIME or AVAILABLE.ENDTIME = '''') '

    if @VOLUNTEERTYPEID is not null
        set @SQL = @SQL + 'and (VOLTYPE.VOLUNTEERTYPEID = @VOLUNTEERTYPEID) '

    if @LOCATIONID is not null
        set @SQL = @SQL + 'and (VOLUNTEERVOLUNTEERLOCATION.ID is null or VOLUNTEERVOLUNTEERLOCATION.VOLUNTEERLOCATIONCODEID = @LOCATIONID) '

    if @ISADMIN = 0
    begin
        if @APPUSER_IN_NONRACROLE = 0
            set @SQL = @SQL + 'and dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, AVAILABLE.VOLUNTEERID, @APPUSER_IN_NOSECGROUPROLE) = 1 '
        if @APPUSER_IN_NONSITEROLE  = 0
            set @SQL = @SQL + 'and dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, AVAILABLE.VOLUNTEERID, @APPUSER_IN_NOSITEROLE) = 1 '
    end

    --set @SQL = @SQL + '

    --    group by AVAILABLE.VOLUNTEERID, SCHEDULE.ASSIGNMENTDATE 

    --    having max(case when ASSIGNEDJOB.OCCURRENCEID is null then 0 else 1 end) = 0 '


    exec sp_executesql @SQL, N'@STARTDATE datetime, @ENDDATE datetime, @JOBOCCURRENCEID uniqueidentifier, @DAYSXML xml, @DAYCOUNT int, @TODAY datetime, @VOLUNTEERTYPEID uniqueidentifier, @LOCATIONID uniqueidentifier, @CURRENTAPPUSERID uniqueidentifier, @APPUSER_IN_NOSECGROUPROLE bit, @APPUSER_IN_NOSITEROLE bit', 
        @STARTDATE, @ENDDATE, @JOBOCCURRENCEID, @DAYSXML, @DAYCOUNT, @TODAY, @VOLUNTEERTYPEID, @LOCATIONID, @CURRENTAPPUSERID, @APPUSER_IN_NOSECGROUPROLE, @APPUSER_IN_NOSITEROLE;

    drop index IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE_ASSIGNMENTDATE on #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE
    drop index IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE_OCCURRENCEID on #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE
    drop index IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE_JOBSTARTTIME on #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE
    drop index IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE_JOBENDTIME on #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE

    drop index IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES_AVAILABLEDATE on #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES
    drop index IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES_STARTTIME on #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES
    drop index IDX_TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES_ENDTIME on #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES

    drop table #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_SCHEDULE
    drop table #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEFORALLDATES

    /* remove volunteers whose special needs are not met. */
    delete from #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEIDS
    where VOLUNTEERID in 
        (select VSN.VOLUNTEERID
         from #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEIDS AVAIL
         inner join VOLUNTEERSPECIALNEED VSN
            on AVAIL.VOLUNTEERID = VSN.VOLUNTEERID
         left outer join dbo.JOBSPECIALNEED JSN
            on JSN.JOBID = @JOBID and JSN.SPECIALNEEDCODEID = VSN.SPECIALNEEDCODEID
         where JSN.ID is null)

    if object_id('tempdb..#TMP_DATA_JOBOCCURRENCE_ASSIGN_GET_VOLUNTEERS_TRAITS') is not null
        drop table #TMP_DATA_JOBOCCURRENCE_ASSIGN_GET_VOLUNTEERS_TRAITS;

    create table #TMP_DATA_JOBOCCURRENCE_ASSIGN_GET_VOLUNTEERS_TRAITS 
        (
            VOLUNTEERID uniqueidentifier, 
            TRAITWEIGHT int, 
            REQUIREDTRAITCOUNT int
    )

    CREATE INDEX IDX_TRAITS_VOLUNTEERID
    ON #TMP_DATA_JOBOCCURRENCE_ASSIGN_GET_VOLUNTEERS_TRAITS (VOLUNTEERID)

    /*FIND TRAITS*/
    insert into #TMP_DATA_JOBOCCURRENCE_ASSIGN_GET_VOLUNTEERS_TRAITS
    select    
        AVAIL.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 #TMP_DATA_JOBOCCURRENCE_ASSIGN_GETVOLUNTEERS_AVAILABLEIDS AVAIL
    left outer join dbo.[UFN_JOBOCCURRENCE_TRAITIDS](@JOBOCCURRENCEID) JT
        on 1 = 1
    left outer join dbo.VOLUNTEERADMINISTRATIVE
        on VOLUNTEERADMINISTRATIVE.VOLUNTEERID = AVAIL.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 = AVAIL.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 = AVAIL.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 = AVAIL.VOLUNTEERID 
                and VOLUNTEERINTEREST.VOLUNTEERINTERESTCODEID = JT.VOLUNTEERINTERESTCODEID
    left outer join dbo.VOLUNTEERLICENSE
        on VOLUNTEERLICENSE.VOLUNTEERID = AVAIL.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 = AVAIL.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 = AVAIL.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
    group by AVAIL.VOLUNTEERID

    /*PUT IT ALL TOGETHER */
    select 
        TRAITS.VOLUNTEERID VOLUNTEERID,
        cast(coalesce(TRAITWEIGHT, 0) as nvarchar(3)) + '%' as MATCH,
        NF.NAME
    from #TMP_DATA_JOBOCCURRENCE_ASSIGN_GET_VOLUNTEERS_TRAITS TRAITS
    inner join dbo.CONSTITUENT 
        on TRAITS.VOLUNTEERID = CONSTITUENT.ID
    left join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    where
        @REQUIREDTRAITCOUNT = TRAITS.REQUIREDTRAITCOUNT
        and DECEASEDCONSTITUENT.ID is null
    order by MATCH desc, CONSTITUENT.KEYNAME asc;