/**
 *Usage for this class:<br>
 *   java -cp /path/to/mm.mysql.jar:. jdbentry DBNAME TABLENAME
 * <P>
 * This can be used as a standalone "application", or as part of a
 * larger program. It creates a top-level Frame, and connects to
 * a mysql database.table.
 *  (Although in principle, it should be trivially usable with other
 *   databases, if you get the right JDBC driver, and adjust getConn())
 *<P>
 * It looks up existing values for each of the columns, and allows the
 * user to input a new row, either from scratch, or as a mix of
 * new column data and selected old column data
 *<P>
 * Requirements:
 *   The name of a local MySQL database and a table.
 *   The gjt.org JDBC driver in a .jar file (added to your classpath)
 *   Permission for the user running this to modify the database, without
 *     password. (or you could change the code, of course)
 *<P>
 * 
 *<P>
 * This class was originally created for use with a "personal expenses"
 * table. But I'm sure there are many, many other uses :-)
 */

/* You are free to use this class anywhere and any way you want. But
 * it would be nice if you gave me credit somewhere.
 * phil@bolthole.com     http://www.bolthole.com
 * @(#) jdbentry.java 1.3@(#)
 */

import java.sql.*;
import java.awt.*;
import java.util.Vector;
import java.awt.event.*;


public class jdbentry implements WindowListener,ActionListener {

	Connection dbconn=null;
	String dbname,tablename;
	Vector colnames, fields,choices;
	Label status;
	int addtotal=0;

	static void debug(String msg){
		System.out.println(msg);
	}

	TextField getTFAt(int colnum){
		return (TextField)fields.elementAt(colnum);
	}
	Choice getChoiceAt(int colnum){
		return (Choice)choices.elementAt(colnum);
	}
	String getColName(int colnum){
		return (String)colnames.elementAt(colnum);
	}

	/* Looks at "global" vars dbname and tablename. But hardcoded
	 * to connect to mysql running on localhost
	 */
	void getConn(){
		debug(" Connecting to database "+dbname);

		String constring;
		// name of database to connect to
		constring="jdbc:mysql://localhost/"+dbname;
		try{


		/* THis is the good stuff, currently*/
		DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());
		String username=null,password=null;
		dbconn=DriverManager.getConnection (constring,username,
						    password);
		

		} catch (Exception err){
			System.out.println("Got error connecting: "+err.getMessage());
		}
	}

	/* colnum index starts at 0*/
	Panel makePanel(int colnum){
		Panel tmpp = new Panel();
		String colname=getColName(colnum);
		Label collabel=new Label(colname);
		tmpp.add(collabel);
		TextField tinput=new TextField(20);
		tmpp.add(tinput);
		fields.addElement(tinput);
		Choice tmpc=makeChoice(colname);
		tmpp.add(tmpc);
		choices.addElement(tmpc);
		return tmpp;
		
	}

	/* get the first 30 unique names for a column. Make a Choice widget */
	Choice makeChoice(String colname){
		Choice tmpchoice=new Choice();
		tmpchoice.add("Input New Value");

		Statement query=null;
		ResultSet results;
		try {
			query=dbconn.createStatement();
			results=query.executeQuery("select distinct "+colname+
						   " from "+ tablename);
			int colcount=0;
			while(results.next()){
				colcount++;
				String name=results.getString(1);
				tmpchoice.add(name);
				if(colcount>30)
					break;
			}
			query.close();
		} catch (SQLException err){
			debug("cannot look up values; "+err.getMessage());
			quit();
		}

		return tmpchoice;
	}

	/* Make an input panel for each column in the database */
	void makeSubPanels(Panel topp){
		for(int pcount=0;pcount<colnames.size();pcount++){
			Panel tmpp=makePanel(pcount);
			topp.add(tmpp);
		}
	}

	void makeControlPanel(Panel topp){
		Panel cp=new Panel();
		Button add,find,quit;
		add=new Button("Add/Replace");
		add.addActionListener(this);
		find=new Button("Find");
		find.addActionListener(this);
		quit=new Button("Quit");
		quit.addActionListener(this);
		cp.add(add);
		cp.add(find);
		cp.add(quit);
		topp.add(cp);
	}

	/* Go through all the column fields, and do the deed*/
	void addData(){
		StringBuffer values=new StringBuffer("replace into "+tablename+
						     " values (");
		for(int ccount=0;ccount<colnames.size();ccount++){
			TextField tf=getTFAt(ccount); 
			Choice ch=getChoiceAt(ccount);
			String cstring=ch.getSelectedItem();
			if(cstring.equals("Input New Value")){
				cstring=tf.getText();
			}
			if(ccount>0){
				values.append(",");
			}
			values.append("\""+cstring+"\"");
		}
		values.append(")");

		debug(" INSERT: "+values.toString());
		Statement query=null;
		try {
			query=dbconn.createStatement();
			query.executeUpdate(values.toString());
			query.close();
		} catch (SQLException err){
			debug("cannot do insert; "+err.getMessage());
			status.setText(err.getMessage());
			return;
		}

		addtotal++;
		status.setText("Added data ("+addtotal+" entries so far)");
	}


	/* Use the given values to find a specific (partial) entry*/
	void findData(){
		StringBuffer values=new StringBuffer("select * from "+tablename+
						     " where ");
		for(int ccount=0;ccount<colnames.size();ccount++){
			String colname=getColName(ccount);
			TextField tf=getTFAt(ccount); 
			Choice ch=getChoiceAt(ccount);
			String cstring=ch.getSelectedItem();
			if(cstring.equals("Input New Value")){
				cstring=tf.getText();
			}

			if(cstring.length()==0)
				continue;

			if(ccount>0){
				values.append(" and ");
			}
			values.append(colname + " = \""+cstring+"\"");
		}

		debug(" FIND: "+values.toString());
		Statement query=null;
		ResultSet results;
		
		try {
			query=dbconn.createStatement();
			results=query.executeQuery(values.toString());

			if(!results.next()){
				status.setText("ERROR: no match");
				query.close();
				return;
			}

			// We've read the data, so now go update what
			// the user sees.
			for(int ccount=0;ccount<colnames.size();ccount++){
				String val=results.getString(ccount+1);
				TextField tf=getTFAt(ccount);
				tf.setText(val);
				Choice ch=getChoiceAt(ccount);
				ch.select(0);
			}

			if(results.next()){
				status.setText("WARNING: multiple matches");
			} else {
				status.setText("Existing data matched");
			}
			
			query.close();
		} catch (SQLException err){
			debug("cannot do find; "+err.getMessage());
			status.setText(err.getMessage());
			return;
		}

	}


	/* prints out debug of rownames, and fills in Vector */
	void getRowNames(){
		
		Statement query=null;
		ResultSet results;
		colnames=new Vector();
		fields=new Vector();
		choices=new Vector();

		try {
			query=dbconn.createStatement();
			results=query.executeQuery("show columns from "+
							tablename);
			int colcount=0;
			while(results.next()){
				colcount++;
				String name=results.getString(1);
				colnames.addElement(name);
				//debug("Name of col "+colcount+"="+ name);
				
			}
			query.close();
		} catch (SQLException err){
			debug("cannot do query; "+err.getMessage());
			quit();
		}

	}

	public void actionPerformed(ActionEvent event){
		Object source=event.getSource();
		if(!(source instanceof Button))
			return;

		String cmd=event.getActionCommand();
		if(cmd.equals("Quit"))
			quit();
		if(cmd.equals("Add/Replace")){
			addData();
			return;
		}
		if(cmd.equals("Find")){
			findData();
			return;
		}

		debug("ERROR: Do not recognize action "+cmd);
		
	}

	/************************************************************/
	/************************************************************/

	/*----------------------------------------*/
	public void quit(){
		if(dbconn!=null){
			try{
			dbconn.close();
			} catch (Exception err){ }
		}
		System.exit(0);
	}


	/**************************************************\
	 * ALL SEVEN of these stupid things are needed,
	 * JUST so we can respond properly to kill-window. Arrggg
	\**************************************************/
	public void windowClosing(WindowEvent evt)
	{
		quit();
	}
	public void windowOpened(WindowEvent evt)   {    }
	public void windowClosed(WindowEvent evt)   {   }
	public void windowActivated(WindowEvent evt){   }
	public void windowDeactivated(WindowEvent evt){   }
	public void windowIconified(WindowEvent evt)  {   }
	public void windowDeiconified(WindowEvent evt){   }

	/**
	 * Give the name of a local MySQL database, and a table to reference.
	 * It will pop up a top-level Frame for the user to tweak data with.
	 */
	public jdbentry(String dname, String tname){
		dbname=dname;
		tablename=tname;
		Frame topwin = new Frame("Input data into database "+dbname);
		Panel toppanel= new Panel(new GridLayout(0,1));
		topwin.add(toppanel);
		Label label=new Label("Table="+tablename);
		toppanel.add(label);

		getConn();
		if(dbconn==null){
			debug("dbconn is null?? (ERROR)");
			quit();
		} else {
			debug("got connection to database");
		}

		
		getRowNames();
		makeSubPanels(toppanel);
		makeControlPanel(toppanel);
		status=new Label("Press Add to add data");
		toppanel.add(status);

		topwin.pack();
		topwin.addWindowListener(this);

		topwin.setVisible(true);
		

	}

	public static void main(String args[]){
		if(args.length<2){
			debug("ERROR: Need database name and table name");
			return;
		}

		jdbentry Me = new jdbentry(args[0],args[1]);
	}
}