def alerta_offp(options):
documento = spark.sql("from documento").\
filter('docu_tpst_dk != 11').\
filter('docu_fsdc_dk = 1')
vista = spark.sql("from vista")
andamento = spark.table('%s.mcpr_andamento' % options['schema_exadata']).\
filter('pcao_dt_cancelamento IS NULL')
sub_andamento = spark.table('%s.mcpr_sub_andamento' % options['schema_exadata']).\
filter('stao_tppr_dk = 6497')
doc_vista = documento.join(vista, documento.DOCU_DK == vista.VIST_DOCU_DK,
'inner')
doc_andamento = doc_vista.join(andamento,
doc_vista.VIST_DK == andamento.PCAO_VIST_DK,
'inner')
doc_sub_andamento = doc_andamento.join(sub_andamento, doc_andamento.PCAO_DK == sub_andamento.STAO_PCAO_DK, 'inner').\
withColumn('dt_fim_prazo', expr("to_timestamp(date_add(pcao_dt_andamento, 365), 'yyyy-MM-dd HH:mm:ss')")).\
withColumn('elapsed', lit(datediff(current_date(), 'dt_fim_prazo')).cast(IntegerType()))
# Pode ter mais de um andamento de oficio
# Nesse caso, consideramos o andamento mais antigo
resultado = doc_sub_andamento.filter('elapsed > 0').\
groupBy(columns[:-1]).agg({'elapsed': 'max'}).\
withColumnRenamed('max(elapsed)', 'alrt_dias_passados')
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado
def alerta_dt2i(options):
documento = spark.sql("from documento")
vista = spark.sql("from vista")
andamento = spark.table('%s.mcpr_andamento' % options['schema_exadata']).\
filter('pcao_dt_cancelamento IS NULL')
adt_ciencia = spark.table('%s.mcpr_sub_andamento' %
options['schema_exadata']).filter(
col('stao_tppr_dk').isin(ciencias))
adt_recurso = spark.table('%s.mcpr_sub_andamento' %
options['schema_exadata']).filter(
col('stao_tppr_dk').isin(recursos))
doc_vista = documento.join(vista, documento.DOCU_DK == vista.VIST_DOCU_DK,
'inner')
doc_andamento = doc_vista.join(andamento,
doc_vista.VIST_DK == andamento.PCAO_VIST_DK,
'inner')
doc_recente = doc_andamento.withColumn(
'elapsed',
lit(datediff(current_date(), 'pcao_dt_andamento')).cast(
IntegerType())).filter('elapsed <= 7').select(columns)
doc_ciencia = doc_andamento.join(
adt_ciencia, doc_andamento.PCAO_DK == adt_ciencia.STAO_PCAO_DK,
'inner').select(
col('docu_dk').alias('cie_docu_dk'),
col('pcao_dt_andamento').alias('cie_dt_mov'))
doc_recurso = doc_andamento.join(
adt_recurso, doc_andamento.PCAO_DK == adt_recurso.STAO_PCAO_DK,
'inner').select(
col('docu_dk').alias('rec_docu_dk'),
col('pcao_dt_andamento').alias('rec_dt_mov'))
doc_cie_rec = doc_ciencia.join(
doc_recurso, doc_ciencia.cie_docu_dk == doc_recurso.rec_docu_dk,
'inner').filter('rec_dt_mov >= cie_dt_mov')
doc_rec_week = doc_cie_rec.join(
doc_recente, doc_cie_rec.rec_docu_dk == doc_recente.alrt_docu_dk,
'inner')
resultado = doc_rec_week.select(
"alrt_docu_dk", "alrt_docu_nr_mp", "alrt_orgi_orga_dk",
"pcao_dt_andamento", "elapsed").groupby([
"alrt_docu_dk",
"alrt_docu_nr_mp",
"alrt_orgi_orga_dk",
]).agg(
max("pcao_dt_andamento").alias("alrt_date_referencia"),
min("elapsed").alias("alrt_dias_referencia"))
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado.select([
'alrt_docu_dk',
'alrt_docu_nr_mp',
'alrt_date_referencia',
'alrt_orgi_orga_dk',
'alrt_dias_referencia',
'alrt_key',
])
def alerta_ro(options):
df = spark.sql("""
WITH ros_que_faltam AS (
SELECT
CAST(substring(proc_numero, 0, 3) AS INTEGER) as nr_delegacia,
MIN(proc_numero) proc_delegacia_inicial,
MAX(proc_numero) max_proc,
substring(MIN(proc_numero), 5, 5) min_serial_delegacia,
substring(MAX(proc_numero), 5, 5) max_serial_delegacia,
CAST(substring(MAX(proc_numero), 5, 5) AS INTEGER) qtd_esperada,
CAST(substring(MAX(proc_numero), 5, 5) AS INTEGER)
- COUNT(DISTINCT proc_numero) qt_ros_faltantes,
COUNT(DISTINCT proc_numero) total_de_ros_recebidos
FROM {0}.seg_pub_in_pol_procedimento
WHERE cast(substring(proc_numero, 11, 4) as int) = year(now())
GROUP BY nr_delegacia
)
SELECT * FROM ros_que_faltam rqf
JOIN {1}.tb_pip_cisp tpc ON rqf.nr_delegacia = tpc.cisp_codigo
WHERE rqf.qt_ros_faltantes >= 1
""".format(options["schema_opengeo"], options["schema_exadata_aux"]))
df = df.withColumn('alrt_key', uuidsha(*key_columns))
return df.select(columns)
def alerta_ctac(options):
"""
Alerta de TACs que não foram comunicadas ao CSMP. Um andamento de TAC
ativa o alerta. Um andamento de ciência ao CSMP em data maior ou igual ao
TAC desativa o alerta. Um andamento de ofício com destinatário ao CSMP
(vide regex) também desativa o alerta.
"""
ANDAMENTOS_OFICIO = (7436, 6581, 6497, 6614, 6615, 6616, 6617, 6618, 6619,
6126, 6989)
ANDAMENTOS_TAC = (1007, 6304, 7858, 6326, 6655, 6670, 4114)
ANDAMENTOS_CIENCIA = (6647, 6643, 6683, 6684, 6685, 6573)
ANDAMENTOS_TAC_CIENCIA = ANDAMENTOS_TAC + ANDAMENTOS_CIENCIA
ANDAMENTOS_OFICIO_CIENCIA = ANDAMENTOS_OFICIO + ANDAMENTOS_CIENCIA
REGEX_CSMP = (
"(CSMP|CONSELHO SUPERIOR|CONSELHO SUPERIOR DO MINIST[ÉE]RIO P[ÚU]BLICO)"
)
resultado = spark.sql("""
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, dt_tac, datediff(current_timestamp(), dt_tac) as elapsed
FROM
(
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, MAX(dt_tac) AS dt_tac, MAX(dt_oficio_csmp) as dt_oficio
FROM
(
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel,
CASE WHEN stao_tppr_dk IN {ANDAMENTOS_OFICIO_CIENCIA} THEN pcao_dt_andamento ELSE NULL END as dt_oficio_csmp,
CASE WHEN stao_tppr_dk IN {ANDAMENTOS_TAC} THEN pcao_dt_andamento ELSE NULL END as dt_tac
FROM documento
JOIN (
SELECT * FROM
vista
JOIN {schema_exadata}.mcpr_andamento ON pcao_vist_dk = vist_dk
JOIN {schema_exadata}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk
WHERE pcao_dt_cancelamento IS NULL
AND pcao_dt_andamento >= to_timestamp('{date_tac_begin}', 'yyyy-MM-dd')
AND (
stao_tppr_dk in {ANDAMENTOS_TAC_CIENCIA} OR
(stao_tppr_dk in {ANDAMENTOS_OFICIO} AND upper(stao_destinatario) REGEXP '{REGEX_CSMP}')
)
) T ON T.vist_docu_dk = docu_dk
) A
GROUP BY docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel
HAVING MAX(dt_tac) IS NOT NULL
) B
WHERE dt_oficio IS NULL OR dt_tac > dt_oficio
""".format(schema_exadata=options['schema_exadata'],
date_tac_begin=options['date_tac_begin'],
ANDAMENTOS_TAC=ANDAMENTOS_TAC,
ANDAMENTOS_OFICIO=ANDAMENTOS_OFICIO,
ANDAMENTOS_TAC_CIENCIA=ANDAMENTOS_TAC_CIENCIA,
ANDAMENTOS_OFICIO_CIENCIA=ANDAMENTOS_OFICIO_CIENCIA,
REGEX_CSMP=REGEX_CSMP))
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado.select(columns)
def alerta_ic1a(options):
ANDAMENTO_PRORROGACAO = 6291
ANDAMENTO_INSTAURACAO = (6511, 6012, 6002)
ANDAMENTOS_TOTAL = (ANDAMENTO_PRORROGACAO,) + ANDAMENTO_INSTAURACAO
TAMANHO_PRAZO = 365
resultado = spark.sql("""
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel,
to_timestamp(date_add(dt_inicio, {TAMANHO_PRAZO}), 'yyyy-MM-dd HH:mm:ss') as dt_fim_prazo,
(datediff(current_timestamp(), dt_inicio) - {TAMANHO_PRAZO}) as elapsed,
B.stao_dk,
CASE WHEN hierarquia IS NOT NULL THEN hierarquia ELSE 'Cadastro do Procedimento no Sistema' END AS hierarquia
FROM
(
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel,
CASE WHEN MAX(pcao_dt_andamento) IS NOT NULL THEN MAX(pcao_dt_andamento) ELSE docu_dt_cadastro END AS dt_inicio,
CASE WHEN MAX(stao_dk) IS NOT NULL THEN MAX(stao_dk) ELSE NULL END AS stao_dk
FROM
(
SELECT docu_dk, docu_nr_mp, docu_dt_cadastro, docu_orgi_orga_dk_responsavel, pcao_dt_andamento, stao_dk
FROM documentos_ativos
LEFT JOIN (SELECT * FROM {schema_exadata}.mcpr_correlacionamento WHERE corr_tpco_dk in (2, 6)) C ON C.corr_docu_dk2 = docu_dk
LEFT JOIN (SELECT * FROM {schema_exadata}.orgi_orgao WHERE orgi_nm_orgao LIKE '%GRUPO DE ATUAÇÃO%') O ON O.orgi_dk = docu_orgi_orga_dk_carga
LEFT JOIN (
SELECT *
FROM vista
JOIN {schema_exadata}.mcpr_andamento ON pcao_vist_dk = vist_dk
JOIN {schema_exadata}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk
WHERE pcao_dt_cancelamento IS NULL
AND stao_tppr_dk in {ANDAMENTOS_TOTAL}
) T ON T.vist_docu_dk = docu_dk
WHERE docu_cldc_dk = 392
AND docu_tpst_dk != 3
AND corr_tpco_dk IS NULL
AND orgi_dk IS NULL
) A
GROUP BY docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, docu_dt_cadastro
) B
LEFT JOIN {schema_exadata}.mcpr_sub_andamento S ON B.stao_dk = S.stao_dk
LEFT JOIN {schema_exadata_aux}.mmps_tp_andamento ON stao_tppr_dk = id
WHERE datediff(current_timestamp(), dt_inicio) > {TAMANHO_PRAZO}
""".format(
schema_exadata=options['schema_exadata'],
schema_exadata_aux=options['schema_exadata_aux'],
ANDAMENTO_INSTAURACAO=ANDAMENTO_INSTAURACAO,
ANDAMENTO_PRORROGACAO=ANDAMENTO_PRORROGACAO,
ANDAMENTOS_TOTAL=ANDAMENTOS_TOTAL,
TAMANHO_PRAZO=TAMANHO_PRAZO
)
)
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado.select(columns)
def alerta_vadf(options):
documento = spark.sql("from documento")
vista = spark.sql("from vista")
resultado = documento.join(vista, vista.VIST_DOCU_DK == documento.DOCU_DK, 'inner').\
filter('docu_fsdc_dk != 1').\
filter('docu_tpst_dk != 11').\
filter('vist_dt_fechamento_vista IS NULL')
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado.select(columns)
def alerta_dntj(options):
documento = spark.sql("from documentos_ativos")
classe = spark.table('%s.mmps_classe_docto' % options['schema_exadata_aux']).\
filter("hierarquia NOT LIKE 'PROCESSO CRIMINAL%'")
personagem = spark.table('%s.mcpr_personagem' % options['schema_exadata']).\
filter("pers_tppe_dk = 7")
pessoa = spark.table('%s.mcpr_pessoa' % options['schema_exadata'])
mp = spark.table('%s.mmps_alias' % options['schema_exadata_aux'])
item = spark.table('%s.mcpr_item_movimentacao' % options['schema_exadata'])
movimentacao = spark.table('%s.mcpr_movimentacao' %
options['schema_exadata'])
interno = spark.table('%s.orgi_orgao' % options['schema_exadata']).\
filter('orgi_tpor_dk = 1')
externo = spark.table('%s.mprj_orgao_ext' % options['schema_exadata']).\
filter('orge_tpoe_dk in (63, 64, 65, 66, 67, 69, 70, 83)')
doc_classe = documento.join(broadcast(classe),
documento.DOCU_CLDC_DK == classe.ID, 'inner')
doc_personagem = doc_classe.join(
personagem, doc_classe.DOCU_DK == personagem.PERS_DOCU_DK, 'inner')
doc_pessoa = doc_personagem.join(
pessoa, doc_personagem.PERS_PESS_DK == pessoa.PESS_DK, 'inner')
doc_mp = doc_pessoa.alias('doc_pessoa').join(
broadcast(mp.alias('mp')),
col('doc_pessoa.PESS_NM_PESSOA') == col('mp.alias'), 'inner')
doc_item = doc_mp.join(item, doc_mp.DOCU_DK == item.ITEM_DOCU_DK, 'inner')
doc_movimentacao = doc_item.join(
movimentacao, doc_item.ITEM_MOVI_DK == movimentacao.MOVI_DK, 'inner')
doc_promotoria = doc_movimentacao.join(
broadcast(interno),
doc_movimentacao.MOVI_ORGA_DK_ORIGEM == interno.ORGI_DK, 'inner')
doc_tribunal = doc_promotoria.join(broadcast(externo), doc_promotoria.MOVI_ORGA_DK_DESTINO == externo.ORGE_ORGA_DK, 'inner').\
groupBy(pre_columns).agg({'movi_dt_recebimento_guia': 'max'}).\
withColumnRenamed('max(movi_dt_recebimento_guia)', 'movi_dt_guia')
item_movi = item.join(movimentacao,
item.ITEM_MOVI_DK == movimentacao.MOVI_DK, 'inner')
doc_retorno = doc_tribunal.join(
item_movi, (doc_tribunal.docu_dk == item_movi.ITEM_DOCU_DK)
& (doc_tribunal.docu_orgi_orga_dk_responsavel
== item_movi.MOVI_ORGA_DK_DESTINO)
& (doc_tribunal.movi_dt_guia < item_movi.MOVI_DT_RECEBIMENTO_GUIA),
'left')
doc_nao_retornado = doc_retorno.filter('movi_dk is null').\
withColumn('dt_guia_tj', expr("to_timestamp(movi_dt_guia, 'yyyy-MM-dd HH:mm:ss')")).\
withColumn('elapsed', lit(datediff(current_date(), 'dt_guia_tj')).cast(IntegerType()))
resultado = doc_nao_retornado.filter('elapsed > 120')
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado.select(columns)
def alerta_nf30(options):
ANDAMENTOS_FINALIZADORES = (6011, 6012, 6013, 6014, 6251, 6252, 6253, 6259, 6260, 6516, 6533, 6556, 6567, 6628, # Movimentos de conversao
6321, 6322, 6323) # Movimentos de indeferimento
ANDAMENTOS_PRORROGACAO = (6291, 7282, 7283)
ANDAMENTOS_AUTUACAO = (6034, 6631, 7751, 7752, 6035, 7754, 7753, 6007, 6632)
ANDAMENTOS_TOTAL = ANDAMENTOS_FINALIZADORES + ANDAMENTOS_PRORROGACAO + ANDAMENTOS_AUTUACAO
resultado = spark.sql("""
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, dt_inicio as data_autuacao, datediff(current_timestamp(), dt_inicio) as elapsed,
CASE WHEN datediff(current_timestamp(), dt_inicio) > 120 THEN 'NF120' ELSE 'NF30' END AS alrt_sigla
FROM
(
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel,
CASE WHEN MAX(dt_autuacao) IS NOT NULL THEN MAX(dt_autuacao) ELSE docu_dt_cadastro END AS dt_inicio,
MAX(nr_dias_prazo) as nr_dias_prazo
FROM
(
SELECT docu_dk, docu_nr_mp, docu_dt_cadastro, docu_orgi_orga_dk_responsavel,
CASE WHEN stao_tppr_dk IN {ANDAMENTOS_AUTUACAO} THEN pcao_dt_andamento ELSE NULL END as dt_autuacao,
CASE WHEN stao_tppr_dk IN {ANDAMENTOS_FINALIZADORES} THEN 1 ELSE 0 END as flag_finalizacao,
CASE WHEN stao_tppr_dk IN {ANDAMENTOS_PRORROGACAO} THEN 120 ELSE 30 END AS nr_dias_prazo
FROM documentos_ativos
LEFT JOIN (SELECT * FROM {schema_exadata}.mcpr_correlacionamento WHERE corr_tpco_dk in (2, 6)) C ON C.corr_docu_dk2 = docu_dk
LEFT JOIN (
SELECT * FROM
vista
JOIN {schema_exadata}.mcpr_andamento ON pcao_vist_dk = vist_dk
JOIN {schema_exadata}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk
WHERE pcao_dt_cancelamento IS NULL
AND stao_tppr_dk in {ANDAMENTOS_TOTAL}
) T ON T.vist_docu_dk = docu_dk
WHERE docu_cldc_dk = 393
AND corr_tpco_dk IS NULL
) A
GROUP BY docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, docu_dt_cadastro
HAVING MAX(flag_finalizacao) = 0
) B
WHERE datediff(current_timestamp(), dt_inicio) > nr_dias_prazo
""".format(
schema_exadata=options['schema_exadata'],
ANDAMENTOS_AUTUACAO=ANDAMENTOS_AUTUACAO,
ANDAMENTOS_FINALIZADORES=ANDAMENTOS_FINALIZADORES,
ANDAMENTOS_PRORROGACAO=ANDAMENTOS_PRORROGACAO,
ANDAMENTOS_TOTAL=ANDAMENTOS_TOTAL
)
)
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado.select(columns)
def alerta_ppfp(options):
ANDAMENTOS_PRORROGACAO = 6291
ANDAMENTOS_AUTUACAO = 6011
ANDAMENTOS_TOTAL = (ANDAMENTOS_PRORROGACAO, ANDAMENTOS_AUTUACAO)
resultado = spark.sql("""
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, dt_fim_prazo,
CASE WHEN elapsed > nr_dias_prazo THEN 'PPFP' ELSE 'PPPV' END AS alrt_sigla,
abs(elapsed - nr_dias_prazo) as elapsed
FROM (
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel,
to_timestamp(date_add(dt_inicio, nr_dias_prazo), 'yyyy-MM-dd HH:mm:ss') as dt_fim_prazo,
datediff(current_timestamp(), dt_inicio) as elapsed, nr_dias_prazo
FROM
(
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel,
CASE WHEN MAX(dt_instauracao) IS NOT NULL THEN MAX(dt_instauracao) ELSE docu_dt_cadastro END AS dt_inicio,
MAX(nr_dias_prazo) as nr_dias_prazo
FROM
(
SELECT docu_dk, docu_nr_mp, docu_dt_cadastro, docu_orgi_orga_dk_responsavel,
CASE WHEN stao_tppr_dk = {ANDAMENTOS_AUTUACAO} THEN pcao_dt_andamento ELSE NULL END as dt_instauracao,
CASE WHEN stao_tppr_dk = {ANDAMENTOS_PRORROGACAO} THEN 180 ELSE 90 END AS nr_dias_prazo
FROM documentos_ativos
LEFT JOIN (SELECT * FROM {schema_exadata}.mcpr_correlacionamento WHERE corr_tpco_dk in (2, 6)) C ON C.corr_docu_dk2 = docu_dk
LEFT JOIN (
SELECT * FROM
vista
JOIN {schema_exadata}.mcpr_andamento ON pcao_vist_dk = vist_dk
JOIN {schema_exadata}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk
WHERE pcao_dt_cancelamento IS NULL
AND stao_tppr_dk IN {ANDAMENTOS_TOTAL}
) T ON T.vist_docu_dk = docu_dk
WHERE docu_cldc_dk = 395
AND docu_tpst_dk != 3
AND corr_tpco_dk IS NULL
) A
GROUP BY docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, docu_dt_cadastro
) B
WHERE datediff(current_timestamp(), dt_inicio) > nr_dias_prazo - 20
) C
""".format(schema_exadata=options['schema_exadata'],
ANDAMENTOS_AUTUACAO=ANDAMENTOS_AUTUACAO,
ANDAMENTOS_PRORROGACAO=ANDAMENTOS_PRORROGACAO,
ANDAMENTOS_TOTAL=ANDAMENTOS_TOTAL))
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado.select(columns)
def alerta_comp(options):
df1 = spark.sql("""
SELECT contratacao, id_item, contrato_iditem, item, dt_contratacao, var_perc
FROM {0}.compras_fora_padrao_capital
WHERE var_perc >= 20
""".format(options["schema_alertas_compras"]))
df2 = spark.sql("""
SELECT id_orgao
FROM {0}.atualizacao_pj_pacote
WHERE UPPER(pacote_atribuicao) LIKE '%%CIDADANIA%%' AND orgao_codamp LIKE '%%CAPITAL%%'
""".format(options["schema_exadata_aux"]))
df = df1.crossJoin(df2)
df = df.withColumn('alrt_key', uuidsha(*key_columns))
return df.select(columns)
def alerta_ouvi(options):
# documento = spark.table('%s.mcpr_documento' % options['schema_exadata'])
documento = spark.sql("from documento")
item_mov = spark.table('%s.mcpr_item_movimentacao' %
options['schema_exadata'])
mov = spark.table('%s.mcpr_movimentacao' % options['schema_exadata'])
doc_mov = item_mov.join(mov, item_mov.ITEM_MOVI_DK == mov.MOVI_DK, 'inner')
resultado = documento.join(doc_mov, documento.DOCU_DK == doc_mov.ITEM_DOCU_DK, 'inner').\
filter('docu_tpdc_dk = 119').\
filter('docu_tpst_dk != 11').\
filter('item_in_recebimento IS NULL').\
filter('movi_tpgu_dk == 2').\
filter('movi_dt_recebimento_guia IS NULL')
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado.select(columns)
def alerta_pa1a(options):
ANDAMENTO_PRORROGACAO = 6291
ANDAMENTO_INSTAURACAO = 6013
ANDAMENTOS_TOTAL = (ANDAMENTO_PRORROGACAO, ANDAMENTO_INSTAURACAO)
TAMANHO_PRAZO = 365
resultado = spark.sql("""
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel,
to_timestamp(date_add(dt_inicio, {TAMANHO_PRAZO}), 'yyyy-MM-dd HH:mm:ss') as dt_fim_prazo,
(datediff(current_timestamp(), dt_inicio) - {TAMANHO_PRAZO}) as elapsed
FROM
(
SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel,
CASE WHEN MAX(pcao_dt_andamento) IS NOT NULL THEN MAX(pcao_dt_andamento) ELSE docu_dt_cadastro END AS dt_inicio
FROM
(
SELECT docu_dk, docu_nr_mp, docu_dt_cadastro, docu_orgi_orga_dk_responsavel, pcao_dt_andamento
FROM documentos_ativos
LEFT JOIN (SELECT * FROM {schema_exadata}.mcpr_correlacionamento WHERE corr_tpco_dk in (2, 6)) C ON C.corr_docu_dk2 = docu_dk
LEFT JOIN (
SELECT *
FROM vista
JOIN {schema_exadata}.mcpr_andamento ON pcao_vist_dk = vist_dk
JOIN {schema_exadata}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk
WHERE pcao_dt_cancelamento IS NULL
AND stao_tppr_dk in {ANDAMENTOS_TOTAL}
) T ON T.vist_docu_dk = docu_dk
WHERE docu_cldc_dk IN (51219, 51220, 51221, 51222, 51223)
AND docu_tpst_dk != 3
AND corr_tpco_dk IS NULL
) A
GROUP BY docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, docu_dt_cadastro
) B
WHERE datediff(current_timestamp(), dt_inicio) > {TAMANHO_PRAZO}
""".format(schema_exadata=options['schema_exadata'],
ANDAMENTO_INSTAURACAO=ANDAMENTO_INSTAURACAO,
ANDAMENTO_PRORROGACAO=ANDAMENTO_PRORROGACAO,
ANDAMENTOS_TOTAL=ANDAMENTOS_TOTAL,
TAMANHO_PRAZO=TAMANHO_PRAZO))
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado.filter('elapsed > 0').select(columns)
def alerta_abr1(options):
# cria este alerta para todos os meses se estiver em ambiente de DEV
if options["schema_exadata_aux"].endswith("_dev"):
months = "%s" % ",".join(str(i) for i in range(1, 13))
else:
months = "4"
procedimentos = spark.sql("""
SELECT
docu_orgi_orga_dk_responsavel, docu_nr_mp, docu_dt_cadastro, docu_dk
FROM documentos_ativos
WHERE datediff(last_day(now()), docu_dt_cadastro) / 365.2425 > 1
AND docu_dt_cancelamento IS NULL
AND docu_cldc_dk = 392
AND docu_tpst_dk != 3
AND (
year(current_date()) = 2020 AND month(current_date()) = 11
OR month(current_date()) IN ({months})
)
""".format(schema_exadata=options["schema_exadata"], months=months))
procedimentos.createOrReplaceTempView("procedimentos")
df = spark.sql("""
SELECT
docu_orgi_orga_dk_responsavel AS id_orgao,
COUNT(1) AS nr_procedimentos,
concat_ws('', year(current_date()), month(current_date())) as ano_mes
FROM procedimentos
INNER JOIN {schema_aux}.atualizacao_pj_pacote pac ON pac.id_orgao = docu_orgi_orga_dk_responsavel
AND UPPER(orgi_nm_orgao) LIKE '%TUTELA%'
GROUP BY docu_orgi_orga_dk_responsavel
""".format(schema_aux=options["schema_exadata_aux"]))
df = df.withColumn('alrt_key', uuidsha(*key_columns))
procedimentos.write.mode("overwrite").saveAsTable("{}.{}".format(
options['schema_alertas'], options['abr1_tabela_aux']))
return df.select(columns)
def alerta_gate(options):
# possivel filter nos documentos?
documento = spark.sql("from documento")
vista = spark.sql(
"select VIST_DOCU_DK, max(VIST_DT_ABERTURA_VISTA) as DT_MAX_VISTA from vista group by VIST_DOCU_DK"
)
instrucao = spark.table('%s.gate_info_tecnica' % options['schema_exadata'])
doc_instrucao = documento.join(broadcast(instrucao),
documento.DOCU_DK == instrucao.ITCN_DOCU_DK,
'inner')
doc_vista = doc_instrucao.join(vista,
doc_instrucao.DOCU_DK == vista.VIST_DOCU_DK,
'left')
doc_sem_vista = doc_vista.filter('DT_MAX_VISTA is null')
doc_vista_anterior = doc_vista.filter('ITCN_DT_CADASTRO > DT_MAX_VISTA')
resultado = doc_sem_vista.union(doc_vista_anterior).\
withColumn('elapsed', lit(datediff(current_date(), 'ITCN_DT_CADASTRO')).cast(IntegerType()))
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado.select(columns)
def alerta_febt(options):
df = spark.sql("""
WITH ultimo_ro_enviado AS (
SELECT
CAST(substring(proc_numero, 0, 3) AS INTEGER) as numero_delegacia,
datediff(current_timestamp(), MAX(data_liberacao)) diff_ultimo_envio,
MAX(data_liberacao) as ultima_liberacao
FROM {0}.seg_pub_in_pol_procedimento
GROUP BY numero_delegacia
)
SELECT
numero_delegacia,
pip_codigo,
cisp_nome_apresentacao,
ultima_liberacao
FROM ultimo_ro_enviado ure
JOIN {1}.tb_pip_cisp tpc ON ure.numero_delegacia = tpc.cisp_codigo
AND ure.diff_ultimo_envio > 30
""".format(options["schema_opengeo"], options["schema_exadata_aux"]))
df = df.withColumn("numero_delegacia", col("numero_delegacia").cast(StringType()))
df = df.withColumn('alrt_key', uuidsha(*key_columns))
return df.select(columns)
def alerta_prcr(options):
# data do fato será usada para a maioria dos cálculos
# Caso a data do fato seja NULL, ou seja maior que a data de cadastro, usar cadastro como data do fato
# Apenas códigos de pacotes de PIPs
# Mantém-se a data do fato original (mesmo NULL ou maior que dt cadastro) para a tabela de metadados do cálculo
doc_pena = spark.sql("""
SELECT docu_dk, docu_nr_mp, docu_nr_externo, docu_tx_etiqueta, docu_dt_fato as docu_dt_fato_original,
CASE WHEN docu_dt_fato < docu_dt_cadastro THEN docu_dt_fato ELSE docu_dt_cadastro END as docu_dt_fato,
docu_dt_cadastro, docu_orgi_orga_dk_responsavel,
cls.id as cldc_dk,
cls.cod_mgp as cldc_ds_classe,
cls.hierarquia as cldc_ds_hierarquia,
tpa.id,
tpa.artigo_lei,
tpa.max_pena,
tpa.nome_delito,
tpa.multiplicador,
tpa.abuso_menor
FROM documentos_ativos
LEFT JOIN {0}.mmps_classe_docto cls ON cls.id = docu_cldc_dk
JOIN {1}.mcpr_assunto_documento ON docu_dk = asdo_docu_dk
JOIN {0}.tb_penas_assuntos tpa ON tpa.id = asdo_assu_dk
JOIN {0}.atualizacao_pj_pacote ON docu_orgi_orga_dk_responsavel = id_orgao
WHERE docu_dt_cadastro >= '2010-01-01'
AND max_pena IS NOT NULL
AND cod_pct IN (200, 201, 202, 203, 204, 205, 206, 207, 208, 209)
AND asdo_dt_fim IS NULL -- tira assuntos que acabaram
""".format(options['schema_exadata_aux'], options['schema_exadata']))
doc_pena.createOrReplaceTempView('DOC_PENA')
# Calcula tempos de prescrição a partir das penas máximas
# Caso um dos assuntos seja multiplicador, multiplicar as penas pelo fator
doc_prescricao = spark.sql("""
WITH PENA_FATORES AS (
SELECT docu_dk, EXP(SUM(LN(max_pena))) AS fator_pena, concat_ws(', ', collect_list(nome_delito)) AS delitos_multiplicadores
FROM DOC_PENA
WHERE multiplicador = 1
GROUP BY docu_dk
)
SELECT *,
CASE
WHEN max_pena_fatorado < 1 THEN 3
WHEN max_pena_fatorado < 2 THEN 4
WHEN max_pena_fatorado < 4 THEN 8
WHEN max_pena_fatorado < 8 THEN 12
WHEN max_pena_fatorado < 12 THEN 16
ELSE 20 END AS tempo_prescricao
FROM (
SELECT
P.*,
CASE WHEN fator_pena IS NOT NULL THEN max_pena * fator_pena ELSE max_pena END AS max_pena_fatorado,
fator_pena,
delitos_multiplicadores
FROM DOC_PENA P
LEFT JOIN PENA_FATORES F ON F.docu_dk = P.docu_dk
WHERE multiplicador = 0
) t
""")
doc_prescricao.createOrReplaceTempView('DOC_PRESCRICAO')
# Se o acusado tiver < 21 ou >= 70, seja na data do fato ou na data presente, multiplicar tempo_prescricao por 0.5
doc_prescricao_fatorado = spark.sql("""
WITH PRESCRICAO_FATORES AS (
SELECT docu_dk, investigado_pess_dk, investigado_nm,
CASE WHEN NOT (dt_compare >= dt_21 AND current_timestamp() < dt_70) THEN 0.5 ELSE NULL END AS fator_prescricao
FROM (
SELECT DISTINCT
docu_dk,
pesf_pess_dk as investigado_pess_dk,
pesf_nm_pessoa_fisica as investigado_nm,
add_months(pesf_dt_nasc, 21 * 12) AS dt_21,
add_months(pesf_dt_nasc, 70 * 12) AS dt_70,
docu_dt_fato AS dt_compare
FROM DOC_PRESCRICAO
JOIN {0}.mcpr_personagem ON pers_docu_dk = docu_dk
JOIN {0}.mcpr_pessoa_fisica ON pers_pesf_dk = pesf_pess_dk
WHERE pers_tppe_dk IN (290, 7, 21, 317, 20, 14, 32, 345, 40, 5, 24)
AND pesf_nm_pessoa_fisica != 'MP'
) t
)
SELECT P.*,
CASE WHEN fator_prescricao IS NOT NULL THEN tempo_prescricao * fator_prescricao ELSE tempo_prescricao END AS tempo_prescricao_fatorado,
fator_prescricao IS NOT NULL AS investigado_maior_70_menor_21,
investigado_pess_dk,
investigado_nm
FROM DOC_PRESCRICAO P
LEFT JOIN PRESCRICAO_FATORES F ON F.docu_dk = P.docu_dk
""".format(options['schema_exadata']))
doc_prescricao_fatorado.createOrReplaceTempView('DOC_PRESCRICAO_FATORADO')
# Calcular data inicial de prescrição
# Casos em que houve rescisão de acordo de não persecução penal, data inicial é a data do andamento
spark.sql("""
SELECT vist_docu_dk, pcao_dt_andamento
FROM vista
JOIN {0}.mcpr_andamento ON vist_dk = pcao_vist_dk
JOIN {0}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk
WHERE stao_tppr_dk = 7920
AND year_month >= 201901
""".format(options['schema_exadata'])).createOrReplaceTempView('DOCS_ANPP')
# Casos de abuso de menor, data inicial é a data de 18 anos do menor,
# no caso em que o menor tinha menos de 18 na data do fato/cadastro
# Prioridade de data inicial: data de 18 anos (caso abuso menor), rescisão de acordo ANPP, dt_fato
dt_inicial = spark.sql("""
WITH DOCS_ABUSO_MENOR AS (
SELECT docu_dk, MAX(dt_18_anos) AS dt_18_anos
FROM (
SELECT docu_dk, CASE WHEN dt_18_anos > docu_dt_fato THEN dt_18_anos ELSE NULL END AS dt_18_anos
FROM DOC_PRESCRICAO_FATORADO P
JOIN {0}.mcpr_personagem ON pers_docu_dk = docu_dk
JOIN (
SELECT
PF.*,
cast(add_months(pesf_dt_nasc, 18*12) as timestamp) AS dt_18_anos
FROM {0}.mcpr_pessoa_fisica PF
) t ON pers_pesf_dk = pesf_pess_dk
WHERE abuso_menor = 1
AND pers_tppe_dk IN (3, 13, 18, 6, 248, 290)
) t2
GROUP BY docu_dk
)
SELECT P.*,
CASE
WHEN dt_18_anos IS NOT NULL AND abuso_menor = 1 THEN dt_18_anos
WHEN pcao_dt_andamento IS NOT NULL THEN pcao_dt_andamento
ELSE docu_dt_fato END AS dt_inicial_prescricao,
dt_18_anos AS vitima_menor_mais_jovem_dt_18_anos,
pcao_dt_andamento AS dt_acordo_npp
FROM DOC_PRESCRICAO_FATORADO P
LEFT JOIN DOCS_ANPP ON vist_docu_dk = docu_dk
LEFT JOIN DOCS_ABUSO_MENOR M ON M.docu_dk = P.docu_dk
""".format(options['schema_exadata']))
dt_inicial.createOrReplaceTempView('DOCS_DT_INICIAL_PRESCRICAO')
# Data de prescrição = data inicial de prescrição + tempo de prescrição
resultado = spark.sql("""
SELECT
D.*,
cast(add_months(dt_inicial_prescricao, tempo_prescricao_fatorado * 12) as timestamp) AS data_prescricao
FROM DOCS_DT_INICIAL_PRESCRICAO D
""").\
withColumn("elapsed", lit(datediff(current_date(), 'data_prescricao')).cast(IntegerType()))
resultado.createOrReplaceTempView('TEMPO_PARA_PRESCRICAO')
spark.catalog.cacheTable("TEMPO_PARA_PRESCRICAO")
# Tabela de detalhes dos alertas, para ser usada no overlay do alerta, e também para debugging
spark.sql("""
SELECT
docu_dk AS adpr_docu_dk,
investigado_pess_dk as adpr_investigado_pess_dk,
investigado_nm as adpr_investigado_nm,
nome_delito as adpr_nome_delito,
id as adpr_id_assunto,
artigo_lei as adpr_artigo_lei,
abuso_menor as adpr_abuso_menor,
max_pena as adpr_max_pena,
delitos_multiplicadores as adpr_delitos_multiplicadores,
fator_pena as adpr_fator_pena,
max_pena_fatorado as adpr_max_pena_fatorado,
tempo_prescricao as adpr_tempo_prescricao,
investigado_maior_70_menor_21 as adpr_investigado_prescricao_reduzida,
tempo_prescricao_fatorado as adpr_tempo_prescricao_fatorado,
vitima_menor_mais_jovem_dt_18_anos as adpr_dt_18_anos_menor_vitima,
dt_acordo_npp as adpr_dt_acordo_npp,
docu_dt_fato_original as adpr_docu_dt_fato,
docu_dt_cadastro as adpr_docu_dt_cadastro,
cast(dt_inicial_prescricao as string) as adpr_dt_inicial_prescricao,
data_prescricao as adpr_dt_final_prescricao,
elapsed as adpr_dias_prescrito
FROM TEMPO_PARA_PRESCRICAO
""").write.mode('overwrite').saveAsTable('{}.{}'.format(
options['schema_alertas'], options['prescricao_tabela_detalhe']))
LIMIAR_PRESCRICAO_PROXIMA = -options['prescricao_limiar']
subtipos = spark.sql("""
SELECT T.*,
CASE
WHEN elapsed > 0 THEN 2 -- prescrito
WHEN elapsed <= {LIMIAR_PRESCRICAO_PROXIMA} THEN 0 -- nem prescrito nem proximo
ELSE 1 -- proximo de prescrever
END AS status_prescricao
FROM TEMPO_PARA_PRESCRICAO T
""".format(LIMIAR_PRESCRICAO_PROXIMA=LIMIAR_PRESCRICAO_PROXIMA))
max_min_status = subtipos.groupBy(columns[:-1]).agg(min('status_prescricao'), max('status_prescricao'), min('elapsed')).\
withColumnRenamed('max(status_prescricao)', 'max_status').\
withColumnRenamed('min(status_prescricao)', 'min_status').\
withColumnRenamed('min(elapsed)', 'alrt_dias_referencia')
max_min_status.createOrReplaceTempView('MAX_MIN_STATUS')
# Os WHEN precisam ser feitos na ordem PRCR1, 2, 3 e depois 4!
resultado = spark.sql("""
SELECT T.*,
CASE
WHEN min_status = 2 THEN 'PRCR1' -- todos prescritos
WHEN min_status = 1 THEN 'PRCR2' -- todos próximos de prescrever
WHEN max_status = 2 THEN 'PRCR3' -- subentende-se min=0 aqui, logo, algum prescrito (mas não todos próximos)
WHEN max_status = 1 THEN 'PRCR4' -- subentende-se min=0, logo, nenhum prescrito, mas algum próximo (não todos)
ELSE NULL -- não entra em nenhum caso (será filtrado)
END AS alrt_sigla,
CASE
WHEN min_status = 2 THEN 'Todos os crimes prescritos'
WHEN min_status = 1 THEN 'Todos os crimes próximos de prescrever'
WHEN max_status = 2 THEN 'Algum crime prescrito'
WHEN max_status = 1 THEN 'Algum crime próximo de prescrever'
ELSE NULL
END AS alrt_descricao
FROM MAX_MIN_STATUS T
""")
resultado = resultado.filter('alrt_sigla IS NOT NULL').select(
columns_alias + ['alrt_sigla', 'alrt_descricao'])
resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns))
return resultado
def alerta_isps(options):
ano_referencia = spark.sql("""
SELECT MAX(ano_referencia) as max_ano
FROM {0}.plataforma_amb_saneamento_snis_info_indic_agua
""".format(options["schema_opengeo"])).collect()[0]['max_ano']
# Dados de saneamento são liberados a cada ano
# Assim, não é necessário calculá-los a cada vez que rodar o alerta
try:
resultados = spark.sql("""
SELECT alrt_orgi_orga_dk, isps_indicador, isps_municipio, alrt_key, isps_ano_referencia
FROM {0}.{1}
WHERE isps_ano_referencia = {2}
""".format(options['schema_alertas'], options['isps_tabela_aux'],
ano_referencia))
if resultados.count() > 0:
return resultados
except:
pass
agua = spark.sql("""
WITH agregados AS (
SELECT cod_mun, municipio, in009, in013, in023, in049
FROM {0}.plataforma_amb_saneamento_snis_info_indic_agua
WHERE ano_referencia = {1}
AND cod_prest IS NULL
),
indicadores AS (
SELECT municipio,
CASE WHEN A.in009 < R.in009 THEN 'Índice de Hidrometação' ELSE NULL END AS ind1,
CASE WHEN A.in013 > R.in013 THEN 'Índice de Perdas de Faturamento' ELSE NULL END AS ind2,
CASE WHEN A.in023 < R.in023 THEN 'Índice de Atendimento Urbano de Água' ELSE NULL END AS ind3,
CASE WHEN A.in049 > R.in049 THEN 'Índice de Perdas na Distribuição' ELSE NULL END AS ind4
FROM agregados A
JOIN (SELECT cod_mun, in009, in013, in023, in049 FROM agregados WHERE cod_mun = 33) R ON R.cod_mun != A.cod_mun
)
SELECT municipio, ind1 as indicador
FROM indicadores
WHERE ind1 IS NOT NULL
UNION ALL
SELECT municipio, ind2 as indicador
FROM indicadores
WHERE ind2 IS NOT NULL
UNION ALL
SELECT municipio, ind3 as indicador
FROM indicadores
WHERE ind3 IS NOT NULL
UNION ALL
SELECT municipio, ind4 as indicador
FROM indicadores
WHERE ind4 IS NOT NULL
""".format(options["schema_opengeo"], ano_referencia))
agua.createOrReplaceTempView('AGUA')
esgoto = spark.sql("""
WITH agregados AS (
SELECT cod_mun, municipio, in015, in016, in024, in046
FROM {0}.plataforma_amb_saneamento_snis_info_indic_esgoto
WHERE ano_referencia = {1}
AND cod_prest IS NULL
),
indicadores AS (
SELECT municipio,
CASE WHEN A.in015 < R.in015 THEN 'Índice de Coleta de Esgoto' ELSE NULL END AS ind1,
CASE WHEN A.in016 < R.in016 THEN 'Índice de Tratamento de Esgoto' ELSE NULL END AS ind2,
CASE WHEN A.in024 < R.in024 THEN 'Índice de Atendimento Urbano de Esgoto Referido' ELSE NULL END AS ind3,
CASE WHEN A.in046 < R.in046 THEN 'Índice de Esgoto Tratado Referido à Água Consumida' ELSE NULL END AS ind4
FROM agregados A
JOIN (SELECT cod_mun, in015, in016, in024, in046 FROM agregados WHERE cod_mun = 33) R ON R.cod_mun != A.cod_mun
)
SELECT municipio, ind1 as indicador
FROM indicadores
WHERE ind1 IS NOT NULL
UNION ALL
SELECT municipio, ind2 as indicador
FROM indicadores
WHERE ind2 IS NOT NULL
UNION ALL
SELECT municipio, ind3 as indicador
FROM indicadores
WHERE ind3 IS NOT NULL
UNION ALL
SELECT municipio, ind4 as indicador
FROM indicadores
WHERE ind4 IS NOT NULL
""".format(options["schema_opengeo"], ano_referencia))
esgoto.createOrReplaceTempView('ESGOTO')
# Tabela de drenagem não possui os agregados para o estado diretamente
# Assim, é necessário calculá-los a partir dos dados de base
drenagem = spark.sql("""
WITH agregados AS (
SELECT
sum(ri013)/sum(ge008) as in040,
((sum(ri029)+sum(ri067))/sum(ge006)) as in041,
sum(ie024)/sum(ie017) as in021,
sum(ie019)/sum(ie017) as in020
FROM {0}.meio_ambiente_amb_saneamento_snis_drenagem_info_indic_2018
),
indicadores AS (
SELECT A.municipio,
CASE WHEN A.in020 < R.in020 THEN 'Taxa de Cobertura de Pavimentação e Meio-Fio na Área Urbana do Município' ELSE NULL END AS ind1,
CASE WHEN A.in021 > R.in021 THEN 'Taxa de Cobertura de Vias Públicas com Redes ou Canais Pluviais Subterrâneos na Área Urbana' ELSE NULL END AS ind2,
CASE WHEN A.in040 > R.in040 THEN 'Parcela de Domicílios em Situação de Risco de Inundação' ELSE NULL END AS ind3,
CASE WHEN A.in041 > R.in041 THEN 'Parcela da População Impactada por Eventos Hidrológicos' ELSE NULL END AS ind4
FROM {0}.plataforma_amb_saneamento_snis_info_indic_drenagem A
JOIN agregados R ON 1 = 1
WHERE ano_referencia = {1}
)
SELECT municipio, ind1 as indicador
FROM indicadores
WHERE ind1 IS NOT NULL
UNION ALL
SELECT municipio, ind2 as indicador
FROM indicadores
WHERE ind2 IS NOT NULL
UNION ALL
SELECT municipio, ind3 as indicador
FROM indicadores
WHERE ind3 IS NOT NULL
UNION ALL
SELECT municipio, ind4 as indicador
FROM indicadores
WHERE ind4 IS NOT NULL
""".format(options["schema_opengeo"], ano_referencia))
drenagem.createOrReplaceTempView('DRENAGEM')
INDICADORES = spark.sql("""
SELECT * FROM AGUA
UNION ALL
SELECT * FROM ESGOTO
UNION ALL
SELECT * FROM DRENAGEM
""")
INDICADORES.createOrReplaceTempView('INDICADORES')
resultados = spark.sql("""
SELECT P.id_orgao as alrt_orgi_orga_dk, I.indicador as isps_indicador, I.municipio as isps_municipio
FROM {0}.atualizacao_pj_pacote P
JOIN {1}.institucional_orgaos_meio_ambiente M ON M.cod_orgao = P.id_orgao
JOIN INDICADORES I ON I.municipio = M.comarca
WHERE cod_pct IN (20, 21, 22, 24, 28, 183)
""".format(options['schema_exadata_aux'], options['schema_opengeo']))
resultados.createOrReplaceTempView('RESULTADOS_ISPS')
spark.catalog.cacheTable("RESULTADOS_ISPS")
resultados = resultados.withColumn('isps_ano_referencia', lit(ano_referencia).cast(IntegerType()))\
.withColumn('alrt_key', uuidsha(*key_columns))
resultados = resultados.select(columns)
resultados.write.mode('append').saveAsTable('{}.{}'.format(
options['schema_alertas'], options['isps_tabela_aux']))
return resultados
def alerta_bdpa(options):
documento = spark.sql("from documento").filter('DOCU_TPST_DK = 3').filter('DOCU_FSDC_DK = 1')
orga_externo = spark.table('%s.mprj_orgao_ext' % options['schema_exadata']).\
withColumnRenamed('ORGE_NM_ORGAO', 'nm_delegacia')
doc_origem = documento.join(
orga_externo,
documento.DOCU_ORGE_ORGA_DK_DELEG_ORIGEM == orga_externo.ORGE_ORGA_DK,
'left'
)
vista = spark.sql("from vista")
doc_vista = doc_origem.join(vista, doc_origem.DOCU_DK == vista.VIST_DOCU_DK, 'inner')
andamento = spark.table('%s.mcpr_andamento' % options['schema_exadata']).\
filter(datediff(to_date(lit("2013-01-01")), 'PCAO_DT_ANDAMENTO') <= 0)
doc_andamento = doc_vista.join(andamento, doc_vista.VIST_DK == andamento.PCAO_VIST_DK, 'inner')
last_andamento = doc_andamento.\
groupBy([col('DOCU_DK'),]).agg({'PCAO_DT_ANDAMENTO': 'max'}).\
withColumnRenamed('max(PCAO_DT_ANDAMENTO)', 'dt_last_andamento').\
withColumnRenamed('DOCU_DK', 'last_docu_dk')
doc_last_andamento = doc_andamento.join(
last_andamento,
[
doc_andamento.DOCU_DK == last_andamento.last_docu_dk,
doc_andamento.PCAO_DT_ANDAMENTO == last_andamento.dt_last_andamento
],
'inner'
)
sub_andamento = spark.table('%s.mcpr_sub_andamento' % options['schema_exadata'])
doc_sub_andamento = doc_last_andamento.join(sub_andamento, doc_last_andamento.PCAO_DK == sub_andamento.STAO_PCAO_DK, 'inner')
tp_baixa = spark.table('%s.mmps_tp_andamento' % options['schema_exadata_aux']).\
filter('id in (6006, 6010, 6363, 6494, 6495, 6519, 6520, 6521, 6522, 6523)')
doc_baixa = doc_sub_andamento.join(tp_baixa, doc_sub_andamento.STAO_TPPR_DK == tp_baixa.ID, 'inner').\
filter('STAO_NR_DIAS_PRAZO IS NOT NULL')
item_mov = spark.table('%s.mcpr_item_movimentacao' % options['schema_exadata'])
doc_item = doc_baixa.join(item_mov, doc_baixa.DOCU_DK == item_mov.ITEM_DOCU_DK, 'inner')
movimentacao = spark.table('%s.mcpr_movimentacao' % options['schema_exadata']).\
withColumn(
'movi_dt_guia',
coalesce(
col('MOVI_DT_RECEBIMENTO_GUIA'),
col('MOVI_DT_ENVIO_GUIA'),
col('MOVI_DT_CRIACAO_GUIA')
)
)
doc_mov = doc_item.join(movimentacao, doc_item.ITEM_MOVI_DK == movimentacao.MOVI_DK, 'inner')
last_mov = doc_mov.groupBy(['docu_dk']).agg({'movi_dt_guia': 'max'}).\
withColumnRenamed('max(movi_dt_guia)', 'dt_guia').\
withColumnRenamed('docu_dk', 'last_mov_docu_dk')
doc_mov_dest = last_mov.\
join(
doc_mov,
[
doc_mov.DOCU_DK == last_mov.last_mov_docu_dk,
doc_mov.movi_dt_guia == last_mov.dt_guia,
],
'inner')
# ORGAOS DA POLICIA
orga_policia = orga_externo.filter('ORGE_TPOE_DK IN (60, 61, 68)').\
withColumnRenamed('nm_delegacia', 'nm_orga_destino').\
withColumnRenamed('ORGE_ORGA_DK', 'ORGE_ORGA_DK_POLICIA')
# APENAS DELEGACIAS
# orga_delegacia = orga_externo.filter('ORGE_TPOE_DK IN (60, 61)')
doc_mov_cop = doc_mov_dest.join(orga_policia, doc_mov_dest.MOVI_ORGA_DK_DESTINO == orga_policia.ORGE_ORGA_DK_POLICIA)
doc_lost = doc_mov_cop.withColumn("dt_fim_prazo", expr("date_add(dt_guia, stao_nr_dias_prazo)")).\
withColumn('elapsed', lit(datediff(current_date(), 'dt_fim_prazo')).cast(IntegerType())).\
filter('elapsed > 0')
doc_lost = doc_lost.withColumn('alrt_key', uuidsha(*key_columns))
return doc_lost.select(columns).distinct()
