.us()
lib(forecast)

setScience(proj="chartio", create=TRUE, subl=FALSE)

Q.storeavg <- 
"SELECT storeid as store, 
        TRUNC(accounting_date) AS Date, 
        transac_typeid, 
        label_sc_group,
        sum(units) as units, 
        sum(gross) as gross

 FROM   aggregated_accounting
 WHERE (accounting_date BETWEEN sysdate - INTERVAL '12 MONTH' AND sysdate)
 GROUP BY 1, 2, 3, 4
 ORDER BY store" 


DT.storeavg <- runQry(Q.storeavg)

DT.storeavg[, GPU := gross / units]
kCols     <- c("store", "transac_typeid", "label_sc_group")
kColsDate <- c("date", kCols)
setkeyIfNot(DT.storeavg, kColsDate)
setcolorderpt(DT.storeavg, kColsDate)

-----   RUN  'GPU Calc.r'


We should now have a  
 

   DT.GPU.Est




NEXT BRING IN DT.MonthlyAnalytics

           date store transac_typeid label_sc_group GPU_estimate     units days_present days_missing units_expected
  1: 2014-04-01     1              4        Allegro    0.9580212      8074           28            2           8651
  2: 2014-04-01     1              4        Orchard    0.8479259       204           28            2            219
  3: 2014-04-01     1              4            RED    1.0265900      1953           28            2           2092
  4: 2014-04-01     1              4        SelectO    0.9596327     10282           28            2          11016
  5: 2014-04-01     1              7        Allegro    0.2000000    680205           28            2         728791
640: 2014-02-01   571              1            RED    0.0338904         2           13           18              5
641: 2014-02-01   571              1        SelectO    0.0330373    145774           17           14         265823
642: 2014-01-01   573             19        Orchard    2.4137842  34687205           17           14       63253139
643: 2014-01-01   573             23        Orchard   13.5220775   4695749           17           14        8562836
644: 2014-02-01   578             29        Orchard    5.7628697   3392039           17           14        6185483


setkeyIfNot(DT.MonthlyAnalytics, kColsDate)
setkeyIfNot(DT.GPU.Est,          kColsDate)

DT.GPU.Est[DT.MonthlyAnalytics, `:=`(units=units, units_expected=units_expected, days_present=days_present, days_missing=days_missing)]

DT.GPU.Est[.(as.Date("2014-02-01"))]


### --- READY TO WRITE ------ ###
DT.GPU.Est[, date_last_calculated := Sys.time()]







-----     The rest is just scratch...... -----


## Average of last 4 months -- note, already ordered by date and store
DT.storeavg[, GPU_avg_last_4Mo := mean(tail(GPU, 3), na.rm=TRUE), by="store,transac_typeid,label_sc_group"]
DT.storeavg[, gpu_check := GPU_avg_last_4Mo]
for (D in head(sort(unique(DT.storeavg$date), decreasing=TRUE), -3) )
  DT.storeavg[!.(D), gpu_check := mean(tail(GPU, 3), na.rm=TRUE), by="store,transac_typeid,label_sc_group"]

DT.storeavg[, max(gross), by=store][order(V1), head(store, 10)]
checks <- DT.storeavg[, round(mean((gpu_check - GPU)/ GPU, na.rm=TRUE), 2), keyby='date,store']


## Dates
n <- 3
minDate <- DT.storeavg[, min(date, na.rm=TRUE)]
maxDate <- DT.storeavg[, max(date, na.rm=TRUE)]
futureDates <- seq(maxDate, length.out=n, by="month") 

# NonDates <- unique(DT.storeavg[, kCols, with=FALSE], by=NULL)
# DT.futuredates <- setkey(rbindlist(lapply(futureDates, data.table, NonDates)))
# DT.storeavg[DT.futuredates]

DT.forecasts <- DT.storeavg[date < min(futureDates), list(date=futureDates, units=c(forecast(units, n)$mean), gross=c(forecast(gross, n)$mean)), by=kCols]
setkeyIfNot(DT.forecasts, key(DT.storeavg))
setcolorderpt(DT.forecasts, intersect(names(DT.storeavg), names(DT.forecasts)))

DT.forecasts[, GPU := gross / units]
DT.forecasts[store=="iTunes"][order(transac_typeid)]

GrossPerUnit <- DT.forecasts[.(futureDates[1L])][, list(store_transac = paste0(store, "-", transac_typeid, "-", ), GPU)]
DT.forecasts[, apply(.SD, 1, pasteC, C="-"), .SDcols=kCols]


f.out <- as.path(outDir, "GrossPerUnit", ext="csv")
write.csv(GrossPerUnit, file=f.out, row.names=FALSE)

f.out