USP_DATAFORMTEMPLATE_EDIT_SYSTEMROLEADDCUSTOMUSERS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SYSTEMROLEADDCUSTOMUSERS(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @USERNAMELIST xml
    )
as

set nocount on;

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

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

BEGIN TRY

    update dbo.SYSTEMROLE set
        CHANGEDBYID = @CHANGEAGENTID, DATECHANGED=@CURRENTDATE
    where ID = @ID;

    --Parse and validate user name list
    declare @NAMES table (CUSTOM_AUTHENTICATION_USERID nvarchar(255))
    insert into @NAMES (CUSTOM_AUTHENTICATION_USERID)
        select distinct CUSTOM_AUTHENTICATION_USERID from dbo.[UFN_SYSTEMROLE_GETCUSTOMUSERNAMELIST_FROMITEMLISTXML](@USERNAMELIST);

    --Add Appusers that don't yet exist
    insert into dbo.APPUSER (CUSTOM_AUTHENTICATION_USERID, [DISPLAYNAME], USERSID, [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
        select distinct CUSTOM_AUTHENTICATION_USERID,CUSTOM_AUTHENTICATION_USERID, SUSER_SID('NT AUTHORITY\ANONYMOUS LOGON'), @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE    
        from @NAMES as NEWUSERS
        where NEWUSERS.CUSTOM_AUTHENTICATION_USERID not in (select CUSTOM_AUTHENTICATION_USERID from dbo.APPUSER);

    --Add users to role
    insert into dbo.SYSTEMROLEAPPUSER (SYSTEMROLEID, APPUSERID, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
        select @ID, APPUSER.ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from dbo.APPUSER
        where APPUSER.CUSTOM_AUTHENTICATION_USERID in (select CUSTOM_AUTHENTICATION_USERID from @NAMES)
        and APPUSER.ID not in (select APPUSERID from dbo.SYSTEMROLEAPPUSER where SYSTEMROLEID=@ID);

    --Update display names for any Appusers that don't have them
    update dbo.APPUSER set DISPLAYNAME = NEWUSERS.CUSTOM_AUTHENTICATION_USERID,CHANGEDBYID = @CHANGEAGENTID,DATECHANGED=@CURRENTDATE
    from dbo.APPUSER
        inner join @NAMES as NEWUSERS
        on APPUSER.CUSTOM_AUTHENTICATION_USERID = NEWUSERS.CUSTOM_AUTHENTICATION_USERID
    where len(APPUSER.DISPLAYNAME) = 0 and len(NEWUSERS.CUSTOM_AUTHENTICATION_USERID) > 0;

    --Update the "synchronized" flag for users that have been entered
    update dbo.SYSTEMROLEAPPUSER set FROMROLESYNC = 0,CHANGEDBYID = @CHANGEAGENTID,DATECHANGED=@CURRENTDATE
    from dbo.SYSTEMROLEAPPUSER
        inner join dbo.APPUSER on SYSTEMROLEAPPUSER.APPUSERID = APPUSER.ID
        inner join @NAMES as NEWUSERS on APPUSER.CUSTOM_AUTHENTICATION_USERID= NEWUSERS.CUSTOM_AUTHENTICATION_USERID
    where SYSTEMROLEID = @ID and SYSTEMROLEAPPUSER.FROMROLESYNC = 1;

    return 0;

END TRY
BEGIN CATCH
    exec dbo.USP_RAISE_ERROR;
    return 1;
END CATCH