Beispiel #1
0
        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
        }