def search(disease: str, geocode: int, ew_start: int, ew_end: int): """ :param disease: :param geocode: :param ew_start: :param ew_end: :return: """ if disease not in CID10.keys(): raise Exception('The diseases available are: %s.' % ', '.join('`%s`' % k for k in CID10.keys())) sql = ''' SELECT *, ( SELECT sum(casos) FROM "Municipio"."Historico_alerta{0}" WHERE municipio_geocodigo={1} AND "SE" BETWEEN {4}01 AND {4}54 ) AS notif_accum_year FROM "Municipio"."Historico_alerta{0}" WHERE municipio_geocodigo={1} AND "SE" BETWEEN {2} AND {3} '''.format( get_disease_suffix(disease), geocode, ew_start, ew_end, str(ew_end)[:4], ) with db_engine.connect() as conn: return pd.read_sql(sql, conn)
def search(disease: str, geocode: int, ew_start: int, ew_end: int): """ :param disease: :param geocode: :param ew_start: :param ew_end: :return: """ if disease not in CID10.keys(): raise Exception( 'The diseases available are: %s.' % ', '.join('`%s`' % k for k in CID10.keys()) ) sql = ''' SELECT *, ( SELECT sum(casos) FROM "Municipio"."Historico_alerta{0}" WHERE municipio_geocodigo={1} AND "SE" BETWEEN {4}01 AND {4}54 ) AS notif_accum_year FROM "Municipio"."Historico_alerta{0}" WHERE municipio_geocodigo={1} AND "SE" BETWEEN {2} AND {3} '''.format( get_disease_suffix(disease), geocode, ew_start, ew_end, str(ew_end)[:4] ) with db_engine.connect() as conn: return pd.read_sql(sql, conn)
def search_rj(disease: str, ew_start: int, ew_end: int): """ :param disease: :param ew_start: :param ew_end: :return: """ if disease not in CID10.keys(): raise Exception('The diseases available are: %s.' % ', '.join('`%s`' % k for k in CID10.keys())) sql = ''' SELECT * FROM "Municipio".alerta_mrj{0}, ( SELECT sum(casos) FROM "Municipio".alerta_mrj{0} WHERE se BETWEEN {3}01 AND {3}54 ) AS notif_accum_year WHERE se BETWEEN {1} AND {2} '''.format(get_disease_suffix(disease), ew_start, ew_end, str(ew_end)[:4]) with db_engine.connect() as conn: return pd.read_sql(sql, conn)
def search_rj(disease: str, ew_start: int, ew_end:int): """ :param disease: :param ew_start: :param ew_end: :return: """ if disease not in CID10.keys(): raise Exception( 'The diseases available are: %s.' % ', '.join('`%s`' % k for k in CID10.keys()) ) sql = ''' SELECT * FROM "Municipio".alerta_mrj{0}, ( SELECT sum(casos) FROM "Municipio".alerta_mrj{0} WHERE se BETWEEN {3}01 AND {3}54 ) AS notif_accum_year WHERE se BETWEEN {1} AND {2} '''.format( get_disease_suffix(disease), ew_start, ew_end, str(ew_end)[:4] ) with db_engine.connect() as conn: return pd.read_sql(sql, conn)
def test_MapFileAlert(self): """ :return: """ for disease in CID10.keys(): mf = mapfile.MapFileAlert(map_class=disease) mf.create_files()
def generate_mapfiles_alert(self): """ :return: """ for disease in CID10.keys(): print('>> Generating %s mapfile' % disease) mf = mapfile.MapFileAlert(map_class=disease) mf.create_files()
def generate_mapfiles_alert(self): """ :return: """ for disease in CID10.keys(): print('>> Generating %s mapfile' % disease) mf = mapfile.MapFileAlert(map_class=disease) mf.create_files()
def search_rj(disease: str, ew_start: int, ew_end:int): """ :param disease: :param ew_start: :param ew_end: :return: """ if disease not in CID10.keys(): raise Exception( 'The diseases available are: %s.' % ', '.join('`%s`' % k for k in CID10.keys()) ) sql = ''' SELECT * FROM "Municipio".alerta_mrj%s WHERE se BETWEEN %s AND %s ''' % (get_disease_suffix(disease), ew_start, ew_end) with db_engine.connect() as conn: return pd.read_sql(sql, conn)
def _get_disease_filter(self, disease): """ :param disease: :return: """ _diseases = ','.join(["'%s'" % cid for cid in CID10.values()]) return ("REPLACE(cid10_codigo, '.', '') IN (%s)" % (_diseases) if disease is None else "REPLACE(cid10_codigo, '.', '') IN ({})".format(','.join([ "'{}'".format(CID10[cid.lower()]) for cid in disease.split(',') ])))
def search(disease: str, geocode: int, ew_start: int, ew_end: int): """ :param disease: :param geocode: :param ew_start: :param ew_end: :return: """ if disease not in CID10.keys(): raise Exception( 'The diseases available are: %s.' % ', '.join('`%s`' % k for k in CID10.keys()) ) sql = ''' SELECT * FROM "Municipio"."Historico_alerta%s" WHERE municipio_geocodigo=%s AND "SE" BETWEEN %s AND %s ''' % (get_disease_suffix(disease), geocode, ew_start, ew_end) with db_engine.connect() as conn: return pd.read_sql(sql, conn)
def _get_disease_filter(self, disease): """ :param disease: :return: """ _diseases = ','.join(["'%s'" % cid for cid in CID10.values()]) return ( "REPLACE(cid10_codigo, '.', '') IN (%s)" % ( _diseases ) if disease is None else "REPLACE(cid10_codigo, '.', '') IN ({})".format(','.join([ "'{}'".format(CID10[cid.lower()]) for cid in disease.split(',') ])) )
def get_disease_dist(self): """ :return: """ _dist_filters = self._process_filter(self.dist_filters, 'disease') disease_label = ' CASE ' for cid_label, cid_id in CID10.items(): disease_label += " WHEN cid10.codigo='{}' THEN '{}' \n".format( cid_id, cid_label.title() ) disease_label += ' ELSE cid10.codigo END AS cid10_nome ' sql = ''' SELECT COALESCE(cid10_nome, NULL) AS category, count(id) AS casos FROM ( SELECT *, {}, {} FROM "Municipio"."Notificacao" AS notif INNER JOIN "Dengue_global"."Municipio" AS municipio ON notif.municipio_geocodigo = municipio.geocodigo LEFT JOIN "Dengue_global"."CID10" AS cid10 ON REPLACE(notif.cid10_codigo, '.', '')=cid10.codigo ) AS tb WHERE {} GROUP BY cid10_nome; '''.format( disease_label, self._age_field, _dist_filters ) with db_engine.connect() as conn: df_disease_dist = pd.read_sql(sql, conn) return df_disease_dist.set_index('category', drop=True)
def get_disease_dist(self): """ :return: """ _dist_filters = self._process_filter(self.dist_filters, 'disease') disease_label = ' CASE ' for cid_label, cid_id in CID10.items(): disease_label += " WHEN cid10.codigo='{}' THEN '{}' \n".format( cid_id, cid_label.title() ) disease_label += ' ELSE cid10.codigo END AS cid10_nome ' sql = ''' SELECT COALESCE(cid10_nome, NULL) AS category, count(id) AS casos FROM ( SELECT *, {}, {} FROM "Municipio"."Notificacao" AS notif INNER JOIN "Dengue_global"."Municipio" AS municipio ON notif.municipio_geocodigo = municipio.geocodigo LEFT JOIN "Dengue_global"."CID10" AS cid10 ON REPLACE(notif.cid10_codigo, '.', '')=cid10.codigo ) AS tb WHERE {} GROUP BY cid10_nome; '''.format(disease_label, self._age_field, _dist_filters) with db_engine.connect() as conn: df_disease_dist = pd.read_sql(sql, conn) return df_disease_dist.set_index('category', drop=True)