private IQueryable <Users> DistanceQuery(IQueryable <Users> query, float latitude, float longitude)
{
var R = 6371; //Result in kilometers
var PiOver180 = Math.PI / 180.0;
return
(query.Select
(
x => new
{
User = x,
Distance =
x.TheAddressesThatThisUserWorksOn
.Select(
y => new
{
Distance = SqlFunctions.SquareRoot(
SqlFunctions.Square(PiOver180 * (y.Longitude - longitude) *
SqlFunctions.Cos(PiOver180 * (y.Latitude + latitude) / 2.0)) +
SqlFunctions.Square(PiOver180 * (y.Latitude - latitude))
) * R
}
).OrderBy(y => y.Distance)
.FirstOrDefault()
.Distance
}
).OrderBy(x => x.Distance)
.ThenByDescending(x => (x.User.RatingCount != 0 ? (x.User.RatingSum / x.User.RatingCount) : 0))
.Select(x => x.User));
}
public List <Place> GetNearestPlaces(double latitude, double longitude, double radius = 10, int limit = 10)
{
MapsContext context = new MapsContext();
//var datas = context.Places.Select(x => Math.Acos(Math.Sin(deg2rad(lat1)) * Math.Sin(deg2rad(lat2))
// + Math.Cos(deg2rad(lat1)) * Math.Cos(deg2rad(lat2)) * Math.Cos(deg2rad(theta))));
//var datas = context.Places.Select(x => Math.Acos(Math.Sin(deg2rad(latitude)) * Math.Sin(deg2rad(x.Latitude))
// + Math.Cos(deg2rad(latitude)) * Math.Cos(deg2rad(x.Latitude)) * Math.Cos(deg2rad(x.Longitude-longitude))));
//var datas = context.Places.Where(x => Math.Acos(Math.Sin(latitude * Math.PI / 180.0) * Math.Sin(x.Latitude * Math.PI / 180.0)
// + Math.Cos(latitude * Math.PI / 180.0) * Math.Cos(x.Latitude * Math.PI / 180.0) * Math.Cos(x.Longitude - longitude * Math.PI / 180.0)) * 6371 <= radius);
//var datas = context.Places.Where(x => 3959 * Math.Acos(Math.Cos(radians(x.Latitude)) * Math.Cos(radians(latitude))
// * Math.Cos(radians(x.Longitude) - radians(longitude)) + Math.Sin(radians(latitude)) *
// Math.Sin(radians(x.Latitude))) * 1.60934 <= radius).ToList();
//var datas = context.Places.Where(x => Math.Sin(x.Latitude / 57.2957795130823) * Math.Sin(latitude / 57.2957795130823) + Math.Cos(x.Latitude / 57.2957795130823) * Math.Cos(latitude / 57.2957795130823) * Math.Cos(x.Latitude / 57.2957795130823 - latitude / 57.2957795130823) * 6371 <= radius);
//var tt = (3959*acos(
// cos(radians(@myLat))*cos(radians(Latitude))*cos(radians(Longitude) - radians(@myLong))
// + sin(radians(@myLat))*sin(radians(Latitude))))*1.60934 <= 10;
//var tt2 = select
//ACOS(
// SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0) +
// COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0 - PI()*@lon1/180.0)
//)*6371;
//var center = new GeoCoordinate(latitude, longitude);
//var result = context.Places.Select(x => new GeoCoordinate(x.Latitude, x.Longitude))
// .Where(x => x.GetDistanceTo(center) < radius);
//var datas = result.ToList();
//var datas = SELECT* FROM Places WHERE
// (Lat >= 1.2393 AND Lat <= 1.5532) AND(Lon >= -1.8184 AND Lon <= 0.4221)
// HAVING
// acos(sin(1.3963) * sin(Lat) + cos(1.3963) * cos(Lat) * cos(Lon - (-0.6981))) <= 0.1570;
var datas = context.Places.Where(x => SqlFunctions.Acos(SqlFunctions.Sin(latitude * SqlFunctions.Pi() / 180.0)
* SqlFunctions.Sin(x.Latitude * SqlFunctions.Pi() / 180.0)
+ SqlFunctions.Cos(latitude * SqlFunctions.Pi() / 180.0)
* SqlFunctions.Cos(x.Latitude * SqlFunctions.Pi() / 180.0)
* SqlFunctions.Cos((x.Longitude * SqlFunctions.Pi() / 180.0) - (longitude * SqlFunctions.Pi() / 180.0))) * 6371 <= radius)
.Take(limit)
.OrderBy(x => SqlFunctions.Acos(SqlFunctions.Sin(latitude * SqlFunctions.Pi() / 180.0)
* SqlFunctions.Sin(x.Latitude * SqlFunctions.Pi() / 180.0)
+ SqlFunctions.Cos(latitude * SqlFunctions.Pi() / 180.0)
* SqlFunctions.Cos(x.Latitude * SqlFunctions.Pi() / 180.0)
* SqlFunctions.Cos((x.Longitude * SqlFunctions.Pi() / 180.0) - (longitude * SqlFunctions.Pi() / 180.0))) * 6371);
var result = datas.ToList();
return(result);
}
public IEnumerable <Distance <Table> > GetNearbyTables(double Latitude, double Longitude, double Accuracy)
{
return(from t in db.Tables
let d = (SqlFunctions.Acos(SqlFunctions.Sin(Math.PI * t.Latitude / 180.0) * SqlFunctions.Sin(Math.PI * Latitude / 180.0) +
SqlFunctions.Cos(Math.PI * t.Latitude / 180.0) * SqlFunctions.Cos(Math.PI * Latitude / 180.0) *
SqlFunctions.Cos(Math.PI * t.Longitude / 180.0 - Math.PI * Longitude / 180.0)) * EARTH_RADIUS_MTR).Value
where d <= t.TableRadius + Accuracy * 2.5
orderby d descending
select new Distance <Table>()
{
Item = t,
DistanceInMeters = d
});
}
public IEnumerable <Distance <Msq> > GetNearbyMsqs(
double Latitude,
double Longitude,
double Accuracy,
double?Radius)
{
return(from m in db.Msqs.Include("User")
let d = (SqlFunctions.Acos(SqlFunctions.Sin(Math.PI * m.Latitude / 180.0) * SqlFunctions.Sin(Math.PI * Latitude / 180.0) +
SqlFunctions.Cos(Math.PI * m.Latitude / 180.0) * SqlFunctions.Cos(Math.PI * Latitude / 180.0) *
SqlFunctions.Cos(Math.PI * m.Longitude / 180.0 - Math.PI * Longitude / 180.0)) * EARTH_RADIUS_MTR).Value
where !Radius.HasValue || d < Radius.Value + Accuracy
orderby m.Entered descending
select new Distance <Msq>()
{
Item = m,
DistanceInMeters = d
});
}
public IEnumerable <Station> GetStationsWithinRadiusMiles(double lat, double lng, double radius)
{
var result = from outer in
(from s in db.Stations
select new
{
Distance = (3959 * SqlFunctions.Acos(SqlFunctions.Cos(SqlFunctions.Radians(lat)) *
SqlFunctions.Cos(SqlFunctions.Radians(s.Location.Latitude)) *
SqlFunctions.Cos(SqlFunctions.Radians(s.Location.Longitude) -
SqlFunctions.Radians(lng)) +
SqlFunctions.Sin(SqlFunctions.Radians(lat)) *
SqlFunctions.Sin((SqlFunctions.Radians(s.Location.Latitude))))),
s
})
where outer.Distance < radius
where outer.Distance != null
orderby outer.Distance
select outer.s;
return(result);
}
public static VagaRemocaoVM ToVM(this IQueryable <VagaRemocao> singleModel, FuncionarioOnline user)
{
return(singleModel.Select(a => new VagaRemocaoVM
{
CargoId = a.CargoId,
DificilAcesso = a.Unidade.DificilAcesso,
Endereco = a.Unidade.Endereco.EnderecoCompleto,
UnidadeId = a.UnidadeId,
Id = a.Id,
Unidade = a.Unidade.Nome,
Distancia =
(
SqlFunctions.Acos(
SqlFunctions.Sin(SqlFunctions.Radians(a.Unidade.Endereco.Latitude)) * SqlFunctions.Sin(SqlFunctions.Radians(user.Latitude))
+
SqlFunctions.Cos(SqlFunctions.Radians(a.Unidade.Endereco.Latitude)) * SqlFunctions.Cos(SqlFunctions.Radians(user.Latitude))
*
SqlFunctions.Cos(SqlFunctions.Radians(a.Unidade.Endereco.Longitude) - SqlFunctions.Radians(user.Longitude))
) * 6378
)
}).Single());
}
public IQueryable <Mechanic> GetMechanic(double lati, double longi)
{
var GlobalConstant1 = 57.2957795130823D;
var GlobalConstant2 = 3958.75586574D;
double latitude = lati;
double longitude = longi;
var loc = (from mechanic in db.Mechanics
let temp = SqlFunctions.Sin(mechanic.Latitude / GlobalConstant1)
* SqlFunctions.Sin(latitude / GlobalConstant1)
+ SqlFunctions.Cos(mechanic.Latitude / GlobalConstant1)
* SqlFunctions.Cos(latitude / GlobalConstant1)
* SqlFunctions.Cos(longitude / GlobalConstant1)
- (mechanic.Longitude / GlobalConstant1)
let calMiles = (GlobalConstant2 * SqlFunctions.Acos(temp > 1 ? 1 : (temp < -1 ? -1 : temp)))
where (mechanic.Latitude > 0 && mechanic.Longitude > 0)
orderby calMiles
select mechanic).Take(5);
return(loc);
}
public Station GetNearestStation(double lat, double lng)
{
var result = (from outer in
(from s in db.Stations
select new
{
Distance = (3959 * SqlFunctions.Acos(SqlFunctions.Cos(SqlFunctions.Radians(lat)) *
SqlFunctions.Cos(SqlFunctions.Radians(s.Location.Latitude)) *
SqlFunctions.Cos(SqlFunctions.Radians(s.Location.Longitude) -
SqlFunctions.Radians(lng)) +
SqlFunctions.Sin(SqlFunctions.Radians(lat)) *
SqlFunctions.Sin((SqlFunctions.Radians(s.Location.Latitude))))),
s
})
orderby outer.Distance
where outer.Distance != null
select outer.s);
var value = result.First();
return(value);
}
/// <summary>
/// 根据距离排序降序
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="queryable"></param>
/// <param name="lng">经度</param>
/// <param name="lat">纬度</param>
/// <returns></returns>
public static IQueryable <TEntity> OrderByDistanceDesc <TEntity>(this IQueryable <TEntity> queryable, double lng, double lat) where TEntity : class, IHasLngAndLat
{
var rtn = from q in queryable
let radLat1 = lat * Math.PI / 180.0
let radLat2 = q.WeiDu * Math.PI / 180.0
let a = radLat1 - radLat2
let b = lng * Math.PI / 180.0 - q.JingDu * Math.PI / 180.0
let s = 2 * SqlFunctions.Asin(SqlFunctions.SquareRoot(Math.Pow((double)SqlFunctions.Sin(a / 2), 2) +
SqlFunctions.Cos(radLat1) * SqlFunctions.Cos(radLat2) * Math.Pow((double)SqlFunctions.Sin(b / 2), 2))) * 6378.137
let d = Math.Round((double)s * 10000) / 10000
orderby d descending
select q;
return(rtn);
}
public IQueryable <Membre> RechercheMembreAvecProprietes(RechercheViewModel laRecherche, int noMembre)
{
#region trouverAgeEnChiffre
//DateTime? ageMax = null;
//DateTime? ageMin = null;
//if (laRecherche.ageMax != null)
//{
// ageMax = DateTime.Now.AddYears(-(int)laRecherche.ageMax);
//}
//if (laRecherche.ageMin != null)
//{
// ageMin = DateTime.Now.AddYears(-(int)laRecherche.ageMin);
//}
#endregion
IQueryable <Membre> membresTrouves = db.Membres;
Membre membreActuel;
try
{
membreActuel = db.Membres.Where(m => m.noMembre == noMembre).Include(m => m.listeNoire).FirstOrDefault();
}
catch (Exception e)
{
Dictionary <string, string> parametres = new Dictionary <string, string>()
{
{ "noMembre", noMembre.ToString() }
};
throw Utilitaires.templateException("(Non-Action) RechercheMembreAvecProprietes", "Membres", "Requête LINQ n'a pas fonctionnée ou la BD est inaccessible.", e, parametres);
}
//on doit avoir un membre, sinon on ne peut pas tester la distance en KM
if (membreActuel != null)
{
//Si on veut la recherche par KM
if (laRecherche.distanceKmDeMoi != null)
{
//La BD doit avoir été bien initialisée
if (membreActuel.ville.lat != null || membreActuel.ville.lng != null)
{
double latMembre = (double)membreActuel.ville.lat;
double lngMembre = (double)membreActuel.ville.lng;
//on définit la requête LINQ to SQL pour trouver les membres à une distance X
membresTrouves =
#region RequeteSQLCalculsDistance
(from m in db.Membres
let membreLatitude = (double)m.ville.lat
let membreLongitude = (double)m.ville.lng
let theta = ((lngMembre - membreLongitude) * Math.PI / 180.0)
let requestLat = (latMembre * Math.PI / 180.0)
let membreLat = (membreLatitude * Math.PI / 180.0)
let dist = (SqlFunctions.Sin(requestLat) * SqlFunctions.Sin(membreLat)) +
(SqlFunctions.Cos(requestLat) * SqlFunctions.Cos(membreLat) * SqlFunctions.Cos(theta))
let cosDist = SqlFunctions.Acos(dist)
let degDist = (cosDist / Math.PI * 180.0)
let absoluteDist = degDist * 60 * 1.1515
let distInKM = absoluteDist * 1.609344
where distInKM < laRecherche.distanceKmDeMoi
select m);
#endregion
}
}
//On ajoute la partie si le membre est connecté (on l'enlève des recherches pour pas qu'il se voit)
membresTrouves = membresTrouves
.Where(m => (m.noMembre != noMembre) &&
//BlackListe
(!m.listeMembresQuiMontBloque.Select(x => x.noMembre).Contains(membreActuel.noMembre)) &&
(!m.listeNoire.Select(x => x.noMembre).Contains(membreActuel.noMembre))
);
}
//Après avoir ajouté les wheres et calculs possibles, on ajoute la query commune et on retourne le tout!
//la recherche par age sera aussi implémenter de cette façon dans AdminController pour la liste des membres
if (laRecherche.ageMin != null)
{
membresTrouves = (from m in membresTrouves
let years = DateTime.Now.Year - ((DateTime)m.dateNaissance).Year
let birthdayThisYear = DbFunctions.AddYears(m.dateNaissance, years)
where laRecherche.ageMax >= (birthdayThisYear > DateTime.Now ? years - 1 : years)
select m
);
}
if (laRecherche.ageMax != null)
{
membresTrouves = (from m in membresTrouves
let years = DateTime.Now.Year - ((DateTime)m.dateNaissance).Year
let birthdayThisYear = DbFunctions.AddYears(m.dateNaissance, years)
where laRecherche.ageMin <= (birthdayThisYear > DateTime.Now ? years - 1 : years)
select m
);
}
membresTrouves = membresTrouves
#region RequeteLINQPourTousLesCriteres
.Where(m => //On s'assure que le membre n'est pas supprimé, car on n'en veut pas dans notre recherche.
(m.compteSupprimeParAdmin == null && m.dateSuppressionDuCompte == null) &&
//((ageMax == null ? true : false) || DbFunctions.TruncateTime(m.dateNaissance) >= DbFunctions.TruncateTime(ageMax)) && //Signe = ajouté 2017-11-19 Anthony Brochu
//((ageMin == null ? true : false) || DbFunctions.TruncateTime(m.dateNaissance) <= DbFunctions.TruncateTime(ageMin)) && //Signe = ajouté 2017-11-19 Anthony Brochu
((laRecherche.homme == null ? true : false) || m.homme == laRecherche.homme) &&
((laRecherche.noRaisonsSite == null ? true : false) || m.listeRaisonsSurSite.Select(r => r.noRaisonSurSite).Contains((laRecherche.noRaisonsSite ?? 0))) &&
((laRecherche.chercheHomme == null ? true : false) || m.rechercheHomme == laRecherche.chercheHomme) &&
((laRecherche.noProvince == null ? true : false) || m.noProvince == laRecherche.noProvince) &&
((laRecherche.noVille == null ? true : false) || m.noVille == laRecherche.noVille) &&
//Caractéristiques Physiques
((laRecherche.noYeuxCouleur == null ? true : false) || m.noYeuxCouleur == laRecherche.noYeuxCouleur) &&
((laRecherche.noCheveuxCouleur == null ? true : false) || m.noCheveuxCouleur == laRecherche.noCheveuxCouleur) &&
((laRecherche.noSilhouette == null ? true : false) || m.noSilhouette == laRecherche.noSilhouette) &&
((laRecherche.noTaille == null ? true : false) || m.noTaille == laRecherche.noTaille) &&
//Informations supplémentaires
((laRecherche.fumeur == null ? true : false) || m.fumeur == laRecherche.fumeur) &&
((laRecherche.noReligion == null ? true : false) || m.noReligion == laRecherche.noReligion) &&
((laRecherche.noOrigine == null ? true : false) || m.noOrigine == laRecherche.noOrigine) &&
((laRecherche.noDesirEnfant == null ? true : false) || m.noDesirEnfant == laRecherche.noDesirEnfant) &&
((laRecherche.noOccupation == null ? true : false) || m.noOccupation == laRecherche.noOccupation) &&
((laRecherche.noSituationFinanciere == null ? true : false) || m.noSituationFinanciere == laRecherche.noSituationFinanciere) &&
((laRecherche.noNiveauEtude == null ? true : false) || m.noNiveauEtude == laRecherche.noNiveauEtude) &&
((laRecherche.nbEnfantsMax == null ? true : false) || m.nbEnfants <= laRecherche.nbEnfantsMax) && //Signe "=" ajouté 2017-11-19 Anthony Brochu
((laRecherche.nbEnfantsMin == null ? true : false) || m.nbEnfants >= laRecherche.nbEnfantsMin) && //Signe "=" ajouté 2017-11-19 Anthony Brochu
((laRecherche.nbAnimauxMax == null ? true : false) || m.nbAnimaux <= laRecherche.nbAnimauxMax) && //Signe "=" ajouté 2017-11-19 Anthony Brochu
((laRecherche.nbAnimauxMin == null ? true : false) || m.nbAnimaux >= laRecherche.nbAnimauxMin) //Signe "=" ajouté 2017-11-19 Anthony Brochu
);
if (laRecherche.noTypeActiviteRecherche != null)
{
membresTrouves = membresTrouves
.Where(m => m.listeHobbies.Select(t => t.noType).Contains((int)laRecherche.noTypeActiviteRecherche));
}
if (laRecherche.noTypeInterets != null)
{
membresTrouves = membresTrouves
.Where(m => m.listeHobbies.Select(t => t.noHobbie).Contains((int)laRecherche.noTypeInterets));
}
#endregion
return(membresTrouves);
}
public ActionResult Search(string inputsearch, string f5, string f6, string f3, string f10, string f15, string f16, string f17, string f18, double?lon1, double?lat1, double?lon2, double?lat2, int?pg)
{
var _p = db.Products.Select(p => p);
int pageSize = 25;
if (pg == null)
{
pg = 1;
}
int pageNumber = (pg ?? 1);
ViewBag.pg = pg;
if (!string.IsNullOrWhiteSpace(inputsearch))
{
inputsearch = inputsearch.Trim();
var aa = inputsearch.Split(' ');
//sentences.Any(x => x.Split(new char[] { ' ' }).Contains(test));
//_p = _p.Where(o => o.F2.Contains(inputsearch) ||
// o.F2.ToLower().Contains(inputsearch) ||
// o.F2.ToLower().Contains(inputsearch.ToLower()) ||
// o.F2.Contains(inputsearch.ToLower()) || aa.Any(w => o.F2.Contains(w)) );
_p = _p.Where(o => aa.Any(w => o.F2.Contains(w)));
}
if (f18 == null)
{
f18 = "";
}
if (f17 == null)
{
f17 = "";
}
if (f15 == null)
{
f15 = "";
}
if (f16 == null)
{
f16 = "";
}
if (f3 == null)
{
f3 = "";
}
if (f5 == null)
{
f5 = "";
}
if (f6 == null)
{
f6 = "";
}
if (f10 == null)
{
f10 = "";
}
if (f18 != null && f18 != "")
{
int _id = Convert.ToInt32(f18);
_p = _p.Where(o => o.F18 == _id);
var _cat = db.Categories.Where(o => o.F1 == _id).FirstOrDefault();
ViewBag.f18n = _cat.F2;
}
if (f17 != null && f17 != "")
{
int _id = Convert.ToInt32(f17);
_p = _p.Where(o => o.F17 == _id);
var _cat = db.Categories.Where(o => o.F1 == _id).FirstOrDefault();
ViewBag.f17n = _cat.F2;
}
if (f15 != null && f15 != "")
{
int _id = Convert.ToInt32(f15);
_p = _p.Where(o => o.F15 == _id);
var _cat = db.Categories.Where(o => o.F1 == _id).FirstOrDefault();
ViewBag.f15n = _cat.F2;
}
if (f16 != null && f16 != "")
{
int _id = Convert.ToInt32(f16);
_p = _p.Where(o => o.F16 == _id);
var _local = db.Locals.Where(o => o.F1 == _id).FirstOrDefault();
ViewBag.f16n = _local.F2;
}
if (f5 != null && f5 != "")
{
_p = _p.Where(o => o.F5 == f5);
}
if (f6 != null && f6 != "")
{
_p = _p.Where(o => o.F6 == f6);
}
//_p = _p.Where((l => (Math.Acos(Math.Sin(Math.PI * lat1 / 180) * Math.Sin(Math.PI * lat1 / 180) + Math.Cos(Math.PI * lat1 / 180.0) * Math.Cos((double)(Math.PI * l.lat1 / 180)) * Math.Cos((double)(Math.PI * l.lon1 / 180 - Math.PI * lon1 / 180))) * 6371) < 100));// (l.lat - point.lat) * (l.lat - point.lat)) + ((l.lng - point.lng) * (l.lng - point.lng))<100
//_p = _p.Where((l => (Math.Acos(Math.Sin(Math.PI * lat2 / 180) * Math.Sin(Math.PI * lat2 / 180) + Math.Cos(Math.PI * lat2 / 180.0) * Math.Cos((double)(Math.PI * l.lat2 / 180)) * Math.Cos((double)(Math.PI * l.lon2 / 180 - Math.PI * lon2 / 180))) * 6371) < 100));
if (f10 != null && f10 == "")
{
_p = _p.OrderByDescending(o => o.F10);
}
else if (f10 != null && f10 == "desc")
{
_p = _p.OrderByDescending(o => o.F10);
}
else if (f10 != null && f10 == "asc")
{
_p = _p.OrderBy(o => o.F10);
}
if (lon1 != null)
{
var startPoint = new { Latitude = lat1, Longitude = lon1 };
_p = _p.OrderBy(x => 12742 * SqlFunctions.Asin(SqlFunctions.SquareRoot(SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lat1 - startPoint.Latitude)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lat1 - startPoint.Latitude)) / 2) +
SqlFunctions.Cos((SqlFunctions.Pi() / 180) * startPoint.Latitude) * SqlFunctions.Cos((SqlFunctions.Pi() / 180) * (x.lat1)) *
SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lon1 - startPoint.Longitude)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lon1 - startPoint.Longitude)) / 2))));
}
if (lon2 != null)
{
var startPoint2 = new { Latitude = lat2, Longitude = lon2 };
_p = _p.OrderBy(x => 12742 * SqlFunctions.Asin(SqlFunctions.SquareRoot(SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lat2 - startPoint2.Latitude)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lat2 - startPoint2.Latitude)) / 2) +
SqlFunctions.Cos((SqlFunctions.Pi() / 180) * startPoint2.Latitude) * SqlFunctions.Cos((SqlFunctions.Pi() / 180) * (x.lat2)) *
SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lon2 - startPoint2.Longitude)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.lon2 - startPoint2.Longitude)) / 2))));
}
if (f3 != null && f3 != "")
{
Configs.TwoNumber _x;
switch (f3)
{
case "desc":
_p = _p.OrderByDescending(x => x.F3);
break;
case "asc":
_p = _p.OrderBy(x => x.F3);
break;
default:
_x = Configs.GetNumber(f3);
_p = _p.Where(x => x.F3 <= _x.Number2 && x.F3 >= _x.Number1);
break;
}
}
var Cat = db.Categories.ToList();
ViewBag.Category = Cat;
var LocalData = db.Locals.ToList();
ViewBag.LocalData = LocalData;
ViewBag.ProductTheLoai = Configs.CreateListProductType();
ViewBag.ProductTrangThai = Configs.CreateListProductStatus();
ViewBag.ProductNgayDang = new List <sortOrder>()
{
new sortOrder()
{
TypeSort = "desc", NameSort = "Giảm dần"
},
new sortOrder()
{
TypeSort = "asc", NameSort = "Tăng dần"
}
};
List <sortOrder> ListGiaBan = new List <sortOrder>()
{
new sortOrder()
{
TypeSort = "desc", NameSort = "Giảm dần"
},
new sortOrder()
{
TypeSort = "asc", NameSort = "Tăng dần"
},
new sortOrder()
{
TypeSort = "0-1", NameSort = "Giá dưới 1 triệu"
}
};
List <sortOrder> myList = Configs.KhoangGia();
var _ConcatListGiaBan = ListGiaBan.Concat(myList).AsEnumerable();
ViewBag.ProductGiaBan = _ConcatListGiaBan;
ViewBag.inputsearch = inputsearch;
ViewBag.f18 = f18;
ViewBag.f17 = f17;
ViewBag.f15 = f15;
ViewBag.f16 = f16;
ViewBag.f3 = f3;
ViewBag.f10 = f10;
ViewBag.f5 = f5;
ViewBag.f6 = f6;
List <ProductShow> _lstProducts = new List <ProductShow>();
foreach (var p in _p)
{
var item = new ProductShow()
{
SanPhamId = p.F1,
TenSp = p.F2,
TinhTrangSp = p.F5,
TheLoai = p.F6,
GiaBan = p.F3,
NgayDang = p.F10,
TenNguoiBan = p.AspNetUser.TenNguoiBan,
SDTNguoiBan = p.AspNetUser.PhoneNumber,
AnhSanPham = p.F11 ?? "/Content/Images/no-image-available.png",
LocalId = p.F16,
SlugCat = Configs.unicodeToNoMark(p.F22),
SlugSubCat = Configs.unicodeToNoMark(p.F20),
slugTenSp = Configs.unicodeToNoMark(p.F2 != null ? p.F2 : "no-title"),
GianHang = p.AspNetUser.UserName,
SlugGianHang = Configs.unicodeToNoMark(p.AspNetUser.TenNguoiBan != null ? p.AspNetUser.TenNguoiBan : "no-title"),
SubCatId = p.F15,
CatId = p.F17,
ParentId = p.F18,
DiaDiem = p.F19 + p.F20,
DiemDi = p.F19,
DiemDen = p.F20
};
_lstProducts.Add(item);
}
return(View(_lstProducts.ToPagedList(pageNumber, pageSize)));
}
// GET: api/Messages/lan,lgt
public IEnumerable <Message> GetMessages(double lat, double lng)
{
double distance = 0.05;
var query = from it in db.Messages
let facilityLatitude = it.Lat
let facilityLongitude = it.Lng
let theta = ((lng - facilityLongitude) * Math.PI / 180.0)
let requestLat = (lat * Math.PI / 180.0)
let facilityLat = (facilityLatitude * Math.PI / 180.0)
let dist = (SqlFunctions.Sin(requestLat) * SqlFunctions.Sin(facilityLat)) + (SqlFunctions.Cos(requestLat) * SqlFunctions.Cos(facilityLat) * SqlFunctions.Cos(theta))
let cosDist = SqlFunctions.Acos(dist)
let degDist = (cosDist / Math.PI * 180.0)
let absoluteDist = degDist * 60 * 1.1515
let distInKM = absoluteDist * 1.609344
where distInKM < distance
select it;
return(query.OrderBy(t => t.LikesNum - t.DislikeNum));
}
public List <M_BoothInfo> GetBoothes(double Lat, double Lng)
{
var startPoint = new { Lat = Lat, Lng = Lng };
var closest = new DatabaseContext().BoothInfo.Where(x => (12742 * SqlFunctions.Asin(SqlFunctions.SquareRoot(SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lat - startPoint.Lat)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lat - startPoint.Lat)) / 2) +
SqlFunctions.Cos((SqlFunctions.Pi() / 180) * startPoint.Lat) * SqlFunctions.Cos((SqlFunctions.Pi() / 180) * (x.Lat)) *
SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lng - startPoint.Lng)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lng - startPoint.Lng)) / 2)))) <= 1.5)
.ToList();
return(closest);
}
public async void Match()
{
using (CarWatchDBEntities entities = new CarWatchDBEntities())
{
while (true)
{
List <Proposal> proposalList = entities.Proposals.ToList();
foreach (Proposal proposal in proposalList)
{
List <Search> searchList = await entities.Searches.Where(
e => 2 *k_EarthRadius *(SqlFunctions.SquareRoot(SqlFunctions.Square(SqlFunctions.Sin((SqlFunctions.Radians(proposal.Latitude) - SqlFunctions.Radians(e.Latitude)) / 2)) + SqlFunctions.Cos(SqlFunctions.Radians(proposal.Latitude)) * SqlFunctions.Cos(SqlFunctions.Radians(e.Latitude)) * SqlFunctions.Square(SqlFunctions.Sin((SqlFunctions.Radians(proposal.Longitude) - SqlFunctions.Radians(e.Longitude)) / 2)))) <= e.Distance).ToListAsync();
if (searchList.Count > 0)
{
List <ExtendedSearch> extendedSearchList = new List <ExtendedSearch>();
foreach (Search search in searchList)
{
FacebookAccount account = await entities.FacebookAccounts.FirstOrDefaultAsync(e => e.Nickname.CompareTo(search.Nickname) == 0);
extendedSearchList.Add(new ExtendedSearch(search, account));
}
extendedSearchList.Sort(new ExtendedSearchComparer());
Search searchToRemove = extendedSearchList[0].Search;
Exchange exchange = createExchangeObject(searchToRemove, proposal);
entities.Exchanges.Add(exchange);
entities.Searches.Remove(searchToRemove);
entities.Proposals.Remove(proposal);
FacebookAccount proposerAccount = await entities.FacebookAccounts.FirstOrDefaultAsync(e => e.Nickname.CompareTo(proposal.Nickname) == 0);
proposerAccount.Rank += 4;
await entities.SaveChangesAsync();
sendPushNotifications(searchToRemove, proposal);
alertOtherSearchers(extendedSearchList, proposal);
}
}
}
}
}
public List <M_Location> GetNearDrivers(double Lat, double Lng)
{
var startPoint = new { Lat = Lat, Lng = Lng };
var closest = new DatabaseContext().Location.Where(x => (12742 * SqlFunctions.Asin(SqlFunctions.SquareRoot(SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lat - startPoint.Lat)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lat - startPoint.Lat)) / 2) +
SqlFunctions.Cos((SqlFunctions.Pi() / 180) * startPoint.Lat) * SqlFunctions.Cos((SqlFunctions.Pi() / 180) * (x.Lat)) *
SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lng - startPoint.Lng)) / 2) * SqlFunctions.Sin(((SqlFunctions.Pi() / 180) * (x.Lng - startPoint.Lng)) / 2)))) <= 3).Where(x => x.IsOld != true)
.ToList();
return(closest);
}
public List <Restaurant> GetAllNearByRestaurants(string state, string city)
{
var coordinates = (from g in db.GeoDatas
where g.State == state && g.City == city
select g).FirstOrDefault();
var lat = (double)coordinates.Latitude;
var lon = (double)coordinates.Longitude;
var location = GeoLocation.FromDegrees(lat, lon);
GeoLocation[] boundingCoordinates = location.BoundingCoordinates(10);
var distance = location.DistanceTo(boundingCoordinates[1]);
var minlat = boundingCoordinates[0].getLatitudeInDegrees();
var maxlat = boundingCoordinates[1].getLatitudeInDegrees();
var minlon = boundingCoordinates[0].getLongitudeInDegrees();
var maxlon = boundingCoordinates[1].getLongitudeInDegrees();
var restaurants = (from a in db.Restaurants
where (a.Address.Latitude >= minlat && a.Address.Latitude <= maxlat) && (a.Address.Longitude >= minlon && a.Address.Longitude <= maxlon) &&
SqlFunctions.Acos(SqlFunctions.Sin(lat) * SqlFunctions.Sin((Double)a.Address.Latitude) + SqlFunctions.Cos(lat) * SqlFunctions.Cos((Double)a.Address.Latitude) * SqlFunctions.Cos((Double)a.Address.Longitude - (lon))) <= distance
select a).ToList();
return(restaurants);
}
