/// <summary>
/// Query a list of patients who are on Active Surveillance and Watchful Waiting (PatientId, PtMRN)
/// </summary>
/// <param name="consumer"></param>
public void QueryWatchfulWaiting(Connectivity.RecordConsumerDelegate consumer)
{
string sql =
@"
SELECT
DISTINCT S.PatientId, P.PtMRN
FROM Status S
INNER JOIN Patients P ON S.PatientId = P.PatientId
WHERE
S.Status IN ('Active Surveillance', 'Watchful Waiting')
AND S.StatusDisease LIKE '%Prostate%'
AND S.PatientId NOT IN
(
SELECT Status.PatientId
FROM Status
INNER JOIN Procedures ON Status.PatientId = Procedures.PatientId
WHERE Status IN ('Active Surveillance', 'Watchful Waiting')
AND ProcName IN ('CP','LP','LP_CONV','PP','RALP','RP','SalvCP','SalvLP','SalvRP')
AND ProcDate > StatusDate
)
";
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
Connectivity.ConsumeRecord(com, consumer);
}
///// <summary>
///// SELECT TableName, TableId
///// FROM TableMetadata
///// </summary>
///// <returns></returns>
//public DataTable ListTableMetadata()
//{
// SqlCommand com = DataAccessHelper.CreateCommand("spListTableMetadata");
// return DataAccessHelper.GetRecord(com).Tables[0];
//}
/// <summary>
/// SELECT MetadataTables.TableName, MetadataFields.FieldName
/// FROM MetadataTables
/// INNER JOIN MetadataFields
/// ON MetadataTables.TableId = MetadataFields.TableId
/// WHERE MetadataFields.FieldIsVirtual = 1
/// ORDER BY MetadataTables.TableName, MetadataFields.FieldName
/// </summary>
/// <param name="recordConsumer"></param>
public static void ReadVirtualFieldMetadata(Connectivity.RecordConsumerDelegate recordConsumer)
{
string sql = @"
SELECT
MetadataTables.TableName, MetadataFields.FieldName
FROM
MetadataTables
INNER JOIN MetadataFields
ON MetadataTables.TableId = MetadataFields.TableId
WHERE MetadataFields.FieldIsVirtual = 1
ORDER BY MetadataTables.TableName, MetadataFields.FieldName" ;
SqlCommand cmd = new SqlCommand(sql);
Connectivity.ConsumeRecord(cmd, recordConsumer);
}
/// <summary>
/// Consume a uniform view of ALL (optnal table filter) Field Metadata represented by the UNION of disease specific and DEFAULT metadata.
/// </summary>
/// <param name="consumer">Data consumer callback</param>
/// <param name="tableName">Filter field metadata by tablename (optional, non-NULL or EMPTY==All Table Fields)</param>
public static void ConsumeFieldMetadata(Connectivity.RecordConsumerDelegate consumer, string tableName)
{
// build WHERE clause
string whereTemplate =
@"
WHERE
a.AttributeId IS NOT NULL {0}
";
string whereSQL = string.Format(whereTemplate, !string.IsNullOrEmpty(tableName) ? "AND a.TableName = @TableName" : "");
SqlCommand com = DataAccessHelper.CreateSqlCommand(string.Format(GET_FIELD_METADATA_SQL, whereSQL));
if (!string.IsNullOrEmpty(tableName))
{
DataAccessHelper.AddStringInputParam(com, "TableName", tableName);
}
Connectivity.ConsumeRecord(com, consumer);
}
// /// <summary>
// /// Consumes records from a query returning metadata for all fields.
// /// </summary>
// /// <param name="consumer">record consumer</param>
// public static void ConsumeFieldMetadata(Connectivity.RecordConsumerDelegate consumer)
// {
// string sql =
// @"SELECT MetadataTables.TableName, MetadataFields.FieldName, MetadataFields.FieldDataType, MetadataFields.FieldIsVirtual,
// MetadataFields.FieldSuppress, MetadataFields.FieldOrder, MetadataFieldAttributes.AttributeName, MetadataFieldAttributeValues.AttributeValue
// FROM MetadataFields INNER JOIN MetadataTables ON MetadataFields.TableId = MetadataTables.TableId
// INNER JOIN MetadataFieldAttributeValues ON MetadataFields.FieldId = MetadataFieldAttributeValues.FieldId
// INNER JOIN MetadataFieldAttributes ON MetadataFieldAttributeValues.AttributeId = MetadataFieldAttributes.AttributeId
// ORDER BY TableName, FieldName, AttributeName, AttributeValue";
// SqlCommand command = new SqlCommand(sql);
// Connectivity.ConsumeRecord(command, consumer);
// }
// public static void ConsumeTableMetadata(Connectivity.RecordConsumerDelegate consumer)
// {
// // string sql =
// // @"SELECT TableName, TableLabel, TableDescription
// // FROM MetadataTables
// // ORDER BY TableName, TableLabel, TableDescription";
// string sql =
// @"SELECT TableName, 'TableLabel' AS TableAttributeName, TableLabel AS TableAttributeValue
// FROM MetadataTables
//
// UNION
//
// SELECT TableName, 'TableDescription' AS TableAttributeName, TableDescription AS TableAttributeValue
// FROM MetadataTables
//
// UNION
//
// SELECT
// MetadataTables.TableName,
// MetadataTableAttributes.TableAttributeName,
// MetadataTableAttributeValues.TableAttributeValue
// FROM MetadataTables
// INNER JOIN MetadataTableAttributeValues
// ON ( MetadataTables.TableId = MetadataTableAttributeValues.TableId )
// INNER JOIN MetadataTableAttributes
// ON ( MetadataTableAttributeValues.TableAttributeId = MetadataTableAttributes.TableAttributeId )
//
// ORDER BY TableName, TableAttributeName, TableAttributeValue";
// SqlCommand command = new SqlCommand(sql);
// Connectivity.ConsumeRecord(command, consumer);
// }
public static void ConsumeTableColumnMaxLengths(Connectivity.RecordConsumerDelegate consumer)
{
string sql =
@"SELECT C.TABLE_NAME,
C.COLUMN_NAME,
C.DATA_TYPE,
C.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_NAME = T.TABLE_NAME
AND C.TABLE_CATALOG = T.TABLE_CATALOG
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE C.TABLE_NAME NOT LIKE 'AuditLog_%' AND
C.TABLE_NAME NOT LIKE 'Z_%' AND
C.TABLE_SCHEMA = 'dbo' AND
C.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND
T.TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME" ;
SqlCommand command = new SqlCommand(sql);
Connectivity.ConsumeRecord(command, consumer);
}
/// <summary>
///
/// </summary>
/// <param name="recordConsumer"></param>
public static void ReadColumns(Connectivity.RecordConsumerDelegate recordConsumer)
{
string sql =
@"
SELECT
C.TABLE_NAME,
C.COLUMN_NAME,
C.DATA_TYPE,
C.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_NAME = T.TABLE_NAME
AND C.TABLE_CATALOG = T.TABLE_CATALOG
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE C.TABLE_NAME NOT LIKE 'AuditLog_%' AND
C.TABLE_NAME NOT LIKE 'Z_%' AND
C.TABLE_SCHEMA = 'dbo' AND
T.TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME";
SqlCommand cmd = new SqlCommand(sql);
Connectivity.ConsumeRecord(cmd, recordConsumer);
}
/// <summary>
/// Get the PSA Doubling Time for a patient's last few (ideally 3) PSAs.
/// </summary>
/// <remarks>
/// Internally, this method will fetch the n (ideally 3) most recent PSAs to compute doubling time, where n >= 2
/// and at least 2 of the PSAs differ by date. If n > 3, then at least 2 of the PSAs used have
/// the same date.
/// </remarks>
/// <param name="patientId">The PatientId</param>
/// <returns>
/// The PSA Doubling Time in months.
/// <br/>
/// If calculated using fewer than two positive (i.e. > 0) PSAs that differ by date, returns <bold>Single.PositiveInfinity</bold>.
/// User interface developers may choose to display this as null or empty.
/// </returns>
public float GetPSADoublingTime(int patientId)
{
/***
* Return all a patient's PSAs, in reverse (i.e. descending) date order,
* and accumulate PSAs into a collection until 3 PSAs in the collection differ by date.
* This accounts for multiple PSAs on the same date (unlikely but possible).
***/
// NOTE: there are some bogons the LabResult field (e.g. ",0.1"). Database-side filtering
// of commas would be unfriendly to cultures that use the comma as a decimal separator.
string sql = @"
SELECT LabDate, LabResult
FROM LabTests
WHERE PatientId = @PatientId
And LabTest = 'PSA'
And LabDate IS NOT NULL
And ISNUMERIC(LabResult) = 1
ORDER BY LabDate DESC" ;
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.Add(new SqlParameter("@PatientId", patientId));
PSADoublingTime dtCalculator = new PSADoublingTime();
DateTime?previousDate = null;
int labDateIndex = 0; // based the above query string
int labResultIndex = 1; // ditto
int dateDiffs = 0;
int psaCount = 0;
Connectivity.RecordConsumerDelegate consumerDelegate =
delegate(IDataRecord record)
{
if (!previousDate.HasValue)
{
previousDate = record.GetDateTime(labDateIndex);
}
DateTime psaDate = record.GetDateTime(labDateIndex);
float psaValue;
if (!float.TryParse(record.GetString(labResultIndex), out psaValue) || psaValue <= 0)
{
// the PSA we read was invalid or non-positive, so ignore it and continue reading PSAs
return(true);
}
dtCalculator.AddPSA(psaValue, psaDate);
// update our states
if (psaDate != previousDate)
{
dateDiffs++;
}
psaCount++;
previousDate = psaDate;
if (psaCount >= 3 && dateDiffs >= 2)
{
// we've satisfied our conditions, so terminate reading
return(false);
}
else
{
// continue reading PSAs
return(true);
}
};
Connectivity.ConsumeRecord(cmd, consumerDelegate);
if (psaCount < 2 || dateDiffs < 1)
{
return(float.PositiveInfinity);
}
try
{
PSADoublingTimeResults results = dtCalculator.CalculateVelocity();
return(results.doublingTimeMonth);
}
catch (NomogramDataException e)
{
return(float.PositiveInfinity);
}
}
/// <summary>
/// Consume a uniform view of ALL Field Metadata represented by the UNION of disease specific and DEFAULT metadata.
/// </summary>
/// <param name="consumer">Data consumer callback</param>
public static void ConsumeFieldMetadata(Connectivity.RecordConsumerDelegate consumer)
{
ConsumeFieldMetadata(consumer, null);
}
/// <summary>
/// Consume a uniform view of ALL Table Metadata (optnal table filter) represented by the UNION of disease specific and DEFAULT metadata.
/// </summary>
/// <param name="consumer">Data consumer callback</param>
/// <param name="tableName">Filter table metadata by tablename (optional, non-NULL or EMPTY==All Tables)</param>
public static void ConsumeTableMetadata(Connectivity.RecordConsumerDelegate consumer, string tableName)
{
string sql =
@"
SELECT
*
FROM
(
SELECT
table_metadata.TableId,
table_metadata.TableName,
table_metadata.TableDescription,
table_metadata.TableLabel,
table_metadata.TableAttributeId,
table_metadata.TableAttributeValueId,
table_metadata.TableAttributeName,
table_metadata.TableAttributeValue,
disease_metadata.DiseaseId,
disease_metadata.DiseaseName,
disease_metadata.DiseaseAttributeName,
disease_metadata.DiseaseAttributeValueId,
disease_metadata.DiseaseAttributeValue
FROM
(
-- get table metadata
SELECT
a.*,
b.TableAttributeValueId,
c.*,
b.TableAttributeValue
FROM MetadataTables a
LEFT OUTER JOIN MetadataTableAttributeValues b
ON a.TableId = b.TableId
LEFT OUTER JOIN MetadataTableAttributes c
ON c.TableAttributeId = b.TableAttributeId
) table_metadata
LEFT OUTER JOIN
(
-- get disease mapping to table metadata
SELECT
a.DiseaseAttributeValueId,
a.DiseaseAttributeValue,
b.DiseaseAttributeName,
c.DiseaseId,
c.DiseaseName,
f.TableId,
f.TableName,
e.TableAttributeId,
e.TableAttributeName,
d.TableAttributeValueId,
d.TableAttributeValue
FROM DiseaseAttributeValues a
INNER JOIN DiseaseAttributes b
ON a.DiseaseAttributeId = b.DiseaseAttributeId
INNER JOIN Diseases c
ON a.DiseaseId = c.DiseaseId
INNER JOIN MetadataTableAttributeValues d
ON b.DiseaseAttributeName = 'TableAttributeValueId' AND a.DiseaseAttributeValue = CAST(d.TableAttributeValueId AS varchar(255))
INNER JOIN MetadataTableAttributes e
ON d.TableAttributeId = e.TableAttributeId
INNER JOIN MetadataTables f
ON d.TableId = f.TableId
) disease_metadata
-- restrict to table and attribute value pri key
ON
table_metadata.TableId = disease_metadata.TableId
AND disease_metadata.TableAttributeValueId = table_metadata.TableAttributeValueId
) a
WHERE
a.TableAttributeId IS NOT NULL {0}
ORDER BY
a.TableName ASC,
a.TableAttributeName ASC
";
// optional where
string whereSQL = string.Empty;
if (!string.IsNullOrEmpty(tableName))
{
whereSQL = "AND a.TableName = @TableName";
}
SqlCommand com = DataAccessHelper.CreateSqlCommand(string.Format(sql, whereSQL));
if (!string.IsNullOrEmpty(tableName))
{
DataAccessHelper.AddStringInputParam(com, "TableName", tableName);
}
Connectivity.ConsumeRecord(com, consumer);
}
private static void _ConsumeBreastQSurveyData(string datasetSQL, string surveyType, string[] surveyItems, Dictionary <string, string> sqlParams, Connectivity.RecordConsumerDelegate callback)
{
string sqlTemplate =
@"
SELECT
*
FROM
(
SELECT
p.PatientId,
p.PtMRN,
s.SurveyId,
s.SurveyDate,
si.SurveyItemNum,
si.SurveyItemResult
FROM Surveys s
INNER JOIN ({0}) ds
ON s.PatientId = ds.PatientId
INNER JOIN Patients p
ON ds.PatientId = p.PatientId
INNER JOIN SurveyItems si
ON s.SurveyId = si.SurveyId
WHERE
s.SurveyType = @SurveyType
)
AS S
PIVOT
(
MIN(SurveyItemResult)
FOR SurveyItemNum IN ({1})
) AS P
ORDER BY
PatientId,
SurveyDate
";
List <string> sanitizedSurveyItems = new List <string>();
// validate non-alpha characters
System.Text.RegularExpressions.Regex regexValidator = new System.Text.RegularExpressions.Regex("[^a-zA-Z0-9]");
foreach (string item in surveyItems)
{
string sanitizedSurveyItem = regexValidator.Replace(item, "");
if (sanitizedSurveyItem == item && !sanitizedSurveyItems.Contains(sanitizedSurveyItem))
{
sanitizedSurveyItems.Add(sanitizedSurveyItem);
}
}
string dynamicColumns = "[" + string.Join("],[", sanitizedSurveyItems.ToArray()) + "]";
string fullSQL = string.Format(sqlTemplate, datasetSQL, dynamicColumns);
SqlCommand cmd = DataAccessHelper.CreateSqlCommand(fullSQL);
foreach (var param in sqlParams)
{
DataAccessHelper.AddStringInputParam(cmd, param.Key, param.Value);
}
Connectivity.ConsumeRecord(cmd, (record) =>
{
callback(record);
return(true);
});
}
public void ConsumeBreastQSurveyData(string datasetSQL, string surveyType, string[] surveyItems, Connectivity.RecordConsumerDelegate callback)
{
var sqlParams = new Dictionary <string, string>()
{
{ "SurveyType", surveyType }
};
_ConsumeBreastQSurveyData(datasetSQL, surveyType, surveyItems, sqlParams, callback);
}
public void ConsumeBreastQSurveyDataByPatient(string datasetSQL, int patientId, string surveyType, string[] surveyItems, Connectivity.RecordConsumerDelegate callback)
{
string sqlTemplate =
@"
SELECT
a.PatientId
FROM Patients a
INNER JOIN ({0}) b
ON a.PatientId = b.PatientId
WHERE
a.PatientId = {1}
";
// get sql
string fullDatasetSQL = string.Format(sqlTemplate, datasetSQL, patientId);
ConsumeBreastQSurveyData(fullDatasetSQL, surveyType, surveyItems, callback);
}
public void ConsumeBreastQSurveyDataByPhysician(string datasetSQL, string[] apptPhysicians, string surveyType, string[] surveyItems, Connectivity.RecordConsumerDelegate callback)
{
string sqlTemplate =
@"
SELECT
a.ApptPatientId AS [PatientId]
FROM Appointments a
INNER JOIN ({0}) b
ON a.ApptPatientId = b.PatientId
WHERE
a.ApptPhysician IN ({1})
";
var sqlParams = new Dictionary <string, string>();
// add apptCaseSurgeons list
for (int i = 0; i < apptPhysicians.Length; i++)
{
string paramName = "ApptPhysician" + i;
string paramValue = apptPhysicians[i];
sqlParams.Add(paramName, paramValue);
}
string apptCaseSurgeonList = "@" + string.Join(",@", sqlParams.Keys.ToArray());
// add survey type
sqlParams.Add("SurveyType", surveyType);
// get sql
string fullDatasetSQL = string.Format(sqlTemplate, datasetSQL, apptCaseSurgeonList);
_ConsumeBreastQSurveyData(fullDatasetSQL, surveyType, surveyItems, sqlParams, callback);
}
