R/unusual.R

In Chiarini/icrmkpi: KPI formula

#---------------------------------------------------------------------------------#
#' unusual File loading --------------------------------------------------------------
#---------------------------------------------------------------------------------#
#' @param file file
#' @return tibble object
#' @export
#' @examples
#' load_unusual('~/data/icrmkpi/unusual/UMFebbraio2020AllCompanies.csv')
#' load_unusual('~/data/icrmkpi/unusual/UMGennaio2020AllCompanies.csv')
load_unusual <- function(file){


  col_name <- c(
    'plant','plant_name','company','company_name'
    ,'posting_date','user_name','year_week','material'
    ,'material_name','gross_weight','weight_unit'
    ,'material_type'
    ,'material_type_name'
    ,'material_group'
    ,'material_group_name'
    ,'prod_hier'
    ,'prod_hier_name'
    ,'transaction_code'
    ,'movement_type'
    ,'movement_name'
    ,'count'
    ,'qty_base_uom'
    ,'amt_loc_curr'
    ,'amt_eur')

  col_type <- "cccccccccccccccccccccccc"


  data <- read_delim(file,delim = ";", skip = 1, col_names= col_name, col_types = col_type)

  data <- data  %>% filter (posting_date != 'Result')

  data <- data %>% select(company, company_name, posting_date, amount = amt_eur )

  data <- data %>%
    mutate(amount=str_replace_all(amount,"\\.",""),
           amount=str_replace_all(amount,",","."),
           amount=as.numeric(amount))

  data <- data %>%
    mutate(posting_date=dmy(posting_date),
           year = year(posting_date),
           month = month(posting_date))

  data <- data %>%
    select( company, company_name, posting_date, amount,year, month)

  data

}

#---------------------------------------------------------------------------------#
#' imports all IWO files ----------------------------------------------------------
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#' all_data <- load_unusual_all("~/data/icrmkpi/unusual/")
load_unusual_all <- function(data_dir) {
  pattern <-  'UM'
  file_list <- list.files(data_dir,full = TRUE, pattern = pattern)
  all_data <- lapply(file_list, load_unusual) %>% bind_rows()
  all_data
}

#---------------------------------------------------------------------------------#
#' imports fg file ----------------------------------------------------------
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#' fg_data <- load_fg("~/data/icrmkpi/unusual/", 'fg-year.csv')

load_fg <- function(data_dir, file) {

  fg_data <- read_delim(file.path(data_dir, file), delim = ';', col_types = 'cdd'  )
  fg_data <- fg_data %>% mutate(year_1 = year_0+1)
  fg_data

}
#---------------------------------------------------------------------------------#
#' prepare unsual data
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#' fg_data <- load_fg("~/data/icrmkpi/unusual/", 'fg-year.csv')
#' unusual_data <- load_unusual_all("~/data/icrmkpi/unusual/")
#' unusual_data  <- prepare_unusual(data=unusual_data, fg_data = fg_data, .company = 'G046',.year=2020, .month = 4 ,rolling = 13)
prepare_unusual <- function(data, fg_data, .company , .year, .month ,  rolling = 13) {


  # data <- unusual_data
  #.company = 'G046'
# .year = 2020
  #.month = 4
  # rolling = 13

  # filtro
  data <- data %>%
    filter ( company == .company)


  # group  at year, month, company, vendor over all invoices
  data <- data %>%
    group_by(year, month, company, company_name) %>%
    summarise(amount = sum(amount)) %>%
    ungroup()

  # fill
  data <- data %>%
    complete(year = unique(year) ,month = 1:12, nesting(company , company_name),
             fill = list(amount = 0 ))

  data <- data %>%
    inner_join(fg_data, by = c('year'='year_1', 'company')) %>%
    mutate(unusual_movement = round(100*amount/year_amount, 2))

  data <- data %>%
    mutate(year_month = ymd(paste(year, month, '01', sep = '-')))

  # filter on dates
  last_date_in   <- ymd(paste(.year, .month, '01', sep = '-'))
  last_date_db  <- data %>% pull(year_month) %>%  max()

  last_year_month <- min(ymd(c(last_date_in, last_date_in)))

  first_year_month <- last_year_month - months(rolling-1)

  data <- data %>%
    filter ( year_month <= last_year_month , year_month >= first_year_month)


  attr(data, 'company') <- .company

  data <- data %>%
    select(year, month, company, company_name, amount, year_amount  , unusual_movement,  year_month)

  data
}

#---------------------------------------------------------------------------------#
#' plot  um data
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#' fg_data <- load_fg("~/data/icrmkpi/unusual/", 'fg-year.csv')
#' unusual_data <- load_unusual_all("~/data/icrmkpi/unusual/")

#' unusual_data  <- prepare_unusual(data=unusual_data, fg_data = fg_data, .company = 'G046',.year=2020, .month = 4 ,rolling = 13)

#' plot_unusual (data = unusual_data)
plot_unusual <- function(data ) {

  .company <- attr(data, 'company')

  pl <- ggplot(data) +
    geom_bar( aes(year_month, unusual_movement), stat = 'identity', color = 'navyblue', fill = 'navyblue') +
    theme_gray () +
    theme(text = element_text(size=20)) +
    xlab( 'Month') + ylab('Unusual Movements %')  +
    scale_x_date(date_labels="%b %y",date_breaks  ="1 month")+
    ggtitle(data$company_name)

  return(pl)
    }