# accounting_new.r

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)



# ----- # 
#  DT.sales   :=  The gross & UNITS figures 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.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)
setcolorderpt(DT.users, keyCols)


## Straight from the SQL ready to go, except for the capitalization
DT.sales[, storename := tolower(storename)]

## SET KEYS AND COLUMN CLASS
coerceKeyColsAndSetKey_(DT.sales, keyCols=keyCols, classCols=keyCols.class)
coerceKeyColsAndSetKey_(DT.users, keyCols=keyCols, classCols=keyCols.class)


DT.merged <- merge(DT.users, DT.sales, all=TRUE)

DT.merged[, gross_per_unit := gross / total_units]
DT.merged[, gross_per_user := gross / unique_users]
DT.merged[, avg_units_per_user := total_units / unique_users]
## ADD number of trackas
## Average Track per user