// $Id: PopulateRoadwaySegmentsFromDisplayLinks.cs,v 1.2 2013-01-03 21:04:52 jonesk Exp $
|
// Copyright TransCore 2012, All Rights Reserved
|
//
|
// PROPRIETARY
|
// THIS SOURCE CODE IS THE PROPERTY OF TRANSCORE. IT MAY BE USED BY RECIPIENT
|
// ONLY FOR THE PURPOSE FOR WHICH IT WAS TRANSMITTED AND MUST BE RETURNED UPON
|
// REQUEST OR WHEN NO LONGER NEEDED BY RECIPIENT. IT MAY NOT BE COPIED OR
|
// COMMUNICATED WITHOUT THE WRITTEN CONSENT OF TRANSCORE.
|
//
|
//-----------------------------------------------------------------------------
|
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Data.SqlTypes;
|
using System.Linq;
|
using Microsoft.SqlServer.Server;
|
|
|
public partial class StoredProcedures
|
{
|
/// <summary>
|
/// Loads WebMap_RoadwaySegment and WebMap_RoadwaySegmentDetectorStation
|
/// using data in FMS_DetectorStation, FMS_ComponentConnect, and FMSDisplayLink
|
/// </summary>
|
[Microsoft.SqlServer.Server.SqlProcedure]
|
public static void PopulateRoadwaySegmentsFromDisplayLinks()
|
{
|
using (SqlConnection sqlConnection = new SqlConnection("context connection=true"))
|
{
|
sqlConnection.Open();
|
|
try
|
{
|
#region Delete any existing RoadwaySegments
|
|
string sqlDelete = @"DELETE FROM WebMap_RoadwaySegmentDisplayLink " +
|
"DELETE FROM WebMap_RoadwaySegmentDetectorStation " +
|
"DELETE FROM WebMap_RoadwaySegment";
|
using (SqlTransaction deleteTransaction = sqlConnection.BeginTransaction("txnRoadwaySegmentDelete"))
|
using (SqlCommand deleteCommand = new SqlCommand(sqlDelete, sqlConnection, deleteTransaction))
|
{
|
deleteCommand.ExecuteNonQuery();
|
deleteTransaction.Commit();
|
}
|
|
#endregion
|
|
List<List<SegmentDetectorStationData>> segmentsDsData = new List<List<SegmentDetectorStationData>>();
|
#region Populate segmentsDsData from FMS_ComponentConnect and FMS_DetectorStation
|
|
string sqlDsConnects = @"SELECT FMS_DetectorStation.IntId, RoadwayName, TmddLinkDirection, " +
|
"FMS_DetectorStation.LinearReference, FMS_ComponentConnect.Distance " +
|
"FROM FMS_DetectorStation " +
|
"JOIN FMS_ComponentConnect ON FMS_DetectorStation.IntId = FMS_ComponentConnect.DSIntId " +
|
"ORDER BY FMS_ComponentConnect.DSIntId, DSIntIdDownstream";
|
using (SqlCommand sqlCommand = new SqlCommand(sqlDsConnects, sqlConnection))
|
using (SqlDataReader sqlDsConnectsReader = sqlCommand.ExecuteReader())
|
{
|
string lastRoadway = string.Empty;
|
int lastDirection = -1;
|
float lastLinearReference = -1;
|
List<SegmentDetectorStationData> thisSegmentData = new List<SegmentDetectorStationData>();
|
while (sqlDsConnectsReader.Read())
|
{
|
Object[] dsConnectValues = new Object[sqlDsConnectsReader.FieldCount];
|
sqlDsConnectsReader.GetValues(dsConnectValues);
|
|
int dsIntId = (int)dsConnectValues[0];
|
|
string roadwayName = dsConnectValues[1].ToString();
|
int tmddLinkDirection = int.Parse(dsConnectValues[2].ToString());
|
float linearReference = float.Parse(dsConnectValues[3].ToString());
|
float distance = float.Parse(dsConnectValues[4].ToString());
|
|
if (roadwayName != lastRoadway || tmddLinkDirection != lastDirection || linearReference < lastLinearReference)
|
{
|
if (thisSegmentData.Count > 0)
|
{
|
segmentsDsData.Add(thisSegmentData);
|
}
|
|
thisSegmentData = new List<SegmentDetectorStationData>();
|
thisSegmentData.Add(new SegmentDetectorStationData(dsIntId, distance));
|
}
|
else
|
{
|
thisSegmentData.Add(new SegmentDetectorStationData(dsIntId, distance));
|
}
|
|
lastRoadway = roadwayName;
|
lastDirection = tmddLinkDirection;
|
lastLinearReference = linearReference;
|
}
|
|
if (thisSegmentData.Count > 0)
|
{
|
segmentsDsData.Add(thisSegmentData);
|
}
|
}
|
|
#endregion
|
|
string roadwaySegmentInsertValues = string.Empty;
|
string roadwaySegmentDlInsertValues = string.Empty;
|
string roadwaySegmentDsInsertValues = string.Empty;
|
#region Build values portions of insert statements
|
|
int segmentId = 0;
|
foreach (List<SegmentDetectorStationData> thisSegmentData in segmentsDsData)
|
{
|
bool segmentDisplayLinkFound = false;
|
int dlSequence = 0;
|
|
foreach (SegmentDetectorStationData segmentDsData in thisSegmentData)
|
{
|
int dsSequence = 0;
|
int dlIntId = -1;
|
int vertexCount = -1;
|
#region roadwaySegmentDsInsertValues = FMS_DisplayLinks.Where(DsIntId = dsIntId) with greatest amount of verticies
|
|
string sqlDisplayLinks = @"SELECT VertexData, FMS_DisplayLink.IntId " +
|
"FROM FMS_DisplayLinkDetStation " +
|
"JOIN FMS_DisplayLink ON FMS_DisplayLinkDetStation.DLIntId = FMS_DisplayLink.IntId " +
|
"WHERE DSIntId = " + segmentDsData.DsIntId;
|
using (SqlCommand sqlCommand2 = new SqlCommand(sqlDisplayLinks, sqlConnection))
|
using (SqlDataReader sqlDisplayLinksReader = sqlCommand2.ExecuteReader())
|
{
|
while (sqlDisplayLinksReader.Read())
|
{
|
Object[] dsDisplayLinkValues = new Object[sqlDisplayLinksReader.FieldCount];
|
sqlDisplayLinksReader.GetValues(dsDisplayLinkValues);
|
|
string vertexData = dsDisplayLinkValues[0].ToString();
|
int countDataSeperatorIdx = vertexData.IndexOf(' ');
|
int thisCount = int.Parse(vertexData.Substring(0, countDataSeperatorIdx));
|
if (thisCount > vertexCount)
|
{
|
dlIntId = Int32.Parse(dsDisplayLinkValues[1].ToString());
|
vertexCount = thisCount;
|
}
|
}
|
}
|
|
#endregion
|
|
if (vertexCount > 0)
|
{
|
segmentDisplayLinkFound = true;
|
|
if (roadwaySegmentDsInsertValues.Length > 0)
|
{
|
roadwaySegmentDsInsertValues += ",";
|
}
|
++dsSequence;
|
roadwaySegmentDsInsertValues += string.Format("((SELECT IntId FROM WebMap_RoadwaySegment WHERE ExtId={0}),{1},{2},{3})",
|
segmentId + 1, segmentDsData.DsIntId, segmentDsData.Distance, dsSequence);
|
|
if (roadwaySegmentDlInsertValues.Length > 0)
|
{
|
roadwaySegmentDlInsertValues += ",";
|
}
|
++dlSequence;
|
roadwaySegmentDlInsertValues += string.Format("((SELECT IntId FROM WebMap_RoadwaySegment WHERE ExtId={0}),{1},{2})",
|
segmentId + 1, dlIntId, dlSequence);
|
}
|
}
|
|
if (segmentDisplayLinkFound)
|
{
|
++segmentId;
|
if (roadwaySegmentInsertValues.Length > 0)
|
{
|
roadwaySegmentInsertValues += ",";
|
}
|
roadwaySegmentInsertValues += string.Format("({0},'Roadway Segment {0}')", segmentId);
|
}
|
}
|
|
#endregion
|
|
#region Insert all rows
|
|
using (SqlTransaction insertTransaction = sqlConnection.BeginTransaction("txnRoadwaySegmentInsert"))
|
{
|
string sqlInsert = @"INSERT INTO WebMap_RoadwaySegment (ExtId, Name) VALUES " + roadwaySegmentInsertValues + " ";
|
using (SqlCommand insertCommand = new SqlCommand(sqlInsert, sqlConnection, insertTransaction))
|
{
|
insertCommand.ExecuteNonQuery();
|
}
|
|
sqlInsert = @"INSERT INTO WebMap_RoadwaySegmentDisplayLink (RoadwaySegmentIntId, DLIntId, Sequence) VALUES " + roadwaySegmentDlInsertValues;
|
using (SqlCommand insertCommand = new SqlCommand(sqlInsert, sqlConnection, insertTransaction))
|
{
|
insertCommand.ExecuteNonQuery();
|
}
|
|
sqlInsert = @"INSERT INTO WebMap_RoadwaySegmentDetectorStation (RoadwaySegmentIntId, DsIntId, Distance, Sequence) VALUES " + roadwaySegmentDsInsertValues;
|
using (SqlCommand insertCommand = new SqlCommand(sqlInsert, sqlConnection, insertTransaction))
|
{
|
insertCommand.ExecuteNonQuery();
|
}
|
|
insertTransaction.Commit();
|
}
|
|
#endregion
|
}
|
catch (Exception ex)
|
{
|
SqlContext.Pipe.Send(ex.Message);
|
}
|
finally
|
{
|
sqlConnection.Close();
|
}
|
}
|
}
|
|
private class SegmentDetectorStationData
|
{
|
public SegmentDetectorStationData(int dsIntId, float distance)
|
{
|
DsIntId = dsIntId;
|
Distance = distance;
|
}
|
|
public int DsIntId { get; private set; }
|
public float Distance { get; private set; }
|
}
|
};
|
