Author: Martin Donovan | GitHub gist | profile.json
library(tidyverse)
library(httr2)
library(tidyjson)
library(DBI)
library(RPostgres)
# Restaurant365 OData API connector — R functions for retrieving GL transaction
# data from the Restaurant365 financial system.
#
# Core pattern: getDFFromEndpoint() handles paginated OData retrieval with basic
# auth. Callers build date/location-filtered URLs and pass them in.
# Transaction headers and line-item details are fetched separately and joined.
# Column names are renamed to match the PostgreSQL schema before insert.
# Idempotency: hasTransactionsForDate() guards against duplicate imports.
#
# Credentials loaded from .Renviron: DB_HOST, DB_PORT, DB_USER, DB_PASS, WINE_DB
getDBConnection <- function() {
dbConnect(
RPostgres::Postgres(),
dbname = Sys.getenv("WINE_DB"),
host = Sys.getenv("DB_HOST"),
port = Sys.getenv("DB_PORT"),
user = Sys.getenv("DB_USER"),
password = Sys.getenv("DB_PASS")
)
}
# --- OData pagination ---------------------------------------------------------
# Fetches all pages from an OData endpoint using $count + $skip pagination.
# Returns a flat dataframe. Handles URL encoding and basic auth.
getDFFromEndpoint <- function(endpointURL, user, password) {
endpointURL <- gsub(" ", "%20", endpointURL)
endpointURL <- gsub("'", "%27", endpointURL)
x <- ifelse(grepl("[?]", endpointURL), "&$count=true", "?$count=true")
endpointURL <- paste(endpointURL, x, sep = "")
req <- request(endpointURL) |> req_auth_basic(user, password)
resp <- req_perform(req)
respJSON <- resp |> httr2::resp_body_json()
count <- respJSON$`@odata.count`
DF <- respJSON$value %>% spread_all()
pageSize <- nrow(DF)
i <- 1
while (nrow(DF) < count) {
epurl <- paste(endpointURL, "&$skip=", i * pageSize, sep = "")
req <- request(epurl) |> req_auth_basic(user, password)
resp <- req_perform(req)
respJSON <- resp |> httr2::resp_body_json()
df <- respJSON$value %>% spread_all()
DF <- bind_rows(DF, df)
i <- i + 1
}
as.data.frame(DF) %>% select(-c(`..JSON`))
}
# --- GL accounts --------------------------------------------------------------
getGLAccountsDF <- function(user, password) {
getDFFromEndpoint("https://odata.restaurant365.net/api/v2/views/GlAccount",
user, password) %>%
select(glAccountId, name, glAccountNumber, glType)
}
getCompanies <- function(user, password) {
getDFFromEndpoint("https://odata.restaurant365.net/api/v2/views/Company",
user, password)
}
getCompanyNameFromId <- function(companyDF, compId) {
compName <- unlist(companyDF %>% filter(companyId == compId) %>% select(name))[1]
as.character(compName)
}
getCompanyNameFromIdVectorized <- Vectorize(getCompanyNameFromId,
vectorize.args = c("compId"))
getEntityId <- function(entities, locId) {
as.integer(entities %>% filter(r365_id == locId) %>% select(entity_id))
}
getEntityIdVectorized <- Vectorize(getEntityId, vectorize.args = c("locId"))
# --- Transaction retrieval ----------------------------------------------------
getTransactionDetails <- function(txID, user, password) {
txDetEndpoint <- stringr::str_glue(
"https://odata.restaurant365.net/api/v2/views/TransactionDetail",
"?$filter=transactionId%20eq%20({txID})"
)
GLAccounts <- getGLAccountsDF(user, password)
DF <- getDFFromEndpoint(txDetEndpoint, user, password)
if (nrow(DF) > 0) {
DF <- DF %>%
group_by(transactionId, glAccountId) %>%
summarise(credit = sum(credit), debit = sum(debit), .groups = "drop")
left_join(DF,
GLAccounts %>% select(glAccountId, name, glAccountNumber, glType),
by = "glAccountId")
} else {
NULL
}
}
getTransactionDetailsDF <- function(txIDs, user, password) {
DF <- NULL
for (row in 1:nrow(txIDs)) {
result <- getTransactionDetails(txIDs[row, "transactionId"], user, password)
if (!is.null(result)) {
DF <- if (is.null(DF)) result else dplyr::bind_rows(DF, result)
}
}
DF
}
getTransactionsForAllLocationsBetweenDF <- function(startDate, endDate, user, password) {
startDate <- as.Date(startDate, origin = "1970-01-01")
endDate <- as.Date(endDate, origin = "1970-01-01")
txEndpoint <- stringr::str_glue(
"https://odata.restaurant365.net/api/v2/views/Transaction",
"?$filter=date%20ge%20{startDate}T00:00:00Z%20and%20date%20lt%20{endDate}T23:59:59Z"
)
txDF <- getDFFromEndpoint(txEndpoint, user, password)
combinedDF <- tibble(.rows = 0)
if (!is.null(txDF) && nrow(txDF) > 0) {
detailDF <- getTransactionDetailsDF(txDF %>% select(transactionId), user, password)
if (!is.null(detailDF)) {
combinedDF <- left_join(detailDF, txDF, by = "transactionId")
}
}
combinedDF
}
getTransactionsForLocationBetweenDF <- function(locationID, startDate, endDate, user, password) {
txEndpoint <- stringr::str_glue(
"https://odata.restaurant365.net/api/v2/views/Transaction",
"?$filter=locationId%20eq%20{locationID}",
"%20and%20date%20ge%20{startDate}T00:00:00Z%20and%20date%20lt%20{endDate}T23:59:59Z"
)
txDF <- getDFFromEndpoint(txEndpoint, user, password)
combinedDF <- tibble(.rows = 0)
if (!is.null(txDF) && nrow(txDF) > 0) {
detailDF <- getTransactionDetailsDF(txDF %>% select(transactionId), user, password)
if (!is.null(detailDF)) {
combinedDF <- left_join(detailDF, txDF, by = "transactionId")
}
}
combinedDF
}
# --- Column rename + insert ---------------------------------------------------
# Renames OData response columns to match the PostgreSQL r365_transactions schema,
# derives entity_id from locationId via the entities lookup table,
# and derives gl_parent_account_code as the 5-char account prefix.
rename_transactionDFColumnsForDB <- function(DF, companies, entities) {
DF %>%
rename(
gl_account_code = glAccountNumber,
r_365_transaction_id = transactionId,
credit_amount = credit,
debit_amount = debit,
gl_account_name = name.x,
gl_type = glType,
transaction_type = type,
row_version = rowVersion,
is_approved = isApproved,
transaction_name = name.y,
created_by = createdBy,
modified_by = modifiedBy,
created_on = createdOn,
modified_on = modifiedOn,
transaction_number = transactionNumber,
company_id = companyId
) %>%
mutate(
company_name = getCompanyNameFromIdVectorized(companies, company_id),
entity_id = getEntityIdVectorized(entities, locationId),
gl_parent_account_code = substring(gl_account_code, 1, 5)
) %>%
select(-c(glAccountId, document.id, locationId, locationName))
}
insert_DF <- function(df) {
con <- getDBConnection()
on.exit(dbDisconnect(con))
DBI::dbWithTransaction(con,
DBI::dbAppendTable(con, "r365_transactions", df))
}
# --- Idempotency helpers ------------------------------------------------------
hasTransactionsForDate <- function(date, con) {
DF <- dbGetQuery(con,
paste("select count(*) from r365_transactions where date = '", date, "'", sep = ""))
DF[1, 1] > 0
}
deleteTransactionsForDate <- function(date, con) {
DBI::dbExecute(con,
paste("delete from r365_transactions where date = '", date, "'", sep = ""))
}
get_missing_dates <- function(origin_date) {
con <- getDBConnection()
on.exit(dbDisconnect(con))
result <- DBI::dbGetQuery(con,
"select distinct date from r365_transactions where date between $1 and $2",
params = list(origin_date, Sys.Date() - 1)) %>%
pull(date) %>%
as.Date()
date_seq <- seq.Date(from = as.Date(origin_date), to = Sys.Date() - 1, by = "day")
missing_dates <- lubridate::setdiff(date_seq, result) %>% lubridate::as_date()
missing_dates
}