public JsonResult AddSegment(SegmentModel model)
{
int segmentId = 0;
try
{
segmentId = _surveyRepository.AddSegment(model);
if (segmentId == 0)
throw new Exception("Returned 0 on projectVersionId" + model.ProjectVersionId);
}
catch (Exception ex)
{
return Json(new { error = "Changes could not be stored. An error has been logged." });
}
return Json(new { message = "Segment successfully added.", segmentId = segmentId });
}
public JsonResult UpdateSegment(SegmentModel model)
{
try
{
XMLService xml = new XMLService(_surveyRepository);
if (!String.IsNullOrEmpty(model.LRSRecord))
{
string data = xml.GenerateXml(xml.GetScheme((int)SchemeName.LRSProjects), new LRSRecord() { Columns = model.LRSRecord.ToDictionary(',') });
model.LRSxml = data;
}
else model.LRSxml = xml.GenerateXml(null, null);
_surveyRepository.UpdateSegment(model);
}
catch (Exception ex)
{
return Json(new { message = "Changes could not be stored. An error has been logged." });
}
return Json(new { message = "Segment successfully updated." });
}
public JsonResult UpdateSegmentSummary(SegmentModel model)
{
try
{
_surveyRepository.UpdateSegmentSummary(model);
}
catch (Exception ex)
{
return Json(new { error = "Changes could not be stored. An error has been logged." });
}
return Json(new { message = "Segment successfully updated." });
}
//private IDictionary<int, string> GetPlanScenarios(List<SqlParameter> sqlParams)
//{
// return GetLookupCollection("[RTP].[GetPlanScenarios]", "NetworkID", "Scenario", sqlParams);
//}
//public IDictionary<int, string> GetPlanScenariosByCycleId(int cycleId)
//{
// List<SqlParameter> sqlParams = new List<SqlParameter>();
// sqlParams.Add(new SqlParameter("@CycleId", cycleId));
// return GetPlanScenarios(sqlParams);
//}
//public IDictionary<int, string> GetPlanScenariosForCurrentCycle(int planYearId)
//{
// var cycle = GetCurrentCycle(planYearId); //GetAmendmentDetails(planYearId, RTPCycleStatus.Active);
// List<SqlParameter> sqlParams = new List<SqlParameter>();
// sqlParams.Add(new SqlParameter("@CycleId", cycle.Id));
// sqlParams.Add(new SqlParameter("@PlanYearId", planYearId));
// return GetPlanScenarios(sqlParams);
//}
//public IDictionary<int, string> GetPlanScenariosByCycle(string cycle)
//{
// List<SqlParameter> sqlParams = new List<SqlParameter>();
// sqlParams.Add(new SqlParameter("@Cycle", cycle));
// return GetPlanScenarios(sqlParams);
//}
//public IDictionary<int, string> GetPlanScenariosByOpenYear(string openYear)
//{
// List<SqlParameter> sqlParams = new List<SqlParameter>();
// sqlParams.Add(new SqlParameter("@OpenYear", openYear));
// return GetPlanScenarios(sqlParams);
//}
//public IDictionary<int, string> GetPlanScenarios(int planYearId)
//{
// List<SqlParameter> sqlParams = new List<SqlParameter>();
// sqlParams.Add(new SqlParameter("@PlanYearId", planYearId));
// return GetPlanScenarios(sqlParams);
//}
//public Cycle GetAmendmentDetails(int timePeriodId, Enums.RTPCycleStatus status)
//{
// return GetCycleDetails(timePeriodId, status);
//}
//public ICollection<CycleAmendment> GetCollectionOfCycles(int timePeriodId)
//{
// return GetCollectionOfCycles(timePeriodId, null);
//}
//public ICollection<CycleAmendment> GetCollectionOfCycles(int timePeriodId, Enums.RTPCycleStatus? status)
//{
// SqlCommand cmd = new SqlCommand("[RTP].[GetCurrentPlanCycle]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@TimePeriodId", timePeriodId);
// if (status.HasValue)
// {
// cmd.Parameters.AddWithValue("@StatusId", (int)status);
// }
// var result = new List<CycleAmendment>();
// CycleAmendment cycle = null;
// using (IDataReader rdr = this.ExecuteReader(cmd))
// {
// while (rdr.Read())
// {
// cycle = new CycleAmendment()
// {
// Id = rdr["id"] != DBNull.Value ? rdr["id"].ToString().SmartParse<int>() : default(int)
// ,
// Name = rdr["cycle"].ToString()
// ,
// StatusId = rdr["statusId"].ToString().SmartParseDefault<int>(default(int))
// };
// cycle.Status = StringEnum.GetStringValue((Enums.RTPCycleStatus)cycle.StatusId);
// result.Add(cycle);
// }
// }
// return result;
//}
//public CycleAmendment GetCurrentCycle(int timePeriodId)
//{
// var cycles = GetCollectionOfCycles(timePeriodId);
// var value = (CycleAmendment)cycles.SingleOrDefault(x => x.StatusId == (int)Enums.RTPCycleStatus.Active) ?? (CycleAmendment)cycles.SingleOrDefault(x => x.StatusId == (int)Enums.RTPCycleStatus.Pending);
// return value ?? new CycleAmendment();
//}
//public Cycle GetCycleDetails(int timePeriodId)
//{
// return GetCycleDetails(timePeriodId, Enums.RTPCycleStatus.Active);
//}
//public Cycle GetCycleDetails(int timePeriodId, Enums.RTPCycleStatus status)
//{
// SqlCommand cmd = new SqlCommand("[RTP].[GetCurrentPlanCycle]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@TimePeriodId", timePeriodId);
// cmd.Parameters.AddWithValue("@StatusId", (int)status);
// var result = new Cycle();
// using (IDataReader rdr = this.ExecuteReader(cmd))
// {
// if (rdr.Read())
// {
// result.Id = rdr["id"] != DBNull.Value ? rdr["id"].ToString().SmartParse<int>() : default(int);
// result.Name = rdr["cycle"].ToString();
// result.StatusId = rdr["statusId"].ToString().SmartParseDefault<int>(default(int));
// }
// }
// return result;
//}
///// <summary>
///// Populate view model for Plan List
///// </summary>
///// <returns></returns>
//public RtpListViewModel GetRtpListViewModel()
//{
// RtpListViewModel model = new RtpListViewModel();
// SqlCommand cmd = new SqlCommand("[RTP].[GetPrograms]");
// cmd.CommandType = CommandType.StoredProcedure;
// IList<RtpStatusModel> rtpPrograms = new List<RtpStatusModel>();
// RtpStatusModel sm = null;
// using (IDataReader rdr = this.ExecuteReader(cmd))
// {
// //be sure we got a reader
// while (rdr.Read())
// {
// sm = new RtpStatusModel();
// sm.ProgramId = (int)rdr["ProgramId"];
// sm.TimePeriodId = Convert.ToInt32(rdr["TimePeriodId"]);
// sm.Plan = rdr["TimePeriod"].ToString();
// sm.Adoption = rdr["AdoptionDate"] != DBNull.Value ? (DateTime?)rdr["AdoptionDate"] : null;
// sm.CDOTAction = rdr["CDOTActionDate"] != DBNull.Value ? (DateTime?)rdr["CDOTActionDate"] : null;
// sm.LastAmended = rdr["LastAmendmentDate"] != DBNull.Value ? (DateTime?)rdr["LastAmendmentDate"] : null;
// sm.PublicHearing = rdr["PublicHearingDate"] != DBNull.Value ? (DateTime?)rdr["PublicHearingDate"] : null;
// sm.USDOTApproval = rdr["USDOTApprovalDate"] != DBNull.Value ? (DateTime?)rdr["USDOTApprovalDate"] : null;
// sm.Notes = rdr["Notes"].ToString();
// //sm.IsCurrent = (bool)rdr["Current"];
// //sm.IsPending = (bool)rdr["Pending"];
// //sm.IsPrevious = (bool)rdr["Previous"];
// sm.RtpSummary = GetSummary(sm.Plan);
// rtpPrograms.Add(sm);
// }
// }
// model.RTPs = rtpPrograms;
// return model;
//}
//#region IRtpRepository Members
///// <summary>
///// Create a new RTP Plan
///// </summary>
///// <param name="startYear"></param>
///// <param name="endYear"></param>
///// <param name="offset"></param>
///// <returns></returns>
//public void CreateRtp(string timePeriod)
//{
// SqlCommand cmd = new SqlCommand("[RTP].[CreatePlan]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@TimePeriod ", timePeriod);
// cmd.Parameters.AddWithValue("@StatusId ", Enums.RtpTimePeriodStatus.New);
// this.ExecuteNonQuery(cmd);
//}
//public void SetPlanCurrent(int timePeriodId)
//{
// SqlCommand cmd = new SqlCommand("[RTP].[SetPlanCurrent]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@TimePeriodId", timePeriodId);
// this.ExecuteNonQuery(cmd);
//}
//public int CreateCategory(string categoryName, string shortName, string description, string plan)
//{
// SqlCommand cmd = new SqlCommand("[RTP].[CreateCategory]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@TimePeriod", plan);
// cmd.Parameters.AddWithValue("@CategoryName", categoryName);
// cmd.Parameters.AddWithValue("@ShortName", shortName);
// cmd.Parameters.AddWithValue("@Description", description);
// SqlParameter outParam = new SqlParameter("@CategoryId", SqlDbType.Int);
// outParam.Direction = ParameterDirection.Output;
// cmd.Parameters.Add(outParam);
// this.ExecuteNonQuery(cmd);
// return (int)cmd.Parameters["@CategoryId"].Value;
//}
//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> GetAmendableProjects(int timePeriodId)
//{
// IList<RtpSummary> list = new List<RtpSummary>();
// try
// {
// using (SqlCommand command = new SqlCommand("[RTP].[GetProjects]") { CommandType = CommandType.StoredProcedure })
// {
// command.Parameters.AddWithValue("@YearID", timePeriodId);
// //command.Parameters.AddWithValue("@AmendmentStatusID", (int)RTPAmendmentStatus.Pending);
// 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)
// };
// list.Add(summary);
// }
// }
// }
// }
// catch
// {
// }
// return list;
//}
///// <summary>
/////
///// </summary>
///// <param name="id"></param>
///// <returns></returns>
//public StatusViewModel GetRtpStatusViewModel(string year)
//{
// int intOut;
// StatusViewModel model = new StatusViewModel();
// SqlCommand cmd = new SqlCommand("[RTP].[GetStatus]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Add(new SqlParameter("@YEAR", SqlDbType.NVarChar));
// cmd.Parameters[0].Value = year;
// model.RtpSummary.RtpYear = year;
// var sm = new RtpStatusModel();
// using (IDataReader rdr = this.ExecuteReader(cmd))
// {
// if (rdr.Read())
// {
// sm.ProgramId = (int)rdr["ProgramId"];
// sm.TimePeriodId = Convert.ToInt32(rdr["TimePeriodId"]);
// sm.Plan = rdr["TimePeriod"].ToString();
// sm.Adoption = rdr["AdoptionDate"] != DBNull.Value ? (DateTime?)rdr["AdoptionDate"] : null;
// //sm.EPAApproval = rdr["USEPAApprovalDate"] != DBNull.Value ? (DateTime?)rdr["USEPAApprovalDate"] : null;
// //sm.GovernorApproval = rdr["GovernorApprovalDate"] != DBNull.Value ? (DateTime?)rdr["GovernorApprovalDate"] : null;
// sm.LastAmended = rdr["LastAmendmentDate"] != DBNull.Value ? (DateTime?)rdr["LastAmendmentDate"] : null;
// sm.PublicHearing = rdr["PublicHearingDate"] != DBNull.Value ? (DateTime?)rdr["PublicHearingDate"] : null;
// sm.CDOTAction = rdr["CDOTActionDate"] != DBNull.Value ? (DateTime?)rdr["CDOTActionDate"] : null;
// sm.USDOTApproval = rdr["USDOTApprovalDate"] != DBNull.Value ? (DateTime?)rdr["USDOTApprovalDate"] : null;
// sm.Notes = rdr["Notes"].ToString();
// //sm.IsCurrent = (bool)rdr["Current"];
// //sm.IsPending = (bool)rdr["Pending"];
// //sm.IsPrevious = (bool)rdr["Previous"];
// sm.BaseYearId = rdr["BaseYearId"] != DBNull.Value ? (Int32.TryParse(rdr["BaseYearId"].ToString(), out intOut) ? Int32.Parse(rdr["BaseYearId"].ToString()) : 0) : 0;
// sm.BaseYear = rdr["BaseYear"] != DBNull.Value ? rdr["BaseYear"].ToString() : String.Empty;
// sm.Description = rdr["Description"].ToString();
// model.RtpSummary.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()
// };
// }
// }
// model.RtpStatus = sm;
// List<SqlParameter> sqlParams = new List<SqlParameter>();
// sqlParams.Add(new SqlParameter("@TimePeriodTypeID", Enums.TimePeriodType.Year));
// model.AvailableYears = GetLookupCollection("[dbo].[Lookup_GetTimePeriodsByTypeId]", "Value", "Label", sqlParams);
// sqlParams = new List<SqlParameter>();
// sqlParams.Add(new SqlParameter("@TimePeriodId", sm.TimePeriodId));
// model.CurrentPlanCycles = GetLookupCollection("[RTP].[GetCurrentPlanCycles]", "id", "cycle", sqlParams);
// sqlParams = new List<SqlParameter>();
// sqlParams.Add(new SqlParameter("@TimePeriodId", sm.TimePeriodId));
// model.PlanUnusedCycles = GetLookupCollection("[RTP].[GetNextCycleKeys]", "id", "cycle", sqlParams);
// model.AvailableCycles = GetLookupCollection("[RTP].[GetAvailablePlanCycles]", "id", "cycle");
// //Enums.RTPCycleStatus rtpCycleStatus = model.RtpStatus.IsCurrent ? Enums.RTPCycleStatus.Active : Enums.RTPCycleStatus.Pending;
// //model.RtpSummary.Cycle = GetAmendmentDetails(sm.TimePeriodId, rtpCycleStatus);
// return model;
//}
//public String SetActiveCycle(int cycleId, int timePeriodId)
//{
// string result = "";
// SqlCommand cmd = new SqlCommand("[RTP].[SetActiveCycle]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@CycleId", cycleId);
// cmd.Parameters.AddWithValue("@TimePeriodId", timePeriodId);
// //cmd.Parameters.AddWithValue("@CycleStatus", RTPCycleStatus.);
// try
// {
// this.ExecuteNonQuery(cmd);
// }
// catch (Exception ex)
// {
// result = ex.Message;
// }
// return result;
//}
//public String UpdateTimePeriodCycleOrder(string cycles)
//{
// string result = String.Empty;
// var modifiedOrder = new List<int>(GetModifiedArrayIndexes(cycles));
// int position = 0;
// if (modifiedOrder.Count > 0)
// {
// using (TransactionScope ts = new TransactionScope())
// {
// foreach (var item in modifiedOrder)
// {
// if (!UpdateCycleSort((int)item, ++position))
// {
// //error = true;
// throw new Exception("Sort Exception");
// }
// }
// ts.Complete();
// }
// }
// return result;
//}
//private bool UpdateCycleSort(int cycleId, int order)
//{
// SqlCommand cmd = new SqlCommand("[RTP].[UpdateCycleSort]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@CycleId", cycleId);
// cmd.Parameters.AddWithValue("@Order", order);
// SqlParameter sqlout = new SqlParameter("@error",SqlDbType.Bit);
// sqlout.Direction = ParameterDirection.Output;
// cmd.Parameters.Add(sqlout);
// try
// {
// this.ExecuteNonQuery(cmd);
// if((bool)cmd.Parameters["@error"].Value)
// return false;
// return true;
// }
// catch (Exception ex)
// {
// Debug.WriteLine(ex.Message);
// }
// return false;
//}
//private IEnumerable<int> GetModifiedArrayIndexes(string positions)
//{
// IEnumerable<string> values = positions.Split(new char[] { ',' });
// foreach (var position in values)
// {
// yield return Convert.ToInt32(position.Replace("cycle_", ""));
// }
//}
///// <summary>
///// Update the RTP Status in the database
///// </summary>
///// <param name="model"></param>
//public void UpdateRtpStatus(RtpStatusModel model)
//{
// SqlCommand cmd = new SqlCommand("[RTP].[UpdateRtpStatus]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@PROGRAMID", model.ProgramId);
// cmd.Parameters.AddWithValue("@TIMEPERIODID", model.TimePeriodId);
// cmd.Parameters.AddWithValue("@YEAR", model.Plan);
// cmd.Parameters.AddWithValue("@BaseYearId", model.BaseYearId > 0 ? (object)model.BaseYearId : 0);
// //cmd.Parameters.AddWithValue("@CURRENT", model.IsCurrent);
// //cmd.Parameters.AddWithValue("@PENDING", model.IsPending);
// //cmd.Parameters.AddWithValue("@PREVIOUS", model.IsPrevious);
// cmd.Parameters.AddWithValue("@NOTES", model.Notes);
// cmd.Parameters.AddWithValue("@Description", model.Description);
// cmd.Parameters.AddWithValue("@ADOPTIONDATE", model.Adoption != null ? (object)model.Adoption.Value : (object)DBNull.Value);
// //cmd.Parameters.AddWithValue("@GOVAPPROVALDATE ", model.GovernorApproval != null ? (object)model.GovernorApproval.Value : (object)DBNull.Value);
// cmd.Parameters.AddWithValue("@PUBLICHEARINGDATE", model.PublicHearing != null ? (object)model.PublicHearing.Value : (object)DBNull.Value);
// cmd.Parameters.AddWithValue("@DOTDATE", model.USDOTApproval != null ? (object)model.USDOTApproval.Value : (object)DBNull.Value);
// cmd.Parameters.AddWithValue("@CDOTDATE", model.CDOTAction != null ? (object)model.CDOTAction.Value : (object)DBNull.Value);
// //cmd.Parameters.AddWithValue("@EPADATE ", model.EPAApproval != null ? (object)model.EPAApproval.Value : (object)DBNull.Value);
// this.ExecuteNonQuery(cmd);
//}
///// <summary>
///// Add a cycle to a TimePeriod
///// </summary>
///// <param name="timePeriodId"></param>
///// <param name="cycleId"></param>
//public string AddCycleToTimePeriod(string timePeriod, int cycleId)
//{
// string result = "";
// SqlCommand cmd = new SqlCommand("[RTP].[AddCycleToTimePeriod]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@TimePeriodId", GetYearId(timePeriod));
// cmd.Parameters.AddWithValue("@CycleId", cycleId);
// cmd.Parameters.AddWithValue("@StatusId", Enums.RTPCycleStatus.Inactive);
// try
// {
// this.ExecuteNonQuery(cmd);
// }
// catch (Exception ex)
// {
// result = ex.Message;
// }
// return result;
//}
///// <summary>
///// Remove a Cycle from TimePeriod
///// </summary>
///// <param name="cycleId"></param>
//public string RemoveCycleFromTimePeriod(int cycleId)
//{
// string result = "";
// SqlCommand cmd = new SqlCommand("[RTP].[RemoveCycleFromTimePeriod]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@CycleId", cycleId);
// cmd.Parameters.AddWithValue("@StatusId", Enums.RTPCycleStatus.New);
// try
// {
// this.ExecuteNonQuery(cmd);
// }
// catch (Exception ex)
// {
// result = ex.Message;
// }
// return result;
//}
//public string UpdateTimePeriodStatusId(int timePeriodId, int statusId)
//{
// string result = "";
// SqlCommand cmd = new SqlCommand("[dbo].[UpdateTimePeriodStatusId]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@TimePeriodId", timePeriodId);
// cmd.Parameters.AddWithValue("@StatusId", statusId);
// try
// {
// this.ExecuteNonQuery(cmd);
// }
// catch (Exception ex)
// {
// result = ex.Message;
// }
// return result;
//}
///// <summary>
///// Create a Cycle
///// </summary>
///// <param name="cycle"></param>
//public string CreateCycle(string cycle)
//{
// string result = "";
// SqlCommand cmd = new SqlCommand("[RTP].[CreateCycle]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@Cycle", cycle);
// try
// {
// this.ExecuteNonQuery(cmd);
// }
// catch (Exception ex)
// {
// result = ex.Message;
// }
// return result;
//}
//public string UpdateCycleName(int cycleId, string cycle)
//{
// string result = String.Empty;
// SqlCommand cmd = new SqlCommand("[RTP].[UpdateCycleName]");
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@CycleId", cycleId);
// cmd.Parameters.AddWithValue("@Cycle", cycle);
// try
// {
// this.ExecuteNonQuery(cmd);
// }
// catch (Exception ex)
// {
// result = ex.Message;
// }
// return result;
//}
/// <summary>
/// Create a new Project in the database
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int CreateProject(string projectName, string facilityName, int timePeriodId, int sponsorOrganizationId, int sponsorContactId, int improvementTypeId, string startAt, string endAt)
{
int retval = default(int);
try
{
using (TransactionScope ts = new TransactionScope())
{
using (SqlCommand command = new SqlCommand("[Survey].[CreateProject]") { CommandType = CommandType.StoredProcedure })
{
command.Parameters.AddWithValue("@ProjectName", projectName);
command.Parameters.AddWithValue("@SponsorOrganizationId", sponsorOrganizationId);
if (!sponsorContactId.Equals(default(int))) command.Parameters.AddWithValue("@SponsorContactId", sponsorContactId);
command.Parameters.AddWithValue("@FacilityName", facilityName);
command.Parameters.AddWithValue("@TimePeriodId", timePeriodId);
//command.Parameters.AddWithValue("@AmendmentStatusId", (int)Enums.RTPAmendmentStatus.Pending);
command.Parameters.AddWithValue("@VersionStatusId", (int)DRCOG.Domain.Enums.SurveyVersionStatus.Pending);
//command.Parameters.AddWithValue("@SponsorContactId", sponsorContactId);
command.Parameters.AddWithValue("@UpdateStatusId", (int)DRCOG.Domain.Enums.SurveyUpdateStatus.Edited);
command.Parameters.AddWithValue("@ImprovementTypeId", improvementTypeId);
//command.Parameters.AddWithValue("@ActionStatusId", default(int));
//command.Parameters.AddWithValue("@AmendmentTypeId", (int)AmendmentType.Administrative);
SqlParameter outParam = new SqlParameter("@ProjectVersionId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
command.Parameters.Add(outParam);
this.ExecuteNonQuery(command);
retval = (int)command.Parameters["@ProjectVersionId"].Value;
}
if (retval.Equals(default(int))) throw new Exception("ProjectVersionId was not returned");
SegmentModel segmentModel = new SegmentModel()
{
ProjectVersionId = retval
,
FacilityName = facilityName
,
StartAt = startAt
,
EndAt = endAt
};
int segmentid = this.AddSegment(segmentModel);
if (segmentid.Equals(default(int))) throw new Exception("Segment was not created");
ts.Complete();
}
}
catch (Exception exc)
{
}
return retval;
}
public void UpdateSegmentSummary(SegmentModel model)
{
using (SqlCommand command = new SqlCommand("[dbo].[UpdateSegmentSummary]") { CommandType = CommandType.StoredProcedure })
{
command.Parameters.AddWithValue("@SegmentId", model.SegmentId);
command.Parameters.AddWithValue("@FacilityName", model.FacilityName != null ? (object)model.FacilityName.ToString() : (object)DBNull.Value);
command.Parameters.AddWithValue("@StartAt", model.StartAt != null ? (object)model.StartAt.ToString() : (object)DBNull.Value);
command.Parameters.AddWithValue("@EndAt", model.EndAt != null ? (object)model.EndAt.ToString() : (object)DBNull.Value);
command.Parameters.AddWithValue("@NetworkId", model.NetworkId > 0 ? model.NetworkId : (object)DBNull.Value);
command.Parameters.AddWithValue("@OpenYear", model.OpenYear > 0 ? model.OpenYear : (object)DBNull.Value);
command.Parameters.AddWithValue("@LanesBase", model.LanesBase);
command.Parameters.AddWithValue("@LanesFuture", model.LanesFuture);
this.ExecuteNonQuery(command);
}
}
public void UpdateSegment(SegmentModel model)
{
using (SqlCommand command = new SqlCommand("[dbo].[UpdateSegment]") { CommandType = CommandType.StoredProcedure })
{
command.Parameters.AddWithValue("@SegmentId", model.SegmentId);
command.Parameters.AddWithValue("@FacilityName", model.FacilityName != null ? (object)model.FacilityName.ToString() : (object)DBNull.Value);
command.Parameters.AddWithValue("@StartAt", model.StartAt != null ? (object)model.StartAt.ToString() : (object)DBNull.Value);
command.Parameters.AddWithValue("@EndAt", model.EndAt != null ? (object)model.EndAt.ToString() : (object)DBNull.Value);
command.Parameters.AddWithValue("@NetworkId", model.NetworkId > 0 ? model.NetworkId : (object)DBNull.Value);
command.Parameters.AddWithValue("@ImprovementTypeId", model.ImprovementTypeId > 0 ? model.ImprovementTypeId : (object)DBNull.Value);
command.Parameters.AddWithValue("@PlanFacilityTypeId", model.PlanFacilityTypeId > 0 ? model.PlanFacilityTypeId : (object)DBNull.Value);
command.Parameters.AddWithValue("@ModelingFacilityTypeId", model.ModelingFacilityTypeId > 0 ? model.ModelingFacilityTypeId : (object)DBNull.Value);
command.Parameters.AddWithValue("@OpenYear", model.OpenYear > 0 ? model.OpenYear : (object)DBNull.Value);
command.Parameters.AddWithValue("@LanesBase", model.LanesBase > 0 ? model.LanesBase : (object)DBNull.Value);
command.Parameters.AddWithValue("@LanesFuture", model.LanesFuture > 0 ? model.LanesFuture : (object)DBNull.Value);
command.Parameters.AddWithValue("@SpacesBase", model.SpacesBase > 0 ? model.SpacesBase : (object)DBNull.Value);
command.Parameters.AddWithValue("@SpacesFuture", model.SpacesFuture > 0 ? model.SpacesFuture : (object)DBNull.Value);
command.Parameters.AddWithValue("@AssignmentStatusId", model.AssignmentStatusID > 0 ? model.AssignmentStatusID : (object)DBNull.Value);
command.Parameters.AddWithValue("@Length", model.Length > 0 ? model.Length : (object)DBNull.Value);
command.Parameters.AddWithValue("@ModelingCheck", model.ModelingCheck);
if (!String.IsNullOrEmpty(model.LRSxml))
{
byte[] encodedString = System.Text.Encoding.UTF8.GetBytes(model.LRSxml);
MemoryStream ms = new MemoryStream(encodedString);
ms.Flush();
ms.Position = 0;
XmlDocument xml = new XmlDocument();
xml.Load(ms);
using (XmlNodeReader xnr = new XmlNodeReader(xml))
{
command.Parameters.Add("@xml", SqlDbType.Xml).Value = new SqlXml(xnr);
//command.Parameters.AddWithValue("@xml", !String.IsNullOrEmpty(model.LRSxml) ? model.LRSxml : (object)DBNull.Value);
command.Parameters.AddWithValue("@xmlSchemeId", !String.IsNullOrEmpty(model.LRSxml) ? (int)SchemeName.LRSProjects : (object)DBNull.Value);
}
}
this.ExecuteNonQuery(command);
}
}
public SegmentModel GetSegmentDetails(int segmentId)
{
DataTable data;
using (SqlCommand command = new SqlCommand("[dbo].[GetSegment]") { CommandType = CommandType.StoredProcedure })
{
command.Parameters.AddWithValue("@SegmentId", segmentId);
data = this.ExecuteDataTable(command);
}
SegmentModel model;
DataRow row = data.Rows[0];
model = new SegmentModel()
{
SegmentId = segmentId
,
FacilityName = row["FacilityName"].ToString()
,
StartAt = row["StartAt"].ToString()
,
EndAt = row["EndAt"].ToString()
,
NetworkId = row["NetworkId"].ToString().SmartParse<int>()
,
ImprovementTypeId = row["ImprovementTypeId"].ToString().SmartParse<int>()
,
PlanFacilityTypeId = row["PlanFacilityTypeId"].ToString().SmartParse<int>()
,
OpenYear = row["OpenYear"].ToString().SmartParse<short>()
,
LanesBase = row["LanesBase"].ToString().SmartParse<short>()
,
LanesFuture = row["LanesFuture"].ToString().SmartParse<short>()
,
SpacesBase = row["SpacesBase"].ToString().SmartParse<short>()
,
SpacesFuture = row["SpacesFuture"].ToString().SmartParse<short>()
,
AssignmentStatusID = row["AssignmentStatusID"].ToString().SmartParse<int>()
,
Length = row["Length"].ToString().SmartParseDefault(default(decimal))
,
ModelingCheck = row["ModelingCheck"].ToString().SmartParseDefault(default(bool))
,
LRSObjectID = row["LRSObjectID"].ToString().SmartParse<int>()
};
return model;
}
public Int32 AddSegment(SegmentModel model)
{
int retval = default(int);
using (SqlCommand command = new SqlCommand("[dbo].[AddSegment]") { CommandType = CommandType.StoredProcedure })
{
command.Parameters.AddWithValue("@ProjectVersionId", model.ProjectVersionId);
command.Parameters.AddWithValue("@FacilityName", model.FacilityName != null ? (object)model.FacilityName.ToString() : (object)DBNull.Value);
command.Parameters.AddWithValue("@StartAt", model.StartAt != null ? (object)model.StartAt.ToString() : (object)DBNull.Value);
command.Parameters.AddWithValue("@EndAt", model.EndAt != null ? (object)model.EndAt.ToString() : (object)DBNull.Value);
command.Parameters.AddWithValue("@ImprovementTypeId", model.ImprovementTypeId > 0 ? model.ImprovementTypeId : (object)DBNull.Value);
command.Parameters.AddWithValue("@PlanFacilityTypeId", model.PlanFacilityTypeId > 0 ? model.PlanFacilityTypeId : (object)DBNull.Value);
command.Parameters.AddWithValue("@OpenYear", model.OpenYear > 0 ? model.OpenYear : (object)DBNull.Value);
command.Parameters.AddWithValue("@LanesBase", model.LanesBase > 0 ? model.LanesBase : (object)DBNull.Value);
command.Parameters.AddWithValue("@LanesFuture", model.LanesFuture > 0 ? model.LanesFuture : (object)DBNull.Value);
command.Parameters.AddWithValue("@SpacesFuture", model.SpacesFuture > 0 ? model.SpacesFuture : (object)DBNull.Value);
command.Parameters.AddWithValue("@AssignmentStatusId", model.AssignmentStatusID > 0 ? model.AssignmentStatusID : (object)DBNull.Value);
command.Parameters.AddWithValue("@Length", model.Length > 0 ? model.Length : (object)DBNull.Value);
SqlParameter outParam = new SqlParameter("@SegmentId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
command.Parameters.Add(outParam);
this.ExecuteNonQuery(command);
retval = (int)command.Parameters["@SegmentId"].Value;
}
return retval;
}