perfect-margin / setup_database.R
Your Name
Initial commit - First working version of dashboard
0480ef8
Raw History Blame Contribute Delete
3.77 kB
library(DBI)
library(RPostgres)
library(tidyverse)
library(lubridate)
library(readr)
# --- CONFIGURATION ---
DB_PORT <- 5433 # The port you selected
MAC_USER <- Sys.info()[["user"]] # Automatically gets your Mac username
CSV_FILE <- "Bid_SalesAnalysisByEstimatorByMonth_121125.csv"
# --- STEP 1: CREATE THE DATABASE ---
print("--- Step 1: Creating Database... ---")
# We must connect to the default 'postgres' database first to create a new one
con_sys <- dbConnect(RPostgres::Postgres(),
dbname = "postgres",
host = "localhost",
port = DB_PORT,
user = MAC_USER,
password = "")
# Try to create the database (fails gracefully if it already exists)
tryCatch({
dbExecute(con_sys, "CREATE DATABASE sales_db")
print("SUCCESS: Database 'sales_db' created.")
}, error = function(e) {
print("NOTE: Database 'sales_db' already exists. Moving on...")
})
dbDisconnect(con_sys)
# --- STEP 2: CREATE TABLES ---
print("--- Step 2: Creating Tables... ---")
# Now connect to the actual 'sales_db'
con <- dbConnect(RPostgres::Postgres(),
dbname = "sales_db",
host = "localhost",
port = DB_PORT,
user = MAC_USER,
password = "")
# Create 'estimators' table
dbExecute(con, "
CREATE TABLE IF NOT EXISTS estimators (
id SERIAL PRIMARY KEY,
primary_name VARCHAR(100) UNIQUE NOT NULL,
active BOOLEAN DEFAULT TRUE
);
")
# Create 'monthly_sales' table
dbExecute(con, "
CREATE TABLE IF NOT EXISTS monthly_sales (
id SERIAL PRIMARY KEY,
estimator_id INTEGER REFERENCES estimators(id),
report_date DATE NOT NULL,
amt_bid NUMERIC(15, 2),
qty_bid INTEGER,
amt_booked NUMERIC(15, 2),
qty_booked INTEGER,
UNIQUE(estimator_id, report_date)
);
")
print("SUCCESS: Tables created.")
# --- STEP 3: LOAD CSV DATA ---
print("--- Step 3: Loading Data... ---")
if (file.exists(CSV_FILE)) {
# Read and Clean Data
raw_data <- read_csv(CSV_FILE, show_col_types = FALSE)
clean_data <- raw_data %>%
mutate(report_date = ym(MoYear)) %>%
mutate(across(c(AmtBid, AmtBooked), ~ as.numeric(gsub("[\\$,]", "", .)))) %>%
mutate(clean_name = case_when(
Estimator == "Scott W. Hutchings" ~ "Scott Hutchings",
Estimator == "SW" ~ "Scott Hutchings",
Estimator == "SH" ~ "Scott Hutchings",
TRUE ~ Estimator
))
# Update Estimators Table (Insert new names)
unique_estimators <- unique(clean_data$clean_name)
for(name in unique_estimators){
exists <- dbGetQuery(con, "SELECT id FROM estimators WHERE primary_name = $1", params = list(name))
if(nrow(exists) == 0){
dbExecute(con, "INSERT INTO estimators (primary_name) VALUES ($1)", params = list(name))
}
}
# Prepare Sales Data for Upload
est_map <- dbGetQuery(con, "SELECT id, primary_name FROM estimators")
upload_ready <- clean_data %>%
left_join(est_map, by = c("clean_name" = "primary_name")) %>%
select(estimator_id = id, report_date, amt_bid = AmtBid, qty_bid = QtyBid,
amt_booked = AmtBooked, qty_booked = QtyBooked)
# Write to DB
tryCatch({
# We use 'on conflict do nothing' logic via a safe append approach
# Since RPostgres dbWriteTable is simple, we just try/catch duplicates
dbWriteTable(con, "monthly_sales", upload_ready, append = TRUE, row.names = FALSE)
print("SUCCESS: Data uploaded to database.")
}, error = function(e) {
print("NOTE: Data might already exist. (Ignoring duplicate errors)")
})
} else {
print("ERROR: CSV file not found. Make sure the file name is correct.")
}
dbDisconnect(con)
print("--- SETUP COMPLETE ---")