USP_GET_CRM_INTEGRATION_STATS

CREATE procedure dbo.USP_GET_CRM_INTEGRATION_STATS
(
@CRMIntegrationRecordsXML AS XML
)
AS
BEGIN

    DECLARE @RecordCount AS INT;
    DECLARE @Counter as INT =1
    DECLARE @RecordTypeCount AS INT;
    DECLARE @RecordTypeCode INT;
    DECLARE @DownloadStartPoint DATETIME;
    DECLARE @LastId INT;

    DECLARE @CrmIntegrationStatsTempTable AS TABLE (PendingRecordCount INT,RecordType INT,UpdateDate DATETIME);

    DECLARE @CRMIntegrationTokenTempTable AS TABLE (Rownumber INT IDENTITY (1,1), RecordTypeCode INT, DownloadStartPoint DATETIME, LastId NVARCHAR(50))

    INSERT INTO @CRMIntegrationTokenTempTable
    SELECT   col.value('data(@TypeCode[1])', 'INT') AS RecordTypeCode
            ,col.value('data(DownloadStartPoint[1])', 'DATETIME') AS DownloadStartPoint
            ,col.value('data(LastId[1])', 'NVARCHAR(100)') AS LastId

    FROM    @CRMIntegrationRecordsXML.nodes('/RecordTypes/RecordType') tbl(col)

    SELECT @RecordTypeCount =Max(RowNumber) FROM @CRMIntegrationTokenTempTable

    WHILE(@Counter <=@RecordTypeCount)
    BEGIN
    select @RecordTypeCode =RecordTypeCode,@DownloadStartPoint = DownloadStartPoint,@LastId = LastId FROM @CRMIntegrationTokenTempTable WHERE RowNumber = @Counter

    /*For Client User*/
        IF @RecordTypeCode = 0
        BEGIN
            SELECT @RecordCount = COUNT(*) 
            FROM dbo.Clients C, dbo.ClientUsers CU 
            LEFT JOIN dbo.backofficesystemusers BOSU ON CU.ID = BOSU.ClientUsersID
            LEFT JOIN dbo.backofficesystempeople BOSP ON BOSU.BackOfficePeopleID = BOSP.ID
            WHERE 
            ISNULL(BOSP.BackOfficeSystemId, 0) = 0
            AND (CU.UpdateDate > @DownloadStartPoint) or (CU.UpdateDate = @DownloadStartPoint and CU.id > @LastId)            
        END

        /*For PersonalPages*/
        IF @RecordTypeCode = 1
        BEGIN
            SELECT @RecordCount = COUNT(*)
            FROM dbo.fundraisersolicitors fs
            INNER JOIN dbo.fundraiserpages fp 
            ON fs.fundraiserid = fp.fundraiserid
            INNER JOIN dbo.sitepages sp 
            ON fp.sitepageid = sp.id
            LEFT OUTER JOIN dbo.stats s 
            ON s.pageid = sp.id and s.frsolicitorid = fs.id
            WHERE fs.backofficeid IS NOT NULL
            AND s.requestdate > @DownloadStartpoint
            GROUP BY fs.id,fs.clientusersID,fp.DisplayName,sp.PublishDate,sp.id
        End

        /*For EmailInfo*/
        IF @RecordTypeCode = 2
                BEGIN
            SELECT @RecordCount = COUNT(*)
                FROM dbo.ClientUserMailForwarding cumf
                WHERE cumf.ClientUsersID IS NOT NULL
                AND cumf.UpdateDate > @DownloadStartpoint
        End

        /*For EmailRecipients*/
        IF @RecordTypeCode = 3
        BEGIN

    IF OBJECT_ID('tempdb..#ResultTable') IS NOT NULL 
        BEGIN
              drop table #ResultTable
        END        

            CREATE TABLE #ResultTable(ejrid INT, updatedate DATETIME, emailid INT, bospid INT, reid INT, userid INT)

            INSERT INTO #ResultTable(ejrid, updatedate, emailid, bospid, userid, reid)  
         SELECT  ejr.id,  
         coalesce(EJRD.DONATIONDATE,ejr.updatedate) UpdateDate,  
         ejr.emailid,  
         ejr.backofficesystempeopleid,  
         ejr.userid,  
         bosp.backofficerecordid  
         FROM dbo.emailjob_recipient ejr WITH (nolock)  
         INNER JOIN dbo.backofficesystempeople BOSP WITH (nolock) ON ejr.BackOfficeSystemPeopleID = BOSP.ID and BOSP.BackOfficeSystemID = 0  
         LEFT JOIN dbo.EMAILJOB_RECIPIENT_DONATED EJRD WITH (nolock) ON ejr.ID = EJRD.EMAILJOBRECIPIENTID  
         WHERE (BOSP.BackofficeRecordID <> 0 and ejr.UpdateDate >= @DownloadStartpoint and NOT (ejr.UpdateDate = @DownloadStartpoint and ejr.ID <= @LastID))  

        UNION

        SELECT  ejr.id,  
         coalesce(EJRD.DONATIONDATE,ejr.updatedate) UpdateDate,  
         ejr.emailid,  
         ejr.backofficesystempeopleid,  
         ejr.userid,  
         bosp.backofficerecordid  
         FROM dbo.emailjob_recipient ejr WITH (nolock)  
         INNER JOIN dbo.backofficesystempeople BOSP WITH (nolock) ON ejr.BackOfficeSystemPeopleID = BOSP.ID and BOSP.BackOfficeSystemID = 0  
         LEFT JOIN dbo.EMAILJOB_RECIPIENT_DONATED EJRD WITH (nolock) ON ejr.ID = EJRD.EMAILJOBRECIPIENTID  
         WHERE  EJRD.ID is not null  
         ORDER BY coalesce(EJRD.DONATIONDATE,ejr.updatedate) , ejr.ID  

            SELECT @RecordCount = Count(*)
            FROM #ResultTable r
            INNER JOIN dbo.EmailJob_Recipient ejr with (nolock) on ejr.id = r.ejrid
            INNER JOIN dbo.email e ON e.id = ejr.EmailID;

            DROP TABLE #ResultTable
        End

        /*For Newsletters*/
        IF @RecordTypeCode = 4
        BEGIN
        SELECT @RecordCount = COUNT(*)
            FROM EmailList_Subscription els 
            inner join BackOfficeSystemPeople bosp on els.BackofficeSystempeopleID=bosp.id and bosp.backofficeSystemid = 0 
            LEFT OUTER JOIN ( 
                SELECT els.id AS"ELSID", MAX(ejr.id) AS "EJRID" 
                    FROM emailjob_recipient ejr  
                        inner JOIN email e ON e.id = ejr.emailid 
                        inner JOIN email_emaillist eel ON eel.emailid = e.id 
                        inner JOIN emaillist el ON el.id = eel.emaillistid 
                        inner JOIN emaillist_subscription els ON els.backofficesystempeopleid = ejr.backofficesystempeopleid AND els.emaillistid = el.id 
                    GROUP BY els.id 
                ) AS subLastIssue ON subLastIssue.elsid = els.id 
            LEFT OUTER JOIN EmailJob_Recipient ejr2 ON ejr2.id = subLastIssue.ejrid 
            LEFT OUTER JOIN email e2 ON e2.id = ejr2.emailid  
            LEFT OUTER join dbo.[Email_Recipient] er on er.ID=ejr2.EmailRecipientID
            inner JOIN emaillist el2 ON el2.id = els.emaillistid 
            WHERE (e2.type IS NULL OR e2.type = 2) AND (ejr2.sent IS NULL OR ejr2.sent = 1) 
            AND (((ejr2.updatedate > @DownloadStartpoint) OR (els.UpdateDate > @DownloadStartPoint))
                OR (ejr2.updatedate IS NULL AND els.UpdateDate = @DownloadStartPoint AND els.EmailListID > @LastID)) 
        End

        /*For Stats*/
        IF @RecordTypeCode = 5
        BEGIN
        select @RecordCount =COUNT(*) 
            FROM dbo.stats s
            LEFT JOIN dbo.sitepages sp on s.pageid = sp.id
            WHERE UserID > 0 AND s.RequestDate > @DownloadStartpoint
        End

        INSERT INTO @crmIntegrationStatsTempTable(PendingRecordCount,RecordType,UpdateDate) 
        select ISNULL(@RecordCount,0) as PendingRecordCount, @RecordTypeCode  as RecordType, @DownloadStartpoint as UpdateDate
        SET @RecordCount = null

    SET @Counter = @Counter+1

    /*Reset variables for next row*/
    SET @RecordTypeCode = NULL
    SET @DownloadStartPoint = NULL
    SET @LastId = NULL
    END



    SELECT PendingRecordCount as PendingRecordCount,RecordType as RecordType,UpdateDate as UpdateDate FROM @crmIntegrationStatsTempTable            
END