using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Oracle.DataAccess.Client;
using System.Data.OleDb;
using CG67.FicheCollege.Domaine;
using CG67.FicheCollege.Interface;

namespace CG67.FicheCollege.Entrepot
{
    public class EntrepotEtablissement : EntrepotBase, IEntrepotEtablissement
    {
        public IList<Etablissement> GetAll()
        {
            IList<Etablissement> resultat = new List<Etablissement>();
            using (SqlConnection connexion = new SqlConnection(this.ChaineDeConnexion))
            {
                try
                {
                    connexion.Open();
                    using (SqlCommand command = connexion.CreateCommand())
                    {
                        command.CommandText = "Select Etablissement.CodeRNE, Etablissement.NomCollege, Adresse.Ville from Etablissement LEFT JOIN Adresse ON (Etablissement.IdAdresse = Adresse.Id) ORDER BY Adresse.Ville, Etablissement.NomCollege ASC";
                        using (SqlDataReader dr = command.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                resultat.Add(new Etablissement(dr["CodeRNE"].ToString(), dr["NomCollege"].ToString(), dr["Ville"].ToString()));
                            }
                        }
                    }
                }
                catch (Exception erreurInterne)
                {
                    throw new Exception(" " + erreurInterne);
                }
                finally
                {
                    if (connexion.State == ConnectionState.Open)
                        connexion.Close();
                }
            }

            return resultat;
        }


        public Etablissement GetByCodeRNEAndAnnee(string codeRNE, int annee)
        {
            Etablissement resultat = null;
            using (SqlConnection connexion = new SqlConnection(this.ChaineDeConnexion))
            {
                try
                {
                    connexion.Open();
                    using (SqlCommand command = connexion.CreateCommand())
                    {
                        command.CommandText = "Select * from Etablissement LEFT JOIN Adresse ON (Etablissement.IdAdresse = Adresse.Id) WHERE Etablissement.CodeRNE = @RNE";
                        command.Parameters.AddWithValue("@RNE", codeRNE);
                        command.Parameters.AddWithValue("@Annee", annee);
                        using (SqlDataReader dr = command.ExecuteReader())
                        {
                            if (dr.Read())
                            {
                                string adresse = dr["NumRue"].ToString() + ", " + dr["TypeDeVoie"].ToString() + " " + dr["Voie"].ToString();
                                
                                resultat = new Etablissement(codeRNE, annee, dr["NomCollege"].ToString(),
                                    dr["TypeEtablissement"].ToString(), Convert.ToInt16(dr["AnneeConstruction"].ToString()),
                                    dr["Telephone"].ToString(), dr["Email"].ToString(), dr["Fax"].ToString(),
                                    dr["NomPropriétaire"].ToString(), adresse, dr["ComplementAdresse"].ToString(),
                                    Convert.ToInt32(dr["CodePostal"].ToString()), dr["Ville"].ToString(), dr["LienPhoto"].ToString(),
                                    EntrepotFactory.GetEntrepotActionEducative().GetByCodeRNEAndAnnee,
                                    EntrepotFactory.GetEntrepotATC().GetByCodeRNE,
                                    EntrepotFactory.GetEntrepotBilinguisme().GetByCodeRNEAndAnnee,
                                    EntrepotFactory.GetEntrepotCommentaires().GetByCodeRNEAndAnnee,
                                    EntrepotFactory.GetEntrepotContact().GetByCodeRNE,
                                    EntrepotFactory.GetEntrepotDotation().GetByCodeRNEAndAnnee,
                                    EntrepotFactory.GetEntrepotEquipement().GetByCodeRNEAndAnnee,
                                    EntrepotFactory.GetEntrepotInvestissement().GetByCodeRNEAndAnnee,
                                    EntrepotFactory.GetEntrepotMCG().GetByCodeRNE,
                                    EntrepotFactory.GetEntrepotLogement().GetByCodeRNE, 
                                    EntrepotFactory.GetEntrepotProgTravaux().GetByCodeRNEAndAnnee, 
                                    EntrepotFactory.GetEntrepotProjetPilote().GetByCodeRNEAndAnnee,
                                    EntrepotFactory.GetEntrepotRestauration().GetByRNEAndAnnee);
                            }
                        }

                        command.CommandText = "SELECT * FROM EtpTheorique WHERE CodeRNE = @RNE AND Annee = @Annee";
                        using (SqlDataReader dr2 = command.ExecuteReader())
                        {
                            if (dr2.Read())
                            {
                                resultat.ValeurETPTheorique = Convert.ToInt32(dr2["Valeur"].ToString());
                            }
                        }

                        command.CommandText = "SELECT * FROM Reserve WHERE CodeRNE = @RNE AND Annee = @Annee";
                        using (SqlDataReader dr3 = command.ExecuteReader())
                        {
                            if (dr3.Read())
                            {
                                resultat.Reserve = Convert.ToInt32(dr3["Montant"].ToString());
                            }
                        }

                        command.CommandText = "SELECT Annee, sum(TotalEleves) as TotalEleves FROM Effectif WHERE CodeRNE = @RNE AND Annee BETWEEN @AnneeMoins10 AND @Annee GROUP BY Annee ORDER BY Annee ASC";
                        command.Parameters.AddWithValue("@AnneeMoins10", annee - 10);
                        using (SqlDataReader dr4 = command.ExecuteReader())
                        {
                            IList<int> lstEffectifReel = new List<int>();
                            while (dr4.Read())
                            {
                                lstEffectifReel.Add(Convert.ToInt32(dr4["TotalEleves"].ToString()));
                            }
                            resultat.LstEffectifReel = lstEffectifReel;
                        }

                        command.CommandText = "SELECT Annee, sum(NbEleve) as TotalEleves FROM PrevisionEffectif WHERE CodeRNE = @RNE AND Annee BETWEEN @Annee AND @AnneePlus5 GROUP BY Annee ORDER BY Annee ASC";
                        command.Parameters.AddWithValue("@AnneePlus5", annee + 5);
                        using (SqlDataReader dr5 = command.ExecuteReader())
                        {
                            IList<int> lstEffectifPrev = new List<int>();
                            while (dr5.Read())
                            {
                                lstEffectifPrev.Add(Convert.ToInt32(dr5["TotalEleves"].ToString()));
                            }
                            resultat.LstEffectifPrevisionnel = lstEffectifPrev;
                        }

                        command.CommandText = "SELECT * from Restructuration WHERE CodeRNE = @RNE";
                        using (SqlDataReader dr6 = command.ExecuteReader())
                        {
                            IList<int> lstRestructuration = new List<int>();
                            while (dr6.Read())
                            {
                                lstRestructuration.Add(Convert.ToInt32(dr6["Annee"].ToString()));
                            }
                            resultat.LstRestructuration = lstRestructuration;
                        }

                        command.CommandText = "SELECT * from SecteurRecrutement WHERE CodeRNE = @RNE";
                        using (SqlDataReader dr7 = command.ExecuteReader())
                        {
                            IList<string> lstSecteurRecrut = new List<string>();
                            while (dr7.Read())
                            {
                                lstSecteurRecrut.Add(dr7["Libelle"].ToString());
                            }
                            resultat.LstSecteurRecrutement = lstSecteurRecrut;
                        }

                        command.CommandText = "SELECT * from Remplacement INNER JOIN ATC ON (ATC.Id = Remplacement.IdATCRemplace) INNER JOIN Contact ON (ATC.Id = Contact.Id) WHERE CodeRNE = @RNE AND Remplacement.DateDebut + DureeEnSemaine >= GETDATE()";
                        using (SqlDataReader dr8 = command.ExecuteReader())
                        {
                            int nbrRemplacements = 0;
                            while (dr8.Read())
                            {
                                nbrRemplacements++; ;
                            }
                            resultat.NbrRemplacementsATC = nbrRemplacements;
                        }
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.ToString());
                }
                finally
                {
                    if (connexion.State == ConnectionState.Open)
                        connexion.Close();
                }
            }

            using (OleDbConnection connexion = new OleDbConnection(this.ChaineDeConnexionAccess))
            {
                try
                {
                    connexion.Open();
                    using (OleDbCommand command = connexion.CreateCommand())
                    {
                        command.CommandText = "SELECT Sum(Externat_Enseignementgeneral + Externat_Labochimie_sciencenat + Externat_Labophysique + Externat_Salleinformatique + Externat_Batimentsdemontables + Externat_Administration + Gymnaseatelier_Gymnases + Gymnaseatelier_Ateliers + Restauration_internats_internats + Restauration_internats_Restauration + Logements_et_exterieur_Logements + Logements_et_exterieur_Autres + Logements_et_exterieur_Logautres + Logements_et_exterieur_Cours + Logements_et_exterieur_Parkings + Logements_et_exterieur_Espacesverts + Logements_et_exterieur_Airessportives + Logements_et_exterieur_Consouvertes) as SurfaceTotale FROM [T_EDL Surfaces données du rectorat] LEFT JOIN T_COLLEGE ON ([T_EDL Surfaces données du rectorat].Lien_T_College = T_COLLEGE.REF_COLLEGE) WHERE T_COLLEGE.CODE_COLLEGE = @RNE";
                        command.Parameters.AddWithValue("@RNE", codeRNE);
                        using (OleDbDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                resultat.Surface = Convert.ToDouble(reader["SurfaceTotale"].ToString());
                            }
                        }
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connexion.State == ConnectionState.Open)
                        connexion.Close();
                }
            }

            return resultat;
        }
    }
}