public void EntryNotFound(Vergelijking vergelijking, string matcher, DataRegel searchrow, RegistratieItem searchitem)
        {
            DataRow row = ds.Tables["outputline"].NewRow();

            row["outputid"]    = koprow["outputid"];
            row["regelnummer"] = ds.Tables["outputline"].Rows.Count + 1;
            row["status"]      = "NOT FOUND";


            row["controle"]         = matcher;
            row["sleutel"]          = CreateRowXml(vergelijking.Naam, searchitem.fieldnames, searchitem.fieldvalues);
            row["analysewaarde"]    = CreateRowXml(matcher, searchitem.fieldnames, searchitem.fieldvalues);
            row["referentiewaarde"] = DBNull.Value;
            //row["analyseregel"] = CreateRowXml(vergelijking.Analysis.DatabronNaam, ToStringArray(searchrow.Table.Columns), ToStringArray(searchrow.ItemArray));
            row["analyseregel"]    = CreateRowXml(vergelijking.Analysis.DatabronNaam, searchrow.FieldNames, searchrow.FieldValues);
            row["referentieregel"] = DBNull.Value;


            row["analysesleutelwaarde"]    = string.Join(", ", searchitem.fieldvalues);
            row["referentiesleutelwaarde"] = string.Join(", ", DBNull.Value);
            row["analyseveldwaarde"]       = string.Join(", ", searchitem.fieldvalues);
            row["referentieveldwaarde"]    = string.Join(", ", DBNull.Value);

            ds.Tables["outputline"].Rows.Add(row);

            missing++;
        }
        public void EntryMatch(Vergelijking vergelijking, DataRegel searchrow, DataRegel found, RegistratieItem sleutel)
        {
            if (Properties.Settings.Default.output_everything)
            {
                DataRow row = ds.Tables["outputline"].NewRow();
                row["outputid"]    = koprow["outputid"];
                row["regelnummer"] = ds.Tables["outputline"].Rows.Count + 1;
                row["status"]      = "VALID";

                row["controle"]         = DBNull.Value;
                row["sleutel"]          = CreateRowXml(vergelijking.Naam, sleutel.fieldnames, sleutel.fieldvalues);
                row["analysewaarde"]    = CreateRowXml(vergelijking.Naam);
                row["referentiewaarde"] = CreateRowXml(vergelijking.Naam);
                //row["analyseregel"] = CreateRowXml(vergelijking.Naam, ToStringArray(searchrow.Table.Columns), ToStringArray(searchrow.ItemArray));
                //row["referentieregel"] = CreateRowXml(vergelijking.Naam, ToStringArray(found.Table.Columns), ToStringArray(found.ItemArray));
                row["analyseregel"]    = CreateRowXml(vergelijking.Naam, searchrow.FieldNames, searchrow.FieldValues);
                row["referentieregel"] = CreateRowXml(vergelijking.Naam, found.FieldNames, found.FieldValues);

                row["analysesleutelwaarde"]    = string.Join(", ", sleutel.fieldvalues);
                row["referentiesleutelwaarde"] = string.Join(", ", sleutel.fieldvalues);
                row["analyseveldwaarde"]       = string.Join(", ", searchrow.FieldValues);
                row["referentieveldwaarde"]    = string.Join(", ", found.FieldValues);

                ds.Tables["outputline"].Rows.Add(row);
            }
            match++;
        }
        public void EntryNoMatch(Vergelijking vergelijking, DataRegel searchrow, RegistratieItem searchitem, DataRegel found, string matcher, RegistratieItem analyse, RegistratieItem reference, RegistratieItem sleutel, bool countaserror)
        {
            DataRow row = ds.Tables["outputline"].NewRow();

            row["outputid"]    = koprow["outputid"];
            row["regelnummer"] = ds.Tables["outputline"].Rows.Count + 1;
            row["status"]      = "NO MATCH";

            row["controle"]         = matcher;
            row["sleutel"]          = CreateRowXml(vergelijking.Naam, sleutel.fieldnames, sleutel.fieldvalues);
            row["analysewaarde"]    = CreateRowXml(matcher, analyse.fieldnames, analyse.fieldvalues);
            row["referentiewaarde"] = CreateRowXml(matcher, reference.fieldnames, reference.fieldvalues);
            //row["analyseregel"] = CreateRowXml(vergelijking.Analysis.DatabronNaam, ToStringArray(searchrow.Table.Columns), ToStringArray(searchrow.ItemArray));
            //row["referentieregel"] = CreateRowXml(vergelijking.Reference.DatabronNaam, ToStringArray(found.Table.Columns), ToStringArray(found.ItemArray));
            row["analyseregel"]    = CreateRowXml(vergelijking.Analysis.DatabronNaam, searchrow.FieldNames, searchrow.FieldValues);
            row["referentieregel"] = CreateRowXml(vergelijking.Reference.DatabronNaam, found.FieldNames, found.FieldValues);

            row["analysesleutelwaarde"]    = string.Join(", ", sleutel.fieldvalues);
            row["referentiesleutelwaarde"] = string.Join(", ", sleutel.fieldvalues);
            row["analyseveldwaarde"]       = string.Join(", ", analyse.fieldvalues);
            row["referentieveldwaarde"]    = string.Join(", ", reference.fieldvalues);

            ds.Tables["outputline"].Rows.Add(row);
            if (countaserror)
            {
                nomatch++;
            }
        }
        public void EntryMatch(Vergelijking vergelijking, string matcher, string sleutelcolumname, string checkcolumnname, string checkvalue, DataRegel found)
        {
            if (Properties.Settings.Default.output_everything)
            {
                DataRow row = ds.Tables["outputline"].NewRow();
                row["outputid"]    = koprow["outputid"];
                row["regelnummer"] = ds.Tables["outputline"].Rows.Count + 1;
                row["status"]      = "VALID";
                //            row["melding"] = "check:" + checkname + " found:" + found[columnname] + " expected:" + checkvalue;
                //            row["referentieregel"] = RegistratieSource.ToString(found);
                //            //row["analyseregel"] = RegistratieSource.ToString(searchrow);
                //            ds.Tables["outputline"].Rows.Add(row);

                row["controle"]      = matcher;
                row["sleutel"]       = CreateRowXml(vergelijking.Naam, sleutelcolumname, Convert.ToString(found[sleutelcolumname]));
                row["analysewaarde"] = CreateRowXml(vergelijking.Naam, checkcolumnname, checkvalue);
                //row["referentiewaarde"] = CreateRowXml(vergelijking);
                row["referentiewaarde"] = DBNull.Value;
                //row["analyseregel"] = CreateRowXml(vergelijking.Naam, ToStringArray(found.Table.Columns), ToStringArray(found.ItemArray));
                row["analyseregel"]    = CreateRowXml(vergelijking.Naam, found.FieldNames, found.FieldValues);
                row["referentieregel"] = DBNull.Value;

                row["analysesleutelwaarde"]    = sleutelcolumname;
                row["referentiesleutelwaarde"] = DBNull.Value;
                row["analyseveldwaarde"]       = string.Join(", ", found.FieldValues);
                row["referentieveldwaarde"]    = DBNull.Value;


                ds.Tables["outputline"].Rows.Add(row);
            }
            match++;
        }
Exemple #5
0
        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();
            }
        }