README.Rmd

---
output: github_document
---

<!-- README.md is generated from README.Rmd. Please edit that file -->

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "man/figures/README-",
  out.width = "100%"
)
```

# pf

<!-- badges: start -->
<!-- badges: end -->

The goal of pf_app is to provide tools for working with ProjectFacts data and easy reporting of the data.

## Installation

You can install `pf` with `remotes` via 

``` r
remotes::install_github("CTU-Bern/pf")
```

Once it's installed, it can be updated via

``` r
pf::update_pf()
```

## Example

```{r example}
library(pf)
```

The following code is used to update the data on the R drive.

```{r updateR, eval = FALSE}
# load the data from R
all_tabs <- getPFData()
# download data via ODBC
all_tabs <- getPFData(NULL)

all_tabs$customer <- decodeCustomFields(all_tabs$customer, all_tabs$customfields)
all_tabs$project <- decodeCustomFields(all_tabs$project, all_tabs$customfields)
all_tabs$ticket <- decodeCustomFields(all_tabs$ticket, all_tabs$customfields)
all_tabs$worker <- decodeCustomFields(all_tabs$worker, all_tabs$customfields)
# save the data to R
savePFdata(all_tabs)
```

Regular usage would be e.g.: 

```{r load, eval = FALSE}
# load the data from R
all_tabs2 <- getPFData()

dat <- prepTime(all_tabs2)
```

Hours spent by DM for set up and STA for analysis:

```{r dm_sta, eval = FALSE}
library(tidyverse)
d <- dat %>% 
  dplyr::mutate(
    tokeep = stringr::str_detect(.$finart_Name, "Database [Ss]et-up|Data [Aa]nalysis|Statist") |
      stringr::str_detect(.$finart_Name1, "Stat")) %>% 
  dplyr::select(ctu_projectName, starts_with("finart"), starts_with("ProjectName"), tokeep) |> #View()
  dplyr::filter(tokeep) %>% 
  dplyr::mutate(div = stringr::word(finart_Name, end = 2),
                div = stringr::str_to_sentence(div), 
                div = case_when(div == "Regular reports" ~ "Data analysis",
                                div == "Study planning" ~ "Data analysis",
                                TRUE ~ div)) %>% 
  dplyr::group_by(ctu_projectName, div) %>% 
  dplyr::summarize(billable_time = sum(billable_time),
            nonbillable_time = sum(nonbillable_time)) %>% 
  dplyr::mutate(billable_time = billable_time/60,
                nonbillable_time = nonbillable_time/60)

writexl::write_xlsx(d, "../DBsetup_STA_time_may2022.xlsx")
d %>% 
  ggplot(aes(x = billable_time)) +
  geom_histogram() +
  facet_wrap(~div)
```

#### Monthly billable time at CTU and division level

```{r}
library(lubridate)
monthly_billable_div <- dat |> 
  mutate(month = month(BookedDate)) |> 
  group_by(ctu_division, year, month) |> 
  filter(billable == "Billable") |> 
  filter(!str_detect(CaseId, "^I")) |> 
  summarize(Timespent_billable = sum(Timespent_billable)) |> #View()
  mutate(year_month = ym(paste0(year, "-", month)), 
         hours = Timespent_billable/60) |> 
  filter(year > 2020) 

monthly_billable_ctu <- monthly_billable_div |> 
  filter(ctu_division != "CI") |> 
  group_by(year, month) |> 
  summarize(Timespent_billable = sum(Timespent_billable)) |> #View()
  mutate(year_month = ym(paste0(year, "-", month)), 
         hours = Timespent_billable/60)

ggplot(monthly_billable_div, 
       aes(x = year_month, 
          y = hours, 
          col = ctu_division)) +
  geom_line() +
  labs(x = "Month", 
       y = "Billable hours", 
       col = "Division")

writexl::write_xlsx(list(divisions = monthly_billable_div, ctu = monthly_billable_ctu), paste0("Monthly_billable_", Sys.Date(), ".xlsx"))
```



### Reports

#### CTU quarterly report

```{r, eval = FALSE}
rmarkdown::render(input = "R:/Projectfacts/ODBC/pf_app/vignettes/quarterly.Rmd", 
                  output_file=file.path('R:/Projectfacts/ODBC/reports', 
                                        paste0("CTUQuarterly_",
                                               Sys.Date(),
                                               ".html")))
```

### Miscellaneous other things

#### Email addresses of project contacts

```{r, eval = FALSE}
library(tidyverse)
View(all_tabs$project)
View(all_tabs$contactfield)

tmp <- all_tabs$project %>% 
  filter(!is.na(FK_CUSTOMERCONTACT)) %>% 
  left_join(all_tabs$crmkontakt, 
            by = c("FK_CUSTOMERCONTACT" = "PK_CRMKONTAKT")) %>% 
  left_join(all_tabs$customer %>% 
              select(PK_CUSTOMER, Path) %>% 
              rename(customer_name = Path), 
            by = c("FK_CUSTOMER.x" = "PK_CUSTOMER")) %>% 
  left_join(all_tabs$contactfield %>% 
              filter(TYPE == 30),
            by = c("FK_CUSTOMERCONTACT" = "FK_CONTACT")) %>% 
  select(Vorname, Nachname, VALUE, customer_name) %>% 
  rename(Email = VALUE) %>% 
  distinct(Vorname, Nachname, Email, .keep_all = TRUE) 

writexl::write_xlsx(tmp, "../reports/emailaddresses.xlsx")
```