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

namespace CG67.FicheCollege.Entrepot
{
    public class EntrepotProgTravaux : EntrepotBase, IEntrepotProgTravaux
    {
        public IList<ProgTravaux> GetByCodeRNEAndAnnee(string codeRNE, int annee)
        {
            IList<ProgTravaux> resultat = new List<ProgTravaux>();
            using (OracleConnection connexion = new OracleConnection(this.ChaineDeConnexionCollege))
            {
                try
                {
                    connexion.Open();
                    using (OracleCommand command = connexion.CreateCommand())
                    {
                        command.CommandText = "SELECT ANNEE, LIBELLE_OPERATION, MONTANT_PREV, TYPE_OPERATION FROM T_OPERAT_SERV_BAT INNER JOIN T_COLLEGE ON (T_OPERAT_SERV_BAT.LIEN_T_COLLEGE = T_COLLEGE.REF_COLLEGE) WHERE TYPE_OPERATION Like 'Restructuration%' AND CODE_COLLEGE = :RNE AND LIBELLE_OPERATION NOT IN (Select LIBELLE_OPERATION FROM T_OPERAT_SERV_BAT WHERE LIBELLE_OPERATION = 'Op') AND ANNEE BETWEEN :AnneeMoins1 AND :AnneePlus1 ORDER BY TYPE_OPERATION, ANNEE ASC";
                        command.Parameters.AddWithValue (":RNE", codeRNE);
                        int anneeMoins1 = annee - 1;
                        command.Parameters.AddWithValue(":AnneeMoins1", anneeMoins1);
                        int anneePlus1 = annee + 1;
                        command.Parameters.AddWithValue (":AnneePlus1", anneePlus1);
                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                resultat.Add(new ProgTravaux(Convert.ToInt16(reader["ANNEE"].ToString()), reader["LIBELLE_OPERATION"].ToString(), Convert.ToDouble(reader["MONTANT_PREV"].ToString()), reader["TYPE_OPERATION"].ToString()));
                            }
                        }
                    }
                }
                catch (Exception erreurInterne)
                {
                    throw new Exception(" " + erreurInterne);
                }
                finally
                {
                    if (connexion.State == ConnectionState.Open)
                        connexion.Close();
                }
            }
            return resultat;
        }
    }
}