public IList<RtpSummary> GetRTPProjects(RTPSearchModel projectSearchModel)
{
//Formerly (DTS) accepted a string 'tipYear' variable and called 'TIP-GetTipProjects'.
//What I would like to do is make a dynamic Linq statement with the parameters from ProjectSearchModel. -DBD
IList<RtpSummary> list = new List<RtpSummary>();
SqlCommand cmd = new SqlCommand("[RTP].[GetProjects]");
cmd.CommandType = CommandType.StoredProcedure;
//I will speed up these queries by restricting the list on three items: TipYear, TipYearID or IsActive. -DBD
if (!projectSearchModel.Exclude_Year) // If we are excluding a TipYear, then we must return everything (no SQL optimization)
{
if (projectSearchModel.RtpYearID != null) cmd.Parameters.AddWithValue("@YEARID", projectSearchModel.RtpYearID);
if (projectSearchModel.NetworkID > 0) cmd.Parameters.AddWithValue("@NetworkID", projectSearchModel.NetworkID);
}
if (projectSearchModel.VersionStatusId > 0)
{
cmd.Parameters.AddWithValue("@VersionStatusId", projectSearchModel.VersionStatusId);
}
if (projectSearchModel.CycleId > 0)
{
cmd.Parameters.AddWithValue("@CycleId", projectSearchModel.CycleId);
}
if (projectSearchModel.ShowCancelledProjects)
{
cmd.Parameters.AddWithValue("@ExcludeCancelled", !projectSearchModel.ShowCancelledProjects);
}
//if ( projectSearchModel.CycleId.Equals(default(int)) ) cmd.Parameters.AddWithValue("@CycleId", projectSearchModel.CycleId);
using (IDataReader rdr = this.ExecuteReader(cmd))
{
while (rdr.Read())
{
RtpSummary summary = new RtpSummary()
{
SponsorAgency = rdr["Sponsor"].ToString()
,
TIPId = rdr["TIPID"].ToString()
,
TipTimePeriod = rdr["TIPTimePeriod"].ToString()
,
RtpId = rdr["RtpYear"].ToString()
,
RtpYear = rdr["RtpYear"] != DBNull.Value ? rdr["RtpYear"].ToString() : "NULL IN DATABASE"
,
Title = rdr["ProjectName"] != DBNull.Value ? rdr["ProjectName"].ToString() : "NULL IN DATABASE"
,
ProjectVersionId = (int)rdr["RTPProjectVersionID"]
,
AmendmentStatus = rdr["AmendmentStatus"] != DBNull.Value ? rdr["AmendmentStatus"].ToString() : ""
,
AmendmentStatusId = rdr["AmendmentStatusId"].ToString().SmartParseDefault<int>(default(int))
,
ImprovementType = rdr["ImprovementType"] != DBNull.Value ? rdr["ImprovementType"].ToString() : ""
,
ProjectType = rdr["ProjectType"] != DBNull.Value ? rdr["ProjectType"].ToString() : ""
,
PlanType = rdr["PlanType"].ToString()
,
ProjectName = rdr["ProjectName"] != DBNull.Value ? rdr["ProjectName"].ToString() : ""
,
// Set ToUpper to ensure results found. No case sensitive required.
COGID = rdr["COGID"] != DBNull.Value ? rdr["COGID"].ToString().ToUpper() : ""
,
VersionStatus = rdr["ProjectVersionStatus"] != DBNull.Value ? rdr["ProjectVersionStatus"].ToString() : ""
};
summary.Cycle.Name = rdr["CycleName"].ToString();
summary.Cycle.Id = rdr["CycleId"].ToString().SmartParseDefault<int>(default(int));
list.Add(summary);
}
}
// These are processed via the SQL retrieval code optimization, so only process this one when exclude is checked.
if ((projectSearchModel.RtpYear != null) && (projectSearchModel.Exclude_Year))
{
list = (from fli in list
where (projectSearchModel.RtpYear.ToWildcardRegex().IsMatch(fli.RtpYear))
select fli).ToList<RtpSummary>();
}
//if ((projectSearchModel.VersionStatusId > 0) && (projectSearchModel.Exclude_ActiveVersion))
//{
// list = (from fli in list
// where (!fli.VersionStatus.Equals(projectSearchModel.VersionStatus))
// select fli).ToList<RtpSummary>();
//}
// Set ToUpper to ensure results found. No case sensitive required.
if (!String.IsNullOrEmpty(projectSearchModel.COGID))
{
projectSearchModel.COGID = projectSearchModel.COGID.ToUpper();
}
//Now that we have the base data, let's apply the rest of our parameters
// Trying to list the paramters here by most restrictive first. Should make searches much quicker. -DBD
if ((projectSearchModel.COGID != null) && (!projectSearchModel.COGID.Equals("")))
{
list = (from fli in list
where ((projectSearchModel.COGID.ToWildcardRegex().IsMatch(fli.COGID)) && (!projectSearchModel.Exclude_COGID))
|| ((!projectSearchModel.COGID.ToWildcardRegex().IsMatch(fli.COGID)) && (projectSearchModel.Exclude_COGID))
select fli).ToList<RtpSummary>();
}
if ((projectSearchModel.RtpID != null) && (!projectSearchModel.RtpID.Equals("")))
{
list = (from fli in list
where ((projectSearchModel.RtpID.ToWildcardRegex().IsMatch(fli.RtpId)) && (!projectSearchModel.Exclude_ID))
|| ((!projectSearchModel.RtpID.ToWildcardRegex().IsMatch(fli.RtpId)) && (projectSearchModel.Exclude_ID))
select fli).ToList<RtpSummary>();
}
if ((projectSearchModel.TipId != null) && (!projectSearchModel.TipId.Equals("")))
{
list = (from fli in list
where ((projectSearchModel.TipId.ToWildcardRegex().IsMatch(fli.TIPId)) && (!projectSearchModel.Exclude_TipId))
|| ((!projectSearchModel.TipId.ToWildcardRegex().IsMatch(fli.TIPId)) && (projectSearchModel.Exclude_TipId))
select fli).ToList<RtpSummary>();
}
if (projectSearchModel.RequireTipId)
{
list = (from fli in list
where ((!String.IsNullOrEmpty(fli.TIPId)) && (projectSearchModel.RequireTipId))
select fli).ToList<RtpSummary>();
}
if (projectSearchModel.SponsorAgency != null)
{
list = (from fli in list
where ((fli.SponsorAgency == projectSearchModel.SponsorAgency) && (!projectSearchModel.Exclude_SponsorAgency))
|| ((fli.SponsorAgency != projectSearchModel.SponsorAgency) && (projectSearchModel.Exclude_SponsorAgency))
select fli).ToList<RtpSummary>();
}
if (projectSearchModel.ImprovementType != null)
{
list = (from fli in list
where ((fli.ImprovementType == projectSearchModel.ImprovementType) && (!projectSearchModel.Exclude_ImprovementType))
|| ((fli.ImprovementType != projectSearchModel.ImprovementType) && (projectSearchModel.Exclude_ImprovementType))
select fli).ToList<RtpSummary>();
}
if (projectSearchModel.AmendmentStatus != null)
{
list = (from fli in list
where ((fli.AmendmentStatus == projectSearchModel.AmendmentStatus) && (!projectSearchModel.Exclude_AmendmentStatus))
|| ((fli.AmendmentStatus != projectSearchModel.AmendmentStatus) && (projectSearchModel.Exclude_AmendmentStatus))
select fli).ToList<RtpSummary>();
}
if (!String.IsNullOrEmpty(projectSearchModel.PlanType))
{
list = (from fli in list
where ((fli.PlanType == projectSearchModel.PlanType) && (!projectSearchModel.Exclude_PlanType))
|| ((fli.PlanType != projectSearchModel.PlanType) && (projectSearchModel.Exclude_PlanType))
select fli).ToList<RtpSummary>();
}
if (projectSearchModel.ProjectType != null)
{
list = (from fli in list
where ((fli.ProjectType == projectSearchModel.ProjectType) && (!projectSearchModel.Exclude_ProjectType))
|| ((fli.ProjectType != projectSearchModel.ProjectType) && (projectSearchModel.Exclude_ProjectType))
select fli).ToList<RtpSummary>();
}
if ((projectSearchModel.ProjectName != null) && (!projectSearchModel.ProjectName.Equals("")))
{
list = (from fli in list
where ((projectSearchModel.ProjectName.ToWildcardRegex().IsMatch(fli.ProjectName)) && (!projectSearchModel.Exclude_ProjectName))
|| ((!projectSearchModel.ProjectName.ToWildcardRegex().IsMatch(fli.ProjectName)) && (projectSearchModel.Exclude_ProjectName))
select fli).ToList<RtpSummary>();
}
return list;
}
public void SetProjectSearchDefaults(RTPSearchModel model)
{
SqlCommand cmd = new SqlCommand("[RTP].[GetSearchDefaults]");
cmd.CommandType = CommandType.StoredProcedure;
int testval;
using (IDataReader rdr = this.ExecuteReader(cmd))
{
while (rdr.Read())
{
model.RtpYearID = (!String.IsNullOrEmpty(rdr["Id"].ToString()) && Int32.TryParse(rdr["Id"].ToString(), out testval)) ? Int32.Parse(rdr["Id"].ToString()) : 0;
model.RtpYear = rdr["Plan"].ToString();
break; // for now this is how it has to be. Just getting a single record.
}
}
}
/// <summary>
/// Returns a list of projects associated with this RTP
/// </summary>
/// <param name="guid"></param>
/// <returns></returns>
public ActionResult ProjectList(string year, string dft, string df, int? page, int? cycleid)
{
LoadSession();
if (String.IsNullOrEmpty(year))
{
year = _rtpRepository.GetCurrentRtpPlanYear();
return RedirectToAction("Dashboard", new { @year = year });
}
//Make a ProjectViewModel object from the search criteria
var projectSearchModel = new RTPSearchModel();
//If there is a 'df' dashboard filter, then the Session search criteria are reset.
if (df != null)
{
//Reset Session search criteria
CurrentSessionApplicationState.ProjectSearchModel = null;
//Assign dashboard search filter criteria
projectSearchModel.RtpYear = year;
//ToDo: convert 'df' we need to know what DashboardListType ('dft') it is (Sponsor = 1,ProjectType = 2,AmendmentStatus = 3)
//ToDo: Assign the 'df' variable to projectSearchModel
switch (dft)
{
case "AmendmentStatus":
projectSearchModel.AmendmentStatus = df;
break;
case "Sponsor":
projectSearchModel.SponsorAgency = df;
break;
case "ProjectType":
projectSearchModel.ProjectType = df;
break;
case "ImprovementType":
projectSearchModel.ImprovementType = df;
break;
case "SponsorWithTipid":
projectSearchModel.SponsorAgency = df;
projectSearchModel.RequireTipId = true;
break;
}
//Assume from dashboard that we only want active projects.
//projectSearchModel.VersionStatusId = rtpSummary.IsPending ? (int)RTPVersionStatus.Pending : (int)RTPVersionStatus.Active;
}
else
{
//Check to see if there is a projectSearchModel in Session. If not, then we have nt selected a dashboard or project search tab option.
var sm = CurrentSessionApplicationState.ProjectSearchModel as RTPSearchModel;
if (sm != null)
{
//Pull ProjectSearchModel from session and use
projectSearchModel = sm;
}
else
{
//Create search using RTPYear and Active Version only (default).
projectSearchModel.RtpYear = year;
//projectSearchModel.VersionStatusId = rtpSummary.IsPending ? (int)RTPVersionStatus.Pending : (int)RTPVersionStatus.Active;
}
}
projectSearchModel.CycleId = cycleid ?? 0;
//Before passing the ProjectSearchModel, make sure it is validated
projectSearchModel = this.ValidateSearchData((RTPSearchModel)projectSearchModel, StringEnum.GetStringValue(CurrentSessionApplicationState.CurrentProgram));
//DTS NOTE: We don't fetch the model from the Repo directly because we will have to handle some complex criteria and filtering options
var viewModel = new ProjectListViewModel();
viewModel.RtpSummary = cycleid != null ? _rtpRepository.GetSummary(year, (int)cycleid) : _rtpRepository.GetSummary(year);
//if (projectSearchModel.CycleId.Equals(default(int))) { projectSearchModel.CycleId = viewModel.RtpSummary.Cycle.Id; }
if (viewModel.RtpSummary.Cycle.StatusId.Equals((int)Enums.RTPCycleStatus.Pending)) projectSearchModel.ShowCancelledProjects = true;
viewModel.ProjectList = _rtpRepository.GetRTPProjects(projectSearchModel);
viewModel.ListCriteria = df;
viewModel.ListType = dft;
if (viewModel.ProjectList.Count > 1000)
{
int originalCount = viewModel.ProjectList.Count;
viewModel.ProjectList = viewModel.ProjectList.Take(1000).ToList();
ViewData["ShowMessage"] = "Your results exceeded 1000 records. Please refine your search to narrow your results";
}
//viewModel.RestorableProjectList = _rtpRepository.GetRestoreProjectList(_rtpRepository.GetYearId(year, Enums.TimePeriodType.PlanYear));
//Now save this projectSearchModel (for future searchs)
CurrentSessionApplicationState.ProjectSearchModel = projectSearchModel;
viewModel.ReturnUrl = Request["ReturnUrl"] ?? String.Empty;
// set page parameters for javascript
var pp = CreatePageParameters();
pp.Add("RtpYear", viewModel.RtpSummary.RtpYear);
pp.Add("CurrentCycleId", viewModel.RtpSummary.Cycle.Id);
pp.Add("PreviousCycleId", viewModel.RtpSummary.Cycle.PriorCycleId);
pp.Add("NextCycleId", viewModel.RtpSummary.Cycle.NextCycleId);
pp.Add("RtpPlanYear", viewModel.RtpSummary.RtpYear);
pp.Add("RtpPlanYearId", viewModel.RtpSummary.RTPYearTimePeriodID);
SetPageParameters(pp);
return View(viewModel);
}
private RTPSearchModel ValidateSearchData(RTPSearchModel projectSearchModel, string currentProgram)
{
//Check completeness of TipYear
if ((projectSearchModel.RtpYearID == null) && (projectSearchModel.RtpYear != null))
{
//Lookup the RtpYearID
projectSearchModel.RtpYearID = _rtpRepository.GetYearId(projectSearchModel.RtpYear, Enums.TimePeriodType.PlanYear);
}
if ((projectSearchModel.RtpYearID != null) && (projectSearchModel.RtpYear == null))
{
//Lookup the TipYear
projectSearchModel.RtpYear = _rtpRepository.GetYear((int)projectSearchModel.RtpYearID);
}
//Check completeness of SponsorAgency
if ((projectSearchModel.SponsorAgencyID == null) && (projectSearchModel.SponsorAgency != null))
{
//Lookup the SponsorAgencyID
projectSearchModel.SponsorAgencyID = _rtpRepository.GetSponsorAgencyID(projectSearchModel.SponsorAgency);
}
if ((projectSearchModel.SponsorAgencyID != null) && (projectSearchModel.SponsorAgency == null))
{
//Lookup the SponsorAgency
projectSearchModel.SponsorAgency = _rtpRepository.GetSponsorAgency(projectSearchModel.SponsorAgencyID);
}
string statusType = "";
switch (currentProgram)
{
case "Transportation Improvement Plan":
statusType = "TIP Amendment Status";
break;
case "Regional Transportation Plan":
statusType = "RTP Amendment Status";
break;
case "Transportation Improvement Survey":
statusType = "Survey Amendment Status";
break;
default:
statusType = "TIP Amendment Status"; // If something goes wrong, assume TIP
break;
}
//Check completeness of AmendmentStatus
if ((projectSearchModel.AmendmentStatusID == null) && (projectSearchModel.AmendmentStatus != null))
{
//Lookup the AmendmentStatusID
projectSearchModel.AmendmentStatusID = _rtpRepository.GetStatusID(projectSearchModel.AmendmentStatus, statusType);
}
if ((projectSearchModel.AmendmentStatusID != null) && (projectSearchModel.AmendmentStatus == null))
{
//Lookup the AmendmentStatus
projectSearchModel.AmendmentStatus = _rtpRepository.GetStatus(projectSearchModel.AmendmentStatusID, statusType);
}
//Check completeness of ImprovementType
if ((projectSearchModel.ImprovementTypeID == null) && (projectSearchModel.ImprovementType != null))
{
//Lookup the ImprovementTypeID
projectSearchModel.ImprovementTypeID = _rtpRepository.GetImprovementTypeID(projectSearchModel.ImprovementType);
}
if ((projectSearchModel.ImprovementTypeID != null) && (projectSearchModel.ImprovementType == null))
{
//Lookup the ImprovementType
projectSearchModel.ImprovementType = _rtpRepository.GetImprovementType(projectSearchModel.ImprovementTypeID);
}
//Check completeness of ProjectType
if ((projectSearchModel.ProjectTypeID == null) && (projectSearchModel.ProjectType != null))
{
//Lookup the ProjectTypeID
projectSearchModel.ProjectTypeID = _rtpRepository.GetProjectTypeID(projectSearchModel.ProjectType);
}
if ((projectSearchModel.ProjectTypeID != null) && (projectSearchModel.ProjectType == null))
{
//Lookup the ProjectType
projectSearchModel.ProjectType = _rtpRepository.GetProjectType(projectSearchModel.ProjectTypeID);
}
//Check completeness of PlanType
if ((projectSearchModel.PlanTypeId == 0) && (projectSearchModel.PlanType != null))
{
//Lookup the PlanTypeID
projectSearchModel.PlanTypeId = _rtpRepository.GetCategoryId(projectSearchModel.PlanType);
}
if ((projectSearchModel.PlanTypeId > 0) && (projectSearchModel.PlanType == null))
{
//Lookup the PlanType
projectSearchModel.PlanType = _rtpRepository.GetCategory(projectSearchModel.PlanTypeId);
}
return projectSearchModel;
}
private RTPSearchModel GetProjectSearchModel()
{
//This method will check to see if a ProjectSearchModel is already in exsitance in the Session object.
//If so, it will copy it to the current object. If not, then defaults will be returned.
//ToDo: Add a variable for the application, so that each application can specifiy its own defaults?
var result = new RTPSearchModel();
LoadSession();
//Get a reference to session object
//ApplicationState appSession = this.GetSession();
if ((CurrentSessionApplicationState.ProjectSearchModel as RTPSearchModel) != null)
{
result = (RTPSearchModel)CurrentSessionApplicationState.ProjectSearchModel;
}
else
{
_rtpRepository.SetProjectSearchDefaults(result);
//Just return some general defaults for now
result.AmendmentStatusID = null;
result.COGID = "";
result.ProjectName = "";
result.ProjectType = "";
result.SponsorAgencyID = null;
result.RtpID = "";
result.ImprovementTypeID = null;
result.VersionStatusId = (int)Enums.RTPVersionStatus.Active;
}
return result;
}
/// <summary>
/// Get a list of the projects in the specified Year
/// </summary>
/// <returns></returns>
public IList<ProjectAmendments> GetProjectAmendments(RTPSearchModel projectSearchModel)
{
IList<ProjectAmendments> list = new List<ProjectAmendments>();
SqlCommand cmd = new SqlCommand("[RTP].[GetProjectAmendmentList]");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProjectId", projectSearchModel.ProjectId);
using (IDataReader rdr = this.ExecuteReader(cmd))
{
while (rdr.Read())
{
list.Add(new ProjectAmendments()
{
ProjectVersionId = (int)rdr["ProjectVersionId"]
,
AmendmentStatus = rdr["AmendmentStatus"] != DBNull.Value ? rdr["AmendmentStatus"].ToString() : ""
,
AmendmentDate = rdr["AmendmentDate"] != DBNull.Value ? (DateTime)rdr["AmendmentDate"] : DateTime.Now
,
ProjectName = rdr["ProjectName"] != DBNull.Value ? rdr["ProjectName"].ToString() : ""
});
}
}
// These are processed via the SQL retrieval code optimization, so only process this one when exclude is checked.
if ((projectSearchModel.RtpYear != null) && (projectSearchModel.Exclude_Year))
{
list = (from fli in list
where (fli.Year != projectSearchModel.RtpYear)
select fli).ToList<ProjectAmendments>();
}
if (projectSearchModel.AmendmentStatus != null)
{
list = (from fli in list
where ((fli.AmendmentStatus == projectSearchModel.AmendmentStatus) && (!projectSearchModel.Exclude_AmendmentStatus))
|| ((fli.AmendmentStatus != projectSearchModel.AmendmentStatus) && (projectSearchModel.Exclude_AmendmentStatus))
select fli).ToList<ProjectAmendments>();
}
if ((projectSearchModel.ProjectName != null) && (!projectSearchModel.ProjectName.Equals("")))
{
list = (from fli in list
where ((fli.ProjectName.Contains(projectSearchModel.ProjectName)) && (!projectSearchModel.Exclude_ProjectName))
|| ((!fli.ProjectName.Contains(projectSearchModel.ProjectName)) && (projectSearchModel.Exclude_ProjectName))
select fli).ToList<ProjectAmendments>();
}
return list;
}
public AmendmentsViewModel GetAmendmentsViewModel(int projectVersionId, string tipYear)
{
var result = new AmendmentsViewModel();
// get project summary info
result.InfoModel = GetProjectInfo(projectVersionId, tipYear);
result.RtpSummary = GetProjectSummary(projectVersionId, tipYear);
RTPSearchModel search = new RTPSearchModel()
{
ProjectId = (Int32)result.RtpSummary.ProjectId
};
result.AmendmentList = GetProjectAmendments(search);
return result;
}