c# - 将每个日期范围内的金额拆分为每月/每年的金额

Question

我基本上是在尝试做与 here 相同的事情只是我想在 C# 中而不是在 SQL 中执行此操作。

我有一个类:

public class AmountPerPeriod
{
    public int Id { get; set; }
    public DateTime Startdate { get; set; }
    public DateTime Enddate { get; set; }
    public decimal Amount { get; set; }
}

对于这个例子，假设我填充了一个 AmountPerPeriod 项目列表:

var lstAmountPerPeriod = new List<AmountPerPeriod>()
            {
                new AmountPerPeriod
                {
                    Id = 1,
                    Startdate = new DateTime(2019, 03, 21),
                    Enddate = new DateTime(2019, 05, 09),
                    Amount = 10000
                },
                new AmountPerPeriod
                {
                    Id = 2,
                    Startdate = new DateTime(2019, 04, 02),
                    Enddate = new DateTime(2019, 04, 10),
                    Amount = 30000
                },
                new AmountPerPeriod
                {
                    Id = 3,
                    Startdate = new DateTime(2018, 11, 01),
                    Enddate = new DateTime(2019, 01, 08),
                    Amount = 20000
                }
            };

我希望我的输出是一个 AmountPerMonth 类列表，如下所示:

public class AmountPerMonth
{
    public int Id { get; set; }
    public int Year { get; set; }
    public int Month { get; set; }
    public decimal Amount { get; set; }
}

就像我应该尝试的那样，我得到了一种工作方法，我觉得它的方法很复杂。这种提供正确结果的方法如下所示:

var result = new List<AmountPerMonth>();

    foreach (var item in lstAmountPerPeriod)
    {
        if (item.Startdate.Year == item.Enddate.Year && item.Startdate.Month == item.Enddate.Month)
        {
            result.Add(new AmountPerMonth
            {
                Amount = item.Amount,
                Id = item.Id,
                Month = item.Startdate.Month,
                Year = item.Startdate.Year
            });
        }
        else
        {
            var numberOfDaysInPeriod = (item.Enddate - item.Startdate).Days+1;
            var amountPerDay = item.Amount / numberOfDaysInPeriod;
            var periodStartDate = item.Startdate;

            bool firstPeriod = true;

            while (periodStartDate.ToFirstDateOfMonth() <= item.Enddate.ToFirstDateOfMonth())
            {
                if (firstPeriod)
                {
                    result.Add(new AmountPerMonth
                    {
                        Amount = ((periodStartDate.ToLastDateOfMonth()-periodStartDate).Days+1)*amountPerDay,
                        Id = item.Id,
                        Month = periodStartDate.Month,
                        Year = periodStartDate.Year
                    });
                }
                else if (periodStartDate.Month != item.Enddate.Month)
                {
                    result.Add(new AmountPerMonth
                    {
                        Amount = ((periodStartDate.ToLastDateOfMonth()-periodStartDate.ToFirstDateOfMonth()).Days+1) * amountPerDay,
                        Id = item.Id,
                        Month = periodStartDate.Month,
                        Year = periodStartDate.Year
                    });
                }
                else
                {
                    result.Add(new AmountPerMonth
                    {
                        Amount = ((item.Enddate - periodStartDate.ToFirstDateOfMonth()).Days+1) * amountPerDay,
                        Id = item.Id,
                        Month = periodStartDate.Month,
                        Year = periodStartDate.Year
                    });
                }

                periodStartDate = periodStartDate.AddMonths(1);

                firstPeriod = false;
            }
        }
    }


// assert using fluentassertions
result.Count.Should().Be(7);
result.First().Amount.Should().Be(2200);
result.Last().Amount.Should().BeApproximately(2318.84M, 2);

// list with result basically should contain:
// ID |month |year   |amount
// ---|------|-------|--------
// 1  |3     | 2019  | 2200.00
// 1  |4     | 2019  | 6000.00
// 1  |5     | 2019  | 1800.00
// 2  |4     | 2019  |30000.00
// 3  |11    | 2018  | 8695.65
// 3  |12    | 2018  | 8985.51
// 3  |1     | 2019  | 2318.84

正如我所说，应该有更简单的方法，甚至可能使用 LINQ。有人有什么建议吗？

提前致谢

Answer 1

使用 LINQ 会容易得多。

var output =
    from app in lstAmountPerPeriod
    let days = (int)app.Enddate.Date.Subtract(app.Startdate.Date).TotalDays + 1
    from day in Enumerable.Range(0, days)
    let daily = new AmountPerPeriod()
    {
        Id = app.Id,
        Startdate = app.Startdate.AddDays(day),
        Enddate = app.Startdate.AddDays(day),
        Amount = app.Amount / days
    }
    group daily.Amount by new
    {
        daily.Id,
        daily.Startdate.Year,
        daily.Startdate.Month
    } into gds
    select new AmountPerMonth()
    {
        Id = gds.Key.Id,
        Year = gds.Key.Year,
        Month = gds.Key.Month,
        Amount = gds.Sum(),
    };

此查询返回: