reports/DBReporter2/DBReporter.java

import java.sql.*;
import java.util.*;
import java.io.*;

// DBReporter class - establishes the database connection and is
// used to run all of the reports.
public class DBReporter {
    
    // Constructor.
    public DBReporter(long startTime, long endTime) {
        _startTime = startTime;
        _endTime = endTime;
    }

    // Runs all of the reports for each distinct machine name.    
    public void runReports(ReportList reports, String webDir) throws SQLException, Exception {
        
        System.out.println("Preparing reports ...");

        // Get the MySQL DB driver.
        try {
            Class.forName("org.gjt.mm.mysql.Driver").newInstance();
        }
        catch (Exception e){
            throw new Exception("Could not find the MySQL driver: " + e);
        }

        try {
            _conn = DriverManager.getConnection("jdbc:mysql://localhost/co600_10_db?user=co600_10&password=bung-i-scream");
            _stmt = _conn.createStatement();
        }
        catch (SQLException e){
            throw new SQLException("Could not connect to the database: " + e);
        }
        
        // This is essential to prevent temporary tables running out of
        // physical memory for large reports.  With this set to 1, all
        // temporary tables shall be written to disk.  This is a daily
        // task, so we shall not be too concerned about the reduced
        // performance.
        try {
            _stmt.executeUpdate("SET SQL_BIG_TABLES = 1;");
        }
        catch (SQLException e){
            System.out.println("WARNING: Could not set MySQL to write temporary tables to disk.");
            System.out.println("Some of the following i-scream reports may fail if there is insufficient memory.");
        }
        
        // Get a list of distinct machine names.
        LinkedList machines = this.getDistinctMachines();
        
        System.out.println("Readying reports for " + machines.size() + " machines.");
        
        // Produce all of the reports for each machine.
        ReportMaker rm = new ReportMaker(_stmt, webDir, DateUtils.shortDateString(_startTime), _startTime, _endTime);
        Iterator machineIt = machines.iterator();
        while (machineIt.hasNext()) {
            String machine = (String)machineIt.next();
            rm.produce(reports, machine);
        }
        
        
        
        // Now output the files for PHP searching.
        // - print report list stuff out to reports.inc
        System.out.println("Writing report.inc");
        File reportInc = new File(new File(webDir), "report.inc");
        try {
            BufferedWriter bw = new BufferedWriter(new FileWriter(reportInc));
            Iterator reportIt = reports.iterator();
            while (reportIt.hasNext()) {
                Report report = (Report)reportIt.next();
                bw.write("<option value=\"" + report.getReportField() + "\">" + report.getFriendlyName() + "</option>");
            }
            bw.flush();
            bw.close();
        }
        catch (IOException e) {
            System.out.println("Unable to create" + reportInc);
        }
        
        
        // - print machine list stuff out to machine_name.inc
        System.out.println("Writing machine_name.inc");
        File machineInc = new File(new File(webDir), "machine_name.inc");
        try {
            BufferedWriter bw = new BufferedWriter(new FileWriter(machineInc));
            machineIt = machines.iterator();
            while (machineIt.hasNext()) {
                String machine = (String)machineIt.next();
                bw.write("<option value=\"" + machine + "\">" + machine + "</option>");
            }
            bw.flush();
            bw.close();
        }
        catch (IOException e) {
            System.out.println("Unable to create" + machineInc);
        }
        
        
        // - print current day out to day.inc
        System.out.println("Writing day.inc");
        File dayInc = new File(new File(webDir), "day.inc");
        try {
            BufferedWriter bw = new BufferedWriter(new FileWriter(dayInc));
            bw.write("<? $day = \"" + DateUtils.shortDateString(_startTime) + "\"; ?>");
            bw.flush();
            bw.close();
        }
        catch (IOException e) {
            System.out.println("Unable to create" + dayInc);
        }
        
        
    }
    
    
    // Returns a list of distinct machine names.
    private LinkedList getDistinctMachines() throws SQLException {
        
        LinkedList machines = new LinkedList();
        try {
            ResultSet rs = _stmt.executeQuery("SELECT DISTINCT machine_name from ipacket WHERE receipt_date >= " + _startTime + " AND receipt_date <= " + _endTime + ";");
            while (rs.next()) {
                String machine_name = rs.getString("machine_name");
                machines.add(machine_name);
            }
            rs.close();
        }
        catch (SQLException e) {
            throw new SQLException("Unable to find distinct machine names: " + e);
        }
        
        return machines;
    }
    
    
    
    private long _startTime;
    private long _endTime;
    
    private Connection _conn = null;
    private Statement _stmt = null;
    
}
Revision:	1.6
Committed:	Fri Mar 23 23:47:28 2001 UTC (24 years, 9 months ago) by pjm2
Branch:	MAIN
CVS Tags:	HEAD
Changes since 1.5:	+0 -0 lines
State:	*FILE REMOVED*
Log Message:	I thought I'd deleted these weeks ago(!)
#	User	Rev	Content
1	pjm2	1.1	import java.sql.*;
2			import java.util.*;
3	pjm2	1.4	import java.io.*;
4	pjm2	1.1
5	pjm2	1.2	// DBReporter class - establishes the database connection and is
6			// used to run all of the reports.
7	pjm2	1.1	public class DBReporter {
8
9	pjm2	1.2	// Constructor.
10	pjm2	1.1	public DBReporter(long startTime, long endTime) {
11			_startTime = startTime;
12			_endTime = endTime;
13			}
14	pjm2	1.2
15			// Runs all of the reports for each distinct machine name.
16	pjm2	1.3	public void runReports(ReportList reports, String webDir) throws SQLException, Exception {
17	pjm2	1.1
18			System.out.println("Preparing reports ...");
19
20	pjm2	1.2	// Get the MySQL DB driver.
21	pjm2	1.1	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	pjm2	1.2
28	pjm2	1.1	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	pjm2	1.2	// Get a list of distinct machine names.
50	pjm2	1.1	LinkedList machines = this.getDistinctMachines();
51
52			System.out.println("Readying reports for " + machines.size() + " machines.");
53
54	pjm2	1.2	// Produce all of the reports for each machine.
55	pjm2	1.3	ReportMaker rm = new ReportMaker(_stmt, webDir, DateUtils.shortDateString(_startTime), _startTime, _endTime);
56	pjm2	1.1	Iterator machineIt = machines.iterator();
57			while (machineIt.hasNext()) {
58			String machine = (String)machineIt.next();
59			rm.produce(reports, machine);
60			}
61	pjm2	1.4
62
63
64			// Now output the files for PHP searching.
65			// - print report list stuff out to reports.inc
66	pjm2	1.5	System.out.println("Writing report.inc");
67			File reportInc = new File(new File(webDir), "report.inc");
68	pjm2	1.4	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	pjm2	1.1
115			}
116
117	pjm2	1.2
118			// Returns a list of distinct machine names.
119	pjm2	1.1	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	pjm2	1.2	rs.close();
129	pjm2	1.1	}
130			catch (SQLException e) {
131			throw new SQLException("Unable to find distinct machine names: " + e);
132			}
133
134			return machines;
135			}
136	pjm2	1.2
137
138	pjm2	1.1
139			private long _startTime;
140			private long _endTime;
141
142			private Connection _conn = null;
143			private Statement _stmt = null;
144
145			}