USP_DATAFORMTEMPLATE_EDIT_APPLICATIONUSERCOPYROLES

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_APPLICATIONUSERCOPYROLES
(
  @ID uniqueidentifier,
  @COPYFROM uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  if (@ID = @COPYFROM)
      begin
        raiserror('ERR_APPUSER_ISSAMEASCOPYFROM', 13, 1);
        return
      end

  if (not exists(select top 1 1 from dbo.APPUSER where ID = @COPYFROM))
      begin
        raiserror('ERR_APPUSER_COPYFROMDOESNOTEXIST', 13, 1);
        return
      end

  set nocount on;

  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    /* cache current context information */
    declare @CONTEXTCACHE uniqueidentifier;
    set @CONTEXTCACHE = CONTEXT_INFO();

    /* set CONTEXT_INFO to @CHANGEAGENTID */
    if not @CHANGEAGENTID is null
      set CONTEXT_INFO @CHANGEAGENTID;

    declare @ISPROXYUSER bit = 0;
    declare @SOURCEUSERPROXYOWNERID uniqueidentifier;
    declare @ISPROXYOWNER bit = 0;
    declare @PROXYOWNERID uniqueidentifier;
    declare @ISPROXYOWNERSYSADMIN bit;

    --This variable is used to store SYSTEMROLEAPPUSERID of target appusers.
    declare @SYSTEMROLEAPPUSER table (ID uniqueidentifier);

    --This variable is used to store ID of target appusers.
    declare @TARGETAPPUSER table (ID uniqueidentifier);

    select @ISPROXYUSER = dbo.UFN_APPUSER_ISPROXYUSER(@ID);

    --If target user is proxy user then we need to check source user has all proxy owners roles
    if @ISPROXYUSER = 1
       begin
            select @PROXYOWNERID = PROXYOWNERID from dbo.APPUSER where ID = @ID;
            select @ISPROXYOWNERSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@PROXYOWNERID);

            if(@ISPROXYOWNERSYSADMIN = 0)
                begin
                    if((select dbo.UFN_PROXYUSER_COMPAREROLES(@PROXYOWNERID, @COPYFROM)) = 0)
                       begin
                           raiserror('ERR_PROXYUSER_PROXYOWNER_COPYFORM_ROLESNOTMATCHED', 13, 1);
                       end
                end
       end
    else
        begin
            select @SOURCEUSERPROXYOWNERID = PROXYOWNERID from dbo.APPUSER where ID = @COPYFROM;
            select @ISPROXYOWNER = dbo.UFN_APPUSER_HASPROXYUSER(@ID);

            if(@ID = @SOURCEUSERPROXYOWNERID)
                begin
                    raiserror('ERR_PROXYUSER_PROXYOWNER_COPYFORM_SOURCEPROXYUSER_TARGETPROXYOWNER', 13, 1);
                end
        end

    --Insert ID of SYSTEMROLEAPPUSER of proxyowner
    insert into @SYSTEMROLEAPPUSER
    select ID from dbo.SYSTEMROLEAPPUSER where APPUSERID = @ID;

    insert into @TARGETAPPUSER
    select @ID;

    --If target user is proxy owner then system roles should be copied from source user to proxyowner and all the proxy users of the proxy owner.
    if @ISPROXYOWNER =1
        begin
            --when target and source user doesn't have proxy owner and proxy user relationship

            --First we have to delete all the roles from proxy owner and its proxy users
            --So in below two statements we are taking ID of SYSTEMROLEAPPUSER of proxy owner and its proxy users
            --Insert ID of SYSTEMROLEAPPUSER of proxy users of proxy owner

                insert into @SYSTEMROLEAPPUSER
                select SYSTEMROLEAPPUSER.ID from dbo.SYSTEMROLEAPPUSER 
                inner join dbo.APPUSER on SYSTEMROLEAPPUSER.APPUSERID = APPUSER.ID
                where APPUSER.PROXYOWNERID = @ID;

            --We have to add system roles for all the proxy users also so taking ID of all the proxy users and proxy owner
                insert into @TARGETAPPUSER
                select ID from dbo.APPUSER where  PROXYOWNERID = @ID;

        end 

    delete from dbo.SYSTEMROLEAPPUSERSITE where SYSTEMROLEAPPUSERID in (select ID from @SYSTEMROLEAPPUSER);
    delete from dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY where SYSTEMROLEAPPUSERID in (select ID from @SYSTEMROLEAPPUSER);

    /* this table may not exist depending on product flags */
    if (exists (select top 1 1 from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'SYSTEMROLEAPPUSERACCOUNTINGELEMENTSECURITY'))
      begin
           delete from dbo.SYSTEMROLEAPPUSERACCOUNTINGELEMENTSECURITY where SYSTEMROLEAPPUSERID in (select ID from @SYSTEMROLEAPPUSER);
      end
     delete from dbo.SYSTEMROLEAPPUSER where  APPUSERID in (select ID from @TARGETAPPUSER);

    /* reset CONTEXT_INFO to previous value */
    if not @CONTEXTCACHE is null
      set CONTEXT_INFO @CONTEXTCACHE;

    insert into dbo.SYSTEMROLEAPPUSER(APPUSERID, SYSTEMROLEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, FROMROLESYNC, SECURITYMODECODE, BRANCHSITEID, CONSTITUENTSECURITYMODECODE, ACCOUNTINGELEMENTSECURITYMODECODE)
    select @ID, SYSTEMROLEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, FROMROLESYNC, SECURITYMODECODE, BRANCHSITEID, CONSTITUENTSECURITYMODECODE, ACCOUNTINGELEMENTSECURITYMODECODE from dbo.SYSTEMROLEAPPUSER where APPUSERID = @COPYFROM;

    --If target user is proxy owner then we need to insert system roles for proxy owner and its proxy users
    --We have already added SYSTEMROLEAPPUSER for proxy owner at line 169. Now we have add SYSTEMROLEAPPUSER for all the proxy owners
    --We need APPUSERID of proxy users from APPUSER table and SYSTEMROLEID's of source user from SYSTEMROLEAPPUSER table
    if @ISPROXYOWNER = 1 
        begin
            insert into dbo.SYSTEMROLEAPPUSER(APPUSERID, SYSTEMROLEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, FROMROLESYNC, SECURITYMODECODE, BRANCHSITEID, CONSTITUENTSECURITYMODECODE, ACCOUNTINGELEMENTSECURITYMODECODE)
            select APPUSER.ID,SYSTEMROLEAPPUSER.SYSTEMROLEID,@CHANGEAGENTID,@CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, FROMROLESYNC, SECURITYMODECODE, BRANCHSITEID, CONSTITUENTSECURITYMODECODE, ACCOUNTINGELEMENTSECURITYMODECODE
            from dbo.APPUSER
            cross join dbo.SYSTEMROLEAPPUSER 
            where APPUSER.ID in (select ID from @TARGETAPPUSER where ID<>@ID) 
            and SYSTEMROLEAPPUSER.APPUSERID=@COPYFROM;
        end

    --This table is used to copy site and constituent security settings
    --If @ISPROXYUSER = 1 then we have to copy settings from proxy owner
    --If @ISPROXYUSER = 1 and Proxy owner is sysadmin then we have to copy settings from source user
    --If @ISPROXYOWNER = 1 then we to copy settings to proxy owner and all proxy users

    select copyfrom.ID as FROMID, copyto.ID as TOID
      into #IDMAP
      from dbo.SYSTEMROLEAPPUSER copyfrom
      inner join SYSTEMROLEAPPUSER copyto on copyfrom.APPUSERID = 
      case when (@ISPROXYUSER = 1 and @ISPROXYOWNERSYSADMIN = 0) then @PROXYOWNERID else @COPYFROM end
      and copyto.APPUSERID in (select ID from @TARGETAPPUSER)
      and copyto.SYSTEMROLEID = copyfrom.SYSTEMROLEID;

    /* this table may not exist depending on product flags */
    if (exists (select top 1 1 from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'SYSTEMROLEAPPUSERACCOUNTINGELEMENTSECURITY'))
        begin
             insert into dbo.SYSTEMROLEAPPUSERACCOUNTINGELEMENTSECURITY(SYSTEMROLEAPPUSERID, ACCOUNTINGELEMENTSECURITYGROUPID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
             select idmap.TOID, srau_aes.ACCOUNTINGELEMENTSECURITYGROUPID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
             from dbo.SYSTEMROLEAPPUSERACCOUNTINGELEMENTSECURITY srau_aes
             inner join #IDMAP idmap on srau_aes.SYSTEMROLEAPPUSERID = idmap.FROMID;
        end

    insert into dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY(SYSTEMROLEAPPUSERID, CONSTITUENTSECURITYATTRIBUTEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select idmap.TOID, srau_cs.CONSTITUENTSECURITYATTRIBUTEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY srau_cs
    inner join #IDMAP idmap on srau_cs.SYSTEMROLEAPPUSERID = idmap.FROMID;

    insert into dbo.SYSTEMROLEAPPUSERSITE(SYSTEMROLEAPPUSERID, SITEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select idmap.TOID, sraus.SITEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from dbo.SYSTEMROLEAPPUSERSITE sraus
    inner join #IDMAP idmap on sraus.SYSTEMROLEAPPUSERID = idmap.FROMID;

    --For proxy user we are coping systemroleappuser data from source user(line no 152).
    --We need to copy SECURITYMODECODE,CONSTITUENTSECURITYMODECODE and BRANCHSITEID from proxy owner
    --We need to copy SECURITYMODECODE,CONSTITUENTSECURITYMODECODE and BRANCHSITEID from source user if proxy owner is sys admin
    if @ISPROXYUSER = 1
        begin
            update PROXYUSER 
            set PROXYUSER.SECURITYMODECODE = PROXYOWNER.SECURITYMODECODE,
             PROXYUSER.CONSTITUENTSECURITYMODECODE = PROXYOWNER.CONSTITUENTSECURITYMODECODE,
             PROXYUSER.BRANCHSITEID = PROXYOWNER.BRANCHSITEID
            from dbo.SYSTEMROLEAPPUSER PROXYUSER 
            inner join dbo.SYSTEMROLEAPPUSER PROXYOWNER on PROXYUSER.SYSTEMROLEID = PROXYOWNER.SYSTEMROLEID
            where PROXYOWNER.APPUSERID = case when @ISPROXYOWNERSYSADMIN = 1 then @COPYFROM else @PROXYOWNERID end and PROXYUSER.APPUSERID = @ID;
        end
  end try
  begin catch
    EXEC dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;