USP_GET_EMAILADDRESS_STATS

CREATE procedure dbo.USP_GET_EMAILADDRESS_STATS
(
    @EMAILADDRESS nvarchar(50)
)
as
begin

    declare
            @NUMBERRECEIVED int,                  -- The number of emails that BBIS has sent this user

            @NUMBEROPENED int,                      -- The number of emails from BBIS that this user has opened

            @CLICKNUMBER int,                        -- The number of links from a BBIS email that the user has clicked

            @TRANSACTIONNUMBER int,                -- The number of transactions created from this user that originate from a BBIS email

            @TRANSACTIONSTOTAL money;            -- The dollar amount of the transactions from this user that have originated from a BBIS email


    -- Get the ID's of emails this user received and the flag determining if they've been opened or not

    declare @USEREMAILJOBRECIPIENT_TABLE table(EmailJobRecipientId int, Opened bit);
    insert into @USEREMAILJOBRECIPIENT_TABLE(EmailJobRecipientId, Opened)
    select ID, Opened
    from dbo.EmailJob_Recipient 
    where EmailAddress = @EMAILADDRESS;

    -- The user's rate of opening emails should be the number of emails they have opened divided by the number of emails they've received

    select @NUMBERRECEIVED = count(EmailJobRecipientId) from @USEREMAILJOBRECIPIENT_TABLE;
    select @NUMBEROPENED = count(EmailJobRecipientId) from @USEREMAILJOBRECIPIENT_TABLE where Opened = 1;

    -- Get the number of pages the a user has reached via a BBIS email (1 click per email)

    -- Each page hit has an email job recipient linked to it (if applicable). 

    -- We can use the email job recipient ids we got earlier to determine when a link was clicked

    select @CLICKNUMBER = count(distinct S.EmailJobRecipientID)
    from dbo.Stats S
    inner join @USEREMAILJOBRECIPIENT_TABLE E on E.EmailJobRecipientId = S.EmailJobRecipientID;

    --  Get the gift amount toal from the Transaction-Table brothers using the Email Recipient IDs

    declare @EMAILTRANSACTIONAMOUNTS_TABLE table(Amount money);
    insert into @EMAILTRANSACTIONAMOUNTS_TABLE(Amount)
    select T.GiftAmount
    from
    (
        select EmailJobRecipientID, GiftAmount from dbo.DonationTransactions where ISDELETED = 0
        union all
        select EmailJobRecipientID, GiftAmount from dbo.EventTransactions where ISDELETED = 0
        union all
        select EmailJobRecipientID, GiftAmount from.dbo.MembershipTransactions where ISDELETED = 0
    ) T
    inner join @USEREMAILJOBRECIPIENT_TABLE E on E.EmailJobRecipientId = T.EmailJobRecipientID;

    -- Get the total number of transactions and the total of those transactions

    select @TRANSACTIONNUMBER = count(Amount), 
           @TRANSACTIONSTOTAL = sum(Amount) 
    from @EMAILTRANSACTIONAMOUNTS_TABLE;

    -- Return all of the values we need!

    select 
       @EMAILADDRESS as EmailAddress,
       -- Dapper Split occurs here (all user info should go above this line, all stat info should go below)

           @NUMBERRECEIVED as NumberOfEmailsReceived,
           @NUMBEROPENED As NumberOfEmailsOpened,
           @CLICKNUMBER As NumberOfEmailsClickedThrough,
           @TRANSACTIONNUMBER As NumberOfTransactionsFromEmail,
           @TRANSACTIONSTOTAL as TotalGiftAmountFromEmails;
end