## 20140920 - Used to  feed the  bi.store_view

### 2016-06-15 ---  This might be out of date and no longer necessary.
###                 There is a shorter query in the sql for store_view which covers what this was intended for  (at least I believe it does)


setScienceIfNot(proj="DeNormalizing", subProj="storeroyaltydollar")


lib(data.table, quiet=TRUE)
lib(reshape2,   quiet=TRUE)



## Query against fact_analytics
q.store_royalty_check  <- makeQry(tbl="fact_analytics", schema="production", distinct=TRUE
                            , colsToPull=c("storeid", month=sql1stOfMonth("download_activity_date"), has_royaltydolar="royaltydollar <> 0")
                            , colsToAgg=c("freeunits", "paidunits"), minDate="2014-02-01", dateCol="download_activity_date")

## Execute Query, and set key to store-month
DT.store_royalty_check <- runQry(q.store_royalty_check)
setkeyIfNot(DT.store_royalty_check, storeid, month)


## For some reason there are NAs in the royaltydollar -- WHY????
if (any( is.na(DT.store_royalty_check[["has_royaltydolar"]])  )) {
  warning("There are NAs in the royaltydollar column: ")
  print (DT.store_royalty_check[is.na(has_royaltydolar)])
  DT.store_royalty_check <- DT.store_royalty_check[!is.na(has_royaltydolar)]
}

## Reshape and calculate 
DT.store_uses_royaltydollar <- dcast(DT.store_royalty_check[, list(has_royaltydolar, perc_has_rd_by_paid = paidunits / sum(paidunits)), keyby=list(storeid, month)] , storeid + month ~ has_royaltydolar)


setDT(DT.store_uses_royaltydollar)
cols.rd <- c("no_rd", "has_rd")
setnames(DT.store_uses_royaltydollar, c("FALSE", "TRUE"), cols.rd)
DT.store_uses_royaltydollar[, (cols.rd) := lapply(.SD, removeNA, repl=0), .SDcols=cols.rd]
DT.store_uses_royaltydollar  <- DT.store_uses_royaltydollar[, lapply(.SD, mean), keyby=list(storeid), .SDcols=cols.rd]
{
  if (all(DT.store_uses_royaltydollar[, sapply(.SD, equals0or1), .SDcols=cols.rd]))
    DT.store_uses_royaltydollar[, (cols.rd) := lapply(.SD, as.logical), .SDcols=cols.rd]
  else 
    stop ("The cols.rd of DT.store_uses_royaltydollar are not all 0 or 1")
}
## Confirm only one true at a time per store
{
  if (all(DT.store_uses_royaltydollar[, xor(has_rd, no_rd)]))  {
    DT.store_uses_royaltydollar[, no_rd := NULL]
    setnames(DT.store_uses_royaltydollar, "has_rd", "store_uses_royaltydollar")
  } else {
    stop ("The cols.rd of DT.store_uses_royaltydollar are not mutually exclusive")
  }
}

print(DT.store_uses_royaltydollar[(store_uses_royaltydollar)])

ingestIntoSQL(DT.store_uses_royaltydollar, schema="bi")