public List <T> List <T>(Func <T, bool> filter)
{
using (NPoco.IDatabase dbContext = new NPoco.Database(connectionString, NPoco.DatabaseType.SqlServer2012))
{
if (filter != null)
{
return(dbContext.Fetch <T>().Where(filter).ToList());
}
else
{
return(dbContext.Fetch <T>().ToList());
}
}
}
public override void FinalizeRun()
{
TotalProcessTime.Start();
List<GSAKWrapper.MapProviders.GeocachePoco> gcl = null;
double? cLat = null;
double? cLon = null;
using (var db = new NPoco.Database(DatabaseConnection.Connection, NPoco.DatabaseType.SQLite))
{
gcl = db.Fetch<GSAKWrapper.MapProviders.GeocachePoco>(string.Format("select Code, Name, CacheType, Found, IsOwner, Latitude, Longitude, kAfterLat, kAfterLon from Caches inner join {0} on Caches.Code={0}.gccode left join Corrected on Caches.Code=Corrected.kCode", ActionInputTableName));
}
if (gcl.Count > 0)
{
var dr = DatabaseConnection.ExecuteReader(string.Format("select AVG(Latitude), AVG(Longitude) from Caches inner join {0} on Caches.Code={0}.gccode left join Corrected on Caches.Code=Corrected.kCode", ActionInputTableName));
if (dr.Read())
{
cLat = dr.GetDouble(0);
cLon = dr.GetDouble(1);
}
}
_context.Send(new SendOrPostCallback(delegate(object state)
{
var wnd = new Dialogs.WindowOSMOfflineMap(gcl, cLat, cLon, 16);
wnd.Show();
}), null);
TotalProcessTime.Stop();
base.FinalizeRun();
}
public WaypointSelectorWindow()
{
InitializeComponent();
Waypoints = new ObservableCollection <string>();
if (!string.IsNullOrEmpty(Settings.Settings.Default.ActiveGeocacheCode) &&
!string.IsNullOrEmpty(Settings.Settings.Default.DatabaseFolderPath) &&
!string.IsNullOrEmpty(Settings.Settings.Default.SelectedDatabase)
)
{
Waypoints.Add(Settings.Settings.Default.ActiveGeocacheCode);
try
{
var fn = System.IO.Path.Combine(Settings.Settings.Default.DatabaseFolderPath, Settings.Settings.Default.SelectedDatabase, "sqlite.db3");
if (System.IO.File.Exists(fn))
{
using (var temp = new Database.DBConSqlite(fn))
using (var db = new NPoco.Database(temp.Connection, NPoco.DatabaseType.SQLite))
{
var lst = db.Fetch <string>("select cCode from Waypoints where cParent=@0", Settings.Settings.Default.ActiveGeocacheCode);
foreach (var s in lst)
{
Waypoints.Add(s);
}
}
}
}
catch
{
}
}
DataContext = this;
}
public override void FinalizeRun()
{
TotalProcessTime.Start();
List <GSAKWrapper.MapProviders.GeocachePoco> gcl = null;
double?cLat = null;
double?cLon = null;
using (var db = new NPoco.Database(DatabaseConnection.Connection, NPoco.DatabaseType.SQLite))
{
gcl = db.Fetch <GSAKWrapper.MapProviders.GeocachePoco>(string.Format("select Code, Name, CacheType, Found, IsOwner, Latitude, Longitude, kAfterLat, kAfterLon from Caches inner join {0} on Caches.Code={0}.gccode left join Corrected on Caches.Code=Corrected.kCode", ActionInputTableName));
}
if (gcl.Count > 0)
{
var dr = DatabaseConnection.ExecuteReader(string.Format("select AVG(Latitude), AVG(Longitude) from Caches inner join {0} on Caches.Code={0}.gccode left join Corrected on Caches.Code=Corrected.kCode", ActionInputTableName));
if (dr.Read())
{
cLat = dr.GetDouble(0);
cLon = dr.GetDouble(1);
}
}
_context.Send(new SendOrPostCallback(delegate(object state)
{
var wnd = new Dialogs.WindowOSMOfflineMap(gcl, cLat, cLon, 16);
wnd.Show();
}), null);
TotalProcessTime.Stop();
base.FinalizeRun();
}
public T SingleOrDefault <T>(Func <T, bool> filter)
{
using (NPoco.IDatabase dbContext = new NPoco.Database(connectionString, NPoco.DatabaseType.SqlServer2012))
{
return(dbContext.Fetch <T>().Where(filter).SingleOrDefault());
}
}
public void NpocoTest()
{
IDagentDatabase database = new DagentDatabase("SQLite");
NPoco.IDatabase db = new NPoco.Database(database.Connection);
//var customers = db.Query<Customer>("select * from customers");
var customers = db.Fetch <Customer>("select * from customers");
foreach (var customer in customers)
{
customer.CustomerPurchases = db.Fetch <CustomerPurchase>("select * from customerPurchases where customerId = @customerId", new { customerId = customer.customerId });
}
ValidList(customers);
}
/// <summary>
///
/// </summary>
/// <param name="offset"></param>
/// <param name="pageLimit"></param>
public void QueryEvents(long offset, int pageLimit)
{
//if (IsRunning == true)
//{
// //OnExclamation("Already performing query");
// return;
//}
IsRunning = true;
new Thread(() =>
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List <Event> data = db.Fetch <Event>(_sql.GetQuery(Sql.Query.SQL_EVENTS), new object[] { offset, pageLimit });
data = Helper.ProcessEventDataSet(data);
OnComplete(data);
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the query: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
/// <summary>
/// Retrieve a lsit of <c>Company</c> one page at a time. Change the value of page and pageSize to alter the amount of
/// <c>Company</c> returned on each query.
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <param name="minval"></param>
/// <returns></returns>
public IEnumerable <Company> GetPage(int page, int pageSize, DateTime minval)
{
var list = new List <Company>();
minval = minval == null ? new DateTime(year: 2000, month: 0, day: 0) : minval;
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
// NOTE: This hard-coding a minimum date value is a temporary fix. Later on, we
// want to change this to some minimum value of the TIMESTAMP data type.
list = Company.Translate(
db.Fetch <CompanyDto>(new NPoco.Sql(
$@"select *
from {_TABLE_NAME_}
where datecreated > TIMESTAMP '2000-01-01'
order by datecreated desc
limit {pageSize}"
))
).ToList();
db.Connection.Close();
}
}
return(list);
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
public void SetConnection(Connection connection)
{
_connection = connection;
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
_acknowledgmentClasses = db.Fetch <AcknowledgmentClass>();
_acknowledgmentClasses = (from a in _acknowledgmentClasses orderby a.Desc select a).ToList();
}
}
/// <summary>
///
/// </summary>
/// <param name="offset"></param>
/// <param name="pageLimit"></param>
public void QueryEventsRulesFromTo(string dateFrom,
string dateTo,
string sid,
long offset,
int pageLimit,
bool includeAcks)
{
//if (IsRunning == true)
//{
// //OnExclamation("Already performing query");
// return;
//}
IsRunning = true;
new Thread(() =>
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List <Event> temp;
string query = _sql.GetQuery(Sql.Query.SQL_RULES_EVENTS);
if (includeAcks == true)
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_id = @2 LIMIT @3, @4" );
}
else
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND (acknowledgment.id IS NULL OR acknowledgment.id = 0 OR acknowledgment.class = 1)
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_id = @2 LIMIT @3, @4" );
}
temp = db.Fetch <Event>(query, new object[] { dateFrom, dateTo, sid, offset, pageLimit });
temp = Helper.ProcessEventDataSet(temp);
OnComplete(temp);
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the query: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
public List <T> Fetch <T>()
{
int count = 0;
while (IsThisInTransaction())
{
string fn = _transactionSetters[DBFilename];
SLogger.Info("Waiting for transaction for 0.5s, caller was " + fn);
Thread.Sleep(500);
count++;
if (count > 240 * 2)
{
throw new FlaException("Waited more than 240s");
}
}
SLogger.Debug("Fetching all " + typeof(T).FullName + " from " + DBFilename);
lock (Dblock) {
return(_database.Fetch <T>() ?? throw new FlaException("Fetch failed"));
}
}
/// <summary>
/// The RecreateAll.
/// </summary>
/// <returns>The <see cref="bool"/>.</returns>
public static bool RecreateAll()
{
DateTime dateUpdate = DateTime.Now.Date;
DateTime fechaCalculo = DateTime.Now.Date;
if (recalculando == true)
{
return(false);
}
recalculando = true;
lCacheBalances.Clear();
lCacheActionResult.Clear();
DB.InsertaEvento("Inicia RecreateAll" + DateTime.Now.ToString());
DB.DatosClimaticosSiarForceRefresh();
NPoco.Database db = DB.ConexionOptiaqua;
List <Models.Temporada> lTemporadas = DB.TemporadasList();
foreach (Models.Temporada temporada in lTemporadas)
{
string idTemporada = temporada.IdTemporada;
if (dateUpdate >= temporada.FechaFinal)
{
fechaCalculo = temporada.FechaFinal;
}
else
{
fechaCalculo = dateUpdate;
}
Dictionary <string, CacheUnidadCultivo> cacheTemporada = new Dictionary <string, CacheUnidadCultivo>();
lCacheBalances.Add(idTemporada, cacheTemporada);
List <string> lIdUnidadCultivo = db.Fetch <string>($"SELECT DISTINCT IdUnidadCultivo from UnidadCultivoCultivo WHERE IdTemporada=@0", idTemporada);
BalanceHidrico bh = null;
foreach (string idUC in lIdUnidadCultivo)
{
//try {
//DB.InsertaEvento("item " + idTemporada + " " + idUC +" "+ DateTime.Now.ToString());
bh = BalanceHidrico.Balance(idUC, fechaCalculo, true, false);
if (bh != null)
{
cacheTemporada.Add(idUC, new CacheUnidadCultivo {
Fecha = dateUpdate, Balance = bh
});
}
//} catch (Exception) {
//}
}
}
DB.InsertaEvento("Finaliza RecreateAll" + DateTime.Now.ToString());
recalculando = false;
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="db"></param>
public void LoadClassifications(NPoco.Database db)
{
using (new HourGlass(this))
{
List <SigClass> data = db.Fetch <SigClass>(_sql.GetQuery(Sql.Query.SQL_SIG_CLASS));
_classifications = new List <NameValue>();
foreach (var result in data)
{
NameValue nameValue = new NameValue();
nameValue.Name = result.Name;
nameValue.Value = result.Id.ToString();
_classifications.Add(nameValue);
}
}
}
/// <summary>
///
/// </summary>
/// <param name="db"></param>
public void LoadAcknowledgementClasses(NPoco.Database db)
{
using (new HourGlass(this))
{
List <AcknowledgmentClass> data = db.Fetch <AcknowledgmentClass>(_sql.GetQuery(Sql.Query.SQL_ACKNOWLEDGEMENT_CLASSES));
_acknowledgementClasses = new List <NameValue>();
foreach (var result in data)
{
NameValue nameValue = new NameValue();
nameValue.Name = result.Desc;
nameValue.Value = result.Id.ToString();
_acknowledgementClasses.Add(nameValue);
}
}
}
public List <GeocacheCollection> GetGeocacheCollections()
{
List <GeocacheCollection> result = null;
lock (this)
{
if (_dbcon != null)
{
using (var db = new NPoco.Database(_dbcon.Connection, NPoco.DatabaseType.SQLite))
{
result = db.Fetch <GeocacheCollection>();
}
}
}
return(result);
}
public List <GeocacheCollectionItem> GetCollectionItems(int id)
{
List <GeocacheCollectionItem> result = null;
lock (this)
{
if (_dbcon != null)
{
using (var db = new NPoco.Database(_dbcon.Connection, NPoco.DatabaseType.SQLite))
{
result = db.Fetch <GeocacheCollectionItem>("where CollectionID = @0", id);
}
}
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="db"></param>
public void LoadSensors(NPoco.Database db)
{
using (new HourGlass(this))
{
List <Sensor> data = db.Fetch <Sensor>(_sql.GetQuery(Sql.Query.SQL_SENSORS));
_sensors = new List <NameValue>();
foreach (var result in data)
{
NameValue nameValue = new NameValue();
nameValue.Name = result.HostName;
nameValue.Value = result.Sid.ToString();
_sensors.Add(nameValue);
}
}
}
public List <ScriptItem> GetScriptItems()
{
List <ScriptItem> result = null;
lock (this)
{
if (_dbcon != null)
{
using (var db = new NPoco.Database(_dbcon.Connection, NPoco.DatabaseType.SQLite))
{
result = db.Fetch <ScriptItem>("select * from Scripts");
}
}
}
return(result);
}
/// <summary>
///
/// </summary>
public void QuerySensors()
{
//if (IsRunning == true)
//{
// OnExclamation("Already performing query");
// return;
//}
IsRunning = true;
new Thread(() =>
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List <Sensor> sensors = db.Fetch <Sensor>(_sql.GetQuery(Sql.Query.SQL_SENSORS));
long count = 0;
foreach (Sensor sensor in sensors)
{
count += sensor.EventCount;
}
foreach (Sensor sensor in sensors)
{
if (sensor.EventCount > 0)
{
sensor.EventPercentage = (int)(sensor.EventCount / count) * 100;
}
}
OnComplete(sensors);
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the query: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
/// <summary>
/// Retrieve a single <c>Company</c>.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Company GetSingle(Guid id)
{
Company company;
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
company = Company.Translate(
db.Fetch <CompanyDto>(new NPoco.Sql(
$@"
select *
from {_TABLE_NAME_}
where id = @0
", id)).FirstOrDefault()
);
db.Connection.Close();
}
}
return(company);
}
/// <summary>
/// Retrieve a single <c>Device</c>.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Device GetSingle(Guid id)
{
Device device;
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
device = Device.Translate(
db.Fetch <DeviceDto>(new NPoco.Sql(
$@"
select *
from {_TABLE_NAME_}
where id = @0
", id)).FirstOrDefault()
);
db.Connection.Close();
}
}
return(device);
}
/// <summary>
///
/// </summary>
private void LoadExcludes()
{
try
{
using (new HourGlass(this))
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
listExcludes.ClearObjects();
var data = db.Fetch<Dictionary<string, object>>(_sql.GetQuery(Sql.Query.SQL_EXCLUDES));
List<Exclude> excludes = new List<Exclude>();
foreach (Dictionary<string, object> temp in data)
{
Exclude exclude = new Exclude();
exclude.Id = long.Parse(temp["id"].ToString());
exclude.SigId = long.Parse(temp["sig_id"].ToString());
exclude.SigSid = long.Parse(temp["sig_sid"].ToString());
exclude.Rule = temp["sig_name"].ToString();
exclude.Comment = temp["comment"].ToString();
exclude.SourceIpText = temp["ip_src"].ToString();
exclude.SourcePortText = temp["port_src"].ToString();
exclude.DestinationIpText = temp["ip_dst"].ToString();
exclude.DestinationPortText = temp["port_dst"].ToString();
if (int.Parse(temp["ip_proto"].ToString()) == (int)Global.Protocols.Tcp)
{
exclude.Protocol = Global.Protocols.Tcp.GetEnumDescription();
}
else if (int.Parse(temp["ip_proto"].ToString()) == (int)Global.Protocols.Udp)
{
exclude.Protocol = Global.Protocols.Udp.GetEnumDescription();
}
else if (int.Parse(temp["ip_proto"].ToString()) == (int)Global.Protocols.Icmp)
{
exclude.Protocol = Global.Protocols.Icmp.GetEnumDescription();
}
if (((byte[])temp["fp"])[0] == 48)
{
exclude.FalsePositive = false;
}
else
{
exclude.FalsePositive = true;
}
exclude.Timestamp = DateTime.Parse(temp["timestamp"].ToString());
excludes.Add(exclude);
}
listExcludes.SetObjects(excludes);
if (excludes.Count > 0)
{
listExcludes.SelectedObject = excludes[0];
}
}
ResizeFilterListColumns();
SetButtonState();
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox("An error occurred whilst loading the excludes" + ex.Message);
}
}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <param name="dateFrom"></param>
/// <param name="includeAcks"></param>
/// <param name="sourceIps"></param>
/// <param name="csv"></param>
public void QueryRuleIpsFrom(string id,
string dateFrom,
bool includeAcks,
bool sourceIps,
bool csv)
{
//if (IsRunning == true)
//{
// OnExclamation("Already performing query");
// return;
//}
IsRunning = true;
new Thread(() =>
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List <string> data = new List <string>();
if (includeAcks == true)
{
if (sourceIps == true)
{
string query = _sql.GetQuery(Sql.Query.SQL_RULES_SRC_IPS);
query = query.Replace("#WHERE#", @"WHERE event.signature=@0
AND event.timestamp > @1");
List <Event> temp = db.Fetch <Event>(query, new object[] { id, dateFrom });
foreach (var rule in temp)
{
data.Add(rule.IpSrcTxt);
}
}
else
{
string query = _sql.GetQuery(Sql.Query.SQL_RULES_DST_IPS);
query = query.Replace("#WHERE#", @"WHERE event.signature=@0
AND event.timestamp > @1");
List <Event> temp = db.Fetch <Event>(query, new object[] { id, dateFrom });
foreach (var rule in temp)
{
data.Add(rule.IpDstTxt);
}
}
}
else
{
if (sourceIps == true)
{
string query = _sql.GetQuery(Sql.Query.SQL_RULES_SRC_IPS);
query = query.Replace("#WHERE#", @"WHERE event.signature=@0
AND (acknowledgment.id IS NULL OR acknowledgment.id = 0 OR acknowledgment.class = 1)
AND event.timestamp > @1");
List <Event> temp = db.Fetch <Event>(query, new object[] { id, dateFrom });
foreach (var rule in temp)
{
data.Add(rule.IpSrcTxt);
}
}
else
{
string query = _sql.GetQuery(Sql.Query.SQL_RULES_DST_IPS);
query = query.Replace("#WHERE#", @"WHERE event.signature=@0
AND (acknowledgment.id IS NULL OR acknowledgment.id = 0 OR acknowledgment.class = 1)
AND event.timestamp > @1");
List <Event> temp = db.Fetch <Event>(query, new object[] { id, dateFrom });
foreach (var rule in temp)
{
data.Add(rule.IpDstTxt);
}
}
}
OnComplete(data, csv);
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the query: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <param name="id"></param>
public FormExcludeEdit(Sql sql, long id)
{
InitializeComponent();
_sql = sql;
_id = id;
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
var data = db.Fetch <Dictionary <string, object> >(_sql.GetQuery(Sql.Query.SQL_EXCLUDE), new object[] { _id });
if (data.Count == 0)
{
UserInterface.DisplayMessageBox(this, "Unable to locate exclude", MessageBoxIcon.Exclamation);
return;
}
ipSource.Text = data[0]["ip_src"].ToString();
if (data[0]["ip_dst"].ToString() != "0")
{
ipDestination.Text = data[0]["ip_dst"].ToString();
}
if (data[0]["port_src"].ToString() != "0")
{
txtSourcePort.Text = data[0]["port_src"].ToString();
}
if (data[0]["port_dst"].ToString() != "0")
{
txtDestinationPort.Text = data[0]["port_dst"].ToString();
}
if (int.Parse(data[0]["ip_proto"].ToString()) == (int)Global.Protocols.Tcp)
{
txtProtocol.Text = Global.Protocols.Tcp.GetEnumDescription();
}
else if (int.Parse(data[0]["ip_proto"].ToString()) == (int)Global.Protocols.Udp)
{
txtProtocol.Text = Global.Protocols.Udp.GetEnumDescription();
}
else if (int.Parse(data[0]["ip_proto"].ToString()) == (int)Global.Protocols.Icmp)
{
txtProtocol.Text = Global.Protocols.Icmp.GetEnumDescription();
}
txtRule.Text = data[0]["sig_name"].ToString();
txtComment.Text = data[0]["comment"].ToString();
if (((byte[])data[0]["fp"])[0] == 48)
{
chkFalsePositive.Checked = false;
}
else
{
chkFalsePositive.Checked = true;
}
}
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox("An error occurred whilst loading the exclude: " + ex.Message);
}
}
public override void FinalizeRun()
{
TotalProcessTime.Start();
//generate map HTML
string html = Utils.ResourceHelper.GetEmbeddedTextFile("/Resources/ActionShowOnlineMap.html");
//icons
StringBuilder sb = new StringBuilder();
sb.AppendLine(string.Format("var foundIcon = new google.maps.MarkerImage(\"{0}\");", Utils.ResourceHelper.GetEmbeddedHtmlImageData("/Resources/Map/found.png")));
sb.AppendLine(string.Format("var unknownIcon = new google.maps.MarkerImage(\"{0}\");", Utils.ResourceHelper.GetEmbeddedHtmlImageData("/Resources/Map/0.png")));
sb.AppendLine(string.Format("var myownIcon = new google.maps.MarkerImage(\"{0}\");", Utils.ResourceHelper.GetEmbeddedHtmlImageData("/Resources/Map/myown.png")));
foreach (var gctype in ApplicationData.Instance.GeocacheTypes)
{
sb.AppendLine(string.Format("var gct{0}Icon = new google.maps.MarkerImage(\"{1}\");", gctype.ID.ToString().Replace("-", "_"), Utils.ResourceHelper.GetEmbeddedHtmlImageData(string.Format("/Resources/Map/{0}.png", gctype.ID))));
sb.AppendLine(string.Format("var gct{0}IconC = new google.maps.MarkerImage(\"{1}\");", gctype.ID.ToString().Replace("-", "_"), Utils.ResourceHelper.GetEmbeddedHtmlImageData(string.Format("/Resources/Map/c{0}.png", gctype.ID))));
}
foreach (var wptype in ApplicationData.Instance.WaypointTypes)
{
sb.AppendLine(string.Format("var wpt{0}Icon = new google.maps.MarkerImage(\"{1}\");", wptype.ID.ToString().Replace("-", "_"), Utils.ResourceHelper.GetEmbeddedHtmlImageData(string.Format("/Resources/Map/{0}.gif", wptype.ID))));
}
html = html.Replace("//icons", sb.ToString());
sb.Length = 0;
using (var db = new NPoco.Database(DatabaseConnection.Connection, NPoco.DatabaseType.SQLite))
{
var gcl = db.Fetch <GeocachePoco>(string.Format("select Code, Name, CacheType, Found, IsOwner, Latitude, Longitude, kAfterLat, kAfterLon from Caches inner join {0} on Caches.Code={0}.gccode left join Corrected on Caches.Code=Corrected.kCode", ActionInputTableName));
foreach (var gc in gcl)
{
var gcicon = "gct0Icon";
if (gc.IsOwner != 0)
{
gcicon = "myownIcon";
}
else if (gc.Found != 0)
{
gcicon = "foundIcon";
}
else
{
var gctype = (from a in ApplicationData.Instance.GeocacheTypes where a.GSAK == gc.CacheType select a).FirstOrDefault();
if (gctype != null)
{
if (gc.kAfterLat != null)
{
gcicon = string.Format("gct{0}IconC", gctype.ID);
}
else
{
gcicon = string.Format("gct{0}Icon", gctype.ID);
}
}
}
sb.AppendFormat("markers.push(addClickListener(new MarkerWithLabel({{position: new google.maps.LatLng({1},{2}),icon:{3},title:'{0}',labelContent:'{0}',labelAnchor: new google.maps.Point(10, 0),labelClass:'labels'}})));", string.Format("{0}-{1}", gc.Code, gc.Name.Replace("'", "").Replace("\\", "")), gc.kAfterLat == null ? gc.Latitude : gc.kAfterLat, gc.kAfterLon == null ? gc.Longitude : gc.kAfterLon, gcicon);
}
sb.AppendLine();
sb.AppendLine("markerClusterer = new MarkerClusterer(map, markers, clusterOptions);");
if (gcl.Count > 0)
{
var dr = DatabaseConnection.ExecuteReader(string.Format("select AVG(Latitude), AVG(Longitude) from Caches inner join {0} on Caches.Code={0}.gccode left join Corrected on Caches.Code=Corrected.kCode", ActionInputTableName));
if (dr.Read())
{
var lat = dr.GetDouble(0);
var lon = dr.GetDouble(1);
sb.AppendLine(string.Format("map.setCenter(new google.maps.LatLng({0}, {1}));", lat.ToString(CultureInfo.InvariantCulture), lon.ToString(CultureInfo.InvariantCulture)));
}
}
}
html = html.Replace("//geocachelist", sb.ToString());
_context.Send(new SendOrPostCallback(delegate(object state)
{
var wnd = new Dialogs.WindowWebBrowser(html, new WebBrowserControl.JSCallback()
{
Name = "bound", Instance = new JSCallBack()
});
wnd.Show();
}), null);
TotalProcessTime.Stop();
base.FinalizeRun();
}
/// <summary>
///
/// </summary>
/// <param name="acknowledgementClass"></param>
private void SetAcknowledgement(string ackClass)
{
var events = listEvents.SelectedObjects.Cast<Event>().ToList();
if (cboRule.SelectedIndex == -1)
{
return;
}
Signature rule = (Signature)cboRule.Items[cboRule.SelectedIndex];
var acknowledgementClass = (from a in _acknowledgmentClasses where a.Desc.ToLower() == ackClass.ToLower() select a).SingleOrDefault();
if (acknowledgementClass == null)
{
UserInterface.DisplayMessageBox(this, "Cannot locate acknowledgement class", MessageBoxIcon.Exclamation);
return;
}
(new Thread(() =>
{
try
{
bool errors = false;
bool acknowledgedPrevious = false;
using (new HourGlass(this))
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
db.BeginTransaction();
foreach (Event temp in events)
{
try
{
bool insert = true;
var ack = db.Fetch<Acknowledgment>("select * from acknowledgment where cid=@0 and sid=@1", new object[] { temp.Cid, temp.Sid });
if (ack.Count() > 0)
{
if (ack.First().Initials != _initials)
{
acknowledgedPrevious = true;
insert = false;
}
else
{
db.Delete(ack.First());
}
}
if (insert == true)
{
Acknowledgment acknowledgment = new Acknowledgment();
acknowledgment.Cid = temp.Cid;
acknowledgment.Sid = temp.Sid;
acknowledgment.Initials = _initials;
acknowledgment.Notes = string.Empty;
acknowledgment.Class = acknowledgementClass.Id;
acknowledgment.Timestamp = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
db.Insert(acknowledgment);
}
}
catch (Exception ex)
{
db.AbortTransaction();
errors = true;
IO.WriteTextToFile("Acknowledgement Insert Error: " + ex.ToString() + Environment.NewLine,
System.IO.Path.Combine(Misc.GetUserDataDirectory(), "Errors.txt"),
true);
break;
}
}
if (errors == false)
{
db.CompleteTransaction();
}
}
if (acknowledgedPrevious == true)
{
UserInterface.DisplayMessageBox(this,
"Some events were not classified due to being already classified",
MessageBoxIcon.Exclamation);
}
if (errors == true)
{
UserInterface.DisplayMessageBox(this,
"Errors occured, check the Errors.txt file",
MessageBoxIcon.Exclamation);
}
LoadRuleEvents(_currentPage);
}
catch (Exception ex)
{
UserInterface.DisplayMessageBox(this,
"Errors occured, check the Errors.txt file",
MessageBoxIcon.Exclamation);
IO.WriteTextToFile("Acknowledgement Insert Error (" + DateTime.Now + "): " + ex.ToString() + Environment.NewLine,
System.IO.Path.Combine(Misc.GetUserDataDirectory(), "Errors.txt"),
true);
}
})).Start();
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void listEvents_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
ctxMenuPayload_Click(this, new EventArgs());
}
else if (e.KeyCode == Keys.F1)
{
if (_initials.Length == 0)
{
UserInterface.DisplayMessageBox(this,
"The user initials have not been set. Manually classify event to set",
MessageBoxIcon.Information);
return;
}
var events = listEvents.Objects.Cast<Event>().ToList();
(new Thread(() =>
{
SetProcessingStatus(false);
bool acknowledgedPrevious = false;
using (new HourGlass(this))
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
foreach (Event temp in events)
{
bool insert = true;
var ack = db.Fetch<Acknowledgment>("select * from acknowledgment where cid=@0 and sid=@1", new object[] { temp.Cid, temp.Sid });
if (ack.Count() > 0)
{
if (ack.First().Initials != _initials)
{
acknowledgedPrevious = true;
insert = false;
}
else
{
db.Delete(ack.First());
}
}
if (insert == true)
{
Acknowledgment acknowledgment = new Acknowledgment();
acknowledgment.Cid = temp.Cid;
acknowledgment.Sid = temp.Sid;
acknowledgment.Initials = _initials;
acknowledgment.Class = 1;
db.Insert(acknowledgment);
}
}
}
if (acknowledgedPrevious == true)
{
UserInterface.DisplayMessageBox(this,
"Some events were not classified due to being already classified",
MessageBoxIcon.Exclamation);
}
SetProcessingStatus(true);
LoadRuleEvents(_currentPage);
})).Start();
}
}
public override void FinalizeRun()
{
TotalProcessTime.Start();
//generate map HTML
string html = Utils.ResourceHelper.GetEmbeddedTextFile("/Resources/ActionShowOnlineMap.html");
//icons
StringBuilder sb = new StringBuilder();
sb.AppendLine(string.Format("var foundIcon = new google.maps.MarkerImage(\"{0}\");", Utils.ResourceHelper.GetEmbeddedHtmlImageData("/Resources/Map/found.png")));
sb.AppendLine(string.Format("var unknownIcon = new google.maps.MarkerImage(\"{0}\");", Utils.ResourceHelper.GetEmbeddedHtmlImageData("/Resources/Map/0.png")));
sb.AppendLine(string.Format("var myownIcon = new google.maps.MarkerImage(\"{0}\");", Utils.ResourceHelper.GetEmbeddedHtmlImageData("/Resources/Map/myown.png")));
foreach (var gctype in ApplicationData.Instance.GeocacheTypes)
{
sb.AppendLine(string.Format("var gct{0}Icon = new google.maps.MarkerImage(\"{1}\");", gctype.ID.ToString().Replace("-", "_"), Utils.ResourceHelper.GetEmbeddedHtmlImageData(string.Format("/Resources/Map/{0}.png", gctype.ID))));
sb.AppendLine(string.Format("var gct{0}IconC = new google.maps.MarkerImage(\"{1}\");", gctype.ID.ToString().Replace("-", "_"), Utils.ResourceHelper.GetEmbeddedHtmlImageData(string.Format("/Resources/Map/c{0}.png", gctype.ID))));
}
foreach (var wptype in ApplicationData.Instance.WaypointTypes)
{
sb.AppendLine(string.Format("var wpt{0}Icon = new google.maps.MarkerImage(\"{1}\");", wptype.ID.ToString().Replace("-", "_"), Utils.ResourceHelper.GetEmbeddedHtmlImageData(string.Format("/Resources/Map/{0}.gif", wptype.ID))));
}
html = html.Replace("//icons", sb.ToString());
sb.Length = 0;
using (var db = new NPoco.Database(DatabaseConnection.Connection, NPoco.DatabaseType.SQLite))
{
var gcl = db.Fetch<GeocachePoco>(string.Format("select Code, Name, CacheType, Found, IsOwner, Latitude, Longitude, kAfterLat, kAfterLon from Caches inner join {0} on Caches.Code={0}.gccode left join Corrected on Caches.Code=Corrected.kCode", ActionInputTableName));
foreach (var gc in gcl)
{
var gcicon = "gct0Icon";
if (gc.IsOwner != 0)
{
gcicon = "myownIcon";
}
else if (gc.Found != 0)
{
gcicon = "foundIcon";
}
else
{
var gctype = (from a in ApplicationData.Instance.GeocacheTypes where a.GSAK == gc.CacheType select a).FirstOrDefault();
if (gctype != null)
{
if (gc.kAfterLat != null)
{
gcicon = string.Format("gct{0}IconC", gctype.ID);
}
else
{
gcicon = string.Format("gct{0}Icon", gctype.ID);
}
}
}
sb.AppendFormat("markers.push(addClickListener(new MarkerWithLabel({{position: new google.maps.LatLng({1},{2}),icon:{3},title:'{0}',labelContent:'{0}',labelAnchor: new google.maps.Point(10, 0),labelClass:'labels'}})));", string.Format("{0}-{1}", gc.Code, gc.Name.Replace("'", "").Replace("\\", "")), gc.kAfterLat == null ? gc.Latitude : gc.kAfterLat, gc.kAfterLon == null ? gc.Longitude : gc.kAfterLon, gcicon);
}
sb.AppendLine();
sb.AppendLine("markerClusterer = new MarkerClusterer(map, markers, clusterOptions);");
if (gcl.Count > 0)
{
var dr = DatabaseConnection.ExecuteReader(string.Format("select AVG(Latitude), AVG(Longitude) from Caches inner join {0} on Caches.Code={0}.gccode left join Corrected on Caches.Code=Corrected.kCode", ActionInputTableName));
if (dr.Read())
{
var lat = dr.GetDouble(0);
var lon = dr.GetDouble(1);
sb.AppendLine(string.Format("map.setCenter(new google.maps.LatLng({0}, {1}));", lat.ToString(CultureInfo.InvariantCulture), lon.ToString(CultureInfo.InvariantCulture)));
}
}
}
html = html.Replace("//geocachelist", sb.ToString());
_context.Send(new SendOrPostCallback(delegate(object state)
{
var wnd = new Dialogs.WindowWebBrowser(html, new WebBrowserControl.JSCallback() { Name = "bound", Instance = new JSCallBack() });
wnd.Show();
}), null);
TotalProcessTime.Stop();
base.FinalizeRun();
}
/// <summary>
///
/// </summary>
public void QuerySensors()
{
//if (IsRunning == true)
//{
// OnExclamation("Already performing query");
// return;
//}
IsRunning = true;
new Thread(() =>
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List<Sensor> sensors = db.Fetch<Sensor>(_sql.GetQuery(Sql.Query.SQL_SENSORS));
long count = 0;
foreach (Sensor sensor in sensors)
{
count += sensor.EventCount;
}
foreach (Sensor sensor in sensors)
{
if (sensor.EventCount > 0)
{
sensor.EventPercentage = (int)(sensor.EventCount / count) * 100;
}
}
OnComplete(sensors);
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the query: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
/// <summary>
///
/// </summary>
/// <param name="offset"></param>
/// <param name="pageLimit"></param>
public void QuerySearch(string where,
object[] args)
{
//if (IsRunning == true)
//{
// OnExclamation("Already performing query");
// return;
//}
IsRunning = true;
new Thread(() =>
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List<Event> data = db.Fetch<Event>(_sql.GetQuery(Sql.Query.SQL_EVENTS_SEARCH) + where, args);
data = Helper.ProcessEventDataSet(data);
OnComplete(data);
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the query: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
/// <summary>
///
/// </summary>
/// <param name="offset"></param>
/// <param name="pageLimit"></param>
public void QueryEventsRulesFromTo(string dateFrom,
string dateTo,
string sid,
long offset,
int pageLimit,
bool includeAcks)
{
//if (IsRunning == true)
//{
// //OnExclamation("Already performing query");
// return;
//}
IsRunning = true;
new Thread(() =>
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List<Event> temp;
string query = _sql.GetQuery(Sql.Query.SQL_RULES_EVENTS);
if (includeAcks == true)
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_id = @2 LIMIT @3, @4");
}
else
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND (acknowledgment.id IS NULL OR acknowledgment.id = 0 OR acknowledgment.class = 1)
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_id = @2 LIMIT @3, @4");
}
temp = db.Fetch<Event>(query, new object[] { dateFrom, dateTo, sid, offset, pageLimit });
temp = Helper.ProcessEventDataSet(temp);
OnComplete(temp);
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the query: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
protected override void PerformExport(object settings)
{
var gpxSetting = settings as ExportGPXSettings;
if (gpxSetting != null && !string.IsNullOrEmpty(gpxSetting.FileName))
{
bool canceled = false;
try
{
using (var db = new NPoco.Database(this.DatabaseConnection.Connection, NPoco.DatabaseType.SQLite))
{
double minLat = 0, minLon = 0, maxLat = 0, maxLon = 0;
if (DatabaseConnection.CurrentDataReader != null && !DatabaseConnection.CurrentDataReader.IsClosed)
{
DatabaseConnection.CurrentDataReader.Close();
}
var gcList = db.Fetch <GeocacheEntryInfo>(string.Format("select Code, Name, CacheType, Difficulty, Terrain, Found, Container, Latitude, Longitude, kAfterLat, kAfterLon from Caches inner join {0} on Caches.Code={0}.gccode left join Corrected on Caches.Code = Corrected.kCode", ActionInputTableName));
minLat = (from a in gcList select Utils.Conversion.StringToDouble(a.Latitude)).Min();
maxLat = (from a in gcList select Utils.Conversion.StringToDouble(a.Latitude)).Max();
minLon = (from a in gcList select Utils.Conversion.StringToDouble(a.Longitude)).Min();
maxLon = (from a in gcList select Utils.Conversion.StringToDouble(a.Longitude)).Max();
DateTime dt = DateTime.Now.AddSeconds(2);
using (Utils.ProgressBlock progress = new Utils.ProgressBlock("ExportingGPX", "CreatingFile", gcList.Count, 0, true))
{
using (System.IO.TemporaryFile gpxFile = new System.IO.TemporaryFile(false))
{
using (ZipOutputStream s = new ZipOutputStream(System.IO.File.Create(gpxFile.Path)))
{
s.SetLevel(9); // 0-9, 9 being the highest compression
s.UseZip64 = UseZip64.Off;
int totalGeocaches = gcList.Count;
int totalProcessed = 0;
int fileIndex = 1;
int geocacheIndex = 0;
int gpxSizeLimit = 4500000; //appr. 4.5MB
XmlDocument doc = new XmlDocument();
XmlDeclaration pi = doc.CreateXmlDeclaration("1.0", "UTF-8", "yes");
doc.InsertBefore(pi, doc.DocumentElement);
XmlElement root = doc.CreateElement("ggz");
doc.AppendChild(root);
XmlAttribute attr = doc.CreateAttribute("xmlns");
XmlText txt = doc.CreateTextNode("http://www.opencaching.com/xmlschemas/ggz/1/0");
attr.AppendChild(txt);
root.Attributes.Append(attr);
XmlElement el = doc.CreateElement("time");
txt = doc.CreateTextNode(string.Format("{0}Z", DateTime.Now.ToUniversalTime().ToString("s")));
el.AppendChild(txt);
root.AppendChild(el);
Utils.GPXGenerator gpxGenerator = new Utils.GPXGenerator(
db
, (from a in gcList select a.Code).ToList()
, gpxSetting.Version
, minLat
, maxLat
, minLon
, maxLon
);
while (gcList.Count > 0)
{
XmlElement elFile = doc.CreateElement("file");
root.AppendChild(elFile);
el = doc.CreateElement("name");
txt = doc.CreateTextNode(string.Format("{0}_{1}.gpx", System.IO.Path.GetFileNameWithoutExtension(gpxSetting.FileName), fileIndex));
el.AppendChild(txt);
elFile.AppendChild(el);
XmlElement elCRC = doc.CreateElement("crc");
elFile.AppendChild(elCRC);
el = doc.CreateElement("time");
txt = doc.CreateTextNode(string.Format("{0}Z", DateTime.Now.ToUniversalTime().ToString("s")));
el.AppendChild(txt);
elFile.AppendChild(el);
//create GPX wpt entries until max size is reached
List <GeocacheEntryInfo> gpxBatchList = new List <GeocacheEntryInfo>();
List <GeocacheEntryInfo> geiList = new List <GeocacheEntryInfo>();
geocacheIndex = 0;
minLat = (from a in gcList select Utils.Conversion.StringToDouble(a.Latitude)).Min();
maxLat = (from a in gcList select Utils.Conversion.StringToDouble(a.Latitude)).Max();
minLon = (from a in gcList select Utils.Conversion.StringToDouble(a.Longitude)).Min();
maxLon = (from a in gcList select Utils.Conversion.StringToDouble(a.Longitude)).Max();
gpxGenerator.SetGeocacheList((from a in gcList select a.Code).ToList(), minLat, maxLat, minLon, maxLon);
StringBuilder sb = new StringBuilder();
gpxGenerator.Start();
while (sb.Length < gpxSizeLimit && geocacheIndex < gpxGenerator.Count)
{
gpxBatchList.Add(gcList[geocacheIndex]);
string gpxText = gpxGenerator.Next();
gcList[geocacheIndex].FileLen = System.Text.UTF8Encoding.UTF8.GetBytes(gpxText).Length + 2;
geiList.Add(gcList[geocacheIndex]);
sb.AppendLine(gpxText);
totalProcessed++;
geocacheIndex++;
if (DateTime.Now >= dt)
{
if (!progress.Update("CreatingFile", totalGeocaches, totalProcessed))
{
canceled = true;
break;
}
dt = DateTime.Now.AddSeconds(2);
}
}
sb.AppendLine(gpxGenerator.Finish());
//insert gpx header
minLat = (from a in gpxBatchList select Utils.Conversion.StringToDouble(a.Latitude)).Min();
maxLat = (from a in gpxBatchList select Utils.Conversion.StringToDouble(a.Latitude)).Max();
minLon = (from a in gpxBatchList select Utils.Conversion.StringToDouble(a.Longitude)).Min();
maxLon = (from a in gpxBatchList select Utils.Conversion.StringToDouble(a.Longitude)).Max();
gpxGenerator.SetGeocacheList((from a in gpxBatchList select a.Code).ToList(), minLat, maxLat, minLon, maxLon);
string gpxHeader = gpxGenerator.Start();
sb.Insert(0, gpxHeader);
gcList.RemoveRange(0, gpxBatchList.Count);
//add gpx to zip
byte[] data;
using (System.IO.TemporaryFile tmp = new System.IO.TemporaryFile(true))
{
using (System.IO.StreamWriter sw = System.IO.File.CreateText(tmp.Path))
{
sw.Write(sb.ToString());
}
data = File.ReadAllBytes(tmp.Path);
}
string fn = string.Format("data/{0}_{1}.gpx", System.IO.Path.GetFileNameWithoutExtension(gpxSetting.FileName), fileIndex);
ZipEntry entry = new ZipEntry(fn);
entry.DateTime = DateTime.Now;
s.PutNextEntry(entry);
s.Write(data, 0, data.Length);
Crc32 crc = new Crc32();
crc.Update(data);
//txt = doc.CreateTextNode(crc16.ComputeChecksum(data).ToString("X8"));
txt = doc.CreateTextNode(crc.Value.ToString("X8"));
elCRC.AppendChild(txt);
int curPos = System.Text.UTF8Encoding.UTF8.GetBytes(gpxHeader).Length;
for (int i = 0; i < geiList.Count; i++)
{
GeocacheEntryInfo gei = geiList[i];
XmlElement chgEl = doc.CreateElement("gch");
elFile.AppendChild(chgEl);
el = doc.CreateElement("code");
txt = doc.CreateTextNode(gei.Code ?? "");
el.AppendChild(txt);
chgEl.AppendChild(el);
el = doc.CreateElement("name");
txt = doc.CreateTextNode(gpxGenerator.validateXml(gei.Name ?? ""));
el.AppendChild(txt);
chgEl.AppendChild(el);
el = doc.CreateElement("type");
txt = doc.CreateTextNode((from a in ApplicationData.Instance.GeocacheTypes where a.GSAK == gei.CacheType select a.GPXTag).FirstOrDefault() ?? "");
el.AppendChild(txt);
chgEl.AppendChild(el);
el = doc.CreateElement("lat");
txt = doc.CreateTextNode(gei.kAfterLat ?? gei.Latitude);
el.AppendChild(txt);
chgEl.AppendChild(el);
el = doc.CreateElement("lon");
txt = doc.CreateTextNode(gei.kAfterLon ?? gei.Longitude);
el.AppendChild(txt);
chgEl.AppendChild(el);
el = doc.CreateElement("file_pos");
txt = doc.CreateTextNode(curPos.ToString());
curPos += gei.FileLen;
el.AppendChild(txt);
chgEl.AppendChild(el);
el = doc.CreateElement("file_len");
txt = doc.CreateTextNode(gei.FileLen.ToString());
el.AppendChild(txt);
chgEl.AppendChild(el);
XmlElement ratingsEl = doc.CreateElement("ratings");
chgEl.AppendChild(ratingsEl);
el = doc.CreateElement("awesomeness");
txt = doc.CreateTextNode("3.0");
el.AppendChild(txt);
ratingsEl.AppendChild(el);
el = doc.CreateElement("difficulty");
txt = doc.CreateTextNode(gei.Difficulty.ToString("0.#").Replace(',', '.'));
el.AppendChild(txt);
ratingsEl.AppendChild(el);
el = doc.CreateElement("size");
switch ((from a in ApplicationData.Instance.GeocacheContainers where a.Name == gei.Container select a.ID).FirstOrDefault())
{
case 1:
txt = doc.CreateTextNode("2.0");
break;
case 5:
txt = doc.CreateTextNode("2.0");
break;
case 6:
txt = doc.CreateTextNode("2.0");
break;
case 2:
txt = doc.CreateTextNode("2.0");
break;
case 3:
txt = doc.CreateTextNode("4.0");
break;
case 4:
txt = doc.CreateTextNode("5.0");
break;
case 8:
txt = doc.CreateTextNode("3.0");
break;
default:
txt = doc.CreateTextNode("3.0");
break;
}
el.AppendChild(txt);
ratingsEl.AppendChild(el);
el = doc.CreateElement("terrain");
txt = doc.CreateTextNode(gei.Terrain.ToString("0.#").Replace(',', '.'));
el.AppendChild(txt);
ratingsEl.AppendChild(el);
if (gei.Found != 0)
{
el = doc.CreateElement("found");
txt = doc.CreateTextNode("true");
el.AppendChild(txt);
chgEl.AppendChild(el);
}
}
fileIndex++;
}
//add index file
// index\com\garmin\geocaches\v0\index.xml
/*
* <gch>
* <code>GC12345</code>
* <name>Cache name</name>
* <type>Traditional Cache</type>
* <lat>33.550217</lat>
* <lon>-117.660617</lon>
* <file_pos>5875</file_pos>
* <file_len>5783</file_len>
* <ratings>
* <awesomeness>3.0</awesomeness>
* <difficulty>1.5</difficulty>
* <size>5.0</size>
* <terrain>1.5</terrain>
* </ratings>
* <found>true</found>
* </gch>
*
* 1 = Nano (not supported, unfortunately, by GC.com yet)
* 2 = Micro
* 3 = Small
* 4 = Regular
* 5 = Large
*
*/
using (System.IO.TemporaryFile tmp = new System.IO.TemporaryFile(true))
{
using (TextWriter sw = new StreamWriter(tmp.Path, false, Encoding.UTF8)) //Set encoding
{
doc.Save(sw);
}
byte[] data = File.ReadAllBytes(tmp.Path);
ZipEntry entry = new ZipEntry("index/com/garmin/geocaches/v0/index.xml");
entry.DateTime = DateTime.Now;
s.PutNextEntry(entry);
s.Write(data, 0, data.Length);
}
s.Finish();
s.Close();
}
if (!canceled)
{
System.IO.File.Copy(gpxFile.Path, gpxSetting.FileName, true);
}
}
}
}
}
catch
{
}
}
}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <param name="dateFrom"></param>
/// <param name="dateTo"></param>
/// <param name="sourceIps"></param>
public void QueryRuleIpsFromTo(string id,
string dateFrom,
string dateTo,
bool includeAcks,
bool sourceIps,
bool csv)
{
//if (IsRunning == true)
//{
// OnExclamation("Already performing query");
// return;
//}
IsRunning = true;
new Thread(() =>
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List<string> data = new List<string>();
if (includeAcks == true)
{
if (sourceIps == true)
{
string query = _sql.GetQuery(Sql.Query.SQL_RULES_SRC_IPS);
query = query.Replace("#WHERE#", @"WHERE event.signature=@0
AND event.timestamp > @1
AND event.timestamp < @2");
List<Event> temp = db.Fetch<Event>(query, new object[] { id, dateFrom, dateTo });
foreach (var rule in temp)
{
data.Add(rule.IpSrcTxt);
}
}
else
{
string query = _sql.GetQuery(Sql.Query.SQL_RULES_DST_IPS);
query = query.Replace("#WHERE#", @"WHERE event.signature=@0
AND event.timestamp > @1
AND event.timestamp < @2");
List<Event> temp = db.Fetch<Event>(query, new object[] { id, dateFrom, dateTo });
foreach (var rule in temp)
{
data.Add(rule.IpDstTxt);
}
}
}
else
{
if (sourceIps == true)
{
string query = _sql.GetQuery(Sql.Query.SQL_RULES_SRC_IPS);
query = query.Replace("#WHERE#", @"WHERE event.signature=@0
AND (acknowledgment.id IS NULL OR acknowledgment.id = 0 OR acknowledgment.class = 1)
AND event.timestamp > @1
AND event.timestamp < @2");
List<Event> temp = db.Fetch<Event>(query, new object[] { id, dateFrom, dateTo });
foreach (var rule in temp)
{
data.Add(rule.IpSrcTxt);
}
}
else
{
string query = _sql.GetQuery(Sql.Query.SQL_RULES_DST_IPS);
query = query.Replace("#WHERE#", @"WHERE event.signature=@0
AND (acknowledgment.id IS NULL OR acknowledgment.id = 0 OR acknowledgment.class = 1)
AND event.timestamp > @1
AND event.timestamp < @2");
List<Event> temp = db.Fetch<Event>(query, new object[] { id, dateFrom, dateTo });
foreach (var rule in temp)
{
data.Add(rule.IpDstTxt);
}
}
}
OnComplete(data, csv);
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the query: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
/// <summary>
///
/// </summary>
/// <param name="dateFrom"></param>
/// <param name="dateTo"></param>
/// <param name="priority"></param>
/// <param name="hostName"></param>
/// <param name="includeAcks"></param>
/// <param name="check"></param>
public void QueryRulesFromToPriority(string dateFrom,
string dateTo,
string priority,
string hostName,
bool includeAcks,
bool check)
{
//if (IsRunning == true)
//{
// //OnExclamation("Already performing query");
// return;
//}
IsRunning = true;
new Thread(() =>
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List<Signature> temp;
string query = _sql.GetQuery(Sql.Query.SQL_RULES);
if (includeAcks == true)
{
if (hostName == string.Empty)
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_priority = @2");
temp = db.Fetch<Signature>(query, new object[] { dateFrom, dateTo, priority });
}
else
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_priority = @2
AND event.sid IN (SELECT sid FROM sensor WHERE hostname=@3)");
temp = db.Fetch<Signature>(query, new object[] { dateFrom, dateTo, priority, hostName });
}
}
else
{
if (hostName == string.Empty)
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND (acknowledgment.id IS NULL OR acknowledgment.id = 0 OR acknowledgment.class = 1)
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_priority = @2");
temp = db.Fetch<Signature>(query, new object[] { dateFrom, dateTo, priority });
}
else
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND (acknowledgment.id IS NULL OR acknowledgment.id = 0 OR acknowledgment.class = 1)
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_priority = @2
AND event.sid IN (SELECT sid FROM sensor WHERE hostname=@3)");
temp = db.Fetch<Signature>(query, new object[] { dateFrom, dateTo, priority, hostName });
}
}
foreach (var rule in temp)
{
rule.Text = rule.Name + " (SID: " + rule.Sid.ToString() + "): " + rule.Count.ToString();
}
OnComplete(temp, check);
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the query: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
private async void Window_Initialized(object sender, EventArgs e)
{
var pa = ProgressCommandLineArguments(Environment.GetCommandLineArgs());
if (!string.IsNullOrEmpty(pa.Database))
{
var d = (from a in AvailableDatabases where string.Compare(a, pa.Database, true) == 0 select a).FirstOrDefault();
if (d != null)
{
Settings.Settings.Default.SelectedDatabase = d;
}
else
{
System.Windows.MessageBox.Show(string.Format("Database '{0}' not found", pa.Database), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
Settings.Settings.Default.ActiveGeocacheCode = pa.GeocacheCode;
if (!string.IsNullOrEmpty(pa.Flow))
{
var fl = (from a in UIControls.ActionBuilder.Manager.Instance.ActionFlows where string.Compare(a.Name, pa.Flow, true) == 0 select a).FirstOrDefault();
if (fl != null)
{
flowBuilder.ActiveActionFlow = fl;
await UIControls.ActionBuilder.Manager.Instance.RunActionFow(fl);
Close();
}
else
{
System.Windows.MessageBox.Show(string.Format("Flow '{0}' not found", pa.Database), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
Close();
}
}
else if (!string.IsNullOrEmpty(pa.Sequence))
{
var sq = (from a in FlowSequences.Manager.Instance.FlowSequences where string.Compare(a.Name, pa.Sequence, true) == 0 select a).FirstOrDefault();
if (sq != null)
{
ActiveFlowSequence = sq;
await FlowSequences.Manager.Instance.RunFowSequence(sq);
Close();
}
else
{
System.Windows.MessageBox.Show(string.Format("Sequence '{0}' not found", pa.Database), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
Close();
}
}
else if (!string.IsNullOrEmpty(pa.Function))
{
switch (pa.Function)
{
case "FormulaSolver":
{
var dlg = new Dialogs.WindowFormulaSolver();
dlg.ShowDialog();
}
break;
case "AddToCollection":
{
var dlg = new Dialogs.WindowAddToGeocacheCollection();
dlg.ShowDialog();
}
break;
case "OfflineOSMMapActive":
{
List <GSAKWrapper.MapProviders.GeocachePoco> gcl = null;
double?cLat = null;
double?cLon = null;
var fn = System.IO.Path.Combine(Settings.Settings.Default.DatabaseFolderPath, Settings.Settings.Default.SelectedDatabase, "sqlite.db3");
if (System.IO.File.Exists(fn))
{
using (var tempdb = new Database.DBConSqlite(fn))
{
using (var db = new NPoco.Database(tempdb.Connection, NPoco.DatabaseType.SQLite))
{
gcl = db.Fetch <GSAKWrapper.MapProviders.GeocachePoco>("select Code, Name, CacheType, Found, IsOwner, Latitude, Longitude, kAfterLat, kAfterLon from Caches left join Corrected on Caches.Code=Corrected.kCode where Caches.Code=@0", pa.GeocacheCode);
}
if (gcl.Count > 0)
{
if (!string.IsNullOrEmpty(gcl[0].kAfterLat) && !string.IsNullOrEmpty(gcl[0].kAfterLon))
{
cLat = double.Parse(gcl[0].kAfterLat, CultureInfo.InvariantCulture);
cLon = double.Parse(gcl[0].kAfterLon, CultureInfo.InvariantCulture);
}
else
{
cLat = double.Parse(gcl[0].Latitude, CultureInfo.InvariantCulture);
cLon = double.Parse(gcl[0].Longitude, CultureInfo.InvariantCulture);
}
var wnd = new Dialogs.WindowOSMOfflineMap(gcl, cLat, cLon, 18);
wnd.ShowDialog();
}
else
{
System.Windows.MessageBox.Show(string.Format("Geocache '{0}' not found", pa.GeocacheCode), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
}
}
break;
}
Close();
}
else
{
#if DEBUG
//if (Settings.Settings.Default.VersionCheckedAtDay != DateTime.Now.Day)
#else
if (Settings.Settings.Default.VersionCheckedAtDay != DateTime.Now.Day)
#endif
{
var thrd = new Thread(new ThreadStart(this.CheckForNewVersionThreadMethod));
thrd.IsBackground = true;
thrd.Start();
}
}
}
/// <summary>
///
/// </summary>
private void LoadExcludes()
{
try
{
using (new HourGlass(this))
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
listExcludes.ClearObjects();
var data = db.Fetch <Dictionary <string, object> >(_sql.GetQuery(Sql.Query.SQL_EXCLUDES));
List <Exclude> excludes = new List <Exclude>();
foreach (Dictionary <string, object> temp in data)
{
Exclude exclude = new Exclude();
exclude.Id = long.Parse(temp["id"].ToString());
exclude.SigId = long.Parse(temp["sig_id"].ToString());
exclude.SigSid = long.Parse(temp["sig_sid"].ToString());
exclude.Rule = temp["sig_name"].ToString();
exclude.Comment = temp["comment"].ToString();
exclude.SourceIpText = temp["ip_src"].ToString();
exclude.SourcePortText = temp["port_src"].ToString();
exclude.DestinationIpText = temp["ip_dst"].ToString();
exclude.DestinationPortText = temp["port_dst"].ToString();
if (int.Parse(temp["ip_proto"].ToString()) == (int)Global.Protocols.Tcp)
{
exclude.Protocol = Global.Protocols.Tcp.GetEnumDescription();
}
else if (int.Parse(temp["ip_proto"].ToString()) == (int)Global.Protocols.Udp)
{
exclude.Protocol = Global.Protocols.Udp.GetEnumDescription();
}
else if (int.Parse(temp["ip_proto"].ToString()) == (int)Global.Protocols.Icmp)
{
exclude.Protocol = Global.Protocols.Icmp.GetEnumDescription();
}
if (((byte[])temp["fp"])[0] == 48)
{
exclude.FalsePositive = false;
}
else
{
exclude.FalsePositive = true;
}
exclude.Timestamp = DateTime.Parse(temp["timestamp"].ToString());
excludes.Add(exclude);
}
listExcludes.SetObjects(excludes);
if (excludes.Count > 0)
{
listExcludes.SelectedObject = excludes[0];
}
}
ResizeFilterListColumns();
SetButtonState();
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox("An error occurred whilst loading the excludes" + ex.Message);
}
}
/// <summary>
///
/// </summary>
private void LoadSessions()
{
MethodInvoker methodInvoker = delegate
{
using (new HourGlass(this))
{
this.UpdateStatusBar("Loading sessions...");
List<Session> sessions = new List<Session>();
using (DbConnection connection = Db.GetOpenConnection(this.dataDirectory))
using (var db = new NPoco.Database(connection, NPoco.DatabaseType.SQLCe))
{
try
{
foreach (var session in db.Fetch<Session>("SELECT * FROM Sessions"))
{
sessions.Add(session);
}
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox(this, "An error occurred whilst retrieving the session records: " + ex.Message);
}
}
listSession.SetObjects(sessions);
if (listSession.Items.Count > 0)
{
listSession.SelectedObject = sessions[0];
}
olvcSourceIp.AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
olvcSourcePort.AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize);
olvcSourceCountry.AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize);
olvcDestinationIp.AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
olvcDestinationPort.AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize);
olvcDestinationCountry.AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize);
olvcSize.AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
olvcHttpHost.AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
olvcHttpMethods.AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
olvcTimestampFirstPacket.AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
olvcTimestampLastPacket.AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
this.UpdateStatusBar(string.Empty);
}
};
if (this.InvokeRequired == true)
{
this.BeginInvoke(methodInvoker);
}
else
{
methodInvoker.Invoke();
}
}
/// <summary>
///
/// </summary>
/// <param name="dateFrom"></param>
/// <param name="dateTo"></param>
/// <param name="priority"></param>
/// <param name="hostName"></param>
/// <param name="includeAcks"></param>
/// <param name="check"></param>
public void QueryRulesFromToPriority(string dateFrom,
string dateTo,
string priority,
string hostName,
bool includeAcks,
bool check)
{
//if (IsRunning == true)
//{
// //OnExclamation("Already performing query");
// return;
//}
IsRunning = true;
new Thread(() =>
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List <Signature> temp;
string query = _sql.GetQuery(Sql.Query.SQL_RULES);
if (includeAcks == true)
{
if (hostName == string.Empty)
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_priority = @2");
temp = db.Fetch <Signature>(query, new object[] { dateFrom, dateTo, priority });
}
else
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_priority = @2
AND event.sid IN (SELECT sid FROM sensor WHERE hostname=@3)");
temp = db.Fetch <Signature>(query, new object[] { dateFrom, dateTo, priority, hostName });
}
}
else
{
if (hostName == string.Empty)
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND (acknowledgment.id IS NULL OR acknowledgment.id = 0 OR acknowledgment.class = 1)
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_priority = @2");
temp = db.Fetch <Signature>(query, new object[] { dateFrom, dateTo, priority });
}
else
{
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND (acknowledgment.id IS NULL OR acknowledgment.id = 0 OR acknowledgment.class = 1)
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_priority = @2
AND event.sid IN (SELECT sid FROM sensor WHERE hostname=@3)");
temp = db.Fetch <Signature>(query, new object[] { dateFrom, dateTo, priority, hostName });
}
}
foreach (var rule in temp)
{
rule.Text = rule.Name + " (SID: " + rule.Sid.ToString() + "): " + rule.Count.ToString();
}
OnComplete(temp, check);
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the query: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
protected override void PerformExport(object settings)
{
var gpxSetting = settings as ExportGPXSettings;
if (gpxSetting != null && !string.IsNullOrEmpty(gpxSetting.FileName))
{
bool canceled = false;
try
{
using (var db = new NPoco.Database(this.DatabaseConnection.Connection, NPoco.DatabaseType.SQLite))
{
double minLat = 0, minLon = 0, maxLat = 0, maxLon = 0;
var dr = DatabaseConnection.ExecuteReader(string.Format("select Min(Latitude), Max(Latitude), Min(Longitude), Max(Longitude) from Caches inner join {0} on Caches.Code={0}.gccode", ActionInputTableName));
if (dr.Read())
{
minLat = Utils.Conversion.StringToDouble(dr.GetString(0));
maxLat = Utils.Conversion.StringToDouble(dr.GetString(1));
minLon = Utils.Conversion.StringToDouble(dr.GetString(2));
maxLon = Utils.Conversion.StringToDouble(dr.GetString(3));
}
dr.Close();
var gcList = db.Fetch<string>(string.Format("select gccode from {0}", ActionInputTableName));
using (Utils.ProgressBlock progress = new Utils.ProgressBlock("ExportingGPX", "CreatingFile", gcList.Count, 0, true))
{
using (System.IO.TemporaryFile gpxFile = new System.IO.TemporaryFile(false))
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(gpxFile.Path, false, Encoding.UTF8))
{
Utils.GPXGenerator gpxGenerator = new Utils.GPXGenerator(
db
, gcList
, gpxSetting.Version
, minLat
, maxLat
, minLon
, maxLon
);
DateTime nextUpdate = DateTime.Now.AddSeconds(1);
//generate header
sw.Write(gpxGenerator.Start());
//preserve mem and do for each cache the export
for (int i = 0; i < gpxGenerator.Count; i++)
{
//write parent
sw.WriteLine(gpxGenerator.Next());
if (gpxSetting.AddChildWaypoints)
{
//write child waypoints
string s = gpxGenerator.WaypointData();
if (!string.IsNullOrEmpty(s))
{
sw.WriteLine(s);
}
}
if (DateTime.Now >= nextUpdate)
{
if (!progress.Update("CreatingFile", gpxGenerator.Count, i + 1))
{
canceled = true;
break;
}
nextUpdate = DateTime.Now.AddSeconds(1);
}
}
//finalize
sw.Write(gpxGenerator.Finish());
}
if (!canceled)
{
if (gpxSetting.FileName.ToLower().EndsWith(".zip"))
{
using (FileStream zipToOpen = new FileStream(gpxSetting.FileName, FileMode.Create))
{
using (ZipArchive archive = new ZipArchive(zipToOpen, ZipArchiveMode.Create))
{
ZipArchiveEntry gpxEntry = archive.CreateEntry("geocaches.gpx");
using (StreamWriter writer = new StreamWriter(gpxEntry.Open()))
{
writer.Write(File.ReadAllText(gpxFile.Path));
}
}
}
}
else
{
System.IO.File.Copy(gpxFile.Path, gpxSetting.FileName, true);
}
}
}
}
}
}
catch
{
}
}
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <param name="id"></param>
public FormExcludeEdit(Sql sql, long id)
{
InitializeComponent();
_sql = sql;
_id = id;
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
var data = db.Fetch<Dictionary<string, object>>(_sql.GetQuery(Sql.Query.SQL_EXCLUDE), new object[] { _id });
if (data.Count == 0)
{
UserInterface.DisplayMessageBox(this, "Unable to locate exclude", MessageBoxIcon.Exclamation);
return;
}
ipSource.Text = data[0]["ip_src"].ToString();
if (data[0]["ip_dst"].ToString() != "0")
{
ipDestination.Text = data[0]["ip_dst"].ToString();
}
if (data[0]["port_src"].ToString() != "0")
{
txtSourcePort.Text = data[0]["port_src"].ToString();
}
if (data[0]["port_dst"].ToString() != "0")
{
txtDestinationPort.Text = data[0]["port_dst"].ToString();
}
if (int.Parse(data[0]["ip_proto"].ToString()) == (int)Global.Protocols.Tcp)
{
txtProtocol.Text = Global.Protocols.Tcp.GetEnumDescription();
}
else if (int.Parse(data[0]["ip_proto"].ToString()) == (int)Global.Protocols.Udp)
{
txtProtocol.Text = Global.Protocols.Udp.GetEnumDescription();
}
else if (int.Parse(data[0]["ip_proto"].ToString()) == (int)Global.Protocols.Icmp)
{
txtProtocol.Text = Global.Protocols.Icmp.GetEnumDescription();
}
txtRule.Text = data[0]["sig_name"].ToString();
txtComment.Text = data[0]["comment"].ToString();
if (((byte[])data[0]["fp"])[0] == 48)
{
chkFalsePositive.Checked = false;
}
else
{
chkFalsePositive.Checked = true;
}
}
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox("An error occurred whilst loading the exclude: " + ex.Message);
}
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
public void SetConnection(Connection connection)
{
_connection = connection;
UpdateSensors();
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
_acknowledgmentClasses = db.Fetch<AcknowledgmentClass>();
_acknowledgmentClasses = (from a in _acknowledgmentClasses orderby a.Desc select a).ToList();
}
}
/// <summary>
///
/// </summary>
private void Process()
{
AcknowledgmentClass acknowledgmentClass = (AcknowledgmentClass)cboClassification.Items[cboClassification.SelectedIndex];
string initials = txtInitials.Text.ToUpper();
string notes = txtNotes.Text;
bool successful = chkSuccessful.Checked;
btnOk.Enabled = false;
btnCancel.Enabled = false;
(new Thread(() =>
{
try
{
bool acknowledgedPrevious = false;
bool errors = false;
using (new HourGlass(this))
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
db.BeginTransaction();
foreach (Event temp in _events)
{
try
{
bool insert = true;
var ack = db.Fetch <Acknowledgment>("select * from acknowledgment where cid=@0 and sid=@1", new object[] { temp.Cid, temp.Sid });
if (ack.Count() > 0)
{
if (ack.First().Initials.ToUpper() != initials)
{
acknowledgedPrevious = true;
insert = false;
}
else
{
db.Delete(ack.First());
}
}
if (insert == true)
{
Acknowledgment acknowledgment = new Acknowledgment();
acknowledgment.Cid = temp.Cid;
acknowledgment.Sid = temp.Sid;
acknowledgment.Initials = initials;
acknowledgment.Notes = notes;
acknowledgment.Class = acknowledgmentClass.Id;
acknowledgment.Successful = successful;
acknowledgment.Timestamp = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
db.Insert(acknowledgment);
}
}
catch (Exception ex)
{
db.AbortTransaction();
errors = true;
IO.WriteTextToFile("Acknowledgement Insert Error (" + DateTime.Now + "): " + ex.ToString() + Environment.NewLine,
System.IO.Path.Combine(Misc.GetUserDataDirectory(), "Errors.txt"),
true);
break;
}
}
if (errors == false)
{
db.CompleteTransaction();
}
}
if (acknowledgedPrevious == true)
{
UserInterface.DisplayMessageBox(this,
"Some events were not classified due to being already classified",
MessageBoxIcon.Exclamation);
}
if (errors == true)
{
UserInterface.DisplayMessageBox(this,
"Errors occured, check the Errors.txt file",
MessageBoxIcon.Exclamation);
}
}
catch (Exception ex)
{
UserInterface.DisplayMessageBox(this,
"Errors occured, check the Errors.txt file",
MessageBoxIcon.Exclamation);
IO.WriteTextToFile("Acknowledgement Insert Error (" + DateTime.Now + "): " + ex.ToString() + Environment.NewLine,
System.IO.Path.Combine(Misc.GetUserDataDirectory(), "Errors.txt"),
true);
}
finally
{
this.DialogResult = DialogResult.OK;
}
})).Start();
}
/// <summary>
///
/// </summary>
public void UpdateSensors()
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List<Sensor> data = db.Fetch<Sensor>(_sql.GetQuery(Sql.Query.SQL_SENSORS_HOSTNAME));
List<NameValue> sensors = new List<NameValue>();
// Add a default
NameValue nameValue = new NameValue();
nameValue.Name = "All";
nameValue.Value = string.Empty;
sensors.Add(nameValue);
foreach (var result in data)
{
nameValue = new NameValue();
nameValue.Name = result.HostName;
nameValue.Value = result.HostName;
sensors.Add(nameValue);
}
cboSensor.Items.Clear();
cboSensor.DisplayMember = "Name";
cboSensor.ValueMember = "Value";
cboSensor.Items.AddRange(sensors.ToArray());
UserInterface.SetDropDownWidth(cboSensor);
if (sensors.Count > 0)
{
cboSensor.SelectedIndex = 0;
}
}
}
public string WaypointData()
{
if (_activeGcg == null)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
sb.AppendLine("<gpx xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" version=\"1.0\" creator=\"Globalcaching. http://www.globalcaching.eu\" xsi:schemaLocation=\"http://www.topografix.com/GPX/1/0 http://www.topografix.com/GPX/1/0/gpx.xsd http://www.groundspeak.com/cache/1/0 http://www.groundspeak.com/cache/1/0/cache.xsd\" xmlns=\"http://www.topografix.com/GPX/1/0\">");
sb.AppendLine(" <name>Waypoints for Cache Listings Generated by Globalcaching Application</name>");
sb.AppendLine(" <desc>This is a list of supporting waypoints for caches generated by Globalcaching App</desc>");
sb.AppendLine(" <author>Globalcaching</author>");
sb.AppendLine(" <url>http://www.globalcaching.eu</url>");
sb.AppendLine(" <urlname>Geocaching - High Tech Treasure Hunting</urlname>");
sb.AppendLine(" <email>[email protected]</email>");
sb.AppendLine(string.Format(" <time>{0}Z</time>", DateTime.Now.ToUniversalTime().ToString("s")));
sb.AppendLine(" <keywords>cache, geocache, waypoints</keywords>");
sb.AppendLine(string.Format(" <bounds minlat=\"{0}\" minlon=\"{1}\" maxlat=\"{2}\" maxlon=\"{3}\" />",
_minLat.ToString(CultureInfo.InvariantCulture),
_minLon.ToString(CultureInfo.InvariantCulture),
_maxLat.ToString(CultureInfo.InvariantCulture),
_maxLon.ToString(CultureInfo.InvariantCulture)
));
return(sb.ToString());
}
else
{
/*
* <wpt lat="50.908867" lon="5.435833">
* <time>2010-06-22T11:05:21.31</time>
* <name>0026K5Z</name>
* <cmt>Laat hier je cachemobiel even uitrusten en doe het laatste stukje te voet</cmt>
* <desc>Parking</desc>
* <url>http://www.geocaching.com/seek/wpt.aspx?WID=565497fc-1de4-4f36-8661-da9d0f7848e5</url>
* <urlname>Parking</urlname>
* <sym>Parking Area</sym>
* <type>Waypoint|Parking Area</type>
* </wpt>
*/
string result = "";
bool hasContent = false;
XmlDocument doc = new XmlDocument();
XmlElement root = doc.CreateElement("root");
doc.AppendChild(root);
var wpts = _db.Fetch <DataTypes.GSAKWaypoints, DataTypes.GSAKWayMemo, WaypointPoco>((a, b) => { return(new WaypointPoco()
{
Waypoints = a, WayMemo = b
}); }, "select Waypoints.*, WayMemo.* from Waypoints left join WayMemo on Waypoints.cParent = WayMemo.cParent where Waypoints.cParent=@0", _activeGcg.Caches.Code);
foreach (var wp in wpts)
{
if (wp.Waypoints != null &&
string.IsNullOrEmpty(wp.Waypoints.cLat) &&
string.IsNullOrEmpty(wp.Waypoints.cLon) &&
!wp.Waypoints.cLat.StartsWith("0.0") &&
!wp.Waypoints.cLon.StartsWith("0.0")
)
{
hasContent = true;
XmlElement wpt = doc.CreateElement("wpt");
XmlAttribute attr = doc.CreateAttribute("lat");
XmlText txt = doc.CreateTextNode(wp.Waypoints.cLat);
attr.AppendChild(txt);
wpt.Attributes.Append(attr);
attr = doc.CreateAttribute("lon");
txt = doc.CreateTextNode(wp.Waypoints.cLon);
attr.AppendChild(txt);
wpt.Attributes.Append(attr);
root.AppendChild(wpt);
XmlElement el = doc.CreateElement("time");
txt = doc.CreateTextNode(string.Format("{0}Z", wp.Waypoints.cDate.ToString("s")));
el.AppendChild(txt);
wpt.AppendChild(el);
el = doc.CreateElement("name");
txt = doc.CreateTextNode(wp.Waypoints.cCode);
el.AppendChild(txt);
wpt.AppendChild(el);
el = doc.CreateElement("cmt");
txt = doc.CreateTextNode(wp.WayMemo == null ? "" : wp.WayMemo.cComment ?? "");
el.AppendChild(txt);
wpt.AppendChild(el);
el = doc.CreateElement("desc");
txt = doc.CreateTextNode(wp.Waypoints.cType);
el.AppendChild(txt);
wpt.AppendChild(el);
el = doc.CreateElement("url");
txt = doc.CreateTextNode(wp.WayMemo == null ? "" : wp.WayMemo.cUrl ?? "");
el.AppendChild(txt);
wpt.AppendChild(el);
el = doc.CreateElement("urlname");
txt = doc.CreateTextNode(wp.Waypoints.cType);
el.AppendChild(txt);
wpt.AppendChild(el);
el = doc.CreateElement("sym");
txt = doc.CreateTextNode(wp.Waypoints.cType);
el.AppendChild(txt);
wpt.AppendChild(el);
el = doc.CreateElement("type");
txt = doc.CreateTextNode(string.Format("Waypoint|{0}", wp.Waypoints.cType));
el.AppendChild(txt);
wpt.AppendChild(el);
}
}
if (hasContent)
{
using (System.IO.TemporaryFile tmp = new System.IO.TemporaryFile(true))
{
doc.Save(tmp.Path);
result = System.IO.File.ReadAllText(tmp.Path).Replace("</root>", "").Replace("<root>\r\n", "").Trim();
}
}
return(validateXml(result));
}
}
/// <summary>
///
/// </summary>
private void Process()
{
AcknowledgmentClass acknowledgmentClass = (AcknowledgmentClass)cboClassification.Items[cboClassification.SelectedIndex];
string initials = txtInitials.Text.ToUpper();
string notes = txtNotes.Text;
bool successful = chkSuccessful.Checked;
btnOk.Enabled = false;
btnCancel.Enabled = false;
(new Thread(() =>
{
try
{
bool acknowledgedPrevious = false;
bool errors = false;
using (new HourGlass(this))
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
db.BeginTransaction();
foreach (Event temp in _events)
{
try
{
bool insert = true;
var ack = db.Fetch<Acknowledgment>("select * from acknowledgment where cid=@0 and sid=@1", new object[] { temp.Cid, temp.Sid });
if (ack.Count() > 0)
{
if (ack.First().Initials.ToUpper() != initials)
{
acknowledgedPrevious = true;
insert = false;
}
else
{
db.Delete(ack.First());
}
}
if (insert == true)
{
Acknowledgment acknowledgment = new Acknowledgment();
acknowledgment.Cid = temp.Cid;
acknowledgment.Sid = temp.Sid;
acknowledgment.Initials = initials;
acknowledgment.Notes = notes;
acknowledgment.Class = acknowledgmentClass.Id;
acknowledgment.Successful = successful;
acknowledgment.Timestamp = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
db.Insert(acknowledgment);
}
}
catch (Exception ex)
{
db.AbortTransaction();
errors = true;
IO.WriteTextToFile("Acknowledgement Insert Error (" + DateTime.Now + "): " + ex.ToString() + Environment.NewLine,
System.IO.Path.Combine(Misc.GetUserDataDirectory(), "Errors.txt"),
true);
break;
}
}
if (errors == false)
{
db.CompleteTransaction();
}
}
if (acknowledgedPrevious == true)
{
UserInterface.DisplayMessageBox(this,
"Some events were not classified due to being already classified",
MessageBoxIcon.Exclamation);
}
if (errors == true)
{
UserInterface.DisplayMessageBox(this,
"Errors occured, check the Errors.txt file",
MessageBoxIcon.Exclamation);
}
}
catch (Exception ex)
{
UserInterface.DisplayMessageBox(this,
"Errors occured, check the Errors.txt file",
MessageBoxIcon.Exclamation);
IO.WriteTextToFile("Acknowledgement Insert Error (" + DateTime.Now + "): " + ex.ToString() + Environment.NewLine,
System.IO.Path.Combine(Misc.GetUserDataDirectory(), "Errors.txt"),
true);
}
finally
{
this.DialogResult = DialogResult.OK;
}
})).Start();
}
protected override void PerformExport(object settings)
{
var excelSetting = settings as ExportExcelSettings;
if (excelSetting != null && !string.IsNullOrEmpty(excelSetting.FileName))
{
using (FileStream stream = new FileStream(excelSetting.FileName, FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
using (var db = new NPoco.Database(this.DatabaseConnection.Connection, NPoco.DatabaseType.SQLite))
{
List <string> geocaches = db.Fetch <string>(string.Format("select Caches.Code from Caches inner join {0} on Caches.Code={0}.gccode", ActionInputTableName));
var nextUpdate = DateTime.Now.AddSeconds(2);
using (Utils.ProgressBlock progress = new Utils.ProgressBlock("ExportExcel", "CreatingFile", geocaches.Count, 0, true))
{
foreach (var sh in excelSetting.Sheets)
{
var sheet = wb.CreateSheet(sh.Name);
var r = sheet.GetOrCreateRow(0);
for (int c = 0; c < sh.SelectedItems.Count; c++)
{
var cell = r.GetOrCreateCol(c);
cell.SetCellValue(sh.SelectedItems[c].Text);
}
}
for (int index = 0; index < geocaches.Count; index++)
{
var gc = db.Fetch <DataTypes.GSAKCaches, DataTypes.GSAKCacheMemo, DataTypes.GSAKCorrected, Excel.GeocacheData>((a, b, c) => { return(new Excel.GeocacheData()
{
Caches = a, CacheMemo = b, Corrected = c
}); }, string.Format("select Caches.*, CacheMemo.*, Corrected.* from Caches inner join {0} on Caches.Code={0}.gccode left join CacheMemo on Caches.Code=CacheMemo.Code left join Corrected on Caches.Code=Corrected.kCode where Caches.Code=@0", ActionInputTableName), geocaches[index]).FirstOrDefault();
foreach (var sh in excelSetting.Sheets)
{
var sheet = wb.GetSheet(sh.Name);
var r = sheet.GetOrCreateRow(index + 1);
for (int c = 0; c < sh.SelectedItems.Count; c++)
{
var cell = r.GetOrCreateCol(c);
var obj = sh.SelectedItems[c].GetValue(gc);
if (obj == null)
{
}
else
{
var t = obj.GetType();
if (t == typeof(bool) || t == typeof(bool?))
{
cell.SetCellValue((bool)obj);
}
else if (t == typeof(int) || t == typeof(int?))
{
cell.SetCellValue((int)obj);
}
else if (t == typeof(string))
{
cell.SetCellValue((string)obj);
}
else if (t == typeof(DateTime) || t == typeof(DateTime?))
{
cell.SetCellValue((DateTime)obj);
}
else if (t == typeof(double) || t == typeof(double?))
{
cell.SetCellValue((double)obj);
}
else if (t == typeof(float) || t == typeof(float?))
{
cell.SetCellValue(Convert.ToDouble(obj));
}
else
{
cell.SetCellValue(obj.ToString());
}
}
}
}
if (DateTime.Now >= nextUpdate)
{
if (!progress.Update("CreatingFile", geocaches.Count, index + 1))
{
break;
}
nextUpdate = DateTime.Now.AddSeconds(1);
}
}
}
}
wb.Write(stream);
}
}
}