public DataSet FormGetFamMemDiagnosisRecords(int PatientId, string FormName, string FormType)
{
DataSet ds = new DataSet();
string sql = "";
if (FormType == "Dynamic")
{
sql = "SELECT * ";
sql += "FROM FamilyMembers ";
sql += "INNER JOIN FamilyMemberDiagnosis ON FamilyMemberDiagnosis.FamilyMemberId = FamilyMembers.FamilyMemberId ";
sql += "WHERE (PatientId = @PatientId) ";
sql += "ORDER BY FamilyMemberDiagnosisId ASC ";
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
DataAccessHelper.AddIntInputParam(com, "PatientId", PatientId);
ds = DataAccessHelper.GetRecord(com);
}
return(ds);
}
public DataSet FormGetSpecialRecords(int PatientId, string FormName, string FormType, string searchField, string searchClause, string searchFilter)
{
DataSet ds = new DataSet();
if (FormType == "Dynamic" && !String.IsNullOrEmpty(searchFilter) && !String.IsNullOrEmpty(searchField))
{
string sql = @"
SELECT *
FROM Hospitalizations
WHERE (PatientId = @PatientId ) AND @SearchField @SearchClause (@SearchFilter)
ORDER BY HospAdmitDate ASC ";
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
DataAccessHelper.AddIntInputParam(com, "PatientId", PatientId);
DataAccessHelper.AddStringInputParam(com, "SearchField", searchField);
DataAccessHelper.AddStringInputParam(com, "SearchClause", searchClause);
DataAccessHelper.AddStringInputParam(com, "SearchFilter", searchFilter);
ds = DataAccessHelper.GetRecord(com);
}
return(ds);
}
private int AddMetaField(int tableId, string fieldName, string fieldDataType, int maxLength, bool fieldIsVirtual, bool fieldSuppress, int fieldOrder, bool fieldOrderIsNull)
{
if (fieldName.IndexOf(' ') != -1)
{
// js client code should prevent fieldnames with spaces
throw new ArgumentException("FieldName for metadata field cannot contain spaces, and client code (javascript) should enforce this. The MetadataFields record was not updated.");
}
int fieldId;
bool addMaxLength = false;
int attributeId = -1;
DataTable dtA = GetAnAttribute("MaxLength"); // This is not very efficient when adding many attributes at once as it gets the id on every add. Also it is dependent on the existence of an attribute named 'MaxLength'.
if (dtA.Rows.Count == 1)
{
addMaxLength = true;
attributeId = int.Parse(dtA.Rows[0][0].ToString()); //MaxLength in MetadataFieldAttributes
}
int isVirtual = 0;
int suppress = 0;
string strFieldOrder = "null";
if (fieldIsVirtual)
{
isVirtual = 1;
}
if (fieldSuppress)
{
suppress = 1;
}
else if (!fieldOrderIsNull)
{
strFieldOrder = fieldOrder.ToString();
MetaFieldOrderFreePosition(tableId, fieldOrder);
}
string sql = "INSERT INTO MetadataFields(TableId, FieldName, FieldDataType, FieldIsVirtual, FieldSuppress, FieldOrder) VALUES (" + tableId + ", '" + fieldName + "', '" + fieldDataType + "', " + isVirtual + ", " + suppress + ", " + strFieldOrder + ");SELECT @@IDENTITY";
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
DataSet ds = DataAccessHelper.GetRecord(com);
if (com.Connection.State != ConnectionState.Closed)
{
com.Connection.Close();
}
fieldId = int.Parse(ds.Tables[0].Rows[0][0].ToString());
if (maxLength >= 0 && addMaxLength)
{
sql = "INSERT INTO MetadataFieldAttributeValues(FieldId, AttributeId, AttributeValue) VALUES(" + fieldId + ", " + attributeId + ", " + maxLength + ")";
com = DataAccessHelper.CreateSqlCommand(sql);
DataAccessHelper.ExecuteScalar(com);
}
if (com.Connection.State != ConnectionState.Closed)
{
com.Connection.Close();
}
attributeId = -1;
dtA = GetAnAttribute("Label");
if (dtA.Rows.Count == 1)
{
attributeId = int.Parse(dtA.Rows[0][0].ToString()); //id in MetadataFieldAttributes
AddAttribute(fieldId, attributeId, fieldName);
}
attributeId = -1;
dtA = GetAnAttribute("ControlType");
if (dtA.Rows.Count == 1)
{
attributeId = int.Parse(dtA.Rows[0][0].ToString()); //id in MetadataFieldAttributes
AddAttribute(fieldId, attributeId, "CaisisTextBox");
}
return(fieldId);
}
public DataSet GetRoles()
{
SqlCommand com = DataAccessHelper.CreateCommand("spGetRoles");
return(DataAccessHelper.GetRecord(com));
}
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);
}
/*public DataSet GetPhysicians(int patientID)
* {
* SqlCommand com = DataAccessHelper.CreateCommand("spGetPhysicians");
* DataAccessHelper.AddIntInputParam(com, "PatientID", patientID);
* return DataAccessHelper.GetList(com);
* }*/
public DataSet GetDistinctPhysicians()
{
SqlCommand com = DataAccessHelper.CreateCommand("spGetDistinctPhysicians");
return(DataAccessHelper.GetList(com));
}
public void LogError(int LoginId,
int DatasetId,
int PatientId,
string ErrUserName,
string ErrURL,
string ErrFullPath,
string ErrFileName,
string ErrForm,
string ErrQueryString,
string ErrBrowser,
string ErrPlatform,
string ErrJavascript,
string ErrIP,
string ErrAuthenticated,
string ErrHostName,
string ErrRoleList,
string ErrSessionIsNew,
int ErrSessionTimeOut,
DateTime?ErrTicketIssueDate,
DateTime?ErrTicketExpiration,
DateTime ErrorTime,
string ErrMachineName,
string ErrorName,
string ErrorMessage,
string ErrStackTrace
)
{
SqlCommand com = DataAccessHelper.CreateCommand("spInsertErrorLog");
DataAccessHelper.AddIntInputParam(com, "UserLoginId", LoginId);
DataAccessHelper.AddIntInputParam(com, "DatasetId", DatasetId);
DataAccessHelper.AddIntInputParam(com, "PatientId", PatientId);
DataAccessHelper.AddStringInputParam(com, "ErrUserName", trimString(ErrUserName, 50));
DataAccessHelper.AddStringInputParam(com, "ErrURL", trimString(ErrURL, 255));
DataAccessHelper.AddStringInputParam(com, "ErrFullPath", trimString(ErrFullPath, 2550));
DataAccessHelper.AddStringInputParam(com, "ErrFileName", trimString(ErrFileName, 255));
DataAccessHelper.AddStringInputParam(com, "ErrForm", trimString(ErrForm, 500));
DataAccessHelper.AddStringInputParam(com, "ErrQueryString", trimString(ErrQueryString, 255));
DataAccessHelper.AddStringInputParam(com, "ErrBrowser", trimString(ErrBrowser, 50));
DataAccessHelper.AddStringInputParam(com, "ErrPlatform", trimString(ErrPlatform, 50));
DataAccessHelper.AddStringInputParam(com, "ErrJavascript", trimString(ErrJavascript, 50));
DataAccessHelper.AddStringInputParam(com, "ErrIP", trimString(ErrIP, 50));
DataAccessHelper.AddStringInputParam(com, "ErrAuthenticated", trimString(ErrAuthenticated, 50));
DataAccessHelper.AddStringInputParam(com, "ErrHostName", trimString(ErrHostName, 255));
DataAccessHelper.AddStringInputParam(com, "ErrRoleList", trimString(ErrRoleList, 255));
DataAccessHelper.AddStringInputParam(com, "ErrSessionIsNew", trimString(ErrSessionIsNew, 50));
DataAccessHelper.AddIntInputParam(com, "ErrSessionTimeOut", ErrSessionTimeOut);
// validate date time params
if (ErrTicketIssueDate.HasValue)
{
DataAccessHelper.AddDateTimeInputParam(com, "ErrTicketIssueDate", ErrTicketIssueDate);
}
if (ErrTicketExpiration.HasValue)
{
DataAccessHelper.AddDateTimeInputParam(com, "ErrTicketExpiration", ErrTicketExpiration);
}
DataAccessHelper.AddDateTimeInputParam(com, "ErrorTime", ErrorTime);
DataAccessHelper.AddStringInputParam(com, "ErrMachineName", trimString(ErrMachineName, 255));
DataAccessHelper.AddStringInputParam(com, "ErrorName", trimString(ErrorName, 50));
DataAccessHelper.AddStringInputParam(com, "ErrorMessage", trimString(ErrorMessage, 255));
DataAccessHelper.AddStringInputParam(com, "ErrStackTrace", trimString(ErrStackTrace, 500));
try
{
DataAccessHelper.ExecuteScalar(com);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
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);
}
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>
///
/// </summary>
/// <param name="diseaseId"></param>
/// <param name="attributeName"></param>
/// <param name="attributeValue"></param>
/// <returns></returns>
public DataTable GetDiseasesByAttribute(int?diseaseId, string attributeName, string attributeValue)
{
string sql =
@"
SELECT
DiseaseId,
DiseaseName,
DiseaseAttributeId,
DiseaseAttributeValueId,
DiseaseAttributeValue,
DiseaseAttributeName
FROM
(
SELECT
a.DiseaseId,
a.DiseaseName,
b.DiseaseAttributeId,
b.DiseaseAttributeValueId,
b.DiseaseAttributeValue,
b.DiseaseAttributeName,
ROW_NUMBER() OVER (PARTITION BY a.DiseaseId ORDER BY b.DiseaseAttributeValueId ASC) AS RowNum
FROM Diseases a
{0} JOIN
(
SELECT
a.DiseaseId,
a.DiseaseAttributeId,
a.DiseaseAttributeValueId,
a.DiseaseAttributeValue,
b.DiseaseAttributeName
FROM DiseaseAttributeValues a
INNER JOIN DiseaseAttributes b
ON a.DiseaseAttributeId = b.DiseaseAttributeId
WHERE b.DiseaseAttributeName = @DiseaseAttributeName
) b
ON a.DiseaseId = b.DiseaseId
{1}
) a
-- WHERE RowNum = 1
{2}
ORDER BY DiseaseName ASC
";
// by disease, inner, else outer
string joinClause = diseaseId.HasValue ? "INNER" : "LEFT OUTER";
string optWhereDisease = diseaseId.HasValue ? " WHERE a.DiseaseId = @DiseaseId " : "";
string optWhereValue = !string.IsNullOrEmpty(attributeValue) ? " WHERE a.DiseaseAttributeValue = @DiseaseAttributeValue" : "";
SqlCommand cmd = DataAccessHelper.CreateSqlCommand(string.Format(sql, joinClause, optWhereDisease, optWhereValue));
// add key field
DataAccessHelper.AddStringInputParam(cmd, "DiseaseAttributeName", attributeName);
// add optional filters
if (diseaseId.HasValue)
{
DataAccessHelper.AddIntInputParam(cmd, "DiseaseId", diseaseId.Value);
}
if (!string.IsNullOrEmpty(attributeValue))
{
DataAccessHelper.AddStringInputParam(cmd, "DiseaseAttributeValue", attributeValue);
}
// execute update
return(DataAccessHelper.GetList(cmd).Tables[0]);
}
public DataSet GetDistinctSurgeryInstitution() // need to select from dataset?
{
SqlCommand com = DataAccessHelper.CreateCommand("spGetDistinctSurgeryInstitutions");
return(DataAccessHelper.GetList(com));
}
/// <summary>
/// Get the recently-created EForm (based on name) for a patient, or null if none is found.
/// </summary>
/// <param name="patientId"></param>
/// <param name="eformName"></param>
/// <returns>Returns associated DataRowView or null if no rows found</returns>
public DataRowView GetPatientRecentEForm(int patientId, string eformName)
{
string sql = @"
SELECT TOP 1 * FROM Eforms
WHERE
EFormName = @EformName AND PatientId = @PatientId
AND ( YEAR(EnteredTime) = YEAR(@Today) AND MONTH(EnteredTime) = MONTH(@Today) AND DAY(EnteredTime) = DAY(@Today) )
AND
CurrentStatus != 'Deleted'
AND ( EformApptTime IS NULL OR EformApptTime > @Today )
ORDER BY CurrentStatus ";
SqlCommand cmd = DataAccessHelper.CreateSqlCommand(sql);
DataAccessHelper.AddIntInputParam(cmd, "PatientId", patientId);
DataAccessHelper.AddStringInputParam(cmd, "EformName", eformName);
DataAccessHelper.AddDateTimeInputParam(cmd, "Today", DateTime.Today);
DataView view = DataAccessHelper.GetList(cmd).Tables[0].DefaultView;
if (view.Count > 0)
{
return(view[0]);
}
else
{
return(null);
}
// OLD 5.0 code, moved to inline SQL
//DataSet d = GetPatientEForms(patientId);
//if (d.Tables[0].Rows.Count > 0)
//{
// DataView v = d.Tables[0].DefaultView;
// for (int i = 0; i < v.Count; i++)
// {
// DataRowView r = v[i];
// DateTime updateTime = (DateTime)r["EnteredTime"];
// string currentStatus = r["CurrentStatus"].ToString();
// // we want to exclude forms created today for older clinic dates
// bool wasPastEform = false;
// if (r["EformApptTime"] != DBNull.Value)
// {
// DateTime eformApptTime = (DateTime)r["EformApptTime"];
// if (eformApptTime.Date < DateTime.Today)
// {
// wasPastEform = true;
// }
// }
// if (!wasPastEform && updateTime.Date == DateTime.Today &&
// currentStatus != "Deleted" &&
// r["EFormName"].ToString() == eformName)
// {
// return r;
// }
// }
//}
//return null;
}
public DataTable GetEforms(DateTime?EnteredTimeStart, DateTime?EnteredTimeEnd, string EnteredBy, DateTime?UpdatedTimeStart, DateTime?UpdatedTimeEnd, string UpdatedBy, string EFormName, string CurrentStatus, DateTime?EFormApptTimeStart, DateTime?EFormApptTimeEnd, string EFormApptPhysician)
{
string sql = "SELECT EForms.PatientId, EForms.EformId, EForms.EFormName, EForms.CurrentStatus, EForms.EFormApptTime, EForms.EFormApptPhysician, EForms.EnteredBy, EForms.EnteredTime, EForms.UpdatedBy, EForms.UpdatedTime, Patients.PtMrn, Patients.PtLastName + ',' + PtFirstName as Name ";
sql += "FROM EForms INNER JOIN Patients ON Patients.PatientId = Eforms.PatientId ";
List <string> searchParams = new List <string>();
if (EFormName.Length > 0)
{
searchParams.Add("(EForms.EFormName = @EFormName)");
}
if (CurrentStatus.Length > 0)
{
searchParams.Add("(EForms.CurrentStatus = @CurrentStatus)");
}
if (EFormApptPhysician.Length > 0)
{
searchParams.Add("(EForms.EFormApptPhysician = @EFormApptPhysician)");
}
if (EnteredBy.Length > 0)
{
searchParams.Add("(EForms.EnteredBy = @EnteredBy)");
}
if (UpdatedBy.Length > 0)
{
searchParams.Add("(EForms.UpdatedBy = @UpdatedBy)");
}
if (EnteredTimeStart != null)
{
searchParams.Add("(EForms.EnteredTime >= @EnteredTimeStart)");
}
if (EnteredTimeEnd != null)
{
searchParams.Add("(EForms.EnteredTime <= @EnteredTimeEnd)");
}
if (UpdatedTimeStart != null)
{
searchParams.Add("(EForms.UpdatedTime >= @UpdatedTimeStart)");
}
if (UpdatedTimeEnd != null)
{
searchParams.Add("(EForms.UpdatedTime <= @UpdatedTimeEnd)");
}
if (EFormApptTimeStart != null)
{
searchParams.Add("(EForms.EFormApptTime >= @EFormApptTimeStart)");
}
if (EFormApptTimeEnd != null)
{
searchParams.Add("(EForms.EFormApptTime <= @EFormApptTimeEnd)");
}
string whereClause = "";
if (searchParams.Count > 0)
{
whereClause = String.Join(" AND ", searchParams.ToArray());
}
else
{
EnteredTimeStart = DateTime.Now;
whereClause = "MONTH(EForms.UpdatedTime) = MONTH(@EnteredTimeStart) AND DAY(EForms.UpdatedTime) = DAY(@EnteredTimeStart) AND YEAR(EForms.UpdatedTime) = YEAR(@EnteredTimeStart) ";
}
sql += ("WHERE " + whereClause);
//if (UpdatedTime != null)
//{
// sql += "WHERE MONTH(EForms.UpdatedTime) = MONTH(@UpdatedTime) AND DAY(EForms.UpdatedTime) = DAY(@UpdatedTime) AND YEAR(EForms.UpdatedTime) = YEAR(@UpdatedTime) ";
//}
sql += "ORDER BY EForms.EFormId ASC";
SqlCommand cmd = DataAccessHelper.CreateCommand(sql);
DataAccessHelper.AddStringInputParam(cmd, "CurrentStatus", CurrentStatus);
DataAccessHelper.AddStringInputParam(cmd, "EFormName", EFormName);
DataAccessHelper.AddStringInputParam(cmd, "EFormApptPhysician", EFormApptPhysician);
DataAccessHelper.AddStringInputParam(cmd, "EnteredBy", EnteredBy);
DataAccessHelper.AddStringInputParam(cmd, "UpdatedBy", UpdatedBy);
DataAccessHelper.AddDateTimeInputParam(cmd, "EnteredTimeStart", EnteredTimeStart);
DataAccessHelper.AddDateTimeInputParam(cmd, "EnteredTimeEnd", EnteredTimeEnd);
DataAccessHelper.AddDateTimeInputParam(cmd, "UpdatedTimeStart", UpdatedTimeStart);
DataAccessHelper.AddDateTimeInputParam(cmd, "UpdatedTimeEnd", UpdatedTimeEnd);
DataAccessHelper.AddDateTimeInputParam(cmd, "EFormApptTimeStart", EFormApptTimeStart);
DataAccessHelper.AddDateTimeInputParam(cmd, "EFormApptTimeEnd", EFormApptTimeEnd);
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
DataAccessHelper.GetList(cmd, dt);
return(dt);
}
public DataSet GetDatasets()
{
SqlCommand com = DataAccessHelper.CreateCommand("spGetDatasets");
return(DataAccessHelper.GetList(com));
}
/// <summary>
/// Gets stats about the specified Dataset (optinally filtered by previous years).
/// SELECT
/// Count (patients added this month+year),
/// Year,
/// Month
/// TotalCount (running total of patients added to this month)
/// FROM @Dataset
/// </summary>
/// <param name="datasetSQL"></param>
/// <param name="previousYears"></param>
/// <returns></returns>
public DataTable GetDataSetStats(string datasetSQL, int?previousYears)
{
// determine starting point
DateTime?prevDate = null;
if (previousYears.HasValue)
{
// Noramlize Date: Jan 7, 2012 -> 2 years -> Jan 7, 2010 -> Jan 1, 2010 00:00:00
prevDate = DateTime.Now.AddYears(-previousYears.Value);
prevDate = new DateTime(prevDate.Value.Year, prevDate.Value.Month, 1, 0, 0, 0);
}
// GET COUNT BY MONTH
string sql =
@"
SELECT
COUNT(p.PatientId) [Count],
YEAR(p.EnteredTime) [Year],
MONTH(p.EnteredTime) [Month]
FROM Patients p
INNER JOIN ({0}) ds
ON p.PatientId = ds.PatientId
WHERE
{1}
GROUP BY
YEAR(p.EnteredTime),
MONTH(p.EnteredTime)
ORDER BY
YEAR(p.EnteredTime) ASC,
MONTH(p.EnteredTime) ASC
";
string whereSQL = "";
// build where clause
if (prevDate.HasValue)
{
whereSQL = "p.EnteredTime > @PreviousDate";
}
else
{
whereSQL = "p.EnteredTime IS NOT NULL";
}
// build group by date SQL
SqlCommand cmd = DataAccessHelper.CreateSqlCommand(string.Format(sql, datasetSQL, whereSQL));
DataAccessHelper.AddStringInputParam(cmd, "DatasetSQL", datasetSQL);
if (prevDate.HasValue)
{
DataAccessHelper.AddDateTimeInputParam(cmd, "PreviousDate", prevDate.Value);
}
DataTable dt = DataAccessHelper.GetRecord(cmd).Tables[0];
// GET STARTING COUNT (if using previous date, get count upto @date)
int startCount = 0;
if (prevDate.HasValue)
{
string getStartingMaxSQL =
@"
SELECT
COUNT(p.PatientId)
FROM Patients p
INNER JOIN ({0}) ds
ON p.PatientId = ds.PatientId
WHERE
p.EnteredTime <= @PreviousDate
";
SqlCommand getCountCMD = DataAccessHelper.CreateSqlCommand(string.Format(getStartingMaxSQL, datasetSQL));
DataAccessHelper.AddStringInputParam(getCountCMD, "DatasetSQL", datasetSQL);
DataAccessHelper.AddDateTimeInputParam(getCountCMD, "PreviousDate", prevDate.Value);
DataTable startCountTable = DataAccessHelper.GetRecord(getCountCMD).Tables[0];
startCount = (int)startCountTable.Rows[0][0];
}
// ADD COMPUTED TOTAL COUNT
DataColumn totalCountColumn = new DataColumn("TotalCount", typeof(int));
dt.Columns.Add(totalCountColumn);
int runningCount = startCount;
// normalize row count to capture running total
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
int rowCount = (int)row["Count"];
int prevRowCount = 0;
if (i > 0)
{
prevRowCount = (int)dt.Rows[i - 1][totalCountColumn];
}
runningCount += rowCount;
row[totalCountColumn] = runningCount;
}
return(dt);
}
/// <summary>
/// Returns a DataSet containing Lookup Code stats.
/// Table 1: TableName, FieldName, LkpFieldNameCount representing metadata fields associated with lookup code.
/// Table 2: count of lookup code by lkpFieldName.
/// Table 3: count of lookup codes tagged with diseaseName.
/// </summary>
/// <param name="lkpFieldName"></param>
/// <param name="diseaseName"></param>
/// <returns></returns>
public DataSet GetLookupCodeStats(string lkpFieldName, string diseaseName)
{
string sql =
@"
SELECT
b.TableName,
a.FieldName,
COUNT(e.LkpFieldName) AS LkpFieldNameCount
FROM MetadataFields a
INNER JOIN MetadataTables b
ON a.TableId = b.TableId
INNER JOIN MetadataFieldAttributeValues c
ON a.FieldId = c.FieldId
INNER JOIN MetadataFieldAttributes d
ON d.AttributeId = c.AttributeId
INNER JOIN LookupCodes e
ON e.LkpFieldName = c.AttributeValue
WHERE
d.AttributeName = 'LookupCode'
AND e.LkpFieldName = @LkpFieldName
GROUP BY
b.TableName,
a.FieldName,
e.LkpFieldName
SELECT
COUNT(LookupCodeId) AS LookupCount
FROM LookupCodes
WHERE LkpFieldName = @LkpFieldName
SELECT
COUNT(c.AttributeId) AS LookupDiseaseCount
FROM LookupCodes a
LEFT OUTER JOIN LookupCodeAttributes b
ON b.LookupCodeId = a.LookupCodeId
AND b.AttributeValue = @DiseaseName
LEFT OUTER JOIN LookupAttributes c
ON c.AttributeId = b.AttributeId
AND c.AttributeName = 'Disease'
WHERE
a.LkpFieldName = @LkpFieldName
AND b.LookupCodeAttributeId IS NOT NULL
GROUP BY
c.AttributeId
";
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
DataAccessHelper.AddStringInputParam(com, "LkpFieldName", lkpFieldName);
DataAccessHelper.AddStringInputParam(com, "DiseaseName", diseaseName);
DataSet ds = DataAccessHelper.GetList(com);
// normalize data sets
var diseaseCount = ds.Tables[2];
if (diseaseCount.Rows.Count == 0)
{
var countRow = diseaseCount.NewRow();
countRow[0] = 0;
diseaseCount.Rows.Add(countRow);
}
return(ds);
}
public DataSet GetLookupFieldNames()
{
SqlCommand com = DataAccessHelper.CreateCommand("spListLkpFieldNames");
return(DataAccessHelper.GetRecord(com));
}
public DataSet GetUserStatistics()
{
SqlCommand com = DataAccessHelper.CreateCommand("spRptAdminUserStatistics");
return(DataAccessHelper.GetRecord(com));
}
