using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.OleDb;

namespace StudentenPunten
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        private OleDbConnection objConnection;
        public int StudentenId = 6;
        public MainWindow()
        {
            InitializeComponent();
            String strConnection;
            strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source='Studenten.accdb';" +
            "Persist Security Info=False";
            objConnection = new OleDbConnection(strConnection);
        }
        private void btnToonStudenten_Click(object sender, RoutedEventArgs e)
        {
            lstStudenten.Items.Clear();
            txtVak.Clear();
            txtPunt.Clear();

            OleDbCommand objCommand;
            OleDbDataReader objDataReader;
            Student objStudent;

            //SQL commando opstellen
            String strSQL;
            strSQL = "SELECT * " +
                "FROM Studenten ORDER BY StudentenVoornaam, StudentenNaam";
            objCommand = new OleDbCommand(strSQL, objConnection);

            objConnection.Open();
            objDataReader = objCommand.ExecuteReader();

            if (objDataReader.HasRows)
            {
                while (objDataReader.Read())
                {
                    int StudentenId = Convert.ToInt32(objDataReader["StudentenId"]);
                    String StudentVoornaam = Convert.ToString(objDataReader["StudentenVoornaam"]);
                    String StudentNaam = Convert.ToString(objDataReader["StudentenNaam"]);
                    objStudent = new Student(StudentenId, StudentNaam, StudentVoornaam);

                    lstStudenten.Items.Add(objStudent);
                }
            }
            objDataReader.Close();
            objConnection.Close();
        }

        private void lstStudenten_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            StudentenSelectionChanged();
        }

        private void StudentenSelectionChanged()
        {
            txtPunt.Clear();
            lstPeriode.Items.Clear();
            if (lstStudenten.SelectedItems.Count == 0) return;
            Student objStudent = (Student)lstStudenten.SelectedItems[0];

            OleDbCommand objCommand;
            OleDbDataReader objDataReader;
            Periode objPeriode;


            //SQL commando opstellen
            String strSQL;
            strSQL = "SELECT DISTINCT PuntenPeriode " +
                "FROM Punten WHERE PuntenStudentenId = " + objStudent.Id;
            objCommand = new OleDbCommand(strSQL, objConnection);

            objConnection.Open();
            objDataReader = objCommand.ExecuteReader();

            if (objDataReader.HasRows)
            {
                while (objDataReader.Read())
                {
                    String PuntenPeriode = Convert.ToString(objDataReader["PuntenPeriode"]);
                    objPeriode = new Periode(PuntenPeriode);

                    lstPeriode.Items.Add(objPeriode);
                }
            }
            objDataReader.Close();
            objConnection.Close();
        }

        private void lstPeriode_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            PeriodeSelectionChanged();
        }
        private void PeriodeSelectionChanged()
        {
            txtPunt.Clear();
            lstVakken.Items.Clear();
            if (lstStudenten.SelectedItems.Count == 0) return;
            if (lstPeriode.SelectedItems.Count == 0) return;
            Student objStudent = (Student)lstStudenten.SelectedItems[0];
        Periode objPeriode = (Periode)lstPeriode.SelectedItems[0];

        OleDbCommand objCommand;
        OleDbDataReader objDataReader;
        Vak objVak;

        //SQL commando opstellen
        String strSQL;
        strSQL = "SELECT PuntenVak, PuntenBehaald " +
                "FROM Punten WHERE PuntenStudentenId = " + objStudent.Id + " AND PuntenPeriode = '" + objPeriode.PuntenPeriode + "';";
            objCommand = new OleDbCommand(strSQL, objConnection);

        objConnection.Open();
            objDataReader = objCommand.ExecuteReader();

            if (objDataReader.HasRows)
            {
                while (objDataReader.Read())
                {
                    String PuntenVak = Convert.ToString(objDataReader["PuntenVak"]);
                    if (!(objDataReader["PuntenBehaald"] is DBNull))
                    {
                        Object sdf = objDataReader["PuntenBehaald"];
                       double PuntenBehaald = Convert.ToInt32(sdf);
                       objVak = new Vak(PuntenVak, PuntenBehaald);
                    }
                    else objVak = new Vak(PuntenVak, -1);

                    lstVakken.Items.Add(objVak);
                }
            }
            objDataReader.Close();
            objConnection.Close();
        }

        private void lstVakken_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (lstVakken.SelectedItems.Count == 0) return;
            Vak objVak = (Vak)lstVakken.SelectedItems[0];
            if (objVak.behaald == -1)
            {
                txtPunt.Text = "";
            }
            else
            {
                txtPunt.Text = objVak.behaald.ToString();
            }
        }

        private void btnPuntOpslaan_Click(object sender, RoutedEventArgs e)
        {
            if (lstVakken.SelectedItems.Count == 0) return;
            Vak objVak = (Vak)lstVakken.SelectedItems[0];
            objVak.behaald = Convert.ToInt32(txtPunt.Text);
        }

        private void btnStudentToevoegen_Click(object sender, RoutedEventArgs e)
        {
            OleDbCommand objCommand;
            //SQL commando opstellen

            String strSQL;
            strSQL = "INSERT INTO Studenten(StudentenVoornaam,StudentenNaam)VALUES(('" + txtStudentVoornaam.Text + "'),('" + txtStudentNaam.Text + "'))";

            objCommand = new OleDbCommand(strSQL, objConnection);

            objConnection.Open();
            int result = objCommand.ExecuteNonQuery();
            objConnection.Close();
            btnToonStudenten_Click(null,null);
        }

        private void btnStudentVerwijderen_Click(object sender, RoutedEventArgs e)
        {
            if (lstStudenten.SelectedItems.Count == 0) return;
            Student objStudent = (Student)lstStudenten.SelectedItems[0];



            OleDbCommand objCommand;
            //SQL commando opstellen
            String strSQL;
            strSQL = "DELETE " +
                "FROM Studenten WHERE StudentenId = " + objStudent.Id + ";";
            objCommand = new OleDbCommand(strSQL, objConnection);

            objConnection.Open();
            int result = objCommand.ExecuteNonQuery();

            if (result == 1)
            {
                lstStudenten.Items.Remove(objStudent);
            }
            objConnection.Close();

            strSQL = "DELETE " +
                "FROM Punten WHERE PuntenStudentenId = " + objStudent.Id + ";";
            objCommand = new OleDbCommand(strSQL, objConnection);
            objConnection.Open();
            int result2 = objCommand.ExecuteNonQuery();

            if (result2 == 1)
            {
                lstStudenten.Items.Remove(objStudent);
            }

            objConnection.Close();
        }

        private void btnVakToevoegen_Click(object sender, RoutedEventArgs e)
        {
            Student objStudent = (Student)lstStudenten.SelectedItems[0];

            OleDbCommand objCommand;
            //SQL commando opstellen

            String strSQL;
            strSQL = "INSERT INTO Punten(PuntenPeriode,PuntenVak,PuntenStudentenId)VALUES(('D1'),('" + txtVak.Text + "'),(" + objStudent.Id + "))";

            objCommand = new OleDbCommand(strSQL, objConnection);

            objConnection.Open();
            int result = objCommand.ExecuteNonQuery();
            objConnection.Close();

            StudentenSelectionChanged();
        }

        private void btnVakVerwijderen_Click(object sender, RoutedEventArgs e)
        {
            if (lstVakken.SelectedItems.Count == 0) return;
            Vak objVak = (Vak)lstVakken.SelectedItems[0];
            Student objStudent = (Student)lstStudenten.SelectedItems[0];


            OleDbCommand objCommand;
            //SQL commando opstellen
            String strSQL;
            strSQL = "DELETE " +
                "FROM Punten WHERE PuntenStudentenId = " + objStudent.Id + " AND PuntenVak = '" + objVak.vak + "';";
            objCommand = new OleDbCommand(strSQL, objConnection);

            objConnection.Open();
            int result = objCommand.ExecuteNonQuery();

            if (result == 1)
            {
                lstVakken.Items.Remove(objVak);
            }
            objConnection.Close();
        }
    }
}