//  Sample Database Interface program

import java.sql.*;
import java.awt.*;
import java.awt.event.*;	
import javax.swing.*;
import javax.swing.table.*;
import java.util.*;
import java.text.*;

public class SyllabTest extends JFrame {
   private DataPanel screenvar;
   private JTextArea msgout;
   private Connection dbconn;

   public SyllabTest() {
      super( "SYLLABUSES TEST PROGRAM" );
	//  Set up GUI environment
     	Container p = getContentPane();
      screenvar = new DataPanel();
      msgout = new JTextArea( 8, 40 );
	p.setLayout( new FlowLayout() );
      p.add( new JScrollPane( screenvar ) );
      p.add( new JScrollPane(msgout) );
		
	// Set up database connection
	try {
         String url = "jdbc:odbc:TestDB";
         Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
         dbconn = DriverManager.getConnection( url );
         msgout.append( "Connection successful\n" );
      }
      catch ( ClassNotFoundException cnfex ) {
         // process ClassNotFoundExceptions here
         cnfex.printStackTrace();
         msgout.append( "Connection unsuccessful\n" +
                        cnfex.toString() );
      }
      catch ( SQLException sqlex ) {
         // process SQLExceptions here
         sqlex.printStackTrace();
         msgout.append( "Connection unsuccessful\n" +
                        sqlex.toString() );
      }
      catch ( Exception excp ) {
         // process remaining Exceptions here
         excp.printStackTrace();
         msgout.append( excp.toString() );
      }

      // Complete GUI

      ButtonPanel controls = new 
	   ButtonPanel( dbconn, screenvar, msgout);
      p.add( controls );
	setSize( 500, 425 );
	show();
   }

    public static void main( String args[] ) {
       SyllabTest syl = new SyllabTest();

       syl.addWindowListener(
          new WindowAdapter() {
             public void windowClosing( WindowEvent e ) {
               System.exit( 0 );
             }
          }
       );
    }
}   





class AddRecord implements ActionListener {
   private DataPanel screenvar;
   private JTextArea msgout;
   private Connection dbconn;

   public AddRecord( Connection dbc, DataPanel scv, JTextArea msg ) {
      dbconn = dbc;
      screenvar = scv;
      msgout = msg;
   }

   public void actionPerformed( ActionEvent e ) {
      try {
         Statement statement = dbconn.createStatement();
	   //  strip out non-numerics from phone number
	   String numstrg = new String();
	   for (int i = 0; i < screenvar.course_id.getText().length(); i++) {  
            if ( screenvar.course_id.getText().charAt(i)>='0' && 
	         screenvar.course_id.getText().charAt(i)<='9') { 
	            numstrg += screenvar.course_id.getText().substring(i,i+1);
		   }
	      }
         if ( !screenvar.course_e.getText().equals( "" ) && 
            !screenvar.course_j.getText().equals( "" ) &&
		!numstrg.equals("") ) {
               String query = "INSERT INTO Table1 (" +
                  "course_j, course_e, course_url, inst_name, " +
                  "department, inst_hp, tel_no, " +
                  "email, course_id" +
                  ") VALUES ('" +
                  screenvar.course_j.getText() + "', '" + 
                  screenvar.course_e.getText()  + "', '" + 
                  screenvar.course_url.getText() + "', '" + 
                  screenvar.inst_name.getText()  + "', '" + 
                  screenvar.department.getText() + "', '" + 
                  screenvar.inst_hp.getText()   + "', '" + 
                  screenvar.tel_no.getText() + "', '" + 
                  screenvar.email.getText() + "', '" + 
                  numstrg                   + "')";
               msgout.append( "\nSending query: " + 
                  dbconn.nativeSQL( query ) + "\n" );
               int result = statement.executeUpdate( query );
               if ( result == 1 ){
                  // read just inserted record to obtain id field
			msgout.append( "\nInsertion successful\n" );
		      try {
		         query = "SELECT * FROM Table1 WHERE course_id='" +
		            numstrg + "'";
		         ResultSet rs = statement.executeQuery( query );
		         rs.next();
		         screenvar.id.setText(String.valueOf(rs.getInt(1)));
		      }
		      catch ( SQLException sqlex ) {
		         msgout.append( sqlex.toString() );
		      }
		}     
            else {
               msgout.append( "\nInsertion failed\n" );
               screenvar.course_j.setText( "" );
               screenvar.course_e.setText( "" );
               screenvar.course_url.setText( "" );
               screenvar.inst_name.setText( "" );
               screenvar.department.setText( "" );
               screenvar.inst_hp.setText( "" );
               screenvar.tel_no.setText( "" );
               screenvar.email.setText( "" );
               screenvar.course_id.setText( "" );
            }
         }
         else 
            msgout.append( "\nEnter at least course_j, course_e, and " +
               "course_id, then press Add button\n" );
         statement.close();
      }
      catch ( SQLException sqlex ) {
         msgout.append( sqlex.toString() );
	   screenvar.course_id.setText("Course ID already exists -- reenter");
      }
   }
}

class FindRecord implements ActionListener {
   private DataPanel screenvar;
   private JTextArea msgout;
   private Connection dbconn;
 
   public FindRecord( Connection dbc, DataPanel scv, JTextArea msg ) {
      dbconn = dbc;
      screenvar = scv;
      msgout = msg;
   }

   public void actionPerformed( ActionEvent e ) {
   try {
      //  strip out non-numerics from course_id
	String numstrg = new String();
	for (int i = 0; i < screenvar.course_id.getText().length(); i++){  
	   if ( screenvar.course_id.getText().charAt(i)>='0' && 
	      screenvar.course_id.getText().charAt(i)<='9') { 
		   numstrg += screenvar.course_id.getText().substring(i,i+1);
	   }
	}
	if ( !numstrg.equals( "" ) ) {
         Statement statement =dbconn.createStatement();
         String query = "SELECT * FROM Table1 " +
            "WHERE course_id = '" +
            numstrg               + "'";
            msgout.append( "\nSending query: "   + 
               dbconn.nativeSQL( query ) + "\n" );
            ResultSet rs = statement.executeQuery( query );
            display( rs );
            statement.close();
         }
      else 
         screenvar.course_id.setText( 
            "Enter course_id then press Find" );
      }
      catch ( SQLException sqlex ) {
 	   msgout.append( sqlex.toString() + sqlex.getMessage() );
	}
   }

   // Display results of query
   public void display( ResultSet rs ) {
      try {         
         rs.next();
         int recordNumber = rs.getInt( 1 );
         if ( recordNumber != 0 ) {
            screenvar.id.setText( String.valueOf(recordNumber) );
            screenvar.course_j.setText( rs.getString( 2 ) );
            screenvar.course_e.setText( rs.getString( 3 ) );
            screenvar.course_url.setText( rs.getString( 4 ) );
            screenvar.inst_name.setText( rs.getString( 5 ) );
            screenvar.department.setText( rs.getString( 6 ) );
            screenvar.inst_hp.setText( rs.getString( 7 ) );
            screenvar.tel_no.setText( rs.getString( 8 ) );
            screenvar.email.setText( rs.getString( 9 ) );
            screenvar.course_id.setText( rs.getString( 10 ) );
         }
         else
            msgout.append( "\nNo record found\n" );         
    }
    catch ( SQLException sqlex ) {
       msgout.append( "\n*** Course Not Recorded In Database ***\n" );
    }
  }
}

class UpdateRecord implements ActionListener {
   private DataPanel screenvar;
   private JTextArea msgout;
   private Connection dbconn;

   public UpdateRecord( Connection dbc, DataPanel scv,
      JTextArea msg ) {
         dbconn = dbc;
         screenvar = scv;
         msgout = msg;
   }

   public void actionPerformed( ActionEvent e ) {
      try {
         Statement statement = dbconn.createStatement();

         if ( ! screenvar.id.getText().equals( "" ) ) {
	   	//  strip out non-numerics from course_id
		String numstrg = new String();
	   	for (int i = 0; i < screenvar.course_id.getText().length(); i++) {  
		   if ( screenvar.course_id.getText().charAt(i)>='0' && 
		      screenvar.course_id.getText().charAt(i)<='9') { 
			   numstrg += screenvar.course_id.getText().substring(i,i+1);
		   }
	      }

	      String query = "UPDATE Table1 SET " +
               "course_j='" + screenvar.course_j.getText() + 
               "', course_e='" + screenvar.course_e.getText() + 
               "', course_url='" + screenvar.course_url.getText() +
               "', inst_name='" + screenvar.inst_name.getText() + 
               "', department='" +
               screenvar.department.getText() + 
               "', inst_hp='" + screenvar.inst_hp.getText() +
               "', tel_no='" + screenvar.tel_no.getText() +
               "', email='" + 
               screenvar.email.getText() + 
               "', course_id='" + numstrg + 
               "' WHERE id=" + screenvar.id.getText();
               msgout.append( "\nSending query: " + 
                  dbconn.nativeSQL( query ) + "\n" );

            int result = statement.executeUpdate( query ); 
            
            if ( result == 1 )
               msgout.append( "\nUpdate successful\n" );
            else {
               msgout.append( "\nUpdate failed\n" );
               screenvar.course_j.setText( "" );
               screenvar.course_e.setText( "" );
               screenvar.course_url.setText( "" );
               screenvar.inst_name.setText( "" );
               screenvar.department.setText( "" );
               screenvar.inst_hp.setText( "" );
               screenvar.tel_no.setText( "" );
               screenvar.email.setText( "" );
               screenvar.course_id.setText( "" );
            }
    	   statement.close();
         }
         else 
            msgout.append( "\nYou may only update an " +
                           "existing record. Use Find to " +
                           "locate the record, then " +
                           "modify the information and " +
                           "press Update.\n" );
      }
      catch ( SQLException sqlex ) {
                  msgout.append( sqlex.toString() );
      }
   }
}

class DataPanel extends JPanel {
      
   JTextField id, course_j, course_e, course_url,     
      course_id, inst_name, department, inst_hp,
      tel_no, email;
   JLabel lcourse_j, lcourse_e, lcourse_url,
	lcourse_id, linst_name, ldepartment, linst_hp,
	ltel_no, lemail;

   public DataPanel() {
      // Label panel
      JPanel labelPanel = new JPanel();
      labelPanel.setLayout( 
        new GridLayout( 9, 1 ) );
	lcourse_j = new JLabel( "Course Name (Japanese):", 0 );
	labelPanel.add( lcourse_j);
 	lcourse_e = new JLabel( "Course Name (English):", 0 );
	labelPanel.add( lcourse_e);
 	lcourse_id = new JLabel( "Course ID:", 0 );
	labelPanel.add( lcourse_id);
 	lcourse_url = new JLabel( "Course URL:", 0 );
	labelPanel.add( lcourse_url);
 	linst_name = new JLabel( "Instructor Name:", 0 );
	labelPanel.add( linst_name);
 	ldepartment = new JLabel( "Department:", 0 );
	labelPanel.add( ldepartment);
 	linst_hp = new JLabel( "Instructor Homepage:", 0 );
	labelPanel.add( linst_hp);
 	ltel_no = new JLabel( "Telephone:", 0 );
	labelPanel.add( ltel_no);
 	lemail = new JLabel( "Email:", 0 );
	labelPanel.add( lemail);

      // TextField panel
      JPanel screenvarPanel = new JPanel();
      screenvarPanel.setLayout( 
         new GridLayout( 9, 1 ) );
      id = new JTextField( 20) ;
	
	course_j = new JTextField( 20 );
      screenvarPanel.add( course_j );
      course_e = new JTextField( 20 );
      screenvarPanel.add( course_e );
 	course_id = new JTextField("Enter number-click Find", 20);
	screenvarPanel.add( course_id );     
	course_url = new JTextField( 20 );
      screenvarPanel.add( course_url );
      inst_name = new JTextField( 20 );
      screenvarPanel.add( inst_name );
      department = new JTextField( 20 );
      screenvarPanel.add( department  );
      inst_hp = new JTextField( 20 );
      screenvarPanel.add( inst_hp );
      tel_no = new JTextField( 20 );
      screenvarPanel.add( tel_no );
      email = new JTextField( 20 );
      screenvarPanel.add( email );

	// Accessibility Section - relate labels and text fields
	lcourse_j.setLabelFor( course_j );
	lcourse_e.setLabelFor( course_e );
	lcourse_id.setLabelFor( course_id );
	lcourse_url.setLabelFor( course_url );
	linst_name.setLabelFor( inst_name );
	ldepartment.setLabelFor( department );
	linst_hp.setLabelFor( inst_hp );
	ltel_no.setLabelFor( tel_no );
	lemail.setLabelFor( email );

      setLayout( new GridLayout( 1, 2 ) );
      add( labelPanel );
      add( screenvarPanel );
   }
}

class ButtonPanel extends JPanel {
      
   public ButtonPanel( Connection dbc, DataPanel scv, 
      JTextArea msg ) {
      setLayout( new GridLayout( 1, 4 ) );
      
      JButton findName = new JButton( "Find" );
      findName.addActionListener( new FindRecord( dbc, scv, msg ) );
      add( findName );

      JButton addName = new JButton( "Add" );
      addName.addActionListener( new AddRecord( dbc, scv, msg ) );
      add( addName );
      
      JButton updateName = new JButton( "Update" );
      updateName.addActionListener(
         new UpdateRecord( dbc, scv, msg ) );
      add( updateName );
      
      JButton clear = new JButton( "Clear" );
      clear.addActionListener( new Clearscreenvar( scv ) );
      add( clear );
      
   }
}


class Clearscreenvar implements ActionListener {
   private DataPanel screenvar;

   public Clearscreenvar( DataPanel scv ) {
      screenvar = scv;
   }

   public void actionPerformed( ActionEvent e )
   {
      screenvar.id.setText( "" );
      screenvar.course_j.setText( "" );
      screenvar.course_e.setText( "" );
      screenvar.course_url.setText( "" );
      screenvar.inst_name.setText( "" );
      screenvar.department.setText( "" );
      screenvar.inst_hp.setText( "" );
      screenvar.tel_no.setText( "" );
      screenvar.email.setText( "" );
      screenvar.course_id.setText( "" );
   }
}