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, STATUT_OP, IMPREVU FROM T_OPERAT_SERV_BAT INNER JOIN T_COLLEGE ON (T_OPERAT_SERV_BAT.LIEN_T_COLLEGE = T_COLLEGE.REF_COLLEGE) WHERE CODE_COLLEGE = :RNE AND LIBELLE_OPERATION NOT IN (Select LIBELLE_OPERATION FROM T_OPERAT_SERV_BAT WHERE LIBELLE_OPERATION = 'Op') AND ANNEE BETWEEN :AnneeMoins2 AND :Annee ORDER BY ANNEE , TYPE_OPERATION ASC";
                        //     command.CommandText = "SELECT ANNEE, LIBELLE_OPERATION, MONTANT_PREV, TYPE_OPERATION, STATUT_OP, IMPREVU 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%' OR TYPE_OPERATION Like 'Maintenance%') AND CODE_COLLEGE = :RNE AND LIBELLE_OPERATION NOT IN (Select LIBELLE_OPERATION FROM T_OPERAT_SERV_BAT WHERE LIBELLE_OPERATION = 'Op') AND ANNEE BETWEEN :AnneeMoins2 AND :Annee ORDER BY ANNEE , TYPE_OPERATION ASC";
                        command.Parameters.AddWithValue (":RNE", codeRNE);
                        int anneeMoins2 = annee - 2;
                        command.Parameters.AddWithValue(":AnneeMoins2", anneeMoins2);
                        command.Parameters.AddWithValue (":Annee", annee +1 );
                        double montantPrev;
                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                try
                                {
                                    montantPrev = Convert.ToDouble(reader["MONTANT_PREV"].ToString());
                                }
                                catch
                                {
                                    montantPrev = 0;
                                }
                                resultat.Add(new ProgTravaux(Convert.ToInt16(reader["ANNEE"].ToString()), reader["LIBELLE_OPERATION"].ToString(), montantPrev, reader["TYPE_OPERATION"].ToString(), reader["STATUT_OP"].ToString(), Convert.ToInt16(reader["IMPREVU"].ToString())));
                            }
                        }
                    }
                }
                catch 
                {
                    throw;
                }
                finally
                {
                    if (connexion.State == ConnectionState.Open)
                        connexion.Close();
                }
            }
            return resultat;
        }
    }
}