## GL - CHECK 2.r

## Need to calculate the monthly total for allegro and selecto

.g(); .us()

TODO: Fix makeQry when no WHERE clause included.  Add limit.   change dateMin to minDate

debugOff("Qry")
debugOn("Qry")
dateCol <- "activity_month"
dateCol <- "accounting_month"
Q <- makeQry("aggregateda_accounting", c("accounting_month", "activity_month", "label_sc_group"), colsToSum=c("gross"), minDate="2014-01-01", dateCol="accounting_month", where="label_sc_group in ('Allegro', 'SelectO')" )
DT.allegro <- runQry(Q)

# setnames(DT.allegro, "activity_month", "date")
# setkeyIfNot(DT.allegro, date, label_sc_group)

DT.allegro[, accounting_month := as.Date(accounting_month)]
DT.allegro[, activity_month   := as.Date(activity_month)]

DT.allegro.activity_month   <- dcast.data.table(DT.allegro[activity_month >= "2014-01-01",   sum(gross), keyby=list(Service=label_sc_group, date=format(activity_month, "%B '%g"))],   Service ~ date, value.var="V1")
DT.allegro.accounting_month <- dcast.data.table(DT.allegro[accounting_month >= "2014-01-01", sum(gross), keyby=list(Service=label_sc_group, date=format(accounting_month, "%B '%g"))], Service ~ date, value.var="V1")

setcolorderpt(DT.allegro.activity_month, c("Service", unlist(sapply(month.name, extract, names(DT.allegro.activity_month)))))
setcolorderpt(DT.allegro.accounting_month, c("Service", unlist(sapply(month.name, extract, names(DT.allegro.accounting_month)))))

DT.allegro.activity_month  <- rbind(DT.allegro.activity_month, DT.allegro.activity_month[, lapply(.SD, sum), by=list(Service=rep("TOTAL", length(Service)))])
DT.allegro.accounting_month  <- rbind(DT.allegro.accounting_month, DT.allegro.accounting_month[, lapply(.SD, sum), by=list(Service=rep("TOTAL", length(Service)))])


{
  cat("\n\n")
  cat("\t  ~=~=~=~= TOTAL BY ACTIVITY MONTH =~=~=~=~\n")
  print(DT.allegro.activity_month, row.names=FALSE)
  cat("\n\n")
  cat("\t  ~=~=~=~= TOTAL BY ACCOUNTING MONTH =~=~=~=~\n")
  print(DT.allegro.accounting_month, row.names=FALSE)
  cat("\n\n")
}