R/master.R

In Chiarini/icrmkpi: KPI formula

#---------------------------------------------------------------------------------#
#' zero  --------------------------------------------------------------------------
#---------------------------------------------------------------------------------#
#' @return 0
#' @export
#' @importFrom stringr str_replace_all str_sub str_split
#' @importFrom dplyr %>% select mutate filter bind_rows semi_join anti_join group_by summarise ungroup left_join pull
#' @importFrom readr read_delim
#' @importFrom lubridate dmy ymd year month days_in_month
#' @importFrom ggplot2  ggplot geom_line xlab ylab theme_minimal aes ggtitle theme_gray theme element_text scale_x_continuous scale_x_date geom_bar
#' @importFrom tidyr complete nesting
#' @examples
#' zero()
zero <- function(){0}

#---------------------------------------------------------------------------------#
#' Formattazione stringhe --------------------------------------------------------
#---------------------------------------------------------------------------------#
#' @param x una stringa
#' @param ... altre stringhe
#' @return una stringa formattata
#' @export
#' @examples
#' prettify_string(x="Da niele")
#' prettify_string(x=c("Da niele","and$rea"))
#'
prettify_string <- function(x, ...){

  # custom replace
  if(!missing(...)){
    args <- list(...)
    for(a in names(args)) x <- str_replace_all(string = x, pattern = a, replacement = args[[a]])
  }

  # remove punctuation
  x <- str_replace_all(x, "[[:punct:]]", "_")

  # remove spaces and tabs
  x <- str_replace_all(x, "[[:space:]]", "_")

  # remove remove special characters
  x <- str_replace_all(x, "[^[:alnum:]]", "_")
  #
  # remove extra _
  x <- str_replace_all(x, "_+", "_")

  # remove accents and weird letters
  x <- iconv(x, from = 'UTF-8', to = 'ASCII//TRANSLIT')

  # everything to lower
  x <- tolower(x)

  x
}

#---------------------------------------------------------------------------------#
#' iwo File loading --------------------------------------------------------------
#---------------------------------------------------------------------------------#
#' @param file file
#' @return tibble object
#' @export
#' @examples
#' load_iwo("~/data/icrmkpi/iwo/IWODicembre2019AllCompanies.csv")
load_iwo <- function(file){

  col_name <- c("company","company_name","chart_of_accounts","chart_of_accounts_name","business_area","business_area_name","vendor","vendor_name",
                "document_type","document_type_des","invoice_number","sap_document_no","invoice_date","posting_date","cost_center","cost_center_des",
                "g_l_account","g_l_account_des","payment_terms","payment_terms_des","payment_date","amount","amount_local")

  col_type <- "ccccccccccccccccccccccc"

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


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

  data <- data %>%
    mutate(company=str_sub(company,4),
           company_input = paste(company, company_name, sep = '-'),
           g_l_account=str_sub(g_l_account,4),
           mastro = str_sub(g_l_account,1,4))

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

  data <- data %>%
    select(posting_date,year, month, company,company_name,vendor_name,g_l_account,mastro,amount, company_input)

  data

}

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

#---------------------------------------------------------------------------------#
#' imports Niagara file ----------------------------------------------------------
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#' niagara_data <- load_niagara("~/data/icrmkpi/iwo/", 'niagara.csv')
load_niagara <- function(data_dir, file) {

  niagara_data <- read_delim(file.path(data_dir, file) , delim = ';', col_type = 'cdi') %>%
    filter ( company != 0) %>%
    mutate(order_amount = order_amount*1000)

  niagara_data

}

#---------------------------------------------------------------------------------#
#' imports exemption file ---------------------------------------------------------
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#' exemption_data <- load_exemption("~/data/icrmkpi/iwo/", 'exemption.csv')
load_exemption <- function(data_dir, file){

  exemption_data <- read_delim(file.path(data_dir, file), delim = ';', col_types = 'cc')
  exemption_data
}


#---------------------------------------------------------------------------------#
#' imports company-region file ----------------------------------------------------
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#' company_region_data <- load_company_region ("~/data/icrmkpi/iwo/", 'company-region.csv')
load_company_region <- function(data_dir, file) {

  company_region_data <- read_delim(file.path(data_dir, file) , delim = ';',  col_type = 'ccc') %>%
    mutate(company = str_sub(company, 4) ,
           company_input = paste(company, company_name, sep = '-'))

  company_region_data

}

#---------------------------------------------------------------------------------#
#' prepare iwo data
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#' niagara_data <- load_niagara("~/data/icrmkpi/iwo/", 'niagara.csv')
#' exemption_data <- load_exemption("~/data/icrmkpi/iwo/", 'exemption.csv')
#' iwo_data <- load_iwo_all("~/data/icrmkpi/iwo/")
#' iwo_data <- prepare_iwo (data = iwo_data, exemption = T, min_amount = 0, .company = 'D002', .year = 2019, .month = 12 ,
#'           exemption_data=exemption_data, niagara_data=niagara_data, rolling = 13)
prepare_iwo <- function(data, exemption, min_amount, .company, .year, .month , exemption_data, niagara_data, rolling = 13) {


  # check .year & .month
  has_year <- '.year' %in% ls()
  has_month <- '.month' %in% ls()

  # filter exemption mastro if TRUE
  if ( exemption ) {
    data <- data %>%
      anti_join(exemption_data, by = 'mastro')
  }

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



  # build data of vendors by year , company that invoiced over min_amount
  large_vendor_data  <- data %>%
    group_by(year , company, vendor_name) %>%
    summarise(amount = mean(amount)) %>%
    ungroup() %>%
    filter ( amount >= min_amount)

  # filter on large verndors only
  data <- data %>%
    semi_join(large_vendor_data, by = c('year', 'company', 'vendor_name'))

  # aggrefgate at copmpany level
  data <- data  %>%
    group_by (  year, month ,company, company_name ) %>%
    summarise(amount = sum(amount)) %>%
    ungroup()

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




  # join with Niagara ordered by company at year-1
  data <- data %>%
    left_join(mutate(niagara_data, year = year+1) , by = c('year', 'company'))


  # compute owi kpi
  data <- data %>%
    mutate(iwo = round(100*(amount/order_amount),2))

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

  # set dates
  data <- data %>%
    mutate(year_month = ymd(paste(as.character(year) , as.character(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


  return(data)
}

#---------------------------------------------------------------------------------#
#' plot  iwo data
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#' plot_iwo (data = iwo_data)
plot_iwo <- function(data ) {

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

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

  return(pl)


}