# Accounting_vs_Analytics_2015.r

cluster.using <- 09

.us()


setScience("Acc_vs_Anal_2015", create=TRUE, load=FALSE, subl=FALSE)

## Cols to pull from both tables
addlCols <- c("storeid", "transac_type_abbr", "label_sc_group")
# addlCols <- c("transac_type_abbr", setdiff(kCols.splitgroup, "date"))
minDate <- as.Date("2013-01-01")
kCols.main <- c("date", addlCols)

## Analytics
BackUpOrRestore("DT.bi_anal_totalpaidunits", clear=TRUE)
dateCol.anal <- "accounting_month"
DT.bi_anal_totalpaidunits <- runQry(makeQry(tbl="analytics", schema="bi", colsToPull=c(dateCol.anal, addlCols), colsToAgg=c(revenue="royaltydollar", "paidunits"), minDate=minDate, dateCol=dateCol.anal), cluster=cluster.using)
BackUpOrRestore("DT.bi_anal_totalpaidunits")

DT.bi_anal_totalpaidunits[, date := lubridate::floor_date(get(dateCol.anal), "month")]
DT.bi_anal_totalpaidunits[, (dateCol.anal) := NULL]
DT.bi_anal_totalpaidunits[, store_uses_rd := storeid %in% DT.bi_anal_totalpaidunits[, all(revenue == 0 | is.na(revenue)), by=storeid][(!V1), storeid]]
DT.bi_anal_totalpaidunits[!(store_uses_rd), revenue := NA]
DT.bi_anal_totalpaidunits[, store_uses_rd := NULL]

## Accounting
BackUpOrRestore("DT.bi_acc_totalpaidunits", clear=TRUE)
dateCol.acc <- "activity_month"
DT.bi_acc_totalpaidunits <- runQry(makeQry(tbl="accounting", schema="bi", colsToPull=c(dateCol.acc, addlCols), colsToAgg=c(revenue="gross", paidunits = "units"), minDate=minDate, dateCol=dateCol.acc, where=list(storeid = DT.bi_anal_totalpaidunits[, unique(storeid)])), cluster=cluster.using)
BackUpOrRestore("DT.bi_acc_totalpaidunits")
{
  if ("accounting_month" %in% names(DT.bi_acc_totalpaidunits))
    stop ("How do you want to handle accounting_month")
    # DT.bi_acc_totalpaidunits[, periodid := accountingperiodid - 1]
  else if ("activity_month" %in% names(DT.bi_acc_totalpaidunits))
    setnames(DT.bi_acc_totalpaidunits, "activity_month", "date")
}
# {
#   if ("accountingperiodid" %in% names(DT.bi_acc_totalpaidunits))
#     DT.bi_acc_totalpaidunits[, periodid := accountingperiodid - 1]
#   else if ("activityperiodid" %in% names(DT.bi_acc_totalpaidunits))
#     setnames(DT.bi_acc_totalpaidunits, "activityperiodid", "periodid")
#   addDateCols.periodid_(DT.bi_acc_totalpaidunits, drop=TRUE, showWarnings=FALSE)
# }

## Match, Aggregate, and Merge
matchKey(DT.bi_acc_totalpaidunits, DT.bi_anal_totalpaidunits, key=kCols.main, organize=TRUE)
DT.bi_anal_totalpaidunits <- DT.bi_anal_totalpaidunits[, lapply(.SD, sumn), by=key(DT.bi_anal_totalpaidunits)]
DT.bi_acc_v_anal <- merge(DT.bi_acc_totalpaidunits, DT.bi_anal_totalpaidunits, suffix=c(".acc", ".anal"))

DT.bi_acc_totalpaidunits[storeid ==1  & date == d][!DT.bi_anal_totalpaidunits]
DT.bi_acc_totalpaidunits[storeid ==1  & date == d]
DT.bi_anal_totalpaidunits[storeid ==1  & date == d]

## Organize the col order
setcolorderpt(DT.bi_acc_v_anal, sort(names(DT.bi_acc_v_anal)))
setcolorderpt(DT.bi_acc_v_anal, c(key(DT.bi_acc_v_anal), "accountingdate"), showWarnings=FALSE)

## Compare
DT.bi_acc_v_anal[, paidunits.anal_to_acc := paidunits.anal / paidunits.acc]
DT.bi_acc_v_anal[, revenue.anal_to_acc   := revenue.anal   / revenue.acc]

### PLOTTING STUFF ... 
if (FALSE)
{
  minDateShow <- '2013-07-01'
  formnumb(suppressWarnings(DT.bi_acc_v_anal[storeid == 1 & date >= minDateShow][, storeid := NULL][ , c("accountingdate", "activitydate") := NULL][, paidunits.anal_to_acc := fwp(paidunits.anal_to_acc)][, revenue.anal_to_acc := fwp(revenue.anal_to_acc) ]))

  DT.bi_acc_v_anal.molt <- melt(DT.bi_acc_v_anal, id.var=key(DT.bi_acc_v_anal), variable.name = "metric")
  addTransacInfo_(DT.bi_acc_v_anal.molt, description=TRUE)
  DT.bi_acc_v_anal.molt


  DT.plot.itunes <- DT.bi_acc_v_anal.molt[storeid == 1 & grepl("anal_to_acc", metric) & date >= minDateShow & transactiontypeid %ni% c(30, 7)]
  DT.plot.itunes[, metric := topropper(gsub("\\.anal_to_acc", "", metric))]
  ggLinegraph(DT.plot.itunes, x="date", y="value", color="metric", yscale = "percent", alpha=1, facet="transac_type~.", xlab="", ylab="Analytics as percentage of accounting\n(matched by activity date)", thickness=.8, dot_alpha=.5) + legendbottom(notitle=TRUE) + geom_hline(y=1, alpha=.25)


}