library(RSelenium)
library(dplyr)
library(purrr)
library(filesstrings)
# DEFINING FUNCTION TO GENERATE CLEAN LOG MESSAGES ----
log_info = function(message){
cat(paste0(
" ", "\n",
"########", "\n",
"*---",
Sys.time(),
"---*",
"\n",
message, "\n",
"########", "\n",
" "))
}
# DEFINING THE FUNCTION TO EXECUTE THE THEMATIC/PERIOD XLS FILES ASK, CHECK AND DOWNLOAD ----
#' @export
exec_report = function(thematic, period, temp_dir, root_dest_dir, selenium_host_name, selenium_port, user, pwd){
log_info(paste0("Starting process to retrieve report for ", thematic$name))
# SET THE FINAL DESTINATION FOLDER WHERE RENAMED REPORT FILE WILL BE STORED
thematic$dest_dir = paste0(root_dest_dir, thematic$name, "/")
# PRECONFIGURATION OF DOWNLOAD FOLDERS----
create_thematic_dir(thematic$dest_dir, thematic$name)
# START BROWSING WITH SELENIUM SERVER DRIVER ----
remDr = driver_initiate(selenium_host_name, selenium_port)
driver_launch(remDr)
remDr$setImplicitWaitTimeout(5000) # we tell the driver to wait 5 sec for element to load before sending timeout
go_to_tilroy(remDr, user, pwd)
go_to_centrale_new_versato(remDr)
go_to_databeheer(remDr, user, pwd)
ask_report(remDr, thematic, period)
check_report(remDr, thematic, period, temp_dir)
}
# GET TABLE LINE DATA FUNCTION ----
get_table_line_data = function(remDr, lineNum){
get_table_column_data = function(remDr,colNum,lineNum){
css_selector = paste0(
".transaction-tbl>tbody>tr:nth-child(",
lineNum, ")",
">td:nth-child(", colNum, ")")
if(colNum == 6){
css_selector = paste0(css_selector,">a")
}
column_data = remDr$findElement(using = "css", css_selector)
while (is.null(column_data)){
Sys.sleep(1)
column_data = remDr$findElement(using = "css", css_selector)
}
if(colNum != 6){
column_data = column_data$getElementText()[[1]]
}
if(colNum == 6){
css_selector = paste0(css_selector,">a")
column_data = column_data$getElementAttribute("href")[[1]]
}
return(column_data)
}
line_data = data.frame(
lineNum = lineNum,
date = get_table_column_data(remDr, 2, 1),
user = get_table_column_data(remDr, 3, 1),
status = get_table_column_data(remDr, 5, 1),
fileURL = "no_file"
)
if(line_data$status == "Gemaakt"){
line_data$fileURL = get_table_column_data(remDr, 6, 1)
}
return(line_data)
}
# CHECK TABLE LINE VALUES FUNTIONS ----
check_table_line_values = function(remDr, line_data){
log_info(paste0("Checking last line values"))
message(line_data[,2:4])
time_current = Sys.time()
time_line_data = strptime(line_data$date, format="%d/%m/%Y %H:%M")
# if difference not greater than 12 hours, ok !
time_diff = as.numeric(time_current - time_line_data)
if(time_diff < 12 * 60){ # to express in minutes
good_time_value = TRUE
}else{
good_time_value = FALSE
}
if(line_data$user == "POK POK"){
good_user_value = TRUE
}else{
good_user_value = FALSE
}
if(line_data$status == "Gemaakt"){
good_status_value = TRUE
}else{
good_status_value = FALSE
}
if(stringr::str_ends(line_data$fileURL, ".xlsx")){
good_fileName_value = TRUE
}else{
good_fileName_value = FALSE
}
if(isTRUE(
good_time_value && good_user_value && good_status_value && good_fileName_value
)){
check = TRUE
}else{
check = FALSE
}
return(check)
}
# ASK REPORT FUNCTION ----
ask_report = function(remDr, thematic, period){
log_info(paste0("Starting process to ask report for ", thematic$name))
go_to_rapportering(remDr)
go_to_menu(remDr, thematic)
click_rapport_selectie(remDr)
period = paste0("period_", period)
if(!is.null(thematic[[period]]$from) && !is.null(thematic[[period]]$to)){
set_period_dates(remDr, thematic[[period]]$from, thematic[[period]]$to)
}
if(isTRUE(thematic$steps$all_shops)){
select_all_shops(remDr)
}
if(isTRUE(thematic$steps$leverancier)){
select_leverancier(remDr)
}
if(isTRUE(thematic$steps$alle_producten)){
select_alle_producten(remDr)
}
click_ask_report(remDr)
}
# CHECK REPORT FUNCTION ----
check_report = function(remDr, thematic, period, temp_dir){
log_info(paste0("Starting process to check report status for ", thematic$name))
go_to_rapportering(remDr)
go_to_menu(remDr, thematic)
click_aangevraagd_rapport(remDr)
line_1_values = get_table_line_data(remDr, 1)
line_1_check = check_table_line_values(remDr, line_1_values)
if(isTRUE(line_1_check)){
log_info(paste0("Report is ready for ", thematic$name))
download_latest_excel(remDr, temp_dir, thematic$dest_dir, thematic$name, period, line_1_values$fileURL, line_1_values$lineNum)
}
else{
log_info(paste0("Report not yet ready for ", thematic$name, ". Will attempt again in 30 sec "))
Sys.sleep(0.5*60) # wait 1/2 minute
check_report(remDr, thematic, period, temp_dir)
}
}
# GO TO THEMATIC PAGE FUNCTION ----
go_to_thematic_page = function(remDr, thematic){
root_url = "https://ve2945.tilroy.com"
thematic_url = paste0(root_url, thematic$submenu_URL)
log_info(paste0("Navigating to ", thematic$menu, " page"))
thematic_page = remDr$navigate(thematic_url)
}
# GO TO THEMATIC MENU/SUBMENU FUNCTION ----
go_to_menu = function(remDr, thematic){
menu_xpath_arg = paste0("//*[contains(text(), ", "'", thematic$menu, "')]")
menu = remDr$findElement(using = "xpath", menu_xpath_arg)
while(is.null(menu)){
Sys.sleep(1)
menu = remDr$findElement(using = "xpath", menu_xpath_arg)
}
menu$clickElement()
if(!is.null(thematic$submenu_URL)){
go_to_thematic_page(remDr, thematic)
}
}
# GET WIPED DATEPICKER FUNCTION ----
get_wiped_datepicker = function(remDr, to_or_from){
if(to_or_from == "from"){
datepicker = remDr$findElement(using = "xpath", "//input[@data-url-parameter='dateFrom']")
while (is.null(datepicker)){
Sys.sleep(1)
datepicker = remDr$findElement(using = "xpath", "//input[@data-url-parameter='dateFrom']")
}
datepicker$clearElement()
}else{
datepicker = remDr$findElement(using = "xpath", "//input[@data-url-parameter='dateTo']")
while (is.null(datepicker)){
Sys.sleep(1)
datepicker = remDr$findElement(using = "xpath", "//input[@data-url-parameter='dateTo']")
}
datepicker$clearElement()
}
return(datepicker)
}
# SET WIPED DATEPICKER DATES FUNCTION ----
set_period_dates = function(remDr, from, to){
log_info(paste0("Entering dates for report"))
get_wiped_datepicker(remDr, "from")$sendKeysToElement(list(as.character(get_period_date(from)), key = "enter"))
get_wiped_datepicker(remDr, "to")$sendKeysToElement(list(as.character(get_period_date(to)), key = "enter"))
click_rapport_selectie(remDr) # this is a hack for the date pickers to work
}
# CLICK ASK REPORT FUNCTION ----
click_ask_report = function(remDr){
button_vraag_rapport = remDr$findElement(using = "xpath", "//*[contains(text(), 'Vraag rapport aan')]")
while(is.null(button_vraag_rapport)){
Sys.sleep(1)
button_vraag_rapport = remDr$findElement(using = "xpath", "//*[contains(text(), 'Vraag rapport aan')]")
}
log_info(paste0("Clicking vraag rapport"))
button_vraag_rapport$clickElement()
}
# GET A TIMESTAMP FOR FILENAME FUNCTION ----
get_timestamp = function(as.time = TRUE ){
timestamp = Sys.time()
if(!isTRUE(as.time)){
timestamp = as.character(timestamp)
timestamp = stringr::str_replace_all(timestamp, "[[:punct:]]", " ")
timestamp = stringr::str_replace_all(timestamp, " ", "_")
}
}
get_time_asked = function(remDr){
time_asked = remDr$findElement(using = "css", ".transaction-tbl>tbody>tr>td:nth-child(2)")$getElementText()
while(is.null(time_asked)){
Sys.sleep(1)
time_asked = remDr$findElement(using = "css", ".transaction-tbl>tbody>tr>td:nth-child(2)")$getElementText()
}
time_asked = strptime(time_asked, format="%d/%m/%Y %H:%M")
return(time_asked)
}
# GET THEMATICS NAMES FUNCTION ----
# get_thematics_name = function(){
# thematics = list(
# Aankooporders = "Aankooporders",
# DailySales = "DailySales",
# AanvulRapportPerWinkel = "AanvulRapportPerWinkel",
# OpenOrdersRapport = "OpenOrdersRapport"
# )
# }
# CLICK EXCEL ICON FUNCTION -----
click_excel_icon = function(remDr){
last_report_xls_icon = remDr$findElement(using = "css", ".transaction-tbl>tbody>tr>td:nth-child(6)>a")
while (is.null(last_report_xls_icon)){
print("Waiting for last report xls icon to show up...")
Sys.sleep(1)
last_report_xls_icon = remDr$findElement(using = "css", ".transaction-tbl>tbody>tr>td:nth-child(6)>a")
}
last_report_xls_icon$clickElement()
}
# CLICK REFRESH FUNCTION ----
click_refresh = function(remDr){
button_refresh = remDr$findElement(using = "xpath", "//*[contains(text(), 'Refresh')]")
while (is.null(button_refresh)){
print("Waiting for refresh button to show up...")
Sys.sleep(1)
button_refresh = remDr$findElement(using = "xpath", "//*[contains(text(), 'Refresh')]")
}
button_refresh$clickElement()
}
# CLICK ALL SHOPS FUNCTION ----
select_all_shops = function(remDr){
all_shops_button = remDr$findElement("id", "selectAll")
while (is.null(all_shops_button)){
print("Waiting for all_shops_button button to show up...")
Sys.sleep(1)
all_shops_button = remDr$findElement("id", "selectAll")
}
all_shops_button$clickElement()
}
# CLICK ALL PRODUCTS FUNCTION ----
select_alle_producten = function(remDr){
alle_producten = remDr$findElement(using = "id", value = "selectionMethodAll")
while (is.null(alle_producten)){
print("Waiting for alle producten button to show up...")
Sys.sleep(1)
alle_producten = remDr$findElement(using = "id", value = "selectionMethodAll")
}
alle_producten$clickElement()
}
# CLICK RETOUR LEVERANCIERS
select_leverancier = function(remDr){
retour_leverancier_stockherstelling = remDr$findElement(using = "id", value = "2523-selection")
#retour_leverancier_stockherstelling = remDr$findElement(using = "xpath", "//*[contains(text(), 'Retour leverancier stockherstelling')]")
while(is.null(retour_leverancier_stockherstelling)){
Sys.sleep(1)
retour_leverancier_stockherstelling = remDr$findElement(using = "id", value = "2523-selection")
}
retour_leverancier_stockherstelling$clickElement()
}
# DOWNLOAD LATEST EXCEL FILE FUNCTION ----
# Note that the files are downloaded to the server default download directory.
# https://stackoverflow.com/questions/35504731/specify-download-folder-in-rselenium
# https://stackoverflow.com/questions/42293193/rselenium-on-docker-where-are-files-downloaded
download_latest_excel = function(remDr, temp_dir, dest_dir, name, period, fileURL, lineNum){
log_info(paste0("Starting download of report for ", name, " ", period))
remDr$navigate(fileURL)
fileName = strsplit(as.character(fileURL), "/")[[1]]
fileName = tail(fileName,1)
oldFile = paste0(temp_dir, fileName) # see docker compose file and execute.R
oldFile_part = paste0(oldFile, ".part")
while(file.exists(oldFile_part)){
Sys.sleep(1)
oldFile_part = paste0(oldFile, ".part")
}
log_info(paste0("Download is completed ", name, " ", period))
newFile = paste0(dest_dir, "NV_", name, "_", "period_", period, "_", get_timestamp(as.time = FALSE), ".xlsx")
file.rename(
from = oldFile,
to = newFile)
# Close the remote driver
driver_close(remDr)
# return the newFile location
return(newFile)
# while (!file.exists(oldFile_part)){
# log_info(paste0("Download is completed ", name, " ", period))
#
#
# # https://towardsdatascience.com/how-to-automate-live-data-to-your-website-with-python-f22b76699674
# # https://stackoverflow.com/questions/52262584/selenium-firefox-webdriver-doesnt-make-a-complete-download-of-a-pdf-with-python
#
# newFile = paste0(dest_dir, "NV_", name, "_", "period_", period, "_", get_timestamp(as.time = FALSE), ".xlsx")
#
# file.rename(
# from = oldFile,
# to = newFile)
#
#
# # file.copy(from = oldFile, to = newFile)
# #
# # while(!file.exists(newFile)){
# # Sys.sleep(1)
# # }
# #
# # file.remove(oldFile) # this blocks the rest of the script !
#
#
# # https://stackoverflow.com/questions/12193779/how-to-write-trycatch-in-r
# # result = tryCatch({
# # file.copy(from = oldFile, to = newFile)
# # file.remove(oldFile)
# # result = "success"
# # }, warning = function(warning_condition) {
# # log_info(paste0("Warning raised while attempting to copy the file for ", name, " ", period, "from temp folder to thematic folder"))
# # return(result = "warning")
# # }, error = function(error_condition) {
# # log_info(paste0("An error occured while attempting to copy the file for ", name, " ", period, "from temp folder to thematic folder"))
# # return(result = "error")
# # }, finally={
# # log_info(paste0("the requested file for ", name, " ", "period was processed with ", result))
# # })
#
# # Close the remote driver
# driver_close(remDr)
#
# # return the newFile location
# return(newFile)
# }
}
# CREATE DIRECTORY FOR THEMATIC FILE DOWNLOAD FUNCTION ----
create_thematic_dir = function(dest_dir, thematic){
if(!dir.exists(dest_dir)){
dir.create(dest_dir)
}else{
print("Destination directory for thematic already exists")
}
}
# CLICK RAPPORT SELECTIE FUNCTION
click_rapport_selectie = function(remDr){
log_info(paste0("Selecting rapport selectie"))
rapportselectie = remDr$findElement(using = "xpath", "//*[contains(text(), 'Rapportselectie')]")
while (is.null(rapportselectie)){
print("Waiting for rapport selectie button to show up...")
Sys.sleep(1)
rapportselectie = remDr$findElement(using = "xpath", "//*[contains(text(), 'Rapportselectie')]")
}
rapportselectie$clickElement() # hack to hid the calendar pop-up
}
# CLICK AANGEVRAAGDRAPPORT SELECTIE FUNCTION
click_aangevraagd_rapport = function(remDr){
log_info(paste0("Selecting aangevraagd rapport"))
aangevraagd_rapport = remDr$findElement(using = "xpath", "//*[contains(text(), 'Aangevraagde rapporten')]")
while (is.null(aangevraagd_rapport)){
print("Waiting for aangevraagd_rapport tab to show up...")
Sys.sleep(1)
aangevraagd_rapport = remDr$findElement(using = "xpath", "//*[contains(text(), 'Aangevraagde rapporten')]")
}
aangevraagd_rapport$clickElement() # hack to hid the calendar pop-up
}
# GET THE PERIOD DATE FUNCTION ----
get_period_date = function(num_days){ # use num_day = 0 for today
current_hour = as.numeric(format(Sys.time(), "%H"))
if(current_hour > 20){
period_date = format(Sys.Date() - num_days, '%d/%m/%Y')
}else{
period_date = format(Sys.Date() -1 - num_days, '%d/%m/%Y')
}
return(period_date)
}
# INITIATE THE DRIVER FUNCTION ----
# https://stackoverflow.com/questions/60170311/how-to-switch-download-directory-using-selenium-firefox-python
driver_initiate = function(selenium_host_name, selenium_port){
log_info(paste0("Initiating the Selenium Driver"))
# setting the firefox profile info for auto xlsx MIME type download
ePrefs = makeFirefoxProfile(
list(
"browser.download.dir" = paste0("/home/seluser/Downloads"), # see docker compose file
#"browser.download.dir" = paste0("/home/seluser/Downloads", "/", download_dir),
"browser.download.folderList" = 2L,
"browser.helperApps.alwaysAsk.force" = FALSE,
"browser.download.manager.showWhenStarting" = FALSE,
"browser.download.manager.showAlertOnComplete" = FALSE,
"browser.helperApps.neverAsk.saveToDisk" = "application/zip,application/octet-stream,application/x-zip-compressed,multipart/x-zip,application/x-rar-compressed, application/octet-stream,application/msword,application/vnd.ms-word.document.macroEnabled.12,application/vnd.openxmlformats-officedocument.wordprocessingml.document,application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.openxmlformats-officedocument.wordprocessingml.document,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/rtf,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel,application/vnd.ms-word.document.macroEnabled.12,application/vnd.openxmlformats-officedocument.wordprocessingml.document,application/xls,application/msword,text/csv,application/vnd.ms-excel.sheet.binary.macroEnabled.12,text/plain,text/csv/xls/xlsb,application/csv,application/download,application/vnd.openxmlformats-officedocument.presentationml.presentation,application/octet-stream"
))
# passing the profile to the selenium driver and opening it
remDr = remoteDriver(
remoteServerAddr = selenium_host_name,
extraCapabilities = ePrefs,
port = as.numeric(selenium_port))
}
# LAUNCH THE DRIVER FUNCTION ----
driver_launch = function(remDr){
remDr$open()
}
# CLOSE THE DRIVER FUNCTION ----
driver_close = function(remDr){
remDr$close() #was closeDriver
}
# GO TO TILROY HOMEPAGE AND LOGIN FUNCTION ----
go_to_tilroy = function(remDr, user, pwd){
log_info(paste0("Navigating to Tilroy homepage"))
# define the url of the vgc tilroy homepage
tilroy_url = "https://ve2945.tilroy.com/index.cfm/login"
# navigate to the vgc tilroy homepage
tilroy = remDr$navigate(tilroy_url)
remDr$refresh()
login = remDr$findElement("id", "edtUser")
while(is.null(login)){
Sys.sleep(1)
login = remDr$findElement("id", "edtUser")
}
# input the login credentials. If login is loaded, password is also !
login$sendKeysToElement(list(user))
remDr$findElement("id", "edtPassword")$sendKeysToElement(list(pwd))
# click on login button
remDr$findElement("name", "btnSubmit")$clickElement()
}
# GO TO CENTRALE NEW VERSATO FUNCTION ----
go_to_centrale_new_versato = function(remDr){
# # Choose centrale New versato based on a css selector as no id or name attributes
# centrale_new_versato_url = "https://ve2945.tilroy.com/index.cfm/shoptill/2414"
# centrale_new_versato = remDr$navigate(centrale_new_versato_url)
#
# # navigate to shop 1
# shop_one_url = "https://ve2945.tilroy.com/index.cfm/chooseTill/2732/1/?locktill=1"
# shop_one= remDr$navigate(shop_one_url)
#
# Choose centrale New versato based on a css selector as no id or name attributes
centrale_new_versato = remDr$findElement(using = "css", "div.shops>ul>a:first-child")
while(is.null(centrale_new_versato)){
Sys.sleep(1)
centrale_new_versato = remDr$findElement(using = "css", "div.shops>ul>a:first-child")
}
log_info(paste0("Selecting Centrale New Versato"))
centrale_new_versato$clickElement()
# Choose the first shop
remDr$findElement(using = "css", "div.tills>ul>a:first-child")$clickElement()
}
# GO TO DATABEHEER FUNCTION ----
go_to_databeheer = function(remDr, user, pwd){
log_info(paste0("Selecting databeheer"))
# Go to databeheer
remDr$refresh()
databeheer = remDr$findElement(using = "xpath", "//*[contains(text(), 'Databeheer')]")
while(is.null(databeheer)){
Sys.sleep(1)
databeheer = remDr$findElement(using = "xpath", "//*[contains(text(), 'Databeheer')]")
}
databeheer$clickElement()
# Re-enter credentials in the pop-up
gebruikersnaam = remDr$findElement("id", "edtUserName")
wachtwoord = remDr$findElement("id", "edtPassword")
login = remDr$findElement(using = "xpath", "//*[contains(text(), 'login')]")
while(is.null(login)){
Sys.sleep(1)
login = remDr$findElement(using = "xpath", "//*[contains(text(), 'login')]")
}
gebruikersnaam$sendKeysToElement(list(user))
wachtwoord$sendKeysToElement(list(pwd))
login$clickElement()
}
# GO TO RAPPORTERING FUNCTION ----
go_to_rapportering = function(remDr){
log_info(paste0("Selecting menu Rapportering"))
rapportering = remDr$findElement(using = "xpath", "//a[contains(@href,'/index.cfm/admin/reporting')]")
while(is.null(rapportering)){
Sys.sleep(1)
rapportering= remDr$findElement(using = "xpath", "//a[contains(@href,'/index.cfm/admin/reporting')]")
}
rapportering$clickElement()
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.