## THIS FILE WILL LEAVE US WITH TWO WORKING TABLES:
##
##   DT.ancillary_from_rev_summary
##   DT.mainline_group_subtotals_from_rev_summary


{
  ## Input file
  f.in.rev_summary <- as.path(folder.revenue_sheets, "Revenue_Summary.xlsx")
  file.exists(f.in.rev_summary)

  {
    ## Clear any previous ingestion
    suppressWarnings(rm(rawsheets_smry.bak, wb))

    ## load in file
    wb <- loadWorkbook(f.in.rev_summary)

    ## Read in the sheets
    sheets <- getSheets(wb)

    selfname_(sheets)

    ## Normally 1e-4 ... raising until manual March error fixed
    tol.for.totals <- 10  # 1e-4

  }

  {
    ## IMPORT SHEETs
    rawsheets_smry <- list()
    for (sheet in sheets) {
      # cat("\n\n        ------------------\n        ", sheet, "\n")
      rawsheets_smry[[sheet]] <- as.data.table(readWorksheet(wb, sheet, check.names=FALSE, rownames=FALSE))[, year := sheet]
    }

    ## bank a backup
    BackUpOrRestore("rawsheets_smry")
    saveImageTo(subProj="in 1c after raw")
  }

  ### ------------------------------------------------ ###

  {
    DT.rev_summary <- rbindlist(rawsheets_smry)
    setnames(DT.rev_summary, "Revenue", "revenue")
    setkeyIfNot(DT.rev_summary, year, revenue, organize=TRUE, verbose=FALSE)

    ## CONVERT currency numbers to numbers and clear NAs in OSC
    {
      .tmp.months_present <- intersect(month.name, names(DT.rev_summary))
      DT.rev_summary[, (.tmp.months_present) := lapply(.SD, currToNumeric), .SDcols=.tmp.months_present]

      ## Clear these NAs. Other Service Rev is *not* expected for mid 2013 and prior. The NAs should be treated as 0.
      DT.rev_summary[year <= 2013 & revenue == "Other Service Rev", (.tmp.months_present) := lapply(.SD, removeNA, replace=0), .SDcols=.tmp.months_present]

      rm(.tmp.months_present)
    }

    ## THERE SHOULD NOT BE ANY BLANK ROWS PER YEAR-COLUMN UNLESS THE WHOLE THE COLUMN IS NA
    stopifnot(DT.rev_summary[, lapply(.SD, function(x) sum(is.na(x))) %in% c(0, .N), keyby=year][, V1])

    ## See:  NAs 
    DT.rev_summary[, lapply(.SD, function(x) sum(is.na(x))), keyby=year]

    ## Clean the 'revenue' value for those items in the dict (ie, other services revenue)
    DT.rev_summary[revenue %in% names(dict.rev_summary_revenue_names), revenue := dict.rev_summary_revenue_names[revenue]]
    # DT.rev_summary[revenue == "Physical",                      revenue := "Physical"]
    # DT.rev_summary[revenue == "Film & TV Sync",                revenue := "Synch"]
    # DT.rev_summary[revenue == "Mechanical Admin & Overrides",  revenue := "Publishing Services"]
    # DT.rev_summary[revenue == "Orchard Performance",           revenue := "Performance Royalty"]
    # DT.rev_summary[revenue == "IODA Brazil Performance",       revenue := "Performance Royalty"]
    # DT.rev_summary[revenue == "Delivery",                      revenue := "Delivery"]


    ## Convert strings all remaining to numeric (This was already done by month. This line may be superflous)
    DT.rev_summary <- DT.rev_summary[, lapply(.SD, function(x) sum(currToNumeric(x)) ), keyby=list(year, revenue)]

    ## CONFIRM TOTALS SUM UP
    stopifnot(
      equals( DT.rev_summary[revenue != "TOTAL", !"revenue", with=FALSE][,  lapply(.SD, sumn), keyby=year]
            , DT.rev_summary[revenue == "TOTAL", !"revenue", with=FALSE][,  lapply(.SD, sumn), keyby=year]
            , tol=tol.for.totals )
    )

    ## CONFIRM: Revenue totals to the value we have expected (based on information outside of this program)
    stopifnot(equals(DT.rev_summary[revenue != "TOTAL" & year == 2014, sum(.SD), .SDcols=month.name[1:4]], .Expected_Rev_Jan_to_April_2014, tol=1.5))
  }
  ### ------------------------------------------------ ###

  {
    ## RESHAPE
    DT.rev_summary.molt <- melt(DT.rev_summary, id.vars=c("year", "revenue"), variable.name="month", value.name="reported_gross")

    ## CONFIRM: Revenue totals to the value we have expected (based on information outside of this program)
    stopifnot(equals(DT.rev_summary.molt[revenue != "TOTAL" & year == 2014 & month %in% month.name[1:4], sum(reported_gross)], .Expected_Rev_Jan_to_April_2014, tol=1.5))

    ## Drop rows where reported_gross is NA. These represent months in the template that have not been filled in yet.
    ## We don't drop them earlier, because it can mess with rbinding. 
    DT.rev_summary.molt <- DT.rev_summary.molt[!is.na(reported_gross)]

    ## Clean up the date
    {
      ## Convert month & year to numeric for date. (month first gets converted to factor with levels)
      DT.rev_summary.molt[, month := as.numeric(factor(month, levels=month.name))]
      DT.rev_summary.molt[, year := as.numeric(year)]


      ## add in date column
      DT.rev_summary.molt[, date := as.Date(sprintf("%04i-%02i-01", year, month)), by=list(year, month)]

      ## CONFIRM: NO NAs in date
      stopifnot(DT.rev_summary.molt[, !is.na(date)])
      DT.rev_summary.molt[, c("year", "month") := NULL]
    }

    ### ---- FOR FORECASTING ---- ###
    {
      if (!is.null(fakeToday))
            DT.rev_summary.molt <- DT.rev_summary.molt[date <= fakeToday.GL]
      ## Set attributes.  Note these are NULL if not forecasting
      attr(DT.rev_summary.molt, "fakeToday.GL") <- fakeToday.GL
      attr(DT.rev_summary.molt, "fakeToday")    <- fakeToday
    }
    ### ---- FOR FORECASTING ---- ###


    setkeyIfNot(DT.rev_summary.molt, date, revenue, organize=TRUE, verbose=FALSE)

    ## BANK then remove THE TOTALS
    DT.Total_Reported_Revenue_By_Month <- DT.rev_summary.molt[revenue == "TOTAL"]
    DT.rev_summary.molt                <- DT.rev_summary.molt[revenue != "TOTAL"]

    ## CONFIRM: Revenue totals to the value we have expected (based on information outside of this program)
    if (is.null(fakeToday) && !equals(.Expected_Rev_Jan_to_April_2014, .tmp.sum <- DT.rev_summary.molt[date >= "2014-01-01" & date <= "2014-04-01"][, sum(reported_gross)], tol=1.5))
      warning("For 2014, Jan-to-April, sum(reported_gross) is off by  $ ", formnumb(.tmp.sum - .Expected_Rev_Jan_to_April_2014)) 

    BackUpOrRestore("DT.rev_summary.molt", clear=TRUE)
  }

"=========================================================================================="
"=========================================================================================="
  ## ---------------------------------------------------  ##
  ##    Add in meta columns to molten rev summary data    ##
  ## ---------------------------------------------------  ##
  BackUpOrRestore("DT.rev_summary.molt")

  ## Use default values for main line .. 
  DT.rev_summary.molt[, meta := dict.rev_summary_meta[revenue]]
  ## ... any remaing NAs are ancillaryline
  DT.rev_summary.molt[is.na(meta), ancillaryline := revenue]

  ## Split out the meta from the dict values
  DT.rev_summary.molt[, (meta_splits_for_dict.rev_summary_meta) := lapply(as.data.table(t(as.data.table(strsplit(meta, ",")))), as.character) ] [, release_is_compilation := as.logical(release_is_compilation)]
  DT.rev_summary.molt[, store_name := ifelse(is.na(meta), paste0("zz_", revenue), "zzz_Mainline_Revenue")]
  DT.rev_summary.molt[, storeid := -1]

  ## NOTE TO SELF:  This confirmation step was useful when dev'ing, but i dont need to include it as a test
  ## CONFIRM:  STORE NAMES ARE SAME
  if (exists("DT.mgmtr_summary_manual")) {
    ## < SEE >
    DT.mgmtr_summary_manual[storeid==-1, cbind(sort(unique(store_name)))]
    DT.rev_summary.molt[, cbind(sort(unique(store_name)))]

    # stopifnot( DT.mgmtr_summary_manual[storeid==-1, cbind(sort(unique(store_name)))] == DT.rev_summary.molt[, cbind(sort(unique(store_name)))] )
  }

  ## TODO:  CONFIRM ALL NA

  ## DEFAULT OSC + META VALUES FOR anicllaryline
  DT.rev_summary.molt[!is.na(ancillaryline), 
  `:=`(   music_vs_video          =  "Music"
        , label_sc_group          =  "Orchard"
        , release_product_type    =  "Music"
        , release_is_compilation  =  FALSE
        , store_musicbucket       =  "Download"
        , store_videobucket       =  "Retail Video"
        )
  ]

  ## Add in store store_videobucket & store_musicbucket
  DT.rev_summary.molt[is.na(store_videobucket), store_videobucket := dict.rev_summary_vidbuck[revenue]]
  DT.rev_summary.molt[is.na(store_musicbucket) & music_vs_video == "Video" & store_videobucket == "Video Services", store_musicbucket := "Streaming"]
  DT.rev_summary.molt[is.na(store_musicbucket), store_musicbucket := "zUncategorized Store"]

## DEVING
DT.rev_summary.molt.precleanup <- copy(DT.rev_summary.molt)

## NOT YET
if (FALSE) {
  ## CLEAN UP GROUPINGS
  music_and_video_bucketcleanup_(DT.rev_summary.molt)
}

  ## Aggregated by meta columns
  DT.rev_summary.molt <- DT.rev_summary.molt[, list(reported_gross=sumn(reported_gross)), keyby=kCols.smry]

## DEVINGG
if (FALSE)
{ 
  kCols.smry_nodate <- setdiff(kCols.smry, "date")
 setkeyIfNot(DT.rev_summary.molt.precleanup, kCols.smry, organize=TRUE, verbose=FALSE)
 print( unique(DT.rev_summary.molt, by=kCols.smry_nodate) )
 print( unique(DT.rev_summary.molt.precleanup, by=kCols.smry_nodate) )
}


  ## CONFIRM: 
  if (is.null(fakeToday))
    stopifnot(equals(.Expected_Rev_Jan_to_April_2014, .tmp.sum <- DT.rev_summary.molt[date >= "2014-01-01" & date <= "2014-04-01"][, sum(reported_gross)], tol=1.5))

  ## ---------------------------------------------------------------------------
  ## Split into two tables: 
  ## ---------------------------------------------------------------------------
  ## (A)  Manual entries that need to be ADDED to the main table
  ## (B)  Expected totals that need to be (1) COMPARED to the totals from GL/OA and (2) ADJUST the GL/OA table

  DT.ancillary_from_rev_summary                <- DT.rev_summary.molt[ !is.na(ancillaryline) ]
  DT.mainline_group_subtotals_from_rev_summary <- DT.rev_summary.molt[  is.na(ancillaryline) ]

  invisible()
}

## Split OSC into Allegro / SelectO
## Split up the Totals according to sub categories
{

    ## TAKE WORKING COPY UNTIL END
    temp_DT.totals <- copy(DT.mainline_group_subtotals_from_rev_summary)

    ## SPLIT music_vs_video == Music
    ## ------------------------------------------------ ##
    {
      ## SEE:  This is what will be split
      temp_DT.totals[store_musicbucket == "zUncategorized Store" & music_vs_video == "Music"]

      inds <- temp_DT.totals[, music_vs_video == "Music"]
      stopifnot(any(inds))
      temp_DT.music  <- temp_DT.totals[ inds]
      temp_DT.totals <- temp_DT.totals[!inds]
      temp_DT.music  <- split_indexCol_byPercentages(temp_DT.music
                              , valueCols    = "reported_gross"
                              , indexCol     = "store_musicbucket"
                              , indexValueToSplit  = "zUncategorized Store"
                              , splatValues  = c("Download", "Streaming")
                              , percentages  = .675
                              , showWarnings = TRUE
                              )
      temp_DT.totals <- setkeyv(rbind(temp_DT.totals, temp_DT.music), key(temp_DT.totals))
      # stop ("compare  temp_DT.totals2  with  temp_DT.totals \n and look for NAs in temp_DT.music")
    }

    ## SPLIT music_vs_video == Movie  :: Video Services
    ## ------------------------------------------------ ##
    ##    Video Services                Movie    Video Services  9.83 %
    ##    Video Services            Video Etc    Video Services 22.12 %
    ##    Video Services           Web Videos    Video Services 67.85 %
    {
      ## SEE:  This is what will be split
      temp_DT.totals[release_product_type == "Movie" & store_videobucket == "Video Services"]

      ## Identify which rows MAY need to be split (depending onon indexValueToSplit)
      inds <- temp_DT.totals[, store_videobucket == "Video Services"]
      stopifnot(any(inds))
      temp.DT_vidserv <- temp_DT.totals[ inds]
      temp_DT.totals  <- temp_DT.totals[!inds]
      temp.DT_vidserv <- (split_indexCol_byPercentages(temp.DT_vidserv
                              , valueCols    = "reported_gross"
                              , indexCol     = "release_product_type"
                              , indexValueToSplit = "Movie"
                              , splatValues  = c("Movie", "Video Etc", "Web Videos")
                              , percentages  = c(.0983, .2222, .6795)
                              , showWarnings = TRUE
                              ))
      temp_DT.totals <- setkeyv(rbind(temp_DT.totals, temp.DT_vidserv), key(temp_DT.totals))
    }

    ## SPLIT music_vs_video == Video  :: Retail Video 
    ## ------------------------------------------------------------ ##
    ##     Retail Video              TV Show      Retail Video 11.50 %
    ##     Retail Video                Movie      Retail Video 88.50 %

    {
      ## SEE:  This is what will be split
      temp_DT.totals[release_product_type == "Movie" & store_videobucket == "Retail Video"]

      ## Identify which rows MAY need to be split (depending onon indexValueToSplit)
      inds <- temp_DT.totals[, store_videobucket == "Retail Video"]
      stopifnot(any(inds))
      temp.DT_retailvid <- temp_DT.totals[ inds]
      temp_DT.totals    <- temp_DT.totals[!inds]
      temp.DT_retailvid <- split_indexCol_byPercentages(temp.DT_retailvid
                              , valueCols    = "reported_gross"
                              , indexCol     = "release_product_type"
                              , indexValueToSplit = "Movie"
                              , splatValues  = c("TV Show", "Movie")
                              , percentages  = c(.1150, .8850)
                              , showWarnings = TRUE
                              )
      temp_DT.totals <- setkeyv(rbind(temp_DT.totals, temp.DT_retailvid), key(temp_DT.totals))
    }


    ## SPLIT OSC
    ## ------------------------------------------------ ##
    temp_DT.totals <- split_indexCol_byPercentages(temp_DT.totals
                          , valueCols    = "reported_gross"
                          , indexCol     = "label_sc_group"
                          , indexValueToSplit = "OSC"
                          , splatValues  = c("Allegro", "SelectO")
                          , percentages  = .64
                          , showWarnings = TRUE
                          )

    ## CONFIRM: gross should be identical, upto date-music_vs_video
    stopifnot(equals(temp_DT.totals[, sum(reported_gross), keyby=list(date, music_vs_video)]
       , DT.mainline_group_subtotals_from_rev_summary[, sum(reported_gross), keyby=list(date, music_vs_video)]))

    DT.mainline_group_subtotals_from_rev_summary <- temp_DT.totals
    rm(temp_DT.totals, temp.DT_vidserv, temp_DT.music, temp.DT_retailvid)


    ## CONFIRM VALUES ADD UP
    if (is.null(fakeToday))
      stopifnot(equals(rbind(DT.ancillary_from_rev_summary, DT.mainline_group_subtotals_from_rev_summary)[date >= "2014-01-01" & date <= "2014-04-01"][, sum(reported_gross)], .Expected_Rev_Jan_to_April_2014, tol=1.5))

    ## ... I dont remember what I was doing here... confirming the values for something, not sure what. 
    DT.mainline_group_subtotals_from_rev_summary[date >= "2014-01-01" & date <= "2014-04-01"][, sum(reported_gross)]
    DT.ancillary_from_rev_summary[date >= "2014-01-01" & date <= "2014-04-01"][, sum(reported_gross)]

  ## Clean up buckets and aggregate
  {
    ## SEE:  (These next few lines, re .preCleanup, can be removed once all working fine)
    DT.ancillary_from_rev_summary.preCleanup <- copy(DT.ancillary_from_rev_summary)
    DT.mainline_group_subtotals_from_rev_summary.preCleanup <- copy(DT.mainline_group_subtotals_from_rev_summary)

    ## There shouldnt be any columns in these two DTs other than kCols.smry & reported_gross
    ## This check is for myself, in case I modified and added a column
    {
      if(length(setdiff(names(DT.ancillary_from_rev_summary), c(kCols.smry, "reported_gross"))) )
        stop ("It looks like you've modified DT.ancillary_from_rev_summary and added columns beyond kCols.smry and 'reported_gross'.\n Your code must now be fixed")
      if (length(setdiff(names(DT.mainline_group_subtotals_from_rev_summary), c(kCols.smry, "reported_gross"))) )
        stop ("It looks like you've modified DT.mainline_group_subtotals_from_rev_summary and added columns beyond kCols.smry and 'reported_gross'.\n Your code must now be fixed")
    }

    DT.ancillary_from_rev_summary  <- changeAndAggregate(DT=DT.ancillary_from_rev_summary, colsToAgg="reported_gross" , changeFunc="music_and_video_bucketcleanup_", exceptAllNA=TRUE)
    DT.mainline_group_subtotals_from_rev_summary  <- changeAndAggregate(DT=DT.mainline_group_subtotals_from_rev_summary, colsToAgg="reported_gross" , changeFunc="music_and_video_bucketcleanup_", exceptAllNA=TRUE)

    ## SEE:  (These next few lines, re .preCleanup, can be removed once all working fine)
    DT.ancillary_from_rev_summary.preCleanup[(date == max(date))]
    DT.ancillary_from_rev_summary[(date == max(date))]
    DT.mainline_group_subtotals_from_rev_summary.preCleanup[(date == max(date))]
    DT.mainline_group_subtotals_from_rev_summary[(date == max(date))]

  }

  jesusForData_Forecasting(DT.ancillary_from_rev_summary)
  jesusForData_Forecasting(DT.mainline_group_subtotals_from_rev_summary)

  invisible()
}