Spaces:
Running
Running
File size: 2,838 Bytes
0480ef8 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | library(DBI)
library(RPostgres)
library(tidyverse)
library(lubridate)
library(readr)
# --- 1. SETTINGS ---
CSV_FILENAME <- "Bid_SalesAnalysisByEstimatorByMonth_121125.csv"
DB_PORT <- 5433
print("--- DIAGNOSTIC START ---")
# --- 2. CHECK FILE EXISTENCE ---
# This checks if RStudio is looking in the right folder
if (file.exists(CSV_FILENAME)) {
print(paste("✅ SUCCESS: Found file:", CSV_FILENAME))
} else {
print(paste("❌ ERROR: Could not find file:", CSV_FILENAME))
print(paste(" R is currently looking in:", getwd()))
stop("Stoppping script. Please move the CSV to the folder listed above.")
}
# --- 3. CONNECT TO DATABASE ---
tryCatch({
con <- dbConnect(RPostgres::Postgres(),
dbname = "sales_db",
host = "localhost",
port = DB_PORT,
user = Sys.info()[["user"]],
password = "")
print("✅ SUCCESS: Connected to Database.")
}, error = function(e) {
print("❌ ERROR: Could not connect to Database. Make sure Postgres.app is running.")
stop(e)
})
# --- 4. LOAD DATA ---
print("--- Attempting Data Load ---")
raw_data <- read_csv(CSV_FILENAME, 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
))
# Insert Estimator Names
unique_estimators <- unique(clean_data$clean_name)
count_new_names <- 0
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))
count_new_names <- count_new_names + 1
}
}
print(paste(" Processed Estimators. Added", count_new_names, "new names."))
# Insert Sales Data
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({
dbWriteTable(con, "monthly_sales", upload_ready, append = TRUE, row.names = FALSE)
print(paste("✅ SUCCESS: Uploaded", nrow(upload_ready), "sales records."))
}, error = function(e) {
print("⚠️ NOTE: Data write skipped (likely duplicate data).")
})
# --- 5. FINAL VERIFICATION ---
row_count <- dbGetQuery(con, "SELECT count(*) FROM monthly_sales")
print(paste("--- FINAL DATABASE STATUS: The table now has", row_count, "rows. ---"))
dbDisconnect(con)
|