USP_DATAFORMTEMPLATE_ADD_ASSIGNVOLUNTEER_MULTIPLEDAYS

The save procedure used by the add dataform template "Volunteer Assign Job Multiple Days Add Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ADD_MULTIPLE_ACTION	nvarchar(73)	IN	Input parameter indicating the context ID for the record being added.
@VOLUNTEERID	uniqueidentifier	IN
@JOBOCCURRENCEID	uniqueidentifier	IN
@STARTDATE	date	IN	Dates
@ENDDATE	date	IN
@ASSIGN_DATES	xml	IN
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ASSIGNVOLUNTEER_MULTIPLEDAYS
(
    @ID uniqueidentifier = null output,
    @CURRENTAPPUSERID uniqueidentifier,
    @ADD_MULTIPLE_ACTION nvarchar(73),
    @VOLUNTEERID uniqueidentifier,
    @JOBOCCURRENCEID uniqueidentifier,
    @STARTDATE date = null,
    @ENDDATE date = null,
    @ASSIGN_DATES xml = null,
    @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;

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 @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

begin try    
    declare @DATES table (VOLUNTEERASSIGNMENTID uniqueidentifier, DATE date, ASSIGN bit);

    insert into @DATES (VOLUNTEERASSIGNMENTID, DATE, ASSIGN) 
    select 
        VOLUNTEERASSIGNMENTID, 
        DATE, 
        ASSIGN
    from
        (select
            T.c.value('(VOLUNTEERASSIGNMENTID)[1]','uniqueidentifier') as VOLUNTEERASSIGNMENTID,
            T.c.value('(DATE)[1]','date') as DATE,
            T.c.value('(ASSIGN)[1]','bit') as ASSIGN
        from @ASSIGN_DATES.nodes('/ASSIGN_DATES/ITEM') T(c)) as DATES;

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

    --remove dates

    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 @DATES DATES 
        on DATES.VOLUNTEERASSIGNMENTID = VOLUNTEERASSIGNMENT.ID
    where 
        DATES.ASSIGN = 0;

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    insert into dbo.VOLUNTEERASSIGNMENT(JOBOCCURRENCEID, VOLUNTEERID, DATE, ADDEDBYID, CHANGEDBYID, DATECHANGED, DATEADDED)
    select 
        @JOBOCCURRENCEID, 
        @VOLUNTEERID, 
        DATES.DATE, 
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE, 
        @CURRENTDATE
    from @DATES DATES
    where 
        (DATES.ASSIGN = 1 and DATES.VOLUNTEERASSIGNMENTID is null) and
        (@ISADMIN = 1 or 
            (@APPUSER_IN_NONRACROLE = 1 or
            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, @VOLUNTEERID, @APPUSER_IN_NOSECGROUPROLE) = 1)
            and
            (@APPUSER_IN_NONSITEROLE = 1 or
            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, @VOLUNTEERID, @APPUSER_IN_NOSITEROLE) = 1)
        );
end try

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

return 0;