spEmail_QueryTransTotals

CREATE    PROCEDURE [dbo].[spEmail_QueryTransTotals]

            @BeginDate datetime,
            @EndDate datetime,
            @QueryType int,
            @EmailType int

            AS

            DECLARE @Table table 
                (
                EmailID int, 
                EmailName varchar(255),
                EmailDate datetime,
                EmailType int,
                EmailTemplateID int,
                TransCount int, 
                TransGiftAmount money, 
                TransGiftCurrencyType int
                )

                if @QueryType = 0 
                --Donations
                    begin    

                        INSERT INTO @Table (EmailID, EmailName, EmailDate, EmailType,    EmailTemplateID, TransCount, TransGiftAmount, TransGiftCurrencyType)

                        SELECT 
                            email.ID EmailID, 
                            email.Name 'EmailName',
                            ejob.CreateDate,
                            email.Type,
                            email.EmailTemplateID,

                            count(dt.DonationTransactionsID), 
                            ISNULL(sum(dt.GiftAmount),0),

                            dt.GiftCurrencyType

                        FROM            
                            dbo.Email email
                            LEFT JOIN DonationTransactions dt ON email.ID = dt.EmailID
                            INNER JOIN dbo.EmailJob ejob ON email.ID = ejob.EmailID

                        WHERE
                            ejob.CreateDate >= @BeginDate AND ejob.CreateDate <= @EndDate
                            AND dt.Status = 1                

                        GROUP BY email.ID, email.Name, dt.GiftCurrencyType, ejob.CreateDate, email.Type, email.EmailTemplateID       

                        ORDER BY  ejob.CreateDate

                    end

                if @QueryType = 1 
                --Event Registrations
                    begin

                        INSERT INTO @Table (EmailID, EmailName, EmailDate, EmailType, EmailTemplateID, TransCount, TransGiftAmount, TransGiftCurrencyType)

                        SELECT 
                            email.ID EmailID, 
                            email.Name 'EmailName',
                            ejob.CreateDate,
                            email.Type,
                            email.EmailTemplateID,

                            count(et.EventTransactionsID), 
                            ISNULL(sum(et.GiftAmount),0),

                            et.GiftCurrencyType

                        FROM            
                            dbo.Email email
                            LEFT JOIN EventTransactions et ON email.ID = et.EmailID
                            INNER JOIN dbo.EmailJob ejob ON email.ID = ejob.EmailID

                        WHERE
                            ejob.CreateDate >= @BeginDate AND ejob.CreateDate <= @EndDate                
                            AND et.Status = 1

                        GROUP BY email.ID, email.Name, et.GiftCurrencyType, ejob.CreateDate, email.Type, email.EmailTemplateID       

                        ORDER BY  ejob.CreateDate

                    end

                if @QueryType = 2
                --Memberships    
                    begin

                        INSERT INTO @Table (EmailID, EmailName, EmailDate, EmailType, EmailTemplateID, TransCount, TransGiftAmount, TransGiftCurrencyType)

                        SELECT 
                            email.ID EmailID, 
                            email.Name 'EmailName',
                            ejob.CreateDate,
                            email.Type,
                            email.EmailTemplateID,

                            count(mt.ID), 
                            ISNULL(sum(mt.GiftAmount),0),

                            mt.GiftCurrencyType

                        FROM            
                            dbo.Email email
                            LEFT JOIN MembershipTransactions mt ON email.ID = mt.EmailID
                            INNER JOIN dbo.EmailJob ejob ON email.ID = ejob.EmailID

                        WHERE
                            ejob.CreateDate >= @BeginDate AND ejob.CreateDate <= @EndDate                
                            AND mt.Status = 1

                        GROUP BY email.ID, email.Name, mt.GiftCurrencyType, ejob.CreateDate, email.Type, email.EmailTemplateID       

                        ORDER BY  ejob.CreateDate

                    end


                DECLARE @SelectTable table 
                (
                EmailID int, 
                EmailName varchar(255),
                EmailDate datetime,
                EmailType int,
                EmailTemplateID int,
                TransCount int, 
                TransGiftAmount money, 
                TransGiftCurrencyType int
                )


                if @EmailType = -1
                    begin
                    INSERT INTO @SelectTable (EmailID, EmailName, EmailDate, EmailType, EmailTemplateID, TransCount, TransGiftAmount, TransGiftCurrencyType)
                    SELECT * FROM @Table
                    end
                else
                    begin
                    INSERT INTO @SelectTable (EmailID, EmailName, EmailDate, EmailType, EmailTemplateID, TransCount, TransGiftAmount, TransGiftCurrencyType)
                    SELECT * FROM @Table 
                        WHERE (EmailType = @EmailType) 
                            or (@EmailType = 7 and EmailType in (7,8,9)) 

                    end


                --ResultSet
                SELECT * FROM @SelectTable


                --ResultSet1
                SELECT DISTINCT TransGiftCurrencyType FROM @Table WHERE TransGiftCurrencyType IS NOT NULL