R/fis_write_datafile.R

In mvbloois/faiR: Collection Of Farminform Functions

#' Write data files for PHAMRO
#'
#' Presupposes relevant network connections
#'
#' @param month character variable
#'
#' @return files
#'
#' @examples
#' ## fis_write_datafile(month = "201908")
#'
#' @importFrom rlang .data
#' @export
fis_write_datafile <- function(month = "20210201") {

  file <- paste0("PH" , stringr::str_sub(month, 1, 6), "_2021.TXT")

  sql <- glue::glue("SELECT CASE WHEN afn.STEEKPROEF = 'J' THEN CAST(RIGHT(afn.AGB_CODE,6) AS INT) \
      WHEN afn.STEEKPROEF = 'N' THEN 0 \
      ELSE 999999 END agb \
  ,   CAST(codes.knmp AS VARCHAR(8)) AS knmp \
  ,   CONVERT(VARCHAR(6), dat.DATUM_AFZET, 112) AS periode \
  ,   SUM(dat.aantal) AS aantal \
  FROM fiaprod.VERKOOPREGEL AS dat \
  INNER JOIN fiaprod.AFNEMER AS afn ON dat.AFNEMER_ID = afn.AFNEMER_ID \
  INNER JOIN ( \
              SELECT DISTINCT cod.beganegrondcode, cod.fcode \
                      , CASE WHEN cod.knmp = '' THEN cte.ref_knmp ELSE cod.knmp END knmp \
                      FROM FI_GEBRUIKERS.Artikelcodes AS cod \
  LEFT JOIN FI_GEBRUIKERS.Report_PharmoRefknmp AS cte ON cod.fcode = cte.fcode \
             ) codes ON codes.beganegrondcode = dat.BEGANEGRONDCODE \
  INNER JOIN fiaprod.BEGANE_GROND_CLUSTER AS clu ON clu.BEGANEGRONDCODE = dat.BEGANEGRONDCODE \
  WHERE dat.DATUM_AFZET = CONVERT(datetime, '{month}', 112) \
  AND dat.OUTLET IN ('31','32','33','34','35','36','37','38','39') \
  AND clu.CLUSTER_DEFINITIE = 'PHARMO' \
  AND codes.knmp != '' \
  GROUP BY  CASE WHEN afn.STEEKPROEF = 'J' THEN CAST(RIGHT(afn.AGB_CODE,6) AS INT) \
        WHEN afn.STEEKPROEF = 'N' THEN 0 \
        ELSE 999999 END \
  ,codes.knmp \
  ,dat.DATUM_AFZET \
  UNION \
  SELECT agb, knmp, periode, aantal FROM FI_GEBRUIKERS.pharmo_tnfalfa \
  WHERE periode + '01' = '{month}' \
  UNION \
  SELECT agb, knmp, periode, aantal FROM FI_GEBRUIKERS.pharmo_l01l02 \
  WHERE periode + '01' = '{month}' ")

  fiaprod <- faiR::connect_usoft("FIAPROD")
  DBI::dbGetQuery(fiaprod, sql) %>%
    dplyr::mutate(aantal = round(.data$aantal, digits = 5)) %>%
    readr::write_csv2(here::here("data", file), col_names = FALSE,
                      eol = "\r\n")
}