USP_DATAFORMTEMPLATE_VIEW_FAFEVENTREGISTRANTSUMMARY

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FAFEVENTREGISTRANTSUMMARY
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ADDRESS nvarchar(max) = null output,
    @EMAIL nvarchar(100) = null output,
    @PHONE nvarchar(20) = null output,
  @PERSONALPAGEURL dbo.UDT_WEBADDRESS = null output,
  @PREVIOUSPARTICIPANT bit = null output,
  @MONEYRAISED money = null output,
  @REGISTRATIONFEEPAID money = null output,
  @REGISTRATIONFEE money = null output,
  @ROLE nvarchar(max) = null output,
  @GROUPNAME nvarchar(max) = null output,
  @NATIONALGROUPNAME nvarchar(max) = null output,
  @PARENTGROUPNAME nvarchar(max) = null output,
  @LOOKUPID nvarchar(max) = null output,
  @REGISTRANTID uniqueidentifier = null output,
  @CONSTITUENTID uniqueidentifier = null output,
  @GROUPID uniqueidentifier = null output,
  @NATIONALGROUPID uniqueidentifier = null output,
  @PARENTGROUPID uniqueidentifier = null output,
  @AUTHENTICATEDSOCAILMEDIA nvarchar(512) = null output,
  @ALLOW bit = null output,
  @ALLOWSSO bit = null output,
  @HAVELOGINPAGE bit = null output,
  @SENDPASSWORDURL nvarchar(512) = null output,
  @LOGINASPARTICIPANTLINK dbo.UDT_WEBADDRESS = null output,
  @HAVEHQPAGE bit = null output,
  @ISUSER bit = null output,
  @URLFORMATSTRING varchar(512) = null output,
  @BBNCUSERNAME varchar(512) = null output,
  @SHAREDKEY varchar(512) = null output,
  @WEBSITESTATUS 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,
    @TEAMFUNDRAISINGTEAMID uniqueidentifier,
    @CONSTITUENTSEQUENCEID 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,
      @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
      @EMAIL = EMAILADDRESS.EMAILADDRESS,
      @PHONE = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER),
      @PREVIOUSPARTICIPANT= case when R2.ID is null then 0 else 1 end,
      @MONEYRAISED= ISNULL(FAFRAISEDTOTAL.RECEIVEDAMOUNT, 0)
            + CASE WHEN opt.ISREGISTRATIONREVENUE = 1 then REGAMOUNT else 0 end  
            + CASE WHEN opt.ISUNPAIDPLEDGES = 1 then UNPAIDPLEDGEAMOUNT else 0 end  
            + CASE WHEN opt.ISUNPAIDRECURRINGGIFTS = 1 then UNPAIDRECURRINGAMOUNT else 0 end  
            + CASE WHEN opt.ISPENDINGMATCHINGGIFTS = 1 then MATCHINGGIFTCLAIMAMOUNT else 0 end  
            + CASE WHEN opt.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then UNCONFIRMEDOFFLINEAMOUNT else 0 end,      
          @REGISTRATIONFEE = RR.AMOUNT,
      @LOOKUPID=REGISTRANT.LOOKUPID,
      @EVENTID=REGISTRANT.EVENTID,
      @REGISTRANTID=REGISTRANT.ID,
      @CONSTITUENTID=REGISTRANT.CONSTITUENTID,
      @CONSTITUENTSEQUENCEID=CONSTITUENT.SEQUENCEID
      from dbo.REGISTRANT
                left join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
        left join dbo.FAFEVENTDONATIONOPTIONSCONFIG opt on EVENT.ID=opt.EVENTID
        left join dbo.EVENTEXTENSION on EVENT.ID = EVENTEXTENSION.EVENTID
        left join dbo.REGISTRANT R2 on EVENTEXTENSION.PRIORYEAREVENTID = R2.EVENTID AND REGISTRANT.CONSTITUENTID = R2.CONSTITUENTID
                left join dbo.CONSTITUENT on REGISTRANT.CONSTITUENTID = CONSTITUENT.ID
                left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY = 1
                left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
        left join dbo.FAFRAISEDTOTAL on FAFRAISEDTOTAL.CONSTITUENTID=REGISTRANT.CONSTITUENTID and FAFRAISEDTOTAL.EVENTID=REGISTRANT.EVENTID
        left join dbo.REGISTRANTREGISTRATION RR ON RR.REGISTRANTID = REGISTRANT.ID
            where REGISTRANT.ID = @ID;

            --Simpler to do the financial calculations here so we don't have to recompute TOTALFEES and TOTALPAID again to figure out the balance
            with [EVENTFEEINFO] as 
            (
                select
                    coalesce((select sum(EVENTREGISTRANTPAYMENT.AMOUNT) from dbo.EVENTREGISTRANTPAYMENT where EVENTREGISTRANTPAYMENT.REGISTRANTID = @ID),0) as [TOTALPAID],
                    coalesce((select sum(CREDITITEMS.TOTAL) from dbo.[UFN_REGISTRANT_GETCREDITITEMS](@ID) CREDITITEMS),0) as [TOTALREFUNDED]
            )
            select @REGISTRATIONFEEPAID = [EVENTFEEINFO].TOTALPAID - [EVENTFEEINFO].TOTALREFUNDED
            from [EVENTFEEINFO]      

      set @WEBSITESTATUS = dbo.UFN_CLIENTSITES_GETEVENTWEBSITESTATUS(@EVENTID) 

      if @WEBSITESTATUS = 1  
      begin
        set @PERSONALPAGEURL = dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(@ID, @EVENTID, 0)
      end

      select @ROLE=ROLE, @TEAMFUNDRAISINGTEAMID=TEAMFUNDRAISINGTEAMID from dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, NULL) where REGISTRANTID = @ID

      SELECT @GroupName = case when TE.[StatusCode] != 2 then T.NAME else '' end, 
            @NATIONALGROUPNAME = NFG.NAME,
            @PARENTGROUPNAME = PT.NAME,
            @GROUPID=T.ID,
            @NATIONALGROUPID=NFG.ID,
            @PARENTGROUPID = PT.ID

      FROM dbo.TEAMFUNDRAISINGTEAM T 
      INNER JOIN dbo.TEAMEXTENSION TE ON T.ID = TE.TEAMFUNDRAISINGTEAMID
      LEFT OUTER JOIN dbo.FAFNFGCAMPAIGNLEVEL NFGCL On NFGCL.ID=TE.NFGCAMPAIGNLEVELID
      LEFT OUTER JOIN dbo.FAFNFGCAMPAIGN NFG on NFG.ID=NFGCL.NFGCAMPAIGNID
      LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAM PT (NOLOCK) on PT.ID = T.PARENTTEAMID
      WHERE T.ID = @TEAMFUNDRAISINGTEAMID      

      --login stuff
        declare @CLIENTSITESID  int = null  
        declare @CLIENTUSERID  int = null  
        declare @HQPID int = null
        declare @SITECONTENTID int = -1  
        declare @LOGINPAGEID int = null   

        select @CLIENTSITESID = eex.CLIENTSITESID, @ALLOW = isnull(rex.[ALLOWADMINACCESSHQ], 1)
          from dbo.REGISTRANT r  
          left join dbo.REGISTRANTEXTENSION rex on rex.REGISTRANTID = r.ID
          left join dbo.EVENTEXTENSION eex ON r.EVENTID = eex.EVENTID  
          where r.ID = @ID       

          set @CLIENTUSERID = dbo.fnGetUserIDFromLinkedRecordID(@CONSTITUENTSEQUENCEID, 0); 
          declare @ISBLACKBAUDLOGIN bit = 1
          set @AUTHENTICATEDSOCAILMEDIA = ''
          select @ISBLACKBAUDLOGIN = 0, @AUTHENTICATEDSOCAILMEDIA = @AUTHENTICATEDSOCAILMEDIA + PROVIDERNAME + ', ' 
          from dbo.CLIENTUSERSEXTENSION where CLIENTUSERID = @CLIENTUSERID

          select @ALLOWSSO = VALUE from dbo.CMSSITESETTING where ENUMID = 25 and CLIENTSITESID = @CLIENTSITESID

          select @HQPID = HQ.VALUE, @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 join dbo.CMSSITESETTING HQ on EX.CLIENTSITESID = HQ.CLIENTSITESID and HQ.ENUMID = 30
          left join dbo.CMSSITESETTING LP on EX.CLIENTSITESID = LP.CLIENTSITESID and LP.ENUMID = 3  
          where EX.EVENTID = @EVENTID

          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  
           BEGIN               
            if @ALLOWSSO = 1   
            BEGIN         
              set @ISUSER = 1
              exec dbo.USP_GETCREATE_SSO_URLFORMATSTRING @URLFORMATSTRING = @URLFORMATSTRING output,@SHAREDKEY = @SHAREDKEY output,@SITEID = @CLIENTSITESID, @SSO_NAME='BBEC Caller 2', @includeIP=0
              if @ISBLACKBAUDLOGIN = 1
                select @SENDPASSWORDURL = VALUE + 'ConfirmationEmail.aspx?pid=' + Convert(varchar(100),@LOGINPAGEID) +  
               '&UserID=' + Convert(varchar(100),@CLIENTUSERID) +  
                  '&SiteContentID=' + Convert(varchar(100),@SITECONTENTID)  
                from dbo.CMSSITESETTING where ENUMID=11 and CLIENTSITESID = @CLIENTSITESID  
              else
                set @AUTHENTICATEDSOCAILMEDIA = 'Authenticated with: ' + SUBSTRING(@AUTHENTICATEDSOCAILMEDIA, 1, LEN(@AUTHENTICATEDSOCAILMEDIA) - 1)
              if @ALLOW = 1  
              BEGIN  
                select @BBNCUSERNAME = USERNAME from dbo.CLIENTUSERS where ID = @CLIENTUSERID                     
                if @HAVEHQPAGE = 1  
                BEGIN                      
                  if @LOGINPAGEID is not null 
                      DECLARE @HOUSEHOLDPARAMETER varchar(100) = ''
                      DECLARE @TEAMID uniqueidentifier = NULL
                      DECLARE @TYPECODE int 
                      SELECT  @TEAMID = TEAMID, @TYPECODE = TEAMTYPECODE
                        FROM dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID)
                      select @LOGINASPARTICIPANTLINK = VALUE + '?pid=' + Convert(varchar(100),@LOGINPAGEID) +  
                      '&ru=page.aspx%3fpid%3d'
                      from dbo.CMSSITESETTING  
                      where ENUMID=10 and CLIENTSITESID = @CLIENTSITESID  
                 END  
               END                
             END  
           END  

    return 0;