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