public IList<RtpSummary> GetRestoreProjectList(int timePeriodId)
{
IList<RtpSummary> list = new List<RtpSummary>();
try
{
using (SqlCommand command = new SqlCommand("[RTP].[GetRestoreProjectList]") { CommandType = CommandType.StoredProcedure })
{
command.Parameters.AddWithValue("@TimePeriodId", timePeriodId);
using (IDataReader rdr = this.ExecuteReader(command))
{
while (rdr.Read())
{
RtpSummary summary = new RtpSummary()
{
ProjectId = rdr["ProjectId"] != DBNull.Value ? rdr["ProjectId"].ToString().SmartParseDefault(default(int)) : default(int)
,
RTPYearTimePeriodID = rdr["TimePeriodId"] != DBNull.Value ? rdr["TimePeriodId"].ToString().SmartParseDefault(default(short)) : default(short)
,
RtpYear = rdr["TimePeriod"].ToString()
,
ProjectVersionId = rdr["ProjectVersionId"] != DBNull.Value ? rdr["ProjectVersionId"].ToString().SmartParseDefault(default(int)) : default(int)
,
ProjectName = rdr["ProjectName"].ToString()
,
LastAmendmentDate = rdr["AmendmentDate"] != DBNull.Value ? rdr["AmendmentDate"].ToString().SmartParseDefault<DateTime>(DateTime.MinValue) : DateTime.MinValue
,
AmendmentStatusId = rdr["ProjectVersionId"] != DBNull.Value ? rdr["ProjectVersionId"].ToString().SmartParseDefault(default(int)) : default(int)
,
AmendmentStatus = rdr["AmendmentStatus"].ToString()
};
list.Add(summary);
}
}
}
}
catch
{
}
return list;
}
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 IList<RtpSummary> GetAmendableProjects(int cycleId, bool excludeHasPending)
{
IList<RtpSummary> list = new List<RtpSummary>();
try
{
using (SqlCommand command = new SqlCommand("[RTP].[GetProjects]") { CommandType = CommandType.StoredProcedure })
{
command.Parameters.AddWithValue("@CycleId", cycleId);
command.Parameters.AddWithValue("@ExcludeHasPending", excludeHasPending);
command.Parameters.AddWithValue("@ExcludeCancelled", true);
using (IDataReader rdr = this.ExecuteReader(command))
{
while (rdr.Read())
{
RtpSummary summary = new RtpSummary()
{
SponsorAgency = rdr["Sponsor"].ToString()
,
ProjectName = rdr["ProjectName"].ToString()
,
RtpYear = rdr["RtpYear"].ToString()
,
ProjectVersionId = rdr["RTPProjectVersionId"] != DBNull.Value ? rdr["RTPProjectVersionId"].ToString().SmartParseDefault(default(int)) : default(int)
};
summary.Cycle.Name = rdr["CycleName"].ToString();
summary.Cycle.Id = rdr["CycleId"].ToString().SmartParseDefault<int>(default(int));
list.Add(summary);
//Logger.Debug("Amendment Status " + rdr["AmendmentStatusId"].ToString());
}
}
}
}
catch (Exception exc)
{
Logger.WarnException("Exception in RtpRepository.GetAmendableProjects", exc);
}
return list;
}
protected RtpSummary GetSummary(List<SqlParameter> parameters, string plan)
{
SqlCommand cmd = new SqlCommand("[RTP].[GetSummary]");
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in parameters)
{
cmd.Parameters.Add(param);
}
RtpSummary model = new RtpSummary();
using (IDataReader rdr = this.ExecuteReader(cmd))
{
if (rdr.Read())
{
model.RtpYear = plan;
//model.IsCurrent = rdr["IsCurrent"] != DBNull.Value ? (bool)rdr["IsCurrent"] : false;
//model.IsPending = rdr["IsPending"] != DBNull.Value ? (bool)rdr["IsPending"] : false;
model.RTPYearTimePeriodID = rdr["TimePeriodID"].ToString().SmartParseDefault<short>(default(short));
model.TimePeriodStatusId = rdr["TimePeriodStatusId"].ToString().SmartParseDefault<int>(default(int));
model.Cycle = new Cycle()
{
Id = rdr["CycleId"].ToString().SmartParseDefault<int>(default(int))
,
Name = rdr["CycleName"].ToString()
,
StatusId = rdr["CycleStatusId"].ToString().SmartParseDefault<int>(default(int))
,
Status = rdr["CycleStatus"].ToString()
,
PriorCycleId = rdr["priorCycleId"].ToString().SmartParseDefault<int>(default(int))
,
NextCycleId = rdr["nextCycleId"].ToString().SmartParseDefault<int>(default(int))
,
NextCycleName = rdr["nextCycle"].ToString()
,
NextCycleStatus = rdr["nextStatus"].ToString()
};
}
}
//model.Cycle = new Cycle();
//model.Cycle = GetCurrentCycle(GetYearId(plan));
return model;
}
/// <summary>
/// Copies a Project in the database
/// </summary>
/// <param name="projectVersionId"></param>
/// <returns>ProjectVersionID</returns>
public RtpSummary CopyProject(string plan, Int32 cycleId, Int32 projectVersionId)
{
CycleAmendment cycle = GetCurrentCycle(GetYearId(plan, Enums.TimePeriodType.PlanYear));
RtpSummary result = null;
using (SqlCommand command = new SqlCommand("[RTP].[CopyProject]") { CommandType = CommandType.StoredProcedure })
{
command.Parameters.AddWithValue("@CurrentProjectVersionId", projectVersionId);
if(!String.IsNullOrEmpty(plan))
command.Parameters.AddWithValue("@TimePeriod", plan);
if(!cycleId.Equals(default(Int32)) )
command.Parameters.AddWithValue("@CycleId", cycleId);
using (IDataReader rdr = this.ExecuteReader(command))
{
if (rdr.Read())
{
result = new RtpSummary()
{
SponsorAgency = rdr["Sponsor"].ToString()
,
TIPId = rdr["TIPID"].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() : ""
,
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() : ""
};
}
}
}
return result;
}
protected IDictionary<int, string> AvailablePools(RtpSummary summary)
{
return GetPoolNames(1, summary.RTPYearTimePeriodID);
}
/// <summary>
/// Gets a human readable summary for the RTP Project Version
/// </summary>
/// <param name="versionId"></param>
/// <param name="tipYear"></param>
/// <remarks>
/// This is used in the project general information block on the right of
/// the project tabs</remarks>
/// <returns></returns>
public RtpSummary GetProjectSummary(int versionId, string plan)
{
//throw new NotImplementedException();
RtpSummary result = null;
using (SqlCommand command = new SqlCommand("[RTP].[GetProjectVersionInfoSummary]") { CommandType = CommandType.StoredProcedure })
{
command.Parameters.AddWithValue("@RtpProjectVersion", versionId);
command.Parameters.AddWithValue("@Year", plan);
using (IDataReader rdr = ExecuteReader(command))
{
if (rdr.Read())
{
result = new RtpSummary();
result.ProjectType = rdr["ImprovementType"].ToString();
result.ProjectName = rdr["ProjectName"].ToString();
result.COGID = rdr["COGID"].ToString();
result.ProjectId = (int)rdr["ProjectId"];
//To be editable, a ProjectVersion must be both Current and in the Current TIP
// IsVersionCurrent = Active
//bool IsVersionCurrent = rdr["IsVersionCurrent"] != DBNull.Value ? (bool)rdr["IsVersionCurrent"] : false;
result.IsActive = rdr["IsVersionCurrent"] != DBNull.Value ? (bool)rdr["IsVersionCurrent"] : false;
//result.IsCurrent = rdr["IsRtpCurrent"] != DBNull.Value ? (bool)rdr["IsRtpCurrent"] : false;
//result.IsPending = rdr["IsPending"] != DBNull.Value ? (bool)rdr["IsPending"] : false;
result.IsTopStatus = rdr["IsTopStatus"] != DBNull.Value ? (bool)rdr["IsTopStatus"] : false;
result.AmendmentStatusId = (int)rdr["AmendmentStatusId"];
//(Int32)TIPAmendmentStatus.Amended
//result.IsEditable = result.IsCurrent;
//&& (new int[] { (int)TIPAmendmentStatus.Proposed, (int)TIPAmendmentStatus.Submitted }.Contains(amendmentStatusId));
result.AmendmentStatus = rdr["AmendmentStatus"].ToString();
result.ProjectVersionId = rdr["ProjectVersionID"] != DBNull.Value ? (int)rdr["ProjectVersionID"] : default(int);
result.PreviousVersionId = rdr["PreviousVersionID"].ToString().SmartParseDefault<int>(default(int));
result.NextVersionId = rdr["NextVersionID"] != DBNull.Value ? (int?)rdr["NextVersionID"] : null;
result.SponsorAgency = rdr["Sponsor"].ToString();
result.RtpYear = rdr["RtpYear"].ToString();
result.NextVersionYear = rdr["NextVersionRtpYear"].ToString();
result.PreviousVersionYear = rdr["PreviousVersionRtpYear"].ToString();
result.RTPYearTimePeriodID = rdr["RtpYearTimePeriodID"] != DBNull.Value ? (short)rdr["RtpYearTimePeriodID"] : (short)0;
result.ProjectType = rdr["ProjectType"].ToString();
result.PlanType = rdr["PlanType"].ToString();
result.AdoptionDate = rdr["AdoptionDate"] != DBNull.Value ? (DateTime)rdr["AdoptionDate"] : DateTime.MinValue;
result.LastAmendmentDate = rdr["LastAmendmentDate"] != DBNull.Value ? (DateTime)rdr["LastAmendmentDate"] : DateTime.MinValue;
result.RtpId = result.RTPYearTimePeriodID.ToString();
result.CategoryId = rdr["RTPCategoryID"] != DBNull.Value ? (int)rdr["RTPCategoryID"] : Int32.MinValue;
result.TimePeriodStatusId = rdr["TimePeriodStatusId"].ToString().SmartParseDefault<int>(default(int));
}
}
}
// Set cycle info
result.Cycle = GetProjectCycleInfo(versionId);
//result.Cycle.Name = cycleInfo.Cycle;
//result.Cycle.Id = cycleInfo.CycleId;
return result;
}
