import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.lang.*;

public class cityperson extends Frame implements ActionListener,ItemListener,FocusListener
{
    public Panel        Panel1;

    public Label        lbl_1;
    public Label        lbl_2;
    public Label        lbl_3;
    public Label        lbl_4;
    public Label        lbl_5;
    public Label        lbl_6;

    public Choice       ch_shortname;
    public TextField    tf_shortname;
    public TextField    tf_contactperson;
    public TextField    tf_extnum;
    public TextField    tf_cityphone;
    public TextField    tf_airphone;
    public TextField    tf_faxnum;

    public Button       but_first;
    public Button       but_previous;
    public Button       but_next;
    public Button       but_last;

    public Label        lbl_message;
    public TextArea     ta_message;

    public Button       but_reset;
    public Button       but_add;
    public Button       but_delete;
    public Button       but_update;

    public Button       but_help;
    public Button       but_exit;


    //
    public Connection con,con1;
    public String ss,s1,s2,s3,s4,s5,s6,s0,sqlstmt;
    public int i,ret;

    public cityperson(Connection c,String s)
    {

        con = c;
        ss  = s;

        setResizable(false);
        setForeground(Color.black);
        //setBackground(Color.lightGray);
        setBackground(new Color(255,255,210));
        setTitle("Cityperson Information");
        setLayout(null);

        Panel1 = new Panel();
        Panel1.setLayout(null);
        Panel1.setForeground(Color.black);
        Panel1.setBackground(Color.gray);

        lbl_1          = new Label("Short Name"     ,Label.LEFT);
        lbl_2          = new Label("Contact Person" ,Label.LEFT);
        lbl_3          = new Label("Ext.No."     ,Label.LEFT);
        lbl_4          = new Label("City Phone"     ,Label.LEFT);
        lbl_5          = new Label("Airport Phone"  ,Label.LEFT);
        lbl_6          = new Label("Fax Number"     ,Label.LEFT);

        ch_shortname        = new Choice();
        tf_shortname        = new TextField("");
        tf_contactperson    = new TextField("");
        tf_extnum           = new TextField("");
        tf_cityphone        = new TextField("");
        tf_airphone         = new TextField("");
        tf_faxnum           = new TextField("");

        but_first       = new Button("First");
        but_previous    = new Button("Previous");
        but_next        = new Button("Next");
        but_last        = new Button("Last");

        but_reset       = new Button("Reset");
        but_add         = new Button("Add");
        but_delete      = new Button("Delete");
        but_update      = new Button("Update");

        but_help        = new Button("Help");
        but_exit        = new Button("Exit");

        lbl_message     = new Label("MESSAGE / ACTION / ERROR",Label.LEFT);
        ta_message      = new TextArea("");

        tf_shortname.setText(ss); // set value of shortname

        // Add()s
        add(Panel1);
        Panel1.add(lbl_1);
        Panel1.add(lbl_2);
        Panel1.add(lbl_3);
        Panel1.add(lbl_4);
        Panel1.add(lbl_5);
        Panel1.add(lbl_6);

        Panel1.add(ch_shortname);
        Panel1.add(tf_shortname);
        Panel1.add(tf_contactperson);
        Panel1.add(tf_extnum);
        Panel1.add(tf_cityphone);
        Panel1.add(tf_airphone);
        Panel1.add(tf_faxnum);

        add(but_first);
        add(but_previous);
        add(but_next);
        add(but_last);

        add(lbl_message);
        add(ta_message);

        add(but_reset);
        add(but_add);
        add(but_delete);
        add(but_update);

        add(but_help);
        add(but_exit);


        // Events add
        ch_shortname    .addItemListener(this);
        tf_shortname    .addFocusListener(this);
  
        but_first       .addActionListener(this);
        but_previous    .addActionListener(this);
        but_next        .addActionListener(this);
        but_last        .addActionListener(this);
        but_reset       .addActionListener(this);
        but_add         .addActionListener(this);
        but_delete      .addActionListener(this);
        but_update      .addActionListener(this);
        but_help        .addActionListener(this);
        but_exit        .addActionListener(this);


        // InitialPosition
        setBounds(20,300,750,295);

        Panel1.setBounds            ( 35,30,685,80);

        lbl_1.setBounds             ( 25,10, 70,20);
        lbl_2.setBounds             (105,10,200,20);
        lbl_3.setBounds             (325,10, 40,20);
        lbl_4.setBounds             (385,10, 80,20);
        lbl_5.setBounds             (485,10, 80,20);
        lbl_6.setBounds             (585,10, 80,20);

        ch_shortname.setBounds      ( 25,60, 60,25);
        tf_shortname.setBounds      ( 25,35, 60,25);
        tf_contactperson.setBounds  (105,35,200,25);
        tf_extnum.setBounds         (325,35, 40,25);
        tf_cityphone.setBounds      (385,35, 80,25);
        tf_airphone.setBounds       (485,35, 80,25);
        tf_faxnum.setBounds         (585,35, 80,25);

        but_first.setBounds         (60,120,75,25);
        but_previous.setBounds      (60,150,75,25); 
        but_next.setBounds          (60,180,75,25);  
        but_last.setBounds          (60,210,75,25);  

        lbl_message.setBounds       (190,120,200,20);
        ta_message.setBounds        (190,150,385,100); 

        but_reset.setBounds         (620,120,75,25);
        but_add.setBounds           (620,150,75,25);
        but_delete.setBounds        (620,180,75,25);
        but_update.setBounds        (620,210,75,25);

        but_help.setBounds          (290,260,75,25);
        but_exit.setBounds          (370,260,75,25);

        // enable/disable buttons
        but_help.setVisible(false);
        but_delete.setEnabled(false);
        tf_shortname.setEnabled(false);
        ch_shortname.setVisible(false);
        ta_message.setEditable(false);

        // retrieve short names
        try
        {
          Statement stmt = con.createStatement();
          ResultSet rs = stmt.executeQuery("select short_name from city");
          while (rs.next())
          {
            String code = rs.getString(1);
            ch_shortname.addItem(code);
          }
        }
        catch(Exception er)
        {
          ta_message.setText ("ERROR: Row does not exist / Database error \n");
          ta_message.append  ("EXCEPTION:" + "[" + er + "]" + "\n");
          ta_message.append  ("SOLUTION: " + "For more details kindly refer Help ");
          return;
        }  //end of short names retrieve


        // focus
       this.requestFocus();

    } //end of constructor



   public void focusGained(FocusEvent e)
   {
     if(e.getSource()==tf_shortname)
     {
       ch_shortname.setVisible(true);
     }
   } // end of focusgained


   public void focusLost(FocusEvent e)
   {
     if(e.getSource()==tf_shortname)
     {
     }
   } // end of focuslost



   public void itemStateChanged(ItemEvent e)
   {
     if(e.getSource()==ch_shortname)
     {
       tf_shortname.setText( ch_shortname.getSelectedItem() );
       ch_shortname.setVisible(false);       
     }
   } // end of itemstatechanged



   public void actionPerformed(ActionEvent e)
   {
        if(e.getSource()==but_exit)
        {
          this.dispose();
        } // end of exit


        if(e.getSource()==but_add)
        {
          try
          {
             ta_message.setText("ACTION: Adding new record ...");
             getInput();

             CallableStatement cstmt = con.prepareCall( "{call insert_cityperson(?,?,?,?,?,?)}" );

             //set IN parameter
             cstmt.setString(1,s1);
             cstmt.setString(2,s2);
             cstmt.setString(3,s3);
             cstmt.setString(4,s4);
             cstmt.setString(5,s5);
             cstmt.setString(6,s6);

             cstmt.executeUpdate();
             ta_message.setText("ACTION: Record Added.");
          }
          catch(Exception er)
          {
             ta_message.setText ("ERROR: Incorrect data entered / Database error \n");
             ta_message.append  ("EXCEPTION:" + "[" + er + "]" + "\n");
             ta_message.append  ("SOLUTION: " + "For more details kindly refer Help ");
             return;
          }
        } // end of but_add



        if(e.getSource()==but_update)
        {
          try
          {
             ta_message.setText("ACTION: Updating record...");
             getInput();

             CallableStatement cstmt = con.prepareCall( "{call update_cityperson(?,?,?,?,?,?)}" );

             cstmt.setString(1,s1);
             cstmt.setString(2,s2);
             cstmt.setString(3,s3);
             cstmt.setString(4,s4);
             cstmt.setString(5,s5);
             cstmt.setString(6,s6);

             cstmt.executeUpdate();
             ta_message.setText("ACTION: Record updated.");
          }
          catch(Exception er)
          {
            ta_message.setText ("ERROR: Incorrect data entered / Database error \n");
            ta_message.append  ("EXCEPTION:" +  "[" + er + "]" + "\n");
            ta_message.append  ("SOLUTION: " + "For more details kindly refer Help ");
          }
        }// end of but_update



        if(e.getSource()==but_reset)
        {
           tf_shortname.setEnabled(false);
           tf_contactperson.setEnabled(true);
           tf_contactperson .setText("");
           tf_extnum        .setText("");
           tf_cityphone     .setText("");
           tf_airphone      .setText("");
           tf_faxnum        .setText("");
           ta_message       .setText("");
           tf_contactperson .requestFocus();
        } // end of reset

        if(e.getSource()==but_first)
        {
          try
          {
             ta_message.setText("ACTION: Retrieving record ...");

             CallableStatement cstmt = con.prepareCall( "{call first_cityperson(?,?,?,?,?,?,?)}" );

             cstmt.setString(1,tf_shortname.getText());
             cstmt.registerOutParameter(2,12); // string
             cstmt.registerOutParameter(3,12); // string
             cstmt.registerOutParameter(4,12); // string
             cstmt.registerOutParameter(5,12); // string
             cstmt.registerOutParameter(6,12); // string
             cstmt.registerOutParameter(7,12); // string

             cstmt.executeUpdate();

             s1 = cstmt.getString(2);
             s2 = cstmt.getString(3);
             s3 = cstmt.getString(4);
             s4 = cstmt.getString(5);
             s5 = cstmt.getString(6);
             s6 = cstmt.getString(7);

             tf_shortname.setText(s1);
             tf_contactperson.setText(s2);
             tf_extnum.setText(s3);
             tf_cityphone.setText(s4);
             tf_airphone.setText(s5);
             tf_faxnum.setText(s6);

             tf_shortname.setEnabled(false);
             tf_contactperson.setEnabled(false);
             ta_message.setText("ACTION: Record retrieved.");
          }
          catch(Exception er)
          {
             ta_message.setText ("ERROR: Row does not exist / Database error \n");
             ta_message.append  ("EXCEPTION:" + "[" + er + "]" + "\n");
             ta_message.append  ("SOLUTION: " + "For more details kindly refer Help ");
             return;
          }
        }// end of but_first

        if(e.getSource()==but_last)
        {
          try
          {
             ta_message.setText("ACTION: Retrieving record ...");

             CallableStatement cstmt = con.prepareCall( "{call last_cityperson(?,?,?,?,?,?,?)}" );

             cstmt.setString(1,tf_shortname.getText());
             cstmt.registerOutParameter(2,12); // string
             cstmt.registerOutParameter(3,12); // string
             cstmt.registerOutParameter(4,12); // string
             cstmt.registerOutParameter(5,12); // string
             cstmt.registerOutParameter(6,12); // string
             cstmt.registerOutParameter(7,12); // string

             cstmt.executeUpdate();

             s1 = cstmt.getString(2);
             s2 = cstmt.getString(3);
             s3 = cstmt.getString(4);
             s4 = cstmt.getString(5);
             s5 = cstmt.getString(6);
             s6 = cstmt.getString(7);

             tf_shortname.setText(s1);
             tf_contactperson.setText(s2);
             tf_extnum.setText(s3);
             tf_cityphone.setText(s4);
             tf_airphone.setText(s5);
             tf_faxnum.setText(s6);

             tf_shortname.setEnabled(false);
             tf_contactperson.setEnabled(false);
             ta_message.setText("ACTION: Record retrieved.");
          }
          catch(Exception er)
          {
             ta_message.setText ("ERROR: Row does not exist / Database error \n");
             ta_message.append  ("EXCEPTION:" + "[" + er + "]" + "\n");
             ta_message.append  ("SOLUTION: " + "For more details kindly refer Help ");
             return;
          }
        }// end of but_last

        if(e.getSource()==but_previous)
        {
          try
          {
             ta_message.setText("ACTION: Retrieving record ...");

             CallableStatement cstmt = con.prepareCall( "{call previous_cityperson(?,?,?,?,?,?,?,?)}" );

             cstmt.setString(1,tf_shortname.getText());
             cstmt.setString(2,tf_contactperson.getText());
             cstmt.registerOutParameter(3,12); // string
             cstmt.registerOutParameter(4,12); // string
             cstmt.registerOutParameter(5,12); // string
             cstmt.registerOutParameter(6,12); // string
             cstmt.registerOutParameter(7,12); // string
             cstmt.registerOutParameter(8,12); // string

             cstmt.executeUpdate();

             s1 = cstmt.getString(3);
             s2 = cstmt.getString(4);
             s3 = cstmt.getString(5);
             s4 = cstmt.getString(6);
             s5 = cstmt.getString(7);
             s6 = cstmt.getString(8);

             tf_shortname.setText(s1);
             tf_contactperson.setText(s2);
             tf_extnum.setText(s3);
             tf_cityphone.setText(s4);
             tf_airphone.setText(s5);
             tf_faxnum.setText(s6);

             tf_shortname.setEnabled(false);
             tf_contactperson.setEnabled(false);
             ta_message.setText("ACTION: Record retrieved.");

          }
          catch(Exception er)
          {
             ta_message.setText ("ERROR: Row does not exist / Database error \n");
             ta_message.append  ("EXCEPTION:" + "[" + er + "]" + "\n");
             ta_message.append  ("SOLUTION: " + "For more details kindly refer Help ");
             return;
          }
        }// end of but_previous

        if(e.getSource()==but_next)
        {
          try
          {
             ta_message.setText("ACTION: Retrieving record ...");

             CallableStatement cstmt = con.prepareCall( "{call next_cityperson(?,?,?,?,?,?,?,?)}" );

             cstmt.setString(1,tf_shortname.getText());
             cstmt.setString(2,tf_contactperson.getText());
             cstmt.registerOutParameter(3,12); // string
             cstmt.registerOutParameter(4,12); // string
             cstmt.registerOutParameter(5,12); // string
             cstmt.registerOutParameter(6,12); // string
             cstmt.registerOutParameter(7,12); // string
             cstmt.registerOutParameter(8,12); // string

             cstmt.executeUpdate();

             s1 = cstmt.getString(3);
             s2 = cstmt.getString(4);
             s3 = cstmt.getString(5);
             s4 = cstmt.getString(6);
             s5 = cstmt.getString(7);
             s6 = cstmt.getString(8);

             tf_shortname.setText(s1);
             tf_contactperson.setText(s2);
             tf_extnum.setText(s3);
             tf_cityphone.setText(s4);
             tf_airphone.setText(s5);
             tf_faxnum.setText(s6);

             tf_shortname.setEnabled(false);
             tf_contactperson.setEnabled(false);
             ta_message.setText("ACTION: Record retrieved.");
          }
          catch(Exception er)
          {
             ta_message.setText ("ERROR: Row does not exist / Database error \n");
             ta_message.append  ("EXCEPTION:" + "[" + er + "]" + "\n");
             ta_message.append  ("SOLUTION: " + "For more details kindly refer Help ");
             
             return;
          }
        }// end of but_next


    }// action performed


   public static void main(String args[])
   {
   } // end main


   public boolean getInput() throws Exception
   {

     s1 = tf_shortname.getText();
     s2 = tf_contactperson.getText();
     s3 = tf_extnum.getText();
     s4 = tf_cityphone.getText();
     s5 = tf_airphone.getText();
     s6 = tf_faxnum.getText();

     if ( (s1 == "") | (s2 == "") | (s3 == "") | (s4 == "") | (s5 == "") | (s6 == "") )
     {
        return false;
     }
     else
     {
        return true;
     }

   }// end of getInput

} // end class