USP_DATALIST_JOBOCCURRENCEMATCHES
This datalist returns all matches for an occurrence.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @JOBOCCURRENCEID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @DAYLIST | nvarchar(max) | IN | Days |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SECURITYFEATUREID | uniqueidentifier | IN | Input parameter indicating the ID of the feature to use for site security checking. |
| @SECURITYFEATURETYPE | tinyint | IN | Input parameter indicating the type of the feature to use for site security checking. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_JOBOCCURRENCEMATCHES
(
@JOBOCCURRENCEID uniqueidentifier = null,
@DAYLIST nvarchar(max) = '',
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as
set nocount on;
declare @LOCATIONID uniqueidentifier;
declare @VOLUNTEERTYPEID uniqueidentifier;
declare @JOBID uniqueidentifier;
declare @TRAITPERCENT int;
declare @TRAITCOUNT numeric(13,2);
declare @DAYCOUNT numeric(13,2);
declare @TODAY datetime;
declare @REQUIREDTRAITCOUNT int = 0;
if len(@DAYLIST) = 0 or @DAYLIST = '00000000'
return;
/* 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
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
/*TABLE CLEANUP */
if not object_id('tempdb..#AVAILABLE') is null
drop table #AVAILABLE
if not object_id('tempdb..#TRAITS') is null
drop table #TRAITS
/*FIND AVAILABLE*/
select AVAILABLE.VOLUNTEERID
into #AVAILABLE
from [UFN_JOBOCCURRENCE_VACANTSPECIFICDAYS](@DAYLIST,@JOBOCCURRENCEID,null) VACANCIES
inner join dbo.JOBOCCURRENCE
on JOBOCCURRENCE.ID = @JOBOCCURRENCEID
inner join dbo.[UFN_VOLUNTEER_AVAILABLESPECIFICDAYS](@DAYLIST, null, 1) AVAILABLE
on AVAILABLE.AVAILABLEDATE = VACANCIES.ASSIGNMENTDATE
left outer join dbo.VOLUNTEERVOLUNTEERTYPE VOLTYPE
on AVAILABLE.VOLUNTEERID = VOLTYPE.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 (@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)
and
((case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)=1
then 1
when (dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, '44C7B85C-A2FB-4705-976F-69A58E612402', AVAILABLE.VOLUNTEERID) = 1
and exists(
select 1 from dbo.CONSTITUENT C
left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = C.ID
where
C.ID = AVAILABLE.VOLUNTEERID
and
(dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[CONSTITUENTSITE].[SITEID] or (SITEID is null and [CONSTITUENTSITE].[SITEID] is null)))
)
) then 1
when @CURRENTAPPUSERID is null
then 1
else 0
end) = 1)
group by AVAILABLE.VOLUNTEERID
having max(case when ASSIGNEDJO.ID is null then 0 else 1 end) = 0
and count(distinct VACANCIES.ASSIGNMENTDATE) = @DAYCOUNT;
create index AVAILABLE_VOLUNTEERID on #AVAILABLE(VOLUNTEERID);
/* remove volunteers whose special needs are not met. */
delete from #AVAILABLE
where VOLUNTEERID in
(select VSN.VOLUNTEERID
from #AVAILABLE 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)
/*FIND 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
into #TRAITS
from #AVAILABLE 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
create index TRAITS_VOLUNTEERID on #TRAITS(VOLUNTEERID);
/*PUT IT ALL TOGETHER */
select TRAITS.VOLUNTEERID VOLUNTEERID,
cast(coalesce(TRAITWEIGHT, 0) as int) as MATCH,
CONSTITUENT.NAME
from #TRAITS TRAITS
inner join dbo.CONSTITUENT
on TRAITS.VOLUNTEERID = CONSTITUENT.ID
where
@REQUIREDTRAITCOUNT = TRAITS.REQUIREDTRAITCOUNT
order by MATCH desc, CONSTITUENT.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