USP_DATAFORMTEMPLATE_VIEW_EVENTSPONSORPROFILE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EVENTSPONSORPROFILE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CONSTITUENTNAME nvarchar(173) = null output,
    @EMAILADDRESSID uniqueidentifier = null output,
  @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @PICTURE varbinary(max) = null output,
    @LOGINASSPONSORLINK dbo.UDT_WEBADDRESS = null output,
    @URLFORMATSTRING varchar(512) = null output,
    @SHAREDKEY varchar(512) = null output,
    @BBNCUSERNAME varchar(512) =null output,
    @HAVEHQPAGE bit = null output,
    @HAVELOGINPAGE bit = null output,
    @WEBSITESTATUS bit = null output,
    @SENDPASSWORDURL nvarchar(512) = null output,
    @ISUSER bit = null output,
    @ALLOWSSO bit = null output,
    @ORGANIZATIONNAME nvarchar(173) = null output,
    @ORGANIZATIONEMAILADDRESSID uniqueidentifier = null output,
  @ORGANIZATIONEMAIL dbo.UDT_EMAILADDRESS = null output,
    @SPONSORSHIPID nvarchar(40) = null output,
    @STATUS nvarchar(10) = null output,
    @DATEADDED dbo.UDT_FUZZYDATE = null output,
    @FEESAMOUNT nvarchar(100) = null output,
  @SPONSORCONSTITUENTID uniqueidentifier = null output,
  @AUTHENTICATEDSOCAILMEDIA nvarchar(512) = null output,
  @ALLOW bit = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    declare @EVENTID uniqueidentifier = null;
    declare @CLIENTUSERID int;

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.
    select @DATALOADED = 1,
        @CONSTITUENTNAME = IC.NAME,
        @EMAILADDRESSID = IE.ID,
    @EMAILADDRESS = IE.EMAILADDRESS,
        @PICTURE = case when ES.ORGANIZATIONCONSTITUENTID is null then IC.PICTURETHUMBNAIL else OC.PICTURETHUMBNAIL end,
        @ORGANIZATIONNAME = OC.NAME,
    @ORGANIZATIONEMAILADDRESSID = OE.ID,
        @ORGANIZATIONEMAIL = OE.EMAILADDRESS,
        @SPONSORSHIPID = ES.LOOKUPID,
        @STATUS = case when ES.ISCANCELLED = 0 then 'Active' else 'Canceled' end,
        @DATEADDED = convert(nvarchar(8), ES.DATEADDED, 112),
        @FEESAMOUNT = ES.FEESAMOUNT,
        @EVENTID = ES.EVENTID,
        @CLIENTUSERID = dbo.fnGetUserIDFromLinkedRecordID(IC.SEQUENCEID, 0),
    @SPONSORCONSTITUENTID = isnull(OC.ID, IC.ID),
    @ALLOW = ES.ALLOWADMINACCESSHQ
    from dbo.EVENTSPONSOR ES(nolock)
    left outer join dbo.CONSTITUENT IC(nolock)
        on IC.ID = ES.CONSTITUENTID
    left outer join dbo.EMAILADDRESS IE(nolock)
        on IE.CONSTITUENTID = IC.ID and IE.ISPRIMARY = 1
    left outer join dbo.CONSTITUENT OC(nolock)
        on OC.ID = ES.ORGANIZATIONCONSTITUENTID
    left outer join dbo.EMAILADDRESS OE(nolock)
        on OE.CONSTITUENTID = OC.ID and OE.ISPRIMARY = 1
    where ES.ID = @ID

    declare @CLIENTSITESID int = null;
    declare @LOGINPAGEID int = null;
    declare @SITECONTENTID int = -1;

    select 
        @CLIENTSITESID = EX.CLIENTSITESID,
        @LOGINPAGEID = LP.VALUE, 
        @HAVEHQPAGE = case when HQ.VALUE is null then 0 else 1 end,
        @HAVELOGINPAGE = case when LP.VALUE is null then 0 else 1 end
    from EVENTEXTENSION EX  
    left outer join dbo.CMSSITESETTING HQ on EX.CLIENTSITESID = HQ.CLIENTSITESID and HQ.ENUMID = 30  
    left outer join dbo.CMSSITESETTING LP on EX.CLIENTSITESID = LP.CLIENTSITESID and LP.ENUMID = 3    
    where EX.EVENTID = @EVENTID;

    set @WEBSITESTATUS = dbo.UFN_CLIENTSITES_GETEVENTWEBSITESTATUS(@EVENTID);
    select @ALLOWSSO = VALUE from dbo.CMSSITESETTING where ENUMID = 25 and CLIENTSITESID = @CLIENTSITESID;

  if exists(select id from dbo.REGISTRANT R where R.CONSTITUENTID = @SPONSORCONSTITUENTID and R.EVENTID = @EVENTID)
    begin
      select @ALLOW = ALLOWADMINACCESSHQ from dbo.REGISTRANT R left outer join dbo.REGISTRANTEXTENSION RE on RE.REGISTRANTID = R.ID where R.CONSTITUENTID = @SPONSORCONSTITUENTID and R.EVENTID = @EVENTID 
    end


    select @SITECONTENTID = P.SITECONTENTID from dbo.PAGECONTENT P  
    inner join dbo.SiteContent SC on SC.ID = P.SiteContentID and SC.ContentTypesID = 33   --sign in part  
    where P.SITEPAGESID = @LOGINPAGEID

  if @WEBSITESTATUS = 1 and @ALLOWSSO = 1
    set @ISUSER = 1;
  select @BBNCUSERNAME = USERNAME from dbo.CLIENTUSERS where ID = @CLIENTUSERID
    exec dbo.USP_GETCREATE_SSO_URLFORMATSTRING @URLFORMATSTRING = @URLFORMATSTRING output,@SHAREDKEY = @SHAREDKEY output,@SITEID = @CLIENTSITESID, @SSO_NAME='BBEC Caller 2', @includeIP=0
  declare @ISBLACKBAUDLOGIN bit = 1
  set @AUTHENTICATEDSOCAILMEDIA = ''
  select @ISBLACKBAUDLOGIN = 0, @AUTHENTICATEDSOCAILMEDIA = @AUTHENTICATEDSOCAILMEDIA + PROVIDERNAME + ', ' from dbo.CLIENTUSERSEXTENSION where CLIENTUSERID = @CLIENTUSERID
  if @ISBLACKBAUDLOGIN = 1
     select @SENDPASSWORDURL = VALUE + 'ConfirmationEmail.aspx?pid=' + Convert(varchar(100),isnull(@LOGINPAGEID, '')) +
                          '&UserID=' + Convert(varchar(100), isnull(@CLIENTUSERID, '')) +  
                          '&SiteContentID=' + Convert(varchar(100), isnull(@SITECONTENTID, ''))    
     from dbo.CMSSITESETTING 
     where ENUMID = 11 and CLIENTSITESID = @CLIENTSITESID;
  else
   set @AUTHENTICATEDSOCAILMEDIA = 'Authenticated with: ' + SUBSTRING(@AUTHENTICATEDSOCAILMEDIA, 1, LEN(@AUTHENTICATEDSOCAILMEDIA) - 1)

    select @LOGINASSPONSORLINK = VALUE + '?pid=' + Convert(varchar(100),@LOGINPAGEID)
    from dbo.CMSSITESETTING SS(nolock) 
    where ENUMID = 10 and CLIENTSITESID = @CLIENTSITESID;

    return 0;