suppressPackageStartupMessages( library(XLConnect)   ) 

## Input files
f.groupings <- c("~/git/orch/src/DeNormalizing/Groupings.xlsx")
wb <- loadWorkbook(f.groupings)

## Read in the sheets
sheets <- getSheets(wb)
sheets <- setdiff(sheets, extract("^_", sheets))

quoteit <- function(x, e=parent.frame())
  sprintf("'%s'", gsub("'", "\\\\'", x) )


caseClause <- list()
for (sheet in sheets) {

    ## Read in the worksheet data
    DT <- as.data.table(readWorksheet(wb, sheet=sheet, check.names=FALSE))

    ## grab the names of the columns
    nms <- names(DT)
    orig <- nms[[1]]

    ## drop orig and ignore any column starting with a "_"
    nms <- setdiff(nms, c(orig, extract("^_", nms)))

    ## The else value is different for some grp's
    else_value_default <- sprintf("'zOTHER %sz'", plrl(toupper(gsub("( |_)*(name|id|code)", "", orig, ignore.case=TRUE)), make=TRUE))

    ## Add these transactiontype ELSE values
    # ELSE 'zOTHER TRANSACTION'
    # ELSE ''
    # ELSE ''
    # ELSE ''


    caseClause[[sheet]] <- list(.table = sprintf("\n\n##  %s", gsub("(_\\d+)", "", sheet)))
    for (grp in nms) {

      else_value <- ifelse(grp == "store_name_short", paste0("\"",orig,"\""), else_value_default)
      else_value <- ifelse(grp == "store_name_short", paste0("\"",orig,"\""), else_value_default)

      ## find the longest string, for cleaner formatting
      mx <- DT[, mnchar(get(orig)) + 1]

      ## pattern for sprintf
      spf.pat <-  paste0("\n  WHEN %-", min(30, mx) + 2 ,"s THEN %s") ## the "+2" is for the quotes

      whenClauses <- pasteC(DT[get(grp) != "_ELSE_", sprintf(spf.pat, quoteit(get(orig)), quoteit(get(grp)))])
      caseClause[[sheet]][[grp]] <- sprintf("CASE \"%s\" %s\n ELSE %s\nEND AS \"%s\"",  orig, whenClauses, else_value, grp)
    }

    # cat(unlist(caseClause), sep="\n\n-------------------\n\n")
}

cat(clipCopy(capture.output(invisible(lapply(caseClause, function(x) cat(unlist(x), sep="\n,\n\n"))))))