Esempio n. 1
0
 public bool CanRun(QueryParts queryParts)
 {
     return(queryParts.IsRollback);
 }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
 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);
 }
Esempio n. 5
0
 /// <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();
        }
Esempio n. 8
0
 /// <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;
            }
        }
Esempio n. 10
0
        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);
            }
        }
Esempio n. 11
0
 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));
        }
Esempio n. 16
0
 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));
        }
Esempio n. 18
0
        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());
 }
Esempio n. 22
0
 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);
            }
        }
Esempio n. 24
0
        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);
            }
        }
Esempio n. 25
0
 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);
 }
Esempio n. 27
0
 public static SubqueryParts ParseSubquery(QueryModel queryModel, QueryParts parentQuery, bool canQueryInMemory)
 {
     return(ParseSubquery(queryModel, parentQuery, canQueryInMemory, parentQuery.ContextName));
 }
Esempio n. 28
0
        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);
            }
        }
Esempio n. 29
0
 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");
        }
Esempio n. 31
0
 public CinarUriParser(string url)
     : base(url)
 {
     this.queryPart = new QueryParts(this.Query, this);
 }