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 EntrepotATC : EntrepotBase, IEntrepotATC
    {
        public IList<ATC> GetByCodeRNE(string codeRNE)
        {
            IList<ATC> resultat = new List<ATC>();
            using (SqlConnection connexion = new SqlConnection(this.ChaineDeConnexion))
            {
                try
                {
                    string FicheDePoste = "";
                   string codeAstre = "";
                   connexion.Open();
                    using (SqlCommand command = connexion.CreateCommand())
                    {
                        command.CommandText = "Select CodeAstreRH from Etablissement WHERE CodeRNE = @RNE";
                        command.Parameters.AddWithValue("@RNE", codeRNE);
                        using (SqlDataReader dr = command.ExecuteReader())
                        {
                            if (dr.Read())
                                codeAstre = dr["CodeAstreRH"].ToString();
                        }
                    }

                    using (OracleConnection connexion1 = new OracleConnection(this.ChaineDeConnexionRH))
                    {
                        try
                        {
                            connexion1.Open();
                            using (OracleCommand command1 = connexion1.CreateCommand())
                            {
                                command1.CommandText = "Select distinct NOM_USUEL, NOM_PRENOM, LIB_POSTE, LIB_CATAGT from V_ATC WHERE COD_STRUC = :CODE_STR";
                               command1.Parameters.AddWithValue(":CODE_STR", codeAstre);
                                using (OracleDataReader dr1 = command1.ExecuteReader())
                                {

                                    while (dr1.Read())
                                    {
                                        FicheDePoste = dr1["LIB_POSTE"].ToString();
                                        FicheDePoste = FicheDePoste.Replace("&", "et");
                                        if (FicheDePoste == "")
                                            FicheDePoste = "Agent polyvalent d'entretien et d'accueil";

                                        resultat.Add(new ATC(dr1["NOM_USUEL"].ToString(), dr1["NOM_PRENOM"].ToString(), FicheDePoste, dr1["LIB_CATAGT"].ToString()));
                                    }
                                }
                            }
                        }

                        catch
                        {
                            throw;
                        }
                        finally
                        {
                            if (connexion1.State == ConnectionState.Open)
                                connexion1.Close();
                        }
                    }    
                    }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connexion.State == ConnectionState.Open)
                        connexion.Close();
                }
            }
            return resultat;
        }
    }
}