File size: 3,768 Bytes
0480ef8
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
library(DBI)
library(RPostgres)
library(tidyverse)
library(lubridate)
library(readr)

# --- CONFIGURATION ---
DB_PORT <- 5433                 # The port you selected
MAC_USER <- Sys.info()[["user"]] # Automatically gets your Mac username
CSV_FILE <- "Bid_SalesAnalysisByEstimatorByMonth_121125.csv"

# --- STEP 1: CREATE THE DATABASE ---
print("--- Step 1: Creating Database... ---")

# We must connect to the default 'postgres' database first to create a new one
con_sys <- dbConnect(RPostgres::Postgres(), 
                     dbname = "postgres", 
                     host = "localhost", 
                     port = DB_PORT, 
                     user = MAC_USER, 
                     password = "")

# Try to create the database (fails gracefully if it already exists)
tryCatch({
  dbExecute(con_sys, "CREATE DATABASE sales_db")
  print("SUCCESS: Database 'sales_db' created.")
}, error = function(e) {
  print("NOTE: Database 'sales_db' already exists. Moving on...")
})

dbDisconnect(con_sys)

# --- STEP 2: CREATE TABLES ---
print("--- Step 2: Creating Tables... ---")

# Now connect to the actual 'sales_db'
con <- dbConnect(RPostgres::Postgres(), 
                 dbname = "sales_db", 
                 host = "localhost", 
                 port = DB_PORT, 
                 user = MAC_USER, 
                 password = "")

# Create 'estimators' table
dbExecute(con, "
  CREATE TABLE IF NOT EXISTS estimators (
    id SERIAL PRIMARY KEY,
    primary_name VARCHAR(100) UNIQUE NOT NULL,
    active BOOLEAN DEFAULT TRUE
  );
")

# Create 'monthly_sales' table
dbExecute(con, "
  CREATE TABLE IF NOT EXISTS monthly_sales (
    id SERIAL PRIMARY KEY,
    estimator_id INTEGER REFERENCES estimators(id),
    report_date DATE NOT NULL,
    amt_bid NUMERIC(15, 2),
    qty_bid INTEGER,
    amt_booked NUMERIC(15, 2),
    qty_booked INTEGER,
    UNIQUE(estimator_id, report_date)
  );
")
print("SUCCESS: Tables created.")

# --- STEP 3: LOAD CSV DATA ---
print("--- Step 3: Loading Data... ---")

if (file.exists(CSV_FILE)) {
  # Read and Clean Data
  raw_data <- read_csv(CSV_FILE, show_col_types = FALSE)
  
  clean_data <- raw_data %>%
    mutate(report_date = ym(MoYear)) %>% 
    mutate(across(c(AmtBid, AmtBooked), ~ as.numeric(gsub("[\\$,]", "", .)))) %>%
    mutate(clean_name = case_when(
      Estimator == "Scott W. Hutchings" ~ "Scott Hutchings",
      Estimator == "SW" ~ "Scott Hutchings", 
      Estimator == "SH" ~ "Scott Hutchings",
      TRUE ~ Estimator
    ))
  
  # Update Estimators Table (Insert new names)
  unique_estimators <- unique(clean_data$clean_name)
  for(name in unique_estimators){
    exists <- dbGetQuery(con, "SELECT id FROM estimators WHERE primary_name = $1", params = list(name))
    if(nrow(exists) == 0){
      dbExecute(con, "INSERT INTO estimators (primary_name) VALUES ($1)", params = list(name))
    }
  }
  
  # Prepare Sales Data for Upload
  est_map <- dbGetQuery(con, "SELECT id, primary_name FROM estimators")
  
  upload_ready <- clean_data %>%
    left_join(est_map, by = c("clean_name" = "primary_name")) %>%
    select(estimator_id = id, report_date, amt_bid = AmtBid, qty_bid = QtyBid, 
           amt_booked = AmtBooked, qty_booked = QtyBooked)
  
  # Write to DB
  tryCatch({
    # We use 'on conflict do nothing' logic via a safe append approach
    # Since RPostgres dbWriteTable is simple, we just try/catch duplicates
    dbWriteTable(con, "monthly_sales", upload_ready, append = TRUE, row.names = FALSE)
    print("SUCCESS: Data uploaded to database.")
  }, error = function(e) {
    print("NOTE: Data might already exist. (Ignoring duplicate errors)")
  })
  
} else {
  print("ERROR: CSV file not found. Make sure the file name is correct.")
}

dbDisconnect(con)
print("--- SETUP COMPLETE ---")