USP_GET_EMAILS_USAGE_TRACKING_DATA

    CREATE PROCEDURE dbo.USP_GET_EMAILS_USAGE_TRACKING_DATA (
        @LastProcessingDate DateTime,
        @ProductName varchar(4)
    )
    AS
    BEGIN

        DECLARE @OrganizationDetails NVARCHAR(400)
        DECLARE @UpperLimitDate DateTime

        SET @UpperLimitDate = DATEADD(DAY,-2,GETUTCDATE())

        SELECT @OrganizationDetails = CLARIFYSITEID,
               @OrganizationDetails = @OrganizationDetails + ' - '
                                      + INSTALLATIONNAME
        FROM   dbo.INSTALLATIONINFO;

        WITH
             --Get all distinct email link clicks for all Emails

             DISTINCT_EMAIL_CLICKS_CTE (EmailId, TotalDistinctClicks)
             AS (SELECT EMAIL.id,
                        Count(DISTINCT( STATS.EmailJobRecipientID )) AS TotalDistinctClicks
                 FROM   dbo.EMAILLINK
                        INNER JOIN dbo.EMAIL
                                ON EMAILLINK.EmailId = EMAIL.Id
                        LEFT OUTER JOIN dbo.SITEPAGES
                                ON SITEPAGES.Id = EMAILLINK.PId
                        LEFT OUTER JOIN dbo.STATS
                                ON (STATS.pageid = EMAILLINK.pid
                                    AND STATS.sourceid = EMAILLINK.emailid
                                    AND STATS.sourcetypeid = 1 -- 1 means source is email.

                                    AND EMAILLINK.PID IS NOT NULL )
                                    OR ( STATS.URL = EMAILLINK.URL
                                        AND STATS.sourceid = EMAILLINK.emailid
                                        AND STATS.sourcetypeid = 1 -- 1 means source is email.

                                        AND EMAILLINK.[URL] IS NOT NULL )
                                    OR ( STATS.DocumentId = EMAILLINK.DocumentID
                                        AND STATS.sourceid = EMAILLINK.emailid
                                        AND STATS.sourcetypeid = 0 -- 0 means source is page.

                                        AND EMAILLINK.[DocumentID] IS NOT NULL )
                 WHERE  EMAILLINK.EmailId = EMAIL.Id
                            AND EMAIL.Deleted = 0
                            AND EMAIL.Type NOT IN ( 1, 7, 15, 18 ) 
                            AND EMAIL.SendAfterDate >  @LastProcessingDate  
                        AND EMAIL.SendAfterDate < @UpperLimitDate  
                 GROUP BY 
                        EMAIL.id),
             --Get the Emails which are actually sent. Actually sent means, they are not bounced, opted out etc.

             EMAILS_REALLY_SENT_CTE(EmailId, Sent)
             AS (SELECT EMAILJOB.emailId,
                        COALESCE(Sum(CASE
                                       WHEN EMAILJOB_RECIPIENT.Sent = 1
                                            AND EMAILJOB_RECIPIENT.dsned <> 1 
                                       THEN 1
                                       ELSE 0
                                     END), 0) AS Sent
                 FROM   dbo.EMAILJOB
                        LEFT OUTER JOIN dbo.EMAILJOB_RECIPIENT
                                ON EMAILJOB_RECIPIENT.EmailJobID = EMAILJOB.ID
                                   AND EMAILJOB_RECIPIENT.[Current] = 1
                                INNER JOIN dbo.EMAIL 
                                ON EMAIL.ID = EMAILJOB.EmailID
                 WHERE  EMAILJOB.[Current] = 1
                        AND EMAILJOB.[Status] = 7 -- 7 means completed.

                                AND EMAIL.Deleted = 0
                                AND EMAIL.Type NOT IN ( 1, 7, 15, 18 ) 
                                AND EMAIL.SendAfterDate >  @LastProcessingDate    
                        AND EMAIL.SendAfterDate < @UpperLimitDate 
                 GROUP BY 
                        EMAILJOB.EmailID),
             -- Gets the emails which are not sent due to any reason

             EMAILS_NOT_SENT_CTE(EmailId, NotSent)
             AS (SELECT EMAIL_RECIPIENT.EmailID,
                        Count(EMAIL_RECIPIENT.EmailID)
                 FROM   dbo.EMAIL_RECIPIENT
                        LEFT OUTER JOIN dbo.EmailJob_Recipient
                                ON EMAIL_RECIPIENT.iD = EMAILJOB_RECIPIENT.EmailRecipientID
                        INNER JOIN dbo.Email
                                ON EMAIL.ID = EMAIL_RECIPIENT.EmailID
                 WHERE  ( ( EMAILJOB_RECIPIENT.DSNed = 'true'
                            AND EMAILJOB_RECIPIENT.Sent = 'true' )
                           OR EMAIL_RECIPIENT.InvalidAddress = 'true'
                           OR EMAIL_RECIPIENT.InvalidAccount = 'true'
                           OR EMAIL_RECIPIENT.OptOut = 'true' )
                                AND EMAIL.Deleted = 0
                                AND EMAIL.Type NOT IN ( 1, 7, 15, 18 ) 
                                AND EMAIL.SendAfterDate >  @LastProcessingDate   
                        AND EMAIL.SendAfterDate < @UpperLimitDate 
                 GROUP BY 
                        EMAIL_RECIPIENT.EmailID)

        --Now get the Usage tracking data by joining the required tables and CTE's

        SELECT @ProductName + '-' + 'Emails' + '-' + @OrganizationDetails AS 'Group',
               CASE
                 WHEN EMAIL.Type = 0 THEN 'Blast'
                 WHEN EMAIL.Type = 2 THEN 'Newsletter'
                 WHEN EMAIL.Type = 3 THEN 'Campaign'
                 WHEN EMAIL.Type = 4 THEN 'FundraiserBlast'
                 WHEN EMAIL.Type = 5 THEN 'Chapter'
                 WHEN EMAIL.Type = 6 THEN 'PersonalPageBlast'
                 WHEN EMAIL.Type = 8 THEN 'ProjectAppealBlastFinal'
                 WHEN EMAIL.Type = 9 THEN 'ProjectAppealSeed'
                 WHEN EMAIL.Type = 10 THEN 'SignupNotification'
                 WHEN EMAIL.Type = 11 THEN 'DonationNotification'
                 WHEN EMAIL.Type = 12 THEN 'PageSharing'
                 WHEN EMAIL.Type = 13 THEN 'SiteNotification'
                 WHEN EMAIL.Type = 14 THEN 'ECard'
                 WHEN EMAIL.Type = 16 THEN 'BBDM'
                 WHEN EMAIL.Type = 17 THEN 'Schedule'
                 WHEN EMAIL.Type = 19 THEN 'Sponsorship'
                 WHEN EMAIL.Type = 17 THEN 'Schedule'
                 WHEN EMAIL.Type = 20 THEN 'AdvocacyTarget'
                 WHEN EMAIL.Type = 21 THEN 'FAFParticipantCommunication'
                 ELSE 'OTHERS'
               END                                                  AS TypeOfEmail,
               EMAIL.ID                                             AS 'EmailID',
               EMAIL.NAME                                           AS 'EmailName',
               EMAIL.SUBJECT                                        AS 'EmailSubject',
               MAX(EMAIL.SendAfterDate)                             AS 'DateSent',
               Isnull(EMAILS_REALLY_SENT_CTE.Sent, 0)               AS 'TotalEmailsDelivered',
               Isnull(EMAILS_NOT_SENT_CTE.NotSent, 0)               AS 'TotalEmailsNotDelivered',
               Sum(EMAIL_STATS.Opened)                              AS 'TotalEmailsOpened',
               DISTINCT_EMAIL_CLICKS_CTE.TotalDistinctClicks        AS 'TotalEmailsClicked'
        FROM   dbo.EMAIL     
               LEFT OUTER JOIN dbo.EMAIL_STATS
                            ON EMAIL_STATS.ID = EMAIL.ID
               LEFT OUTER JOIN DISTINCT_EMAIL_CLICKS_CTE
                            ON DISTINCT_EMAIL_CLICKS_CTE.EmailId = EMAIL.ID
               LEFT OUTER JOIN EMAILS_NOT_SENT_CTE
                            ON EMAILS_NOT_SENT_CTE.EmailId = Email.ID
               LEFT OUTER JOIN EMAILS_REALLY_SENT_CTE
                            ON EMAILS_REALLY_SENT_CTE.EmailId = Email.ID
        WHERE  EMAIL.SendAfterDate > @LastProcessingDate   
               AND EMAIL.SendAfterDate < @UpperLimitDate 
               AND Email.Type NOT IN ( 1, 7, 15, 18 )   -- These types have been excluded as per the requirements of the Usage Tracking feature

                 AND EMAIL.Deleted = 0
        GROUP  BY EMAIL.Type,
                  EMAIL.ID,
                  EMAIL.NAME,
                  EMAIL.Subject,        
                  EMAILS_REALLY_SENT_CTE.Sent,
                  EMAILS_NOT_SENT_CTE.NotSent,
                  DISTINCT_EMAIL_CLICKS_CTE.TotalDistinctClicks
        ORDER  BY Email.type 

    END