0

This is my code which is working currently I would like to know if these two things are possible when using mvc charts? And how to go about doing them

My controller

public ActionResult CharterColumn()
{
    ArrayList xValue = new ArrayList();
    ArrayList yValue = new ArrayList();

    var results = (from c in db.Timesheets select c);

    results.ToList().ForEach(rs => xValue.Add(rs.Date));
    //I want to format this to show the only the months on the x axis

    results.ToList().ForEach(rs => yValue.Add(rs.Hours));
    //And I want to calculate the sum of the hours for each month worked by a specific employee

    new Chart(width: 800, height: 400, theme: ChartTheme.Yellow)
    .AddTitle("Test")
    .AddSeries("Default", chartType: "Column", xValue: xValue, yValues: yValue)
    .Write("bmp");

    return null;
}

and my view

<div>
   <img src= "@Url.Action("CharterColumn")" alt="Chart"/>
</div>
adiga
  • 34,372
  • 9
  • 61
  • 83
Kimberly
  • 17
  • 6

1 Answers1

1

You can group the TimeSheet records by month and year like this:

DateTimeFormatInfo dtfi = new DateTimeFormatInfo();

// gets the records grouped based on Year and Month. 
// Creates an object of IEnumerable<IGrouping<>> type
var groupedByMonth = result
                        .OrderByDescending(x => x.Date)
                        .GroupBy(x => new { x.Date.Year, x.Date.Month }).ToList();


// gets the months names in a list
List<string> monthNames = groupedByMonth
                    .Select(a => dtfi.GetAbbreviatedMonthName(a.Key.Month))
                    .ToList();

// gets the total hours per month
List<int> hoursPerMonth = groupedByMonth
                        .Select(a => a.Sum(p => p.Hours))
                        .ToList();


ArrayList xValue = new ArrayList(monthNames);
ArrayList yValue = new ArrayList(hoursPerMonth);

I'm using DateTimeFormatInfo to get the month's name. You can also achieve this without this:

List<string> monthNames = groupedByMonth
                    .Select(a => a.FirstOrDefault().Date.ToString("MMMM"))
                    .ToList();
adiga
  • 34,372
  • 9
  • 61
  • 83
  • An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code Additional information: LINQ to Entities does not recognize the method 'System.String GetAbbreviatedMonthName(Int32)' method, and this method cannot be translated into a store expression. Hi i have tried it am getting this error – Kimberly Sep 16 '17 at 12:25
  • @Kimberly try doing it without the `dtfi`, as mentioned at the end. I think you'd have to do `.ToList()` before the linq query. – adiga Sep 16 '17 at 12:27
  • Additional information: LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression. – Kimberly Sep 16 '17 at 12:33
  • Still getting this – Kimberly Sep 16 '17 at 12:33
  • @Kimberly add a `ToList()` after `result` in this line: `var groupedByMonth = result.ToList() .OrderByDescending(x => x.Date)` – adiga Sep 16 '17 at 12:34
  • sorry to trouble you again... the months on the chart are appearing in a weird format and also the bars are not appearing – Kimberly Sep 16 '17 at 14:48