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 EntrepotContact : EntrepotBase, IEntrepotContact
    {
        public IList<Contact> GetAllContactByCodeRNE(string codeRNE)
        {
            IList<Contact> resultat = GetByCodeRNE(codeRNE);
            Contact cg = GetConseillerGeneralByRNE(codeRNE);
            if (cg != null)
                resultat.Add(cg);
            return resultat;
        }
        public IList<Contact> GetByCodeRNE(string codeRNE)
        {
            IList<Contact> resultat = new List<Contact>();
            using (SqlConnection connexion = new SqlConnection(this.ChaineDeConnexion))
            {
                try
                {
                    connexion.Open();
                    using (SqlCommand command = connexion.CreateCommand())
                    {

//                        command.CommandText = "SELECT * FROM Contact WHERE CodeRNE = @RNE";
                        command.CommandText = "SELECT * FROM Contact INNER JOIN  Fonction ON Contact.IdFonction = Fonction.Id  WHERE (CodeRNE = @RNE)";
                        command.Parameters.AddWithValue("@RNE", codeRNE);
                        using (SqlDataReader dr = command.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                resultat.Add(new Contact(dr["Civilite"].ToString(), dr["Nom"].ToString(), dr["Prenom"].ToString(), dr["Libelle"].ToString(),dr["TypeContact"].ToString()));
                            }
                        }
                    }
                }
                catch 
                {
                    throw;
                }
                finally
                {
                    if (connexion.State == ConnectionState.Open)
                        connexion.Close();
                }
            }
            return resultat;
        }

        public Contact GetConseillerGeneralByRNE(string codeRNE)
        {
            Contact resultat = null;
            using (SqlConnection connexion = new SqlConnection(this.ChaineDeConnexion))
            {
                try
                {
                    connexion.Open();
                    using (SqlCommand command = connexion.CreateCommand())
                    {
                        //command.CommandText = "SELECT * FROM Contact WHERE id = (select idConseillerGeneral from etablissement where codeRNE=@RNE)";
                        command.CommandText = "SELECT * FROM Contact, Fonction  WHERE (IdFonction = Fonction.Id) and Contact.id = (select idConseillerGeneral from etablissement where codeRNE=@RNE)";
                        command.Parameters.AddWithValue("@RNE", codeRNE);
                        using (SqlDataReader dr = command.ExecuteReader())
                        {
                            if (dr.Read())
                            {
                                resultat = new Contact(dr["Civilite"].ToString(), dr["Nom"].ToString(), dr["Prenom"].ToString(), dr["Libelle"].ToString(), dr["TypeContact"].ToString());
                            }
                        }
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connexion.State == ConnectionState.Open)
                        connexion.Close();
                }
                return resultat;
            }
        }
    }
}