/// <summary> /// Finds transaction counts for a specific product in every month. /// </summary> /// <param name="mvanumber">required</param> /// <param name="productID">required</param> /// <param name="startDateVal">required</param> /// <param name="endDateVal">required</param> /// <returns>A list that contains a count item for every month inside the time period.</returns> public List<MonthItem> GetTransactionCountsPerMonthForProduct(int mvanumber, string productID, int startDateVal, int endDateVal) { var monthItems = new List<MonthItem>(); DateTime dtLimit = DateTime.UtcNow.Date.AddDays(-Constants.DaysToKeepTransactions); List<string> filters = new List<string>(); filters.Add("mvaNumber eq " + mvanumber); filters.Add("date ge " + startDateVal); filters.Add("date le " + endDateVal); filters.Add("productID eq '" + productID + "'"); var sp = new SearchParameters(); sp.Top = 10000; sp.Filter = string.Join(" and ", filters); DocumentSearchResult<TotalsSearchItem> response2 = TotalsIndexClient.Documents.Search<TotalsSearchItem>("*", sp); while (true) { foreach (var res in response2.Results) { var sDateVal = res.Document.Date.ToString(); var item = monthItems.FirstOrDefault(itm => itm.Date == sDateVal); if (item == null) { item = new MonthItem { Date = sDateVal, Counts = new List<ProductTransactionCount> { new ProductTransactionCount { ProductID = productID, Total = 0 } } }; monthItems.Add(item); } item.Counts.First().Total += res.Document.Amount.Value; } if (response2.ContinuationToken == null) { break; } response2 = TotalsIndexClient.Documents.ContinueSearch<TotalsSearchItem>(response2.ContinuationToken); } filters = new List<string>(); filters.Add("mvaNumber eq " + mvanumber); filters.Add("date ge " + startDateVal); filters.Add("date le " + endDateVal); filters.Add("dateTime ge " + dtLimit.ToString(Constants.ODataDateFormat)); filters.Add("productID eq '" + productID + "'"); sp = new SearchParameters(); sp.Top = 0; sp.Filter = string.Join(" and ", filters); sp.Facets = new List<string> { "date,count:1000,sort:value" }; DocumentSearchResult<TransactionSearchItem> response = TransactionIndexClient.Documents.Search<TransactionSearchItem>("*", sp); var facetItems = response.Facets.First().Value; var monthItems2 = facetItems.Select(f => new MonthItem { Date = f.Value.ToString(), Counts = new List<ProductTransactionCount> { new ProductTransactionCount { ProductID = productID, Total = f.Count ?? 0 } } }).ToList(); //combine the results from 2 different sources: foreach (var item in monthItems2) { var foundItem = monthItems.FirstOrDefault(m => m.Date == item.Date); if(foundItem == null) { monthItems.Add(item); } else { foundItem.Counts.First().Total += item.Counts.First().Total; } } return monthItems.OrderBy(itm => itm.Date).ToList(); }
/// <summary> /// Finds customer's transaction counts per product per month. /// </summary> /// <param name="mvanumber">required</param> /// <param name="customerInternalID">required</param> /// <param name="startDateVal">required</param> /// <param name="endDateVal">required</param> /// <returns>List that contains a items for every month inside the time period. The items contain transaction counts per item.</returns> public List<MonthItem> GetTransactionCountsPerMonthPerProduct(int mvanumber, string customerInternalID, int startDateVal, int endDateVal) { var monthItems = new List<MonthItem>(); DateTime dtLimit = DateTime.UtcNow.Date.AddDays(-Constants.DaysToKeepTransactions); DateTime dtLimitWoDay = dtLimit.AddDays(-dtLimit.Day + 1); //remove day component from DateTime var dtStart = DateTime.ParseExact(startDateVal.ToString(), Constants.DateStringFormat, CultureInfo.InvariantCulture); var dtEnd = DateTime.ParseExact(endDateVal.ToString(), Constants.DateStringFormat, CultureInfo.InvariantCulture); //do a query only for months that are inside the time period where individual transaction results should be used dtStart = dtLimitWoDay > dtStart ? dtLimitWoDay : dtStart; //Need to do a search query for each month overlapping the search period. //There's no other way to get per product transactions for multiple months. for (DateTime d = dtStart; d <= dtEnd; d = d.AddMonths(1)) { string strDate = d.ToString(Constants.DateStringFormat); var filters = new List<string>(); filters.Add("mvaNumber eq " + mvanumber); filters.Add("date eq " + strDate); filters.Add("dateTime ge " + dtLimit.ToString(Constants.ODataDateFormat)); if (!string.IsNullOrWhiteSpace(customerInternalID)) { filters.Add("clientInternalID eq '" + customerInternalID + "'"); } var sp = new SearchParameters(); sp.Top = 0; sp.Filter = string.Join(" and ", filters); sp.Facets = new List<string> { "productID,count:100" }; DocumentSearchResult<TransactionSearchItem> response = TransactionIndexClient.Documents.Search<TransactionSearchItem>("*", sp); var facetItems = response.Facets.First().Value; monthItems.Add(new MonthItem { Date = strDate, Counts = facetItems.Select(f => new ProductTransactionCount { ProductID = f.Value.ToString(), Total = f.Count ?? 0 }).ToList() }); } var filters2 = new List<string>(); filters2.Add("mvaNumber eq " + mvanumber); filters2.Add("date ge " + startDateVal); filters2.Add("date le " + endDateVal); if (!string.IsNullOrWhiteSpace(customerInternalID)) { filters2.Add("clientInternalID eq '" + customerInternalID + "'"); } var sp2 = new SearchParameters(); sp2.Top = 10000; sp2.Filter = string.Join(" and ", filters2); DocumentSearchResult<TotalsSearchItem> response2 = TotalsIndexClient.Documents.Search<TotalsSearchItem>("*", sp2); while (true) { foreach (var res in response2.Results) { var sDateVal = res.Document.Date.ToString(); var mItem = monthItems.FirstOrDefault(itm => itm.Date == sDateVal); if (mItem == null) { mItem = new MonthItem { Date = sDateVal, Counts = new List<ProductTransactionCount>() }; monthItems.Add(mItem); } var pItem = mItem.Counts.FirstOrDefault(itm => itm.ProductID == res.Document.ProductID); if(pItem == null) { pItem = new ProductTransactionCount { ProductID = res.Document.ProductID, Total = 0, Included = 0 }; //need to ensure that mItem.Counts is actually a List<T> ((List<ProductTransactionCount>)mItem.Counts).Add(pItem); } pItem.Total += res.Document.Amount.Value; } if (response2.ContinuationToken == null) { break; } response2 = TotalsIndexClient.Documents.ContinueSearch<TotalsSearchItem>(response2.ContinuationToken); } foreach (var mItem in monthItems) { mItem.Counts = mItem.Counts.OrderByDescending(itm => itm.Total); } return monthItems.OrderBy(itm => itm.Date).ToList(); }