使用MVC的动态列报表 [英] Dynamic columns report by using MVC

问题描述

我有一个表数据，如下所示:

I have a table data something like following:

 CompanyName     Amount        Date
 -------------------------------------
 CompanyA        150.00     2013-01-02
 CompanyB         40.00     2013-01-05
 CompanyC         75.00     2013-02-02
 CompanyA        250.00     2013-02-12
 CompanyB        100.00     2013-03-27
 CompanyC        350.00     2013-04-01
    .
    .
    .

我需要创建一个包含日期范围和所选公司的报告，如下所示:

I need to build a report with date range and selected company as following:

 Company    Jan-13  Feb-13  Mar-13  Apr-13  May-13  ...   Total
 ----------------------------------------------------------------
 Company A  $150    $239    $30     $500    $0      ...   $1,809
 Company B  $80     $0      $150    $200    $0      ...   $1,730
 Company C  $50     $30     $239    $0      $150    ...   $2,033
 Company D  $600    $150    $80     $30     $25     ...   $1,100
 Company E  $0      $50     $30     $150    $100    ...   $1,080
    .
    .
    .
    .
 MonthlyAmt $2,952   $1,377 $1,828  $2,470  $1,405  ...   $25,608

请注意，月"列是动态的，具体取决于输入的日期范围.

Please note that the Month columns are dynamic depend on the date range input.

那么SQL或Linq可以像这样甜美地检索数据吗?

So is SQL or Linq can retrieve data sweetly like that?

如果不能，我的粗略想法是先获得一行数据，如下所示:

If cannot, my rough idea is get the one row data first like following:

 Company A  Amount
 -----------------
 Jan-13     150
 Feb-13     239
 Mar-13     30
    .
    .
    .
    .

之后，使用循环收集所有数据并将其放入ViewBag数组中. 然后在查看页面"中分配数据. 但是，如果那样的话，将没有排序功能和页面列表功能. 我可以知道对此有更好的主意吗?或这就是局限性.

After that use a loop to collect all the data and put it in an array ViewBag. Then assign the data in View Page. But if like that, there will be no sorting function and pagelist function. May I know is there any better idea for this? Or this is the limitation.

非常感谢您.

推荐答案

在linqpad中检查了完整示例(按日期过滤不在范围内) 如果该月没有交易，则不会为所有公司提供所有月的记录.

Complete example checked in linqpad (filtering by date is left out of scope) and it does not produce records for all months for all companies if there are not transactions for that month.

void Main()
{
    var transactions = new List<transaction>(){
        new transaction() {
            volume = 5,
            company = "company1",
            date = DateTime.Parse("2012.12.15")
        },
        new transaction() {
            volume = 15,
            company = "company1",
            date = DateTime.Parse("2012.12.25")
        },
        new transaction() {
            volume = 25,
            company = "company2",
            date = DateTime.Parse("2012.12.25")
        },
        new transaction() {
            volume = 35,
            company = "company2",
            date = DateTime.Parse("2012.11.25")
        },
        new transaction() {
            volume = 45,
            company = "company3",
            date = DateTime.Parse("2012.12.25")
        },
    };

    transactions.Dump();

    var allTransactionsViewModel = transactions
            .GroupBy(t=>new {t.company,t.date.Month})
            .GroupBy(g=>g.Key.company, g=>new{g.Key.Month ,Total= g.Sum(t=>t.volume)})
            ;

    allTransactionsViewModel.Dump();        
}


class transaction {
 public int volume;
 public string company;
 public DateTime date;
}

这样您就可以

    table   
      @foreach(var g in allTransactionsViewModel){  
       tr
        @foreach(var s in g){  
          td @s.total /td  
        }  
       /tr  
    /table

这篇关于使用MVC的动态列报表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！