public virtual Select CreateUnsubscribedSubQuery(Guid recordId)
{
var select = new Select(UserConnection)
.Column(Column.Const(1))
.From("BulkEmailSubscription")
.Where("ContactId").IsEqual("Contact", "Id")
.And("BulkEmailSubsStatusId").IsEqual(Column.Parameter(MarketingConsts.BulkEmailContactUnsubscribed)) as Select;
if (RootSchemaName.Equals("BulkEmailSplit"))
{
select.And("BulkEmailTypeId").In(
new Select(UserConnection)
.Column("BulkEmail", "TypeId")
.From("BulkEmail")
.InnerJoin("BulkEmailType").On("BulkEmail", "TypeId").IsEqual("BulkEmailType", "Id")
.Where("BulkEmailType", "IsSignable").IsEqual(Column.Parameter(true))
.And("SplitTestId").IsEqual(Column.Parameter(recordId))
);
}
else
{
select.And("BulkEmailTypeId").In(
new Select(UserConnection)
.Column("TypeId")
.From("BulkEmail")
.Where("Id").IsEqual(Column.Parameter(recordId))
);
}
return(select);
}
public virtual bool IsBulkEmailIsSignable(Guid recordId)
{
Select checkSelect = new Select(UserConnection)
.Column("bulkEmailType", "IsSignable")
.From("BulkEmailType").As("bulkEmailType")
.InnerJoin("BulkEmail").As("bulkEmail").On("bulkEmailType", "Id")
.IsEqual("bulkEmail", "TypeId") as Select;
if (RootSchemaName.Equals("BulkEmailSplit"))
{
checkSelect.Where("bulkEmail", "SplitTestId").IsEqual(Column.Parameter(recordId));
}
else
{
checkSelect.Where("bulkEmail", "Id").IsEqual(Column.Parameter(recordId));
}
bool isSignable = false;
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader reader = checkSelect.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
bool isSignableValue = UserConnection.DBTypeConverter.DBValueToBool(reader.GetValue(0));
isSignable = isSignable || isSignableValue;
}
}
}
return(isSignable);
}
public virtual Select ProcessDynamicFolder(Guid folderId)
{
var dataValueTypeManager = (DataValueTypeManager)UserConnection.AppManagerProvider.GetManager("DataValueTypeManager");
DataValueType dateTimeDataValueType = dataValueTypeManager.GetInstanceByName("DateTime");
DataValueType guidDataValueType = dataValueTypeManager.GetInstanceByName("Guid");
DataValueType intDataValueType = dataValueTypeManager.GetInstanceByName("Integer");
EntitySchema targetSchema = UserConnection.EntitySchemaManager.GetInstanceByUId(TargetSchemaUId);
IEntitySchemaQueryFilterItem filters = CommonUtilities.GetFolderEsqFilters(
UserConnection, folderId, TargetFolderSchemaUId, TargetSchemaUId, true);
var esq = new EntitySchemaQuery(targetSchema);
if (RootSchemaName.Equals("BulkEmail") || RootSchemaName.Equals("BulkEmailSplit"))
{
esq.AddColumn(RootSchemaRecordRId, intDataValueType).SetForcedQueryColumnValueAlias("RootSchemaRecordId");
esq.AddColumn("Email");
}
else
{
esq.AddColumn(RootSchemaRecordId, guidDataValueType).SetForcedQueryColumnValueAlias("RootSchemaRecordId");;
esq.AddColumn(DefResponseId, guidDataValueType);
esq.AddColumn(true, dataValueTypeManager.GetInstanceByName("Boolean"));
esq.AddColumn(UserConnection.CurrentUser.ContactId, guidDataValueType);
esq.AddColumn(UserConnection.CurrentUser.ContactId, guidDataValueType);
esq.AddColumn("Id").SetForcedQueryColumnValueAlias("ContactRecordId");
if (IsSetCampaignFirstStep == true && RootSchemaName == "Campaign")
{
var campaignFirstStepId = GetCampaignFirstStep(RootSchemaRecordId);
esq.AddColumn(campaignFirstStepId, guidDataValueType).SetForcedQueryColumnValueAlias("CurrentStepId");
}
}
if (filters != null)
{
esq.Filters.Add(filters);
}
Select folderQuery = esq.GetSelectQuery(UserConnection);
Select resultSelect;
switch (RootSchemaName)
{
case "Event":
resultSelect = GetContactInEventDynamicFolderProcessingQuery(folderQuery);
break;
case "BulkEmailSplit":
case "BulkEmail":
resultSelect = GetContactInBulkEmailDynamicFolderProcessingQuery(folderQuery);
break;
case "Campaign":
resultSelect = GetContactInCampaignDynamicFolderProcessingQuery(folderQuery);
break;
default:
resultSelect = null;
break;
}
return(resultSelect);
}
public virtual int GetRestTargetAudienceCount()
{
Select countQuery = new Select(UserConnection)
.Column(Func.Count("Id"))
.From(TargetSchemaName);
if (TargetSchemaName.Equals("MandrillRecipient"))
{
countQuery.Where(TargetSchemaBindingColumnValueName)
.IsEqual(Column.Parameter(RootSchemaRecordRId));
}
else if (!RootSchemaName.Equals("BulkEmailSplit"))
{
countQuery.Where(TargetSchemaBindingColumnValueName)
.IsEqual(Column.Parameter(RootSchemaRecordId));
}
return((countQuery as Select).ExecuteScalar <int>());
}
public virtual void SetRootStatus(Guid StatusId, int RecipientCount, DateTime ProcessingDate)
{
var update = new Update(UserConnection, RootSchemaName)
.Set("SegmentsStatusId", Column.Parameter(StatusId))
.Set("ModifiedOn", Column.Parameter(DateTime.UtcNow))
.Set("ModifiedById", Column.Parameter(MarketingConsts.MandrillUserId));
if ((ProcessingDate != DateTime.MinValue) &&
(RootSchemaName.Equals("Event", StringComparison.CurrentCultureIgnoreCase) ||
RootSchemaName.Equals("BulkEmail", StringComparison.CurrentCultureIgnoreCase)))
{
update = update.Set("LastActualizeDate", Column.Parameter(ProcessingDate));
}
string targetCounterColumnName;
switch (RootSchemaName)
{
case "Campaign":
targetCounterColumnName = "TargetTotal";
break;
case "BulkEmailSplit":
case "Event":
case "BulkEmail":
targetCounterColumnName = "RecipientCount";
break;
default:
targetCounterColumnName = string.Empty;
break;
}
if (!string.IsNullOrEmpty(targetCounterColumnName) && RecipientCount >= 0)
{
update = update.Set(targetCounterColumnName, Column.Parameter(RecipientCount));
}
update.Where("Id").IsEqual(Column.Parameter(RootSchemaRecordId));
update.Execute();
}
