public TypeCuisine GetCuisineFirst(int id)
{
using (var db = new RestaurantDbContext())
{
var lbCuisine = (from cuisine in db.db_typecuisine
where cuisine.Id_Cuisine==id
select cuisine).FirstOrDefault();
return lbCuisine;
}
}
public List<TypeCuisine> GetAllCuisine()
{
using (var db = new RestaurantDbContext())
{
var lbCuisine = from cuisine in db.db_typecuisine
orderby cuisine.lb_cuisne ascending
select cuisine;
return lbCuisine.ToList();
}
}
public static List<string> GetAllVille()
{
using(var db = new RestaurantDbContext())
{
var querry_ville = from ville in db.db_ville
orderby ville.lb_ville ascending
select ville.lb_ville;
return querry_ville.ToList();
}
}
public Ville GetVilleFirst(string search)
{
using (var db = new RestaurantDbContext())
{
var lbVille = (from ville in db.db_ville
where ville.lb_ville.ToLower().Trim().Contains(search.ToLower().Trim())
select ville).FirstOrDefault();
return lbVille;
}
}
public int getNbDelicieux(int id)
{
using (var db = new RestaurantDbContext())
{
int nbDelicieux = (from restaurant in db.db_restaurants
from note in restaurant.Notations
where restaurant.Id_Restaurant == id && note.Note==5
select note).Count();
return nbDelicieux;
}
}
public int getNbMauvais(int id)
{
using (var db = new RestaurantDbContext())
{
int nbMauvais = (from restaurant in db.db_restaurants
from note in db.db_notation
where restaurant.Id_Restaurant == id && note.Note == 1
select note).Count();
return nbMauvais;
}
}
public int getNbAcceptable(int id)
{
using (var db = new RestaurantDbContext())
{
int nbAcceptable = (from restaurant in db.db_restaurants
from note in restaurant.Notations
where restaurant.Id_Restaurant == id && note.Note == 3
select note).Count();
return nbAcceptable;
}
}
public List<ViewModelVille> GetVilleByValue(string search)
{
using (var db = new RestaurantDbContext())
{
var lbVille = from ville in db.db_ville
where ville.lb_ville.ToLower().Trim().Contains(search.ToLower().Trim())
select new ViewModelVille()
{
Id = ville.Id_Ville,
Ville = ville.lb_ville,
};
return lbVille.ToList();
}
}
public Dictionary<int, string> GetBigVille(string villeBig)
{
using(var db = new RestaurantDbContext())
{
var s_ville1 = from ville in db.db_ville
where ville.lb_ville.Equals(villeBig)
select new
{
id = ville.Id_Ville,
s_ville = ville.lb_ville
};
return s_ville1.ToDictionary(o=>o.id,o=>o.s_ville);
}
}
public ViewModelDetailRestaurants DetailsRestaurantBySearch(string s_nom, string s_rue, string s_ville_cp)
{
using (var db = new RestaurantDbContext())
{
var details = from restaurant in db.db_restaurants
from cuisine in restaurant.TypeCuisines
from note in restaurant.Notations
join addresse in db.db_addresse on restaurant.Id_Restaurant equals addresse.RestaurantsId
join ville in db.db_ville on addresse.VilleId equals ville.Id_Ville
join picture in db.db_Picture on restaurant.Id_Restaurant equals picture.fk_Restaurant
let ville_cp = addresse.lb_codepostal.Trim().ToLower() + " " + ville.lb_ville.Trim().ToLower()
where restaurant.isActive && picture.active && restaurant.lb_nom.Trim().ToLower().Equals(s_nom.Trim().ToLower()) //|| picture.active
&& addresse.lb_rue.Trim().ToLower().Equals(s_rue.Trim().ToLower())
&& ville_cp.Equals(s_ville_cp.Trim().ToLower())
select new
{
Id = restaurant.Id_Restaurant,
Nom = restaurant.lb_nom,
Notation = note.Note,
Adresse = addresse.lb_rue,
CP = ville_cp,
Ville = ville.lb_ville,
Cuisine = cuisine.lb_cuisne,
Phone = restaurant.lb_tel,
WebSite = restaurant.lb_web,
Description = restaurant.lb_description,
BytePicture = picture.lb_Picure,
NamePicture = picture.lb_Name,
Role = picture.role
};
var group_details = from gpRestaurant in details
group gpRestaurant by gpRestaurant.Id into gp
select new ViewModelDetailRestaurants()
{
Id = gp.Key,
Nom = gp.FirstOrDefault().Nom,
Ville = gp.FirstOrDefault().Ville,
Notation = gp.Sum(s => s.Notation)/gp.Count(),
Adresse = gp.FirstOrDefault().Adresse,
CP = gp.FirstOrDefault().CP,
Cuisine = gp.Select(s => s.Cuisine).ToList(),
Phone = gp.FirstOrDefault().Phone,
WebSite = gp.FirstOrDefault().WebSite,
Description = gp.FirstOrDefault().Description,
BytePicture = gp.Select(s => s.BytePicture).ToList(),
NamePicture = gp.Select(s => s.NamePicture).ToList(),
Roles = gp.Select(s => s.Role).ToList(),
};
return group_details.FirstOrDefault();
}
}
public ActionResult Proposer(ViewModelAddRestaurant model, bool EmptyVille)
{
LinqCuisine linqCuisine = new LinqCuisine();
if (ModelState.IsValid)
{
Restaurants view_newRestaurant = model.m_restaurant;
Ville view_ville = model.m_ville;
Adresse view_addresse = model.m_adresse;
Notation view_notation = model.m_notation;
view_newRestaurant.lb_nom = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(view_newRestaurant.lb_nom);
LinqRestaurant linqRestautant = new LinqRestaurant();
int result = linqRestautant.ExistRestaurant(view_newRestaurant.lb_nom, view_addresse.lb_rue, view_addresse.lb_codepostal, view_ville.lb_ville);
if (result>0)
return RedirectToAction("Index", "Home");
LinqVille linqVille = new LinqVille();
var existingCity = linqVille.GetVilleFirst(view_ville.lb_ville.ToLower());
using(var db = new RestaurantDbContext())
{
if ((EmptyVille) && existingCity == null)
{
view_ville.lb_ville = char.ToUpper(view_ville.lb_ville[0]) + view_ville.lb_ville.Substring(1);
view_addresse.lb_rue = view_addresse.lb_rue.Replace(",", "");
db.db_ville.Add(view_ville);
db.db_addresse.Add(view_addresse);
}
else
{
view_addresse.lb_rue = view_addresse.lb_rue.Replace(",", "");
db.db_ville.Attach(existingCity);
existingCity.Adresses.Add(view_addresse);
}
foreach (string typeCuisine in model.SelectedItemsCuisine)
{
TypeCuisine existingCuisine = linqCuisine.GetCuisineFirst(int.Parse(typeCuisine));
db.db_typecuisine.Attach(existingCuisine);
existingCuisine.Restaurants.Add(view_newRestaurant);
}
view_newRestaurant.Notations.Add(view_notation);
db.SaveChanges();
}
return RedirectToAction("Index", "Home");
}
model.ItemsCuisine = linqCuisine.GetAllCuisine();
return View(model);
}
public List<ViewModelSearch> Search(string search)
{
using (var db = new RestaurantDbContext())
{
var result = from restaurant in db.db_restaurants
join addresse in db.db_addresse on restaurant.Id_Restaurant equals addresse.RestaurantsId
join ville in db.db_ville on addresse.VilleId equals ville.Id_Ville
where restaurant.isActive && (restaurant.lb_nom.ToLower().Trim().Contains(search.ToLower().Trim())
|| addresse.lb_rue.ToLower().Trim().Equals(search.ToLower().Trim())
|| addresse.lb_codepostal.ToLower().Trim().Equals(search.ToLower().Trim())
|| ville.lb_ville.ToLower().Trim().Equals(search.ToLower().Trim()))
select new ViewModelSearch()
{
Id = restaurant.Id_Restaurant,
Nom = restaurant.lb_nom,
Adresse = addresse.lb_rue + ", " + addresse.lb_codepostal + " " + ville.lb_ville,
};
return result.ToList();
}
}
public List<ViewModelListerRestaurants> ListerRestaurants(string search)
{
using (var db = new RestaurantDbContext())
{
var result = (from restaurant in db.db_restaurants
from cuisine in restaurant.TypeCuisines
from note in restaurant.Notations
join addresse in db.db_addresse on restaurant.Id_Restaurant equals addresse.RestaurantsId
join ville in db.db_ville on addresse.VilleId equals ville.Id_Ville
join picture in db.db_Picture on restaurant.Id_Restaurant equals picture.fk_Restaurant
where restaurant.isActive && picture.active && picture.role.Equals("header") && (restaurant.lb_nom.ToLower().Trim().Equals(search.ToLower().Trim())
|| addresse.lb_rue.ToLower().Trim().Equals(search.ToLower().Trim())
|| addresse.lb_codepostal.ToLower().Trim().Equals(search.ToLower().Trim())
|| ville.lb_ville.ToLower().Trim().Equals(search.ToLower().Trim()))
|| cuisine.lb_cuisne.ToLower().Trim().Equals(search.ToLower().Trim())
select new ViewModelListerRestaurants()
{
Id = restaurant.Id_Restaurant,
Nom = restaurant.lb_nom,
Ville = ville.lb_ville,
IdCuisine = cuisine.Id_Cuisine,
StrCuisine = cuisine.lb_cuisne,
Notation = note.Note,
BytePicture = picture.lb_Picure,
NamePicture = picture.lb_Name,
}).Distinct();
return result.ToList();
}
}
public List<ViewModelListerRestaurants> ListerRestaurantByCuisine(int id)
{
using (var db =new RestaurantDbContext())
{
var result = from restaurant in db.db_restaurants
from cuisine in restaurant.TypeCuisines
from note in restaurant.Notations
join addresse in db.db_addresse on restaurant.Id_Restaurant equals addresse.RestaurantsId
//join picture in db.db_Picture on restaurant.Id_Restaurant equals picture.fk_Restaurant
join ville in db.db_ville on addresse.VilleId equals ville.Id_Ville
where restaurant.isActive && cuisine.Id_Cuisine == id //|| picture.active
select new ViewModelListerRestaurants()
{
Id = restaurant.Id_Restaurant,
Nom = restaurant.lb_nom,
Ville = ville.lb_ville,
IdCuisine = cuisine.Id_Cuisine,
StrCuisine = cuisine.lb_cuisne,
Notation = note.Note
};
return result.ToList();
}
}
public int ExistRestaurant(string str_nom, string str_rue, string str_cp, string str_ville)
{
using (var db = new RestaurantDbContext())
{
var existingRestaurant = from restaurant in db.db_restaurants
join addr in db.db_addresse on restaurant.Id_Restaurant equals addr.RestaurantsId
join ville in db.db_ville on addr.VilleId equals ville.Id_Ville
where restaurant.lb_nom.ToLower().Trim().Equals(str_nom.ToLower().Trim())
&& addr.lb_rue.ToLower().Trim().Equals(str_rue.ToLower().Trim())
&& addr.lb_codepostal.ToLower().Trim().Equals(str_cp.ToLower().Trim())
&& ville.lb_ville.ToLower().Trim().Equals(str_ville.ToLower().Trim())
select restaurant;
return existingRestaurant.Count();
}
}
public ViewModelDetailRestaurants DetailsRestaurantByList(int? id)
{
using(var db = new RestaurantDbContext())
{
#region
//var details = from restaurant in db.db_restaurants
// from cuisine in restaurant.TypeCuisines
// from note in restaurant.Notations
// join addresse in db.db_addresse on restaurant.Id_Restaurant equals addresse.RestaurantsId
// join ville in db.db_ville on addresse.VilleId equals ville.Id_Ville
// where restaurant.Id_Restaurant == id
// select new ViewModelDetailRestaurants()
// {
// Id = restaurant.Id_Restaurant,
// Nom = restaurant.lb_nom,
// Notation = note.Note,
// Adresse = addresse.lb_rue,
// CP = addresse.lb_codepostal,
// Ville = ville.lb_ville,
// Cuisine = cuisine.lb_cuisne,
// Phone=restaurant.lb_tel,
// WebSite=restaurant.lb_web,
// };
#endregion
var details = from restaurant in db.db_restaurants
from cuisine in restaurant.TypeCuisines
from note in restaurant.Notations
join addresse in db.db_addresse on restaurant.Id_Restaurant equals addresse.RestaurantsId
join ville in db.db_ville on addresse.VilleId equals ville.Id_Ville
join picture in db.db_Picture on restaurant.Id_Restaurant equals picture.fk_Restaurant
where restaurant.Id_Restaurant == id && picture.active
select new
{
Id = restaurant.Id_Restaurant,
Nom = restaurant.lb_nom,
Notation = note.Note,
Adresse = addresse.lb_rue,
CP = addresse.lb_codepostal,
Ville = ville.lb_ville,
Cuisine = cuisine.lb_cuisne,
Phone = restaurant.lb_tel,
WebSite = restaurant.lb_web,
Description = restaurant.lb_description,
BytePicture = picture.lb_Picure,
NamePicture = picture.lb_Name,
Role = picture.role
};
var group_details = from gpRestaurant in details
group gpRestaurant by gpRestaurant.Id into gp
select new ViewModelDetailRestaurants()
{
Id = gp.Key,
Nom = gp.FirstOrDefault().Nom,
Ville = gp.FirstOrDefault().Ville,
Notation = gp.Sum(s => s.Notation)/gp.Count(),
Adresse = gp.FirstOrDefault().Adresse,
CP = gp.FirstOrDefault().CP,
Cuisine = gp.Select(s => s.Cuisine).ToList(),
Phone = gp.FirstOrDefault().Phone,
WebSite = gp.FirstOrDefault().WebSite,
Description = gp.FirstOrDefault().Description,
BytePicture = gp.Select(s => s.BytePicture).ToList(),
NamePicture = gp.Select(s => s.NamePicture).ToList(),
Roles = gp.Select(s => s.Role).ToList(),
};
return group_details.FirstOrDefault();
}
}