/// <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>
/// Query a Colorectal patients active survailance state
/// </summary>
/// <param name="patientId"></param>
/// <returns></returns>
public DateTime?QueryColorectalActiveSurvailance(int patientId)
{
DateTime?statusDate = null;
string sql =
@"
SELECT b.PtMrn, MIN(StatusDate)MinStatusDate --, a.PatientId, status
FROM Status a INNER JOIN Patients b ON a.PatientId = b.PatientId
WHERE
b.PatientId = @PatientId
AND StatusDisease = 'Rectal Cancer'
AND Status IN ('Active surveillance/NED','Active surveillance/AWD')
GROUP BY a.PatientId, PtMRN, Status
";
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
DataAccessHelper.AddIntInputParam(com, "PatientId", patientId);
Connectivity.ConsumeRecord(com, (r) =>
{
int fieldIndex = r.GetOrdinal("MinStatusDate");
if (!r.IsDBNull(fieldIndex))
{
statusDate = (DateTime)r.GetDateTime(fieldIndex);
}
return(true);
});
return(statusDate);
}
public static DataView GetLookupData(string tablename, string valueFieldname, string textFieldname,
string restrictions, string order)
{
DataTable table = new DataTable();
Connectivity.GetDistinctForLookup(tablename, valueFieldname, textFieldname, restrictions, order, table);
return(table.DefaultView);
}
/// <summary>
/// Get the audit log for a speficic record in the database, selecting a sequence of fields in the table, in addition to virtual fields
/// associated with the table.
/// </summary>
/// <param name="tableName"></param>
/// <param name="primaryKeyName"></param>
/// <param name="primaryKey"></param>
/// <param name="normalFields">List of fields (in the table) to retrieve for the log</param>
/// <param name="virtualFields">Virtual fields (if any)</param>
/// <returns></returns>
public DataTable GetVirtualAuditByTableAndPrimaryKey(string tableName, string primaryKeyName, int primaryKey, IEnumerable <string> normalFields, IEnumerable <string> virtualFields)
{
DataTable table = new DataTable();
using (SqlConnection connection = Connectivity.GetConnection())
{
string sql = SqlBuilder.BuildVirtualAuditSelectStatement(tableName, primaryKeyName, primaryKey, normalFields, virtualFields);
SqlCommand cmd = new SqlCommand(sql, connection);
DataAccessHelper.GetList(cmd, table);
}
return(table);
}
///// <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>
/// Get the audit log for a specific record in the database.
/// </summary>
/// <param name="tableName">The name of the table containing the record.</param>
/// <param name="priKeyName">The name of the primary key of the table to be audited.</param>
/// <param name="priKeyValue">the value of the primary key to be audited</param>
/// <returns></returns>
//public DataTable GetAuditByTableAndPrimaryKey(string tableName, string priKeyName, int priKeyValue, bool tableHasVirtualFields)
//{
// SqlConnection con = Connectivity.GetConnection();
// SqlCommand comm = new SqlCommand("select * from AuditLog_" + tableName + " where " + priKeyName + "=" + priKeyValue + " ORDER BY LoggedTime DESC", con);
// DataTable dt = new DataTable();
// DataAccessHelper.GetList(comm, dt);
// return dt;
//}
/// <summary>
/// Get the audit log for a specific record in the database with only the columns specified in the selectFields list.
/// </summary>
/// <param name="tableName"></param>
/// <param name="priKeyName"></param>
/// <param name="priKeyValue"></param>
/// <param name="selectFields">List of fields to retrieve for the log</param>
/// <returns></returns>
public DataTable GetAuditByTableAndPrimaryKey(string tableName, string priKeyName, int priKeyValue, IEnumerable <string> inclusionFields)
{
//if (inclusionFields.Count == 0)
//{
// return GetAuditByTableAndPrimaryKey(tableName, priKeyName, priKeyValue);
//}
SqlConnection con = Connectivity.GetConnection();
string fieldsString = "";
foreach (string s in inclusionFields)
{
fieldsString += s + ",";
}
fieldsString = fieldsString.Remove(fieldsString.Length - 1);
// TODO: support virtual fields
SqlCommand comm = new SqlCommand("select " + fieldsString + " from AuditLog_" + tableName + " where " + priKeyName + "=" + priKeyValue + " ORDER BY LoggedTime DESC", con);
DataTable dt = new DataTable();
DataAccessHelper.GetList(comm, dt);
return(dt);
}
public static void ConsumeProstateQOLSurveyDates(int patientId, Func <int, DateTime, bool> consumer)
{
string sql =
@"SELECT SurveyId, SurveyDate
FROM Surveys
WHERE PatientId = @PatientId
AND SurveyDate IS NOT NULL
AND SurveyType = 'Prostate QOL Survey'
ORDER BY SurveyDate" ;
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@PatientId", patientId);
Func <IDataRecord, bool> f =
r =>
{
return(consumer(r.GetInt32(0), r.GetDateTime(1)));
};
Connectivity.ConsumeRecord(cmd, new Connectivity.RecordConsumerDelegate(f));
}
public static IDictionary <KeyValuePair <string, string>, string> GetSurveyItemResponseMap(string surveyType)
{
string sql =
@"SELECT
MetadataSurveyQuestions.QuestionNumber,
MetadataSurveyQuestions.Question,
MetadataQuestionResponses.ResponseText,
MetadataQuestionResponses.ResponseValue
FROM MetadataQuestionResponses
INNER JOIN MetadataSurveyQuestions
ON MetadataQuestionResponses.MetadataSurveyQuestionId = MetadataSurveyQuestions.MetadataSurveyQuestionId
INNER JOIN MetadataSurveys
ON MetadataSurveyQuestions.MetadataSurveyId = MetadataSurveys.MetadataSurveyId
WHERE MetadataSurveys.SurveyType = @SurveyType" ;
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@SurveyType", surveyType);
Dictionary <KeyValuePair <string, string>, string> map = new Dictionary <KeyValuePair <string, string>, string>();
Func <IDataRecord, bool> consumer =
record =>
{
string num = record.GetString(0);
string question = record.GetString(1);
string response = record.GetString(2);
string val = record.GetString(3);
map[new KeyValuePair <string, string>(question, val)] = response;
map[new KeyValuePair <string, string>(num, val)] = response;
return(true);
};
Connectivity.ConsumeRecord(cmd, new Connectivity.RecordConsumerDelegate(consumer));
return(map);
}
// /// <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);
}
public static BaselineScores GetPhysicianBaselineScores(int patientId, DateTime procedureDate)
{
#region sql
string sql =
@"SELECT
CASE
WHEN UrinaryFunction.SurveyDate <= CS.SurveyDate AND
COALESCE(UrinaryFunction.SurveyResult, UrinaryFunction.SurveyTotal) IS NOT NULL
THEN COALESCE(UrinaryFunction.SurveyResult, UrinaryFunction.SurveyTotal)
WHEN CS.SurveyDate < UrinaryFunction.SurveyDate AND
Continence.SurveyItemResult IS NOT NULL
THEN Continence.SurveyItemResult
ELSE COALESCE(UrinaryFunction.SurveyResult, UrinaryFunction.SurveyTotal, Continence.SurveyItemResult)
END AS BaselineUFScore,
CASE
WHEN SexualFunction.SurveyDate <= EFS.SurveyDate AND
COALESCE(SexualFunction.SurveyResult, SexualFunction.SurveyTotal) IS NOT NULL
THEN COALESCE(SexualFunction.SurveyResult, SexualFunction.SurveyTotal)
WHEN EFS.SurveyDate < SexualFunction.SurveyDate AND
ErectileFunction.SurveyItemResult IS NOT NULL
THEN ErectileFunction.SurveyItemResult
ELSE COALESCE(SexualFunction.SurveyResult, SexualFunction.SurveyTotal, ErectileFunction.SurveyItemResult)
END AS BaselineEFScore
FROM
Procedures P
-- urinary
LEFT OUTER JOIN Surveys UrinaryFunction
ON UrinaryFunction.SurveyId =
(
SELECT TOP 1 SurveyId
FROM Surveys
WHERE PatientId = P.PatientId
AND SurveyType = 'Urinary Function'
AND SurveyDate > P.ProcDate
ORDER BY SurveyDate
)
LEFT OUTER JOIN SurveyItems Continence
ON Continence.SurveyItemId =
(
SELECT TOP 1 SurveyItemId
FROM Surveys
INNER JOIN SurveyItems
ON Surveys.SurveyId = SurveyItems.SurveyId
AND SurveyItem = 'Continence'
WHERE PatientId = P.PatientId
AND SurveyType = 'Urinary Function'
AND SurveyDate > P.ProcDate
ORDER BY SurveyDate
)
LEFT OUTER JOIN Surveys CS
ON Continence.SurveyId = CS.SurveyId
-- erectile
LEFT OUTER JOIN Surveys SexualFunction
ON SexualFunction.SurveyId =
(
SELECT TOP 1 SurveyId
FROM Surveys
WHERE PatientId = P.PatientId
AND SurveyType = 'Sexual Function'
AND SurveyDate > P.ProcDate
ORDER BY SurveyDate
)
LEFT OUTER JOIN SurveyItems ErectileFunction
ON ErectileFunction.SurveyItemId =
(
SELECT TOP 1 SurveyItemId
FROM Surveys
INNER JOIN SurveyItems
ON Surveys.SurveyId = SurveyItems.SurveyId
AND SurveyItem = 'Erectile Function'
WHERE PatientId = P.PatientId
AND SurveyType = 'Sexual Function'
AND SurveyDate > P.ProcDate
ORDER BY SurveyDate
)
LEFT OUTER JOIN Surveys EFS
ON ErectileFunction.SurveyId = EFS.SurveyId
WHERE P.ProcName IN ('CP','LP','LP_CONV','PP','RALP','RP','SalvCP','SalvLP','SalvRP') AND
P.ProcDate = @ProcDate AND
P.PatientId = @PatientId" ;
#endregion
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@PatientId", patientId);
cmd.Parameters.AddWithValue("@ProcDate", procedureDate);
BaselineScores baseline = new BaselineScores();
Connectivity.ConsumeRecord(
cmd,
record =>
{
int ef, uf;
if (!record.IsDBNull(0) && int.TryParse(record.GetString(0), out uf))
{
baseline.UrinaryFunction = uf;
}
if (!record.IsDBNull(1) && int.TryParse(record.GetString(1), out ef))
{
baseline.ErectileFunction = ef;
}
return(false);
}
);
return(baseline);
}
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);
});
}
/// <summary>
/// Visit each item in the Prostate GU HPI and process the results to the consumer callback.
/// </summary>
/// <param name="PatientId"></param>
/// <param name="ShowEFormValues"></param>
/// <param name="consumer"></param>
private static void ConsumePatientHPIProstateGU(int PatientId, System.Byte ShowEFormValues, Action <Dictionary <string, object> > consumer)
{
SqlCommand com = DataAccessHelper.CreateCommand("spGetPatientHPIProstateGU");
DataAccessHelper.AddIntInputParam(com, "PatientId", PatientId);
DataAccessHelper.AddBitInputParam(com, "EForm", ShowEFormValues);
char[] sep1 = { '|' };
char[] sep2 = { ',' };
int groupIndex = 0;
// while row data returned, cleanup and notify consumer with clean data
Connectivity.ConsumeRecord(com, (record) =>
{
// maybe move this into a RowFilter for a DataView later - jf
string filter = record["Filters"].ToString();
string[] tableNames = record["TableNames"].ToString().Split(sep2);
string[] primaryKeys = record["PrimaryKeys"].ToString().Split(sep2);
string[] hpiItems = record["patientHPIDelimited"].ToString().Split(sep1, StringSplitOptions.RemoveEmptyEntries);
string hpiString = record["patientHPIDelimited"].ToString();
string sortDate = record["SortDate"].ToString();
int numberOfHpiItems = tableNames.Length; //primaryKeys.Length;
string hpi;
if (numberOfHpiItems > 0)
{
DateTime hpiDate;
string hpiDateString = "";
// group like items into grouping
for (int i = 0; i < hpiItems.Length; i++)
{
int groupRowIndex = i;
var hpiItem = hpiItems[i];
string table = "";
// this if statement is here to catch the special case of there being a mismatch in the lengths of patientHPIDelimited, tablenames, and primarykeys
if (i < tableNames.Length)
{
table = tableNames[i];
}
int priKey;
string priKeyStr = "";
// this extra if statement is here to catch the special case of there being a mismatch in the lengths of patientHPIDelimited, tablenames, and primarykeys
if (i < primaryKeys.Length)
{
priKeyStr = primaryKeys[i];
}
bool priKeyIsInt = Int32.TryParse(priKeyStr, out priKey);
string endDate = "";
string item = "";
if (table.Length > 0)
{
hpi = hpiItem.Replace("__", "");
var pairs = hpi.Split(' ');
// extract dates
// get End Date
if (pairs.First().Contains("-"))
{
var dateField = pairs.First().Split('-');
hpiDateString = dateField.ElementAt(0);
endDate = dateField.ElementAt(1);
item = string.Join(" ", pairs.Skip(1).Take(pairs.Count()).ToArray());
}
else
{
if (pairs.Count() > 0)
{
if (i == 0)
{
if (DateTime.TryParse(pairs.First(), out hpiDate))
{
hpiDateString = pairs.First();
item = string.Join(" ", pairs.Skip(1).Take(pairs.Count()).ToArray());
}
else
{
item = string.Join(" ", pairs.ToArray());
}
}
else
{
item = string.Join(" ", pairs.ToArray());
}
}
}
// create callback data source
Dictionary <string, object> consumerData = new Dictionary <string, object>();
// set field values
consumerData["Table"] = table;
consumerData["PriKey"] = priKey;
DateTime sDateParse = DateTime.Now;
DateTime eDateParse = DateTime.Now;
if (!string.IsNullOrEmpty(sortDate) && DateTime.TryParse(sortDate, out sDateParse))
{
consumerData["StartDate"] = sDateParse;
}
else if (!string.IsNullOrEmpty(hpiDateString) && DateTime.TryParse(hpiDateString, out sDateParse))
{
consumerData["StartDate"] = sDateParse;
}
else
{
consumerData["StartDate"] = DBNull.Value;
}
if (!string.IsNullOrEmpty(endDate) && DateTime.TryParse(endDate, out eDateParse))
{
consumerData["EndDate"] = eDateParse;
}
else
{
consumerData["EndDate"] = DBNull.Value;
}
// date text fields
consumerData["StartDateText"] = hpiDateString;
consumerData["EndDateText"] = endDate;
consumerData["Item"] = item;
consumerData["Filters"] = filter;
// set lookup indexes
consumerData["GroupIndex"] = groupIndex;
consumerData["GroupRowIndex"] = groupRowIndex;
// call consumer with current data
consumer(consumerData);
}
else
{
}
}
}
else
{
}
// increment
groupIndex++;
// continue
return(true);
});
}
/// <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 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);
}
public static void ConsumeStarDataPoints(int patientId, Func <IDataRecord, bool> consumer)
{
#region sql
string sql =
@"SELECT
Patients.PatientId,
DATEDIFF(year, PtBirthDate, GETDATE()) AS PtAge,
PreSurgeryPSA.LabResult AS PreSurgeryPSA,
RP.ProcDateText AS RPDate,
PP.PathGG1,
PP.PathGG2,
PP.PathExtension AS ECE,
CASE
WHEN PP.PathExtension IN ('Established', 'Focal') THEN 1
WHEN PP.PathExtension IN ('Invades Capsule', 'None') THEN 0
ELSE NULL
END AS ECE_Parsed,
PP.PathSV_Inv AS SVI,
CASE
WHEN PP.PathSV_Inv = 'Positive' THEN 1
WHEN PP.PathSV_Inv = 'Negative' THEN 0
ELSE NULL
END as SVI_Parsed,
PLND_Path.PathResult AS LNI,
CASE
WHEN PLND_Path.PathResult LIKE '%normal%' THEN 0
WHEN PLND_Path.PathResult LIKE 'Pos%' THEN 1
WHEN PLND_Path.PathResult LIKE 'ABN%' THEN 1
WHEN PLND_Path.PathResult LIKE 'Negative%' THEN 0
WHEN PLND_Path.PathResult LIKE 'No%' THEN 0
WHEN PLND_Path.PathResult IS NULL THEN 0
ELSE NULL
END AS LNI_Parsed,
CASE
WHEN PLND.ProcedureID IS NOT NULL THEN 'Yes'
ELSE 'No'
END AS PLND,
CASE
WHEN (SocHxTobaccoType LIKE 'Cig%' OR SocHxTobaccoType IN ('Pipe', 'Yes')) OR
(SocHxTobaccoType IS NULL AND SocHxTobaccoPacksPerDay IS NOT NULL)
THEN 'Yes'
ELSE 'No'
END AS Smoker
FROM
Patients
INNER JOIN Procedures RP
ON Patients.PatientId = RP.PatientId
AND RP.ProcName IN (
'CP','LP','LP_CONV','PP','RALP','RP','SalvCP','SalvLP','SalvRP'
)
LEFT OUTER JOIN Procedures PLND
ON Patients.PatientId = PLND.PatientId
AND PLND.ProcName = 'PLND'
LEFT OUTER JOIN LabTests PreSurgeryPSA
ON Patients.PatientId = PreSurgeryPSA.PatientId
AND PreSurgeryPSA.LabTestId = (
SELECT TOP 1 LabTestId
FROM LabTests
WHERE LabDate <= RP.ProcDate
AND LabTest = 'PSA'
AND PatientId = Patients.PatientId
ORDER BY
LabDate DESC,
CASE LabQuality
WHEN 'RR' THEN 4
WHEN 'STD' THEN 3
WHEN 'REV' THEN 2
WHEN 'OUT' THEN 1
ELSE 0
END DESC,
LabResult DESC
)
LEFT OUTER JOIN ProstatectomyPath PP
ON PP.PathologyId = (
SELECT TOP 1 Pathology.PathologyId
FROM Pathology
INNER JOIN ProstatectomyPath
ON Pathology.PathologyId = ProstatectomyPath.PathologyId
WHERE ProcedureId = RP.ProcedureId
ORDER BY
CASE PathQuality
WHEN 'RR' THEN 4
WHEN 'STD' THEN 3
WHEN 'REV' THEN 2
WHEN 'OUT' THEN 1
ELSE 0
END DESC
)
LEFT OUTER JOIN Pathology PLND_Path
ON Patients.PatientId = PLND_Path.PatientId
AND PLND_Path.PathologyId = (
SELECT TOP 1 PathologyId
FROM Pathology
WHERE PatientId = Patients.PatientId
AND ProcedureId = PLND.ProcedureId
AND PathSpecimenType = 'PLND'
ORDER BY
CASE PathQuality
WHEN 'RR' THEN 4
WHEN 'STD' THEN 3
WHEN 'REV' THEN 2
WHEN 'OUT' THEN 1
ELSE 0
END DESC
)
LEFT OUTER JOIN SocialHistories
ON Patients.PatientId = SocialHistories.PatientId
WHERE Patients.PatientId = @PatientId" ;
#endregion
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@PatientId", patientId);
Connectivity.ConsumeRecord(cmd, new Connectivity.RecordConsumerDelegate(consumer));
}
public void Execute()
{
Connectivity.InsertBatch(new SqlCommand(buf.ToString()));
}