USP_DATALIST_FUNDRAISINGGROUPMERGEDATA

CREATE procedure dbo.USP_DATALIST_FUNDRAISINGGROUPMERGEDATA
(
  @EVENTID uniqueidentifier,
  @GROUPID uniqueidentifier,
  @ISPREVIOUSEVENT bit = 0, -- used for marketing message merge fields only

  @CONSTITUENTID uniqueidentifier = null -- constituent leader ID if applicable

)
  as
      set nocount on;

  declare @numberOfTeams int,
          @FGTotalAmountRaised nvarchar(10),
          @FGNUMBEROFPARTICIPANTSRECRUITED int,
          @TYPECODE int,
          @FGNumberOfCommunicationsSent int,
          @NumberOfDonorsRetained decimal(5,2),
          @LYFundraisingGoal money,
          @LYFundraisingTotal money,
          @FGPageUrl nvarchar(500),
              @FGPARTICIPANTGOALACTUAL int,
              @FGNumberofteammembersactual int,
              @FGNumberofteammembersRetained int,
          @IsLeader bit = 0,
          @PreviousTeamID uniqueidentifier,
          @PreviousTeamCount int


  --Get typeCode of group

  select @TYPECODE=TYPECODE from TEAMEXTENSION where TEAMFUNDRAISINGTEAMID=@GROUPID 

  -- todo -- change logic

  select @numberOfTeams = count(1) 
    from TEAMFUNDRAISINGTEAM T
   where T.PARENTTEAMID = @GROUPID

   --- smart field

  -- exec dbo.USP_SMARTFIELD_VALUEBYRECORDID @SMARTFIELDCATALOGID='ed84f75a-b11a-4eb8-b9e0-673a3ab7a15a', @RECORDID=@GROUPID, @SFINSTANCENAME_ENDWITH='TO DATE', @RESULT=@FGTotalAmountRaised OUTPUT;


    /*select  @FGTotalAmountRaised = CASE WHEN TE.TYPECODE=1 THEN dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(@GROUPID, TE.EVENTID)
                                                        WHEN TE.TYPECODE=2 THEN dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(@GROUPID, TE.EVENTID)
                                                        WHEN TE.TYPECODE=3 THEN dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(@GROUPID, TE.EVENTID) END
    from dbo.TEAMEXTENSION TE
    where TE.TEAMFUNDRAISINGTEAMID = @GROUPID*/

   --Get Number of participants recruited

  select  @FGNUMBEROFPARTICIPANTSRECRUITED=COUNT(fc.TYPEGUID)     
  from      FAFCOMMUNICATIONSLOG fl
  join      FAFEVENTCOMMUNICATIONCHANNEL fc on fl.EMAILJOBID = fc.EMAILJOBID
  where  fc.TYPEGUID IN (select REGISTRANTID from dbo.UFN_REGISTRANT_GROUPMEMBERLIST() where GROUPID= @GROUPID AND TYPECODE=@TYPECODE)

    --get number of communications sent 

  select @FGNumberOfCommunicationsSent=COUNT(ID) 
  from   FAFCOMMUNICATIONSLOG 
  where  CLIENTUSERSID  IN (
  select dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(CONSTITUENTID) from REGISTRANT 
  where ID IN (select REGISTRANTID from dbo.UFN_REGISTRANT_GROUPMEMBERLIST() where GROUPID= @GROUPID AND TYPECODE=@TYPECODE) )

    --get number of donors retained

  select @NumberOfDonorsRetained=case when count(RC.ID)=0 then 0 else cast(COUNT(A.RID) as decimal)/cast(COUNT(RC.ID) as decimal) end
    from dbo.UFN_FAF_GETTEAMHIEARACHY(@GROUPID) T
    inner join TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=T.teamID
    inner join dbo.EVENTEXTENSION ET ON ET.EVENTID=TE.EVENTID
    left join dbo.REVENUERECOGNITION RC ON RC.CONSTITUENTID=TE.TEAMCONSTITUENTID
    left join (
        select TE1.TEAMCONSTITUENTID
        , TE1.EventID
        ,RC1.ID RID 
        from dbo.UFN_FAF_GETTEAMHIEARACHY(@GROUPID) T1
        inner join TEAMEXTENSION TE1 ON TE1.TEAMFUNDRAISINGTEAMID=T1.teamID
        inner join dbo.REVENUERECOGNITION RC1 ON RC1.CONSTITUENTID=TE1.TEAMCONSTITUENTID
    ) A ON A.TEAMCONSTITUENTID=TE.TEAMCONSTITUENTID and A.EVENTID=ET.PRIORYEAREVENTID

    --Get last year data

    select @LYFundraisingGoal=LYRE.FUNDRAISINGGOAL
            ,@LYFundraisingTotal=  dbo.UFN_REGISTRANT_GETDONATIONAMOUNT(LYR.ID, EEXT.PRIORYEAREVENTID, LYTFT.ID, LYTE.TYPECODE, NULL)
    from EVENTEXTENSION EEXT 
    left join dbo.EVENTEXTENSION LYET ON LYET.ID=EEXT.PRIORYEAREVENTID 
    left join dbo.EVENT LYEV ON LYEV.ID=LYET.EVENTID
    left join dbo.TEAMFUNDRAISINGTEAM LYTFT ON LYTFT.APPEALID=LYEV.APPEALID 
    left join dbo.TEAMEXTENSION LYTE ON LYTE.TEAMFUNDRAISINGTEAMID=LYTFT.ID 
    left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN LYTC on LYTFT.ID = LYTC.TEAMFUNDRAISINGTEAMID
    left join dbo.REGISTRANT LYR on LYR.constituentid = LYTC.constituentID and LYR.eventID = LYEV.ID 
    left join dbo.RegistrantExtension LYRE on LYRE.registrantID = LYR.ID
    where LYTE.TEAMCONSTITUENTID=@GROUPID 
    and EEXT.ID=@EVENTID

    select @FGPageUrl=dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(@GROUPID,@EVENTID,TYPECODE)
    from TEAMEXTENSION 
    where TEAMFUNDRAISINGTEAMID=@GROUPID
        and TEAMEXTENSION.EVENTID=@EVENTID 

  -- If it is the only parameter I need ? instead of & for a valid URL

    IF CHARINDEX( '?',  @FGPageUrl ) = 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 @FGPageUrl=(case when RIGHT(@FGPageUrl,1)='/' then LEFT(@FGPageUrl,len(@FGPageUrl)-1) else @FGPageUrl end) + '?ch=1&frmfaf=1'
        end
    ELSE
          set @FGPageUrl=(case when RIGHT(@FGPageUrl,1)='/' then LEFT(@FGPageUrl,len(@FGPageUrl)-1) else @FGPageUrl end) + '&ch=1&frmfaf=1'

  declare @dashboardURL nvarchar(500)
  select @dashboardURL = S2.VALUE + '?pid=' + S1.VALUE + '&tab=' + cast(@TYPECODE 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

     select @FGPARTICIPANTGOALACTUAL = COUNT(fc.TYPEGUID)     
     from      FAFCOMMUNICATIONSLOG fl
   join      FAFEVENTCOMMUNICATIONCHANNEL fc on fl.EMAILJOBID = fc.EMAILJOBID
   where      fc.TYPEGUID IN (
            select REGISTRANTID from dbo.UFN_REGISTRANT_GROUPMEMBERLIST() RG
            inner join TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=RG.GROUPID 
            where GROUPID=@GROUPID  AND RG.TYPECODE=TE.TYPECODE
        )

   select @IsLeader=1 from dbo.TEAMFUNDRAISINGTEAMCAPTAIN where TEAMFUNDRAISINGTEAMID = @GROUPID and CONSTITUENTID = @CONSTITUENTID

   --number of team member actual

   select @FGNumberofteammembersactual = COUNT(teamid) from dbo.UFN_NUMBERTEAMMEMBERS_GROUP (@GROUPID)

   -- get previous team ID

   select @PreviousTeamID = pTX.TEAMFUNDRAISINGTEAMID from TEAMEXTENSION TX 
   join EVENTEXTENSION EX on TX.EVENTID = EX.EVENTID
   left join TEAMEXTENSION pTX on pTX.TEAMCONSTITUENTID = TX.TEAMCONSTITUENTID and EX.PRIORYEAREVENTID = pTX.EVENTID
   where TX.TEAMFUNDRAISINGTEAMID = @GROUPID

   -- get previous team member count if there is one   

   select @PreviousTeamCount=count(teamid) from dbo.UFN_NUMBERTEAMMEMBERS_GROUP (@PreviousTeamID)

   --Group number Of Team Members Retained

   select @FGNumberofteammembersRetained = SUM(MEMBERRETAINEDNUMBER) from dbo.UFN_REGISTRANT_GROUPMEMBERLIST() where GROUPID= @GROUPID

  select 
    FGID = TeamC.LookupID
  , FGName = T.NAME
  , FGLeaderName = C.NAME
  , FGTypeName = TE.[Type]
  , FGPageUrl =@FGPageUrl

  , FGMinFundraisingGoal = case when TE.TYPECODE = 3 then T.Goal else 0 end
  , FGTargetFundraisingGoal = TE.TargetFundraisingGoal
  , FGTotalAmountGoal = isnull(T.Goal,0)
  , FGTotalAmountRaised = 0
  , FGPercentageOfFundraisingGoal = 0

  , FGParticipantgoal = TE.PARTICIPANTGOAL 
  , FGParticipantactual =isnull(@FGPARTICIPANTGOALACTUAL,0)
  , FGParcParticipant = case when isnull(TE.PARTICIPANTGOAL ,0)= 0 then 0 else CONVERT(DECIMAL, @FGPARTICIPANTGOALACTUAL)/TE.PARTICIPANTGOAL  end


  , FGNumberofcommunicationstosend = TE.COMMUNICATIONGOAL 
  , FGNumberOfCommunicationsSent = isnull(@FGNumberOfCommunicationsSent,0)
  , FGPercentageOfCommunicationsSent =case when ISNULL(TE.COMMUNICATIONGOAL,0) = 0 then 0 else cast(@FGNumberOfCommunicationsSent as decimal)/TE.COMMUNICATIONGOAL end

  , FGNumberofteammembersgoal = TE.TEAMMEMBERGOAL 
  , FGNumberofteammembersactual = ISNULL(@FGNumberofteammembersactual, 0)
  ,    FGPERCENTAGEOFTEAMMEMBERSGOAL = case isnull(TE.TEAMMEMBERGOAL, 0) when 0 then 0 else convert(decimal,ISNULL(@FGNumberofteammembersactual, 0)) / TE.TEAMMEMBERGOAL end
  , FGPercentageOfTeamMembersToRetain = isnull(TE.PCTTEAMMEMBERRETENSION,0)
  , FGPercentageOfTeamMembersRetained =case isnull(@PreviousTeamCount, 0) when 0 then 0 else convert(decimal(5,2),ISNULL(@FGNumberofteammembersRetained, 0)) / @PreviousTeamCount end

  , FGNumberofteamsgoal = TE.TEAMRECRUITMENTGOAL
  , FGNumberofteamsactual = isnull(AG.TEAMNUMBER,0)
  , FGPercentageofteamsgoal = case when isnull(TE.TEAMRECRUITMENTGOAL,0)= 0 then 0 else CONVERT(DECIMAL(5,2), isnull(AG.TEAMNUMBER,0))/TE.TEAMRECRUITMENTGOAL end

  , FGNumberofcompaniesgoal = null -- this is for fundraising coach of future sprint

  , FGNumberofcompaniesactual = null
  , FGPercentageofcompaniesgoal = null

  , FGNumberforothergoal = isnull(TE.OTHERGOAL,0)

   --future sprint

  , FGPercentageOfDonorsToRetain = TE.DONORRETENTIONGOAL 
  --not sure

  , FGPercentageOfDonorsRetained =@NumberOfDonorsRetained
  , FGPercentageOfTeamsToRetain = TE.PCTTEAMSRETENSION
  , FGPercentageOfTeamsRetained = case when isnull(AG.PREVIOUSTEAMSCNT,0)=0 then 0 else convert(decimal,AG.TEAMRETAINEDNUMBER)/AG.PREVIOUSTEAMSCNT end
  , FGPercentageOfCompaniesToRetain = null
  , FGPercentageOfCompaniesRetained = null

  , FGLYTotalAmountGoal = isnull(@LYFundraisingGoal,0)
  , FGLYTotalAmountActual = case when ISNULL(@LYFundraisingGoal,0) =0 then 0 else convert(decimal,@LYFundraisingTotal)/@LYFundraisingGoal end
  , FGLYNumberOfParticipantsToRecruit = null
  , FGLYNumberOfParticipantsRecruited = null
  , FGLYPercentageOfDonorsToRetain = null
  , FGLYPercentageOfDonorsRetained = null
  , FGLYNumberOfCommunicationsToSend = null
  , FGLYNumberOfCommunicationsSent = null
  , FGLYNumberOfTeamMembersGoal = null
  , FGLYNumberOfTeamMembersActual = null
  , FGLYPercentageOfTeamMembersToRetain = null
  , FGLYPercentageOfTeamMembersRetained = null
  , FGLYNumberOfTeamsGoal = null
  , FGLYNumberOfTeamsActual = null
  , FGLYPercentageOfTeamsToRetain = null
  , FGLYPercentageOfTeamsRetained = null
  , FGLYNumberOfCompaniesGoal = null
  , FGLYNumberOfCompaniesActual = null
  , FGLYPercentageOfCompaniesToRetain = null
  , FGLYPercentageOfCompaniesRetained = null

  , FGLYGroupTypeName = (CASE WHEN @ISPREVIOUSEVENT = 1 THEN TE.[Type] ELSE PrevTE.TYPE END)
  , FGDashboardURL=@dashboardURL


  --, SHOWHOUSEHOLDSECTION = case when TE.TYPECODE = 3 then 1 else 0 end

  --, SHOWTEAMSECTION = case when TE.TYPECODE = 1 then 1 else 0 end

  --, SHOWCOMPANYSECTION = case when TE.TYPECODE = 2 then 1 else 0 end

  --, SHOWFUNDRAISINGCOACHSECTION = 0


  from dbo.TEAMFUNDRAISINGTEAM T
  inner join dbo.EVENT EV on T.APPEALID = EV.APPEALID 
  inner join dbo.EVENTEXTENSION EEXT on EV.ID = EEXT.EVENTID
  left outer join dbo.TEAMEXTENSION TE on T.ID = TE.TEAMFUNDRAISINGTEAMID 
  left outer join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC on T.ID = TC.TEAMFUNDRAISINGTEAMID and (@IsLeader=0 or (TC.CONSTITUENTID = @CONSTITUENTID and @IsLeader = 1))
  left outer join dbo.CONSTITUENT C on C.ID = TC.CONSTITUENTID
  left outer join dbo.REGISTRANT R on R.constituentid = TC.constituentID and r.eventID = @EVENTID
  left outer join dbo.RegistrantExtension RE on RE.registrantID = R.ID
  left outer join dbo.CMSSITESETTING CMS1 on CMS1.CLIENTSITESID = EEXT.CLIENTSITESID and CMS1.SETTING = 'PAGEBASEURL'
  left outer join dbo.CMSSITESETTING CMS2 on CMS2.CLIENTSITESID = EEXT.CLIENTSITESID and CMS2.SETTING = 'FAFPARTICIPANTPAGEID'
  left join    dbo.UFN_COMPANY_TEAMS (@GROUPID) AG    ON TE.EVENTID= AG.EVENTID    
  left join dbo.EVENTEXTENSION PrevET on PrevET.EVENTID = EEXT.PRIORYEAREVENTID
  left join dbo.TEAMEXTENSION PrevTE on PrevTE.EVENTID = PrevET.EVENTID and PrevTE.TEAMCONSTITUENTID = TE.TEAMCONSTITUENTID
  left join dbo.CONSTITUENT TeamC ON TeamC.ID=TE.TEAMCONSTITUENTID

  where EV.ID = @EVENTID
        and T.ID = @GROUPID