Exemplo n.º 1
0
        private InsertSelect BuildInsertSelectToResultTable(Guid sessionId)
        {
            var recipientsWithMaskSelect = new Select(SegmentationContext.UserConnection)
                                           .Column("EntityId").As("RecipientId")
                                           .Column(Func.Sum("BlockIndex")).As("Mask")
                                           .From("DCRecipientOp")
                                           .Where("SessionId").IsEqual(Column.Parameter(sessionId))
                                           .GroupBy("EntityId");
            var recipientsWithReplicasSelect = new Select(SegmentationContext.UserConnection)
                                               .Column("op", "RecipientId").As("RecipientId")
                                               .Column("dcr", "Id").As("DCReplicaId")
                                               .Column(Column.Parameter(SegmentationContext.Template.RecordId))
                                               .As(SegmentationContext.RecordIdTargetColumn)
                                               .From("DCReplica").As("dcr").InnerJoin(recipientsWithMaskSelect).As("op")
                                               .On("dcr", "Mask").IsEqual("op", "Mask")
                                               .Where("dcr", "DCTemplateId").IsEqual(Column.Parameter(SegmentationContext.Template.Id)) as Select;

            recipientsWithReplicasSelect.SpecifyNoLockHints();
            var insertSelect = new InsertSelect(SegmentationContext.UserConnection)
                               .Into(SegmentationContext.TargetTable)
                               .Set(SegmentationContext.EntityIdTargetColumn, SegmentationContext.ReplicaIdTargetColumn,
                                    SegmentationContext.RecordIdTargetColumn)
                               .FromSelect(recipientsWithReplicasSelect);

            return(insertSelect);
        }
        static string GetInsertSelectSql(AnyDbConnection cnn, InsertSelect query)
        {
            ISqlOmRenderer renderer = Qb.CreateRenderer(cnn.DatabaseProvider);
            string         sql      = renderer.RenderInsertSelect(query);

            return(sql);
        }
Exemplo n.º 3
0
        private InsertSelect BuildInsertSelect(DCTemplateBlockModel block, int groupIndex, Guid sessionId, bool isFirstSegment)
        {
            var withEntityColumnSelect = new Select(SegmentationContext.UserConnection)
                                         .Column(SegmentationContext.SourceAliasForFilter, SegmentationContext.EntityIdSourceColumn)
                                         .From(SegmentationContext.SourceAudience).As(SegmentationContext.SourceAliasForFilter);
            var extendedAudienceSelect = new Select(withEntityColumnSelect)
                                         .Column(Column.Parameter(sessionId)).As("SessionId")
                                         .Column(Column.Parameter(block.Index)).As("BlockIndex")
                                         .Column(Column.Parameter(groupIndex)).As("GroupIndex");

            if (!block.IsDefault)
            {
                var filter = GetFilterFromBlock(block);
                ExtendAudienceSelectWithFilter(ref extendedAudienceSelect, filter);
            }
            if (!isFirstSegment)
            {
                ExtendAudienceSelectWithExceptCondition(ref extendedAudienceSelect, groupIndex);
            }
            extendedAudienceSelect.SpecifyNoLockHints();
            var simpleInsert = new InsertSelect(SegmentationContext.UserConnection)
                               .Into("DCRecipientOp")
                               .Set("EntityId", "SessionId", "BlockIndex", "GroupIndex")
                               .FromSelect(extendedAudienceSelect);

            return(simpleInsert);
        }
Exemplo n.º 4
0
        public int ExecuteNonQuery(InsertSelect query)
        {
            ISqlOmRenderer render = Qb.CreateRenderer(_anyConnection.DatabaseProvider);

            _dbCommand.CommandText = render.RenderInsertSelect(query);
            _dbCommand.CommandType = CommandType.Text;
            FillParameters(_dbCommand, query.Query.CommandParams, render);
            return(ExecuteNonQuery());
        }
Exemplo n.º 5
0
        public string CopyBookshopEntry(Guid bookId)
        {
            // copying book from the section
            var newId = Guid.NewGuid();
            var query = new Select(_userConnection)
                        .Column(Column.Const(newId))
                        .Column("UsrName")
                        .Column("UsrAuthorName")
                        .Column("UsrBookRating")
                        .Column("UsrReleaseData")
                        .Column("UsrIsInStock")
                        .From("UsrBookSectionV3")
                        .Where("Id").IsEqual(Column.Parameter(bookId))
                        as Select;
            var newBookEntry = new InsertSelect(_userConnection)
                               .Into("UsrBookSectionV3")
                               .Set("Id")
                               .Set("UsrName")
                               .Set("UsrAuthorName")
                               .Set("UsrBookRating")
                               .Set("UsrReleaseData")
                               .Set("UsrIsInStock")
                               .FromSelect(query);

            newBookEntry.Execute();


            var dateNewEntry = new Select(_userConnection)
                               .Column("CreatedOn")
                               .From("UsrBookSectionV3")
                               .OrderByDesc("CreatedOn") as Select;
            var maxDate = dateNewEntry.ExecuteScalar <DateTime>();

            var selectEntryWithMaxDate = new Select(_userConnection)
                                         .Column("Id")
                                         .From("UsrBookSectionV3")
                                         .Where("CreatedOn").IsEqual(Column.Parameter(maxDate)) as Select;
            var idNewEntry = selectEntryWithMaxDate.ExecuteScalar <string>();

            var selectDetail = new Select(_userConnection)
                               .Column("UsrAlphabetLetter")
                               .Column("UsrBookCount")
                               .Column(Column.Const(newId))
                               .From("UsrBookShelf")
                               .Where("UsrLookupBookSectionId").IsEqual(Column.Parameter(bookId)) as Select;
            var insertEntry = new InsertSelect(_userConnection)
                              .Into("UsrBookShelf")
                              .Set("UsrAlphabetLetter")
                              .Set("UsrBookCount")
                              .Set("UsrLookupBookSectionId")
                              .FromSelect(selectDetail);

            insertEntry.Execute();

            return(string.Empty);
        }
        protected virtual int InsertMultiDeleteQueue(IEnumerable <Guid> recordIds)
        {
            var select = GetSelectQueryFromEntityWithCondition(recordIds);
            var insert = new InsertSelect(_userConnection)
                         .Into(MULTI_DELETE_QUEUE)
                         .Set("EntitySchemaUId", "RecordId", "SysAdminUnitId", "OperationKey", "State", "IsRoot")
                         .FromSelect(select);

            return(insert.Execute());
        }
Exemplo n.º 7
0
        private int AddAudienceToEvent(Select audienceSelect)
        {
            var audienceToAddSelect = GetAudienceToAddSelect(audienceSelect);
            var insertSelect        = new InsertSelect(UserConnection)
                                      .Into(EventTargetTableName)
                                      .Set("ContactId", "EventId", "EventResponseId")
                                      .FromSelect(audienceToAddSelect);

            return(insertSelect.Execute());
        }
        /// <summary>
        /// Creates query for instert contacts into the event audience.
        /// </summary>
        /// <param name="select">Query for select contacts.</param>
        /// <returns>The insert query.</returns>
        private InsertSelect GetInsertSelectContactsEventFromCampaign(Select select)
        {
            var insertSelect = new InsertSelect(_userConnection)
                               .Into("EventTarget")
                               .Set("EventId", "EventResponseId", "IsFromGroup", "CreatedOn", "ModifiedOn", "ModifiedById",
                                    "ContactId")
                               .FromSelect(select);

            return(insertSelect);
        }
Exemplo n.º 9
0
        private void Bufferize()
        {
            Query      selectQuery  = GetHistorizeCollectionQuery(GetProcessLogArchivingCount(), GetArchivationStartDate());
            IDBCommand insertSelect = new InsertSelect(_userConnection)
                                      .Into(LogBuffer)
                                      .Set("SysProcessLogId", "SessionId")
                                      .FromSelect(selectQuery);

            insertSelect.Execute(_dbExecutor);
        }
Exemplo n.º 10
0
        private InsertSelect GetQueryForInsertInCampaignParticipants(Query audienceQuery)
        {
            ExtendSelectColumnsForInsert(audienceQuery as Select);
            InsertSelect audienceInsert = new InsertSelect(UserConnection).Into(CampaignParticipantTable)
                                          .Set("ContactId", "CampaignId", "CampaignItemId", "StepModifiedOn",
                                               "StepCompletedOn", "StatusId", "StepCompleted")
                                          .FromSelect(audienceQuery);

            return(audienceInsert);
        }
Exemplo n.º 11
0
        /// <summary>
        /// Returns insert query for capmaign participants synchronization.
        /// </summary>
        /// <param name="userConnection">An instance of <see cref="UserConnection"/>.</param>
        /// <returns>Insert query for capmaign participants synchronization.</returns>
        protected virtual InsertSelect GetMoveParticipantsQuery(UserConnection userConnection)
        {
            var contactsToMoveSelect = GetParticipantsToMoveSelect(userConnection);
            var query = new InsertSelect(userConnection)
                        .Into(CampaignParticipantTableName)
                        .Set("CreatedOn", "CreatedById", "CampaignId", "ContactId", "StatusId", "CampaignItemId",
                             "StepModifiedOn", "StepCompletedOn", "StepCompleted")
                        .FromSelect(contactsToMoveSelect);

            return(query);
        }
Exemplo n.º 12
0
        private void AddNewRemindings(Select select)
        {
            var insertSelect =
                new InsertSelect(UserConnection)
                .Into("Reminding")
                .Set("ContactId", "SubjectId", "SysEntitySchemaId", "SubjectCaption", "RemindTime",
                     "NotificationTypeId", "ProcessListeners", "SenderId", "SourceId")
                .FromSelect(select);

            ExecuteDbQuery(insertSelect);
        }
        private InsertSelect GetInsertToQuery(Select selectQuery)
        {
            var insertSelect = new InsertSelect(UserConnection)
                               .Into(HistorySchemaName)
                               .Set(ColumnNames)
                               .FromSelect(selectQuery);

            insertSelect.InitializeParameters();
            insertSelect.BuildParametersAsValue = true;
            return(insertSelect);
        }
Exemplo n.º 14
0
        /// <summary>
        /// Add audience by filter.
        /// </summary>
        /// <param name="audienceSelect">Query for inserting audience.</param>
        /// <param name="bulkEmailId">Unique identifier of the campaign step.</param>
        /// <param name="sessionUId">Unique identifier of the session.</param>
        /// <returns>Number of contacts added.</returns>
        public virtual int Add(Select audienceSelect, Guid bulkEmailId, Guid sessionUId)
        {
            var workSelect = ApplyBulkEmailInfoToAudienceSelect(audienceSelect, bulkEmailId, sessionUId);

            workSelect = ApplyBulkEmailUnsubscribedRestrictionSelect(workSelect, bulkEmailId);
            var insertSelect = new InsertSelect(_userConnection)
                               .Into("MandrillRecipient")
                               .Set("ContactRId", "EmailAddress", "BulkEmailRId", "SessionUId", "IsSent", "Timestamp")
                               .FromSelect(workSelect);

            return(insertSelect.Execute());
        }
        public static async Task <int> ExecuteAsync(this AnyDbConnection cnn, InsertSelect query, IDbTransaction transaction = null, int?commandTimeout = default(int?))
        {
            string insertSelectSql = GetInsertSelectSql(cnn, query);

            if (!commandTimeout.HasValue)
            {
                commandTimeout = cnn.DefaultCommandTimeout;
            }
            DynamicParameters parameters = GetParameters(query.Query.CommandParams);

            return(await SqlMapper.ExecuteAsync((IDbConnection)cnn, insertSelectSql, (object)parameters, transaction, commandTimeout, (CommandType?)CommandType.Text));
        }
        public static int Execute(this AnyDbConnection cnn, InsertSelect query, IDbTransaction transaction = null,
                                  int?commandTimeout = null)
        {
            string sql = GetInsertSelectSql(cnn, query);

            if (!commandTimeout.HasValue)
            {
                commandTimeout = cnn.DefaultCommandTimeout;
            }
            DynamicParameters parameters = GetParameters(query.Query.CommandParams);

            return(cnn.Execute(sql, parameters, transaction, commandTimeout, CommandType.Text));
        }
        protected SqlString GenerateIdInsertSelect(IQueryable persister, string tableAlias, IASTNode whereClause)
        {
            var            select         = new SqlSelectBuilder(Factory);
            SelectFragment selectFragment = new SelectFragment(Factory.Dialect)
                                            .AddColumns(tableAlias, persister.IdentifierColumnNames, persister.IdentifierColumnNames);

            select.SetSelectClause(selectFragment.ToFragmentString().Substring(2));

            string    rootTableName    = persister.TableName;
            SqlString fromJoinFragment = persister.FromJoinFragment(tableAlias, true, false);

            select.SetFromClause(rootTableName + " " + tableAlias + fromJoinFragment);

            var whereJoinFragment = GetWhereJoinFragment(persister, tableAlias);

            SqlString userWhereClause = SqlString.Empty;

            if (whereClause.ChildCount != 0)
            {
                // If a where clause was specified in the update/delete query, use it to limit the
                // returned ids here...
                try
                {
                    var nodes = new CommonTreeNodeStream(whereClause);
                    var gen   = new SqlGenerator(Factory, nodes);
                    gen.whereClause();
                    userWhereClause = gen.GetSQL().Substring(7);
                }
                catch (RecognitionException e)
                {
                    throw new HibernateException("Unable to generate id select for DML operation", e);
                }
                if (whereJoinFragment.Length > 0)
                {
                    whereJoinFragment = whereJoinFragment.Append(" and ");
                }
            }

            select.SetWhereClause(whereJoinFragment + userWhereClause);

            var insert = new InsertSelect();

            if (Factory.Settings.IsCommentsEnabled)
            {
                insert.SetComment("insert-select for " + persister.EntityName + " ids");
            }
            insert.SetTableName(persister.TemporaryIdTableName);
            insert.SetSelect(select);
            return(insert.ToSqlString());
        }
Exemplo n.º 18
0
        public void DoCopy(Guid bookId)
        {
            #region Запрашиваем данные книги, затем добавляем новую книгу
            var query = new Select(_userConnection)
                        .Column("NWName")
                        .Column("NWNotes")
                        .Column("NWDateOfPublication")
                        .Column("NWAuthorId")
                        .Column("NWisSale")
                        .Column("NWTirage")
                        .From("NWLibrary")
                        .Where("Id")
                        .IsEqual(Column.Parameter(bookId)) as Select;

            var insel = new InsertSelect(_userConnection)
                        .Into("NWLibrary")
                        .Set("NWName")
                        .Set("NWNotes")
                        .Set("NWDateOfPublication")
                        .Set("NWAuthorId")
                        .Set("NWisSale")
                        .Set("NWTirage")
                        .FromSelect(query);
            insel.Execute();
            var bookName = query.ExecuteScalar <string>();

            //Запрашиваем ID новой книги
            query = new Select(_userConnection)
                    .Column("Id")
                    .From("NWLibrary")
                    .Where("Id")
                    .IsNotEqual(Column.Parameter(bookId))
                    .And("NWName").IsEqual(Column.Const(bookName)) as Select;
            var idNewBook = query.ExecuteScalar <string>();

            //Данные детальки
            var query2 = new Select(_userConnection)
                         .Column(Column.Const(idNewBook))
                         .Column("NWAuthorToBookId")
                         .From("NWAuthorAndBook")
                         .Where("NWBookToAuthorId")
                         .IsEqual(Column.Parameter(bookId)) as Select;

            var insel1 = new InsertSelect(_userConnection)
                         .Into("NWAuthorAndBook")
                         .Set("NWBookToAuthorId", "NWAuthorToBookId")
                         .FromSelect(query2);
            insel1.Execute();
        }
 /// <summary>
 /// Adds elements from queue with filtration.
 /// </summary>
 /// <param name="entitySchemaName">Entity schema name of queue element.</param>
 /// <param name="entitySelect">Query to queue object with filtration.</param>
 /// <param name="queueId">Identifier of queue.</param>
 /// <param name="queueName">Queue name.</param>
 /// <returns></returns>
 private int AddEntityQueueItemsByFilter(string entitySchemaName, Select entitySelect, Guid queueId,
                                         string queueName)
 {
     try {
         entitySelect.Top(_insertQueueItemTopValue)
         .Column(new QueryParameter("QueueId", queueId)).As("QueueId")
         .Column(new QueryParameter("StatusId", _newQueueItemStatusId)).As("StatusId");
         var selectCondition = new QueryCondition();
         selectCondition.IsNot = true;
         selectCondition.Exists(
             new Select(_userConnection)
             .Column("QueueItem", "Id")
             .From("QueueItem")
             .InnerJoin("QueueItemStatus")
             .On("QueueItemStatus", "Id").IsEqual("QueueItem", "StatusId")
             .Where(entitySchemaName, "Id").IsEqual("QueueItem", "EntityRecordId")
             .And("QueueItem", "QueueId").IsEqual(Column.Parameter(queueId))
             .And("QueueItemStatus", "IsFinal").IsNotEqual(Column.Parameter(true)));
         if (!entitySelect.HasCondition)
         {
             entitySelect.Where(selectCondition);
         }
         else
         {
             entitySelect.And(selectCondition);
         }
         var insertSelect = new InsertSelect(_userConnection)
                            .Into("QueueItem")
                            .Set("EntityRecordId", "QueueId", "StatusId")
                            .FromSelect(entitySelect);
         DateTime addedRecordsStartTime = DateTime.Now;
         int      addedRecords          = insertSelect.Execute();
         if (addedRecordsStartTime.AddSeconds(60) < DateTime.Now)
         {
             insertSelect.BuildParametersAsValue = true;
             QueuesUtilities.LogDebug(string.Format(GetResourceValue("LongInsertQueueItemsMessage"),
                                                    queueName, _autoUpdateQueuesProcessId.ToString(), insertSelect.GetSqlText()));
         }
         return(addedRecords);
     } catch (Exception e) {
         QueuesUtilities.LogError(string.Format(GetResourceValue("InvokeMethodErrorMessage"),
                                                string.Concat("AddEntityQueueItemsByFilter ", queueName), e.Message), e);
         throw;
     }
 }
        public static int CopyEntityAdministrateByRecordsRights(UserConnection userConnection, CopyEntityRightsParams copyParams)
        {
            var sourceSchema = userConnection.EntitySchemaManager.GetInstanceByName(copyParams.SourceSchemaName);
            var targetSchema = userConnection.EntitySchemaManager.GetInstanceByName(copyParams.TargetSchemaName);

            if (!sourceSchema.AdministratedByRecords || !targetSchema.AdministratedByRecords)
            {
                return(0);
            }
            var sourceRightTableName = string.Format(SysRightTablePattern, copyParams.SourceSchemaName);
            var targetRightTableName = string.Format(SysRightTablePattern, copyParams.TargetSchemaName);

            var targetRightsSelect = new Select(userConnection)
                                     .Column("Id")
                                     .Column("SysAdminUnitId")
                                     .Column("Operation")
                                     .From(targetRightTableName)
                                     .Where("RecordId").IsEqual(Column.Const(copyParams.TargetEntityId));

            var sourceRightsSelect = new Select(userConnection)
                                     .Column(Column.Const(DateTime.UtcNow)).As("CreatedOn")
                                     .Column(Column.Const(userConnection.CurrentUser.ContactId)).As("CreatedById")
                                     .Column(Column.Const(DateTime.UtcNow)).As("ModifiedOn")
                                     .Column(Column.Const(userConnection.CurrentUser.ContactId)).As("ModifiedById")
                                     .Column(Column.Const(copyParams.TargetEntityId)).As("RecordId")
                                     .Column("srt", "SysAdminUnitId")
                                     .Column("srt", "Operation")
                                     .Column("srt", "RightLevel")
                                     .Column("srt", "Position")
                                     .Column(Column.Const(ManualRightSourceId)).As("SourceId")
                                     .From(sourceRightTableName).As("srt")
                                     .LeftOuterJoin(targetRightsSelect).As("trt")
                                     .On("srt", "SysAdminUnitId").IsEqual("trt", "SysAdminUnitId")
                                     .And("srt", "Operation").IsEqual("trt", "Operation")
                                     .Where("srt", "RecordId").IsEqual(Column.Const(copyParams.SourceEntityId))
                                     .And("trt", "Id").IsNull();

            var insertTargetRights = new InsertSelect(userConnection)
                                     .Into(targetRightTableName)
                                     .Set("CreatedOn", "CreatedById", "ModifiedOn", "ModifiedById", "RecordId", "SysAdminUnitId", "Operation", "RightLevel", "Position", "SourceId")
                                     .FromSelect(sourceRightsSelect);

            return(insertTargetRights.Execute());
        }
Exemplo n.º 21
0
        public string CopyRec(Guid pieRecId)
        {
            var id     = Guid.NewGuid();
            var select = new Select(_userConnection)
                         .Column(Column.Const(id))
                         .Column("UsrName")
                         .Column("UsrPieS1Id")
                         .Column("UsrDateS")
                         .Column("UsrWt")
                         .Column("UsrCountPie")
                         .Column("UsrContOrderId")
                         .Column("UsrWish")
                         .Column("UsrClientCart")
                         .Column("UsrNaturJe")
                         .From("UsrPie")
                         .Where("Id").IsEqual(Column.Parameter(pieRecId)) as Select;
            var insel = new InsertSelect(_userConnection)
                        .Into("UsrPie")
                        .Set("Id", "UsrName", "UsrPieS1Id", "UsrDateS", "UsrWt", "UsrCountPie", "UsrContOrderId", "UsrWish", "UsrClientCart", "UsrNaturJe")
                        .FromSelect(select);

            insel.Execute();

            var selectDetail = new Select(_userConnection)
                               .Column("UsrVeusFilling")
                               .Column("UsrNLayers")
                               .Column("UsrColorLayer")
                               .Column(Column.Const(id))
                               .From("UsrFillingPie")
                               .Where("UsrOrderVeusId").IsEqual(Column.Parameter(pieRecId)) as Select;
            var inselDetail = new InsertSelect(_userConnection)
                              .Into("UsrFillingPie")
                              .Set("UsrVeusFilling", "UsrNLayers", "UsrColorLayer", "UsrOrderVeusId")
                              .FromSelect(selectDetail);

            inselDetail.Execute();

            return(string.Empty);
        }
        public string GetName(Guid RecordId)
        {
            var id    = Guid.NewGuid();
            var query = new Select(_userConnection)
                        .Column(Column.Const(id))
                        .Column("UsrName")
                        .Column("UsrNotes")
                        .Column("UsrLookup1Id")
                        .From("UsrSale")
                        .Where("Id")
                        .IsEqual(Column.Parameter(RecordId)) as Select;

            var insert = new InsertSelect(_userConnection)
                         .Into("UsrSale")
                         .Set("Id", "UsrName", "UsrNotes", "UsrLookup1Id")
                         .FromSelect(query);

            insert.Execute();

            //Данные детали
            var queryDetail = new Select(_userConnection)
                              .Column(Column.Const(id))
                              .Column("UsrCirculation")
                              .Column("UsrReprinting")
                              .From("UsrGenre")
                              .Where("UsrOrderId")
                              .IsEqual(Column.Parameter(RecordId)) as Select;

            var insert1 = new InsertSelect(_userConnection)
                          .Into("UsrGenre")
                          .Set("UsrOrderId", "UsrCirculation", "UsrReprinting")
                          .FromSelect(queryDetail);

            insert1.Execute();

            return(string.Empty);
        }
Exemplo n.º 23
0
        /// <summary>
        /// Tries to parse a script node.
        /// </summary>
        /// <param name="Parser">Custom parser.</param>
        /// <param name="Result">Parsed Script Node.</param>
        /// <returns>If successful in parsing a script node.</returns>
        public bool TryParse(ScriptParser Parser, out ScriptNode Result)
        {
            Result = null;

            try
            {
                string s = Parser.NextToken().ToUpper();
                if (s != "INTO")
                {
                    return(false);
                }

                if (!SelectParser.TryParseSources(Parser, out SourceDefinition Source))
                {
                    return(false);
                }

                switch (Parser.PeekNextToken().ToUpper())
                {
                case "(":
                    Parser.NextToken();

                    ScriptNode Node = Parser.ParseList();
                    if (!(Node is ElementList Columns))
                    {
                        Columns = new ElementList(new ScriptNode[] { Node }, Node.Start, Node.Length, Node.Expression);
                    }

                    if (Parser.NextToken() != ")")
                    {
                        return(false);
                    }

                    if (Parser.NextToken().ToUpper() != "VALUES")
                    {
                        return(false);
                    }

                    if (Parser.NextToken() != "(")
                    {
                        return(false);
                    }

                    Node = Parser.ParseList();
                    if (!(Node is ElementList Values))
                    {
                        Values = new ElementList(new ScriptNode[] { Node }, Node.Start, Node.Length, Node.Expression);
                    }

                    if (Values.Elements.Length != Columns.Elements.Length)
                    {
                        return(false);
                    }

                    if (Parser.NextToken() != ")")
                    {
                        return(false);
                    }

                    Result = new InsertValues(Source, Columns, Values, Parser.Start, Parser.Length, Parser.Expression);
                    return(true);

                case "SELECT":
                    Node = Parser.ParseStatement();
                    if (!(Node is Select Select))
                    {
                        return(false);
                    }

                    Result = new InsertSelect(Source, Select, Parser.Start, Parser.Position, Parser.Expression);
                    return(true);

                case "OBJECT":
                case "OBJECTS":
                    Parser.NextToken();

                    Node = Parser.ParseList();
                    if (!(Node is ElementList Objects))
                    {
                        Objects = new ElementList(new ScriptNode[] { Node }, Node.Start, Node.Length, Node.Expression);
                    }

                    Result = new InsertObjects(Source, Objects, Parser.Start, Parser.Length, Parser.Expression);
                    return(true);

                default:
                    return(false);
                }
            }
            catch (Exception)
            {
                return(false);
            }
        }
 /// <summary>
 /// ######### ######## ####### ## ###### #######.
 /// </summary>
 /// <param name="userConnection">################ ###########.</param>
 /// <param name="folderEntity">###### #######.</param>
 /// <param name="entitySchemaName">######## ##### ####### #######.</param>
 /// <param name="queueId">############# #######.</param>
 /// <returns></returns>
 private int AddFolderEntityQueueItems(UserConnection userConnection, Entity folderEntity,
                                       string entitySchemaName, Guid queueId)
 {
     try {
         EntitySchemaManager entitySchemaManager = userConnection.EntitySchemaManager;
         var esq = new EntitySchemaQuery(entitySchemaManager, entitySchemaName);
         esq.PrimaryQueryColumn.IsVisible = true;
         Guid folderTypeId = folderEntity.GetTypedColumnValue <Guid>("FolderTypeId");
         Guid folderId     = folderEntity.PrimaryColumnValue;
         if (folderTypeId == SearchFolderTypeId)
         {
             byte[] searchData       = folderEntity.GetBytesValue("SearchData");
             string serializedFilter = Encoding.UTF8.GetString(searchData, 0, searchData.Length);
             if (serializedFilter.IsNullOrEmpty())
             {
                 return(0);
             }
             // TODO #CC-678 ########### # ######### ############## ######## ServiceStackTextHelper.
             Filters filters = Json.Deserialize <Filters>(serializedFilter);
             IEntitySchemaQueryFilterItem esqFilters = filters.BuildEsqFilter(entitySchemaName, userConnection);
             var queryFilterCollection = esqFilters as EntitySchemaQueryFilterCollection;
             if (queryFilterCollection.Count == 0)
             {
                 return(0);
             }
             if (queryFilterCollection != null)
             {
                 esq.Filters.LogicalOperation = queryFilterCollection.LogicalOperation;
                 esq.Filters.IsNot            = queryFilterCollection.IsNot;
                 foreach (IEntitySchemaQueryFilterItem filter in queryFilterCollection)
                 {
                     esq.Filters.Add(filter);
                 }
             }
             else
             {
                 esq.Filters.Add(esqFilters);
             }
         }
         else if (folderTypeId == GeneralFolderTypeId)
         {
             esq.Filters.Add(esq.CreateFilterWithParameters(FilterComparisonType.Equal,
                                                            string.Format("[{0}InFolder:{0}:Id].Folder", entitySchemaName), folderId));
         }
         Select select = esq.GetSelectQuery(userConnection);
         select = select
                  .Column(new QueryParameter("QueueId", queueId))
                  .Column(new QueryParameter("StatusId", NewQueueItemStatusId))
                  .And().Not().Exists(new Select(userConnection)
                                      .Column("Id")
                                      .From("QueueItem")
                                      .Where(entitySchemaName, "Id").IsEqual("QueueItem", "EntityRecordId")
                                      .And("QueueItem", "QueueId").IsEqual(Column.Parameter(queueId))) as Select;
         var insertSelect = new InsertSelect(userConnection)
                            .Into("QueueItem")
                            .Set("EntityRecordId", "QueueId", "StatusId")
                            .FromSelect(select);
         return(insertSelect.Execute());
     } catch (Exception e) {
         QueuesUtilities.LogError(string.Format(GetResourceValue(userConnection, "InvokeMethodErrorMessage"),
                                                "AddFolderEntityQueueItems", e.Message), e);
         throw;
     }
 }
Exemplo n.º 25
0
 protected void InsertRecipientsIntoBulkEmails(int newRecipientsCount, int recipientsPerEmail,
                                               int initialLowBound, int targetsCount, List <int> targets, string transferTableName)
 {
     using (var dbExecutor = UserConnection.EnsureDBConnection()) {
         dbExecutor.CommandTimeout = 1800;
         int maxRowsInQuery = 100000;
         int batchesCount   = recipientsPerEmail / maxRowsInQuery;
         if (recipientsPerEmail % maxRowsInQuery > 0)
         {
             batchesCount++;
         }
         for (int i = 0; i < targetsCount; i++)
         {
             int bulkEmailRId  = targets[i];
             int audienceCount = 0;
             //Batch counter
             int lowBound  = i * recipientsPerEmail;
             int highBound = lowBound;
             for (int j = 0; j < batchesCount; j++)
             {
                 if (recipientsPerEmail < (maxRowsInQuery * (j + 1)))
                 {
                     highBound += recipientsPerEmail - (maxRowsInQuery * j);
                 }
                 else
                 {
                     highBound += maxRowsInQuery;
                 }
                 //Build insertSelect
                 var innerSelect = new Select(UserConnection)
                                   .Top(recipientsPerEmail)
                                   .Column("BulkEmailRId")
                                   .Column("EmailAddress")
                                   .Column("ContactRId")
                                   .From(new Select(UserConnection)
                                         .Column(Column.Const(bulkEmailRId)).As("BulkEmailRId")
                                         .Column("EmailAddress")
                                         .Column("ContactRId")
                                         .Column("Id")
                                         .Column(Column.SqlText("(ROW_NUMBER() OVER (ORDER BY [Order]))")).As("RowNum")
                                         .From(transferTableName)
                                         .Where("Id").IsGreater(Column.Const(initialLowBound))
                                         ).As("middleSelect")
                                   .Where("RowNum").IsBetween(Column.Const(lowBound + 1))
                                   .And(Column.Const(highBound));
                 InsertSelect insertSelect = new InsertSelect(UserConnection)
                                             .Into("MandrillRecipient")
                                             .Set("BulkEmailRId", "EmailAddress", "ContactRId")
                                             .FromSelect(innerSelect);
                 //Do it
                 lowBound       = highBound;
                 audienceCount += insertSelect.Execute(dbExecutor);
             }
             //Update BulkEmail Recipient Counter
             var update = new Update(UserConnection, "BulkEmail")
                          .Set("ModifiedOn", Column.Parameter(DateTime.UtcNow))
                          .Set("RecipientCount", QueryColumnExpression.Add(
                                   new QueryColumnExpression("RecipientCount"),
                                   Column.Parameter(audienceCount)))
                          .Set("SegmentsStatusId", Column.Parameter(MarketingConsts.SegmentStatusUpdatedId))
                          .Where("RId").IsEqual(Column.Parameter(bulkEmailRId))
                          .Execute(dbExecutor);
         }
     }
 }
Exemplo n.º 26
0
        public string GetSql(InsertSelect query)
        {
            ISqlOmRenderer renderer = Qb.CreateRenderer(AnyDbSetting.DatabaseProvider);

            return(renderer.RenderInsertSelect(query));
        }
        public virtual InsertSelect GetInsertSelect(Select select)
        {
            InsertSelect insertSelect;

            switch (RootSchemaName)
            {
            case "Event":
                var eventTargetExistsSelect = new Select(UserConnection)
                                              .From("EventTarget").Column("Id")
                                              .Where("ContactId").IsEqual("ResultSelect", "ContactRecordId")
                                              .And("EventId").IsEqual("ResultSelect", "RootSchemaRecordId") as Select;
                eventTargetExistsSelect.SpecifyNoLockHints(true);
                insertSelect = new InsertSelect(UserConnection)
                               .Into("EventTarget")
                               .Set("EventId", "EventResponseId", "IsFromGroup", "CreatedById",
                                    "ModifiedById", "ContactId")
                               .FromSelect(select.Where().Not().Exists(
                                               eventTargetExistsSelect
                                               ));
                break;

            case "BulkEmailSplit":
            case "BulkEmail":
                var mandrillRecipientExistsSelect = new Select(UserConnection)
                                                    .From(TargetSchemaName).Column("Id")
                                                    .Where("ContactRId").IsEqual("ResultSelect", "ContactRecordId")
                                                    .And(TargetSchemaBindingColumnValueName).IsEqual("ResultSelect", "RootSchemaRecordId") as Select;
                mandrillRecipientExistsSelect.SpecifyNoLockHints(true);
                insertSelect = new InsertSelect(UserConnection)
                               .Into(TargetSchemaName)
                               .Set(TargetSchemaBindingColumnValueName, "EmailAddress", "ContactRId")
                               .FromSelect(select.Where().Not().Exists(
                                               mandrillRecipientExistsSelect
                                               ));
                break;

            case "Campaign":
                var campaignTargetExistsSelect = new Select(UserConnection)
                                                 .From("CampaignTarget").Column("Id")
                                                 .Where("ContactId").IsEqual("ResultSelect", "ContactRecordId")
                                                 .And("CampaignId").IsEqual("ResultSelect", "RootSchemaRecordId") as Select;
                campaignTargetExistsSelect.SpecifyNoLockHints(true);
                insertSelect = new InsertSelect(UserConnection)
                               .Into("CampaignTarget");
                if (IsSetCampaignFirstStep == false)
                {
                    insertSelect.Set("CampaignId", "CampaignTargetStatusId", "IsFromGroup",
                                     "ModifiedById", "ContactId");
                }
                else
                {
                    insertSelect.Set("CampaignId", "CampaignTargetStatusId", "IsFromGroup",
                                     "ModifiedById", "ContactId", "CurrentStepId");
                }
                insertSelect.FromSelect(select.Where().Not().Exists(
                                            campaignTargetExistsSelect
                                            ));
                break;

            default:
                insertSelect = null;
                break;
            }
            return(insertSelect);
        }
Exemplo n.º 28
0
 public string RenderInsertSelect(InsertSelect query)
 {
     return(RenderInsertSelect(Qb.GetQueryObject(query)));
 }