# katie

## genreid == 12 is Classical

setScience("misc", subProj="KatieGB")
setGitBranchToSystem()
.g()

tbl <- "fact_analytics"
whereIn <- list(countryid = 3, storeid = '286', genreid = 12)
minDate <- "2015-01-01"
dateCol <- "download_activity_date"

colsToPull=c("trackid", "artistid", "labelid", "transactiontypeid")
colsToAgg = "units" # c(".RU")  ## no revenue information in spotify
colsWithaggFunc=c(users_number_of = "COUNT(distinct storeuserid)")

DT.top_spotify_classical_tracks_in_2015 <- makeQry(tbl=tbl, colsWithaggFunc=colsWithaggFunc, colsToAgg=colsToAgg, colsToPull=colsToPull, minDate=minDate, dateCol=dateCol, whereIn=whereIn) %>% runQry
BackUpOrRestore("DT.top_spotify_classical_tracks_in_2015")

if (FALSE) {
  DT.crop <- copy(DT.top_spotify_classical_tracks_in_2015[1:20])
  addAllMeta_(DT.crop, label_priority=TRUE, label_country=TRUE)
}

addAllMeta_(DT.top_spotify_classical_tracks_in_2015, label_priority=TRUE, label_country=TRUE)
DT.top_spotify_classical_tracks_in_2015[, country_code := "GB"]
setcolorderpt(DT.top_spotify_classical_tracks_in_2015, "country_code")
DT.top_spotify_classical_tracks_in_2015[, upc := as.idcol(upc)]

# addLabelName.labelid_(DT.top_spotify_classical_tracks_in_2015)

jesusForData(DT.top_spotify_classical_tracks_in_2015)
try({DT.top_spotify_classical_tracks_in_2015 <- DT.top_spotify_classical_tracks_in_2015[order(units, transac_type_abbr, decreasing=TRUE)]})


## AGGREGATE OUT Transact
DT.top_spotify_classical_tracks_in_2015_OVERALL <- aggregateDT(DT.top_spotify_classical_tracks_in_2015, exclude=c("transac_type_abbr", "transac_type", "users_number_of"), colsToAgg=c("revenue", "units"))

DT.top_spotify_classical_tracks_in_2015_OVERALL <- DT.top_spotify_classical_tracks_in_2015_OVERALL[order(units, decreasing=TRUE)]
DT.top_spotify_classical_tracks_in_2015_OVERALL[, revenue := NULL][1:5000]

DT.top_spotify_classical_tracks_in_2015_PREMIUM <- DT.top_spotify_classical_tracks_in_2015[transac_type_abbr=='S'][order(units, decreasing=TRUE)][, revenue := NULL][, users_number_of := NULL][1:5000]

DT.top_spotify_classical_tracks_in_2015_ADSupported <- DT.top_spotify_classical_tracks_in_2015[transac_type_abbr=='AS'][order(units, decreasing=TRUE)][, revenue := NULL][, users_number_of := NULL][1:5000]


f.out_overall <- writeDT(DT.top_spotify_classical_tracks_in_2015_OVERALL, to=c("Katie Ferguson <kferguson@theorchard.com>", "Tom Brown <tbrown@theorchard.com>"))
f.out_PREMIUM <- writeDT(DT.top_spotify_classical_tracks_in_2015_PREMIUM, to=c("Katie Ferguson <kferguson@theorchard.com>", "Tom Brown <tbrown@theorchard.com>"), cc="dataRequests@theorchard.com")
f.out_ADSupported <- writeDT(DT.top_spotify_classical_tracks_in_2015_ADSupported, to=c("Katie Ferguson <kferguson@theorchard.com>", "Tom Brown <tbrown@theorchard.com>"), cc="dataRequests@theorchard.com")


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

2015-07-15 -- Double check
--------------------------
setDBall(cluster=4)
whereIn <- list(storeid=286, isrcid=15224753, transactiontypeid=10, countryid=3)
maxDate <- as.Date("2015-07-05")

DT.check_fact_sales_by_accounting_period <- runQry(makeQry("fact_sales", colsToPull=c("ISRCID", "releaseid", "trackid", "transactiontypeid", "artistid"), colsToAgg=".RU", where=whereIn, minDate="2015-01-01", maxDate=maxDate, dateCol="activityperiodid"))

DT.check_fact_analytics <- runQry(makeQry("fact_analytics", colsToPull=c("ISRCID", "releaseid", "trackid", "transactiontypeid", "artistid"), colsToAgg=".RU", where=whereIn, minDate="2015-01-01", maxDate=maxDate, dateCol="download_activity_date"))


DT.check_fact_sales_by_accounting_period
DT.check_fact_analytics

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