spFundRaiserSolicitorDonorsByAction

            CREATE PROCEDURE [dbo].[spFundRaiserSolicitorDonorsByAction]
            (
            @FundRaiserSolicitorsID int,
            @Action int
            )
            AS
                BEGIN

                SELECT     
                actions.AddressbookContactsID as 'AddressBookContactID',
                actions.FundraiserSolicitorContactID as 'SolicitorContactID',
                sum(donations.amount) as 'TotalAmountDonated',
                count(donations.ID) as 'DonationCount',
                donations.IsAthon,
                null as 'FundRaiserDonationID'
                FROM  
                dbo.FundraiserSolicitorContactAction actions
                inner join dbo.FundRaiserDonations donations on donations.id = actions.FundRaiserDonationsID
                inner join dbo.DonationTransactions donationTran on donations.DonationTransactionsID = donationTran.DonationTransactionsID

                WHERE
                actions.FundraiserSolicitorsID = @FundRaiserSolicitorsID
                AND     (
                    (@Action=8 and donations.ThankedDate is null) 
                        OR 
                    (@Action=2 and donations.ThankedDate is not null)
                    )
                AND
                donationTran.isDeleted = 0

                GROUP BY
                actions.FundraiserSolicitorContactID, actions.AddressbookContactsID, donations.IsAthon

                UNION

                SELECT     
                actions.AddressbookContactsID as 'AddressBookContactID',
                actions.FundraiserSolicitorContactID as 'SolicitorContactID',
                sum(donations.amount * solicitors.athonunits) as 'TotalAmountDonated',
                count(donations.ID) as 'DonationCount',
                donations.IsAthon,
                donations.ID as 'FundRaiserDonationID'
                FROM  
                dbo.FundraiserSolicitorContactAction actions
                inner join dbo.FundRaiserDonations donations on donations.id = actions.FundRaiserDonationsID
                inner join dbo.FundRaiserSolicitors solicitors on solicitors.id = donations.SolicitorID

                WHERE
                actions.FundraiserSolicitorsID = @FundRaiserSolicitorsID
                AND
                    donations.IsAthon=1 
                AND     (
                    (@Action=13 and donations.InvoiceDate is null and donations.InvoicePaid=0) 
                        OR 
                    (@Action=14 and donations.InvoiceDate is not null)
                        OR
                    (@Action=15 and donations.InvoiceDate is not null and exists(select null from dbo.FundRaiserDonations frd where frd.PledgeID=donations.ID))
                        OR
                    (@Action=16 and donations.InvoiceDate is not null and not exists(select null from dbo.FundRaiserDonations frd where frd.PledgeID=donations.ID))
                    )

                GROUP BY
                actions.FundraiserSolicitorContactID, actions.AddressbookContactsID, donations.IsAthon, donations.amount, donations.ID

                ORDER BY    
                TotalAmountDonated

            END