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 EntrepotMCG : EntrepotBase, IEntrepotMCG
    {
        public MCG GetByCodeRNE(string codeRNE)
        {
            MCG resultat = null;
            using (SqlConnection connexion = new SqlConnection(this.ChaineDeConnexion))
            {
                try
                {
                    connexion.Open();
                    using (SqlCommand command = connexion.CreateCommand())
                    {
                        //command.CommandText = "Select Nom, Prenom, Ville FROM Etablissement INNER JOIN Mcg ON (Etablissement.IdMCG = Mcg.Id) INNER JOIN Contact ON (Mcg.IdContact = Contact.Id) WHERE Etablissement.CodeRNE = @RNE AND Fonction = 'Directeur MCG'";
                        //command.CommandText = "Select Nom, Prenom, Ville FROM Etablissement INNER JOIN Mcg ON (Etablissement.IdMCG = Mcg.Id) INNER JOIN Contact ON (Mcg.IdContact = Contact.Id) WHERE Etablissement.CodeRNE = @RNE AND Fonction = 'Directeur MCG'";
                        command.CommandText = "SELECT Etablissement.CodeRNE, Ville, Civilite, Nom, Prenom FROM Etablissement INNER JOIN (Mcg INNER JOIN Contact ON Mcg.IdContact = Contact.Id) ON Etablissement.IdMCG = Mcg.Id INNER JOIN Fonction ON (Contact.IdFonction = Fonction.Id) WHERE Etablissement.CodeRNE = @RNE and Fonction.Libelle='Directeur MCG';";


                        command.Parameters.AddWithValue("@RNE", codeRNE);
                        using (SqlDataReader dr = command.ExecuteReader())
                        {
                            if (dr.Read())
                            {

                                using (SqlConnection connexion1 = new SqlConnection(this.ChaineDeConnexion))
                                {
                                    try
                                    {
                                        connexion1.Open();
                                        using (SqlCommand command1 = connexion1.CreateCommand())
                                        {
                                            command1.CommandText = "SELECT Contact.Nom, Contact.Prenom, Etablissement.CodeRNE FROM Etablissement INNER JOIN Mcg ON Etablissement.IdMCG = Mcg.Id INNER JOIN Contact ON Mcg.IdReferentPEP = Contact.Id WHERE Etablissement.CodeRNE = @RNE";


                                            command1.Parameters.AddWithValue("@RNE", codeRNE);
                                            using (SqlDataReader dr1 = command1.ExecuteReader())
                                            {
                                                if (dr1.Read())
                                                {

                                                    resultat = new MCG(dr["Ville"].ToString(), dr["Nom"].ToString(), dr["Prenom"].ToString(), dr1["Nom"].ToString(), dr1["Prenom"].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;
        }
    }
}