public static bool GetAllSystemNotes()
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from SystemNote"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(false);
}
globalSystemNotes.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
SystemNoteClass sys = new SystemNoteClass(dr);
globalSystemNotes.Add(sys);
}
return(true);
}
}
}
catch
{
return(false);
}
}
public int GetComments(DateTime starttime, out List<SystemNoteClass> notes)
{
notes = new List<SystemNoteClass>();
var json = GetComments(starttime);
if (json == null)
{
return 0;
}
JObject msg = JObject.Parse(json);
int msgnr = msg["msgnum"].Value<int>();
JArray comments = (JArray)msg["comments"];
if (comments != null)
{
foreach (JObject jo in comments)
{
SystemNoteClass note = new SystemNoteClass();
note.Name = jo["system"].Value<string>();
note.Note = jo["comment"].Value<string>();
note.Time = DateTime.ParseExact(jo["lastUpdate"].Value<string>(), "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal).ToLocalTime();
notes.Add(note);
}
}
return msgnr;
}
public static bool GetAllSystemNotes()
{
try
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("select * from SystemNote"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(false);
}
globalSystemNotes.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
SystemNoteClass sys = new SystemNoteClass(dr);
globalSystemNotes[sys.Name.ToLower()] = sys;
}
return(true);
}
}
}
catch
{
return(false);
}
}
public static bool GetAllSystemNotes()
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser())
{
using (DbCommand cmd = cn.CreateCommand("select * from SystemNote"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return false;
}
globalSystemNotes.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
SystemNoteClass sys = new SystemNoteClass(dr);
globalSystemNotes[sys.Name.ToLower()] = sys;
}
return true;
}
}
}
catch
{
return false;
}
}
public string SetComment(SystemNoteClass sn)
{
string query;
query = "set-comment?systemName=" + HttpUtility.UrlEncode(sn.Name) + "&commanderName=" + HttpUtility.UrlEncode(commanderName) + "&apiKey=" + apiKey + "&comment=" + HttpUtility.UrlEncode(sn.Note);
var response = RequestGet("api-logs-v1/" + query);
return response.Body;
}
private bool UpgradeDB4()
{
string query1 = "ALTER TABLE SystemNote ADD COLUMN Note TEXT";
string dbfile = GetSQLiteDBFile();
try
{
File.Copy(dbfile, dbfile.Replace("EDDiscovery.sqlite", "EDDiscovery3.sqlite"));
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception: " + ex.Message);
System.Diagnostics.Trace.WriteLine("Trace: " + ex.StackTrace);
}
try
{
ExecuteQuery(query1);
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception: " + ex.Message);
System.Diagnostics.Trace.WriteLine("Trace: " + ex.StackTrace);
MessageBox.Show("UpgradeDB4 error: " + ex.Message);
}
GetAllSystems();
foreach (SystemClass sys in globalSystems)
{
if (sys.Note != null && sys.Note.Length > 0)
{
SystemNoteClass note = new SystemNoteClass();
note.Name = sys.name;
note.Note = sys.Note;
note.Time = DateTime.Now;
note.Add();
}
}
PutSettingInt("DBVer", 4);
return true;
}
public bool GetAllSystemNotes()
{
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 SystemNote";
ds = SqlQueryText(cn, cmd);
if (ds.Tables.Count == 0)
{
return false;
}
//
if (ds.Tables[0].Rows.Count == 0)
{
return false;
}
globalSystemNotes.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
SystemNoteClass sys = new SystemNoteClass(dr);
globalSystemNotes[sys.Name.ToLower()] = sys;
}
return true;
}
}
}
catch
{
return false;
}
}
private void Sync(EDSMClass edsm)
{
try
{
mainForm.LogLine("EDSM sync begin");
List<HistoryEntry> hlfsdunsyncedlist = mainForm.history.FilterByNotEDSMSyncedAndFSD; // first entry is oldest
if ( _syncTo && hlfsdunsyncedlist.Count > 0 ) // send systems to edsm (verified with dates, 29/9/2016, utc throughout)
{
DateTime utcmin = hlfsdunsyncedlist[0].EventTimeUTC.AddDays(-1); // 1 days for margin ;-) only get them back to this date for speed..
mainForm.LogLine("EDSM: Sending " + hlfsdunsyncedlist.Count.ToString() + " flightlog entries");
List<HistoryEntry> edsmsystemlog = null;
edsm.GetLogs(utcmin, out edsmsystemlog); // always returns a log, time is in UTC as per HistoryEntry and JournalEntry
int edsmsystemssent = 0;
foreach (var he in hlfsdunsyncedlist)
{
if (Exit)
{
running = false;
return;
}
HistoryEntry ps2 = (from c in edsmsystemlog where c.System.name == he.System.name && c.EventTimeUTC.Ticks == he.EventTimeUTC.Ticks select c).FirstOrDefault();
if (ps2 != null) // it did, just make sure EDSM sync flag is set..
{
he.SetEdsmSync();
}
else
{
string errmsg; // (verified with EDSM 29/9/2016)
// it converts to UTC inside the function, supply local for now
if ( edsm.SendTravelLog(he.System.name, he.EventTimeUTC, he.System.HasCoordinate && !he.IsStarPosFromEDSM, he.System.x, he.System.y, he.System.z, out errmsg) )
he.SetEdsmSync();
if (errmsg.Length > 0)
mainForm.LogLine(errmsg);
edsmsystemssent++;
}
}
mainForm.LogLine(string.Format("EDSM Systems sent {0}", edsmsystemssent));
}
// TBD Comments to edsm?
if ( _syncFrom ) // Verified ok with time 29/9/2016
{
var json = edsm.GetComments(new DateTime(2011, 1, 1));
if (json != null)
{
JObject msg = JObject.Parse(json);
int msgnr = msg["msgnum"].Value<int>();
JArray comments = (JArray)msg["comments"];
if (comments != null)
{
int commentsadded = 0;
foreach (JObject jo in comments)
{
string name = jo["system"].Value<string>();
string note = jo["comment"].Value<string>();
string utctime = jo["lastUpdate"].Value<string>();
int edsmid = 0;
if (!Int32.TryParse(JSONHelper.GetStringDef(jo["systemId"], "0"), out edsmid))
edsmid = 0;
DateTime localtime = DateTime.ParseExact(utctime, "yyyy-MM-dd HH:mm:ss",
CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal).ToLocalTime();
SystemNoteClass curnote = SystemNoteClass.GetNoteOnSystem(name, edsmid);
if (curnote != null)
{
// curnote uses local time to store
if (localtime.Ticks > curnote.Time.Ticks) // if newer, add on (verified with EDSM 29/9/2016)
{
curnote.Note += ". EDSM: " + note;
curnote.Time = localtime;
curnote.EdsmId = edsmid;
curnote.Update();
commentsadded++;
}
}
else
{
curnote = new SystemNoteClass();
curnote.Note = note;
curnote.Time = localtime;
curnote.Name = name;
curnote.Journalid = 0;
curnote.EdsmId = edsmid;
curnote.Add();
commentsadded++;
}
}
mainForm.LogLine(string.Format("EDSM Comments downloaded/updated {0}", commentsadded));
}
}
}
if (_syncFrom ) // verified after struggle 29/9/2016
{
List<HistoryEntry> edsmsystemlog = null;
edsm.GetLogs(new DateTime(2011, 1, 1), out edsmsystemlog); // get the full list of systems
edsmsystemlog = edsmsystemlog.OrderBy(s => s.EventTimeUTC).ToList();
List<HistoryEntry> hlfsdlist = mainForm.history.FilterByTravel.Where(h => h.IsLocOrJump).OrderBy(h => h.EventTimeUTC).ToList(); // FSD jumps only
List<HistoryEntry> toadd = new List<HistoryEntry>();
int previdx = -1;
foreach (HistoryEntry he in edsmsystemlog) // find out list of ones not present
{
int index = hlfsdlist.FindIndex(x => x.System.name.Equals(he.System.name, StringComparison.InvariantCultureIgnoreCase) && x.EventTimeUTC.Ticks == he.EventTimeUTC.Ticks);
if (index < 0)
{
// Look for any entries where DST may have thrown off the time
foreach (var vi in hlfsdlist.Select((v,i) => new {v = v, i = i}).Where(vi => vi.v.System.name.Equals(he.System.name, StringComparison.InvariantCultureIgnoreCase)))
{
if (vi.i > previdx)
{
double hdiff = vi.v.EventTimeUTC.Subtract(he.EventTimeUTC).TotalHours;
if (hdiff >= -2 && hdiff <= 2 && hdiff == Math.Floor(hdiff))
{
if (vi.v.System.id_edsm <= 0)
{
vi.v.System.id_edsm = 0;
mainForm.history.FillEDSM(vi.v);
}
if (vi.v.System.id_edsm <= 0 || vi.v.System.id_edsm == he.System.id_edsm)
{
index = vi.i;
break;
}
}
}
}
}
if (index < 0)
{
toadd.Add(he);
}
else
{
previdx = index;
}
}
if ( toadd.Count >0 ) // if we have any, we can add
{
TravelLogUnit tlu = new TravelLogUnit(); // need a tlu for it
tlu.type = 2; // EDSM
tlu.Name = "EDSM-" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
tlu.Size = 0;
tlu.Path = "EDSM";
tlu.CommanderId = EDDiscoveryForm.EDDConfig.CurrentCommander.Nr;
tlu.Add(); // Add to Database
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true))
{
foreach (HistoryEntry he in toadd)
{
EDDiscovery.EliteDangerous.JournalEntry je =
EDDiscovery.EliteDangerous.JournalEntry.CreateFSDJournalEntry(tlu.id, tlu.CommanderId.Value, he.EventTimeUTC,
he.System.name, he.System.x, he.System.y, he.System.z,
_defmapcolour, (int)EDDiscovery.EliteDangerous.SyncFlags.EDSM);
System.Diagnostics.Trace.WriteLine(string.Format("Add {0} {1}", je.EventTimeUTC, he.System.name));
je.Add(cn);
}
}
if (OnDownloadedSystems != null)
OnDownloadedSystems();
mainForm.LogLine(string.Format("EDSM downloaded {0} systems", toadd.Count));
}
}
mainForm.LogLine("EDSM sync Done");
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception ex:" + ex.Message);
mainForm.LogLineHighlight("EDSM sync Exception " + ex.Message);
}
}
private void StoreSystemNote()
{
string txt;
try
{
//SystemPosition sp = (SystemPosition)dataGridView1.Rows[lastRowIndex].Cells[1].Tag;
txt = richTextBoxNote.Text;
if (currentSysPos != null && !txt.Equals(currentSysPos.curSystem.Note))
{
SystemNoteClass sn;
List<SystemClass> systems = new List<SystemClass>();
if (SQLiteDBClass.globalSystemNotes.ContainsKey(currentSysPos.curSystem.SearchName))
{
sn = SQLiteDBClass.globalSystemNotes[currentSysPos.curSystem.SearchName];
sn.Note = txt;
sn.Time = DateTime.Now;
sn.Update();
}
else
{
sn = new SystemNoteClass();
sn.Name = currentSysPos.curSystem.name;
sn.Note = txt;
sn.Time = DateTime.Now;
sn.Add();
}
currentSysPos.curSystem.Note = txt;
dataGridView1.Rows[lastRowIndex].Cells[3].Value = txt;
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
LogText("Exception : " + ex.Message, Color.Red);
LogText(ex.StackTrace, Color.Red);
}
}
private void StoreSystemNote()
{
string txt;
try
{
EDSMClass edsm = new EDSMClass();
SQLiteDBClass db = new SQLiteDBClass();
edsm.apiKey = db.GetSettingString("EDSMApiKey", "");
edsm.commanderName = db.GetSettingString("CommanderName", "");
if (currentSysPos == null || currentSysPos.curSystem == null)
return;
//SystemPosition sp = (SystemPosition)dataGridView1.Rows[lastRowIndex].Cells[1].Tag;
txt = richTextBoxNote.Text;
if (currentSysPos.curSystem.Note == null)
currentSysPos.curSystem.Note = "";
if (currentSysPos != null && !txt.Equals(currentSysPos.curSystem.Note))
{
SystemNoteClass sn;
List<SystemClass> systems = new List<SystemClass>();
if (SQLiteDBClass.globalSystemNotes.ContainsKey(currentSysPos.curSystem.SearchName))
{
sn = SQLiteDBClass.globalSystemNotes[currentSysPos.curSystem.SearchName];
sn.Note = txt;
sn.Time = DateTime.Now;
sn.Update();
}
else
{
sn = new SystemNoteClass();
sn.Name = currentSysPos.curSystem.name;
sn.Note = txt;
sn.Time = DateTime.Now;
sn.Add();
}
currentSysPos.curSystem.Note = txt;
dataGridView1.Rows[lastRowIndex].Cells[3].Value = txt;
if (edsm.commanderName == null || edsm.apiKey == null)
return;
if (edsm.commanderName.Length>1 && edsm.apiKey.Length>1)
edsm.SetComment(sn);
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
LogText("Exception : " + ex.Message, Color.Red);
LogText(ex.StackTrace, Color.Red);
}
}
private void StoreSystemNote()
{
if (userControlTravelGrid.currentGridRow < 0)
return;
try
{
HistoryEntry sys = userControlTravelGrid.GetCurrentHistoryEntry;
SystemNoteClass sn = userControlTravelGrid.GetCurrentSystemNoteClass;
string txt = richTextBoxNote.Text.Trim();
if ( (sn == null && txt.Length>0) || (sn!=null && !sn.Note.Equals(txt))) // if no system note, and text, or system not is not text
{
if ( sn != null && (sn.Journalid == sys.Journalid || sn.Journalid == 0 || (sn.Name.Equals(sys.System.name, StringComparison.InvariantCultureIgnoreCase) && sn.EdsmId <= 0) || (sn.EdsmId > 0 && sn.EdsmId == sys.System.id_edsm)) ) // already there, update
{
sn.Note = txt;
sn.Time = DateTime.Now;
sn.Name = (sys.IsFSDJump) ? sys.System.name : "";
sn.Journalid = sys.Journalid;
sn.EdsmId = sys.IsFSDJump ? sys.System.id_edsm : 0;
sn.Update();
}
else
{
sn = new SystemNoteClass();
sn.Note = txt;
sn.Time = DateTime.Now;
sn.Name = (sys.IsFSDJump) ? sys.System.name : "";
sn.Journalid = sys.Journalid;
sn.EdsmId = sys.IsFSDJump ? sys.System.id_edsm : 0;
sn.Add();
userControlTravelGrid.UpdateCurrentNoteTag(sn);
}
userControlTravelGrid.UpdateCurrentNote(txt);
if (EDDiscoveryForm.EDDConfig.CurrentCommander.SyncToEdsm && sys.IsFSDJump ) // only send on FSD jumps
EDSMSync.SendComments(sn.Name,sn.Note,sn.EdsmId);
_discoveryForm.Map.UpdateNote();
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
_discoveryForm.LogLineHighlight("Exception : " + ex.Message);
_discoveryForm.LogLineHighlight(ex.StackTrace);
}
}
