spFundRaiserSolicitorDonationTotals

            CREATE         PROCEDURE [dbo].[spFundRaiserSolicitorDonationTotals]
            (@FundRaiserSolicitorID 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 @AggregateGoal money

            DECLARE @AthonAmount money
            DECLARE @AthonCount int

            DECLARE @FRDTable as Table(
            ID int primary key,
            amount money,
            solicitorID int,
            ProcessedDate datetime,
            IsOffline bit,
            IsAthon bit,
            IsDeleted bit)

            INSERT INTO @FRDTable
            SELECT FRDonation.ID, FRDonation.amount, FRDonation.SolicitorID, Donation.processed_date, FRDonation.IsOffline, FRDonation.IsAthon, Donation.IsDeleted
            FROM
            dbo.DonationTransactions Donation
            inner join dbo.FundRaiserDonations FRDonation on Donation.Status = 1 and FRDonation.SolicitorID = @FundRaiserSolicitorID and FRDonation.DonationTransactionsID = Donation.DonationTransactionsID

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            --Online Rejected Athon Amount
            SELECT
            @AthonAmount = isnull(sum(FRD.Amount * frs.AthonUnits),0),
            @AthonCount = count(FRD.ID)
            FROM
            @FRDTable frd
            inner join dbo.FundRaiserSolicitors frs on frs.ID = frd.solicitorID
            WHERE
            FRD.IsOffline = 0
            and
            FRD.IsAthon = 1
            and
            frd.IsDeleted = 1

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

            --Goal @AggregateGoal
            SELECT
            @AggregateGoal = Solicitor.Goal
            FROM
            dbo.FundRaiserSolicitors Solicitor
            WHERE
            Solicitor.id = @FundRaiserSolicitorID


            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',

            @AggregateGoal as 'AggregateGoal'