# 01 Retrieve Files From FTP.r

.us()
setScience("Currency_Exchange_Rates", create=TRUE, subl=FALSE, load=FALSE)
cluster.out <- 9
lib(reshape2)

f.in <- ingest.p("Average Monthly FX rates.xlsx")

list_DT.exchangerates <- XLStoDT(f.in, startRow=3)

## Cleanup
## Two things: Superfluous trailing columns
## The "Quarterly columns", the first of which is indicated by a "Avr"
## change year column and bring it to front
cleanUpExRateSheets <- function(DT)  {
  DT <- copy(DT)
  setnames(DT, "sheetSource", "year")
  setnames(DT, "Col1", "currency_code")
  ## drop the trailing two columns if they exist
  suppressWarnings(DT[, c("Col14", "Col15") := NULL])
  row.quarterly <- DT[, grep("Av[rg]", JAN)]
  if (!length(row.quarterly))
    browser(text="no row Quarterly")

  DT <- DT[1:(row.quarterly-1)]
  ## Drop any rows where the currency_code is missing and the January column is missing (likely that other columns missing as well)

  return( DT[!(is.na(currency_code) & is.na(JAN))] )
}


kCols <- c("currency_code", "year")
DT.exchangerates.wide <- rbindlist(lapply(list_DT.exchangerates, cleanUpExRateSheets))

## Some month columns were pulled in as characters. Convert to numeric
exr.numeric.cols <- setdiff(names(DT.exchangerates.wide), "currency_code")
DT.exchangerates.wide[, (exr.numeric.cols) := lapply(.SD, as.numeric), .SDcols=exr.numeric.cols]
DT.exchangerates <- melt(DT.exchangerates.wide, id=c("currency_code", "year"), value.name="currency_rate", variable.name="MONTH_ABB")

## Clean up month_abb column
DT.exchangerates[, MONTH_ABB := substr(MONTH_ABB, 1, 3)]

if (DT.exchangerates[, lunique(MONTH_ABB)] != 12)
  warning ("There are not exactly 12 months")

minYear <- DT.exchangerates.wide[, min(year)]
maxYear <- DT.exchangerates.wide[, max(year)]

## OLD AS OF 2015-02-15
# if (!exists("DT.periods")) {
#   DT.periods <- character()
#   if(!identical(.Pfm, "Darwin"))
#     DT.periods <- try(runQry(sprintf("SELECT * FROM dim_period WHERE year BETWEEN %i and %i", 2008-1, 2014+1)))
#   if (isErr(DT.periods) || !is.data.table(DT.periods))
#     loadFromJesus("DT.periods")
#   else
#     jesusForData("DT.periods")

#   period.numeric.cols <- c("periodid", "year", "month")
#   DT.periods[, (period.numeric.cols) := lapply(.SD, as.numeric), .SDcols=period.numeric.cols]
# }
# DT.periods[, MONTH_ABB := toupper(month.abb[month])]
##
## NEW: 
DT.periods <- get_dim_period(refresh=TRUE)
DT.periods[, MONTH_ABB := toupper(substr(month_name, 1, 3))]
DT.periods[, year      := year(perioddate)]


matchKey(DT.periods, DT.exchangerates, c("year", "MONTH_ABB"))
DT.exchangerates[DT.periods, accountingperiodid := periodid, allow=TRUE]

## 2015-02-15 -- Fail on NA in accountingperiodid, dont just warn (downstream)
if (any(is.na(DT.exchangerates$accountingperiodid)))
  stop("There are NAs in DT.exchangerates$accountingperiodid\nHINT: Probably due to an incomplete match with DT.periods\n        Check for mis-spelled MONTH_ABB values in both DTs")

## Drop unmatched periodids that also have NA currency_rates
DT.exchangerates <- DT.exchangerates[!(is.na(accountingperiodid) & is.na(currency_rate))]

if (any(DT.exchangerates[, is.na(accountingperiodid)]))
  warning ("There are NAs in accountingperiodid")


## for now, dropping   "TRY (no GP)" 
DT.exchangerates <- DT.exchangerates[currency_code != "TRY (no GP)"]


## ADD USD 
if ("USD" %ni% DT.exchangerates[["currency_code"]]) {
  tmp_DT.USD <- unique(DT.exchangerates[!is.na(currency_rate)], by="accountingperiodid")
  tmp_DT.USD[, `:=`(currency_code="USD", currency_rate=1)]
  DT.exchangerates <- rbind(DT.exchangerates, tmp_DT.USD)
  rm(tmp_DT.USD)
}



setcolorderpt(DT.exchangerates, c("accountingperiodid", "currency_code", "year", "MONTH_ABB"))

setkeyIfNot(DT.exchangerates, c("accountingperiodid", "currency_code"))
ingestIntoSQL(DT.exchangerates, cluster=cluster.out, schema="bi", tbl="exchange_rates_from_mikeb", append=FALSE)


f.out <- out.p(DT.exchangerates[!is.na(currency_rate)] [(accountingperiodid == maxn(accountingperiodid)), sprintf("exchange_rates_upto_%s_%s", year[[1]], MONTH_ABB[[1]])], ext="tsv")

write.table(DT.exchangerates, file=f.out, sep="\t", row.names=FALSE, col.names=TRUE)

## Copy the tsv file to Amazon S3 bucket
bucket.srer <- s3_p("dev-staging-dim-imports", "staging_royalty_exchange_rate", dateStamp(), default=NULL)
cmd.s3put <- sprintf("s3cmd put %s %sCurrency_Exchange_Rates.tsv", f.out, bucket.srer)
system(cmd.s3put)


jesusForData(DT.exchangerates, dir=data.p(proj="Currency_Exchange_Rates"))