/// <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);
}
}
}
