---
title: "MU MAP"
output: 
  flexdashboard::flex_dashboard:
    theme: bootstrap
#    logo: NKU.png
    social: [ "twitter", "facebook", "menu" ]
    source: embed
    navbar:
      - { title: "NKU SR web page", href: "https://www.nku.gov.sk/web/sao", align: right }
---


```{r setup, include=FALSE}

library(crosstalk)
library(leaflet)
library(plotly)
library(DT)

# inputs defined here
InputData = read.csv2("ddddd2.csv")
InputDataF = read.csv2("zisteniii2.csv")

MapData = data.frame(InputData)
MapDataF = data.frame(InputDataF)

MapDataVzdel = MapData[MapData$pocet_skol != 0,]
MapDataZdrav = MapData[MapData$pocet_nemocnic != 0,]
MapDataEufon = MapData[MapData$Contructed_funds != 0,]

zist = SharedData$new(MapDataF)
vzdel = SharedData$new(MapDataVzdel) 
zdrav = SharedData$new(MapDataZdrav)
eufon = SharedData$new(MapDataEufon)

```
 
-------------------------------------
    
# General info {data-icon="fa-lightbulb"}

### Municipalities - click for more info 
    
```{r map1}


vyskoc = paste("","",MapData$Obec,"","","
",
               "","Status","",MapData$status,"
",
               "","Inhabitants 1993","",MapData$po1993,"
",
               "","Inhabitants 2018","",MapData$po2018,"
",
               "","Actual revenue 2018","",MapData$skutocne_prijmy_2018,"
",
               "","Actual expenditure 2018","",MapData$skutocne_vydavky_2018,"
",
               "","Debt per capita","",MapData$dlh_percapita,"
",
               "","EU funds spending per capita","",MapData$Spent_funds_pc,"
",
               "","Number of schools","",MapData$pocet_skol,"
",
               "","Performed audits since 2012","",MapData$kontroly_spolu,"
",
               paste(' Municipality homepage', sep = ''), "
",
               paste(' Financial statements', sep = ''), "
",
               paste(' Public procurement profile', sep = ''), "
",
               paste(' Central register of contracts - supplier', sep = ''), "
",
               paste(' Central register of contracts - purchaser', sep = ''), "
",       
               paste(' SAO SR auditee web page', sep = ''), "
", 
               "","Performed audits","","
",MapData$KA_vobci,"
")
               

        leaflet(data = "MapData") %>%
          addTiles() %>%  
          addCircleMarkers(MapData$XXX, MapData$YYY, popup = vyskoc, clusterOptions = markerClusterOptions())

```
    
# Audit findings {data-icon="fa-compress"}

Column {data-width=700}
-------------------------------------
    
### 
    
```{r map2}

#dorobit 
vyskocF = paste("","",MapDataF$Municipality,"","","
",
                "","Audits performed since 2009","",MapDataF$pocet_kontrol_2009_19,"
",
                "","Count of all findings","",MapDataF$pocet_zisteni,"
")

zist %>% 
      leaflet() %>%
          addTiles() %>%  
          addCircles(lng = ~XXX, lat = ~YYY, radius = ~pocet_kontrol_2009_19 * 10, popup = vyskocF)

```

Column {data-width=300}
-------------------------------------

### 
```{r filters2}
filter_select(
  id = "Municipality",
  label = "Municipality",
  sharedData = zist,
  group = ~Municipality
)

bscols(
   filter_checkbox(
    id = "Typ_KA",
    label = "Type of audit",
    sharedData = zist,
    group = ~Typ_KA)
  ,
   filter_checkbox(
    id = "Rok_PKC",
    label = "Year of audit",
    sharedData = zist,
    group = ~Rok_PKC)
  )

filter_slider(
    id = "Suma",
    label = "Amount",
    sharedData = zist,
    column = ~Suma,
    step = 50,
    round = TRUE,
    sep = "",
    ticks = FALSE
  )
 


```

### 
    
```{r findings}
zist %>% 
  DT::datatable(
    filter = "top",  # allows filtering on each column
#    extensions = c(
#      "Buttons",  # add download buttons, etc
#      "Scroller"  # for scrolling down the rows rather than pagination
#    ),
    rownames = FALSE,  # remove rownames
    style = "bootstrap",
    class = "compact",
    width = "100%",
    options = list(
      dom = "Blrtip",  # specify content (search box, etc)
      deferRender = TRUE,
      scrollY = 300,
      scroller = TRUE,
      columnDefs = list(
        list(
          visible = FALSE,
          targets = c(0:2, 5:12)
        )
#      ), 
#      buttons = list(
#        I("colvis"),  # turn columns on and off
#        "csv",  # download as .csv
#        "excel"  # download as .xlsx
      )
    )
  )
```

# Education {data-icon="fa-chalkboard-teacher"}

Column {data-width=700}
-------------------------------------
    
### 
    
```{r map3}


vyskoc2 = paste("","",MapDataVzdel$Obec,"","","
",
                "","Schools","",MapDataVzdel$pocet_skol,"
",
                "","Ecclesiastic","",MapDataVzdel$pocet_cirkevne,"
",
                "","Private","",MapDataVzdel$pocet_sukromne,"
",
                "","Public","",MapDataVzdel$pocet_V,"
",
                "","State","",MapDataVzdel$pocet_statne,"
",
                "","Average salary","", MapDataVzdel$priemmzda_uc, "
",
                "","Teachers count","", MapDataVzdel$pocet_ucitelov, "
")
vzdel %>% 
      leaflet() %>%
          addTiles() %>%  
          addCircles(lng = ~XXX, lat = ~YYY, radius = ~pocet_skol * 200, popup = vyskoc2)

```

Column {data-width=300}
-------------------------------------

### 

```{r filters3}

filter_select(
  id = "Okres",
  label = "Choose county",
  sharedData = vzdel,
  group = ~Okres
)

filter_checkbox(
    id = "status",
    label = "Municipality / city / village",
    sharedData = vzdel,
    group = ~status
  )

filter_slider(
    id = "priemmzda_uc",
    label = "Average salary of pedagogic employee (in euro)",
    sharedData = vzdel,
    column = ~priemmzda_uc,
    step = 50,
    round = TRUE,
    sep = "",
    ticks = FALSE
  )

filter_slider(
    id = "pocet_ucitelov",
    label = "Teachers count",
    sharedData = vzdel,
    column = ~pocet_ucitelov,
    step = 5,
    round = TRUE,
    sep = "",
    ticks = FALSE
  )

```

### Histogram of average salary of pedagogic employee (in euro)
    
```{r plot3}
vzdel %>% 
  plot_ly(x = ~priemmzda_uc, histfunc='sum',  type = "histogram")
  
```

# Health {data-icon="fa-h-square"}

Column {data-width=700}
-------------------------------------
    
### 
    
```{r map4}

vyskoc3 = paste("","",MapDataZdrav$Obec,"","","
",
                "","Hospitals count","",MapDataZdrav$pocet_nemocnic,"
",
                "","Hospitals costs","",MapDataZdrav$naklady_nemocnic,"
",
                "","Hospitals costs without operational","",MapDataZdrav$naklady_nemocnic_bezspravy,"
",
                "","Revenue from health insurance","",MapDataZdrav$trzby_odZP,"
",
                "","Average salary","", MapDataZdrav$priemmzda_zd, "
")

zdrav %>% 
      leaflet() %>%
          addTiles() %>%  
#          addMarkers(lng = ~XXX, lat = ~YYY)
          addCircleMarkers(lng = ~XXX, lat = ~YYY, radius = ~pocet_nemocnic * 2, popup = vyskoc3)

```

Column {data-width=300}
-------------------------------------

### 

```{r filters4}
filter_select(
  id = "Obec",
  label = "Municipality",
  sharedData = zdrav,
  group = ~Obec
)

filter_checkbox(
    id = "pocet_nemocnic",
    label = "Hospitals count",
    sharedData = zdrav,
    group = ~pocet_nemocnic
  )

filter_slider(
    id = "priemmzda_zd",
    label = "Average salary",
    sharedData = zdrav,
    column = ~priemmzda_zd,
    step = 50,
    round = TRUE,
    sep = "",
    ticks = FALSE
  )

filter_slider(
    id = "naklady_nemocnic",
    label = "Hospitals costs",
    sharedData = zdrav,
    column = ~naklady_nemocnic,
    step = 500,
    round = TRUE,
    sep = "",
    ticks = FALSE
  )

filter_slider(
    id = "trzby_odZP",
    label = "Revenue from health insurance",
    sharedData = zdrav,
    column = ~trzby_odZP,
    step = 500,
    round = TRUE,
    sep = "",
    ticks = FALSE
  )

```

### Plot of hospital financing (revenues from health insurance vs. hospital total costs)
    
```{r plot4}
zdrav %>% 
  plot_ly(x = ~naklady_nemocnic, y = ~trzby_odZP) #? color = ~status
  
```

# EU funds {data-icon="fa-euro-sign"}

Column {data-width=700}
-------------------------------------
    
### 
    
```{r map5}

vyskoc5 = paste("","",MapDataEufon$Obec,"","","
",
                "","Contracted funds","",MapDataEufon$Contructed_funds,"
",
                "","Provided funds","",MapDataEufon$Provided_funds,"
",
                "","Spent funds","",MapDataEufon$Spent_funds,"
",
                "","Contracted funds per capita","",MapDataEufon$condstructed_funds_pc,"
",
                "","Provided funds per capita","",MapDataEufon$provided_funds_pc,"
",
                "","Spent funds per capita","", MapDataEufon$Spent_funds_pc, "
",
                "","Municipal debt per capita","", MapDataEufon$dlh_percapita, "
")
eufon %>% 
      leaflet() %>%
          addTiles() %>%  
          addCircles(lng = ~XXX, lat = ~YYY, radius = ~Spent_funds_pc * 2, popup = vyskoc5)

```

Column {data-width=300}
-------------------------------------

### Cumulative for 2014 - 2020, per capita

```{r filters5}

filter_select(
  id = "Obec",
  label = "Municipality",
  sharedData = eufon,
  group = ~Obec
)

filter_slider(
    id = "condstructed_funds_pc",
    label = "Contracted funds per capita",
    sharedData = eufon,
    column = ~condstructed_funds_pc,
    step = 50,
    round = TRUE,
    sep = "",
    ticks = FALSE
  )

filter_slider(
    id = "provided_funds_pc",
    label = "Provided funds per capita",
    sharedData = eufon,
    column = ~provided_funds_pc,
    step = 50,
    round = TRUE,
    sep = "",
    ticks = FALSE
  )

filter_slider(
    id = "Spent_funds_pc",
    label = "Spent funds per capita",
    sharedData = eufon,
    column = ~Spent_funds_pc,
    step = 1000,
    round = TRUE,
    sep = "",
    ticks = FALSE
  )

filter_slider(
    id = "dlh_percapita",
    label = "Municipal debt per capita",
    sharedData = eufon,
    column = ~dlh_percapita,
    step = 1000,
    round = TRUE,
    sep = "",
    ticks = FALSE
  )

```

### Plot of provided vs. spent funds (per capita)
    
```{r plot5}
eufon %>% 
  plot_ly(x = ~provided_funds_pc, y = ~Spent_funds_pc, color = ~status)  
  
```


-------------------------------------
    
# About {data-icon="fa-info-circle"}

Column {data-width=400}
-------------------------------------
    
### About this app 

#### IDEA 

The SAO SR can audit approximately 2 900 municipalities. Our office needs an effective analysis tool to identify potential municipalities for audit.
By the end of 2019 we decided to create a database including all municipalities in Slovakia that will be a basis for risk analysis in self-government and selection of entities for audit. In the future we would like to gain all available data on civic amenities of municipalities and use it also for the sample selection. In January 2020 analysts of our office went through training on open software R. When the invitation to hackathon in Prague had come, we started building the municipality database and using R to visualize it.

#### SK DATA per approximately 2 900 municipalities

1.	The list of all municipalities (Statistical Office of the Slovak Republic)
2.	Population in municipality in 1993 – 2018 (source: Statistical Office of the Slovak Republic)
3.	Budgets - budgeted/expected revenues and expenditures of 2018, budgeted/expected revenues of 2019, actual revenues v 2018 (source: Datacentre, Register of financial statements)
4.	SAO‘s audits conducted in municipalities in 2012 – 2018 – number of audits, audit findings, links to final reports (source: internal database from our IT department)
5.	Elementary schools data of 2018 – number of schools in the municipality, type of founder, average registered number of pedagogic staff in the school, labor expenditures, tariff salaries, remunerations, average salary of pedagogic employees, number of students, number of classes, number of students from socially disadvantaged environment, coefficient of qualification structure (source: Ministry of Education SR)
6.	Hospitals data of 2018: total costs, costs without operational, incomes from health insurance companies for medical activities, average salary, number of employees (source: Ministry of Health SR)
7.	Drawings from EU funds for programming period 2014 – 2020 when municipality is recipient – contracted funds, provided funds, spent funds (source: ITMS database)
8.	Coordinates of municipalities (source: Register of addresses from Ministry of Interior SR, www.slovensko.sk)



   
Column {.tabset}
-------------------------------------
   
### General info tab

#### Interactive map of regional structure of Slovakia with the number of municipalities, you can zoom and the number of municipalities is updated. For individual municipality you can see the basic information on population, public procurement profile, link to the website of municipality etc.

 
### Audit findings tab
    
#### Interactive map with number of audits conducted in individual municipalities for period 2012 - 2018, with links to final audit reports and findings.


### Education tab
    
#### Interactive map analysis of all elementary schools in Slovakia in 2018. You can see the number of elementary schools in municipality, histogram of average salary of pedagogic employees, you can choose county, town or village.


### Health tab
    
#### Interactive map analysis of all hospitals in Slovakia in 2018. You can see the number of hospitals in municipality, scatterplot for costs and revenues from health insurance companies, you can choose your municipality.


### EU funds tab
    
#### Interactive map of drawings from EU funds for programming period 2014 – 2020. You can select 3 indicators: contracted funds, provided funds, spent funds per capita, choose your municipality and see also the municipal debt per capita.