private decimal CalcVacationDaysRemaining(int consultant_id, meradia_db db)
{
// timeslips for vacation days are entered under a generic
// "meradia" SOW for phase "Vacation". so we need the Meradia
// SOW, and the ID for the Vacation phase.
// first, get the company_id for Meradia Group
int meradia = db.companies.
Where(c => c.company_nm.Contains("meradia group")).
Select(c => c.ID).First();
// next, get the SOW for Meradia timeslips
int meradia_sow = db.sows.
Where(s => s.company_id == meradia && s.description.Contains("for meradia internal time slips")).
Select(s => s.ID).First();
// next, get the id for the "Vacation" phase of the Meradia SOW
int vacation_phase = db.timeslip_phase.
Where(t => t.sow_id == meradia_sow && t.slip_phase_name == "Vacation").
Select(p => p.ID).First();
// sum vacation hours YTD from consultant timeslips
decimal vac_hours = db.timeslips.
Where(t => t.consultant_id == consultant_id &&
t.sow_id == meradia_sow &&
t.slip_phase_nm == vacation_phase &&
t.slip_start_dt >= this.dates.ytd_start_dt &&
t.slip_end_dt <= this.dates.ytd_end_dt).
Sum(t => t.slip_time);
// subtract vacation hours from vacation days available. then convert
// the results to 8-hour days. round to one decimal place because the
// minimum vacation hours are 4 (i.e. half day).
return(decimal.Round(((this.vacation_days_available * 8.0M) - vac_hours) / 8.0M, 1));
}
private decimal SumHours(meradia_db db, int consultant_id, System.DateTime start_dt, System.DateTime end_dt)
{
decimal?hours = db.timeslips.
Where(t => t.consultant_id == consultant_id &&
t.slip_start_dt >= start_dt &&
t.slip_end_dt <= end_dt).
Sum(t => (decimal?)t.slip_time);
// round to 2 decimals, because hours can only be entered
// in quarter-hour increments (i.e. 0, .25, .5, .75)
return(decimal.Round((hours ?? 0), 2));
}
// Constructors
public ConsultantDashboard(int consultant_id)
{
this.dates = new TimeSlipDates(System.DateTime.Now);
meradia_db db = new meradia_db();
// get vacation days available for the current year
this.vacation_days_available = (
db.consultants.Where(c => c.ID == consultant_id).
Select(c => c.vacation_available).First()) ?? 0M;
this.vacation_days_remaining = CalcVacationDaysRemaining(consultant_id, db);
this.hours_this_week = SumHours(db, consultant_id, dates.this_week_start_dt, dates.this_week_end_dt);
this.hours_last_week = SumHours(db, consultant_id, dates.last_week_start_dt, dates.last_week_end_dt);
this.hours_mtd = SumHours(db, consultant_id, dates.mtd_start_dt, dates.mtd_end_dt);
this.hours_last_month = SumHours(db, consultant_id, dates.last_month_start_dt, dates.last_month_end_dt);
}
