//Written by Jane Jakeman 2010 copyright
//This file is called from the html form action and places the
//data input to the html form to the mysql database
//using java servlets, a jdbc database connection
//and a mysql server side database
//hosted by a server with Apache Tomcat capability
//after this file is compiled to a Class file using command prompt
//javac Entryform.java from the correct directory in command prompt
//All this work has been written painfully by Jane Jakeman

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

public class EntryForm extends HttpServlet
{
  public void init(ServletConfig config) throws ServletException
  {
    super.init(config);
  }
  //process the doPost request
  public void doPost(HttpServletRequest req, HttpServletResponse res) 
                                 throws ServletException, IOException
  {
    String connectionURL = "jdbc:mysql://localhost/databaseNameRemoved";
    Connection connection=null;
    res.setContentType("text/html");
    PrintWriter out = res.getWriter();
   //get the variables from the entry form html fields
   //this data is destined eventually for TBLPERSON
    String fname = req.getParameter("firstName");  
    String sname = req.getParameter("secondName");
    String address1 = req.getParameter("houseNum");
    String address2 = req.getParameter("streetName");
    String address3 = req.getParameter("town");
	String postcode = req.getParameter("postcode");
	String phone = req.getParameter("phoneNum");
	String personEmail = req.getParameter("personEmail");
  //this data is destined eventually for TBLENTRY
	int nPeople = Integer.parseInt(req.getParameter("numPeople"));
	String oType = req.getParameter("orgType");                   
	String eCat = req.getParameter("entryCat");                   
	String vType = req.getParameter("vehType");
	String vReg = req.getParameter("vehReg");
	int pPart = Integer.parseInt(req.getParameter("prevPart"));
 
     try 
	 {
      // Load the database driver
      Class.forName("org.gjt.mm.mysql.Driver");
      // Get a Connection to the database
      connection = DriverManager.getConnection(connectionURL, "usernameRemoved", "passwordRemoved"); 
      //prepare the statement
	  String sql = "insert into TBLPERSON values (?,?,?,?,?,?,?,?,?)";//placeholders
      PreparedStatement pst = connection.prepareStatement(sql);
	  //INSERT TO TBL PERSON
      pst.setInt(1,0);//setting auto_increment to default 0, db will create next number 
	  pst.setString(2, fname);
      pst.setString(3, sname);
      pst.setString(4, address1);
      pst.setString(5, address2);
      pst.setString(6, address3);
	  pst.setString(7, postcode);
	  pst.setString(8, phone);
	  pst.setString(9, personEmail);

	 // test the data was input
      int numRowsChanged = pst.executeUpdate();
    
	    Statement stmt = null;
        ResultSet rs2 = null;
	    stmt = connection.createStatement();
        rs2 = stmt.executeQuery("SELECT personId FROM TBLPERSON ORDER BY personId DESC LIMIT 1");
     //got the last number in personId
        int b = 0;
     // display recordset
        while(rs2.next()) 
		{
	    String a=(rs2.getObject(1).toString());
	    b = Integer.parseInt(a);
        out.print(b);//shows last number
        }
    //Now using the last number (variable b) to insert as a relationship link in TBLENTRY, column 2 personId
      String sql2 ="insert into TBLENTRY values (?,?,?,?,?,?,?,?)";
	  PreparedStatement pst2 = connection.prepareStatement(sql2);
	//insert to TBLENTRY
	  pst2.setInt(1,0);
	  pst2.setInt(2,b);//this is personId from TBLPERSON
	  pst2.setInt(3,nPeople);
	  pst2.setString(4,oType);
	  pst2.setString(5,eCat);
	  pst2.setString(6,vType);
	  pst2.setString(7,vReg);
	  pst2.setInt(8,pPart);
  //TEST SHOWS IT WORKS
	  int numRowsChanged2 = pst2.executeUpdate();
  //print user friendly message 
      out.println(" Hello  ");
      out.println(" "+fname+" ");
      out.println(" '"+sname+"'");
	  out.println(" <br /><br />Your entry form details have been sent.  Thank you!<br /><br />");
	  out.print("<p\"><a href=\"");
      out.print( "http://www.completewebsites.biz" );
      out.print("\">Back</a></p>");

      pst.close();
    }
    catch(ClassNotFoundException e)
	{
      out.println("Couldn't load database driver: " + e.getMessage());
    }
    catch(SQLException e)
	{
      out.println("SQLException caught: " + e.getMessage());
    }
    catch (Exception e)
	{
      out.println(e);
    } finally {
      // close the database connection
               try {
                     if (connection != null) connection.close();
                   }
					catch (SQLException ignored)
					{
						out.println(ignored);
					}
			  }
	   //Connection con = null;//2nd connection
	   PreparedStatement pst = null;
	   PreparedStatement pst2 = null;
       Statement stmt = null;//2nd statement
       ResultSet rs2 = null; //2nd result set
 
try {
        if(rs2 != null)
		{
          rs2.close();
          rs2 = null;
        }
        if(stmt != null) 
		{
          stmt.close();
          stmt = null;
        }
		if(pst !=null)
		{
		pst.close();
		pst = null;
        }
		if(pst2 !=null)
		{
		pst2.close();
		pst2 = null;
		}
	
	} catch (SQLException e) {}
		out.close(); 
   }
 }