gistfile1.txt

outputs <- read_excel(path = "data-raw/outputs_profservcices.xlsx") %>% 
  mutate(hours_counted = case_when(
    activity == "Output" ~ hours,
    activity == "A la Carte Referral" & prosper_approved == "Yes" ~ hours,
    activity == "SBLC Referral" & accepted == "Yes" ~ hours,
    activity == "MFS Referral" & accepted == "Yes" ~ hours,
    activity == "MarketLink Referral" & accepted == "Yes" ~ hours
  )) %>% 
  mutate(activity_categorized = case_when(
    activity == "Output" ~ "Output",
    TRUE ~ "Referral"
  )) 

outputs_hours <- outputs %>% 
  group_by(clientid, activity_categorized) %>% 
  summarize(total_hours = sum(hours_counted)) 

outputs_referrals <- outputs %>% 
  filter(activity != "Output") %>% 
  mutate(activity_approved = case_when(
    activity == "A la Carte Referral" & prosper_approved == "Yes" ~ "Yes",
    activity == "SBLC Referral" & accepted == "Yes" ~ "Yes",
    activity == "MFS Referral" & accepted == "Yes" ~ "Yes",
    activity == "MarketLink Referral" & accepted == "Yes" ~ "Yes"
  )) %>% 
  filter(activity_approved == "Yes") %>% 
  count(clientid,
        name = "number_of_referrals")