data_fethcer.r

library(RPostgreSQL)
library(dplyr)
library(whisker)
source("config.R")

redshift <<- src_postgres(
  dbname   = redshift_database,
  host     = redshift_server,
  port     = redshift_port,
  user     = redshift_username,
  password = redshift_password
)

make_sql_list <- function(stem, ids) {
  sql_list <- "("
  for(i in 1:length(ids)) {
    sql_list <- paste0(sql_list, "'", stem, "#", ids[i], "',")
  }
  sql_list <- substr(sql_list, 1, nchar(sql_list) - 1)
  sql_list <- paste0(sql_list, ")")
  return(sql_list)
}

seconds_to_hms <- function(seconds) {
  h = floor(seconds/3600)
  m = floor((seconds - 3600 * h)/60)
  s = seconds - 3600 * h - 60 * m
  h = ifelse(h < 10, paste0("0", h), as.character(h))
  m = ifelse(m < 10, paste0("0", m), as.character(m))
  s = ifelse(s < 10, paste0("0", s), as.character(s))
  return(paste0(h, ":", m, ":", s))
}

agent_stats <- function(from, to, receptions) {
  reception_list <- make_sql_list("Reception", receptions)
  sql_template <- "
    SELECT 
      endpoint_id, SUM(first_responder::INTEGER) AS answers, COUNT(*) AS calls, SUM(duration) AS duration_secs
    FROM 
      analytics_calls
    WHERE 
      endpoint_type = 'Employee'
      AND answered = 1
      AND incoming = 1
      AND root_endpoint IN {{reception_list}}
      AND started_at >= '{{from}}'
      AND started_at <= '{{to}}'
   GROUP BY 
      endpoint_id
  "
  params <- list(
    from           = as.character(as.Date(from)),
    to             = as.character(as.Date(to)),
    reception_list = reception_list
  )
  sql_string <- whisker.render(sql_template, params)
  result <- tbl(redshift, sql(sql_string))
  result <- result %>% collect() %>% arrange(desc(answers)) %>% mutate(duration_hms = seconds_to_hms(duration_secs))
  return(result)
}