BPM sum and group by

Kinetic ERP
1

I am writing a Pre-Processing BPM on Erp.BO.JobEntry.Update.

I want to sum Sales by Year and publish to a message box.

Currently I am returning total sales but I want to return the results by year based on ReqDate:

Sales = Db.OrderRel.Where(r => r.Company == Session.CompanyID
                              && r.PartNum == ttJobHead_xRow.PartNum).Sum(r => r.OurJobShippedQty + r.OurStockShippedQty);

Can anyone assist?

2

My long winded way can’t be efficient:

int currentYear = DateTime.Now.Year;
    DateTime Year1 = new DateTime(currentYear, 1, 1).AddYears(-2);
    DateTime Year2 = new DateTime(currentYear, 1, 1).AddYears(-1);
    DateTime Year3 = new DateTime(currentYear, 1, 1);
    
    decimal Sales1 = Db.OrderRel.Where(r => r.Company == Session.CompanyID
                                      && r.PartNum == ttJobHead_xRow.PartNum
                                      && r.ReqDate >= Year1
                                      && r.ReqDate < Year2).Sum(r => r.OurJobShippedQty + r.OurStockShippedQty);
                                      
    decimal Sales2 = Db.OrderRel.Where(r => r.Company == Session.CompanyID
                                      && r.PartNum == ttJobHead_xRow.PartNum
                                      && r.ReqDate >= Year2
                                      && r.ReqDate < Year3).Sum(r => r.OurJobShippedQty + r.OurStockShippedQty);
                                      
    decimal Sales3 = Db.OrderRel.Where(r => r.Company == Session.CompanyID
                                      && r.PartNum == ttJobHead_xRow.PartNum
                                      && r.ReqDate >= Year3).Sum(r => r.OurJobShippedQty + r.OurStockShippedQty);
3

It cuts against the grain of code-dependents, but you could call a BAQ with parameters. :person_shrugging:

4

Hello @rppmorris,

You can dynamically add a calculation with one DB call, as shown in the snap code below.
Instead of making three separate database calls to calculate sales for each year, we can optimize by fetching all the required data in a single DB call.

int numberOfPastYears = 5; // Specify the number of past years you want to consider
int currentYear = DateTime.Now.Year;
DateTime startDate = new DateTime(currentYear - numberOfPastYears + 1, 1, 1);
DateTime endDate = new DateTime(currentYear + 1, 1, 1); // Next year's start date

var salesByYear = Db.OrderRel
    .Where(r => r.Company == Session.CompanyID
             && r.PartNum == ttJobHead_xRow.PartNum
             && r.ReqDate >= startDate
             && r.ReqDate < endDate)
    .GroupBy(r => r.ReqDate.Year)
    .Select(group => new
    {
        Year = group.Key,
        Sales = group.Sum(r => r.OurJobShippedQty + r.OurStockShippedQty)
    })
    .ToList();

// Initialize dictionary to store sales for each year
var salesForYears = new Dictionary<int, decimal>();

// Populate the dictionary with sales for each year
for (int year = currentYear - numberOfPastYears + 1; year <= currentYear; year++)
{
    salesForYears[year] = salesByYear.FirstOrDefault(s => s.Year == year)?.Sales ?? 0;
}

// Create message to display sales by year
StringBuilder message = new StringBuilder();
message.AppendLine("Sales by Year:");
foreach (var kvp in salesForYears)
{
    message.AppendLine($"Year: {kvp.Key}, Sales: {kvp.Value}");
}

// Display the message box
MessageBox.Show(message.ToString(), "Sales by Year", MessageBoxButtons.OK, MessageBoxIcon.Information);
5
  1. Grab all the orders from the beginning and end of your date range
  2. Group by Year
  3. Sum totals
  4. Copy to local memory with ToList();
var orders = Db.OrderRel.Where(r => 
                               r.Company == Session.CompanyID &&
                               r.PartNum == ttJobHead_xRow.PartNum &&
                               (int)r.ReqDate.Value.Year >= beginYear &&
                               (int)r.ReqDate.Value.Year <= endYear)
                        .GroupBy(g => (int)g.ReqDate.Value.Year)
                        .Select(s => new
                        {
                            yr = s.Key,
                            qty = s.Sum(x => x.OurJobShippedQty + 
                                             x.OurStockShippedQty)
                        })
                        .ToList();

EDIT, this is basically the same query as @Dnyanraj_Patil. His is slightly more straightforward (not bothering with casting dates into ints), but the gist is identical.

6

Thank you for reply.

int numberOfPastYears = 5; // Specify the number of past years you want to consider
    int currentYear = DateTime.Now.Year;
    DateTime startDate = new DateTime(currentYear - numberOfPastYears + 1, 1, 1);
    DateTime endDate = new DateTime(currentYear + 1, 1, 1); // Next year's start date

    var salesByYear = Db.OrderRel
    .Where(r => r.Company == Session.CompanyID
             && r.PartNum == ttJobHead_xRow.PartNum
             && r.ReqDate >= startDate
             && r.ReqDate < endDate)
    .GroupBy(r => r.ReqDate.Year)
    .Select(group => new
    {
        Year = group.Key,
        Sales = group.Sum(r => r.OurJobShippedQty + r.OurStockShippedQty)
    })
    .ToList();

When I try to compile it errors with:
The type arguments for method ‘Queryable.GroupBy<TSource, TKey>(IQueryable, Expression<Func<TSource, TKey>>)’ cannot be inferred from the usage. Try specifying the type arguments explicitly.

7

@jtownsend - thank you for the reposnse.

Code working.

8

@Dnyanraj_Patil - thank you for the response.

Code working.

1 Like
9

@rppmorris , Great to hear that.