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

namespace CG67.FicheCollege.Entrepot
{
    public class EntrepotClasseDecouverte : EntrepotBase, IEntrepotClasseDecouverte
    {

        public IList<ClasseDecouverte> GetByCodeRNEAndAnnee(string codeRNE, int annee)
        {
            IList<ClasseDecouverte> resultat = new List<ClasseDecouverte>();
            string codeAstre = "";
            string An = annee.ToString();
            using (SqlConnection connexion1 = new SqlConnection(this.ChaineDeConnexion))
            {

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

                using (OracleConnection connexion = new OracleConnection(this.ChaineDeConnexionSubvention))
                {
                    try
                    {
                        connexion.Open();
                        using (OracleCommand command = connexion.CreateCommand())
                        { // modif le 14/04/2010 - modification pour l'année en cours => année civile
                            //command.CommandText = "Select ANNEE, ZONE, Sum(TO_NUMBER(NB_ELEVE)) as NBELEVES, Sum(MT_VOTE) AS SUBVENTION from ASTRE.W67_SIC_FICHE_ELU WHERE ANNEE = '" + annee + "' AND COD_TIERS = '" + codeAstre + "' GROUP BY ZONE, ANNEE ORDER BY ZONE ";
                            command.CommandText = "Select ANNEE, ZONE, Sum(TO_NUMBER(NB_ELEVE)) as NBELEVES, Sum(MT_VOTE) AS SUBVENTION from ASTRE.W67_SIC_FICHE_ELU WHERE ANNEE = :ANNEE AND COD_TIERS = :COD_ASTRE GROUP BY ZONE, ANNEE ORDER BY ZONE ";
                            command.Parameters.AddWithValue(":ANNEE", annee);
                            command.Parameters.AddWithValue(":COD_ASTRE", codeAstre);
                            using (OracleDataReader ds = command.ExecuteReader())
                            {

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

                return resultat;
            }
        }

       }
}