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);
}
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);
}
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());
}
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());
}
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);
}
private void Bufferize()
{
Query selectQuery = GetHistorizeCollectionQuery(GetProcessLogArchivingCount(), GetArchivationStartDate());
IDBCommand insertSelect = new InsertSelect(_userConnection)
.Into(LogBuffer)
.Set("SysProcessLogId", "SessionId")
.FromSelect(selectQuery);
insertSelect.Execute(_dbExecutor);
}
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);
}
/// <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);
}
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);
}
/// <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());
}
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());
}
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);
}
/// <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;
}
}
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);
}
}
}
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);
}
public string RenderInsertSelect(InsertSelect query)
{
return(RenderInsertSelect(Qb.GetQueryObject(query)));
}