# plot9.r
# R file to draw various plots

# wayne.smith@cgu.edu
# created: September 30, 2005
# updated: October 12, 2005


# we need the ODBC extension -- '> install.packages( "RODBC" )'
library( "RODBC" )

# we need the RSvgDevice extension -- '> install.packages( "RSvgDevice" )'
library( "RSvgDevice" )


# for each plot, we would like to have several different formats
drawPlots <- function( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, fileRoot, main, ylab, xlab ) {

  # add the current date to the main title
  main <- paste( main, paste( "source: FCC ULS data -- run date: ", format(Sys.Date(), "%d %b %Y"), sep = ""), sep = "" )

  # set the graphics device to file-based output in .png format
  png( file = paste( fileRoot, ".png", sep = "" ), width = 800, height = 600 )

  # draw the graphic
  #boxplot( data.table )
  #plot( frequency_assigned, CountOfFrequency )
  ts.plot( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, gpars = list( main = main, ylab = ylab, xlab = xlab, lty = c( 1:4 ), las = 1, cex.lab = 1.2 ))
  legend("topleft", c( "StateCA", "CountiesSoCal", "CountyLA", "CityLA" ), lty=c( 1:4 ))

# use this for testing only
#  ts.plot( data.tsStateCA )
#  legend("topleft", c( "StateCA" ), lty=1 )
#  ts.plot( data.tsCountyLA )
#  legend("topleft", c( "CountyLA" ), lty=1 )
#  ts.plot( data.tsCityLA )
#  legend("topleft", c( "CityLA" ), lty=1 )

  # disable the current graphics device
  dev.off()

  # set the graphics device to file-based output in .pdf format
  pdf( file = paste( fileRoot, ".pdf", sep = "" ), title = "R Graphics Output", paper = "letter" )

  # draw the graphic
  #boxplot( data.table )
  #plot( frequency_assigned, CountOfFrequency )
  ts.plot( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, gpars = list( main = main, ylab = ylab, xlab = xlab, lty = c( 1:4 ), las = 1, cex.lab = 1.2 ))
  legend("topleft", c( "StateCA", "CountiesSoCal", "CountyLA", "CityLA" ), lty=c( 1:4 ))

  # disable the current graphics device
  dev.off()

  # set the graphics device to file-based output in .svg format
  devSVG( file = paste( fileRoot, ".svg", sep = "" ))

  # draw the graphic
  #boxplot( data.table )
  #plot( frequency_assigned, CountOfFrequency )
  ts.plot( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, gpars = list( main = main, ylab = ylab, xlab = xlab, lty = c( 1:4 ), las = 1, cex.lab = 1.2 ))
  legend("topleft", c( "StateCA", "CountiesSoCal", "CountyLA", "CityLA" ), lty=c( 1:4 ))

  # disable the current graphics device
  dev.off()
}


# populate a data frame from an ODBC SQL query
dataQueryStateCA <- function( code, scaled ) {

  channel <- odbcConnect( "uls" )

  queryStringStateCA <- paste( "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_state = 'CA' AND code = '", paste( code, "' GROUP BY YearText", sep = "" ), sep = "" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
  #data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_state = 'CA' AND code = 'LIASS' GROUP BY YearText" )
  data.actionquery <- sqlQuery( channel, queryStringStateCA )
  data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot2 SELECT Year, Count FROM tblPlotYear LEFT JOIN tblTempPlot1 ON tblPlotYear.Year = tblTempPlot1.YearText" )
  data.actionaquery <- sqlQuery( channel, "UPDATE tblTempPlot2 SET Count = 0 WHERE Count IS NULL" )

  if ( scaled == 'FALSE' )
    data.StateCA <- sqlQuery( channel, "SELECT Year, Count FROM tblTempPlot2 WHERE Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) ORDER BY Year" )
  else
    data.StateCA <- sqlQuery( channel, "SELECT tblTempPlot2.Year AS Year, ( tblTempPlot2.Count / tblPlotPopulationSum.PopulationSumMM ) AS Count FROM tblTempPlot2 JOIN tblPlotPopulationSum ON tblTempPlot2.Year = tblPlotPopulationSum.Year WHERE tblTempPlot2.Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) AND tblPlotPopulationSum.Region = 'StateCA' ORDER BY tblTempPlot2.Year" )    

  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )

  odbcClose( channel )

  return( data.StateCA )

}

# populate a data frame from an ODBC SQL query
dataQueryCountiesSoCal <- function( code, scaled ) {

  channel <- odbcConnect( "uls" )

  queryStringCountiesSoCal <- paste( "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_county IN ( 'IMPERIAL', 'KERN', 'LOS ANGELES', 'ORANGE', 'RIVERSIDE', 'SAN BERNARDINO', 'SAN DIEGO', 'VENTURA' ) AND code = '", paste( code, "' GROUP BY YearText", sep = "" ), sep = "" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
  #data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_county IN ( 'IMPERIAL', 'KERN', 'LOS ANGELES', 'ORANGE', 'RIVERSIDE', 'SAN BERNARDINO', 'SAN DIEGO', 'VENTURA' ) AND code = 'LIASS' GROUP BY YearText" )
  data.actionquery <- sqlQuery( channel, queryStringCountiesSoCal )
  data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot2 SELECT Year, Count FROM tblPlotYear LEFT JOIN tblTempPlot1 ON tblPlotYear.Year = tblTempPlot1.YearText" )
  data.actionaquery <- sqlQuery( channel, "UPDATE tblTempPlot2 SET Count = 0 WHERE Count IS NULL" )

  if ( scaled == 'FALSE' )
    data.CountiesSoCal <- sqlQuery( channel, "SELECT Year, Count FROM tblTempPlot2 WHERE Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) ORDER BY Year" )
  else
    data.CountiesSoCal <- sqlQuery( channel, "SELECT tblTempPlot2.Year AS Year, ( tblTempPlot2.Count / tblPlotPopulationSum.PopulationSumMM ) AS Count FROM tblTempPlot2 JOIN tblPlotPopulationSum ON tblTempPlot2.Year = tblPlotPopulationSum.Year WHERE tblTempPlot2.Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) AND tblPlotPopulationSum.Region = 'CountiesSoCal' ORDER BY tblTempPlot2.Year" )    

  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )

  odbcClose( channel )

  return( data.CountiesSoCal )

}

# populate a data frame from an ODBC SQL query
dataQueryCountyLA <- function( code, scaled ) {

  channel <- odbcConnect( "uls" )

  queryStringCountyLA <- paste( "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_county = 'LOS ANGELES' AND code = '", paste( code, "' GROUP BY YearText", sep = "" ), sep = "" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
  #data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_county = 'LOS ANGELES' AND code = 'LIASS' GROUP BY YearText" )
  data.actionquery <- sqlQuery( channel, queryStringCountyLA )
  data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot2 SELECT Year, Count FROM tblPlotYear LEFT JOIN tblTempPlot1 ON tblPlotYear.Year = tblTempPlot1.YearText" )
  data.actionaquery <- sqlQuery( channel, "UPDATE tblTempPlot2 SET Count = 0 WHERE Count IS NULL" )

  if ( scaled == 'FALSE' )
    data.CountyLA <- sqlQuery( channel, "SELECT Year, Count FROM tblTempPlot2 WHERE Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) ORDER BY Year" )
  else
    data.CountyLA <- sqlQuery( channel, "SELECT tblTempPlot2.Year AS Year, ( tblTempPlot2.Count / tblPlotPopulationSum.PopulationSumMM ) AS Count FROM tblTempPlot2 JOIN tblPlotPopulationSum ON tblTempPlot2.Year = tblPlotPopulationSum.Year WHERE tblTempPlot2.Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) AND tblPlotPopulationSum.Region = 'CountyLA' ORDER BY tblTempPlot2.Year" )    

  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )

odbcClose( channel )

return( data.CountyLA )

}

# populate a data frame from an ODBC SQL query
dataQueryCityLA <- function( code, scaled ) {

  channel <- odbcConnect( "uls" )

  queryStringCityLA <- paste( "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_city = 'LOS ANGELES' AND code = '", paste( code, "' GROUP BY YearText", sep = "" ), sep = "" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
  #data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot1 SELECT YearText, count( DISTINCT call_sign ) AS Count FROM tblTimeSeries3 WHERE location_city = 'LOS ANGELES' AND code = 'LIASS' GROUP BY YearText" )
  data.actionquery <- sqlQuery( channel, queryStringCityLA )
  data.actionquery <- sqlQuery( channel, "CREATE TABLE tblTempPlot2 SELECT Year, Count FROM tblPlotYear LEFT JOIN tblTempPlot1 ON tblPlotYear.Year = tblTempPlot1.YearText" )
  data.actionaquery <- sqlQuery( channel, "UPDATE tblTempPlot2 SET Count = 0 WHERE Count IS NULL" )

  if ( scaled == 'FALSE' )
    data.CityLA <- sqlQuery( channel, "SELECT Year, Count FROM tblTempPlot2 WHERE Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) ORDER BY Year" )
  else
    data.CityLA <- sqlQuery( channel, "SELECT tblTempPlot2.Year AS Year, ( tblTempPlot2.Count / tblPlotPopulationSum.PopulationSumMM ) AS Count FROM tblTempPlot2 JOIN tblPlotPopulationSum ON tblTempPlot2.Year = tblPlotPopulationSum.Year WHERE tblTempPlot2.Year IN ( '1994', '1995', '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004' ) AND tblPlotPopulationSum.Region = 'CityLA' ORDER BY tblTempPlot2.Year" )    

  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot2" )
  data.actionquery <- sqlQuery( channel, "DROP TABLE IF EXISTS tblTempPlot1" )

  odbcClose( channel )

  return( data.CityLA )

}


# populate a code description from an ODBC SQL query
queryCodeDescription <- function( code ) {
  channel <- odbcConnect( "uls" )

  queryStringCode <- paste( "SELECT CodeDescription FROM tblPlotHSCode WHERE code = '", paste( code, "'", sep = "" ), sep = "" )
  data.resultset <- sqlQuery( channel, queryStringCode )

  odbcClose( channel )

  return( data.resultset )

}


# design and develop a complete plot
generatePlot <- function( fileRoot, countField, code ) {

  scaled = 'FALSE'

  # get the data from an ODBC query and define the time series
  data.StateCA <- dataQueryStateCA( code, scaled )
    data.tsStateCA <- ts( data = data.StateCA$Count, start = 1994, frequency = 1 )
  data.CountiesSoCal <- dataQueryCountiesSoCal( code, scaled )
    data.tsCountiesSoCal <- ts( data = data.CountiesSoCal$Count, start = 1994, frequency = 1 )
  data.CountyLA <- dataQueryCountyLA( code, scaled )
    data.tsCountyLA <- ts( data = data.CountyLA$Count, start = 1994, frequency = 1 )
  data.CityLA <- dataQueryCityLA( code, scaled )
    data.tsCityLA <- ts( data = data.CityLA$Count, start = 1994, frequency = 1 )

  # get the code description from an ODBC query
  code.resultset <- queryCodeDescription( code )
  code.description = code.resultset$CodeDescription
  code.descriptionString = paste( "[", paste( code.description, "]", sep = "" ), sep = "" )

  # draw the plot (in three different file formats)
  drawPlots( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, fileRoot, paste( "Number of Unique Callsigns by Year\n", paste( code.descriptionString, "\n", sep = "" ), sep = "" ), "Callsigns", "Year" )

}


# design and develop a complete plot (scaled by regional population)
generateScaledPlot <- function( fileRoot, countField, code ) {

  scaled = 'TRUE'

  # get the data from an ODBC query and define the time series
  data.StateCA <- dataQueryStateCA( code, scaled )
    data.tsStateCA <- ts( data = data.StateCA$Count, start = 1994, frequency = 1 )
  data.CountiesSoCal <- dataQueryCountiesSoCal( code, scaled )
    data.tsCountiesSoCal <- ts( data = data.CountiesSoCal$Count, start = 1994, frequency = 1 )
  data.CountyLA <- dataQueryCountyLA( code, scaled )
    data.tsCountyLA <- ts( data = data.CountyLA$Count, start = 1994, frequency = 1 )
  data.CityLA <- dataQueryCityLA( code, scaled )
    data.tsCityLA <- ts( data = data.CityLA$Count, start = 1994, frequency = 1 )

  # get the code description from an ODBC query
  code.resultset <- queryCodeDescription( code )
  code.description = code.resultset$CodeDescription
  code.descriptionString = paste( "[", paste( code.description, "]", sep = "" ), sep = "" )

  # draw the plot (in three different file formats)
  drawPlots( data.tsStateCA, data.tsCountiesSoCal, data.tsCountyLA, data.tsCityLA, fileRoot, paste( "Number of Unique Callsigns (per 1MM population) by Year\n", paste( code.descriptionString, "\n", sep = "" ), sep = "" ), "Callsigns (per 1MM pop.)", "Year" )

}


# draw all the Plots
generatePlot(
  "liass-call_sign",                              # fileRoot
  "call_sign",                                    # which field to count
   "LIASS"                                        # which event code to use
)

generatePlot( "liren-call_sign", "call_sign", "LIREN" )
generateScaledPlot( "liass-call_sign-scaled", "call_sign", "LIASS" )
generateScaledPlot( "liren-call_sign-scaled", "call_sign", "LIREN" )


# exit R
#q()