/// <summary> /// Post-processing of the clicked link/opened email records generated via subscriber link. Ensure valid emails from subscriber table. /// </summary> private static void ProcessClickedLinkAndOpenedEmailGeneratedViaSubscriberLink() { bool isSeparated = !String.IsNullOrEmpty(DatabaseSeparationHelper.ConnStringSeparate); // If the database is separated, create temporary table with subscribers' ID and email if (isSeparated) { // Get the subscribers with email from the default database var subscribersQuery = new ObjectQuery("newsletter.subscriber") .Columns("SubscriberID", "SubscriberEmail") .WhereNull("SubscriberType").Or() .WhereEquals("SubscriberType", "om.contact"); // Get the subscribers of user type from the default database var subscribersFromUsersQuery = new ObjectQuery("newsletter.subscriber") .Source(s => s.LeftJoin<UserInfo>("SubscriberRelatedID", "UserID")) .Columns(new QueryColumn("SubscriberID"), new QueryColumn("Email").As("SubscriberEmail")) .WhereEquals("SubscriberType", "cms.user") .WhereNotNull("Email"); DataSet subscribers = subscribersQuery.Union(subscribersFromUsersQuery); const string createTempTableScript = @" CREATE TABLE [Temp_Newsletter_Subscriber] ( [SubscriberEmail] [nvarchar] (400), [SubscriberID] [int] NOT NULL ) ON [PRIMARY]; "; // Create temporary table with subscribers on the separated db using (new CMSConnectionScope(DatabaseSeparationHelper.OM_CONNECTION_STRING, false, true)) { ConnectionHelper.ExecuteQuery(createTempTableScript, null, QueryTypeEnum.SQLQuery); } // Fill the table with subscribers' data using (var bulkCopy = new SqlBulkCopy(DatabaseSeparationHelper.ConnStringSeparate, SqlBulkCopyOptions.KeepIdentity)) { bulkCopy.ColumnMappings.Add("SubscriberEmail", "SubscriberEmail"); bulkCopy.ColumnMappings.Add("SubscriberID", "SubscriberID"); bulkCopy.BulkCopyTimeout = 6000; bulkCopy.DestinationTableName = "Temp_Newsletter_Subscriber"; bulkCopy.WriteToServer(subscribers.Tables[0]); } const string updateClicksScript = @" UPDATE Newsletter_ClickedLink SET ClickedLinkEmail = SubscriberEmail FROM Newsletter_ClickedLink LEFT JOIN Temp_Newsletter_Subscriber ON REPLACE(ClickedLinkEmail,'subscriberid_','') = Temp_Newsletter_Subscriber.SubscriberID WHERE ClickedLinkEmail NOT LIKE '%@%' AND SubscriberEmail IS NOT NULL; "; const string updateOpensScript = @" UPDATE Newsletter_OpenedEmail SET OpenedEmailEmail = SubscriberEmail FROM Newsletter_OpenedEmail LEFT JOIN Temp_Newsletter_Subscriber ON REPLACE(OpenedEmailEmail,'subscriberid_','') = SubscriberID WHERE OpenedEmailEmail NOT LIKE '%@%' AND SubscriberEmail IS NOT NULL; "; var existingIssueIDs = new ObjectQuery("newsletter.issue") .Column("IssueID") .GetListResult<int>(); string deleteOpensWithNonExistingIssues = string.Format(@" DELETE FROM [Newsletter_OpenedEmail] WHERE [OpenedEmailIssueID] NOT IN ({0}) ", string.Join(",", existingIssueIDs)); // Update the data in ClickedLink/OpenedEmail table using OM connection string (or default) using (new CMSConnectionScope(DatabaseSeparationHelper.OM_CONNECTION_STRING, false, true)) { ConnectionHelper.ExecuteQuery(updateClicksScript, null, QueryTypeEnum.SQLQuery); ConnectionHelper.ExecuteQuery(updateOpensScript, null, QueryTypeEnum.SQLQuery); ConnectionHelper.ExecuteQuery(deleteOpensWithNonExistingIssues, null, QueryTypeEnum.SQLQuery); } const string dropTempTableScript = @" DROP TABLE Temp_Newsletter_Subscriber; "; const string deleteUnresolvedClicksScript = @" DELETE FROM Newsletter_ClickedLink WHERE ClickedLinkEmail LIKE 'subscriberid_%' OR ClickedLinkEmail = ''; "; const string deleteUnresolvedOpensScript = @" DELETE FROM Newsletter_OpenedEmail WHERE OpenedEmailEmail LIKE 'subscriberid_%' OR OpenedEmailEmail = ''; "; const string deleteDuplicatesScript = @" WITH NOE AS( SELECT OpenedEmailID, OpenedEmailEmail, OpenedEmailIssueID, OpenedEmailTime, RN = ROW_NUMBER() OVER (PARTITION BY OpenedEmailEmail, OpenedEmailIssueID ORDER BY OpenedEmailTime ASC) FROM Newsletter_OpenedEmail ) DELETE FROM NOE WHERE RN > 1;"; // Delete the temporary table on the separated database and ClickedLinks/OpenedEmails with unresolved emails using (new CMSConnectionScope(DatabaseSeparationHelper.OM_CONNECTION_STRING, false, false)) { ConnectionHelper.ExecuteQuery(dropTempTableScript, null, QueryTypeEnum.SQLQuery); ConnectionHelper.ExecuteQuery(deleteUnresolvedClicksScript, null, QueryTypeEnum.SQLQuery); ConnectionHelper.ExecuteQuery(deleteUnresolvedOpensScript, null, QueryTypeEnum.SQLQuery); ConnectionHelper.ExecuteQuery(deleteDuplicatesScript, null, QueryTypeEnum.SQLQuery); } // Update Issues with opened email count var openedEmailsSummary = new ObjectQuery("newsletter.openedemail") .Columns(new QueryColumn("OpenedEmailIssueID"), new AggregatedColumn(AggregationType.Count, "OpenedEmailIssueID").As("Count")) .GroupBy("OpenedEmailIssueID"); const string createOpenedEmailsSummaryTempTableScript = @" CREATE TABLE [Temp_Opened_Summary] ( [OpenedEmailIssueID] [int], [Count] [int] ) ON [PRIMARY]; "; ConnectionHelper.ExecuteQuery(createOpenedEmailsSummaryTempTableScript, null, QueryTypeEnum.SQLQuery); using (var bulkCopy = new SqlBulkCopy(ConnectionHelper.ConnectionString, SqlBulkCopyOptions.KeepIdentity)) { bulkCopy.ColumnMappings.Add("OpenedEmailIssueID", "OpenedEmailIssueID"); bulkCopy.ColumnMappings.Add("Count", "Count"); bulkCopy.BulkCopyTimeout = 6000; bulkCopy.DestinationTableName = "Temp_Opened_Summary"; bulkCopy.WriteToServer(openedEmailsSummary.Tables[0]); } const string updateIssueOpenedEmailsScript = @" UPDATE Newsletter_NewsletterIssue SET IssueOpenedEmails = ( SELECT Count FROM Temp_Opened_Summary WHERE OpenedEmailIssueID = IssueID); "; ConnectionHelper.ExecuteQuery(updateIssueOpenedEmailsScript, null, QueryTypeEnum.SQLQuery); const string dropOpenedEmailsSummaryTempTableScript = @" DROP TABLE Temp_Opened_Summary; "; ConnectionHelper.ExecuteQuery(dropOpenedEmailsSummaryTempTableScript, null, QueryTypeEnum.SQLQuery); } else { const string updateClicksScript = @" UPDATE Newsletter_ClickedLink SET ClickedLinkEmail = ISNULL(SubscriberEmail, Email) FROM Newsletter_ClickedLink LEFT JOIN Newsletter_Subscriber ON REPLACE(ClickedLinkEmail,'subscriberid_','') = Newsletter_Subscriber.SubscriberID LEFT JOIN CMS_User ON (UserID = SubscriberRelatedID AND SubscriberType = 'cms.user') WHERE ClickedLinkEmail NOT LIKE '%@%' AND (SubscriberEmail IS NOT NULL OR Email IS NOT NULL); "; const string deleteUnresolvedClicksScript = @" DELETE FROM Newsletter_ClickedLink WHERE ClickedLinkEmail LIKE 'subscriberid_%' OR ClickedLinkEmail = ''; "; const string updateOpensScript = @" UPDATE Newsletter_OpenedEmail SET OpenedEmailEmail = SubscriberEmail FROM Newsletter_OpenedEmail LEFT JOIN Newsletter_Subscriber ON REPLACE(OpenedEmailEmail,'subscriberid_','') = SubscriberID WHERE OpenedEmailEmail NOT LIKE '%@%' AND SubscriberEmail IS NOT NULL; "; const string deleteUnresolvedOpensScript = @" DELETE FROM Newsletter_OpenedEmail WHERE OpenedEmailEmail LIKE 'subscriberid_%' OR OpenedEmailEmail = ''; "; const string deleteDuplicatesScript = @" WITH NOE AS( SELECT OpenedEmailID, OpenedEmailEmail, OpenedEmailIssueID, OpenedEmailTime, RN = ROW_NUMBER() OVER (PARTITION BY OpenedEmailEmail, OpenedEmailIssueID ORDER BY OpenedEmailTime ASC) FROM Newsletter_OpenedEmail ) DELETE FROM NOE WHERE RN > 1; "; const string updateIssueOpenedEmailsScript = @" UPDATE Newsletter_NewsletterIssue SET IssueOpenedEmails = ( SELECT COUNT(OpenedEmailIssueID) FROM Newsletter_OpenedEmail WHERE OpenedEmailIssueID = IssueID GROUP BY OpenedEmailIssueID); "; // Update the data in ClickedLink/OpenEmail and delete unresolved emails using (new CMSConnectionScope()) { ConnectionHelper.ExecuteQuery(updateClicksScript, null, QueryTypeEnum.SQLQuery); ConnectionHelper.ExecuteQuery(deleteUnresolvedClicksScript, null, QueryTypeEnum.SQLQuery); ConnectionHelper.ExecuteQuery(updateOpensScript, null, QueryTypeEnum.SQLQuery); ConnectionHelper.ExecuteQuery(deleteUnresolvedOpensScript, null, QueryTypeEnum.SQLQuery); ConnectionHelper.ExecuteQuery(deleteDuplicatesScript, null, QueryTypeEnum.SQLQuery); ConnectionHelper.ExecuteQuery(updateIssueOpenedEmailsScript, null, QueryTypeEnum.SQLQuery); } } }