rm(list=ls())

library(RMySQL)
library(dplyr)
library(ggplot2)

con = dbConnect(MySQL(),user="root", password="qwer1234",
                dbname="nhiss", host="localhost")
myQuery <- "SELECT
              CONCAT(MDCARE_STRT_YEAR, '-', LPAD(MDCARE_STRT_MONTH, 2, '0')) AS YEARMONTH,
              MDCARE_STRT_YEAR,
              MDCARE_STRT_MONTH,
              MDCARE_STRT_DAY,
              MDCARE_STRT_DT,
              THE_ID
            FROM MCARE
            ORDER BY MDCARE_STRT_DT ASC"
R1 <- dbGetQuery(con, myQuery)
R2 <- tbl_df(R1)
R3 <- R2 %>%
  group_by(YEARMONTH, MDCARE_STRT_YEAR, MDCARE_STRT_MONTH) %>%
  summarize(count = n_distinct(THE_ID)) %>%
  arrange(MDCARE_STRT_YEAR, MDCARE_STRT_MONTH)


R3$YEARMONTH = as.factor(R3$YEARMONTH)
R3$MDCARE_STRT_YEAR = as.factor(R3$MDCARE_STRT_YEAR)
R3$MDCARE_STRT_MONTH = as.factor(R3$MDCARE_STRT_MONTH)
R3$count = as.numeric(R3$count)

ggplot(R3, aes(x=YEARMONTH, y=count)) +
  geom_bar(stat="identity") +
  theme(axis.text.x = element_text(angle=90, vjust=0.5, hjust=1))

ggplot(R3, aes(MDCARE_STRT_MONTH, count)) +
  geom_bar(aes(fill=MDCARE_STRT_YEAR), stat="identity", position="dodge") +
  theme(axis.text.x = element_text(angle=90, vjust=0.5, hjust=1))

R4 <- R3 %>%
  group_by(MDCARE_STRT_MONTH) %>%
  summarize(avg = mean(count), stdv = sd(count)) %>%
  select(MDCARE_STRT_MONTH, avg, stdv) %>%
  arrange(MDCARE_STRT_MONTH)

ggplot(R4, aes(x=MDCARE_STRT_MONTH, y=avg)) +
  geom_bar(stat="identity") +
  coord_cartesian(xlim=c(1, 12)) +
  geom_errorbar(data=R4, mapping=aes(x=MDCARE_STRT_MONTH, ymin=avg-stdv, ymax=avg+stdv))

dbDisconnect(con)