## Spotify_concile.r


"This file ingests the Excel file Lee sent me after the phone meeting with him and Pras and we realized that the numbers he has for Spotify are different from what I have. "




lib("XLConnect")

setScience(proj="dbconcile", create=TRUE, subl=FALSE, load=FALSE)

f <- f.spotify_ASR <- ingest.p("Spotify Streams Analysis_28.Aug.14 V6.xlsx")

if (!file.exists(f.spotify_ASR))
  stop("f.spotify_ASR does not exist")


BackUpOrRestore("DT.Spot_ASR_Revenue", clear=TRUE)
BackUpOrRestore("DT.Spot_ASR_Streams", clear=TRUE)
BackUpOrRestore("DT.Spot_ASR_GPU", clear=TRUE)

XLStoDT(f.spotify_ASR, nm.prefix="DT.Spot_ASR_", startCol=1, startRow=1, overwrite=TRUE, sheetAsCol=FALSE)

## clean up POSIX values in colnames
DT.Spot_ASR_ColNames[, names(DT.Spot_ASR_ColNames) := lapply(.SD, function(x) ifelse(grepl("00:00:00", x), yes=as.character(format(as.POSIXct(x, format="%Y-%m-%d %H:%M:%S", origin=.origin.utc), format="%Y-%m-%d")), no=x))]

## apply colnames
{
  for (nm in names(DT.Spot_ASR_ColNames))
    setnames(get(sprintf("DT.Spot_ASR_%s", nm)), DT.Spot_ASR_ColNames[[nm]])
  ## if succesfull, drop colnames
  if (identical(names(DT.Spot_ASR_Revenue), DT.Spot_ASR_ColNames[["Revenue"]]))
    rm(DT.Spot_ASR_ColNames)
}

## Fix Self Reference
DT.Spot_ASR_Revenue <- copy(DT.Spot_ASR_Revenue)
DT.Spot_ASR_Streams <- copy(DT.Spot_ASR_Streams)
DT.Spot_ASR_GPU     <- copy(DT.Spot_ASR_GPU)


BackUpOrRestore("DT.Spot_ASR_Revenue")
BackUpOrRestore("DT.Spot_ASR_Streams")
BackUpOrRestore("DT.Spot_ASR_GPU")

## Fix Groupings
spot.cleanGrouping_(DT.Spot_ASR_Revenue)
spot.cleanGrouping_(DT.Spot_ASR_Streams)
spot.cleanGrouping_(DT.Spot_ASR_GPU)


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

qry.anal.monthly <- 
  makeQry(tbl="fact_analytics"
        , colsToPull=c(month=sql1stOfMonth("download_activity_date"), "transactiontypeid")
        , colsToAgg=c("units", "paidunits", "freeunits")
        , aggFunc="sum"
        , where = list(storeid = 286)
        , minDate = '2014-01-01'
        , dateCol = "download_activity_date"
        , schema="production"
        )

qry.anal.daily <- 
  makeQry(tbl="fact_analytics"
        , colsToPull=c(date="download_activity_date", "transactiontypeid")
        , colsToAgg=c("units", "paidunits", "freeunits")
        , aggFunc="sum"
        , where = list(storeid = 286)
        , minDate = '2014-01-01'
        , dateCol = "download_activity_date"
        , schema="production"
        )

qry.acc.monthly <- 
  makeQry(tbl="fact_sales"
        , colsToPull=c("accountingmonth", "accountingperiodid", "activityperiodid", "transactiontypeid")
        , colsToAgg=c(units="sales", "gross")
        , aggFunc="sum"
        , where = list(storeid = 286)
        , minDate = 181
        , dateCol = "accountingperiodid"
        , schema="production"
        )

setDBall(cluster=9)

DT.anal.monthly <- runQry(qry.anal.monthly)
DT.anal.daily   <- runQry(qry.anal.daily)
DT.acc.monthly  <- runQry(qry.acc.monthly)

jesusForData(DT.anal.monthly)
jesusForData(DT.anal.daily)
jesusForData(DT.acc.monthly)

addDateCols.periodid_(DT.acc.monthly, refresh=TRUE)


file.img <- saveImageTo()
msg.email <- sprintf("file for dbconcile: \n    bringme(\"%s\")     \n", file.img)
EmailStatusUpdate(to="rsaporta@gmail.com", msg=msg.email)
print(file.img)
cat(msg.email, "\n")