# -------------------------------------------------------------------------------------------------------------------------  #
  #  -----------------------------------------------------------------------------------------------------------------------   #
  #                                                                                                                            #
  #           File Name              :  mbworkbook.r                                                                           #
  #           Last Updated Funclist  :  16 Feb 2015,  1:56 AM (Monday)                                                         #
  #                                                                                                                            #
  #           Author Name            :  Rick Saporta                                                                           #
  #           Author Email           :  RSaporta@TheOrchard.com                                                                #
  #           Author URL             :  www.github.com/rsaporta                                                                #
  #                                                                                                                            #
  #           Packages Called        :  NA                                                                                     #
  #           Packages Used via NS   :  NA                                                                                     #
  #                                                                                                                            #
  #  -----------------------------------------------------------------------------------------------------------------------   #
  #                                                                                                                            #
  #   addRevenueFromWorksheet_ ( DT, dateCol.DT=getDateCol(DT, guess.if_null=TRUE), minDate=min(DT[[dateCol.DT]])              #
  #                              , colToSplitBy="revenue.acc" )                                                                #
  #   get_mbworkbook           ( minDate="2013-01-01"                                                                          #
  #                              , maxDate.expected=prevMonth(monthFloor(today() - 5), 2), refresh=TRUE                        #
  #                              , showWarnings=TRUE, envir.load=globalenv() )                                                 #
  #                                                                                                                            #
  #                                                                                                                            #
  #                                                     <END FUNCS>                                                            #
  #  -----------------------------------------------------------------------------------------------------------------------   #
  # -------------------------------------------------------------------------------------------------------------------------  #


addRevenueFromWorksheet_ <- function(DT, dateCol.DT=getDateCol(DT, guess.if_null=TRUE), minDate=min(DT[[dateCol.DT]]), colToSplitBy = "revenue.acc" ) {
## colToSplitBy :: a column in DT that will be usd to talley the percentages with which to split revenue.asr 

  kCols.datestore <- c(dateCol.DT, "storeid")

  if ("storeid" %ni% names(DT)) 
    stop ("DT must contain a storeid column")
  ## If there are any duplicate rows by kCols and the colToSplitBy is not in DT, that is an error
  if (any(duplicated(DT, by=kCols.datestore)) && colToSplitBy %ni% names(DT))
    stop ("colToSplitBy = '", colToSplitBy, "' is not a column of the DT")
  if ("revenue.asr" %in% names(DT))
    stop ("'revenue.asr' is already a column in the DT. Manually drop it in order to proceed")


  ## Pull the mbworkbook and modify it slightly. Namely, the storeid is a string. Change it
  DT.mbworkbook <- get_mbworkbook(refresh=TRUE, minDate=minDate)
  dateCol.mb    <- getDateCol(DT.mbworkbook, value.if_null="accounting_month")

  ## Modify slightly
  DT.mbworkbook <- copy(DT.mbworkbook)[, storeid := as.integer(as.num.nowarn(storeid))]
  DT.mbworkbook <- DT.mbworkbook[!is.na(storeid)]

  ## dateCol from DT.mbworkbook
  addColsFrom_(DT, DT.mbworkbook, colsToBring=c("revenue.asr"="statement_thismonth"), joinCols.r=kCols.datestore, joinCols.g=c(dateCol.mb, "storeid"))

  ## If there are any groups where there is more than one store.... 
  ## ... then split according to the percOfTotal
  if (any(DT[, .N, by=kCols.datestore][, N > 0])) {
    DT[, revenue.asr.TotalForMonth := revenue.asr]
    DT[, revenue.asr := revenue.asr * percOfTotal(get(colToSplitBy)), by=kCols.datestore]

    ## The last date might be an issue of the accounting run didnt populate yet
    max_accounting_run_date <- DT[!is.na(get(colToSplitBy)), maxn(get(dateCol.DT))]
    max_mbworkbook_date     <- DT.mbworkbook[, maxn(accounting_month)]
    if (any(is.na(DT[get(dateCol.DT) <= max_mbworkbook_date, revenue.asr]))) {
      warning ("TODO -- max date split")
      tmp_DT.final_perc <- DT[(get(dateCol.DT) == prevMonth(max_accounting_run_date)), list(transid, perc = percOfTotal(get(colToSplitBy))), by=kCols.datestore]
      while (tmp_DT.final_perc[1, date] <= max_mbworkbook_date) {
        tmp_DT.final_perc[, date := nextMonth(date)]
        setkeyIfNot(tmp_DT.final_perc, verbose=FALSE)
        DT[tmp_DT.final_perc, revenue.asr := revenue.asr.TotalForMonth * i.perc]
      }
    }

    ## < SEE  > 
    DT[date >= "2014-06-01"]
    ## CONFIRM: The sum of revenue.asr by month-store equals the revenue.asr.TotalForMonth value
    stopifnot(DT[date >= minn(DT.mbworkbook[[dateCol.mb]]) & date <= max_mbworkbook_date, equals(revenue.asr.TotalForMonth[[1]], sumn(revenue.asr), na.check=FALSE), by=kCols.datestore][, V1])
  }
  return(DT)
}


get_mbworkbook <- function(minDate="2013-01-01", maxDate.expected=prevMonth(monthFloor(today()-5), 2), refresh=TRUE, showWarnings=TRUE, envir.load=globalenv()) {
  if (!refresh) {
    if (exists("DT.mbworkbook") && is.data.table(DT.mbworkbook)) {
      DT.mbworkbook <- get("DT.mbworkbook")
    } else {
      loadFromJesus("DT.mbworkbook", over=TRUE, assignTo="DT.mbworkbook")
    }

    if (!isInDateRange(DT.mbworkbook, "accounting_month", minDate, maxDate.expected))
      refresh <- TRUE
  } 

  if (refresh) {
      tmp_DT <- try(runQry(.m(tbl="mbworkbook", schem="bi", colsToPull="*", aggFunc=NULL, dateCol="accounting_month", minDate=minDate), verbose=FALSE), silent=TRUE)
      if (!isErr(tmp_DT))
        DT.mbworkbook <- tmp_DT
  }

  if (!exists("DT.mbworkbook") || isErr(DT.mbworkbook))
    stop ("DT.mbworkbook did not load properly")

  if (!isInDateRange(DT.mbworkbook, "accounting_month", minDate, maxDate.expected))
    verboseMsg(showWarnings, "DT.mbworkbook loaded but it does not have the expected date range", call.=FALSE)

  assign("DT.mbworkbook", DT.mbworkbook, envir=envir.load)
  return(invisible(DT.mbworkbook))
}