library(odbc)
library(DBI)
library(dplyr)
library(ggplot2)
con <- dbConnect(odbc(), Driver = "ODBC Driver 17 for SQL Server", 
                 Server = "REX\\SQLEXPRESS", Database = "VYUKA_DATABAZE", 
                 Trusted_Connection = "yes",
                 encoding = "CP1250")

#data<-dbReadTable(con, "nakaza") #nacítá celá data do pameti R 
data<-tbl(con, "nakaza") #pouze odkaz na data

data %>% select(nak_datum_hlaseni)

data %>% mutate(rok=year(nak_datum_hlaseni))

data %>% filter(typ_hlaseni=='N')

d<-data %>% 
  group_by(nak_datum_hlaseni) %>%
  summarise(celkem = count(nak_datum_hlaseni))
d
show_query(d)

nakaza<-data %>% 
  group_by(nak_datum_hlaseni) %>%
  filter(typ_hlaseni=='N') %>%
  summarise(celkem = count(nak_datum_hlaseni)) %>%
  arrange(nak_datum_hlaseni)
  
nakaza %>% plot(celkem ~ nak_datum_hlaseni, data = .,type='l')

nakaza %>% ggplot(data = .)  + geom_line(aes(x=nak_datum_hlaseni , y = celkem))

nakaza %>%  ggplot(data = .)  + geom_line(aes(x=nak_datum_hlaseni , y = celkem))  + 
   geom_smooth(aes(x=nak_datum_hlaseni , y = celkem),span = 0.25)

okres<-tbl(con, "okres")

inner_join(data,okres)

nakaza_v_praze<-inner_join(data,okres) %>% 
  group_by(nak_datum_hlaseni) %>%
  filter(typ_hlaseni=='N', ok_nazev_kratky=='Praha') %>%
  summarise(celkem = count(nak_datum_hlaseni)) 

nakaza_v_praze %>%
  summarize(Max = max(celkem, na.rm=TRUE))

nakaza_v_praze %>% ggplot(data = .)  + geom_line(aes(x=nak_datum_hlaseni , y = celkem))

nakaza_v_praze %>%
  ggplot(data = .) + geom_histogram(aes(x = celkem))

b<-nakaza_v_praze %>%
  ggplot(data = .) + geom_boxplot(aes(x = celkem))
b
ggplot_build(b)$data

kraj<-tbl(con, "kraj")

inner_join(inner_join(data,okres),kraj) %>% 
  group_by(nak_datum_hlaseni,ok_nazev_kratky) %>%
  filter(typ_hlaseni=='N', kr_nazev=='Stredoceský kraj') %>%
  summarise(celkem = count(nak_datum_hlaseni)) %>%
  ggplot(data = .) + geom_boxplot(aes(x=ok_nazev_kratky, y = celkem))


inner_join(okres,kraj)  %>% 
  mutate(pocet_obci=
           case_when(
             ok_pocet_obci <= 10  ~ "0-30",
             ok_pocet_obci <= 100 & ok_pocet_obci > 10 ~ "31-100",
             TRUE ~ "více než 101")) %>% 
  ggplot(data = .) + geom_bar(aes(x = kr_nazev, fill=pocet_obci))