使用按日期/月分组的日期期间操作列表中的数据
本文关键字:日期 操作 列表 数据 | 更新日期: 2023-09-27 18:22:25
我有一个特定时间段(activationDate到endDate)的服务销售列表。我需要生成一个按月份-年份分组的销售报告(例如2012年4月)。每个月我都想展示一个月的使用量和天数。
我的班级:
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);
}
}
我有一种感觉,我在这里错过了一些东西,必须有一种更优雅的方式来做到这一点。
这是一张图片,展示了一些销售情况以及由此产生的报告。
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
项创建到该组中。
当你掌握了这一点后,下一步就是使用这些组来计算你想在每个组中计算的内容。因此,如果你只是想合计每个年度月份的所有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 ();
}
我同意Rem先生的观点,LINQ是我们的发展方向。由于你的计算很复杂,我也会创建一个辅助函数:
Func<DateTime, DateTime, bool> matchMonth = (date1, date2) =>
date1.Month == date2.Month && date1.Year == date2.Year;
然后,您可以创建一个用于计算的函数:
Func<MySale, DateTime, int> calcDaysMonth = (sale, currDate) =>
{
if (matchMonth(sale.ActivationDate, sale.EndDate))
{
return (sale.EndDate.Day - sale.ActivationDate.Day + 1);
}
else
{
if (matchMonth(sale.ActivationDate, currDate))
return (currDate.AddMonths(1) - sale.ActivationDate).Days;
else if (matchMonth(sale.EndDate, currDate)
return sale.EndDate.Day;
else
return 0;
}
}
如果你将这些技术与雷姆的结合起来,你应该有一个漂亮、可读、简洁的函数,可以为你收集数据,并且易于测试和调试。