/*
* public RegistratieItem GetFieldValues(DataRow row, string[] fieldnames)
* {
* string[] fieldvalues = new string[fieldnames.Count()];
* for (int i = 0; i < fieldnames.Count(); i++)
* {
* var value = row[fieldnames[i]];
* fieldvalues[i] = escaper.GetFieldValue(value);
* }
* return new RegistratieItem(fieldnames, fieldvalues);
* }
*/
public SortedDictionary <RegistratieItem, DataRegel> GetSortedList(string[] fieldnames)
{
SortedDictionary <RegistratieItem, DataRegel> result = new SortedDictionary <RegistratieItem, DataRegel>();
foreach (System.Data.DataRow row in data.Rows)
{
//RegistratieItem item = GetFieldValues(row, fieldnames);
DataRegel regel = new DataRegel(this, row);
RegistratieItem item = regel.GetFieldValues(fieldnames);
if (!result.ContainsKey(item))
{
result.Add(item, regel);
}
else
{
Output.Warn("\tdouble entry in reference for: " + item);
}
}
return(result);
}
static void Main(string[] args)
{
// . as decimal-seperator, etc
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.InvariantCulture;
System.Threading.Thread.CurrentThread.CurrentUICulture = System.Globalization.CultureInfo.InvariantCulture;
//TODO:
// field xml
Output.Info("*****************\n***** START *****\n*****************");
//Output.Info("Using: " + Properties.Settings.Default.output_format + " as output format");
string filter = null;
if (args.Count() > 0)
{
filter = args[0];
Output.Info("Filter op vergelijking: '" + filter + "'");
}
#if !DEBUG
try
{
#endif
var provider = DbProviderFactories.GetFactory(Properties.Settings.Default.databaseprovider);
var connection = provider.CreateConnection();
connection.ConnectionString = Properties.Settings.Default.databaseconnection.Replace("${WORKING_DIRECTORY}", System.IO.Directory.GetCurrentDirectory());
Output.Info("ISpiegel connection:" + connection.ConnectionString);
// If error.message == The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. ==> are we debugging in 32-bits (x86) mode?
connection.Open();
#region COMPARE
var command = provider.CreateCommand();
if (filter == null)
{
command.CommandText = "SELECT * FROM " + Properties.Settings.Default.databaseprefix + "vergelijking WHERE actief = -1 ORDER BY vergelijkingnaam";
}
else
{
command.CommandText = "SELECT * FROM " + Properties.Settings.Default.databaseprefix + "vergelijking WHERE vergelijkingnaam LIKE '" + filter + "'";
}
command.Connection = connection;
var adapter = provider.CreateDataAdapter();
adapter.SelectCommand = command;
var table = new DataTable();
adapter.Fill(table);
foreach (DataRow comparerow in table.Rows)
{
var vergelijking = new Vergelijking(Convert.ToString(comparerow["vergelijkingnaam"]), Convert.ToString(comparerow["veldtoewijzing"]), Convert.ToString(comparerow["referentiedatabronnaam"]), Convert.ToString(comparerow["analysedatabronnaam"]), Convert.ToString(comparerow["rapporttype"]));
Output.Info("START: " + vergelijking.Naam);
#if !DEBUG
try
{
#endif
// what shall we do with the console reporter?
DatabaseReporter reporter = new DatabaseReporter(provider, connection);
XPathDocument document = new XPathDocument(new StringReader(vergelijking.VeldToewijzing));
XPathNavigator compareconfig = document.CreateNavigator();
compareconfig = compareconfig.SelectSingleNode("/compare");
reporter.Start(
vergelijking.Naam,
vergelijking.ReferentieDatabronNaam,
vergelijking.AnalyseDatabronNaam,
vergelijking.VeldToewijzing,
null,
null,
vergelijking.Rapporttype
);
// create the data sources
Output.Info("\t[" + vergelijking.ReferentieDatabronNaam + "] data will be loaded");
var reference = Databron.GetData(provider, connection, vergelijking.ReferentieDatabronNaam);
vergelijking.Reference = reference;
//RegistratieSource reference = new RegistratieSource(referencetable);
Output.Info("\t[" + vergelijking.ReferentieDatabronNaam + "] data loaded (#" + reference.Count + ")");
Output.Info("\t[" + vergelijking.AnalyseDatabronNaam + "] data will be loaded");
var analysis = Databron.GetData(provider, connection, vergelijking.AnalyseDatabronNaam);
vergelijking.Analysis = analysis;
//RegistratieSource analysis = new RegistratieSource(analysetable);
Output.Info("\t[" + vergelijking.AnalyseDatabronNaam + "] data loaded (#" + analysis.Count + ")");
// check the columns (better error messages!)
#region matching
foreach (XPathNavigator field in compareconfig.Select("//field"))
{
XPathNavigator referencefield = field.SelectSingleNode("@reference-field");
if (referencefield != null && !reference.Columns.Contains(referencefield.Value))
{
Output.Warn("reference-column:" + field.SelectSingleNode("@reference-field").Value + " not found in:");
foreach (var name in reference.Columns)
{
Output.Warn("\t" + name.ToString());
}
throw new InvalidDataException("reference-column:" + field.SelectSingleNode("@reference-field").Value + " not found ");
}
XPathNavigator analysisfield = field.SelectSingleNode("@analysis-field");
if (analysisfield != null && !analysis.Columns.Contains(analysisfield.Value))
{
Output.Warn("analysis-column:" + field.SelectSingleNode("@analysis-field").Value + " not found in:");
foreach (var name in analysis.Columns)
{
Output.Warn("\t" + name.ToString());
}
throw new InvalidDataException("analysis-column:" + field.SelectSingleNode("@analysis-field").Value + " not found");
}
}
Output.Info("\t[check] field references correct");
#endregion
// export into csv, so we can use i-spiegel
#region export csv
/*
* {
* DirectoryInfo exportdirectory = new DirectoryInfo("data");
* if (!exportdirectory.Exists) exportdirectory.Create();
* exportdirectory = new DirectoryInfo("data\\" + comparename);
* if (!exportdirectory.Exists) exportdirectory.Create();
*
* List<string> n = new List<string>();
* List<string> r = new List<string>();
* List<string> a = new List<string>();
* foreach (XPathNavigator field in compareconfig.Select("//field"))
* {
* n.Add(field.SelectSingleNode("@name").Value);
* if (field.SelectSingleNode("@reference-field") != null)
* {
* r.Add(field.SelectSingleNode("@reference-field").Value);
* }
* else
* {
* r.Add(null);
* }
* if (field.SelectSingleNode("@analysis-field") != null)
* {
* a.Add(field.SelectSingleNode("@analysis-field").Value);
* }
* else
* {
* a.Add(null);
* }
* }
* Output.Info("\tSTART: exporting the data");
* try
* {
* reference.Export(r, n, exportdirectory, compareconfig.SelectSingleNode("@reference").Value);
* analysis.Export(a, n, exportdirectory, compareconfig.SelectSingleNode("@analysis").Value);
*
* Output.Info("\tSTOP: exporting the data");
* }
* catch(Exception ex) {
* Output.Warn("\tERROR: exporting the data", ex);
* }
* }
*/
#endregion
// matches
#region build matchers
Dictionary <string, List <string>[]> matchers = new Dictionary <string, List <string>[]>();
foreach (XPathNavigator match in compareconfig.Select("match"))
{
String name = match.SelectSingleNode("@id").Value;
List <string> r = new List <string>();
List <string> a = new List <string>();
foreach (XPathNavigator field in match.Select("field"))
{
r.Add(field.SelectSingleNode("@reference-field").Value);
a.Add(field.SelectSingleNode("@analysis-field").Value);
}
List <string>[] ra = new List <string> [2];
ra[0] = r;
ra[1] = a;
if (matchers.ContainsKey(name))
{
throw new Exception("match with id:" + name + " does already exist!");
}
matchers.Add(name, ra);
}
#endregion
// create compare array
#region build lookup
String primary = compareconfig.SelectSingleNode("@primary").Value;
SortedDictionary <RegistratieItem, DataRegel> lookup = reference.GetSortedList(matchers[primary][0].ToArray());
Output.Info("\t[lookup] index succesfull");
#endregion
// now start the loop
foreach (DataRegel regel in analysis.Regels)
{
// primary match
string[] analysisrows = matchers[primary][1].ToArray();
//RegistratieItem matcher = analysis.GetFieldValues(row, analysisrows);
RegistratieItem matcher = regel.GetFieldValues(analysisrows);
if (!lookup.ContainsKey(matcher))
{
//reporter.EntryNotFound(vergelijking, primary, row, matcher);
reporter.EntryNotFound(vergelijking, primary, regel, matcher);
continue;
}
// System.Data.DataRow found = lookup[matcher];
DataRegel found = lookup[matcher];
bool fullmatch = true;
bool firsterror = true;
foreach (string matchername in matchers.Keys)
{
if (matchername != primary)
{
string[] analysisfields = matchers[matchername][1].ToArray();
string[] referencefields = matchers[matchername][0].ToArray();
// RegistratieItem a = analysis.GetFieldValues(row, analysisfields);
// RegistratieItem r = reference.GetFieldValues(found, referencefields);
RegistratieItem a = regel.GetFieldValues(analysisfields);
RegistratieItem r = found.GetFieldValues(referencefields);
if (!a.Equals(r))
{
fullmatch = false;
reporter.EntryNoMatch(vergelijking, regel, matcher, found, matchername, a, r, matcher, firsterror);
firsterror = false;
}
}
}
if (fullmatch)
{
reporter.EntryMatch(vergelijking, regel, found, matcher);
}
}
reporter.Stop(
vergelijking.Naam,
reference.ApplicatieNaam,
analysis.ApplicatieNaam,
reference.ReferentieQuery,
analysis.ReferentieQuery,
reference.GemeenteCode,
analysis.GemeenteCode,
analysis.Count,
reference.Count);
Output.Info("STOP: " + vergelijking.Naam);
#if !DEBUG
}
catch (Exception ex)
{
Output.Warn("ERROR PROCESSING: " + vergelijking.Naam, ex);
}
#endif
}
#endregion COMPARE
#region CHECK
command = provider.CreateCommand();
// "small detail", in access boolean: true = false and visaversa
if (filter == null)
{
command.CommandText = "SELECT * FROM " + Properties.Settings.Default.databaseprefix + "controle WHERE actief = -1 ORDER BY controlenaam";
}
else
{
command.CommandText = "SELECT * FROM " + Properties.Settings.Default.databaseprefix + "controle WHERE controlenaam LIKE '" + filter + "'";
}
command.Connection = connection;
adapter = provider.CreateDataAdapter();
adapter.SelectCommand = command;
table = new DataTable();
adapter.Fill(table);
foreach (DataRow checkrow in table.Rows)
{
string controlenaam = Convert.ToString(checkrow["controlenaam"]);
string datasourcename = Convert.ToString(checkrow["databronnaam"]);
string primary = Convert.ToString(checkrow["sleutelkolom"]);
string columnname = Convert.ToString(checkrow["controlekolom"]);
string checkvalue = Convert.ToString(checkrow["controlewaarde"]);
string rapporttype = Convert.ToString(checkrow["rapporttype"]);
var vergelijking = new Vergelijking(controlenaam, primary, datasourcename, columnname, rapporttype);
Output.Info("START: " + controlenaam);
#if !DEBUG
try
{
#endif
DatabaseReporter reporter = new DatabaseReporter(provider, connection);
reporter.Start(controlenaam, null, datasourcename, columnname + "='" + checkvalue + "'", null, datasourcename, rapporttype);
var controle = Databron.GetData(provider, connection, datasourcename);
foreach (DataRegel datarow in controle.Regels)
{
var found = datarow[columnname];
if (Convert.ToString(found).Equals(checkvalue))
{
reporter.EntryMatch(vergelijking, controlenaam, primary, columnname, checkvalue, datarow);
}
else
{
reporter.EntryInvalid(vergelijking, controlenaam, primary, columnname, checkvalue, datarow);
}
}
reporter.Stop(
controlenaam,
controle.ApplicatieNaam,
null,
controle.ReferentieQuery,
null,
controle.GemeenteCode,
null,
controle.Regels.Count,
0);
Output.Info("STOP: " + controlenaam);
#if !DEBUG
}
catch (Exception ex)
{
Output.Warn("ERROR PROCESSING: " + controlenaam, ex);
}
#endif
}
#endregion CHECK
connection.Close();
Output.Info("*****************\n***** STOP ******\n*****************");
#if !DEBUG
}
catch (Exception ex)
{
Output.Error("*****************\n***** ERROR ******\n*****************", ex);
}
#endif
if (Properties.Settings.Default.email_smtp.Length > 0)
{
System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();
message.To.Add(Properties.Settings.Default.email_receiver);
message.Subject = Properties.Settings.Default.email_subject;
message.From = new System.Net.Mail.MailAddress(Properties.Settings.Default.email_from);
message.Body = Output.ToString();
System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient(Properties.Settings.Default.email_smtp);
smtp.UseDefaultCredentials = true;
smtp.Send(message);
}
else
{
Console.WriteLine("\n\n=== no emailserver configured, waiting for user-input ===");
Console.WriteLine("Press Any Key to Continue");
Console.ReadKey();
}
}
public void Stop(string vergelijkingnaam, string analyseapplicatie, string referentieapplicatie, string analysequery, string referentiequery, string analysegemeentecode, string referentiegemeentecode, long analysecount, long referencecount)
{
Output.Info("\t" + ((int)((100.0 / analysecount) * match)) + "% gelijk #" + match + " afwijkend #" + nomatch + " mist #" + missing + "(adding #" + ds.Tables["outputline"].Rows.Count + " outputlines )");
// save the regels
regeladapter.Update(ds, "outputline");
// update the koprow
koprow["referentieapplicatie"] = referentieapplicatie;
koprow["analyseapplicatie"] = analyseapplicatie;
koprow["referentiequery"] = referentiequery;
koprow["analysequery"] = analysequery;
koprow["referentiegemeentecode"] = referentiegemeentecode;
koprow["analysegemeentecode"] = analysegemeentecode;
koprow["percentage"] = (100.0 / analysecount) * match;
koprow["referentieaantal"] = referencecount;
koprow["analyseaantal"] = analysecount;
koprow["gelijkaantal"] = match;
koprow["afwijkingaantal"] = nomatch;
koprow["nietgevondenaantal"] = missing;
//koprow["ongeldigaantal"] = invalid;
// the code that you want to measure comes here
watch.Stop();
koprow["looptijd"] = watch.Elapsed.TotalSeconds;
//ds.Tables["output"].Rows[0]. koprow
//koprow.SetModified();
kopadapter.Update(ds, "output");
if (Properties.Settings.Default.influxdb_url != "")
{
// dimensions
String ResultLine = "referentieapplicatie=" + (referentieapplicatie == null ? "GEEN" : referentieapplicatie.Replace(" ", "\\ ").Replace(",", "\\,").Replace("=", "\\=")) + ",";
ResultLine += "analyseapplicatie=" + (analyseapplicatie == null ? "GEEN" : analyseapplicatie.Replace(" ", "\\ ").Replace(",", "\\,").Replace("=", "\\=")) + ",";
ResultLine += "referentiegemeentecode=" + (referentiegemeentecode == null ? "GEEN" : referentiegemeentecode.Replace(" ", "\\ ").Replace(",", "\\,").Replace("=", "\\=")) + ",";
ResultLine += "analysegemeentecode=" + (analysegemeentecode == null ? "GEEN" : analysegemeentecode.Replace(" ", "\\ ").Replace(",", "\\,").Replace("=", "\\=")) + " ";
// values
ResultLine += "referentieaantal=" + referencecount + ",";
ResultLine += "analyseaantal=" + analysecount + ",";
ResultLine += "gelijkaantal=" + match + ",";
ResultLine += "afwijkingaantal=" + nomatch + ",";
ResultLine += "nietgevondenaantal=" + missing + ",";
//ResultLine += "ongeldigaantal=" + invalid + ",";
ResultLine += "percentage=" + Math.Round((100.0 / analysecount) * match, 2);
var postdata = "ispiegel,";
postdata += "vergelijking=" + vergelijkingnaam.Replace(" ", "\\ ").Replace(",", "\\,").Replace("=", "\\=") + ",";
postdata += ResultLine;
var client = new ShootAndForgetWebClient();
if (Properties.Settings.Default.influxdb_auth != "")
{
// ik wil in één keer de boel versturen, stomme "client.Credentials = new NetworkCredential"!
string credentials = Convert.ToBase64String(System.Text.Encoding.ASCII.GetBytes(Properties.Settings.Default.influxdb_auth));
client.Headers[System.Net.HttpRequestHeader.Authorization] = string.Format("Basic {0}", credentials);
}
client.Headers[System.Net.HttpRequestHeader.ContentType] = "application/x-www-form-urlencoded";
Output.Info("\t>>> posting to: " + Properties.Settings.Default.influxdb_url + " the following data:" + postdata);
try
{
var response = client.UploadString(Properties.Settings.Default.influxdb_url, postdata);
}
catch (System.Net.WebException ex)
{
Output.Warn("Sending the data to: " + Properties.Settings.Default.influxdb_url, ex);
}
}
}
public static Databron GetData(DbProviderFactory configprovider, DbConnection configconnection, String datasourcename)
{
// const string sql = "SELECT * FROM SOME_TABLE WHERE Name = @name";
// cmd.CommandText = sql;
// cmd.Parameters.AddWithValue("@name", name);
var command = configprovider.CreateCommand();
// TODO: parameter
command.CommandText = "SELECT * FROM " + Properties.Settings.Default.databaseprefix + "databron WHERE databronnaam LIKE '" + datasourcename + "'";
command.Connection = configconnection;
var adapter = configprovider.CreateDataAdapter();
adapter.SelectCommand = command;
var table = new DataTable();
adapter.Fill(table);
if (table.Rows.Count != 1)
{
throw new Exception("Kon de datasource met naam:" + datasourcename + " niet vinden!");
}
String datasource_provider = Convert.ToString(table.Rows[0]["provider"]);
String datasource_connectionstring = Convert.ToString(table.Rows[0]["connectionstring"]);
String datasource_query = Convert.ToString(table.Rows[0]["query"]);
String applicatie = Convert.ToString(table.Rows[0]["applicatienaam"]);
String gemeentecode = Convert.ToString(table.Rows[0]["gemeentecode"]);
String escapesequence = "";
// make backwards compatible
if (table.Columns.Contains("escapesequence"))
{
escapesequence = Convert.ToString(table.Rows[0]["escapesequence"]);
}
else
{
Output.Warn("!!! Column 'escapesequence' not defined in databron, using NO escapers anywhere !!!");
}
EscapeSequence escaper = new EscapeSequence(escapesequence);
var connection = new ISpiegel.Provider.FileSystem.Connection();
DbProviderFactory datasource_factory = null;
if (datasource_provider.Equals("ISpiegel.Providers.FileSystem"))
{
datasource_factory = new ISpiegel.Provider.FileSystem.Factory();
}
else
{
datasource_factory = DbProviderFactories.GetFactory(datasource_provider);
}
var datasource_connection = datasource_factory.CreateConnection();
datasource_connection.ConnectionString = datasource_connectionstring.Replace("${WORKING_DIRECTORY}", System.IO.Directory.GetCurrentDirectory());
datasource_connection.Open();
var datasource_command = datasource_factory.CreateCommand();
datasource_command.CommandText = datasource_query;
datasource_command.Connection = datasource_connection;
var datasource_adapter = datasource_factory.CreateDataAdapter();
datasource_adapter.SelectCommand = datasource_command;
var datasource_table = new DataTable();
datasource_adapter.Fill(datasource_table);
datasource_connection.Close();
// datasource_table.ExtendedProperties.Add("databronnaam", datasourcename);
// datasource_table.ExtendedProperties.Add("applicatienaam", applicatie);
// datasource_table.ExtendedProperties.Add("referentiequery", datasource_query);
// datasource_table.ExtendedProperties.Add("gemeentecode", gemeentecode);
return(new Databron(datasource_table, datasourcename, applicatie, datasource_query, gemeentecode, escaper));
}