/// <summary>
/// Gets a uniform view of Field Metadata by the table's specific filters.
/// Represents the UNION of disease specific and DEFAULT metadata.
/// </summary>
/// <param name="filter">A pair representing the filter MetadataTable's filter</param>
/// <param name="diseaseId">The optional DiseaseId filter</param>
/// <returns>A uniform view of a table's fields and metadata</returns>
private static DataTable GetFieldMetadata(KeyValuePair <string, object> filter, int?diseaseId)
{
// build WHERE clause
string whereSQL =
string.Format(
@"
-- restrict to current table fields, disease and non-disease values only
WHERE
a.{0} = @{0}
AND (a.DiseaseId = @DiseaseId OR a.DiseaseId IS NULL)
", filter.Key);
// create command
SqlCommand com = DataAccessHelper.CreateSqlCommand(string.Format(GET_FIELD_METADATA_SQL, whereSQL));
// add params
if (filter.Value is int)
{
DataAccessHelper.AddIntInputParam(com, filter.Key, filter.Value);
}
else if (filter.Value is string)
{
DataAccessHelper.AddStringInputParam(com, filter.Key, (string)filter.Value);
}
DataAccessHelper.AddInputParam(com, "DiseaseId", diseaseId, true);
return(DataAccessHelper.GetRecord(com).Tables[0]);
}
/// <summary>
/// Returns a list of Surveys and Items by specified match expression ('%Like Match%' or 'Exact Match')
/// </summary>
/// <param name="patientId"></param>
/// <param name="type"></param>
/// <param name="fromDate"></param>
/// <param name="toDate"></param>
/// <returns></returns>
public DataTable GetSurveysAndItemsByType(int patientId, string type, DateTime?fromDate, DateTime?toDate)
{
string sql =
@"
SELECT
a.SurveyId,
a.SurveyDateText,
a.SurveyDate,
a.SurveyType,
b.SurveyItemId,
b.SurveyItemNum,
b.SurveyItem,
b.SurveyItemResult
FROM Surveys a
INNER JOIN SurveyItems b
ON a.SurveyId = b.SurveyId
WHERE
a.PatientId = @PatientId
AND a.SurveyType {0} @SurveyType
ORDER BY
a.SurveyDate
";
bool isLike = type.Contains("%");
SqlCommand com = DataAccessHelper.CreateSqlCommand(string.Format(sql, isLike ? "LIKE" : "="));
DataAccessHelper.AddIntInputParam(com, "PatientId", patientId);
DataAccessHelper.AddStringInputParam(com, "SurveyType", type);
DataAccessHelper.AddInputParam(com, "FromDate", fromDate, true);
DataAccessHelper.AddInputParam(com, "ToDate", toDate, true);
DataTable dt = DataAccessHelper.GetRecord(com).Tables[0];
return(dt);
}
/// <summary>
/// Returns a list of records from the dest table which related to the source system.
/// collapseRecords=true, only records from dest table which are related to the source tables (.ie., INNER JOIN)
/// collapseRecords=false, all records from dest table including optional related source records (i.e., LEFT JOIN)
/// </summary>
/// <param name="patientId">The patient id</param>
/// <param name="sourceTable">The table which to check for relations</param>
/// <param name="sourceKeyName">The pri key name of the source table</param>
/// <param name="sourceKeyValue">The pri key value of the source table</param>
/// <param name="destTable">The table which to check for relation</param>
/// <param name="destTableKeyName">The table's pri key used for relation</param>
/// <param name="collapseRecords">True=inner join, False=outer join</param>
/// <returns></returns>
public static DataTable GetRelatedRecordsByTableAndKey(int patientId, string destTable, string destTableKeyName, int?destTableKeyValue, string sourceTable, string sourceTableKeyName, bool collapseRecords)
{
string sql = @"
SELECT
a.*,
b.RelatedRecordId,
b.DestTableName,
b.DestPrimaryKey,
b.SrcTableName,
b.SrcPrimaryKey,
b.RelationStrength
FROM {2} a
{4} RelatedRecords b
ON
b.SrcSystem = 'Caisis'
AND b.SrcTableName = @SrcTableName
AND b.SrcPrimaryKey = a.{3}
AND b.DestTableName = @DestTableName
{4} {0} c
ON b.DestPrimaryKey = c.{1}
WHERE
a.PatientId = @PatientId
ORDER BY
CASE WHEN RelatedRecordId IS NULL THEN 1
ELSE 0
END ASC,
b.DestPrimaryKey ASC
";
// build dynamic sql
// collapseRecords==only include records which are related
string collapseStatement = collapseRecords ? "INNER JOIN" : "LEFT OUTER JOIN";
string[] formatParams = new string[] { destTable, destTableKeyName, sourceTable, sourceTableKeyName, collapseStatement };
string formattedSQL = string.Format(sql, formatParams);
// build command
SqlCommand com = DataAccessHelper.CreateSqlCommand(formattedSQL);
// add required params
DataAccessHelper.AddIntInputParam(com, "PatientId", patientId);
DataAccessHelper.AddStringInputParam(com, "SrcSystem", CAISIS_SRC_SYSTEM);
DataAccessHelper.AddStringInputParam(com, "SrcTableName", sourceTable);
if (destTableKeyValue.HasValue)
{
DataAccessHelper.AddIntInputParam(com, "DestPrimaryKey", destTableKeyValue.Value);
}
else
{
DataAccessHelper.AddInputParam(com, "DestPrimaryKey", null, true);
}
DataAccessHelper.AddStringInputParam(com, "DestTableName", destTable);
DataTable dt = DataAccessHelper.GetRecord(com).Tables[0];
return(dt);
}
private DataTable _GetAllContactsAndUser(int?organizationId)
{
string sql =
@"
SELECT
ContactId,
FirstName,
LastName,
UserId,
UserName,
UserFirstName,
UserLastName,
UserEmail,
OrganizationId,
Name,
OrganizationContactId
FROM
(
SELECT
a.ContactId,
a.FirstName,
a.LastName,
d.UserId,
d.UserName,
d.UserFirstName,
d.UserLastName,
d.UserEmail,
c.OrganizationId,
c.Name,
-- select on valid organization
CASE
WHEN
@OrganizationId IS NULL OR c.OrganizationId = @OrganizationId
THEN b.OrganizationContactId
ELSE NULL
END AS OrganizationContactId,
-- select single contact for organization filter
ROW_NUMBER() OVER(PARTITION BY a.ContactId, d.UserId ORDER BY
CASE
WHEN c.OrganizationId = @OrganizationId THEN 0
ELSE 1
END
ASC) AS RowNum
FROM Contact a
LEFT OUTER JOIN ProjectOrganization_ProjectContact b
ON a.ContactId = b.ContactId
LEFT OUTER JOIN ProjectOrganization c
ON b.OrganizationId = c.OrganizationId
FULL OUTER JOIN Users d
ON a.UserId = d.UserId
) a
WHERE
@OrganizationId IS NULL
OR
-- restrict to valid contacts with current organization
(a.ContactId IS NOT NULL AND a.RowNum = 1)
ORDER BY
CASE
WHEN @OrganizationId IS NULL
THEN 0
-- current organization contacts sort first
ELSE ISNULL(OrganizationContactId - OrganizationContactId, 1)
END ASC,
LastName ASC
";
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
DataAccessHelper.AddInputParam(com, "OrganizationId", organizationId, true);
DataTable dt = DataAccessHelper.GetRecord(com).Tables[0];
return(dt);
}
///// <summary>
///// UPDATE FieldMetadata
///// SET FieldName = @FieldName, FieldDescription = @FieldDescription,
///// FieldLabel = @FieldLabel, FieldControlType = @FieldControlType,
///// FieldDataType = @FieldDataType, FieldRowSource = @FieldRowSource
///// WHERE FieldId = @FieldId
///// </summary>
///// <returns></returns>
//public bool UpdateFieldMetadata(int fieldId, string name, string desc, string label, string controlType,
// string dataType, string rowSource)
//{
// SqlCommand com = DataAccessHelper.CreateCommand("spUpdateFieldMetadataForAdmin");
// // set params
// AddParameterWithNullCheck(com, "@FieldId", fieldId);
// AddParameterWithNullCheck(com, "@FieldName", name);
// AddParameterWithNullCheck(com, "@FieldDescription", desc);
// AddParameterWithNullCheck(com, "@FieldLabel", label);
// AddParameterWithNullCheck(com, "@FieldControlType", controlType);
// AddParameterWithNullCheck(com, "@FieldDataType", dataType);
// AddParameterWithNullCheck(com, "@FieldRowSource", rowSource);
// DataAccessHelper.ExecuteScalar(com);
// return true;
//}
// comes in handy, was stolen from AdminMetadata.aspx.cs
//private static void AddParameterWithNullCheck(SqlCommand cmd, string fieldname, object val)
//{
// if (val == null)
// {
// cmd.Parameters.Add(new SqlParameter(fieldname, DBNull.Value));
// }
// else
// {
// cmd.Parameters.Add(new SqlParameter(fieldname, val));
// }
//}
/*protected override void SetParams()
* {
* this._GetStoredProc = null;
* this._InsertStoredProc = null;
* this._UpdateStoredProc = null;
* this._DeleteStoredProc = null;
* this._GetByParentStoredProc = null;
* }*/
///// <summary>
///// Gets the values associated with a table and attribute
///// </summary>
///// <param name="tableId"></param>
///// <param name="tableAttributeId"></param>
///// <returns></returns>
//public DataTable GetTableMetaDataTableAttributeValues(int tableId, int tableAttributeId)
//{
// string sql = "SELECT * FROM MetadataTableAttributeValues ";
// sql += "LEFT OUTER JOIN MetadataTables ";
// sql += "ON MetadataTables.TableId = MetadataTableAttributeValues.TableId ";
// sql += "LEFT OUTER JOIN MetadataTableAttributes ";
// sql += "ON MetadataTableAttributes.TableAttributeId = MetadataTableAttributeValues.TableAttributeId ";
// sql += "WHERE MetadataTableAttributeValues.TableId = @TableId AND MetadataTableAttributeValues.TableAttributeId = @TableAttributeId";
// SqlCommand cmd = DataAccessHelper.CreateSqlCommand(sql);
// DataAccessHelper.AddIntInputParam(cmd, "TableId", tableId);
// DataAccessHelper.AddIntInputParam(cmd, "TableAttributeId", tableAttributeId);
// return DataAccessHelper.GetRecord(cmd).Tables[0];
//}
///// <summary>
///// Gets all attribute values for a table
///// </summary>
///// <param name="tableId"></param>
///// <returns></returns>
//public DataTable GetTableMetaDataTableAttributeValues(int tableId)
//{
// string sql = "SELECT * FROM MetadataTableAttributeValues ";
// sql += "LEFT OUTER JOIN MetadataTables ";
// sql += "ON MetadataTables.TableId = MetadataTableAttributeValues.TableId ";
// sql += "LEFT OUTER JOIN MetadataTableAttributes ";
// sql += "ON MetadataTableAttributes.TableAttributeId = MetadataTableAttributeValues.TableAttributeId ";
// sql += "WHERE MetadataTableAttributeValues.TableId = @TableId AND MetadataTableAttributeValues.TableAttributeId = @TableAttributeId";
// SqlCommand cmd = DataAccessHelper.CreateSqlCommand(sql);
// DataAccessHelper.AddIntInputParam(cmd, "TableId", tableId);
// return DataAccessHelper.GetRecord(cmd).Tables[0];
//}
#region TABLE METADATA
public DataTable GetAllTableMetadata(int?tableId, int?diseaseId)
{
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
-- restrict to current table fields, disease and non-disease values only
WHERE
(a.DiseaseId = @DiseaseId OR a.DiseaseId IS NULL) {0}
ORDER BY
a.TableName ASC,
a.TableAttributeName ASC
";
string tableFilter = tableId.HasValue ? " AND a.TableId = @TableId " : "";
// create command
SqlCommand com = DataAccessHelper.CreateSqlCommand(string.Format(sql, tableFilter));
// add params
DataAccessHelper.AddInputParam(com, "DiseaseId", diseaseId, true);
DataAccessHelper.AddInputParam(com, "TableId", tableId, true);
// exec and return
DataTable dt = DataAccessHelper.GetRecord(com).Tables[0];
return(dt);
}
public static DataTable GetRelatedRecordsByTableAndKey(int patientId, string destTableName, string destTableKeyName, int?destTableKeyValue, IDictionary <KeyValuePair <string, string>, KeyValuePair <string, string> > srcTableLookup)
{
string sql = @"
SELECT
b.RelatedRecordId,
b.SrcTableName,
b.SrcPrimaryKey,
b.DestTableName,
b.DestPrimaryKey,
b.RelationStrength,
{3} AS SrcTableField,
{4} AS SrcTableSortField
FROM {1} a
INNER JOIN RelatedRecords b
ON
b.SrcSystem = 'Caisis'
AND b.SrcTableName = @SrcTableName_{1}
AND b.SrcPrimaryKey = a.{2}
AND b.DestTableName = @DestTableName
AND b.DestPrimaryKey = @DestPrimaryKey
INNER JOIN {0} c
ON b.DestPrimaryKey = @DestPrimaryKey
WHERE
a.PatientId = @PatientId
";
List <string> buffer = new List <string>();
foreach (var srcTablePair in srcTableLookup)
{
string srcTableName = srcTablePair.Key.Key;
string srcTablePriKeyName = srcTablePair.Key.Value;
string srcTableDisplayColumn = srcTablePair.Value.Key;
string srcTableSortColumn = srcTablePair.Value.Value;
srcTableDisplayColumn = !string.IsNullOrEmpty(srcTableDisplayColumn) ? "a." + srcTableDisplayColumn : "''";
srcTableSortColumn = !string.IsNullOrEmpty(srcTableSortColumn) ? "a." + srcTableSortColumn : "''";
string[] formatParams = new string[] { destTableName, srcTableName, srcTablePriKeyName, srcTableDisplayColumn, srcTableSortColumn };
string formattedSQL = string.Format(sql, formatParams);
buffer.Add(formattedSQL);
}
string fullSQL = string.Join(" UNION ", buffer.ToArray());
// build command
SqlCommand com = DataAccessHelper.CreateSqlCommand(fullSQL);
// add required params
DataAccessHelper.AddIntInputParam(com, "PatientId", patientId);
DataAccessHelper.AddStringInputParam(com, "SrcSystem", CAISIS_SRC_SYSTEM);
DataAccessHelper.AddStringInputParam(com, "DestTableName", destTableName);
if (destTableKeyValue.HasValue)
{
DataAccessHelper.AddIntInputParam(com, "DestPrimaryKey", destTableKeyValue.Value);
}
else
{
DataAccessHelper.AddInputParam(com, "DestPrimaryKey", null, true);
}
// add dynamic params
foreach (var srcTablePair in srcTableLookup)
{
string srcTableName = srcTablePair.Key.Key;
DataAccessHelper.AddStringInputParam(com, "SrcTableName" + "_" + srcTableName, srcTableName);
}
DataTable dt = DataAccessHelper.GetRecord(com).Tables[0];
return(dt);
}
