USP_DATAFORMTEMPLATE_ADD_ASSIGNVOLUNTEERS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ASSIGNVOLUNTEERS
(
    @ID uniqueidentifier = null output,
    @CURRENTAPPUSERID uniqueidentifier,
    @JOBOCCURRENCEID uniqueidentifier,
    @STARTDATE date = null,
    @ENDDATE date = null,
    @VOLUNTEERS xml = null,
    @SINGLEDAY_EDIT bit = 0,
    @CHANGEAGENTID uniqueidentifier = null
)
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;
declare @JOBOCCURRENCE_STARTDATE date;

declare @DATESVALID_FOR_OCCURRENCE bit = 1;

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);

if not exists(select 1 from dbo.UFN_JOBOCCURRENCE_SCHEDULE(@STARTDATE, @ENDDATE, @JOBOCCURRENCEID, null))
    set @DATESVALID_FOR_OCCURRENCE = 0;

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();

begin try

    declare @ASSIGNMENTS table (VOLUNTEERASSIGNMENTID uniqueidentifier, VOLUNTEERID uniqueidentifier, ASSIGN bit)

    insert into @ASSIGNMENTS (VOLUNTEERASSIGNMENTID, VOLUNTEERID, ASSIGN) 
    select 
        VOLUNTEERASSIGNMENTID, 
        VOLUNTEERID, 
        ASSIGN
    from
        (select
            T.c.value('(VOLUNTEERASSIGNMENTID)[1]','uniqueidentifier') as VOLUNTEERASSIGNMENTID,
            T.c.value('(VOLUNTEERID)[1]','uniqueidentifier') as VOLUNTEERID,
            T.c.value('(ASSIGN)[1]','bit') as ASSIGN
        from @VOLUNTEERS.nodes('/VOLUNTEERS/ITEM') T(c)) as VOLUNTEERS;

    if not exists(select * from @ASSIGNMENTS where (ASSIGN = 1 and VOLUNTEERASSIGNMENTID is null) or (ASSIGN = 0 and not VOLUNTEERASSIGNMENTID is null))
        raiserror('BBERR_ASSIGNVOLUNTEERS_ATLEASTONEVOLUNTEER', 13, 1);

    if  (0 = (select ISACTIVE from dbo.JOBOCCURRENCE where ID = @JOBOCCURRENCEID)) and    -- Job occurrence is inactive
        (exists (select 1 from @ASSIGNMENTS where ASSIGN = 1))                            -- Someone is being assigned to it
        raiserror('BBERR_ASSIGNVOLUNTEERS_OCCURRENCEISINACTIVE', 13, 1);

    --remove assignments
    declare @contextCache varbinary(128);
    set @contextCache = CONTEXT_INFO();

    if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

    delete dbo.VOLUNTEERASSIGNMENT 
    from dbo.VOLUNTEERASSIGNMENT
    inner join @ASSIGNMENTS VOLUNTEERS 
        on VOLUNTEERS.VOLUNTEERASSIGNMENTID = VOLUNTEERASSIGNMENT.ID
    where 
        VOLUNTEERS.ASSIGN = 0;

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    if @DATESVALID_FOR_OCCURRENCE = 0 and @SINGLEDAY_EDIT = 0 
        raiserror('BBERR_ASSIGNVOLUNTEERS_NOOCCURRENCEEXISTS', 13, 1);

    select @JOBOCCURRENCE_STARTDATE = STARTDATE from dbo.JOBOCCURRENCE where ID = @JOBOCCURRENCEID;

    if @STARTDATE < @JOBOCCURRENCE_STARTDATE
        set @STARTDATE = @JOBOCCURRENCE_STARTDATE;

    if exists (
            select
                1
            from 
                dbo.VOLUNTEERASSIGNMENT 
            inner join dbo.JOBOCCURRENCE on JOBOCCURRENCE.ID = @JOBOCCURRENCEID
            inner join dbo.JOBOCCURRENCE ASSIGNEDJOB on ASSIGNEDJOB.ID = VOLUNTEERASSIGNMENT.JOBOCCURRENCEID
            inner join @ASSIGNMENTS VOLUNTEERS on VOLUNTEERASSIGNMENT.VOLUNTEERID = VOLUNTEERS.VOLUNTEERID and VOLUNTEERASSIGNMENT.DATE = @STARTDATE
            where
                (VOLUNTEERS.ASSIGN = 1)
                and ASSIGNEDJOB.ID <> JOBOCCURRENCE.ID
                and ((ASSIGNEDJOB.STARTTIME < JOBOCCURRENCE.ENDTIME and ASSIGNEDJOB.ENDTIME > JOBOCCURRENCE.STARTTIME)
                or (ASSIGNEDJOB.STARTTIME = JOBOCCURRENCE.STARTTIME and ASSIGNEDJOB.ENDTIME = JOBOCCURRENCE.ENDTIME)
                or ASSIGNEDJOB.STARTTIME = '' or ASSIGNEDJOB.ENDTIME = ''
                or JOBOCCURRENCE.STARTTIME = '' or JOBOCCURRENCE.ENDTIME  = ''))
        raiserror('BBERR_VOLUNTEERALREADYSCHEDULED', 13, 1);

    --add assignments
    --allow add/edit on days with assignments that no longer fall within timeframe of job occurrence
    if @SINGLEDAY_EDIT = 1 and @DATESVALID_FOR_OCCURRENCE = 0 and 
        exists(select 1 from @ASSIGNMENTS where (ASSIGN = 1 and VOLUNTEERASSIGNMENTID is null))

        insert into dbo.VOLUNTEERASSIGNMENT(JOBOCCURRENCEID, VOLUNTEERID, DATE, ADDEDBYID, CHANGEDBYID, DATECHANGED, DATEADDED)
        select 
            @JOBOCCURRENCEID, 
            VOLUNTEERS.VOLUNTEERID, 
            @STARTDATE, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        from @ASSIGNMENTS VOLUNTEERS
        inner join dbo.JOBOCCURRENCE 
            on JOBOCCURRENCE.ID = @JOBOCCURRENCEID
        where 
            (VOLUNTEERS.ASSIGN = 1 and VOLUNTEERS.VOLUNTEERASSIGNMENTID is null) and
            (@ISADMIN = 1 or 
                (@APPUSER_IN_NONRACROLE = 1 or
                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, VOLUNTEERS.VOLUNTEERID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                and
                (@APPUSER_IN_NONSITEROLE = 1 or
                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, VOLUNTEERS.VOLUNTEERID, @APPUSER_IN_NOSITEROLE) = 1)
            ) and
            not exists ( --don't include in list if a conflict exists on the specific date 
                select 1
                from dbo.VOLUNTEERASSIGNMENT 
                inner join dbo.JOBOCCURRENCE ASSIGNEDJOB 
                    on ASSIGNEDJOB.ID = VOLUNTEERASSIGNMENT.JOBOCCURRENCEID
                where 
                    VOLUNTEERASSIGNMENT.VOLUNTEERID = VOLUNTEERS.VOLUNTEERID and VOLUNTEERASSIGNMENT.DATE = @STARTDATE
                    and ((ASSIGNEDJOB.STARTTIME < JOBOCCURRENCE.ENDTIME and ASSIGNEDJOB.ENDTIME > JOBOCCURRENCE.STARTTIME)
                    or (ASSIGNEDJOB.STARTTIME = JOBOCCURRENCE.STARTTIME and ASSIGNEDJOB.ENDTIME = JOBOCCURRENCE.ENDTIME)
                    or ASSIGNEDJOB.STARTTIME = '' or ASSIGNEDJOB.ENDTIME = ''
                    or JOBOCCURRENCE.STARTTIME = '' or JOBOCCURRENCE.ENDTIME  = ''));
    else
        insert into dbo.VOLUNTEERASSIGNMENT(JOBOCCURRENCEID, VOLUNTEERID, DATE, ADDEDBYID, CHANGEDBYID, DATECHANGED, DATEADDED)
        select 
            @JOBOCCURRENCEID, 
            VOLUNTEER_DATES.VOLUNTEERID, 
            VOLUNTEER_DATES.ASSIGNMENTDATE, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        from 
            (select 
                VOLUNTEERS.VOLUNTEERASSIGNMENTID,
                VOLUNTEERS.VOLUNTEERID, 
                DATES.ASSIGNMENTDATE,
                ASSIGN,
                OPENINGS,
                ROW_NUMBER() OVER (PARTITION BY DATES.ASSIGNMENTDATE order by DATES.ASSIGNMENTDATE, CONSTITUENT.KEYNAME) as ROWNUMBER
             from @ASSIGNMENTS VOLUNTEERS
             inner join dbo.CONSTITUENT
                on CONSTITUENT.ID = VOLUNTEERS.VOLUNTEERID
             cross join dbo.UFN_JOBOCCURRENCE_SCHEDULE(@STARTDATE, @ENDDATE, @JOBOCCURRENCEID, null) DATES
             where not exists ( --don't include in list if a conflict exists on the specific date 
                select 1
                from dbo.VOLUNTEERASSIGNMENT 
                cross apply dbo.UFN_JOBOCCURRENCE_SCHEDULE(VOLUNTEERASSIGNMENT.DATE, VOLUNTEERASSIGNMENT.DATE, VOLUNTEERASSIGNMENT.JOBOCCURRENCEID, null) ASSIGNEDJOB 
                where 
                    VOLUNTEERASSIGNMENT.VOLUNTEERID = VOLUNTEERS.VOLUNTEERID and VOLUNTEERASSIGNMENT.DATE = DATES.ASSIGNMENTDATE
                    and ((ASSIGNEDJOB.JOBSTARTTIME < DATES.JOBENDTIME and ASSIGNEDJOB.JOBENDTIME > DATES.JOBSTARTTIME)
                    or (ASSIGNEDJOB.JOBSTARTTIME = DATES.JOBSTARTTIME and ASSIGNEDJOB.JOBENDTIME = DATES.JOBENDTIME)
                    or ASSIGNEDJOB.JOBSTARTTIME = '' or ASSIGNEDJOB.JOBENDTIME = ''
                    or DATES.JOBSTARTTIME = '' or DATES.JOBENDTIME  = ''))
                and (VOLUNTEERS.ASSIGN = 1 and VOLUNTEERS.VOLUNTEERASSIGNMENTID is null)
            ) VOLUNTEER_DATES
        where 
            (@SINGLEDAY_EDIT = 1 or (VOLUNTEER_DATES.ROWNUMBER <= VOLUNTEER_DATES.OPENINGS)) and
            (@ISADMIN = 1 or 
                (@APPUSER_IN_NONRACROLE = 1 or
                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, VOLUNTEER_DATES.VOLUNTEERID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                and
                (@APPUSER_IN_NONSITEROLE = 1 or
                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, VOLUNTEER_DATES.VOLUNTEERID, @APPUSER_IN_NOSITEROLE) = 1)
            );
end try

begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0;