def usuario():
''' Usuários por status '''
# Cadastrados
usuario = GAL_Cursor.execute('''
SELECT COUNT(co_seq_usuario) FROM tb_usr_usuario ''').fetchone()
for cad in usuario:
cad = cad
# Ativos
ativos = GAL_Cursor.execute('''
SELECT COUNT(st_usuario_ativo) FROM tb_usr_usuario WHERE st_usuario_ativo='A' ''').fetchone()
for atv in ativos:
atv = atv
# Inativos
inativos = GAL_Cursor.execute('''
SELECT COUNT(st_usuario_ativo) FROM tb_usr_usuario WHERE st_usuario_ativo='I' ''').fetchone()
for itv in inativos:
itv = itv
# Não Ativados
naoativos = GAL_Cursor.execute('''
SELECT COUNT(st_usuario_ativo) FROM tb_usr_usuario WHERE st_usuario_ativo='1' ''').fetchone()
for ntv in naoativos:
ntv = ntv
# Agrupando e gravando no BD local
usuarios = (cad, atv, itv, ntv)
APP_Cursor.execute('''INSERT OR REPLACE INTO usuario (cadastrado, ativo, inativo, nao_ativado)
VALUES (?, ?, ?, ?)''', usuarios)
def exames_nao_conformidade():
ex_nc = GAL_Cursor.execute('''
SELECT COUNT (rl_bmh_nao_conformidade_exame.co_examereq), tb_bmh_nao_conformidade.ds_nconf
FROM rl_bmh_nao_conformidade_exame, tb_bmh_nao_conformidade
WHERE rl_bmh_nao_conformidade_exame.co_nconf = tb_bmh_nao_conformidade.co_nconf
GROUP BY tb_bmh_nao_conformidade.ds_nconf ''').fetchall()
APP_Cursor.execute('''
INSERT OR REPLACE INTO bmh_exame_nao_conformidade (exames, nao_conformidade)
VALUES (?, ?) ''', ex_nc)
def regional():
''' Laboratórios po Regional '''
reg_lab = GAL_Cursor.execute('''
SELECT DISTINCT (nu_regional) AS REG,
CASE
WHEN nu_regional = '00'
THEN REPLACE (nu_regional, '00', 'Externo (outra UF)')
WHEN nu_regional = '01'
THEN REPLACE (nu_regional, '01', '1ª R.S. - Paranaguá')
WHEN nu_regional = '02'
THEN REPLACE (nu_regional, '02', '2ª R.S. - Metropolitana')
WHEN nu_regional = '03'
THEN REPLACE (nu_regional, '03', '3ª R.S. - Ponta Grossa')
WHEN nu_regional = '04'
THEN REPLACE (nu_regional, '04', '4ª R.S. - Irati')
WHEN nu_regional = '05'
THEN REPLACE (nu_regional, '05', '5ª R.S. - Guarapuava')
WHEN nu_regional = '06'
THEN REPLACE (nu_regional, '06', '6ª R.S. - União da Vitória')
WHEN nu_regional = '07'
THEN REPLACE (nu_regional, '07', '7ª R.S. - Pato Branco')
WHEN nu_regional = '08'
THEN REPLACE (nu_regional, '08', '8ª R.S. - Francisco Beltrão')
WHEN nu_regional = '09'
THEN REPLACE (nu_regional, '09', '9ª R.S. - Foz do Iguaçu')
WHEN nu_regional = '10'
THEN REPLACE (nu_regional, '10', '10ª R.S. - Cascavel')
WHEN nu_regional = '11'
THEN REPLACE (nu_regional, '11', '11ª R.S. - Campo Mourão')
WHEN nu_regional = '12'
THEN REPLACE (nu_regional, '12', '12ª R.S. - Umuarama')
WHEN nu_regional = '13'
THEN REPLACE (nu_regional, '13', '13ª R.S. - Cianorte')
WHEN nu_regional = '14'
THEN REPLACE (nu_regional, '14', '14ª R.S. - Paranavaí')
WHEN nu_regional = '15'
THEN REPLACE (nu_regional, '15', '15ª R.S. - Maringá')
WHEN nu_regional = '16'
THEN REPLACE (nu_regional, '16', '16ª R.S. - Apucarana')
WHEN nu_regional = '17'
THEN REPLACE (nu_regional, '17', '17ª R.S. - Londrina')
WHEN nu_regional = '18'
THEN REPLACE (nu_regional, '18', '18ª R.S. - Cornélio Procópio')
WHEN nu_regional = '19'
THEN REPLACE (nu_regional, '19', '19ª R.S. - Jacarezinho')
WHEN nu_regional = '20'
THEN REPLACE (nu_regional, '20', '20ª R.S. - Toledo')
WHEN nu_regional = '21'
THEN REPLACE (nu_regional, '21', '21ª R.S. - Telêmaco Borba')
WHEN nu_regional = '22'
THEN REPLACE (nu_regional, '22', '22ª R.S. - Ivaiporã')
END AS REG,
COUNT(co_labor) AS LAB FROM tb_lab_laboratorio GROUP BY nu_regional
ORDER BY nu_regional ''').fetchall()
# Gravando no BD local
APP_Cursor.execute('''INSERT OR REPLACE INTO regional (codigo, regional, laboratorio) VALUES (?, ?, ?)''', reg_lab)
def exames_liberados():
# 2009 - Total de liberados
a09_lib = GAL_Cursor.execute('''
SELECT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 5 AND dt_status BETWEEN
'2009-01-01 00:00:00' AND '2009-12-31 23:59:59' ''').fetchone()
for l09 in a09_lib:
l09 = l09
# 2010 - Total de liberados
a10_lib = GAL_Cursor.execute('''
SELECT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 5 AND dt_status BETWEEN
'2010-01-01 00:00:00' AND '2010-12-31 23:e59:59' ''').fetchone()
for l10 in a10_lib:
l10 = l10
# 2011 - Total de liberados
a11_lib = GAL_Cursor.execute('''
SELECT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 5 AND dt_status BETWEEN
'2011-01-01 00:00:00' AND '2011-12-31 23:59:59' ''').fetchone()
for l11 in a11_lib:
l11 = l11
# 2012 - Total de liberados
a12_lib = GAL_Cursor.execute('''
SELECT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 5 AND dt_status BETWEEN
'2012-01-01 00:00:00' AND '2012-12-31 23:59:59' ''').fetchone()
for l12 in a12_lib:
l12 = l12
# 2013 - Total de liberados
a13_lib = GAL_Cursor.execute('''
SELECT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 5 AND dt_status BETWEEN
'2013-01-01 00:00:00' AND '2013-12-31 23:59:59' ''').fetchone()
for l13 in a13_lib:
l13 = l13
# 2014 - Total de liberados
a14_lib = GAL_Cursor.execute('''
SELECT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 5 AND dt_status BETWEEN
'2014-01-01 00:00:00' AND '2014-12-31 23:59:59' ''').fetchone()
for l14 in a14_lib:
l14 = l14
# 2015 - Total de liberados
a15_lib = GAL_Cursor.execute('''
SELECT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 5 AND dt_status BETWEEN
'2015-01-01 00:00:00' AND '2015-12-31 23:59:59' ''').fetchone()
for l15 in a15_lib:
l15 = l15
# 2016 - Total de liberados
a16_lib = GAL_Cursor.execute('''
SELECT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 5 AND dt_status BETWEEN
'2016-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for l16 in a16_lib:
l16 = l16
# Agrupando e gravando no BD local
exames_liberados = (l09, l10, l11, l12, l13, l14, l15, l16)
APP_Cursor.execute('''
INSERT OR REPLACE INTO bmh_exame_liberado (
ano_2009, ano_2010, ano_2011, ano_2012, ano_2013, ano_2014, ano_2015, ano_2016)
VALUES (?, ?, ?, ?, ?, ?, ?, ?) ''', exames_liberados)
def classificacao():
''' Laboratórios por Classificação '''
classificacao = GAL_Cursor.execute('''
SELECT DISTINCT (l.co_classificacao), c.ds_classificacao, COUNT(l.co_labor) FROM tb_lab_laboratorio AS L
INNER JOIN tb_lab_classificacao AS c ON c.co_classificacao = l.co_classificacao
GROUP BY l.co_classificacao, c.ds_classificacao ORDER BY l.co_classificacao ''').fetchall()
# Gravando no BD local
APP_Cursor.execute('''INSERT OR REPLACE INTO classificacao (codigo, descricao, laboratorio)
VALUES (?, ?, ?)''', classificacao)
def exame_nao_conformidade(ano):
""" Recupera os exames descartados de acordo com o motivo por ano/mês """
if ano not in anos:
print("Ano inválido!")
exit()
mes = 1
while mes <= 10:
if mes == 2:
dia = 28
elif mes in (4, 6, 9, 11):
dia = 30
elif mes in (1, 3, 5, 7, 8, 10, 12):
dia = 31
if mes not in (10, 11, 12):
mes = "0" + str(mes)
select = """ SELECT tb_bmh_nao_conformidade.co_nconf, tb_bmh_nao_conformidade.ds_nconf,
COUNT (rl_bmh_nao_conformidade_exame.co_examereq)
FROM
rl_bmh_nao_conformidade_exame, tb_bmh_nao_conformidade, tb_bmh_exame_requisicao """
where = """ WHERE rl_bmh_nao_conformidade_exame.co_examereq = tb_bmh_exame_requisicao.co_seq_examereq
AND rl_bmh_nao_conformidade_exame.co_nconf = tb_bmh_nao_conformidade.co_nconf
AND tb_bmh_exame_requisicao.dt_status BETWEEN """
date_ini = "'" + str(ano) + "-" + str(mes) + "-01 " + time_ini + "'"
date_end = "'" + str(ano) + "-" + str(mes) + "-" + str(dia) + " " + time_end + "'"
group = " GROUP BY tb_bmh_nao_conformidade.co_nconf"
sql = select + where + date_ini + ' AND ' + date_end + group + '; '
execute = GAL_Cursor.execute(sql).fetchall()
for res in execute:
if mes not in (10, 11, 12):
mes = mes.lstrip('0')
data = int(ano), int(mes)
print(data)
updata = res
print(updata)
insert = " INSERT OR IGNORE INTO exame_nao_conformidade ( ano, mes) VALUES (?, ?)"
upd_data = "UPDATE exame_nao_conformidade SET "
upd_value = "codigo=?, motivo=?, total=? "
upd_where = "WHERE ano=" + str(ano) + " AND mes=" + str(mes)
sql_upd = upd_data + upd_value + upd_where
print(sql_upd)
APP_Cursor.execute(insert, data)
APP_Cursor.execute(sql_upd, updata)
mes = int(mes)
mes += 1
def versao():
''' Versão do GAL, do aplicativo e data de atualização '''
versao = GAL_Cursor.execute('''
SELECT co_versao, dt_versao FROM tb_versao_bd ORDER BY co_seq_versao DESC LIMIT 1 ''').fetchall()
for v in versao:
codigo = v.co_versao
atualizacao = v.dt_versao
# Data da atualização do BD local
from datetime import date
dt = date.today()
sistema = (str(dt.day) + '/' + str(dt.month) + '/' + str(dt.year))
# Agrupando e gravando no BD local
versoes = (codigo, atualizacao, sistema)
APP_Cursor.execute('''INSERT OR REPLACE INTO versao (codigo, atualizacao, sistema) VALUES (?,?,?)''', versoes)
def exames():
# Total de exames configurados
tot_cfg = GAL_Cursor.execute('''
SELECT COUNT (co_exame) FROM rl_bmh_exame_metodologia ''').fetchone()
for cfg in tot_cfg:
cfg = cfg
# Total de exames ativos
tot_atv = GAL_Cursor.execute('''
SELECT COUNT(*) FROM (SELECT DISTINCT co_exame,co_metodo FROM rl_bmh_config_exame WHERE tp_fluxo = 'B')
AS exames ''').fetchone()
for atv in tot_atv:
atv = atv
# Total de pesquisas ativas
psq_atv = GAL_Cursor.execute('''
SELECT COUNT (DISTINCT no_pesquisa) FROM tb_bmh_pesquisa WHERE st_pesquisa ='A' ''').fetchone()
for psqa in psq_atv:
psqa = psqa
# Total de pesquisas inativas
psq_int = GAL_Cursor.execute('''
SELECT COUNT (DISTINCT no_pesquisa) FROM tb_bmh_pesquisa WHERE st_pesquisa ='I' ''').fetchone()
for psqi in psq_int:
psqi = psqi
# Total de exames cadastrados
tot_cad = GAL_Cursor.execute('''
SELECT COUNT(co_seq_examereq) FROM tb_bmh_exame_requisicao ''').fetchone()
for cad in tot_cad:
cad = cad
# Total de exames processados
tot_pro = GAL_Cursor.execute('''
SELECT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 5 or co_status = 6 or
co_status = 12 ''').fetchone()
for pro in tot_pro:
pro = pro
# Total de exames correlativos
tot_crl = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_seq_examereq) FROM tb_bmh_exame_requisicao WHERE st_correlativo = 1 ''').fetchone()
for crl in tot_crl:
crl = crl
# Agrupando e gravando no BD local
exames = (cfg, atv, psqa, psqi, cad, pro, crl)
APP_Cursor.execute('''
INSERT OR REPLACE INTO bmh_exames (
configurado, ativado, pesquisa_ativa, pesquisa_inativa, cadastrado, processado, correlativo)
VALUES (?, ?, ?, ?, ?, ?, ?) ''', exames)
def requisicao():
# Cadastradas
cadastradas = GAL_Cursor.execute('''
SELECT COUNT(co_requisicao) FROM tb_bmh_requisicao ''').fetchone()
for cad in cadastradas:
cad = cad
# Fechadas
fechadas = GAL_Cursor.execute('''
SELECT COUNT(co_requisicao) FROM tb_bmh_requisicao WHERE dt_fechamento IS NOT NULL ''').fetchone()
for fec in fechadas:
fec = fec
# Correlativos
correlativo = GAL_Cursor.execute('''
SELECT COUNT(st_correlativo) FROM tb_bmh_requisicao where st_correlativo = 1 ''').fetchone()
for crl in correlativo:
crl = crl
# Agrupando e gravando no BD local
reqs = (cad, fec, crl)
APP_Cursor.execute('INSERT OR REPLACE INTO bmh_requisicao (cadastrada, fechada, correlativo) VALUES (?, ?, ?)', reqs)
def laboratorios():
''' Laboratórios por Modulo '''
# AMB Lab. LL
lab_ll = GAL_Cursor.execute(''' SELECT COUNT (co_labor) FROM ta_amb_config_laboratorio
WHERE tp_labor = 'LL' ''').fetchone()
for amb_ll in lab_ll:
amb_ll = amb_ll
# AMB Lab. LR
lab_lr = GAL_Cursor.execute(''' SELECT COUNT (co_labor) FROM ta_amb_config_laboratorio
WHERE tp_labor = 'LR' ''').fetchone()
for amb_lr in lab_lr:
amb_lr = amb_lr
# BAN Lab. LL
lab_ll = GAL_Cursor.execute(''' SELECT COUNT (co_labor) FROM ta_ban_config_laboratorio
WHERE tp_labor = 'LL' ''').fetchone()
for ban_ll in lab_ll:
ban_ll = ban_ll
# BAN Lab. LR
lab_lr = GAL_Cursor.execute(''' SELECT COUNT (co_labor) FROM ta_ban_config_laboratorio
WHERE tp_labor = 'LR' ''').fetchone()
for ban_lr in lab_lr:
ban_lr = ban_lr
# BMH Lab. LL
lab_ll = GAL_Cursor.execute(''' SELECT COUNT (co_labor) FROM ta_bmh_config_laboratorio
WHERE tp_labor = 'LL' ''').fetchone()
for bmh_ll in lab_ll:
bmh_ll = bmh_ll
# BMH Lab. LR
lab_lr = GAL_Cursor.execute(''' SELECT COUNT (co_labor) FROM ta_bmh_config_laboratorio
WHERE tp_labor = 'LR' ''').fetchone()
for bmh_lr in lab_lr:
bmh_lr = bmh_lr
# # CQ Lab. LL
# lab_ll = GAL_Cursor.execute(''' SELECT COUNT (co_labor) FROM ta_cq_config_laboratorio
# WHERE tp_labor = 'LL' ''').fetchone()
# for cq_ll in lab_ll:
# cq_ll = cq_ll
# # CQ Lab. LR
# lab_lr = GAL_Cursor.execute(''' SELECT COUNT (co_labor) FROM ta_cq_config_laboratorio
# WHERE tp_labor = 'LR' ''').fetchone()
# for cq_lr in lab_lr:
# cq_lr = cq_lr
# Agrupando e gravando no BD local
amb_labs = ('Ambiental', amb_ll, amb_lr)
APP_Cursor.execute('INSERT OR REPLACE INTO laboratorio (modulo, local, rede) VALUES (?, ?, ?)', amb_labs)
ban_labs = ('Biologia Animal', ban_ll, ban_lr)
APP_Cursor.execute('INSERT OR REPLACE INTO laboratorio (modulo, local, rede) VALUES (?, ?, ?)', ban_labs)
bmh_labs = ('Biologia Médica', bmh_ll, bmh_lr)
APP_Cursor.execute('INSERT OR REPLACE INTO laboratorio (modulo, local, rede) VALUES (?, ?, ?)', bmh_labs)
cq_labs = ('Controle de Qualidade', 0, 0)
APP_Cursor.execute('INSERT OR REPLACE INTO laboratorio (modulo, local, rede) VALUES (?, ?, ?)', cq_labs)
def requisicao_nao_conformidade():
rq_nc = GAL_Cursor.execute('''
SELECT
rl_bmh_nao_conformidade_exame.co_examereq AS "Requisição",
DATE_PART('year', tb_bmh_exame_requisicao.dt_cadastro::timestamp) AS "Ano",
tb_bmh_nao_conformidade.co_nconf AS "Código",
tb_bmh_nao_conformidade.ds_nconf AS "Motivo"
FROM
rl_bmh_nao_conformidade_exame,
tb_bmh_nao_conformidade,
tb_bmh_exame_requisicao
WHERE
rl_bmh_nao_conformidade_exame.co_nconf = tb_bmh_nao_conformidade.co_nconf
AND
rl_bmh_nao_conformidade_exame.co_examereq = tb_bmh_exame_requisicao.co_seq_examereq
GROUP BY tb_bmh_nao_conformidade.ds_nconf,
rl_bmh_nao_conformidade_exame.co_examereq,
tb_bmh_exame_requisicao.dt_cadastro,
tb_bmh_nao_conformidade.co_nconf
ORDER BY tb_bmh_exame_requisicao.dt_cadastro ''').fetchall()
APP_Cursor.execute('''
INSERT OR REPLACE INTO bmh_requisicao_nao_conformidade (requisicao, ano, codigo, nao_conformidade)
VALUES (?, ?, ?, ?) ''', rq_nc)
def exames_status():
# Total de status: TRIAGEM
tot_tri = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 1 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for tri in tot_tri:
tri = tri
# Total de status: EM ANALISE
tot_anl = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 2 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for anl in tot_anl:
anl = anl
# Total de status: RESULTADO CADADASTRADO
tot_rcd = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 4 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for rcd in tot_rcd:
rcd = rcd
# Total de status: RESULTADO LIBERADO
tot_lib = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 5 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for lib in tot_lib:
lib = lib
# Total de status: EXAME NAO REALIZADO
tot_nrl = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 6 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for nrl in tot_nrl:
nrl = nrl
# Total de status: DISPONIVEL P/ ENCAMINHAR
tot_dec = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 7 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for dec in tot_dec:
dec = dec
# Total de status: ENCAMINHADO P/ LR
tot_elr = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 8 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for elr in tot_elr:
elr = elr
# Total de status: ENCAMINHADO P/ LAB. REF
tot_erf = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 9 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for erf in tot_erf:
erf = erf
# Total de status: EXAME CANCELADO
tot_can = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 12 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for can in tot_can:
can = can
# Total de status: EXAME APROV. AGUARD. AUTOMACAO
tot_aau = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 13 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for aau in tot_aau:
aau = aau
# Total de status: AUTOMACAO EM PROCESSO
tot_aep = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 14 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for aep in tot_aep:
aep = aep
# Total de status: AUTOMACAO PROCESSADA
tot_aut = GAL_Cursor.execute('''
SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame WHERE co_status = 15 AND tp_status = 'U'
AND dt_status BETWEEN '2009-01-01 00:00:00' AND '2016-12-31 23:59:59' ''').fetchone()
for aut in tot_aut:
aut = aut
# Agrupando e gravando no BD local
exames_status = (tri, anl, rcd, lib, nrl, dec, elr, erf, can, aau, aep, aut)
APP_Cursor.execute('''
INSERT OR REPLACE INTO bmh_exame_status (
triagem, analise, res_cadastrado, liberados, nao_realizado, disp_encaminhar, encaminhado_lr,
encaminhado_rf, cancelado, aguardando_automacao, automacao_processo, automacao_processada)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ''', exames_status)
def status(status, ano):
""" Recupera os exames de acordo com o status por ano/mês """
if ano not in anos:
print("Ano inválido!")
exit()
if status == 1:
str_status = 'triagem'
elif status == 2:
str_status = 'analise'
elif status == 4:
str_status = 'resultado_cadastrado'
elif status == 5:
str_status = 'liberados'
elif status == 6:
str_status = 'nao_realizado'
elif status == 7:
str_status = 'disp_encaminhar'
elif status == 8:
str_status = 'enc_lab_regional'
elif status == 9:
str_status = 'enc_lab_referencia'
elif status == 12:
str_status = 'cancelado'
elif status == 13:
str_status = 'aguardando_automacao'
elif status == 14:
str_status = 'automacao_processo'
elif status == 15:
str_status = 'automacao_processada'
elif status == 16:
str_status = 'imp_lab_externo'
elif status == 17:
str_status = 'transcrito'
elif status == 18:
str_status = 'enc_lab_externo '
else:
print("Status inválido!")
exit()
mes = 1
while mes <= 12:
if mes == 2:
dia = 28
elif mes in (4, 6, 9, 11):
dia = 30
elif mes in (1, 3, 5, 7, 8, 10, 12):
dia = 31
if mes not in (10, 11, 12):
mes = "0" + str(mes)
select = "SELECT DISTINCT COUNT(co_examereq) FROM th_bmh_status_exame "
where = "WHERE co_status = " + str(status) + " AND tp_status = 'U' AND dt_status BETWEEN "
date_ini = "'" + str(ano) + "-" + str(mes) + "-01 " + time_ini + "'"
date_end = "'" + str(ano) + "-" + str(mes) + "-" + str(dia) + " " + time_end + "'"
sql = select + where + date_ini + ' AND ' + date_end + '; '
execute = GAL_Cursor.execute(sql).fetchone()
for res in execute:
data = int(ano), int(mes)
updata = res, int(mes)
insert = " INSERT OR IGNORE INTO exame_status ( ano, mes) VALUES (?, ?)"
upd_data = "UPDATE exame_status SET "
upd_value = str_status + "=?"
upd_where = " WHERE ano=" + str(ano) + " AND mes=?"
sql_upd = upd_data + upd_value + upd_where
APP_Cursor.execute(insert, data)
APP_Cursor.execute(sql_upd, updata)
mes = int(mes)
mes += 1