void loadgraph() // Function for loading and reloading graph
{
// TO DO:
// - RELOAD BUTTON
// - ENABLE/DISABLE SERIES -> toggle true/false box | | <-> | X |
// - Enable/Disable YasValues on graph -> toggle true/false box | | <-> | X |
// - SET MULTIPLIERS -> insert integer in box | 1 | <-> | 3 | <-> | X |
// - SET TIME DOMAIN (1:00 - 24:00 wordt bijvorobeeld 13:00 - 15:00) |.|-----------|.....|
// - SHOW YVALUE ON GRAPH
// Show lines in GUI
// Multipliers in GUI
int MultiplierFietsdiefstal = 1;
int MultiplierStraatroof = 1;
// CREATING CONNECTION
// Jonah : string databaseplace = "C:\\Users\\Jonah Kalkman\\Desktop\\Project3\\WindowsFormsApplication1\\WindowsFormsApplication1\\Official_Database.mdf";
// Job : string databaseplace = "C:\\Users\\jobka\\Documents\\GitHub\\Project3\\WindowsFormsApplication1\\WindowsFormsApplication1\\Official_Database.mdf";
// Oguzhan :string databaseplace = "C:\\Users\\Oguzhan\\Documents\\GitHub\\Project3\\WindowsFormsApplication1\\WindowsFormsApplication1\\Official_Database.mdf";
// Dion: string databaseplace = "C:\\Users\\jobka\\Documents\\GitHub\\Project3\\WindowsFormsApplication1\\WindowsFormsApplication1\\Official_Database.mdf";
string databaseplace = "C:\\Users\\jobka\\Documents\\GitHub\\Project3\\WindowsFormsApplication1\\WindowsFormsApplication1\\Official_Database.mdf";
SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=" + databaseplace + ";Integrated Security=True"); //Connection with database
// fietsdiefstal
SqlCommand FDcommand;
SqlDataReader FDreader;
// straatroof
SqlCommand SRcommand;
SqlDataReader SRreader;
// Draw components
// opened (fietsdiefstal)
chart1.Series[0].Points.Clear();
chart1.Series[1].Points.Clear();
if (ShowFietsdiefstal == true)
{
con.Open(); //open database connection
FDcommand = new SqlCommand("select uur, count(waarde) from fietsdiefstal WHERE uur >= " + minimumtime + " and uur <= " + maximumtime + "AND plaats = 'Rotterdam' GROUP BY uur ORDER BY uur ASC;", con); // [Xvalue, Yvalue] = output query
FDreader = FDcommand.ExecuteReader(); // Make it readable
while (FDreader.Read()) // Read query
{
string output = FDreader.GetValue(0).ToString();
var xvalue = GetInt(output); // Get int out of database: 0 if not convertable
output = FDreader.GetValue(1).ToString();
var yvalue = GetInt(output);
chart1.Series["Fietsdiefstal"].Points.AddXY(xvalue, yvalue * MultiplierFietsdiefstal); // Add point to graph
chart1.Series["Fietsdiefstal"].Points[chart1.Series["Fietsdiefstal"].Points.Count() - 1].AxisLabel = xvalue.ToString() + ":00"; // Time shown underneath graph
if (ShowYOnFietsdiefstal == true)
{
chart1.Series["Fietsdiefstal"].Points[chart1.Series["Fietsdiefstal"].Points.Count() - 1].Label = yvalue.ToString();
}
//ADD VALUE TO POINT: chart1.Series["Fietsdiefstal"].Points[chart1.Series["Fietsdiefstal"].Points.Count() - 1].Label = xvalue.ToString() + ":00"; // comment on the graph
}
con.Close();
}
// closed
// Straatroof queries
// opened
if (ShowStraatroof == true)
{
con.Open();
SRcommand = new SqlCommand("select uur, count(waarde) from straatroof WHERE uur >= " + minimumtime + " and uur <= " + maximumtime + "AND plaats = 'Rotterdam' GROUP BY uur ORDER BY uur ASC;", con);
SRreader = SRcommand.ExecuteReader();
while (SRreader.Read())
{
string output = SRreader.GetValue(0).ToString();
var xvalue = GetInt(output);
output = SRreader.GetValue(1).ToString();
var yvalue = GetInt(output);
chart1.Series["Straatroof"].Points.AddXY(xvalue, yvalue * MultiplierStraatroof);
chart1.Series["Straatroof"].Points[chart1.Series["Straatroof"].Points.Count() - 1].AxisLabel = xvalue.ToString() + ":00";
if (ShowYOnStraatroof == true)
{
chart1.Series["Straatroof"].Points[chart1.Series["Straatroof"].Points.Count() - 1].Label = yvalue.ToString();
}
//ADD VALUE TO POINT: chart1.Series["Straatroof"].Points[chart1.Series["Straatroof"].Points.Count() - 1].Label = xvalue.ToString() + ":00";
}
con.Close();
}
chart1.ChartAreas[0].AxisX.Maximum = maximumtime + 1;
chart1.ChartAreas[0].AxisX.Minimum = minimumtime - 1;
label1.Text = "";
}
public void loadgraph() // function creates the graph
{
// CREATING CONNECTION
// Jonah : string databaseplace = "C:\\Users\\Jonah Kalkman\\Desktop\\Project3\\WindowsFormsApplication1\\WindowsFormsApplication1\\Official_Database.mdf";
// Job : string databaseplace = "C:\\Users\\jobka\\Documents\\GitHub\\Project3\\WindowsFormsApplication1\\WindowsFormsApplication1\\Official_Database.mdf";
// Oguzhan :string databaseplace = "C:\\Users\\Oguzhan\\Documents\\GitHub\\Project3\\WindowsFormsApplication1\\WindowsFormsApplication1\\Official_Database.mdf";
// Robin : string databaseplace = "C:\\Users\\robin\\Documents\\GitHub\\Project3\\WindowsFormsApplication1\\WindowsFormsApplication1\\Official_Database.mdf";
// Dion : string databaseplace = "C:\\Users\\Dionykn\\Documents\\GitHub\\Project3\\WindowsFormsApplication1\\WindowsFormsApplication1\\Official_Database.mdf";
string databaseplace = "C:\\Users\\robin\\Documents\\GitHub\\Project3\\WindowsFormsApplication1\\WindowsFormsApplication1\\Official_Database.mdf";
SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=" + databaseplace + ";Integrated Security=True"); //Connection with database
// fietsdiefstal
SqlCommand FDcommand;
SqlDataReader FDreader;
// straatroof
SqlCommand SRcommand;
SqlDataReader SRreader;
// total
SqlCommand Totalcommand;
SqlDataReader Totalreader;
// Draw components
int districtfietsdiefstal;
int districtstraatroof;
int districttotal;
// opened (fietsdiefstal)
chart1.Series[0].Points.Clear();
chart1.Series[1].Points.Clear();
chart1.Series[2].Points.Clear();
foreach (int district in SelectedDisticts) //create a plot for every distict in the districtlist
{
districtfietsdiefstal = 0;
districtstraatroof = 0;
districttotal = 0;
string sqlquery;
// fietsdiefstal
if (ShowFietsdiefstal == true) //if we want to show it
{
con.Open(); //open database connection
sqlquery = CreateQuerie("fietsdiefstal", district); //select count(*) from fietsdiefstal where district = 'district 1'
FDcommand = new SqlCommand(sqlquery, con); //(output = 1 row of district count size)
FDreader = FDcommand.ExecuteReader(); // Make it readable
while (FDreader.Read()) // Read query
{
int output = FDreader.GetInt32(0);
districtfietsdiefstal = output;
}
chart1.Series["Fietsdiefstal"].Points.AddXY(GetName(district), districtfietsdiefstal); // Add point to graph
chart1.Series["Fietsdiefstal"].Points[chart1.Series["Fietsdiefstal"].Points.Count() - 1].AxisLabel = GetName(district); //add axislabel (underneath)
if (ShowYOnFietsdiefstal == true)
{
chart1.Series["Fietsdiefstal"].Points[chart1.Series["Fietsdiefstal"].Points.Count() - 1].Label = districtfietsdiefstal.ToString(); //add value to point
}
//ADD VALUE TO POINT: chart1.Series["Fietsdiefstal"].Points[chart1.Series["Fietsdiefstal"].Points.Count() - 1].Label = xvalue.ToString() + ":00"; // comment on the graph
con.Close(); //close database connection
}
// straatroof
if (ShowStraatroof == true) // if we want to show it
{
con.Open(); //open database connection
sqlquery = CreateQuerie("straatroof", district); //select count(*) from fietsdiefstal where district = 'district 1';
SRcommand = new SqlCommand(sqlquery, con); //(output = 1 row of district count size)
SRreader = SRcommand.ExecuteReader(); // Make it readable
while (SRreader.Read()) // Read query
{
string output = SRreader.GetValue(0).ToString();
districtstraatroof = GetInt(output); // Get int out of database: 0 -> gets string out of database
}
chart1.Series["Straatroof"].Points.AddXY(GetName(district), districtstraatroof); // Add point to graph
chart1.Series["Straatroof"].Points[chart1.Series["Straatroof"].Points.Count() - 1].AxisLabel = GetName(district); // Time shown underneath graph
if (ShowYOnStraatroof == true)
{
chart1.Series["Straatroof"].Points[chart1.Series["Straatroof"].Points.Count() - 1].Label = districtstraatroof.ToString();
}
//ADD VALUE TO POINT: chart1.Series["Fietsdiefstal"].Points[chart1.Series["Fietsdiefstal"].Points.Count() - 1].Label = xvalue.ToString() + ":00"; // comment on the graph
con.Close(); // close connection with database
}
// total
if (ShowTotal == true) // if we want to show it
{
con.Open(); //open database connection
sqlquery = CreateTotalQuery(district); //select count(*) from fietsdiefstal where district = 'district 1'
Totalcommand = new SqlCommand(sqlquery, con); //(output = 1 row of district count size)
Totalreader = Totalcommand.ExecuteReader(); // Make it readable
while (Totalreader.Read()) // Read query
{
int output = Totalreader.GetInt32(0); // get value out
// TO PROOF WE USED OUR JOIN FUNCTION CORRECTLY
Console.WriteLine("total by query: " + output.ToString());
Console.WriteLine("Total by calculator: " + (districtfietsdiefstal + districtstraatroof).ToString());
districttotal = output; //output; // Get int out of database: 0 if not convertable
}
con.Close(); //close connection
chart1.Series["Total"].Points.AddXY(GetName(district), districttotal); //add point to graph
chart1.Series["Total"].Points[chart1.Series["Total"].Points.Count() - 1].AxisLabel = GetName(district); // add axislabel
if (ShowYOnTotal == true)
{
chart1.Series["Total"].Points[chart1.Series["Total"].Points.Count() - 1].Label = districttotal.ToString(); // add value to it
}
}
}
chart1.ChartAreas[0].AxisX.Maximum = SelectedDisticts.Count() + 1; //make axis as big as the number of points + 1
}