UFN_ALERT_GETAPPLICATIONLINK_2

create function dbo.UFN_ALERT_GETAPPLICATIONLINK_2
(
  @PAGEID uniqueidentifier,
  @CONTEXTRECORDID uniqueidentifier = null,
  @TABID uniqueidentifier = null
)
returns nvarchar(2048)
with execute as owner
as begin

  declare @HOSTINGDATABASEID nvarchar(36);
  declare @HOSTINGSITEINFOID nvarchar(36);
  declare @HOSTINGLINKURL nvarchar(1024);
  declare @ROOTURL nvarchar(1024);
  declare @QUERYSTRING nvarchar(1024);
  declare @SELECTEDDATABASE nvarchar(128);
  declare @TARGET nvarchar(500);

  select
    @HOSTINGDATABASEID = cast(value as nvarchar(36)) 
  from sys.fn_listextendedproperty(N'Hosting_DatabaseID', default, default, default, default, default, default);

  select
    @HOSTINGLINKURL = cast(value as nvarchar(1024)) 
  from sys.fn_listextendedproperty(N'Hosting_LinkUrl', default, default, default, default, default, default);

  if @HOSTINGLINKURL is null
    select
      @ROOTURL = [ROOT],
      @SELECTEDDATABASE = [SELECTEDDATABASE]
    from dbo.[APPLICATIONURLPARTS]
  else
    begin
      set @ROOTURL = @HOSTINGLINKURL;
      set @SELECTEDDATABASE = @HOSTINGDATABASEID;
    end

  if @ROOTURL is null
    return '<No root url was found - specify root url in the email alerts settings dialog.>';

  -- hosted

  if @HOSTINGDATABASEID is not null
    begin
      select
        @HOSTINGSITEINFOID = cast(value as nvarchar(36)) 
      from sys.fn_listextendedproperty(N'Hosting_SiteInfoID', default, default, default, default, default, default);

      set @QUERYSTRING = '?SITEINFOID=' + @HOSTINGSITEINFOID + '&DATABASEID=' + @HOSTINGDATABASEID;

      set @TARGET = '&pageId=' + cast(@PAGEID as nvarchar(36));

      if @TABID is not null
        set @TARGET = @TARGET + '&tabId=' + cast(@TABID as nvarchar(36));

      if @CONTEXTRECORDID is not null and exists (select top 1 1 from dbo.[PAGEDEFINITIONCATALOG] where [ID] = @PAGEID and [CONTEXTRECORDTYPEID] is not null)
        set @TARGET = @TARGET + '&recordId=' + cast(@CONTEXTRECORDID as nvarchar(36));

      set @QUERYSTRING = @QUERYSTRING + '&TARGET=' + dbo.[UFN_URLENCODE]('webui/webshellpage.aspx?databasename=' + @SELECTEDDATABASE + '#pageType=p' + @TARGET)
    end
  -- not hosted

  else
    begin
      if right(@ROOTURL, 1) <> '/' set @ROOTURL = @ROOTURL + '/';

      set @QUERYSTRING = '&pageId=' + cast(@PAGEID as nvarchar(36));

      set @ROOTURL = @ROOTURL + 'webui/webshellpage.aspx?databasename=' + @SELECTEDDATABASE + '#pageType=p'

      if @TABID is not null
        set @QUERYSTRING = @QUERYSTRING + '&tabId=' + cast(@TABID as nvarchar(36));

      if @CONTEXTRECORDID is not null and exists (select top 1 1 from dbo.[PAGEDEFINITIONCATALOG] where [ID] = @PAGEID and [CONTEXTRECORDTYPEID] is not null)
        set @QUERYSTRING = @QUERYSTRING + '&recordId=' + cast(@CONTEXTRECORDID as nvarchar(36));
    end

  return @ROOTURL + @QUERYSTRING;
end