public static Place FindPlaceByRoad(Road road)
{
using (var context = new CopacesCcDbContext())
{
//var fullPlaceName = road.Jurisdiction;
//var placeName = fullPlaceName
// .Replace("COUNTY", "")
// .Replace("CITY", "")
// .Trim();
//var matchPlace = context.Places.Where(p => p.Name == placeName).ToList();
//if (!matchPlace.Any())
// return null;
//// Sometimes a city and a county may have the same name,
//// like Newton County and Newton City, so futher filtering
//// is needed
//Place place;
//if (matchPlace.Count > 1)
//{
// var type = fullPlaceName.ToUpper().Contains("COUNTY") ? "County" : "City";
// place = matchPlace.First(p => p.Type == type);
//}
//else
// place = matchPlace.First();
var placeName = road.Jurisdiction;
var placeType = road.PlaceType;
var findQuery = context.Places.Where(p => p.Name == placeName && p.Type == placeType);
if (findQuery.Any())
return findQuery.First();
return null;
}
}
public static Place FindPlaceBySegment(CopacesCcDbContext context, Segment segment)
{
var road = FindRoadBySegment(context, segment);
if (road == null)
return null;
return FindPlaceByRoad(road);
}
public static Road FindRoadBySegment(CopacesCcDbContext context, Segment segment)
{
var roadId = segment.RoadId;
var matchRoad = context.Roads.Where(r => r.Id == roadId).ToList();
if (!matchRoad.Any())
return null;
return matchRoad.First();
}
public static void SeedRoadSurveysFromXml(CopacesCcDbContext context, String xmlUrl)
{
var xmlRoot = XElement.Parse(Client.DownloadString(xmlUrl));
var roadSurveys =
(from roadSurvey in xmlRoot.Elements("RoadSurvey")
select new RoadSurvey
{
RoadId = (String)roadSurvey.Element("RoadID"),
Date = (DateTime)roadSurvey.Element("Road_Survey_Date"),
ProjectRating = (double?)roadSurvey.Element("Project_Rating"),
RutAvg = (double?)roadSurvey.Element("Rut_Avg"),
RutDeduct = (double?)roadSurvey.Element("Rut_Deduct"),
LoadLevel1Avg = (double?)roadSurvey.Element("Load_Sev1_Avg"),
LoadLevel1Deduct = (double?)roadSurvey.Element("Load_Sev1_Deduct"),
LoadLevel2Avg = (double?)roadSurvey.Element("Load_Sev2_Avg"),
LoadLevel2Deduct = (double?)roadSurvey.Element("Load_Sev2_Deduct"),
LoadLevel3Avg = (double?)roadSurvey.Element("Load_Sev3_Avg"),
LoadLevel3Deduct = (double?)roadSurvey.Element("Load_Sev3_Deduct"),
LoadLevel4Avg = (double?)roadSurvey.Element("Load_Sev4_Avg"),
LoadLevel4Deduct = (double?)roadSurvey.Element("Load_Sev4_Deduct"),
BlockLevel = (double?)roadSurvey.Element("Block_Sev"),
BlockAvg = (double?)roadSurvey.Element("Block_Avg"),
BlockDeduct = (double?)roadSurvey.Element("Block_Deduct"),
ReflectLevel = (double?)roadSurvey.Element("Reflect_Sev"),
ReflectAvg = (double?)roadSurvey.Element("Reflect_Avg"),
ReflectDeduct = (double?)roadSurvey.Element("Reflect_Deduct"),
EdgeLevel = (double?)roadSurvey.Element("Edge_Sev"),
EdgeAvg = (double?)roadSurvey.Element("Edge_Avg"),
EdgeDeduct = (double?)roadSurvey.Element("Edge_Deduct"),
RavelAvg = (double?)roadSurvey.Element("Ravel_Avg"),
RavelDeduct = (double?)roadSurvey.Element("Ravel_Deduct"),
RavelLevel = (double?)roadSurvey.Element("Ravel_Sev"),
CorrugAvg = (double?)roadSurvey.Element("Corrug_Avg"),
CorrugLevel = (double?)roadSurvey.Element("Corrug_Sev"),
CorrugDeduct = (double?)roadSurvey.Element("Corrug_Deduct"),
LossLevel = (double?)roadSurvey.Element("Loss_Sev"),
LossAvg = (double?)roadSurvey.Element("Loss_Avg"),
LossDeduct = (double?)roadSurvey.Element("Loss_Deduct"),
PatchAvg = (double?)roadSurvey.Element("Rut_Avg"),
PatchDeduct = (double?)roadSurvey.Element("Patch_Deduct"),
BleedAvg = (double?)roadSurvey.Element("Bleed_Avg"),
BleedDeduct = (double?)roadSurvey.Element("Bleed_Deduct"),
BleedLevel = (double?)roadSurvey.Element("Bleed_Sev"),
isDeleted = false
}).ToList();
if (roadSurveys == null)
return;
context.BulkInsert(roadSurveys);
context.SaveChanges();
}
public static void SeedPlacesFromXml(CopacesCcDbContext context, String xmlUrl)
{
var html = Client.DownloadString(xmlUrl);
var root = XElement.Parse(html);
var places = root.Elements("Place").Select(x => new Place
{
Id = (string)x.Element("Id"),
Name = (String)x.Element("Name"),
CountyNo = (int?)x.Element("CountyNo"),
District = (int?)x.Element("District"),
Type = x.Element("Type").Value
});
context.BulkInsert(places);
context.SaveChanges();
}
public static void SeedSegmentsFromXml(CopacesCcDbContext context, String xmlUrl)
{
var xmlRoot = XElement.Parse(Client.DownloadString(xmlUrl));
var segList =
(from segment in xmlRoot.Elements("Segment")
select new Segment
{
SequenceId = (int)segment.Element("Sequence_ID"),
InventId = (int)segment.Element("INVENT_ID"),
RoadId = (string)segment.Element("RoadID"),
From = (String)segment.Element("Segment_From"),
To = (String)segment.Element("Segment_To"),
Length = (double?)segment.Element("Length"),
PlaceId = (string)segment.Element("PlaceId"),
isDeleted = false
}).ToList();
context.BulkInsert(segList);
context.SaveChanges();
}
public static void SeedRoadsFromXml(CopacesCcDbContext context, String xmlUrl)
{
var html = Client.DownloadString(xmlUrl);
var xmlRoot = XElement.Parse(html);
var roadList =
(from road in xmlRoot.Elements("Road")
select new Road
{
Name = (String)road.Element("Road_Name"),
Id = (String)road.Element("RoadID"),
Jurisdiction = (String)road.Element("Jurisdiction"),
PlaceType = (String)road.Element("PlaceType"),
PlaceId = (String)road.Element("PlaceId"),
HasUnfinishedSurvey = false,
isDeleted = false
}).ToList();
context.BulkInsert(roadList);
context.SaveChanges();
}
public static void SeedSegSurveysFromXml(CopacesCcDbContext context, String xmlUrl)
{
var xmlRoot = XElement.Parse(Client.DownloadString(xmlUrl));
var segSurveys =
(from segSurvey in xmlRoot.Elements("SegSurvey")
select new SegmentSurvey
{
RoadId = (string)segSurvey.Element("RoadID"),
SequenceId = (int)segSurvey.Element("Sequence_ID"),
InventId = (int)segSurvey.Element("INVENT_ID"),
RoadSurveyDate = (DateTime?)segSurvey.Element("Road_Survey_Date"),
SegSurveyDate = (DateTime?)segSurvey.Element("Survey_Date"),
CopacesRating = (double?)segSurvey.Element("COPACES_Rating"),
Rater = (string)segSurvey.Element("RaterTG"),
TreatmentYear = (string)segSurvey.Element("Treatment_Year"),
TreatmentMethod = (string)segSurvey.Element("Treatment_Method"),
LaneDirection = (string)segSurvey.Element("Lane_Direction"),
LaneNum = (string)segSurvey.Element("Lane_No"),
SampleLocation = (string)segSurvey.Element("Sample_Location"),
RutOutWp = (double?)segSurvey.Element("Rut_Out_WP"),
RutInWp = (double?)segSurvey.Element("Rut_In_WP"),
LoadLevel1 = (double?)segSurvey.Element("Load_Lev1"),
LoadLevel2 = (double?)segSurvey.Element("Load_Lev2"),
LoadLevel3 = (double?)segSurvey.Element("Load_Lev3"),
LoadLevel4 = (double?)segSurvey.Element("Load_Lev4"),
BlockPercentage = (double?)segSurvey.Element("Block_Pct"),
BlockLevel = (int?) segSurvey.Element("Block_Lev"),
ReflectNum = (double?)segSurvey.Element("Reflect_No"),
ReflectLength = (double?)segSurvey.Element("Reflect_Len"),
ReflectLevel = (int?) segSurvey.Element("Reflect_Lev"),
RavelPercentage = (double?)segSurvey.Element("Ravel_Pct"),
RavelLevel = (int?) segSurvey.Element("Ravel_Lev"),
EdgePercentage = (double?)segSurvey.Element("Edge_Pct"),
EdgeLevel = (int?) segSurvey.Element("Edge_Lev"),
BleedPercentage = (double?)segSurvey.Element("Bleed_Pct"),
BleedLevel = (int?) segSurvey.Element("Bleed_Lev"),
CorrugPercentage = (double?)segSurvey.Element("Corrug_Pct"),
CorrugLevel = (int?) segSurvey.Element("Corrug_Lev"),
LossPavPercentage = (double?)segSurvey.Element("Loss_Pave_Pct"),
LossPavLevel = (int?) segSurvey.Element("Loss_Pave_Lev"),
CrossSlopeLeft = (double?)segSurvey.Element("Cross_Slope_Left"),
CrossSlopeRight = (double?)segSurvey.Element("Cross_Slope_Right"),
PatchPotholeNum = (int?)segSurvey.Element("Patch_pothole"),
IsWindshieldSurvey = (string)segSurvey.Element("IsWindshield_Survey") == "YES",
Remarks = (string)segSurvey.Element("Comments"),
PlaceId = (string)segSurvey.Element("PlaceId")
}).ToList();
foreach (var segmentSurvey in segSurveys)
{
segmentSurvey.Id = ((DateTime)segmentSurvey.SegSurveyDate).ToFileTime() + "-" + segmentSurvey.InventId;
}
context.BulkInsert(segSurveys);
context.SaveChanges();
}
