### ~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~ ### { .g() if (!exists(srcDir)) srcDir <- "~/git/orch/src/MgmtReport" ## Which files to parse files_to_readlines <- dir(srcDir, all=TRUE, pattern="^GL_pt(\\d).*\\.r$", full=TRUE) DTs <- c("DT.acc", "DT.mexpanded", "DT.anal", "DT.merged", "DT.split_percs", "DT.GL", "DT.GL.summaries", "DT.mgmtr_summary", "DT.mgmtr_summary_manual", "DT.mgmtr_summary_tall", "DT.stores", "DT.manual", "DT.mainline_group_subtotals_from_rev_summary", "DT.storeparts", "DT.greatplains_oa_map", "DT.rev_summary.molt", "DT.supplychain_percentages", "DT.ancillary_from_rev_summary", "DT.rev_summary", "DT.supplychain_percentages.wide", "DT.Total_Reported_Revenue_By_Month", "DT.manl_crop", "DT.cleaned") DT.lsos <- lsosdt() setnames(DT.lsos, "name", "DT") DT.DT_info <- findAllDTsInFile(files_to_readlines) setFactorsToChars_(DT.DT_info) matchKey(DT.lsos, DT.DT_info, "DT") if (any(DTs %ni% DT.DT_info$DT)) warning(warningCols("Missing DT from DT.DT_info (file reader): ", setdiff(DTs, DT.DT_info$DT))) if (any(DTs %ni% DT.lsos$DT)) warning(warningCols("Missing DT from DT.lsos (in memory): ", setdiff(DTs, DT.lsos$DT))) # if (any(DT.lsos$DT %ni% DTs)) # warning(warningCols("Missing DT from DT.lsos (in memory): ", setdiff(DT.lsos$DT, DTs))) # if (any(DT.DT_info$DT %ni% DTs)) # warning(warningCols("Missing DT from DT.DT_info (file reader): ", setdiff(DT.DT_info$DT, DTs))) DT_full_info <- merge(DT.DT_info, DT.lsos, all=TRUE) [.(DTs)] DT_full_info[DT == "DT.GL.summaries", definition := "{ < aggregating DT.GL by date-store-m_vs_v_by_dmv}"] DT_full_info[DT == "DT.mgmtr_summary_tall", definition := "< based off of DT.GL.summaries >"] DT_full_info[DT == "DT.supplychain_percentages", definition := ifelse(duplicated(DT), "< rbinding with self, adding 'statement' >", definition)] setkey(DT_full_info, file, ordering) DT_full_info[, is_dup := ifelse(duplicated(DT), "(X)", "")] setcolorderpt(DT_full_info, c("is_dup", "DT", "file", "definition", "rows", "cols")) DT_full_info[, file := sprintf(" %-41s ", substr(gsub("\\.r", "", file), 1, 38))] DT_full_info[, definition := sprintf(paste0(" %-", mnchar(definition), "s"), definition)] setWidth(333, confirm=FALSE) print(DT_full_info[order(file, ordering)]) } ### ~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~ ### 1: DT.stores GL_pt0_b Keys Folders and Variables get_dim_store(refresh=TRUE) 508 7 1 storeid, store_name 2: DT.anal GL_pt1_a Import OA from DB Querying Ag copy(Qres.list[["aggregated_analytics"]]) 11,459 33 1 date, storeid, continent_ordered, release_is_compilat.. 3: DT.acc GL_pt1_a Import OA from DB Querying Ag copy(Qres.list[["aggregated_accounting"]]) 61,850 30 2 date, storeid, continent_ordered, release_is_compilat.. 4: DT.GL GL_pt1_b Ingest GL from Revenue Worksh rbindlist(cleanedSheets) 2,840 20 6 storepart 5: (X) DT.GL GL_pt1_b Ingest GL from Revenue Worksh DT.GL[!is.na(DMV)] 2,840 20 7 storepart 6: (X) DT.GL GL_pt1_b Ingest GL from Revenue Worksh DT.GL[accounting_month >= minDateToKeep] 2,840 20 8 storepart 7: (X) DT.GL GL_pt1_b Ingest GL from Revenue Worksh DT.GL[! (storepart=="Send Me Mobile" & Music_vs_Video_by_DMV == "Video") ] 2,840 20 9 storepart 8: (X) DT.GL GL_pt1_b Ingest GL from Revenue Worksh DT.GL[storepart %ni% stores.dropping.nogross] 2,840 20 10 storepart 9: (X) DT.stores GL_pt1_b Ingest GL from Revenue Worksh get_dim_store(refresh=FALSE) 508 7 11 storeid, store_name 10: DT.greatplains_oa_map GL_pt1_b Ingest GL from Revenue Worksh fread(f.in.greatplains) 205 4 13 storepart, greatplains_storeid 11: DT.storeparts GL_pt1_b Ingest GL from Revenue Worksh copy(DT.GL[, list(DMV=unique(DMV)), keyby=list(storepart, greatplains_storeid=ID_greatplains)]) 206 7 14 storepart, greatplains_storeid 12: DT.GL.summaries GL_pt1_b Ingest GL from Revenue Worksh { < aggregating DT.GL by date-store-m_vs_v_by_dmv} 2,019 15 16 accounting_month, storeid, store_name, Music_vs_Video.. 13: DT.rev_summary GL_pt1_c Ingest Revenue Summaries rbindlist(rawsheets_smry) 32 14 1 year, revenue 14: (X) DT.rev_summary GL_pt1_c Ingest Revenue Summaries DT.rev_summary[, lapply(.SD, function(x) sum(currToNumeric(x)) ), keyby=list(year, revenue)] 32 14 2 year, revenue 15: DT.rev_summary.molt GL_pt1_c Ingest Revenue Summaries melt(DT.rev_summary, id.vars=c("year", "revenue"), variable.name="month", value.name="reported_gross") 204 11 3 date, storeid, store_name, music_vs_video, label_sc_g.. 16: (X) DT.rev_summary.molt GL_pt1_c Ingest Revenue Summaries DT.rev_summary.molt[!is.na(reported_gross)] 204 11 4 date, storeid, store_name, music_vs_video, label_sc_g.. 17: DT.Total_Reported_Revenue_By_Month GL_pt1_c Ingest Revenue Summaries DT.rev_summary.molt[revenue == "TOTAL"] 17 3 5 date, revenue 18: (X) DT.rev_summary.molt GL_pt1_c Ingest Revenue Summaries DT.rev_summary.molt[revenue != "TOTAL"] 204 11 6 date, storeid, store_name, music_vs_video, label_sc_g.. 19: (X) DT.rev_summary.molt GL_pt1_c Ingest Revenue Summaries DT.rev_summary.molt[, list(reported_gross=sumn(reported_gross)), keyby=kCols.smry] 204 11 7 date, storeid, store_name, music_vs_video, label_sc_g.. 20: DT.ancillary_from_rev_summary GL_pt1_c Ingest Revenue Summaries DT.rev_summary.molt[ grepl("^zz_", store_name)] 102 11 8 date, storeid, store_name, music_vs_video, label_sc_g.. 21: DT.mainline_group_subtotals_from_rev_summary GL_pt1_c Ingest Revenue Summaries DT.rev_summary.molt[!grepl("^zz_", store_name)] 255 11 9 date, storeid, store_name, music_vs_video, label_sc_g.. 22: (X) DT.mainline_group_subtotals_from_rev_summary GL_pt1_c Ingest Revenue Summaries temp_DT.totals 255 11 14 date, storeid, store_name, music_vs_video, label_sc_g.. 23: (X) DT.stores GL_pt1_d Ingest Manual Tempalte worksh get_dim_store(refresh=FALSE) 508 7 1 storeid, store_name 24: DT.manual GL_pt1_d Ingest Manual Tempalte worksh rbindlist(DT.manual.ll) [, "DONT EDIT PAST THIS COLUMN" := NULL] 259 20 3 date, storeid, store_name, music_vs_video, label_sc_g.. 25: DT.supplychain_percentages.wide GL_pt1_e Ingest RED and OSC percentage fread(file.ASR_percof_DS_forDSO) 18 6 1 26: (X) DT.supplychain_percentages.wide GL_pt1_e Ingest RED and OSC percentage rbind(DT.supplychain_percentages.wide[.(head(seq(minDateToKeep, min(date), by="month"), -1))], DT.supplychain_percentages.wide) 18 6 2 27: DT.supplychain_percentages GL_pt1_e Ingest RED and OSC percentage melt(DT.supplychain_percentages.wide, id.var=c("date", "type"), variable.name="label_sc_group", value.name="ASR_percof_DS_forDSO") 144 5 3 label_sc_group, date 28: (X) DT.supplychain_percentages GL_pt1_e Ingest RED and OSC percentage < rbinding with self, adding 'statement' > 144 5 4 label_sc_group, date 29: DT.mgmtr_summary_tall GL_pt2_b Create mgmtr_summary_tall fro < based off of DT.GL.summaries > 2,019 16 1 date, storeid, store_name, music_vs_video, label_sc_g.. 30: DT.split_percs GL_pt2_c Split revenue by Subgroups rbind(tmp_DT.split_percs.acc, tmp_DT.split_percs.anal) 4,532 13 2 date, storeid, store_name, music_vs_video, label_sc_g.. 31: DT.merged GL_pt2_c Split revenue by Subgroups merge(DT.mgmtr_summary_tall, DT.split_percs, all=TRUE) [date >= minDateToKeep] 5,906 25 3 music_vs_video, label_sc_group, release_product_type,.. 32: (X) DT.merged GL_pt2_c Split revenue by Subgroups rbind( DT.merged[!tmp_DT.stores_needing_dates], DT.merged[ tmp_DT.stores_needing_dates] ) 5,906 25 4 music_vs_video, label_sc_group, release_product_type,.. 33: DT.mexpanded GL_pt2_c Split revenue by Subgroups DT.merged[, .SD[temp_DT.splitCols_expanded], by=kCols.main] 53,214 28 5 date, storeid, store_name, music_vs_video, label_sc_g.. 34: (X) DT.mexpanded GL_pt2_c Split revenue by Subgroups DT.merged[, setkeyIfNot(.SD, names(temp_DT.splitCols_expanded), verbose=FALSE)[temp_DT.splitCols_expanded], by=kCols.main] 53,214 28 6 date, storeid, store_name, music_vs_video, label_sc_g.. 35: DT.cleaned GL_pt2_c Split revenue by Subgroups DT.mexpanded[!(delete.this.row)] 50,565 24 8 storeid, store_name 36: DT.manl_crop GL_pt3_a Create DT.manl_crop copy(DT.manual) 259 14 1 date, storeid, store_name, music_vs_video, label_sc_g.. 37: DT.mgmtr_summary GL_pt3_b Create Wide mgmtr_summary fro copy(DT.cleaned) NA NA 5 NA 38: (X) DT.mgmtr_summary GL_pt3_b Create Wide mgmtr_summary fro copy(DT.mgmtr_summary.bak) NA NA 7 NA 39: (X) DT.mgmtr_summary GL_pt3_b Create Wide mgmtr_summary fro DT.mgmtr_summary[, lapply(.SD, sumn), keyby=c(kCols.smry, "zz_hasData", "margin_for_perc_split", "perc_split_as_reported", "GL_gross_forDSagg")] NA NA 8 NA 40: (X) DT.mgmtr_summary GL_pt3_b Create Wide mgmtr_summary fro DT.mgmtr_summary[!(gross == 0 & DT.mgmtr_summary[, rowSums(is.na(.SD))==length(cols.to.spliton), .SDcols=cols.to.spliton])] NA NA 9 NA 41: DT.mgmtr_summary_manual GL_pt3_b Create Wide mgmtr_summary fro merge(DT.mgmtr_summary, DT.manl_crop, all=TRUE, allow=TRUE) NA NA 10 NA 42: (X) DT.mgmtr_summary_manual GL_pt3_b Create Wide mgmtr_summary fro copy(DT.mgmtr_summary_manual.bak) NA NA 12 NA 43: (X) DT.mgmtr_summary_manual GL_pt3_b Create Wide mgmtr_summary fro rbind(DT.mgmtr_summary_manual, DT.rev_in_summary_not_in_GL, fill=TRUE) [is.na(gross), gross := rev_in_summary_not_in_GL] NA NA 14 NA 44: (X) DT.mgmtr_summary_manual GL_pt5 - incomplete months DT.mgmtr_summary_manual[!(store_name == "zzz_Mainline_Revenue" & store_musicbucket == "Other Services")] NA NA 1 NA DT.stores :: OA dim_store with additional meta DT.anal :: aggregation of flat tables, by date-store DT.acc :: aggregation of flat tables, by date-store DT.GL :: ingestion of GL xlsx file. created from rbindlist(cleanedSheets), remoivng NAs and keeping only accounting_month >= minDateToKeep and dropping stores with no gross DT.greatplains_oa_map :: ingestion of CSV, raw mapping of OA ids to gp ids DT.storeparts :: taken from GL, unique store names and gp_storeid DT.GL.summaries :: aggregation of store data and renaming of some columns. aggregate by date-store (and M_vs_V_DMV) DT.rev_summary :: ingestion of REVENUE SUMMARY xlsx file. Two sheets, rbound into one. DT.rev_summary.molt :: Melted version of Revenue Summary, where each date went from being its own column to being reshaped into one column. The 'total' row is removed and put into a separate DT DT.Total_Reported_Revenue_By_Month :: subset of molten rev summary; this is the TOTAL rows from the molten revenue summary DT.ancillary_from_rev_summary :: subset of molten rev summary; these are the ANCILLARY rows for each month DT.mainline_group_subtotals_from_rev_summary :: subset of molten rev summary; these are the AS REPORTED SUBTOTALS -- by m_v, osc, bucket, etc. Calculated values should ADD TO THIS DT.manual :: ingestion of TEMPLATE xlsx files, rbound into one. DT.supplychain_percentages.wide :: ingestiong of AS-REPORTED OSC split percentages, before reshaping and NA-filled for dates to keep DT.supplychain_percentages :: Melted version of DT.supplychain_percentages.wide, rbound with a duplicate version of itself, but with type 'statement' DT.mgmtr_summary_tall :: used to create DT.merged, this is simply a modified version of DT.GL.summaries with some columns ('GL_gross_forDS') added and IDs changed to numeric DT.split_percs :: used to create DT.merged, this is the total gross (or gest) Calculated from OA flat tables. gross is summed per kCols.splitgroup and perc_split_ofOA_byDSO is calculated for each DS DT.merged :: Merge of OA (DT.split_percs) & GL (DT.mgmtr_summary_tall), adding in the most recent OA dates for all of the GL stores (not just the OA analytics store) DT.mexpanded :: expands DT.merged by adding a row for each combination of the values of cols.to.spliton (rather, just the combinations that exist) DT.cleaned :: copy of DT.mexpanded, dropping columns not needed AND dropping rows marked for deletion (the superfluous rows from the GL/OA merger). Also here is where gross := GL_gross_forDS * perc_split DT.manl_crop :: copy of DT.manual, dropping columns. Also adding in reported_gross from DT.ancillary_from_rev_summary DT.mgmtr_summary :: copy of DT.cleaned (ie, cleaned merger of GL/OA) and aggregating by store group and cols.to.spliton DT.mgmtr_summary_manual :: Ultimately, what gets loaded into the DB. This is a merge of DT.mgmtr_summary with DT.manl_crop DT.supplychain_percentages <- rbind(DT.supplychain_percentages, DT.supplychain_percentages[, list(type="statement", ASR_percof_DS_forDSO=NA_real_), keyby=list(date, label_sc_group)]) DT.mgmtr_summary_tall <- { DT.GL.summaries[, list( date = accounting_month , storeid = suppressWarnings(as.integer(storeid)) , storeid_char = storeid , store_name = store_name , music_vs_video_by_dmv = Music_vs_Video_by_DMV , ancillaryline = NA_character_ , GL_firstDate_forS = GL_firstDate_forS , gross = Total_ThisMonth , budget = NA_real_ , gross_last_modified = last_modified , budget_last_modified = as.POSIXct(NA_real_, origin=.origin.utc) ) ] }