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