| 1 |
import java.sql.*; |
| 2 |
import java.util.*; |
| 3 |
import java.io.*; |
| 4 |
|
| 5 |
// DBReporter class - establishes the database connection and is |
| 6 |
// used to run all of the reports. |
| 7 |
public class DBReporter { |
| 8 |
|
| 9 |
// Constructor. |
| 10 |
public DBReporter(long startTime, long endTime) { |
| 11 |
_startTime = startTime; |
| 12 |
_endTime = endTime; |
| 13 |
} |
| 14 |
|
| 15 |
// Runs all of the reports for each distinct machine name. |
| 16 |
public void runReports(ReportList reports, String webDir) throws SQLException, Exception { |
| 17 |
|
| 18 |
System.out.println("Preparing reports ..."); |
| 19 |
|
| 20 |
// Get the MySQL DB driver. |
| 21 |
try { |
| 22 |
Class.forName("org.gjt.mm.mysql.Driver").newInstance(); |
| 23 |
} |
| 24 |
catch (Exception e){ |
| 25 |
throw new Exception("Could not find the MySQL driver: " + e); |
| 26 |
} |
| 27 |
|
| 28 |
try { |
| 29 |
_conn = DriverManager.getConnection("jdbc:mysql://localhost/co600_10_db?user=co600_10&password=bung-i-scream"); |
| 30 |
_stmt = _conn.createStatement(); |
| 31 |
} |
| 32 |
catch (SQLException e){ |
| 33 |
throw new SQLException("Could not connect to the database: " + e); |
| 34 |
} |
| 35 |
|
| 36 |
// This is essential to prevent temporary tables running out of |
| 37 |
// physical memory for large reports. With this set to 1, all |
| 38 |
// temporary tables shall be written to disk. This is a daily |
| 39 |
// task, so we shall not be too concerned about the reduced |
| 40 |
// performance. |
| 41 |
try { |
| 42 |
_stmt.executeUpdate("SET SQL_BIG_TABLES = 1;"); |
| 43 |
} |
| 44 |
catch (SQLException e){ |
| 45 |
System.out.println("WARNING: Could not set MySQL to write temporary tables to disk."); |
| 46 |
System.out.println("Some of the following i-scream reports may fail if there is insufficient memory."); |
| 47 |
} |
| 48 |
|
| 49 |
// Get a list of distinct machine names. |
| 50 |
LinkedList machines = this.getDistinctMachines(); |
| 51 |
|
| 52 |
System.out.println("Readying reports for " + machines.size() + " machines."); |
| 53 |
|
| 54 |
// Produce all of the reports for each machine. |
| 55 |
ReportMaker rm = new ReportMaker(_stmt, webDir, DateUtils.shortDateString(_startTime), _startTime, _endTime); |
| 56 |
Iterator machineIt = machines.iterator(); |
| 57 |
while (machineIt.hasNext()) { |
| 58 |
String machine = (String)machineIt.next(); |
| 59 |
rm.produce(reports, machine); |
| 60 |
} |
| 61 |
|
| 62 |
|
| 63 |
|
| 64 |
// Now output the files for PHP searching. |
| 65 |
// - print report list stuff out to reports.inc |
| 66 |
System.out.println("Writing report.inc"); |
| 67 |
File reportInc = new File(new File(webDir), "report.inc"); |
| 68 |
try { |
| 69 |
BufferedWriter bw = new BufferedWriter(new FileWriter(reportInc)); |
| 70 |
Iterator reportIt = reports.iterator(); |
| 71 |
while (reportIt.hasNext()) { |
| 72 |
Report report = (Report)reportIt.next(); |
| 73 |
bw.write("<option value=\"" + report.getReportField() + "\">" + report.getFriendlyName() + "</option>"); |
| 74 |
} |
| 75 |
bw.flush(); |
| 76 |
bw.close(); |
| 77 |
} |
| 78 |
catch (IOException e) { |
| 79 |
System.out.println("Unable to create" + reportInc); |
| 80 |
} |
| 81 |
|
| 82 |
|
| 83 |
// - print machine list stuff out to machine_name.inc |
| 84 |
System.out.println("Writing machine_name.inc"); |
| 85 |
File machineInc = new File(new File(webDir), "machine_name.inc"); |
| 86 |
try { |
| 87 |
BufferedWriter bw = new BufferedWriter(new FileWriter(machineInc)); |
| 88 |
machineIt = machines.iterator(); |
| 89 |
while (machineIt.hasNext()) { |
| 90 |
String machine = (String)machineIt.next(); |
| 91 |
bw.write("<option value=\"" + machine + "\">" + machine + "</option>"); |
| 92 |
} |
| 93 |
bw.flush(); |
| 94 |
bw.close(); |
| 95 |
} |
| 96 |
catch (IOException e) { |
| 97 |
System.out.println("Unable to create" + machineInc); |
| 98 |
} |
| 99 |
|
| 100 |
|
| 101 |
// - print current day out to day.inc |
| 102 |
System.out.println("Writing day.inc"); |
| 103 |
File dayInc = new File(new File(webDir), "day.inc"); |
| 104 |
try { |
| 105 |
BufferedWriter bw = new BufferedWriter(new FileWriter(dayInc)); |
| 106 |
bw.write("<? $day = \"" + DateUtils.shortDateString(_startTime) + "\"; ?>"); |
| 107 |
bw.flush(); |
| 108 |
bw.close(); |
| 109 |
} |
| 110 |
catch (IOException e) { |
| 111 |
System.out.println("Unable to create" + dayInc); |
| 112 |
} |
| 113 |
|
| 114 |
|
| 115 |
} |
| 116 |
|
| 117 |
|
| 118 |
// Returns a list of distinct machine names. |
| 119 |
private LinkedList getDistinctMachines() throws SQLException { |
| 120 |
|
| 121 |
LinkedList machines = new LinkedList(); |
| 122 |
try { |
| 123 |
ResultSet rs = _stmt.executeQuery("SELECT DISTINCT machine_name from ipacket WHERE receipt_date >= " + _startTime + " AND receipt_date <= " + _endTime + ";"); |
| 124 |
while (rs.next()) { |
| 125 |
String machine_name = rs.getString("machine_name"); |
| 126 |
machines.add(machine_name); |
| 127 |
} |
| 128 |
rs.close(); |
| 129 |
} |
| 130 |
catch (SQLException e) { |
| 131 |
throw new SQLException("Unable to find distinct machine names: " + e); |
| 132 |
} |
| 133 |
|
| 134 |
return machines; |
| 135 |
} |
| 136 |
|
| 137 |
|
| 138 |
|
| 139 |
private long _startTime; |
| 140 |
private long _endTime; |
| 141 |
|
| 142 |
private Connection _conn = null; |
| 143 |
private Statement _stmt = null; |
| 144 |
|
| 145 |
} |
