Spaces:
Running
Running
File size: 2,940 Bytes
6d3a243 f814daa | 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 | 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")
}
})
|