/// <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())); }