USP_DATAFORMTEMPLATE_VIEW_FAFEVENTGROUPSUMMARY

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FAFEVENTGROUPSUMMARY
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @LEADERS nvarchar(max) = null output,
  @GROUPTYPE nvarchar(max) = null output,
    @PARENTGROUPNAME nvarchar(max) = null output,
    @STATUS nvarchar(20) = null output,
  @COMPANYREGISTRATION bit = null output,
  @COMPANYNAME nvarchar(max) = null output,
  @GROUPPAGEURL dbo.UDT_WEBADDRESS = null output,
  @NATIONALGROUPNAME nvarchar(max) = null output,
  @RAISEDTODATE money = null output,
  @RAISEDPREVIOUSEVENT money = null output,
  @FUNDRAISINGGOAL money = null output,
  @TEAMMEMBERS int = null output,
  @TEAMMEMBERSGOAL int = null output,
  @TEAMFUNDRAISINGTEAMID uniqueidentifier = null output,
  @PARENTTEAMFUNDRAISINGTEAMID uniqueidentifier = null output,
  @ORGANIZATIONCONSTITUENTID uniqueidentifier = null output,
  @NATIONALGROUPID uniqueidentifier = null output
)
as
    set nocount on;

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

  declare
    @EVENTID uniqueidentifier,
    @WEBSITESTATUS bit,
    @GROUPTYPECODE tinyint

    SELECT @EVENTID=EVENTID FROM TEAMEXTENSION WHERE TEAMFUNDRAISINGTEAMID=@ID

    SELECT @LEADERS=dbo.UDA_BUILDLIST(c2.NAME) from TEAMEXTENSION iTX
      left join TEAMFUNDRAISINGTEAMCAPTAIN tftc on iTX.TEAMFUNDRAISINGTEAMID = tftc.TEAMFUNDRAISINGTEAMID
      left join CONSTITUENT c2 on tftc.CONSTITUENTID = c2.ID 
    where iTX.EVENTID = @EVENTID
    and iTX.TEAMFUNDRAISINGTEAMID=@ID

    -- 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,
    @GROUPTYPE = TX.TYPE,
    @PARENTGROUPNAME = IC.Name,
    @STATUS = TX.STATUS,
    @COMPANYREGISTRATION = CASE WHEN FOIR.ID is null then 0 else 1 end,
    @COMPANYNAME = COMPANY.KEYNAME,
    @NATIONALGROUPNAME = NFG.NAME,
    @RAISEDTODATE = (select AMOUNTRAISED from dbo.UFN_FAF_GROUPTOTALSROLLUP(CURRENTEVENT.ID,@ID)),
    @RAISEDPREVIOUSEVENT = (select AMOUNTRAISED from dbo.UFN_FAF_GROUPTOTALSROLLUP(PRIOREVENT.ID,PRIORTEAMEXTENSION.TEAMFUNDRAISINGTEAMID)),
    @FUNDRAISINGGOAL = TFT.GOAL,
    @TEAMMEMBERS = FGS.TOTALPARTICIPANTS,
    @TEAMMEMBERSGOAL = TX.TEAMMEMBERGOAL,
    @TEAMFUNDRAISINGTEAMID = TFT.ID,
    @PARENTTEAMFUNDRAISINGTEAMID=TFT.PARENTTEAMID,
    @ORGANIZATIONCONSTITUENTID=COMPANY.ID,
    @NATIONALGROUPID = NFG.ID,
    @GROUPTYPECODE = TX.TYPECODE
  from dbo.TEAMFUNDRAISINGTEAM TFT  
  join TEAMEXTENSION TX on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
  left join CONSTITUENT C on TX.TEAMCONSTITUENTID = C.ID
  left join TEAMEXTENSION ITX on ITX.TEAMFUNDRAISINGTEAMID = TFT.PARENTTEAMID
  left join CONSTITUENT IC on IC.ID = iTX.TEAMCONSTITUENTID
  left join dbo.EVENT CURRENTEVENT on TX.EVENTID=CURRENTEVENT.ID
  left join dbo.EVENTEXTENSION CURRENTEVENTEXTENSION on TX.EVENTID=CURRENTEVENTEXTENSION.EVENTID
  left join dbo.EVENT PRIOREVENT on CURRENTEVENTEXTENSION.PRIORYEAREVENTID=PRIOREVENT.ID
  left join dbo.TEAMEXTENSION PRIORTEAMEXTENSION on PRIOREVENT.ID=PRIORTEAMEXTENSION.EVENTID and C.ID=PRIORTEAMEXTENSION.TEAMCONSTITUENTID
  left join dbo.FAFGROUPSUMMARYINFORMATION FGS on C.ID=FGS.GROUPCONSTITUENTID and CURRENTEVENT.ID=FGS.EVENTID
  left join dbo.FAFNFGCAMPAIGNLEVEL NFGCL On NFGCL.ID=TX.NFGCAMPAIGNLEVELID
  left join dbo.FAFNFGCAMPAIGN NFG on NFG.ID=NFGCL.NFGCAMPAIGNID
  left join dbo.FAFORGANIZATIONINDIVIDUALRELATION FOIR on C.ID=FOIR.INDIVIDUALCONSTITUENTID and CURRENTEVENT.ID=FOIR.EVENTID and FOIR.TYPECODE=1
  left join dbo.CONSTITUENT COMPANY on FOIR.ORGANIZATIONCONSTITUENTID=COMPANY.ID
  where TFT.ID=@ID  

  set @WEBSITESTATUS = dbo.UFN_CLIENTSITES_GETEVENTWEBSITESTATUS(@EVENTID) 

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

return 0;