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