private string GetFromString(DataTable dt)
{
// create from clause
string fromStr = sqlConnStr + "." + dt.TableName;
string tableName = dt.TableName;
ParentChildRelation dr = (ParentChildRelation)this.relations[tableName];
// create From String
if (IsLinkedToPatients(tableName))
{
if (dr != null)
{
fromStr += " INNER JOIN " + dr.ParentTable + " on " + dr.ParentTable + "." + dr.ParentKey + " = "
+ dr.ChildTable + "." + dr.ChildKey;
/* * uncomment this and comment out line above to compare shortCutted SQL
* fromStr = "("+fromStr+" INNER JOIN "+sqlConnStr+"."+dr.ParentTable+" on "+dr.ParentTable+"."+dr.ParentKey+" = "
+ dr.ChildTable+"."+ dr.ChildKey+")"
+ " INNER JOIN ["+this.keyDb+"].tmpSelectedPatients on "+ dr.ParentTable +".PatientId = tmpSelectedPatients.PatientId ";
*/
}
else
{
//fromStr += " INNER JOIN tmpSelectedPatients on "+tableName+".PatientId = tmpSelectedPatients.PatientId ";
fromStr += " INNER JOIN [" + this.keyDb + "].tmpSelectedPatients on " + tableName + ".PatientId = tmpSelectedPatients.PatientId ";
//fromStr += " INNER JOIN "+sqlConnStr+".Patients on "+tableName+".PatientId = Patients.PatientId ";
}
}
fromStr = " from " + fromStr;
return(fromStr);
}
/// <summary>
/// Returns the Select into sql string for a specified table for the DeIdentified privacy level.
/// </summary>
/// <param name="dt">DataTable for which SQL will be generated.</param>
/// <returns>Sql string.</returns>
///
private string GetDeidentifiedSelect(DataTable dt)
{
string selectStr = "";
string tableName = dt.TableName;
ParentChildRelation dr = (ParentChildRelation)this.relations[tableName];
DataColumnCollection cols = dt.Columns;
// if table contains PatientId, must add AnonymousId
if (dr == null && this.IsLinkedToPatients(tableName))
{
selectStr += " AnonymousId, ";
}
foreach (DataColumn col in cols)
{
if (!tableName.Equals("Patients"))
{
// the following columns should not be included.
if (IncludeColumn(col.ColumnName) && col.ColumnName.IndexOf("DateText") == -1)
{
selectStr += this.GetDeidentifiedSelectItem(col);
}
}
else
{
// Patient table is select statement is generated differently from all other tables
if (IncludePatientColumn(col.ColumnName) && col.ColumnName.IndexOf("City") == -1 &&
col.ColumnName.IndexOf("PostalCode") == -1)
{
selectStr += this.GetDeidentifiedSelectItem(col);
}
}
}
// remove last comma
selectStr = selectStr.Substring(0, selectStr.Length - 2);
return(selectStr);
}
private string GetLimitedSelect(DataTable dt)
{
string selectStr = "";
string tableName = dt.TableName;
ParentChildRelation dr = (ParentChildRelation)this.relations[tableName];
DataColumnCollection cols = dt.Columns;
// if table contains PatientId, must add AnonymousId
if (dr == null && this.IsLinkedToPatients(tableName))
{
selectStr += " AnonymousId, ";
}
foreach (DataColumn col in cols)
{
if (!tableName.Equals("Patients"))
{
if (this.IncludeColumn(col.ColumnName))
{
selectStr += col.Table.TableName + "." + col.ColumnName + ", ";
}
}
else
{
// Patient table is select statement is generated differently from all other tables
if (IncludePatientColumn(col.ColumnName))
{
selectStr += col.Table.TableName + "." + col.ColumnName + ", ";
}
}
}
// remove last comma
selectStr = selectStr.Substring(0, selectStr.Length - 2);
return(selectStr);
}
/// <summary>
/// Retrieves the SQL string to be used for select Into export for the specified table
/// </summary>
/// <param name="dt">DataTable which represents the database table to be exported.</param>
/// <returns>SQL string used for export.</returns>
public string GetSelectSql(DataTable dt)
{
if (!privacyLevel.Equals("identified") && IsIdentifiedOnlyExport(dt.TableName))
{
return(null);
}
ParentChildRelation dr = null;
if (dt.Columns[Patient.PatientId] == null)
{
dr = (ParentChildRelation)this.relations[dt.TableName];
}
string selectStr = "select ";
switch (this.privacyLevel)
{
case "identified":
selectStr += this.GetIdentifiedSelect(dt);
break;
case "deidentified":
selectStr += this.GetDeidentifiedSelect(dt);
break;
case "limited":
selectStr += this.GetLimitedSelect(dt);
break;
}
string fromStr = this.GetFromString(dt);
return(selectStr + " into " + dt.TableName + fromStr);
}
private string GetIdentifiedSelect(DataTable dt)
{
string selectStr = "";
string tableName = dt.TableName;
ParentChildRelation dr = (ParentChildRelation)this.relations[tableName];
if (dr == null && this.IsLinkedToPatients(dt.TableName))
{
selectStr += "tmpSelectedPatients.PtMRN, ";
}
DataColumnCollection cols = dt.Columns;
foreach (DataColumn col in cols)
{
selectStr += col.Table.TableName + "." + col.ColumnName + ", ";
}
// remove last comma
selectStr = selectStr.Substring(0, selectStr.Length - 2);
return(selectStr);
}
/// <summary>
/// Adds one DataRelation and names it according to the ChildTable.
/// </summary>
/// <param name="childTable">Name of the child table.</param>
/// <param name="parentTable"></param>
/// <param name="columnName"></param>
private void AddDataRelation(string childTable, string parentTable, string columnName)
{
ParentChildRelation pr = new ParentChildRelation(parentTable, childTable, columnName);
this.relations.Add(childTable, pr);
}