package hotel2;

import javax.swing.*;
import javax.swing.JOptionPane.*;
import java.awt.*;

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

import java.util.*;
import com.borland.dx.sql.dataset.*;
import com.borland.dbswing.*;




/**
 * <p>Title: 客房官理系統</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: </p>
 * @author OK
 * @version 1.0
 */

public class Frame1 extends JFrame {
  JButton jButton1 = new JButton();
  JPanel jPanel1 = new JPanel();
  JButton jButton2 = new JButton();
  JButton jButton3 = new JButton();
  JButton jButton4 = new JButton();
  JPanel jPanel2 = new JPanel();
  JPanel jPanel3 = new JPanel();
  JPanel jPanel4 = new JPanel();
  JLabel jLabel2 = new JLabel();
  JLabel jLabel3 = new JLabel();
  Connection connect;
  String url;
  boolean roomordered;
  int price_eachday=1;





  JButton jButton5 = new JButton();
  GridLayout gridLayout1 = new GridLayout();
  String Slected[]={"男","女"};
  JMenuBar jMenuBar1 = new JMenuBar();
  JMenu jMenu1 = new JMenu();
  JMenu jMenu2 = new JMenu();
  JMenuItem jMenuItem1 = new JMenuItem();
  JLabel jLabel4 = new JLabel();
  JLabel jLabel6 = new JLabel();
  JLabel jLabel7 = new JLabel();
  JLabel jLabel5 = new JLabel();
  JMenu jMenu3 = new JMenu();
  JMenuItem jMenuItem2 = new JMenuItem();
  JTextField jTextField6 = new JTextField();
  JTextField jTextField2 = new JTextField();
  JComboBox jComboBox1 = new JComboBox(Slected);
  JTextField jTextField1 = new JTextField();
  JTextField jTextField5 = new JTextField();
  JTextField jTextField4 = new JTextField();
  Clocklabel clocklabel1 = new Clocklabel();
  JScrollPane jScrollPane1 = new JScrollPane();
  JTextArea jTextArea1 = new JTextArea();
  Database database1 = new Database();
  QueryDataSet queryDataSet1 = new QueryDataSet();
  TableScrollPane tableScrollPane1 = new TableScrollPane();
  JdbTable jdbTable1 = new JdbTable();
  JdbNavToolBar jdbNavToolBar1 = new JdbNavToolBar();
  JdbStatusLabel jdbStatusLabel1 = new JdbStatusLabel();


  public Frame1() {
    try {
      jbInit();
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }
  private void jbInit() throws Exception {
    jButton1.setText("添加客戶");
    jButton1.addActionListener(new Frame1_jButton1_actionAdapter(this));
    this.getContentPane().setLayout(null);
    jPanel1.setLayout(gridLayout1);
    jButton2.setText("刪除客戶");
    jButton2.addActionListener(new Frame1_jButton2_actionAdapter(this));
    jButton3.setText("房客查尋");
    jButton3.addActionListener(new Frame1_jButton3_actionAdapter(this));
    jButton4.setText("空房查尋");
    jButton4.addActionListener(new Frame1_jButton4_actionAdapter(this));
    jPanel2.setLayout(null);
    jPanel3.setLayout(null);
    jLabel2.setFont(new java.awt.Font("Dialog", 0, 13));
    jLabel2.setText("          姓名");
    jLabel2.setBounds(new Rectangle(0, 0, 161, 32));
    jLabel3.setFont(new java.awt.Font("Dialog", 0, 13));
    jLabel3.setText("          性別");
    jLabel3.setBounds(new Rectangle(0, 32, 161, 32));
    jPanel4.setLayout(null);

    jButton5.setText("清空");
    jButton5.addActionListener(new Frame1_jButton5_actionAdapter(this));
    this.setLocale(java.util.Locale.getDefault());
    this.setJMenuBar(null);
    this.setState(Frame.NORMAL);
    jMenu1.setText("  關于");
    jMenu2.setText("作者及版權");
    jMenuItem1.setText("Read me");
    jMenuItem1.addActionListener(new Frame1_jMenuItem1_actionAdapter(this));
    jPanel4.setDebugGraphicsOptions(0);
    jPanel4.setBounds(new Rectangle(335, 0, 118, 202));
    jLabel4.setFont(new java.awt.Font("Dialog", 0, 13));
    jLabel4.setText("          年齡");
    jLabel4.setBounds(new Rectangle(0, 64, 161, 32));
    jLabel6.setFont(new java.awt.Font("Dialog", 0, 13));
    jLabel6.setText("          房號");
    jLabel6.setBounds(new Rectangle(0, 128, 161, 32));
    jLabel7.setFont(new java.awt.Font("Dialog", 0, 13));
    jLabel7.setText("       當前金額");
    jLabel7.setBounds(new Rectangle(0, 160, 161, 32));
    jLabel5.setFont(new java.awt.Font("Dialog", 0, 13));
    jLabel5.setText("       身份證號");
    jLabel5.setBounds(new Rectangle(0, 96, 161, 32));
    jPanel2.setBounds(new Rectangle(-2, 78, 471, 202));
    jPanel1.setBounds(new Rectangle(0, 1, 493, 41));
    jPanel3.setDebugGraphicsOptions(0);
    jPanel3.setBounds(new Rectangle(2, 0, 322, 197));
    jMenu3.setText("  使用方法");
    jMenuItem2.setText("Read_me");
    jMenuItem2.addActionListener(new Frame1_jMenuItem2_actionAdapter(this));
    jTextField6.setText("jTextField6");
    jTextField6.setBounds(new Rectangle(161, 128, 161, 32));
    jTextField2.setText("jTextField2");
    jTextField2.setBounds(new Rectangle(161, 0, 161, 32));
    jComboBox1.setDebugGraphicsOptions(0);
    jComboBox1.setToolTipText("");
    jComboBox1.setEditable(false);
    jComboBox1.setSelectedIndex(0);
    jComboBox1.setSelectedItem(this);
    jComboBox1.setBounds(new Rectangle(161, 32, 161, 32));
    jTextField1.setText("jTextField1");
    jTextField1.setBounds(new Rectangle(161, 160, 161, 32));
    jTextField1.setEditable(false);
    jTextField5.setText("jTextField5");
    jTextField5.setBounds(new Rectangle(161, 96, 161, 32));
    jTextField4.setText("jTextField4");
    jTextField4.setBounds(new Rectangle(161, 65, 161, 32));
    clocklabel1.setBounds(new Rectangle(46, 78, 72, 19));
    jScrollPane1.setBounds(new Rectangle(9, 285, 443, 116));
    jScrollPane1.setHorizontalScrollBarPolicy(JScrollPane.HORIZONTAL_SCROLLBAR_ALWAYS);
    jTextArea1.setText("jTextArea1");
    //jTextArea1.setLineWrap(true);

    database1.setConnection(new com.borland.dx.sql.dataset.ConnectionDescriptor("jdbc:odbc:hotel2", "", "", false, "sun.jdbc.odbc.JdbcOdbcDriver"));
    queryDataSet1.setQuery(new com.borland.dx.sql.dataset.QueryDescriptor(database1, "select * from hotel2", null, true, Load.ALL));
    tableScrollPane1.setBounds(new Rectangle(491, 42, 491, 359));
    jdbTable1.setDataSet(queryDataSet1);
    jdbNavToolBar1.setBounds(new Rectangle(491, 2, 491, 39));
    jdbStatusLabel1.setText("jdbStatusLabel1");
    jPanel1.add(jButton1, null);
    jPanel1.add(jButton2, null);
    jPanel1.add(jButton3, null);
    jPanel1.add(jButton4, null);
    jPanel1.add(jButton5, null);
    this.getContentPane().add(jdbNavToolBar1, null);
    this.getContentPane().add(tableScrollPane1, null);
    tableScrollPane1.getViewport().add(jdbTable1, null);
    jdbNavToolBar1.add(jdbStatusLabel1, null);
    this.getContentPane().add(jScrollPane1, null);
    jScrollPane1.getViewport().add(jTextArea1, null);
    jPanel3.add(jLabel2, null);
    jPanel3.add(jTextField2, null);
    jPanel3.add(jLabel3, null);
    jPanel3.add(jComboBox1, null);
    jPanel3.add(jLabel5, null);
    jPanel3.add(jTextField5, null);
    jPanel3.add(jLabel6, null);
    jPanel3.add(jTextField6, null);
    jPanel3.add(jLabel7, null);
    jPanel3.add(jTextField1, null);
    jPanel3.add(jLabel4, null);
    jPanel3.add(jTextField4, null);
    jPanel2.add(jPanel4, null);
    jPanel4.add(clocklabel1, null);
    this.getContentPane().add(jPanel2, null);
    jPanel2.add(jPanel3, null);
    this.getContentPane().add(jPanel1, null);
    jMenuBar1.add(jMenu1);
    jMenu1.add(jMenu2);
    jMenu1.add(jMenu3);
    jMenu2.add(jMenuItem1);
    jMenu3.add(jMenuItem2);
    setJMenuBar(jMenuBar1);

try{
      url = "jdbc:odbc:hotel2";
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      connect = DriverManager.getConnection(url);
      jTextArea1.append("Connection Successful!");
    }
    catch ( ClassNotFoundException cnfex ) {
         // process ClassNotFoundExceptions here
         cnfex.printStackTrace();

      }
      catch ( SQLException sqlex ) {
         // process SQLExceptions here
         sqlex.printStackTrace();

      }
      catch ( Exception ex ) {
         // process remaining Exceptions here
         ex.printStackTrace();

      }
    setSize(new Dimension(997, 550));
    show();
  }

  void jButton1_actionPerformed(ActionEvent e) {
    try {
          roomwithpeople(jTextField6.getText());
          if(roomordered)
            {
              JOptionPane.showMessageDialog(null, jTextField6.getText()+"房間已經有客人，請您另選一間");
              jTextField6.setText("");

            }
         else
          {
           // boolean judge=false;
            Statement statement = connect.createStatement();
            Calendar cal=Calendar.getInstance( );
            String c=cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
            if (namelegal()&&legal()&&judge_sex()&&judge_idnum()) {
              String query = "INSERT INTO hotel2 (" +
                  " name,sex,age,idnumber,room ,in_date" +

                  ") VALUES ('" +

                  jTextField2.getText() + "', '" +
                  jComboBox1.getSelectedItem() + "', '" +
                  jTextField4.getText() + "', '" +
                  jTextField5.getText() + "', '" +
                  jTextField6.getText() +"', '"+
                  c+"')";


              jTextArea1.append("\n發送的命令:" + connect.nativeSQL(query) + "\n");

              int result = statement.executeUpdate(query);

              if (result == 1)
                jTextArea1.append("\n插入成功\n");
              else {
                jTextArea1.append("\n插入失敗\n");
                jTextField2.setText("");
                jComboBox1.setSelectedIndex( -1);
                jTextField4.setText("");
                jTextField5.setText("");
                jTextField6.setText("");

              }
            }
          //  else
           //   jTextArea1.append("\n必須輸入輸入姓名和房號");

            statement.close();
          }


        }
         catch ( SQLException sqlex ) {
            sqlex.printStackTrace();
            jTextArea1.append( sqlex.toString() );
         }

  }

  void roomwithpeople(String roomnum)
  {

    try{

      Statement statement = connect.createStatement( );
        String query = "SELECT * FROM hotel2 WHERE room = '"+roomnum+"'";
        jTextArea1.append("\n發送的命令: " +
                          connect.nativeSQL(query)
                          + "\n");
        ResultSet rs = statement.executeQuery(query);
        judge(rs);

        statement.close();

      }
      catch(SQLException sqlex){sqlex.printStackTrace();
         jTextArea1.append( sqlex.toString() );}



  }
    void judge( ResultSet r )
   {

      try {
         //r.next();

         //int recordNumber = r.getInt( 1 );

         if (r.next())
         {

            roomordered=true;

         }
         else
             roomordered=false;
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
         jTextArea1.append( sqlex.toString() );
      }

   }




  void jButton3_actionPerformed(ActionEvent e) {
    try {
         if ( !jTextField2.getText().equals( "" ) ) {
           Statement statement =connect.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
           String query="SELECT ALL  * FROM hotel2 " +
                           "WHERE name = '" +
                           jTextField2.getText() + "'";

           jTextArea1.append( "\n發送的命令: " +
                           connect.nativeSQL( query )
                           + "\n" );
            ResultSet rs = statement.executeQuery( query );
            display( rs );
            jTextArea1.append( "\n查詢成功\n" );
            statement.close();
         }
         else
            jTextArea1.setText(
               "請輸入要查找的房客姓名" );
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
         jTextArea1.append( sqlex.toString() );
      }


  }
   void display( ResultSet rs )
   {
      try {
            Calendar cal=Calendar.getInstance( );
            String c=cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
            boolean morerows=false;

         //y

         if (rs.next() ) {

            jTextField2.setText( rs.getString(1) );
            jComboBox1.setSelectedItem( rs.getString( 2 ) );
            jTextField4.setText( rs.getString( 3 ) );
            jTextField5.setText( rs.getString( 4 ) );
            jTextField6.setText( rs.getString( 5 ) );
            String temp=rs.getString(6);
            Countdays count =new Countdays(temp,c);
            jTextField1.setText(" "+price_eachday*count.getdays());
            if(rs.next()) morerows=true;
         }
         else
            jTextArea1.append( "\n沒找到記錄\n" );

           if(morerows)
          {    rs.beforeFirst();
               message(" 您要查找的人的姓名不止一個，請在信息欄中查看，謝謝！");
               String p="name"+"\t\t"+"sex"+"\t\t"+"age"+"\t\t"+"idnumber"+"\t\t\t\t"+"room"
                        +"\t\t"+"money"+"\n";
               while(rs.next())
               {
                 String temp=rs.getString(6);
                 Countdays count =new Countdays(temp,c);
                 String money=" "+price_eachday*count.getdays();
                 p=p+rs.getString( 1 )+"\t\t"+ rs.getString( 2 )+"\t\t"+rs.getString( 3 )
                   +"\t\t"+ rs.getString( 4 )+"\t\t\t"+rs.getString( 5 )+"\t\t"+money+"\n";

               }
               jTextArea1.setText(p);




          }
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
         jTextArea1.append( sqlex.toString() );
      }
   }


  void jButton5_actionPerformed(ActionEvent e) {
        jTextField2.setText("");
        jComboBox1.setSelectedIndex(-1) ;
        jTextField4.setText("");
        jTextField5.setText("");
        jTextField6.setText("");
        jTextField1.setText("");
        jTextArea1.setText("");
  }
boolean morethanone=false;
  void jButton2_actionPerformed(ActionEvent e) {


    try {
            if ( !jTextField2.getText().equals( "" ) ) {
              Statement statement =connect.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                                         ResultSet.CONCUR_UPDATABLE);
              String query="SELECT ALL  * FROM hotel2 " +
                              "WHERE name = '" +
                              jTextField2.getText() + "'";

              jTextArea1.append( "\n發送的命令: " +
                              connect.nativeSQL( query )
                              + "\n" );

               ResultSet rs = statement.executeQuery( query );
               displaydetail( rs );
               //jTextArea1.append( "\n查詢成功\n" );
               statement.close();
            }
            else
               jTextArea1.setText(
                  "請輸入要刪除的房客姓名" );
         }
         catch ( SQLException sqlex ) {
            sqlex.printStackTrace();
            jTextArea1.append( sqlex.toString() );
         }




   /*      try {
                     Statement statement = connect.createStatement();

                     if ( !jTextField2.getText().equals( "" ) ) {
                       String query="";
                       if(morethanone==false)
                       query = "DELETE FROM hotel2 WHERE name LIKE" +
                           "'"+jTextField2.getText()+"'";
                       if(morethanone==true)