# 21 Orchard Spotify Daily.r
lib(ggplot)
lib(reshape2)

.us()
setScience("Spotify_Accounting_ETL", create=TRUE, subl=FALSE, load=FALSE)
# setScience("Spotify_Accounting_ETL", create=TRUE, subl=FALSE, load=TRUE)


colsToAgg <- c("units", "gest")

# ------------------------------ #
DT.stores <- get_dim_store(refresh=FALSE)

DT.orchspot <- runQry(.m(tbl="analytics", schema="bi", 
                   , colsToAgg = colsToAgg
                   , colsToPull = c("activity_date", "label_sc_group", "store_name", "country_code", "transac_type")
                   , minDate = "2014-07-01"
                   , dateCol = "activity_date"
                   , key = "colsToPull"
                  ))
setkeyIfNot(DT.orchspot, setdiff(names(DT.orchspot), colsToAgg), verbose=FALSE)
jesusForData(DT.orchspot)
saveImageTo()
# ------------------------------ #


loadFromJesus("DT.orchspot", over=TRUE)

## A lot of data still not in.  Crop the tail
DT.orchspot <- DT.orchspot[activity_date < monthFloor(prevMonth(today()))]

include <- c("activity_date", "label_sc_group")
DT.orchspot.bytrans   <- aggregateDT(DT.orchspot[store_name == "Spotify"], by=c(include, "transac_type"), colsToAgg=colsToAgg)
DT.orchspot.bycountry <- aggregateDT(DT.orchspot[store_name == "Spotify"], by=c(include, "country_code"), colsToAgg=colsToAgg)
DT.orchspot.bystore   <- aggregateDT(DT.orchspot[store_name == "Spotify"], by=c(include, "store_name"), colsToAgg=colsToAgg)
DT.orchspot.aggd      <- aggregateDT(DT.orchspot[store_name == "Spotify"], by=c(include), colsToAgg=colsToAgg)


## Choose a DT.plot

## --------------------------- ##
##     By TransactionType      ##
## --------------------------- ##
DT.plot <- copy(DT.orchspot.bytrans)

DT.plot[, distributor := factor(ifelse(label_sc_group %in% c("Orchard", "RED"), label_sc_group, "Other SC"), levels = c("Orchard", "RED", "Other SC"))]

Plots.Spotify_units_by_trans <- {
  lapply(unique(DT.plot$transac_type), function(trans)
    ggLinegraph(DT=DT.plot[transac_type == trans], x="activity_date", y="units", color="label_sc_group", facet_formula = "distributor ~ .", facet_scales="free_y", size=.5, dotsize=1.25, title=trans) + color_by_dict("dict.colors.sc") + nolegend()
  )
}

f.out.units_by_trans <- printToPDF(Plots.Spotify_units_by_trans, height=5, main="Activity in OA Analytics across all stores & countries", sub=orchardFootNote())

## ----------------------- ##
##     By Country          ##
## ----------------------- ##
DT.plot <- copy(DT.orchspot.bycountry)
DT.plot <- DT.plot[country_code %in% DT.plot[, mean(units) > 2e5, keyby=country_code][(V1), country_code]]

## Convert store_name to factor
if ("store_name" %in% names(DT.plot))
  makeStoresFactor(DT.plot)
DT.plot[, distributor := factor(ifelse(label_sc_group %in% c("Orchard", "RED"), label_sc_group, "Other SC"), levels = c("Orchard", "RED", "Other SC"))]
## Add country information
addCountry.byCode_(DT.plot)

Plots.Spotify_units_by_country <- {
  lapply(unique(DT.plot$country_name), function(country)
    ggLinegraph(DT=DT.plot[country_name == country], x="activity_date", y="units", color="label_sc_group", facet_formula = "distributor ~ .", facet_scales="free_y", size=.5, dotsize=1.25, title=country) + color_by_dict("dict.colors.sc") + nolegend()
  )
}

f.out.units_by_country <- printToPDF(Plots.Spotify_units_by_country, height=5, main="Activity in OA Analytics across all stores & transaction types", sub=orchardFootNote())


## ----------------------- ##
##     By Store            ##
## ----------------------- ##
DT.plot <- copy(DT.orchspot.bystore)

## Convert store_name to factor
if ("store_name" %in% names(DT.plot))
  makeStoresFactor(DT.plot)
DT.plot[, distributor := factor(ifelse(label_sc_group %in% c("Orchard", "RED"), label_sc_group, "Other SC"), levels = c("Orchard", "RED", "Other SC"))]


Plots.Spotify_units_by_store <- {
  lapply(levels(DT.plot$store_name), function(store)
    ggLinegraph(DT=DT.plot[store_name == store], x="activity_date", y="units", color="label_sc_group", facet_formula = "distributor ~ .", facet_scales="free_y", size=.5, dotsize=1.25, title=store) + color_by_dict("dict.colors.sc") + nolegend()
  )
}

f.out.units_by_store <- printToPDF(Plots.Spotify_units_by_store, height=5, main="Activity in OA Analytics across all countries & transaction types", sub=orchardFootNote())

## ----------------------- ##
##     Overall             ##
## ----------------------- ##

DT.plot <- copy(DT.orchspot.aggd)

DT.plot[, distributor := factor(ifelse(label_sc_group %in% c("Orchard", "RED"), label_sc_group, "Other SC"), levels = c("Orchard", "RED", "Other SC"))]
DT.plot[, distributor := factor(label_sc_group, levels = c("Orchard", "RED", "SelectO", "Allegro"))]

P.Spotify_units_overall <- 
  ggLinegraph(DT=DT.plot, x="activity_date", y="units", color="label_sc_group", facet_formula = "distributor ~ .", facet_scales="free_y", size=.5, dotsize=1.25, yscale="millions", title = "Aggregated across all OA Analytics")+ color_by_dict("dict.colors.sc", "Supply Chain Group") + nolegend()

f.out.OverAll <- printToPDF(P.Spotify_units_overall, height=5, sub=orchardFootNote())

## ----------------------- ##



Now repeat, but for JUST spotify




## SCRATCH
## ---

### SINGLE 
P <- 
  ggLinegraph(DT=DT.plot, x="activity_date", y="units", color="label_sc_group", facet_formula = "distributor ~ .", facet_scales="free_y", size=.5, dotsize=1.25, yscale="millions", title = "Activity across all stores in OA Analytics", vline="2014-09-15", vline_alpha=.13)+ color_by_dict("dict.colors.sc", "Supply Chain Group") + nolegend()