Esempio n. 1
0
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)
Esempio n. 2
0
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)
Esempio n. 3
0
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)
Esempio n. 4
0
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)
Esempio n. 5
0
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)
Esempio n. 6
0
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
Esempio n. 7
0
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)
Esempio n. 8
0
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)
Esempio n. 9
0
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)
Esempio n. 10
0
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)
Esempio n. 11
0
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)
Esempio n. 12
0
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)
Esempio n. 13
0
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