## extract esar cholera platform uga data to taxonomy format
library(pacman)
p_load(here,dplyr,readr,stringr,readxl,tidyr,aweek)
here()


countryls <- c("Zimbabwe", "Somalia", "Kenya", "Tanzania", "Zambia", "Uganda", "Angola", "Mozambique", "Malawi", "Burundi", "Rwanda", "Namibia", "South Sudan", "Madagascar", "Comoros", "Swaziland", "Botswana", "Eritrea", "Lesotho", "South Africa")
library(taxdat)

for(countryname in countryls){

  cntry <- fix_country_name(countryname)
  who_reg <- lookup_WHO_region(countryname)

  ## note that we are temporarily labeling Weeks 1-22 cumulative as W22 in 2017 document
  dat_2017 = read_xlsx(here("2017 Cholera Data Set.xlsx"),range="A4:BG18",
                       col_names = c("country",
                                     paste0("W",rep(sprintf("%.2d",22:49),each=2),"-",
                                            rep(c("C","D"),time=length(22:49))),"Cum-C","Cum-D"))  %>% 
    filter(country==countryname)

  dat_2018 = read_xls(here("2018 Cholera Data set.xls"),range="A3:DC13",
                      col_names = c("country",
                                    paste0("W",rep(sprintf("%.2d",1:52),each=2),"-",
                                           rep(c("C","D"),time=52)),"Cum-C","Cum-D")) %>% 
    filter(country==countryname & !is.na("W01-C")) ## Burundi listed twice

  dat_2019 = read_xls(here("2019 Cholera Data set.xls"),
                      range="A3:AK14",
                      col_names = c("country",
                                     paste0("W",rep(sprintf("%.2d",1:17),each=2),"-",
                                            rep(c("C","D"),time=17)),"Cum-C","Cum-D")) %>% 
    filter(country==countryname)


  cases = bind_rows(
    dat_2017 %>% select(ends_with("-C")) %>% select_all(~gsub("-C","",.)) %>% mutate_all(as.numeric) %>% gather(week,sCh) %>% mutate(week = paste0(2017,"-",week)),
    dat_2018 %>% select(ends_with("-C")) %>% select_all(~gsub("-C","",.)) %>% mutate_all(as.numeric) %>% gather(week,sCh) %>% mutate(week = paste0(2018,"-",week)),
    dat_2019 %>% select(ends_with("-C")) %>% select_all(~gsub("-C","",.)) %>% mutate_all(as.numeric) %>% gather(week,sCh) %>% mutate(week = paste0(2019,"-",week)))

  deaths = bind_rows(
    dat_2017 %>% select(ends_with("-D")) %>% select_all(~gsub("-D","",.)) %>% mutate_all(as.numeric) %>% gather(week,deaths) %>% mutate(week = paste0(2017,"-",week)),
    dat_2018 %>% select(ends_with("-D")) %>% select_all(~gsub("-D","",.)) %>% mutate_all(as.numeric) %>% gather(week,deaths) %>% mutate(week = paste0(2018,"-",week)),
    dat_2019 %>% select(ends_with("-D")) %>% select_all(~gsub("-D","",.)) %>% mutate_all(as.numeric) %>% gather(week,deaths) %>% mutate(week = paste0(2019,"-",week)))

  rc <- full_join(cases,deaths) %>% 
    mutate(who_region = who_reg, ISO_A1 = cntry, TL = week2date(week,week_start=1),TR = TL + 6,Primary=TRUE)

  ## now deal with the cumulatives 
  ## assuming they are correct when the sums of the cases does not equal the cumulative number listed...
  rc <- rc %>%  mutate(TL = if_else(week == "2019-Cum",week2date("2019-W01"),
                              if_else(week == "2018-Cum",week2date("2018-W01"),
                              if_else(week == "2017-Cum",week2date("2017-W01"),
                              TL
                             ))),
                 TR = if_else(week == "2019-Cum",week2date("2019-W16")+6,
                              if_else(week == "2018-Cum",week2date("2018-W52")+6,
                                      if_else(week == "2017-Cum",week2date("2017-W52")+6,
                                              TR
                                      )))
                 )

  ## finally deal with the 2017 dates
  rc <- rc %>% mutate(TL = if_else(week == "2017-W22",week2date("2017-W01"),TL))

  ## add phantom col and remove complete NA observations
  rc <- rc %>% mutate(Phantom = "") %>% rename(epiweek = week) #%>% filter(!is.na(sCh) & !is.na(deaths))

  write_csv(rc, paste0("taxfile_esar_", cntry, ".csv"))

}
