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); }