nflistBayer: Nflist organization for bayer

## code to prepare `terms_mix_bayer_brasil` dataset goes here

devtools::load_all()

# nflists_bayer_brasil %>% glimpse()
# tipo_documento_bayer_brasil %>% glimpse()
# campanhas_bayer_brasil %>% glimpse()

filtered_docs <- tipo_documento_bayer_brasil %>%
  # filter(tipo_doc_faturamento %in% c("ZBF2", "ZBID", "ZBFR", "ZBFI", "Z6ZF")) %>% #   Vendas
  # filter(tipo_de_doc_2 %in% c("Venda","Venda Barter","Recusa","Devolução")) %>% # Devoluções
  select(tipo_doc_faturamento, tipo_de_doc_2, tipo_doc)

df_doc <- nflists_bayer_brasil %>%
  inner_join(filtered_docs, by = c("tipo_doc_fat" = "tipo_doc_faturamento"))

# df_doc %>%
#   anti_join(campanhas_bayer_brasil %>% mutate(id = id %>% as.character), by = c("id_campanha" = "id")) %>%
#   summarise(suma = sum(montante_encargo_imposto))
#
# df_doc %>%
#   inner_join(campanhas_bayer_brasil %>% mutate(id = id %>% as.character), by = c("id_campanha" = "id")) %>%
#   summarise(suma = sum(montante_encargo_imposto))

df_doc_camp <- df_doc %>%
  left_join(campanhas_bayer_brasil %>% mutate(id = id %>% as.character()), by = c("id_campanha" = "id"))

df_to_model <- df_doc_camp %>%
  select(
    cliente, nome_do_cliente, cidade, regiao, nota_fiscal,
    data_de_emissao, data_de_vencto, month, modalidade, montante_encargo_imposto, org_vendas, tipo_de_doc_2, tipo_doc_fat
  )

terms_mix_bayer_brasil_analysis <- df_to_model %>%
  mutate(
    prazo = lubridate::as_date(data_de_vencto) - lubridate::as_date(data_de_emissao),
    terms_mix = case_when(
      modalidade %>% str_detect("Barter") ~ "barter",
      modalidade %>% str_detect("CRA") ~ "CRA",
      modalidade %>% str_detect("Rural") ~ "credito_rural",
      prazo <= 0 ~ "Cash",
      prazo <= 30 ~ "Short-Term",
      prazo > 30 ~ "Traditional"
    )
  ) %>%
  # filter(terms_mix %>% is.na() %>% `!`()) %>%
  mutate(
    business = "crop_protection",
    legacy = "bayer"
  ) %>%
  mutate(data_de_emissao = lubridate::as_date(data_de_emissao)) %>%
  filter(data_de_emissao <= lubridate::as_date("2020-03-31"), data_de_emissao >= lubridate::as_date("2020-01-01"))

clr <- readxl::read_excel("inst/extdata/data/bayer/clrs/terms_mix_compare.xlsx", sheet = "CLR") %>%
  janitor::clean_names() %>%
  mutate(data_doc = lubridate::as_date(data_doc)) %>%
  filter(data_doc <= lubridate::as_date("2020-03-31"))


clr %>%
  group_by(tipo_2) %>%
  summarise(soma = sum(valor_brl))

to_bring <- terms_mix_bayer_brasil_analysis %>%
  group_by(nome_do_cliente, nota_fiscal, tipo_doc_fat, tipo_de_doc_2, data_de_emissao, data_de_vencto, terms_mix) %>%
  summarise(valor = sum(montante_encargo_imposto))

clr_join <- clr %>%
  rename(nota_fiscal = nº_nota_fiscal) %>%
  mutate(id = dplyr::row_number()) %>%
  left_join(to_bring, by = c("nome_do_cliente", "nota_fiscal")) %>%
  group_by(id) %>%
  add_count() %>%
  filter(nota_fiscal != "0000000000000000") %>%
  mutate(clr_nflist = valor_brl - valor)

clr_join %>%
  writexl::write_xlsx("compare/base_join.xlsx")

clr_join %>%
  filter(n > 1)

clr_join %>%
  group_by(tipo_2, tipo_de_doc_2) %>%
  summarise(clr = sum(valor_brl), nflist = sum(valor)) %>%
  writexl::write_xlsx("compare/totals1.xlsx")


clr_join %>%
  group_by(tipo_2, tipo_de_doc_2, terms_mix) %>%
  summarise(clr = sum(valor_brl), nflist = sum(valor)) %>%
  writexl::write_xlsx("compare/totals2.xlsx")


terms_mix_bayer_brasil_analysis %>%
  group_by(modalidade, tipo_de_doc_2, terms_mix) %>%
  summarise(soma = sum(montante_encargo_imposto)) %>%
  writexl::write_xlsx("compare/nflist_totals.xlsx")

# terms_mix_bayer_brasil_analysis %>%
#   group_by(terms_mix) %>%
#   summarise(soma = sum(montante_encargo_imposto)) %>%
#   writexl::write_xlsx("compare/terms_mix_totals.xlsx")


clr %>%
  group_by(tipo_2) %>%
  summarise(valor_brl_total = sum(valor_brl)) %>%
  writexl::write_xlsx("compare/clr_totals.xlsx")