package bookingsystem;
import java.util.Vector;
import java.util.Date;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.text.ParseException;
import java.util.Calendar;
import java.util.*;
import java.text.*;
/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2007</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class Restaurant {
    private Customer customer;
    private Booking booking;
    private Table table;
   Connection cn = ConnectionDB.getCon();

    public Restaurant() {
    }

    //用于顯示預約的列表
    public Vector getAllRervations(Date date) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String predate = df.format(date);
        System.out.println("Current datetime is: " + predate);

        Vector v = new Vector();

        Statement st = null;
        ResultSet rs = null;

        try {
            String sql = "select * from reservation where arrivaldate = '" +
                         predate + "'";
            st = cn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                int tempRid = rs.getInt("RESERVATION_ID");
                int tempcovers = rs.getInt("covers");
                String temppredate = rs.getString("predate");
                String temparrivaldate = rs.getString("arrivaldate");
                String temparrivaltime = rs.getString("arrivaltime");
                String temptalno = rs.getString("talno");
                int tempcno = rs.getInt("cno");
                boolean tempisarrival = rs.getBoolean("isarrival");
                boolean tempispaybill = rs.getBoolean("ispaybill");
                Table temptable = new Table(temptalno);
                Customer tempcus = new Customer(tempcno);

                Reservation res = new Reservation(tempRid,tempcovers, temppredate,
                                                  temptable, tempcus,
                                                  temparrivaldate,
                                                  temparrivaltime);
                v.add(res);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return v;
    }

    public void modifyReservation(int R_ID,int covers,String arrivalDate,String arrivalTime,String Talno,boolean isarrival) throws SQLException {
         PreparedStatement ps;
         String sql = "UPDATE RESERVATION SET COVERS = "+covers+",ARRIVALDATE = '"+arrivalDate
                     +"', ARRIVALTIME = '"+arrivalTime+"',TALNO ='"+Talno+"',ISARRIVAL = "+isarrival+" where reservation_id = "+R_ID;
        ps= cn.prepareStatement(sql);
        ps.executeUpdate();

    }

    public void deleteReservation(int R_ID)
    {
        PreparedStatement ps;
        int tempR_id = R_ID;
        String delSql = "DELETE FROM RESERVATION WHERE RESERVATION_ID = "+tempR_id;
        try {
             ps = cn.prepareStatement(delSql);
             ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }



    }
    //顯示用餐的列表
   public Vector getdining(String tempstate)
        {
            Vector v =new Vector();
            Connection cn=ConnectionDB.getCon();
            Statement st=null;
            ResultSet rs=null;
            try{
            String sql = "select * from T_TABLE where state = '" + tempstate + "'";
            st =cn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next())
            {
                String temptalno = rs.getString("talno");
                Table temptable = new Table(temptalno);
                v.add(temptable);

            }
            }catch(SQLException e)
            {
                e.printStackTrace();
            }

           return v;
        }
        /**
         * 設置某個時間桌子的狀態，主要用來查看主屏幕上的桌子狀態
         * @param date Date
         */
        public void gettablestate(Date date)
   {
       SimpleDateFormat formattime = new SimpleDateFormat("yyyy-MM-dd HH:mm");
       SimpleDateFormat formatdate = new SimpleDateFormat("yyyy-MM-dd");


       String nowTime = formattime.format(date);
       String nowDate = formatdate.format(date);
       String stdTime1 = nowDate + " 9:30";
       String stdTime2 = nowDate + " 12:00";
       String stdTime3 = nowDate + " 17:30";
       String stdTime4 = nowDate + " 20:00";
       String stdTime5 = nowDate + " 19:30";
       String stdTime6 = nowDate + " 22:00";
       Connection cn=ConnectionDB.getCon();
       Statement st1=null;
       ResultSet rs1=null;
       Statement st2=null;
       ResultSet rs2=null;
       Statement st3=null;
       ResultSet rs3=null;
       boolean isarrival_false = false; //表示還沒有來
       boolean ispaybill_false = false;//表示還沒有結帳
       boolean isarrival_true = true;
       boolean ispaybill_true = true;

       if(isDateBefore(stdTime1,nowTime)&&isDateBefore(nowTime,stdTime2))
       {
           try{
           String arrvialtime = "10:00-12:00";

           String sqlget1 = "select TALNO from RESERVATION where ARRIVALDATE= '" + nowDate+ "' and ARRIVALTIME ='"+arrvialtime+"' and ISARRIVAL="+isarrival_false+" and ISPAYBILL = "+ispaybill_false;
           st1 =cn.createStatement();
           rs1 = st1.executeQuery(sqlget1);
           while (rs1.next())
           {
               String temptalno = rs1.getString("talno");
               String sqlupdate = "update T_TABLE set STATE=? where TALNO=?";
               try {
                   PreparedStatement ps = cn.prepareStatement(sqlupdate);
                   ps.setString(1,"reservation");
                   ps.setString(2,temptalno);
                   ps.executeUpdate();
           } catch (SQLException e) {
                   e.printStackTrace();
           }


           }
           String sqlget2 = "select TALNO from RESERVATION where ARRIVALDATE= '" + nowDate+ "' and ARRIVALTIME ='"+arrvialtime+"' and ISARRIVAL="+isarrival_true+" and ISPAYBILL = "+ispaybill_false;
           st2 = cn.createStatement();
           rs2 = st2.executeQuery(sqlget2);
           while (rs2.next())
           {
               String temptalno = rs2.getString("talno");
               String sqlupdate = "update T_TABLE set STATE=? where TALNO=?";
               try {
                   PreparedStatement ps = cn.prepareStatement(sqlupdate);
                   ps.setString(1,"dining");
                   ps.setString(2,temptalno);
                   ps.executeUpdate();
           } catch (SQLException e) {
                   e.printStackTrace();
           }


           }
         String sqlget3 = "select TALNO from RESERVATION where ARRIVALDATE= '" + nowDate+ "' and ARRIVALTIME ='"+arrvialtime+"' and ISARRIVAL="+isarrival_true+" and ISPAYBILL = "+ispaybill_true;
          st3 = cn.createStatement();
          rs3 = st3.executeQuery(sqlget3);
          while (rs3.next())
          {
              String temptalno = rs3.getString("talno");
              String sqlupdate = "update T_TABLE set STATE=? where TALNO=?";
              try {
                  PreparedStatement ps = cn.prepareStatement(sqlupdate);
                  ps.setString(1,"free");
                  ps.setString(2,temptalno);
                  ps.executeUpdate();
          } catch (SQLException e) {
                  e.printStackTrace();
          }


          }

           }catch(SQLException e)
           {
               e.printStackTrace();
           }
           ConnectionDB.close(rs1);
           ConnectionDB.close(st1);
           ConnectionDB.close(rs2);
           ConnectionDB.close(st2);