# -------------------------------------------------------------------------------------------------------------------------  #
  #  -----------------------------------------------------------------------------------------------------------------------   #
  #                                                                                                                            #
  #           File Name              :  itunes_544.r                                                                           #
  #           Last Updated Funclist  :  08 Feb 2015,  5:12 AM (Sunday)                                                         #
  #                                                                                                                            #
  #           Author Name            :  Rick Saporta                                                                           #
  #           Author Email           :  RickSaporta@gmail.com                                                                  #
  #           Author URL             :  www.github.com/rsaporta                                                                #
  #                                                                                                                            #
  #           Packages Called        :  NA                                                                                     #
  #           Packages Used via NS   :  NA                                                                                     #
  #                                                                                                                            #
  #  -----------------------------------------------------------------------------------------------------------------------   #
  #                                                                                                                            #
  #   convertDateToiTunes544 ( julian )                                                                                        #
  #   itunes_544             ( minDate="2011-10-02", maxDate=Sys.Date() + 90, out=c("DT", "SQL", "both"), assign=TRUE          #
  #                            , assign.to.envir=TRUE, envir=parent.frame(), assign.nm="DT.iTunes544"                          #
  #                            , dateCol="download_activity_date", dateColAS="iTunesMonth", elseValue="''"                     #
  #                            , refresh="always", verbose=FALSE )                                                             #
  #                                                                                                                            #
  #                                                                                                                            #
  #                                                     <END FUNCS>                                                            #
  #  -----------------------------------------------------------------------------------------------------------------------   #
  # -------------------------------------------------------------------------------------------------------------------------  #

## itunes_544.r

convertDateToiTunes544 <- function(julian) {
  if (is.character(julian))
    julian <- as.Date(julian, origin=.origin)
  if (!is.Date(julian))
    stop ("julian should be a date")

  minD <- min(julian)
  maxD <- max(julian)
  DT.itunes_544 <- itunes_544(min=min(julian)-35, maxDate=max(julian)+35, out="DT", assign.to.envir=FALSE)
  DT.itunes_544 <- DT.itunes_544[, list(date=unlist(dseq)), by=list(month544=month)][, date := as.Date(date, origin=.origin)]
  setkeyIfNot(DT.itunes_544, "date", verbose=FALSE)

  return(DT.itunes_544[.(julian)][["month544"]])
}

itunes_544 <- function(
                        minDate="2011-10-02"
                      , maxDate=Sys.Date()+90
                      , out=c("DT", "SQL", "BOTH")
                      , assign=TRUE, assign.to.envir=TRUE, envir=parent.frame(), assign.nm="DT.iTunes544"
                      , dateCol   = "download_activity_date"
                      , dateColAS = "iTunesMonth"
                      , elseValue
                      , refresh = "always"
                      , prefix_for_sql=NULL
                      , coerce_sql_THEN_value=TRUE
                      , verbose=(out == "SQL")) {

  if (is.character(maxDate) && missing(out) && substr(tolower(maxDate), 1, 1) %in% c("d", "s", "b"))
    stop ("did you accidentally assign the `out` paramter to `maxDate`?")

  if (!missing(refresh))
    message("itunes_544() does not query the database.  No need to set the refresh argument")
  out %<>% toupper
  out <- match.arg(out)

  ## Allow for minDate and maxDate to be Years
  minDate <- convertYYYYtodate(minDate, warn=FALSE)
  maxDate <- convertYYYYtodate(maxDate, warn=FALSE)

  ## Known Start for a 5-4-4 year
  Start <- as.Date("2011-10-02")  # alternative: "2012-09-30"
  End   <- max(Sys.Date()+90, maxDate)  ## take the max of a default and maxDate

  ## How many days in each period.  sum(daysPerPeriod) is how many days in a quarter
  daysPerPeriod <- c(5, 4, 4) * 7
  quarters <- ceiling(c((End-Start+1) / sum(daysPerPeriod)))

  cut_points <- cumsum(rep(daysPerPeriod, times=quarters))
  inds <- mapply(function(s, e) s:e, c(1, head(cut_points+1, -1)), cut_points)
  # sapply(inds, length)

  dates <- as.Date(seq.Date(Start, (End+34), by="day"))
  dates <- lapply(inds, function(ind) dates[ind])

  ## Combine into a DT and remove any NAs
  DT.iTunes544 <- rbindlist(lapply(dates, function(d) data.table(start=min(d), end=max(d), dseq=list(d)))) [!is.na(start)]
  DT.iTunes544[, month := as.Date(format(dseq[[1]][[15]], "%Y-%m-01")), by=start]
  setcolorderpt(DT.iTunes544, c("month", "start", "end"))
  setkey(DT.iTunes544, month)

  if (minDate < DT.iTunes544[, min(start)]) {
    warning ("minDate (",minDate,") is less than the earliest known iTunes accounting start date (",DT.iTunes544[, min(start)],")\n")
  } 

  ## We want to capture the minDate & maxDate, inclusive, so we use the opposite endpoints to filter
  DT.iTunes544 <- DT.iTunes544[end >= minDate & start <= maxDate]

  if (!is.null(prefix_for_sql))
    prefix_for_sql %<>% removeText("\\.$", .) %>% paste0(".")
  clause.dateCol <- paste0(prefix_for_sql, dateCol)

  ## This is the string format that will be used in the srintf
  ## The ifclause simply differentiates whether we should coerce the DATE output from a string to Date, or leave it as is
  if (coerce_sql_THEN_value)  
    clause.when <- paste("   WHEN", clause.dateCol, "between '%s' and '%s' THEN  TO_DATE('%s', 'YYYY-MM-DD')" )
  else
    clause.when <- paste("   WHEN", clause.dateCol, "between '%s' and '%s' THEN '%s'" )

  ## Auto setup the else val
  if (missing(elseValue))
    elseValue <- sprintf("cast(date_trunc('month', %s) as date)", clause.dateCol)

  clause.case <- DT.iTunes544[, paste("CASE\n", pasteC(sprintf(clause.when, start, end, month), C="\n "), "\n  ELSE ", elseValue, "\nEND\nAS ", dateColAS,"\n")]

  if (assign.to.envir)
    assign(assign.nm, DT.iTunes544, envir=envir)

  ret <- if (out == "SQL") clause.case else if (out == "DT") DT.iTunes544 else list(DT=DT.iTunes544, SQL=clause.case)

  if (verbose)
    cat(clause.case)

  if (out == "SQL") {
    warning ("\n-------------------------------------------------------\n\nThe SQL output may be Incomplete --- specifically, make sure it has the prefix (ie the 'FF.____') and that it is missing the TO_DATE(..., 'YY..DD') part\n-------------------------------------------------------\n")
  }

  if (verbose)
    return(invisible(ret))
  return(ret)
}



# TO_CHAR(download_activity_date, 'YYYY-mm-01') as month
##   
##   # --------------
##   #x  ### ___ THIS IS WRONG _____.   See May 2013
##   #x  ## If the 1st is Thurs, Fri or Sat, then The month starts that next Sunday
##   #x  ## Else the month starts that day or the previous sunday
##   #x  DT.iTunes544[, start_calcd := as.Date({.wd <- wday(month); ifelse(.wd >= 5, month + (8 - .wd), month - (.wd - 1) )}, origin=.origin)]
##   #x  DT.iTunes544[(start_calcd != start)]
##   #x  ### ___ THIS IS WRONG _____.   See May 2013
##   # ---------------------
##   
##   
##   
##   # ---------------------
##   ### MOD Formuala
##   w <- 1:52
##   (w-1) %% 13 + 1
##   ## 
##   DT.w <- data.table(w=w[35:52])
##   DT.w[, m13 := (w-1) %% 13 + 1]
##   rep(month.abb[8:12])
##   # ---------------------
##   
##   # --------------
##   ### SQL 
##   SELECT extract('Week' from download_activity_date+1) as Week, 
##   download_activity_date
##   FROM aggregated_analytics
##   WHERE download_activity_date between '2013-09-20' AND '2013-11-15'
##   GROUP BY download_activity_date, Week
##   ORDER BY download_activity_date
##   # --------------
##   
##