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