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 EntrepotClasseDecouverte : EntrepotBase, IEntrepotClasseDecouverte
    {

        public ClasseDecouverte GetByCodeRNEAndAnnee(string codeRNE, int annee)
        {
            ClasseDecouverte resultat = new ClasseDecouverte();
            string codeAstre = "";
            using (SqlConnection connexion1 = new SqlConnection(this.ChaineDeConnexion))
            {

                try
                {
                    connexion1.Open();
                    using (SqlCommand command = connexion1.CreateCommand())
                    {
                        command.CommandText = "Select NumeroTiersAstreGF from Etablissement WHERE CodeRNE = @RNE";
                        command.Parameters.AddWithValue("@RNE", codeRNE);
                        using (SqlDataReader dr = command.ExecuteReader())
                        {
                            if (dr.Read())
                                codeAstre = dr["NumeroTiersAstreGF"].ToString();
                        }
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connexion1.State == ConnectionState.Open)
                        connexion1.Close();
                }

                using (SqlConnection connexion = new SqlConnection(this.ChaineDeConnexionSubvention))
                {
                    try
                    {
                        connexion.Open();
                        using (SqlCommand command = connexion.CreateCommand())
                        {
                            command.CommandText = "Select * from ASTRE_W67_SIC_FICHE_ELU WHERE CodeRNE = @RNE";
                            command.Parameters.AddWithValue("@RNE", codeAstre);
                            using (SqlDataReader dr = command.ExecuteReader())
                            {

                                while (dr.Read())
                                {
                                    resultat= new ClasseDecouverte(dr["annee"].ToString(), dr["ZONE"].ToString(), dr["NB_ELEVE"].ToString(), Convert.ToDouble(dr["MT_VOTE"].ToString()));
                                }
                            }
                        }
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        if (connexion.State == ConnectionState.Open)
                            connexion.Close();
                    }
                }

                return resultat;
            }
        }

        #region IEntrepotClasseDecouverte Membres

        ClasseDecouverte IEntrepotClasseDecouverte.GetByCodeRNEAndAnnee(string codeRNE, int annee)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion

    }
}