asp.net mvc , raw sql : Display data in Views generated using aggregate function

Question

I am using ASP.NET MVC, EF 6 and SQL Server 2008.

I want to generate a view which would show sum of all the sales in each day for a particular month in a particular year.

I found LINQ query very complicated in such type of job, So I used a raw SQL query. I wrote query and tested in SQL server and it worked fine.

select  
    YEAR(Date) as Year, 
    MONTH(Date) as month, 
    DAY(Date) as date,   
    SUM(GrandTotal) as Total  
from 
    Sales 
where 
    Year(Date) = 2014 
    and MONTH(Date) = 12 
group by 
    DAY(Date), YEAR(Date), MONTH(date)

Result

Well currently I don't have much data. But it looks like I got what I wanted from a query.

I wrote a controller for this purpose and now I have no idea how to display this data in View.

public ActionResult MonthlySalesByDate()
{
        DateTime today = DateTime.Now.Date;
        int _year = today.Year;
        int _month = today.Month;

        //raw sql query 
        string query = "select SUM(GrandTotal) as Total, DAY(Date) as date, MONTH(Date) as month, YEAR(Date) as Year from Sales where Year(Date) = " + _year + " and MONTH(Date) =" + _month + "  Group by DAY(Date), YEAR(Date), MONTH(date)";

        //executing raw sql query 
        var _model = db.Stocks.SqlQuery(query).ToList();           

        return View(_model);
}

Please help me out with this. If there is better way of doing this or if I am making mistakes, please let me know.

How do you want to display it? A heading for the month and year and a table? What should the table look like (columns for days and totals, or columns for each day)? — user3559349
– user3559349, Commented Oct 24, 2015 at 5:03
I want to display as columns : | Date | TotalSales | . It would be much better if date column includes all the dates in that month in ascending order and the respective total sales and also displays 0 if there is no sales for a particular date. — Avishekh Bharati
– Avishekh Bharati, Commented Oct 24, 2015 at 5:23
A linq query to get the days and sum of its totals is relatively simple if you only want the days with amounts (i.e to mimic you sql query). I'll have a think about how to generate it with all days in the month — user3559349
– user3559349, Commented Oct 24, 2015 at 5:32
And how am i suppose to display that in view?? Do I have to create a view model for that ?? Well, the main requirement is to generate view with all days in the month and respective sales . — Avishekh Bharati
– Avishekh Bharati, Commented Oct 24, 2015 at 5:36
Yes, you will need a view model. I will post an answer shortly. — user3559349
– user3559349, Commented Oct 24, 2015 at 5:37

score 2 · Accepted Answer · 2015-10-24 08:43:33Z

Start by creating view models to represent what you want to display in the view

public class DayTotalVM
{
  public int Day { get; set; }
  [DisplayFormat(DataFormatString = "{0:C}")]
  public decimal Total { get; set; }
}
public class SalesVM
{
  [DisplayFormat(DataFormatString = "{0:MMMM yyyy}")]
  public DateTime Date { get; set; }
  public List<DayTotalVM> Days { get; set; }
}

The sql query you have can be generated in linq and projected into your view models using

int year = 2014;
int month = 12;
var query = db.Sales.Where(x => x.Date.Year == year && x.Date.Month == month)
    .GroupBy(x => x.Date).Select(g => new DayTotalVM
    {
        Day = g.Key.Day,
        Total = g.Sum(x => x.Total)
    })

However this will only give you the 2 items as per you above image, but from the comments you want to display all days in the month, so you can add

int daysInMonth = DateTime.DaysInMonth(year, month);
List<DayTotalVM> days = new List<DayTotalVM>();
for(int i = 1; i < daysInMonth + 1; i++)
{
    DayTotalVM item = new DayTotalVM () { Day = i };
    DayTotalVM ex = query.Where(x => x.Day == i).FirstOrDefault();
    if (ex != null)
    {
        item.Total = ex.Total;
    }
    days.Add(item);
}

and finally initialize and return your view model

SalesVM model = new SalesVM();
{
  Date = new DateTime(year, month, 1),
  Days = days 
}
return View(model);

And then the view would be

@model SalesVM
@Html.DisplayFor(m => m.Date);
<table>
  @for(int i = 0; i < Model.Days.Count; i++)
  {
    <tr>
      <td>@Html.DisplayFor(m => m.Days[i].Day)</td>
      <td>@Html.DisplayFor(m => m.Days[i].Total)</td>
    </tr>
  }
</table>

Edit

The for loop could be replace by using a GroupJoin()

public ActionResult MonthlySalesByDate(int year, int month)
{
  int daysInMonth = DateTime.DaysInMonth(year, month);
  var days = Enumerable.Range(1, daysInMonth);
  var query = db.Sales.Where(x => x.Date.Year == year && x.Date.Month == month).Select(g => new
  {
    Day = g.Date.Day,
    Total = g.Total
  });
  var model = new SalesVM
  {
    Date = new DateTime(year, month, 1),
    Days = days.GroupJoin(query, d => d, q => q.Day, (d, q) => new DayTotalVM
    {
      Day = d,
      Total = q.Sum(x => x.Total)
    }).ToList()
  };
  return View(model);
}

Collectives™ on Stack Overflow

asp.net mvc , raw sql : Display data in Views generated using aggregate function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related