private static bool HasID(DBTable table, Guid documentID, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddTable(table);
sqlSelect.AddAggregate(new Aggre_Count());
sqlSelect.AddCriteria(new Crit_MatchCriteria(table, "uiSkemaID", MatchType.Equal, documentID));
return(runner.SelectWithSingleAggregate(executer, connection, sqlSelect) > 0);
}
private static void FixMailBox(Guid pluginID, Guid groupID, DBTable gruppePostkasseTable, DBTable side1Table, DBTable side2Table, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumns(gruppePostkasseTable, "uiBrevID", "uiSkemaID", "txCPRNr", "txBesked");
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiGruppeID", MatchType.Equal, groupID));
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiPluginID", MatchType.Equal, pluginID));
using (DBReader reader = runner.GetReader(executer, connection, sqlSelect))
{
DBRow row;
int count = 0;
try
{
while ((row = reader.GetNextRow()) != null)
{
Guid documentID = (Guid)row["uiSkemaID"];
bool deleteIt = false;
int option = 0;
if (HasID(side1Table, documentID, runner, executer, connection))
{
if (HasID(side2Table, documentID, runner, executer, connection))
{
option = 1;
deleteIt = true;
}
else if (OnlyOnePage((String)row["txBesked"]))
{
option = 2;
deleteIt = true;
}
if (deleteIt)
{
// Delete the document
Output(option + "," + row["txCPRNr"] + "," + documentID.ToString("B").ToUpper());
SQL_DeleteStatement sqlDelete = new SQL_DeleteStatement(gruppePostkasseTable);
sqlDelete.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiBrevID", MatchType.Equal, row["uiBrevID"]));
runner.Delete(executer, connection, sqlDelete);
count++;
}
}
}
}
finally
{
Output("Deleted " + count + " documents");
}
}
}
private static Guid FindPluginID(DBTable pluginsTable, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(pluginsTable, "uiPluginID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(pluginsTable, "txNavn", MatchType.Equal, "dk.hob.ei.mortality.Plugin"));
DBRow row = runner.SelectAndReturnFirstRow(executer, connection, sqlSelect);
if (row == null)
{
return(Guid.Empty);
}
return((Guid)row["uiPluginID"]);
}
private static Guid FindGroupID(DBTable gruppeTable, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(gruppeTable, "uiGruppeID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppeTable, "txNavn", MatchType.Equal, "Dødsattester midlertidig opbevaring"));
DBRow row = runner.SelectAndReturnFirstRow(executer, connection, sqlSelect);
if (row == null)
{
return(Guid.Empty);
}
return((Guid)row["uiGruppeID"]);
}
private static void AddIDs(SQL_SelectStatement sqlSelect, DBRunner runner, ISQLExecuter executer, DBConnection connection, Dictionary <Guid, Object> ids)
{
using (DBReader reader = runner.GetReader(executer, connection, sqlSelect))
{
DBRow row;
while ((row = reader.GetNextRow()) != null)
{
if (row["uiSkemaID"] != null)
{
Guid id = (Guid)row["uiSkemaID"];
ids[id] = null;
}
}
}
}
private static void FindDocuments(DBTable pluginsTable, DBTable gruppePostkasseTable, DBTable revisionssporTable, DBRunner runner, ISQLExecuter executer, DBConnection connection, String plugin, params String[] tables)
{
Output("");
Output("Find plugin ID for " + plugin);
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(pluginsTable, "uiPluginID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(pluginsTable, "txNavn", MatchType.Equal, plugin));
DBRow row = runner.SelectAndReturnFirstRow(executer, connection, sqlSelect);
if (row == null)
{
throw new Exception("Couldn't find plugin ID");
}
Guid pluginID = (Guid)row["uiPluginID"];
// Find all the document IDs
Dictionary <Guid, Object> documentIDs = new Dictionary <Guid, Object>();
Output("Find document IDs for " + gruppePostkasseTable.TableName);
sqlSelect = new SQL_SelectStatement();
sqlSelect.Distinct = true;
sqlSelect.AddColumn(gruppePostkasseTable, "uiSkemaID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiPluginID", MatchType.Equal, pluginID));
AddIDs(sqlSelect, runner, executer, connection, documentIDs);
foreach (String tableName in tables)
{
Output("Find document IDs for " + tableName);
DBTable table = pluginsTable.Database.AddTable("EISST", tableName);
table.AddColumn("uiSkemaID", ColumnType.Guid, ColumnFlag.None);
sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(table, "uiSkemaID");
AddIDs(sqlSelect, runner, executer, connection, documentIDs);
}
Output("Check the revisionsspor to see which documents are missing");
sqlSelect = new SQL_SelectStatement();
sqlSelect.Distinct = true;
sqlSelect.AddColumn(revisionssporTable, "uiSkemaID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "uiPluginID", MatchType.Equal, pluginID));
sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "iType", MatchType.Equal, (short)0));
sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "uiSkemaID", MatchType.IsNotNull));
sqlSelect.AddSort(revisionssporTable, "uiSkemaID", Order.Ascending);
using (DBReader reader = runner.GetReader(executer, connection, sqlSelect))
{
while ((row = reader.GetNextRow()) != null)
{
Guid id = (Guid)row["uiSkemaID"];
if (!documentIDs.ContainsKey(id))
{
Output(id.ToString("B").ToUpper());
}
}
}
}
