USP_DATALIST_INDIVIDUALMERGEDATA

CREATE procedure dbo.USP_DATALIST_INDIVIDUALMERGEDATA
  (
      @EVENTID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier = null,
    @REGISTRANTID uniqueidentifier = null,
    @PAGETYPE tinyint = 0,
    @PREVIOUSEVENTID uniqueidentifier = null,  -- used for faf default marketing messages,

    @GROUPID uniqueidentifier = null          -- need an adjustment when retrieving url for household, can't use registrantid

  )
  as
    set nocount on;

    if @REGISTRANTID is null 
        select @REGISTRANTID = ID from dbo.REGISTRANT (nolock) where CONSTITUENTID = @CONSTITUENTID

    if @CONSTITUENTID is null 
        select @CONSTITUENTID = CONSTITUENTID from dbo.REGISTRANT (nolock) where ID = @REGISTRANTID

    declare @IPTotalFundraisingAmount nvarchar(10)  

    select @IPTotalFundraisingAmount = dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(@REGISTRANTID, @EVENTID)  

    declare @url nvarchar(500),@IPPageUrlWithoutTracking nvarchar(500)
    IF @GROUPID = NULL  OR @PAGETYPE != 3 -- most cases this will be true  

        select   @url=dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(ID,EVENTID,@PAGETYPE)  
        from REGISTRANT   
        WHERE (@CONSTITUENTID is null or CONSTITUENTID=@CONSTITUENTID)  
        and (@REGISTRANTID is null or ID=@REGISTRANTID)  
        AND EVENTID=@EVENTID   
    ELSE  
      SET @url = ( SELECT   dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(@GROUPID,@EVENTID,@PAGETYPE) )    

    set @IPPageUrlWithoutTracking = @url 

    IF CHARINDEX( '?',  @url ) = 0   
    begin  
        declare @personalpageid int  
        select @personalpageid=VALUE from dbo.CMSSITESETTING  
        where CMSSITESETTING.ENUMID = 30 and CMSSITESETTING.CLIENTSITESID in (select CLIENTSITESID from dbo.EVENTEXTENSION where EVENTID = @EVENTID)   
        set @url=(case when RIGHT(@url,1)='/' then LEFT(@url,len(@url)-1) else @url end) + '?ch=1&frmfaf=1'  
    end  
    ELSE  
        set @url=(case when RIGHT(@url,1)='/' then LEFT(@url,len(@url)-1) else @url end) + '&ch=1&frmfaf=1'   

    declare @dashboardURL nvarchar(500)  
    select @dashboardURL = S2.VALUE + '?pid=' + S1.VALUE + '&tab=' + cast(@PAGETYPE as nvarchar(2))  
    from EVENTEXTENSION EX --on EX.EVENTID = R.EVENTID  

    left join CMSSITESETTING S1 on EX.CLIENTSITESID = S1.CLIENTSITESID and S1.ENUMID = 38  
    left join CMSSITESETTING S2 on EX.CLIENTSITESID = S2.CLIENTSITESID and S2.ENUMID = 10  
    where EX.EVENTID = @EVENTID  

    IF @PREVIOUSEVENTID IS NOT NULL  
    BEGIN  
    SET @EVENTID = @PREVIOUSEVENTID  
    SELECT @REGISTRANTID = ID from dbo.REGISTRANT WHERE CONSTITUENTID = @CONSTITUENTID and EVENTID = @EVENTID  
    END  

    declare @ManagerNotificationPageID int  
    select top 1 @ManagerNotificationPageID = VPC.PageID  
    from vwPageContent VPC  
    inner join dbo.SitePages SP on SP.ID = VPC.PageID  
    inner join dbo.EVENTEXTENSION EE ON EE.CLIENTSITESID = SP.ClientSitesID and EE.EVENTID = @EVENTID  
    LEFT OUTER JOIN  
    (select PAGEMODELITEM.OBJECTGUID  
    from dbo.PAGEMODELITEM   
    inner join dbo.PAGEMODEL on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID  
    where PAGEMODEL.LOCKARTIFACTS = 1  
    ) PMI on PMI.OBJECTGUID = VPC.PageGuid   
    inner join dbo.APEXFORMCONTENT AFC ON AFC.ID = VPC.Guid  
    inner join dbo.APEXFORM AF ON AF.ID = AFC.FORMID AND AF.FORMTYPEID = '89D3C4CC-4D01-4E8B-B82E-04AD966EEE09'    
    WHERE VPC.IsTemplatePage = 0  
    and VPC.ContentTypesID = 121  
    and (PMI.OBJECTGUID is null)  

    select   
    IPID = C.lookupid   
    ,IPName = C.NAME  
    ,IPFirstName = C.FIRSTNAME  
    ,IPLastName = C.KEYNAME  
    ,IPPageUrl = @url  
    ,IPManageNotificationUrl = cmss3.VALUE + 'page.aspx?pid=' + CAST(@ManagerNotificationPageID AS NVARCHAR(50)) + '&frmfaf=1' -- to identify that the link is in a faf email  

    ,IPUserName = CU.USERNAME    
    ,IPMinFundraisingGoal = RGE.FUNDRAISINGGOAL     
    ,IPPaysBalanceMessage = IFRGTG.PARTICIPANTPAYSBALANCEMESSAGE   
    ,IPTargetFundraisingGoal = RGE.TARGETFUNDRAISINGGOAL  
    ,IPTotalFundraisingAmount = cast(@IPTotalFundraisingAmount as money)   
    ,IPPercentageOfTargetFundraisingGoal = (case when ISNULL(RGE.TARGETFUNDRAISINGGOAL,0) = 0 then 0 else cast(@IPTotalFundraisingAmount as money) / RGE.TARGETFUNDRAISINGGOAL end)  
    ,IPNumberOfParticipantsToRecruit = RGE.MEMBERECRUITMENTGOAL    
    ,IPPercentageOfDonorsToRetain = RGE.DONORRETENTIONGOAL  
    ,IPNumberOfCommunicationsToSend = RGE.COMMUNICATIONGOAL    
    ,INumberForOtherGoal = RGE.OTHERUNITGOAL  

    ,IPNumberOfParticipantsRecruited=(select  COUNT(fc.TYPEGUID)    
                                            from   FAFCOMMUNICATIONSLOG fl  
                                            join   FAFEVENTCOMMUNICATIONCHANNEL fc on fl.EMAILJOBID = fc.EMAILJOBID  
                                            WHERE   fl.CLIENTUSERSID  IN (SELECT dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT (RG.CONSTITUENTID)))  

    ,IPParOfParticipantsRecruited= case when isnull(RGE.MEMBERECRUITMENTGOAL,0) =0 then 0 else   
          Convert(decimal,(select  COUNT(fc.TYPEGUID)    
                                            from   FAFCOMMUNICATIONSLOG fl  
                                            join   FAFEVENTCOMMUNICATIONCHANNEL fc on fl.EMAILJOBID = fc.EMAILJOBID  
                                            WHERE   fl.CLIENTUSERSID  IN (SELECT dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT (RG.CONSTITUENTID)) AND EVENTID=EV.ID))/RGE.MEMBERECRUITMENTGOAL  
          End  

    ,IPParOfDonorsRetained=isnull((select RetainedDonorCount from dbo.UFN_PARTICIPANT_DONOR_RETENTION(@CONSTITUENTID, @EVENTID)),0)  
    ,IPNumberOfCommunicationsSent=dbo.UFN_FAFGETTOTALCOMMUNICATIONS(EV.ID, dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(RG.CONSTITUENTID))  
    ,IPParOfCommunicationsSent=case when isnull(RGE.COMMUNICATIONGOAL,0) = 0 then 0 else   
         CONVERT(DECIMAL,dbo.UFN_FAFGETTOTALCOMMUNICATIONS(EV.ID, dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(RG.CONSTITUENTID)))/RGE.COMMUNICATIONGOAL END  

    ,ILYMinFundraisingGoal=isnull(LYRGE.FUNDRAISINGGOAL,0.00)  
    ,ILYTargetFundraisingGoal=isnull(LYRGE.TARGETFUNDRAISINGGOAL ,0.00)  
    ,ILYTotalAmoutRaised= (case when @PREVIOUSEVENTID is null then dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(LYRG.ID,LYRG.EVENTID) else dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(@REGISTRANTID,@EVENTID) end)  
    ,IPDashboardURL=@dashboardURL    

    /*future sprint*/  
    --,ILYNumberOfParticipantsToRecruit=null  

    --,ILYNumberOfParticipantsRecruited=nulL  

    --,ILYPercentageOfDonorsToRetain=null  

    --,ILYPercentageOfDonorsRetained=null  

    --,ILYNumberOfCommunicationsToSend=null  

    --,ILYNumberOfCommunicationsSent=null 


    ,IPPageUrlWithoutTracking = @IPPageUrlWithoutTracking

    ,RG.ID REGISTRANTID

    from dbo.REGISTRANT RG      
    inner join [EVENT] EV on EV.ID = RG.EVENTID and EV.ID = @EVENTID    
    inner join EVENTEXTENSION EEXT on EV.ID = EEXT.EVENTID  
    inner join dbo.CONSTITUENT C on C.ID = RG.CONSTITUENTID   
    left outer join REGISTRANTEXTENSION RGE on RGE.REGISTRANTID = RG.ID    
    left outer join REGISTRANTREGISTRATION RGG on RGG.REGISTRANTID =RG.ID   
    left outer join EVENTPRICE EVP on EVP.EVENTID=@EVENTID and EVP.ID=RGG.EVENTPRICEID   
    left outer join FAFREGISTRATIONTYPEGOAL IFRGTG on IFRGTG.EVENTPRICEID=EVP.ID and IFRGTG.REGTYPEGOALCODE=0   
    left outer join BACKOFFICESYSTEMPEOPLE BOSP on BOSP.BackofficeRecordID=C.SEQUENCEID and BOSP.BACKOFFICESYSTEMID = 0    
    left outer join BACKOFFICESYSTEMUSERS BOSU on  BOSU.BACKOFFICEPEOPLEID=BOSP.ID and BOSU.[CURRENT] = 1  
    left outer join CLIENTUSERS CU on CU.ID = BOSU.CLIENTUSERSID and CU.DELETED = 0   
    left outer join CMSSITESETTING CMS1 on CMS1.CLIENTSITESID = EEXT.CLIENTSITESID and CMS1.SETTING = 'PAGEBASEURL'  
    left outer join CMSSITESETTING CMS2 on CMS2.CLIENTSITESID = EEXT.CLIENTSITESID and CMS2.SETTING = 'FAFPARTICIPANTPAGEID'  
    --  LEFT JOIN dbo.V_QUERY_REGISTRANT_FUNDRAISINGTOTAL V ON RG.ID= V.ID  

    --Last year  

    left join dbo.EVENTEXTENSION LYET ON LYET.EVENTID=EEXT.PRIORYEAREVENTID  
    left join dbo.REGISTRANT LYRG ON LYRG.CONSTITUENTID=RG.CONSTITUENTID AND LYRG.EVENTID=LYET.EVENTID  
    left join dbo.REGISTRANTEXTENSION LYRGE ON LYRGE.REGISTRANTID=LYRG.ID   
    --Url  

    left join dbo.CMSSITESETTING CMSS3 on EEXT.CLIENTSITESID = CMSS3.CLIENTSITESID and CMSS3.ENUMID = 11  
    WHERE RG.ID = @REGISTRANTID