using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CG67.FicheCollege.Domaine;
using CG67.FicheCollege.Interface;

namespace CG67.FicheCollege.Entrepot
{
    public class EntrepotCanton : EntrepotBase, IEntrepotCanton
    {
        public IList<Canton> GetByCodeRNE(string codeRNE)
        {
            IList<Canton> resultat = new List<Canton>();
            string libelleCanton = " ";
            string CodeCanton = " "; 
             using (SqlConnection connexion = new SqlConnection(this.ChaineDeConnexion))
                {
                    try
                    {
                        connexion.Open();
                        using (SqlCommand command = connexion.CreateCommand())
                        {
                          
                          //   command.CommandText = "SELECT  Libellé, Etablissement.CodeRNE FROM Canton INNER JOIN Etablissement ON Canton.Id = Etablissement.IdCanton WHERE Etablissement.CodeRNE = @RNE ";
                            command.CommandText = "SELECT  Libellé, Etablissement.CodeRNE, Canton.Id FROM Canton INNER JOIN Etablissement ON Canton.Id = Etablissement.IdCanton WHERE Etablissement.CodeRNE = @RNE ";
                            command.Parameters.AddWithValue("@RNE", codeRNE);
                            using (SqlDataReader dr = command.ExecuteReader())
                            {
                                if (dr.Read())
                                {

                                    libelleCanton = dr["Libellé"].ToString();
                                    CodeCanton = dr["Id"].ToString();

                                }
                                
                                 using (SqlConnection connexion1 = new SqlConnection(this.ChaineDeConnexion))
                                    {
                                        try
                                        {   
                                            connexion1.Open();
                                            using (SqlCommand command1 = connexion1.CreateCommand())
                                            {
                                                //command1.CommandText = "SELECT Contact.Nom, Contact.Prenom FROM Contact INNER JOIN ON ElusCanton ON ElusCanton.IdContact = Contact.Id WHERE ElusCanton.IdCanton = @IdCanton";
                                                command1.CommandText = "SELECT Contact.Civilite, Contact.Nom, Contact.Prenom, Contact.IdFonction FROM Contact INNER JOIN ElusCanton ON ElusCanton.IdContact = Contact.Id WHERE ElusCanton.IdCanton = @IdCanton ORDER BY Contact.Civilite";

                                                command1.Parameters.AddWithValue("@IdCanton", CodeCanton);
                                                using (SqlDataReader dr1 = command1.ExecuteReader())
                                                {
                                                    while (dr1.Read())
                                                    {

                                                        resultat.Add(new Canton(libelleCanton, dr1["Civilite"].ToString(), dr1["Nom"].ToString(), dr1["Prenom"].ToString(), dr1["IdFonction"].ToString()));
                                                    }
                                                        
}
                                                }
                                            }

                                        catch (Exception erreurInterne)
                                        {
                                            throw new Exception(" " + erreurInterne);
                                        }
                                        finally
                                        {
                                            if (connexion1.State == ConnectionState.Open)
                                                connexion1.Close();
                                        }
                                    }

                                }
                            }
                        }
                    
                    catch (Exception erreurInterne)
                    {
                        throw new Exception(" " + erreurInterne);
                    }
                    finally
                    {
                        if (connexion.State == ConnectionState.Open)
                            connexion.Close();
                    }
                }
                return resultat;
            }
        }

    }