# 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("iTune", c("t_units", "u_customers"), "_12WeeksEnding_", endMonth, "_by_UPC_and_Country")
  setattr(AsNames, "names", c("t", "u"))

  QRYS[[endMonth]] <- 
  paste("
  SELECT upc, user_country,
         SUM (netUnits) AS  ", AsNames[["u"]], ",
         COUNT (netUnits) AS  ", AsNames[["t"]], "
  FROM     (SELECT upc, customer_identifier AS customerid, country_code AS user_country,
                   SUM ( units *  ((-1) ^ ((sale_return = 'R') IS TRUE)::int)  )  AS netUnits
            FROM  production.staging_raw_itunes
            WHERE download_date >= '", dateRange[["start"]], "'
              AND download_date <= '", dateRange[["end"]], "'
            GROUP BY upc, country_code, customer_identifier
           ) ", paste0("mini_", endMonth) ,"
  GROUP BY upc, user_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.itunes.count <- runQry(QRY, verbose=TRUE, verbose.max.lines=22L, verbose.max.width=110L)
  if (0==nrow(DB.itunes.count))
     stop("No Rows Pulled")
  print(DB.itunes.count)
#  try(
#    setnames(DB.itunes.count, "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.itunes.count", "_", qry_timestamp)
  fileBackUps[[ f.ind ]] <-
       jesus(DB.itunes.count, dir=dataDir, sub=FALSE, stampFile=TRUE, stampDir=FALSE, summary=FALSE, verbose=FALSE)
  
  invisible(reproduce((fileBackUps[[f.ind]]), name="file.DB.itunes.count", clipboard=FALSE, showSample=FALSE))

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

  beep(10, .2)

}