package studentmessage;

import java.awt.*;
import javax.swing.*;
import java.awt.event.*;
import java.sql.*;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */

public class IndexInt
{
  JFrame inframe;
  JPanel panel, panelup, paneldown, panelcenter;
  JLabel lblCustID,lblNumber,lblUserName,lblUserSource,lblTel,lblMail,lblAddr;
  GridBagLayout g;
  GridBagConstraints c;
  JTextField txtCustID, txtNumber, txtName, txtSource,txtTel,txtMail,txtAddr;
  JButton btnQuery, btnInsert, btnDelete, btnUpdate;
  BorderLayout bl;
  FlowLayout fl;
  GridLayout gl;
  public IndexInt()
  {
    inframe=new JFrame("學生信息錄入");
    panel = new JPanel();
    panelup = new JPanel();
    paneldown = new JPanel();
    panelcenter = new JPanel();
    lblCustID = new JLabel("學生ID");
    txtCustID = new JTextField(10);
    lblNumber = new JLabel("學號:");
    lblUserName = new JLabel("姓名:");
    lblUserSource = new JLabel("分數:");
    lblTel=new JLabel("電話號碼:");
    lblMail=new JLabel("郵箱地址:");
    lblAddr=new JLabel("家庭住址:");
    btnQuery = new JButton("查詢");
    btnInsert = new JButton("增加");
    btnDelete = new JButton("刪除");
    btnUpdate = new JButton("修改");
    txtNumber = new JTextField(10);
    txtName = new JTextField(10);
    txtSource = new JTextField(10);
    txtTel=new JTextField(10);
    txtMail= new JTextField(10);
    txtAddr= new JTextField(10);
    bl = new BorderLayout();
    fl = new FlowLayout(FlowLayout.LEFT);
    gl = new GridLayout(1, 3);
    g = new GridBagLayout();
    c = new GridBagConstraints();
    panelcenter.setLayout(g);
    panel.setLayout(bl);
    panelup.setLayout(fl);
    paneldown.setLayout(gl);

    panelup.add(lblCustID);
    panelup.add(txtCustID);
    panelup.add(btnQuery);
    panel.add(panelup, BorderLayout.NORTH);

    paneldown.add(btnInsert);
    paneldown.add(btnDelete);
    paneldown.add(btnUpdate);
    panel.add(paneldown, BorderLayout.SOUTH);
    panel.add(panelcenter, BorderLayout.WEST);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 0;
    c.gridy = 5;
    g.setConstraints(lblNumber, c);
    panelcenter.add(lblNumber);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 1;
    c.gridy = 5;
    g.setConstraints(txtNumber, c);
    panelcenter.add(txtNumber);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 0;
    c.gridy = 6;
    g.setConstraints(lblUserName, c);
    panelcenter.add(lblUserName);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 1;
    c.gridy = 6;
    g.setConstraints(txtName, c);
    panelcenter.add(txtName);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 0;
    c.gridy = 7;
    g.setConstraints(lblUserSource, c);
    panelcenter.add(lblUserSource);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 1;
    c.gridy = 7;
    g.setConstraints(txtSource, c);
    panelcenter.add(txtSource);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 0;
    c.gridy = 8;
    g.setConstraints(lblTel, c);
    panelcenter.add(lblTel);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 1;
    c.gridy = 8;
    g.setConstraints(txtTel, c);
    panelcenter.add(txtTel);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 0;
    c.gridy = 9;
    g.setConstraints(lblMail, c);
    panelcenter.add(lblMail);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 1;
    c.gridy = 9;
    g.setConstraints(txtMail, c);
    panelcenter.add(txtMail);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 0;
    c.gridy = 10;
    g.setConstraints(lblAddr, c);
    panelcenter.add(lblAddr);

    c.anchor = GridBagConstraints.WEST;
    c.gridx = 1;
    c.gridy = 10;
    g.setConstraints(txtAddr, c);
    panelcenter.add(txtAddr);

    inframe.getContentPane().add(panel);


    Query q = new Query();
    btnQuery.addActionListener(q);
    btnInsert.addActionListener(q);
    btnDelete.addActionListener(q);
    btnUpdate.addActionListener(q);
  }
  class Query implements ActionListener
{
    public void actionPerformed(ActionEvent evt)
    {
      Object obj = evt.getSource(); //獲取引發事件的事件源
      if (obj==btnQuery)
      {
        String strCustID=txtCustID.getText().trim();
        try
        {//加載驅動程序
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         //連接數據庫
          Connection con = DriverManager.getConnection("jdbc:odbc:SDB");
          //執行SQL語句
          String sql = "select * from Tabel where 學號=?";
          PreparedStatement stmt = con.prepareStatement(sql);
          stmt.setString(1,strCustID);
          ResultSet rs=stmt.executeQuery();
          if(rs.next())
          {
            txtNumber.setText(rs.getString(1));
            txtName.setText(rs.getString(2));
            txtSource.setText(rs.getString(3));
            txtTel.setText(rs.getString(4));
            txtMail.setText(rs.getString(5));
            txtAddr.setText(rs.getString(6));
            con.close();
         }
         else {
                JOptionPane.showMessageDialog(inframe, "沒有您查詢的學生信息!");
                txtCustID.setText("");
              }
        }
        catch(ClassNotFoundException e1)
      {
        JOptionPane.showMessageDialog(inframe,e1.getMessage());
      }
      catch(SQLException e2)
      {
        JOptionPane.showMessageDialog(inframe,e2.getMessage());
      }
      }
      if(obj==btnInsert)
      {
        String strNumber=txtNumber.getText().trim();
        String strName=txtName.getText().trim();
        String strSource=txtSource.getText().trim();
        String strTel=txtTel.getText().trim();
        String strMail=txtMail.getText().trim();
        String strAddr=txtAddr.getText().trim();
        try
        {
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          Connection con = DriverManager.getConnection("jdbc:odbc:SDB");
          String str = "insert into Tabel values(?,?,?,?,?,?)";
          PreparedStatement stmt = con.prepareStatement(str);
          //傳遞參數
          stmt.setString(1,strNumber);
          stmt.setString(2,strName);
          stmt.setString(3,strSource);
          stmt.setString(4,strTel);
          stmt.setString(5,strMail);
          stmt.setString(6,strAddr);
          ResultSet rs;
          int i = stmt.executeUpdate();
          //返回結果不為空時
          if(i!=0)
          {
            JOptionPane.showMessageDialog(inframe, "添加記錄成功！");
            con.close();
            txtNumber.setText("");
            txtName.setText("");
            txtSource.setText("");
            txtTel.setText("");
            txtMail.setText("");
            txtAddr.setText("");
            con.close();
          }
          else
          {
            JOptionPane.showMessageDialog(inframe, "添加記錄失敗！");
            txtNumber.setText("");
            txtName.setText("");
            txtSource.setText("");
            txtTel.setText("");
            txtMail.setText("");
            txtAddr.setText("");
          }
        }
        catch(ClassNotFoundException e1)
        {
          JOptionPane.showMessageDialog(inframe,e1.getMessage());
        }
        catch(SQLException e2)
        {
          JOptionPane.showMessageDialog(inframe,e2.getMessage());
        }
      }
      if(obj==btnDelete)
      {
        String strNumber=txtNumber.getText().trim();
        String strName=txtName.getText().trim();
        String strSource=txtSource.getText().trim();
        try
        {
          String strCustID=txtCustID.getText().trim();
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          Connection con = DriverManager.getConnection("jdbc:odbc:SDB");
          String str = "delete from Tabel where 學號=?";
          PreparedStatement stmt = con.prepareStatement(str);
          stmt.setString(1,strCustID);
          ResultSet rs;
          int i = stmt.executeUpdate();
          if(i!=0)
          {
            JOptionPane.showMessageDialog(inframe, "成功刪除信息！");
            txtCustID.setText("");
            con.close();
          }
          else
          {
            JOptionPane.showMessageDialog(inframe, "刪除信息失敗！");
            txtCustID.setText("");
          }
        }
        catch(ClassNotFoundException e1)
        {
          JOptionPane.showMessageDialog(inframe,e1.getMessage());
        }
        catch(SQLException e2)
        {
          JOptionPane.showMessageDialog(inframe,e2.getMessage());
        }
      }
      if(obj==btnUpdate)
      {
        try
        {
          String strNumber=txtNumber.getText().trim();
          String strName=txtName.getText().trim();
          String strSource=txtSource.getText().trim();
          String strTel=txtTel.getText().trim();
          String strMail=txtMail.getText().trim();
          String strAddr=txtAddr.getText().trim();
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          Connection con = DriverManager.getConnection("jdbc:odbc:SDB");
          String str = "Update  into Tabel Set 姓名=?,分數=?,電話號碼=?,E-mail=?,家庭住址=? ";
          PreparedStatement stmt = con.prepareStatement(str);
          stmt.setString(1,strName);
          stmt.setString(2,strSource);
          stmt.setString(3,strTel);
          stmt.setString(4,strMail);
          stmt.setString(5,strAddr);
          //stmt.setString(6,strNumber);
          ResultSet rs;
          int i = stmt.executeUpdate();
          if(i!=0)
          {
            JOptionPane.showMessageDialog(inframe, "修改記錄成功！");
            con.close();
            txtNumber.setText("");
            txtName.setText("");
            txtSource.setText("");
            txtTel.setText("");
            txtMail.setText("");
            txtAddr.setText("");
            con.close();
          }
          else
          {
            JOptionPane.showMessageDialog(inframe, "修改記錄失敗！");
            txtNumber.setText("");
            txtName.setText("");
            txtSource.setText("");
            txtTel.setText("");
            txtMail.setText("");
            txtAddr.setText("");
          }
        }
        catch(ClassNotFoundException e1)
         {
           JOptionPane.showMessageDialog(inframe,e1.getMessage());
          }
          catch(SQLException e2)
          {
            JOptionPane.showMessageDialog(inframe,e2.getMessage());
          }
      }
    }
  }
 }