Spaces:
Running
Running
phantomas34
Seed IMS tables (follow-ups, NLP scores, projects) into DuckDB for Client/Revenue Intelligence
f814daa Download seed_data.R from phantomas34/perfect-margin: direct link, hf CLI and curl.
- Browser
- Download file 2.94 kB
-
https://huggingface.co/spaces/phantomas34/perfect-margin/resolve/main/seed_data.R
- Command line
-
hf download hf://spaces/phantomas34/perfect-margin/seed_data.R
-
curl -L -o seed_data.R https://huggingface.co/spaces/phantomas34/perfect-margin/resolve/main/seed_data.R
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") | |
| } | |
| }) | |