/// <summary>
/// Create a New PlanCycle and attach it to the given plan year.
/// </summary>
/// <param name="cycle">with Name and Description</param>
/// <param name="rtpYearId">ID of plan year</param>
/// <returns>ID of created Plan Cycle</returns>
public int CreateRtpPlanCycle(DRCOG.Domain.ViewModels.RTP.PlanCycle cycle, int rtpYearId)
{
using (var db = new Models.TRIPSEntities())
{
// there can be only one cycle in this plan year with a status of New
var stId = db.StatusTypes.First(st => st.StatusType1 == "Cycle Status").StatusTypeID;
var newId = db.Status.First(s => s.StatusTypeID == stId && s.Status1 == "New").StatusID;
// see if there is already a new plan cycle for this RTP Year
var previousNew = db.TimePeriodCycles.FirstOrDefault(tpc => tpc.TimePeriodId == rtpYearId && tpc.Cycle.statusId == newId);
if (previousNew != null)
{
throw new Exception("There is already a New Plan Cycle");
}
byte listOrder = 0;
int? priorCycleId = null;
// if this is not the first cycle in the time period, attach it to the end
// of the two lists - ListOrder and priorCycleId
var planCycles = db.TimePeriodCycles.Where(tpc => tpc.TimePeriodId == rtpYearId);
if (planCycles.Count() > 0)
{
// find last cycle in this time period. "last" means highest ListOrder.
var lo = planCycles.Max(tpc => tpc.ListOrder);
var prior = planCycles.First(tpc => tpc.ListOrder == lo);
priorCycleId = prior.CycleId;
listOrder = lo ?? 0;
}
// find largest ListOrder in these cycles and add 1
++listOrder;
var mcycle = new Models.Cycle()
{
cycle1 = cycle.Name,
Description = cycle.Description,
statusId = newId,
priorCycleId = priorCycleId
};
db.Cycles.AddObject(mcycle);
var mtpc = new Models.TimePeriodCycle()
{
Cycle = mcycle,
TimePeriodId = (short)rtpYearId,
ListOrder = listOrder
};
db.TimePeriodCycles.AddObject(mtpc);
db.SaveChanges();
return mcycle.id;
}
}
public DRCOG.Domain.Models.LRS RtpGetLrs(int id)
{
using (var db = new Models.TRIPSEntities())
{
var lrs = ParseLrs(db.LRS.First(l => id == l.Id));
if (lrs == null)
{
throw new Exception("Invalid data in LRS " + id.ToString());
}
return lrs;
}
}
public void RtpDeleteLrs(int id)
{
using (var db = new Models.TRIPSEntities())
{
var lrs = db.LRS.FirstOrDefault(l => id == l.Id);
if (lrs != null)
{
db.LRS.DeleteObject(lrs);
db.SaveChanges();
}
}
}
public DRCOG.Domain.ViewModels.RTP.ReportsViewModel GetReportsViewModel(string year)
{
var result = new DRCOG.Domain.ViewModels.RTP.ReportsViewModel();
using (var db = new Models.TRIPSEntities())
{
result.RtpSummary = new DRCOG.Domain.Models.RTP.RtpSummary();
var summary = db.RtpGetSummary(year, null).FirstOrDefault();
if (summary != null)
{
// many things left unset
result.RtpSummary.RtpYear = year;
result.RtpSummary.RTPYearTimePeriodID = summary.TimePeriodID;
result.RtpSummary.TimePeriodStatusId = summary.TimePeriodStatusId ?? 0;
result.RtpSummary.Cycle = new DRCOG.Domain.Models.Cycle()
{
// many things left unset
Id = summary.CycleId ?? 0,
Name = summary.CycleName,
StatusId = summary.CycleStatusId ?? 0,
Status = summary.CycleStatus,
PriorCycleId = summary.priorCycleId ?? 0,
NextCycleId = summary.nextCycleId ?? 0,
NextCycleName = summary.nextCycle,
NextCycleStatus = summary.nextStatus
};
}
else
{
Logger.Debug("No summary found for year " + year);
}
// TODO: is this the right Time Period ID for this?
result.CurrentPlanCycles = new List<KeyValuePair<int, string>>();
result.CurrentPlanCycles.Add(new KeyValuePair<int, string>(0, "All"));
var cycles = db.RtpGetCurrentPlanCycles(result.RtpSummary.RTPYearTimePeriodID);
foreach (var cycle in cycles.OrderBy(c => c.cycle))
{
result.CurrentPlanCycles.Add(new KeyValuePair<int, string>(cycle.id, cycle.cycle));
}
result.SurveyYears = new List<KeyValuePair<int, string>>();
var years = db.TimePeriods.Join(db.ProgramInstances,
tp => tp.TimePeriodID,
pi => pi.TimePeriodID,
(tp, pi) => tp)
.Where(tp => tp.TimePeriodTypeID == (short)Enums.TimePeriodType.Survey);
foreach (var tp in years.OrderBy(t => t.TimePeriod1))
{
var name = tp.TimePeriod1;
if (string.IsNullOrWhiteSpace(name))
{
name = "<No Name>";
}
result.SurveyYears.Add(new KeyValuePair<int, string>(tp.TimePeriodID, name));
}
}
return result;
}
public int RtpCreatePlan(RtpCreatePlanRequest request)
{
using (var db = new Models.TRIPSEntities())
{
// create a time period for the plan
var tp = new Models.TimePeriod()
{
TimePeriodTypeID = (int)Enums.TimePeriodType.PlanYear,
TimePeriod1 = request.PlanName
};
db.TimePeriods.AddObject(tp);
// create pieces of the plan, with a status of Pending
var pi = new Models.ProgramInstance()
{
StatusId = (int)Enums.RtpTimePeriodStatus.Pending,
ProgramID = 2,
TimePeriod = tp
};
db.ProgramInstances.AddObject(pi);
var rtpi = new Models.RTPProgramInstance()
{
ProgramInstance = pi,
TimePeriod = tp
};
db.RTPProgramInstances.AddObject(rtpi);
db.SaveChanges();
Logger.Debug("created RTP Program Instance " + rtpi.RTPProgramID.ToString() + ", " + rtpi.TimePeriodID.ToString());
// we had to save the changes to get IDs for the created objects,
// since the TimePeriodID is part of a compound key.
// TODO: get active plan
int activePlan = 78;
// copy the sponsors from the current plan
foreach (var sponsor in db.RTPProgramInstanceSponsors.Where(r => r.TimePeriodID == activePlan))
{
// first create a base ProgramInstanceSponsor
var pis = new Models.ProgramInstanceSponsor()
{
ProgramID = sponsor.RTPProgramID,
SponsorID = sponsor.SponsorID,
TimePeriodID = tp.TimePeriodID
};
db.ProgramInstanceSponsors.AddObject(pis);
var copy = new Models.RTPProgramInstanceSponsor()
{
EmailDate = sponsor.EmailDate,
FormPrintedDate = sponsor.FormPrintedDate,
ReadyDate = sponsor.ReadyDate,
RTPProgramID = sponsor.RTPProgramID,
SponsorID = sponsor.SponsorID,
TimePeriodID = tp.TimePeriodID
};
db.RTPProgramInstanceSponsors.AddObject(copy);
}
// create a pending cycle
var mcycle = new Models.Cycle()
{
cycle1 = request.CycleName,
Description = request.CycleDescription,
statusId = (int)Enums.RTPCycleStatus.Pending,
priorCycleId = null
};
db.Cycles.AddObject(mcycle);
var mtpc = new Models.TimePeriodCycle()
{
Cycle = mcycle,
TimePeriodId = tp.TimePeriodID,
ListOrder = 1
};
db.TimePeriodCycles.AddObject(mtpc);
db.SaveChanges();
Logger.Debug("copied sponsors from RTP Plan " + activePlan.ToString());
Logger.Debug("created RTP Plan Cycle " + mcycle.id.ToString());
return tp.TimePeriodID;
}
}
public void UpdateRtpPlanCycle(DRCOG.Domain.ViewModels.RTP.PlanCycle cycle)
{
using (var db = new Models.TRIPSEntities())
{
var found = db.Cycles.FirstOrDefault(c => c.id == cycle.Id);
if (found == null)
{
throw new Exception("No such RTP Plan Cycle");
}
found.cycle1 = cycle.Name;
found.Description = cycle.Description;
db.SaveChanges();
}
}
/// <summary>
/// Add projects to given Survey. Currently, this must be the latest Survey and must be in
/// a New status.
/// </summary>
/// <param name="surveyId"></param>
/// <param name="projects"></param>
public void SurveyAmendProjects(int surveyId, IEnumerable<int> projects)
{
using (var db = new Models.TRIPSEntities())
{
foreach (var pid in projects)
{
// Due to crappy data, we expect some operations to fail, but we
// want to do as much as possible.
try
{
Logger.Debug("Copy SurveyProjectVersion " + pid.ToString());
var ov = new System.Data.Objects.ObjectParameter("NewProjectVersionId", typeof(int));
var result = db.SurveyCopyProject(pid, surveyId, null, ov);
var npid = ov.Value;
Logger.Debug("created SurveyProjectVersion " + npid.ToString());
}
catch (Exception ex)
{
var message = string.Format("SurveyAmendProjects failed for id {0}", pid);
Logger.WarnException(message, ex);
}
}
db.SaveChanges();
}
}
/// <summary>
/// Copy the given projects into the given RTP Plan Year and mark them Pending.
/// </summary>
/// <remarks>seems identical to RtpAmendProjects. question the VersionStatus.</remarks>
/// <param name="rtpPlanYearId">ID of the RTP Plan Year</param>
/// <param name="projects">list of RTP ProjectVersion IDs</param>
public void RtpRestoreProjects(int rtpPlanYearId, IEnumerable<int> projects)
{
using (var db = new Models.TRIPSEntities())
{
var pc = RtpAssurePendingCycle(db, rtpPlanYearId);
Logger.Debug("RTP Pending Cycle is " + pc.id.ToString());
db.SaveChanges();
foreach (var pid in projects)
{
// Due to crappy data, we expect some operations to fail, but we
// want to do as much as possible.
try
{
Logger.Debug("Copy RTPProjectVersion " + pid.ToString());
var result = db.RtpCopyProject(pid, null, rtpPlanYearId, pc.id);
var npid = result.First().RTPProjectVersionID;
Logger.Debug("created RTPProjectVersion " + npid.ToString());
// get the newly created RTPProjectVersion
var npv = db.RTPProjectVersions.First(p => p.RTPProjectVersionID == npid);
// set status to Pending
npv.AmendmentStatusID = (int)Enums.RTPAmendmentStatus.Pending;
// TODO: this seems to be the only difference
npv.VersionStatusID = (int)Enums.RTPVersionStatus.Pending;
}
catch (Exception ex)
{
var message = string.Format("RtpRestoreProjects failed for id {0}", pid);
Logger.WarnException(message, ex);
}
}
db.SaveChanges();
}
}
public IEnumerable<DRCOG.Domain.ViewModels.RTP.PlanCycle> GetRtpPlanCycles(int rtpYearId)
{
var result = new List<DRCOG.Domain.ViewModels.RTP.PlanCycle>();
using (var db = new Models.TRIPSEntities())
{
var tpcs = db.TimePeriodCycles.Where(t => t.TimePeriodId == rtpYearId).OrderByDescending(t => t.ListOrder);
foreach (var tpc in tpcs)
{
result.Add(new DRCOG.Domain.ViewModels.RTP.PlanCycle()
{
Description = tpc.Cycle.Description,
Id = tpc.CycleId,
Name = tpc.Cycle.cycle1,
Status = tpc.Cycle.Status.Status1
});
}
}
return result;
}
public byte[] GetSurveyModelerExtractDocument(int? timePeriodId)
{
using (var db = new Models.TRIPSEntities())
{
var extracts = db.SurveyModelerExtract(timePeriodId);
var results = extracts.ToArray();
using (var pck = new ExcelPackage())
{
// Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("ModelerExtract");
// Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromCollection(results, true);
ws.Cells.AutoFitColumns();
return pck.GetAsByteArray();
}
}
}
public DRCOG.Domain.ViewModels.RTP.PlanCycle GetRtpPlanCycle(int id)
{
using (var db = new Models.TRIPSEntities())
{
var found = db.Cycles.FirstOrDefault(c => c.id == id);
if (found == null)
{
return null;
}
return new DRCOG.Domain.ViewModels.RTP.PlanCycle()
{
Description = found.Description,
Id = found.id,
Name = found.cycle1,
Status = found.Status.Status1
};
}
}
public int GetRtpCurrentPlanId()
{
using (var db = new Models.TRIPSEntities())
{
var plan = db.ProgramInstances.FirstOrDefault(
tp => tp.StatusId == (int)Enums.RtpTimePeriodStatus.Current &&
tp.ProgramID == (int)Enums.TimePeriodType.TimePeriod);
if (plan == null)
{
return 0;
}
return plan.TimePeriodID;
}
}
/// <summary>
/// Get the ID of the Active cycle in the Current plan.
/// </summary>
/// <returns>id of active cycle of current plan</returns>
public int GetRtpActivePlanCycleId(int planId)
{
using (var db = new Models.TRIPSEntities())
{
// we need to join Cycles and TimePeriodCycles to get Cycle
var cycle = db.Cycles.Join(db.TimePeriodCycles,
c => c.id,
tpc => tpc.CycleId,
(c, tpc) => new { StatusId = c.statusId, CycleId = c.id, PlanId = tpc.TimePeriodId })
.Where(x => x.StatusId == (int)Enums.RTPCycleStatus.Active && x.PlanId == planId)
.FirstOrDefault();
if (cycle == null)
{
return 0;
}
return cycle.CycleId;
}
}
public IEnumerable<DRCOG.Domain.Models.LRS> RtpGetLrsForSegment(int segmentId)
{
var lrsl = new List<DRCOG.Domain.Models.LRS>(); using (var db = new Models.TRIPSEntities())
{
lrsl.AddRange(db.LRS.Where(l => l.ProjectSegmentId == segmentId).ToArray().Select(lrs => ParseLrs(lrs)).Where(l => l != null));
}
return lrsl;
}
/// <summary>
/// Get TIP Status
/// </summary>
/// <remarks>obsoletes TIP.GetStatus</remarks>
/// <param name="rtpYearId">ID of the RTP Plan Year</param>
/// <returns></returns>
public DRCOG.Domain.Models.TipStatusModel GetTipStatus(int rtpYearId)
{
var model = new DRCOG.Domain.Models.TipStatusModel();
using (var db = new Models.TRIPSEntities())
{
var p = db.ProgramInstances.Single(pi => pi.TimePeriodID == rtpYearId);
var tp = db.TimePeriods.Single(t => t.TimePeriodID == rtpYearId);
var tpi = db.TIPProgramInstances.Single(pi => pi.TimePeriodID == rtpYearId && pi.TIPProgramID == p.ProgramID);
model.Adoption = tpi.AdoptionDate;
model.EPAApproval = tpi.USEPAApprovalDate;
model.GovernorApproval = tpi.GovernorApprovalDate;
model.IsCurrent = p.Current.Value;
model.IsPending = p.Pending.Value;
model.IsPrevious = p.Previous.Value;
model.LastAmended = tpi.LastAmendmentDate;
model.Notes = p.Notes;
model.ProgramId = p.ProgramID;
model.PublicHearing = tpi.PublicHearingDate;
model.ShowDelayDate = tpi.ShowDelayDate;
model.TimePeriodId = tpi.TimePeriodID;
model.TipYear = tp.TimePeriod1;
model.USDOTApproval = tpi.USDOTApprovalDate;
foreach (var f in tp.FundingIncrements)
{
Logger.Debug("'" + f.FundingIncrement1 + "'");
}
var fs = new FundingIncrementSetter(db, tp);
model.FundingIncrement_Year_1 = fs.GetFundingIncrement("Year 1");
model.FundingIncrement_Year_2 = fs.GetFundingIncrement("Year 2");
model.FundingIncrement_Year_3 = fs.GetFundingIncrement("Year 3");
model.FundingIncrement_Year_4 = fs.GetFundingIncrement("Year 4");
model.FundingIncrement_Year_5 = fs.GetFundingIncrement("Year 5");
model.FundingIncrement_Year_6 = fs.GetFundingIncrement("Year 6");
model.FundingIncrement_Years_4_6 = fs.GetFundingIncrement("Years 4-6");
model.FundingIncrement_Years_5_6 = fs.GetFundingIncrement("Years 5-6");
}
return model;
}
public IEnumerable<SelectListItem> RtpGetSponsorOrganizations(int rtpPlanYearId)
{
using (var db = new Models.TRIPSEntities())
{
// we need to fetch these or the ToString will be sent to SQL server (and fail)
var orgs = db.RTPProgramInstanceSponsors.Where(s => s.TimePeriodID == rtpPlanYearId).Select(r => r.SponsorOrganization.Organization).ToArray();
// make a list
return orgs.Select(o => new SelectListItem()
{
Text = o.OrganizationName,
Value = o.OrganizationID.ToString()
}).ToArray();
}
}
public int GetTipYearId(string year)
{
using (var db = new Models.TRIPSEntities())
{
var tper = db.TimePeriods.FirstOrDefault(tp => tp.TimePeriod1 == year && tp.TimePeriodTypeID == 2);
if (tper == null)
{
throw new Exception("No such TIP Year " + year);
}
return tper.TimePeriodID;
}
}
/// <summary>
/// Update the XML data in an RTP Project LRS record.
/// </summary>
/// <param name="lrs">data</param>
public void RtpUpdateLrs(DRCOG.Domain.Models.LRS lrs)
{
var model = CompileLrs(lrs);
using (var db = new Models.TRIPSEntities())
{
var record = db.LRS.First(l => l.Id == lrs.Id);
record.Data = model.Data;
db.SaveChanges();
}
}
/// <summary>
/// Mark the projects Adopted, mark the Pending Cycle Active, and
/// mark the Active Cycle Inactive.
/// </summary>
/// <param name="rtpPlanYearId">ID of the RTP Plan Year</param>
/// <param name="projects">list of RTP ProjectVersion IDs</param>
public void RtpAdoptProjects(int rtpPlanYearId, IEnumerable<int> projects)
{
using (var db = new Models.TRIPSEntities())
{
var pc = RtpAssurePendingCycle(db, rtpPlanYearId);
Logger.Debug("RTP Pending Cycle is " + pc.id.ToString());
var tp = db.TimePeriods.First(t => t.TimePeriodID == rtpPlanYearId);
foreach (var pid in projects)
{
// Due to crappy data, we expect some operations to fail, but we
// want to do as much as possible.
try
{
var npv = db.RTPProjectVersions.First(p => p.RTPProjectVersionID == pid);
// set statuses (stati?)
npv.AmendmentStatusID = (int)Enums.RTPAmendmentStatus.Amended;
npv.VersionStatusID = (int)Enums.RTPVersionStatus.Active;
// TODO: set status of previous version, if any
//var ppv = db.RTPProjectVersions.First(p => p.RTPProjectVersionID == npv.Pre);
}
catch (Exception ex)
{
var message = string.Format("RtpAdoptProjects failed for id {0}", pid);
Logger.WarnException(message, ex);
}
}
/// Set the current Active Cycle (if any) to Inactive.
/// Set the current Pending Cycle to Active.
var ac = tp.TimePeriodCycles.FirstOrDefault(tpc => tpc.Cycle.statusId == (int)Enums.RTPCycleStatus.Active);
if (ac != null)
{
ac.Cycle.statusId = (int)Enums.RTPCycleStatus.Inactive;
}
pc.statusId = (int)Enums.RTPCycleStatus.Active;
db.SaveChanges();
}
}
public void TryCatchError()
{
using (var db = new Models.TRIPSEntities())
{
db.TryCatchError();
}
}
public int RtpCreateLrs(DRCOG.Domain.Models.LRS lrs)
{
var model = CompileLrs(lrs);
using (var db = new Models.TRIPSEntities())
{
db.LRS.AddObject(model);
db.SaveChanges();
return model.Id;
}
}
/// <summary>
/// Update the TIP status.
/// </summary>
/// <param name="model"></param>
public void UpdateTipStatus(DRCOG.Domain.Models.TipStatusModel model)
{
using (var db = new Models.TRIPSEntities())
{
var p = db.ProgramInstances.Single(pi => pi.TimePeriodID == model.TimePeriodId);
var tp = db.TimePeriods.Single(t => t.TimePeriodID == model.TimePeriodId);
var tpi = db.TIPProgramInstances.Single(pi => pi.TimePeriodID == model.TimePeriodId && pi.TIPProgramID == p.ProgramID);
//p.ClosingDate = ?
p.Current = model.IsCurrent;
p.Notes = model.Notes;
p.Pending = model.IsPending;
p.Previous = model.IsPrevious;
tpi.AdoptionDate = model.Adoption;
tpi.GovernorApprovalDate = model.GovernorApproval;
tpi.PublicHearingDate = model.PublicHearing;
tpi.ShowDelayDate = model.ShowDelayDate;
tpi.USDOTApprovalDate = model.USDOTApproval;
tpi.USEPAApprovalDate = model.EPAApproval;
var fs = new FundingIncrementSetter(db, tp);
fs.SetFundingIncrement(model.FundingIncrement_Year_1, "Year 1");
fs.SetFundingIncrement(model.FundingIncrement_Year_2, "Year 2");
fs.SetFundingIncrement(model.FundingIncrement_Year_3, "Year 3");
fs.SetFundingIncrement(model.FundingIncrement_Year_4, "Year 4");
fs.SetFundingIncrement(model.FundingIncrement_Year_5, "Year 5");
fs.SetFundingIncrement(model.FundingIncrement_Year_6, "Year 6");
fs.SetFundingIncrement(model.FundingIncrement_Years_4_6, "Years 4-6");
fs.SetFundingIncrement(model.FundingIncrement_Years_5_6, "Years 5-6");
db.SaveChanges();
}
}
public int RtpCreatePlan(RtpCreatePlanRequest request)
{
using (var db = new Models.TRIPSEntities())
{
var ov = new System.Data.Objects.ObjectParameter("TimePeriodId", typeof(int));
db.RtpCreatePlan(request.PlanName, request.CycleName, request.CycleDescription, ov);
return (int)ov.Value;
}
}
/// <summary>
/// Delete RTP Plan Cycle from RTP Year and then from Cycles. Not currently used in production,
/// but useful for testing.
/// </summary>
/// <param name="id"></param>
public void DeleteRtpPlanCycle(int id)
{
using (var db = new Models.TRIPSEntities())
{
var cycle = db.Cycles.FirstOrDefault(c => c.id == id);
if (cycle == null)
{
throw new Exception("No such RTP Plan Cycle");
}
var refs = db.TimePeriodCycles.Where(tpc => tpc.CycleId == id);
foreach (var t in refs)
{
db.TimePeriodCycles.DeleteObject(t);
}
db.Cycles.DeleteObject(cycle);
db.SaveChanges();
}
}
public IEnumerable<SelectListItem> GetFundingIncrements(int tipYearId)
{
using (var db = new Models.TRIPSEntities())
{
var tps = db.TimePeriods.Where(tp => tp.TimePeriodID == tipYearId).SingleOrDefault();
if (tps == null)
{
throw new Exception("No such TimePeriod " + tipYearId);
}
return tps.FundingIncrements.Select(fi => new SelectListItem()
{
Text = fi.FundingIncrement1,
Value = fi.FundingIncrementID.ToString()
}).ToArray();
}
}
