public ActionResult Trier(string tri, string ordre, int pageActuel, string titre, string isbn, string auteur, string sigle, int typeArticle)
{
string recherche = construireRecherche(titre, isbn, auteur, sigle);
var totalRows = OffreBean.GetTotalRows(recherche);
var count = totalRows / 8.0;
var pageCount = (int)Math.Ceiling((decimal)count);
var newPageActuel = GererPage("", pageActuel, pageCount);
int start = ((newPageActuel - 1) * 8) + 1;
int last = start + 7;
ViewBag.PageActuel = newPageActuel;
ViewBag.ReqRech = recherche;
if (typeArticle == 2)
{
tri = tri.Replace("l.", "n.");
return(View("Index", ObtenirListeNotesDeCours(start, last, recherche, tri, ordre)));
}
else if (typeArticle == 3)
{
tri = tri.Replace("l.Nom", "c.Modele");
return(View("Index", ObtenirListeCalculatrice(start, last, recherche, tri, ordre)));
}
else
{
return(View("Index", ObtenirListeLivres(start, last, recherche, tri, ordre)));
}
}
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));
}
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 ActionResult Rechercher(string titre, string isbn, string auteur, string sigle, int pageActuel, string tri, string ordre)
{
string recherche = construireRecherche(titre, isbn, auteur, sigle);
var totalRows = OffreBean.GetTotalRows(recherche);
var count = totalRows / 8.0;
var pageCount = (int)Math.Ceiling((decimal)count);
var newPageActuel = GererPage("", pageActuel, pageCount);
int start = ((newPageActuel - 1) * 8) + 1;
int last = start + 7;
ViewBag.PageActuel = 1;
ViewBag.ReqRech = recherche;
return(View("Index", ObtenirListeLivres(start, last, recherche, tri, ordre)));
}
public List <OffreBean> ObtenirListeOffresUtilNotesCours()
{
List <OffreBean> offres = null;
SqlConnection cnn = null;
var connetionString = Properties.Settings.Default.dbConnectionString;
var sql = "SELECT o.Id, n.Nom " +
"FROM Offre o " +
"JOIN NotesDeCours n On o.IdArticle = n.IdNotesDeCours " +
"Where o.IdTypeArticle = 2 AND 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.IdArticle = l.Id " +
"JOIN Etat e ON o.Etat = e.CodeEtat " +
"Where o.IndActif='1' AND o.IdTypeArticle = 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;
ViewBag.typeArticle = 1;
}
return(offres);
}
