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()