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);
}