managedata.java

java开发

package newBookingSystem;


import java.sql.ResultSet;

import java.sql.Date;


/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2007</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class ManageData {
    private ConnectDataBase connectDataBase;

    private ResultSet dataSet; //save data which is read from dataBase;
    private String[] tableState;


    public ManageData() {

        connectDataBase = new ConnectDataBase();
        tableState = new String[16];

        try {
            connectDataBase.connect();
        } catch (Exception ex) {
            //JOptionPane.showMessageDialog(null, "连接数据失败!");
            ex.printStackTrace();
        } //end catch

    }

    //read data of Table from dataBase;
    public int[][] getTableData() {
        int[][] tableData = new int[16][2];
        int index = 0;
        String sql = "Select * from tables";
        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            dataSet = connectDataBase.executeQuery();

            while (dataSet.next()) {
                tableData[index][0] = dataSet.getInt("number");
                tableData[index][1] = dataSet.getInt("places");
                index++;

            }

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        } //end catch

        return tableData;

    }

    //read data of Customer
    public String[][] getCustomerData()

    {
        String[][] CustomerData = new String[16][2];
        int index = 0;
        String sql = "Select * from Customer";
        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            dataSet = connectDataBase.executeQuery();

            while (dataSet.next()) {
                CustomerData[index][0] = dataSet.getString("customerName");
                CustomerData[index][1] = dataSet.getString("phoneNumber");
                index++;

            }

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        } //end catch

        return CustomerData;

    }

    // get customerID with his name and phoneNumber
    public int getCustomerID(String name, String phoneNumber)

    {
        int customerID = 0;
        String sql = "Select * from Customer where customerName='" + name +
                     "' and phoneNumber='" + phoneNumber + "'";
        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            dataSet = connectDataBase.executeQuery();

            while (dataSet.next()) {
                customerID = dataSet.getInt("customerID");

            }

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        } //end catch

        return customerID;

    }

  public String[] getTableState()
  {
      return tableState;
  }
//获取所给日期餐馆桌子的状态
 public void   DisplayTableState(Date date)
 {
      //read table' State of WalkIn  from  dataBase
     String walkTableState[] = getWalkInTableState( date);
     //read table' State of Reservation from dataBase
     String reservationTableState[] = getReservationTableState(date);

     for (int i = 0; i<16;i++)
     {
         if(walkTableState[i]!=null )
         {
             tableState[i]= walkTableState[i];
         }
         else if (reservationTableState[i]!=null )
         {
             tableState[i]=reservationTableState[i];
         }
         else
              tableState[i] = "empty";
     }


 }

    //read table' State of Reservation from dataBase
    public String[] getReservationTableState(Date date) {

        String sql = "";
        String StateReservation[] = new String[16];


              sql =  "select number, state from Reservation where bookDate ='" +
                date.toString() + "'";

        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            dataSet = connectDataBase.executeQuery();

            while (dataSet.next()) {
                int i = dataSet.getInt("number");
                    String s = dataSet.getString("state");
                //根据Reservation.state 内容，则设置数组StateReservation；
                if (dataSet.getString("state").trim().equals( "Arrival")) {

                    StateReservation[dataSet.getInt("number")-1] = "reservationArrival";
                }
                else
                    StateReservation[dataSet.getInt("number")-1] = "reservationNotArrival";

            }

        } catch (Exception ex) {

            ex.printStackTrace();
        } //end catch

        return StateReservation;

    }

    //read table' State of WalkIn  from  dataBase

    public String[] getWalkInTableState(Date date) {

        String StateWalkIn[] = new String[16];

        String sql = " Select number from WalkIn"
                     + " where   WalkIn.bookDate='" + date.toString() + "'";

        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            dataSet = connectDataBase.executeQuery();
            while (dataSet.next()) {
                //获取字段名为“number”
                StateWalkIn[dataSet.getInt("number")-1] = "WalkIn";
            }

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        }
        return StateWalkIn;

    }


    //make new booking of Walk
    public boolean makeNewWalkIn(WalkIn walkIn) {
        String sql;
        int covers;
        Date date;
        int number;

        covers = walkIn.getCovers();
        date = new java.sql.Date(walkIn.getDate().getTime());
        number = walkIn.getTable().getNumber();

        sql = "Insert into WalkIn values(" + covers + ",'" + date.toString() +
              "'," + number + ")";
        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            connectDataBase.executeUpdate();

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        } //end catch

        return true;

    }

    //make record of  customer
    public boolean makeNewCustomer(String name, String phoneNumber) {
        String sql;
        sql = "Insert into Customer values('" + name + "','" + phoneNumber +
              "')";
        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            connectDataBase.executeUpdate();

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        } //end catch

        return true;
    }

    //创建新的Reservation
    public boolean makeNewReservation(Reservation reservation) {
        String sql;
        int covers;
        Date date;
        int number;
        String name;
        String phoneNumber;
        int customerID = 0;

        date = new java.sql.Date(reservation.getDate().getTime());
        number = reservation.getTable().getNumber();

        covers = reservation.getCovers();

        name = reservation.getCutomerInstance().getName();
        phoneNumber = reservation.getCutomerInstance().getPhoneNumber();

        //检查客户是否存在,如果返回的值是0，则不存在，反之，存在
        customerID = IsExitCustomer(name, phoneNumber);

        //  如果客户不存在则在Customer表里创建这个客户的记录
        if (customerID == 0) {
            //make new record of customer
            makeNewCustomer(name, phoneNumber);
            //read the customerID from the table of Customer
            customerID = getCustomerID(name, phoneNumber);
        }
        sql = "Insert into Reservation values(" + covers + ",'" + date.toString() +
              "'," + number + "," + customerID + ",'NotArrival'" +  ")";
        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            connectDataBase.executeUpdate();

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        } //end catch

        return true;
    }

   /*
    //delete the records which is in walkIn of table
    public boolean CancelWalkIn(WalkIn walkIn) {
        String sql;
        //int covers;
        // Date date;

        int number;

        Date date;

        date = Date.valueOf(walkIn.getDate().toString());
        number = walkIn.getTable().getNumber();

        //delete the walkIn  whose bookdate is date and whose tableNumber is number;
        sql = "delete  from  WalkIn where number=" + number + " and bookDate='" +
              date.toString() + "'";

        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            connectDataBase.executeUpdate();

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        } //end catch

        return true;

    }
   */

    public boolean CancelReservation(Date d, int tno) {

        //we can delete a  record of reservation only  need his bookdate and tableNumber;
        String sql;

        //delete the reservation whose bookdate is date and whose tableNumber is number;
        sql = "delete  from  Reservation where number=" + tno +
              " and bookDate='" + d.toString() + "'";
        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            connectDataBase.executeUpdate();

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        } //end catch

        return true;

    }

    public boolean DeleteCustomer(String name, String Tel) {
        String sql = "delete  from  Customer where customerName='" + name +
                     "' and phoneNumber='" + Tel + "'";
        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            connectDataBase.executeUpdate();

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        } //end catch

        return true;
    }

    //检查客户是否存在,如果返回的值是0，则不存在，反之，存在
    public int IsExitCustomer(String name, String Tel) {
        //标记客户是否存在

        int id = 0;
        String sql = "select *  from  Customer where customerName='" + name +
                     "' and phoneNumber='" + Tel + "'";

        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            dataSet = connectDataBase.executeQuery();

            while (dataSet.next()) {
                id = dataSet.getInt("customerID");
                if (Integer.toString(id) !=null) {
                    //客户存在
                    break;
                }
            }

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        } //end catch

        return id;
    }

  /*
    public boolean TranferWalkIn(Vector vector) {
        String sql;
        //int covers;
        Date date;
        int number;
        String name;

        //this table'number is that the customer want to tranfer ;


        //how to coform the record that is to update?????????
        //Update the  Reservation'number  whose bookdate is date and whose
        sql = "delete  into WalkIn values()";
        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            connectDataBase.executeUpdate();

        } catch (Exception ex) { //end try

            ex.printStackTrace();
        } //end catch

        return true;
    }
  */


    public boolean TranferReservation(Date date, int oldNo, int newNo) {
        String sql;
        //int covers;

        //Update the reservation's number  whose bookdate is date and whose customerID that
        // we can find the customerID with the name and phoneNumber;
        sql = "Update Reservation SET number=" + newNo + "where bookDate='" +
              date.toString() + "' and number=" + oldNo;
        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            connectDataBase.executeUpdate();

        } catch (Exception ex) { //end try

            ex.printStackTrace();

        } //end for

        return true;
    }

    //记录到来
    public void ReservationRecordArrival(Date date,int tno)
    {


       String  sql = "Update Reservation SET state='Arrival'" + "where bookDate='" +
              date.toString() + "' and number=" + tno;
        try {

            connectDataBase.setQuery(sql);
            connectDataBase.preparedStatement();
            connectDataBase.executeUpdate();

        } catch (Exception ex) { //end try

            ex.printStackTrace();

        }
    }

}