public IEnumerable <EncounterSummary> Get()
{
List <EncounterSummary> encounterSummaryList = new List <EncounterSummary>();
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
string sql = @"select
e.description as Description,
count(case when p.gender = 'M' then gender end) as Male,
count(case when p.gender = 'F' then gender end) as Female,
count(gender) as Total
from patient p
join encounter e on e.patient_id = p.id
group by e.description";
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
using (SQLiteDataAdapter sda = new SQLiteDataAdapter())
{
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
encounterSummaryList = Generics.ConvertDataTable <EncounterSummary>(dt);
}
}
}
conn.Close();
}
return(encounterSummaryList);
}
public IEnumerable <DiagnosisSummary> Get()
{
List <DiagnosisSummary> diagnosisSummaryList = new List <DiagnosisSummary>();
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
string sql = @"select
d.name as Diagnosis,
count(case when p.gender = 'M' then gender end) as Male,
count(case when p.gender = 'F' then gender end) as Female,
count(gender) as Total
from patient p
join encounter e on e.patient_id = p.id
join encounter_diagnosis ed on ed.encounter_id = e.id
join diagnosis d on d.id = ed.diagnosis_id
group by d.name
order by Total desc limit 10";
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
using (SQLiteDataAdapter sda = new SQLiteDataAdapter())
{
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
diagnosisSummaryList = Generics.ConvertDataTable <DiagnosisSummary>(dt);
}
}
}
conn.Close();
}
return(diagnosisSummaryList);
}