spGetEmailLinks

CREATE PROCEDURE [dbo].[spGetEmailLinks]
( @EMailID INT ) AS
BEGIN
DECLARE @TotalEmailLinks INT
DECLARE @TotalTransactions INT
DECLARE @EmailLinksStatsTable TABLE
(
   ID INT
  , EmailID    INT
  , PID    INT
  , PageName NVARCHAR(500)
  , URL NVARCHAR(max)
  , Hits INT
  , Visitors INT
  , DocumentId INT
  ,TransactionCount INT
)

--Get transaction count against each email link


DECLARE @Transactions TABLE(
TransactionData  XML,
TransactionType INT
)
INSERT INTO @Transactions 
SELECT
CAST(REPLACE(CAST(ET.XMLObjectData AS NVARCHAR(MAX)),'encoding="utf-8"', '')AS XML) AS TransactionData,0
FROM  dbo.EventTransactions AS ET 
INNER JOIN EmailJob_recipient AS ER ON ER.ID = ET.EmailJobRecipientId 
WHERE ER.emailId =@EMailID and ER.Sent = 1 and ET.ISDELETED = 0


INSERT INTO @Transactions 
SELECT
CAST(REPLACE(CAST(DT.XMLObjectData AS NVARCHAR(MAX)),'encoding="utf-8"', '')AS XML) AS TransactionData,1
FROM  dbo.DonationTransactions AS DT
INNER JOIN EmailJob_recipient AS ER ON ER.ID = DT.EmailJobRecipientId 
WHERE ER.emailId =@EMailID and ER.Sent = 1 and DT.ISDELETED = 0

INSERT INTO @Transactions 
SELECT
CAST(REPLACE(CAST(MT.XMLObjectData AS NVARCHAR(MAX)),'encoding="utf-8"', '') AS XML) AS TransactionData,2
FROM  dbo.MembershipTransactions AS MT 
INNER JOIN EmailJob_recipient AS ER ON ER.ID = MT.EmailJobRecipientId 
WHERE ER.emailId =@EMailID and ER.Sent = 1 and MT.ISDELETED = 0

DECLARE @TransactionsStats TABLE (PageId INT,PageURL nvarchar(1784))
INSERT INTO @TransactionsStats
SELECT     
    CASE  WHEN TransactionType = 0 THEN transactionData.value('declare namespace ns="urn:blackbaud.RE7.XDATA";(/ns:ShelbyEventRegTran/ns:Origin/ns:PageID)[1]', 'NVARCHAR(30)')
          WHEN TransactionType = 1 THEN transactionData.value('declare namespace ns="urn:blackbaud.RE7.XDATA";(/ns:ShelbyDonationTran/ns:Origin/ns:PageID)[1]', 'NVARCHAR(30)')
          WHEN TransactionType = 2 THEN transactionData.value('declare namespace ns="urn:blackbaud.RE7.XDATA";(/ns:ShelbyMembershipTransaction/ns:Origin/ns:PageID)[1]', 'NVARCHAR(30)')
          ELSE '' END AS PageID,
    CASE  WHEN TransactionType = 0 THEN transactionData.value('declare namespace ns="urn:blackbaud.RE7.XDATA";(/ns:ShelbyEventRegTran/ns:Origin/ns:PageURL)[1]', 'NVARCHAR(2100)')
          WHEN TransactionType = 1 THEN transactionData.value('declare namespace ns="urn:blackbaud.RE7.XDATA";(/ns:ShelbyDonationTran/ns:Origin/ns:PageURL)[1]', 'NVARCHAR(2100)')
          WHEN TransactionType = 2 THEN transactionData.value('declare namespace ns="urn:blackbaud.RE7.XDATA";(/ns:ShelbyMembershipTransaction/ns:Origin/ns:PageURL)[1]', 'NVARCHAR(2100)')
          ELSE '' END AS PageURL      
FROM @Transactions 

INSERT INTO @EmailLinksStatsTable
SELECT    EL.[ID]
        , EL.[EmailID]
        , EL.[PID]
        , P.PageName 
        , EL.[URL] 
        , COUNT(S.statsid) hits 
        , COUNT(DISTINCT S.emailjobrecipientid) visitors        
        , EL.[DocumentID]
        , ISNULL(T.transactionCount,0) AS TransactionCount
FROM [EmailLink]    EL
LEFT OUTER JOIN        SitePages    P ON P.ID = EL.PID
LEFT OUTER JOIN        Stats        S ON S.pageid = EL.pid AND s.sourceid = EL.emailid AND S.sourcetypeid = 1
LEFT OUTER JOIN (SELECT PageId , COUNT(1) as TransactionCount,Min(PageURL) as PageURL FROM @TransactionsStats GROUP BY PageId) AS T ON T.PageId = EL.PID or T.PageURL like EL.URL+'%'
WHERE EL.EmailID = @EmailID AND EL.[PID] IS NOT null
GROUP BY EL.[ID], EL.[EmailID], EL.[PID], P.PageName, EL.[URL], EL.[DocumentID],T.TransactionCount

UNION

SELECT      EL.[ID]
        , EL.[EmailID]
        , EL.[PID]
        , P.PageName
        , EL.[URL]
        , COUNT(S.statsid) hits
        , COUNT(DISTINCT S.emailjobrecipientid) visitors        
        , EL.[DocumentID]        
        , ISNULL(T.transactionCount,0) AS TransactionCount
FROM [EmailLink]    EL
LEFT OUTER JOIN        SitePages    P ON P.ID = EL.PID
LEFT OUTER JOIN        Stats        S ON S.URL = EL.URL AND S.SOURCEID = EL.EMAILID  AND S.SOURCETYPEID = 1
LEFT OUTER JOIN (SELECT PageId , COUNT(1) as TransactionCount,Min(PageURL) as PageURL FROM @TransactionsStats GROUP BY PageId) AS T ON T.PageId = EL.PID or T.PageURL like EL.URL+'%'
WHERE EL.EMAILID = @EmailID AND EL.[URL] IS NOT NULL
GROUP BY EL.[ID], EL.[EmailID], EL.[PID], PageName, EL.[URL], EL.[DocumentID],T.TransactionCount

UNION

SELECT      EL.[ID]
        , EL.[EmailID]
        , EL.[PID]
        , P.PageName
        , EL.[URL]
        , COUNT(S.statsid) hits
        , COUNT(DISTINCT S.emailjobrecipientid) visitors        
        , EL.[DocumentID]        
        , ISNULL(T.transactionCount,0) as TransactionCount
FROM [EmailLink]    EL
LEFT OUTER JOIN        SitePages    P ON P.ID = EL.PID
LEFT OUTER JOIN        Stats        S ON S.DocumentId = EL.DocumentID AND S.sourceid = EL.emailid  AND S.sourcetypeid = 0
LEFT OUTER JOIN (SELECT PageId , COUNT(1) as TransactionCount,Min(PageURL) as PageURL FROM @TransactionsStats GROUP BY PageId) AS T ON T.PageId = EL.PID or T.PageURL like EL.URL+'%'
WHERE EL.EmailID = @EmailID and EL.[DocumentID] IS NOT NULL
GROUP BY EL.[ID], EL.[EmailID], EL.[PID], PageName, EL.[URL], EL.[DocumentID],T.TransactionCount

SELECT @TotalEmailLinks = SUM(Visitors),@TotalTransactions = SUM(TransactionCount) FROM @EmailLinksStatsTable

    IF @TotalEmailLinks IS NOT NULL AND  @TotalEmailLinks > 0 BEGIN 
        Select DISTINCT
              ET.ID
            , ET.EmailID
            , ET.PID
            , ET.PageName
            , CASE WHEN EJ.Status = 7 THEN ET.Hits ELSE 0 END AS Hits
            , ET.URL
            , CASE WHEN EJ.Status = 7 THEN ET.Visitors ELSE 0 END AS Visitors
            , ET.[DocumentID]
            , CASE WHEN EJ.Status = 7 THEN CONVERT(DECIMAL(10,2) ,ROUND((et.Visitors*100.0)/ @TotalEmailLinks, 2)) ELSE 0.00 END AS ClickPercentage
            , TransactionCount
            , CASE WHEN @TotalTransactions <=0 THEN 0 ELSE CONVERT(DECIMAL(10,2) ,ROUND((ET.TransactionCount*100.0)/ @TotalTransactions, 2)) END AS TransactionPercentage

        FROM @EmailLinksStatsTable  ET 
        INNER JOIN EmailJob EJ
        ON ET.EmailID = EJ.EmailID
    END
    ELSE BEGIN
        SELECT DISTINCT
              ET.ID
            , ET.EmailID
            , ET.PID
            , ET.PageName
            , CASE WHEN EJ.Status = 7 THEN ET.Hits ELSE 0 END AS Hits
            , ET.URL
            , CASE WHEN EJ.Status = 7 THEN ET.Visitors ELSE 0 END AS Visitors
            , ET.[DocumentID]
            , CONVERT(decimal(10,2) ,0.00) ClickPercentage
            ,TransactionCount
            ,CASE WHEN @TotalTransactions <=0 THEN 0 ELSE CONVERT(DECIMAL(10,2) ,ROUND((ET.TransactionCount*100.0)/ @TotalTransactions, 2)) END AS TransactionPercentage
        FROM @EmailLinksStatsTable  ET 
        INNER JOIN EmailJob EJ
        ON ET.EmailID = EJ.EmailID
    END
END