public void SetProjectSearchDefaults(TIPSearchModel model)
{
SqlCommand cmd = new SqlCommand("[Tip].[GetSearchDefaults]");
cmd.CommandType = CommandType.StoredProcedure;
int testval;
using (IDataReader rdr = this.ExecuteReader(cmd))
{
while (rdr.Read())
{
model.TipYearID = (!String.IsNullOrEmpty(rdr["Id"].ToString()) && Int32.TryParse(rdr["Id"].ToString(), out testval)) ? Int32.Parse(rdr["Id"].ToString()) : 0;
model.TipYear = rdr["Name"].ToString();
break; // for now this is how it has to be. Just getting a single record.
}
}
}
/// <summary>
/// Get a list of the projects in the specified TIP Year
/// </summary>
/// <param name="tipYear"></param>
/// <returns></returns>
public IList<ProjectAmendments> GetProjectAmendments(TIPSearchModel projectSearchModel)
{
IList<ProjectAmendments> list = new List<ProjectAmendments>();
SqlCommand cmd = new SqlCommand("[TIP].[GetProjectAmendmentList]");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProjectId", projectSearchModel.ProjectId);
cmd.Parameters.AddWithValue("@TimePeriodId", projectSearchModel.TipYearID);
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() : ""
, AmendmentStatusId = rdr["AmendmentStatusID"].ToString().SmartParseDefault<int>(default(int))
, AmendmentDate = rdr["AmendmentDate"] != DBNull.Value ? (DateTime)rdr["AmendmentDate"] : DateTime.MinValue
, ProjectName = rdr["ProjectName"] != DBNull.Value ? rdr["ProjectName"].ToString() : ""
, AmendmentCharacter = rdr["AmendmentCharacter"] != DBNull.Value ? rdr["AmendmentCharacter"].ToString() : ""
, AmendmentReason = rdr["AmendmentReason"].ToString()
, VersionStatus = rdr["VersionStatus"] != DBNull.Value ? rdr["VersionStatus"].ToString() : ""
, Year = rdr["TimePeriod"].ToString()
});
}
}
// These are processed via the SQL retrieval code optimization, so only process this one when exclude is checked.
if ((projectSearchModel.TipYear != null) && (projectSearchModel.Exclude_TipYear))
{
list = (from fli in list
where (fli.Year != projectSearchModel.TipYear)
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;
}
/// <summary>
/// Get a list of the projects in the specified TIP Year
/// </summary>
/// <param name="tipYear"></param>
/// <returns></returns>
public IList<TipSummary> GetTIPProjects(TIPSearchModel 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<TipSummary> list = new List<TipSummary>();
SqlCommand cmd = new SqlCommand("[TIP].[GetProjects2]");
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add(new SqlParameter("@TIPYEAR", SqlDbType.NVarChar));
//cmd.Parameters[0].Value = tipYear;
//I will speed up these queries by restricting the list on three items: TipYear, TipYearID or IsActive. -DBD
if (!projectSearchModel.Exclude_TipYear) // If we are excluding a TipYear, then we must return everything (no SQL optimization)
{
if (projectSearchModel.TipYear != null) cmd.Parameters.AddWithValue("@TimePeriod", projectSearchModel.TipYear);
if (projectSearchModel.TipYearID != null) cmd.Parameters.AddWithValue("@TimePeriodId", projectSearchModel.TipYearID);
}
if (projectSearchModel.VersionStatusId > 0)
{
//if ((bool)projectSearchModel.ActiveVersion) cmd.Parameters.AddWithValue("@ISACTIVE", 1);
cmd.Parameters.AddWithValue("@VersionStatusId", projectSearchModel.VersionStatusId);
//else cmd.Parameters.AddWithValue("@ISACTIVE", 0);
}
if (!projectSearchModel.AmendmentTypeId.Equals(default(int)))
{
cmd.Parameters.AddWithValue("@AmendmentTypeId", projectSearchModel.AmendmentTypeId);
}
if (!projectSearchModel.AmendmentStatusID.Equals(default(int)))
{
cmd.Parameters.AddWithValue("@AmendmentStatusId", projectSearchModel.AmendmentStatusID);
}
if (!projectSearchModel.FundingTypeId.Equals(default(int)))
{
cmd.Parameters.AddWithValue("@FundingTypeId", projectSearchModel.FundingTypeId);
}
if (!projectSearchModel.FundingIncrementID.Equals(default(int)))
{
cmd.Parameters.AddWithValue("@FundingIncrementId", projectSearchModel.FundingIncrementID);
}
if (!String.IsNullOrEmpty(projectSearchModel.ScopeTerm))
{
cmd.Parameters.AddWithValue("@ScopeTerm", projectSearchModel.ScopeTerm);
}
if (!String.IsNullOrEmpty(projectSearchModel.PoolTerm))
{
cmd.Parameters.AddWithValue("@PoolTerm", projectSearchModel.PoolTerm);
}
if (!String.IsNullOrEmpty(projectSearchModel.GeographyName))
{
cmd.Parameters.AddWithValue("@GeographyName", projectSearchModel.GeographyName);
}
if (!projectSearchModel.CdotRegionId.Equals(default(int)))
{
cmd.Parameters.AddWithValue("@CdotRegionId", projectSearchModel.CdotRegionId);
}
using (IDataReader rdr = this.ExecuteReader(cmd))
{
while (rdr.Read())
{
list.Add(new TipSummary()
{
SponsorAgency = rdr["Sponsor"].ToString()
, TipId = rdr["TIPID"].ToString()
, StipId = rdr["STIPID"].ToString()
, TipYear = rdr["TipYear"] != DBNull.Value ? rdr["TipYear"].ToString() : "NULL IN DATABASE"
, Title = rdr["ProjectName"] != DBNull.Value ? rdr["ProjectName"].ToString() :"NULL IN DATABASE"
, ProjectVersionId = (int)rdr["TipProjectVersionId"]
, AmendmentStatus = rdr["AmendmentStatus"] !=DBNull.Value ? rdr["AmendmentStatus"].ToString():""
, AmendmentDate = rdr["AmendmentDate"] != DBNull.Value ? (DateTime)rdr["AmendmentDate"] : (DateTime)DateTime.MinValue
, ImprovementType = rdr["ImprovementType"] !=DBNull.Value ? rdr["ImprovementType"].ToString(): ""
, ProjectType = rdr["ProjectType"] != DBNull.Value ? rdr["ProjectType"].ToString(): ""
, ProjectName = rdr["ProjectName"] != DBNull.Value ? rdr["ProjectName"].ToString() : ""
, COGID = rdr["COGID"] !=DBNull.Value ? rdr["COGID"].ToString() : ""
, VersionStatus = rdr["ProjectVersionStatus"] != DBNull.Value ? rdr["ProjectVersionStatus"].ToString() : ""
});
}
}
// These are processed via the SQL retrieval code optimization, so only process this one when exclude is checked.
if (!String.IsNullOrEmpty(projectSearchModel.TipYear) && projectSearchModel.Exclude_TipYear)
{
list = (from fli in list
where (fli.TipYear != projectSearchModel.TipYear)
select fli).ToList<TipSummary>();
}
if ((!String.IsNullOrEmpty(projectSearchModel.VersionStatus)) && (projectSearchModel.Exclude_ActiveVersion))
{
list = (from fli in list
where (!fli.VersionStatus.Equals(projectSearchModel.VersionStatus))
select fli).ToList<TipSummary>();
}
//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 (!String.IsNullOrEmpty(projectSearchModel.COGID))
{
list = (from fli in list
where ((fli.COGID.ToLower().Contains(projectSearchModel.COGID.ToLower())) && (!projectSearchModel.Exclude_COGID))
|| ((!fli.COGID.ToLower().Contains(projectSearchModel.COGID.ToLower())) && (projectSearchModel.Exclude_COGID))
select fli).ToList<TipSummary>();
}
if (!String.IsNullOrEmpty(projectSearchModel.TipID))
{
list = (from fli in list
where ((fli.TipId.ToLower().Contains(projectSearchModel.TipID.ToLower())) && (!projectSearchModel.Exclude_TipID))
|| ((!fli.TipId.ToLower().Contains(projectSearchModel.TipID.ToLower())) && (projectSearchModel.Exclude_TipID))
select fli).ToList<TipSummary>();
}
if (!String.IsNullOrEmpty(projectSearchModel.StipId))
{
list = (from fli in list
where (fli.StipId.ToLower().Contains(projectSearchModel.StipId.ToLower()))
select fli).ToList<TipSummary>();
}
if (!String.IsNullOrEmpty(projectSearchModel.SponsorAgency))
{
list = (from fli in list
where ((fli.SponsorAgency == projectSearchModel.SponsorAgency) && (!projectSearchModel.Exclude_SponsorAgency))
|| ((fli.SponsorAgency != projectSearchModel.SponsorAgency) && (projectSearchModel.Exclude_SponsorAgency))
select fli).ToList<TipSummary>();
}
if (!String.IsNullOrEmpty(projectSearchModel.ImprovementType))
{
list = (from fli in list
where ((fli.ImprovementType == projectSearchModel.ImprovementType) && (!projectSearchModel.Exclude_ImprovementType))
|| ((fli.ImprovementType != projectSearchModel.ImprovementType) && (projectSearchModel.Exclude_ImprovementType))
select fli).ToList<TipSummary>();
}
if (!String.IsNullOrEmpty(projectSearchModel.AmendmentStatus))
{
list = (from fli in list
where ((fli.AmendmentStatus == projectSearchModel.AmendmentStatus) && (!projectSearchModel.Exclude_AmendmentStatus))
|| ((fli.AmendmentStatus != projectSearchModel.AmendmentStatus) && (projectSearchModel.Exclude_AmendmentStatus))
select fli).ToList<TipSummary>();
}
if (!String.IsNullOrEmpty(projectSearchModel.ProjectType))
{
list = (from fli in list
where ((fli.ProjectType == projectSearchModel.ProjectType) && (!projectSearchModel.Exclude_ProjectType))
|| ((fli.ProjectType != projectSearchModel.ProjectType) && (projectSearchModel.Exclude_ProjectType))
select fli).ToList<TipSummary>();
}
if (!String.IsNullOrEmpty(projectSearchModel.ProjectName))
{
list = (from fli in list
where ((fli.ProjectName.ToLower().Contains(projectSearchModel.ProjectName.ToLower())) && (!projectSearchModel.Exclude_ProjectName))
|| ((!fli.ProjectName.ToLower().Contains(projectSearchModel.ProjectName.ToLower())) && (projectSearchModel.Exclude_ProjectName))
select fli).ToList<TipSummary>();
}
return list;
}
public AmendmentsViewModel GetAmendmentsViewModel(int projectVersionId, string tipYear)
{
var result = new AmendmentsViewModel();
// get project summary info
result.InfoModel = GetProjectInfo(projectVersionId, tipYear);
result.ProjectSummary = GetProjectSummary(projectVersionId);
TIPSearchModel search = new TIPSearchModel()
{
ProjectId = (Int32)result.ProjectSummary.ProjectId
,
TipYearID = GetYearId(tipYear, Enums.TimePeriodType.TimePeriod)
};
result.AmendmentList = GetProjectAmendments(search);
result.ProjectAmendments = result.AmendmentList.FirstOrDefault() ?? new ProjectAmendments();
//result.ProjectAmendments.ProjectVersionId = projectVersionId;
var allowedTypes = new List<Enums.AmendmentType>();
allowedTypes.Add(Enums.AmendmentType.Administrative);
allowedTypes.Add(Enums.AmendmentType.Policy);
result.AmendmentTypes = new Dictionary<int, string>();
foreach (Enums.AmendmentType type in allowedTypes)
{
result.AmendmentTypes.Add((int)type, StringEnum.GetStringValue(type));
}
return result;
}
public DetailViewModel GetDetailViewModel(Int32 projectVersionId, String tipYear)
{
var result = new DetailViewModel();
result.StringValues = new Dictionary<string, string>();
result.GeneralInfo = GetProjectGeneralInfo(projectVersionId);
InfoViewModel InfoViewModel = GetProjectInfoViewModel(projectVersionId, tipYear);
SegmentViewModel SegmentViewModel = GetSegmentViewModel(projectVersionId);
// get project summary info
result.ProjectSummary = GetProjectSummary(projectVersionId);
result.InfoModel = InfoViewModel.InfoModel;
result.ProjectSponsorsModel = InfoViewModel.ProjectSponsorsModel;
//result.StringValues.Add("AdminLevel",GetValueByKey(AvailableAdminLevels, result.InfoModel.AdministrativeLevelId.Value));
//result.StringValues.Add("ProjectType", GetValueByKey(AvailableProjectTypes, result.InfoModel.ProjectTypeId.Value));
//result.StringValues.Add("ImprovementType", GetValueByKey(AvailableImprovementTypes, result.InfoModel.ImprovementTypeId.Value));
//result.StringValues.Add("RoadOrTransit", GetValueByKey(AvailableRoadOrTransitTypes, result.InfoModel.TransportationTypeId.Value));
//result.StringValues.Add("PoolName", GetValueByKey(AvailablePools(result.ProjectSummary), result.InfoModel.ProjectPoolId.Value));
result.Segments = GetProjectSegments(projectVersionId);
result.PoolProjects = GetPoolProjects(projectVersionId);
TIPSearchModel search = new TIPSearchModel()
{
ProjectId = (Int32)result.ProjectSummary.ProjectId
,
TipYearID = GetYearId(tipYear, Enums.TimePeriodType.TimePeriod)
};
result.AmendmentList = GetProjectAmendments(search);
result.MuniShares = GetProjectMunicipalityShares(projectVersionId);
result.CountyShares = GetProjectCountyShares(projectVersionId);
result.FundingDetailPivotModel = GetFundingDetailPivot(projectVersionId);
result.TipProjectFunding = GetFunding(projectVersionId).FirstOrDefault();
return result;
}
/// <summary>
/// Returns a list of projects associated with this TIP
/// </summary>
/// <param name="guid"></param>
/// <returns></returns>
public ActionResult ProjectListCustom(TIPSearchModel projectSearchModel)
{
//DTS did NOT undersand this. The 'year' variable here is NOT a search criterium. -DBD
//The 'df' variable is a dashboard (quick search) filter.
LoadSession();
projectSearchModel.VersionStatusId = (int)Enums.TIPVersionStatus.Active;
var viewModel = new ProjectListViewModel();
viewModel.TipSummary.TipYear = projectSearchModel.TipYear;
viewModel.ProjectList = GetProjectList(projectSearchModel);
viewModel.CurrentSponsors = _tipRepository.GetCurrentTimePeriodSponsorAgencies(projectSearchModel.TipYear, Enums.ApplicationState.TIP).ToDictionary(x => (int)x.OrganizationId, x => x.OrganizationName);
return View(viewModel);
}
public ActionResult NonTipProjectList(string year)
{
var viewModel = new ProjectListViewModel();
viewModel.TipSummary.TipYear = year;
var projectSearchModel = new TIPSearchModel();
projectSearchModel.TipYear = year;
viewModel.ProjectList = _tipRepository.GetTIPProjects(projectSearchModel);
return View(viewModel);
}
private TIPSearchModel ValidateSearchData(TIPSearchModel projectSearchModel, string currentProgram)
{
//Check completeness of TipYear
if ((projectSearchModel.TipYearID == null) && (projectSearchModel.TipYear != null))
{
//Lookup the TipYearID
projectSearchModel.TipYearID = _tipRepository.GetYearId(projectSearchModel.TipYear, Enums.TimePeriodType.TimePeriod);
}
if ((projectSearchModel.TipYearID != null) && (projectSearchModel.TipYear == null))
{
//Lookup the TipYear
projectSearchModel.TipYear = _tipRepository.GetYear((int)projectSearchModel.TipYearID);
}
//Check completeness of SponsorAgency
if ((projectSearchModel.SponsorAgencyID == null) && (projectSearchModel.SponsorAgency != null))
{
//Lookup the SponsorAgencyID
projectSearchModel.SponsorAgencyID = _tipRepository.GetSponsorAgencyID(projectSearchModel.SponsorAgency);
}
if ((projectSearchModel.SponsorAgencyID != null) && (projectSearchModel.SponsorAgency == null))
{
//Lookup the SponsorAgency
projectSearchModel.SponsorAgency = _tipRepository.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 = _tipRepository.GetStatusID(projectSearchModel.AmendmentStatus, statusType);
}
if ((projectSearchModel.AmendmentStatusID != null) && (projectSearchModel.AmendmentStatus == null))
{
//Lookup the AmendmentStatus
projectSearchModel.AmendmentStatus = _tipRepository.GetStatus(projectSearchModel.AmendmentStatusID, statusType);
}
//Check completeness of ImprovementType
if ((projectSearchModel.ImprovementTypeID == null) && (projectSearchModel.ImprovementType != null))
{
//Lookup the ImprovementTypeID
projectSearchModel.ImprovementTypeID = _tipRepository.GetImprovementTypeID(projectSearchModel.ImprovementType);
}
if ((projectSearchModel.ImprovementTypeID != null) && (projectSearchModel.ImprovementType == null))
{
//Lookup the ImprovementType
projectSearchModel.ImprovementType = _tipRepository.GetImprovementType(projectSearchModel.ImprovementTypeID);
}
//Check completeness of ProjectType
if ((projectSearchModel.ProjectTypeID == null) && (projectSearchModel.ProjectType != null))
{
//Lookup the ProjectTypeID
projectSearchModel.ProjectTypeID = _tipRepository.GetProjectTypeID(projectSearchModel.ProjectType);
}
if ((projectSearchModel.ProjectTypeID != null) && (projectSearchModel.ProjectType == null))
{
//Lookup the ProjectType
projectSearchModel.ProjectType = _tipRepository.GetProjectType(projectSearchModel.ProjectTypeID);
}
return projectSearchModel;
}
private TIPSearchModel 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 TIPSearchModel();
LoadSession();
//Get a reference to session object
//ApplicationState appSession = this.GetSession();
//if (CurrentSessionApplicationState.ProjectSearchModel != null)
//{
// result = (TIPSearchModel)CurrentSessionApplicationState.ProjectSearchModel;
//}
//else
//{
_tipRepository.SetProjectSearchDefaults(result);
//Just return some general defaults for now
//result.AmendmentStatus = "";
result.AmendmentStatusID = null;
result.COGID = "";
result.ProjectName = "";
result.ProjectType = "";
result.FundingType = "";
//result.SponsorAgency = "";
result.SponsorAgencyID = null;
result.TipID = "";
result.StipId = String.Empty;
//result.TipYear = "2008-2013";
//result.TipYearID = 8;
//result.ImprovementType = "";
result.ImprovementTypeID = null;
result.VersionStatusId = (int)Enums.TIPVersionStatus.Active;
//}
return result;
}
private IList<TipSummary> GetProjectList(TIPSearchModel projectSearchModel)
{
//Before passing the ProjectSearchModel, make sure it is validated
projectSearchModel = this.ValidateSearchData((TIPSearchModel)projectSearchModel, StringEnum.GetStringValue(CurrentSessionApplicationState.CurrentProgram));
var projectList = _tipRepository.GetTIPProjects(projectSearchModel);
//Now save this projectSearchModel (for future searchs)
CurrentSessionApplicationState.ProjectSearchModel = projectSearchModel;
return projectList;
}
