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_dord(options):
#documento = spark.table('%s.mcpr_documento' % options['schema_exadata'])
documento = spark.sql("from documento")
# vista = spark.table('%s.mcpr_vista' % options['schema_exadata'])
vista = spark.sql("from vista")
andamento = spark.table(
'%s.mcpr_andamento' %
options['schema_exadata']).filter('pcao_tpsa_dk = 2')
doc_vista = documento.join(vista, vista.VIST_DOCU_DK == documento.DOCU_DK)
doc_andamento = doc_vista.join(andamento,
doc_vista.VIST_DK == andamento.PCAO_VIST_DK)
last_andamento = doc_andamento.select(['docu_dk', 'pcao_dt_andamento']).\
groupBy('docu_dk').agg({'pcao_dt_andamento': 'max'}).\
withColumnRenamed('max(pcao_dt_andamento)', 'last_date').\
withColumnRenamed('docu_dk', 'land_docu_dk')
check_andamento = doc_andamento.join(
last_andamento, (doc_andamento.DOCU_DK == last_andamento.land_docu_dk)
& (doc_andamento.PCAO_DT_ANDAMENTO == last_andamento.last_date))
return check_andamento.\
filter('docu_tpst_dk != 11').\
filter('docu_fsdc_dk = 1').\
filter('vist_orgi_orga_dk != docu_orgi_orga_dk_responsavel').\
select(columns)
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_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 generate_relationship_csv_for_neo4j(
destination,
table_name,
n_partitions=100):
dest_folder = '{}/relationships/{}'.format(destination, table_name)
data = spark.sql("""
SELECT * FROM {}
""".format(table_name)).repartition(n_partitions)
data.write\
.mode('overwrite')\
.option('quote', '"')\
.option('escape', '"')\
.csv("{}/data".format(dest_folder))
header = []
for col_name in data.columns:
if col_name == 'start_node':
header.append(col_name + ':START_ID')
elif col_name == 'end_node':
header.append(col_name + ':END_ID')
elif col_name == 'label':
header.append(col_name + ':TYPE')
else:
header.append(col_name)
header = ",".join(header)
save_header(header, dest_folder)
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_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 __init__(self, options):
spark.conf.set("spark.sql.sources.partitionOverwriteMode","dynamic")
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1)
self.options = options
# Setando o nome das tabelas de detalhe aqui, podemos centralizá-las como atributos de AlertaSession
self.options['prescricao_tabela_detalhe'] = self.PRCR_DETALHE_TABLE_NAME
self.options['isps_tabela_aux'] = self.ISPS_AUX_TABLE_NAME
self.options['abr1_tabela_aux'] = self.ABR1_AUX_TABLE_NAME
self.hist_name = lambda x: 'hist_' + x
# Definir o schema no nome da tabela temp evita possíveis conflitos
# entre processos em produção e desenvolvimento
self.temp_name = lambda x: '{0}.temp_{1}'.format(options['schema_alertas'], x)
# Evita que tabela temporária de processos anteriores com erro
# influencie no resultado do processo atual.
for table in self.TABLE_NAMES:
spark.sql("DROP TABLE IF EXISTS {0}".format(self.temp_name(table)))
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_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_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_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_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 write_dataframe(self):
spark.catalog.clearCache()
with Timer():
for table in self.TABLE_NAMES:
spark.sparkContext.setJobGroup("Final tables", table)
print("Escrevendo a tabela {}".format(table))
temp_table_df = spark.table(self.temp_name(table))
table_name = '{0}.{1}'.format(self.options['schema_alertas'], table)
temp_table_df.repartition(3).write.mode("overwrite").saveAsTable(table_name)
hist_table_df = temp_table_df.\
withColumn("dt_calculo", date_format(current_timestamp(), "yyyyMMdd")).\
withColumn("dt_partition", date_format(current_timestamp(), "yyyyMM"))
hist_table_name = '{0}.{1}'.format(self.options['schema_alertas'], self.hist_name(table))
try:
current_hist = spark.sql("""
SELECT * FROM {0} WHERE dt_partition = '{1}' AND dt_calculo <> '{2}'
""".format(
hist_table_name,
datetime.now().strftime('%Y%m'),
datetime.now().strftime('%Y%m%d')
)
)
except:
current_hist = None
if current_hist:
hist_table_df = current_hist.union(hist_table_df)
hist_table_df.write.mode("overwrite").saveAsTable(hist_table_name + "_temp")
hist_table_df = spark.table(hist_table_name + "_temp")
hist_table_df.coalesce(3).write.mode("overwrite").insertInto(hist_table_name, overwrite=True)
spark.sql("drop table {0}".format(hist_table_name + "_temp"))
else:
hist_table_df.coalesce(3).write.partitionBy("dt_partition").saveAsTable(hist_table_name)
spark.sql("drop table {0}".format(self.temp_name(table)))
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_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_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)
from base import spark
from timer import timer
print('Generating People/Company\'s connections: Work')
with timer():
print('Building connections and UUID')
with timer():
tabela = spark.sql("""select
vinculo.uuid,
'TRABALHA' label,
pessoa.uuid start_node,
empresa.uuid end_node,
vinculo.dt_inicio,
vinculo.dt_fim,
vinculo.vinc_ativo
from bases.lc_vinculo_trabalhista vinculo
inner join bases.pessoa_fisica pessoa on
pessoa.num_cpf = vinculo.cpf
inner join bases.lc_cnpj empresa on
empresa.num_cnpj = vinculo.cnpj""")
print('Persisting Work OPE')
with timer():
tabela.write.mode('overwrite').saveAsTable(
'dadossinapse.vinculo_trabalhista_ope')
from base import spark
from opg_utils import uuidsha
from timer import timer
spark.udf.register('uuidsha', uuidsha)
print('Generating father connections')
with timer():
spark.sql("""analyze table bases.pessoa_fisica compute statistics""")
filhotes = spark.sql("""select
uuid idpessoa,
nome_pai,
data_nascimento
from bases.pessoa_fisica
where
data_nascimento > cast('1800-01-01' as timestamp)
and data_nascimento < cast('2019-01-01' as timestamp)
and nome_pai IS NOT NULL and nome_pai != ''""")
filhotes.registerTempTable("filhotes")
papais = spark.sql("""select
uuid idpai,
nome,
data_nascimento + interval 13 years reprodutivo_de,
data_nascimento + interval 50 years reprodutivo_ate
from bases.pessoa_fisica
where
ind_sexo = 'M'
and data_nascimento > cast('1800-01-01' as timestamp)
and data_nascimento < cast('2019-01-01' as timestamp)
and nome IS NOT NULL and nome != ''""")
papais.registerTempTable("papais")
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()
pessoa = spark.table('bases.pessoa_fisica')
multa = spark.table('bases.detran_multa')
veiculo = spark.table('bases.detran_veiculo')
empresa = spark.table('bases.lc_cnpj')
# Removing left padding zeros
multa = multa.withColumn("cpf", expr(
"substring(ident2, 4, length(ident2))"))
veiculo = veiculo.withColumn("cpf", expr(
"substring(cpfcgc, 4, length(cpfcgc))"))
veiculo.registerTempTable("veiculo_cpf")
multa.registerTempTable("multa_cpf")
pessoas_com_carro = spark.sql(
"""select *
from bases.pessoa_fisica
where num_cpf in (select cpf from veiculo_cpf)"""
)
# Merge persons with tickets
pessoa_multa = pessoa.filter('num_cpf is not null').\
withColumnRenamed('uuid', 'start_node').\
join(multa, pessoas_com_carro.num_cpf == multa.cpf).\
select(['start_node', 'uuid']).\
withColumnRenamed('uuid', 'end_node').\
withColumn('label', lit('AUTUADO').cast(StringType())).\
withColumn('uuid', uuidshaudf())
veiculo_multa = veiculo.filter('cpfcgc is not null').\
withColumnRenamed('uuid', 'start_node').\
join(multa, veiculo.placa == multa.pl_vei_inf).\
nl.extend(x.split(" ")[1:])
lc_cpf_null_list.append(" ".join(nl))
lc_cpf_null_list = "\n".join(lc_cpf_null_list)
selected_columns = """
{},
{}
""".format(lc_cpf_columns, rgcivil_columns)
print('Generating Pessoa Table')
with timer():
print('Joining lc_cpf and detran_regcivil')
with timer():
max_dt = spark.sql("""
SELECT nu_rg, MAX(dt_expedicao_carteira) as max_date
FROM bases.detran_regcivil
GROUP BY nu_rg
""")
max_dt.registerTempTable("max_dt")
detran_max_exp_dt = spark.sql("""
SELECT A.* FROM bases.detran_regcivil A
INNER JOIN max_dt
ON A.nu_rg = max_dt.nu_rg AND
(A.dt_expedicao_carteira = max_dt.max_date
OR (A.dt_expedicao_carteira IS NULL AND max_dt.max_date IS NULL))
""")
detran_max_exp_dt = detran_max_exp_dt.withColumn(
'no_cidadao', remove_accents('no_cidadao'))
detran_max_exp_dt = detran_max_exp_dt.withColumn(
'no_paicidadao', remove_accents('no_paicidadao'))
detran_max_exp_dt = detran_max_exp_dt.withColumn(
from base import spark
from timer import timer
print('Generating Peoples OPV')
with timer():
print('Querying people attribute ')
with timer():
tabela = spark.sql("""from bases.pessoa_fisica
select uuid,
num_cpf,
data_nascimento,
nome,
nome_mae,
ind_sexo,
sigla_uf,
num_rg,
nome_rg,
nome_pai,
sensivel,
'Pessoa' label
""")
print('Persisting people OPV')
with timer():
tabela.write.mode('overwrite').saveAsTable(
'dadossinapse.pessoa_fisica_opv')
# print('Generating Companys OPV')
# with timer():
# print('Querying Company\'s atributes')
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 generateAlertas(self):
print('Verificando alertas existentes em {0}'.format(datetime.today()))
with Timer():
spark.table('%s.mcpr_documento' % self.options['schema_exadata']) \
.createOrReplaceTempView("documento")
#spark.catalog.cacheTable("documento")
#spark.sql("from documento").count()
spark.table('%s.mcpr_vista' % self.options['schema_exadata']) \
.createOrReplaceTempView("vista")
# spark.catalog.cacheTable("vista")
# spark.sql("from vista").count()
# Deixar aqui por enquanto, para corrigir mais rapidamente o bug
# Será necessária uma mudança maior de padronização mais à frente
spark.sql("""
SELECT D.*
FROM documento D
LEFT JOIN (
SELECT item_docu_dk
FROM {0}.mcpr_item_movimentacao
JOIN {0}.mcpr_movimentacao ON item_movi_dk = movi_dk
WHERE movi_orga_dk_destino IN (200819, 100500)
) T ON item_docu_dk = docu_dk
LEFT JOIN (
SELECT vist_docu_dk,
CASE
WHEN cod_pct IN (20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 180, 181, 182, 183)
AND stao_tppr_dk IN (
7912, 6548, 6326, 6681, 6678, 6645, 6682, 6680, 6679,
6644, 6668, 6666, 6665, 6669, 6667, 6664, 6655, 6662,
6659, 6658, 6663, 6661, 6660, 6657, 6670, 6676, 6674,
6673, 6677, 6675, 6672, 6018, 6341, 6338, 6019, 6017,
6591, 6339, 6553, 7871, 6343, 6340, 6342, 6021, 6334,
6331, 6022, 6020, 6593, 6332, 7872, 6336, 6333, 6335,
7745, 6346, 6345, 6015, 6016, 6325, 6327, 6328, 6329,
6330, 6337, 6344, 6656, 6671, 7869, 7870, 6324, 7834,
7737, 6350, 6251, 6655, 6326
)
THEN 1
WHEN cod_pct >= 200
AND stao_tppr_dk IN (
6682, 6669, 6018, 6341, 6338, 6019, 6017, 6591, 6339,
7871, 6343, 6340, 6342, 7745, 6346, 7915, 6272, 6253,
6392, 6377, 6378, 6359, 6362, 6361, 6436, 6524, 7737,
7811, 6625, 6718, 7834, 6350
)
THEN 1
ELSE null
END AS is_arquivamento
FROM documento
LEFT JOIN {1}.atualizacao_pj_pacote ON id_orgao = docu_orgi_orga_dk_responsavel
JOIN vista ON vist_docu_dk = docu_dk
JOIN {0}.mcpr_andamento ON vist_dk = pcao_vist_dk
JOIN {0}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk
JOIN {0}.mcpr_tp_andamento ON tppr_dk = stao_tppr_dk
) A ON vist_docu_dk = docu_dk AND is_arquivamento IS NOT NULL
WHERE item_docu_dk IS NULL
AND vist_docu_dk IS NULL
AND docu_fsdc_dk = 1
AND docu_tpst_dk != 11
""".format(self.options['schema_exadata'], self.options['schema_exadata_aux'])).createOrReplaceTempView("documentos_ativos")
spark.catalog.cacheTable("documentos_ativos")
spark.sql("from documentos_ativos").count()
for alerta, (func, table, columns) in self.alerta_list.items():
spark.sparkContext.setJobGroup(alerta, alerta)
self.generateAlerta(alerta, func, table, columns)
self.write_dataframe()
from base import spark
from timer import timer
from opg_utils import uuidsha
uuidshaudf = spark.udf.register('uuidsha', uuidsha)
print('Generating People/Company\'s connections: Contributory')
with timer():
print('Building connections and UUID')
with timer():
tabela = spark.sql("""from dadossinapse.pessoa_fisica_opv pessoa
inner join dadossinapse.pessoa_juridica_opv empresa on
empresa.num_cpf_responsavel = pessoa.num_cpf
select 'SOCIO_RESPONSAVEL' label,
pessoa.uuid start_node,
empresa.uuid end_node""").withColumn('uuid', uuidshaudf())
print('Persisting Contributory OPE')
with timer():
tabela.write.mode('overwrite').saveAsTable(
'dadossinapse.socio_responsavel_ope')
print('Genrating People/Company\'s connection: Partnership')
with timer():
print('Building connections and UUID')
with timer():
tabela = spark.sql("""from bases.lc_socio sociedade
inner join bases.pessoa_fisica pessoa on
pessoa.num_cpf = sociedade.cpf_socio
def check_table_exists(self, schema, table_name):
spark.sql("use %s" % schema)
result_table_check = spark.sql("SHOW TABLES LIKE '%s'" % table_name).count()
return True if result_table_check > 0 else False
from base import spark
from opg_utils import uuidsha
from timer import timer
from pyspark.sql.functions import lit, lower
from pyspark.sql.types import StringType
uuidshaudf = spark.udf.register('uuidshaudf', uuidsha)
with timer():
spark.sql('analyze table bases.orgaos compute statistics')
spark.sql('analyze table bases.lc_cnpj compute statistics')
spark.sql('analyze table bases.documentos compute statistics')
spark.sql('analyze table bases.personagem compute statistics')
spark.sql('analyze table bases.pessoa_fisica compute statistics')
spark.sql('analyze table exadata.mcpr_pessoa_fisica compute statistics')
orgaos = spark.table('bases.orgaos')
empresas = spark.table('bases.lc_cnpj')
documentos = spark.table('bases.documentos')
personagem = spark.table('bases.personagem')
det_pessoa_fisica = spark.table('bases.pessoa_fisica')
mgp_pessoa_fisica = spark.table('exadata.mcpr_pessoa_fisica')
pessoa_fisica_cpf = det_pessoa_fisica.join(
mgp_pessoa_fisica,
det_pessoa_fisica.num_cpf == mgp_pessoa_fisica.pesf_cpf)
pessoa_fisica_rg = det_pessoa_fisica.join(
mgp_pessoa_fisica,
det_pessoa_fisica.num_rg == mgp_pessoa_fisica.pesf_nr_rg)
