# July Aug Sept
# USE WEEKS! - 12 Weeks ending 9/30

utilSource(verbose=TRUE)

monthsUsing <- 6:9

#  ## ALTERNATIVE.  At least one day into the next month.  But why do this ?
#  for (endMonth in month.abb[(6:9)+1]) {
#    endDate <- StringToDate.CheckingForMonth(endMonth, end=FALSE)
#    dateRange <- weekSpan(weeks=12, end=endDate, round.end="Mon", round.direction="future", range=FALSE)
#    print(table(month(dateRange)))
#  }

QRYS <- c()
for (endMonth in month.abb[monthsUsing]) {

  # The 12 Week period ending on the MONDAY on or following the last day of the month.
  dateRange <- weekSpan(weeks=12, end=endMonth, round.end="Mon", round.direction="future", range=TRUE)

  AsNames <- paste0("Spot", c("t_units", "u_customers"), "_12WeeksEnding_", endMonth, "_by_UPC_and_Country")
  setattr(AsNames, "names", c("t", "u"))

  QRYS[[endMonth]] <- 
  paste("
  SELECT upc, country AS user_country,
         COUNT (DISTINCT customerid) AS ", AsNames[["u"]], ",
         COUNT (customerid)          AS ", AsNames[["t"]], "
  FROM  production.staging_raw_spotify
  WHERE download_date >= '", dateRange[["start"]], "'
    AND download_date <= '", dateRange[["end"]], "'
  GROUP BY upc, country
  ")
}

if (length(QRYS) > 1) {
  nms.q <- names(QRYS)
  if (is.null(nms.q) || !all(nchar(nms.q)> 0) || anyDuplicated(nms.q))
    stop("`QRYS` does not have proper names")
  QRYS <- paste0("(", QRYS, ")  ", nms.q)
  QRYS[[1L]] <- paste0("SELECT * FROM ", QRYS[[1L]], " ")
  QRYS[-1L] <- paste("\nFULL OUTER JOIN\n", QRYS[-1L], "\nUSING (upc, user_country)\n")
}
QRY <- pasteC(QRYS, C=" ")


{

  # Submit the qruery and store the results
  library(RPostgreSQL)
  try(dbDisconnect(con), silent=TRUE)
  con    <- getDB(drv.str="PostgreSQL")
  DB.all.count.Sept <- runQry(QRY, verbose=TRUE, verbose.max.lines=22L, verbose.max.width=110L)
  if (0==nrow(DB.all.count.Sept))
     stop("No Rows Pulled")
  print(DB.all.count.Sept)
#  try(
#    setnames(DB.all.count.Sept, "country", "user_country")
#  , silent=TRUE)

  ## ------------------------------ ##
  try(dbDisconnect(con), silent=TRUE)
  try(detach(package:RPostgreSQL), silent=TRUE)
  ## ------------------------------ ##



  ## ------------------------------ ##
  ##      SAVE THE DATA             ##
  ## ------------------------------ ##  
  if (!exists("fileBackUps"))
    fileBackUps <- c()
  qry_timestamp <- timeStamp(frmt=frmt.monthday_time)
  f.ind <- paste0("DB.all.count.Sept", "_", qry_timestamp)
  fileBackUps[[ f.ind ]] <-
       jesus(DB.all.count.Sept, dir=dataDir, sub=FALSE, stampFile=TRUE, stampDir=FALSE, summary=FALSE, verbose=FALSE)
  
  invisible(reproduce((fileBackUps[[f.ind]]), name="file.DB.all.count.Sept", clipboard=FALSE, showSample=FALSE))

  # Clean up garbage before saving image
  gcQuietly()
  ## ------------------------------ ##
}



{ 
beep(1)
Sys.sleep(.75)
beep(3, .25)
beep(1, .35)
beep(1, .5)
beep(1, .5)
beep(1, .5)
}




#  
#  
#  
#  
#  
#  
#  
#  
#  
#  
#  
#  
#  dateRange
#  {
#  print(diffs <- dateRange[[length(dateRange)]] - dateRange[[1]] + 1)
#  print(as.numeric(diffs) %% 7)
#  cat(" ~~~~~~~~ \n")
#  }
#  
#  count: 
#     for each artist
#          T/U Streams  ~  Country   
#  
#    for each genre
#          T/U Streams ~  Country
#  
#  
#  t_StreamsFor{MONTH}_ByCountry
#  t_StreamsFor{MONTH}_ByCountry
#