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);
}