spFundRaiserDonationTotals

            CREATE              PROCEDURE [dbo].[spFundRaiserDonationTotals]
                (@FundRaiserID int)
                AS

                DECLARE @OfflineUnProcessedAmount money
                DECLARE @OfflineProcessedAmount money
                DECLARE @OfflineRejectedAmount money

                DECLARE @OnlineUnProcessedAmount money
                DECLARE @OnlineProcessedAmount money
                DECLARE @OnlineRejectedAmount money

                DECLARE @OfflineUnProcessedCount int
                DECLARE @OfflineProcessedCount int
                DECLARE @OfflineRejectedCount int

                DECLARE @OnlineUnProcessedCount int
                DECLARE @OnlineProcessedCount int
                DECLARE @OnlineRejectedCount int

                DECLARE @FundGoal money
                DECLARE @AggregateGoal money
                DECLARE @AggregateAthonUnits int

                DECLARE @TeamCount int
                DECLARE @SolicitorCount int

                DECLARE @AthonAmount money
                DECLARE @AthonCount int

                DECLARE @FRDTable Table(
                    ID int,
                    processed_date datetime,
                    SolicitorID int,
                    Amount money,
                    IsOffline bit,
                    IsAthon bit,
                    IsDeleted bit,
                    AthonUnits int)

                INSERT INTO @FRDTable
                    SELECT FRDonation.ID, Donation.Processed_Date, Solicitor.ID, FRDonation.Amount, FRDonation.IsOffline, FRDonation.IsAthon, Donation.IsDeleted, Solicitor.AthonUnits
                    FROM
                    dbo.DonationTransactions Donation
                    inner join dbo.FundRaiserDonations FRDonation on FRDonation.DonationTransactionsID = Donation.DonationTransactionsID
                    inner join dbo.FundRaiserSolicitors Solicitor on Solicitor.ID = FRDonation.SolicitorID
                    WHERE Solicitor.RootFundRaiserID = @FundraiserID

                --Offline Unprocessed Amount
                SELECT
                @OfflineUnProcessedAmount = isnull(sum(Amount),0),
                @OfflineUnProcessedCount = count(ID)
                FROM
                @FRDTable
                WHERE
                processed_date is null 
                and 
                IsOffline = 1
                and 
                IsAthon = 0
                and
                IsDeleted = 0

                --Offline Unprocessed Athon Amount
                SELECT
                @AthonAmount = isnull(sum(Amount * AthonUnits),0),
                @AthonCount = count(ID)
                FROM
                @FRDTable
                WHERE
                processed_date is null 
                and 
                IsOffline = 1
                and 
                IsAthon = 1
                and
                IsDeleted = 0
                and
                not exists (select null from dbo.FundRaiserDonations frd where frd.PledgeID=ID)

                SET @OfflineUnProcessedAmount = @OfflineUnProcessedAmount + @AthonAmount
                SET @OfflineUnProcessedCount = @OfflineUnProcessedCount + @AthonCount

                --Offline Processed Amount
                SELECT
                @OfflineProcessedAmount = isnull(sum(Amount),0),
                @OfflineProcessedCount = count(ID)
                FROM
                @FRDTable
                WHERE
                processed_date is not null 
                and 
                IsOffline = 1
                and 
                IsAthon = 0
                and
                IsDeleted = 0

                --Offline Processed Athon Amount
                SELECT
                @AthonAmount = isnull(sum(Amount * AthonUnits),0),
                @AthonCount = count(ID)
                FROM
                @FRDTable
                WHERE
                processed_date is not null 
                and 
                IsOffline = 1
                and 
                IsAthon = 1
                and
                IsDeleted = 0
                and
                not exists (select null from dbo.FundRaiserDonations frd where frd.PledgeID=ID)

                SET @OfflineProcessedAmount = @OfflineProcessedAmount + @AthonAmount
                SET @OfflineProcessedCount = @OfflineProcessedCount + @AthonCount

                --Offline Rejected Amount
                SELECT
                @OfflineRejectedAmount = isnull(sum(Amount),0),
                @OfflineRejectedCount = count(ID)
                FROM
                @FRDTable
                WHERE
                IsOffline = 1
                and
                IsAthon = 0
                and
                IsDeleted = 1

                --Offline Rejected Athon Amount
                SELECT
                @AthonAmount = isnull(sum(Amount * AthonUnits),0),
                @AthonCount = count(ID)
                FROM
                @FRDTable
                WHERE
                IsOffline = 1
                and
                IsAthon = 1
                and
                IsDeleted = 1
                and
                not exists (select null from dbo.FundRaiserDonations frd where frd.PledgeID=ID)

                SET @OfflineRejectedAmount = @OfflineRejectedAmount + @AthonAmount
                SET @OfflineRejectedCount = @OfflineRejectedCount + @AthonCount

                --Online Unprocessed Amount
                SELECT
                @OnlineUnProcessedAmount = isnull(sum(Amount),0),
                @OnlineUnProcessedCount = count(ID)
                FROM
                @FRDTable
                WHERE
                processed_date is null 
                and 
                IsOffline = 0
                and
                IsAthon = 0
                and
                IsDeleted = 0

                --Online Unprocessed Athon Amount
                SELECT
                @AthonAmount = isnull(sum(Amount * AthonUnits),0),
                @AthonCount = count(ID)
                FROM
                @FRDTable
                WHERE
                processed_date is null 
                and 
                IsOffline = 0
                and
                IsAthon = 1
                and
                IsDeleted = 0
                and
                not exists (select null from dbo.FundRaiserDonations frd where frd.PledgeID=ID)

                SET @OnlineUnProcessedAmount = @OnlineUnProcessedAmount + @AthonAmount
                SET @OnlineUnProcessedCount = @OnlineUnProcessedCount + @AthonCount

                --Online Processed Amount
                SELECT
                @OnlineProcessedAmount = isnull(sum(Amount),0),
                @OnlineProcessedCount = count(ID)
                FROM
                @FRDTable
                WHERE
                processed_date is not null 
                and 
                IsOffline = 0
                and 
                IsAthon = 0
                and
                IsDeleted = 0

                --Online Processed Athon Amount
                SELECT
                @AthonAmount = isnull(sum(Amount * AthonUnits),0),
                @AthonCount = count(ID)
                FROM
                @FRDTable
                WHERE
                processed_date is not null 
                and 
                IsOffline = 0
                and 
                IsAthon = 1
                and
                IsDeleted = 0
                and
                not exists (select null from dbo.FundRaiserDonations frd where frd.PledgeID=ID)

                SET @OnlineProcessedAmount = @OnlineProcessedAmount + @AthonAmount
                SET @OnlineProcessedCount = @OnlineProcessedCount + @AthonCount

                --Online Rejected Amount
                SELECT
                @OnlineRejectedAmount = isnull(sum(Amount),0),
                @OnlineRejectedCount = count(ID)
                FROM
                @FRDTable
                WHERE
                IsOffline = 0
                and
                IsAthon = 0
                and
                IsDeleted = 1

                --Online Rejected Athon Amount
                SELECT
                @AthonAmount = isnull(sum(Amount * AthonUnits),0),
                @AthonCount = count(ID)
                FROM
                @FRDTable
                WHERE
                IsOffline = 0
                and
                IsAthon = 1
                and
                IsDeleted = 1
                and
                not exists (select null from dbo.FundRaiserDonations frd where frd.PledgeID=ID)

                SET @OnlineRejectedAmount = @OnlineRejectedAmount + @AthonAmount
                SET @OnlineRejectedCount = @OnlineRejectedCount + @AthonCount

                --Goal @FundGoal 

                SELECT
                @FundGoal = fr.Goal
                FROM
                dbo.FundRaisers fr
                WHERE
                fr.id = @FundRaiserID


                --Goal @AggregateGoal, AthonUnits @AggregateAthonUnits
                SELECT
                @AggregateGoal = isnull(sum(solicitor.goal),0), @AggregateAthonUnits = isnull(sum(solicitor.AthonUnits),0), @SolicitorCount = Count(ID)
                FROM
                dbo.FundRaiserSolicitors solicitor
                WHERE Solicitor.RootFundraiserID = @FundRaiserID

                --All Team Count
                SELECT
                @TeamCount = Count(ID)
                FROM
                dbo.FundRaiserTeams frt
                WHERE frt.RootFundraiserID = @FundRaiserID

                --Return Values
                SELECT 
                @OfflineUnProcessedAmount as 'OfflineUnProcessedAmount',
                @OfflineUnProcessedCount as 'OfflineUnProcessedCount',

                @OfflineProcessedAmount as 'OfflineProcessedAmount',
                @OfflineProcessedCount as 'OfflineProcessedCount',

                @OfflineRejectedAmount as 'OfflineRejectedAmount',
                @OfflineRejectedCount as 'OfflineRejectedCount',

                @OnlineUnProcessedAmount as 'OnlineUnProcessedAmount',
                @OnlineUnProcessedCount as 'OnlineUnProcessedCount',

                @OnlineProcessedAmount as 'OnlineProcessedAmount',
                @OnlineProcessedCount as 'OnlineProcessedCount',

                @OnlineRejectedAmount as 'OnlineRejectedAmount',
                @OnlineRejectedCount as 'OnlineRejectedCount',

                @FundGoal as 'FundGoal',
                @AggregateGoal as 'AggregateGoal',
                @AggregateAthonUnits as 'AggregateAthonUnits',

                @TeamCount as 'TeamCount',
                @SolicitorCount as 'SolicitorCount'