library(XLConnect)
library(ggplot2)
library(gridExtra)

setwd("C:/coding/R")
sharestats <- loadWorkbook("./data/openshares/ShareStats_8_21.xlsx")
sheet1 <- readWorksheet(sharestats, sheet = 1)
sheet2 <- readWorksheet(sharestats, sheet = 2)
sheet3 <- readWorksheet(sharestats, sheet = 3)
sheet4 <- readWorksheet(sharestats, sheet = 4)

## populate raw data
h1 <- sum(sheet1$HostCt)
s1 <- sum(sheet1$ShareCt)

h2 <- sum(sheet2$HostCt)
s2 <- sum(sheet2$ShareCt)

h3 <- sum(sheet3$HostCt)
s3 <- sum(sheet3$ShareCt)

h4 <- sum(sheet4$HostCt)
s4 <- sum(sheet4$ShareCt)

## read in data to frame
df <- data.frame(date = factor(c("7/27/2014","8/04/2014","8/13/2014","08/21/2014"), 
        levels=c("7/27/2014","8/04/2014","8/13/2014","08/21/2014")), 
        hostct = c(h1, h2, h3, h4), sharect = c(s1, s2, s3, s4))

## plot
plot1 <- ggplot(data=df, aes(x=date, y=hostct, fill=date, width=0.75)) + geom_histogram(stat="identity") +
        scale_fill_manual(values = c("purple","blue","darkgreen","black")) +
        xlab("Date of scan") + ylab("Host Count") + ggtitle("Count: Hosts w/ Open Shares")
plot2 <- ggplot(data=df, aes(x=date, y=sharect, fill=date, width=0.75)) + geom_histogram(stat="identity") + 
        xlab("Date of scan") + ylab("Share Count") + ggtitle("Count: Open Shares Total")
grid.arrange(plot1, plot2, nrow=1, ncol=2, legend="Open Shares Trend Summary")