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());
}
}
/// <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);
}
public int Update <T>(T obj)
{
using (NPoco.IDatabase dbContext = new NPoco.Database(connectionString, NPoco.DatabaseType.SqlServer2012))
{
return(dbContext.Update(obj));
}
}
public GeocacheCollection GetCollection(string name, bool createIfNotExists = false)
{
GeocacheCollection result = null;
if (!string.IsNullOrEmpty(name))
{
lock (this)
{
if (_dbcon != null)
{
using (var db = new NPoco.Database(_dbcon.Connection, NPoco.DatabaseType.SQLite))
{
result = db.FirstOrDefault <GeocacheCollection>("where Name like @0", name);
if (result == null && createIfNotExists)
{
//just the lazy way
var record = db.FirstOrDefault <GeocacheCollection>("order by CollectionID desc limit 1");
result = new GeocacheCollection();
result.Name = name;
result.CollectionID = record == null ? 1 : record.CollectionID + 1;
db.Insert(result);
}
}
}
}
}
return(result);
}
public void Insert <T>(T obj)
{
using (NPoco.IDatabase dbContext = new NPoco.Database(connectionString, NPoco.DatabaseType.SqlServer2012))
{
dbContext.Insert <T>(obj);
}
}
public T SingleByIdEmployee <T>(long id)
{
using (NPoco.IDatabase dbContext = new NPoco.Database(connectionString, NPoco.DatabaseType.SqlServer2012))
{
return(dbContext.SingleById <T>(id));;
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ctxMenuAcknowledgmentClear_Click(object sender, EventArgs e)
{
if (listEvents.SelectedObjects.Count == 0)
{
return;
}
var list = listEvents.SelectedObjects.Cast <Event>().ToList();
(new Thread(() =>
{
SetProcessingStatus(false);
using (new HourGlass(this))
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
foreach (Event temp in list)
{
if (temp.AcknowledgmentId == 0)
{
continue;
}
Acknowledgment acknowledgment = new Acknowledgment();
acknowledgment.Id = temp.AcknowledgmentId;
var ack = db.SingleById <Acknowledgment>(acknowledgment.Id);
db.Delete(ack);
}
}
SetProcessingStatus(true);
LoadSearch(_currentPage);
})).Start();
}
private object GetWaypointPostion(string wpt)
{
string result = "";
if (!string.IsNullOrEmpty(Settings.Settings.Default.ActiveGeocacheCode) &&
!string.IsNullOrEmpty(Settings.Settings.Default.DatabaseFolderPath) &&
!string.IsNullOrEmpty(Settings.Settings.Default.SelectedDatabase)
)
{
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 wp = db.FirstOrDefault <DataTypes.GSAKWaypoints>("select * from Waypoints where cCode=@0", wpt);
if (!string.IsNullOrEmpty(wp.cLat) && !string.IsNullOrEmpty(wp.cLon))
{
result = Utils.Conversion.GetCoordinatesPresentation(Utils.Conversion.StringToDouble(wp.cLat), Utils.Conversion.StringToDouble(wp.cLon));
}
}
}
}
catch
{
}
}
return(result);
}
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();
}
private string GetGeocachePostion(string code)
{
string result = "";
if (!string.IsNullOrEmpty(code)
&& !string.IsNullOrEmpty(Settings.Settings.Default.DatabaseFolderPath)
&& !string.IsNullOrEmpty(Settings.Settings.Default.SelectedDatabase)
)
{
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 wp = db.FirstOrDefault<DataTypes.GSAKCaches>("select * from Caches where Code=@0", code);
var wpc = db.FirstOrDefault<DataTypes.GSAKCorrected>("select * from Corrected where kCode=@0", code);
if (wpc != null && !string.IsNullOrEmpty(wpc.kAfterLat) && !string.IsNullOrEmpty(wpc.kAfterLon))
{
result = Utils.Conversion.GetCoordinatesPresentation(Utils.Conversion.StringToDouble(wpc.kAfterLat), Utils.Conversion.StringToDouble(wpc.kAfterLon));
}
else if (!string.IsNullOrEmpty(wp.Latitude) && !string.IsNullOrEmpty(wp.Longitude))
{
result = Utils.Conversion.GetCoordinatesPresentation(Utils.Conversion.StringToDouble(wp.Latitude), Utils.Conversion.StringToDouble(wp.Longitude));
}
}
}
}
catch
{
}
}
return result;
}
/// <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>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOk_Click(object sender, EventArgs e)
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
Exclude exclude = db.SingleOrDefaultById <Exclude>(_id);
if (exclude == null)
{
UserInterface.DisplayMessageBox(this, "Unable to locate exclude", MessageBoxIcon.Exclamation);
return;
}
if (chkFalsePositive.Checked == true)
{
exclude.FalsePositive = true;
}
else
{
exclude.FalsePositive = false;
}
exclude.Comment = txtComment.Text;
db.Update(exclude);
this.DialogResult = System.Windows.Forms.DialogResult.OK;
}
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox("An error occurred whilst editing the exclude: " + ex.Message);
}
}
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 void StoreShapeFileItems(List <ShapefileItem> items)
{
lock (this)
{
if (_dbcon != null)
{
using (var db = new NPoco.Database(_dbcon.Connection, NPoco.DatabaseType.SQLite))
{
db.BeginTransaction();
try
{
db.Execute("delete from ShapeFileItemV2");
foreach (var s in items)
{
db.Insert("ShapeFileItemV2", null, s);
}
db.CompleteTransaction();
}
catch
{
db.AbortTransaction();
}
}
}
}
}
public int Delete <T>(int id)
{
using (NPoco.IDatabase dbContext = new NPoco.Database(connectionString, NPoco.DatabaseType.SqlServer2012))
{
var obj = dbContext.SingleById <T>(id);
return(dbContext.Delete <T>(obj));
}
}
public DTO.Database.CurrencyExchangeRateDto InsertCurrencyExchangeRate(DTO.Database.CurrencyExchangeRateDto currencyExchangeRate)
{
using (NPoco.IDatabase dbContext = new NPoco.Database(DTO.Global.SQLConnectionString, NPoco.DatabaseType.SqlServer2012))
{
dbContext.Insert("tbl_CurrencyExchangeRate", "CurrencyExchangeRateID", currencyExchangeRate);
}
return(currencyExchangeRate);
}
/// <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 static void ClearTables(NPoco.Database db)
{
db.Execute("DELETE FROM persons");
db.Execute("UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='persons'");
db.Execute("DELETE FROM marriages");
db.Execute("UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='marriages'");
db.Execute("DELETE FROM marriage_sons");
db.Execute("UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='marriage_sons'");
}
public DTO.Database.CurrencyExchangeRateDto CurrencyExchangeRate(DateTime date, string currencyCode)
{
DTO.Database.CurrencyExchangeRateDto result;
using (NPoco.IDatabase dbContext = new NPoco.Database(DTO.Global.SQLConnectionString, NPoco.DatabaseType.SqlServer2012))
{
result = dbContext.FirstOrDefault <DTO.Database.CurrencyExchangeRateDto>("SELECT * FROM tbl_CurrencyExchangeRate WHERE CurrencyExchangeRateShortDate = " + Helper.DateHelper.ShortDate(date) + " AND CurrencyCode = '" + currencyCode + "'");
}
return(result);
}
public IEnumerable <CurrencyExchangeRateDto> CurrencyExchangeRateList(DateTime date)
{
IEnumerable <CurrencyExchangeRateDto> result;
using (NPoco.IDatabase dbContext = new NPoco.Database(DTO.Global.SQLConnectionString, NPoco.DatabaseType.SqlServer2012))
{
result = dbContext.Query <DTO.Database.CurrencyExchangeRateDto>("SELECT * FROM tbl_CurrencyExchangeRate WHERE CurrencyExchangeRateShortDate = " + Helper.DateHelper.ShortDate(date));
}
return(result);
}
/// <summary>
/// Batch inserts a collection of <c>Company</c> objects.
/// </summary>
/// <param name="companies"></param>
public void Insert(IEnumerable <Company> companies)
{
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
db.InsertBatch <CompanyDto>(CompanyDto.Translate(companies.ToList()));
db.Connection.Close();
}
}
}
/// <summary>
/// Insert a single <c>Company</c> object.
/// </summary>
/// <param name="company"></param>
public void Insert(Company company)
{
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
db.Save <CompanyDto>(CompanyDto.Translate(company));
db.Connection.Close();
}
}
}
/// <summary>
/// Insert a single <c>Device</c> object.
/// </summary>
/// <param name="device"></param>
public void Insert(Device device)
{
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
db.Save <DeviceDto>(DeviceDto.Translate(device));
db.Connection.Close();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void FormFilter_Load(object sender, EventArgs e)
{
this.Show();
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
LoadClassifications(db);
LoadAcknowledgementClasses(db);
LoadSensors(db);
LoadProtocols();
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOk_Click(object sender, EventArgs e)
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
Exclude exclude = new Exclude();
if (chkRule.Checked == true)
{
exclude.SigId = _ruleId;
}
if (chkSourceIp.Checked == true)
{
byte[] ip = _sourceIp.GetAddressBytes();
Array.Reverse(ip);
exclude.SourceIp = BitConverter.ToUInt32(ip, 0);
}
if (chkSrcPort.Checked == true)
{
exclude.SourcePort = ushort.Parse(txtSourcePort.Text);
}
if (chkDestinationIp.Checked == true)
{
byte[] ip = _destinationIp.GetAddressBytes();
Array.Reverse(ip);
exclude.DestinationIp = BitConverter.ToUInt32(ip, 0);
}
if (chkDestPort.Checked == true)
{
exclude.DestinationPort = ushort.Parse(txtDestinationPort.Text);
}
exclude.IpProto = _ipProto;
exclude.Comment = txtComment.Text;
exclude.FalsePositive = chkFalsePositive.Checked;
exclude.Timestamp = DateTime.Now;
db.Insert(exclude);
}
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox("An error occurred whilst adding the exclude: " + ex.Message);
return;
}
this.DialogResult = System.Windows.Forms.DialogResult.OK;
}
/// <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);
}
public void DeleteGeocacheCollectionItem(int colid, string code)
{
lock (this)
{
if (_dbcon != null)
{
using (var db = new NPoco.Database(_dbcon.Connection, NPoco.DatabaseType.SQLite))
{
db.Execute("delete from GeocacheCollectionItem where CollectionID = @0 and GeocacheCode = @1", colid, code);
}
}
}
}
public void DeleteGeocacheCollection(int id)
{
lock (this)
{
if (_dbcon != null)
{
using (var db = new NPoco.Database(_dbcon.Connection, NPoco.DatabaseType.SQLite))
{
db.Execute("delete from GeocacheCollection where CollectionID = @0", id);
}
}
}
}
public void DeleteScriptItem(string name)
{
lock (this)
{
if (_dbcon != null)
{
using (var db = new NPoco.Database(_dbcon.Connection, NPoco.DatabaseType.SQLite))
{
db.Execute("delete from Scripts where Name=@0", name);
}
}
}
}
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 GPXGenerator(
NPoco.Database db,
List<string> gcList,
Version gpxVersion,
double minLat, double maxLat, double minLon, double maxLon
)
{
_db = db;
_gcList = gcList;
_gpxVersion = gpxVersion;
_minLat = minLat;
_maxLat = maxLat;
_minLon = minLon;
_maxLon = maxLon;
_index = 0;
}
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>
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="sender"></param>
/// <param name="e"></param>
private void contextDecodeGzip_Click(object sender, EventArgs e)
{
(new Thread(() =>
{
MethodInvoker methodInvoker = delegate
{
using (new HourGlass(this))
{
Session temp = (Session)listSession.SelectedObjects[0];
if (temp == null)
{
contextDecodeGzip.Enabled = false;
return;
}
if (temp == null)
{
UserInterface.DisplayErrorMessageBox(this, "Unable to locate session");
return;
}
var httpParser = new HttpParser();
httpParser.Process(this.dataDirectory, temp);
LoadSession(temp);
using (DbConnection connection = Db.GetOpenConnection(this.dataDirectory))
using (var db = new NPoco.Database(connection, NPoco.DatabaseType.SQLCe))
{
try
{
var session = db.SingleOrDefaultById<Session>(temp.Id);
if (session != null)
{
listSession.RefreshObject(session);
}
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox(this, "An error occurred whilst retreiving the sessions details: " + ex.Message);
}
}
}
};
if (this.InvokeRequired == true)
{
this.BeginInvoke(methodInvoker);
}
else
{
methodInvoker.Invoke();
}
})).Start();
}
private object GetWaypointPostion(string wpt)
{
string result = "";
if (!string.IsNullOrEmpty(Settings.Settings.Default.ActiveGeocacheCode)
&& !string.IsNullOrEmpty(Settings.Settings.Default.DatabaseFolderPath)
&& !string.IsNullOrEmpty(Settings.Settings.Default.SelectedDatabase)
)
{
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 wp = db.FirstOrDefault<DataTypes.GSAKWaypoints>("select * from Waypoints where cCode=@0", wpt);
if (!string.IsNullOrEmpty(wp.cLat) && !string.IsNullOrEmpty(wp.cLon))
{
result = Utils.Conversion.GetCoordinatesPresentation(Utils.Conversion.StringToDouble(wp.cLat), Utils.Conversion.StringToDouble(wp.cLon));
}
}
}
}
catch
{
}
}
return result;
}
/// <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>
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;
}
}
}
/// <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>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnFilterAdd_Click(object sender, EventArgs e)
{
using (FormFilter formFilter = new FormFilter(_sql, _filters))
{
using (new HourGlass(this))
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
formFilter.LoadClassifications(db);
}
if (formFilter.ShowDialog(this) == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
_filters.Add(formFilter.Filter);
LoadFilters();
LoadSearch(1);
SetFilterButtonStatus(true);
}
}
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="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();
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void FormFilter_Load(object sender, EventArgs e)
{
this.Show();
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
LoadClassifications(db);
LoadAcknowledgementClasses(db);
LoadSensors(db);
LoadProtocols();
}
}
/// <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();
}
/// <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="sender"></param>
/// <param name="e"></param>
private void btnOk_Click(object sender, EventArgs e)
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
Exclude exclude = db.SingleOrDefaultById<Exclude>(_id);
if (exclude == null)
{
UserInterface.DisplayMessageBox(this, "Unable to locate exclude", MessageBoxIcon.Exclamation);
return;
}
if (chkFalsePositive.Checked == true)
{
exclude.FalsePositive = true;
}
else
{
exclude.FalsePositive = false;
}
exclude.Comment = txtComment.Text;
db.Update(exclude);
this.DialogResult = System.Windows.Forms.DialogResult.OK;
}
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox("An error occurred whilst editing the exclude: " + ex.Message);
}
}
/// <summary>
///
/// </summary>
/// <param name="packet"></param>
private void WriteOldSessions(PcapDotNet.Packets.Packet packet)
{
try
{
OnMessage("Writing session data to database..." + _packetCount + " packets");
using (DbConnection dbConnection = Db.GetOpenConnection(_outputPath))
using (var db = new NPoco.Database(dbConnection, NPoco.DatabaseType.SQLCe))
{
var keys = _dictionary.Keys.ToList();
foreach (var connection in keys)
{
var temp = _dictionary[connection];
if (temp.TimestampLastPacket == null)
{
if (temp.DataSize == 0)
{
_dictionary[connection].Dispose();
_dictionary.Remove(connection);
}
continue;
}
if (temp.HasFin == false)
{
if (packet != null)
{
// Lets ignore sessions that are still within the threshold
if (packet.Timestamp < temp.TimestampLastPacket.Value.AddMinutes(SessionInterval))
{
continue;
}
}
}
else
{
if (packet != null)
{
// Only kill sessions that have had a FIN and at least a minute has past
if (packet.Timestamp < temp.TimestampLastPacket.Value.AddMinutes(1))
{
continue;
}
}
}
Session session = CreateNewSession(temp.Guid,
temp.DataSize,
connection);
_dictionary[connection].Dispose();
if (_dictionary.Remove(connection) == false)
{
Console.WriteLine("Unable to remove connection object: " + connection.GetName());
}
if (temp.DataSize > 0)
{
int pk = Convert.ToInt32(db.Insert("Sessions", "Id", session));
PerformSessionProcessing(session, pk);
}
}
OnMessage("Commiting database transaction..." + _packetCount + " packets");
}
}
catch (Exception ex)
{
this.Log().Error(ex.ToString());
}
finally
{
OnMessage(string.Empty);
}
}
/// <summary>
///
/// </summary>
/// <param name="session"></param>
private void UpdateDatabaseSession()
{
List<string> methods = new List<string>();
string host = string.Empty;
foreach (Message message in this.parser.Messages)
{
// Ensure that the request object is valid
if (message.Request.Method.Length == 0)
{
continue;
}
host = message.Request.Host;
if (methods.Contains(message.Request.Method) == false)
{
methods.Add(message.Request.Method);
}
}
using (DbConnection dbConnection = Db.GetOpenConnection(this.outputPath))
using (var db = new NPoco.Database(dbConnection, NPoco.DatabaseType.SQLCe))
{
var obj = db.SingleOrDefaultById<Session>(this.session.Id);
if (obj != null)
{
methods.Sort();
this.session.HttpMethods = string.Join(",", methods);
this.session.HttpHost = host;
int ret = db.Update(this.session);
}
}
}
/// <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="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();
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDelete_Click(object sender, System.EventArgs e)
{
if (listExcludes.SelectedObjects.Count == 0)
{
return;
}
if (listExcludes.SelectedObjects.Count == 1)
{
Exclude exclude = (Exclude)listExcludes.SelectedObjects[0];
DialogResult dialogResult = MessageBox.Show(this,
"Are you sure you want to delete the exclude?",
Application.ProductName,
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (dialogResult == System.Windows.Forms.DialogResult.No)
{
return;
}
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
Exclude temp = db.SingleOrDefaultById<Exclude>(exclude.Id);
if (temp == null)
{
UserInterface.DisplayMessageBox(this, "Unable to locate exclude", MessageBoxIcon.Exclamation);
return;
}
int ret = db.Delete(temp);
if (ret != 1)
{
UserInterface.DisplayErrorMessageBox(this, "The exclude could not be deleted");
return;
}
}
LoadExcludes();
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox("An error occurred whilst deleting the exclude" + ex.Message);
}
}
else
{
int count = listExcludes.SelectedObjects.Count;
DialogResult dialogResult = MessageBox.Show(this,
"Are you sure you want to delete all " + count + " excludes?",
Application.ProductName,
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (dialogResult == System.Windows.Forms.DialogResult.No)
{
return;
}
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
foreach (var item in listExcludes.SelectedObjects)
{
Exclude exclude = (Exclude)item;
Exclude temp = db.SingleOrDefaultById<Exclude>(exclude.Id);
if (temp == null)
{
UserInterface.DisplayMessageBox(this, "Unable to locate exclude", MessageBoxIcon.Exclamation);
return;
}
int ret = db.Delete(temp);
if (ret != 1)
{
UserInterface.DisplayErrorMessageBox(this,
"The exclude could not be deleted: " + Environment.NewLine +
exclude.ToString());
continue;
}
}
}
LoadExcludes();
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox("An error occurred whilst deleting the exclude" + ex.Message);
}
}
}
/// <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="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="sender"></param>
/// <param name="e"></param>
private void ctxMenuAcknowledgmentClear_Click(object sender, EventArgs e)
{
if (listEvents.SelectedObjects.Count == 0)
{
return;
}
var list = listEvents.SelectedObjects.Cast<Event>().ToList();
(new Thread(() =>
{
SetProcessingStatus(false);
using (new HourGlass(this))
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
foreach (Event temp in list)
{
if (temp.AcknowledgmentId == 0)
{
continue;
}
Acknowledgment acknowledgment = new Acknowledgment();
acknowledgment.Id = temp.AcknowledgmentId;
var ack = db.SingleById<Acknowledgment>(acknowledgment.Id);
db.Delete(ack);
}
}
SetProcessingStatus(true);
LoadRuleEvents(_currentPage);
})).Start();
}
/// <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>
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>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOk_Click(object sender, EventArgs e)
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
Exclude exclude = new Exclude();
if (chkRule.Checked == true)
{
exclude.SigId = _ruleId;
}
if (chkSourceIp.Checked == true)
{
byte[] ip = _sourceIp.GetAddressBytes();
Array.Reverse(ip);
exclude.SourceIp = BitConverter.ToUInt32(ip, 0);
}
if (chkSrcPort.Checked == true)
{
exclude.SourcePort = ushort.Parse(txtSourcePort.Text);
}
if (chkDestinationIp.Checked == true)
{
byte[] ip = _destinationIp.GetAddressBytes();
Array.Reverse(ip);
exclude.DestinationIp = BitConverter.ToUInt32(ip, 0);
}
if (chkDestPort.Checked == true)
{
exclude.DestinationPort = ushort.Parse(txtDestinationPort.Text);
}
exclude.IpProto = _ipProto;
exclude.Comment = txtComment.Text;
exclude.FalsePositive = chkFalsePositive.Checked;
exclude.Timestamp = DateTime.Now;
db.Insert(exclude);
}
}
catch (Exception ex)
{
UserInterface.DisplayErrorMessageBox("An error occurred whilst adding the exclude: " + ex.Message);
return;
}
this.DialogResult = System.Windows.Forms.DialogResult.OK;
}
