.us()
analytics()



lastDateInMonth <- function(x) {
  lubridate::ceiling_date(x+1, unit="month") - 1
}

firstDateInMonth <- function(x) {
  lubridate::floor_date(x, unit="month")
}


iTunesDaysInMonth <- function(x) {
  ## Every 3rd month is 35 days, the rest are 28 days
  ifelse(data.table::month(x) %% 3, 35, 28)
}


## key columns from previous file
kCols     <- c("store", "transac_typeid", "label_sc_group")
kColsDate <- c("date", kCols)


Q.MonthlyAnalytics <- sprintf("
  SELECT  storeid AS store, 
          transac_typeid, 
          label_sc_group,
          download_accounting_month AS date, 
          max(download_activity_date) as max_date, 
          sum(units) as units
   FROM   aggregated_analytics
   WHERE (download_activity_date BETWEEN sysdate - INTERVAL '8 MONTH' AND sysdate)
     %s
     AND NOT labelid is NULL
   GROUP BY 1, 2, 3, 4
   ORDER BY store
  "
 , 
  c(iTunes      = "AND storeid = 1", 
    OtherStores = "AND NOT storeid = 1")
)

## Add names for lapply
setattr(Q.MonthlyAnalytics, "names", c("iTunes", "OtherStores"))

listOfDTs <- lapply(Q.MonthlyAnalytics, runQry)

DT.MonthlyAnalytics <- rbindlist(listOfDTs)

## ----- CLEAN THE DATA ----- ##
suppressWarnings(rm(DT.MonthlyAnalytics.bak))

## Drop any missing label_sc_group
if ("label_sc_group" %in% names(DT.MonthlyAnalytics))
  DT.MonthlyAnalytics <- DT.MonthlyAnalytics[!is.na(label_sc_group)]

## Convert Dates from Strings
dateCols <- c("date", "max_date")
DT.MonthlyAnalytics[, (dateCols) := lapply(.SD, as.Date), .SDcols=dateCols]

## Apply the same max date across the whole store, regardless of group. Bank the old max_date, for inspection at some other time
setnames(DT.MonthlyAnalytics, "max_date", "max_date_bygrp")
DT.MonthlyAnalytics[, max_date := max(max_date_bygrp, na.rm=TRUE), by="store,date"]

## Set keys, prepare for merge
setkeyIfNot(DT.MonthlyAnalytics, kCols)
setcolorderpt(DT.MonthlyAnalytics, c(kCols, "date", "max_date"))
## ----- CLEAN THE DATA ----- ##



##  --------------------------------------------    ##
## We will first add in the itunes start/end months
##    then we will put in the regular start/end months
setkey(DT.iTunes544, "month")
setkeyIfNot(DT.MonthlyAnalytics, kColsDate)
DT.MonthlyAnalytics[DT.iTunes544, `:=`(month_end=i.end, month_start=i.start), allow.cartesian=TRUE]
DT.MonthlyAnalytics[store != 'iTunes', `:=`(month_end=lastDateInMonth(date), month_start=firstDateInMonth(date) )]


DT.MonthlyAnalytics[, days_present := as.numeric(max_date - (month_start-1) )]
DT.MonthlyAnalytics[, days_missing := as.numeric(month_end - max_date)]
## When month is full, we will have max_date == month_end. No (-1) needed.

jesusForData(DT.MonthlyAnalytics)


## Set keys, prepare for merge
setkeyIfNot(DT.MonthlyAnalytics, kColsDate)
setcolorderpt(DT.MonthlyAnalytics, kColsDate)


## Calculate expected units, simple extrapolation by total number of days
DT.MonthlyAnalytics[, units_expected := round(units * (1 + days_missing / days_present))]


  ## Take backup
  if (!exists("DT.MonthlyAnalytics.bak")) {
    DT.MonthlyAnalytics.bak <- copy(DT.MonthlyAnalytics)
  }


## -- FROM BACKUP ---- ##
DT.MonthlyAnalytics <- copy(DT.MonthlyAnalytics.bak)

## Merge in Gross Per Unit
# DT.MonthlyAnalytics[DT.forecasts, `:=`(GPU=GPU) ]

----  Make sure Ran StoreAvgs and StoreAvgs 3


## 
setkeyIfNot(DT.MonthlyAnalytics, kCols)
setkeyIfNot(DT.storeavg, kCols)
DT.MonthlyAnalytics[unique(DT.storeavg), `:=`(GPU_avg_last_4Mo=GPU_avg_last_4Mo), allow.cartesian=TRUE]
## Any Analytics for which we already have accounting data, we have the actual gpu, no need to use averages
DT.MonthlyAnalytics[date <= max(DT.storeavg$date), GPU_avg_last_4Mo := NA_real_]
## Put the key back
setkeyIfNot(DT.MonthlyAnalytics, kColsDate)

jesusForData(DT.MonthlyAnalytics)


++++++++++++++++++++++++++++  LEFT OFF HERE +++++++++++++++++++++++++++++

## some GPU are NA,  fill them with the lowest value per store. 
## if aftwards still missing, throw an error
DT.MonthlyAnalytics[DT.MonthlyAnalytics[, list(.filler=min(GPU, na.rm=TRUE)), by='date,store'], .filler := i..filler]
DT.MonthlyAnalytics[is.na(GPU), GPU := .filler]
DT.MonthlyAnalytics[, .filler := NULL]

## YouTube Movies missing
YT_movies <- DT.MonthlyAnalytics[store=="YouTube Movies", list(transac_typeid, date, GPU)]
if (any(is.na(YT_movies$GPU) | is.infinite(YT_movies$GPU))) {
  YT <- DT.MonthlyAnalytics[store=="YouTube", list(transac_typeid, date, GPU)]
  YT_movies[, GPU := NULL]
  setkey(YT, transac_typeid, date)
  setkey(YT_movies, transac_typeid, date)
  YT_movies <- merge(YT, YT_movies)
  YT_movies[, store := "YouTube Movies"]
  setkey(YT_movies, store, transac_typeid, date)
  keybak <- key(DT.MonthlyAnalytics)
  setkeyv(DT.MonthlyAnalytics, key(YT_movies))
  DT.MonthlyAnalytics[YT_movies, GPU := i.GPU]
  setkeyv(DT.MonthlyAnalytics, keybak)
}


DT.MonthlyAnalytics[, gross_expected := GPU * units_expected]


DT.MonthlyAnalytics[DT.forecasts, `:=`(date=i.date, units_forecasted=i.units) ]

formnumb(DT.MonthlyAnalytics[!is.na(units_forecasted)] [date==as.Date("2014-04-01")], round=-1)

formnumb(DT.storeavg[store=="eMusic"][order(transac_typeid, date)], round=-1)

DT.forecasts


GrossPerUnit