pacman::p_load(readxl, ggplot2, dplyr, psych, tidyr, stringr, plotly, readr,h2o, rstudioapi, randomForest, tibble, psych, padr, fpp2, lubridate, DT, tidyverse, xlsx, janitor,stringi, openxlsx, foreach)
library(dplyr)
li_bases <- read_excel("~/Documents/@/CasaDelOptico2023/Analisis/data/bases/fabricaciones_bases_16-08-22_16-12-22.xlsx", sheet = 1, col_names = FALSE)
#--Adhoc--assign column names
names(li_bases) <- li_bases[2,]
#--Adhoc--remove rows
li_bases <- li_bases[-c(1,2),]
#Read RDS
#Add Stock Min & Max
#stock.min at 28-10-21
ventas_All_Estab <- readRDS("~/Documents/@/CasaDelOptico2023/Analisis/scripts/loop_Ventas_detalle_allDistrib_19_04-19_08_v1.rds")
#READ RDS by parts
#Assign VAR BY Estab's file name
#Olivos
var_li_medidas_mono <- "listado_medidas_stock_Olivos_Mono_v1.rds"
var_li_medidas_multi <- "listado_medidas_stock_Olivos_Multi_v1.rds"
#read Rds
listado_medidas_mono = readRDS(paste0("~/Documents/@/CasaDelOptico2023/Analisis/scripts/listado_medidas/", var_li_medidas_mono, sep =""))
listado_medidas_multi = readRDS(paste0("~/Documents/@/CasaDelOptico2023/Analisis/scripts/listado_medidas/", var_li_medidas_multi, sep =""))
### Data transf:
########################Convert to Mayus: Cod. Artic. ########################
tb_Kardex_distinct$CODIGO_ARTICULO <- toupper(tb_Kardex_distinct$CODIGO_ARTICULO)
########################Remove Sufix from Establecimientos ########################
tb_Kardex_distinct_noSufix <- tb_Kardex_distinct %>% mutate(CODIGO_ARTICULO=trimws(gsub("02-+[0-9]{2}", "", CODIGO_ARTICULO)))
########################Fix the problem with CIL ########################
tb_Kardex_distinct_noSufix_cil <- tb_Kardex_distinct_noSufix %>%
mutate(MEDIDA= if_else(FORMA == "CIL" , trimws(gsub("[A-Z0-9]{6}", "",tb_Kardex_distinct_noSufix$CODIGO_ARTICULO)), tb_Kardex_distinct_noSufix$MEDIDA))
#--Mono
#SEPARATE CODIGO_ARTICULO to Material_Code & Medida
pre_TL_long_tb_medidas_wCod_mono_Material_re <- long_tb_medidas_wCod_mono_Material_re %>% separate( col = COD_ARTICULO, into = c("MATERIAL_COD","COD_temp"), sep=3, remove = FALSE)
#Delete temp Col
pre_TL_long_tb_medidas_wCod_mono_Material_re$COD_temp <- NULL
#--Multi
#SEPARATE CODIGO_ARTICULO to Material_Code & Medida (temp)
pre_TL_long_tb_medidas_wCod_multi_Material_re <- long_tb_medidas_wCod_multi_Material_re %>% separate( col = COD_ARTICULO, into = c("MATERIAL_COD","COD_temp"), sep=6, remove = FALSE)
#Delete temp Col
pre_TL_long_tb_medidas_wCod_multi_Material_re$COD_temp <- NULL
#--Mono
#SEPARATE CODIGO_ARTICULO to Material_Code & Medida
pre_TL_long_tb_medidas_wCod_mono_Material_re <- long_tb_medidas_wCod_mono_Material_re %>% separate( col = COD_ARTICULO, into = c("MATERIAL_COD","COD_temp"), sep=3, remove = FALSE)
#Delete temp Col
pre_TL_long_tb_medidas_wCod_mono_Material_re$COD_temp <- NULL
#--Multi
#SEPARATE CODIGO_ARTICULO to Material_Code & Medida (temp)
pre_TL_long_tb_medidas_wCod_multi_Material_re <- long_tb_medidas_wCod_multi_Material_re %>% separate( col = COD_ARTICULO, into = c("MATERIAL_COD","COD_temp"), sep=6, remove = FALSE)
#Delete temp Col
pre_TL_long_tb_medidas_wCod_multi_Material_re$COD_temp <- NULL
########################Change column names########################
names(min_max_new_391)[names(min_max_new_391) == "StockMin_Trimestral"] <- "StockMin_New"
names(min_max_new_391)[names(min_max_new_391) == "StockMax_Trimestral"] <- "StockMax_New"
########################List files / Path dir ########################
path_dir <-"~/Documents/@/CasaDelOptico2023/Analisis/data/ventas-fab/"
names_dtll <- sort(list.files(path_dir, pattern = "det|fab*.xls"))
names_vtas_mov <- sort(list.files(path_dir, pattern = "mov|fab*.xls"))
names_cab <- sort(list.files(path_dir, pattern = "cab|fab*.xls"))
###############################################################################
# FUNCTION 3: pre_Cab_doc #
###############################################################################
f3 <- function(i){
#import
pre_Cab_doc = read_excel(paste(path_dir, i, sep = ""), sheet=1, col_names = FALSE)
#--assign column names
names(pre_Cab_doc) <- pre_Cab_doc[2,]
#--remove rows
pre_Cab_doc <- pre_Cab_doc[-c(1,2),]
########################Column formats########################
##Data types
#--ESTABLECIMIENTO - as character
pre_Cab_doc$ESTABLECIMIENTO <- as.character(pre_Cab_doc$ESTABLECIMIENTO)
#--Nº ORDEN - as character
pre_Cab_doc$`Nº ORDEN` <- as.character(pre_Cab_doc$`Nº ORDEN`)
#--COD. CLIENTE - as character
pre_Cab_doc$`COD. CLIENTE` <- as.character(pre_Cab_doc$`COD. CLIENTE`)
#--CLIENTE - as character
pre_Cab_doc$CLIENTE <- as.character(pre_Cab_doc$CLIENTE)
#--FECHA - as date
pre_Cab_doc$FECHA <- as.Date(pre_Cab_doc$FECHA, format = "%d/%m/%Y")
#--TOTAL - as numeric
pre_Cab_doc$TOTAL <- as.numeric(pre_Cab_doc$TOTAL)
#--ADELANTO - as numeric
pre_Cab_doc$ADELANTO <- as.numeric(pre_Cab_doc$ADELANTO)
#--SALDO - as numeric
pre_Cab_doc$SALDO <- as.numeric(pre_Cab_doc$SALDO)
#--Nº CPE - as character
pre_Cab_doc$`Nº CPE` <- as.character(pre_Cab_doc$`Nº CPE`)
#--ESTADO - as factor
pre_Cab_doc$ESTADO <- as.character(pre_Cab_doc$ESTADO)
########################Change column names########################
#--Nº GENERADO
names(pre_Cab_doc)[names(pre_Cab_doc) == "Nº ORDEN"] <- "N_ORDEN"
#--COD. CLIENTE
names(pre_Cab_doc)[names(pre_Cab_doc) == "COD. CLIENTE"] <- "COD_CLIENTE"
#--Nº CPE
names(pre_Cab_doc)[names(pre_Cab_doc) == "Nº CPE"] <- "N_CPE"
#To prevent differences with the TOTAL from Detalle_Fab!
names(pre_Cab_doc)[names(pre_Cab_doc) == "TOTAL"] <- "TOTAL MOV."
#prevent duplicates based on ESTADO
pre_Cab_doc <- pre_Cab_doc %>% mutate(ESTADO=recode(ESTADO,
"ENTREGADA"="AFIRMATIVO",
"PROCESADA"="AFIRMATIVO",
"REGISTRADA"="AFIRMATIVO"
))
#Return [dont forget]
return(pre_Cab_doc)
}
#Loop
#single
list_cab_doc <- lapply(names_cab, f3)
#list_ventas_dtll[[1]]
#Convert each list #list_ventas_mov & #list_ventas_dtll to tables
#--ventas_dtll
tb_bind_ventas_dtll <- bind_rows(list_ventas_dtll)
#tb_bind_ventas_dtll$NameFile <- NULL
#--ventas_mov
tb_bind_ventas_mov <- bind_rows(list_ventas_mov)
#tb_bind_ventas_mov$NameFile <- NULL
#--cab_doc
tb_bind_cab_doc <- bind_rows(list_cab_doc)