USP_DATAFORMTEMPLATE_EDIT_APPUSERSYSTEMROLE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_APPUSERSYSTEMROLE
(
  @ID uniqueidentifier,
  @SYSTEMROLEID uniqueidentifier,
  @SECURITYMODECODE tinyint,
  @SITES xml, 
  @CHANGEAGENTID uniqueidentifier,
  @BRANCHSITEID uniqueidentifier,
  @CONSTITUENTSECURITYMODECODE tinyint,
  @CONSTITUENTSECURITYGROUPS xml
)
as
begin
  set nocount on;

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

  declare @CURRENTDATE datetime;
  declare @ISPROXYUSER bit = 0;
  declare @PROXYOWNERNAME nvarchar(128);
  declare @APPUSERID uniqueidentifier = null;
  declare @PROXYUSERHASPERMISSION bit = 0;
  declare @PROXYCOUNT int = 0;
  declare @ISAPPUSERSYSADMIN bit = 0;
  declare @PROXYOWNERID uniqueidentifier = null;
  declare @ISPROXYOWNERSYSADMIN bit = 0;
  set @CURRENTDATE=getdate();

  if @SECURITYMODECODE <> 3 
    set @BRANCHSITEID = null

  begin try
    if @CONSTITUENTSECURITYMODECODE = 2 and @CONSTITUENTSECURITYGROUPS is null begin
      raiserror('At least one constituent security group must be selected.', 13, 1);
    end

    if @SECURITYMODECODE = 2 and @SITES is null begin
      raiserror('At least one site must be selected.', 13, 1);
    end

    select @APPUSERID= APPUSERID from dbo.SYSTEMROLEAPPUSER where SYSTEMROLEAPPUSER.ID = @ID;
    select @ISPROXYUSER = dbo.UFN_APPUSER_ISPROXYUSER(@APPUSERID)

    select @PROXYCOUNT = count(1) from dbo.APPUSER 
    join dbo.SYSTEMROLEAPPUSER on APPUSER.ID = SYSTEMROLEAPPUSER.APPUSERID 
    where [PROXYOWNERID] = @APPUSERID and [SYSTEMROLEID] = @SYSTEMROLEID;

    select @PROXYOWNERID = PROXYOWNERID from dbo.APPUSER where ID = @APPUSERID;
    select @ISPROXYOWNERSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@PROXYOWNERID);

    select @ISAPPUSERSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@APPUSERID)

    if @ISPROXYUSER = 1
        begin



            if @ISPROXYOWNERSYSADMIN = 0
                begin
                    select @PROXYUSERHASPERMISSION = dbo.UFN_PROXYUSER_CHECKROLESANDPERMISSIONS(@APPUSERID,@SITES,@SYSTEMROLEID,
                                             @SECURITYMODECODE,@BRANCHSITEID,@CONSTITUENTSECURITYMODECODE, @CONSTITUENTSECURITYGROUPS)
                end
             else
                begin
                    set @PROXYUSERHASPERMISSION = 1;
                end

            if @PROXYUSERHASPERMISSION = 0 
                begin
                    raiserror('BBERR_PROXYUSER_PERMISSION', 13, 1);
                end
        end

    exec dbo.USP_SYSTEMROLEAPPUSER_VALIDATESECURITYGROUPS @CONSTITUENTSECURITYGROUPS;

    update
      dbo.SYSTEMROLEAPPUSER
    set
      SYSTEMROLEAPPUSER.SYSTEMROLEID = @SYSTEMROLEID,
      SYSTEMROLEAPPUSER.SECURITYMODECODE = @SECURITYMODECODE,
      SYSTEMROLEAPPUSER.DATECHANGED = @CURRENTDATE,
      SYSTEMROLEAPPUSER.CHANGEDBYID = @CHANGEAGENTID,
      SYSTEMROLEAPPUSER.BRANCHSITEID = @BRANCHSITEID,
      SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE = @CONSTITUENTSECURITYMODECODE
    where
      SYSTEMROLEAPPUSER.ID = @ID;

    -- Clear the sites collection if 'All records' or 'Records with no site assigned' is set
    if @SECURITYMODECODE = 0 or @SECURITYMODECODE = 1
      set @SITES = null

    exec dbo.USP_SYSTEMROLEAPPUSER_GETSITES_UPDATEFROMXML @ID, @SITES, @CHANGEAGENTID, @CURRENTDATE;

    -- Clear constituent security groups if 'All records' or 'Records with no security group assigned' is set
    if @CONSTITUENTSECURITYMODECODE = 0 or @CONSTITUENTSECURITYMODECODE = 1
      set @CONSTITUENTSECURITYGROUPS = null

    exec dbo.USP_SYSTEMROLEAPPUSER_GETSECURITYGROUPS_UPDATEFROMXML @ID, @CONSTITUENTSECURITYGROUPS, @CHANGEAGENTID, @CURRENTDATE;

     if isnull(@ISPROXYUSER,0) = 0 and @PROXYCOUNT > 0 and @ISAPPUSERSYSADMIN = 0
      begin
            exec dbo.USP_APPUSER_WITHPROXY_UPDATEPROXYPERMISSIONS @APPUSERID, @SITES, @SYSTEMROLEID, @SECURITYMODECODE, @BRANCHSITEID, @CONSTITUENTSECURITYMODECODE, @CONSTITUENTSECURITYGROUPS, @PROXYCOUNT, @CHANGEAGENTID, @CURRENTDATE;
      end
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch    
end