# ---------------------------------------------------------------------------------------------------- # # # can run using # Revoscript -e '.us(); .g(); source("~/git/orch/src/MgmtReport/GL Auto Latest Forecast Update.r")' # # ---------------------------------------------------------------------------------------------------- # if (getProjName() != "MgmtReport" || !exists("kCols.splitgroup")) { message("Starting Email-the-Forecast script & project needs to be changed from '", getProjName() , "' to 'MgmtReport'") setScience("MgmtReport", load=TRUE, create=TRUE, subl=FALSE, quiet=TRUE) } lib(XLConnect) # lib(WriteXLS) lib(mailR) DT.stores <- get_dim_store(refresh=TRUE) matchKey(DT.forecasts , DT.ancillary_with_forecasts, kCols.splitgroup) DT.forecasts.full <- rbind(DT.forecasts[][, gross := forecasted_value.net] , DT.ancillary_with_forecasts[date>=min(DT.forecasts$date)][, gross := GL_net_forDSOM], fill=TRUE) ## Clean bucket info and Add superbucket info music_and_video_bucketcleanup_(DT.forecasts.full) cleanBuckets_(DT.forecasts.full) colsDropping <- c(forecastColsToBringIn, "forecasted_value.net", "GL_net_forDSOM", "gross_last_modified", "todayIsSimulated", "today", "fakeToday", "fakeToday.GL") colsDropping <- unique(colsDropping) ## Drop some columns DT.forecasts.full[, (colsDropping) := NULL] DT.forecasts.full[is.na(forecasted_hi80.net), forecasted_hi80.net := 1.1 * gross] DT.forecasts.full[is.na(forecasted_lo80.net), forecasted_lo80.net := .9 * gross] DT.forecasts.full[is.na(forecasted_hi95.net), forecasted_hi95.net := 1.2 * gross] DT.forecasts.full[is.na(forecasted_lo95.net), forecasted_lo95.net := .8 * gross] ## Clean up meta columns for Month and Store addColsFrom_(DT.forecasts.full, DT.stores, colsToBring="store_name_short", joinCols="store_name") DT.forecasts.full[, Year := as.numeric(format(date, "%Y"))] DT.forecasts.full[, Month := factor(month.name[month(date)], levels=month.name)] colsUsing <- c("gross", "forecasted_hi80.net", "forecasted_lo80.net", "forecasted_hi95.net", "forecasted_lo95.net") # lib(ggplot2) # DT.predictions <- DT.forecasts.full[, lapply(.SD, sum), .SDcols=colsUsing, keyby=date] # ggplot(data=DT.predictions, x="forecast", aes(color=date)) + geom_point(aes(y=gross)) + thousands.y() + geom_bar() # ggplot(data=DT.predictions, aes(fill=Month)) + geom_bar(aes(x=1, y=gross), position="dodge", stat="identity")+ thousands.y() + dict <- c("forecasted_hi95.net", "forecasted_lo95.net", "forecasted_hi80.net", "forecasted_lo80.net") selfname_(dict) dict <- gsub("forecasted_lo", "- ", gsub("forecasted_hi", "+ ", gsub("\\.net", " Confidence", dict))) dict <- c(dict, gross = "forecasted gross (net of SC)") storesusing_always <- extract("(Amazon|iTunes|Spotify|Muve|Google)", DT.forecasts.full[, store_name_short]) storesusing_topX <- DT.forecasts.full[, sumn(gross), keyby=c(kCols.datestore, "store_name_short")][, mean(V1), keyby=c(kCols.store, "store_name_short")][order(V1, decreasing=TRUE)][storeid != -1][1:12, store_name_short] storesusing <- unique(c(storesusing_always, storesusing_topX)) stores <- DT.forecasts.full[, lapply(.SD, function(x) round(sum(x)/1000, 1)), .SDcols=colsUsing, keyby=list(Year, Month, "Store"=ifelse(store_name_short %in% storesusing, store_name_short, "All Other Stores"))] ## Order according to Gross for first month tmp.storeLevels <- stores[(Year == min(Year))] [(Month == Month[[1]]), Store[order(-gross)]] tmp.storeLevels <- c(setdiff(tmp.storeLevels, "All Other Stores"), "All Other Stores") stores[, Store := factor(Store, levels = tmp.storeLevels)] stores <- stores[order(Month, Store)] setNamesDict(stores, dict, silent=TRUE) rm(tmp.storeLevels) orchard <- DT.forecasts.full[, c("Overview"="Total Rev Net of SC", lapply(.SD, function(x) round(sum(x)/1000, 1))), .SDcols=colsUsing, keyby=list(Month)] setNamesDict(orchard, dict, silent=TRUE) s_vs_d <- DT.forecasts.full[, lapply(.SD, function(x) round(sum(x)/1000, 1)), .SDcols=colsUsing, keyby=list(Month, "Stream vs Download" = store_musicsuperbucket)] setNamesDict(s_vs_d, dict, silent=TRUE) m_vs_v <- DT.forecasts.full[, lapply(.SD, function(x) round(sum(x)/1000, 1)), .SDcols=colsUsing, keyby=list(Month, "Music vs Video" = music_vs_video)] setNamesDict(m_vs_v, dict, silent=TRUE) music_video_ancillary <- DT.forecasts.full[][ , music_video_or_ancillary := factor(ifelse(is.na(ancillaryline), music_vs_video, ancillaryline), levels=c("Music", "Video", unique(ancillaryline))) ][, c(lapply(.SD, function(x) round(sum(x)/1000, 1))), .SDcols=colsUsing, keyby=list(Month, music_video_or_ancillary)] setNamesDict(music_video_ancillary, dict, silent=TRUE) ### TEMPORARY UNTIL exportXLS works ### ---------------------------------------- ### ## OLD 2014-10-15 nms.sheets <- names(DTs.list) ## OLD 2014-10-15 writeWorksheetToFile(f.out.xlsx ## OLD 2014-10-15 , data = DTs.list ## OLD 2014-10-15 , sheet = nms.sheets ## OLD 2014-10-15 , header = TRUE ## OLD 2014-10-15 , rownames = NULL ## OLD 2014-10-15 # , styleAction = XLC$STYLE_ACTION.XLCONNECT ## OLD 2014-10-15 , styleAction = XLC$STYLE_ACTION.DATATYPE ## OLD 2014-10-15 ) ## OLD 2014-10-15 ## OLD 2014-10-15 wb <- loadWorkbook(f.out.xlsx, create=!file.exists(f.out.xlsx)) ## OLD 2014-10-15 ## OLD 2014-10-15 for (i in seq(nms.sheets)) ## OLD 2014-10-15 setColumnWidth(wb, sheet=nms.sheets[[i]], column=seq(DTs.list[[i]]), width=-1) ## OLD 2014-10-15 saveWorkbook(wb, f.out.xlsx) ## OLD 2014-10-15 rm(wb) f.out.xlsx <- out.p(timeStamp("Automated_Forecasts"), ext="xlsx") zArchiveExceptLatest(f.out.xlsx) # nms.sheets <- c("Company Overview", "Stores", "Streams vs DLs", "Music vs Vid", "Music Video Ancillary") DTs.list <- list( "Company Overview" = orchard , "Stores" = stores , "Streams vs DLs" = s_vs_d , "Music vs Vid" = m_vs_v , "Music Video Ancillary" = music_video_ancillary ) exportXLS.usingXLConnect(f.out.xlsx, DTs.list) ### ---------------------------------------- ### # doesnot work on CENTOS : ## NOT WORKING ON CENTOS # doesnot work on CENTOS : if (FALSE) { # doesnot work on CENTOS : f.out.xlsx <- exportXLS( DT.nms = c("orchard", "stores", "m_vs_v", "s_vs_d") # doesnot work on CENTOS : , SheetNames = c("Company Overview", "Stores", "Music vs Vid", "Streams vs DLs") # doesnot work on CENTOS : , f.name = "forecasts" # doesnot work on CENTOS : , FreezeRow=1 # doesnot work on CENTOS : ) # doesnot work on CENTOS : } ### ---------------------------------------- ### ## ================================================================== ## to <- c(Rick_GMAIL="rsaporta@gmail.com", Lee="lee@theorchard.com", Josh="josh@theorchard.com", Pras="prashant@theorchard.com") # to <- c(Rick_GMAIL="rsaporta@gmail.com", Lee="lee@theorchard.com", Josh="josh@theorchard.com") cc.recipients <- c("Rick Saporta"="rsaporta@theorchard.com") subject <- "Revenue forecast" body <- "Good morning %fname%, Attached please find the latest Revenue Forecasts. %info% Until next time, " attachments <- c(f.out.xlsx) ## TEST RUN -- only send to self if (exists("TEST_RUN") && isTRUE(TEST_RUN)) { to <- getTesterEmailAddresses()$to cc.recipients <- getTesterEmailAddresses()$cc message("\t\t\t\tUsing TEST_RUN settings\n\n to: ", pasteC(capture.output(to), C="\n "), "\n\n cc: ", pasteC(capture.output(cc.recipients), C="\n ")) } email(to=to, subject=subject, body=body, bcc="rsaporta+thesam_out@theorchard.com", cc=cc.recipients, signature="SAM" , use.single.name.instead=TRUE, attachments=attachments, fail.if.cannot.attach=TRUE, verbose=TRUE)