## This ingests the short file with Jan-April that was sent to me by Lee. 

.us()
lib(XLConnect)

f.in <- "~/git/orch/data/MgmtReport/Monthly Manual Gross Rev/Monthly Gross Rev.xlsx"
sheet.nm <- "Cleaned"


## read in file
rawGrossRev <- as.data.table(readWorksheet(loadWorkbook(f.in), sheet=sheet.nm, check.names=FALSE))

## remove NA rows
rawGrossRev <- rawGrossRev[!is.na(rawGrossRev[[1]])]
setnames(rawGrossRev, 1, "item")


DT.grossrev <- reshape2::melt(rawGrossRev, id.vars="item", variable.name="month", value.name="stone")

## For now, all dates are 2014.  Convert the months to integer, then to date
DT.grossrev[, month := match(month, month.abb)]
DT.grossrev[, date  := as.Date(sprintf("2014-%02i-01", month))]

items.Music <- c("Download", "Streaming & Other Services")
items.Video <- c("Video Retail Distribution", "Video Platform Services")

DT.grossrev[, music_vs_video := NULL]

DT.grossrev[item %in% items.Music, music_vs_video := "Music"]
DT.grossrev[item %in% items.Video, music_vs_video := "Video"]

DT.grossrev.total <- DT.grossrev[, list(item=paste(music_vs_video, "Total"), category=music_vs_video, monthlynumber=sum(stone)), by=list(date,music_vs_video, month)][, music_vs_video := "Total"]

DT.grossrev <- rbind(DT.grossrev, DT.grossrev.total, use.names=TRUE)


DT.grossrev[, music_vs_video := factor(music_vs_video, levels=c("Music", "Video", "Total"))]

## Add a column to indicate the type of monthly number
DT.grossrev[, numberis := "stone"]
setnames(DT.grossrev, "stone", "monthlynumber")

## No longer need monthly integer
DT.grossrev[, month := NULL]

setkey(DT.grossrev, date, music_vs_video, item)

## reorder, for aesthetics
setcolorderpt(DT.grossrev, c("date", "music_vs_video", "item", "numberis"))


QRY.list <- makeSQLtable(ret, table.name="manualnumbs", schema="ds_scratch", factorToChar=FALSE, quiet=TRUE, numericDecimals=c(22, 9))

QRY.create <- QRY.list[["QRY.create"]]
QRY.insert <- QRY.list[["QRY.insert"]]

QRY.drop <- stringr::str_extract(QRY.create, "CREATE TABLE .+?\\s")
QRY.drop <- gsub("CREATE", "DROP", QRY.drop)

{
  verbose.dbcon.off()
  if (dropCurrentTable) {
      cat("Dropping existing table .......   ")
      runQry(QRY.drop,   results.not.expected=TRUE, verbose=FALSE)
      cat("Creating table ................   ")
      runQry(QRY.create, results.not.expected=TRUE, verbose=FALSE)
  }
  cat("Inserting data into table .....   ")
  runQry(QRY.insert, results.not.expected=TRUE, notifyWhenDone=TRUE, verbose=FALSE)
}