USP_REGISTRANTREGISTRATION_CHANGEFRIENDLYURL

CREATE procedure dbo.USP_REGISTRANTREGISTRATION_CHANGEFRIENDLYURL
(
  @CLIENTSITESID int,
  @REGISTRANTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @ROLECODE tinyint,
  @ROLECODEORIGINAL tinyint,
  @PERSONALVANITYURL nvarchar(510),
  @PERSONALVANITYID int,
  @GROUPID uniqueidentifier = null,
  @GROUPVANITYURL nvarchar(510) = null,
  @GROUPVANITYID int = null,
  @USERID int
)
as
begin try

  declare @ParticipantID uniqueidentifier,
          @Group_ParticipantID uniqueidentifier,
          @PageType int = 0,
          @FriendlyURLID uniqueidentifier,
          @SEQUENCEID int,
          @PPageID int,
          @pkID int = 0

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

  if patindex('%[^0-9a-zA-Z_-]%',@PERSONALVANITYURL) <> 0  or patindex('%[^0-9a-zA-Z_-]%',@GROUPVANITYURL) <> 0 
     raiserror('INUSEVANITYURLFORMAT',13,1)  

  /*if @ROLECODE in (3,4) and @ROLECODEORIGINAL not in (3,4)
  begin
      if @PERSONALVANITYID > 0
      begin
        delete from dbo.FAFFRIENDLYURLPARAMS where SEQUENCEID in (select RealmID from dbo.VanityURL where ID = @PERSONALVANITYID) and PARTICIPANTID = @REGISTRANTID
            delete from dbo.VanityURL where ID = @PERSONALVANITYID
      end      
  end*/

  --if @ROLECODE not in (3,4) /* if not in a household member or head of household */

  --begin

    if @PERSONALVANITYURL = ''  
    begin
        if @PERSONALVANITYID > 0
      begin
            delete from dbo.FAFFRIENDLYURLPARAMS where SEQUENCEID in (select RealmID from dbo.VanityURL where ID = @PERSONALVANITYID) and PARTICIPANTID = @REGISTRANTID
            delete from dbo.VanityURL where ID = @PERSONALVANITYID    
      end
    end
    else
    begin        
          select @PARTICIPANTID=PARTICIPANTID from dbo.VanityURL 
          join dbo.FAFFRIENDLYURLPARAMS on VanityURL.RealmID = FAFFRIENDLYURLPARAMS.SEQUENCEID and VanityURL.RealmTypeID = 7
          where VanityURL.ClientSitesID = @CLIENTSITESID and VanityURL.VanityURL = @PERSONALVANITYURL

        if @PARTICIPANTID is not null and @PARTICIPANTID <> @REGISTRANTID
            raiserror('INUSEVANITYURLFORPERSONAL',13,1)
        else
        begin
            if @PERSONALVANITYID is null or @PERSONALVANITYID = 0
            begin

                  set @FriendlyURLID = NEWID();

                exec dbo.USP_DATAFORMTEMPLATE_ADD_FAF_FRIENDLY_URL_PARAMETERS @ID=@FriendlyURLID output, @CHANGEAGENTID=@CHANGEAGENTID, @PAGETYPE=0,@PARTICIPANTID=@REGISTRANTID

                select @SEQUENCEID=SEQUENCEID from dbo.FAFFRIENDLYURLPARAMS where ID = @FriendlyURLID

                select @PPageID=cast(Value as int) from dbo.CMSSITESETTING where ClientSitesID = @CLIENTSITESID and ENUMID = 30

                exec dbo.spAddUpdate_VanityURL @PKID=@PERSONALVANITYID output, @RealmTypeID=7,@RealmID=@SEQUENCEID,@ClientUserID=@USERID, @PageID=@PPageID,@TabID=1,@VanityURL=@PERSONALVANITYURL,@TargetURL=null,@ClientSitesID=@CLIENTSITESID

            end
            else
                update dbo.[VanityURL] set VanityURL = @PERSONALVANITYURL,  DateChanged = GetDate() where ID = @PERSONALVANITYID 
        end
    end /* end handling personal friendly URL */
  --end 


  if (@GROUPID is not null and @ROLECODE in(0,1,3))
  begin  
      if @GROUPVANITYURL = ''
      begin
        if @GROUPVANITYID > 0
      begin
        declare @RealmID int
        select @RealmID = RealmID from dbo.VanityURL where ID = @GROUPVANITYID

          delete from dbo.FAFFRIENDLYURLPARAMS where SEQUENCEID = @RealmID and PARTICIPANTID = @GROUPID
          delete from dbo.VanityURL where ID = @GROUPVANITYID 
      end
      end
      else
      begin
              select @Group_ParticipantID=PARTICIPANTID from dbo.VanityURL 
              join dbo.FAFFRIENDLYURLPARAMS on VanityURL.RealmID = FAFFRIENDLYURLPARAMS.SEQUENCEID and VanityURL.RealmTypeID = 7
              WHERE VanityURL.ClientSitesID = @CLIENTSITESID and VanityURL.VanityURL = @GROUPVANITYURL             

          --select @Group_ParticipantID

          if @Group_ParticipantID is not null and @Group_ParticipantID <> @GROUPID
            raiserror('INUSEVANITYURLFORGROUP', 13,1)
          else  
          begin
            if @GROUPVANITYID is null or @GROUPVANITYID = 0
            begin
                if @ROLECODE = 0
                  set @PageType = 2
                else
                  set @PageType = @ROLECODE

                  set @FriendlyURLID = NEWID();

                exec dbo.USP_DATAFORMTEMPLATE_ADD_FAF_FRIENDLY_URL_PARAMETERS @ID=@FriendlyURLID output, @CHANGEAGENTID=@CHANGEAGENTID, @PAGETYPE=@PageType,@PARTICIPANTID=@GROUPID

                select @SEQUENCEID=SEQUENCEID from dbo.FAFFRIENDLYURLPARAMS where ID = @FriendlyURLID
                select @PPageID=cast(Value as int) from dbo.CMSSITESETTING where ClientSitesID = @CLIENTSITESID and ENUMID = 30

                exec dbo.spAddUpdate_VanityURL @PKID=@GROUPVANITYID output, @RealmTypeID=7,@RealmID=@SEQUENCEID,@ClientUserID=@USERID, @PageID=@PPageID,@TabID=1,@VanityURL=@GROUPVANITYURL,@TargetURL=null,@ClientSitesID=@CLIENTSITESID
            end
            else
                update dbo.[VanityURL] set VanityURL = @GROUPVANITYURL, DateChanged = GetDate() where ID = @GROUPVANITYID  
          end
      end /* end for group inner  */
  end

end try
begin catch
  exec USP_RAISE_ERROR
  return 1;
end catch

return 0;