/*
 * @(#)Guestbook.java: Porgram to load saved e-mail files containing British Plate 
 * Armour Society (BPAS) guestbook entries into the Database.
 *
 * Saved files are in the format below:
 * #########################
 * 
From: SVR4 nobody uid [nobody@http-4.homepages.demon.net] on behalf of
webmaster@dethorpe.demon.co.uk
Sent: 13 August 2003 03:08
To: webmaster@dethorpe.demon.co.uk
Subject: http://www.dethorpe.demon.co.uk/Guestbook.html

This data is mailed from your web page http://www.dethorpe.demon.co.uk/Guestbook.html
The host that sent this request was 80.1.36.22 [80.1.36.22] (although if a proxy was used, you will get the address of the proxy instead) The user's browser reported itself to be
	Mozilla/4.0 (compatible; MSIE 5.0; Windows 98; DigExt)

----- BEGIN FORM -----
VTI-GROUP	0
Name	A name
email	email@provider.com
GroupName	none
Membership	ON
Message	Message text goes here,
multiple lines allowed

I would also be greatfull if you could tell me if chin mail and plate armour parts would be exceptable to start, legs, arms ect, and helm of course.

----- END FORM -----
 *
 *
 *
 * Copyright (C) 2004 Craig Nicholas
 * This program is free software; you can redistribute it and/or modify it under the terms of the GNU 
 *
 * General Public License as published by the Free Software Foundation; either version 2 of the 
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
 * See the GNU General Public License for more details.

 * You should have received a copy of the GNU General Public License along with this program; 
 * if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
 * Boston, MA 02111-1307 USA
 *
 * Craig Nicholas
 * Website: www.britishplate.org.uk
 *
 */
package com.dethorpe.guestbook;

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.io.*;
import java.util.HashMap;
import java.text.*;
//import gbFilter.*;


class Guestbook {

    static String url = "jdbc:odbc:bpas";	
    static private PreparedStatement stmt = null;    
 
    /* 
     * Method to log SQL exceptions
     */
	private static void logSqlException(SQLException ex)
	{
		System.out.println("\n--- SQLException caught ---\n");
		while (ex != null) {
			System.out.println("Message:   "
        	                           + ex.getMessage ());
			System.out.println("SQLState:  "
        	                           + ex.getSQLState ());
			System.out.println("ErrorCode: "
        	                           + ex.getErrorCode ());
			ex = ex.getNextException();
			System.out.println("");
		}
	}

	/*
	 * Method to set an indicator field bind variable
	 */
	public static void setIndicator(String ind, int field) throws SQLException
	{
		if (ind == null || ind.equals("NO"))
			stmt.setInt(field,0);
		else
			stmt.setInt(field,1);
	}
	
	/*
	 * Method to store the guestbook entry fields into a new row in the database
	 */
	public static boolean storeEntry(HashMap hm)
	{
		// Hash map keys
		String dateKey 		= "date";
		String nameKey 		= "Name";
		String emailKey 	= "email";
		String groupNameKey = "GroupName";	
		String msgKey 		= "Message";
		String groupIndKey 	= "GroupCheck";
		String bpasIndKey 	= "Membership";
		
		String value;
		
	   // create and execute insert to database
       try {
			// set bind variables
			
			// DATE is mandatory
			value = (String)hm.get(dateKey);
	
			if (value != null)
			{
				DateFormat df = new SimpleDateFormat("dd MMMM yyyy HH:mm");
				Timestamp time = new Timestamp(df.parse(value).getTime());
				stmt.setTimestamp(1,time);
				System.out.println("Date is: " + time.toString());
			}
			else
				throw new Exception("Date missing from guestbook data");
	
			// NAME is mandatory
			value = (String)hm.get(nameKey);
	
			if (value != null)
				stmt.setString(2,value);
			else
				throw new Exception("Name missing from guestbook data");
				
			// EMAIL is optional
			stmt.setString(3,(String)hm.get(emailKey));
			
			// OtherGroupInd
			setIndicator((String)hm.get(groupIndKey),4);
		
			// OTHER GROUP is optional
			stmt.setString(5,(String)hm.get(groupNameKey));

			// BpasInd
			setIndicator((String)hm.get(bpasIndKey),6);
				
			// MESSAGE is mandatory
			value = (String)hm.get(msgKey);
	
			if (value != null)
			{
				System.out.println( "About to set Message to : " + value );
				stmt.setAsciiStream(7,new ByteArrayInputStream(value.getBytes()), value.length() );
			}
			else
				throw new Exception("Message missing from guestbook data");
			
			// execute the statement
			System.out.println( "About to execute insert");
			stmt.executeUpdate();
            
        } catch(SQLException ex) {
           	System.err.println("Failed to execute insert");
           	logSqlException(ex);
           	return false;
       	} catch(Exception e) {
           	System.err.println("Failed to insert data");
            System.err.println("Exception: " + e.getMessage());
           	return false;
        }
       	
       	return true;
	}
	
	public static void main(String args[]) {
		System.out.println("Starting Guestbook...");
		
		Connection con = null;
        String insertString;
        insertString = "INSERT INTO Guestbook (MessageDate, Name, email, OtherGroupInd, OtherGroupName, BpasInfoInd, Message) " +
                           "VALUES (?,?,?,?,?,?,?);";
		HashMap hm;
		
		// get guestbook dir from environment
		String gbDirName = System.getProperty("MY_DOCUMENTS");
		if (gbDirName == null )
		{
		    System.err.println("MY_DOCUMENTS environment variable not set");
		    System.exit(1);
		}
		else
		{
			gbDirName += "\\BPAS\\Guestbook mails"; // add rest of path
		}
		
		// Create class for DB connection
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
            System.exit(1);
        }

		// Connect to the database
        try {
            con = DriverManager.getConnection(url, "", "");
            System.out.println ("Connected to: " + url);
        } catch(SQLException ex) {
            System.err.println("Failed to Connect to database");
            logSqlException(ex);
            System.exit(1);
        }

       	// Prepare the insert statement
        try {
			stmt = con.prepareStatement(insertString);
	    } catch(SQLException ex) {
           	System.err.println("Failed to prepare insert");
           	logSqlException(ex);
            System.exit(1);
        }
        
        
		// open guestbook directory
        File gbDir = new File(gbDirName);
        
        if (!gbDir.isDirectory())
        {
        	System.err.println (gbDirName + " Is not a directory");
        	System.exit(1);
        }
        
        // Get list of guestbook email files in directory
        String [] gbFiles = gbDir.list(new gbFilter());
        
        // Loop through guestbook mail files
        for ( int i = 0; i < gbFiles.length; i++)
        {
        	System.out.println("Processing File: "+ gbFiles[i]);
        	
        	// Extract the form data from the file
    	    try {
    	    	hm = (new gbFile(gbDirName, gbFiles[i])).getFormFields();
        	} catch (Exception e)
        	{
        		System.out.println ("Failed to extract data from file '" + 
        							 gbFiles[i] +
        							 "' Skipping file.");
        		System.out.println ("   Exception was: " + e.getMessage());
        		continue;
        	}
        	
        	// insert the form data into the database
       		if (!storeEntry (hm))
        	{
        		System.out.println ("Failed to store data from file '" + 
        							 gbFiles[i] +
        							 "' Skipping file.");
        		continue;
        	}
        	
        	// if we get here it worked so remove the file
        	// (production system would archive it)
        	System.out.println("Deleteing File " + gbDirName + "\\" + gbFiles[i]);
        	File df = new File (gbDirName + "\\" + gbFiles[i]);
        	
        	if (!df.delete())
        		System.err.println("Failed to delete file '"+ gbDirName + "\\" + gbFiles[i]);
        }

		// Close the statement and database connection         
        
        try {
        	stmt.close();
            con.close();
        } catch(SQLException ex) {
            System.err.println("Failed to close statement or connection");
            logSqlException(ex);
        }
        
        System.out.println("\n ### Guestbook mails imported to DB, press Enter to exit\n");
        try { 
        int key = System.in.read();
    	}  catch (Exception e) {};
	}
	
}