# accounting.r

con <- getDB(drv.str="MySQL", verbose=verbose)

setScience("Accounting", subl=FALSE)



loadFromJesus("~/git/orch/data/iradio/DT.accounting-20140305_1750-687x6.RDS")
loadFromJesus("~/git/orch/data/iradio/DT.itunes.uniqueusers.monthly-20140305_1752-17x4.RDS")
loadFromJesus("~/git/orch/data/iradio/DT.spotify.uniqueusers.monthly-20140305_1752-16x4.RDS")
loadFromJesus( "~/git/orch/data/Accounting/DT.monthly.streams-20140306_1504-34x6.RDS")
loadFromJesus( "~/git/orch/data/Accounting/DT.sales-20140306_1840-29x5.RDS", over=TRUE)



lsos(DT)

# ----- # 
###
#  DT.sales   :=  The sales figures per kcols
#  DT.streams := The number of streams per kcols
#  DT.users   := The number of unique users per kcols

keyCols <- c("storename", "year", "month")
keyCols.class <- c("character", "integer", "integer")
storesUsing <- c("itunes", "spotify")

###  DT.sales

DT.accounting[, storename := tolower  (storename)]
DT.accounting[, year := as.numeric(year)]
DT.accounting[, month := as.numeric(month)]
DT.sales <- setkeyv(DT.accounting[storename %in% storesUsing], keyCols)


#### DT.users
DT.itunes.uniqueusers.monthly [, dataSource := gsub("staging_raw_", "", dataSource)]
DT.spotify.uniqueusers.monthly[, dataSource := gsub("staging_raw_", "", dataSource)]
setnames(DT.itunes.uniqueusers.monthly, "dataSource", "storename")
setnames(DT.spotify.uniqueusers.monthly, "dataSource", "storename")

DT.users <- setkeyv(rbind(DT.spotify.uniqueusers.monthly, DT.itunes.uniqueusers.monthly), keyCols)


#### DT.streams
DT.streams <- DT.monthly.streams[, storename := tolower  (storename)]
setkeyv(DT.streams, keyCols)


##### ADD IN SALES TO EACH

coerceKeyColsAndSetKey_(DT.sales, keyCols=keyCols, classCols=keyCols.class)
coerceKeyColsAndSetKey_(DT.accounting, keyCols=keyCols, classCols=keyCols.class)
coerceKeyColsAndSetKey_(DT.users, keyCols=keyCols, classCols=keyCols.class)

DT.merged <- 
  DT.sales[  DT.accounting  ]  [  DT.users ]

salesCols <- c("sales", "gross", "adjusted_gross")
DT.merged[, paste0(salesCols, "_per_user") := lapply(.SD[, salesCols, with=FALSE], "/", unique_users), .SDcols=c(salesCols, "unique_users")]
setcolorderpt(DT.merged, c(keyCols, "unique_users"))
DT.merged

## Streams

DT.merged <- DT.merged [DT.monthly.streams]






1:                  DT.accounting 39.96 KB data.table  687       6 (<not set>)
2:  DT.itunes.uniqueusers.monthly  2.39 KB data.table   17       4 year, month
3: DT.spotify.uniqueusers.monthly  2.25 KB data.table   16       4 year, month