# 01 Revenue_by_store.r

setScience("Accounting", subl=FALSE, create=FALSE)


## This is what I use to generate the named vector.  Just a console-function.
if (FALSE) {
  storeNameVector(c(1, 286, 187, 348, 424, 497))
}

storesUsing <- c('iTunes'=1, 'Amazon Digital Services Inc.'=187, 'Spotify'=286
                , 'deezer'=348, 'rDio'=424, 'Cricket Communications, Inc.'=497)
keyCols <- c("storename", "year", "month")


Q <- 
sprintf(
"   SELECT s.storename AS storename, year, month, sum(gross) AS revenue, sum(sales) AS units
   FROM   fact_sales fs
   LEFT JOIN dim_store s ON fs.storeid=s.storeid
   LEFT JOIN (%s) p ON fs.activityPeriodId=p.periodid
   WHERE  fs.storeid IN %s
     AND  activityPeriodId >= (%s) 
     AND  activityPeriodId <= (%s) 
   GROUP BY storename, year, month
   ORDER BY storename, year, month"

   , YMfromPeriod()
   , pasteQ(storesUsing, q="")
   , PeriodbyYM.qry("2012-09-01")
   , PeriodbyYM.qry("2014-03-01")
)


## Pull the data
DT.Sales.byStore <- runQry(Q)

## Clean up the store name
DT.Sales.byStore[, storename := tolower( strsplit(storename, " ")[[c(1,1)]] ), by=storename]

## Set Keys
setkeyIfNot(DT.Sales.byStore, keyCols)