c# - How to sort MMMYY on Chart.Js and display the values that match the month?

Question

this is going to be long and may get confusing but please bear with me.

My previous question I asked how to pull data from the database and display it in charts dynamically, I have managed to do it courtesy of @Rena. This is the solution https://stackoverflow.com/a/67967555/15397944. I am currently facing some challenges now, I would split my question into two parts but first I will show my model design.

Model

public class payable
{
    public int Id {get; set;}
    public decimal Amount {get; set;}
    public string Month {get; set;}
}

public class receivable
{
    public int Id {get; set;}
    public decimal Amount {get; set;}
    public string Month {get; set;}
}

public class payroll
{
    public int Id {get; set;}
    public decimal Amount {get; set;}
    public string Month {get; set;}
}

public class year
{
    public int Id {get; set;}
    public string Month {get; set;}
}

In the database, the tables have different values in different months.

For example if I call sum amount in payable, group by month and select month distinct, I would get something like

+-------+-------+
| JUL20 | $400  |
+-------+-------+
| SEP20 | $200  |
+-------+-------+
| OCT20 | $300  |
+-------+-------+
| DEC20 | $2000 |
+-------+-------+
| JAN21 | $1910 |
+-------+-------+
| FEB21 | $900  |
+-------+-------+
| MAR21 | $203  |
+-------+-------+
| APR21 | $194  |
+-------+-------+
| MAY21 | $1000 |
+-------+-------+

But if I do the same thing for receivable, I would get something like

+-------+-------+
| DEC20 | $1939 |
+-------+-------+
| JAN21 | $191  |
+-------+-------+
| FEB21 | $430  |
+-------+-------+
| MAR21 | $135  |
+-------+-------+
| APR21 | $945  |
+-------+-------+
| MAY21 | $1240 |
+-------+-------+

There are some old months from 2020 in some table but I only want my charts to show those of 2021, hence, the reason I created the table Year, to simply have Jan - Present month in 2021, the string is shown as MMMYY (e.g. JAN21). So then this creates an issue for me when displaying charts because I am unsure how to show only if payable.month = year.month

So here is part 1, in the charts I would simply like something like this: where Expense = payable + payroll.

But I was not able to do that which was fine for me, I could simply call all 3 and display them in 3 different charts which is okay for my project solution as well.

However, here is part 2. Following the solution, when I change the Handler method for month list to the respective tables or the year table, I will get a jumble of the months (arranged alphabetically) but then that means the the other two tables will be arranged wrongly.

    public JsonResult OnGetRevenueData()
    {
     <!-- Code omitted to shorten -->
        var monthList = _context.year.Select(a => a.Month).Distinct().ToArray();
        return new JsonResult(new { revenue = countRevenue, expense = countExpense, month = monthList });
    }
}

For example, Receivable chart would look like this

Because the data is followed according to this And I know this because I got this by changing the code to

    public JsonResult OnGetRevenueData()
    {
     <!-- Code omitted to shorten -->
        var monthList = _context.receivable.Select(a => a.Month).Distinct().ToArray();
        return new JsonResult(new { revenue = countRevenue, expense = countExpense, month = monthList });
    }
}

As you can see it is simply following the array, so even if for the first image where the label of months follow the year table, and I OrderBy(year.Id) which would give me JAN-MAY correctly, the values would not follow as JAN would then be receivable's FEB and FEB would then become APR so on and so forth.

So my questions are, is there a way to achieve part 1, where the month of receivable, payable and payroll table match the year table months which is the label for the chart. Otherwise, is it possible to do it in part 2 instead?

Any help is much appreciated and thank you for reading until the end.

---

Edit: After implementing solution but the numbers do not tally with DataTables as well as Database SQL.

As you can see, for DataTables I filtered it to show MAR21, and using footerCallback I summed the values in the column Amount, which gives me the correct sum

footerCallback code

"footerCallback": function (row, data, start, end, display) 
{
   var api = this.api(), data;
   var numberRenderer = $.fn.dataTable.render.number(',', '.', 2, '$').display;
// Remove the formatting to get integer data for summation
   var intVal = function (i) 
{
   return typeof i === 'string' ? 
      i.replace(/[\$,]/g, '') * 1 :
         typeof i === 'number' ?
            i : 0;
};

   Total = api
      .column(4, { page: 'current' })
      .data()
      .reduce(function (a, b) {
      return intVal(a) + intVal(b);
      }, 0);
   $(api.column(4).footer()).html(
      'Total : ' + numberRenderer(Total)
   );
}

This is confirmed that it is right by comparing it with the database itself using SQL

However, on the solution after the graph is implemented, this is what I got Image shows number to be 757210.83.

As you can see from the screenshot as well, the MAY column doesn't have expense but in fact in the Database there are values.

Upon checking further, the value 757210.83, belongs to May. So the values are summing correctly but displaying wrongly. MAY numbers shown on MAR column and MAR numbers showing on MAY column. The others as well - JAN numbers showing on APR column, FEB numbers showing on JAN column, APR numbers showing on FEB column. So, I'm pretty confused as to why it is the way it is, it isn't sorted by alphabetical either since APR number isn't in JAN column.

But this still does not explain the missing Expense column as all of them have expense (payroll and/or payable).

标签： c#linqasp.net-corechart.jsrazor-pages

Answer 1

You could change your linq like below:

public JsonResult OnGetRevenueData()
{
    var year = _context.year.Select(a => a.Month).ToList();
    var Revenue = (from t in _context.Revenue
                    where year.Contains(t.Month)
                    group t by new {  Month = t.Month} into g                              
                    select new 
                    {
                        Amount = g.Sum(a => a.Amount),
                        Month = g.Key.Month
                    }).ToList();
    var countRevenue = Revenue.OrderBy(a => DateTime.ParseExact(a.Month, "MMMyy", CultureInfo.InvariantCulture).Month)
                                .Select(a=>a.Amount)
                                .ToList();
    var countPayroll = (from t in _context.payroll
                        where year.Contains(t.Month)
                        group t by new { Month = t.Month } into g
                        select new {
                            Amount = g.Sum(a => a.Amount),
                            Month = g.Key.Month
                        }).ToList();
    var countPayable = (from t in _context.payable
                        where year.Contains(t.Month)
                        group t by new { Month = t.Month } into g
                        select new
                        {
                            Amount = g.Sum(a => a.Amount),
                            Month = g.Key.Month
                        }).ToList();
      //change here......
    var leftOuterJoin = from a in countPayable
                        join b in countPayroll on a.Month equals b.Month into temp
                        from count in temp.DefaultIfEmpty()
                        select new
                        {
                            Month = a.Month,
                            Amount = a.Amount
                        };
    var rightOuterJoin =
        from b in countPayroll
        join a in countPayable on b.Month equals a.Month into temp
        from count in temp.DefaultIfEmpty()
        select new
        {
            Month = b.Month,
            Amount = b.Amount
        };
    var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);
    var Expense = (from t in fullOuterJoin
                    group t by new
                    {
                        Month = t.Month
                    } into g
                    select new
                    {
                        Amount = g.Sum(a => a.Amount),
                        Month = g.Key.Month
                    }
                    ).ToList();
    var countExpense = Expense.OrderBy(a => DateTime.ParseExact(a.Month, "MMMyy", CultureInfo.InvariantCulture).Month)
                                .Select(a => a.Amount)
                                .ToList();

    var yearList = ((from y in _context.year
                        select y.Month
                        )
                    .AsEnumerable()
                    .OrderBy(s => DateTime.ParseExact(s, "MMMyy", CultureInfo.InvariantCulture).Month)
                    ).ToArray();
    return new JsonResult(new { revenue = countRevenue, expense = countExpense, month = yearList });
}

Result:

Bar Chart:

payable table:

payroll table:

Revenue table:

year table: