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