using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Qu6Choix.SelectedIndexChanged += new EventHandler(Qu6Choix_SelectedIndexChanged);
        Qu7Choix.SelectedIndexChanged += new EventHandler(Qu7Choix_SelectedIndexChanged);
        Qu8Choix.SelectedIndexChanged += new EventHandler(Qu8Choix_SelectedIndexChanged);
        Qu9Choix.SelectedIndexChanged += new EventHandler(Qu9Choix_SelectedIndexChanged);
        Terminer.Click += new ImageClickEventHandler(Terminer_Click);
    }

    protected void Qu5Choix_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Qu5Choix.SelectedValue == "Oui")
        {
            Qu5Rep.Visible = true;
            Label1.Visible = true;
        }
        else
        {
            Qu5Rep.Visible = !Qu5Rep.Visible;
            Label1.Visible = !Label1.Visible;
        }
    }

    protected void Qu6Choix_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Qu6Choix.SelectedValue == "Oui")
        {
            Qu6Rep.Visible = true;
            Label2.Visible = true;
        }
        else
        {
            Qu6Rep.Visible = !Qu6Rep.Visible;
            Label2.Visible = !Label2.Visible;
        }
    }

    protected void Qu7Choix_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Qu7Choix.SelectedValue == "Oui")
        {
            Qu7Rep.Visible = true;
            Label3.Visible = !Label3.Visible;
        }
        else
        {
            Qu7Rep.Visible = !Qu7Rep.Visible;
            Label3.Visible = !Label3.Visible;
        }
    }

    protected void Qu8Choix_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Qu8Choix.SelectedValue == "Oui")
        {
            Qu8Rep.Visible = true;
            Label4.Visible = true;
        }
        else
        {
            Qu8Rep.Visible = !Qu8Rep.Visible;
            Label4.Visible = !Label4.Visible;
        }
    }

    protected void Qu9Choix_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Qu9Choix.SelectedValue == "Oui")
        {
            Qu9Rep.Visible = true;
            Label5.Visible = true;
        }
        else
        {
            Qu9Rep.Visible = !Qu9Rep.Visible;
            Label5.Visible = !Label5.Visible;
        }
    }

    protected void Qu10Choix_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Qu9Choix.SelectedValue == "Oui")
        {
            Qu10Rep.Visible = true;
            Label7.Visible = true;
        }
        else
        {
            Qu10Rep.Visible = !Qu9Rep.Visible;
            Label7.Visible = !Label5.Visible;
        }
    }

    protected void Terminer_Click(object sender, EventArgs e)
    {
        if (!CheckIfAlreadyDone())
        {
            InsertValueInBD();
            Response.Redirect("~/MerciEcrit.aspx");
        }
    }

    private void InsertValueInBD()
    {
        int[] id = new int[] {0,0,0,0,0,0,0,0,0,0,0};
        string query;
        OleDbDataReader reader;
        OleDbCommand command;
        OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["SondBD"].ConnectionString;
        
        try
        {
           conn.Open();

            #region Question 1
            
            //Insérer dans la base de données
            query = "INSERT INTO Question1(Reponse) VALUES (@Rep)";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@Rep",Qu1Rep.SelectedValue));
            command.ExecuteNonQuery();
            
            //Avoir l'id corespondant
            command.Dispose();
            query = "SELECT * FROM GetLastQu1ID";
            command = new OleDbCommand(query, conn);
            reader = command.ExecuteReader();
            while (reader.Read())
                id[0] = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();
            #endregion

            #region Question 2

            //Insérer dans la base de données
            command.Dispose();
            query = "INSERT INTO Question2(Quinzaine,Donneurs,EcritLanau,CultureEcole,AVoixHaute,Carrefour,Slam,ConcourLit)" 
                    + "VALUES (@quinz,@donn,@ecit,@cult,@avoix,@carr,@slam,@conc)";
            command = new OleDbCommand(query, conn); 
            command.Parameters.Add(new OleDbParameter("@quinz",(Qu2QuinRep.SelectedValue == "Oui")));
            command.Parameters.Add(new OleDbParameter("@donn", (Qu2DonRep.SelectedValue == "Oui")));
            command.Parameters.Add(new OleDbParameter("@ecrit", (Qu2EcritLanauRep.SelectedValue == "Oui")));
            command.Parameters.Add(new OleDbParameter("@cult", (Qu2PrgCulRep.SelectedValue == "Oui")));
            command.Parameters.Add(new OleDbParameter("@avoix", (Qu2CollectifRep.SelectedValue == "Oui")));
            command.Parameters.Add(new OleDbParameter("@carr", (Qu2CarrRep.SelectedValue == "Oui")));
            command.Parameters.Add(new OleDbParameter("@slam", (Qu2SlamRep.SelectedValue == "Oui")));
            command.Parameters.Add(new OleDbParameter("@conc", (Qu2ConRep.SelectedValue == "Oui")));
            command.ExecuteNonQuery();

            //Avoir l'id corespondant
            command.Dispose();
            query = "SELECT * FROM GetLastQu2ID";
            command = new OleDbCommand(query, conn);
            reader = command.ExecuteReader();
            while (reader.Read())
                id[1] = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();

            #endregion

            #region Question 3

            //Insérer dans la base de données
            command.Dispose();
            query = "INSERT INTO Question3(ActiCon,ActiRes,Atelier,Camp,Classe,Concours,Conference,Expo,Mentorat,Parcours,PrixLivre,Residence,Salon,GestionCar,MiseEnMarche,NouvTech)" +
                    "VALUES (@acticon,@actiRes,@atel,@camp,@classe,@conc,@conf,@expo,@mento,@par,@prixlivre,@res,@salon,@gest,@mise,@nouv)";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@acticon", RateActiConValue.Value));
            command.Parameters.Add(new OleDbParameter("@actiRes", RateActiResValue.Value));
            command.Parameters.Add(new OleDbParameter("@atel", RateActiEcritValue.Value));
            command.Parameters.Add(new OleDbParameter("@camp", RateCampValue.Value));
            command.Parameters.Add(new OleDbParameter("@classe", RateClasseValue.Value));
            command.Parameters.Add(new OleDbParameter("@conc", RateConcourValue.Value));
            command.Parameters.Add(new OleDbParameter("@conf", RateConfValue.Value));
            command.Parameters.Add(new OleDbParameter("@expo", RateExpoValue.Value));
            command.Parameters.Add(new OleDbParameter("@expo", RateGesCarrValue.Value));
            command.Parameters.Add(new OleDbParameter("@mento", RateMentoValue.Value));
            command.Parameters.Add(new OleDbParameter("@par", RateParcourValue.Value));
            command.Parameters.Add(new OleDbParameter("@prixlivre", RatePrixLivreValue.Value));
            command.Parameters.Add(new OleDbParameter("@res", RateResiValue.Value));
            command.Parameters.Add(new OleDbParameter("@salon",RateSalonValue.Value));
            command.Parameters.Add(new OleDbParameter("@mise", RateMiseMarcheValue.Value));
            command.Parameters.Add(new OleDbParameter("@nouv", RateNouvtecValue.Value));

            command.ExecuteNonQuery();

            //Avoir l'id corespondant
            command.Dispose();
            query = "SELECT * FROM GetLastQu3ID";
            command = new OleDbCommand(query, conn);
            reader = command.ExecuteReader();
            while (reader.Read())
                id[2] = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();
            #endregion

            #region Question 4

            //Insérer dans la base de données
            command.Dispose();
            query = "INSERT INTO Question4(Valeur) VALUES (@valeur)";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@valeur",Qu5Rep.Text));
            command.ExecuteNonQuery();

            //Avoir l'id corespondant
            command.Dispose();
            query = "SELECT * FROM GetLastQu4ID";
            command = new OleDbCommand(query, conn);
            reader = command.ExecuteReader();
            while (reader.Read())
                id[3] = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();
            #endregion

            #region Question 5

            //Insérer dans la base de données
            command.Dispose();
            query = "INSERT INTO Question5(Valeur) VALUES (@valeur)";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@valeur", Qu6Rep.Text));
            command.ExecuteNonQuery();

            //Avoir l'id corespondant
            command.Dispose();
            query = "SELECT * FROM GetLastQu5ID";
            command = new OleDbCommand(query, conn);
            reader = command.ExecuteReader();
            while (reader.Read())
                id[4] = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();

            #endregion

            #region Question 6

            //Insérer dans la base de données
            command.Dispose();
            query = "INSERT INTO Question6(Valeur) VALUES (@valeur)";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@valeur", Qu7Rep.Text));
            command.ExecuteNonQuery();

            //Avoir l'id corespondant
            command.Dispose();
            query = "SELECT * FROM GetLastQu6ID";
            command = new OleDbCommand(query, conn);
            reader = command.ExecuteReader();
            while (reader.Read())
                id[5] = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();

            #endregion

            #region Question 7

            //Insérer dans la base de données
            command.Dispose();
            query = "INSERT INTO Question7(Valeur) VALUES (@valeur)";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@valeur", Qu8Rep.Text));
            command.ExecuteNonQuery();

            //Avoir l'id corespondant
            command.Dispose();
            query = "SELECT * FROM GetLastQu7ID";
            command = new OleDbCommand(query, conn);
            reader = command.ExecuteReader();
            while (reader.Read())
                id[6] = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();

            #endregion

            #region Question 8

            //Insérer dans la base de données
            command.Dispose();
            query = "INSERT INTO Question8(Valeur) VALUES (@valeur)";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@valeur", Qu9Rep.Text));
            command.ExecuteNonQuery();

            //Avoir l'id corespondant
            command.Dispose();
            query = "SELECT * FROM GetLastQu8ID";
            command = new OleDbCommand(query, conn);
            reader = command.ExecuteReader();
            while (reader.Read())
                id[7] = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();
            #endregion

            #region Question 9 
            command.Dispose();
            query = "INSERT INTO Question9(Reponse) VALUES (@rep)";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@rep",(Qu10FormCon.SelectedValue == "Oui")));
            command.ExecuteNonQuery();

            //Avoir l'id corespondant
            command.Dispose();
            query = "SELECT * FROM GetLastQu9ID";
            command = new OleDbCommand(query, conn);
            reader = command.ExecuteReader();
            while (reader.Read())
                id[8] = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();
            #endregion

            #region Question 10
            //Insérer dans la base de données
            command.Dispose();
            query = "INSERT INTO Question10(Valeur) VALUES (@valeur)";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@valeur", Qu10Rep.Text));
            command.ExecuteNonQuery();

            //Avoir l'id corespondant
            command.Dispose();
            query = "SELECT * FROM GetLastQu10ID";
            command = new OleDbCommand(query, conn);
            reader = command.ExecuteReader();
            while (reader.Read())
                id[9] = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();
            #endregion


            #region Information

            //Insérer dans la base de données
            command.Dispose();
            query = "INSERT INTO Personnes(Nom,Statut,Membre,NoTel,Courriel) VALUES" +
                    "(@nom,@stat,@mem,@notel,@courr)";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@nom", txtNom.Text));
            command.Parameters.Add(new OleDbParameter("@stat", cmbStatut.SelectedValue));
            command.Parameters.Add(new OleDbParameter("@mem", (cmbMembre.SelectedValue == "Oui")));
            command.Parameters.Add(new OleDbParameter("@notel", txtTel.Text));
            command.Parameters.Add(new OleDbParameter("@courr", txtCourriel.Text));
            command.ExecuteNonQuery();

            //Avoir l'id corespondant
            command.Dispose();
            query = "SELECT * FROM GetLastPersID";
            command = new OleDbCommand(query, conn);
            reader = command.ExecuteReader();
            while (reader.Read())
                id[10] = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();

            #endregion

            #region Sondage
            command.Dispose();
            query = "INSERT INTO Sondage(Question1,Question2,Question3,Question4,Question5,Question6,Question7,Question8,Question9,Question10,InfoPers)" +
                    "VALUES (@q1,@q2,@q3,@q4,@q5,@q6,@q7,@q8,@q9,@q10,@info)";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@q1", id[0]));
            command.Parameters.Add(new OleDbParameter("@q2", id[1]));
            command.Parameters.Add(new OleDbParameter("@q3", id[2]));
            command.Parameters.Add(new OleDbParameter("@q4", id[3]));
            command.Parameters.Add(new OleDbParameter("@q5", id[4]));
            command.Parameters.Add(new OleDbParameter("@q6", id[5]));
            command.Parameters.Add(new OleDbParameter("@q7", id[6]));
            command.Parameters.Add(new OleDbParameter("@q8", id[7]));
            command.Parameters.Add(new OleDbParameter("@q9", id[8]));
            command.Parameters.Add(new OleDbParameter("@q10", id[9]));
            command.Parameters.Add(new OleDbParameter("@info", id[10]));

            command.ExecuteNonQuery();
            #endregion
        }
        finally
        {
            conn.Close();
        } 
    }

    private bool CheckIfAlreadyDone()
    {
        string query;
        OleDbDataReader reader;
        OleDbCommand command;
        OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["SondBD"].ConnectionString;

        try
        {
            conn.Open();
            query = "SELECT Nom,Statut,Membre,NoTel,Courriel FROM Personnes WHERE" +
        " Nom = @nom AND Statut = @stat AND Membre = @mem AND NoTel = @notel AND Courriel = @courr";
            command = new OleDbCommand(query, conn);
            command.Parameters.Add(new OleDbParameter("@nom", txtNom.Text));
            command.Parameters.Add(new OleDbParameter("@stat", cmbStatut.SelectedValue));
            command.Parameters.Add(new OleDbParameter("@mem", (cmbMembre.SelectedValue == "Oui")));
            command.Parameters.Add(new OleDbParameter("@notel", txtTel.Text));
            command.Parameters.Add(new OleDbParameter("@courr", txtCourriel.Text));
            reader = command.ExecuteReader();
            if (reader.HasRows) {
                conn.Close();
                return true;
             }

        }
        finally
        {
            conn.Close();
        }
        return !true;
    }
}