modflblla: Helpers para el desarrollo de modelos

#' Function obtiene variables de cmr (al mes de observacion)
#'
#' Function que calcula variables dado una tabla de rut periodo y una conexion
#' @param con una conexion odbc
#' @param data data frame con rut y mes_matriz
#' @param mora_sav Calcula morsa y si tiene o no super avance
#' @param porc_uso_hab_pag Calcula porcentaje de uso y habito de pago maximo y bhv
#' @param rene Calcula meses desde la ulta rene desde CMR_RENES
#' @param ant Antigueda CMR desde CMR_FEC_APER_MIN
#' @export
get_cmr_vars <- function(
  con,
  data,
  mora_sav = TRUE,
  porc_uso_hab_pag_bhv = TRUE,
  rene = TRUE,
  ant = TRUE
  ) {

  stopifnot(any(mora_sav, porc_uso_hab_pag_bhv, rene, ant))

  t0 <- Sys.time()

  message("Starting at: ", as.character(t0))

  data <- validate_data_matriz(data)

  name <- "cmr_vars_aux"

  daux <- upload_data(con, data, tblname = name)

  if(mora_sav) {
    message("mora max cmr y super avance")
    # mora max cmr o si tiene super avance CMR
    # ((COD_EST <> 5) and FEC_CAST = 0)

    dkdt <- tbl(con, "K_DATA_SBIF_HISTO") %>%
      filter(COD_EST != "5", FEC_CAST == "0") %>%
      group_by(RUT, ANOMES) %>%
      summarise(
        cmr_mora_max = max(MORA_AC),
        cmr_sdo_super_av = sum(SALDE_SU)
      )

    dcmr1 <- daux %>%
      left_join(dkdt, by = c("rut" = "RUT", "mes_matriz" = "ANOMES")) %>%
      collect() %>%
      modflblla:::clean_df()

    dcmr1 <- dcmr1 %>%
      mutate(cmr_tiene_super_av = ifelse(cmr_sdo_super_av > 0, 1, 0))

  } else {
    dcmr1 <- tibble(rut = double(0), mes_matriz = double(0))
  }

  if(porc_uso_hab_pag_bhv) {

    message("calculando porcentaje de uso y maximo habito de pago y scoringbhv")
    # porcentaje de uso y max min
    # ((COD_EST <> 5) and FEC_CAST = 0) or not (ABIERTA = 0 and SALDEUD = 0)
    dkdt <- tbl(con, "K_DATA_SBIF_HISTO") %>%
      filter((COD_EST != "5" & FEC_CAST == "0") | !(ABIERTA == "0" & SALDEUD == 0)) %>%
      select(ANOMES, RUT, CUENTA, SALDEUD, CUPO, SCORING) %>%
      left_join(
        tbl(con, "K_DATA_SBIF_HABIPAG") %>%
          mutate(
            RUT = sql("CAST(RUT AS FLOAT)"),
            CUENTA =sql("CAST(CUENTA AS FLOAT)")
          ),
        by = c("ANOMES", "RUT", "CUENTA")
      )

    dcmr2 <- daux %>%
      left_join(dkdt, by = c("rut" = "RUT", "mes_matriz" = "ANOMES")) %>%
      collect() %>%
      modflblla:::clean_df()

    # dcmr2 %>%
    #   count(habipag) %>%
    #   arrange(habipag)

    dcmr2 <- dcmr2 %>%
      mutate(
        habipag2 = case_when(
          habipag == "A" ~ 10L,
          habipag == "B" ~ 11L,
          habipag == "C" ~ 12L,
          habipag == "D" ~ 13L,
          habipag == "E" ~ 14L,
          habipag == ""  ~ NA_integer_,
          habipag == "0" ~ NA_integer_,
          is.na(habipag) ~ NA_integer_,
          TRUE           ~ as.integer(habipag)
        )
      )

    message("habito de pago vacio o 0 se junta con los que no tiene")
    dcmr2 %>% count(habipag, habipag2)
    dcmr2 %>% count(habipag2)

    dcmr2 <- dcmr2 %>%
      group_by(rut, mes_matriz) %>%
      summarise(
        cmr_n = n(),
        cmr_saldo = sum(saldeud),
        cmr_cupo = sum(cupo),
        cmr_max_habipag = max(habipag2, na.rm = TRUE),
        cmr_min_behavior = min(scoring, na.rm = TRUE)
      ) %>%
      mutate(
        cmr_max_habipag = ifelse(is.infinite(cmr_max_habipag), NA, cmr_max_habipag),
        cmr_porc_uso = cmr_saldo/cmr_cupo
      ) %>%
      ungroup()
  } else {
    dcmr2 <- tibble(rut = double(0), mes_matriz = double(0))
  }

  if(rene) {
    message("calculando RENES")

    dcmr3 <- left_join(daux, tbl(con, "CMR_RENES"), by = "rut") %>%
      filter(mes_matriz >= fecreneg) %>%
      collect()

    dcmr3 <- dcmr3 %>%
      select(rut, mes_matriz, fecreneg)

    dcmr3 <- dcmr3 %>%
      mutate(fecreneg = as.numeric(fecreneg)) %>%
      group_by(rut, mes_matriz) %>%
      summarise(fecreneg = max(fecreneg)) %>%
      mutate(
        year.x = floor(mes_matriz/100),
        month.x = mes_matriz - 100 * year.x,
        year.y = floor(fecreneg/100),
        month.y = fecreneg - 100 * year.y,
        diff = 12 * (year.x - year.y) + (month.x - month.y)
      ) %>%
      ungroup() %>%
      select(rut, mes_matriz, cmr_meses_ult_ren = diff)

  } else {
    dcmr3 <- tibble(rut = double(0), mes_matriz = double(0))
  }

  if(ant) {
    message("calculando ANTIGUEDAD")

    dcmr4 <- left_join(daux, tbl(con, "CMR_FEC_APER_MIN"), by = "rut") %>%
      collect()

    dcmr4 <- dcmr4 %>%
      mutate(FEC_APER_MIN = floor(as.numeric(FEC_APER_MIN)/100)) %>%
      select(rut, mes_matriz, fec_aper_min = FEC_APER_MIN)

    dcmr4 <- dcmr4 %>%
      mutate(fec_aper_min = as.numeric(fec_aper_min)) %>%
      group_by(rut, mes_matriz) %>%
      summarise(fec_aper_min = max(fec_aper_min)) %>%
      mutate(
        year.x = floor(mes_matriz/100),
        month.x = mes_matriz - 100 * year.x,
        year.y = floor(fec_aper_min/100),
        month.y = fec_aper_min - 100 * year.y,
        diff = 12 * (year.x - year.y) + (month.x - month.y)
      ) %>%
      ungroup() %>%
      select(rut, mes_matriz, cmr_antiguedad = diff)

  } else {
    dcmr4 <- tibble(rut = double(0), mes_matriz = double(0))
  }


  dcmr <- list(dcmr1, dcmr2, dcmr3, dcmr4) %>%
    reduce(full_join, by = c("rut", "mes_matriz"))

  rm(dcmr1, dcmr2, dcmr3, dcmr4)

  message("Ending at: ", as.character(Sys.time()))
  print(difftime(Sys.time(), t0))

  dcmr

}

#' Function que calcula tiempo desde la ultima cmr rene
#'
#' Function que calcula variables dado una tabla de rut periodo y una conexion
#' @param con una conexion odbc
#' @param data data frame con rut y mes_matriz
#' @param periodos vector numerico con periodos a calcular tendencias, resumentes, etc
#' @export
get_cmr_rene_vars <- function(con, data) {

  t0 <- Sys.time()

  message("Starting at: ", as.character(t0))

  data <- validate_data_matriz(data)

  name <- create_name(data, prefix = "cmr_rene")

  drut <- upload_data(con, data, tblname = name)

  dcmrr <- tbl(con, "CMR_RENES")

  daux <- left_join(drut, dcmrr, by = "rut") %>%
    filter(mes_matriz >= fecreneg) %>%
    collect()

  daux <- daux %>%
    mutate(fecreneg = as.numeric(fecreneg)) %>%
    group_by(rut, id, mes_matriz) %>%
    summarise(fecreneg = max(fecreneg)) %>%
    mutate(
      year.x = floor(mes_matriz/100),
      month.x = mes_matriz - 100 * year.x,
      year.y = floor(fecreneg/100),
      month.y = fecreneg - 100 * year.y,
      diff = 12 * (year.x - year.y) + (month.x - month.y)
    ) %>%
    ungroup() %>%
    select(id, rut, mes_matriz, cmr_meses_ult_ren = diff)

  message("Ending at: ", as.character(Sys.time()))
  print(difftime(Sys.time(), t0))

  daux

}