R/read_all_prosha.R

In St-Digital-Twin/Dtwin: Re-usable functions for City Dtwin

#' @title Download all master-data from Prosha
#' @description Function download all master-data from dwh_prod and munstat2 databases from Prosha
#' @usage read_all_prosha(is_mnst = FALSE, sheet = "All")
#' @param is_mnst if TRUE, master data download from "munstat2" as well
#' @param sheet A name character of the needed sheet, like "location", All means all tables
#' @return List with 2 leaves containe separated tibbles with master-data tables (location, indicator, okved, etc..)
#' @export
#' @importFrom dplyr %>%
#' @importFrom tibble tibble
#' @importFrom tibble as_tibble
#' @importFrom tidyr pivot_wider
#' @importFrom dplyr tbl
#' @importFrom DBI dbGetQuery
#' @importFrom DBI dbListTables
#' @importFrom dplyr filter
#' @examples
#' read_all_prosha()
#' read_all_prosha(is_mnst = TRUE)
#' read_all_prosha(sheet = c("location", "okved"))

read_all_prosha   <- function (is_mnst = FALSE, sheet = "All"){ #sheet = "sex"

  fact_tables <- c ("demography", "economic", "city_environ", "project_fact", "catalogs.region_connection", "orgs")
  # fact_type <- data.frame(fact_tables, fact_type = c(1,0,2,NA))

  dwh_prod <- set_conn_prosha (dbname   = "dwh_prod")
  dwh_prod_table <- DBI::dbListTables(dwh_prod) %>% as_tibble()  # Вызываем таблицы баз данных
  dwh_mnst <- set_conn_prosha (dbname   = "munstat2")
  dwh_mnst_table <- DBI::dbListTables(dwh_mnst) %>% as_tibble()  # Вызываем таблицы баз данных

  if (sheet != "All") {
    dwh_prod_table <- dwh_prod_table %>% filter (value %in% sheet)
    dwh_mnst_table <- dwh_mnst_table %>% filter (value %in% sheet)
  }

  # Записывание всех таблиц в оперативную память
  data_list_1 <- list (dwh_prod = dwh_prod_table %>% pivot_wider(names_from = "value") %>% as.list(),
                       dwh_mnst = dwh_mnst_table %>% pivot_wider(names_from = 'value') %>% as.list())

  # Процедура выкачивания всех справочников
  for(i in 1:length(data_list_1$dwh_prod)) {
    if(!names(data_list_1$dwh_prod[i]) %in% fact_tables){
      data_list_1$dwh_prod[[i]] <- dplyr::tbl(dwh_prod, names(data_list_1$dwh_prod[i])) %>% as_tibble()}
  }

  if (is_mnst) {
    for(i in 1:length(data_list_1$dwh_mnst)) {
      if(!names(data_list_1$dwh_mnst[i]) %in% fact_tables){
        data_list_1$dwh_mnst[[i]] <- dplyr::tbl(dwh_mnst, names(data_list_1$dwh_mnst[i])) %>% as_tibble()}
    }

    if (!is.null (data_list_1$dwh_mnst$location) ){
      data_list_1$dwh_mnst$location <- data_list_1$dwh_mnst$location %>% oktmo_restore ("oktmo")
    } else data_list_1$dwh_mnst$location <- dbGetQuery(dwh_mnst, "SELECT * FROM catalogs.oktmo") %>% as_tibble()

  } else data_list_1$dwh_mnst = data_list_1$dwh_prod

  return(data_list_1)
}