public bool CanRun(QueryParts queryParts)
{
return(queryParts.IsRollback);
}
protected override void GenerateReport()
{
ProfileHelper.Next("Начало формирования запроса");
_clients = GetClientWithSetFilter(_RegionEqual, _RegionNonEqual,
_PayerEqual, _PayerNonEqual, _Clients, _ClientsNON, null);
var hash = new Hashtable();
var data = new List <ReportData>();
var clientsCount = _clients.Count;
var checkSuppliersCount = _reportParams.ContainsKey("FirmCodeEqual");
var maxSuppliersCount = 0;
foreach (var client in _clients)
{
// проверка клиента на доступность
var cl = GetClientWithSetFilter(_RegionEqual, _RegionNonEqual, _PayerEqual, _PayerNonEqual, _Clients, _ClientsNON, client);
if (cl == null || cl.Count == 0)
{
clientsCount--;
continue; // возможно, клиент был заблокирован во время подготовки отчета
}
ClientCode = Convert.ToInt32(client);
InvokeGetActivePrices();
if (checkSuppliersCount)
{
maxSuppliersCount = Math.Max(maxSuppliersCount,
Connection.Read <uint>("select count(*) from usersettings.ActivePrices group by FirmCode").Count());
}
var joinText = _AllAssortment ? "Left JOIN" : "JOIN";
string withWithoutPropertiesText;
if (_WithWithoutProperties)
{
withWithoutPropertiesText = String.Format(@" if(C0.SynonymCode is not null, S.Synonym, {0}) ", GetCatalogProductNameSubquery("p.id"));
}
else
{
withWithoutPropertiesText = String.Format(@" if(C0.SynonymCode is not null, S.Synonym, {0}) ", QueryParts.GetFullFormSubquery("p.id", true));
}
var firmcr = _ProducerAccount ? "and ifnull(C0.CodeFirmCr,0) = ifnull(c00.CodeFirmCr,0)" : string.Empty;
var JunkWhere = _regionsWhere.Length == 0 ? " WHERE c00.Junk = 0 " : " AND c00.Junk = 0 ";
DataAdapter.SelectCommand.CommandText =
string.Format(
@"
select c00.ProductId, p.CatalogId, c00.CodeFirmCr, c0.Code, c0.CodeCr,
{2} as ProductName,
if(c0.SynonymFirmCrCode is not null, Sf.Synonym , Prod.Name) as ProdName,
if(if(round(cc.Cost * Prices.Upcost, 2) < c00.MinBoundCost, c00.MinBoundCost, round(cc.Cost * Prices.Upcost, 2)) > c00.MaxBoundCost,
c00.MaxBoundCost, if(round(cc.Cost*Prices.UpCost,2) < c00.MinBoundCost, c00.MinBoundCost, round(cc.Cost * Prices.Upcost, 2))) as Cost,
Prices.FirmCode, Prices.PriceCode
from Usersettings.ActivePrices Prices
join farm.core0 c00 on c00.PriceCode = Prices.PriceCode
join farm.CoreCosts cc on cc.Core_Id = c00.Id and cc.PC_CostCode = Prices.CostCode
{1} farm.Core0 c0 on c0.productid = c00.productid {5} and C0.PriceCode = {0}
join catalogs.Products as p on p.id = c00.productid
join Catalogs.Catalog as cg on p.catalogid = cg.id
left join Catalogs.Producers Prod on c00.CodeFirmCr = Prod.Id
left join farm.Synonym S on C0.SynonymCode = S.SynonymCode
left join farm.SynonymFirmCr Sf on C0.SynonymFirmCrCode = Sf.SynonymFirmCrCode
{3}
{4}
", priceForCorel, joinText, withWithoutPropertiesText, _regionsWhere, JunkWhere, firmcr);
#if DEBUG
Debug.WriteLine(DataAdapter.SelectCommand.CommandText);
#endif
var offers = new DataTable();
DataAdapter.Fill(offers);
NoisingCostInDataTable(offers, "Cost", "FirmCode", _SupplierNoise);
foreach (var group in Group(offers))
{
var offer = group.First();
var dataItem = FindItem(hash, offer, data);
dataItem.Drugstore.AddRange(group.Select(r => r.Field <UInt32>("FirmCode")).Where(u => !dataItem.Drugstore.Contains(u)));
dataItem.Costs.Add(group.Min(r => r.Field <decimal>("Cost")));
}
#if DEBUG
Console.WriteLine("Код клиента: " + ClientCode + " Строк в таблице: " + data.Count);
#endif
} //foreach (var client in _clients)
if (checkSuppliersCount && maxSuppliersCount < 3)
{
throw new ReportException(String.Format("Фактическое количество прайс" +
" листов меньше трех, получено прайс-листов {0}", maxSuppliersCount));
}
ProfileHelper.SpendedTime(string.Format("По {0}ти клиентам запрос выполнен за ", clientsCount));
var dtRes = new DataTable("Results");
dtRes.Columns.Add("Code");
if (_showCodeCr)
{
dtRes.Columns.Add("CodeCr");
dtRes.Columns["CodeCr"].Caption = "Код изготовителя";
dtRes.Columns["CodeCr"].ExtendedProperties["Width"] = 10;
}
dtRes.Columns.Add("ProductName");
dtRes.Columns["ProductName"].Caption = "Наименование";
dtRes.Columns["ProductName"].ExtendedProperties["Width"] = 65;
if (_ProducerAccount)
{
dtRes.Columns.Add("CodeFirmCr");
dtRes.Columns["CodeFirmCr"].Caption = "Производитель";
dtRes.Columns["CodeFirmCr"].ExtendedProperties["Width"] = 25;
}
dtRes.Columns.Add("MinCost", typeof(decimal));
dtRes.Columns["Code"].Caption = "Код товара";
dtRes.Columns["MinCost"].Caption = "Минимальная цена";
var costNumber = new List <int>();
for (double i = 0.01; i < 0.7; i += 0.1)
{
var okrugl = Math.Round((i * clientsCount) + 1);
if (okrugl > 1)
{
if (!costNumber.Contains((int)okrugl))
{
costNumber.Add((int)okrugl);
dtRes.Columns.Add("Cost" + okrugl, typeof(decimal));
dtRes.Columns["Cost" + okrugl].Caption = (100 - i * 100) + "% (" + okrugl + "я цена)";
}
}
if (i == 0.01)
{
i -= 0.01;
}
}
dtRes.Columns.Add("SupplierCount", typeof(int));
dtRes.Columns.Add("DrugstoreCount", typeof(int));
dtRes.Columns["SupplierCount"].Caption = "Количество поставщиков";
dtRes.Columns["SupplierCount"].ExtendedProperties["Width"] = 10;
dtRes.Columns["DrugstoreCount"].Caption = "Количество аптек";
dtRes.Columns["DrugstoreCount"].ExtendedProperties["Width"] = 10;
if (_ProducerAccount)
{
data = data.OrderBy(i => i.Name).ThenBy(i => ((ProducerAwareReportData)i).ProducerName).ToList();
}
else
{
data = data.OrderBy(i => i.Name).ToList();
}
foreach (var dataItem in data)
{
var newRow = dtRes.NewRow();
if (_ProducerAccount)
{
newRow["CodeFirmCr"] = ((ProducerAwareReportData)dataItem).ProducerName;
}
if (_showCodeCr)
{
newRow["CodeCr"] = dataItem.CodeCr;
}
newRow["Code"] = dataItem.Code;
newRow["MinCost"] = dataItem.Costs.Min();
newRow["ProductName"] = dataItem.Name;
newRow["DrugstoreCount"] = dataItem.Costs.Count;
newRow["SupplierCount"] = dataItem.Drugstore.Count;
dataItem.Costs.Sort();
foreach (var i in costNumber)
{
if (dataItem.Costs.Count < i)
{
break;
}
newRow["Cost" + i] = dataItem.Costs[i - 1];
}
dtRes.Rows.Add(newRow);
}
_dsReport.Tables.Add(dtRes);
}
public bool CanRun(QueryParts queryParts)
{
//this should only work on queries like: DELETE * FROM Collection WHERE Collection.PartitionKey = 'test'
return(queryParts.CleanQueryType.Equals(Constants.QueryParsingKeywords.DELETE, StringComparison.InvariantCultureIgnoreCase) &&
queryParts.CleanQueryBody.Equals("*") && !string.IsNullOrEmpty(queryParts.CleanQueryWhere));
}
public override Task RenderResults(IReadOnlyCollection <object> results, string collectionName, QueryParts query, bool appendResults, int queryStatementIndex)
{
_view.RenderResults(results, collectionName, query, appendResults, queryStatementIndex);
return(Task.CompletedTask);
}
/// <inheritdoc/>
public Maybe <QueryParts> AddWherePostId(QueryParts parts, WpPostId?postId, IImmutableList <WpPostId> postIds)
{
// Add Post ID EQUAL
if (postId is WpPostId id and {
Value : > 0
})
private SqlGeneratorExpressionTreeVisitor(QueryParts queryParts, string contextName, QueryContext context)
{
this.QueryParts = queryParts;
this.ContextName = contextName;
this.Context = context;
}
public async void RenderResults(IReadOnlyCollection <object> results, string collectionName, QueryParts query, bool appendResults, int queryStatementIndex)
{
if (!appendResults)
{
resultListView.Groups.Clear();
resultListView.Items.Clear();
_totalDocumentCount = 0;
}
var textPartitionKeyPath = await Presenter.LookupPartitionKeyPath(query.CollectionName);
var groupName = "Query 1";
var headers = Presenter.LookupResultListViewHeaders(results.FirstOrDefault(), textPartitionKeyPath);
if (appendResults)
{
resultListView.Groups.Add(new ListViewGroup
{
Header = $"Query {queryStatementIndex} ({results.Count} Documents)",
Name = $"Query{resultListView.Groups.Count}",
HeaderAlignment = HorizontalAlignment.Center
});
groupName = $"Query {queryStatementIndex}";
if (resultListView.Groups.Count == 1)
{
//first group set headers
resultListView.Columns[1].Text = headers.header1;
resultListView.Columns[2].Text = headers.header2;
}
else
{
//if the next query has a different select, then clear column headers
if (resultListView.Columns[1].Text != headers.header1)
{
resultListView.Columns[1].Text = string.Empty;
}
if (resultListView.Columns[2].Text != headers.header2)
{
resultListView.Columns[2].Text = string.Empty;
}
}
}
else
{
resultListView.Columns[1].Text = headers.header1;
resultListView.Columns[2].Text = headers.header2;
}
var validResultCount = 0;
foreach (var item in results)
{
var fromObject = JObject.FromObject(item);
if (!fromObject.HasValues)
{
continue;
}
validResultCount++;
var listItem = new ListViewItem();
if (appendResults && resultListView.Groups.Count > 0)
{
listItem.Group = resultListView.Groups[resultListView.Groups.Count - 1];
}
listItem.Tag = new DocumentResult {
Document = fromObject, CollectionName = collectionName, Query = query, GroupName = groupName
};
JProperty col1Prop = null;
JToken col1Token = null;
var columnText = fromObject.Properties().ParseColumnText(textPartitionKeyPath);
var resultProps = fromObject.Properties();
var subItem = new ListViewSubItem
{
Text = columnText.col1RowText
};
listItem.SubItems.Add(subItem);
if (resultProps.Count() > 1)
{
subItem = new ListViewSubItem
{
Text = columnText.col2RowText
};
if (!string.IsNullOrEmpty(subItem.Text))
{
listItem.SubItems.Add(subItem);
}
}
resultListView.Items.Add(listItem);
}
_totalDocumentCount += validResultCount;
SetResultCountLabel();
resultListToolStrip.Refresh();
}
/// <inheritdoc/> public override Maybe <QueryParts> AddSort(QueryParts parts, bool sortRandom, IImmutableList <(IColumn, SortOrder)> sort) =>
public override async Task RenderResults(IReadOnlyCollection <object> results, string collectionName, QueryParts query, bool appendResults, int queryStatementIndex)
{
var textPartitionKeyPath = await LookupPartitionKeyPath(query.CollectionName);
var column1Header = "";
var column2Header = "";
var queryHeader = "";
queryHeader = $"Query {queryStatementIndex} ({results.Count} Documents)";
var headers = LookupResultListViewHeaders(results.FirstOrDefault(), textPartitionKeyPath);
if (appendResults)
{
if (_groupCount == 1)
{
//first group set headers
column1Header = headers.header1;
column2Header = headers.header2;
}
else
{
//if the next query has a different select, then clear column headers
if (column1Header != headers.header1)
{
column1Header = string.Empty;
}
if (column2Header != headers.header2)
{
column2Header = string.Empty;
}
}
}
else
{
column1Header = headers.header1;
column2Header = headers.header2;
}
_outputTraceInformation.OutputColumn1 = column1Header;
_outputTraceInformation.OutputColumn2 = column2Header;
var outputDetailRecord = new OutputDetailRecord {
QueryHeader = queryHeader
};
_outputTraceInformation.OutputDetailRecords.Add(outputDetailRecord);
foreach (var item in results)
{
var fromObject = JObject.FromObject(item);
if (!fromObject.HasValues)
{
continue;
}
var documentDetail = new DocumentDetail {
Document = fromObject.ToString()
};
outputDetailRecord.Records.Add(documentDetail);
var columnText = fromObject.Properties().ParseColumnText(textPartitionKeyPath);
documentDetail.DisplayField1 = columnText.col1RowText;
documentDetail.DisplayField2 = columnText.col2RowText;
}
}
public async Task <(bool success, IReadOnlyCollection <object> results)> RunAsync(IDocumentStore documentStore, Connection connection, QueryParts queryParts, bool logStats, ILogger logger, CancellationToken cancellationToken, Dictionary <string, IReadOnlyCollection <object> > variables = null)
{
try
{
if (!queryParts.IsValidQuery())
{
logger.LogError("Invalid Query. Aborting Update.");
return(false, null);
}
var ids = queryParts.CleanQueryBody.Split(new[] { ',' });
if (queryParts.CleanQueryUpdateType == Constants.QueryParsingKeywords.REPLACE && ids.Length > 1)
{
var errorMessage = $"{Constants.QueryParsingKeywords.REPLACE} only supports replacing 1 document at a time.";
logger.LogError(errorMessage);
return(false, null);
}
if (queryParts.IsTransaction)
{
logger.LogInformation($"Transaction Created. TransactionId: {queryParts.TransactionId}");
await _transactionTask.BackuQueryAsync(connection.Name, connection.Database, queryParts.CollectionName, queryParts.TransactionId, queryParts.CleanOrginalQuery);
}
var partitionKeyPath = await documentStore.LookupPartitionKeyPath(connection.Database, queryParts.CollectionName);
var updateCount = 0;
var actionTransactionCacheBlock = new ActionBlock <string>(async documentId =>
{
//this handles transaction saving for recovery
await documentStore.ExecuteAsync(connection.Database, queryParts.CollectionName,
async(IDocumentExecuteContext context) =>
{
if (cancellationToken.IsCancellationRequested)
{
throw new TaskCanceledException("Task has been requested to cancel.");
}
JObject jDoc = null;
if (queryParts.IsTransaction)
{
var backupResult = await _transactionTask.BackupAsync(context, connection.Name, connection.Database, queryParts.CollectionName, queryParts.TransactionId, logger, documentId);
if (!backupResult.isSuccess)
{
logger.LogError($"Unable to backup document {documentId}. Skipping Update.");
return(false);
}
jDoc = backupResult.document;
}
if (queryParts.IsReplaceUpdateQuery())
{
var fullJObjectToUpdate = JObject.Parse(queryParts.CleanQueryUpdateBody);
var fullJObjectPartionKeyValue = fullJObjectToUpdate.SelectToken(partitionKeyPath).ToString();
var fullJObjectUpdatedDoc = await context.UpdateAsync(fullJObjectToUpdate, new RequestOptions
{
PartitionKey = fullJObjectPartionKeyValue
});
if (fullJObjectUpdatedDoc != null)
{
Interlocked.Increment(ref updateCount);
logger.LogInformation($"Updated {documentId}");
}
else
{
logger.LogInformation($"Document {documentId} unable to be updated.");
}
return(true);
}
//this is a partial update
if (jDoc == null)
{
//this would only need to run if not in a transaction, because in a transaction we have already queried for the doc and have it.
var queryToFindOptions = new QueryOptions
{
PopulateQueryMetrics = false,
EnableCrossPartitionQuery = true,
MaxItemCount = 1,
};
//we have to query to find the partitionKey value so we can do the delete
var queryToFind = context.QueryAsSql <object>($"SELECT * FROM {queryParts.CollectionName} WHERE {queryParts.CollectionName}.id = '{documentId.CleanId()}'", queryToFindOptions);
var queryResultDoc = (await queryToFind.ConvertAndLogRequestUnits(false, logger)).FirstOrDefault();
if (queryResultDoc == null)
{
logger.LogInformation($"Document {documentId} not found. Skipping Update");
return(false);
}
jDoc = JObject.FromObject(queryResultDoc);
}
var partionKeyValue = jDoc.SelectToken(partitionKeyPath).ToString();
var partialDoc = JObject.Parse(queryParts.CleanQueryUpdateBody);
//ensure the partial update is not trying to update id or the partition key
var pToken = partialDoc.SelectToken(partitionKeyPath);
var idToken = partialDoc.SelectToken(Constants.DocumentFields.ID);
if (pToken != null || idToken != null)
{
logger.LogError($"Updates are not allowed on ids or existing partition keys of a document. Skipping updated for document {documentId}.");
return(false);
}
var shouldUpdateToEmptyArray = partialDoc.HasEmptyJArray();
jDoc.Merge(partialDoc, new JsonMergeSettings
{
MergeArrayHandling = shouldUpdateToEmptyArray ? MergeArrayHandling.Replace : MergeArrayHandling.Merge,
MergeNullValueHandling = MergeNullValueHandling.Merge
});
//save
var updatedDoc = await context.UpdateAsync(jDoc, new RequestOptions
{
PartitionKey = partionKeyValue
});
if (updatedDoc != null)
{
Interlocked.Increment(ref updateCount);
logger.LogInformation($"Updated {documentId}");
}
else
{
logger.LogInformation($"Document {documentId} unable to be updated.");
}
return(true);
}, cancellationToken);
},
new ExecutionDataflowBlockOptions
{
MaxDegreeOfParallelism = MAX_DEGREE_PARALLEL,
CancellationToken = cancellationToken
});
foreach (var id in ids)
{
actionTransactionCacheBlock.Post(id);
}
actionTransactionCacheBlock.Complete();
await actionTransactionCacheBlock.Completion;
logger.LogInformation($"Updated {updateCount} out of {ids.Length}");
if (queryParts.IsTransaction && updateCount > 0)
{
logger.LogInformation($"To rollback execute: ROLLBACK {queryParts.TransactionId}");
}
return(true, null);
}
catch (Exception ex)
{
var errorMessage = $"Unable to run {Constants.QueryParsingKeywords.UPDATE} query.";
if (queryParts.CleanQueryUpdateType == Constants.QueryParsingKeywords.REPLACE)
{
errorMessage += $"{Constants.QueryParsingKeywords.REPLACE} only supports replacing 1 document at a time.";
}
logger.Log(LogLevel.Error, new EventId(), errorMessage, ex);
return(false, null);
}
}
public static SubqueryParts ParseSubquery(QueryModel queryModel, QueryParts parentQuery, string contextName, QueryContext context)
{
return(ParseSubquery(queryModel, parentQuery, false, contextName, context));
}
public virtual List <Offer> GetOffers(int clientId, uint sourcePriceCode, uint?noiseSupplierId, bool allAssortment, bool byCatalog, bool withProducers)
{
ClientCode = clientId;
InvokeGetActivePrices();
var assortmentSupplierId = Convert.ToUInt32(
MySqlHelper.ExecuteScalar(Connection,
@"
select FirmCode
from usersettings.pricesdata
where pricesdata.PriceCode = ?PriceCode
",
new MySqlParameter("?PriceCode", sourcePriceCode)));
//Заполняем код региона прайс-листа как домашний код региона клиента, относительно которого строится отчет
var SourceRegionCode = Convert.ToUInt64(
MySqlHelper.ExecuteScalar(Connection,
@"
select RegionCode
from Customers.Clients
where Id = ?ClientCode",
new MySqlParameter("?ClientCode", ClientCode)));
var enabledCost = MySqlHelper.ExecuteScalar(
Connection,
"select CostCode from ActivePrices where PriceCode = ?SourcePC and RegionCode = ?SourceRegionCode",
new MySqlParameter("?SourcePC", sourcePriceCode),
new MySqlParameter("?SourceRegionCode", SourceRegionCode));
if (enabledCost != null)
{
MySqlHelper.ExecuteNonQuery(
Connection,
@"
drop temporary table IF EXISTS Usersettings.SourcePrice;
create temporary table Usersettings.SourcePrice engine=MEMORY
select * from ActivePrices where PriceCode = ?SourcePC and RegionCode = ?SourceRegionCode;",
new MySqlParameter("?SourcePC", sourcePriceCode),
new MySqlParameter("?SourceRegionCode", SourceRegionCode));
}
var joinText = allAssortment || sourcePriceCode == 0 ? " Left JOIN " : " JOIN ";
string withWithoutPropertiesText;
if (byCatalog)
{
withWithoutPropertiesText = String.Format(@" if(C0.SynonymCode is not null, S.Synonym, {0}) ", GetCatalogProductNameSubquery("p.id"));
}
else
{
withWithoutPropertiesText = String.Format(@" if(C0.SynonymCode is not null, S.Synonym, {0}) ", QueryParts.GetFullFormSubquery("p.id", true));
}
var firmcr = withProducers ? " and ifnull(C0.CodeFirmCr,0) = ifnull(c00.CodeFirmCr,0) " : string.Empty;
var producerId = withProducers ? " ifnull(c00.CodeFirmCr, 0) " : " 0 ";
var producerName = withProducers ? " if(c0.SynonymFirmCrCode is not null, Sfc.Synonym , Prod.Name) " : " '-' ";
var result = new List <Offer>();
DataAdapter.SelectCommand.CommandText =
string.Format(
@"
select
p.CatalogId,
c00.ProductId,
{0} as ProducerId,
{1} as ProductName,
{2} as ProducerName,
c00.Id as CoreId,
c00.Code,
Prices.FirmCode as SupplierId,
c00.PriceCode as PriceId,
Prices.RegionCode as RegionId,
c00.Quantity,
if(if(round(cc.Cost * Prices.Upcost, 2) < c00.MinBoundCost, c00.MinBoundCost, round(cc.Cost * Prices.Upcost, 2)) > c00.MaxBoundCost,
c00.MaxBoundCost, if(round(cc.Cost*Prices.UpCost,2) < c00.MinBoundCost, c00.MinBoundCost, round(cc.Cost * Prices.Upcost, 2))) as Cost,
c0.Id as AssortmentCoreId,
c0.Code as AssortmentCode,
c0.CodeCr as AssortmentCodeCr,
{9} as AssortmentSupplierId,
c0.PriceCode as AssortmentPriceId,
{10} as AssortmentRegionId,
c0.Quantity as AssortmentQuantity,
{7} as AssortmentCost
from
Usersettings.ActivePrices Prices
join farm.core0 c00 on c00.PriceCode = Prices.PriceCode
join farm.CoreCosts cc on cc.Core_Id = c00.Id and cc.PC_CostCode = Prices.CostCode
join catalogs.Products as p on p.id = c00.productid
join Catalogs.Catalog as cg on p.catalogid = cg.id
{3} farm.Core0 c0 on c0.productid = c00.productid {4} and C0.PriceCode = {5}
{6}
left join Catalogs.Producers Prod on c00.CodeFirmCr = Prod.Id
left join farm.Synonym S on C0.SynonymCode = S.SynonymCode
left join farm.SynonymFirmCr Sfc on C0.SynonymFirmCrCode = Sfc.SynonymFirmCrCode
{8}
WHERE
{11}
",
producerId,
withWithoutPropertiesText,
producerName,
joinText,
firmcr,
sourcePriceCode,
(enabledCost != null)
? @"
left join farm.CoreCosts cc0 on cc0.Core_Id = c0.Id and cc0.PC_CostCode = " + enabledCost + @"
left join Usersettings.SourcePrice c0Prices on c0Prices.CostCode = " + enabledCost
: "",
(enabledCost != null)
? @"
if(cc0.Cost is null, 0,
if(if(round(cc0.Cost * c0Prices.Upcost, 2) < c0.MinBoundCost, c0.MinBoundCost, round(cc0.Cost * c0Prices.Upcost, 2)) > c0.MaxBoundCost,
c0.MaxBoundCost, if(round(cc0.Cost*c0Prices.UpCost,2) < c0.MinBoundCost, c0.MinBoundCost, round(cc0.Cost * c0Prices.Upcost, 2)))
)"
: " null ",
@"",
assortmentSupplierId,
SourceRegionCode,
sourcePriceCode == 0
? " c00.Junk = 0 "
: @"
({1} (c0.PriceCode <> c00.PriceCode) or (Prices.RegionCode <> {0}) or (c0.Id = c00.Id))
and (c00.Junk = 0 or c0.Id = c00.Id)".Format(SourceRegionCode, allAssortment || sourcePriceCode == 0 ? "(c0.PriceCode is null) or" : string.Empty));
Random random = null;
if (noiseSupplierId.HasValue)
{
random = new Random();
}
#if DEBUG
Debug.WriteLine(DataAdapter.SelectCommand.CommandText);
#endif
using (var reader = DataAdapter.SelectCommand.ExecuteReader()) {
foreach (var row in reader.Cast <IDataRecord>())
{
var offer = new Offer(row, noiseSupplierId, random);
result.Add(offer);
}
}
return(result);
}
public async Task <(bool success, IReadOnlyCollection <object> results)> RunAsync(IDocumentStore documentStore, Connection connection, QueryParts queryParts, bool logStats, ILogger logger, CancellationToken cancellationToken, Dictionary <string, IReadOnlyCollection <object> > variables = null)
{
try
{
if (!queryParts.IsValidQuery())
{
logger.LogError("Invalid Query. Aborting Delete.");
return(false, null);
}
var ids = queryParts.CleanQueryBody.Split(new[] { ',' });
if (queryParts.IsTransaction)
{
logger.LogInformation($"Transaction Created. TransactionId: {queryParts.TransactionId}");
await _transactionTask.BackuQueryAsync(connection.Name, connection.Database, queryParts.CollectionName, queryParts.TransactionId, queryParts.CleanOrginalQuery);
}
var partitionKeyPath = await documentStore.LookupPartitionKeyPath(connection.Database, queryParts.CollectionName);
var deleteCount = 0;
var actionTransactionCacheBlock = new ActionBlock <string>(async documentId =>
{
//this handles transaction saving for recovery
await documentStore.ExecuteAsync(connection.Database, queryParts.CollectionName,
async(IDocumentExecuteContext context) =>
{
if (cancellationToken.IsCancellationRequested)
{
throw new TaskCanceledException("Task has been requested to cancel.");
}
if (queryParts.IsTransaction)
{
var backupResult = await _transactionTask.BackupAsync(context, connection.Name, connection.Database, queryParts.CollectionName, queryParts.TransactionId, logger, documentId);
if (!backupResult.isSuccess)
{
logger.LogError($"Unable to backup document {documentId}. Skipping Delete.");
return(false);
}
}
var queryToFindOptions = new QueryOptions
{
PopulateQueryMetrics = false,
EnableCrossPartitionQuery = true,
MaxItemCount = 1,
};
//we have to query to find the partitionKey value so we can do the delete
var queryToFind = context.QueryAsSql <object>($"SELECT {queryParts.CollectionName}.{partitionKeyPath} FROM {queryParts.CollectionName} WHERE {queryParts.CollectionName}.id = '{documentId.CleanId()}'", queryToFindOptions);
var partitionKeyResult = (await queryToFind.ConvertAndLogRequestUnits(false, logger)).FirstOrDefault();
if (partitionKeyResult != null)
{
var jobj = JObject.FromObject(partitionKeyResult);
var partionKeyValue = jobj.SelectToken(partitionKeyPath).ToString();
var deleted = await context.DeleteAsync(documentId.CleanId(), new RequestOptions
{
PartitionKey = partionKeyValue
});
if (deleted)
{
Interlocked.Increment(ref deleteCount);
logger.LogInformation($"Deleted {documentId}");
}
else
{
logger.LogInformation($"Document {documentId} unable to be deleted.");
}
}
else
{
logger.LogInformation($"Document {documentId} not found. Skipping");
}
return(true);
}, cancellationToken);
},
new ExecutionDataflowBlockOptions
{
MaxDegreeOfParallelism = MAX_DEGREE_PARALLEL,
CancellationToken = cancellationToken
});
foreach (var id in ids)
{
actionTransactionCacheBlock.Post(id);
}
actionTransactionCacheBlock.Complete();
await actionTransactionCacheBlock.Completion;
logger.LogInformation($"Deleted {deleteCount} out of {ids.Length}");
if (queryParts.IsTransaction && deleteCount > 0)
{
logger.LogInformation($"To rollback execute: ROLLBACK {queryParts.TransactionId}");
}
return(true, null);
}
catch (Exception ex)
{
logger.Log(LogLevel.Error, new EventId(), $"Unable to run {Constants.QueryParsingKeywords.DELETE} query", ex);
return(false, null);
}
}
public bool CanRun(QueryParts queryParts)
{
return(queryParts.CleanQueryType.Equals(Constants.QueryParsingKeywords.DELETE, StringComparison.InvariantCultureIgnoreCase) && !queryParts.CleanQueryBody.Equals("*"));
}
private static Func <string, T> ProjectExpression <T>(SubQueryExpression sqe, QueryParts parts)
{
var mqp =
new MainQueryParts(
parts.Locator,
parts.ConverterFactory,
parts.Simplifications,
parts.ExpressionMatchers,
parts.MemberMatchers,
parts.ProjectionMatchers);
var subquery = SubqueryGeneratorQueryModelVisitor.ParseSubquery(sqe.QueryModel, mqp, parts.ContextName, parts.Context.Select());
var newExpression = sqe.QueryModel.SelectClause.Selector as NewExpression;
if (newExpression != null)
{
return(ProjectNew <T>(newExpression, parts, subquery));
}
return(ProjectMapping <T>(sqe, parts, subquery));
}
public static string GetSqlExpression(Expression linqExpression, QueryParts queryParts)
{
//TODO pass queryParts context!?
return(GetSqlExpression(linqExpression, queryParts, QueryContext.Standard));
}
private static Result CreateResult(QuerySourceReferenceExpression expression, QueryParts parts)
{
var qs = expression.ReferencedQuerySource;
var fc = qs as FromClauseBase;
if (fc != null)
{
var ce = fc.FromExpression as ConstantExpression;
if (ce != null)
{
var type = ce.Value.GetType();
if (type.IsQueryable())
{
return(CreateResult(qs.ItemName, type.GetGenericArguments()[0], qs, parts, true));
}
else
{
return(CreateResult(qs.ItemName, qs.ItemType, qs, parts, true));
}
}
else if (fc.FromExpression is QuerySourceReferenceExpression)
{
return(CreateResult(qs.ItemName, qs.ItemType, qs, parts, true));
}
else if (fc.FromExpression is SubQueryExpression)
{
var sqe = fc.FromExpression as SubQueryExpression;
var source = GetOriginalSource(sqe.QueryModel.MainFromClause);
return(CreateResult(qs.ItemName, source.ItemType, qs, parts, false));
}
}
return(CreateResult(qs.ItemName, qs.ItemType, qs, parts, true));
}
protected override void GenerateReport()
{
ProfileHelper.Next("PreGetOffers");
if (_priceCode == 0)
{
throw new ReportException("В отчете не установлен параметр \"Прайс-лист\".");
}
var customerFirmName = GetSupplierName(_priceCode);
CheckPriceActual(_priceCode);
if (_byWeightCosts)
{
ProcessWeigth();
return;
}
GetOffers(_SupplierNoise);
var enabledPrice = Convert.ToInt32(
MySqlHelper.ExecuteScalar(
DataAdapter.SelectCommand.Connection,
"select PriceCode from ActivePrices where PriceCode = ?PriceCode",
new MySqlParameter("?PriceCode", _priceCode)));
if (enabledPrice == 0 && !_byBaseCosts)
{
var clientShortName = Convert.ToString(
MySqlHelper.ExecuteScalar(
DataAdapter.SelectCommand.Connection,
@"select Name from Customers.Clients where Id = ?FirmCode",
new MySqlParameter("?FirmCode", ClientCode)));
throw new ReportException(String.Format("Для клиента {0} ({1}) не доступен прайс-лист {2} ({3}).", clientShortName, ClientCode, customerFirmName, _priceCode));
}
DataAdapter.SelectCommand.Parameters.Clear();
var selectCommandText = String.Empty;
switch (_reportType)
{
case DefReportType.ByName: {
selectCommandText = @"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
NameId int Unsigned,
key NameId(NameId))engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct Catalog.NameId
from
ActivePrices apt,
Core c,
Catalogs.Products,
Catalogs.Catalog
where
apt.PriceCode <> ?SourcePC
and apt.PriceCode=c.PriceCode
and Products.Id = c.ProductId
and Catalog.Id = products.CatalogId;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
NameId int Unsigned,
Code VARCHAR(20) not NULL,
key NameId(NameId))engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct Catalog.NameId, FarmCore.Code
from
(
Core c
inner join Catalogs.Products on c.ProductId = Products.Id
inner join Catalogs.Catalog on Products.CatalogId = Catalog.Id
)
inner join farm.Core0 FarmCore on FarmCore.Id = c.Id
left join SummaryByPrices st on st.NameId = Catalog.NameId
where
c.PriceCode=?SourcePC
and st.NameId is NULL
and Catalog.Pharmacie = 1;
select distinct OtherByPrice.Code, CatalogNames.Name
from
OtherByPrice
inner join catalogs.CatalogNames on OtherByPrice.NameId = CatalogNames.Id
order by CatalogNames.Name;";
break;
}
case DefReportType.ByNameAndForm: {
selectCommandText = @"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
CatalogId int Unsigned,
key CatalogId(CatalogId)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct Products.CatalogId
from
ActivePrices apt,
Core c,
Catalogs.Products
where
apt.PriceCode <> ?SourcePC
and apt.PriceCode=c.PriceCode
and Products.Id = c.ProductId;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
CatalogId int Unsigned,
Code VARCHAR(20) not NULL,
key CatalogId(CatalogId) ) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct Products.CatalogId, FarmCore.Code
from
(
Core c
inner join Catalogs.Products on c.ProductId = Products.Id
)
inner join farm.Core0 FarmCore on FarmCore.Id = c.Id
left join SummaryByPrices st on st.CatalogId = Products.CatalogId
where
c.PriceCode=?SourcePC
and st.CatalogId is NULL;
select distinct OtherByPrice.Code, CatalogNames.Name, CatalogForms.Form
from
OtherByPrice
inner join catalogs.catalog on OtherByPrice.CatalogId = catalog.Id
inner join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
inner join catalogs.CatalogForms on catalog.FormId = CatalogForms.Id
where catalog.Pharmacie = 1
order by CatalogNames.Name, CatalogForms.Form;";
break;
}
case DefReportType.ByNameAndFormAndFirmCr: {
selectCommandText = @"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
CatalogId int Unsigned,
CodeFirmCr int Unsigned,
key CatalogId(CatalogId),
key CodeFirmCr(CodeFirmCr)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct Products.CatalogId, FarmCore.CodeFirmCr
from
ActivePrices apt,
Core c,
farm.Core0 FarmCore,
Catalogs.Products
where
apt.PriceCode <> ?SourcePC
and apt.PriceCode = c.PriceCode
and Products.Id = c.ProductId
and FarmCore.Id = c.Id
and FarmCore.CodeFirmCr is not null;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
CatalogId int Unsigned,
CodeFirmCr int Unsigned,
Code VARCHAR(20) not NULL,
key CatalogId(CatalogId),
key CodeFirmCr(CodeFirmCr) ) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct Products.CatalogId, FarmCore.CodeFirmCr, FarmCore.Code
from
Core c
inner join farm.Core0 FarmCore on c.Id = FarmCore.Id
inner join Catalogs.Products on c.ProductId = Products.Id
left join SummaryByPrices st on st.CatalogId = Products.CatalogId and st.CodeFirmCr = FarmCore.CodeFirmCr
where
c.PriceCode=?SourcePC
and FarmCore.CodeFirmCr is not null
and st.CatalogId is NULL;
select distinct OtherByPrice.Code, CatalogNames.Name, CatalogForms.Form, Producers.Name as FirmCr
from
OtherByPrice
join catalogs.catalog on OtherByPrice.CatalogId = catalog.Id
join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
join catalogs.CatalogForms on catalog.FormId = CatalogForms.Id
join Catalogs.Producers on Producers.Id = OtherByPrice.CodeFirmCr
where catalog.Pharmacie = 1
order by CatalogNames.Name, CatalogForms.Form, Producers.Name;";
break;
}
case DefReportType.ByProduct: {
selectCommandText = String.Format(@"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
ProductId int Unsigned,
key ProductId(ProductId)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct c.ProductId
from
ActivePrices apt,
Core c
where
apt.PriceCode <> ?SourcePC
and apt.PriceCode=c.PriceCode;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
ProductId int Unsigned,
Code VARCHAR(20) not NULL,
key ProductId(ProductId)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct c.ProductId, FarmCore.Code
from
Core c
inner join farm.Core0 FarmCore on FarmCore.Id = c.Id
left join SummaryByPrices st on st.ProductId = c.ProductId
where
c.PriceCode=?SourcePC
and st.ProductId is NULL;
select
distinct
OtherByPrice.Code,
CatalogNames.Name,
{0} as FullForm
from
(
OtherByPrice
inner join catalogs.products on OtherByPrice.ProductId = products.Id
inner join catalogs.catalog on products.CatalogId = catalog.Id
inner join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
)
where catalog.Pharmacie = 1
order by CatalogNames.Name, FullForm;
", QueryParts.GetFullFormSubquery("OtherByPrice.ProductId", false));
break;
}
case DefReportType.ByProductAndFirmCr: {
selectCommandText = String.Format(@"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
ProductId int Unsigned,
CodeFirmCr int Unsigned,
key ProductId(ProductId),
key CodeFirmCr(CodeFirmCr)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct c.ProductId, FarmCore.CodeFirmCr
from
ActivePrices apt,
Core c,
farm.Core0 FarmCore
where
apt.PriceCode <> ?SourcePC
and apt.PriceCode=c.PriceCode
and FarmCore.Id = c.Id
and FarmCore.CodeFirmCr is not null;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
ProductId int Unsigned,
CodeFirmCr int Unsigned,
Code VARCHAR(20) not NULL,
key ProductId(ProductId),
key CodeFirmCr(CodeFirmCr) ) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct c.ProductId, FarmCore.CodeFirmCr, FarmCore.Code
from
Core c
inner join farm.Core0 FarmCore on FarmCore.Id = c.Id
left join SummaryByPrices st on st.ProductId = c.ProductId and st.CodeFirmCr = FarmCore.CodeFirmCr
where
c.PriceCode=?SourcePC
FarmCore.CodeFirmCr is not null
and st.ProductId is NULL;
select
distinct
OtherByPrice.Code,
CatalogNames.Name,
{0} as FullForm,
Producers.Name as FirmCr
from
OtherByPrice
join catalogs.products on OtherByPrice.ProductId = products.Id
join catalogs.catalog on products.CatalogId = catalog.Id
join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
join Catalogs.Producers on Producers.Id = OtherByPrice.CodeFirmCr
where catalog.Pharmacie = 1
order by CatalogNames.Name, FullForm, Producers.Name;
", QueryParts.GetFullFormSubquery("OtherByPrice.ProductId", false));
break;
}
}
DataAdapter.SelectCommand.CommandText = selectCommandText;
DataAdapter.SelectCommand.Parameters.AddWithValue("?SourcePC", _priceCode);
DataAdapter.Fill(_dsReport, "Results");
}
private static Result CreateResult(FromClauseBase fromClause, QueryParts parts)
{
var ce = fromClause.FromExpression as ConstantExpression;
if (ce != null)
{
var type = ce.Value.GetType();
if (type.IsQueryable())
{
return(CreateResult(fromClause.ItemName, type.GetGenericArguments()[0], fromClause, parts, true));
}
return(CreateResult(fromClause.ItemName, fromClause.ItemType, fromClause, parts, true));
}
var qsre = fromClause.FromExpression as QuerySourceReferenceExpression;
if (qsre != null)
{
if (fromClause.ItemType.IsInterface && qsre.ReferencedQuerySource.ItemType.IsGrouping())
{
var mfc = qsre.ReferencedQuerySource as MainFromClause;
if (mfc != null)
{
var sq = mfc.FromExpression as SubQueryExpression;
if (sq != null)
{
var smf = sq.QueryModel.MainFromClause.ItemType;
if (fromClause.ItemType.IsAssignableFrom(smf))
{
return(CreateResult(fromClause.ItemName, smf, fromClause, parts, false));
}
}
}
}
return(CreateResult(fromClause.ItemName, fromClause.ItemType, fromClause, parts, true));
}
var sqe = fromClause.FromExpression as SubQueryExpression;
if (sqe != null)
{
var source = GetOriginalSource(sqe.QueryModel.MainFromClause);
//TODO: ugly hack to decide if VALUE(X) should be used or not
var simplified = fromClause is MainFromClause
? parts.TryToSimplifyMainFrom(fromClause as MainFromClause)
: fromClause is AdditionalFromClause
? parts.TryToSimplifyAdditionalFrom(fromClause as AdditionalFromClause)
: null;
if (fromClause.ItemType == source.ItemType ||
fromClause.ItemType.IsAssignableFrom(source.ItemType) ||
fromClause.ItemType.IsGrouping())
{
return(CreateResult(fromClause.ItemName, source.ItemType, fromClause, parts, source == simplified));
}
return(CreateProjector(fromClause.ItemName, fromClause, sqe, parts));
}
var nae = fromClause.FromExpression as NewArrayExpression;
if (nae != null)
{
return(CreateResult(fromClause.ItemName, fromClause.ItemType, fromClause, parts, true));
}
var me = fromClause.FromExpression as MemberExpression;
if (me != null)
{
return(CreateResult(fromClause.ItemName, fromClause.ItemType, fromClause, parts, true));
}
throw new NotImplementedException("Unknown from clause. Please provide feedback about missing feature.");
}
public static string GetSqlExpression(Expression linqExpression, QueryParts queryParts)
{
//TODO pass queryParts context!?
return(GetSqlExpression(linqExpression, queryParts, string.Empty, queryParts.Context));
}
public bool CanRun(QueryParts queryParts)
{
return(queryParts.IsValidInsertQuery());
}
private string SimplifyMemberExpression(QueryParts query, SubqueryParts parts, string exp, MemberExpression ma)
{
return(exp + " IN (SELECT " + query.GetSqlExpression(parts.Selector)
+ " FROM unnest(" + query.GetSqlExpression(ma) + ") \"" + query.MainFrom.ItemName + "\")");
}
public async Task <(bool success, IReadOnlyCollection <object> results)> RunAsync(IDocumentStore documentStore, Connection connection, QueryParts queryParts, bool logStats, ILogger logger, CancellationToken cancellationToken, Dictionary <string, IReadOnlyCollection <object> > variables = null)
{
try
{
if (!queryParts.IsValidInsertQuery())
{
return(false, null);
}
var jsonDocument = JsonConvert.DeserializeObject <dynamic>(queryParts.CleanQueryBody);
var newDocs = await documentStore.ExecuteAsync(connection.Database, queryParts.CollectionName,
async (IDocumentExecuteContext context) =>
{
var jDocs = new List <JObject>();
if (jsonDocument.Type == JTokenType.Array)
{
foreach (JObject d in JArray.FromObject(jsonDocument))
{
await context.CreateAsync(d);
jDocs.Add(d);
}
return(jDocs);
}
var doc = JObject.FromObject(jsonDocument);
var singlaDoc = await context.CreateAsync(doc);
jDocs.Add(singlaDoc);
return(jDocs);
}, cancellationToken);
foreach (var newDoc in newDocs)
{
logger.LogInformation($"Document {newDoc[Constants.DocumentFields.ID]} created.");
}
return(true, null);
}
catch (Exception ex)
{
logger.Log(LogLevel.Error, new EventId(), $"Unable to run {Constants.QueryParsingKeywords.INSERT} query", ex);
return(false, null);
}
}
public async Task <(bool success, IReadOnlyCollection <object> results)> RunAsync(IDocumentStore documentStore, Connection connection, QueryParts queryParts, bool logStats, ILogger logger, CancellationToken cancellationToken, Dictionary <string, IReadOnlyCollection <object> > variables = null)
{
try
{
if (!queryParts.IsValidQuery())
{
logger.LogError("Invalid Query. Aborting Delete.");
return(false, null);
}
//get the ids
var selectQuery = queryParts.ToRawSelectQuery();
var results = await documentStore.ExecuteAsync(connection.Database, queryParts.CollectionName,
async (IDocumentExecuteContext context) =>
{
var queryOptions = new QueryOptions
{
PopulateQueryMetrics = true,
EnableCrossPartitionQuery = true,
MaxBufferedItemCount = 200,
MaxDegreeOfParallelism = MAX_DEGREE_PARALLEL,
MaxItemCount = -1,
};
if (variables != null && variables.Any() && queryParts.HasVariablesInWhereClause())
{
selectQuery = _variableInjectionTask.InjectVariables(selectQuery, variables, logger);
}
var query = context.QueryAsSql <object>(selectQuery, queryOptions);
return(await query.ConvertAndLogRequestUnits(false, logger));
}, cancellationToken);
var fromObjects = JArray.FromObject(results);
if (queryParts.IsTransaction)
{
logger.LogInformation($"Transaction Created. TransactionId: {queryParts.TransactionId}");
await _transactionTask.BackuQueryAsync(connection.Name, connection.Database, queryParts.CollectionName, queryParts.TransactionId, queryParts.CleanOrginalQuery);
}
var partitionKeyPath = await documentStore.LookupPartitionKeyPath(connection.Database, queryParts.CollectionName);
var deleteCount = 0;
var actionTransactionCacheBlock = new ActionBlock <JObject>(async document =>
{
await documentStore.ExecuteAsync(connection.Database, queryParts.CollectionName,
async(IDocumentExecuteContext context) =>
{
if (cancellationToken.IsCancellationRequested)
{
throw new TaskCanceledException("Task has been requested to cancel.");
}
var documentId = document[Constants.DocumentFields.ID].ToString();
if (queryParts.IsTransaction)
{
var backupResult = await _transactionTask.BackupAsync(context, connection.Name, connection.Database, queryParts.CollectionName, queryParts.TransactionId, logger, null, document);
if (!backupResult.isSuccess)
{
logger.LogError($"Unable to backup document {documentId}. Skipping Delete.");
return(false);
}
}
var partionKeyValue = document.SelectToken(partitionKeyPath).ToString();
var deleted = await context.DeleteAsync(documentId.CleanId(), new RequestOptions
{
PartitionKey = partionKeyValue
});
if (deleted)
{
Interlocked.Increment(ref deleteCount);
logger.LogInformation($"Deleted {documentId}");
}
else
{
logger.LogInformation($"Document {documentId} unable to be deleted.");
}
return(true);
}, cancellationToken);
},
new ExecutionDataflowBlockOptions
{
MaxDegreeOfParallelism = MAX_DEGREE_PARALLEL,
CancellationToken = cancellationToken
});
foreach (JObject doc in fromObjects)
{
actionTransactionCacheBlock.Post(doc);
}
actionTransactionCacheBlock.Complete();
await actionTransactionCacheBlock.Completion;
logger.LogInformation($"Deleted {deleteCount} out of {fromObjects.Count}");
if (queryParts.IsTransaction && deleteCount > 0)
{
logger.LogInformation($"To rollback execute: ROLLBACK {queryParts.TransactionId}");
}
return(true, null);
}
catch (Exception ex)
{
logger.Log(LogLevel.Error, new EventId(), $"Unable to run {Constants.QueryParsingKeywords.DELETE} query", ex);
return(false, null);
}
}
public static SubqueryParts ParseSubquery(QueryModel queryModel, QueryParts parentQuery)
{
return(ParseSubquery(queryModel, parentQuery, false, parentQuery.ContextName));
}
public void RenderResults(IReadOnlyCollection <object> results, string collectionName, QueryParts query, bool appendResults, int queryStatementIndex)
{
_view.RenderResults(results, collectionName, query, appendResults, queryStatementIndex);
}
public static SubqueryParts ParseSubquery(QueryModel queryModel, QueryParts parentQuery, bool canQueryInMemory)
{
return(ParseSubquery(queryModel, parentQuery, canQueryInMemory, parentQuery.ContextName));
}
public async Task <(bool success, IReadOnlyCollection <object> results)> RunAsync(IDocumentStore documentStore, Connection connection, QueryParts queryParts, bool logStats, ILogger logger, CancellationToken cancellationToken, Dictionary <string, IReadOnlyCollection <object> > variables = null)
{
try
{
if (!queryParts.IsRollback)
{
return(false, null);
}
var collectionName = queryParts.RollbackName.Split(new[] { '_' })[0];
//get each file from rollback folder if exists
var files = _transactionTask.GetRollbackFiles(connection.Name, connection.Database, collectionName, queryParts.RollbackName);
if (files.Length == 0)
{
logger.LogInformation($"No files found for {queryParts.RollbackName}. Aborting Rollback.");
return(false, null);
}
var updateCount = 0;
var rollbackBlock = new ActionBlock <JObject>(async document =>
{
//this handles transaction saving for recovery
await documentStore.ExecuteAsync(connection.Database, collectionName,
async(IDocumentExecuteContext context) =>
{
if (cancellationToken.IsCancellationRequested)
{
throw new TaskCanceledException("Task has been requested to cancel.");
}
var result = await context.UpdateAsync(document);
Interlocked.Increment(ref updateCount);
logger.LogInformation($"Restored {document[Constants.DocumentFields.ID]}");
return(result);
}, cancellationToken);
},
new ExecutionDataflowBlockOptions
{
MaxDegreeOfParallelism = MAX_DEGREE_PARALLEL,
CancellationToken = cancellationToken
});
foreach (var file in files)
{
var jobj = JObject.Parse(File.ReadAllText(file.FullName));
rollbackBlock.Post(jobj);
}
rollbackBlock.Complete();
await rollbackBlock.Completion;
logger.LogInformation($"Rolled back {updateCount} out of {files.Length}");
return(true, null);
}
catch (Exception ex)
{
logger.Log(LogLevel.Error, new EventId(), $"Unable to execute {Constants.QueryParsingKeywords.ROLLBACK}", ex);
return(false, null);
}
}
private SubqueryGeneratorQueryModelVisitor(QueryParts parentQuery, bool canQueryInMemory, Expression selector, string contextName)
{
QueryParts = new SubqueryParts(parentQuery, canQueryInMemory, selector, contextName);
}
protected void GetMinPrice()
{
var sql = @"
select
SourcePrice.ID,
SourcePrice.Code,
AllPrices.CatalogCode,
AllPrices.Cost,";
if (_reportType == 2 || _reportType == 4)
{
sql += @" FarmCore.Quantity,";
}
if (_calculateByCatalog)
{
sql += String.Format(" ifnull(s.Synonym, {0}) as FullName, ", GetCatalogProductNameSubquery("AllPrices.ProductId"));
}
else
{
sql += String.Format(" ifnull(s.Synonym, {0}) as FullName, ", QueryParts.GetFullFormSubquery("AllPrices.ProductId", true));
}
//Если отчет без учета производителя, то код не учитываем и выводим "-"
if (_reportType <= 2)
{
sql += @"
'-' as FirmCr,
0 As Cfc ";
}
else
{
sql += @"
ifnull(sfc.Synonym, Cfc.Name) as FirmCr,
cfc.Id As Cfc";
}
sql += @"
from
(
catalogs.products,
farm.core0 FarmCore,";
//Если отчет полный, то интересуют все прайс-листы, если нет, то только SourcePC
if (_reportIsFull)
{
if (_reportType <= 2)
{
sql += @"
Core AllPrices
)
left join TmpSourceCodes SourcePrice on SourcePrice.CatalogCode=AllPrices.CatalogCode ";
}
else
{
sql += @"
Core AllPrices
)
left join TmpSourceCodes SourcePrice on SourcePrice.CatalogCode=AllPrices.CatalogCode and SourcePrice.codefirmcr=FarmCore.codefirmcr";
}
}
else
{
sql += @"
Core AllPrices,
TmpSourceCodes SourcePrice
)";
}
//Если отчет с учетом производителя, то пересекаем с таблицей Producers
if (_reportType > 2)
{
sql += @"
left join catalogs.Producers cfc on cfc.Id = FarmCore.codefirmcr";
}
sql += @"
left join farm.synonym s on s.SynonymCode = SourcePrice.SynonymCode
left join farm.synonymfirmcr sfc on sfc.SynonymFirmCrCode = SourcePrice.SynonymFirmCrCode
where
products.id = AllPrices.ProductId
and FarmCore.Id = AllPrices.Id";
sql += @"
and (( ( (AllPrices.PriceCode <> SourcePrice.PriceCode) or (AllPrices.RegionCode <> SourcePrice.RegionCode) or (SourcePrice.id is null) ) and (FarmCore.Junk =0) and (FarmCore.Await=0) )
or ( (AllPrices.PriceCode = SourcePrice.PriceCode) and (AllPrices.RegionCode = SourcePrice.RegionCode) and (AllPrices.Id = SourcePrice.id) ) )" ;
//Если отчет не полный, то выбираем только те, которые есть в SourcePC
if (!_reportIsFull)
{
if (_reportType <= 2)
{
sql += @"
and SourcePrice.CatalogCode=AllPrices.CatalogCode ";
}
else
{
sql += @"
and SourcePrice.CatalogCode=AllPrices.CatalogCode and SourcePrice.codefirmcr=FarmCore.codefirmcr ";
}
}
sql += @"
order by FullName, FirmCr";
DataAdapter.SelectCommand.CommandText = sql;
DataAdapter.Fill(_dsReport, "MinCatalog");
}
public CinarUriParser(string url)
: base(url)
{
this.queryPart = new QueryParts(this.Query, this);
}