I am wondering how i can simplify my queries as I know I am carrying out bad practice and going the long way about things.

Basically, I have shifts being entered into an app on a weekly basis, and i am calculating the hours worked each week. I am wanting to know how do I go about getting the start date of a week and the end date of a week based on a known shift, which has been entered into the app (ShiftDate).

Quick example below of 4 weeks, where I have worked out the start and end of the week using moment.js and passed them down to the c# function (represented by vStart, vEnd etc...).

I am struggling how to obtain start and end of a week that contains x.ShiftDate

Week1 = pg.Sum(x => x.ShiftDate >= vStart1 && x.ShiftDate <= vEnd1 ? x.HoursWorked : 0),
                             Week2 = pg.Sum(x => x.ShiftDate >= vStart2 && x.ShiftDate <= vEnd2 ? x.HoursWorked : 0),
                             Week3 = pg.Sum(x => x.ShiftDate >= vStart3 && x.ShiftDate <= vEnd3 ? x.HoursWorked : 0),
                             Week4 = pg.Sum(x => x.ShiftDate >= vStart4 && x.ShiftDate <= vEnd4 ? x.HoursWorked : 0),

I want to figure out how to just write the sum function once, without having to repeat for each week within the month

CodePudding user response：

You can do this:

var weeklyHours = 
from data in SomeList
group t by new { WeekNumber = (t.SomeTargetDate - firstDay).Days / 7} into weeks
select new
{
    WeekNumber = weeks.Key.WeekNumber,
    Minutes = weeks.Sum(t => t.Minutes)  // here your logic of how minutes are calculated can come
};

CodePudding user response：

Without your class data object code this is what I come up with:

namespace ShiftWork
{
    public class UnitTest1
    {
        [Fact]
        public void Test1()
        {
            DateTime vStart1 = DateTime.Now;
            DateTime vEnd1 = vStart1.AddDays(6);
            DateTime vStart2 = vEnd1.AddDays(1);
            DateTime vEnd2 = vStart2.AddDays(6);
            DateTime vStart3 = vEnd2.AddDays(1);
            DateTime vEnd3 = vStart3.AddDays(6);
            DateTime vStart4 = vEnd3.AddDays(1);
            DateTime vEnd4 = vStart4.AddDays(6);

            var startAndEnd = new List<Tuple<DateTime, DateTime>>();
            startAndEnd.Add(new Tuple<DateTime, DateTime>(vStart1, vEnd1));
            startAndEnd.Add(new Tuple<DateTime, DateTime>(vStart2, vEnd2));
            startAndEnd.Add(new Tuple<DateTime, DateTime>(vStart3, vEnd3));
            startAndEnd.Add(new Tuple<DateTime, DateTime>(vStart4, vEnd4));

            var pg = new List<Data>();
            pg.Add(new Data() { ShiftDate = vStart1, HoursWorked = 1 });
            pg.Add(new Data() { ShiftDate = vStart2, HoursWorked = 3 });
            pg.Add(new Data() { ShiftDate = vStart3, HoursWorked = 5 });
            pg.Add(new Data() { ShiftDate = vStart3, HoursWorked = 7 });

            var Week1 = pg.Sum(x => x.ShiftDate >= vStart1 && x.ShiftDate <= vEnd1 ? x.HoursWorked : 0);
            var Week2 = pg.Sum(x => x.ShiftDate >= vStart2 && x.ShiftDate <= vEnd2 ? x.HoursWorked : 0);
            var Week3 = pg.Sum(x => x.ShiftDate >= vStart3 && x.ShiftDate <= vEnd3 ? x.HoursWorked : 0);
            var Week4 = pg.Sum(x => x.ShiftDate >= vStart4 && x.ShiftDate <= vEnd4 ? x.HoursWorked : 0);

            var hoursWorked = pg.Where(x => startAndEnd.Any(dates => dates.Item1 <= x.ShiftDate && dates.Item2 >= x.ShiftDate)).Sum(x => x.HoursWorked);

            Assert.Equal(16,hoursWorked);

            // delete the first week from criteria
            startAndEnd.Remove(startAndEnd.First());

            hoursWorked = pg.Where(x => startAndEnd.Any(dates => dates.Item1 <= x.ShiftDate && dates.Item2 >= x.ShiftDate)).Sum(x => x.HoursWorked);

            // should be 15 because week one deleted
            Assert.Equal(15, hoursWorked);

        }
    }

    public class Data
    {
        public DateTime ShiftDate { get; set; }
        public decimal HoursWorked { get; set; }
    }
}