public static DistanceClass GetDistanceClass(EDDiscovery2.DB.ISystem s1, EDDiscovery2.DB.ISystem s2)
{
if (s1 == null || s2 == null)
{
return(null);
}
try
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM Distances WHERE (NameA = @NameA and NameB = @NameB) OR (NameA = @NameB and NameB = @NameA) limit 1"))
{
cmd.AddParameterWithValue("@NameA", s1.name);
cmd.AddParameterWithValue("@NameB", s2.name);
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) // if found.
{
DistanceClass dist = new DistanceClass(ds.Tables[0].Rows[0]);
return(dist);
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
}
return(null);
}
public static List <DistanceClass> GetDistancesByStatus(int status)
{
List <DistanceClass> ldist = new List <DistanceClass>();
try
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("select * from Distances WHERE status='" + status.ToString() + "'"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DistanceClass dist = new DistanceClass(dr);
ldist.Add(dist);
}
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
}
return(ldist);
}
/*
* {
* "date": "2015-08-31 07:30:58",
* "submitted_by": [
* {
* "cmdr": "Mixolydian",
* "date": "2015-04-12 17:11:39"
* }
* ],
* "sys1": {
* "name": "Col 359 Sector ZJ-Z d86",
* "date": "2015-05-12 15:29:33"
* },
* "sys2": {
* "name": "KOI 1925",
* "date": "2015-08-31 07:31:01"
* },
* "distance": 34.86
* }
*
*/
public static DistanceClass ParseEDSM(JObject jo, string date)
{
DistanceClass dist = new DistanceClass();
JObject sys1, sys2;
sys1 = (JObject)jo["sys1"];
sys2 = (JObject)jo["sys2"];
JArray submitted_by = (JArray)jo["submitted_by"];
dist.NameA = sys1["name"].Value <string>();
dist.NameB = sys2["name"].Value <string>();
dist.Dist = jo["distance"].Value <float>();
if (submitted_by.Count > 0)
{
dist.CommanderCreate = submitted_by[0]["cmdr"].Value <string>();
}
else
{
dist.CommanderCreate = "";
}
dist.CreateTime = jo["date"].Value <DateTime>();
dist.Status = DistancsEnum.EDSC;
return(dist);
}
public bool GetAllDistances(bool loadAlldata)
{
try
{
using (SQLiteConnection cn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
DataSet ds = null;
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
if (!loadAlldata)
{
cmd.CommandText = "select * from Distances WHERE status='3' or status = '4'";// EDDiscovery = 3, EDDiscoverySubmitted = 4
}
else
{
cmd.CommandText = "select * from Distances";
}
ds = SqlQueryText(cn, cmd);
if (ds.Tables.Count == 0)
{
return(false);
}
//
if (ds.Tables[0].Rows.Count == 0)
{
return(false);
}
dictDistances.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
DistanceClass distance = new DistanceClass(dr);
AddDistanceToCache(distance);
}
return(true);
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
return(false);
}
}
public List <DistanceClass> GetDistancesByStatus(int status)
{
List <DistanceClass> ldist = new List <DistanceClass>();
try
{
using (SQLiteConnection cn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
DataSet ds = null;
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
cmd.CommandText = "select * from Distances WHERE status='" + status.ToString() + "'";
ds = SqlQueryText(cn, cmd);
if (ds.Tables.Count == 0)
{
return(ldist);
}
//
if (ds.Tables[0].Rows.Count == 0)
{
return(ldist);
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
DistanceClass dist = new DistanceClass(dr);
ldist.Add(dist);
}
return(ldist);
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
return(ldist);
}
}
public static void FillVisitedSystems(List <VisitedSystemsClass> visitedSystems, bool usedb)
{
try
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
DbCommand cmd = cn.CreateCommand("SELECT * FROM Distances WHERE(NameA = @NameA and NameB = @NameB) OR(NameA = @NameB and NameB = @NameA) limit 1");
for (int i = 1; i < visitedSystems.Count; i++) // now we filled in current system, fill in previous system (except for last)
{
VisitedSystemsClass cur = visitedSystems[i];
VisitedSystemsClass prev = visitedSystems[i - 1];
cur.prevSystem = prev.curSystem;
double dist = SystemClass.Distance(cur.curSystem, prev.curSystem); // Try the easy way
if (dist < 0 && usedb) // failed, and use the db is allowed..
{
cmd.Parameters.Clear();
cmd.AddParameterWithValue("@NameA", cur.Name);
cmd.AddParameterWithValue("@NameB", prev.Name);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
DistanceClass dst = new DistanceClass(reader);
dist = dst.Dist;
}
}
}
if (dist > 0)
{
cur.strDistance = dist.ToString("0.00");
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
}
}
public static List <DistanceClass> ParseEDSM(string json, ref string date)
{
List <DistanceClass> listDistances;
JArray edsm = null;
if (json != null)
{
edsm = (JArray)JArray.Parse(json);
}
listDistances = new List <DistanceClass>();
DateTime maxdate = DateTime.Parse(date, new CultureInfo("sv-SE"));
if (edsm == null)
{
return(listDistances);
}
if (edsm != null)
{
foreach (JObject jo in edsm)
{
DistanceClass dist = ParseEDSM(jo, date);
if (dist.NameA != null && dist.NameB != null)
{
listDistances.Add(dist);
if (dist.CreateTime.Subtract(maxdate).TotalSeconds > 0)
{
maxdate = dist.CreateTime;
}
}
}
}
// date = edscdata["date"].Value<string>();
date = maxdate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
edsm = null;
return(listDistances);
}
public List<DistanceClass> GetDistances(string systemname)
{
List<DistanceClass> listDistances = new List<DistanceClass>();
try
{
string json;
string query;
query = "?sysname=" + WebUtility.HtmlEncode(systemname) + "&coords=1&distances=1&submitted=1";
json = RequestGet("sysinfo.php" + query);
//http://the-temple.de/public/sysinfo.php?sysname=Col+359+Sector+CP-Y+c1-18&coords=1&include_hidden=1&distances=1&submitted=1
if (json.Length > 1)
{
JObject ditancesresp = (JObject)JObject.Parse(json);
JArray distances = (JArray)ditancesresp["distances"];
if (distances != null)
{
foreach (JObject jo in distances)
{
DistanceClass dc = new DistanceClass();
dc.NameA = systemname;
dc.NameB = jo["name"].Value<string>();
dc.Dist = jo["dist"].Value<float>();
// dc.CommanderCreate = jo[]
listDistances.Add(dc);
}
}
}
}
catch
{
}
return listDistances;
}
public static double DistanceDB(SystemClass s1, SystemClass s2)
{
List <DistanceClass> dists = new List <DistanceClass>();
try
{
using (SQLiteConnection cn = new SQLiteConnection(SQLiteDBClass.ConnectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
DataSet ds = null;
DataSet ds2 = null;
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
cmd.CommandText = "SELECT * FROM Distances WHERE NameA = @NameA COLLATE NOCASE and NameB = @NameB COLLATE NOCASE ";
cmd.Parameters.AddWithValue("@NameA", s1.name);
cmd.Parameters.AddWithValue("@NameB", s2.name);
ds = SqlQueryText(cn, cmd);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@NameA", s2.name);
cmd.Parameters.AddWithValue("@NameB", s1.name);
ds2 = SqlQueryText(cn, cmd);
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DistanceClass dist = new DistanceClass(dr);
dists.Add(dist);
}
}
}
if (ds2.Tables.Count > 0)
{
if (ds2.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds2.Tables[0].Rows)
{
DistanceClass dist = new DistanceClass(dr);
dists.Add(dist);
}
}
}
if (dists.Count == 0)
{
return(-1);
}
return(dists[0].Dist);
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
return(-1);
}
}
public static void AddDistanceToCache(DistanceClass distance)
{
dictDistances[GetDistanceCacheKey(distance.NameA, distance.NameB)] = distance.Dist;
}
private static long ParseEDSMUpdateDistancesReader(JsonTextReader jr, ref string date , bool removenonedsmids, Func<bool> cancelRequested, Action<int, string> reportProgress)
{
List<DistanceClass> toupdate = new List<DistanceClass>();
List<DistanceClass> newpairs = new List<DistanceClass>();
DateTime maxdate = DateTime.Parse(date, new CultureInfo("sv-SE"));
bool emptydatabase = GetTotalDistances() == 0; // if empty database, we can skip the lookup
using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) // open the db
{
int c = 0;
DbCommand cmd = null;
int lasttc = Environment.TickCount;
try
{
cmd = cn.CreateCommand("select * from Distances where id_edsm=@id limit 1"); // 1 return matching
while (jr.Read() && !cancelRequested())
{
if (jr.TokenType == JsonToken.StartObject)
{
JObject jo = JObject.Load(jr);
DistanceClass dc = new DistanceClass(jo);
if (dc.CreateTime.Subtract(maxdate).TotalSeconds > 0)
maxdate = dc.CreateTime;
if (++c % 10000 == 0)
{
Console.WriteLine("Dist Count " + c + " Delta " + (Environment.TickCount - lasttc) + " newpairs " + newpairs.Count + " update " + toupdate.Count());
reportProgress(-1, $"Reading EDSM distances: {c} processed, {newpairs.Count} new, {toupdate.Count} to update");
lasttc = Environment.TickCount;
}
if (emptydatabase) // empty DB, just store..
{
newpairs.Add(dc);
}
else
{
cmd.Parameters.Clear();
cmd.AddParameterWithValue("id", dc.id_edsm);
using (DbDataReader reader1 = cmd.ExecuteReader()) // see if ESDM ID is there..
{
if (reader1.Read()) // its there..
{
DistanceClass dbdc = new DistanceClass(reader1);
// see if EDSM data changed..
if (!dbdc.NameA.Equals(dc.NameA) || !dbdc.NameB.Equals(dc.NameB) || Math.Abs(dbdc.Dist - dc.Dist) > 0.05)
{
dbdc.NameA = dc.NameA;
dbdc.NameB = dc.NameB;
dbdc.Dist = dc.Dist;
toupdate.Add(dbdc);
}
}
else // not in database..
{
//Console.WriteLine("Add new system " + system.name);
newpairs.Add(dc);
}
}
}
}
}
}
catch
{
MessageBox.Show("There is a problem using the EDSM distance file." + Environment.NewLine +
"Please perform a manual EDSM distance sync (see Admin menu) next time you run the program ", "ESDM Sync Error");
}
finally
{
if (cmd != null) cmd.Dispose();
}
}
if (cancelRequested())
{
return 0;
}
using (SQLiteConnectionSystem cn2 = new SQLiteConnectionSystem()) // open the db
{
if (toupdate.Count > 0)
{
reportProgress(-1, $"Updating EDSM distances: {toupdate.Count} distances to update");
using (DbTransaction transaction = cn2.BeginTransaction())
{
foreach (DistanceClass dc in toupdate)
dc.Update(cn2, transaction);
transaction.Commit();
}
}
if (cancelRequested())
{
return toupdate.Count();
}
if (newpairs.Count > 0)
{
int count = 0;
while (count < newpairs.Count())
{
using (DbTransaction transaction = cn2.BeginTransaction())
{
while (count < newpairs.Count())
{
newpairs[count].Store(cn2, transaction);
if (++count % 10000 == 0)
break;
}
reportProgress(count * 100 / newpairs.Count, $"Adding EDSM distances: {count} added");
Console.WriteLine("EDSM Dist Store Count " + count);
transaction.Commit();
}
if (cancelRequested())
{
return toupdate.Count() + count;
}
}
}
if (removenonedsmids) // done on a full sync..
{
reportProgress(-1, "Removing distances without an ID");
Console.WriteLine("Delete old ones");
using (DbCommand cmddel = cn2.CreateCommand("Delete from Distances where id_edsm is null"))
{
SQLiteDBClass.SQLNonQueryText(cn2, cmddel);
}
}
}
date = maxdate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
return toupdate.Count + newpairs.Count;
}
public static List<DistanceClass> GetDistancesByStatus(int status)
{
List<DistanceClass> ldist = new List<DistanceClass>();
try
{
using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem())
{
using (DbCommand cmd = cn.CreateCommand("select * from Distances WHERE status='" + status.ToString() + "'"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DistanceClass dist = new DistanceClass(dr);
ldist.Add(dist);
}
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
}
return ldist;
}
public static DistanceClass GetDistanceClass(EDDiscovery2.DB.ISystem s1, EDDiscovery2.DB.ISystem s2)
{
if (s1 == null || s2 == null)
return null;
try
{
using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem())
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM Distances WHERE (NameA = @NameA and NameB = @NameB) OR (NameA = @NameB and NameB = @NameA) limit 1"))
{
cmd.AddParameterWithValue("@NameA", s1.name);
cmd.AddParameterWithValue("@NameB", s2.name);
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) // if found.
{
DistanceClass dist = new DistanceClass(ds.Tables[0].Rows[0]);
return dist;
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
}
return null;
}
public static void FillVisitedSystems(List<VisitedSystemsClass> visitedSystems, bool usedb)
{
try
{
using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem())
{
DbCommand cmd = cn.CreateCommand("SELECT * FROM Distances WHERE(NameA = @NameA and NameB = @NameB) OR(NameA = @NameB and NameB = @NameA) limit 1");
for (int i = 1; i < visitedSystems.Count; i++) // now we filled in current system, fill in previous system (except for last)
{
VisitedSystemsClass cur = visitedSystems[i];
VisitedSystemsClass prev = visitedSystems[i - 1];
cur.prevSystem = prev.curSystem;
double dist = SystemClass.Distance(cur.curSystem, prev.curSystem); // Try the easy way
if ( dist < 0 && usedb ) // failed, and use the db is allowed..
{
cmd.Parameters.Clear();
cmd.AddParameterWithValue("@NameA", cur.Name);
cmd.AddParameterWithValue("@NameB", prev.Name);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
DistanceClass dst = new DistanceClass(reader);
dist = dst.Dist;
}
}
}
if (dist > 0)
cur.strDistance = dist.ToString("0.00");
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
}
}
public static void AddDistanceToCache(DistanceClass distance)
{
globalDistances.Add(distance);
dictDistances[distance.NameA.ToLower() + ":" + distance.NameB.ToLower()] = distance;
dictDistances[distance.NameB.ToLower() + ":" + distance.NameA.ToLower()] = distance;
}
public static double DistanceDB(SystemClass s1, SystemClass s2)
{
List<DistanceClass> dists = new List<DistanceClass>();
try
{
using (SQLiteConnection cn = new SQLiteConnection(SQLiteDBClass.ConnectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
DataSet ds = null;
DataSet ds2 = null;
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
cmd.CommandText = "SELECT * FROM Distances WHERE NameA = @NameA COLLATE NOCASE and NameB = @NameB COLLATE NOCASE ";
cmd.Parameters.AddWithValue("@NameA", s1.name);
cmd.Parameters.AddWithValue("@NameB", s2.name);
ds = SqlQueryText(cn, cmd);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@NameA", s2.name);
cmd.Parameters.AddWithValue("@NameB", s1.name);
ds2 = SqlQueryText(cn, cmd);
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DistanceClass dist = new DistanceClass(dr);
dists.Add(dist);
}
}
}
if (ds2.Tables.Count > 0)
{
if (ds2.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds2.Tables[0].Rows)
{
DistanceClass dist = new DistanceClass(dr);
dists.Add(dist);
}
}
}
if (dists.Count == 0)
return -1;
return dists[0].Dist;
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
return -1;
}
}
public bool GetAllDistances(bool loadAlldata)
{
try
{
using (SQLiteConnection cn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
DataSet ds = null;
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
if (!loadAlldata)
cmd.CommandText = "select * from Distances WHERE status='3' or status = '4'";// EDDiscovery = 3, EDDiscoverySubmitted = 4
else
cmd.CommandText = "select * from Distances";
ds = SqlQueryText(cn, cmd);
if (ds.Tables.Count == 0)
{
return false;
}
//
if (ds.Tables[0].Rows.Count == 0)
{
return false;
}
dictDistances.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
DistanceClass distance = new DistanceClass(dr);
AddDistanceToCache(distance);
}
return true;
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
return false;
}
}
public static List <DistanceClass> Parse(JObject jo, string date)
{
List <DistanceClass> dists = new List <DistanceClass>();
string NameA = jo["name"].Value <string>();
JArray ja = (JArray)jo["refs"];
foreach (JObject jdist in ja)
{
DistanceClass dist = new DistanceClass();
dist.NameA = NameA;
dist.NameB = jdist["name"].Value <string>();
dist.Dist = jdist["dist"].Value <float>();
dist.CommanderCreate = jdist["commanderupdate"].Value <string>();
dist.CreateTime = jdist["updatedate"].Value <DateTime>();
dist.Status = DistancsEnum.EDSC;
if (date.StartsWith("2010-")) // första gången läg in fflera om cr>1;
{
dist.CommanderCreate = jdist["commandercreate"].Value <string>();
dist.CreateTime = jdist["createdate"].Value <DateTime>();
dist.Status = DistancsEnum.EDSC;
int cr = jdist["cr"].Value <int>();
dists.Add(dist);
if (cr > 1)
{
dist = new DistanceClass();
dist.NameA = NameA;
dist.NameB = jdist["name"].Value <string>();
dist.Dist = jdist["dist"].Value <double>();
dist.CommanderCreate = jdist["commanderupdate"].Value <string>();
dist.CreateTime = jdist["updatedate"].Value <DateTime>();
dist.Status = DistancsEnum.EDSC;
if (cr > 2)
{
dist.NameA = NameA;
}
if (cr > 5)
{
cr = 5;
}
for (int ii = 1; ii < cr; ii++)
{
dists.Add(dist);
}
}
}
else
{
dists.Add(dist);
}
}
return(dists);
}
private static long ParseEDSMUpdateDistancesReader(JsonTextReader jr, ref string date, bool removenonedsmids)
{
List <DistanceClass> toupdate = new List <DistanceClass>();
List <DistanceClass> newpairs = new List <DistanceClass>();
DateTime maxdate = DateTime.Parse(date, new CultureInfo("sv-SE"));
bool emptydatabase = GetTotalDistances() == 0; // if empty database, we can skip the lookup
using (SQLiteConnectionED cn = new SQLiteConnectionED()) // open the db
{
int c = 0;
DbCommand cmd = null;
int lasttc = Environment.TickCount;
try
{
cmd = cn.CreateCommand("select * from Distances where id_edsm=@id limit 1"); // 1 return matching
while (jr.Read())
{
if (jr.TokenType == JsonToken.StartObject)
{
JObject jo = JObject.Load(jr);
DistanceClass dc = new DistanceClass(jo);
if (dc.CreateTime.Subtract(maxdate).TotalSeconds > 0)
{
maxdate = dc.CreateTime;
}
if (++c % 10000 == 0)
{
Console.WriteLine("Dist Count " + c + " Delta " + (Environment.TickCount - lasttc) + " newpairs " + newpairs.Count + " update " + toupdate.Count());
lasttc = Environment.TickCount;
}
if (emptydatabase) // empty DB, just store..
{
newpairs.Add(dc);
}
else
{
cmd.Parameters.Clear();
cmd.AddParameterWithValue("id", dc.id_edsm);
using (DbDataReader reader1 = cmd.ExecuteReader()) // see if ESDM ID is there..
{
if (reader1.Read()) // its there..
{
DistanceClass dbdc = new DistanceClass(reader1);
// see if EDSM data changed..
if (!dbdc.NameA.Equals(dc.NameA) || !dbdc.NameB.Equals(dc.NameB) || Math.Abs(dbdc.Dist - dc.Dist) > 0.05)
{
dbdc.NameA = dc.NameA;
dbdc.NameB = dc.NameB;
dbdc.Dist = dc.Dist;
toupdate.Add(dbdc);
}
}
else // not in database..
{
//Console.WriteLine("Add new system " + system.name);
newpairs.Add(dc);
}
}
}
}
}
}
catch
{
MessageBox.Show("There is a problem using the EDSM distance file." + Environment.NewLine +
"Please perform a manual EDSM distance sync (see Admin menu) next time you run the program ", "ESDM Sync Error");
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
}
using (SQLiteConnectionED cn2 = new SQLiteConnectionED()) // open the db
{
if (toupdate.Count > 0)
{
using (DbTransaction transaction = cn2.BeginTransaction())
{
foreach (DistanceClass dc in toupdate)
{
dc.Update(cn2, transaction);
}
transaction.Commit();
}
}
if (newpairs.Count > 0)
{
int count = 0;
while (count < newpairs.Count())
{
using (DbTransaction transaction = cn2.BeginTransaction())
{
while (count < newpairs.Count())
{
newpairs[count].Store(cn2, transaction);
if (++count % 10000 == 0)
{
break;
}
}
Console.WriteLine("EDSM Dist Store Count " + count);
transaction.Commit();
}
}
}
if (removenonedsmids) // done on a full sync..
{
Console.WriteLine("Delete old ones");
using (DbCommand cmddel = cn2.CreateCommand("Delete from Distances where id_edsm is null"))
{
SQLiteDBClass.SQLNonQueryText(cn2, cmddel);
}
}
}
date = maxdate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
return(toupdate.Count + newpairs.Count);
}
public static double FindDistance(EDDiscovery2.DB.ISystem s1, EDDiscovery2.DB.ISystem s2)
{
DistanceClass dst = GetDistanceClass(s1, s2);
return((dst == null) ? -1 : dst.Dist);
}
public static void AddDistanceToCache(DistanceClass distance)
{
dictDistances[GetDistanceCacheKey(distance.NameA, distance.NameB)] = distance;
}
internal void NewPosition(object source)
{
try
{
string name = netlog.visitedSystems.Last().Name;
Invoke((MethodInvoker)delegate
{
LogText("Arrived to system: ");
SystemClass sys1 = SystemData.GetSystem(name);
if (sys1 == null || sys1.HasCoordinate == false)
LogText(name , Color.Blue);
else
LogText(name );
int count = GetVisitsCount(name);
LogText(" : Vist nr " + count.ToString() + Environment.NewLine);
System.Diagnostics.Trace.WriteLine("Arrived to system: " + name + " " + count.ToString() + ":th visit.");
var result = visitedSystems.OrderByDescending(a => a.time).ToList<SystemPosition>();
if (TrilaterationControl.Visible)
{
CloseTrilateration();
MessageBox.Show("You have arrived to another system while trilaterating."
+ " As a pre-caution to prevent any mistakes with submitting wrong systems or distances"
+ ", your trilateration was aborted.");
}
buttonTrilaterate.Enabled = false; // when we arrive to new system, currently opened SystemInformation will _always_ be for non-current system
SystemPosition item = result[0];
SystemPosition item2;
if (result.Count > 1)
item2 = result[1];
else
item2 = null;
// grab distance to next (this) system
textBoxDistanceToNextSystem.Enabled = false;
if (textBoxDistanceToNextSystem.Text.Length > 0 && item2 != null)
{
SystemClass currentSystem = null, previousSystem = null;
SystemData.SystemList.ForEach(s =>
{
if (s.name == item.Name) currentSystem = s;
if (s.name == item2.Name) previousSystem = s;
});
if (currentSystem == null || previousSystem == null || !currentSystem.HasCoordinate || !previousSystem.HasCoordinate)
{
var presetDistance = DistanceAsDouble(textBoxDistanceToNextSystem.Text.Trim(), 45);
if (presetDistance.HasValue)
{
var distance = new DistanceClass
{
Dist = presetDistance.Value,
CreateTime = DateTime.UtcNow,
CommanderCreate = textBoxCmdrName.Text.Trim(),
NameA = item.Name,
NameB = item2.Name,
Status = DistancsEnum.EDDiscovery
};
Console.Write("Pre-set distance " + distance.NameA + " -> " + distance.NameB + " = " + distance.Dist);
distance.Store();
SQLiteDBClass.AddDistanceToCache(distance);
}
}
}
textBoxDistanceToNextSystem.Clear();
textBoxDistanceToNextSystem.Enabled = true;
AddHistoryRow(new DateTime(1990, 1, 1), item, item2);
lastRowIndex += 1;
StoreSystemNote();
});
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception NewPosition: " + ex.Message);
System.Diagnostics.Trace.WriteLine("Trace: " + ex.StackTrace);
}
}
public List<DistanceClass> GetDistances(string systemname)
{
List<DistanceClass> listDistances = new List<DistanceClass>();
try
{
string query;
query = "?sysname=" + HttpUtility.UrlEncode(systemname) + "&coords=1&distances=1&submitted=1";
var response = RequestGet("api-v1/system" + query);
var json = response.Body;
//https://www.edsm.net/api-v1/system?sysname=Col+359+Sector+CP-Y+c1-18&coords=1&include_hidden=1&distances=1&submitted=1
if (json.Length > 1)
{
JObject ditancesresp = (JObject)JObject.Parse(json);
JArray distances = (JArray)ditancesresp["distances"];
if (distances != null)
{
foreach (JObject jo in distances)
{
DistanceClass dc = new DistanceClass();
dc.NameA = systemname;
dc.NameB = jo["name"].Value<string>();
dc.Dist = jo["dist"].Value<float>();
// dc.CommanderCreate = jo[]
listDistances.Add(dc);
}
}
}
}
catch
{
}
return listDistances;
}
private void buttonUpdate_Click(object sender, EventArgs e)
{
var dist = DistanceAsDouble(textBoxDistance.Text.Trim());
if (!dist.HasValue)
MessageBox.Show("Distance in wrong format!");
else
{
DistanceClass distance = new DistanceClass();
distance.Dist = dist.Value;
distance.CreateTime = DateTime.UtcNow;
distance.CommanderCreate = textBoxCmdrName.Text.Trim();
distance.NameA = textBoxSystem.Text;
distance.NameB = textBoxPrevSystem.Text;
distance.Status = DistancsEnum.EDDiscovery;
distance.Store();
SQLiteDBClass.AddDistanceToCache(distance);
dataGridView1.Rows[lastRowIndex].Cells[2].Value = textBoxDistance.Text.Trim();
}
}
public static List<DistanceClass> Parse(JObject jo, string date)
{
List<DistanceClass> dists = new List<DistanceClass>();
string NameA = jo["name"].Value<string>();
JArray ja = (JArray)jo["refs"];
foreach (JObject jdist in ja)
{
DistanceClass dist = new DistanceClass();
dist.NameA = NameA;
dist.NameB = jdist["name"].Value<string>();
dist.Dist = jdist["dist"].Value<float>();
dist.CommanderCreate = jdist["commanderupdate"].Value<string>();
dist.CreateTime = jdist["updatedate"].Value<DateTime>();
dist.Status = DistancsEnum.EDSC;
if (date.StartsWith("2010-")) // första gången läg in fflera om cr>1;
{
dist.CommanderCreate = jdist["commandercreate"].Value<string>();
dist.CreateTime = jdist["createdate"].Value<DateTime>();
dist.Status = DistancsEnum.EDSC;
int cr = jdist["cr"].Value<int>();
dists.Add(dist);
if (cr > 1)
{
dist = new DistanceClass();
dist.NameA = NameA;
dist.NameB = jdist["name"].Value<string>();
dist.Dist = jdist["dist"].Value<double>();
dist.CommanderCreate = jdist["commanderupdate"].Value<string>();
dist.CreateTime = jdist["updatedate"].Value<DateTime>();
dist.Status = DistancsEnum.EDSC;
if (cr > 2)
dist.NameA = NameA;
if (cr > 5)
cr = 5;
for (int ii = 1; ii < cr; ii++)
dists.Add(dist);
}
}
else
dists.Add(dist);
}
return dists;
}
/*
{
"date": "2015-08-31 07:30:58",
"submitted_by": [
{
"cmdr": "Mixolydian",
"date": "2015-04-12 17:11:39"
}
],
"sys1": {
"name": "Col 359 Sector ZJ-Z d86",
"date": "2015-05-12 15:29:33"
},
"sys2": {
"name": "KOI 1925",
"date": "2015-08-31 07:31:01"
},
"distance": 34.86
}
*/
public static DistanceClass ParseEDSM(JObject jo, string date)
{
DistanceClass dist = new DistanceClass();
JObject sys1, sys2;
sys1 = (JObject)jo["sys1"];
sys2 = (JObject)jo["sys2"];
JArray submitted_by = (JArray)jo["submitted_by"];
dist.NameA = sys1["name"].Value<string>();
dist.NameB = sys2["name"].Value<string>();
dist.Dist = jo["distance"].Value<float>();
if (submitted_by.Count > 0)
{
dist.CommanderCreate = submitted_by[0]["cmdr"].Value<string>();
}
else
dist.CommanderCreate = "";
dist.CreateTime = jo["date"].Value<DateTime>();
dist.Status = DistancsEnum.EDSC;
return dist;
}
