OutOfStock:

#### OOS_Automate.R
#Author: Danel Shields (daniel.shields@abbott.com)
#Create Date: 03/22/2022
#Notes: This is a script to run a sequence of SQLs against Point of Sale data to determine Out of Stock Rates
#       by item/retailer/week.  The purpose of coding it in this way (functions) is to enable automation.

#Last Updated: 03/22/2022 (daniel.shields@abbott.com) - saving as a package in GitHub to Enable Automation.



#### Load Packages ----------------------------------------------------------

# #first, common packages needed and used in this code and/or common functions
# #then, custom packages hosted on Github for common functions

library(readtext)
library(mailR)
library(odbc)
library(lubridate)
library(htmlTable)

library(devtools)

install_github("ANPD-Data-Analytics/Connections")
install_github("ANPD-Data-Analytics/CommonFunctions")

library(Connections)
library(CommonFunctions)

#Open Log File and Start Logging - to Desktop
LogFile <- paste0("C:/Users/", Connections::sysUser511, "/Desktop/OOS_Automate_Log.txt")
#CommonFunctions::LogEvent(paste0("Starting Logging for ", LogFile), LogFile)
#log Events - CommonFunctions::LogEvent Version overwrites file with every new log entry for some reason....
LocalLogEvent <- function (LogString, LogFile) {
  log_con <- file(LogFile, open="a")
  log_string <- paste(now(), ": ", LogString)
  cat(log_string, file = log_con, sep="\n")
}


#Declare RunScript Function to be called through Automation
RunScript <- function() {
tryCatch( {

LocalLogEvent("OOS Process Begining", LogFile)


#### Loop Through SQL ####

#Load all relevalnt Brands to be analyzed into a list.

BR_FAMILY_LIST <- list('Ensure', 'Glucerna', 'Similac', 'Pedialyte', 'Pediasure', 'ZonePerfect')
LIST_LEN <- length(BR_FAMILY_LIST)


#Populate initial SQL to be referenced/manipulated in loop later
LocalLogEvent("Populate Root tSQL from https://raw.githubusercontent.com/ANPD-Data-Analytics/OutOfStock/main/", LogFile)
SQLPath <- "https://raw.githubusercontent.com/ANPD-Data-Analytics/OutOfStock/main/"

tSQL1 <- readtext(paste0(SQLPath, "OOS_SQL_w_BF_Variables_Q1.txt"))
tSQL2 <- readtext(paste0(SQLPath, "OOS_SQL_w_BF_Variables_Q2.txt"))
tSQL3 <- readtext(paste0(SQLPath, "OOS_SQL_w_BF_Variables_Q3.txt"))
tSQL4 <- readtext(paste0(SQLPath, "OOS_SQL_w_BF_Variables_Q4.txt"))
tSQL5 <- readtext(paste0(SQLPath, "OOS_SQL_w_BF_Variables_Q5.txt"))
tSQL6 <- readtext(paste0(SQLPath, "OOS_SQL_w_BF_Variables_Q6.txt"))
tSQL7 <- readtext(paste0(SQLPath, "OOS_SQL_w_BF_Variables_Q7.txt"))
tSQL8 <- readtext(paste0(SQLPath, "OOS_SQL_w_BF_Variables_Q8.txt"))



#define my counter for the loop; starting at 1 for the first record of the BR_FM list
i <- 1

#loop through the BR_FAMILY_LIST using the counter and repeat function.
while (i <= LIST_LEN) {

  #Populate the necessary SQLs into variables.
  SQL1 <- tSQL1
  SQL2 <- tSQL2
  SQL3 <- tSQL3
  SQL4 <- tSQL4
  SQL5 <- tSQL5
  SQL6 <- tSQL6
  SQL7 <- tSQL7
  SQL8 <- tSQL8

  #Replace the AN_BRAND_FAMILY_VARIABLE with the item within the list - for where we're at in the iteration
  SQL1 <- gsub("AN_BRAND_FAMILY_VARIABLE",BR_FAMILY_LIST[i], SQL1)
  SQL2 <- gsub("AN_BRAND_FAMILY_VARIABLE",BR_FAMILY_LIST[i], SQL2)
  SQL3 <- gsub("AN_BRAND_FAMILY_VARIABLE",BR_FAMILY_LIST[i], SQL3)
  SQL4 <- gsub("AN_BRAND_FAMILY_VARIABLE",BR_FAMILY_LIST[i], SQL4)
  SQL5 <- gsub("AN_BRAND_FAMILY_VARIABLE",BR_FAMILY_LIST[i], SQL5)
  SQL6 <- gsub("AN_BRAND_FAMILY_VARIABLE",BR_FAMILY_LIST[i], SQL6)
  SQL7 <- gsub("AN_BRAND_FAMILY_VARIABLE",BR_FAMILY_LIST[i], SQL7)
  SQL8 <- gsub("AN_BRAND_FAMILY_VARIABLE",BR_FAMILY_LIST[i], SQL8)

  #Execute the SQLs; building and dropping temp tables to calculate as necessary
  LocalLogEvent(paste("SQL1 BEGIN: ", BR_FAMILY_LIST[i]), LogFile)
  qrySandboxExecute(SQL1)

  LocalLogEvent(paste("SQL2 BEGIN: ", BR_FAMILY_LIST[i]), LogFile)
  qrySandboxExecute(SQL2)

  LocalLogEvent(paste("SQL3 BEGIN: ", BR_FAMILY_LIST[i]), LogFile)
  qrySandboxExecute(SQL3)

  LocalLogEvent(paste("SQL4 BEGIN: ", BR_FAMILY_LIST[i]), LogFile)
  qrySandboxExecute(SQL4)

  LocalLogEvent(paste("SQL5 BEGIN: ", BR_FAMILY_LIST[i]), LogFile)
  qrySandboxExecute(SQL5)

  LocalLogEvent(paste("SQL6 BEGIN: ", BR_FAMILY_LIST[i]), LogFile)
  qrySandboxExecute(SQL6)

  LocalLogEvent(paste("SQL7 BEGIN: ", BR_FAMILY_LIST[i]), LogFile)
  qrySandboxExecute(SQL7)

  LocalLogEvent(paste("SQL8 BEGIN: ", BR_FAMILY_LIST[i]), LogFile)
  qrySandboxExecute(SQL8)

  #iterate the counter to iterate the list
  i <- i + 1

  #break the loop if we've passed the end of the list (all variables are loaded and done)
  #if(i > LIST_LEN){ break }

}


#### Generate and Write Output for Reporting ####
LocalLogEvent("Output SQL and .csv write - OOSOutput.csv to Desktop", LogFile)


###daniel.shields@abbott.com (03/22/2022) - commenting out OOSOutput.csv query and file create.
###Data is democratized via Power BI
#SQL <-  readtext(paste0(SQLPath, "OOS_SQL_w_BF_Variables_FINALOUTPUT-RECALLVERSION.txt"))
#OOSOut <- qrySandboxReturn(SQL)

#define the location for the output file
setwd(paste("C:/Users/", Connections::sysUser511, "/Desktop", sep = ""))
#outputloc = "OOSOutput.csv"

#delete the file if it exists already
#if (file.exists(outputloc)){
#  file.remove(outputloc)}

# Write .csv as output
# write.csv(OOSOut
#           , file = outputloc
#           , na = ""
#           , row.names = FALSE)



LocalLogEvent("Output SQL and .csv write - KrogerOOSOutput.csv", LogFile)

SQL <-  readtext(paste0(SQLPath, "KrogerSimNWOOS.txt"))
KrogerOOSOut <- qrySandboxReturn(SQL)

#define the location for the output file
outputloc = "KrogerOOSOutput.csv"

#delete the file if it exists already
if (file.exists(outputloc)){
  file.remove(outputloc)}

# Write .csv as output
write.csv(KrogerOOSOut
          , file = outputloc
          , na = ""
          , row.names = FALSE)



####compose and send email notification(s) on completion####

LocalLogEvent("Sending Complete email to ANPD-MI team", LogFile)

to <- c("daniel.shields@abbott.com","carly.goodman@abbott.com","jeffrey.glasnapp@abbott.com")
from <- "daniel.shields@abbott.com"
subject <- "OOS Dataset ready"
body <- paste("Dataset written.  Copy can be found @C:/Users/", Connections::sysUser511, "/Desktop/OOSOutput.csv", sep = "")

sendEmailNoAtt(to, from, subject, body)



####compose and send Kroger email notification on completion####

LocalLogEvent("Sending Complete email (with Attachment) to ANPD-MI team + Kroger (Angie)", LogFile)

to <- c("daniel.shields@abbott.com","carly.goodman@abbott.com","jeffrey.glasnapp@abbott.com","Angela.Doliboa@abbott.com")
from <- "daniel.shields@abbott.com"
subject <- "OOS Dataset ready: Kroger Sim NW OOS"
body <- paste("Dataset written and Kroger Sim NW OOS data Attached", "\n\n\n")
htmlbody = paste(body, htmlTable(KrogerOOSOut))

sendEmailWAtt(to, from, subject, htmlbody, outputloc)

}
, error = function(e) {
  to <- c("daniel.shields@abbott.com","carly.goodman@abbott.com","Jeffrey.Glasnapp@abbott.com")
  from <- "data.analytics@abbott.com"
  subject <- "Error caught in OOS_Automate script"
  body = e$message
  sendEmailNoAtt(to, from, subject, body)
}
)

#### Clear Environment ####
rm(list = ls())

}