## Input file f.in.rev_sheet <- as.path(folder.revenue_sheets, "Revenue_Spreadsheet.xlsx") last_modified <- file.info(f.in.rev_sheet)[["mtime"]] ## New columns for DS were added in Oct 2014. This changes how some parts are treated. date_DS_added <- as.Date("2014-10-01") { ## Clear any previous ingestion suppressWarnings(rm(rawsheets_GL.bak, wb)) ## load in file wb <- loadWorkbook(f.in.rev_sheet) ## Read in the sheets sheets <- getSheets(wb) ## For each store, we want to konw when revenue was first booked in the GL for that store, ## (since the date in GL might differ from the date in OA) ## If we don't import all the sheets, we may get an innacurate 'first date booked' ## Therefore, import everything, even if it takes longer, then drop afterwords. ## NO LONGER : if (any(parseSheetName(sheets) < minDateToKeep)) ## NO LONGER : sheets <- sheets[-which(parseSheetName(sheets) < minDateToKeep)] ## Sheet 'Apr 2012 DMV Revenue' is weird. We don't need it for our purposes, drop it. sheets <- sheets[sheets %ni% 'Apr 2012 DMV Revenue'] ## Drop any "Quarterly Revenue" sheets sheets <- setdiff(sheets, extract("^(Q\\d)", sheets)) ## Drop any "Q4" type sheets, or any sheets named "sheet x" sheets <- sheets[!grepl("^(Q\\d|[Ss]heet\\s*\\d)", sheets) ] ## Drop any GL type sheets <- sheets[!grepl("\\bGL\\b", sheets)] selfname_(sheets) sheetdates <- parseSheetName(sheets) } { ## IMPORT SHEETs rawsheets_GL <- list() for (sheet in sheets) { cat("\n\n ------------------\n ", sheet, "\n") # rawsheets_GL[[sheet]] <- cbind(readWorksheet(wb, sheet, check.names=FALSE), sheet_nm=NA) rawsheets_GL[[sheet]] <- readWorksheet(wb, sheet, check.names=FALSE, rownames=FALSE) ## "Warning: Error detected in cell \\[A-Z]+\\d+ - Invalid cell reference" } ## bank a backup if (!exists("rawsheets_GL.bak")) rawsheets_GL.bak <- rawsheets_GL } ### ---------------------------------------------------------------- ### ## Clean each individual sheet ## Dropping superfluous rows above/below the body of the table ## Combine the sheets into one data.table ### ---------------------------------------------------------------- ### ## Find the first real row of data, which is one after the first none-NA row firstRow <- 1 + min(which(!is.na(rawsheets_GL[[1]][[1]]))) ## CONFIRM: The same row is NA for all tables. Column 6 should have a few extra headings stopifnot(sapply(rawsheets_GL, function(x) min(which(!is.na(x[[1]])))) == firstRow - 1) stopifnot(sapply(rawsheets_GL, function(x) min(which(!is.na(x[[6]])))) == firstRow - 2) ## Rows 13 & 14 are the actual start nms.13 <- sapply(rawsheets_GL, function(d) unlist(trim(d[firstRow - 2, ]))) nms.14 <- sapply(rawsheets_GL, function(d) unlist(trim(d[firstRow - 1, ]))) ## should be of equal length stopifnot(length(nms.13) == length(nms.14)) ## Drop the accrual columns that will be duplicated shortly AccrualColsDropping <- c("Digital Accrual", "DS Accrual", "Mobile Accrual", "Video Accrual", "TVT Dig. Accrual", "TVT Mob. Accrual") nms.14 <- lapply(nms.14, function(x) ifelse(x %in% AccrualColsDropping, "", x) ) ## Actual & Invoice apply to the column left and right # nms.13 <- lapply(seq(nms.13), function(i) { for (i in seq(nms.13)) { x <- nms.13[[i]] ## Beginning Oct 2014, a fourth column has been added for "Accrual" and "Invoiced" inds.neigh <- seq.int(from=ifelse(sheetdates[[i]] >= date_DS_added, -2, -1), to=1, by=1) inds.neighNo0 <- setdiff(inds.neigh, 0L) setNames(nm=c(LETTERS, getLLL(2))[seq(length(nms.14[[i]]))], obj = nms.14[[i]]) ## Invoiced ind.inv <- which(x == "Invoiced") if (!all(is.na( x [inds.neighNo0 + ind.inv] ))) stop ("'Invoiced' neighbors not NA for current iteration") else { nms.14[[i]] [ inds.neigh + ind.inv] <- paste(nms.14[[i]] [ inds.neigh + ind.inv], "Invoiced") } ## Accrual ind.accr <- which(x == "Accrual") if (!all(is.na( x [inds.neighNo0 + ind.accr] ))) stop ("Accrual neighbors not NA for current iteration") else { if (is.na(nms.14[[i]][inds.neigh + ind.accr][2])) nms.14[[i]][inds.neigh + ind.accr][2] <- "DS" ## the second group of columns is missing its header. nms.14[[i]] [ inds.neigh + ind.accr] <- paste(nms.14[[i]] [ inds.neigh + ind.accr], "Accrual") } # ## Invoiced # ind.inv <- which(x == "Invoiced") # if (!all(is.na( x [c(-1, 1) + ind.inv] ))) # stop ("'Invoiced' neighbors not NA for current iteration") # else # nms.14[[i]] [ (-1):1 + ind.inv] <- paste(nms.14[[i]] [ (-1):1 + ind.inv], "Invoiced") # # ## Accrual # ind.accr <- which(x == "Accrual") # if (!all(is.na( x [c(-1, 1) + ind.accr] ))) # stop ("Accrual neighbors not NA for current iteration") # else # nms.14[[i]] [ (-1):1 + ind.accr] <- paste(nms.14[[i]] [ (-1):1 + ind.accr], "Accrual") } ## Use row 14 as the names nms <- nms.14 ## replace NAs with ''. They will be dropped later. nms <- lapply(nms, removeNA, repl="") ## For some reason there are extra (hidden) columns named April, May. Drop these. nms <- lapply(nms, function(x) {if (x[7]=="April" && x[8] == "May") x[7:8] <- ""; x} ) ## the "Accrual" Column is different has slightly different names in different, but should always be column Q (17) nms <- lapply(nms, function(x) {if (grepl("(.*-\\s*)?Accrual$", x[17])) x[17] <- "Accrual" else print("Accrual not col#17 for x[17]"); x}) stopifnot(sapply(nms, function(x) sum(x=="Accrual") == 1)) ## Create empty lists to store results cleanedSheets <- emptylist(rawsheets_GL) colsNotDropping.list <- emptylist(rawsheets_GL) totalsForChecking <- emptylist(rawsheets_GL) ## These columns will be dropped. Only Some sheets have these, and it messes it up for everyone. colsManuallyDropping <- c("", "GP Inv", "GP Invoiced", "GP Invoiced May", "Reporting") ## THESE CANNOT BE DROPPED, INSTEAD NEED TO BE COMBINED INTO ACCRUAL colsAccrual <- c("Accrual", "IODA Accrual", "IRIS Accrual", "IODA Accrual Combined") DT.hasPositiveAccrual <- data.table(sheets, sheetorder=seq(sheets), IODA=NA, IRIS=NA, key="sheets") ### Used to confirm all of the 'April' and 'May' Columns are Zero ---- ### DT.aprilmaycheck <- emptylist(sheets) ## Clean up the rawsheets_GL to drop the unused header info for (sh in names(rawsheets_GL)) { ## The 11th column ("K") should have one or two "total" line(s), ## with the values in the column prior to it .total_row <- grep("Total", rawsheets_GL[[sh]][[11]], ignore.case=TRUE) totalsForChecking[[sh]] <- cbind(date=sheetdates[[sh]], rawsheets_GL[[sh]] [.total_row, 10:11]) DT <- rawsheets_GL[[sh]] ## Safety check, to make sure I dont run this for loop twice and drop data accidentally. stopifnot(min(which(!is.na(DT[[1]]))) == firstRow - 1) ## Number of rows N <- nrow(DT) ## The data starts at 'firstRow' DT <- as.data.table(DT [firstRow:N, ]) ## Apply the names setnames(DT, unlist(nms[[sh]])) totalsRow <- DT[, which(LABEL=="Totals")] ## This value should not be blank stopifnot(length(totalsRow) > 0) ## Separate the TOTALS from the BODY # DT.Total <- DT[totalsRow:nrow(DT)] DT.Body <- DT[1:(totalsRow-1)] DT.Body[, accounting_month := sheetdates[[sh]] ] ## Capture the April / May count of blanks DT.aprilmaycheck[[sh]] <- data.table(sheets=sh, setNames(DT.Body[ , 7:8, with=FALSE], c("A", "M")) [, lapply(.SD, function(x) sum(!is.na(x)))], key="sheets") ## Combine the accrual columns. (This really only applies to "Apr 2013 Revenue") if (length( IodaIrisCols <- extract("(IODA|IRIS) Accrual", DT.Body) )) { DT.Body[, Accrual := rowSums(currToNumeric(.SD), na.rm=TRUE), .SDcols=c("Accrual", IodaIrisCols)] ## ---- -Note which sheets have none-zero values for each column ---- ## IODA.col <- extract("IODA", IodaIrisCols) if (length(IODA.col) > 1) stop("IODA more than one col") else if (length(IODA.col) > 0) DT.hasPositiveAccrual[.(sh), IODA := sum(currToNumeric(DT.Body[[IODA.col]]), na.rm=TRUE) > 0] IRIS.col <- extract("IRIS", IodaIrisCols) if (length(IRIS.col) > 1) stop("IRIS more than one col") else if (length(IRIS.col) > 0) DT.hasPositiveAccrual[.(sh), IRIS := sum(currToNumeric(DT.Body[[IRIS.col]]), na.rm=TRUE) > 0] ## ---- -Note which sheets have none-zero values for each column ---- ## DT.Body[, (IodaIrisCols) := NULL] } ## Manually drop some columns DT.Body <- DT.Body[, names(DT.Body) %ni% colsManuallyDropping, with=FALSE] ## Add in a "DS" column for anything before Oct 2014 ## first confirm it is needed if (parseSheetName(sh) < date_DS_added) { stopifnot(!grepl("^DS ", names(DT.Body))) DT.Body[, paste("DS", c("Invoiced", "Accrual")) := "0.0"] wh.inv <- which(names(DT.Body)=="Digital Invoiced") setcolorderpt(DT.Body, startCols=c((names(DT.Body))[1:wh.inv], "DS Invoiced")) wh.acr <- which(names(DT.Body)=="Digital Accrual") setcolorderpt(DT.Body, startCols=c((names(DT.Body))[1:wh.acr], "DS Accrual")) } ## Assign it back in cleanedSheets[[sh]] <- DT.Body } DT.aprilmaycheck <- rbindlist(DT.aprilmaycheck) if (any(DT.aprilmaycheck[, list(A, M)] != 0) ) warning("Some DT.aprilmaycheck are none-Zero") ## Clean up these DTs, to have it in the proper order setkey(DT.hasPositiveAccrual, sheetorder) ## Confirm that all column names match to the dictionary { nms.cleaned <- lapply(cleanedSheets, names) # x # ## add in DS column? # x # nms.cleaned[sheetdates < date_DS_added] <- # x # lapply(nms.cleaned[sheetdates < date_DS_added], function(x) { # x # wh.inv <- which(x=="Digital Invoiced") # x # wh.acr <- which(x=="Digital Accrual") # x # stopifnot(length(c(wh.inv, wh.acr)) == 2, c(wh.inv, wh.acr) > 1, c(wh.inv, wh.acr) < length(x), wh.acr - wh.inv == 3) # x # c(x[1:wh.inv], "DS Invoiced", x[(wh.inv+1):wh.acr], "DS Accrual", x[(wh.acr+1):length(x)]) # x # }) stopifnot(sapply(nms.cleaned, function(x) length(x) == length(dict.GL_colnames))) matched.names <- sapply(nms.cleaned, function(x) substr(x, nchar(x)-9, nchar(x)) == substr(dict.GL_colnames, nchar(dict.GL_colnames)-9, nchar(dict.GL_colnames)) ) # NEW : stopifnot(mapply(function(x, D) length(x) == (length(dict.GL_colnames) - 2 * !D), x=nms.cleaned, D=sheetdates >= date_DS_added)) # NEW : matched.names <- # NEW : mapply(function(x, D) {dict <- if (D) dict.GL_colnames else dict.GL_colnames[!grepl("^DS ", dict.GL_colnames)] # NEW : substr(x, nchar(x)-9, nchar(x)) == substr(dict, nchar(dict)-9, nchar(dict)) }, x=nms.cleaned, D=sheetdates >= date_DS_added) stopifnot(sapply(as.data.table(matched.names, keep.rownames=TRUE)[rn %ni% c("Statement_ThisMonth_I", "Accrual_ThisMonth")][, !"rn", with=FALSE], all)) ## If all is good, change the names for (i in seq(cleanedSheets)) setnames(cleanedSheets[[i]], names(dict.GL_colnames)) } ## backup the cleanedSheets simply for when DEV'ing I can reload and start from this point jesusForData(cleanedSheets) ### ------------------------------------------ ### ### create DT.totals_from_bottom_of_GL ### ### ------------------------------------------ ### { DT.totals_from_bottom_of_GL <- rbindlist(totalsForChecking) DT.totals_from_bottom_of_GL[, Col10 := currToNumeric(Col10)] DT.totals_from_bottom_of_GL[Col11 != "Total (AA-AG)", Col11 := "Total"] DT.totals_from_bottom_of_GL[Col11 == "Total (AA-AG)", Col11 := "Total_aa_ag"] DT.totals_from_bottom_of_GL <- dcast.data.table(DT.totals_from_bottom_of_GL, date~Col11, value.var="Col10") DT.totals_from_bottom_of_GL[, total_minus_total_aa_ag := Total - Total_aa_ag] DT.totals_from_bottom_of_GL[total_minus_total_aa_ag != 0] DT.totals_from_bottom_of_GL <- DT.totals_from_bottom_of_GL[!(is.na(Total) & is.na(Total_aa_ag))] ## The total_minus_total_aa_ag should be $0. There are a few known exceptions. -- Other than those, throw an error tmp.known_exceptions <- as.Date(c("2012-05-01", "2012-06-01", "2013-02-01")) tmp.known_exceptions <- c(tmp.known_exceptions, DT.totals_from_bottom_of_GL[, max(date)]) ## 2015-02-10 -- I believe somethign changed in the worksheet. Any new dates may not add up tmp.known_exceptions <- c(tmp.known_exceptions, seq(as.Date("2014-11-01"), today(), by="month")) stopifnot(DT.totals_from_bottom_of_GL[!equals0(total_minus_total_aa_ag), date %in% tmp.known_exceptions]) rm(tmp.known_exceptions) ## Drop the other columns DT.totals_from_bottom_of_GL[, c("Total_aa_ag", "total_minus_total_aa_ag") := NULL] setkeyIfNot(DT.totals_from_bottom_of_GL, date) } ### ------------------------------------------ ### ### create DT.GL from cleanedSheets ### ### ------------------------------------------ ### ## Create the GL data.table DT.GL <- rbindlist(cleanedSheets) DT.GL[, DMV := toupper(DMV)] if (DT.GL[, sum(is.na(DMV))] < 4) DT.GL <- DT.GL[!is.na(DMV)] ## all of iTunes is getting one id DT.GL[grep("iTunes", storepart, ignore.case=TRUE), storepart := gsub(".*iTunes.*", "iTunes", storepart, ignore.case=TRUE)] ## Cleanup some StorePart names. Mostly Capitalization DT.GL[grep("emusic", storepart, ignore.case=TRUE), storepart := gsub("emusic", "eMusic", storepart, ignore.case=TRUE)] DT.GL[grep("MusicLoad", storepart, ignore.case=TRUE), storepart := gsub("MusicLoad", "MusicLoad", storepart, ignore.case=TRUE)] DT.GL[storepart == "Groove/Livewire (Canada)", storepart := "Groove Mobile (Livewire) - Canada"] ## Fix IDs for some known human errors { DT.GL[storepart=="Basepoint Media" & is.na(ID_greatplains), ID_greatplains := "BASE0001" ] DT.GL[storepart=="Internet Bookshop Italia" & is.na(ID_greatplains), ID_greatplains := "IBSI0001" ] DT.GL[storepart=="Orange Suisse - Digital" & is.na(ID_greatplains), ID_greatplains := "ORAN0003" ] DT.GL[storepart=="Beats Music" & (is.na(ID_greatplains) | ID_greatplains == "BELL0001" ), ID_greatplains := "BEAT0003" ] DT.GL[storepart=="Gee Beyond Holdings" & (is.na(ID_greatplains) | ID_greatplains == "GEEB001" ), ID_greatplains := "GEEB0001" ] DT.GL[storepart=="Monstar Labs FM Japan" & (is.na(ID_greatplains) | ID_greatplains == "NOKI0001" ), ID_greatplains := "MUSI9998" ] DT.GL[storepart=="Revibe AB" & (is.na(ID_greatplains) | ID_greatplains == "RIGH0003" ), ID_greatplains := "REVI9998" ] DT.GL[storepart=="Apach Network S.A" & (is.na(ID_greatplains) | ID_greatplains == "MUSI00011" ), ID_greatplains := "MUSI0011" ] DT.GL[storepart=="Omnifone - Qriocity" & (is.na(ID_greatplains) | ID_greatplains == "OMNI000Q" ), ID_greatplains := "OMNI0001" ] DT.GL[storepart=="Cricket" & (is.na(ID_greatplains)) & accounting_month < '2013-01-01', ID_greatplains := "CRIC0001" ] message(" ** Confirm with Michael / Pras about Microsoft VOD -- using id 'MICROVOD' **") DT.GL[storepart=="Microsoft (VOD) (X-Box)", ID_greatplains := "MICROVOD"] dict.Amazon= c( "Amazon On Demand (DOD)" = "Amazon DOD" , "Amazon On Demand (DOD Europe)" = "Amazon DOD" , "Amazon On Demand (US Video)" = "Amazon Video" , "Amazon Prime" = "Amazon Video" ## All other, just "Amazon" ) unique(DT.GL[grepl("^Amazon", storepart), list(storepart, ID_greatplains)]) invisible() } ## CONFIRM: ID_greatplains should be ALL NA or Not Any NA, for each storepart stopifnot(DT.GL[, {nas <- is.na(ID_greatplains); all(nas) | !any(nas) }, by=storepart][, (V1) ] ) ## Set keys setkeyIfNot(DT.GL, kCols.GL, organize=TRUE) ## Find the first & last date per store DT.GL[, GL_firstDate_forS := as.Date(min(accounting_month), origin=.origin), by=c(setdiff(kCols.GL, c("accounting_month", "DMV")))] DT.GL[, GL_lastDate_forS := as.Date(max(accounting_month), origin=.origin), by=c(setdiff(kCols.GL, c("accounting_month", "DMV")))] ## ONLY KEEP THOSE BEYOND THE minDateToKeep DT.GL <- DT.GL[accounting_month >= minDateToKeep] ## Convert dollar dollar strings to numbers. (This did not happen automatically because of the extra rows before the body of the table) ## Most columbs in DT.GL are numeric, except for those in the kCols.GL and date cols (and Music_vs_Video_by_DMV if DEV'ing) non_numeric_cols <- c(kCols.GL, nwhich(sapply(DT.GL, inherits, "Date")), "Music_vs_Video_by_DMV") ## Music_vs_Video_by_DMV not yet in the DT, but when DEVing, this will mess up the col otherwise for (col in setdiff(names(DT.GL), non_numeric_cols)) DT.GL[, (col) := currToNumeric(get(col))] ## Calculate the actual statement value for this month. (Sometimes in column T, sometimes in I) DT.GL[, Statement_ThisMonth := Statement_ThisMonth_I] [is.naor0(Statement_ThisMonth) & !is.naor0(Statement_ThisMonth_T), Statement_ThisMonth := Statement_ThisMonth_T] ## 'Subtot_StatementsPlusTrueUps_ThisMonth' should NOT have any NAs. ## there is one in Sept 2013. Other than that, give warning ## ## EXPECTED NAs in these two rows. Change to 0: DT.GL[is.na(Subtot_StatementsPlusTrueUps_ThisMonth) & {(storepart == "iTunes" & accounting_month == "2014-10-01") | (storepart == "IC Agency" & accounting_month == "2013-09-01")}, Subtot_StatementsPlusTrueUps_ThisMonth := 0] if (DT.GL[accounting_month != "2013-09-01", any(is.na(Subtot_StatementsPlusTrueUps_ThisMonth))]) { warning("There are NAs in DT.GL$Subtot_StatementsPlusTrueUps_ThisMonth (column 'J')\nPlease Investigate for ") print(DT.GL[accounting_month != "2013-09-01"][(is.na(Subtot_StatementsPlusTrueUps_ThisMonth))]) ## Change the remainder to 0 to not crash downstream DT.GL[is.na(Subtot_StatementsPlusTrueUps_ThisMonth), Subtot_StatementsPlusTrueUps_ThisMonth := 0] } ## Calculate the actual Total DT.GL[, Total_ThisMonth := Subtot_StatementsPlusTrueUps_ThisMonth + Accrual_ThisMonth] ## V is video; D & M are Music DT.GL[, Music_vs_Video_by_DMV := ifelse(DMV == "V", "Video", "Music")] ## Manually fix error. ## In May & June 2013 there appears to be "Video" monies accounted to "Send Me Mobile" ## The same ammount is added in may then subtracted in June. This is the only time Send Me Mobile ## has any video, it is otherwise always Music. ## I believe this is an error and will drop it. if (DT.GL[storepart=="Send Me Mobile" & Music_vs_Video_by_DMV == "Video", sum(Total_ThisMonth)] == 0) DT.GL <- DT.GL[! (storepart=="Send Me Mobile" & Music_vs_Video_by_DMV == "Video") ] ## UPDATE 2015-02-10 ## For iTunes, beginning on the December statement, there is a Video line item. ## we will simply mark it as D for now. But speak to Mike B / Pras 1as to t1why 1its 1there. DT.GL[storepart == "iTunes" & DMV == "V" & accounting_month >= "2014-01-01" , `:=`(DMV = "D", Music_vs_Video_by_DMV="Music")] ## Every store is either ONLY Music or ONLY Video. (Except "Send Me Mobile" fixed above) ## Later on we check: stopifnot(sapply(c("storeid", "store_name", "storepart"), isUniqueByGroup, DT=DT.GL, colsToCheck="Music_vs_Video_by_DMV")) stopifnot(sapply(c("storepart"), isUniqueByGroup, DT=DT.GL, colsToCheck="Music_vs_Video_by_DMV")) ## These are the columns that should talley up to their resepctive subtotal column. Confirm they do, then drop these. DMV_statement_cols <- c(D="Music_Statement", M="Mobile_Statement", V="Video_Statement", DS="Streaming_Statement") DMV_acrrual_cols <- c(D="Music_Accrual", M="Mobile_Accrual", V="Video_Accrual", DS="Streaming_Accrual") ## ------- CHECK FOR & CAPTURE ANY ERRORS ------- ### { Errors <- list() ## Some stores have more than one DMV Errors[["MoreThanOne_DMV"]] <- unique(DT.GL[storepart %in% DT.GL[, lunique(DMV), keyby=storepart][(V1 != 1), storepart], list(accounting_month, storepart, ID_greatplains, DMV)], by=c("storepart", "DMV")) ## Some stores have $0 gross always. These may have been originally hidden, etc. Drop these. stores.dropping.nogross <- DT.GL[, sum(Total_ThisMonth == 0) == .N, by=storepart] [(V1), storepart] ## confirm stopifnot(DT.GL[storepart %in% stores.dropping.nogross][, all(Total_ThisMonth == 0)]) Errors[["Stores_With_No_Gross_EVER"]] <- DT.GL[storepart %in% stores.dropping.nogross] ## Drop those stores if (length(stores.dropping.nogross)) { message(sprintf(" * Dropping %i stores (%i rows) that have $0 gross throughout the whole GL.", length(stores.dropping.nogross), nrow(DT.GL[storepart %in% stores.dropping.nogross]))) DT.GL <- DT.GL[storepart %ni% stores.dropping.nogross] } ## Some stores do not report every month Errors[["No_Statement_ThisMonth"]] <- DT.GL[accounting_month < (Sys.Date() - 60)][is.naor0(Statement_ThisMonth)] ## Columns E + F + I equal Column J (less floatingpoint error) Errors[["Subtot_StatementsPlusTrueUps_ThisMonth_notequals_sum_of_EFI"]] <- DT.GL[abs(Subtot_StatementsPlusTrueUps_ThisMonth - (Statements_older_ThanMonth + TrueUp_LastMonth + Statement_ThisMonth_I)) > 1e-4] ## The sum of the "Accrual" or "Invoiced" separated DMV columns should equal their respective 'total' column Errors[["Subtot_StatementsPlusTrueUps_ThisMonth_byDMV"]] <- DT.GL[DT.GL[, {.dcol <- get(DMV_statement_cols[[d]]); removeNA(Subtot_StatementsPlusTrueUps_ThisMonth, 0) != removeNA(.dcol, 0)}, by=list(d=DMV)]$V1] Errors[["Accrual_ThisMonth_byDMV"]] <- DT.GL[DT.GL[, {.dcol <- get(DMV_acrrual_cols[[d]]); removeNA(Accrual_ThisMonth, 0) != removeNA(.dcol, 0)}, by=list(d=DMV)]$V1] ## More than one of the Accrual Columns have none-zero values Errors[["Statement_Off3"]] <- DT.GL[DT.GL[, .I[!(rowSums(.SD==0) %in% 2:3)], .SDcols=DMV_statement_cols]] Errors[["Accrual_Off3"]] <- DT.GL[DT.GL[, .I[!(rowSums(.SD==0) %in% 2:3)], .SDcols=DMV_acrrual_cols]] ## Then Drop these columns, they are not needed DT.GL[, (DMV_statement_cols) := NULL] DT.GL[, (DMV_acrrual_cols) := NULL] ## The accrual Part1 & Part2 should add up. If not, stop. If so, drop the column. { ## Accrual_ThisMonth should be the sum of the two parts. (Replacing NAs with 0) ## Note, this will not sum up for those few April 2013 Accruals were IODA/IRIS were split out stopifnot(DT.GL[!(accounting_month == '2013-04-01' & storepart %in% c("Amazon - US", "Cricket", "Google", "MediaNet / MusicNet", "Spotify", "deezer - EU")) , removeNA(Accrual_part1, 0) + removeNA(Accrual_part2, 0) == removeNA(Accrual_ThisMonth, 0)]) ## IF the above is correct, then we can drop the two parts. DT.GL[, paste0("Accrual_part", 1:2) := NULL] message(" * 'Accrual_part1' & 'Accrual_part2' have been removed") } DT.GL[, TrueUp_FutureMonth_RickCalculated := removeNA(Statement_ThisMonth_T, 0) - removeNA(Accrual_ThisMonth, 0) ] DT.GL[, TrueUp_DIFF_RICK_and_GL := TrueUp_FutureMonth_RickCalculated - TrueUp_FutureMonth] # set to 0 DT.GL[abs(TrueUp_DIFF_RICK_and_GL) < 1e-5 | is.na(TrueUp_DIFF_RICK_and_GL), TrueUp_DIFF_RICK_and_GL := 0] ## There should not be any TRUEUP Errors Errors[["TrueUps_RickCalculated"]] <- DT.GL[TrueUp_DIFF_RICK_and_GL != 0] if (nrow(Errors$TrueUps)) warning("There are TrueUp Errors!") # TrueUp_FutureMonth[date-1] == TrueUp_LastMonth[date] Errors[["TrueUps_Comparing_FutureMonth_LastMonth"]] <- DT.GL[ , { datekeys <- seq(from=min(accounting_month), max(accounting_month), by="month") # setkey(.SD, accounting_month) # .SD[.(datekeys)] [TrueUp_FutureMonth!=shiftUp(TrueUp_LastMonth)] .SD[order(accounting_month)][accounting_month %in% datekeys] [TrueUp_FutureMonth!=shiftUp(TrueUp_LastMonth)] } , .SDcols = c("accounting_month", "TrueUp_FutureMonth", "TrueUp_LastMonth", "Statement_ThisMonth_T", "Accrual_ThisMonth") , by=list(st=storepart) ] ## Generally, should have a value in Statement_ThisMonth_I or Accrual_ThisMonth, but not both. ## These normally represent oddities in store reporting, not necessarily any error. Errors[["Has_accrual_and_Statment"]] <- DT.GL[!is.naor0(Statement_ThisMonth_I) & !is.naor0(Accrual_ThisMonth)] ## Should have value in I or T, but not both. ## These are likely human errors. They have probably been placed in the wrong spot. Errors[["Has_values_in_columns_I_and_T"]] <- DT.GL[!is.naor0(Statement_ThisMonth_I) & !is.naor0(Statement_ThisMonth_T)] } ## ------- END: CHECK FOR & CAPTURE ANY ERRORS ------- ### ## Leave parens, and break at dash # DT.stores_GP[, spart := gsub("((\\s?)|(\\s*-\\s*))\\(.*\\)(\\s*-.*)*", "", storepart)] ## take the first part storepart which will serve as ID_greatplains if it is otherwise blank, and can serve as the store_name if it is blank DT.GL[, spart := gsub("(\\s*-\\s*).*", "", gsub("^24/7 .*", "24/7", storepart))] DT.GL[is.na(ID_greatplains), ID_greatplains := paste0("_", gsub(" .*", "", spart))] { " Statement_ThisMonth_I := If the store reported to us in time for Month Close Accrual_ThisMonth := Value used if the store did not report to us in time for Month Close Statement_ThisMonth_T := Filled in after the month closes, usually in retrospect. " invisible() } ## CONFIRM AGAINST DT.totals_from_bottom_of_GL { setkeyIfNot(DT.GL, "accounting_month", verbose=FALSE) setkeyIfNot(DT.totals_from_bottom_of_GL, "date", verbose=FALSE) ## Add in the Total_Calculated column DT.totals_from_bottom_of_GL[DT.GL[, list(Total_Calculated = sum(Total_ThisMonth)), keyby=list(date=accounting_month)], Total_Calculated := Total_Calculated] ## Calculate the difference DT.totals_from_bottom_of_GL[, diff_from_calculated := Total - Total_Calculated] ## adjust for Machine precision errors DT.totals_from_bottom_of_GL[abs(diff_from_calculated) < 0.025, diff_from_calculated := 0] ## There are three expected none-zero values for the diff. The rest needs to be checked stopifnot(DT.totals_from_bottom_of_GL[!equals0(diff_from_calculated), date %in% as.Date(sprintf("2013-%2i-01", 4:6)) ]) ## Identify errors ## < see > formnumb(DT.totals_from_bottom_of_GL[diff_from_calculated != 0]) } # DT.GL[, { # # browser() # d_1 <- as.Date(d, origin=.origin) %m-% months(1) # merge( # DT.GL[.(d_1), list(storepart, TrueUp_FutureMonth)][, TrueUp_FutureMonth, keyby=storepart] # , .SD[, TrueUp_LastMonth, keyby=storepart] # , all=TRUE) # } , by=list(d=accounting_month)] # # DT.TrueUp_ThisMonthLastMonth_Check[!is.na(TrueUp_FutureMonth)] # # source("~/git/orch/src/chartio/readgl.r") # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # ~~~~~~~~~~~~~ LEFT OFF HERE ~~~~~ # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # # (1) dictionary for the columns <[ DONE! ]> # (2) Keep only those needed <[ DONE! ]> # (3) Confirm math # # ## ----- MATCH TO STORES IN OA -------- ## DT.stores <- get_dim_store(refresh=FALSE) DT.stores2 <- copy(DT.stores[, list(store_name, storeid)]) setkey(DT.stores2, store_name) f.in.greatplains <- ingest.p("greatplains_storeid.csv") stopifnot(file.exists(f.in.greatplains)) DT.greatplains_oa_map <- fread(f.in.greatplains) ## Extract the storepart & greatplains id from DT.GL, along with DMV DT.storeparts <- copy(DT.GL[, list(DMV=unique(DMV)), keyby=list(storepart, greatplains_storeid=ID_greatplains)]) ## The only expected missing storepart is 'Breakage' if (nrow(DT.storeparts[greatplains_storeid %ni% DT.greatplains_oa_map$greatplains_storeid])) { warning ("TODO: Dont forget to address 'Breakage'") print(DT.storeparts[greatplains_storeid %ni% DT.greatplains_oa_map$greatplains_storeid]) ## If there are others, address those as well } firstWordOf <- function(x) sapply(strsplit(x, " "), "[[", 1L) ## initialize the store_name & storeid columns DT.storeparts[, `:=`(store_name=NA_character_, storeid=NA_integer_)] # ### This next part is the manual matching I had previously done before given a mapping # if (FALSE) # { # # ## Extract the first and first two words from storepart, which will be used for matching # DT.storeparts[, firstword := gsub(",", "", sapply(strsplit(storepart, " "), function(x) x[[1]])) ] # DT.storeparts[, first2words := sapply(strsplit(storepart, " "), function(x) ifelse(length(x)==1, x, pasteC(x[1:2], C=" ")) ) ] # # ## Manually append the Amazon's # DT.storeparts[ storepart %in% c("Amazon On Demand (US Video)", "Amazon Prime") # , c("store_name", "storeid") := list("Amazon Instant Video", 376L) ] # DT.storeparts[ storepart %in% c("Amazon On Demand (DOD Europe)", "Amazon On Demand (DOD)") # , c("store_name", "storeid") := list("Amazon DOD", 419L) ] # DT.storeparts[ is.na(storeid) & storepart %like% "Amazon" # , c("store_name", "storeid") := list("Amazon Digital Services Inc.", 187L) ] # ## iTunes # DT.storeparts[ grepl("^iTunes", storepart), c("store_name", "storeid") := list("iTunes", 1L) ] # ## Manual setting # DT.storeparts[ storepart %like% "Internet Q", c("store_name", "storeid") := DT.stores2[.("InternetQ SA dba Akazoo and MP Greek")], by=list(s=storepart)] # DT.storeparts[ storepart %like% "Library Ideas", c("store_name", "storeid") := DT.stores2[.("Library Ideas / Freegal")], by=list(s=storepart)] # DT.storeparts[ storepart %like% "^Orange", c("store_name", "storeid") := DT.stores2[.("Orange Switzerland")], by=list(s=storepart)] # DT.storeparts[ storepart %like% "^Groove", c("store_name", "storeid") := DT.stores2[.("Livewire Mobile, Inc.")], by=list(s=storepart)] # DT.storeparts[ storepart %like% "^PocketGroup", c("store_name", "storeid") := DT.stores2[.("Pocketgroup")], by=list(s=storepart)] # DT.storeparts[ storepart %like% "^InProdicon", c("store_name", "storeid") := DT.stores2[.("Inprodicon")], by=list(s=storepart)] # DT.storeparts[ storepart %like% "^TT Net", c("store_name", "storeid") := DT.stores2[.("TTNET")], by=list(s=storepart)] # DT.storeparts[ storepart %like% "^Simfy", c("store_name", "storeid") := DT.stores2[.("simfy GmbH")], by=list(s=storepart)] # DT.storeparts[ storepart %like% "^Neowizbugs", c("store_name", "storeid") := DT.stores2[.("Neowiz Bugs")], by=list(s=storepart)] # DT.storeparts[ storepart %like% "^Guerva", c("store_name", "storeid") := DT.stores2[.("Guvera")], by=list(s=storepart)] # DT.storeparts[ storepart == "Microsoft (VOD) (X-Box)", c("store_name", "storeid") := DT.stores2[.("Microsoft X-Box")], by=list(s=storepart)] # DT.storeparts[ storepart == "HMV", c("store_name", "storeid") := DT.stores2[.("HMV Canada")], by=list(s=storepart)] # DT.storeparts[ storepart == "In Demand", c("store_name", "storeid") := DT.stores2[.("InDemand (Cable VOD)")], by=list(s=storepart)] # DT.storeparts[ storepart == "I.C. Agency Co. Ltd", c("store_name", "storeid") := DT.stores2[.("IC Agency")], by=list(s=storepart)] # DT.storeparts[ storepart == "One Screen", c("store_name", "storeid") := DT.stores2[.("OneScreen")], by=list(s=storepart)] # DT.storeparts[ storepart == "MySpace", c("store_name", "storeid") := DT.stores2[.("Myspace")], by=list(s=storepart)] # DT.storeparts[ storepart == "MySpace (Video)", c("store_name", "storeid") := DT.stores2[.("Myspace Video")], by=list(s=storepart)] # DT.storeparts[ storepart == "AMI - Entertainment", c("store_name", "storeid") := DT.stores2[.("AMI Entertainment")], by=list(s=storepart)] # DT.storeparts[ storepart == "YouTube (Music - Video & Audio)", c("store_name", "storeid") := DT.stores2[.("YouTube")], by=list(s=storepart)] # DT.storeparts[ storepart == "YouTube (Standard - DMGI Video)", c("store_name", "storeid") := DT.stores2[.("YouTube Movies")], by=list(s=storepart)] # DT.storeparts[ storepart == "Arvato (Mondia)", c("store_name", "storeid") := DT.stores2[.("Mondia Media")], by=list(s=storepart)] # DT.storeparts[ storepart == "MaxSound (Liquid Spins)", c("store_name", "storeid") := DT.stores2[.("Liquid- Anderson Merchandisers")], by=list(s=storepart)] # DT.storeparts[ storepart == "Send Me Mobile", c("store_name", "storeid") := DT.stores2[.("SendMe Mobile")], by=list(s=storepart)] # DT.storeparts[ storepart == "Sony Network (Video)", c("store_name", "storeid") := DT.stores2[.("Sony Network Entertainment")], by=list(s=storepart)] # DT.storeparts[ storepart == "Sony Network Entertainment", c("store_name", "storeid") := DT.stores2[.("Sony Connect")], by=list(s=storepart)] # { # DT.storeparts[grepl("^Real", storepart), c("store_name", "storeid") := DT.stores2[.("REAL Networks")], by=list(s=storepart)] # DT.storeparts[grepl("Real\\s+GmbH", storepart), c("store_name", "storeid") := DT.stores2[.("Real GmbH")], by=list(s=storepart)] # DT.storeparts[ storepart == "Real/Rhapsody-EU", c("store_name", "storeid") := DT.stores2[.("Rhapsody")], by=list(s=storepart)] # invisible() # } # ## First try to match each name in DT.stores2 to a name in storeparts # DT.storeparts[is.na(storeid), c("store_name", "storeid") := DT.stores2[.(s)], by=list(s=storepart)] # DT.storeparts[is.na(storeid), c("store_name", "storeid") := DT.stores2[.(s)], by=list(s=firstword)] # DT.storeparts[is.na(storeid), c("store_name", "storeid") := DT.stores2[.(s)], by=list(s=first2words)] # ## Next grepl # DT.storeparts[is.na(storeid), c("store_name", "storeid") := DT.stores2[grepl(escapeRegEx(s), store_name, ignore.case=TRUE) & sum(grepl(escapeRegEx(s), store_name, ignore.case=TRUE))==1], by=list(s=storepart)] # DT.storeparts[is.na(storeid), c("store_name", "storeid") := DT.stores2[grepl(escapeRegEx(s), store_name, ignore.case=TRUE) & sum(grepl(escapeRegEx(s), store_name, ignore.case=TRUE))==1], by=list(s=first2words)] # DT.storeparts[is.na(storeid), c("store_name", "storeid") := DT.stores2[grepl(escapeRegEx(s), store_name, ignore.case=TRUE) & sum(grepl(escapeRegEx(s), store_name, ignore.case=TRUE))==1], by=list(s=firstword)] # DT.storeparts[is.na(storeid), store_name := trim(firstword)] # } ## Merge in the mapping from CSV matchKey(DT.storeparts, DT.greatplains_oa_map, key=c("storepart","greatplains_storeid"), superset.ok=FALSE, organize=TRUE) DT.storeparts[DT.greatplains_oa_map, `:=`(OA_storeid=OA_storeid, OA_storename=OA_storename)] ## A Lot of stores are labeled with a simple " - BFM" These are getting added more frequently and can cause bugs DT.storeparts[, bfm := grepl("\\bbfm\\b", storepart, ignore.case=TRUE)] # DT.storeparts[, gsub(" (- |\\()?BFM\\)?", "", storepart), by=greatplains_storeid] ## Assign the OA values as default DT.storeparts[(storeid != OA_storeid) | is.na(storeid), `:=`(storeid=OA_storeid, store_name=OA_storename)] ## Check for any missing values. Output for Joanna if (nrow(DT.storeparts[is.na(storeid)])) { message("Some stores from GL remain with no ID") print(DT.storeparts[is.na(storeid)]) ## Output to CSV { DT.gp_ids_missing <- DT.storeparts[is.na(OA_storeid), list(storepart, greatplains_storeid, DMV, OA_storeid, OA_storename)] ## Add in revenue information DT.gp_ids_missing[DT.GL[, sum(Total_ThisMonth), keyby=storepart], gross_rev_2013_2014 := V1, allow=TRUE] f.missing.greatplains.out <- out.p("greatplains_missing_storeid", ext="csv") write.csv(DT.gp_ids_missing, f.missing.greatplains.out, row.names=FALSE) message("Missing greatplains ID have been outputed to \n\t ", f.missing.greatplains.out, "\n") suppressWarnings(rm(DT.gp_ids_missing, f.missing.greatplains.out)) } } stopifnot(nrow(DT.storeparts[greatplains_storeid %in% c("GP_MUSI0011", "MUSI0011")]) >= 1) ## Now the storeparts info needs to make it back into the DT.GL { matchKey(DT.storeparts, DT.GL, key="storepart") ## just to be safe, drop storeid, which we will merge in from DT.storepart if (!is.null(DT.GL$storeid)) DT.GL[, storeid := NULL] ## Add to DT.GL the storeid and store_name from the DT.storeparts that was just computed DT.GL[DT.storeparts, c("store_name", "storeid") := list(i.store_name, i.storeid), allow=TRUE] ## Some id's are missing and we will use the ID_greatplains which is alpha, so convert to character to allow those values in. DT.GL[, storeid := as.character(storeid)] DT.GL[is.na(storeid), storeid := paste0("GP_", ID_greatplains)] ## some NA's in store_name remain. Use spart for those (removing S.A, Ltd. etc) DT.GL[is.na(store_name), store_name := gsub(" (S.A|(Pty )?Ltd.)$", "", spart)] } ## FOR FORECASTING { if (!is.null(fakeToday)) { DT.GL <- DT.GL[accounting_month <= fakeToday.GL] ## There should not be any remaining forws with *FIRST* date beyond fake today if (any(DT.GL[, GL_firstDate_forS > fakeToday.GL])) stop("Some of * DT.GL[, GL_firstDate_forS > fakeToday.GL] * are TRUE for fakeToday.GL = ", fakeToday.GL, "\n") ## Adjust the *LAST* date DT.GL[, GL_lastDate_forS := as.Date(max(accounting_month), origin=.origin), by=c(setdiff(kCols.GL, "accounting_month"))] } ## Set attributes. Note these are NULL if not forecasting attr(DT.GL, "fakeToday.GL") <- fakeToday.GL attr(DT.GL, "fakeToday") <- fakeToday invisible() } jesusForData_Forecasting(DT.GL) jesusForData_Forecasting(last_modified)