public static List<AvgSpeedReportItem> GetReportData()
{
string query =
@"WITH RankedLink AS (
SELECT ROW_NUMBER() OVER (ORDER BY Link, SegEndC) [Number], *
FROM dbo.Olaine_LINK_EVAL),
MiddleLink AS (
SELECT AVG(Number) [MiddleLink]
FROM RankedLink
GROUP BY Link),
AvgSpeed AS (
SELECT AVG(v__0_) [speed], Link
FROM dbo.Olaine_LINK_EVAL
GROUP BY Link)
SELECT RankedLink.Link, 0 [Lane], (SegStX + SegEndX)/2 [center.x], (SegStY + SegEndY)/2 [center.y], AvgSpeed.speed
FROM RankedLink
JOIN MiddleLink ON RankedLink.Number=MiddleLink.MiddleLink
JOIN AvgSpeed ON RankedLink.Link=AvgSpeed.Link";
var list = new List<AvgSpeedReportItem>();
try
{
var sb = new OleDbConnectionStringBuilder(vissim.Instance.Evaluation.Wrap().GetConnectionString());
if (sb.ContainsKey("Password"))
{
using (var conn = new OleDbConnection(sb.ConnectionString))
{
conn.Open();
try
{
using (var reader = new OleDbCommand(query, conn).ExecuteReader())
{
while (reader.Read())
{
list.Add(new AvgSpeedReportItem()
{
Link = reader.GetInt32(0),
Lane = reader.GetInt32(1),
Center = new Point(reader.GetDouble(2), reader.GetDouble(3)),
AvgSpeed = reader.GetDouble(4)
});
}
}
}
finally
{
conn.Close();
}
}
}
}
catch { }
return list;
}
void caricaDaDb()
{
string sconn;
sconn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb";
OleDbConnection conn = new OleDbConnection(sconn);
try
{
conn.Open();
N_NODI = getint32("SELECT valore FROM parametri WHERE parametro='n' ", 0, conn);
settaNnodi(N_NODI);
m = getint32("SELECT valore FROM parametri WHERE parametro='m' ", 0, conn);
//leggo nodi
OleDbDataReader myreader = new OleDbCommand("select x,y,nome from nodi", conn).ExecuteReader();
for (int i = 0; i < N_NODI; i++)
{
nodi[i].x = nodi[i].y = 0;
archi[i] = new ArrayList();
d[i] = new ArrayList();
}
while (myreader.Read())
{
int x;
int y;
int nome;
//int a, b, c;
//myreader.Read();
x = myreader.GetInt32(0);
y = myreader.GetInt32(1);
nome = Convert.ToInt32(myreader.GetString(2));
nodi[nome].x = x;
nodi[nome].y = y;
nodi[nome].name = Convert.ToString(nome);
archi[nome] = new ArrayList();
d[nome] = new ArrayList();
}
//leggo archi
myreader = new OleDbCommand("select x,y,dist from archi", conn).ExecuteReader();
//for (int j = 0; j < m; j++) // per ogni arco...
while (myreader.Read())
{
int from;
int dest;
int dist;
//myreader.Read();
from = myreader.GetInt32(0);
dest = myreader.GetInt32(1);
dist = myreader.GetInt32(2);
//aggiungo arco
if (archi[from] == null)
archi[from] = new ArrayList();
if (d[from] == null)
d[from] = new ArrayList();
archi[from].Add(dest);
d[from].Add(dist);
}
DrawGraph();
conn.Close();
}
catch (OleDbException e)
{
MessageBox.Show("Errore DB" + e.Message);
}
}
int getint32(String query, int num_colonn, OleDbConnection conn)
{
OleDbDataReader myreader = new OleDbCommand(query, conn).ExecuteReader();
myreader.Read();
return myreader.GetInt32(num_colonn);
}
