using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace GSB_CR
{
    public partial class Form3 : Form
    {

        public Form3()
        {
            InitializeComponent();
        }

        public void Form3_Load(object sender, EventArgs e)
        {
            MySqlConnection connect = new MySqlConnection("SERVER=localhost;PORT=3306;DATABASE=gsb_cr;UID=gsbcr;PWD=serge;SslMode=none");
            connect.Open();
            MySqlDataReader readerPraticien;
            MySqlCommand praticien = new MySqlCommand(("SELECT nom, prenom FROM praticien"), connect);
            readerPraticien = praticien.ExecuteReader();
            while (readerPraticien.Read())
            {
                cBx_nom1.Items.Add(readerPraticien.GetString("nom") + " " + readerPraticien.GetString("prenom"));
                cbx_Praticien.Items.Add(readerPraticien.GetString("nom") + " " + readerPraticien.GetString("prenom"));
            }
            readerPraticien.Close();

            MySqlDataReader readerNumRapport;
            MySqlCommand numRapport = new MySqlCommand(("SELECT id FROM rapport"), connect);
            readerNumRapport = numRapport.ExecuteReader();
            while (readerNumRapport.Read())
            {
                cBx_numRapp.Items.Add(readerNumRapport.GetString("id"));
                cBx_numRapp2.Items.Add(readerNumRapport.GetString("id"));
            }
            readerNumRapport.Close();

            connect.Close();
        }

        public int countRapport()
        {
            MySqlConnection connect = new MySqlConnection("SERVER=localhost;PORT=3306;DATABASE=gsb_cr;UID=gsbcr;PWD=serge;SslMode=none");
            int i = 0;
            connect.Open();
            MySqlCommand count = new MySqlCommand("SELECT COUNT(*) FROM rapport WHERE id = @id", connect);
            count.Parameters.AddWithValue("@id", txt_numRap.Text);
            count.ExecuteNonQuery();
            i = int.Parse(count.ExecuteScalar().ToString());
            count.Parameters.Clear();
            connect.Close();
            return i;
        }
        private void btn_creer_Click_1(object sender, EventArgs e)
        {
            if (countRapport() == 0)
            {
                if (txt_motif1.Text.Length != 0 || txt_motif1.Text.Length != 0)
                {
                    MySqlConnection connect = new MySqlConnection("SERVER=localhost;PORT=3306;DATABASE=gsb_cr;UID=gsbcr;PWD=serge;SslMode=none");
                    connect.Open();

                    MySqlDataReader reader_idP;
                    MySqlCommand reqT = new MySqlCommand("SELECT id FROM praticien WHERE nom = @nom AND prenom = @prenom;", connect);
                    string idPraticien = "";
                    string cbx = Convert.ToString(cBx_nom1.Text);
                    int longueur = cbx.Length;
                    int val = cbx.IndexOf(" ");
                    string nom = cbx.Substring(0, val);
                    int valeur = val + 1;
                    int longueurprenom = longueur - valeur;
                    string prenom = cbx.Substring(valeur, longueurprenom);
                    reqT.Parameters.AddWithValue("@nom", nom);
                    reqT.Parameters.AddWithValue("@prenom", prenom);

                    reader_idP = reqT.ExecuteReader();
                    while (reader_idP.Read())
                    {
                        idPraticien = reader_idP.GetString("id");
                    }
                    reqT.Parameters.Clear();
                    reader_idP.Close();

                    MySqlCommand req = new MySqlCommand("INSERT INTO rapport(motif, date, bilan, id_Praticien) VALUES(@motif, @date, @bilan, @id_Praticien)", connect);

                    req.Parameters.AddWithValue("@motif", txt_motif1.Text);
                    req.Parameters.AddWithValue("@date", Convert.ToDateTime(dateTime1.Value));
                    req.Parameters.AddWithValue("@bilan", txt_Bilan1.Text);
                    req.Parameters.AddWithValue("@id_Praticien", idPraticien);

                    req.ExecuteNonQuery();
                    req.Parameters.Clear();
                    connect.Close();
                }
                else
                {
                    MessageBox.Show("Motif ou bilan non saisi");
                }
            }
            else
            {
                MessageBox.Show("Numéro de rapport déjà existent");
            }
        }

        private void btn_rechercher_Click(object sender, EventArgs e)
        {
            MySqlConnection connect = new MySqlConnection("SERVER=localhost;PORT=3306;DATABASE=gsb_cr;UID=gsbcr;PWD=serge;SslMode=none");
            connect.Open();
            MySqlDataReader reader;
            MySqlCommand rechercher = new MySqlCommand(("SELECT rapport.id, rapport.motif, rapport.bilan, rapport.date, praticien.nom, praticien.prenom FROM rapport, praticien WHERE rapport.id_Praticien=praticien.id AND rapport.id = @numRapp"), connect);
            rechercher.Parameters.AddWithValue("@numRapp", cBx_numRapp.Text);
            reader = rechercher.ExecuteReader();
            while (reader.Read())
            {
                txt_motif2.Text = reader.GetString("motif");
                dateTime2.Value = (Convert.ToDateTime(reader.GetString("date")));
                txt_Bilan2.Text = reader.GetString("bilan");
                cBx_nom2.Text = reader.GetString("nom") + " " + reader.GetString("prenom");
            }
            connect.Close();
        }

        private void btn_modifier_Click(object sender, EventArgs e)
        {
            if (countRapport() == 0)
            {

                MySqlConnection connect = new MySqlConnection("SERVER=localhost;PORT=3306;DATABASE=gsb_cr;UID=gsbcr;PWD=serge;SslMode=none");
                connect.Open();

                MySqlDataReader reader_idP;
                MySqlCommand reqT = new MySqlCommand("SELECT id FROM praticien WHERE nom = @nom AND prenom = @prenom;", connect);
                string idPraticien = "";
                string cbx = Convert.ToString(cbx_Praticien.Text);
                int longueur = cbx.Length;
                int val = cbx.IndexOf(" ");
                string nom = cbx.Substring(0, val);
                int valeur = val + 1;
                int longueurprenom = longueur - valeur;
                string prenom = cbx.Substring(valeur, longueurprenom);
                reqT.Parameters.AddWithValue("@nom", nom);
                reqT.Parameters.AddWithValue("@prenom", prenom);

                reader_idP = reqT.ExecuteReader();
                while (reader_idP.Read())
                {
                    idPraticien = reader_idP.GetString("id");
                }
                reqT.Parameters.Clear();
                reader_idP.Close();

                MySqlCommand req = new MySqlCommand("UPDATE rapport SET motif = @motif, date = @date, bilan = @bilan WHERE id_Praticien = @id)", connect);

                req.Parameters.AddWithValue("@id", cBx_numRapp2.SelectedIndex + 1);
                req.Parameters.AddWithValue("@motif", txt_motif.Text);
                req.Parameters.AddWithValue("@date", Convert.ToDateTime(dateTimePicker1.Value));
                req.Parameters.AddWithValue("@bilan", txt_bilan.Text);
                req.Parameters.AddWithValue("@id_Praticien", idPraticien);

                req.ExecuteNonQuery();
                req.Parameters.Clear();
                connect.Close();

            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            MySqlConnection connect = new MySqlConnection("SERVER=localhost;PORT=3306;DATABASE=gsb_cr;UID=gsbcr;PWD=serge;SslMode=none");
            connect.Open();
            MySqlDataReader reader;
            MySqlCommand rechercher = new MySqlCommand(("SELECT rapport.id, rapport.motif, rapport.bilan, rapport.date, praticien.nom, praticien.prenom FROM rapport, praticien WHERE rapport.id_Praticien=praticien.id AND rapport.id = @numRapp"), connect);
            rechercher.Parameters.AddWithValue("@numRapp", cBx_numRapp2.Text);
            reader = rechercher.ExecuteReader();
            while (reader.Read())
            {
                txt_motif.Text = reader.GetString("motif");
                dateTimePicker1.Value = (Convert.ToDateTime(reader.GetString("date")));
                txt_bilan.Text = reader.GetString("bilan");
                cbx_Praticien.Text = reader.GetString("nom") + " " + reader.GetString("prenom");
            }
            connect.Close();
        }
    }
}