### ------------------------------------------------- ###
BackUpOrRestore("DT.cleaned.aggd")

{
  cols.to.yago <- c("GL_net_forDSOM", "GL_gross_forDSOM", "OA_gross_forDSOM")
  cols.to.yago <- setNames(obj=paste0(cols.to.yago, ".yago"), nm=cols.to.yago)
}

{
  ## DEV'ing, clear gross_yago if present
  suppressWarnings(DT.cleaned.aggd[, (cols.to.yago) := NULL])

  ## Put in last months GL_netOfSC_forDS
  DT.yago <- copy(DT.cleaned.aggd)

  ## This year's values are NEXT YEAR's YAGO values
  DT.yago[, date := date %m+% months(12)]

  setnames(DT.yago, names(cols.to.yago), cols.to.yago)

  ## Merge it in
  matchKey(DT.cleaned.aggd, DT.yago, kCols.smry_supbuk)
  j.txt <- sprintf("`:=`(%s)",  commaSep(sprintf("%s=i.%1$s", cols.to.yago)) )
  DT.cleaned.aggd[DT.yago, eval(parse(text=j.txt)), allow=FALSE]


  ## NOTE: Taking the mean across year's will not necessarilly line up, since there will be groups in current year not in yago 
  ##       (and possibly vice versa, but unlikely).  
  ## To check, we must sort by splitgroups and remove NAs


  ## CONFRIM: values lines up with yago (Two methods)
  {

    ## TODO:  Method 1 does not work.  I did not spend too much time looking into it. 
    #         Using method 2 instead

#    ## Method 1:  ordering by split group cols, then by month then year, gross_yago will be offset from GL_netOfSC_forDS by one row. Shifting up, they should line up and be equal
#    for (c.nm in names(cols.to.yago))
#      stopifnot(DT.cleaned.aggd[TRUE][, .month := month(date)][, .year := year(date)][, list(.current = mean(get(c.nm), na.rm=TRUE), .yago = mean(get(c.nm), na.rm=TRUE)), keyby=c(setdiff(kCols.smry_supbuk, "date"), ".month", ".year")
#                                    ][, .yago := shiftUp(.yago) ][!is.na(.yago), all(.current == .yago)])

    ## Method 2:  More manual procedure. Specifically, *leave* the NAs in the sum, ie do NOT use 'sumn'.  This will take care of categroies not matching up one year with the next
    for (c.nm in names(cols.to.yago)) {
      cat(sprintf("Confriming yago for  %18s ....... ", c.nm), sep="")
      stopifnot({
          merge(
          DT.cleaned.aggd[year(date) == year(max(date)-1), list(.gross_last_year=sum(get(c.nm))), keyby=kCols.smry_supbuk]
          , 
          DT.cleaned.aggd[year(date) == year(max(date)), list(.gross_this_year=sum(get(c.nm))), keyby=kCols.smry_supbuk]
          )[, equals(.gross_this_year, .gross_last_year, na.check=TRUE) & !all(is.na(.gross_this_year))]
        })
      cat("OK!\n")
    }

  }
  

  ## Cleanup Budget: 
  ## Budget may not properly be distributed across all the different subgroups
  ## Therefore, any parent group with *some* rows with budget, will have all NAs in budget treated as zeros. 
  ## ** The question is, what is a parent group?? **   For now, we will use date
  setkeyIfNot(DT.cleaned.aggd, key=kCols.date, superset.ok=TRUE, verbose=FALSE, warnForColNameInEnv=FALSE)
  DT.cleaned.aggd[DT.cleaned.aggd[, any(!is.na(budget)), keyby=kCols.main][(V1)], budget := removeNA(budget, replace=0)]

  DT.cleaned.aggd[, var_gross_vs_budget_raw  := GL_net_forDSOM - budget]

  ## Compute growth and variance
  ## WHICH ONE?  ALLOW NAs or NO? 
  {
    # DT.cleaned.aggd[!is.na(GL_net_forDSOM.yago) , yoy_growth_no_NA  :=          GL_net_forDSOM     -  GL_net_forDSOM.yago ]
    DT.cleaned.aggd[!is.na(GL_net_forDSOM.yago) , yoy_growth        := removeNA(GL_net_forDSOM, 0) -  GL_net_forDSOM.yago ]

    # DT.cleaned.aggd[!is.na(budget) , var_gross_vs_budget_no_NA  :=          GL_net_forDSOM     -  budget ]
    DT.cleaned.aggd[!is.na(budget) , var_gross_vs_budget        := removeNA(GL_net_forDSOM, 0) -  budget ]
  }
 
  ## If we do not yet have GL_net_forDSOM for a given month (ie, we received budget figures before OA or GL figures)
  ##  Then make those  var_gross_vs_budget  rows NA
  ##  note that there will be values in the GL_net_forDSOM for those where manually estimating
  ##
  ## This line identifies offending dates
  {
    .offendingDates <- DT.cleaned.aggd[is.na(ancillaryline), all(GL_net_forDSOM[!is.na(GL_net_forDSOM)]==0), keyby=date][(V1), date]
    if (length(.offendingDates))
        DT.cleaned.aggd[date %in% .offendingDates, var_gross_vs_budget := NA]
    rm(.offendingDates)
  }

  ## TODO:  I pasted these in from previous work.  I did not review what it is actually checking, but the tests pass. 

  ## CONFIRM Budget variance
  {  
    ## CONFIRM:  The sum of the var_gross_vs_budget should be equal to the diff of the sum(GL_net_forDSOM) - sum(budget);
    ##             except where (zz_hasData == FALSE) sumn(var_gross_vs_budget) == 0  *this last part assumes we will never be exactly spot on budget to revenue.  CAREFUL! 
    ##             except where sumn(var_gross_vs_budget) == 0  *this last part assumes we will never be exactly spot on budget to revenue.  CAREFUL! 
## ?????
## TODO:  What is the correct filter for [i = ..] in each of these? 
# ---------------------------- #
# ? stopifnot(merge(DT.cleaned.aggd[!(zz_hasData == FALSE), list(auto_var = sumn(var_gross_vs_budget)), keyby=kCols.main], DT.cleaned.aggd[!is.na(budget), lapply(.SD, sumn), keyby=kCols.main, .SDcols=c("GL_net_forDSOM", "budget")][, list(man_var=GL_net_forDSOM-budget), keyby=kCols.main]    ) [auto_var != 0, equals(auto_var, man_var)])
stopifnot(merge(DT.cleaned.aggd[, list(auto_var = sumn(var_gross_vs_budget)), keyby=kCols.main], DT.cleaned.aggd[, lapply(.SD, sumn), keyby=kCols.main, .SDcols=c("GL_net_forDSOM", "budget")][, list(man_var=GL_net_forDSOM-budget), keyby=kCols.main]    ) [auto_var != 0, equals(auto_var, man_var)])
stopifnot(merge(DT.cleaned.aggd[!is.na(budget), list(auto_var = sumn(var_gross_vs_budget)), keyby=kCols.main], DT.cleaned.aggd[!is.na(budget), lapply(.SD, sumn), keyby=kCols.main, .SDcols=c("GL_net_forDSOM", "budget")][, list(man_var=GL_net_forDSOM-budget), keyby=kCols.main]    ) [auto_var != 0, equals(auto_var, man_var)])
# ---------------------------- #

    ## CONFIRM:  The only month when the entire sumn(var_gross_vs_budget) == 0 should be the last month. (ie, we got budget before transactions)
    stopifnot(DT.cleaned.aggd[!is.na(budget), list(auto_var = sumn(var_gross_vs_budget)), keyby=date][auto_var==0, date == max(DT.cleaned.aggd$date)])
  }
 
  ## drop the 'last modified' columns.  We won't use them in this table
  suppressWarnings( DT.cleaned.aggd[, paste0(c("budget", "GL_net_forDSOM"), "_last_modified") := NULL] )

  invisible()
}

BackUpOrRestore("DT.cleaned.aggd", clear=TRUE)

message("Done creating  'DT.cleaned.aggd' with yago and var")