Exemple #1
0
        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);
        }
Exemple #2
0
        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);
        }