import_data.Rmd

---
title: "Import data"
output: html_notebook
---

```{r setup}
library("opensignauxfaibles")
library("dplyr")
library("purrr")
library("RPostgreSQL")
database_signauxfaibles <- database_connect()
```

## Import table altares

```{bash}
in2csv data-raw/altares/RECAP_ALTARES.xlsx > data-raw/altares/RECAP_ALTARES.csv
```

```{r}
db_drop_table_ifexist(db = database_signauxfaibles, table = "table_altares")
import_table_altares(
  path = "data-raw/altares/RECAP_ALTARES.csv"
  ) %>% 
  insert_multi(db = database_signauxfaibles,
               table = "table_altares",
               df = .,
               slices = 10)
```

### Tables des codes RJ/LJ

```{r import-code-rjlj}
table_code_rj_lj <- tibble::tibble(
  code = c("PCL010501","PCL010502","PCL030105","PCL05010102","PCL05010203","PCL05010402","PCL05010302","PCL05010502","PCL05010702","PCL05010802","PCL05010901","PCL05011003","PCL05011101","PCL05011203","PCL05011303","PCL05011403","PCL05011503","PCL05011603","PCL05011902","PCL05012003","PCL0108","PCL0109","PCL030107","PCL030108","PCL030307","PCL030308","PCL05010103","PCL05010104","PCL05010204","PCL05010205","PCL05010303","PCL05010304","PCL05010403","PCL05010404","PCL05010503","PCL05010504","PCL05010703","PCL05010803","PCL05011004","PCL05011005","PCL05011102","PCL05011103","PCL05011204","PCL05011205","PCL05011304","PCL05011305","PCL05011404","PCL05011405","PCL05011504","PCL05011505","PCL05011604","PCL05011605","PCL05011903","PCL05011904","PCL05012004","PCL05012005", "PCL040802"))

db_drop_table_ifexist(db = database_signauxfaibles, table = "table_code_rj_lj")
copy_to(dest = database_signauxfaibles, df = table_code_rj_lj, temporary = FALSE)
```

## Activité partielle

```{r import_apart}
db_drop_table_ifexist(db = database_signauxfaibles, table = "table_activitepartielle")

dplyr::bind_rows(
  import_table_activite_partielle(
    path = "data-raw/activite_partielle/act_partielle_ddes_2012_mai2017.xlsx"
    ),
  import_table_activite_partielle(
    path = "data-raw/activite_partielle/act_partielle_ddes_2012_juin2017.xlsx",
    hta = "hta",
    mta = "mta",
    effectif_autorise = "eff_auto"
    ),
  import_table_activite_partielle(
    path = "data-raw/activite_partielle/act_partielle_ddes_2012_juillet2017.xlsx",
    hta = "hta",
    mta = "mta",
    effectif_autorise = "eff_auto"
    ),
  import_table_activite_partielle(
    path = "data-raw/activite_partielle/act_partielle_ddes_2012_août2017.xlsx",
    hta = "hta",
    mta = "mta",
    effectif_autorise = "eff_auto"
    ),
  import_table_activite_partielle(
    path = "data-raw/activite_partielle/act_partielle_ddes_2012_sept2017.xlsx",
    hta = "hta",
    mta = "mta",
    effectif_autorise = "eff_auto"
    ),
  import_table_activite_partielle(
    path = "data-raw/activite_partielle/act_partielle_ddes_2012_oct2017.xlsx",
    hta = "hta",
    mta = "mta",
    effectif_autorise = "eff_auto"
    )
  ) %>%
  dplyr::distinct() %>%
  dplyr::copy_to(
    dest = database_signauxfaibles,
    df = .,
    temporary = FALSE,
    name = "table_activitepartielle"
    )
```

### Heures consommées

```{r import-apart-heuresconsommees}
db_drop_table_ifexist(db = database_signauxfaibles, table = "table_apart_consommee")

dplyr::bind_rows(
  import_apart_heuresconsommees(
    path = "data-raw/activite_partielle/act_partielle_consommée.xlsx",
    sheet = "INDEMNITE",
    skip = 0,
    date = "mois_concerne_par_le_paiement",
    effectif_concerne = "effectifs_concernes_par_le_paiement",
    heures_consommees = "heures_consommees",
    montants = "montants_des_heures_consommees"),
  import_apart_heuresconsommees(
    path = "data-raw/activite_partielle/act_partielle_conso_juin17.xlsx",
    sheet = "INDEMNITE",
    skip = 0,
    date = "mois",
    effectif_concerne = "effectifs",
    heures_consommees = "heures",
    montants = "montants"),
  import_apart_heuresconsommees(
    path = "data-raw/activite_partielle/act_partielle_conso_juillet2017.xlsx",
    sheet = "INDEMNITE",
    skip = 1,
    date = "mois",
    effectif_concerne = "effectifs",
    heures_consommees = "heures",
    montants = "montants"),
  import_apart_heuresconsommees(
    path = "data-raw/activite_partielle/act_partielle_conso_août2017.xlsx",
    sheet = "INDEMNITE",
    skip = 0,
    date = "mois",
    effectif_concerne = "effectifs",
    heures_consommees = "heures",
    montants = "montants"
    ),
  import_apart_heuresconsommees(
    path = "data-raw/activite_partielle/act_partielle_conso_sept2017.xlsx",
    sheet = "INDEMNITE",
    skip = 0,
    date = "mois",
    effectif_concerne = "effectifs",
    heures_consommees = "heures",
    montants = "montants"
    ),
  import_apart_heuresconsommees(
    path = "data-raw/activite_partielle/act_partielle_conso_oct2017.xlsx",
    sheet = "INDEMNITE",
    skip = 0,
    date = "mois",
    effectif_concerne = "effectifs",
    heures_consommees = "heures",
    montants = "montants"
    )
  ) %>%
  dplyr::distinct() %>%
  dplyr::copy_to(
    dest = database_signauxfaibles,
    df = .,
    name = "table_apart_consommee",
    temporary = FALSE
    )
```


## Cotisations URSSAF

```{r import-cotisations}
db_drop_table_ifexist(
  db = database_signauxfaibles,
  table = "table_cotisation"
  )

dplyr::bind_rows(
  import_table_cotisation(path = "data-raw/cotisations/Urssaf_bourgogne_Cotis_dues_histo_31_08_2016.txt"),
  import_table_cotisation_csv(path = "data-raw/cotisations/Urssaf_bourgogne_Cotis_dues_09_2016_01_2017.csv"),
  import_table_cotisation_csv(path = "data-raw/cotisations/Urssaf_bourgogne_cotis_dues_02_2017_03_2017.csv"),
  import_table_cotisation_csv(path = "data-raw/cotisations/Urssaf_bourgogne_cotis_dues_04_2017_07_2017.csv"),
  import_table_cotisation_csv(path = "data-raw/cotisations/Urssaf_bourgogne_cotis_dues_08_2017_10_2017.csv"),
  import_table_cotisation(path = "data-raw/cotisations/Cotis_dues.txt"),
  import_table_cotisation_csv(path = "data-raw/cotisations/Urssaf_frc_cotis_dues_02_2017_03_2017.csv"),
  import_table_cotisation_csv(path = "data-raw/cotisations/Urssaf_frc_cotis_dues_04_2017_07_2017.csv"),
  import_table_cotisation_csv(path = "data-raw/cotisations/Urssaf_frc_cotis_dues_08_2017_10_2017.csv")
  ) %>%
  dplyr::distinct() %>%
  dplyr::copy_to(
    dest = database_signauxfaibles,
    df = .,
    name = "table_cotisation",
    temporary = FALSE,
    indexes = list("numero_compte", "period")
    )
```

## Débits URSSAF

```{r import-debits}
db_drop_table_ifexist(
  db = database_signauxfaibles,
  table = "table_debit"
)

purrr::map(
  .x = dir(path = "data-raw/debits/", pattern = "*\\.csv") %>%
    paste0("data-raw/debits/", .),
  .f = import_table_debit
  ) %>%
  dplyr::bind_rows() %>%
  dplyr::distinct() %>%
  dplyr::copy_to(
    dest = database_signauxfaibles,
    df = .,
    name = "table_debit",
    temporary = FALSE
  )
```

## Effectifs

```{r import-effectif}
db_drop_table_ifexist(
  db = database_signauxfaibles,
  table = "table_effectif"
  )

import_table_effectif2(
  path = "data-raw/effectif/Urssaf_emploi_BFC_201001_201707.csv"
  ) %>%
  dplyr::copy_to(
    dest = database_signauxfaibles,
    df = .,
    name = "table_effectif",
    temporary = FALSE
    )
```

## NAF

```{r}
import_table_naf(path = "data-raw/naf/naf2008_5_niveaux.xls") %>%
  glimpse()
```

## Données CCSF

```{r import-ccsv}
db_drop_table_ifexist(db = database_signauxfaibles, "table_ccsv")

dplyr::bind_rows(
  import_table_ccsv(path = "data-raw/ccsv/Bourgogne_ccsf.csv"),
  import_table_ccsv(path = "data-raw/ccsv/FRC_ccsf.csv")
  ) %>%
  dplyr::distinct() %>%
  dplyr::copy_to(
    dest = database_signauxfaibles,
    df = .,
    name = "table_ccsv",
    temporary = FALSE,
    indexes = list("numero_compte")
  )
```

## Délais URSSAF

```{r import-delais}
db_drop_table_ifexist(db = database_signauxfaibles, "table_ccsv")

purrr::map(
  .x = dir(path = "data-raw/delais/", pattern = "*.csv") %>%
    paste0("data-raw/delais/", .),
  .f = import_table_delais
  ) %>%
  dplyr::bind_rows() %>%
  dplyr::distinct() %>%
  dplyr::copy_to(
    dest = database_signauxfaibles,
    df = .,
    name = "table_ccsv",
    temporary = FALSE
    )
```

## SIRENE

-> On n'importe plus SIRENE

```{r}
import_table_sirene(path = "data-raw/direccte/sirene/bfc.sas7bdat")
```