/// <summary>
/// Fetches and returns project number, version etc listed in the remote cohort database for this cohort
/// </summary>
public IExternalCohortDefinitionData GetExternalData()
{
var db = ExternalCohortTable.Discover();
var syntax = db.Server.GetQuerySyntaxHelper();
string sql =
$@"Select
{syntax.EnsureWrapped("projectNumber")},
{syntax.EnsureWrapped("description")},
{syntax.EnsureWrapped("version")},
{syntax.EnsureWrapped("dtCreated")}
from {ExternalCohortTable.DefinitionTableName}
where
{syntax.EnsureWrapped("id")} = {OriginID}";
using (var con = db.Server.GetConnection())
{
con.Open();
using (var getDescription = db.Server.GetCommand(sql, con))
{
using (var r = getDescription.ExecuteReader())
{
if (!r.Read())
{
throw new Exception("No records returned for Cohort OriginID " + OriginID);
}
return(new ExternalCohortDefinitionData(r, ExternalCohortTable.Name));
}
}
}
}
/// <summary>
/// Returns the remote DataTable row held in <paramref name="externalSource"/> that describes all cohorts held in it (that have at least one identifier mapping).
/// </summary>
/// <param name="externalSource"></param>
/// <param name="displayMemberName"></param>
/// <param name="valueMemberName"></param>
/// <param name="versionMemberName"></param>
/// <param name="projectNumberMemberName"></param>
/// <returns></returns>
public static DataTable GetImportableCohortDefinitionsTable(ExternalCohortTable externalSource, out string displayMemberName, out string valueMemberName, out string versionMemberName, out string projectNumberMemberName)
{
var server = externalSource.Discover().Server;
var syntax = server.GetQuerySyntaxHelper();
using (var con = server.GetConnection())
{
con.Open();
string sql =
$@"Select
{syntax.EnsureWrapped("description")},
{syntax.EnsureWrapped("id")},
{syntax.EnsureWrapped("version")},
{syntax.EnsureWrapped("projectNumber")}
from {externalSource.DefinitionTableName}
where
exists (Select 1 from {externalSource.TableName} WHERE {externalSource.DefinitionTableForeignKeyField}=id)";
using (var da = server.GetDataAdapter(sql, con))
{
displayMemberName = "description";
valueMemberName = "id";
versionMemberName = "version";
projectNumberMemberName = "projectNumber";
DataTable toReturn = new DataTable();
da.Fill(toReturn);
return(toReturn);
}
}
}
/// <summary>
/// Returns the remote DataTable row held in <paramref name="externalSource"/> that describes all cohorts held in it (that have at least one identifier mapping).
/// </summary>
/// <param name="externalSource"></param>
/// <param name="displayMemberName"></param>
/// <param name="valueMemberName"></param>
/// <param name="versionMemberName"></param>
/// <param name="projectNumberMemberName"></param>
/// <returns></returns>
public static DataTable GetImportableCohortDefinitionsTable(ExternalCohortTable externalSource, out string displayMemberName, out string valueMemberName, out string versionMemberName, out string projectNumberMemberName)
{
var server = externalSource.Discover().Server;
using (var con = server.GetConnection())
{
con.Open();
string sql = string.Format(
"Select description,id,version,projectNumber from {0} where exists (Select 1 from {1} WHERE {2}=id)"
, externalSource.DefinitionTableName,
externalSource.TableName,
externalSource.DefinitionTableForeignKeyField);
var da = server.GetDataAdapter(sql, con);
displayMemberName = "description";
valueMemberName = "id";
versionMemberName = "version";
projectNumberMemberName = "projectNumber";
DataTable toReturn = new DataTable();
da.Fill(toReturn);
return(toReturn);
}
}
/// <inheritdoc/>
public string GetReleaseIdentifierDataType()
{
DiscoveredTable table = ExternalCohortTable.Discover().ExpectTable(ExternalCohortTable.TableName);
//discover the column
return(table.DiscoverColumn(GetReleaseIdentifier(true))
.DataType.SQLType); //and return it's datatype
}
/// <inheritdoc/>
public IQuerySyntaxHelper GetQuerySyntaxHelper()
{
if (_cachedQuerySyntaxHelper == null)
{
_cachedQuerySyntaxHelper = ExternalCohortTable.GetQuerySyntaxHelper();
}
return(_cachedQuerySyntaxHelper);
}
/// <summary>
/// Returns details of all cohorts held in <paramref name="externalSource"/> (that have at least one identifier mapping).
/// </summary>
/// <param name="externalSource"></param>
/// <returns></returns>
public static IEnumerable <CohortDefinition> GetImportableCohortDefinitions(ExternalCohortTable externalSource)
{
string displayMemberName, valueMemberName, versionMemberName, projectNumberMemberName;
DataTable dt = GetImportableCohortDefinitionsTable(externalSource, out displayMemberName, out valueMemberName, out versionMemberName, out projectNumberMemberName);
foreach (DataRow r in dt.Rows)
{
yield return
(new CohortDefinition(
Convert.ToInt32(r[valueMemberName]),
r[displayMemberName].ToString(),
Convert.ToInt32(r[versionMemberName]),
Convert.ToInt32(r[projectNumberMemberName])
, externalSource));
}
}
/// <summary>
/// Creates a new cohort reference in the data export database. This must resolve (via <paramref name="originalId"/>) to
/// a row in the external cohort database (<paramref name="externalSource"/>).
/// </summary>
/// <param name="repository"></param>
/// <param name="externalSource"></param>
/// <param name="originalId"></param>
public ExtractableCohort(IDataExportRepository repository, ExternalCohortTable externalSource, int originalId)
{
Repository = repository;
if (!externalSource.IDExistsInCohortTable(originalId))
{
throw new Exception("ID " + originalId + " does not exist in Cohort Definitions (Referential Integrity Problem)");
}
Repository.InsertAndHydrate(this, new Dictionary <string, object>
{
{ "OriginID", originalId },
{ "ExternalCohortTable_ID", externalSource.ID }
});
ClearAllInjections();
}
/// <inheritdoc/>
public IExternalCohortDefinitionData GetExternalData(int timeoutInSeconds = -1)
{
var db = ExternalCohortTable.Discover();
var syntax = db.Server.GetQuerySyntaxHelper();
string sql =
$@"Select
{syntax.EnsureWrapped("projectNumber")},
{syntax.EnsureWrapped("description")},
{syntax.EnsureWrapped("version")},
{syntax.EnsureWrapped("dtCreated")}
from {ExternalCohortTable.DefinitionTableName}
where
{syntax.EnsureWrapped("id")} = {OriginID}";
if (timeoutInSeconds != -1)
{
db.Server.TestConnection(timeoutInSeconds * 1000);
}
using (var con = db.Server.GetConnection())
{
con.Open();
using (var getDescription = db.Server.GetCommand(sql, con))
{
if (timeoutInSeconds != -1)
{
getDescription.CommandTimeout = timeoutInSeconds;
}
using (var r = getDescription.ExecuteReader())
{
if (!r.Read())
{
return(ExternalCohortDefinitionData.Orphan);
}
return(new ExternalCohortDefinitionData(r, ExternalCohortTable.Name));
}
}
}
}
/// <summary>
/// Fetches and returns project number, version etc listed in the remote cohort database for this cohort
/// </summary>
public IExternalCohortDefinitionData GetExternalData()
{
string sql = @"select projectNumber, description,version,dtCreated from " + ExternalCohortTable.DefinitionTableName + " where id = " + OriginID;
var db = ExternalCohortTable.Discover();
using (var con = db.Server.GetConnection())
{
con.Open();
var getDescription = db.Server.GetCommand(sql, con);
var r = getDescription.ExecuteReader();
if (!r.Read())
{
throw new Exception("No records returned for Cohort OriginID " + OriginID);
}
return(new ExternalCohortDefinitionData(r, ExternalCohortTable.Name));
}
}
/// <inheritdoc/>
public void InjectKnown(ExternalCohortTable instance)
{
_knownExternalCohortTable = new Lazy <IExternalCohortTable>(() => instance);
}
/// <inheritdoc/>
public void ReverseAnonymiseDataTable(DataTable toProcess, IDataLoadEventListener listener, bool allowCaching)
{
int haveWarnedAboutTop1AlreadyCount = 10;
var syntax = ExternalCohortTable.GetQuerySyntaxHelper();
string privateIdentifier = syntax.GetRuntimeName(GetPrivateIdentifier());
string releaseIdentifier = syntax.GetRuntimeName(GetReleaseIdentifier());
//if we don't want to support caching or there is no cached value yet
if (!allowCaching || _releaseToPrivateKeyDictionary == null)
{
DataTable map = FetchEntireCohort();
Stopwatch sw = new Stopwatch();
sw.Start();
//dictionary of released values (for the cohort) back to private values
_releaseToPrivateKeyDictionary = new Dictionary <string, string>();
foreach (DataRow r in map.Rows)
{
if (_releaseToPrivateKeyDictionary.Keys.Contains(r[releaseIdentifier]))
{
if (haveWarnedAboutTop1AlreadyCount > 0)
{
haveWarnedAboutTop1AlreadyCount--;
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Top 1-ing will occur for release identifier " + r[releaseIdentifier] + " because it maps to multiple private identifiers"));
}
else
{
if (haveWarnedAboutTop1AlreadyCount == 0)
{
haveWarnedAboutTop1AlreadyCount = -1;
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Top 1-ing error message disabled due to flood of messages"));
}
}
}
else
{
_releaseToPrivateKeyDictionary.Add(r[releaseIdentifier].ToString().Trim(), r[privateIdentifier].ToString().Trim());
}
_reverseAnonymiseProgressFetchingMap++;
if (_reverseAnonymiseProgressFetchingMap % 500 == 0)
{
listener.OnProgress(this, new ProgressEventArgs("Assembling Release Map Dictionary", new ProgressMeasurement(_reverseAnonymiseProgressFetchingMap, ProgressType.Records), sw.Elapsed));
}
}
listener.OnProgress(this, new ProgressEventArgs("Assembling Release Map Dictionary", new ProgressMeasurement(_reverseAnonymiseProgressFetchingMap, ProgressType.Records), sw.Elapsed));
}
int nullsFound = 0;
int substitutions = 0;
Stopwatch sw2 = new Stopwatch();
sw2.Start();
//fix values
foreach (DataRow row in toProcess.Rows)
{
try
{
object value = row[releaseIdentifier];
if (value == null || value == DBNull.Value)
{
nullsFound++;
continue;
}
row[releaseIdentifier] = _releaseToPrivateKeyDictionary[value.ToString().Trim()].Trim();//swap release value for private value (reversing the anonymisation)
substitutions++;
_reverseAnonymiseProgressReversing++;
if (_reverseAnonymiseProgressReversing % 500 == 0)
{
listener.OnProgress(this, new ProgressEventArgs("Substituting Release Identifiers For Private Identifiers", new ProgressMeasurement(_reverseAnonymiseProgressReversing, ProgressType.Records), sw2.Elapsed));
}
}
catch (KeyNotFoundException e)
{
throw new Exception("Could not find private identifier (" + privateIdentifier + ") for the release identifier (" + releaseIdentifier + ") with value '" + row[releaseIdentifier] + "' in cohort with cohortDefinitionID " + OriginID, e);
}
}
//final value
listener.OnProgress(this, new ProgressEventArgs("Substituting Release Identifiers For Private Identifiers", new ProgressMeasurement(_reverseAnonymiseProgressReversing, ProgressType.Records), sw2.Elapsed));
if (nullsFound > 0)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Found " + nullsFound + " null release identifiers amongst the " + toProcess.Rows.Count + " rows of the input data table (on which we were attempting to reverse annonymise)"));
}
listener.OnNotify(this, new NotifyEventArgs(substitutions > 0?ProgressEventType.Information : ProgressEventType.Error, "Substituted " + substitutions + " release identifiers for private identifiers in input data table (input data table contained " + toProcess.Rows.Count + " rows)"));
toProcess.Columns[releaseIdentifier].ColumnName = privateIdentifier;
}
/// <inheritdoc/>
public DiscoveredDatabase GetDatabaseServer()
{
return(ExternalCohortTable.Discover());
}
/// <inheritdoc/>
public string GetReleaseIdentifierDataType()
{
return(ExternalCohortTable.DiscoverReleaseIdentifier().DataType.SQLType);
}
