//public List<Parking> SelectParkings(MapDetailsModel d)
//{
// List<Parking> list = MainWindow.DatabaseViewModel.Parkings.Where(k =>
// k.laty >= d.bounds._southWest.lat &&
// k.laty <= d.bounds._northEast.lat &&
// k.lonx >= d.bounds._southWest.lng &&
// k.lonx <= d.bounds._northEast.lng
// ).ToList();
// return list;
// //var db = Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "Data", Settings.Default.Database);
// //var cs = $"Data Source = {db}; Cache = Shared";
// //using (var conn = new SQLiteConnection(cs))
// //{
// // conn.Open();
// // DataTable dt = new DataTable();
// // SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM PARKING WHERE laty >= @LAT_MIN and laty <= @LAT_MAX and lonx >= @LNG_MIN and lonx <= @LNG_MAX ", conn);
// // //SQLiteCommand cmd = new SQLiteCommand("select * from parking", conn);
// // cmd.Parameters.AddWithValue("@LAT_MIN", d.bounds._southWest.lat);
// // cmd.Parameters.AddWithValue("@LAT_MAX", d.bounds._northEast.lat);
// // cmd.Parameters.AddWithValue("@LNG_MIN", d.bounds._southWest.lng);
// // cmd.Parameters.AddWithValue("@LNG_MAX", d.bounds._northEast.lng);
// // SQLiteDataReader reader = cmd.ExecuteReader();
// // dt.Load(reader);
// // return dt;
// //}
// //return null;
//}
//public DataTable SelectParkings2(MapDetailsModel d)
//{
// var db = Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "Data", Settings.Default.Database);
// var cs = $"Data Source = {db}; Cache = Shared";
// using (var conn = new SQLiteConnection(cs))
// {
// conn.Open();
// DataTable dt = new DataTable();
// SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM PARKING WHERE laty >= @LAT_MIN and laty <= @LAT_MAX and lonx >= @LNG_MIN and lonx <= @LNG_MAX ", conn);
// //SQLiteCommand cmd = new SQLiteCommand("select * from parking", conn);
// cmd.Parameters.AddWithValue("@LAT_MIN", d.bounds._southWest.lat);
// cmd.Parameters.AddWithValue("@LAT_MAX", d.bounds._northEast.lat);
// cmd.Parameters.AddWithValue("@LNG_MIN", d.bounds._southWest.lng);
// cmd.Parameters.AddWithValue("@LNG_MAX", d.bounds._northEast.lng);
// SQLiteDataReader reader = cmd.ExecuteReader();
// dt.Load(reader);
// return dt;
// }
// return null;
//}
//public List<AirPort> SelectAirPorts(MapDetailsModel d)
//{
// return MainWindow.DatabaseViewModel.Airports.Where(k =>
// //k.ident == "LHBP" &&
// k.rating >= d.rating &&
// k.laty >= d.bounds._southWest.lat &&
// k.laty <= d.bounds._northEast.lat &&
// k.lonx >= d.bounds._southWest.lng &&
// k.lonx <= d.bounds._northEast.lng
// ).ToList();
//}
public DataTable SelectRunways2(MapDetailsModel d)
{
var db = Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "Data", Settings.Default.Database);
var cs = $"Data Source = {db}; Cache = Shared";
using (var conn = new SQLiteConnection(cs))
{
conn.Open();
DataTable dt = new DataTable();
SQLiteCommand cmd = new SQLiteCommand(@"
SELECT
A.IDENT, A.NAME, A.CITY,A.FUEL_FLAGS,A.TOWER_FREQUENCY, A.ATIS_FREQUENCY,
R.RUNWAY_ID,R.HEADING,R.LONX, R.LATY, R.ALTITUDE
FROM AIRPORT A
LEFT JOIN RUNWAY R ON A.AIRPORT_ID = R.AIRPORT_ID
WHERE A.LATY >= @LAT_MIN AND A.LATY <= @LAT_MAX AND A.LONX >= @LNG_MIN AND A.LONX <= @LNG_MAX AND A.RATING >= @RATING
LIMIT 1000"
, conn);
//SQLiteCommand cmd = new SQLiteCommand("select * from parking", conn);
cmd.Parameters.AddWithValue("@LAT_MIN", d.bounds._southWest.lat);
cmd.Parameters.AddWithValue("@LAT_MAX", d.bounds._northEast.lat);
cmd.Parameters.AddWithValue("@LNG_MIN", d.bounds._southWest.lng);
cmd.Parameters.AddWithValue("@LNG_MAX", d.bounds._northEast.lng);
cmd.Parameters.AddWithValue("@RATING", d.rating);
SQLiteDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
return(dt);
}
return(null);
}
public List <VOR> SelectVORs(MapDetailsModel d)
{
return(this.VORs.Where(k =>
k.laty >= d.bounds._southWest.lat &&
k.laty <= d.bounds._northEast.lat &&
k.lonx >= d.bounds._southWest.lng &&
k.lonx <= d.bounds._northEast.lng
).ToList());
}
public List <AirPort> SelectAirPorts(MapDetailsModel d)
{
return(this.Airports.Where(k =>
k.rating >= d.rating &&
k.laty >= d.bounds._southWest.lat &&
k.laty <= d.bounds._northEast.lat &&
k.lonx >= d.bounds._southWest.lng &&
k.lonx <= d.bounds._northEast.lng
).ToList());
}
public DataTable SelectParkings(MapDetailsModel d)
{
var db = Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "Data", Settings.Default.Database);
var cs = $"Data Source = {db}; Cache = Shared";
using (var conn = new SQLiteConnection(cs))
{
conn.Open();
DataTable dt = new DataTable();
SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM PARKING WHERE laty >= @LAT_MIN and laty <= @LAT_MAX and lonx >= @LNG_MIN and lonx <= @LNG_MAX ", conn);
//SQLiteCommand cmd = new SQLiteCommand("select * from parking", conn);
cmd.Parameters.AddWithValue("@LAT_MIN", d.bounds._southWest.lat);
cmd.Parameters.AddWithValue("@LAT_MAX", d.bounds._northEast.lat);
cmd.Parameters.AddWithValue("@LNG_MIN", d.bounds._southWest.lng);
cmd.Parameters.AddWithValue("@LNG_MAX", d.bounds._northEast.lng);
SQLiteDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
return(dt);
}
return(null);
}
protected override void OnMessage(MessageEventArgs e)
{
string res = "";
if (this.MainWindow != null && this.MainWindow.SimConnectViewModel != null && this.MainWindow.FlightPlanViewModel != null)
{
//if (Globals.MainWindow.FlightPlanViewModel.LoadDt != this.LastFlightPlanDt)
//{
// this.Send(Globals.MainWindow.FlightPlanViewModel.FlightPlanJSON);
// LastFlightPlanDt = Globals.MainWindow.FlightPlanViewModel.LoadDt;
//}
if (e.Data == "getposition")
{
var lat = this.MainWindow.SimConnectViewModel.VarList.Where(k => k.VarName == "PLANE LATITUDE").FirstOrDefault();
var lng = this.MainWindow.SimConnectViewModel.VarList.Where(k => k.VarName == "PLANE LONGITUDE").FirstOrDefault();
var hdg = this.MainWindow.SimConnectViewModel.VarList.Where(k => k.VarName == "PLANE HEADING DEGREES TRUE").FirstOrDefault();
if (lat != null && lng != null && hdg != null)
{
res = JSon.ToString(res, "PLANE_LATITUDE", lat.Value != null ? (double)lat.Value : 47.434387018741525);
res = JSon.ToString(res, "PLANE_LONGITUDE", lng.Value != null ? (double)lng.Value : 19.264183044433597);
res = JSon.ToString(res, "PLANE_HEADING_DEGREES_TRUE", hdg.Value != null ? (double)hdg.Value : 0);
this.Send("{\"type\":\"position\", \"data\": {" + res + "}}");
}
else
{
this.Send("{\"type\":\"status\", \"data\": \"No valid position!\"}");
}
return;
}
else if (this.DealWithDataBase)
{
if (e.Data.StartsWith("{\"cmd\":"))
{
QueryModel q = JsonConvert.DeserializeObject <QueryModel>(e.Data);
if (q.cmd == "getairports")
{
MapDetailsModel d = JsonConvert.DeserializeObject <MapDetailsModel>(q.data.ToString());
AirPortsInfo ai = new AirPortsInfo();
ai.AirPorts = Globals.DatabaseViewModel.SelectAirPorts(d);
res = JsonConvert.SerializeObject(ai);
this.Send(res);
return;
}
else if (q.cmd == "getparkings")
{
MapDetailsModel d = JsonConvert.DeserializeObject <MapDetailsModel>(q.data.ToString());
ResponseParkings rap = new ResponseParkings();
rap.type = "parkings";
rap.parkings = Globals.DatabaseViewModel.SelectParkings(d);
res = JsonConvert.SerializeObject(rap);
this.Send(res);
return;
}
else if (q.cmd == "getvors")
{
MapDetailsModel d = JsonConvert.DeserializeObject <MapDetailsModel>(q.data.ToString());
ResponseVORs vrs = new ResponseVORs();
vrs.vors = Globals.DatabaseViewModel.SelectVORs(d);
res = JsonConvert.SerializeObject(vrs);
this.Send(res);
return;
}
else if (q.cmd == "getairportbyname")
{
AirPortInfo ai = new AirPortInfo();
ai.AirPort = Globals.DatabaseViewModel.SelectAirPorts(q.data.ToString());
res = JsonConvert.SerializeObject(ai);
this.Send(res);
return;
}
else if (q.cmd == "getvorbyid")
{
VORInfo v = new VORInfo();
v.VOR = Globals.DatabaseViewModel.SelectVOR((Int64)q.data);
res = JsonConvert.SerializeObject(v);
this.Send(res);
return;
}
this.Send("{\"type\":\"status\", \"data\": \"Unknown cmd\"}");
}
else
{
this.Send("{\"type\":\"status\", \"data\": \"Unknown query\"}");
}
}
else
{
this.Send("{\"type\":\"status\", \"data\": \"No database\"}");
}
}
else
{
this.Send("{\"type\":\"status\", \"data\": \"Not Connected\"}");
}
}
