load.R

# Load libraries and helper functions
source("lib/helpers.R")
source("lib/amendments.R")
source("lib/vendors.R")
source("lib/categories.R")
source("lib/inflation_adjustments.R")
source("lib/exports.R")
source("lib/research_findings.R")

# Start time
run_start_time <- now()
paste("Start time:", run_start_time)
add_log_entry("start_time", run_start_time)

# Options =======================================

# Summary parameters (used below)
summary_start_fiscal_year_short <- 2017
summary_end_fiscal_year_short <- 2021
summary_vendor_annual_total_threshold <- 1000000
summary_vendor_recent_threshold_years <- 2

summary_maximum_duration_cutoff_years <- 52
summary_original_value_minimum_cutoff <- 5000

# Note: to be deprecated
summary_total_vendor_rows <- 400
summary_per_owner_org_vendor_rows <- 100

# Load options from the .env file (loaded in _libraries.R)
option_download_remotely <- Sys.getenv("option_download_remotely", TRUE)

# Local contracts file (for testing or previous downloads)
option_local_contracts_data_source <- Sys.getenv("option_local_contracts_data_source", "data/testing/contracts.csv")
option_local_remove_derived_columns <- Sys.getenv("option_local_remove_derived_columns", TRUE)

# Summary export options
option_update_summary_csv_files <- Sys.getenv("option_update_summary_csv_files", TRUE)
option_remove_existing_summary_folders <- Sys.getenv("option_remove_existing_summary_folders", TRUE)

# Optional test filtering to a specific department or vendor
option_filter_to_department <- Sys.getenv("option_filter_to_department")
option_filter_to_vendor <- Sys.getenv("option_filter_to_vendor")

if(option_filter_to_department != "" | option_filter_to_vendor != "") {
  option_filter_enabled <- TRUE
} else {
  option_filter_enabled <- FALSE
}


# Data import ===================================

# Download the contracts csv file, list it for today (if not already downloaded) and then parse it:
if(option_download_remotely == TRUE) {
  print("Downloading remotely (if not already downloaded today)")
  contracts <- get_contracts_csv_locally_or_from_url(contract_col_types)
} else {
  print("Using the local copy stored at:")
  add_log_entry("csv_source", "local/testing")
  print(option_local_contracts_data_source)
  # Previous version (for local operations)
  # Import the CSV file
  contracts <- read_csv(
    option_local_contracts_data_source,
    col_types = contract_col_types
  ) %>%
    clean_names()
  
  if(option_local_remove_derived_columns == TRUE) {
    # If you're loading previously exported contracts testing data
    # remove the derived columns and the (joined) category column.
    # This avoids column name overlap conflicts later.
    # Note: This only applies to locally-loaded files (since the source data CSV from open.canada.ca doesn't include the derived columns.)
    contracts <- contracts %>%
      select(!starts_with("d_")) %>%
      select(!starts_with("category"))
  }
}


# Optionally filter by department ===============

if(! (is.na(option_filter_to_department) | option_filter_to_department == "")) {
  cat("Filtering by department: ", option_filter_to_department)
  add_log_entry("option_filter_to_department", option_filter_to_department)
  
  contracts <- contracts %>%
    filter(owner_org == option_filter_to_department)
  
} else {
  cat("Including all departments.")
}


# Vendor name normalization =====================

# Add vendor name normalization here, before amendments are found and combined below.
# For simplicity, the normalized names are stored in d_vendor_name
contracts <- contracts %>%
  mutate(
    d_clean_vendor_name = clean_vendor_names(vendor_name)
  ) %>%
  left_join(vendor_matching, by = c("d_clean_vendor_name" = "company_name")) %>%
  rename(d_normalized_vendor_name = "parent_company")

# For companies that aren't in the normalization table
# include their regular names anyway:
contracts <- contracts %>%
  mutate(
    d_vendor_name = case_when(
      !is.na(d_normalized_vendor_name) ~ d_normalized_vendor_name,
      TRUE ~ d_clean_vendor_name,
    )
  )


# Optionally filter by (normalized) vendor name =====

if(! (is.na(option_filter_to_vendor) | option_filter_to_vendor == "")) {
  cat("Filtering by vendor: ", option_filter_to_vendor)
  add_log_entry("option_filter_to_vendor", option_filter_to_vendor)
  
  contracts <- contracts %>%
    filter(d_vendor_name == option_filter_to_vendor)
  
} else {
  cat("Including all vendors.")
}


# Total entries (after filtering)
add_log_entry("total_csv_entries", count(contracts))

# Start and end year for calculations
add_log_entry("summary_start_fiscal_year", convert_start_year_to_fiscal_year(summary_start_fiscal_year_short))
add_log_entry("summary_end_fiscal_year", convert_start_year_to_fiscal_year(summary_end_fiscal_year_short))



# Initial data mutations ========================

# Convert Y/N values to logical TRUE/FALSE values, for specific columns
contracts <- contracts %>%
  mutate(
    across(c(indigenous_business_excluding_psib, potential_commercial_exploitation, former_public_servant, ministers_office),
           ~ recode(., 
                    "N" = FALSE, 
                    "Y" = TRUE,
                    .default = as.logical(NA))
    )
  )

# Correct any accidentally-switched reference numbers and procurement IDs
contracts <- contracts %>%
  mutate(
    d_procurement_id = case_when(
      str_detect(procurement_id, "\\d{4}-\\d{4}-Q\\d-") ~ reference_number,
      TRUE ~ procurement_id
    ),
    d_reference_number = case_when(
      str_detect(procurement_id, "\\d{4}-\\d{4}-Q\\d-") ~ procurement_id,
      TRUE ~ reference_number
    )
  )

# Remove any suffixes (of 3 characters/digits or less) from procurement IDs, to improve the accuracy of procurement ID-based amendment grouping.
# Note: This could be refactored using regexes instead of a multi-stage mutate with string locations. The code below reverses the order of the procurement ID, finds the first "/" (used to denote suffixes in procurement IDs), and if it exists and is less than or equal to 4 characters (including the "/") then it uses the procurement ID with that suffix portion removed.
contracts <- contracts %>%
  mutate(
    d_procurement_id_suffix_reversed_id = stri_reverse(d_procurement_id),
    d_procurement_id_suffix_pos = str_locate(d_procurement_id_suffix_reversed_id, "/")[,"start"],
    d_procurement_id_suffix_remainder = str_sub(d_procurement_id_suffix_reversed_id, start = d_procurement_id_suffix_pos + 1L),
    d_procurement_id = case_when(
      d_procurement_id_suffix_pos <= 4 ~ stri_reverse(d_procurement_id_suffix_remainder),
      TRUE ~ d_procurement_id
    )
  ) %>%
  select(! starts_with("d_procurement_id_suffix"))

# Use the reporting period if it exists, otherwise get it from the reference number
# Store it in d_reporting_period (derived reporting period)
contracts <- contracts %>%
  mutate(
    d_reporting_period = case_when(
      is_valid_reporting_period(reporting_period) ~ reporting_period,
      !is.na(get_reporting_period_from_reference_number(d_reference_number)) ~ get_reporting_period_from_reference_number(d_reference_number),
      TRUE ~ get_reporting_period_from_date(contract_date)
    )
  )


# Sort the contracts dataset by ascending reporting period, then owner_org
contracts <- contracts %>%
  arrange(d_reporting_period, owner_org, d_reference_number)
  

# For grouping/comparison purposes, get the fiscal year from the reporting period
contracts <- contracts %>%
  mutate(
    d_reporting_year = get_fiscal_year_from_reporting_period(d_reporting_period)
  )


# Create a (possibly) unique ID for each row
# Depends on each department maintaining unique reference_number entries
# which appears to be the case.
# Can be checked using janitor with 
# contracts %>% get_dupes(d_reference_number)
contracts <- contracts %>%
  mutate(
    d_reference_number = str_c(owner_org, "-", d_reference_number)
  )
 

# Create a start date using (in order of priority)
# contract_period_start, contract_date
# (original logic in assureRequiredContractValues in the PHP code)
contracts <- contracts %>%
  mutate(
    d_start_date = case_when(
      !is.na(contract_period_start) ~ contract_period_start,
      !is.na(contract_date) ~ contract_date,
      TRUE ~ NA_Date_
    )
  )


# Create an end date using the row-wise largest of:
# delivery_date, contract_period_start, contract_date
contracts <- contracts %>%
  rowwise() %>% 
  mutate(
    d_end_date = max(delivery_date, contract_period_start, contract_date, na.rm = TRUE)
  ) %>%
  ungroup()


# Ensure that there's an "original value" to compare to, even in the first contract's case where it might only be listed in contract_value
# A lot of contracts have (erroneously) low original values, e.g. $1.13 on a $600M contract
# https://search.open.canada.ca/en/ct/id/dfo-mpo,C-2020-2021-Q1-01265
# So we add a summary_original_value_minimum_cutoff value (e.g. $5000) to ignore these.
contracts <- contracts %>%
  mutate(
    d_original_original_value = case_when(
      original_value > summary_original_value_minimum_cutoff ~ original_value, # Also takes care of NA entries
      # In some cases, due to date entry errors, the contract_value might also be tiny, e.g. 0.01
      # This filters out those situations, and leaves those with an NA for d_original_original_value
      contract_value > summary_original_value_minimum_cutoff ~ contract_value,
      TRUE ~ NA_real_
    ),
    # To avoid data entry errors being used in future analysis calculations
    # we'll use the same cutoff for contract values.
    # Note: this involves an extensive change to where contract_value is used, be careful!
    d_contract_value = case_when(
      contract_value > summary_original_value_minimum_cutoff ~ contract_value,
      TRUE ~ NA_real_
    )
  )


# Categorizing into an industry category ========

# Get slightly cleaner versions of descriptions and object codes
# 1. Detect any object codes *in* the description text, based on 3- or 4-digit numbers.
# 2. Add missing object codes.
# 3. Remove object codes from description text.
# 3. Clean up description text with str_squish and str_to_sentence.
contracts <- contracts %>%
  mutate(
    d_description_en_economic_object_code = str_extract(description_en, "^\\d{3,4}"),
    #d_description_en = str_extract(description_en, "\\D+"),
    d_description_en = description_en,
  ) %>%
  mutate(
    d_economic_object_code = case_when(
      !is.na(economic_object_code) ~ economic_object_code,
      !is.na(d_description_en_economic_object_code) ~ d_description_en_economic_object_code,
      TRUE ~ NA_character_
    )
  ) %>%
  mutate(
    d_economic_object_code = str_pad(d_economic_object_code, 4, side = "left", pad = "0"),
    d_description_en = str_to_sentence(str_squish(d_description_en))
  )

contracts <- contracts %>%
  unite(
    col = "d_description_comments_extended_lower",
    c("description_en", "comments_en", "additional_comments_en"),
    sep = " ",
    remove = FALSE,
    na.rm = TRUE
  ) %>%
  mutate(
    d_description_comments_extended_lower = str_to_lower(str_squish(d_description_comments_extended_lower))
  )

# IT-specific category matching
contracts <- contracts %>%
  identify_it_subcategories()

# Add in industry categories (where these exist) using the category matching table.
contracts <- contracts %>%
  left_join(category_matching, by = "d_economic_object_code")


# Note: to add here, additional category matching based on contract descriptions, typical company activities based on other contract entries, etc.

# Add in industry categories (where these exist) using the description matching table.
contracts <- contracts %>%
  left_join(description_matching, by = "d_description_en")

# Add in vendor-specific (edge case) categories based on vendor names and economic object codes.
# Uses a combination join, described in the documentation here:
# https://dplyr.tidyverse.org/reference/mutate-joins.html#arguments
contracts <- contracts %>%
  left_join(vendor_specific_category_matching, by = c("d_economic_object_code", "d_vendor_name"))

# Note: these left joins create several versions of the same row, in cases where multiple category options match. Be sure to use distinct() as needed in subsequent cases before tallying up totals.

# Update 2: use the category from the (edge cases) vendor + economic object code override.
# Then, use economic object codes.
# Then, use the description field.
contracts <- contracts %>%
  mutate(
    category = NA_character_,
    category = case_when(
      !is.na(category_by_vendor_and_economic_object_code) ~ category_by_vendor_and_economic_object_code,
      !is.na(category_by_it_subcategory) ~ category_by_it_subcategory,
      !is.na(category_by_economic_object_code) ~ category_by_economic_object_code,
      !is.na(category_by_description) ~ category_by_description,
      TRUE ~ NA_character_
    )
  )

# Add "it_other" to any IT contracts that don't already have a subcategory:
contracts <- contracts %>%
  mutate(
    d_it_subcategory = case_when(
      is.na(d_it_subcategory) & category == "3_information_technology" ~ "it_other",
      TRUE ~ d_it_subcategory
    )
  )

# Review descriptions of "it_other" contracts for specific keywords
contracts <- contracts %>%
  update_it_other_subcategories()

# Specific handling for Department of National Defence
# Because Transportation & Logistics and Information Technology have a
# significant amount of overlap in economic object codes used between
# civilian and military applications, we'll bulk update each of these
# categories to "11_defence" for DND specifically.

# Note: this isn't indicated in any other columns; it may be worth
# adding a "category_by_departmental_override" column as a reminder
# why these categories are what they are.
contracts <- contracts %>%
  mutate(
    category = case_when(
      owner_org == "dnd-mdn" & category == "3_information_technology" ~ "11_defence",
      owner_org == "dnd-mdn" & category == "5_transportation_and_logistics" ~ "11_defence",
      TRUE ~ category
    )
  )

# Revert d_it_subcategory for defence contracts to make sure totals aren't thrown off:
contracts <- contracts %>%
  mutate(
    d_it_subcategory = case_when(
      category != "3_information_technology" ~ NA_character_,
      TRUE ~ d_it_subcategory
    )
  )



# Set "NA" categories to "0_other" to improve handling on subsequent exports and the website display.
contracts <- contracts %>%
  mutate(
    category = case_when(
      is.na(category) ~ "0_other",
      TRUE ~ category
    )
  )


# Temp: get descriptions and object codes
# Note: this only includes descriptions with at least 10 entries.
# (Shortens the total list from 12k to 3k rows.)
# Note: review if "category" should be included here first.
# For consistency, just the economic object codes are selected and counted at first.
# Then, the left join and distinct() calls below add in example descriptions for ease of reference, from the chronologically most recent time in the data set that the economic object code is used.
contract_descriptions_object_codes <- contracts %>%
  select(d_economic_object_code) %>%
  group_by(d_economic_object_code) %>%
  add_count(d_economic_object_code, name = "count") %>%
  distinct() %>%
  #filter(n >= 10) %>%
  arrange(desc(count))
  
# Use a version of contracts in reverse chronological order
# to get higher-quality descriptions (more recent seem to be higher-quality)
# TODO: Confirm if this is a computationally-expensive procedure!
# Note: only three fields are currently kept; revisit this if this is useful for other operations.
contracts_reversed <- contracts %>%
  arrange(desc(d_reporting_period)) %>%
  select(d_reference_number, d_reporting_period, d_economic_object_code, d_description_en, category)

# Thanks to
# https://www.marsja.se/how-to-remove-duplicates-in-r-rows-columns-dplyr/
contract_descriptions_object_codes <- contract_descriptions_object_codes %>%
  left_join(contracts_reversed, by = "d_economic_object_code") %>%
  select(d_economic_object_code, count, d_description_en, category) %>%
  distinct(d_economic_object_code, .keep_all = TRUE)


# Amendment group identification ================

add_log_entry("start_amendment_grouping")

# Find amendment groups based on procurement_id, or on start date + contract value 
contracts <- find_amendment_groups_v2(contracts)

add_log_entry("finish_amendment_grouping")

# Group contracts and amendments together =========

# Contract spending grouped by amendment groups
# For each amendment group, find the original row's start date, and the last row's end date
contract_spending_overall <- contracts %>%
  arrange(d_reporting_period, owner_org, d_reference_number) %>% # This is done above, but for safety, doing it again here to ensure that the first() and last() calls below work properly.
  # TODO: the first() and last() calls below could have the d_reporting_period sort order set; confirm if that affects totals later.
  select(d_reference_number, d_vendor_name, d_reporting_period, d_start_date, d_end_date, d_contract_value, d_original_original_value, d_amendment_group_id, owner_org, d_number_of_amendments, d_economic_object_code, d_description_en, category, d_it_subcategory, d_description_comments_extended_lower, intellectual_property, commodity_type) %>%
  group_by(d_amendment_group_id) %>%
  mutate(
    d_most_recent_category = last(category),
    d_most_recent_it_subcategory = last(d_it_subcategory),
    d_most_recent_description_en = last(d_description_en),
    d_overall_start_date = first(d_start_date),
    d_overall_end_date = last(d_end_date),
    # In some amendment cases, later amendment entries mean that the last(d_end_date) is earlier in time than the first(d_start_date).
    # To handle those cases, we'll set the d_overall_start_date to be the same as the d_overall_end_date - before doing the d_daily_contract_value calculation below.
    d_overall_start_date = case_when(
      d_overall_start_date > d_overall_end_date ~ d_overall_end_date,
      TRUE ~ d_overall_start_date,
    ),
    # Note: this uses either the contract value or an original value, if supplied
    # Note: this previously ordered by d_original_original_value, but in some cases that
    # led to amendment percentage increase errors (due to inconsistent data entry by departments).
    # TODO: review how this handles NA entries, hopefully na.omit() works well here.
    d_original_contract_value = first(na.omit(d_original_original_value), order_by = d_reporting_period),
    d_overall_contract_value = last(na.omit(d_contract_value)),
    d_daily_contract_value = d_overall_contract_value / as.integer(d_overall_end_date - d_overall_start_date + 1), # The +1 is added so it's inclusive of the start and end dates themselves.
    # Avoid multiple entries appearing due to previous amendment totals
    d_overall_number_of_amendments = last(d_number_of_amendments, order_by = d_number_of_amendments),
    d_overall_description_comments_extended = last(na.omit(d_description_comments_extended_lower)),
    d_intellectual_property = last(na.omit(intellectual_property)),
    d_commodity_type = last(na.omit(commodity_type))
  ) %>%
  ungroup()

# Ensure that these entries are unique, for future analysis
# From now on, contract_spending_overall represents groups of 
# matched contracts with their amendments.
contract_spending_overall <- contract_spending_overall %>%
  select(owner_org, d_vendor_name, d_amendment_group_id, d_overall_number_of_amendments, d_most_recent_category, d_most_recent_it_subcategory, d_overall_start_date, d_overall_end_date, d_original_contract_value, d_overall_contract_value, d_daily_contract_value, d_overall_description_comments_extended, d_intellectual_property, d_commodity_type) %>%
  distinct()


# Remove spurious contracts with date entry errors that lead to 100+ year long contracts, etc.
# Note: this removes some contracts from all subsequent calculations
# Future work could involve intelligently fixing dates, e.g. a "2122" end date typically should be "2022" if the start date was 2021, etc.
# This removes about 106 contracts (as of source data from 2022-09-30).
contract_spending_overall <- contract_spending_overall %>%
  calculate_overall_duration(TRUE)


# Maintain a set of contract vendor names for normalization troubleshooting later
vendor_names <- contracts %>%
  select(vendor_name, d_clean_vendor_name, d_normalized_vendor_name, d_vendor_name) %>%
  distinct() %>%
  arrange(d_clean_vendor_name)

# Get a list of all owner organizations (departments/agencies)
owner_orgs <- contracts %>%
  select(owner_org) %>%
  distinct() %>%
  arrange(owner_org) %>%
  pull(owner_org)

owner_org_names <- contracts %>%
  select(owner_org, owner_org_title) %>%
  distinct() %>%
  arrange(owner_org) %>%
  separate(col = owner_org_title,
           into = c("owner_org_name_en", "owner_org_name_fr"),
           sep = " \\| ")


# Clean up original contracts table =============

# Note: for IT subcategory or category troubleshooting, it might be necessary to re-add the origin columns for these categories.
contracts_individual_entries <- contracts %>%
  select(
    d_reference_number,
    d_amendment_group_id,
    owner_org,
    vendor_name,
    d_vendor_name,
    d_procurement_id,
    d_economic_object_code,
    d_description_comments_extended_lower,
    d_reporting_period,
    d_reporting_year,
    d_start_date,
    d_end_date,
    d_contract_value,
    d_original_original_value,
    category,
    d_it_subcategory,
    d_is_amendment,
    d_amendment_via
  )

# TODO: Confirm if this is unhelpful later.
# Removes the original "contracts" object to save on system memory:
# print("Reminder: removing the 'contracts' data frame to save memory.")
# rm(contracts)



# Calculate contract spending over time (pivot and complete into per-day data) =======

# Simplified version (linearized across the total duration as per the last amendment; doesn't account for changes in spending per-amendment in stages)

# With thanks to
# https://github.com/lchski/parliamentarians-analysis/blob/master/analysis/members.R#L7-L13
# Note: if new columns are added to input dataframes, 
# they must be added to the complete() function call below.
contract_spending_by_date <- contract_spending_overall %>%
  select(owner_org, d_vendor_name, d_amendment_group_id, d_most_recent_category, d_most_recent_it_subcategory, d_overall_start_date, d_overall_end_date, d_daily_contract_value) %>%
  distinct() %>% # Since the same data is now in each row, don't multiple-count contracts with amendments
  pivot_longer(
    c(d_overall_start_date, d_overall_end_date),
    values_to = "date",
    names_to = NULL
    ) %>%
  group_by(d_amendment_group_id) %>%
  complete(date = full_seq(date, 1), nesting(owner_org, d_vendor_name, d_daily_contract_value, d_most_recent_category, d_most_recent_it_subcategory)) %>%
  ungroup()

# Add (short) fiscal year, for grouping calculations
contract_spending_by_date <- contract_spending_by_date %>%
  # TODO: update or refactor get_short_fiscal_year_from_date to match this, if helpful.
  mutate(
    d_fiscal_year_short = case_when(
      month(date) <= 3 ~ year(date) - 1,
      month(date) <= 12 ~ year(date),
      TRUE ~ NA_real_,
    )
  )

# Filter to just the requested fiscal year range
# (defined in the parameters at the top)
# Note: this applies to all of the calculations that follow.
contract_spending_by_date <- contract_spending_by_date %>%
  filter(
    d_fiscal_year_short >= summary_start_fiscal_year_short,
    d_fiscal_year_short <= summary_end_fiscal_year_short,
  ) 
  

# Inflation correction (constant 2019 dollars) in the per-day table =====

# Factor in inflation calculations using the code in inflation_adjustments.R
# This could optionally be done before the fiscal year range above,
# but it's probably a tiny bit faster to do it afterwards with fewer rows.
add_log_entry("start_inflation_calculations")
add_log_entry("inflation_price_index_vector", option_inflation_price_index_vector)

contract_spending_by_date <- contract_spending_by_date %>%
  mutate(
    # Keeps e.g. "2017-04" which matches the ref_date column in the multiplier table
    ref_date = substr(date, 1, 7)
  ) %>%
  left_join(constant_dollars_multiplier_table, by = "ref_date") %>%
  mutate(
    d_daily_contract_value_constant_2019_dollars = d_daily_contract_value * constant_dollars_multiplier_2019
  ) %>%
  select(! c(ref_date, constant_dollars_multiplier_2019))

add_log_entry("finish_inflation_calculations")


# Contract spending overall versions (for analysis) =======

# Contracts that were new since the start of the summary_start_fiscal_year_short fiscal year:
contract_spending_overall_initiated <- contract_spending_overall %>%
  filter(
    d_overall_start_date >= ymd(str_c(summary_start_fiscal_year_short,"04","01"))
  )

# Contracts that have been active since the start of the summary_start_fiscal_year_short fiscal year:
contract_spending_overall_ongoing <- contract_spending_overall %>%
  filter(
    d_overall_end_date >= ymd(str_c(summary_start_fiscal_year_short,"04","01"))
  )

# Contracts that have been active since the start of the summary_start_fiscal_year_short fiscal year, and that didn't start after the end of summary_end_fiscal_year_short
contract_spending_overall_active <- contract_spending_overall %>%
  filter(
    d_overall_end_date >= ymd(str_c(summary_start_fiscal_year_short,"04","01")),
    d_overall_start_date < ymd(str_c(summary_end_fiscal_year_short + 1,"04","01"))
  )


# Summaries =====================================

add_log_entry("start_summary_exports")

# Helper variables
# e.g. 4 (years inclusive from the start to end of the coverage range)
summary_total_years <- summary_end_fiscal_year_short - summary_start_fiscal_year_short + 1L
# e.g. "2017_to_2020"
summary_overall_years_file_suffix <- str_c(summary_start_fiscal_year_short, "_to_", summary_end_fiscal_year_short)


# If requested, first delete the vendors/ and departments/ folders recursively
# See exports.R for more details
# (If a filter option is applied, don't do this. Note: there are additional options that prevent this within the function itself.)
if(option_filter_enabled == FALSE) {
  remove_existing_summary_folders()
}

# Determine which vendors have enough spending (e.g. averaging $1M per year in the time range, as defined in summary_vendor_annual_total_threshold)
# This replaces top_n_vendors
if(option_filter_enabled == FALSE) {
  summary_included_vendors <- get_summary_included_vendors()
} else {
  # Workaround here to ensure that vendors aren't dropped from departmental totals when testing with a filter option
  summary_included_vendors <- read_csv(str_c(output_meta_path, "vendors.csv")) %>%
    pull(name)
}

# For overall (government-wide) summaries that will appear on the homepage, first create a list-column with the different summary types (core public service departments, DND, and all departments)
summary_overall = tibble(summary_type = c("core", "dnd", "all"))

# Note: to add, summaries across "all time" of the specified time range
# plus filename labels for these that indicate the time range
summary_overall <- summary_overall %>%
  mutate(
    summary_by_fiscal_year = map(summary_type, get_summary_overall_by_fiscal_year),
    summary_by_fiscal_year_by_vendor = map(summary_type, get_summary_overall_by_fiscal_year_by_vendor),
    summary_by_fiscal_year_by_category = map(summary_type, get_summary_overall_by_fiscal_year_by_category),
    summary_by_fiscal_year_by_it_subcategory = map(summary_type, get_summary_overall_by_fiscal_year_by_it_subcategory),
    summary_by_fiscal_year_by_department = map(summary_type, get_summary_overall_by_fiscal_year_by_owner_org),
    !!str_c("summary_by_vendor_overall", "_", summary_overall_years_file_suffix) := map(summary_type, get_summary_overall_by_vendor),
    !!str_c("summary_by_category_overall", "_", summary_overall_years_file_suffix) := map(summary_type, get_summary_overall_by_category),
    !!str_c("summary_by_it_subcategory_overall", "_", summary_overall_years_file_suffix) := map(summary_type, get_summary_overall_by_it_subcategory),
    !!str_c("summary_by_department_overall", "_", summary_overall_years_file_suffix) := map(summary_type, get_summary_overall_by_owner_org),
  )

# Make output directories, if needed
create_summary_folders(output_overall_path, summary_overall$summary_type)

# Export overall summaries
# (unless a vendor or department filter is applied)
if(option_filter_enabled == FALSE) {
  export_summary(summary_overall, output_overall_path)
  
  # Once we know that these folders exist (thanks to create_summary_folders above)
  # Let's also generate the summary_type specific research findings CSV files:
  add_log_entry("start_research_findings_export")
  # From research_findings.R
  save_all_research_findings()
  add_log_entry("finish_research_findings_export")
}


# Optional bulk exports
# TODO: add option flag

contract_spending_overall_ongoing %>% 
  # slice_head(n = 20) %>% 
  arrange(owner_org, d_overall_start_date, d_vendor_name, desc(d_overall_end_date)) %>% 
    write_csv("../contracts-data-bulk-output/2023-06-07/contract_spending_overall_ongoing.csv", na = "")

contract_spending_overall %>% 
  # slice_head(n = 20) %>% 
  arrange(owner_org, d_overall_start_date, d_vendor_name, desc(d_overall_end_date)) %>% 
  write_csv("../contracts-data-bulk-output/2023-06-07/contract_spending_overall.csv", na = "")

# zip::zip("../contracts-data-bulk-output/2023-06-07/contract_spending_overall_ongoing.csv.zip", "../contracts-data-bulk-output/2023-06-07/contract_spending_overall_ongoing.csv")

# Summary by owner_org, vendor, and category =======

# see exports.R for the functions that are used here.

# Create a list-column and provide calculations for each department
summary_departments <- tibble(owner_org = owner_orgs)

# With thanks to
# https://jennybc.github.io/purrr-tutorial/index.html
summary_departments <- summary_departments %>%
    mutate(
      !!str_c("summary_by_vendor_overall", "_", summary_overall_years_file_suffix) := map(owner_org, get_summary_overall_total_by_vendor_by_owner),
      summary_by_fiscal_year_by_vendor = map(owner_org, get_summary_total_by_vendor_and_fiscal_year_by_owner),
      summary_by_fiscal_year_by_category = map(owner_org, get_summary_total_by_category_and_fiscal_year_by_owner_org),
      summary_by_fiscal_year_by_it_subcategory = map(owner_org, get_summary_total_by_it_subcategory_and_fiscal_year_by_owner_org),
      summary_by_fiscal_year = map(owner_org, get_summary_total_by_fiscal_year_by_owner_org),
      !!str_c("summary_by_category_overall", "_", summary_overall_years_file_suffix) := map(owner_org, get_summary_total_by_category_by_owner_org),
      !!str_c("summary_by_it_subcategory_overall", "_", summary_overall_years_file_suffix) := map(owner_org, get_summary_total_by_it_subcategory_by_owner_org),
      )


# Summary by vendor
summary_vendors = tibble(vendor = summary_included_vendors)

# Performance improvement when using a filter option
if(option_filter_enabled == TRUE) {
  summary_vendors = tibble(vendor = get_summary_included_vendors())
}

# Thanks to
# https://stackoverflow.com/a/26003971/756641
# For the concatenation below to include the "overall years" file suffix
summary_vendors <- summary_vendors %>%
  mutate(
    summary_by_fiscal_year = map(vendor, get_summary_total_by_fiscal_year_by_vendor),
    summary_by_fiscal_year_by_department = map(vendor, get_summary_total_by_fiscal_year_and_owner_org_by_vendor),
    summary_by_fiscal_year_by_category = map(vendor, get_summary_total_by_fiscal_year_and_category_by_vendor),
    summary_by_fiscal_year_by_it_subcategory = map(vendor, get_summary_total_by_fiscal_year_and_it_subcategory_by_vendor),
    !!str_c("summary_by_category_overall", "_", summary_overall_years_file_suffix) := map(vendor, get_summary_total_by_category_by_vendor),
    !!str_c("summary_by_it_subcategory_overall", "_", summary_overall_years_file_suffix) := map(vendor, get_summary_total_by_it_subcategory_by_vendor),
    original_vendor_names = map(vendor, get_original_vendor_names)
  )


# Get a summary for each of the industry categories
industry_categories <- contracts_individual_entries %>%
  select(category) %>%
  distinct() %>%
  arrange(category) %>%
  pull(category)

# Note: review how NA categories are handled in these summary functions.
# Currently NA data doesn't appear to be included (may need to handle is.na situations specifically).
# Or, we could bulk-assign NA entries to an "other" category ahead of time.

summary_categories = tibble(category = industry_categories) %>%
  filter(!is.na(category))

summary_categories <- summary_categories %>%
  mutate(
    !!str_c("summary_by_vendor_overall", "_", summary_overall_years_file_suffix) := map(category, get_summary_overall_total_by_vendor_by_category),
    summary_by_fiscal_year = map(category, get_summary_total_by_fiscal_year_by_category),
    summary_by_fiscal_year_by_vendor = map(category, get_summary_total_by_vendor_and_fiscal_year_by_category),
    !!str_c("summary_by_department_overall", "_", summary_overall_years_file_suffix) := map(category, get_summary_overall_total_by_owner_org_by_category),
    summary_by_fiscal_year_by_department = map(category, get_summary_total_by_owner_org_and_fiscal_year_by_category),
  )


# Get a summary for each IT subcategory
it_subcategories <- contracts_individual_entries %>%
  filter(!is.na(d_it_subcategory)) %>%
  select(d_it_subcategory) %>%
  distinct() %>%
  arrange(d_it_subcategory) %>%
  pull(d_it_subcategory)

summary_it_subcategories = tibble(it_subcategory = it_subcategories)

summary_it_subcategories <- summary_it_subcategories %>%
  mutate(
    !!str_c("summary_by_vendor_overall", "_", summary_overall_years_file_suffix) := map(it_subcategory, get_summary_overall_total_by_vendor_by_it_subcategory),
    summary_by_fiscal_year = map(it_subcategory, get_summary_total_by_fiscal_year_by_it_subcategory),
    summary_by_fiscal_year_by_vendor = map(it_subcategory, get_summary_total_by_vendor_and_fiscal_year_by_it_subcategory),
    !!str_c("summary_by_department_overall", "_", summary_overall_years_file_suffix) := map(it_subcategory, get_summary_overall_total_by_owner_org_by_it_subcategory),
    summary_by_fiscal_year_by_department = map(it_subcategory, get_summary_total_by_owner_org_and_fiscal_year_by_it_subcategory),
  )


# Meta tables of vendors, categories, and depts ====

meta_vendors <- tibble(name = summary_included_vendors)

meta_vendors <- meta_vendors %>%
  mutate(
    filepath = get_vendor_filename_from_vendor_name(name)
  )

# Bring in vendor labels with friendly capitalization where these exist.
meta_vendors <- meta_vendors %>%
  add_friendly_vendor_name_capitalization()

meta_departments <- owner_org_names %>%
  rename(
    name = owner_org_name_en,
    filepath = owner_org
  ) %>%
  select(name, filepath)

# Note: add friendly names for category labels
meta_categories <- category_labels %>%
  rename(
    name = category_name,
    filepath = original_category
  ) %>%
  select(name, filepath)

meta_it_subcategories <- it_subcategory_labels %>%
  rename(
    name = it_subcategory_name,
    filepath = original_it_subcategory
  ) %>%
  select(name, filepath)

# TODO: add friendly names to IT subcategory labels

# Export CSV files of summary tables =============

# Export CSV files of the summary tables
if(option_update_summary_csv_files == TRUE) {
  
  # Make per-owner org output directories, if needed
  create_summary_folders(output_department_path, summary_departments$owner_org)
  
  # Export department summaries using the reusable function
  if(option_filter_to_vendor == "") {
    export_summary(summary_departments, output_department_path)
  }
  
  
  # Per-vendor summaries
  # Make directories if needed
  create_summary_folders(output_vendor_path, summary_vendors$vendor)
  
  # Export vendor summaries using the reusable function
  if(option_filter_to_department == "") {
    export_summary(summary_vendors, output_vendor_path)
  }
  
  # Per-category summaries
  # Make directories if needed
  create_summary_folders(output_category_path, summary_categories$category)
  
  # Export category summaries using the reusable function
  if(option_filter_enabled == FALSE) {
    export_summary(summary_categories, output_category_path)
  }
  
  # Per-IT subcategory summaries
  # Make directories if needed
  create_summary_folders(output_it_subcategory_path, summary_it_subcategories$it_subcategory)
  
  # Export IT subcategory summaries using the reusable function
  if(option_filter_enabled == FALSE) {
    export_summary(summary_it_subcategories, output_it_subcategory_path)
  }
  
  
  # If necessary, create the "meta" folder
  dir_create(output_meta_path)
  
  # Export meta files to that location
  if(option_filter_enabled == FALSE) {
    meta_vendors %>%
      write_csv(str_c(output_meta_path, "vendors.csv"))
    meta_departments %>%
      write_csv(str_c(output_meta_path, "departments.csv"))
    meta_categories %>%
      write_csv(str_c(output_meta_path, "categories.csv"))
    meta_it_subcategories %>%
      write_csv(str_c(output_meta_path, "it_subcategories.csv"))
  }
  
  
  
  # End summary exports
  add_log_entry("finish_summary_exports")
  
  # Note: temporary for manual vendor name normalization work.
  # TODO: remove this later.
  vendor_names %>%
    write_csv("data/testing/tmp_vendor_names.csv")
  
  # TODO: remove this later.
  contract_descriptions_object_codes %>%
    write_csv("data/testing/tmp_descriptions_object_codes.csv")
  
  # TODO: remove this later
  # contracts %>%
  #   relocate(
  #     d_reference_number,
  #     vendor_name,
  #     d_clean_vendor_name,
  #     d_normalized_vendor_name
  #   ) %>%
  #   write_csv("data/testing/tmp_contracts_clean_names.csv")
  
}

run_end_time <- now()
paste("Start time was:", run_start_time)
paste("End time was:", run_end_time)


add_log_entry("end_time", run_end_time)
add_log_entry("run_duration_hours", round(time_length(interval(run_start_time, run_end_time), "hours"), digits = 2))

# Thanks to
# https://stackoverflow.com/a/56930258/756641
add_log_entry("run_memory_mb", sum(.Internal(gc(FALSE, TRUE, TRUE))[13:14]))


export_log_entries()


# TESTING (2022-04-12)

# contracts %>% count(owner_org, sort=TRUE)
# 
# sum(is.na(contracts$reference_number))
# sum(is.na(contracts$reporting_period))
# 
# ggplot(contracts) + 
#   geom_bar(mapping = aes(x = reporting_period))


# TESTING (2022-04-13)

# sum(is.na(contracts$d_reporting_period))
# 
# ggplot(contracts) + 
#   geom_bar(mapping = aes(x = d_reporting_period))
# 
# ggplot(contracts) + 
#   geom_bar(mapping = aes(x = d_reporting_year))
# 
# contracts %>%
#   filter(is.na(d_reporting_year)) %>%
#   select(reference_number, vendor_name, owner_org, contract_date, d_reporting_period, d_reporting_year, everything()) #%>%
#   #count(owner_org, sort=TRUE)
# 
# sum(is.na(contracts$reference_number))
# 
# contracts %>% count(reference_number, sort=TRUE)
# contracts %>% count(d_reference_number, sort=TRUE)
# 
# contracts %>% 
#   get_dupes(d_reference_number)
# 
# 
# contracts %>%
#   select(vendor_name, owner_org, contract_date, contract_period_start, d_start_date, everything())
# 
# sum(is.na(contracts$contract_period_start))
# sum(is.na(contracts$contract_date))
# sum(is.na(contracts$d_start_date))
# 
# 
# contracts %>%
#   select(contract_date, contract_period_start, delivery_date, d_start_date, d_end_date, vendor_name, owner_org, everything())


# contracts %>% 
#   filter(contract_value > 1000000) %>%
#   filter(owner_org == "ssc-spc") %>%
#   relocate(d_reference_number, vendor_name, d_start_date, d_end_date, contract_value, original_value, amendment_value, procurement_id, description_en) %>%
#   sample_n(20) #%>%
#   #write_csv("data/testing/2022-04-13-sample-contracts.csv")


# Testing (2022-04-14)

# contracts %>% relocate(d_reference_number, d_amendment_group_id, d_is_amendment, d_number_of_amendments, d_amendment_via) %>% View()


# Testing (2022-04-18)

# contracts %>% select(vendor_name, d_clean_vendor_name, d_normalized_vendor_name, d_vendor_name) %>% View()


# Testing (2022-06-23)

# # Review for repeated amendment group IDs / incomplete grouping of related amendments
# # Note: come back to this and tweak the grouping parameters.
# contract_spending_overall %>%
#   select(owner_org, d_vendor_name, d_amendment_group_id, d_overall_contract_value, d_description_en, d_economic_object_code, d_most_recent_category, d_overall_start_date, d_overall_end_date, d_daily_contract_value) %>%
#   distinct() %>%
#   arrange(desc(d_overall_contract_value)) %>%
#   slice_head(n = 100) %>%
#   View()
#   #write_csv(str_c("data/testing/", today(), "-contracts-spending-overall-largest.csv"))
# # 
# # Specific large-scale vendors for testing purposes
# tmp_key_vendors = c(
#   "BGIS",
#   "SIGNATURE SUR LE SAINT LAURENT"
#   )
# 
# tmp_key_reference_numbers <- contract_spending_overall %>%
#   select(owner_org, d_vendor_name, d_amendment_group_id, d_overall_contract_value, d_description_en, d_economic_object_code, category, d_overall_start_date, d_overall_end_date, d_daily_contract_value) %>%
#   distinct() %>%
#   filter(d_vendor_name %in% tmp_key_vendors) %>%
#   arrange(desc(d_overall_contract_value)) %>%
#   slice_head(n = 30) %>%
#   pull(d_amendment_group_id)
# 
# contracts %>%
#   filter(d_amendment_group_id %in% tmp_key_reference_numbers) %>%
#   arrange(contract_date) %>%
#   select(
#     d_reference_number,
#     d_vendor_name,
#     d_amendment_group_id,
#     d_number_of_amendments,
#     procurement_id,
#     original_value,
#     starts_with("d_"), everything()
#     ) %>%
#   #View()
#   write_csv(str_c("data/testing/", today(), "-contracts-key-amendment-testing.csv"))
# 

# 
# contract_spending_overall %>%
#   select(owner_org, d_amendment_group_id, d_overall_contract_value, d_vendor_name, d_description_en, d_economic_object_code, category, d_overall_start_date, d_overall_end_date, d_daily_contract_value) %>%
#   distinct() %>%
#   arrange(desc(d_overall_contract_value)) %>% 
#   filter(is.na(d_economic_object_code)) %>% 
#   #View()
#   write_csv(str_c("data/testing/", today(), "-contracts-without-economic-object-codes.csv"))
# 
# # Temporarily exclude DND
# contract_spending_overall %>%
#   select(owner_org, d_amendment_group_id, d_overall_contract_value, d_vendor_name, d_description_en, d_economic_object_code, category, d_overall_start_date, d_overall_end_date, d_daily_contract_value, comments_en, additional_comments_en) %>%
#   distinct() %>%
#   arrange(desc(d_overall_contract_value)) %>% 
#   filter(is.na(d_economic_object_code)) %>% 
#   filter(owner_org != "dnd-mdn") %>% 
#   #View()
#   write_csv(str_c("data/testing/", today(), "-contracts-without-economic-object-codes-exclude-dnd.csv"))
# 
# # Filter for just more recent contracts
# contract_spending_overall %>%
#   select(owner_org, d_amendment_group_id, d_overall_contract_value, d_vendor_name, d_description_en, d_economic_object_code, category, d_overall_start_date, d_overall_end_date, d_daily_contract_value) %>%
#   distinct() %>%
#   arrange(desc(d_overall_contract_value)) %>% 
#   filter(is.na(d_economic_object_code)) %>% 
#   filter(d_overall_end_date > "2017-03-31") %>% 
#   #View()
#   write_csv(str_c("data/testing/", today(), "-contracts-without-economic-object-codes-recent.csv"))
# 
# # Filter for just more recent contracts, and only include vendor + description
# contract_spending_overall %>%
#   select(owner_org, d_amendment_group_id, d_overall_contract_value, d_vendor_name, d_description_en, d_economic_object_code, category, d_overall_start_date, d_overall_end_date, d_daily_contract_value) %>%
#   distinct() %>%
#   arrange(desc(d_overall_contract_value)) %>%
#   filter(is.na(d_economic_object_code)) %>%
#   filter(d_overall_end_date > "2017-03-31") %>%
#   select(owner_org, d_description_en) %>%
#   distinct() %>%
#   #View()
#   write_csv(str_c("data/testing/", today(), "-contracts-without-economic-object-codes-recent-abridged.csv"))

# Testing (2022-06-29)

# contract_spending_overall %>%
#   filter(
#     d_overall_start_date >= ymd(str_c(summary_start_fiscal_year_short,"04","01")),
#     d_overall_start_date <= ymd(str_c(summary_end_fiscal_year_short,"03","31")),
#   )  %>%
#   select(d_description_en, category) %>%
#   distinct() %>%
#   arrange(category) %>%
#   #View()
#   write_csv(str_c("data/testing/tmp-", today(), "-description-category-matching.csv"))

# contract_spending_overall %>%
#   filter(
#     d_overall_start_date >= ymd(str_c(summary_start_fiscal_year_short,"04","01")),
#     d_overall_start_date <= ymd(str_c(summary_end_fiscal_year_short,"03","31")),
#   )  %>%
#   select(category, d_description_en) %>%
#   distinct() %>%
#   arrange(category) %>%
#   filter(!is.na(category)) %>%
#   #View()
#   bind_rows(description_matching) %>%
#   arrange(category) %>%
#   distinct() %>%
#   #View()
#   # Note: overwrites the existing description matching file (!)
#   write_csv(description_matching_file)

# Testing (2022-06-30)

# contract_spending_overall %>% 
#   filter(is.na(category)) %>% 
#   arrange(desc(contract_value)) %>% 
#   View()
# 
# 
# contract_spending_overall %>% 
#   filter(is.na(category)) %>% 
#   filter(
#     d_overall_end_date >= ymd(str_c(summary_start_fiscal_year_short,"04","01")),
#     d_overall_start_date <= ymd(str_c(summary_end_fiscal_year_short,"03","31"))
#   ) %>%
#   arrange(desc(d_overall_contract_value)) %>% 
#   select(d_vendor_name, d_overall_contract_value, d_most_recent_description_en, d_amendment_group_id, d_overall_start_date, d_overall_end_date) %>%
#   distinct() %>%
#   #View()
#   write_csv(str_c("data/testing/tmp-", today(), "-description-category-matching.csv"))
# 
# contracts %>% 
#   filter(is.na(category)) %>% 
#   arrange(desc(contract_value)) %>% 
#   View()


# Testing (2022-07-04)

# contracts %>%
#   filter(d_economic_object_code == "3252") %>%
#   arrange(desc(contract_value)) %>%
#   select(owner_org, d_vendor_name, contract_value, d_description_en, comments_en, additional_comments_en, d_start_date, d_end_date, d_amendment_group_id, procurement_id, original_value, d_economic_object_code, starts_with("category")) %>%
#   View()
# 
# Export of the current state for reviewing without parsing runs:
# 
# contracts %>%
#   write_csv(str_c("data/testing/tmp-", today(), "-contracts.csv"))
# 
# contract_spending_overall %>%
#   write_csv(str_c("data/testing/tmp-", today(), "-contract-spending-overall.csv"))

# Testing (2022-07-05)

# vendor_name_to_look_up <- "AMAZON"
# 
# contracts %>%
#   filter(str_detect(d_vendor_name, vendor_name_to_look_up)) %>%
#   select(d_vendor_name, d_description_en, owner_org, contract_value) %>%
#   arrange(d_vendor_name) %>%
#   View()
# 
# contracts %>%
#   filter(str_detect(d_vendor_name, vendor_name_to_look_up)) %>%
#   select(d_vendor_name) %>%
#   arrange(d_vendor_name) %>%
#   View()
# 
# # See vendor-normalization.R for more

# # Procurement ID mixups (in which departments mistakenly switched the procurement_id and reference_number values)
# contracts %>%
#   filter(str_detect(procurement_id, "-Q")) %>%
#   arrange(desc(contract_value)) %>%
#   select(owner_org, d_vendor_name, procurement_id, reference_number, contract_value, d_description_en, comments_en, additional_comments_en, d_start_date, d_end_date, d_amendment_group_id, original_value, d_economic_object_code) %>%
#   slice_sample(n = 40) %>%
#   write_csv(str_c("data/testing/", today(), "-sample-contracts-procurement-id-mixup.csv"))
# 
# # Procurement ID "appendages" (suffixes at the end with e.g. the initials of the contracting officer at the time)
# example_contracts_appendages <- contracts %>%
#   filter(str_detect(procurement_id, "/")) %>%
#   filter(contract_value > 100000) %>%
#   filter(d_start_date > "2017-01-01") %>%
#   arrange(desc(contract_value)) %>%
#   select(owner_org, d_vendor_name, procurement_id, reference_number, contract_value, d_description_en, comments_en, additional_comments_en, d_start_date, d_end_date, d_amendment_group_id, original_value, d_economic_object_code) %>%
#   slice_sample(n = 40)
# 
# contracts %>%
#   filter(d_vendor_name == "D H PARTNERSHIP") %>%
#   arrange(desc(contract_value)) %>%
#   select(owner_org, d_vendor_name, procurement_id, reference_number, contract_value, d_description_en, comments_en, additional_comments_en, d_start_date, d_end_date, d_amendment_group_id, original_value, d_economic_object_code) %>%
#   bind_rows(example_contracts_appendages) %>%
#   write_csv(str_c("data/testing/", today(), "-sample-contracts-procurement-id-suffixes.csv"))

# # Load these back in for testing
# example_contracts <- read_csv("data/testing/2022-07-05-sample-contracts-procurement-id-mixup.csv")
# 
# example_contracts %>%
#   mutate(
#     d_procurement_id = case_when(
#       str_detect(procurement_id, "\\d{4}-\\d{4}-Q\\d-") ~ reference_number,
#       TRUE ~ procurement_id
#     ),
#     d_reference_number = case_when(
#       str_detect(procurement_id, "\\d{4}-\\d{4}-Q\\d-") ~ procurement_id,
#       TRUE ~ reference_number
#     )
#   ) %>%
#   View()

# # Load these back in for testing
# example_contracts <- read_csv("data/testing/2022-07-05-sample-contracts-procurement-id-suffixes.csv")
# 
# example_contracts %>%
#   rename(
#     d_procurement_id = "procurement_id"
#   ) %>%
#   mutate(
#     d_procurement_id_suffix_reversed_id = stri_reverse(d_procurement_id),
#     d_procurement_id_suffix_pos = str_locate(d_procurement_id_suffix_reversed_id, "/")[,"start"],
#     d_procurement_id_suffix_remainder = str_sub(d_procurement_id_suffix_reversed_id, start = d_procurement_id_suffix_pos + 1L),
#     d_procurement_id = case_when(
#       d_procurement_id_suffix_pos <= 4 ~ stri_reverse(d_procurement_id_suffix_remainder),
#       TRUE ~ d_procurement_id
#     )
#   ) %>%
#   relocate(owner_org, d_vendor_name, starts_with("d_procurement_id")) %>%
#   select(! starts_with("d_procurement_id_suffix")) %>%
#   View()

# Testing (2022-07-06)

# tibble(parent_company = top_n_vendors) %>%
#   write_csv(str_c("data/testing/", today(), "-vendors-above-annual-threshold.csv"))
# 
# contracts %>%
#   select(d_vendor_name) %>%
#   distinct() %>%
#   arrange(d_vendor_name) %>%
#   write_csv(str_c("data/testing/", today(), "-all-vendors.csv"))

# Testing (2022-07-11)

# contract_spending_by_date %>%
#   filter_by_summary_type("core") %>%
#   filter_vendors_if_required(TRUE) %>%
#   View()
# 
# contracts %>% 
#   filter(d_vendor_name == "VANCOUVER SHIPYARDS") %>% 
#   arrange(desc(contract_value )) %>% 
#   select(d_reference_number, d_procurement_id, contract_value, everything()) %>% 
#   View()

# contracts %>%
#   filter(d_vendor_name == "CAE") %>%
#   filter_by_summary_type("core") %>%
#   arrange(desc(contract_value )) %>%
#   select(d_reference_number, d_procurement_id, contract_value, everything()) %>%
#   View()

# contracts %>% 
#   select(owner_org, owner_org_title) %>% 
#   distinct() %>% 
#   arrange(owner_org) %>% 
#   write_csv(str_c("data/testing/tmp-", today(), "-owner-orgs.csv"))

# Testing (2022-08-01)

# vendor_name_to_look_up <- "L3HARRIS"
# 
# contract_spending_overall %>%
#   filter(d_vendor_name == vendor_name_to_look_up) %>%
#   select(owner_org, d_amendment_group_id, d_overall_contract_value, d_overall_start_date, d_overall_end_date, d_most_recent_category, d_most_recent_description_en) %>%
#   arrange(desc(d_overall_contract_value)) %>%
#   distinct() %>%
#   View()
# 
# contracts %>%
#   filter(d_amendment_group_id == "csa-asc-C-2017-2018-Q1-00001") %>%
#   View()
# 
# 
# economic_object_code_name_to_look_up <- "1244"
# 
# contracts %>%
#   filter(d_economic_object_code == economic_object_code_name_to_look_up) %>%
#   arrange(desc(contract_value)) %>%
#   select(owner_org, d_vendor_name, contract_value, d_description_en, comments_en, additional_comments_en, d_start_date, d_end_date, d_amendment_group_id, procurement_id, original_value, d_economic_object_code, starts_with("category")) %>%
#   View()
# 
# contracts %>%
#   filter(d_vendor_name == vendor_name_to_look_up) %>%
#   select(owner_org, d_vendor_name, procurement_id, reference_number, contract_value, d_description_en, comments_en, additional_comments_en, d_start_date, d_end_date, d_amendment_group_id, original_value, d_economic_object_code, starts_with("category")) %>%
#   arrange(desc(contract_value)) %>%
#   distinct() %>%
#   View()
# 
# contract_spending_overall %>%
#   filter(owner_org == "tc") %>%
#   filter(category == "11_defence") %>%
#   select(owner_org, d_amendment_group_id, d_overall_contract_value, d_overall_start_date, d_overall_end_date, d_most_recent_category, d_most_recent_description_en) %>%
#     arrange(desc(d_overall_contract_value)) %>%
#     distinct() %>%
#   View()
# 
# contracts %>%
#   filter(d_amendment_group_id == "tc-TC-2012-2013-Q2-02004") %>%
#   View()
# 
# example_contracts <- contracts %>%
#   filter(owner_org %in% c("tc", "dnd-mdn")) %>%
#   slice_sample(n = 20)
# 
# example_contracts %>%
#   View()
# 
# example_contracts %>%
#   mutate(
#     category = case_when(
#       owner_org == "dnd-mdn" & category == "3_information_technology" ~ "11_defence",
#       owner_org == "dnd-mdn" & category == "5_transportation_and_logistics" ~ "11_defence",
#       TRUE ~ category
#     )
#   ) %>%
#   View()

# Testing (2022-08-02)

# Review percentages per category

# summary_categories$summary_overall_total_by_vendor_by_category_2017_to_2020[[5]]
# 
# total_it <- summary_overall %>% filter(summary_type == "all") %>% pull(summary_overall_by_category_2017_to_2020) %>% first() %>% filter(d_most_recent_category == "3_information_technology") %>% pull(total)
# 
# summary_categories$summary_overall_total_by_vendor_by_category_2017_to_2020[[5]] %>% slice(1:10) %>% pull(overall_total) %>% sum() / total_it
# 
# 
# # More vendor/category exploration
# vendor_name_to_look_up <- "ROGERS"
# 
# contract_spending_overall %>%
#   filter(d_vendor_name == vendor_name_to_look_up) %>%
#   select(owner_org, d_amendment_group_id, d_overall_contract_value, d_overall_start_date, d_overall_end_date, d_most_recent_category, d_most_recent_description_en) %>%
#   arrange(desc(d_overall_contract_value)) %>%
#   distinct() %>%
#   View()
# 
# # ssc-spc-C-2017-2018-Q1-00615
# # ssc-spc-C-2019-2020-Q1-00179
# # esdc-edsc-C-2022-2023-Q1-00335
# 
# 
# contracts %>%
#   filter(d_amendment_group_id == "esdc-edsc-C-2022-2023-Q1-00335") %>%
#   View()
# 
# economic_object_code_name_to_look_up <- "0491"
# 
# contracts %>%
#   filter(d_economic_object_code == economic_object_code_name_to_look_up) %>%
#   arrange(desc(contract_value)) %>%
#   select(owner_org, d_vendor_name, contract_value, d_description_en, comments_en, additional_comments_en, d_start_date, d_end_date, d_amendment_group_id, procurement_id, original_value, d_economic_object_code, starts_with("category")) %>%
#   View()

# Testing (2022-08-11)

# economic_object_code_name_to_look_up <- "2035"
# 
# contracts %>%
#   filter(d_economic_object_code == str_pad(economic_object_code_name_to_look_up, 4, side = "left", pad = "0")) %>%
#   arrange(desc(contract_value)) %>%
#   select(owner_org, vendor_name, contract_value, d_description_en, comments_en, additional_comments_en, procurement_id, original_value, d_economic_object_code) %>%
#   View()


# # Testing (2022-08-27)
# 
# x <- do_research_findings_call("s432_mean_amendment_increase_percentage", "all", "d_vendor_name", TRUE)
# 
# x <- x %>% arrange(desc(mean_amendment_increase_percentage))
# 
# x %>% View()
# 
# 
# vendor_to_look_up <- "INMARSAT SOLUTIONS"
# 
# y <- find_overall_contracts_by_d_vendor_name(vendor_to_look_up)
# 
# y <- y %>%
#   filter(d_overall_number_of_amendments > 0) %>%
#   # Avoid division by 0 errors
#   filter(d_original_contract_value > 0) %>%
#   mutate(
#     d_contract_value_increase = d_overall_contract_value - d_original_contract_value,
#     d_amendment_increase_percentage = d_contract_value_increase / d_original_contract_value
#   )
# 
# y %>% arrange(desc(d_amendment_increase_percentage)) %>% View()
# 
# # Review this with the updated original value handling above
# # TAG HR
# find_contract_by_d_amendment_group_id("ps-sp-C-2020-2021-Q1-00032")
# 
# # ALTIS HUMAN RESOURCES
# find_contract_by_d_amendment_group_id("ps-sp-C-2020-2021-Q1-00030")
# find_contract_by_d_amendment_group_id("ps-sp-C-2020-2021-Q1-00031")
# 
# # (check amendment handling for)
# find_contract_by_d_amendment_group_id("osfi-bsif-C-2018-2019-Q2-00027") %>% View()
# 
# # MACK TRUCKS 
# find_contract_by_d_amendment_group_id("dnd-mdn-C-2015-2016-Q1-02989")
# 
# 
# # DESIRE2LEARN
# find_contract_by_d_amendment_group_id("csps-efpc-C-2019-2020-Q4-00030")
# 
# 
# # LOGISTIK UNICORP
# find_contract_by_d_amendment_group_id("dnd-mdn-C-2014-2015-Q3-00361") %>% View()
# 
# # PCL CONSTRUCTORS
# find_contract_by_d_amendment_group_id("pwgsc-tpsgc-C-2018-2019-Q4-02019") %>% View()
# 
# # INMARSAT SOLUTIONS
# find_contract_by_d_amendment_group_id("ssc-spc-C-2016-2017-Q2-00004") %>% View()
# 
# 
# contract_spending_overall %>% filter(is.na(d_original_contract_value)) %>% select(d_amendment_group_id, d_vendor_name, d_overall_contract_value, d_original_contract_value)