private void PopulateGeographicViewmodel(ReportsViewModel vm)
{
Stopwatch sw = new Stopwatch();
sw.Start();
try
{
using (var connection = GetConnection())
{
using (var cmd = GetGeographicSQLCommand(vm, connection))
{
// execute query, consume results, etc. here
using (SqlDataReader reader = cmd.ExecuteReader())
{
vm.GeographicViewmodel.Regions.Clear();
while (reader.Read())
{
string City = reader.FieldToString("City");
string ZipCode = reader.FieldToString("ZipCode");
int Encounters = reader.FieldToInt("Encounters");
double Revenue = reader.FieldToDouble("Revenue");
double RevenuePerEncounter = reader.FieldToDouble("RevenuePerEncounter");
int TotalHouseholds = reader.FieldToInt("TotalHouseholds");
double EncountersPerMil = reader.FieldToInt("EncountersPerMil");
double PercentOfHouseholdsWithPatients = reader.FieldToDouble("PercentOfHouseholdsWithPatients");
int NumberOfPatients = reader.FieldToInt("NumberOfPatients");
double PatientsPerMil = reader.FieldToDouble("PatientsPerMil");
var values = new GeographicModel()
{
City = City,
ZipCode = ZipCode,
Encounters = Encounters,
Revenue = Revenue,
RevenuePerEncounter = RevenuePerEncounter,
TotalHouseholds = TotalHouseholds,
EncountersPerMil = EncountersPerMil,
PercentOfHouseholdsWithPatients = PercentOfHouseholdsWithPatients,
NumberOfPatients = NumberOfPatients,
PatientsPerMil = PatientsPerMil,
fillColor = "red",
Opacity = 0.6,
strokeColor = "blue"
};
vm.GeographicViewmodel.Regions.Add(values);
}
}
}
}
}
finally
{
sw.Stop();
LoggerHelper.RecordTiming(System.Reflection.MethodBase.GetCurrentMethod().Name, sw, log);
log.DebugFormat("{0} rows returned", vm.GeographicViewmodel.Regions.Count);
if (vm.GeographicViewmodel.Regions.Count == 0)
{
log.Error("PopulateGeographicViewmodel did NOT return any regions!");
}
}
}
public void GetGeographicData(ReportsViewModel vm)
{
vm.GeographicViewmodel.ServicCenters.Clear();
// if there are no service centers, no reason to do any more work.
if (PopulateServiceCenters(vm))
{
PopulateGeographicViewmodel(vm);
PopulateZipCodeBoundries(vm.GeographicViewmodel.Regions);
}
}
private bool PopulateServiceCenters(ReportsViewModel vm)
{
foreach (var item in vm.OrganizationList)
{
if (item.Value == true)
{
var facility = vm.ClientOrganizationIDList.FirstOrDefault(dc => dc.Description == item.Key);
vm.GeographicViewmodel.ServicCenters.Add(facility);
}
}
return vm.GeographicViewmodel.ServicCenters.Count > 0;
}
public void GetCommunityExplorerData(ReportsViewModel vm)
{
GetCommunityExplorerDetails(vm, "IRM.spCommunityExplorerGetGenderData", vm.CommunityDataByGender);
GetCommunityExplorerDetails(vm, "IRM.spCommunityExplorerGetAgeData", vm.CommunityDataByAge);
GetCommunityExplorerDetails(vm, "IRM.spCommunityExplorerGetIncomeData", vm.CommunityDataByIncome);
GetCommunityExplorerDetails(vm, "IRM.spCommunityExplorerGetOwnHomeData", vm.CommunityDataByHomeOwner);
GetCommunityExplorerDetails(vm, "IRM.spCommunityExplorerGetPresenceOfChildrenData", vm.CommunityDataByHasChildren);
}
private SqlCommand GetGeographicSQLCommand(ReportsViewModel vm, SqlConnection connection)
{
//Setup the SQL command object
SqlCommand cmd = new SqlCommand("IRM.spGeographicExplorerData", connection);
cmd.CommandType = CommandType.StoredProcedure;
//Add date range params
cmd.Parameters.AddWithValue("@StartDate", vm.StartDate);
cmd.Parameters.AddWithValue("@EndDate", vm.EndDate);
var EncounterTypesfilterTempTable = BuildFilterParameter(vm.EncounterTypesList, vm.EncounterTypesIDlist);
var ServiceLinesfilterTempTable = BuildFilterParameter(vm.ServiceLinesList, vm.DiagnosisCodeIDlist);
var ClientOrganizationfilterTempTable = BuildFilterParameter(vm.OrganizationList, vm.ClientOrganizationIDList);
var PayerTypefilterTempTable = BuildFilterParameter(vm.PayerTypeList, vm.PayerTypeIDlist);
SqlParameter tvparam = cmd.Parameters.AddWithValue("@EncounterTypeFilterList", EncounterTypesfilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@DiagnosisFilterList", ServiceLinesfilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@ClientOrganizationFilterList", ClientOrganizationfilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@PayerTyperFilterList", PayerTypefilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
return cmd;
}
private void GetCommunityExplorerDetails(ReportsViewModel vm, string queryText, List<CommunityReportDataModel> dataList)
{
Stopwatch sw = new Stopwatch();
sw.Start();
try
{
using (var connection = GetConnection())
{
using (var cmd = GetCommunityExplorerQueryCommand(connection, vm, queryText))
{
// execute query, consume results, etc. here
using (SqlDataReader reader = cmd.ExecuteReader())
{
dataList.Clear();
while (reader.Read())
{
var data = new CommunityReportDataModel()
{
Name = reader.FieldToString("GroupLabel"),
IndividualCount = reader.FieldToInt("Persons"),
HouseholdCount = reader.FieldToInt("Households"),
PatientPercentage = reader.FieldToInt("PatientPercentage")
};
dataList.Add(data);
}
}
}
}
}
finally
{
sw.Stop();
LoggerHelper.RecordTiming(System.Reflection.MethodBase.GetCurrentMethod().Name, sw, log);
log.DebugFormat("{0} rows returned by {1}", dataList.Count, queryText);
}
}
private SqlCommand GetCommunityExplorerQueryCommand(SqlConnection connection, ReportsViewModel vm, string sqlCommand)
{
//Setup the SQL command object
var cmd = new SqlCommand(sqlCommand, connection)
{
CommandType = CommandType.StoredProcedure
};
// Build DataTables based on the user selected filter criteria
DataTable GenderfilterTempTable = BuildFilterParameterChar(vm.GenderList, vm.GenderIDlist);
DataTable AgefilterTempTable = BuildFilterParameter(vm.AgeRangeList, vm.AgeRangeIDlist);
DataTable HomeownerfilterTempTable = BuildFilterParameter(vm.IncomeRangeList, vm.IncomeRangeIDlist);
DataTable IncomeRangefilterTempTable = BuildFilterParameter(vm.IncomeRangeList, vm.IncomeRangeIDlist);
DataTable PresenceOfChildrenfilterTempTable = BuildFilterParameterChar(vm.HasChildrenList, vm.HasChildrenIDlist);
var tvparam = cmd.Parameters.AddWithValue("@GenderFilterList", GenderfilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@AgeFilterList", AgefilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@OwnHouseFilterList", HomeownerfilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@IncomeFilterList", IncomeRangefilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@PresenceOfChildrenFilterList", PresenceOfChildrenfilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@UnitOfMeasure", 0);
return cmd;
}
//private async Task<List<PatientReportDataModel>> GetPatientExplorerDetailsAsync(ReportsViewModel vm, string queryText)
//{
// Stopwatch sw = new Stopwatch();
// sw.Start();
// List<PatientReportDataModel> dataList = new List<PatientReportDataModel>();
// try
// {
// SqlConnection connection = new SqlConnection(healthcareSystemInfo.ConnectionString);
// await connection.OpenAsync().ConfigureAwait(false);
// try
// {
// log.Debug("Async Connection opened");
// using (var cmd = GetPatientExplorerQueryCommand(connection, vm, queryText))
// {
// // execute query, consume results, etc. here
// log.Debug("executing async reader");
// using (SqlDataReader reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
// {
// log.Debug("async reader complete");
// dataList.Clear();
// while (await reader.ReadAsync())
// {
// log.Debug("read data");
// var data = new PatientReportDataModel()
// {
// Category = reader.FieldToString("CategoryLabel"),
// Name = reader.FieldToString("GroupLabel"),
// Amount = reader.FieldToInt("Revenue"),
// Count = reader.FieldToInt("Encounters")
// };
// dataList.Add(data);
// }
// }
// }
// }
// finally
// {
// connection.Close();
// }
// }
// finally
// {
// sw.Stop();
// LoggerHelper.RecordTiming(System.Reflection.MethodBase.GetCurrentMethod().Name, sw, log);
// log.DebugFormat("{0} rows returned by {1}", dataList.Count, queryText);
// }
// return dataList;
//}
//public async Task<ReportsViewModel> GetPatientExplorerDataAsync(ReportsViewModel vm)
//{
// var GenderTask = GetPatientExplorerDetailsAsync(vm, "IRM.spPatientExplorerGetGenderData");
// var AgeTask = GetPatientExplorerDetailsAsync(vm, "IRM.spPatientExplorerGetAgeData");
// var IncomeTask = GetPatientExplorerDetailsAsync(vm, "IRM.spPatientExplorerGetIncomeData");
// var PayerTypeTask = GetPatientExplorerDetailsAsync(vm, "IRM.spPatientExplorerGetPayerTypeData");
// try
// {
// Task.WaitAll(GenderTask, AgeTask, IncomeTask, PayerTypeTask);
// }
// catch (AggregateException ex)
// {
// throw;
// }
// vm.PatientDataByGender.Clear();
// GenderTask.Result.ForEach(x => vm.PatientDataByGender.Add(x));
// vm.PatientDataByAge.Clear();
// AgeTask.Result.ForEach(x => vm.PatientDataByAge.Add(x));
// vm.PatientDataByIncome.Clear();
// IncomeTask.Result.ForEach(x => vm.PatientDataByIncome.Add(x));
// vm.PatientDataByPayerType.Clear();
// PayerTypeTask.Result.ForEach(x => vm.PatientDataByPayerType.Add(x));
// return vm;
//}
public void GetPatentExplorerData(ReportsViewModel vm)
{
GetPatientExplorerDetails(vm, "IRM.spPatientExplorerGetGenderData", vm.PatientDataByGender);
GetPatientExplorerDetails(vm, "IRM.spPatientExplorerGetAgeData", vm.PatientDataByAge);
GetPatientExplorerDetails(vm, "IRM.spPatientExplorerGetIncomeData", vm.PatientDataByIncome);
GetPatientExplorerDetails(vm, "IRM.spPatientExplorerGetPayerTypeData", vm.PatientDataByPayerType);
}
private void GetPatientExplorerDetails(ReportsViewModel vm, string queryText, List<PatientReportDataModel> dataList)
{
Stopwatch sw = new Stopwatch();
sw.Start();
try
{
using (var connection = GetConnection())
{
using (var cmd = GetPatientExplorerQueryCommand(connection, vm, queryText))
{
// execute query, consume results, etc. here
using (SqlDataReader reader = cmd.ExecuteReader())
{
dataList.Clear();
while (reader.Read())
{
var data = new PatientReportDataModel()
{
Category = reader.FieldToString("CategoryLabel"),
Name = reader.FieldToString("GroupLabel"),
Amount = reader.FieldToInt("Revenue"),
Count = reader.FieldToInt("Encounters")
};
dataList.Add(data);
}
log.DebugFormat("{0} returned {1} results.", queryText, dataList.Count);
}
}
}
}
finally
{
sw.Stop();
LoggerHelper.RecordTiming(System.Reflection.MethodBase.GetCurrentMethod().Name, sw, log);
log.DebugFormat("{0} rows returned by {1}", dataList.Count, queryText);
}
}
private SqlCommand GetPatientExplorerQueryCommand(SqlConnection connection, ReportsViewModel vm, string sqlCommand)
{
//Setup the SQL command object
var cmd = new SqlCommand(sqlCommand, connection);
cmd.CommandType = CommandType.StoredProcedure;
//Add date range params
cmd.Parameters.AddWithValue("@StartDate", vm.StartDate);
cmd.Parameters.AddWithValue("@EndDate", vm.EndDate);
// Build DataTables based on the user selected filter criteria
DataTable GenderfilterTempTable = BuildFilterParameterChar(vm.GenderList, vm.GenderIDlist);
DataTable AgefilterTempTable = BuildFilterParameter(vm.AgeRangeList, vm.AgeRangeIDlist);
DataTable IncomeRangefilterTempTable = BuildFilterParameter(vm.IncomeRangeList, vm.IncomeRangeIDlist);
DataTable EncounterTypesfilterTempTable = BuildFilterParameter(vm.EncounterTypesList, vm.EncounterTypesIDlist);
DataTable ServiceLinesfilterTempTable = BuildFilterParameter(vm.ServiceLinesList, vm.DiagnosisCodeIDlist);
DataTable ClientOrganizationfilterTempTable = BuildFilterParameter(vm.OrganizationList, vm.ClientOrganizationIDList);
DataTable PayerTypefilterTempTable = BuildFilterParameter(vm.PayerTypeList, vm.PayerTypeIDlist);
log.DebugFormat("Patient Ex: Gender {0} rows", GenderfilterTempTable.Rows.Count);
log.DebugFormat("Patient Ex: Age {0} rows", AgefilterTempTable.Rows.Count);
log.DebugFormat("Patient Ex: Income {0} rows", IncomeRangefilterTempTable.Rows.Count);
log.DebugFormat("Patient Ex: EncounterTypes {0} rows", EncounterTypesfilterTempTable.Rows.Count);
log.DebugFormat("Patient Ex: ServiceLines {0} rows", ServiceLinesfilterTempTable.Rows.Count);
log.DebugFormat("Patient Ex: ClientOrganizations {0} rows", ClientOrganizationfilterTempTable.Rows.Count);
log.DebugFormat("Patient Ex: PayerType {0} rows", PayerTypefilterTempTable.Rows.Count);
SqlParameter tvparam = cmd.Parameters.AddWithValue("@EncounterTypeFilterList", EncounterTypesfilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@DiagnosisFilterList", ServiceLinesfilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@ClientOrganizationFilterList", ClientOrganizationfilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@GenderFilterList", GenderfilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@AgeFilterList", AgefilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@IncomeFilterList", IncomeRangefilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@PayerTyperFilterList", PayerTypefilterTempTable);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam = cmd.Parameters.AddWithValue("@UnitOfMeasure", 0);
return cmd;
}
