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") } })