USP_REPORT_GROUPFUNDRAISINGANDGIVING_SUMMARY

CREATE procedure dbo.USP_REPORT_GROUPFUNDRAISINGANDGIVING_SUMMARY
(
    @GROUPID uniqueidentifier,
    @REPORTUSERID nvarchar(128) = null,
    @ALTREPORTUSERID nvarchar(128) = null
)
as
begin

    declare @CURRENTAPPUSERID uniqueidentifier;
    declare @CURRENTDATE datetime;

    declare @LOOKUPID nvarchar(100);
    declare @CONSTITUENCIES nvarchar(100);
    declare @GROUPTYPE nvarchar(150);
    declare @NUMMEMBERS int;
    declare @EVENTID uniqueidentifier;
    declare @EVENTNAME nvarchar(100);
    declare @CAMPAIGNID uniqueidentifier;
    declare @CAMPAIGNNAME nvarchar(100);

    set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    select 
        @LOOKUPID = LOOKUPID,
        @CONSTITUENCIES = dbo.UFN_CONSTITUENT_GETCONSTITUENCYLIST(@GROUPID,@CURRENTAPPUSERID),
        @GROUPTYPE = GROUPTYPE.NAME
    from dbo.CONSTITUENT
    inner join dbo.GROUPDATA
        on CONSTITUENT.ID = GROUPDATA.ID
    inner join dbo.GROUPTYPE
        on GROUPDATA.GROUPTYPEID = GROUPTYPE.ID
    where CONSTITUENT.ID = @GROUPID;

    --Event

    select top 1
        @EVENTID = EVENT.ID,
        @EVENTNAME = EVENT.NAME
    from dbo.EVENTCOORDINATOR
    inner join dbo.EVENT
        on EVENT.ID = EVENTCOORDINATOR.EVENTID
    where CONSTITUENTID = @GROUPID
    order by EVENT.ISACTIVE desc,
            case when EVENT.ENDDATE >= @CURRENTDATE then 0 else 1 end asc,
            EVENT.STARTDATE asc,
            EVENT.NAME asc;

    --Campaign

    select top 1
        @CAMPAIGNID = CAMPAIGN.ID,
        @CAMPAIGNNAME = CAMPAIGN.NAME
    from dbo.CAMPAIGNFUNDRAISER
    inner join dbo.CAMPAIGN
        on CAMPAIGNFUNDRAISER.CAMPAIGNID = CAMPAIGN.ID
    where 
        CAMPAIGNFUNDRAISER.CONSTITUENTID = @GROUPID
    order by 
        case when CAMPAIGNFUNDRAISER.DATETO >= @CURRENTDATE then 0 else 1 end asc,
        CAMPAIGNFUNDRAISER.DATEFROM asc,
        CAMPAIGN.NAME asc;


    select @NUMMEMBERS = COUNT(*) 
    from GROUPMEMBER
    where GROUPID = @GROUPID;

    select
        dbo.UFN_CONSTITUENT_BUILDNAME(@GROUPID) GROUPNAME,
        @LOOKUPID LOOKUPID,
        @CONSTITUENCIES CONSTITUENCIES,
        @GROUPTYPE GROUPTYPE,
        @NUMMEMBERS NUMMEMBERS,
        'http://www.blackbaud.com/EVENTID?EVENTID=' + CONVERT(nvarchar(36),@EVENTID) as EVENTURL,
        @EVENTNAME EVENTNAME,
        'http://www.blackbaud.com/CAMPAIGNID?CAMPAIGNID=' + CONVERT(nvarchar(36),@CAMPAIGNID) as CAMPAIGNURL,
        @CAMPAIGNNAME CAMPAIGNNAME

end