## findMaxDate.r

## TODO:  This is used in GL pt 1a - querying OA. 
##  However,  qTableDesc  now has much of this for most tables. 


## When forecasting and fakeing the date, we need to know what date to query the agg tables to. 
## This function returns the last date in the original tables by ingestion time

findMaxDate <- function(tbl, maxDate=Sys.Date() + 400, cluster=4, silent.error=TRUE, showWarnings=TRUE, verbose=TRUE) {
## RETURNS NULL if we are not "in forecasting mode" (ie fakeToday is NULL or does not exist)

  ## ------------------------------------------------------------------------------------ ##
  ## NOTE TO SELF:  Be careful!  Make sure not to exit without setting back cluster
  ## ------------------------------------------------------------------------------------ ##

  orig.cluster <- getCluster()

               
  ## Different queries based on which tbl checking
  {
    if (tbl %in% c("accounting", "aggregated_accounting"))
      QRY <- sprintf("SELECT  
                        CAST(CAST(YEAR AS VARCHAR(4)) + RIGHT ('0' + CAST(MONTH AS VARCHAR(2)),2) + '01' AS DATETIME) AS maxDate
                      FROM dim_period 
                      WHERE periodid = (SELECT max(accountingperiodid) From fact_sales WHERE dateCreated <= '%s')", maxDate)
    else if (tbl %in% c("analytics", "aggregated_analytics"))
      QRY <- sprintf("SELECT MAX(activity_date) AS maxdate FROM fact_analytics WHERE processeddaytime <= '%s'", maxDate)
    else 
      stop("Do not know what query to run for table ", tbl, "\n")
  }

  ## Execute query -- careful to set cluster just right before, and then setting it back. 
  setDBsettings(cluster=cluster)
  ret <- try(runQry(QRY, verbose=FALSE, cluster=cluster)[, as.Date(maxdate)], silent=silent.error)
  setDBsettings(cluster=orig.cluster)

  if (isErr(ret)) {
    tableExists <- qTableExists(tbl)
    msg <- sprintf("Could not find the max date for table \"%s\"\nThe table %s exist", tbl, ifelse(tableExists, "however DOES", "does NOT"))
    if (showWarnings)
      warning("Could not find the max date for table \"", tbl, "\"\n  HINT:Check ")
    ret <- NA
  }

  verboseMsg(verbose, "The maxDate for", tbl, "is", as.character(ret), time=FALSE)

  return(ret)
}