import java.sql.*;
import java.lang.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class DatabaseServlet2 extends HttpServlet {


    // Initialise and set variables
	String url="jdbc:MySQL:///TESTDB";  // URL specifying the JDBC connection to a MySQL database TESTDB.
	Connection con = null;              // Database connection object
	Statement stmt = null;              // Statement String
	String query;                       // Query String


//-----------------------------------------------------------------------------

	public void doGet(HttpServletRequest req, HttpServletResponse res)
		   throws ServletException, IOException {

        // Set the output characterics for the return data
        res.setContentType("text/html");
		ServletOutputStream out = res.getOutputStream();

        // Establish the database connection
		try {
			// Connect to TESTDB
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,"[database username]","[database password]");
           	stmt = con.createStatement();

            // Build the query statement and retrieve the database records
            query = "SELECT * FROM ProductSales WHERE Year='2001'";
            ResultSet srs = stmt.executeQuery(query);

            // Process the database records and return the Data
            out.println(GraphData(srs));

		} // End try


        // Error handling
		catch(ClassNotFoundException e) {out.println("Could not load database driver: " + e.getMessage());}
		catch(SQLException e) {out.println("SQLException caught: " + e.getMessage());}

        // All finished so close the database connection
		finally {
				 try {if (con != null) con.close();}
			     catch (SQLException e) {}
        }




						   } // End doGet
//-----------------------------------------------------------------------------


    public void doPost(HttpServletRequest request,HttpServletResponse response)
		        throws ServletException, IOException {doGet(request, response);}

//-----------------------------------------------------------------------------

	public static String GraphData(ResultSet srs) {

		String rsltStr = "\n";
		String productname = null;
		String jansales;
		String febsales;
		String marsales;
		String aprsales;
		String maysales;
		String junsales;
		String julsales;
		String augsales;
		String sepsales;
		String octsales;
		String novsales;
		String decsales;

		// Read through the records and construct the return string
        //   ProductX will be set series1
        //   ProductY will be set to series2
        //   ProductZ will be set to series3

		try {

 		  while (srs.next()) {

			jansales = "0.0";
			febsales = "0.0";
			marsales = "0.0";
			aprsales = "0.0";
			maysales = "0.0";
			junsales = "0.0";
			julsales = "0.0";
			augsales = "0.0";
			sepsales = "0.0";
			octsales = "0.0";
			novsales = "0.0";
			decsales = "0.0";

			productname = srs.getString("ProductName");
			q1sales = srs.getString("Quarter1sales");
			q2sales = srs.getString("Quarter2sales");
			q3sales = srs.getString("Quarter3sales");
			q4sales = srs.getString("Quarter4sales");

			jansales = srs.getString("m1sales");
			febsales = srs.getString("m2sales");
			marsales = srs.getString("m3sales");
			aprsales = srs.getString("m4sales");
			maysales = srs.getString("m5sales");
			junsales = srs.getString("m6sales");
			julsales = srs.getString("m7sales");
			augsales = srs.getString("m8sales");
			sepsales = srs.getString("m9sales");
			octsales = srs.getString("m10sales");
			novsales = srs.getString("m11sales");
			decsales = srs.getString("m12sales");

            if (productname.equals("ProductX")) {
				rsltStr = rsltStr +
				          "data1series1        "+ jansales +"\n"+
				          "data2series1        "+ febsales +"\n"+
				          "data3series1        "+ marsales +"\n"+
				          "data4series1        "+ aprsales +"\n"+
				          "data5series1        "+ maysales +"\n"+
				          "data6series1        "+ junsales +"\n"+
				          "data7series1        "+ julsales +"\n"+
				          "data8series1        "+ augsales +"\n"+
				          "data9series1        "+ sepsales +"\n"+
				          "data10series1       "+ octsales +"\n"+
				          "data11series1       "+ novsales +"\n"+
				          "data12series1       "+ decsales +"\n";

			}
            if (productname.equals("ProductY")) {
				rsltStr = rsltStr +
				          "data1series2        "+ jansales +"\n"+
				          "data2series2        "+ febsales +"\n"+
				          "data3series2        "+ marsales +"\n"+
				          "data4series2        "+ aprsales +"\n"+
				          "data5series2        "+ maysales +"\n"+
				          "data6series2        "+ junsales +"\n"+
				          "data7series2        "+ julsales +"\n"+
				          "data8series2        "+ augsales +"\n"+
				          "data9series2        "+ sepsales +"\n"+
				          "data10series2       "+ octsales +"\n"+
				          "data11series2       "+ novsales +"\n"+
				          "data12series2       "+ decsales +"\n";
			}
            if (productname.equals("ProductZ")) {
				rsltStr = rsltStr +
				          "data1series3        "+ jansales +"\n"+
				          "data2series3        "+ febsales +"\n"+
				          "data3series3        "+ marsales +"\n"+
				          "data4series3        "+ aprsales +"\n"+
				          "data5series3        "+ maysales +"\n"+
				          "data6series3        "+ junsales +"\n"+
				          "data7series3        "+ julsales +"\n"+
				          "data8series3        "+ augsales +"\n"+
				          "data9series3        "+ sepsales +"\n"+
				          "data10series3       "+ octsales +"\n"+
				          "data11series3       "+ novsales +"\n"+
				          "data12series3       "+ decsales +"\n";
			}


		  } // end while


		} // End try


        // Error handling
		catch(SQLException e) {rsltStr = rsltStr + "\nSQLException caught: " + e.getMessage();}


         return(rsltStr);
				   	       }


//-----------------------------------------------------------------------------------

} // End class