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)