library("RMySQL")
library("ggplot2")
library("dplyr")
dbDisconnect(mydb)
all_cons <- dbListConnections(MySQL())
for (con in all_cons)
  dbDisconnect(con)
mydb = dbConnect(MySQL(), user='threader', password='<PASSWORD>', dbname='threads', host='localhost')

query <- "
SELECT
visits.*,
people.first_name,
people.last_name,
SUBSTR(visits.clienttime, 1, 10) date,
IF(orgs.name IS NOT NULL, orgs.name, IF (people.email like '%projectspac%', 'project spaces', 'other')) org_name
FROM
page_visitation visits
JOIN
people people
ON
people.uid = visits.user_id
LEFT OUTER JOIN
orgs orgs
ON
orgs.uid = people.org_id AND
orgs.uid IN ('34360266575', '34360211026', '34360210992') AND  SUBSTR(visits.clienttime, 1, 10) IN ('2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04', '2018-01-05', '2018-01-06', '2018-01-07', '2018-01-08', '2018-01-09', '2018-01-10', '2018-01-11', '2018-01-12', '2018-01-13', '2018-01-14', '2018-01-15', '2018-01-16', '2018-01-17', '2018-01-18')
"

rs = dbSendQuery(mydb, query)
data = fetch(rs, n=-1)

by_org <- data %>% group_by(org_name, date) %>%
  summarize(visits = n(), dau = n_distinct(user_id)) %>%
  filter(date %in% c('2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04', '2018-01-05', '2018-01-06', '2018-01-07', '2018-01-08', '2018-01-09', '2018-01-10', '2018-01-11', '2018-01-12', '2018-01-13', '2018-01-14', '2018-01-15', '2018-01-16', '2018-01-17', '2018-01-18')) %>%
  filter(org_name %in% c('Albert', 'Chirps', 'project spaces', 'Recharge'))

ggplot(by_org, aes(x = as.Date(date, format="%Y-%m-%d"), y = visits, color=org_name)) +
  geom_line() +
  ggtitle("page visits") +
  theme(plot.title = element_text(size=16, hjust=0.5)) +
  xlab("date")

ggplot(by_org, aes(x = as.Date(date, format="%Y-%m-%d"), y = dau, color=org_name)) +
  geom_line() +
  ggtitle("page visiting dau") +
  theme(plot.title = element_text(size=16, hjust=0.5)) +
  xlab("date")

query <- "
SELECT
  contents.*,
  people.first_name,
  people.last_name,
  SUBSTR(contents.created_timestamp, 1, 10) date,
  IF(orgs.name IS NOT NULL, orgs.name, IF (people.email like '%projectspac%', 'project spaces', 'other')) org_name
FROM
  contents contents
JOIN
  people people
ON
  people.uid = contents.actor_id AND
  contents.content_type IN ('comment', 'reply', 'thread')
LEFT OUTER JOIN
  orgs orgs
ON
  orgs.uid = people.org_id AND
  orgs.uid IN ('34360266575', '34360211026', '34360210992') AND SUBSTR(contents.created_timestamp, 1, 10) IN ('2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04', '2018-01-05', '2018-01-06', '2018-01-07', '2018-01-08', '2018-01-09', '2018-01-10', '2018-01-11', '2018-01-12', '2018-01-13', '2018-01-14', '2018-01-15', '2018-01-16', '2018-01-17', '2018-01-18')
"

rs = dbSendQuery(mydb, query)
data = fetch(rs, n=-1)

by_org <- data %>% group_by(org_name, date) %>%
  summarize(writes = n(), writes_dau = n_distinct(actor_id)) %>%
  filter(date %in% c('2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04', '2018-01-05', '2018-01-06', '2018-01-07', '2018-01-08', '2018-01-09', '2018-01-10', '2018-01-11', '2018-01-12', '2018-01-13', '2018-01-14', '2018-01-15', '2018-01-16', '2018-01-17', '2018-01-18')) %>%
  filter(org_name %in% c('Albert', 'Chirps', 'project spaces', 'Recharge'))

ggplot(by_org, aes(x = as.Date(date, format="%Y-%m-%d"), y = writes, color=org_name)) +
  geom_line() +
  ggtitle("writes") +
  theme(plot.title = element_text(size=16, hjust=0.5)) +
  xlab("date")

ggplot(by_org, aes(x = as.Date(date, format="%Y-%m-%d"), y = writes_dau, color=org_name)) +
  geom_line() +
  ggtitle("writes dau") +
  theme(plot.title = element_text(size=16, hjust=0.5)) +
  xlab("date")