"

Note to self: I need to check what will happen if only one or two of these files come in. 
              Do I need to wait for all three files? 


~~~~~~~~~~~~~~~~~~~~~
Whats remaining? 
    A designated folder
    A script that runs every so often, grabs the files in the designated folder
       based on the file name, determine which function to run
       (possibly wait for all three files)
    Adding in EXCHANGE RATE processing. 

GIT COMMITS! 
~~~~~~~~~~~~~~~~~~~~~
"


## EXECUTE THIS MANUALLY
if (FALSE)
{

    ## EG: 
    # file.in <- "~/git/orch/ingest/MgmtReport/newFiles_to_process/Average Monthly FX rates.xlsx"
    # file.in <- "~/git/orch/ingest/MgmtReport/newFiles_to_process/supplychain_split_percs.xlsx"
    # file.in <- "~/git/orch/ingest/MgmtReport/newFiles_to_process/Revenues 01-14 - 08-14.xlsx" # Revenues 01-14 - 07-14.xlsx"
    # file.in <- "~/git/orch/ingest/MgmtReport/newFiles_to_process/August 2014 - Revenue Spreadsheet.xlsx" # July 2014 - Revenue Spreadsheet.xlsx"

    ## Parse & execute based on teh filename

    ## To not accidentally execute when sourcing this file
    setScience("MgmtReport", subl=FALSE, load=FALSE, create=FALSE)


    ## File in could be any of these. 
    folder.in <- ingest.p("newFiles_to_process")
    files.in <- extractFilesFromFolder(folder=folder.in, ext="xls[x]", full=TRUE)

    if (!length(files.in)) {
      warning ("There are no files in the folder '", folder.in, "'\n\n", pasteR(40), "\n    \t NOTHING PROCESSED\n", pasteR(40), "\n")
    } else
      message ("There are ", length(files.in), " files in the folder '", folder.in, "'\n\n", pasteR(40), "\n")

    for (file_name in names(files.in)) {
      if (grepl("Revenues \\d", file_name)) {
        cat("mgmt_file_process.Revenue_summary(", file_name, ")", "\n")
        mgmt_file_process.Revenue_summary(files.in[[file_name]])
      } else if (grepl("^RED|supplychain", file_name, ignore.case=TRUE)) {
        cat("mgmt_file_process.sc_splits(", file_name, ")", "\n")
        try(mgmt_file_process.sc_splits(files.in[[file_name]]), silent=FALSE)
      } else if (grepl(" Revenue Spreadsheet\\.", file_name)) {
        cat("mgmt_file_process.Revenue_worksheet(", file_name, ")", "\n")
        mgmt_file_process.Revenue_worksheet(files.in[[file_name]])
      } else {
        cat ("Unknown for file: ", file_name, "\n")
      } 
    }

} ## // if (FALSE)


append_timestamped_subfolder_to_path <- function(folder, flag, prefix="Processed-", frmt="%Y%m%d", seconds=FALSE) {
## given a folder path, appends a subfolder to it. 
## Note that this is simple and does not check whether or not folder is actually a folder on the OS
##   thus is folder is accidentally a file name, the results will be "path/file/subfoler"
##
## Does NOT create the folder.  
## This function simply modifies a string

    subf   <- paste0(prefix, timeStamp(frmt=frmt, seconds=seconds))
    as.path(folder, subf, expand=FALSE)

}

mgmt_file_process.Revenue_summary <- function(file.in, folder.zArchive=ingest.p("Revenue_Sheets", "zArchived"), year=format(Sys.Date(), "%Y")) {

  require(XLConnect)

  file.out  <- ingest.p("Revenue_Sheets", "Revenue_Summary.xlsx")
  year.str  <- as.character(year)

  ## Error check - make sure file.exists
  stopIfFileMissing(file.in)
  stopIfFileMissing(file.out)

  ## ------------------------------------------ ##
  ## Check for year-change
  ## ------------------------------------------ ##
      sheets.out <- getSheets(wb.out <- loadWorkbook(file.out))

      if (year %ni% sheets.out) {
        ## grab the active accounting year
        year.acct <- max(as.numeric(sheets.out), na.rm=TRUE)
        ## check if at the start of a new year. If so, check for any empty columns at the end of the previous year
        if (year == (year.acct + 1) & data.table::month(Sys.Date()) <= 3) {
          current.sheet <- setDT(readWorksheet(wb.out, sheet=as.character(year.acct)))
          current.blank_months <- nwhich(apply(is.na(current.sheet), 2, all))
          ## If the blank columns are one of the last three months, then set the year to year.acct
          if (min(match(current.blank_months, month.name)) >= (12 - 3)) {
            year     <- (year.acct)
            year.str <- as.character(year.acct)
          }
        }
      }

      ## CHECK AGAIN, if still not a proper sheet, we have an error -- probably need to create a sheet
      if (year %ni% sheets.out)
        stop("\n'year' = ", year, " which is not one of the sheets.\nThe max sheet is ", year.acct, "\n\nHINT: This might mean you need to create the next sheet -- ie, for 2015")
      
      ## cleanup
      rm(wb.out)
      gc()
  ## ------------------------------------------ ##


  ## ------------------------------------------ ##
  wb <- try(loadWorkbook(file.in), silent=TRUE)

  ## Check that file was loaded properly
  if (isErr(wb))
    notifyRick.FileIngestionFailed(file.in, "File exists, but could not load the wrokbook. Perhaps it was open elsewhere")

  raw <- readWorksheet(wb, sheet=1, startCol=0, startRow=3, check.names=FALSE)
  setDT(raw)

  Revenue_RowsToDrop <- c("Orchard", "IODA", "IRIS")
  drop.byname  <- raw[,  Revenue %in%  c("Orchard", "IODA", "IRIS") ]
  drop.byblank <- raw[, !"Revenue", with=FALSE][, rowSums(is.na(.SD)) == ncol(.SD)]
  drop.byNA    <- raw[, is.na(Revenue)]

  raw <- raw[!(drop.byname | drop.byblank | drop.byNA)]

  ## change the capitalization of the "TOTAL" to "Total"
  raw[Revenue == "Total", Revenue := "TOTAL"]


  ## ------------------------------------------ ##
  ## Backup the file.in and file.out
  ## ------------------------------------------ ##
  ## Two separate archive folders
  folder.zArchive_Processed <- append_timestamped_subfolder_to_path(folder.zArchive, prefix="Processed-", flag=use.timestamped.subfolder)
  folder.zArchive_Last_Used <- append_timestamped_subfolder_to_path(folder.zArchive, prefix="Last_Used-", flag=use.timestamped.subfolder)

  dir.create(folder.zArchive_Processed, recursive=TRUE, showWarnings=FALSE)
  dir.create(folder.zArchive_Last_Used, recursive=TRUE, showWarnings=FALSE)

  ## file.in gets moved;  file.out gets copied
  file.rename(file.in  , as.path(folder.zArchive_Processed, basename(file.in) ) )
  file.copy  (file.out , as.path(folder.zArchive_Last_Used, basename(file.out)) )
  ## ------------------------------------------ ##

  ## ------------------------------------------ ##
  ## Load in the current
  ## ------------------------------------------ ##
  wb.current <- try(loadWorkbook(file.out), silent=TRUE)

  ## Check that file was loaded properly
  if (isErr(wb.current))
    notifyRick.FileIngestionFailed(file.out, "File exists, but could not load the wrokbook. Perhaps it was open elsewhere")

  current <- readWorksheet(wb.current, sheet=year.str, startCol=0, startRow=0, check.names=FALSE)
  setDT(current)

  ## The Revenue column needs to be identical
  if (!(identical(current$Revenue, raw$Revenue) && !is.null(raw$Revenue)))
    notifyRick.FileIngestionFailed(file.in, hint="The Revenue column of 'current' and 'raw' are NOT identical. (Thus trying to put the values in would not align correctly)", fail_at_end=TRUE)


  ## Identify which NAs are available in the raw
  blankCols <- nwhich(colSums(is.na(current)) == nrow(current))

  ## the cols to bring over from raw to current are those which are blank in current and available in raw
  colsToBringOver <- intersect(blankCols, names(raw))
 
  ## If there are no colsToBringOver, fail
  if (!length(colsToBringOver))
    notifyRick.FileIngestionFailed(file.in, hint="There were no columns in the new file to bring over", fail_at_end=TRUE)

  ## Grab the columns
  newCols <- raw[, colsToBringOver, with=FALSE]

  ## For some reason, the values are coming in negative. Flip them. 
  if (all(colSums(newCols) < -1e4))
    newCols[, names(newCols) := lapply(.SD, function(x) -x)]

  ## Confirm one more time that the Revenue columns match
  stopifnot(all(current$Revenue == raw$Revenue))
  for (col in colsToBringOver)
    current[, (col) := newCols[[col]] ]

  ## Write the sheet back in
  writeWorksheet(wb.current, data=current, sheet=year.str, header=TRUE, rownames=NULL)
  saveWorkbook(wb.current)


}

mgmt_file_process.Revenue_worksheet <- function(file.in, folder.zArchive=ingest.p("Revenue_Sheets", "zArchived"), use.timestamped.subfolder=TRUE, Confirm=TRUE) {

  ## STEPS: 
  # COPY (not move) file.in to file.archive
  # MOVE file.in to file.out  (ie, overwrite file.out)

  ## Error check - make sure file.exists
  stopIfFileMissing(file.in)

  ## Optionally append time stamped subfolder
  folder.zArchive <- append_timestamped_subfolder_to_path(folder.zArchive, flag=use.timestamped.subfolder)
  dir.create(folder.zArchive, recursive=TRUE, showWarnings=FALSE)

  file.out      <- ingest.p("Revenue_Sheets", "Revenue_Spreadsheet.xlsx")
  file.zarchive <- as.path(folder.zArchive, expand=FALSE)

  file.copy(file.in, file.zarchive)
  file.rename(file.in, file.out)

  return(file.out)
}


mgmt_file_process.sc_splits <- function(file.in, folder.zArchive=ingest.p("SC_splits", "zArchived"), use.timestamped.subfolder=TRUE, Confirm=TRUE) {

  ## STEPS: 
  # ingest file
  # check for signs of manual error
  # clean raw file to expected output
  # overwrite to f.out
  # create a (posisbly timestamped) subfolder in zArchived
  # move file.in to zArchived


  require(XLConnect)

  ## Error check - make sure file.exists
  stopIfFileMissing(file.in)
  
  ## Error check - confirm correct project set
  if (!exists("projName") || projName != "MgmtReport")
    stop("Project is not correctly set.\nHINT: Did you run setScience(proj='MgmtReport') ?")

  ## Folder were file will be moved to. Do NOT create it yet. Wait til file.out succesfully written
  folder.zArchive <- append_timestamped_subfolder_to_path(folder.zArchive, flag=use.timestamped.subfolder)

  ## file.out is where the cleaned CSV will be written to
  ## file.zarchive is where the file.in will be MOVED to 
  ## 
  file.out <- ingest.p("SC_splits", "supplychain_split_percs.csv")
  file.zarchive <- as.path(folder.zArchive, basename(file.in))


  ## Used for testing raw data and to extract the columns
  ## Yes, these are deliberately hardcoded
  expectedRows <- 14
  expectedCols <- c("DATE", "RED_PERC", "OSC_PERC")
  colNamesOut  <- c("date", "RED",      "OSC")

  wb <- loadWorkbook(file.in)

  raw <- readWorksheet(wb, sheet=1, check.names=FALSE)

  ## We cannot be certain that sheet 1 will have our data.
  ## Check that it is a data.frame and that it has our expected columns and minimum rows
  if (!is.data.frame(raw) )
    notifyRick.FileIngestionFailed(file.in, hint="raw was not a data.frame", fail_at_end=TRUE)

  ## make the names upppercase, to avoid 'right names wrong caps'
  colnames(raw) <- toupper(colnames(raw))
  if (any(expectedCols %ni% colnames(raw)))
    notifyRick.FileIngestionFailed(file.in, hint="one or more expectedCols is missing", fail_at_end=TRUE)

  ## DO NOT FAIL, BUT DO NOTIFY. (ie, it might be possible that we are processing a shorter time span)
  if (nrow(raw) < expectedRows) 
    notifyRick.FileIngestionFailed(file.in, hint=sprintf("raw has only %i rows, minimum expected was %i", nrow(raw), expectedRows), fail_at_end=FALSE)

  ## Convert to a data.table
  setDT(raw)

  ## If there are superfluous columns, drop them
  if (length(superfluousCols <- setdiff(names(raw), expectedCols)))
    raw[, (superfluousCols) := NULL]

  ## CLEAN UP FOR EXPORT
  setnames(raw, colNamesOut)
  raw[, type := "as-reported estimate"]

  ## format to simple date
  raw[, date := format(date, format="%Y-%m-%d")]

  ## WRITE THE FILE
  # write.csv(x=raw, file=file.out, append=FALSE, sep=",", row.names=FALSE, col.names=TRUE)
  write.table(x=raw, file=file.out, append=FALSE, sep=",", row.names=FALSE, col.names=TRUE)
  .a(write.table)

  ## CONFRIM
  if (Confirm) {
    DT.confirm <- fread(file.out)
    DT.confirm[, date := format(date, format="%Y-%m-%d")]

    if (!identical(DT.confirm, raw))
      notifyRick.FileIngestionFailed(file.in, hint="File was written, but DT.Confirm failed", fail_at_end=FALSE)
  }

  ## MOVE file.in to zArchive
  dir.create(folder.zArchive, recursive=TRUE, showWarnings=FALSE)
  caught <- try(file.rename(from=file.in, to=file.zarchive))
  if (isErr(caught)) {
    notifyRick.FileIngestionFailed(file.in, hint=sprintf("Output succesful, however was not able to move file to archive location \n  %s\n ", file.out), fail_at_end=FALSE)
  }

  return(file.out)
}


notifyRick.FileIngestionFailed <- function(file.in, hint, fail_at_end=TRUE) {
  # browser()
  cat(hint, "\n")
  if (fail_at_end)
    stop()

}