perfect-margin / seed_data.R
phantomas34
Seed IMS tables (follow-ups, NLP scores, projects) into DuckDB for Client/Revenue Intelligence
f814daa
Raw History Blame Contribute Delete
2.94 kB
local({
csv_path <- Sys.getenv("DATA_CSV", "Bid_SalesAnalysisByEstimatorByMonth_121125.csv")
raw <- readr::read_csv(csv_path, show_col_types = FALSE)
clean <- raw %>%
mutate(
report_date = lubridate::ym(MoYear),
across(c(AmtBid, AmtBooked), ~ as.numeric(gsub("[\\$,]", "", .))),
primary_name = case_when(
Estimator == "Scott W. Hutchings" ~ "Scott Hutchings",
Estimator %in% c("SW", "SH") ~ "Scott Hutchings",
TRUE ~ Estimator))
estimators <- clean %>%
distinct(primary_name) %>% arrange(primary_name) %>%
mutate(id = row_number(), active = TRUE) %>%
select(id, primary_name, active)
monthly_sales <- clean %>%
left_join(select(estimators, estimator_id = id, primary_name), by = "primary_name") %>%
transmute(id = row_number(), estimator_id, report_date,
amt_bid = AmtBid, qty_bid = QtyBid,
amt_booked = AmtBooked, qty_booked = QtyBooked)
conn <- poolCheckout(db_pool)
dbWriteTable(conn, "estimators", estimators, overwrite = TRUE)
dbWriteTable(conn, "monthly_sales", monthly_sales, overwrite = TRUE)
poolReturn(conn)
})
# ── IMS TABLES (Client & Revenue Intelligence) β€” seeded from data/*.csv ──────
# Defensive: any missing/bad file is skipped so app startup never fails.
local({
conn <- poolCheckout(db_pool)
on.exit(poolReturn(conn))
load_csv <- function(tbl) {
f <- file.path("data", paste0(tbl, ".csv"))
if (!file.exists(f)) { message(sprintf("[seed] skip %s (no file)", tbl)); return(FALSE) }
ok <- tryCatch({
dbExecute(conn, sprintf(
"CREATE OR REPLACE TABLE %s AS SELECT * FROM read_csv_auto('%s', header=true, nullstr='NA', sample_size=-1)",
tbl, f)); TRUE
}, error = function(e) { message(sprintf("[seed] load %s FAILED: %s", tbl, conditionMessage(e))); FALSE })
if (ok) message(sprintf("[seed] loaded %s (%s rows)", tbl,
dbGetQuery(conn, sprintf("SELECT COUNT(*) n FROM %s", tbl))$n))
ok
}
retype <- function(tbl, cols, type) for (c in cols) tryCatch(
dbExecute(conn, sprintf("ALTER TABLE %s ALTER COLUMN %s TYPE %s", tbl, c, type)),
error = function(e) message(sprintf("[seed] %s.%s -> %s skipped: %s", tbl, c, type, conditionMessage(e))))
if (load_csv("bid___follow_up"))
retype("bid___follow_up", c("bidfudate"), "DATE")
if (load_csv("follow_up_scores")) {
retype("follow_up_scores", c("follow_up_date"), "DATE")
retype("follow_up_scores", c("rule_score","award_probability"), "DOUBLE")
}
if (load_csv("pm___project_file")) {
retype("pm___project_file", c("projectdatecreated","substantialcompletiondate","finalcompletiondate"), "DATE")
retype("pm___project_file", c("billingcompletepercent"), "DOUBLE")
}
if (load_csv("project_file")) {
retype("project_file", c("contractdate"), "DATE")
retype("project_file", c("contractamt"), "DOUBLE")
}
})