I need to calculate the count of total assigned week and total benched week for an employee.
An employee is assigned in different projects in some time duration which will always start from Monday and always end on Friday. Monday to Friday will be considered as 1 week. Any overlapping weeks of assigned status should be considered as 1 week if employee is working in assigned status week project and that project duration falls under benched status week project so that week will be not counted in total bench week count.
Below are the relevant scenario as below.
"AssignedHistory":[
{"Project":1,"status":"Assigned","Startdate":"01/03/2022", "Enddate":"01/07/2022" },
{"Project":2,"status":"Assigned","Startdate":"01/10/2022", "Enddate":"01/14/2022" },
{"Project":3,"status":"Assigned", "Startdate":"01/10/2022", "Enddate":"01/21/2022" },
{"Project":4,"status":"Bench","Startdate":"01/17/2022", "Enddate":"01/21/2022" },
{"Project":5,"status":"Bench","Startdate":"02/07/2022", "Enddate":"02/11/2022" }
]
Here I need to find the count of total assigned week and total benched week for an employee, and expected result should be :
Total assigned week:3
Total benched week :1
Since 1 week of assigned status is overlapping for 2 projects from 10th Jan to 14 Jan and also for benched week for project 4 it is overlapping with assigned status of project 3 from 17th Jan to 21st Jan.
This is how I am trying
var assignedweek = AssignedHistory.Where(x => new []{"Assigned"}.Contains(x.status)).ToList();
var benchedweek = AssignedHistory.Where(x => new []{"Bench"}.Contains(x.status)).ToList();
int AssignedWeekCount = assignedweek
.SelectMany(a => {
DateTime firstSunday = a.Startdate.AddDays(-(int)a.Startdate.DayOfWeek);
DateTime lastSunday = a.Enddate.AddDays(-(int)a.Enddate.DayOfWeek);
int weeks = (lastSunday - firstSunday).Days / 7 1;
// Enumerate one Sunday per week
return Enumerable.Range(0, weeks).Select(i => firstSunday.AddDays(7 * i));
})
.Distinct()
.Count();
int BenchWeekCount = benchedweek
.SelectMany(a => {
DateTime firstSunday = a.Startdate.AddDays(-(int)a.Startdate.DayOfWeek);
DateTime lastSunday = a.Enddate.AddDays(-(int)a.Enddate.DayOfWeek);
int weeks = (lastSunday - firstSunday).Days / 7 1;
// Enumerate one Sunday per week
return Enumerable.Range(0, weeks).Select(i => firstSunday.AddDays(7 * i));
})
.Distinct()
.Count();
But it is giving incorrect bench week count if there is overlapping bench week with assigned week. I am not able to remove the overlapping week from benched week.
Any suggestions will be highly appreciated.
CodePudding user response：
I think this will help. This is a formula for calculating the number of overlapping weeks, where an Assigned week overlaps a Bench week.
This formula will calculate how many Bench weeks have Assigned weeks overlapping with them, which you can subtract from your Bench Week Count.
var overlappingWeeks = benchedweek.Where(b => assignedweek.Any(
a => a.Startdate < b.Enddate && a.Enddate > b.Startdate)
)
.Select(b => b.Startdate)
.Distinct()
.Count();
ETA: I forgot that there could be multiple bench weeks that covered the same week, and my formula would have counted them all! So I added Select and a Distinct.
Let me know how this works in your scenario: I did it from memory, so it hasn't been tested.
CodePudding user response：
Possible it would be interesting for you create Week structure like this:
public class Week
{
public int WeekId { get; private set; }
private DateTime FirstDayOfWeek {get; set; }
private const int numDayInWeek = 7;
public Week(Week anotherWeek): this(anotherWeek.FirstDayOfWeek)
{
}
public Week(DateTime date)
{
FirstDayOfWeek = GetFirstDateOfWeek(date);
var numWeekInYear = GetIndexWeekInYearForFirstDayOfWeek(date);
WeekId = GenerateWeekId(numWeekInYear, FirstDayOfWeek.Year);
}
private int GetIndexWeekInYearForFirstDayOfWeek(DateTime date)
{
return (GetFirstDateOfWeek(date).DayOfYear - 1) / numDayInWeek 1;
}
private DateTime GetFirstDateOfWeek(DateTime date)
{
return date.AddDays(-(int)date.DayOfWeek);
}
private int GenerateWeekId(int numWeekInYear, int year)
{
return year * 100 numWeekInYear;
}
public static List<Week> operator -(Week last, Week first)
{
List<Week> ret = new List<Week>();
if (last.WeekId < first.WeekId)
return ret;
for(Week cursor = first; cursor <= last; cursor )
ret.Add(cursor);
return ret;
}
public static Week operator (Week current)
{
return new Week(current.FirstDayOfWeek.AddDays(numDayInWeek));
}
public static bool operator >= (Week left, Week right)
{
return left.FirstDayOfWeek >= right.FirstDayOfWeek;
}
public static bool operator <= (Week left, Week right)
{
return left.FirstDayOfWeek <= right.FirstDayOfWeek;
}
public override int GetHashCode()
{
return WeekId;
}
public override bool Equals(object obj)
{
return Equals(obj as Week);
}
public bool Equals(Week obj)
{
return obj != null && obj.WeekId == this.WeekId;
}
}
And using this struct like this:
internal class ProjectDataCalculator
{
public List<Week> GetProjectDataWeeks(ProjectData projectData)
{
return new Week(projectData.EndDate) - new Week(projectData.StartDate);
}
public ProjectReport GetProjectReport(List<ProjectData> projectDatas)
{
ProjectReport report = new ProjectReport();
foreach (var projectData in projectDatas)
{
foreach (var week in GetProjectDataWeeks(projectData))
{
DefineWeekAssigned(report, projectData.Assigned, week);
}
}
return report;
}
private void DefineWeekAssigned(ProjectReport report, ProjectDataStatus assigned, Week week)
{
if(report.WeeksStatus.ContainsKey(week))
{
if (assigned == ProjectDataStatus.Assigned)
report.WeeksStatus[week] = assigned;
} else
{
report.WeeksStatus[week] = assigned;
}
}
}
internal class ProjectReport {
public Dictionary<Week, ProjectDataStatus> WeeksStatus { get; set; } = new Dictionary<Week, ProjectDataStatus>();
}
enum ProjectDataStatus
{
Assigned,
Bench
}
internal class ProjectData
{
public int Project { get; set; }
public ProjectDataStatus Assigned { get; set; }
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
}