plotting.Rmd

---
title: "plotting functions for SMT Dental Pack"
---

```{r packages}

library(ggrepel)
library(grid)  # Load the grid package for textGrob
library(tidyverse)
library(formattable)
library(cowplot)
library(data.table)
library(ggpubr)
```

```{r get financial year dates}
# get financial year from month
latest_final_month <- max(UDA_calendar_data$month[UDA_calendar_data$final_yn == "Y"])

get_financial_year <- function(month, ...){
  ifelse(month(month)>3, 
       paste(year(month), "/", substr(year(month)+1,start=3,stop=4), sep = ""), 
       paste(year(month)-1, "/", substr(year(month),start=3,stop=4), sep = ""))
}

# get first month of the financial year
get_fy_start <- function(financial_year){
  as.Date(paste(substr(financial_year,1,4), "04-01", sep = "-"), format = "%Y-%m-%d")
}
```

```{r set financial year dates}
current_fy_start <- get_fy_start(latest_final_month)
current_fy_end <- current_fy_start %m+% months(11)
current_fy <- get_financial_year(current_fy_start)
previous_fy_start <- current_fy_start %m-% months(12)
previous_fy_end <- current_fy_start %m-% months(1)
previous_fy <- get_financial_year(previous_fy_start)

current_fy_month2 <- as.character(as.Date(current_fy_start) %m+% months(1))
current_fy_month3 <- as.character(as.Date(current_fy_start) %m+% months(2))
current_fy_month4 <- as.character(as.Date(current_fy_start) %m+% months(3))
current_fy_month5 <- as.character(as.Date(current_fy_start) %m+% months(4))
current_fy_month6 <- as.character(as.Date(current_fy_start) %m+% months(5))
current_fy_month7 <- as.character(as.Date(current_fy_start) %m+% months(6))
current_fy_month8 <- as.character(as.Date(current_fy_start) %m+% months(7))
current_fy_month9 <- as.character(as.Date(current_fy_start) %m+% months(8))
current_fy_month10 <- as.character(as.Date(current_fy_start) %m+% months(9))
current_fy_month11 <- as.character(as.Date(current_fy_start) %m+% months(10))
current_fy_month12 <- as.character(as.Date(current_fy_start) %m+% months(11))
```

```{r get colour palette}
get_colour_palette <- function(){
  
  # The palette with grey:
  c("#E69F00", "#56B4E9", "#009E73", "#0072B2", "#D55E00", "#CC79A7","#F0E442",  "#999999")
  
}
```

```{r UDA/UOA %delivered/contracted - standardised by working days without historical data}
# function use for export data file
plot_UDA_UOA_delivery_wd <- function(data = UDA_calendar_data, 
                                     UDAorUOA = "UDA",
                                     level = "National",
                                     region_STP_name = NULL,
                                     plotChart = TRUE, 
                                     all_regions_and_STPs = FALSE){
  
  #filter for STP or region-- select the STP or region you want
  if(level == "Regional"){
    
    data <- data %>%
      filter(region_name == region_STP_name)
    subtitle <- region_STP_name
    
  }else if(level == "STP"){
    
    data <- data %>%
      filter(commissioner_name == region_STP_name)
    subtitle <- region_STP_name
    
  }else{
    subtitle <- "England"
  }
  
  if(UDAorUOA == "UDA"){
    
    #get data into the right format if this is for UDA
    data <- get_delivery_data(data = data, UDAorUOA = "UDA", all_regions_and_STPs =all_regions_and_STPs)
    
    #set title and ylab and target of percentage of UDA delivered for each quarter
    title <- "monthly percentage of usual annual contracted UDAs \nsubmitted across all contracts standardised by working days"
    ylab <- "% of contracted UDAs submitted"
    #captionTitle <- "*These are treatment months."
    
    lineCol <- "#CC79A7"
    
    # calculate percentage of delivered UDA standardised by no of working days
    data <- data %>% 
      left_join(working_days,by=c('month')) %>% 
      mutate(perc_standardised_wd_int = 
               100*monthly_UDA_UOAs_delivered /(annual_contracted_UDA_UOA*(`no workdays`/`total workdays`))) %>% 
      mutate(perc_standardised_wd = round(perc_standardised_wd_int, digits = 0))
    
  }else{
    
    #get data into the right format if this is for UOA
    data <- get_delivery_data(data = data, UDAorUOA = "UOA", all_regions_and_STPs = F)
    title <- "monthly percentage of usual annual contracted UOAs \nsubmitted across all contracts standardised by working days"
    ylab <- "% of contracted UOAs submitted"
    #captionTitle <- "**These are treatment months.."
    lineCol <- "steelblue"
    
    # to calculate percentage of delivered UOA standardized by no of working days
    data <- data %>% 
      left_join(working_days,by=c('month')) %>% 
      mutate(perc_standardised_wd_int = 
               100*monthly_UDA_UOAs_delivered /(annual_contracted_UDA_UOA*(`no workdays`/`total workdays`))) %>% 
      mutate(perc_standardised_wd = round(perc_standardised_wd_int, digits = 0))
  }
  
  data <- data %>%
    mutate(month = as.Date(month))
  
  if(plotChart == TRUE){
    
    # plot code
    p <- ggplot(data, 
                aes(x = month, 
                    y = perc_standardised_wd)) +
      theme_bw() +
      geom_line(colour = "blue",
                linewidth = 1) +
      geom_point(colour = lineCol) 
      
      p <- p +
        annotate(geom = "text",
                 x = data$month,
                 y = data$perc_standardised_wd - 3,
                 label = paste0(data$perc_standardised_wd, "%"),
                 size = 3,
                 color="blue")
      
    p <- p +
      scale_x_date(date_breaks = "1 month",
                   date_labels = "%b-%y") +
      scale_y_continuous(limits = c(0, max(c(data$perc_standardised_wd, 95), na.rm = T) + 5),
                         breaks = seq(0, max(c(data$perc_standardised_wd, 95), na.rm = T) + 5, 10)) +
      labs(title = title,
           x = "Month",
           y = ylab,
           subtitle = subtitle) +
      theme(axis.text.x = element_text(angle = 90, vjust=-0.0001))
    
    p
    
  }else{
    
    # what if plotChart == FALSE -- want to create a table instead of a plot
    if(UDAorUOA == "UDA"){
      new_col_names <- c("calendar_month" = "month",
                         "UDAs_delivered_month" = "monthly_UDA_UOAs_delivered",
                         "UDAs_annual_contracted" = "annual_contracted_UDA_UOA",
                         "UDAs_delivered_month_percent_contracted_standardised" = "perc_standardised_wd")
    }else{
      
      new_col_names <- c("calendar_month" = "month",
                         "UOAs_delivered_month" = "monthly_UDA_UOAs_delivered",
                         "UOAs_annual_contracted" = "annual_contracted_UDA_UOA",
                         "UOAs_delivered_month_percent_contracted_standardised" = "perc_standardised_wd")
      
      data <- data %>% 
        mutate(financial_year = get_financial_year(month))
    }
    
    data$`month` <- format(as.Date(data$`month`), "%Y-%m")
    
    #rename columns
    data <- data %>%
      rename(any_of(new_col_names))
  }
}

```

```{r UDA/UOA %delivered/contracted - standardised by working days without historical data -- include all regions}
# function used for export data file
plot_UDA_UOA_delivery_all_regions <- function(data = UDA_calendar_data, 
                                              UDAorUOA = "UDA",
                                              level = "National",
                                              region_STP_name = NULL,
                                              plotChart = TRUE, 
                                              all_regions_and_STPs = FALSE){
   
  if(UDAorUOA == "UDA"){
    
    #include data only after 2020-04-01 as there is no region info before that and select fields needed
   data <- data %>%
     mutate(month = as.Date(month)) %>%
     filter(month >= as.Date ("2020-04-01")) %>%
     select(month, contract_number, commissioner_name, region_name, 
             annual_contracted_UDA, UDA_band_1,UDA_band_2,UDA_band_3,UDA_other,UDA_urgent,UDA_delivered)

  # get data into the right format 
  # calculate the annual contracted UDA, delivered UDA and percentage of UDA delivered (standardised by no of working days)
   data <- get_delivery_data(data = data, UDAorUOA = "UDA", all_regions_and_STPs = TRUE)
   data <- data %>%
     filter(month>"2020-03-01")
   
   data <- data %>%
    group_by(month, region_name) %>%
    summarise(monthly_UDA_UOAs_BAND1_delivered = sum(monthly_UDA_UOAs_BAND1_delivered, na.rm = TRUE),
              monthly_UDA_UOAs_BAND2_delivered = sum(monthly_UDA_UOAs_BAND2_delivered, na.rm = TRUE),
              monthly_UDA_UOAs_BAND3_delivered = sum(monthly_UDA_UOAs_BAND3_delivered, na.rm = TRUE),
              monthly_UDA_UOAs_BAND_OTHER_delivered = sum(monthly_UDA_UOAs_BAND_OTHER_delivered, na.rm = TRUE),
              monthly_UDA_UOAs_BAND_URGENT_delivered = sum(monthly_UDA_UOAs_BAND_URGENT_delivered, na.rm = TRUE),
              monthly_UDA_UOAs_delivered = sum(monthly_UDA_UOAs_delivered, na.rm = TRUE),
              annual_contracted_UDA_UOA = sum(annual_contracted_UDA_UOA, na.rm = TRUE)) %>%
    filter(!is.na(region_name))
  

   #percentage of UDA delivered (standardised by no of working days)
   data <- data %>% 
     left_join(working_days,by=c('month')) %>% 
      mutate(perc_standardised_wd_int = 
               100*(monthly_UDA_UOAs_delivered /(annual_contracted_UDA_UOA*(`no workdays`/`total workdays`))))%>% 
      mutate(perc_standardised_wd = round(perc_standardised_wd_int, digits = 0))
   
   title <- "Calendar monthly percentage of usual annual contracted UDAs \nsubmitted across all contracts standardised by working days"
   ylab <- "% of contracted UDAs submitted"
   # captionTitle <- "*These are calendar months and data has been scaled up by 12."
   lineCol <- "coral"
   lineCol <- "#CC79A7"
   
  }else{
    
    #include data only after 2020-04-01 as there is no region info before that and select fields needed
    data <- data %>%
      mutate(month = as.Date(month)) %>%
      filter(month >= as.Date ("2020-04-01")) %>%
      select(month, contract_number, commissioner_name, region_name, 
             annual_contracted_UOA, UOA_delivered)
    
    # get data into the right format 
    # calculate the annual contracted UOA, delivered UOA and percentage of UOA delivered (standardized by no of working days)
    data <- get_delivery_data(data = data, UDAorUOA = "UOA", all_regions_and_STPs = TRUE)
    data <- data %>% 
      filter(month>"2020-03-01")
    
    data <- data %>%
      group_by(month, region_name) %>%
      summarise(monthly_UDA_UOAs_delivered = sum(monthly_UDA_UOAs_delivered, na.rm = TRUE),
              annual_contracted_UDA_UOA = sum(annual_contracted_UDA_UOA, na.rm = TRUE)) %>%
      filter(!is.na(region_name))
    
    data <- data %>% 
      left_join(working_days,by=c('month')) %>% 
      mutate(perc_standardised_wd_int = 
               100*(monthly_UDA_UOAs_delivered /(annual_contracted_UDA_UOA*(`no workdays`/`total workdays`))))%>% 
      mutate(perc_standardised_wd = round(perc_standardised_wd_int, digits = 0))
    
    title <- "Calendar monthly percentage of usual annual contracted UOAs \nsubmitted across all contracts standardised by working days"
    ylab <- "% of contracted UOAs submitted"
    # captionTitle <- "*These are calendar months and data has been scaled up by 12."
    lineCol <- "coral"
    lineCol <- "#CC79A7"
  }   
    
  if(plotChart == TRUE){
    
    #plot code
    p <- ggplot(data, 
                aes(x = month, 
                    y = perc_standardised_wd, 
                    colour = region_name)) +
      theme_bw() +
      geom_line(size = 1) +
      geom_point() +
      scale_x_date(date_breaks = "1 month", 
                   date_labels = "%b-%y") +
      scale_y_continuous(limits = c(0, max(c(data$perc_standardised_wd, 95), na.rm = T) + 5),
                         breaks = seq(0, max(c(data$perc_standardised_wd, 95), na.rm = T) + 5, 10)) +
      labs(title = title, 
           x = "Month",
           y = ylab, 
           #caption = captionTitle,
           colour = "Region") +
      theme(axis.text.x = element_text(angle = 90, vjust=-0.0001))
    
    p
    
  }else{
   #IF WANT TO GET A DATA TABLE -- rename
    if(UDAorUOA == "UDA"){
      new_col_names <- c("calendar_month" = "month",
                         "UDAs_delivered_month" = "monthly_UDA_UOAs_delivered",
                         "UDAs_annual_contracted" = "annual_contracted_UDA_UOA",
                         "UDAs_delivered_month_percent_contracted_standardised" = "perc_standardised_wd")
    }else{
      
      new_col_names <- c("calendar_month" = "month",
                         "UOAs_delivered_month" = "monthly_UDA_UOAs_delivered",
                         "UOAs_annual_contracted" = "annual_contracted_UDA_UOA",
                         "UOAs_delivered_month_percent_contracted_standardised" = "perc_standardised_wd")
      
      data <- data %>% 
        mutate(financial_year = get_financial_year(month))
    }
   
    data$`month` <- format(as.Date(data$`month`), "%Y-%m")
    
     data <- data %>%
      rename(any_of(new_col_names))
  }
}
```

```{r UDA/UOA %delivered/contracted - standardised by working days without historical data -- include all ICBs -- table}
# used in export data file
Table_UDA_UOA_delivery_all_ICBs <- function(data = UOA_calendar_data,
                                            UDAorUOA = "UOA"){

  if(UDAorUOA == "UDA"){
    #include data only after 2020-04-01 and select fields needed
    data <- data %>%
      mutate(month = as.Date(month)) %>%
      filter(month >= as.Date ("2020-04-01")) %>%
      select(month, contract_number, commissioner_name, region_name, annual_contracted_UDA, UDA_band_1,UDA_band_2,UDA_band_3,UDA_other,UDA_urgent,UDA_delivered)
    
    #get data into the right format and calculate the annual contracted UDA, delivered UDA and percentage of UDA delivered
    data <- get_delivery_data(data = data, UDAorUOA = "UDA", all_regions_and_STPs = TRUE)
    data <- data %>%
      filter(month>"2020-03-01")
    
    data <- data %>%
      group_by(month, commissioner_name) %>%
      summarise(monthly_UDA_UOAs_BAND1_delivered = sum(monthly_UDA_UOAs_BAND1_delivered, na.rm = TRUE),
              monthly_UDA_UOAs_BAND2_delivered = sum(monthly_UDA_UOAs_BAND2_delivered, na.rm = TRUE),
              monthly_UDA_UOAs_BAND3_delivered = sum(monthly_UDA_UOAs_BAND3_delivered, na.rm = TRUE),
              monthly_UDA_UOAs_BAND_OTHER_delivered = sum(monthly_UDA_UOAs_BAND_OTHER_delivered, na.rm = TRUE),
              monthly_UDA_UOAs_BAND_URGENT_delivered = sum(monthly_UDA_UOAs_BAND_URGENT_delivered, na.rm = TRUE),
              monthly_UDA_UOAs_delivered = sum(monthly_UDA_UOAs_delivered, na.rm = TRUE),
                annual_contracted_UDA_UOA = sum(annual_contracted_UDA_UOA, na.rm = TRUE))
    
    data <- data %>% 
      left_join(working_days,by=c('month')) %>% 
      mutate(perc_standardised_wd_int = 
               100*(monthly_UDA_UOAs_delivered /(annual_contracted_UDA_UOA*(`no workdays`/`total workdays`))))%>% 
      mutate(perc_standardised_wd = round(perc_standardised_wd_int, digits = 0))
    
   #rename columns
   new_col_names <- c("calendar_month" = "month",
                         "UDAs_delivered_month" = "monthly_UDA_UOAs_delivered",
                         "UDAs_annual_contracted" = "annual_contracted_UDA_UOA",
                         "UDAs_delivered_month_percent_contracted_standardised" = "perc_standardised_wd")
   
   data$`month` <- format(as.Date(data$`month`), "%Y-%m")
   data <- data %>%
      rename(any_of(new_col_names))
   
  }else{
    
    #do similar things for UOA
    data <- data %>%
      mutate(month = as.Date(month)) %>%
      filter(month >= as.Date ("2020-04-01")) %>%
      select(month, contract_number, commissioner_name, region_name,  annual_contracted_UOA,UOA_delivered)
    
    #get data into the right format and calculate the annual contracted UDA, delivered UDA and percentage of UDA delivered
    data <- get_delivery_data(data = data, UDAorUOA = "UOA", all_regions_and_STPs = TRUE)
    data <- data %>%
      filter(month>"2020-03-01")
    
    data <- data %>%
      group_by(month, commissioner_name) %>%
      summarise(monthly_UDA_UOAs_delivered = sum(monthly_UDA_UOAs_delivered, na.rm = TRUE),
                annual_contracted_UDA_UOA = sum(annual_contracted_UDA_UOA, na.rm = TRUE))
    
    data <- data %>% 
      left_join(working_days,by=c('month')) %>% 
      mutate(perc_standardised_wd_int = 
               100*(monthly_UDA_UOAs_delivered /(annual_contracted_UDA_UOA*(`no workdays`/`total workdays`))))%>% 
      mutate(perc_standardised_wd = round(perc_standardised_wd_int, digits = 0)) %>% 
      rename(calendar_month = month)
    
    data <- data %>% 
        mutate(financial_year = get_financial_year(calendar_month))
    
    data$calendar_month <- format(as.Date(data$calendar_month), "%Y-%m")
    
    data <- data
  }   
  
}  

```

```{r UDA activity- standardised by working days with historical data}

plot_historic_UDA_delivery_wd <- function(data = UDA_calendar_data,
                                          level = "National",
                                          region_STP_name = NULL){
  
  #filter for STP or region in need
  if(level == "Regional"){
    data <- data %>%
      filter(region_name == region_STP_name)
    subtitle <- region_STP_name
    
  }else if(level == "STP"){
    
    data <- data %>%
      filter(commissioner_name == region_STP_name)
    subtitle <- region_STP_name
    
  }else{
    
    subtitle <- "England"
  }
  
  #prepare historic data 
  historic_data <- data %>%
    select(month, contract_number, commissioner_name, region_name,
           annual_contracted_UDA, UDA_delivered)
  
  #create a new field as financial year
  # calculate full year UDA delivered, contracted UDA and percentage of UDA delivered
  historic_data <- historic_data  %>%
    mutate(financial_year = get_financial_year(month))
  
  historic_latest_contracted_uda <- historic_data %>% 
    group_by(month, financial_year) %>% 
    summarise(final_contracted_UDAs = sum(annual_contracted_UDA, na.rm = TRUE)) %>% 
    filter(month(month) == 3)
  
  historic_data <- historic_data %>% 
    group_by(financial_year) %>% 
    summarise(full_year_UDA_delivery = sum(UDA_delivered, na.rm = TRUE)) %>% 
    left_join(historic_latest_contracted_uda, by = "financial_year") %>% 
    mutate(perc_UDA_delivered = full_year_UDA_delivery * 100 / final_contracted_UDAs)
  
  #get current FY data ready
  # way to calculate percentage of UDA delivered which is standardized by no of working days 
  #calculate UDA_delivery & contracted_UDAs
  data_try <- data %>%
    group_by(month) %>%
    summarise(UDA_delivery = sum(UDA_delivered, na.rm = TRUE),
              contracted_UDAs = sum(annual_contracted_UDA, na.rm = TRUE)) %>%
    # only include current FY data -- calculate percentage of UDA delivered -- standardized by no of working days 
    filter(month >= as.Date (current_fy_start)) %>%
    left_join(working_days,by=c('month')) %>% 
    mutate(perc_UDA_delivered = 100* (UDA_delivery /(contracted_UDAs*(`no workdays`/`total workdays`)))) %>%
    mutate(perc_UDA_delivered = round(perc_UDA_delivered, digits = 0)) %>% 
    bind_rows(historic_data) %>% 
    filter(!is.na(month))
  
  #divide data in to "Previous Financial Years (Averages)", "Current Financial Year"
  data <- data_try %>%
    mutate(year_type = ifelse(is.na(financial_year), "Current Financial Year", 
                              "Previous Financial Years (Averages)"),
           month  = as.Date(ifelse(is.na(financial_year), month, get_fy_start(financial_year))))
  
  data$year_type <- factor(data$year_type, levels = c("Previous Financial Years (Averages)",
                                                      "Current Financial Year"))
  
  data <- data %>% 
    mutate(line_for_100 = 100)
  
  #divide data to two datasets -- previous and current FY
  data1 <- data %>%
    filter(year_type=="Previous Financial Years (Averages)") 
  
  data2 <- data%>%
    filter(year_type=="Current Financial Year") 
  
# Define plot 1
p1 <- ggplot() +
  geom_line(data = data1, aes(x = month, y = perc_UDA_delivered), colour = "black") +
  geom_line(data = data1, aes(x = month, y = line_for_100), colour = "blue") +
  geom_point(data = data1, aes(x = month, y = perc_UDA_delivered), colour = "steelblue") +
  ylab("Percentage of contracted UDAs delivered") +
  xlab(NULL) +
  ggtitle("Previous Financial Years (Averages)") +
  geom_text(data = filter(data1, year_type == "Previous Financial Years (Averages)"),
            aes(x = month + 120, y = perc_UDA_delivered + 4, label = paste0(round(perc_UDA_delivered), "%")),
            size = 3) +
  theme_bw() +
  scale_y_continuous(limits = c(0, max(c(data1$perc_UDA_delivered, 95), na.rm = T) + 5),
                     breaks = seq(0, max(c(data1$perc_UDA_delivered, 95), na.rm = T) + 5, 10)) +
  theme(axis.text.x = element_text(angle = 90, vjust = -0.0001),
        legend.position = "none")

# Define plot 2
p2 <- ggplot() +
  geom_line(data = subset(data2, month <= latest_final_month), linetype = 1, 
            aes(x = month, y = perc_UDA_delivered), colour = "black") +
  geom_line(data = subset(data2, month >= latest_final_month), linetype = 2,
            aes(x = month, y = perc_UDA_delivered), colour = "black") +
  geom_line(data = data2, aes(x = month, y = line_for_100), colour = "blue") +
  geom_point(data = data2, aes(x = month, y = perc_UDA_delivered), colour = "steelblue") +
  ylab(NULL) +
  xlab(NULL) +
  ggtitle("Current Financial Year") +
  geom_text(data = filter(data2, year_type == "Current Financial Year"),
            aes(x = month, y = perc_UDA_delivered + 4, label = paste0(round(perc_UDA_delivered), "%")),
            size = 3) +
  scale_x_date(breaks = "1 month", date_labels = "%b %Y") +
  scale_y_continuous(limits = c(0, max(c(data2$perc_UDA_delivered, 95), na.rm = T) + 5),
                     breaks = seq(0, max(c(data2$perc_UDA_delivered, 95), na.rm = T) + 5, 10)) +
  theme_bw() +
  theme(axis.text.x = element_text(angle = 90, vjust = -0.0001),
        legend.position = "none")

# Define title and subtitle
main_title <- "Monthly percentage of usual annual contracted UDAs standardised by working days"

# Arrange and annotate figure
figure <- ggarrange(p1, p2, nrow = 1)

# Add footnote
footnote_text <- "Note: Contracts included: GDS/PDS/PDS+ where Total contracted UDA>100.
The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher."

# Create annotation
annotation <- annotate_figure(
  figure,
  top = text_grob(paste(main_title, "\n", subtitle), hjust = 0, x = unit(40, "pt"), size = 14),
  bottom = text_grob("Time period")
)

# Add footnote through annotation
annotation2 <- annotate_figure(
  annotation, 
  bottom = textGrob(footnote_text, x = unit(0, "npc"), y = unit(0, "npc") + unit(1, "line"), just = "left", gp = gpar(fontsize = 10, fontface = "italic"))
)

print(annotation2)

}
```

```{r UOA activity data - with historic data}
plot_historic_UOA_delivery_wd <- function(data = UOA_calendar_data,
                                          level = "National",
                                          region_STP_name = NULL){
  
  #filter for STP or region in need
  if(level == "Regional"){
    data <- data %>%
      filter(region_name == region_STP_name )
    
    subtitle <- region_STP_name
    
  }else if(level == "STP"){
    data <- data %>%
      filter(commissioner_name == region_STP_name)
    
    subtitle <- region_STP_name
    
  }else{
    subtitle <- "England"
  }
  
  #prepare historical data
  historic_data <- data %>%
    select(month, contract_number, commissioner_name, region_name,
           annual_contracted_UOA, UOA_delivered)

  # create a new field as financial year
  # then calculate full year UDA delivered, contracted UDA and percentage of UDA delivered
  historic_data <- historic_data  %>%
    mutate(financial_year = get_financial_year(month))

  historic_latest_contracted_uoa <- historic_data %>%
    group_by(month, financial_year) %>%
    summarise(final_contracted_UOAs = sum(annual_contracted_UOA, na.rm = TRUE)) %>%
    filter(month(month) == 3)

  historic_data <- historic_data %>%
    group_by(financial_year) %>%
    summarise(full_year_UOA_delivery = sum(UOA_delivered, na.rm = TRUE)) %>%
    left_join(historic_latest_contracted_uoa, by = "financial_year") %>%
    mutate(perc_UOA_delivered = full_year_UOA_delivery * 100 / final_contracted_UOAs) %>%
    filter(!is.na(financial_year))

  #get current FY data ready
  data_try <- data %>%
    group_by(month) %>%
    summarise(UOA_delivery = sum(UOA_delivered, na.rm = TRUE),
              contracted_UOAs = sum(annual_contracted_UOA, na.rm = TRUE)) %>%
    # only include current FY data -- calculate percentage of UDA delivered -- standardized by no of working days
    filter(month >= as.Date (current_fy_start)) %>%
    left_join(working_days,by=c('month')) %>% 
    mutate(perc_UOA_delivered =100* (UOA_delivery / (contracted_UOAs*(`no workdays`/`total workdays`)))) %>%
    mutate(perc_UOA_delivered = round(perc_UOA_delivered, digits = 0)) %>%
    bind_rows(historic_data) %>% 
    filter(!is.na(month))
  
  #divide data in to "Previous Financial Years (Averages)", "Current Financial Year"
  data <- data_try %>%
    mutate(year_type = if_else(is.na(financial_year), "Current Financial Year", 
                               "Previous Financial Years (Averages)"), 
           month  = as.Date(ifelse(is.na(financial_year), month, get_fy_start(financial_year))))
  
  data$year_type <- factor(data$year_type, levels = c("Previous Financial Years (Averages)",
                                                      "Current Financial Year"))
  
  #add a target line
  data<- data %>% 
    mutate(line_for_100 = 100)
  
  #divide data to two datasets -- previous and current FY
  data1 <- data %>%
    filter(year_type=="Previous Financial Years (Averages)")
  
  data2 <- data%>%
    filter(year_type=="Current Financial Year")
  
  # Define plot 1
  p1 <- ggplot() +
    geom_line(data = data1,
              aes(x = month,
                  y = perc_UOA_delivered),
              colour = "black") +
    geom_line(data = data1,
              aes(x = month,
                  y = line_for_100),
              colour = "blue") +
    geom_point(data = data1,
               aes(x = month,
                   y = perc_UOA_delivered),
               colour = "steelblue") +
    ylab("Percentage of contracted UOAs delivered") +
    xlab(NULL) +
    geom_text(data = filter(data1, year_type == "Previous Financial Years (Averages)"),
              aes(x = month + 120,
                  y = perc_UOA_delivered - 4,
                  label = financial_year),
              size = 3) +
    geom_text(data = filter(data1, year_type == "Previous Financial Years (Averages)"),
              aes(x = month + 120,
                  y = perc_UOA_delivered + 4,
                  label = paste0(round(perc_UOA_delivered), "%")),
              size = 3) +
    theme_bw() +
    scale_y_continuous(limits = c(0, max(c(data1$perc_UOA_delivered, 95), na.rm = T) + 5),
                       breaks = seq(0, max(c(data1$perc_UOA_delivered, 95), na.rm = T) + 5, 10)) +
    theme(axis.text.x = element_text(angle = 90, vjust=-0.0001),
          legend.position = "none")
  
  # Define plot 2
  p2 <- ggplot() +
    geom_line(data = data2, aes(x = month, y = perc_UOA_delivered), linetype = 2, colour = "black") +
    geom_line(data = data2, aes(x = month, y = line_for_100), colour = "blue") +
    geom_point(data = data2, aes(x = month, y = perc_UOA_delivered), colour = "steelblue") +
    ylab(NULL) +
    xlab(NULL) +
    ggtitle("Current Financial Year") +
    geom_text(data = filter(data2, year_type == "Current Financial Year"),
              aes(x = month, y = perc_UOA_delivered + 4, label = paste0(round(perc_UOA_delivered), "%")),
              size = 3) +
    scale_x_date(breaks = "1 month", date_labels = "%b %Y") +
    scale_y_continuous(limits = c(0, max(c(data2$perc_UOA_delivered, 95), na.rm = T) + 5),
                       breaks = seq(0, max(c(data2$perc_UOA_delivered, 95), na.rm = T) + 5, 10)) +
    theme_bw() +
    theme(axis.text.x = element_text(angle = 90, vjust = -0.0001),
          legend.position = "none")
  
  # Define title and subtitle
  main_title <- "Monthly percentage of usual annual contracted UOAs standardised by working days"
  
  # Arrange and annotate figure
  figure <- ggarrange(p1, 
                      p2, 
                      nrow = 1)
  
  # Add footnote
  footnote_text <- "Note: Contracts included: GDS/PDS/PDS+ excluding contracts with no annual contracted UOAs.
  All data for the latest financial year will be incomplete and subject to change as there is no restriction on when UOA information can be submitted. We expect final data to be higher."
  
  # Create annotation
  annotation <- annotate_figure(
    figure, 
    top = text_grob(paste(main_title, "\n", subtitle), hjust = 0, x = unit(40, "pt"), size = 14), 
    bottom = text_grob("Time period")
  )
  
  # Add footnote through annotation
  annotation2 <- annotate_figure(
    annotation, 
    bottom = textGrob(footnote_text, x = unit(0, "npc"), y = unit(0, "npc") + unit(1, "line"), just = "left", gp = gpar(fontsize = 10, fontface = "italic"))
  )
  
  print(annotation2)
}
```

``` {r YTD UDA/UDA delivered}
plot_YTD_UDA_UOA_delivery <- function(data = UDA_calendar_data, 
                                      UDAorUOA = "UDA",
                                      level = "National",
                                      region_STP_name = NULL,
                                      plotChart = TRUE, 
                                      all_regions_and_STPs = FALSE){

  #filter for STP or region in need
  if(level == "Regional"){
    data <- data %>%
      filter(region_name == region_STP_name)
    
    subtitle <- region_STP_name
    
  }else if(level == "STP"){
    data <- data %>%
      filter(commissioner_name == region_STP_name)
    
    subtitle <- region_STP_name
  }else{
    subtitle <- "England"
  }
  
  if(UDAorUOA == "UDA"){
    #get data into the right format for UDA
    data <- get_delivery_data(data, UDAorUOA = "UDA", all_regions_and_STPs = all_regions_and_STPs)
    title <- "YTD UDA delivery across all contracts against annual contracted UDAs (millions)"
    ylab <- "YTD UDA delivery (millions)"
    captionTitle <- "Contracts included: GDS/PDS/PDS+ where Total contracted UDA>100.
    The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher."
    lineCol <- "#CC79A7"
    labels <- c("Expected YTD delivery to meet contracted UDAs \nif delivery were equal across the financial year", 
                "Total annual contracted UDAs", 
                "Actual YTD delivery")
  }else{
    #get data into the right format for UOA
    data <- get_delivery_data(data, UDAorUOA = "UOA", all_regions_and_STPs = all_regions_and_STPs)
    title <- "YTD UOA delivery across all contracts against annual contracted UOAs (millions)"
    ylab <- "YTD UOA delivery (millions)"
    captionTitle <- "Contracts included: GDS/PDS/PDS+ excluding contracts with no annual contracted UOAs.
All data for the latest financial year will be incomplete and subject to change as there is no restriction on when UOA information can be submitted. We expect final data to be higher."
    lineCol <- "#CC79A7"
    labels <- c("Expected YTD delivery to meet contracted UOAs \nif delivery were equal across the financial year",

                "Total annual contracted UOAs", 
                "Actual YTD delivery")
  }
  
  # extract previous financial year data -- calculate cumulative sum of monthly_UDA_UOAs_delivered
  historic_data <- data %>% 
    filter(month >= as.Date(previous_fy_start) & month <= as.Date(previous_fy_end)) %>% 
    mutate(YTD_delivery = cumsum(monthly_UDA_UOAs_delivered))
  
  # extract current financial year data -- calculate cumulative sum of monthly_UDA_UOAs_delivered
  data <- data %>%
    filter(month >= as.Date(current_fy_start)) %>% 
    mutate(YTD_delivery = cumsum(monthly_UDA_UOAs_delivered))

  # add latest contracted UDA/UOA as a column to use in plot
  # also add for previous financial year
  # output both as a value to be used in later calculations
  data$latest_annual_contracted_UDA_UOAs <- dplyr::last(data$annual_contracted_UDA_UOA)
  mean_annual_contracted_UDA_UOAs <- dplyr::last(data$annual_contracted_UDA_UOA)
  historic_data$latest_annual_contracted_UDA_UOAs <- dplyr::last(historic_data$annual_contracted_UDA_UOA)
  mean_annual_contracted_UDA_UOAs_historic <- dplyr::last(historic_data$annual_contracted_UDA_UOA)
  
  #add in blanks for the rest of the year
  if(all_regions_and_STPs == FALSE & nrow(data) < 12){
  
    if(!(as.Date(current_fy_month2) %in% data$month)){
      data <- data %>% 
        add_row(month = as.Date(current_fy_month2),
                latest_annual_contracted_UDA_UOAs = mean_annual_contracted_UDA_UOAs)
    }
  
    if(!(as.Date(current_fy_month3) %in% data$month)){
      data <- data %>% 
        add_row(month = as.Date(current_fy_month3),
                latest_annual_contracted_UDA_UOAs = mean_annual_contracted_UDA_UOAs)
    }
  
    if(!(as.Date(current_fy_month4) %in% data$month)){
      data <- data %>% 
        add_row(month = as.Date(current_fy_month4),
                latest_annual_contracted_UDA_UOAs = mean_annual_contracted_UDA_UOAs)
    }
  
    if(!(as.Date(current_fy_month5) %in% data$month)){
      data <- data %>% 
        add_row(month = as.Date(current_fy_month5),
                latest_annual_contracted_UDA_UOAs = mean_annual_contracted_UDA_UOAs)
    }
  
    if(!(as.Date(current_fy_month6) %in% data$month)){
      data <- data %>% 
        add_row(month = as.Date(current_fy_month6), 
                latest_annual_contracted_UDA_UOAs = mean_annual_contracted_UDA_UOAs)
    }
  
    if(!(as.Date(current_fy_month7) %in% data$month)){
      data <- data %>% 
        add_row(month = as.Date(current_fy_month7),
                latest_annual_contracted_UDA_UOAs = mean_annual_contracted_UDA_UOAs)
    }
  
    if(!(as.Date(current_fy_month8) %in% data$month)){
      data <- data %>% 
        add_row(month = as.Date(current_fy_month8),
                latest_annual_contracted_UDA_UOAs = mean_annual_contracted_UDA_UOAs)
    }
  
    if(!(as.Date(current_fy_month9) %in% data$month)){
      data <- data %>% 
        add_row(month = as.Date(current_fy_month9),
                latest_annual_contracted_UDA_UOAs = mean_annual_contracted_UDA_UOAs)
    }
  
    if(!(as.Date(current_fy_month10) %in% data$month)){
      data <- data %>% 
        add_row(month = as.Date(current_fy_month10),
                latest_annual_contracted_UDA_UOAs = mean_annual_contracted_UDA_UOAs)
    }
  
    if(!(as.Date(current_fy_month11) %in% data$month)){
      data <- data %>% 
        add_row(month = as.Date(current_fy_month11),
                latest_annual_contracted_UDA_UOAs = mean_annual_contracted_UDA_UOAs)
    }
  
    if(!(as.Date(current_fy_month12) %in% data$month)){
      data <- data %>% 
        add_row(month = as.Date(current_fy_month12),
                latest_annual_contracted_UDA_UOAs = mean_annual_contracted_UDA_UOAs)
    }
  }
  
  #add column for expected YTD delivery
  data_to_print <- data %>%
    mutate(expected_delivery = 
             seq(mean_annual_contracted_UDA_UOAs/12, mean_annual_contracted_UDA_UOAs, mean_annual_contracted_UDA_UOAs/12)) %>%
    select(month, YTD_delivery, expected_delivery, latest_annual_contracted_UDA_UOAs)
  
  #pivot for plotting
  data <- data_to_print %>%
    pivot_longer(cols = c("YTD_delivery", "expected_delivery", "latest_annual_contracted_UDA_UOAs"),
                 names_to = "measure", 
                 values_to = "value")

  # format historic data to match current year
  historic_data_to_print <- historic_data %>% 
    mutate(expected_delivery = 
             seq(mean_annual_contracted_UDA_UOAs_historic/12, mean_annual_contracted_UDA_UOAs_historic, 
                 mean_annual_contracted_UDA_UOAs_historic/12)) %>%
    select(month, YTD_delivery, expected_delivery, latest_annual_contracted_UDA_UOAs)

  historic_data <- historic_data_to_print %>% 
    pivot_longer(cols = c("YTD_delivery", "expected_delivery", "latest_annual_contracted_UDA_UOAs"),
                 names_to = "measure", 
                 values_to = "value")

  # bind current and previous financial years
  # pivot so latest contracted UDA or UOAs is a separate column
  data <- rbind(historic_data, data) %>% 
    mutate(year_type = ifelse(month > max(historic_data$month), "Current Financial Year", "Previous Financial Year"))

  data$year_type <- factor(data$year_type, levels = c("Previous Financial Year",
                                                      "Current Financial Year"))  
  
  current_yr_latest_data <- data %>% 
    filter(measure == "YTD_delivery" & value > 0)
  
  prev_yr_latest_data <- max(historic_data$month)
  
  if(plotChart == TRUE){
    if(UDAorUOA == "UDA"){
      
    current_yr_latest_data <- latest_final_month
    
    data_labels <- data %>% 
      filter(month %in% c(prev_yr_latest_data, current_yr_latest_data) & measure == "YTD_delivery")
    
      #plot code
    p <- ggplot(data, aes(x = month, 
                          y = value/1000000, 
                          colour = measure)) +
      theme_bw() +
      geom_line(data = subset(data, measure == "expected_delivery"), linetype = 2, size = 1) +
      geom_line(data = subset(data, measure == "latest_annual_contracted_UDA_UOAs"), 
                linetype = 2, size = 1) +
      
      geom_line(data = subset(data, measure == "YTD_delivery" & month <= latest_final_month), 
                linetype = 1, size = 1) +
      geom_line(data = subset(data, measure == "YTD_delivery" & month >= latest_final_month), 
                linetype = 2, size = 1) +
      geom_point() +
      geom_text_repel(aes(label = prettyNum(value, big.mark=",")), 
                      data = data_labels, 
                      colour = "black", 
                      position = "dodge") + 

      scale_x_date(date_breaks = "1 month",
                   date_labels = "%b-%y") +
      labs(title = title,
           x = "Month",
           y = ylab,
           subtitle = subtitle,
           caption = captionTitle,
           colour = "",
           linetype = "") +
        
      theme(axis.text.x = element_text(angle = 90, vjust=-0.0001)) +
      facet_wrap(vars(year_type),
                 scales = "free_x") +
        
      scale_colour_manual(values = c("grey", "black", lineCol), labels = labels) +
      scale_linetype_manual(values = c("dashed", "dashed", "solid"), labels = labels,
                            guide = "none") +
      theme(legend.position="bottom")
    
    p
    } else {
      current_yr_latest_data <- max(current_yr_latest_data$month)
      
      data_labels <- data %>% 
        filter(month %in% c(prev_yr_latest_data, current_yr_latest_data) & measure == "YTD_delivery")
      #plot code
    p <- ggplot(data, aes(x = month, 
                          y = value/1000000, 
                          colour = measure)) +
      theme_bw() +
      geom_line(data = subset(data, measure == "expected_delivery"), linetype = 2, size = 1) +
      geom_line(data = subset(data, measure == "latest_annual_contracted_UDA_UOAs"), 
                linetype = 2, size = 1) +
      
      geom_line(data = subset(data, measure == "YTD_delivery" & month <= current_fy_start), 
                linetype = 1, size = 1) +
      geom_line(data = subset(data, measure == "YTD_delivery" & month >= current_fy_start), 
                linetype = 2, size = 1) +
      geom_point() +
      geom_text_repel(aes(label = prettyNum(value, big.mark=",")), 
                      data = data_labels, 
                      colour = "black", 
                      position = "dodge") + 

      scale_x_date(date_breaks = "1 month",
                   date_labels = "%b-%y") +
      labs(title = title,
           x = "Month",
           y = ylab,
           subtitle = subtitle,
           caption = captionTitle,
           colour = "",
           linetype = "") +
        
      theme(axis.text.x = element_text(angle = 90, vjust=-0.0001)) +
      facet_wrap(vars(year_type),
                 scales = "free_x") +
        
      scale_colour_manual(values = c("grey", "black", lineCol), labels = labels) +
      scale_linetype_manual(values = c("dashed", "dashed", "solid"), labels = labels,
                            guide = "none") +
      theme(legend.position="bottom")
    
    p
    }
    
  }else{
    #if want a data table instead
    data_to_print <- data_to_print %>%
      rename(`Calendar month` = month,
             `YTD delivery` = YTD_delivery,
             `Required YTD delivery in order to meet contracted UDAs` = expected_delivery)
    
    data_to_print$`Calendar month` <- format(as.Date(data_to_print$`Calendar month`), "%Y-%m")
  }
}
```

```{r YTD UDA/UOA delivered table}
# function used in export data file
Table_YTD_UDA_UOA_delivery <- function(data = UDA_calendar_data,
                                       UDAorUOA = "UDA",
                                       level = "National",
                                       all_regions_and_STPs = FALSE){

  if(UDAorUOA == "UDA"){
    #get data into the right format for UDA
    data <- get_delivery_data(data, UDAorUOA = "UDA", all_regions_and_STPs = all_regions_and_STPs)
    
  }else{
    #get data into the right format for UOA
    data <- get_delivery_data(data, UDAorUOA = "UOA", all_regions_and_STPs = all_regions_and_STPs)
  }
  
  data$calendar_month <- format(as.Date(data$`month`), "%Y-%m")
  
  #create a new variable 'financial_year'
  data <- data %>% 
    mutate(financial_year= get_financial_year(month))
  
  if(level == "Regional"){
    
    #calculate monthly_UDA_UOAs_delivered by `Calendar month`,region_name, financial_year
    #then cumulative sum of YT delivery by financial_year and region
    data <- data %>%
      filter(month >= as.Date ("2020-04-01")) %>%
      group_by(calendar_month,region_name, financial_year) %>%
      summarise(monthly_UDA_UOAs_delivered = sum(monthly_UDA_UOAs_delivered)) %>%
      group_by(region_name, financial_year) %>%
      dplyr::mutate(YTD_delivery = cumsum(monthly_UDA_UOAs_delivered)) %>%
      select(calendar_month,YTD_delivery)
    
  }else if(level == "STP"){
   
    #calculate monthly_UDA_UOAs_delivered by calendar_month,commissioner_name, financial_year
    #then cumulative sum of YT delivery by financial_year and commissioner_name
    data <- data %>%
      filter(month >= as.Date ("2020-04-01")) %>%
      group_by(calendar_month,commissioner_name, financial_year) %>%
      summarise(monthly_UDA_UOAs_delivered = sum(monthly_UDA_UOAs_delivered)) %>%
      group_by(commissioner_name, financial_year) %>%
      dplyr::mutate(YTD_delivery = cumsum(monthly_UDA_UOAs_delivered)) %>%
      select(calendar_month,YTD_delivery)
   
  }else{

#calculate cumulative sum of YTD delivery by financial_year
    data <- data %>%
      arrange(calendar_month) %>%
      group_by(financial_year) %>% 
      dplyr::mutate(YTD_delivery = cumsum(monthly_UDA_UOAs_delivered)) %>%
      select(calendar_month,YTD_delivery)
  }
}
  
```

```{r monthly UDA delivered}
plot_monthly_uda_delivered <- function(data = UDA_calendar_data, 
                                       level = "National", 
                                       region_STP_name = NULL){
  # filter for STP or region - select the STP or region you want
  if(level == "Regional"){
    data <- data %>% 
      filter(region_name == region_STP_name)
    subtitle <- region_STP_name
    } else if(level == "STP"){
      data <- data %>% 
        filter(commissioner_name == region_STP_name)
      subtitle <- region_STP_name
      } else {
        subtitle <- "England"
        }
  
  # calculate UDA delivered by month
  data <- data %>% 
    filter(month >= previous_fy_start & month <= current_fy_end) %>% 
    group_by(month) %>% 
    summarise(uda_delivered = sum(UDA_delivered, na.rm = TRUE)) %>% 
    mutate(financial_year = ifelse(month <= previous_fy_end, previous_fy, current_fy), 
           month_name = format(month, "%B"))

  # set month names to the right order
  data$month_name <- factor(data$month_name, 
                            levels = c("April", "May", "June", "July", "August", "September", "October",
                                       "November", "December", "January", "February", "March"))
  
  # create data labels for latest complete month (two months prior to most recent month)
  latest_complete_month <- format(latest_final_month, "%B")
  
  data_labels <- data %>% 
    filter(month_name == latest_complete_month)

  # plot chart
  ggplot(data, aes(x = month_name, y = uda_delivered, fill = financial_year)) + 
    geom_bar(position = "dodge", stat = "identity") +  
    geom_text(aes(label = prettyNum(uda_delivered, big.mark=",")), 
              data = data_labels,
              colour = "black",
              vjust = -0.5, 
              position = position_dodge(.9), 
              size = 7/.pt) + 
    theme_bw() +
    theme(legend.position = "bottom") + 
    labs(title = "Monthly UDAs delivered", 
         subtitle = subtitle, 
         x = "Month", 
         y = "UDAs delivered", 
         caption = "Contracts included: GDS/PDS/PDS+ where Total contracted UDA>100.
The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher.") + 
    scale_fill_manual(values = get_colour_palette(), name = "") + 
    scale_y_continuous(labels = function(x) format(x, big.mark = ",", scientific = FALSE))
}
```

```{r banded_CoT}
plot_banded_CoT <- function(data = UDA_calendar_data_FD, 
                            level = "National", 
                            region_STP_name = NULL, 
                            plotChart = TRUE, 
                            all_regions_and_STPs = FALSE, 
                            asIndex = FALSE){
  
  #avoid standard form on axes
  options(scipen = 100)
  
  #toggle subtitle
  if(level == "Regional"){
    
    #filter for region or STP
    data <- data %>% 
      filter(region_name == region_STP_name)
    subtitle <- region_STP_name 
    
  }else if(level == "STP"){
    
    #filter for region or STP
    data <- data %>% 
      filter(commissioner_name == region_STP_name)
    subtitle <- region_STP_name
    
  }else{
    subtitle <- "England"
    data <- data
  }
  
  #get data into the right format
  data_to_print <- get_banded_COTs_data(data, all_regions_and_STPs = all_regions_and_STPs)
  data <- data_to_print %>% 
    pivot_longer(cols = !month, names_to = "variable", values_to = "value")
  
  data <- data %>%
    mutate(month = as.Date(month)) %>%
    rename(band = variable, CoTs = value)  %>% 
    filter(band != "region_name") %>% 
    mutate(band = replace(band, band == "band1", "Band 1"),
           band = replace(band, band == "band2", "Band 2"),
           band = replace(band, band == "band3", "Band 3"),
           band = replace(band, band == "other", "Other"),
           band = replace(band, band == "urgent", "Urgent"),
           band = replace(band, band == "band2a", "Band 2a"),
           band = replace(band, band == "band2b", "Band 2b"),
           band = replace(band, band == "band2c", "Band 2c"))
  
  
  if(asIndex == TRUE){
    
    #set everything as index of Feb 2020 and standardise for working days
    # remove band 2 split
    data <- data %>%
      filter(band %in% c("Band 1", "Band 2", "Band 3", "Other", "Urgent")) %>% 
      left_join(working_days,by=c('month')) %>% 
      mutate(standardised_CoTs = CoTs *(`no workdays`/`total workdays`), 
             month_number = substr(month, 6, 7), 
             year_number = substr(month, 1, 4))
    
    dataFeb2020 <- data %>% 
      filter(year_number == "2020" & month_number == "02") %>% 
      select(month_number, band, 
             CoTs_feb2020 = CoTs)
    
    data <- data %>% 
      left_join(dataFeb2020, by = c("band")) %>% 
      mutate(CoTs = CoTs * 100 / CoTs_feb2020)
    
    title <- "Banded Courses of Treatment as a Percentage of February 2020 Delivery Including Foundation Dentists"
    ylab <- "Percentage of February 2020 completed CoT"
    caption <- "Contracts included: GDS/PDS/PDS+ where Total contracted UDA>100.
The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher.
This chart includes foundation dentists so total counts will differ to other charts."
    
  }else{ # or standardise for working days only
    # remove band 2 total after split introduced
  data <- data %>%
    pivot_wider(names_from = band, values_from = CoTs) %>% 
    mutate(`Band 2` = ifelse(month > "2022-10-01", NA, `Band 2`),
           `Band 2a` = ifelse(month <= "2022-10-01", NA, `Band 2a`),
           `Band 2b` = ifelse(month <= "2022-10-01", NA, `Band 2b`),
           `Band 2c` = ifelse(month <= "2022-10-01", NA, `Band 2c`)) %>% 
    pivot_longer(cols = !month, names_to = "band", values_to = "CoTs") %>% 
      left_join(working_days,by=c('month')) %>% 
      mutate(standardised_CoTs = CoTs *(`no workdays`/`total workdays`)) %>%
      select(-CoTs) %>% 
      rename(CoTs = standardised_CoTs)
      
      title <- "Banded Courses of Treatment Including Foundation Dentists"
      ylab <- "Number of completed Courses of Treatment"

      caption <- "Contracts included: GDS/PDS/PDS+ where Total contracted UDA>100.
The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher.
This chart includes foundation dentists so total counts will differ to other charts."
    }

  
  data_labels <- data %>%
    filter(month == latest_final_month) %>% 
    mutate(CoTs = round(CoTs))
  
  if(plotChart == TRUE){
    
    #plot code
    ggplot(data, aes(x = month, 
                     y = CoTs, 
                     colour = band)) +
      theme_bw() +
      theme(axis.text.x = element_text(angle = 90)) +
      geom_line(data = subset(data, month <= latest_final_month), linetype = 1,
                size = 1) +
      geom_line(data = subset(data, month >= latest_final_month), linetype = 2,
                size = 1) +
      geom_point() +
      geom_text_repel(aes(label = prettyNum(CoTs, big.mark=",")), 
                      data = data_labels, 
                      colour = "black", 
                      position = "dodge") +
      scale_x_date(breaks = function(x) seq.Date(from = min(data$month), to = max(data$month), by = "1 month"), 
                   date_labels = "%b-%y") +
      scale_colour_manual(#labels = c("Band 1", "Band 2", "Band 2a", "Band 2b", "Band 2c", "Band 3", "Other", "Urgent"),
                          values = get_colour_palette()) +
      scale_y_continuous(breaks = scales::breaks_pretty(),
                         labels = function(x) format(x, big.mark = ",", scientific = FALSE)) +
      labs(title = title,
           x = "Month",
           y = ylab,
           colour = "Band",

           subtitle = subtitle,
           caption = caption)

  }else{
    
  data <- data_to_print %>%
    mutate(month = format(as.Date(month), "%Y-%m"))
  }
}
```

```{r table_banded_CoT}
# used in export data file
table_banded_CoT <- function(data = UDA_calendar_data_FD, 
                             level = "National", 
                             all_regions_and_STPs = FALSE, 
                             asIndex = FALSE, 
                             standardised = TRUE){
  
  #get data into the right format for banded_CoT
  data_to_print <- get_banded_COTs_data(data, level = level, all_regions_and_STPs = all_regions_and_STPs)
  
  if(level == "Regional"){
    # filter for region or STP
    #calculate in each band
    data_to_print <- reshape2::melt(data_to_print, id.vars = c("month", "region_name"))
    
    data <- data_to_print %>%
          filter(variable != "commissioner_name") %>% 
          left_join(working_days, by = c("month")) %>%
          mutate(month = format(as.Date(month), "%Y-%m"),
                 CoTs = as.numeric(value),
                 standardised_CoTs = CoTs*(`no workdays`/`total workdays`)) %>% 
          group_by(month, region_name, variable) %>% 
          summarise(standardised_CoTs = sum(standardised_CoTs, na.rm = TRUE), 
                    CoTs = sum(CoTs, na.rm = TRUE))

    }else if(level == "STP"){
      
      #filter for region or STP
      #calculate in each band
      data_to_print <- reshape2::melt(data_to_print, id.vars = c("month", "commissioner_name"))
      
      data <- data_to_print %>%
          filter(variable != "region_name") %>% 
          left_join(working_days, by = c("month")) %>%
          mutate(month = format(as.Date(month), "%Y-%m"),
                 CoTs = as.numeric(value),
                 standardised_CoTs = CoTs*(`no workdays`/`total workdays`)) %>% 
          group_by(month, commissioner_name, variable) %>% 
          summarise(standardised_CoTs = sum(standardised_CoTs, na.rm = TRUE), 
                    CoTs = sum(CoTs, na.rm = TRUE))
      
      }else{
        #at national level
        #calculate in each band
        data_to_print <- reshape2::melt(data_to_print, id.vars = c("month"))
        
        data <- data_to_print %>%
          left_join(working_days, by = c("month")) %>%
          mutate(month = format(as.Date(month), "%Y-%m"),
                 CoTs = as.numeric(value),
                 standardised_CoTs = CoTs*(`no workdays`/`total workdays`)) %>% 
          group_by(month, variable) %>% 
          summarise(standardised_CoTs = sum(standardised_CoTs, na.rm = TRUE), 
                    CoTs = sum(CoTs, na.rm = TRUE))
      }
  
  # select either standardised or unstandardised CoTs
  if(standardised == TRUE){
    data <- data %>% 
      select(-CoTs) %>% 
      pivot_wider(names_from = variable, values_from = standardised_CoTs)
  } else{
    data <- data %>% 
      select(-standardised_CoTs) %>% 
      pivot_wider(names_from = variable, values_from = CoTs)
  }
}
```

```{r 111 dental}

plot_111_referrals <- function(data = dental_data_111,
                               plotChart = TRUE){
  
  #calculate case volume for each month
  data <- data %>% 
    mutate(month = as.Date(month)) %>%
    group_by(month) %>%
    summarise(monthly_case_volume = sum(monthly_case_volume))
  
  data_labels <- data %>% 
    filter(month == max(month))
  
  #plot code
  p <- ggplot(data, 
              aes(x = month, 
                  y = monthly_case_volume)) +
    theme_bw() +
    theme(axis.text.x = element_text(angle = 90)) +
    geom_line(colour = "steelblue", 
              size = 1) +
    geom_point(colour = "steelblue") +
    geom_text_repel(aes(label = prettyNum(monthly_case_volume, big.mark = ",")), 
                    data = data_labels, 
                    colour = "black", 
                    position = "dodge") +
    scale_x_date(breaks = function(x) seq.Date(from = min(data$month), to = max(data$month), by = "1 month"), 
                 date_labels = "%b-%y") +
    scale_y_continuous(breaks = seq(0, 
                                    max(data$monthly_case_volume, na.rm = T) + 10000,
                                    10000),
                       limits = c(0, max(data$monthly_case_volume))) +
    labs(title = "111 call volume recommended towards dental services", 
         x = "Month",
         y = "Call volume", 
         subtitle = "England",
         caption = "* This data is 1 month behind other data in this pack."
    )
  
  if(plotChart == TRUE){
    
    p
    
  }else{
    
    data
    
  }
}

```

```{r unique patients rolling}

plot_unique_patients_rolling <- function(data = unique_patients_rolling, 
                                         level = "National", 
                                         region_STP_name = NULL, 
                                         plotChart = TRUE, 
                                         all_regions_and_STPs = FALSE, 
                                         asIndex = FALSE){
  
  #get data in the right format for unique_patients
  data <- pull_unique_patients()
  
  data$commissioner_name <- str_to_title(data$commissioner_name)
  data$commissioner_name <- gsub("Icb", "ICB", data$commissioner_name)
  
  options(scipen = 5)

chartCaption <- "Contracts included: GDS/PDS/PDS+
Includes all patients seen
The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher.
Unique patients are based on ICB of patient home postcode, each patient is counted only once and allocated to an ICB as identified by their most recent FP17 patient postcode."

#set subtitles and select columns to use for ICB or national
if(level == "STP"){
  subtitle <- region_STP_name
  
  # select ICB unique patient numbers
    new_data <- data %>% 
      filter(commissioner_name == region_STP_name) %>% 
      select(month, all_12m_count, child_12m_count, adult_24m_count) %>% 
      mutate(all_12m_count = as.numeric(all_12m_count), 
             child_12m_count = as.numeric(child_12m_count), 
             adult_24m_count = as.numeric(adult_24m_count))
}else{
  subtitle <- "England"
  
  # select unique values by month for national metrics
    new_data <- data %>% 
      select(month, all_12m_count_ctry, child_12m_count_ctry, adult_24m_count_ctry) %>%
      distinct() %>% 
      mutate(all_12m_count = as.numeric(all_12m_count_ctry), 
             child_12m_count = as.numeric(child_12m_count_ctry), 
             adult_24m_count = as.numeric(adult_24m_count_ctry)) %>% 
      select(-c(all_12m_count_ctry, child_12m_count_ctry, adult_24m_count_ctry))
}

if(plotChart == TRUE & asIndex == FALSE){
  #rename -- pivot to get the right format
  data <- new_data %>% 
  rename(`All (12 month rolling period)` = all_12m_count, 
         `Children (12 month rolling period)` = child_12m_count, 
         `Adults (24 month rolling period)` = adult_24m_count) %>% 
  pivot_longer(!month) %>% 
  rename(total_unique_patients = value)
  
  title <- "Total number of unique patients seen over a rolling 12 or 24 month period"
  ylab <- "Unique patients"
  
  data_labels <- data %>% 
    filter(month == latest_final_month)
  
  data$name <- factor(data$name, 
                            levels = c("Adults (24 month rolling period)", 
                                       "Children (12 month rolling period)", 
                                       "All (12 month rolling period)"))
  
  #plot code
  ggplot(data, aes(x = month, 
                   y = total_unique_patients, 
                   colour = name)) +
    theme_bw() +
    geom_line(data = subset(data, month <= latest_final_month), linetype = 1) +
    geom_line(data = subset(data, month >= latest_final_month), linetype = 2) +
    geom_point() +
    geom_text_repel(aes(label = prettyNum(total_unique_patients, big.mark=",")), 
                    data = data_labels, 
                    colour = "black", 
                    position = "dodge") + 
    scale_y_continuous(labels=function(x) format(x, big.mark = ",", scientific = FALSE)) +
    scale_colour_manual(values = get_colour_palette(), 
                        name = "") +
    labs(title = title,
         x = "12 or 24 month rolling period end date",
         y = ylab,
         subtitle = subtitle,
         caption = chartCaption
    ) +
    scale_x_date(breaks = function(x) seq.Date(from = min(data$month), to = max(data$month), by = "1 month"), 
                 date_labels = "%b-%y") +
    theme(axis.text.x = element_text(angle = 90))
  
} else if(plotChart == TRUE & asIndex == TRUE){
  
  #rename
  data <- new_data %>% 
  rename(`All (12 month rolling period)` = all_12m_count, 
         `Children (12 month rolling period)` = child_12m_count, 
         `Adults (24 month rolling period)` = adult_24m_count)
  
  #reshape to the right format
  data <- reshape2::melt(data, id.vars = "month")


  #Feb 2020 has been used as an reference
  dataFeb2020 <- data %>% 
      filter(month == "2020-02-01") %>% 
      rename(value_Feb2020 = value)

  
    data <- data %>% 
      left_join(dataFeb2020, by = c("variable")) %>% 
      mutate(value = value * 100 / value_Feb2020) %>% 
      select(-month.y) %>% 
      rename(month = month.x)

    title <- "Total number of unique patients seen over a rolling 12 or 24 month period as a percentage of February 2020 figures"
    ylab <- "Percentage of February 2020 unique patients"
    
    data_labels <- data %>% 
      filter(month == latest_final_month)
  
    data$variable <- factor(data$variable, 
                            levels = c("Adults (24 month rolling period)", 
                                       "Children (12 month rolling period)", 
                                       "All (12 month rolling period)"))
    
    #plot code
    ggplot(data, 
           aes(x = month,
               y = value,
               colour = variable)) +
      theme_bw() +
      geom_line(data = subset(data, month <= latest_final_month), linetype = 1) +
      geom_line(data = subset(data, month >= latest_final_month), linetype = 2) +
      geom_point() +
    geom_text_repel(aes(label = round(value)), 
                    data = data_labels, 
                    colour = "black", 
                    position = "dodge") + 
      scale_y_continuous(labels=function(x) format(x, big.mark = ",", scientific = FALSE)) +
      scale_colour_manual(values = get_colour_palette(), 
                          name = "") +
      labs(title = title,
           x = "12 or 24 month rolling period end date",
           y = ylab,
           subtitle = subtitle,
           caption = chartCaption) +
      scale_x_date(breaks = function(x) seq.Date(from = min(data$month), to = max(data$month), by = "1 month"), 
                   date_labels = "%b-%y") +
      theme(axis.text.x = element_text(angle = 90))
  }
}
```

```{r npp activity}
plot_npp_activity <- function(data = npp_data, 
                              level = "National", 
                              region_STP_name = NULL, 
                              by_band = FALSE){
  # group and filter based on level
  if(level == "Regional"){
    data <- data %>% 
      filter(region_name == region_STP_name) %>% 
      group_by(month, region_name)
    subtitle <- region_STP_name
    
  } else if (level == "STP"){
    data <- data %>% 
      filter(commissioner_name == region_STP_name) %>%
      group_by(month, commissioner_name)
    
    subtitle <- region_STP_name
    
  } else {
    data <- data %>%
      group_by(month)
    
    subtitle <- "England"
  }
  
  if (by_band == FALSE){
  # calculate number of patients seen
  # filter for new_patient_tariff_amount > 0 to only count contracts seeing patients under NPP
  data <- data %>% 
    filter(month >= "2024-03-01" 
           & new_patient_tariff_amount > 0) %>% 
    summarise(adult_total = sum(adult_count), 
              child_total = sum(child_count), 
              total = sum(total)) %>% 
    pivot_longer(cols = adult_total:total, 
                 names_to = "variable", 
                 values_to = "values")
  
  # create data labels for latest month
  data_labels <- data %>% 
    filter(month == latest_final_month)
  
  chartCaption <- "Contracts included: GDS/PDS/PDS+
  Includes all patients seen.
  The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher."
  
  # plot chart
  ggplot(data, aes(x = month,
                   y = values, 
                   colour = variable)) + 
    geom_line(data = subset(data, month <= latest_final_month), linetype = 1,
              linewidth = 1) + 
    geom_line(data = subset(data, month >= latest_final_month), linetype = 2,
              linewidth = 1) + 
    geom_point() +
    geom_text_repel(aes(label = prettyNum(values, big.mark=",")), 
                    data = data_labels, 
                    colour = "black", 
                    position = "dodge") +
    theme_bw() + 
    scale_x_date(date_breaks = "1 month",
                 date_labels = "%b-%y") +
    scale_colour_manual(values = get_colour_palette(), 
                        name = "", 
                        labels = c("Adults", "Children", "Total")) + 
    scale_y_continuous(labels = function(x) format(x, big.mark = ",", scientific = FALSE)) + 
    labs(title = "Number of eligible patients seen according to the New Patient Premium definition", 
         subtitle = subtitle, 
         y = "Eligible patients", 
         x = "Month", 
         caption = chartCaption) + 
    theme(legend.position = "bottom")
  }
  
  else if (by_band == TRUE){
    # calculate number of patients seen by band
  # filter for new_patient_tariff_amount > 0 to only count contracts seeing patients under NPP
  data <- data %>% 
    filter(month >= "2024-03-01" 
           & new_patient_tariff_amount > 0) %>% 
    summarise(`Adults Band 1` = sum(band1_adult_count), 
              `Adults Bands 2&3` = sum(band23_adult_count),
              `Children Band 1` = sum(band1_child_count), 
              `Children Bands 2&3` = sum(band23_child_count)) %>% 
    pivot_longer(cols = `Adults Band 1`:`Children Bands 2&3`, 
                 names_to = "variable", 
                 values_to = "values")
  
  # create data labels for latest month
  data_labels <- data %>% 
    filter(month == latest_final_month)
  
  chartCaption <- "Contracts included: GDS/PDS/PDS+
  Includes all patients seen.
  The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher."
  
  ggplot(data, aes(x = month,
                   y = values, 
                   colour = variable)) + 
    geom_line(data = subset(data, month <= latest_final_month), linetype = 1,
              linewidth = 1) + 
    geom_line(data = subset(data, month >= latest_final_month), linetype = 2,
              linewidth = 1) + 
    geom_point() +
    geom_text_repel(aes(label = prettyNum(values, big.mark=",")), 
                    data = data_labels, 
                    colour = "black", 
                    position = "dodge") +
    theme_bw() + 
    scale_x_date(date_breaks = "1 month",
                 date_labels = "%b-%y") +
    scale_colour_manual(values = get_colour_palette(), 
                        name = "") + 
    scale_y_continuous(labels = function(x) format(x, big.mark = ",", scientific = FALSE)) + 
    labs(title = "Number of eligible patients seen according to the New Patient Premium definition by band", 
         subtitle = subtitle, 
         y = "Eligible patients", 
         x = "Month", 
         caption = chartCaption) + 
    theme(legend.position = "bottom")
  }
}
```

```{r npp contracts plot}
plot_npp_contracts <- function(data = npp_data, 
                               level = "National", 
                               region_STP_name = NULL){
  # check if contract saw new patient or not
  data <- data %>% 
    mutate(saw_new_patient = ifelse(new_patient_tariff_amount == 0, "no", "yes")) %>% 
    filter(month >= "2024-03-01")
  
  # group and filter based on level
  if(level == "Regional"){
    data <- data %>% 
      filter(region_name == region_STP_name) %>% 
      group_by(month, saw_new_patient, region_name)
  
    subtitle <- region_STP_name

  } else if (level == "STP"){
    data <- data %>% 
      filter(commissioner_name == region_STP_name) %>% 
      group_by(month, saw_new_patient, commissioner_name)
    
    subtitle <- region_STP_name

  } else {
    data <- data %>% 
      group_by(month, saw_new_patient)
  
    subtitle <- "England"
  }
  
  # calculate number of contracts eligible and number which saw new patients
  data <- data %>% 
    summarise(n_contracts = as.numeric(n_distinct(contract_number))) %>% 
    pivot_wider(names_from = saw_new_patient, values_from = n_contracts) %>% 
    mutate(yes = ifelse(is.na(yes), 0, yes), 
           no = ifelse(is.na(no), 0, no), 
           eligible = no + yes) %>% 
    select(-no) %>% 
    pivot_longer(cols = yes:eligible, 
                 names_to = "variable", 
                 values_to = "values")

  # create data labels for the latest month
  data_labels <- data %>% 
    filter(month == latest_final_month)
  
  # plot chart
  ggplot(data, aes(x = month, 
                   y = values, 
                   fill = variable)) + 
    geom_bar(stat = "identity", position = "dodge") + 
    geom_text(aes(label = prettyNum(values, big.mark=",")), 
              data = data_labels,
              colour = "black",
              vjust = -0.5, 
              position = position_dodge(width = 25)) +
    theme_bw() + 
    scale_x_date(date_breaks = "1 month",
                 date_labels = "%b-%y") +
    scale_fill_manual(values = get_colour_palette(), 
                      name = "", 
                      labels = c("Eligible", "Saw new patients")) + 
    scale_y_continuous(labels = function(x) format(x, big.mark = ",", scientific = FALSE)) + 
    labs(title = "Number of eligible contracts that have seen a new patient under the New Patient Premium definition", 
         subtitle = subtitle, 
         y = "Number of contracts", 
         x = "Month", 
         caption = "The most recent two months data will be incomplete and subject to change, 
         particularly for the most recent month. 
         We expect final data to be higher.") + 
    theme(legend.position = "bottom")
}
```

```{r npp contracts table}
table_npp_contracts <- function(data = npp_data, 
                                level = "National", 
                                region_STP_name = NULL){
  # check if contract saw new patient or not
  data <- data %>% 
    mutate(saw_new_patient = ifelse(new_patient_tariff_amount == 0, "no", "yes")) %>% 
    filter(month >= "2024-03-01")
  
  # group and filter based on level
  if(level == "Regional"){
    data <- data %>% 
      filter(region_name == region_STP_name) %>% 
      group_by(month, saw_new_patient, region_name)
  
    subtitle <- region_STP_name

  } else if (level == "STP"){
    data <- data %>% 
      filter(commissioner_name == region_STP_name) %>% 
      group_by(month, saw_new_patient, commissioner_name)
    
    subtitle <- region_STP_name

  } else {
    data <- data %>% 
      group_by(month, saw_new_patient)
  
    subtitle <- "England"
  }
  
  # calculate percentage of eligible contracts which saw a new patient
  data <- data %>% 
    summarise(n_contracts = as.numeric(n_distinct(contract_number))) %>% 
    pivot_wider(names_from = saw_new_patient, values_from = n_contracts) %>% 
    mutate(yes = ifelse(is.na(yes), 0, yes), 
           no = ifelse(is.na(no), 0, no), 
           eligible = no + yes) %>% 
    select(-no) %>% 
    mutate(percent = yes/eligible, 
           percent = paste0(round(percent*100), "%", sep = "")) %>% 
    select(`Month` = month, 
           `Percentage of \n eligible contracts` = percent)
  
  # convert into grob object to use in slides
  table_to_print <- tableGrob(data, rows = NULL)
}
```

```{r npp-pre/post-intro-plot}
plot_npp_activity_monitoring <- function(level = "National", 
                                         region_STP_name = NULL){
  
  monthly_delivered <- get_npp_data(level = level, 
                                    region_STP_name = region_STP_name)
  
  plt <- ggplot(monthly_delivered, 
                aes(x = financial_year,
                    y = total, 
                    fill = GROUP)) + 
    geom_bar(stat = "identity", position = "stack") + 
    facet_wrap(~month_name, ncol=12, strip.position = "bottom") + 
    xlab("Month") + 
    ylab("Completed courses of treatment") + 
    scale_y_continuous(labels = scales::comma) + 
    scale_fill_manual(values = c("#003087", "#E5C9D9", "#AE2573"),
                      labels = c("Pre NPP launch (2023/24)",
                                 "Post NPP launch - unpaid (2024/25)",
                                 "Post NPP launch - paid (2024/25)")) + 
    theme(panel.border = element_blank(),
          panel.background = element_blank(), 
          strip.background = element_blank(),
          axis.text.x = element_blank(),
          axis.ticks.x = element_blank(),
          legend.position = "bottom",
          legend.title = element_blank())
  
  plt
}
```

```{r npp-comparison-table}
table_npp_monitoring <- function(level = "National", 
                                 region_STP_name = NULL){
  
  monthly_delivered_post <- npp_data %>% 
    filter(month >= "2024-03-01") %>% 
    group_by(month, commissioner_code, commissioner_name, region_name) %>% 
    summarise(TotalNPP = sum(band1_adult_count, band1_child_count, band23_adult_count, band23_child_count, na.rm = TRUE)) %>% 
    rename(YEAR_MONTH = month, 
           COMMISSIONER_CODE = commissioner_code,
           COMMISSIONER_NAME = commissioner_name,
           Region_Name = region_name)
  
  comparison <- comparison %>% 
    left_join(monthly_delivered_post, by = c("Region_Name", "COMMISSIONER_CODE", "COMMISSIONER_NAME", "YEAR_MONTH"))
  
  if(level == "National"){
    
    table_data <- comparison
    
  } else if(level == "Regional"){
    
    table_data <- comparison %>% 
      filter(Region_Name == region_STP_name)
    
  } else if(level == "STP"){
    
    table_data <- comparison %>% 
      filter(COMMISSIONER_NAME == region_STP_name)
  }
  
  table_data <- table_data %>% 
      group_by(YEAR_MONTH) %>% 
      summarise(total_ncdr = sum(TotalNPP, na.rm = TRUE), 
                total_eden = sum(total_eden, na.rm = TRUE)) %>% 
      mutate(pct_diff = (total_ncdr - total_eden)/total_eden, 
             total_ncdr = prettyNum(round(total_ncdr, 0), big.mark = ","), 
             total_eden = prettyNum(round(total_eden, 0), big.mark = ","), 
             pct_diff = paste0(round(pct_diff*100), "%", sep = "")) %>% 
    rename(Month = YEAR_MONTH, 
           COMPASS = total_ncdr,
           eDEN = total_eden, 
           `Percentage difference` = pct_diff)
  
  table_to_print <- tableGrob(table_data, rows = NULL)
}
```

```{r DCP1}
plot_DCP_analysis <- function(data = UDA_calendar_data,
                              dcp_data = DCP_data,
                              UDA_or_FP17 = "FP17",
                              level = "National",
                              region_STP_name = NULL){
  
  dcp_data <- dcp_data %>%
    rename(month = Month)
  
  #filter for region or STP
  if(level == "Regional"){
    
    data <- data %>% 
      filter(region_name == region_STP_name )
    
    dcp_data <- dcp_data %>%
      filter(Region == region_STP_name)
    
    subtitle <- region_STP_name 
    
  }else if(level == "STP"){
    
    data <- data %>% 
      filter(commissioner_name == region_STP_name)
    
    dcp_data <- dcp_data %>%
      filter(commissioner_name == region_STP_name)
    
    subtitle <- region_STP_name
    
  }else{
    subtitle <- "England"
  }
  
  #calculate total FP17, UDA_B1, B2, B3 and urgent by month for "Total_dentist_only_and_DCP_assisted' by using UDA Calendar data
  delivery_total <-  data %>% 
    group_by(month) %>%
    dplyr::summarise( total_FP17 = sum(general_FP17s, na.rm = TRUE),
                      total_B1 = sum(UDA_band_1, na.rm = TRUE),
                      total_B2 = sum(UDA_band_2, na.rm = TRUE),
                      total_B3 = sum(UDA_band_3, na.rm = TRUE),
                      total_urgent = sum(UDA_urgent, na.rm = TRUE)) %>%
    mutate(DCP_description = "Total_dentist_only_and_DCP_assisted") %>%
    select(month, DCP_description, total_FP17,total_B1, total_B2, total_B3, total_urgent) %>% 
    rename(`Band 1` = total_B1, `Band 2` = total_B2, `Band 3` = total_B3, Urgent = total_urgent)
  
  #calculate total FP17, UDA_B1, B2, B3 and urgent by month for separate DCP description by using DCP data
  dcp_main_new <- dcp_data %>% 
    filter(DCP_description != 'Clinical Technician') %>%
    filter(DCP_description != 'Technician') %>%
    filter(DCP_DIR_DESC != "Technician") %>% 
    rename(assisted = DCP_description, led = DCP_DIR_DESC)
  
  dcp_summary <- dcp_main_new %>%
  group_by(month, assisted, led) %>%
  summarise (total_FP17 = sum(FP17_Current_Year_total, na.rm = TRUE),
             total_B1 = sum(Band_1._UDA, na.rm = TRUE),
             total_B2 = sum(Band_2._UDA, na.rm = TRUE),
             total_B3 = sum(Band_3._UDA, na.rm = TRUE),
             total_urgent = sum(Urgent_UDA, na.rm = TRUE)) %>% 
  pivot_longer(cols = assisted:led, names_to = "role", values_to = "DCP_description") %>% 
  filter(!DCP_description == "") %>% 
  group_by(month, role, DCP_description) %>% 
  summarise(total_FP17 = sum(total_FP17, na.rm = TRUE),
            total_B1 = sum(total_B1, na.rm = TRUE),
            total_B2 = sum(total_B2, na.rm = TRUE),
            total_B3 = sum(total_B3, na.rm = TRUE),
            total_urgent = sum(total_urgent, na.rm = TRUE)) %>% 
  rename(`Band 1` = total_B1, `Band 2` = total_B2, `Band 3` = total_B3, Urgent = total_urgent)
  
  # exclude DCP led before April 2024 (direct access not introduced before then)
  dcp_summary <- dcp_summary %>% 
    filter(!(month <= "2024-03-01" & role == "led"))

#pivot delivery total and seperate dcp into long format
  dcp_summary_longer <- dcp_summary %>% 
    pivot_longer(cols = total_FP17:Urgent,
                 names_to = "Bands",
                 names_prefix = "dcp",
                 values_to = "numbers",
                 values_drop_na = TRUE) 
  
  delivery_total_longer <- delivery_total %>% 
    pivot_longer(cols = total_FP17:Urgent,
                 names_to = "Bands",
                 names_prefix = "dcp",
                 values_to = "all_numbers",
                 values_drop_na = TRUE)
  
  #join total and separate dcp to get the full data ready for plot
  all_lookup <- left_join(dcp_summary_longer, delivery_total_longer, 
                          by = c("month", "Bands"))
  
  #calculate the percentage of each dcp description/total delivery 
  total <- all_lookup %>% 
    mutate(dcp_percent = formattable::percent (numbers / all_numbers, digits=2))
  
  if(UDA_or_FP17 == "UDA"){
    
    #UDA is only for those with 'band'
    filtered_data_UDA = filter(total, Bands %in% c("Band 1",	"Band 2",	"Band 3", "Urgent")) %>%
      select(month, "DCP_description.x", "Bands", "assisted_percent") %>%
      rename (DCP_description = DCP_description.x) %>%
      mutate(month = as.Date(month)) %>%
      mutate(DCP_description = str_replace_all(DCP_description, "_", " "))
    
    data_labels <- filtered_data_UDA %>% 
      filter(month == latest_final_month)

    UDA_plot <- ggplot(filtered_data_UDA,
                       aes(x = month, y = assisted_percent)) +
      facet_grid(cols = vars(DCP_description),
                 scales = "free_x") +
      geom_line(data = subset(filtered_data_UDA, month <= latest_final_month), linetype = 1,
                aes(colour = Bands), size = 1) +
      geom_line(data = subset(filtered_data_UDA, month >= latest_final_month), linetype = 2,
                aes(colour = Bands), size = 1) +
      geom_point(aes(colour = Bands)) +
      geom_text_repel(aes(label = assisted_percent), 
                      data = data_labels, 
                      colour = "black", 
                      position = "dodge") +
      scale_fill_manual(values = c("#009E73", "#F0E442", "#D55E00"),
                        labels = c("Dental Nurse assisted", "Hygienist", "Therapist")) +
      scale_x_date(date_breaks = "1 month",
                   date_labels = "%b-%y") +
      theme_bw() +
      labs(title = "Percentage of total UDAs delivered which had Dental Care Practitioner (DCP) assistance by DCP roles",
           subtitle = subtitle,
           x = "Month",
           y = "Percentage of total UDAs delivered",
           colour = "Bands",
           caption = "Contracts included: GDS/PDS/PDS+ where Total contracted UDA>100
           The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher.") + 
      scale_y_continuous(labels = scales::percent)+
      theme(axis.text.x = element_text(angle = 90, vjust=-0.0001))

    UDA_plot

  }else{
    
    #UDA is only for those with 'total_FP17'
    filtered_data_FP17 <- total %>%
      filter( Bands %in% c("total_FP17")) %>% 
      select(month, role, "DCP_description.x", Bands, dcp_percent) %>%
      mutate(month = as.Date(month)) %>%
      rename ( Percent_of_FP17 = Bands, DCP_description = DCP_description.x) %>%
      mutate(DCP_description = str_replace_all(DCP_description, "_", " "), 
             DCP_description = replace(DCP_description, DCP_description == "Nurse", "Dental Nurse"), 
             role = ifelse(role == "assisted", "DCP assisted", "DCP led")) 
    
    #get the number for the latest month -- used for label in the plot
    latest_data <- filtered_data_FP17 %>%
      filter(month == latest_final_month)

    FP17_plot <- ggplot(filtered_data_FP17, 
                        aes(x=month, y= dcp_percent)) +
      geom_line(data = subset(filtered_data_FP17, month <= latest_final_month), linetype = 1,
                aes(colour = DCP_description), size = 1) +
      geom_line(data = subset(filtered_data_FP17, month >= latest_final_month), linetype = 2,
                aes(colour = DCP_description), size = 1) +
      geom_point(aes(colour = DCP_description)) +
      geom_text_repel(data = latest_data, aes(label = dcp_percent), 
                    colour = "black", size = 3.5, vjust = -0.25) + 
      facet_wrap(vars(role), 
                 scales = "free_x") +
      theme(legend.position="bottom") +
      theme_bw() +
      scale_x_date(date_breaks = "1 month",
                   date_labels = "%b-%y") +
      labs(title = "Percentage of total Courses of Treatment (CoTs) delivered which had Dental Care Practitioner (DCP) involvement",
           subtitle = subtitle,
           x = "Month",
           y = "Percentage of total CoTs delivered",
           colour = "DCP description",
           caption = "Contracts included: GDS/PDS/PDS+ where Total contracted UDA>100
           The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher.") + 
      scale_y_continuous(labels = scales::percent)  +
      theme(axis.text.x = element_text(angle = 90, vjust=-0.0001))
    
    FP17_plot
    
  }
}
```

```{r DCP plot by band}
plot_DCP_analysis_band <- function(data = UDA_calendar_data,
                                   dcp_data = DCP_data,
                                   UDA_or_FP17 = "UDA",
                                   level = "National",
                                   region_STP_name = NULL){

  dcp_data <- dcp_data %>%
    rename(month = Month)

  #filter for region or STP
  if(level == "Regional"){
    data <- data %>% 
      filter(region_name == region_STP_name )

    dcp_data <- dcp_data %>%
      filter(Region == region_STP_name)

    subtitle <- region_STP_name 
  }else if(level == "STP"){
    data <- data %>% 
      filter(commissioner_name == region_STP_name)

    dcp_data <- dcp_data %>%
      filter(commissioner_name == region_STP_name)

    subtitle <- region_STP_name
  }else{
    subtitle <- "England"
  }
 #calculate total FP17, UDA_B1, B2, B3 and urgent by month for "Total_dentist_only_and_DCP_assisted' by using UDA Calendar data
  delivery_total <-  data %>% 
    group_by(month) %>%
    dplyr::summarise( total_FP17 = sum(general_FP17s, na.rm = TRUE),
                      total_B1 = sum(UDA_band_1, na.rm = TRUE),
                      total_B2 = sum(UDA_band_2, na.rm = TRUE),
                      total_B3 = sum(UDA_band_3, na.rm = TRUE),
                      total_urgent = sum(UDA_urgent, na.rm = TRUE)) %>%
    mutate(DCP_description = "Total_dentist_only_and_DCP_assisted") %>%
    select(month, DCP_description, total_FP17,total_B1, total_B2, total_B3, total_urgent) %>% 
    rename(`Band 1` = total_B1, `Band 2` = total_B2, `Band 3` = total_B3, Urgent = total_urgent)

#calculate total FP17, UDA_B1, B2, B3 and urgent by month for separate DCP description by using DCP data
  dcp_main_new <- dcp_data %>% 
    filter(DCP_description != 'Clinical Technician') %>%
    filter(DCP_description != 'Technician') %>% 
    filter(DCP_DIR_DESC != "Technician") %>% 
    rename(assisted = DCP_description, led = DCP_DIR_DESC)

  dcp_summary <- dcp_main_new %>%
    group_by(month, assisted, led) %>%
    summarise (total_FP17 = sum(FP17_Current_Year_total, na.rm = TRUE),
               total_B1 = sum(Band_1._UDA, na.rm = TRUE),
               total_B2 = sum(Band_2._UDA, na.rm = TRUE),
               total_B3 = sum(Band_3._UDA, na.rm = TRUE),
               total_urgent = sum(Urgent_UDA, na.rm = TRUE)) %>% 
    pivot_longer(cols = assisted:led, names_to = "role", values_to = "DCP_description") %>% 
    filter(!DCP_description == "") %>% 
    group_by(month, role, DCP_description) %>% 
    summarise(total_FP17 = sum(total_FP17, na.rm = TRUE),
              total_B1 = sum(total_B1, na.rm = TRUE),
              total_B2 = sum(total_B2, na.rm = TRUE),
              total_B3 = sum(total_B3, na.rm = TRUE),
              total_urgent = sum(total_urgent, na.rm = TRUE)) %>% 
    rename(`Band 1` = total_B1, `Band 2` = total_B2, `Band 3` = total_B3, Urgent = total_urgent)
  
   # exclude DCP led before April 2024 (direct access not introduced before then)
  dcp_summary <- dcp_summary %>% 
    filter(!(month <= "2024-03-01" & role == "led"))

  #change the format of total delivery and separate dcp and then get full data ready for plotting
  dcp_summary_longer <- dcp_summary %>%
    pivot_longer(cols = total_FP17:Urgent,
                 names_to = "Bands",
                 names_prefix = "dcp",
                 values_to = "numbers",
                 values_drop_na = TRUE) 

  delivery_total_longer <- delivery_total %>% 
    pivot_longer(cols = total_FP17:Urgent,
                 names_to = "Bands",
                 names_prefix = "dcp",
                 values_to = "all_numbers",
                 values_drop_na = TRUE)

  all_lookup <- left_join(dcp_summary_longer, delivery_total_longer, 
                          by = c("month", "Bands"))
                          
  #calculate the percentage of each dcp description/total delivery 
  total <- all_lookup %>% 
    mutate(dcp_percent = formattable::percent (numbers / all_numbers, digits=2),
           Bands = replace(Bands, Bands=="total_B1", "UDA Band 1"),
           Bands = replace(Bands, Bands=="total_B2", "UDA Band 2"),
           Bands = replace(Bands, Bands=="total_B3", "UDA Band 3"),
           Bands = replace(Bands, Bands=="total_urgent", "UDA Urgent"))

  if(UDA_or_FP17 == "UDA"){
   #UDA is only for those with 'band'
    filtered_data_UDA = filter(total, Bands %in% c("Band 1",	"Band 2",	"Band 3", "Urgent")) %>%
      select(month, role, "DCP_description.x", Bands, dcp_percent) %>%
      rename (DCP_description = DCP_description.x) %>%
      mutate(month = as.Date(month)) %>%
      mutate(DCP_description = str_replace_all(DCP_description, "_", " "),
             DCP_description = replace(DCP_description, DCP_description == "Nurse", "Dental Nurse"),
             DCP_description = paste(DCP_description, role, sep = " ")) %>% 
      filter(DCP_description %in% c("Dental Nurse assisted", "Hygienist assisted", "Therapist assisted", "Therapist led"))
    
    data_labels <- filtered_data_UDA %>% 
      filter(month == latest_final_month)

    UDA_plot <- ggplot(filtered_data_UDA,
                   aes(x=month, y= dcp_percent)) +
      facet_grid(cols = vars(DCP_description),
                 scales = "free_x") +
      geom_line(data = subset(filtered_data_UDA, month <= latest_final_month), linetype = 1,
                aes(colour = Bands), size = 1) +
      geom_line(data = subset(filtered_data_UDA, month >= latest_final_month), linetype = 2,
                aes(colour = Bands), size = 1) +
      geom_point(aes(colour = Bands)) +
      geom_text_repel(data = data_labels,
                      aes(label = dcp_percent),
                      colour = "black",
                      position = "dodge") +
      scale_fill_manual(values = c("#009E73", "#F0E442", "#D55E00"),
                        labels = c("Dental Nurse assisted", "Hygienist assisted", "Therapist assisted")) +
      scale_x_date(date_breaks = "1 month",
                   date_labels = "%b-%y") +
      theme_bw() +
      labs(title = "Percentage of total UDAs delivered which had Dental Care Practitioner (DCP) involvement by DCP roles",
           subtitle = subtitle,
           x = "Month",
           y = "Percentage of total UDAs delivered",
           colour = "Bands",
           caption = "Contracts included: GDS/PDS/PDS+ where Total contracted UDA>100.
The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher") + 
      scale_y_continuous(labels = scales::percent)+
      theme(axis.text.x = element_text(angle = 90, vjust=-0.0001))

    UDA_plot

  }else{
#UDA is only for those with 'total_FP17'
    filtered_data_FP17 <- total %>%
      filter( Bands %in% c("total_FP17")) %>% 
      select("month", "DCP_description.x", "Bands", "assisted_percent") %>%
      mutate(month = as.Date(month)) %>%
      rename ( Percent_of_FP17 = Bands, DCP_description = DCP_description.x) %>%
      mutate(DCP_description = str_replace_all(DCP_description, "_", " ")) 

    FP17_plot <- ggplot(filtered_data_FP17,
                        aes(x=month, y= assisted_percent)) +
      geom_line(data = subset(filtered_data_FP17, month <= latest_final_month), linetype = 1,
                aes(colour = DCP_description), size = 1) +
      geom_line(data = subset(filtered_data_FP17, month >= latest_final_month), linetype = 2,
                aes(colour = DCP_description), size = 1) +
      geom_point(aes(colour = DCP_description)) +
      geom_text(aes(label = assisted_percent),
                colour = "black", size= 3.5, vjust=-0.25) +
      theme(legend.position="bottom") +
      theme_bw() +
      scale_x_date(date_breaks = "1 month",
                   date_labels = "%b-%y") +
      labs(title = "Percentage of total Courses of Treatment (CoTs) delivered which had DCP* assistance",
           subtitle = subtitle,
           x = "Month",
           y = "Percentage of total CoTs delivered",
           colour = "DCP description",
           caption = "Contracts included: GDS/PDS/PDS+ where Total contracted UDA>100.
The most recent two months data will be incomplete and subject to change, particularly for the most recent month. We expect final data to be higher") + 
      scale_y_continuous(labels = scales::percent)  +
      theme(axis.text.x = element_text(angle = 90, vjust=-0.0001))

    FP17_plot

  }
}
```

```{r working days table}
table_working_days <- function(){
  # filter for the current and previous financial year#
  workdays <- working_days %>% 
    filter(month >= previous_fy_start & month <= current_fy_end) %>% 
    mutate(fy = ifelse(month >= previous_fy_start & month <= previous_fy_end, previous_fy, current_fy), 
           month_name = format(month, "%B"))
  
  # set order of months
  workdays$month_name = factor(workdays$month_name, 
                               levels = c("April", "May", "June", "July", "August", "September", 
                                          "October", "November", "December", "January", "February", "March"))

  # create table comparing workdays in each month
  workdays <- workdays %>% 
    select(month_name, fy, `no workdays`) %>% 
    pivot_wider(names_from = fy, 
                values_from = `no workdays`) %>% 
    rename(Month = month_name, 
           `2023/24 \n working days` = `2023/24`,
           `2024/25 \n working days` = `2024/25`)
  
  # convert to grob object to use in slides
  table_to_print <- tableGrob(workdays, rows = NULL)
}
```

```{r BPE Plot}

plot_BPE_no_oral_health_risk<- function(data = BPE_data,
                                         level = "National",
                                         region_STP_name = NULL,
                                         plotChart = TRUE,
                                         quartileOutput = "LQ"){

  data <- data %>% 
    filter(!is.na(Highest.BPE.Sextant.Score),
          Year_Month>= "2023-04-01")
  
  data[is.na(data)] <- 0


  #filter for STP or region
  if(level == "Regional"){
    data <- data %>%
      filter(Latest.Region.Description == region_STP_name )
    subtitle <- region_STP_name
  }else if(level == "STP"){
    data <- data %>%
      filter(commissioner_name == region_STP_name)
    subtitle <- region_STP_name
  }else{
    subtitle <- "England"
  }

  ###########################method1 for quantile########################
  # METHOD NOT CURRENTLY IN USE
#CALCULATE THE PERCENTAGE OF forms with highest BPE_Sextant_Score
#  data_org <- data %>%
#    group_by(Year_Month, Contract.Number) %>%
#    summarise(Nforms = sum(Total.Form.Count),
#              NcompYear_Monthedforms = sum(Forms_with_Highest_BPE_Sextant_Score)) %>% 
#    mutate (percentcompYear_Montheform = formattable::percent(NcompYear_Monthedforms/ Nforms, digits =0))


#  data <- data %>% 
#    filter(Total.Form.Count>0) %>%
#    group_by(Year_Month, Contract.Number) %>%
#    summarise (compYear_Monthe_forms = sum(Forms_with_Highest_BPE_Sextant_Score), 
#               nlow_risk = 
#                 sum (as.numeric(Total_Form_Count_Highest_BPE_Sextant_Score_0_or_1_and_0_UDT), na.rm = TRUE),
#               low_risk_less1year = 
#                 sum(as.numeric(Total_Form_Count_Highest_BPE_Sextant_Score_0_or_1_and_UDT_0_and_RRI_less_than_1_year), na.rm = TRUE)) %>%  
#    mutate (percent_low_risk_whic_are1_year = 
#              formattable::percent (low_risk_less1year/nlow_risk, digits =1)) %>% 
#    mutate (percentlowrisk= formattable::percent (nlow_risk/ compYear_Monthe_forms, digits =0) ) %>% 
#    filter(!is.na(percent_low_risk_whic_are1_year)) %>% 
#    filter (percent_low_risk_whic_are1_year<2)%>%
#    mutate(threshold_percent_low_risk_whic_are1_year=ifelse(percent_low_risk_whic_are1_year >= 0.5, 'YES','NO'))

#  p_meds <- data %>% 
#    group_by(Year_Month) %>% 
#    summarise (med = median(percent_low_risk_whic_are1_year))

#  max_month <- max (data$Year_Month)

#  p_meds_latest <- data %>% 
#    group_by (Year_Month) %>% 
#    summarise(med = median(percent_low_risk_whic_are1_year)) %>% 
#    filter(Year_Month ==max_month)

#  p_meds_latest_p <- formattable::percent (p_meds_latest$med, digits = 0)

#  t <- data %>%
#    group_by(Year_Month)%>%
#    summarise(Minimum= quantile(percent_low_risk_whic_are1_year, probs = c(0)) ,
#              First_Quartile= quantile(percent_low_risk_whic_are1_year, probs = c(0.25)) ,
#              Second_Quartile= quantile(percent_low_risk_whic_are1_year, probs = c(0.5)) ,
#              Third_Quartile= quantile(percent_low_risk_whic_are1_year, probs = c(0.75)),
#              Maximum = quantile(percent_low_risk_whic_are1_year, probs = c(1)) )

#  t<-melt(setDT(t), id.vars = c("Year_Month"), variable.name = "Perc_lowR_recall")
#  t$`Perc_lowR_recall`<- factor(t$`Perc_lowR_recall`, levels = c("Minimum", "First_Quartile", "Second_Quartile","Third_Quartile", "Maximum"))
#  t_a<-subset(t,`Perc_lowR_recall` != "Minimum")

# if(plotChart == TRUE){
#    p_test <- 
#      ggplot(data = t_a, aes(x = `Year_Month` , y = `value`, group =`Perc_lowR_recall`)) +
#      geom_line(aes(linetype=`Perc_lowR_recall`))+
#      geom_point(aes(shape = `Perc_lowR_recall`), size =3) + scale_shape_manual(values = c(17, 19,1,8)) +
#      labs(x = "Month",
#           y = "",
#           title = "Percentrage of Low Risk Patients Recalled within a Year by Contractors",
#           subtitle = subtitle
#      ) +
#      scale_y_continuous(labels = scales::percent_format(accuracy = 1)) +
#      scale_x_date(date_labels = "%b-%y") +
#      geom_text(data = subset(t,`Perc_lowR_recall`== "Second_Quartile"), aes(x = Year_Month, y = `value`, label = `value`), 
#                size = 3.5, vjust = -.7) + theme(legend.position = "bottom",legend.title = element_blank ())

#    p_test

#  }else if(!is.null(quartileOutput)){

    #get latest year of data
#    numbers_calc <- data %>%  
#      filter(Year_Month == max_month)

    #output differentquartile based on input
#    if(quartileOutput == "LQ"){
#      quartile <- quantile(numbers_calc$percent_low_risk_whic_are1_year, prob = 0.25)
#    }else if(quartileOutput == "UQ"){
#      quartile <- quantile(numbers_calc$percent_low_risk_whic_are1_year, prob = 0.75)
#    }else{
#      quartile <- quantile(numbers_calc$percent_low_risk_whic_are1_year, prob = 0.5)
#    }

#    quartile
#  }else{

    #return max date
#    max_month
#  }
  
  ######################## method 2 for total#######################################
  # METHOD NOT CURRENTLY IN USE
#  data <- data %>% 
#    filter(Total.Form.Count>0) %>%
#    group_by(Year_Month) %>%
#    summarise (Nforms = sum(Total.Form.Count),
#              NcompYear_Monthedforms = sum(Forms_with_Highest_BPE_Sextant_Score),
#              compYear_Monthe_forms = sum(Forms_with_Highest_BPE_Sextant_Score), 
#               nlow_risk = 
#                 sum (as.numeric(Total_Form_Count_Highest_BPE_Sextant_Score_0_or_1_and_0_UDT), na.rm = TRUE),
#               low_risk_less1year = 
#                 sum(as.numeric(Total_Form_Count_Highest_BPE_Sextant_Score_0_or_1_and_UDT_0_and_RRI_less_than_1_year), na.rm = TRUE)) %>%  
#    mutate (percentcompYear_Montheform = formattable::percent(NcompYear_Monthedforms/ Nforms, digits =0),
#            percent_low_risk_whic_are1_year = 
#              formattable::percent (low_risk_less1year/nlow_risk, digits =1)) %>% 
#    mutate (percentlowrisk= formattable::percent (nlow_risk/ compYear_Monthe_forms, digits =0) ) %>% 
#    filter(!is.na(percent_low_risk_whic_are1_year)) %>% 
#    filter (percent_low_risk_whic_are1_year<2)

#  max_month <- max (data$Year_Month)
 
#    p_test <- 
#      ggplot(data, aes(x = `Year_Month` , y = `percent_low_risk_whic_are1_year`)) +
 #     geom_line()+
 #     geom_point() +
#      labs(x = "Month",
#           y = "",
#           title = "Percentrage of Low Risk Patients Recalled within a Year by Contractors",
#           subtitle = subtitle
#      ) +
#      scale_y_continuous(labels = scales::percent_format(accuracy = 1)) +
#      scale_x_date(date_labels = "%b-%y") +
#      geom_text(aes(x = Year_Month, y = `percent_low_risk_whic_are1_year`, label = `percent_low_risk_whic_are1_year`), 
#            size = 2.5, vjust = -.7) + 
#  theme(legend.position = "bottom", legend.title = element_blank())
#  p_test


########################################Method 3 50%#####################################
  # This method calculates the percentage of contracts bringing back more 50% or more low risk patients within a year
   data <- data %>% 
    filter(Total.Form.Count>0) %>%
    group_by(Year_Month, Contract.Number) %>%
    summarise (nlow_risk = 
                 sum (as.numeric(Total_Form_Count_Highest_BPE_Sextant_Score_0_or_1_and_0_UDT), na.rm = TRUE),
               low_risk_less1year = 
                 sum(as.numeric(Total_Form_Count_Highest_BPE_Sextant_Score_0_or_1_and_UDT_0_and_RRI_less_than_1_year), na.rm = TRUE)) %>%  
    mutate (percent_low_risk_whic_are1_year = (low_risk_less1year/nlow_risk)) %>% 
    filter(!is.na(percent_low_risk_whic_are1_year)) %>% 
    filter (percent_low_risk_whic_are1_year<2)%>%
    mutate(threshold_percent_low_risk_whic_are1_year=ifelse(percent_low_risk_whic_are1_year >= 0.5, 'YES','NO'))
  
 data_total <- data %>% 
    group_by(Year_Month) %>%
    summarise (NContractors = n_distinct(Contract.Number)) 
 
 data_high <- data %>% 
    filter(threshold_percent_low_risk_whic_are1_year=='YES') %>%
    group_by(Year_Month) %>%
    summarise (low_risk_NContractors = n_distinct(Contract.Number)) 
 
 data_all<-data_total%>%
   left_join(data_high, by='Year_Month')%>%
   mutate(pro_50=(low_risk_NContractors/ NContractors) )
 
    p_test <- 
      ggplot(data_all, aes(x = `Year_Month` , y = `pro_50`)) +
      geom_line(data = subset(data_all, Year_Month <= latest_final_month), linetype = 1)+
      geom_line(data = subset(data_all, Year_Month >= latest_final_month), linetype = 2)+
      geom_point() +
      labs(x = "Month",
           y = "",
           title = "Percentage of contracts who recall 50% or more of their low risk patients within a year",
           subtitle = subtitle
      ) +
      scale_y_continuous(labels = scales::percent_format(accuracy = 1), 
                         limits = c(0, NA)) +
      scale_x_date(date_labels = "%b-%y") +
      geom_text(aes(x = Year_Month, y = `pro_50`, label = scales::percent(`pro_50`, accuracy=0.1)), 
            size = 2.5, vjust = -.7) + 
  theme(legend.position = "bottom", legend.title = element_blank())
    p_test
 
}


```

```{r uoa start/complete}
plot_UOA_start_complete <- function(data = UOA_calendar_data,
                                          level = "National",
                                          region_STP_name = NULL){
  
  #filter for STP or region in need
  if(level == "Regional"){
    data <- data %>%
      filter(region_name == region_STP_name )
    
    subtitle <- region_STP_name
    
  }else if(level == "STP"){
    data <- data %>%
      filter(commissioner_name == region_STP_name)
    
    subtitle <- region_STP_name
    
  }else{
    subtitle <- "England"
  }


  #get current FY data ready
  data_try <- data %>%
    group_by(month) %>%
    summarise(UOA_started = sum(ortho_trt_started, na.rm = TRUE),
              UOA_completed = sum(ortho_trt_completed, na.rm = TRUE),
              monthly_UOAs_delivered = sum(UOA_delivered, na.rm = T)) %>%
    filter(month >= previous_fy_start) 
  
# Reshape data into long format for UOA_started and UOA_completed
data_long <- data_try %>%
  pivot_longer(cols = c(UOA_started, UOA_completed), names_to = "Category", values_to = "Value")

# Create a separate data frame for the line plot
data_line <- data_try %>%
  select(month, monthly_UOAs_delivered) %>%
  mutate(Category = "UOAs Delivered", Value = monthly_UOAs_delivered / 20)

# Combine the two data frames for plotting
data_combined <- bind_rows(data_long, data_line)

# Create the plot
p=ggplot(data_combined, aes(x = month, y = Value, fill = Category)) +
  geom_bar(data = data_long, stat = "identity", position = position_dodge(width = 14), width = 14) +
  geom_text(data = data_long, aes(x = month, y = Value, label = Value), 
            position = position_dodge(width = 0.6), vjust =12.5, size = 2) + # Bar plot
  geom_line(data = subset(data_line, month <= current_fy_start), linetype = 1, aes(color = Category), size = 1) +  # Line plot
  geom_line(data = subset(data_line, month >= current_fy_start), linetype = 2, aes(color = Category), size = 1) +  # Line plot
  geom_point(data = data_line, aes(color = Category), size = 1.5) +  # Points on the line
  geom_text(data = data_line, aes(x = month, y = Value, label = Value * 20), 
            vjust = -0.5, size = 2, color = "darkblue") + 
  scale_y_continuous(
    name = "UOA Start/Stop",
    sec.axis = sec_axis(~ . * 20, name = "UOAs Delivered")
  ) +
  scale_x_date(date_labels = "%m/%Y", date_breaks = "1 month") +
  scale_fill_manual(values = c("UOA_started" = "skyblue", "UOA_completed" = "lightgreen")) +
  scale_color_manual(values = c("UOAs Delivered" = "darkblue")) +
  labs(
    title = "UOA Delivery and UOAs Started/Completed",
    subtitle =subtitle, 
    x = "Treatment Month",
    fill = "Legend",
    color = ""
  ) +
  theme_minimal() +
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1),
    legend.position = "bottom" 
  )
 
 p
  
  
}
```