c# - 使用按日期/月份分组的日期期间操作列表中的数据

Question

我有一个特定时间段(激活日期到结束日期)的服务销售列表。我需要生成按月分组的销售报告(例如 2012 年 4 月)。对于每个月，我想显示一个月的使用量和使用天数。

我的类(class):

 public class SaleMonth
 {
    public DateTime MonthYear { get; set; }//.ToString("Y")

    public int FullMonth { get; set; }
    public int DaysMonth { get; set; }

   public string TotalMonths { get { return String.Format("{0:N2}", 
                                  (((FullMonth * 30.5) + DaysMonth) / 30.5)); } }
 }

我尝试过的:

using (CompanyContext db = new CompanyContext())
{
   var saleList =  db.MySales.ToList();
   DateTime from = saleList.Min(s => s.ActivationDate), 
       to = saleList.Max(s => s.EndDate);

   for (DateTime currDate = from.AddDays(-from.Day + 1)
                                .AddTicks(-from.TimeOfDay.Ticks); 
                 currDate < to; 
                 currDate = currDate.AddMonths(1))
   {
      var sm = new SaleMonth
      {
          MonthYear = currDate,
          FullMonth = 0,
          DaysMonth = 0
      };

      var monthSell = saleList.Where(p => p.ActivationDate < currDate.AddMonths(1) 
                                              || p.EndDate > currDate);
      foreach (var sale in monthSell)
      {
         if (sale.ActivationDate.Month == sale.EndDate.Month
             && sale.ActivationDate.Year == sale.EndDate.Year)
         {//eg 4/6/13 - 17/6/13
             sm.DaysMonth += (sale.EndDate.Day - sale.ActivationDate.Day + 1);
         }
         else
         {
            if (sale.ActivationDate.Year == currDate.Year 
                  && sale.ActivationDate.Month == currDate.Month)
               sm.DaysMonth += (currDate.AddMonths(1) - sale.ActivationDate).Days;
            else if (sale.EndDate.Year == currDate.Year 
                  && sale.EndDate.Month == currDate.Month)
               sm.DaysMonth += sale.EndDate.Day;
            else if(sale.ActivationDate.Date <= currDate 
                  && sale.EndDate > currDate.AddMonths(1))
               sm.FullMonth++;
          }                               
       }
       vm.SaleMonthList.Add(sm);
   }
}

我觉得我在这里遗漏了一些东西，必须有一种更优雅的方式来做到这一点。

Here is a picture显示一些销售和由此产生的报告。

Answer 1

LINQ 确实包含一种对数据进行分组的方法。首先看一下这个声明:

// group by Year-Month
var rows = from s in saleList
    orderby s.MonthYear
    group s by new { Year = s.MonthYear.Year, Month = s.MonthYear.Month };

以上语句将获取您的数据并将其按年-月分组，这样它将为每个年-月组合创建一个主键，并将所有相应的 SaleMonth 项目创建到该组中.

当您掌握了这一点后，下一步就是使用这些组来计算您想要在每个组中计算的任何内容。因此，如果您只是想计算每个 Year-Month 的所有 FullMonths 和 DaysMonths 的总和，您可以这样做:

var rowsTotals = from s in saleList
    orderby s.MonthYear
    group s by new { Year = s.MonthYear.Year, Month = s.MonthYear.Month } into grp
    select new
    {
        YearMonth = grp.Key.Year + " " + grp.Key.Month,
        FullMonthTotal = grp.Sum (x => x.FullMonth),
        DaysMonthTotal = grp.Sum (x => x.DaysMonth)
    };

编辑:

再看看你在做什么，我认为这样做会更有效率:

// populate our class with the time period we are interested in
var startDate = saleList.Min (x => x.ActivationDate);
var endDate = saleList.Max (x => x.EndDate);

List<SaleMonth> salesReport = new List<SaleMonth>();
for(var i = new DateTime(startDate.Year, startDate.Month, 1); 
    i <= new DateTime(endDate.Year, endDate.Month, 1);
    i = i.AddMonths(1))
{
    salesReport.Add(new SaleMonth { MonthYear = i });
}

// loop through each Month-Year
foreach(var sr in salesReport)
{
    // get all the sales that happen in this month
    var lastDayThisMonth = sr.MonthYear.AddMonths(1).AddDays(-1);
    var sales = from s in saleList
        where lastDayThisMonth >= s.ActivationDate, 
        where sr.MonthYear <= s.EndDate
    select s;

    // calculate the number of days the sale spans for just this month
    var nextMonth = sr.MonthYear.AddMonths(1);
    var firstOfNextMonth = sr.MonthYear.AddMonths(1).AddDays(-1).Day;
    sr.DaysMonth = sales.Sum (x =>
        (x.EndDate < nextMonth ? x.EndDate.Day : firstOfNextMonth) -
            (sr.MonthYear > x.ActivationDate ? 
             sr.MonthYear.Day : x.ActivationDate.Day));

    // how many sales occur over the entire month
    sr.FullMonth = sales.Where (x => x.ActivationDate <= sr.MonthYear && 
                                nextMonth < x.EndDate).Count ();
}