public ActionResult GetDetailsJson(int noOffre)
        {
            SqlConnection cnn = null;
            string connetionString = Properties.Settings.Default.dbConnectionString;
            SqlCommand command = null;
            OffreBean offre = null;
            string sql = "SELECT o.Id, e.DesctEtat, o.CoursOblig, o.CoursRecom, l.CodeISBN_10, " +
                         "l.CodeISBN_13, l.Nom, l.Image, o.Remarques, u.Email, u.PhoneNumber, l.Auteur, o.Prix, l.AnneeEdition " +
                         "FROM Offre o " +
                         "JOIN Livre l On o.IdArticle = l.Id " +
                         "JOIN Etat e ON o.Etat = e.CodeEtat " +
                         "JOIN AspNetUsers u On u.Id = o.userId " +
                         "Where o.IdTypeArticle = 1 AND o.Id = " + noOffre;

            cnn = new SqlConnection(connetionString);

            try
            {
                cnn.Open();
                command = new SqlCommand(sql, cnn);
                var dataReader = command.ExecuteReader();

                while (dataReader.Read())
                {
                    offre = new OffreBean
                    {
                        NoOffre = (int)dataReader.GetValue(0),
                        EtatLivre = dataReader.GetValue(1).ToString(),
                        CoursObligatoires = dataReader.GetValue(2).ToString(),
                        CoursRecommandes = dataReader.GetValue(3).ToString(),
                        CodeIsbn_10 = dataReader.GetValue(4).ToString(),
                        CodeIsbn_13 = dataReader.GetValue(5).ToString(),
                        NomLivre = dataReader.GetValue(6).ToString(),
                        ImageLivre = dataReader.GetValue(7).ToString(),
                        Remarques = dataReader.GetValue(8).ToString(),
                        Email = dataReader.GetValue(9).ToString(),
                        Phone = dataReader.GetValue(10).ToString(),
                        Auteur = dataReader.GetValue(11).ToString(),
                        Prix = Convert.ToDouble(dataReader.GetValue(12).ToString()),
                        AnneeEdition = dataReader.GetValue(13).ToString()
                    };

                }

                dataReader.Close();
                command.Dispose();
                cnn.Close();
            }
            catch (Exception ex)
            {
                offre = new OffreBean();
                offre.Message = "Une erreur est survenue";
            }

            return Json(offre, JsonRequestBehavior.AllowGet);
        }
        public List<OffreBean> ObtenirListeOffresUtil()
        {
            List<OffreBean> offres = null;
            SqlConnection cnn = null;
            var connetionString = Properties.Settings.Default.dbConnectionString;
            var sql = "SELECT o.Id, l.Nom " +
                         "FROM Offre o " +
                         "JOIN Livre l On o.IdLivre = l.Id " +
                         "Where o.userId = '" + User.Identity.GetUserId() + "' And o.IndActif='1' order by o.Id desc";

            cnn = new SqlConnection(connetionString);

            try
            {
                cnn.Open();
                var command = new SqlCommand(sql, cnn);
                offres = new List<OffreBean>();

                var dataReader = command.ExecuteReader();

                while (dataReader.Read())
                {
                    var offre = new OffreBean
                    {
                        NoOffre = (int)dataReader.GetValue(0),
                        NomLivre = dataReader.GetValue(1).ToString(),
                        estNouv = false
                    };

                    offres.Add(offre);
                }

                dataReader.Close();
                command.Dispose();
                cnn.Close();
            }
            catch (Exception ex)
            {

            }

            return offres;
        }
        public List<OffreBean> ObtenirListeLivres(int start = 1, int last = 8, string reqRech = "", string tri = "l.Nom", string ordre = "ASC")
        {
            List<OffreBean> offres = null;
            SqlConnection cnn = null;
            string connetionString = Properties.Settings.Default.dbConnectionString;

            if (string.IsNullOrEmpty(tri))
            {
                tri = "l.Nom";
                ordre = "ASC";
            }

            var sql = "SELECT a.* FROM(";
            sql += "SELECT o.Id, e.DesctEtat, o.CoursOblig, o.CoursRecom, l.CodeISBN_10, " +
                   "l.CodeISBN_13, l.Nom, l.Image, o.Remarques, l.SousTitre, l.Auteur,o.Prix, o.IndActif, " +
                   "ROW_NUMBER() OVER (ORDER BY " + tri + " " + ordre + " ) AS ROWNUMBERS " +
                         "FROM Offre o " +
                         "JOIN Livre l On o.IdLivre = l.Id " +
                         "JOIN Etat e ON o.Etat = e.CodeEtat " +
                         "Where o.IndActif='1' " + reqRech;

            sql += ") a WHERE a.ROWNUMBERS BETWEEN " + start + " AND " + last;

            cnn = new SqlConnection(connetionString);

            try
            {
                cnn.Open();
                var command = new SqlCommand(sql, cnn);
                offres = new List<OffreBean>();

                SqlDataReader dataReader = command.ExecuteReader();

                while (dataReader.Read())
                {
                    var offre = new OffreBean
                    {
                        NoOffre = (int) dataReader.GetValue(0),
                        EtatLivre = dataReader.GetValue(1).ToString(),
                        CoursObligatoires = dataReader.GetValue(2).ToString(),
                        CoursRecommandes = dataReader.GetValue(3).ToString(),
                        CodeIsbn_10 = dataReader.GetValue(4).ToString(),
                        CodeIsbn_13 = dataReader.GetValue(5).ToString(),
                        NomLivre = dataReader.GetValue(6).ToString(),
                        ImageLivre = dataReader.GetValue(7).ToString() != "" ? dataReader.GetValue(7).ToString() : "https://encrypted-tbn3.gstatic.com/images?q=tbn:ANd9GcR7lSOcD29L4UQ8vuBd3rj1CREOKYOxCQE1Qrf8rAJmC500pR_9dA",
                        Remarques = dataReader.GetValue(8).ToString(),
                        SousTitre = dataReader.GetValue(9).ToString(),
                        Auteur = dataReader.GetValue(10).ToString(),
                        Prix = Convert.ToDouble(dataReader.GetValue(11).ToString())
                    };

                    offres.Add(offre);
                }

                dataReader.Close();
                command.Dispose();
                cnn.Close();
            }
            catch (Exception ex)
            {

            }

            if (offres != null && offres.Count > 0)
            {
                offres.First().OrdreItems.First(x => x.Value.Equals(ordre)).Selected = true;
                offres.First().TriItems.First(x => x.Value.Equals(tri)).Selected = true;
            }
            return offres;
        }
        public ActionResult GetDetailsJsonNotes(int noOffre)
        {
            SqlConnection cnn = null;
            string connetionString = Properties.Settings.Default.dbConnectionString;
            SqlCommand command = null;
            OffreBean offre = null;
            string sql = "SELECT o.Id, e.DesctEtat, o.CoursOblig, o.CoursRecom, " +
                         "n.Nom, n.SousTitre,n.MoisRedaction,n.AnneeRedaction,n.MoisRevision,n.AnneeRevision, o.Remarques, u.Email, u.PhoneNumber,  o.Prix " +
                         "FROM Offre o " +
                         "JOIN NotesDeCours n On o.IdArticle = n.IdNotesDeCours " +
                         "JOIN Etat e ON o.Etat = e.CodeEtat " +
                         "JOIN AspNetUsers u On u.Id = o.userId " +
                         "Where o.IdTypeArticle=2 AND o.Id = " + noOffre;

            cnn = new SqlConnection(connetionString);

            try
            {
                cnn.Open();
                command = new SqlCommand(sql, cnn);
                var dataReader = command.ExecuteReader();

                while (dataReader.Read())
                {
                    offre = new OffreBean
                    {
                        NoOffre = (int)dataReader.GetValue(0),
                        EtatLivre = dataReader.GetValue(1).ToString(),
                        CoursObligatoires = dataReader.GetValue(2).ToString(),
                        CoursRecommandes = dataReader.GetValue(3).ToString(),
                        NomNotesCours = dataReader.GetValue(4).ToString(),
                        SousTitre = dataReader.GetValue(5).ToString(),
                        MoisRedaction = dataReader.GetValue(6).ToString(),
                        AnneeRedaction = int.Parse(dataReader.GetValue(7).ToString()),
                        MoisRevision = dataReader.GetValue(8).ToString(),
                        AnneeRevision = int.Parse(dataReader.GetValue(9).ToString()),
                        Remarques = dataReader.GetValue(10).ToString(),
                        Email = dataReader.GetValue(11).ToString(),
                        Phone = dataReader.GetValue(12).ToString(),
                        Prix = Convert.ToDouble(dataReader.GetValue(13).ToString()),
                    };

                }

                dataReader.Close();
                command.Dispose();
                cnn.Close();
            }
            catch (Exception ex)
            {
                offre = new OffreBean();
                offre.Message = "Une erreur est survenue";
            }

            return Json(offre, JsonRequestBehavior.AllowGet);
        }