library(DBI)
library(RMySQL)
library(car)

GetDB <- function (SQL) {
  mySQL.Connection <- dbConnect(MySQL(), host=mySQL.Host, user=mySQL.User, password=mySQL.Password, db=mySQL.Schema)

  ResultSet <- dbGetQuery(mySQL.Connection, SQL)
  dbDisconnect(mySQL.Connection)
  
  ResultSet
}

SaveScatterPlotGraphics <- function(pngFileName, formula, graphicsTitle, graphicsXTitle, graphicsYTitle) {
  png(file=pngFileName)
    scatterplot(formula, reg.line=lm, smooth=TRUE, spread=TRUE, boxplots='xy', 
span=0.5, main=graphicsTitle, xlab=graphicsXTitle, ylab=graphicsYTitle)
  dev.off()
}

PrintANOVA <- function (formula, heading) {
  cat(paste("ANOVA(", heading, ")", sep=""))
  cat("\n")
  R.ANOVA.PAGE_FAULTS = aov(formula)
  summary(R.ANOVA.PAGE_FAULTS) 
  print(model.tables(R.ANOVA.PAGE_FAULTS, "means"), digits=3)
  cat("\n\n")
}

mySQL.Host <- "127.0.0.1"
mySQL.User <- "nmon"
mySQL.Password <- "nmon"
mySQL.Schema <- "nmon"

R.Chart.Folder <- "/var/nmon/Results/Graphics"
R.Analysis.Folder <- "/var/nmon/Results/Analysis"

mySQL.SQL <- paste("SELECT DISTINCT DATE(ZZZZ) AS TXN_DATE FROM ",
 mySQL.Schema, ".page ORDER BY ZZZZ LIMIT 30", sep="")
mySQL.DATA <- GetDB(mySQL.SQL)

Date.Start <- mySQL.DATA$TXN_DATE[1]
Date.End <- mySQL.DATA$TXN_DATE[length(mySQL.DATA$TXN_DATE)]

mySQL.SQL <- paste("SELECT HOSTNAME, TO_DAYS(ZZZZ) AS TXN_DATE,
 AVG(faults) AS PAGE_FAULTS, AVG(pgin) AS PAGE_IN, AVG(pgout) AS PAGE_OUT, AVG(reclaims) AS PAGE_RECLAIMS, AVG(scans) AS PAGE_SCANS FROM ", mySQL.Schema, ".page WHERE DATE(ZZZZ) BETWEEN '", Date.Start, "' AND '", Date.End,
 "' GROUP BY HOSTNAME, DATE(ZZZZ) ORDER BY HOSTNAME, DATE(ZZZZ)", sep="")

mySQL.DATA <- GetDB(mySQL.SQL)

TXN_DATE <- (mySQL.DATA$TXN_DATE) 
HOSTNAME <- (mySQL.DATA$HOSTNAME)
PAGE_FAULTS <- (mySQL.DATA$PAGE_FAULTS)
PAGE_IN <- (mySQL.DATA$PAGE_IN)
PAGE_OUT <- (mySQL.DATA$PAGE_OUT)

mySQL.Chart <- paste(R.Chart.Folder, "/", "AVG_HOST_PAGE_FAULTS.png", sep="")
SaveScatterPlotGraphics(mySQL.Chart, PAGE_FAULTS~TXN_DATE | HOSTNAME, "PAGING.FAULTS~TXN.DATE by HOST (Last 30 Days)", "Day #", "PAGING FAULTS")

mySQL.Chart <- paste(R.Chart.Folder, "/", "AVG_HOST_PAGE_IN.png", sep="")
SaveScatterPlotGraphics(mySQL.Chart, PAGE_IN~TXN_DATE | HOSTNAME, "PAGING.IN~TXN.DATE by HOST (Last 30 Days)", "Day #", "PAGING IN")

mySQL.Chart <- paste(R.Chart.Folder, "/", "AVG_HOST_PAGE_OUT.png", sep="")
SaveScatterPlotGraphics(mySQL.Chart, PAGE_OUT~TXN_DATE | HOSTNAME, "PAGING.OUT~TXN.DATE by HOST (Last 30 Days)", "Day #", "PAGING OUT")

sink(paste(R.Analysis.Folder, "/", "AVG_HOST_PAGING.txt", sep=""))

PrintANOVA(PAGE_FAULTS~factor(HOSTNAME), "PAGE_FAULTS")
PrintANOVA(PAGE_IN~factor(HOSTNAME), "PAGE_IN")
PrintANOVA(PAGE_OUT~factor(HOSTNAME), "PAGE_OUT")

sink()

rm(list=(ls()))