public OrderTrackingAggregation GetOrderTrackingEntities(IEnumerable <Workgroup> workgroups, DateTime createdAfter, DateTime createBefore, int size = 1000) { var index = IndexHelper.GetIndexName(Indexes.OrderTracking); var workgroupIds = workgroups.Select(w => w.Id).ToArray(); var results = _client.Search <OrderTrackingEntity>( s => s.Index(index) .Size(size) .Query(f => f.DateRange(r => r.Field(o => o.OrderCreated).GreaterThan(createdAfter).LessThan(createBefore)) && f.Term(x => x.IsComplete, true) && f.Terms(o => o.Field(field => field.WorkgroupId).Terms(workgroupIds))) .Aggregations( a => a.Average("AverageTimeToCompletion", avg => avg.Field(x => x.MinutesToCompletion)) .Average("AverageTimeToRoleComplete", avg => avg.Field(x => x.MinutesToApprove)) .Average("AverageTimeToAccountManagers", avg => avg.Field(x => x.MinutesToAccountManagerComplete)) .Average("AverageTimeToPurchaser", avg => avg.Field(x => x.MinutesToPurchaserComplete))) ); return(new OrderTrackingAggregation { OrderTrackingEntities = results.Hits.Select(h => h.Source).ToList(), AverageTimeToAccountManagers = results.Aggregations.Average("AverageTimeToAccountManagers").Value, AverageTimeToApprover = results.Aggregations.Average("AverageTimeToRoleComplete").Value, AverageTimeToCompletion = results.Aggregations.Average("AverageTimeToCompletion").Value, AverageTimeToPurchaser = results.Aggregations.Average("AverageTimeToPurchaser").Value }); }
public int NumRecords(Indexes index) { var indexName = IndexHelper.GetIndexName(index); // hopefully this works despite count not having a generic option. I overwrote the index inside the action instead. return((int)_client.Count <OrderHistory>(x => x.Index(indexName)).Count); }
public DateTime LastModified(Indexes index) { //TODO: no idea if this will work var indexName = IndexHelper.GetIndexName(index); return (Convert.ToDateTime(_client.IndicesStats(i => i.Index(indexName)).Indices[indexName].Total.Indexing.Time)); }
public IList <IdAndName> SearchBuildings(string searchTerm) { var index = IndexHelper.GetIndexName(Indexes.Buildings); var results = _client.Search <Building>( s => s.Index(index).Query(q => q.QueryString(qs => qs.Query(searchTerm)))); return(results.Hits.Select(h => new IdAndName(h.Source.Id, h.Source.BuildingName)).ToList()); }
public OrderTrackingAggregationByRole GetOrderTrackingEntitiesByRole(IEnumerable <Workgroup> workgroups, DateTime createdAfter, DateTime createBefore, string role, int size) { var index = IndexHelper.GetIndexName(Indexes.OrderTracking); var workgroupIds = workgroups.Select(w => w.Id).ToArray(); string roleNames = "purchaserId"; string timeField = "minutesToPurchaserComplete"; if (role == "Approver") { roleNames = "approverId"; timeField = "minutesToApprove"; } if (role == "AccountManager") { roleNames = "accountManagerId"; timeField = "minutesToAccountManagerComplete"; } // get the .keyword version of the index, for aggregates (since we aren't full text searching against the field) roleNames = roleNames + ".keyword"; var results = _client.Search <OrderTrackingEntity>( s => s.Index(index) .Size(size) .Query(f => f.DateRange(r => r.Field(o => o.OrderCreated).GreaterThan(createdAfter).LessThan(createBefore)) && f.Term(x => x.Status, "complete") && f.Terms(o => o.Field(field => field.WorkgroupId).Terms(workgroupIds))) .Aggregations( a => a.Terms("RolePercentiles", t => t.Field(roleNames) .Aggregations(b => b.Percentiles("PercentileTimes", p => p.Field(timeField) .Percents(new double[] { 0, 25, 50, 75, 100 }))) ) .Average("AverageTimeToRoleComplete", avg => avg.Field(timeField))) ); var names = results.Aggregations.Terms("RolePercentiles").Buckets.Select(n => n.Key + "; n=" + n.DocCount.ToString()).ToArray(); var percentilesInRole = results.Aggregations.Terms("RolePercentiles") .Buckets.Select(user => user.PercentilesBucket("PercentileTimes")) .Select( uservalue => uservalue.Items.OrderBy(o => o.Percentile).Select(a => (a.Value.HasValue ? a.Value.Value : 0) / 1440).ToArray() // converted to days to help with scale ) .ToList(); return(new OrderTrackingAggregationByRole { OrderTrackingEntities = results.Hits.Select(h => h.Source).ToList(), AverageTimeToRoleComplete = results.Aggregations.Average("AverageTimeToRoleComplete").Value / 1440, NamesInRole = names, PercentilesForRole = percentilesInRole }); }
public OrderTrackingAggregationByRole GetOrderTrackingEntitiesByRole(IEnumerable <Workgroup> workgroups, DateTime createdAfter, DateTime createBefore, string role, int size) { var index = IndexHelper.GetIndexName(Indexes.OrderTracking); var workgroupIds = workgroups.Select(w => w.Id).ToArray(); string roleNames = "purchaserId"; string timeField = "minutesToPurchaserComplete"; if (role == "Approver") { roleNames = "approverId"; timeField = "minutesToApprove"; } if (role == "AccountManager") { roleNames = "accountManagerId"; timeField = "minutesToAccountManagerComplete"; } var results = _client.Search <OrderTrackingEntity>( s => s.Index(index) .Size(size) .Query(a => a.Filtered(fq => fq.Query(q => q.MatchAll()).Filter(f => f.Range(r => r.OnField(o => o.OrderCreated).Greater(createdAfter).Lower(createBefore)) && f.Term(x => x.Status, "complete") && f.Terms(o => o.WorkgroupId, workgroupIds)))) .Aggregations( a => a.Terms("RolePercentiles", t => t.Field(roleNames) .Aggregations(b => b.Percentiles("PercentileTimes", p => p.Field(timeField) .Percentages(new double[] { 0, 25, 50, 75, 100 }))) ) .Average("AverageTimeToRoleComplete", avg => avg.Field(timeField))) ); var names = results.Aggs.Terms("RolePercentiles").Items.Select(n => n.Key + "; n=" + n.DocCount.ToString()).ToArray(); var percentilesInRole = results.Aggs.Terms("RolePercentiles") .Items.Select(user => (PercentilesMetric)user.Aggregations["PercentileTimes"]) .Select( uservalue => uservalue.Items.OrderBy(o => o.Percentile).Select(a => a.Value / 1440).ToArray() // converted to days to help with scale ) .ToList(); return(new OrderTrackingAggregationByRole { OrderTrackingEntities = results.Hits.Select(h => h.Source).ToList(), AverageTimeToRoleComplete = results.Aggs.Average("AverageTimeToRoleComplete").Value / 1440, NamesInRole = names, PercentilesForRole = percentilesInRole }); }
public IList <SearchResults.CommentResult> SearchComments(string searchTerm, int[] allowedIds) { var index = IndexHelper.GetIndexName(Indexes.Comments); var results = _client.Search <SearchResults.CommentResult>( s => s.Index(index).Query(q => q.QueryString(qs => qs.Query(searchTerm))) .Filter(f => f.Terms(x => x.OrderId, allowedIds)) .SortDescending(x => x.DateCreated) .Size(MaxSeachResults)); return(results.Hits.Select(h => h.Source).ToList()); }
public IList <SearchResults.OrderResult> SearchOrders(string searchTerm, int[] allowedIds) { var index = IndexHelper.GetIndexName(Indexes.OrderHistory); var results = _client.Search <OrderHistory>( s => s.Index(index).Query(q => q.QueryString(qs => qs.Query(searchTerm))) .Filter(f => f.Terms(x => x.OrderId, allowedIds)) .SortDescending(x => x.LastActionDate) .Size(MaxSeachResults)); return(results.Hits.Select(h => AutoMapper.Mapper.Map <SearchResults.OrderResult>(h.Source)).ToList()); }
public DateTime LastModified(Indexes index) { //TODO: no idea if this will work var indexName = IndexHelper.GetIndexName(index); var indexStats = _client.Indices.Stats(indexName); if (indexStats.IsValid && indexStats.Indices.ContainsKey(indexName)) { return(Convert.ToDateTime(indexStats.Indices[indexName].Total.Indexing.Time)); } return(Convert.ToDateTime(DateTime.MinValue)); }
public IList <OrderHistory> GetOrdersByWorkgroups(IEnumerable <Workgroup> workgroups, DateTime createdAfter, DateTime createdBefore) { var index = IndexHelper.GetIndexName(Indexes.OrderHistory); var workgroupIds = workgroups.Select(w => w.Id).ToArray(); var results = _client.Search <OrderHistory>( s => s.Index(index) .Filter(f => f.Range(r => r.OnField(o => o.DateCreated).Greater(createdAfter).Lower(createdBefore)) && f.Terms(o => o.WorkgroupId, workgroupIds)) .Size(int.MaxValue) ); return(results.Hits.Select(h => h.Source).ToList()); }
public IList <OrderHistory> GetOrdersByWorkgroups(IEnumerable <Workgroup> workgroups, DateTime createdAfter, DateTime createdBefore, int size = 1000) { var index = IndexHelper.GetIndexName(Indexes.OrderHistory); var workgroupIds = workgroups.Select(w => w.Id).ToArray(); var results = _client.Search <OrderHistory>( s => s.Index(index) .Size(size) .Query(f => f.DateRange(r => r.Field(o => o.DateCreated).GreaterThan(createdAfter).LessThan(createdBefore)) && f.Terms(o => o.Field(field => field.WorkgroupId).Terms(workgroupIds))) ); return(results.Hits.Select(h => h.Source).ToList()); }
/// <summary> /// return just the orders within the given date ranges /// </summary> /// <param name="orderids"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <param name="startLastActionDate"></param> /// <param name="endLastActionDate"></param> /// <param name="statusId"></param> /// <returns></returns> public IndexedList <OrderHistory> GetOrderHistory(int[] orderids, DateTime?startDate, DateTime?endDate, DateTime?startLastActionDate, DateTime?endLastActionDate, string statusId) { var filters = new List <FilterContainer>(); filters.Add(Filter <OrderHistory> .Terms(x => x.OrderId, orderids.Select(x => x.ToString(CultureInfo.InvariantCulture)))); if (!endLastActionDate.HasValue) { endLastActionDate = DateTime.UtcNow.AddDays(1); } if (startLastActionDate.HasValue) { filters.Add( Filter <OrderHistory> .Range( o => o.OnField(x => x.LastActionDate) .GreaterOrEquals(startLastActionDate.Value) .LowerOrEquals(endLastActionDate))); } if (startDate.HasValue) { filters.Add(Filter <OrderHistory> .Range(o => o.OnField(x => x.DateCreated).GreaterOrEquals(startDate))); } if (endDate.HasValue) { filters.Add(Filter <OrderHistory> .Range(o => o.OnField(x => x.DateCreated).LowerOrEquals(endDate))); } if (!string.IsNullOrWhiteSpace(statusId)) { filters.Add(Filter <OrderHistory> .Term(a => a.StatusId, statusId.ToLower())); } var orders = _client.Search <OrderHistory>( s => s.Index(IndexHelper.GetIndexName(Indexes.OrderHistory)) .Size(orderids.Length > MaxReturnValues ? MaxReturnValues : orderids.Length) .Filter(f => f.And(filters.ToArray()))); return(new IndexedList <OrderHistory> { Results = orders.Hits.Select(h => h.Source).ToList(), LastModified = DateTime.UtcNow.ToPacificTime().AddMinutes(-5).ToLocalTime() }); }
/// <summary> /// return just the orders within the given date ranges /// </summary> /// <param name="orderids"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <param name="startLastActionDate"></param> /// <param name="endLastActionDate"></param> /// <param name="statusId"></param> /// <returns></returns> public IndexedList <OrderHistory> GetOrderHistory(int[] orderids, DateTime?startDate, DateTime?endDate, DateTime?startLastActionDate, DateTime?endLastActionDate, string statusId) { var filters = new List <QueryContainer>(); if (!endLastActionDate.HasValue) { endLastActionDate = DateTime.UtcNow.AddDays(1); } if (startLastActionDate.HasValue) { filters.Add( Query <OrderHistory> .DateRange( o => o.Field(x => x.LastActionDate) .GreaterThanOrEquals(startLastActionDate.Value) .LessThanOrEquals(endLastActionDate))); } if (startDate.HasValue) { filters.Add(Query <OrderHistory> .DateRange(o => o.Field(x => x.DateCreated).GreaterThanOrEquals(startDate))); } if (endDate.HasValue) { filters.Add(Query <OrderHistory> .DateRange(o => o.Field(x => x.DateCreated).LessThanOrEquals(endDate))); } if (!string.IsNullOrWhiteSpace(statusId)) { filters.Add(Query <OrderHistory> .Term(a => a.StatusId, statusId.ToLower())); } var orders = _client.Search <OrderHistory>( s => s.Index(IndexHelper.GetIndexName(Indexes.OrderHistory)) .Query(q => q.Bool(b => b.Must(filters.ToArray()))) .Sort(s1 => s1.Descending(f => f.LastActionDate)) .PostFilter(f => f.ConstantScore(c => c.Filter(x => x.Terms(t => t.Field(q => q.OrderId).Terms(orderids))))) //used to be .Query(f => f.And(filters.ToArray())));. Now boolean must query/filter .Size(orderids.Length > MaxReturnValues ? MaxReturnValues : orderids.Length)); return(new IndexedList <OrderHistory> { Results = orders.Hits.Select(h => h.Source).ToList(), LastModified = DateTime.UtcNow.ToPacificTime().AddMinutes(-5).ToLocalTime() }); }
void WriteIndex <T>(IEnumerable <T> entities, Indexes indexes, Func <T, object> idAccessor = null, bool recreate = true) where T : class { if (entities == null) { return; } var index = IndexHelper.GetIndexName(indexes); if (recreate) //TODO: might have to check to see if index exists first time { _client.DeleteIndex(index); _client.CreateIndex(index); } var batches = entities.Partition(5000).ToArray(); //split into batches of up to 5000 foreach (var batch in batches) { var bulkOperation = new BulkDescriptor(); foreach (var item in batch) { T localItem = item; if (idAccessor == null) //if null let elasticsearch set the id { bulkOperation.Index <T>(b => b.Document(localItem).Index(index)); } else { var id = idAccessor(localItem); //Be tricky so we can handle number and string ids if (id is int) { bulkOperation.Index <T>(b => b.Document(localItem).Id((int)id).Index(index)); } else { bulkOperation.Index <T>(b => b.Document(localItem).Id(id.ToString()).Index(index)); } } } _client.Bulk(_ => bulkOperation); } }
public IList <SearchResults.CommentResult> GetLatestComments(int count, int[] allowedIds) { if (allowedIds.Length == 0) // no results if you aren't allowed to see anything { return(new List <SearchResults.CommentResult>()); } var index = IndexHelper.GetIndexName(Indexes.Comments); var results = _client.Search <SearchResults.CommentResult>( s => s.Index(index) .PostFilter(f => f.ConstantScore(c => c.Filter(x => x.Terms(t => t.Field(q => q.OrderId).Terms(allowedIds))))) .Sort(sort => sort.Descending(d => d.DateCreated)) .Size(count)); return(results.Hits.Select(h => h.Source).ToList()); }
public IList <SearchResults.CustomFieldResult> SearchCustomFieldAnswers(string searchTerm, int[] allowedIds) { if (allowedIds.Length == 0) // no results if you aren't allowed to see anything { return(new List <SearchResults.CustomFieldResult>()); } var index = IndexHelper.GetIndexName(Indexes.CustomAnswers); var results = _client.Search <SearchResults.CustomFieldResult>( s => s.Index(index) .Query( q => q.QueryString(qs => qs.Query(searchTerm)) ) .PostFilter(f => f.ConstantScore(c => c.Filter(x => x.Terms(t => t.Field(q => q.OrderId).Terms(allowedIds))))) .Sort(sort => sort.Descending(d => d.OrderId)) .Size(MaxSeachResults)); return(results.Hits.Select(h => h.Source).ToList()); }
public void UpdateOrderIndexes() { var lastUpdate = DateTime.UtcNow.ToPacificTime().AddMinutes(-10); //10 minutes ago. //var lastUpdate = DateTime.UtcNow.ToPacificTime().Subtract(TimeSpan.FromMinutes(10)); //10 minutes ago. IEnumerable <OrderHistory> orderHistoryEntries = null; IEnumerable <SearchResults.LineResult> lineItems = null; IEnumerable <SearchResults.CustomFieldResult> customAnswers = null; IEnumerable <OrderTrackingEntity> orderTrackingEntities = null; int[] updatedOrderIds; using (var conn = _dbService.GetConnection()) { updatedOrderIds = conn.Query <int>("select DISTINCT OrderId from OrderTracking where DateCreated > @lastUpdate", new { lastUpdate }).ToArray(); if (updatedOrderIds.Any()) { var updatedOrderIdsParameter = new StringBuilder(); foreach (var updatedOrderId in updatedOrderIds) { updatedOrderIdsParameter.AppendFormat("({0}),", updatedOrderId); } updatedOrderIdsParameter.Remove(updatedOrderIdsParameter.Length - 1, 1); //take off the last comma orderHistoryEntries = conn.Query <OrderHistory>(string.Format(@"DECLARE @OrderIds OrderIdsTableType INSERT INTO @OrderIds VALUES {0} select * from udf_GetOrderHistoryForOrderIds(@OrderIds)", updatedOrderIdsParameter)).ToList(); lineItems = conn.Query <SearchResults.LineResult>( "SELECT [Id], [OrderId], [RequestNumber], [Unit], [Quantity], [Description], [Url], [Notes], [CatalogNumber], [CommodityId], [ReceivedNotes], [PaidNotes] FROM vLineResults WHERE orderid in @updatedOrderIds", new { updatedOrderIds }).ToList(); customAnswers = conn.Query <SearchResults.CustomFieldResult>( "SELECT [Id], [OrderId], [RequestNumber], [Question], [Answer] FROM vCustomFieldResults WHERE orderid in @updatedOrderIds", new { updatedOrderIds }).ToList(); var orderInfo = conn.Query <OrderTrackingDto>( "select * from [vOrderTrackingIndex] WHERE orderid in @updatedOrderIds ORDER BY ActionDate DESC", new { updatedOrderIds }).ToList(); orderTrackingEntities = ProcessTrackingEntities(orderInfo); } } if (updatedOrderIds.Any()) { //Clear out existing lines and custom fields for the orders we are about to recreate _client.DeleteByQuery <SearchResults.LineResult>( q => q.Index(IndexHelper.GetIndexName(Indexes.LineItems)).Query(rq => rq.Terms(f => f.OrderId, updatedOrderIds))); _client.DeleteByQuery <SearchResults.CustomFieldResult>( q => q.Index(IndexHelper.GetIndexName(Indexes.CustomAnswers)) .Query(rq => rq.Terms(f => f.OrderId, updatedOrderIds))); _client.DeleteByQuery <OrderTrackingEntity>( q => q.Index(IndexHelper.GetIndexName(Indexes.OrderTracking)) .Query(rq => rq.Terms(f => f.OrderId, updatedOrderIds))); WriteIndex(orderHistoryEntries, Indexes.OrderHistory, e => e.OrderId, recreate: false); WriteIndex(lineItems, Indexes.LineItems, recreate: false); WriteIndex(customAnswers, Indexes.CustomAnswers, recreate: false); WriteIndex(orderTrackingEntities, Indexes.OrderTracking, o => o.OrderId, recreate: false); } }
public int NumRecords(Indexes index) { var indexName = IndexHelper.GetIndexName(index); return((int)_client.Status(i => i.Index(indexName)).Indices[indexName].IndexDocs.NumberOfDocs); }