USP_DATAFORMTEMPLATE_VIEW_GROUP_REGISTRATION_GOALS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_GROUP_REGISTRATION_GOALS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @FUNDRAISINGGOAL money = null output,
    @TEAMRECRUITMENTGOAL int = null output,
    @PCTTEAMSRETENSION decimal(9,2) = null output,
    @TEAMMEMBERGOAL int = null output,
    @PCTTEAMMEMBERRETENSION decimal(9,2) = null output,   
    @PARTICIPANTGOAL int = null output,
    @COMMUNICATIONGOAL int = null output,
    @TARGETFUNDRAISINGGOAL money = null output,
    @DONORRETENTIONGOAL decimal(9,2) = null output,   
    @PROGRESSFUNDRAISINGGOAL  money = null output,
    @PROGRESSTEAMRECRUITMENTGOAL int = null output,
    @PROGRESSPCTTEAMSRETENSION decimal(9,2) = null output,
    @PROGRESSTEAMMEMBERGOAL int = null output,
    @PROGRESSPCTTEAMMEMBERRETENSION decimal(9,2) = null output,
    @PROGRESSPARTICIPANTGOAL int = null output,
    @PROGRESSCOMMUNICATIONGOAL int = null output,
    @PROGRESSDONORRETENTIONGOAL decimal(9,2) = null output,   
    @TYPE nvarchar(50)  = null output,
    @LEADERS nvarchar(200) = null output
)
as
    set nocount on;

  declare @PREVIOUSTEAMID uniqueidentifier,
          @PREVIOUSTEAMCOUNT int,
          @GROUPCONSTITUENTID uniqueidentifier,
          @TOTALSOCIALPOSTS int=0,
          @TOTALOTHERCOMMS int=0

  select @PREVIOUSTEAMID = pTX.TEAMFUNDRAISINGTEAMID, @GROUPCONSTITUENTID=TX.TEAMCONSTITUENTID 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 = @ID

  select @PREVIOUSTEAMCOUNT=count(teamid) from dbo.UFN_NUMBERTEAMMEMBERS_GROUP (@PREVIOUSTEAMID)

  select @TOTALOTHERCOMMS=TOTALCOMMUNICATIONSENT from dbo.FAFGROUPSUMMARYINFORMATION where GROUPCONSTITUENTID=@GROUPCONSTITUENTID
  SET @TOTALSOCIALPOSTS=(select count(F.ID) AS COMMUNICATIONSENT from  dbo.FAFEVENTSOCIALNETWORKPOST F (nolock)
    join REGISTRANT R on F.REGISTRANTID=R.ID
      join (
              select TX.EVENTID, G.ParentID as GROUPID, G.ParentTeamConstituentID as TEAMCONSTITUENTID, PTX.TYPECODE, C.ID as CONSTITUENTID
              from dbo.UFN_FAFGROUP_GETALLGROUPS() G 
              join dbo.TEAMEXTENSION PTX (nolock) on G.PARENTID = PTX.TEAMFUNDRAISINGTEAMID
              join dbo.TEAMEXTENSION TX (nolock) on G.TeamID = TX.TEAMFUNDRAISINGTEAMID
              join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TX.TEAMFUNDRAISINGTEAMID = TFTM.TEAMFUNDRAISINGTEAMID
              join dbo.TEAMFUNDRAISER TF (nolock) on TF.ID = TFTM.TEAMFUNDRAISERID
              join dbo.CONSTITUENT C (nolock) on C.ID = TF.CONSTITUENTID 
              join dbo.BackOfficeSystemPeople BOSP(nolock) on BOSP.BackofficeRecordID = C.SEQUENCEID and BOSP.BackOfficeSystemID = 0   
              join dbo.BackOfficeSystemUsers BOSU(nolock) on BOSU.BackofficePeopleID = BOSP.ID and BOSU.[CURRENT] = 1  
              join dbo.ClientUsers CU(nolock) on CU.ID = BOSU.ClientUsersID and CU.Deleted = 0 

      ) A on A.CONSTITUENTID = R.CONSTITUENTID and A.EVENTID = R.EVENTID
      where A.GROUPID=@ID
      group by A.GROUPID, A.TEAMCONSTITUENTID, A.EVENTID , A.TYPECODE)

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

  select  @DATALOADED = 1,
          @FUNDRAISINGGOAL = TFT.[GOAL] ,
          @TEAMRECRUITMENTGOAL = TE.TEAMRECRUITMENTGOAL,
          @PCTTEAMSRETENSION = TE.PCTTEAMSRETENSION * 100,
          @TEAMMEMBERGOAL = TE.TEAMMEMBERGOAL,
          @PCTTEAMMEMBERRETENSION = TE.PCTTEAMMEMBERRETENSION * 100,
          @PARTICIPANTGOAL = TE.TEAMMEMBERGOAL,
          @COMMUNICATIONGOAL = TE.COMMUNICATIONGOAL,
          @TARGETFUNDRAISINGGOAL = TE.TARGETFUNDRAISINGGOAL,
          @DONORRETENTIONGOAL = TE.DONORRETENTIONGOAL * 100,
          @TYPE = TE.TYPE        
          ,@PROGRESSFUNDRAISINGGOAL=                case WHEN TE.TYPECODE=1 THEN dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TFT.ID, TE.EVENTID)
                                                                                          WHEN TE.TYPECODE=2 THEN dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(TFT.ID, TE.EVENTID)
                                                                                          WHEN TE.TYPECODE=3 THEN dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(TFT.ID, TE.EVENTID) END                                                              
                  ,@PROGRESSTEAMRECRUITMENTGOAL=    AG.TEAMNUMBER
                  ,@PROGRESSPCTTEAMSRETENSION=      case when isnull(AG.PREVIOUSTEAMSCNT,0) = 0 then 0 else (convert(decimal(9,2),AG.TEAMRETAINEDNUMBER)/AG.PREVIOUSTEAMSCNT) * 100 end
                  --,@PROGRESSTEAMMEMBERGOAL=                (select    Value from dbo.UFN_NUMBERTEAMMEMBERS_GROUP() where ID= @ID)        
          ,@PROGRESSTEAMMEMBERGOAL=                (select Value from dbo.UFN_NUMBERGROUPMEMBERS (@ID))        
                  ,@PROGRESSPCTTEAMMEMBERRETENSION= (select    case when isnull(@PREVIOUSTEAMCOUNT,0) = 0 then 0 
                                                          else cast((convert(decimal(5,2),sum(MEMBERRETAINEDNUMBER))/convert(decimal(5,2),@PREVIOUSTEAMCOUNT)) as decimal(5,2)) * 100 end 
                                             from dbo.UFN_REGISTRANT_GROUPMEMBERLIST() where GROUPID= @ID AND TYPECODE=TE.TYPECODE )        
                  ,@PROGRESSPARTICIPANTGOAL=          (select  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= @ID AND TYPECODE=TE.TYPECODE))                                            
          ,@PROGRESSCOMMUNICATIONGOAL=isnull(@TOTALOTHERCOMMS,0)+isnull(@TOTALSOCIALPOSTS,0)
                  ,@PROGRESSDONORRETENTIONGOAL=            NULL
  from          dbo.TEAMEXTENSION TE 
  join          dbo.TEAMFUNDRAISINGTEAM TFT        ON TE.TEAMFUNDRAISINGTEAMID = TFT.ID
  join      dbo.EVENTEXTENSION EX on TE.EVENTID = EX.EVENTID
  left join    dbo.UFN_COMPANY_TEAMS (@ID) AG    ON TE.EVENTID= AG.EVENTID    
  left join TEAMEXTENSION pTX on pTX.TEAMCONSTITUENTID = TE.TEAMCONSTITUENTID and EX.PRIORYEAREVENTID = pTX.EVENTID
  where          TE.TEAMFUNDRAISINGTEAMID = @ID

  select 
        @LEADERS = dbo.UDA_BUILDLIST(C.NAME) 
  from dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC (NOLOCK)
  left join dbo.CONSTITUENT C WITH (NOLOCK) on C.ID = TC.CONSTITUENTID
  where TC.TEAMFUNDRAISINGTEAMID = @ID
  group by TC.TEAMFUNDRAISINGTEAMID

    return 0;