USP_APPUSERCLAIMS_LINKAPPUSER

CREATE procedure dbo.USP_APPUSERCLAIMS_LINKAPPUSER
(
  @NAMEIDENTIFIER nvarchar(450),
  @WSFEDERATIONINVITATIONID uniqueidentifier = null,
  @EMAILADDRESS nvarchar(100) = null,
  @SKIPMAKEANONYMOUS bit = 0
)
as
begin
  set nocount on;

  declare @APPUSERID uniqueidentifier = null;

  select
    @APPUSERID = APPUSER.ID
  from dbo.APPUSER
  inner join dbo.APPUSERTHIRDPARTYAUTH
    on APPUSER.ID = APPUSERTHIRDPARTYAUTH.ID
  left outer join dbo.APPUSERCLAIMS
    on APPUSER.ID = APPUSERCLAIMS.APPUSERID
  where APPUSERTHIRDPARTYAUTH.INACTIVE = 0
    and APPUSERCLAIMS.NAMEIDENTIFIER is null
    and APPUSERTHIRDPARTYAUTH.INVITATIONID = @WSFEDERATIONINVITATIONID;

  if @APPUSERID is null
    begin
      set @WSFEDERATIONINVITATIONID = null;

      select
        @APPUSERID = APPUSER.ID
      from dbo.APPUSER
      left outer join dbo.APPUSERTHIRDPARTYAUTH
        on APPUSER.ID = APPUSERTHIRDPARTYAUTH.ID
      left outer join dbo.APPUSERCLAIMS
        on APPUSER.ID = APPUSERCLAIMS.APPUSERID
      where isnull(APPUSERTHIRDPARTYAUTH.INACTIVE, 0) = 0
        and APPUSERCLAIMS.NAMEIDENTIFIER is null
        and APPUSER.EMAILADDRESS = @EMAILADDRESS;
    end
  else
    -- Bug 631823 

    -- If invitationID is mapped to one user, but the email address belongs to another...raise error

    if exists(
      select APPUSER.ID
      from dbo.APPUSER
      left outer join dbo.APPUSERCLAIMS
        on APPUSER.ID = APPUSERCLAIMS.APPUSERID
      where APPUSER.ID <> @APPUSERID
        and APPUSER.EMAILADDRESS = @EMAILADDRESS
    )
      begin
        raiserror('BBERR_DUPLICATEEMAILADDRESS', 13, 1);
        return 1;
      end

  if @APPUSERID is not null
    begin
      declare @CURRENTDATE datetime = getdate();
      declare @CHANGEAGENTID uniqueidentifier;
      declare @OLDUSERNAME nvarchar(256);

      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

      select @OLDUSERNAME = USERNAME from dbo.APPUSER where ID=@APPUSERID;

      insert into dbo.APPUSERCLAIMS
        (NAMEIDENTIFIER, APPUSERID, EMAILADDRESS, ISREGISTERED)
      values
        (@NAMEIDENTIFIER, @APPUSERID, @EMAILADDRESS, 0);

      if @SKIPMAKEANONYMOUS = 0
        begin
          update dbo.APPUSER
          set APPUSER.USERSID = SUSER_SID('NT AUTHORITY\ANONYMOUS LOGON'),
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
      where APPUSER.ID = @APPUSERID
          and APPUSER.USERSID <> SUSER_SID('NT AUTHORITY\ANONYMOUS LOGON');

          update dbo.CHANGEAGENT
          set USERNAME = (select USERNAME from dbo.APPUSER where ID = @APPUSERID)
          where USERNAME =  @OLDUSERNAME;
        end

      if exists(select 1 from dbo.APPUSERTHIRDPARTYAUTH where ID = @APPUSERID)
        update dbo.APPUSERTHIRDPARTYAUTH set
          INVITATIONSTATUSCODE = 3, -- Active

          INVITATIONDATE = @CURRENTDATE,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where ID = @APPUSERID;

      else
        insert into dbo.APPUSERTHIRDPARTYAUTH
          (ID, INVITATIONID, INVITATIONSTATUSCODE, INVITATIONDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values
          (@APPUSERID, @WSFEDERATIONINVITATIONID, 3, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    end

  select @APPUSERID as APPUSERID;

  return 0;
end