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 EntrepotInvestissementDCE : EntrepotBase, IEntrepotInvestissementDCE
    {
        public IList<InvestissementDCE> GetByCodeRNEAndAnnee(string codeRNE, int annee)
        {
            IList<InvestissementDCE> resultat = new List<InvestissementDCE>();


             string codeAstre = "";
            string An = annee.ToString();
            using (SqlConnection connexion1 = new SqlConnection(this.ChaineDeConnexion))
            {

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

            }
//            using (SqlConnection connexion = new SqlConnection(this.ChaineDeConnexion))
            using (OracleConnection connexion = new OracleConnection(this.ChaineDeConnexionAstreGF))

            {

                try
                {
                    connexion.Open();
                    // using (SqlCommand command = connexion.CreateCommand())
                    using (OracleCommand command = connexion.CreateCommand())
                    {
//                        command.CommandText = "Select Annee, Libelle, Montant from Investissement INNER JOIN TypeDotation ON Investissement.IdDotation = TypeDotation.id WHERE CodeRNE = @RNE AND Annee = @Annee ORDER BY Libelle ASC";
//                        command.Parameters.AddWithValue("@RNE", codeRNE);
//                        command.Parameters.AddWithValue("@Annee", annee);
//                        using (SqlDataReader dr = command.ExecuteReader())

                        // command.CommandText = "Select NCOD_EXEBUD, LIB_C_COUT, MNT_VENT from SO.W67_ANA_MDT_VUE8_COLLEGE3800 INNER JOIN Etablissement ON Etablissement.NumeroAnaAstreGF = SO_W67_ANA_MDT_VUE8_COLLEGE3800.LIB_SIGLE WHERE CodeRNE = :RNE AND NCOD_EXEBUD = :Annee ORDER BY LIB_C_COUT ASC";
                        command.CommandText = "Select NCOD_EXEBUD, LIB_C_COUT, Sum(TO_NUMBER(Round(MNT_VENT))) As SUBVENTION from SO.W67_ANA_MDT_VUE8_COLLEGE3800 WHERE  SO.W67_ANA_MDT_VUE8_COLLEGE3800.LIB_SIGLE= :COD_ASTRE AND NCOD_EXEBUD >= :ANNEE GROUP BY NCOD_EXEBUD, LIB_C_COUT ORDER BY NCOD_EXEBUD, LIB_C_COUT ASC";
                         command.Parameters.AddWithValue(":ANNEE", annee);
                            command.Parameters.AddWithValue(":COD_ASTRE", codeAstre);
 //                       command.Parameters.AddWithValue(":RNE", codeRNE);
  //                      command.Parameters.AddWithValue(":Annee", annee);
                        using (OracleDataReader ds = command.ExecuteReader())



                        {
                            while (ds.Read())
                            {
//                                resultat.Add(new InvestissementDCE(Convert.ToInt16(dr["Annee"].ToString()), dr["Libelle"].ToString(), Convert.ToDouble(dr["Montant"].ToString())));
                                resultat.Add(new InvestissementDCE(Convert.ToInt16(ds["NCOD_EXEBUD"].ToString()), ds["LIB_C_COUT"].ToString(), Convert.ToDouble(ds["SUBVENTION"].ToString())));
                            }
                        }
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connexion.State == ConnectionState.Open)
                        connexion.Close();
                }
            }

            return resultat;
        }
    }
}