/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Reservation;

import Bean.ReservationBean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.ResourceBundle;
import javax.servlet.*;
/**
 *
 * @author user
 */
public class DBLayer {
    private String driver;
    private String url;
    private String user;
    private String password;
    private Connection connection;

    public DBLayer()
    {
        ResourceBundle sql = ResourceBundle.getBundle("Reservation.Properties");
        this.driver = sql.getString("driver");
        this.url = sql.getString("url");
        this.user = sql.getString("user");
        this.password = sql.getString("password");
    }
    private boolean loadClass() {
        //Load whatever preloaded driver from SQL properties file.
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    private boolean connect()
    {
        //There is already a connection.
        if(connection!=null)
        {
            return true;
        }
        loadClass();
        try
        {
            connection = DriverManager.getConnection(url, user, password);
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public void close()
    {
        try
        {
            if(connection != null)
            {
                connection.close();
            }
        } catch(SQLException e)
        {
            e.printStackTrace();
        }
        connection = null;
    }
    public boolean Start()
    {
        if(connect())
        {
            try
            {
                connection.setAutoCommit(false);
            } catch (SQLException e)
            {
                e.printStackTrace();
                return false;
            }
            return true;
        }
        System.out.println("Error! Can't connect");
        return false;
    }
    public boolean Commit()
    {
        try
        {
            connection.commit();
        } catch(SQLException e)
        {
            e.printStackTrace();
            return false;

        } finally {
            close();
        }
        return true;
    }

    public boolean AddReservation(String checkIn, String checkOut,String givenName,String familyName,String emailAddress,String mobileNumber,String room,String packageType,String price) throws SQLException{
        if(checkIn == null || checkOut == null || givenName == null || familyName == null || emailAddress == null || mobileNumber == null || room == null || packageType == null || price == null) {

        } else {
            Connection conn = null;
            Statement stmt = null;

            try{
                Class.forName(driver);
            } catch (ClassNotFoundException ex) {
                ex.printStackTrace();
            }

            try {
                conn = DriverManager.getConnection(url,user,password);
                conn.setAutoCommit(false);

                PreparedStatement ps;

                String sqlInsert = "insert into Reservation(checkIn, checkOut, givenName, familyName, emailAddress, mobileNumber, room, packageType, price) values(?,?,?,?,?,?,?,?,?)";
                ps = conn.prepareStatement(sqlInsert);

                ps.setString(1, checkIn);
                ps.setString(2, checkOut);
                ps.setString(3, givenName);
                ps.setString(4, familyName);
                ps.setString(5, emailAddress);
                ps.setString(6, mobileNumber);
                ps.setString(7, room);
                ps.setString(8, packageType);
                ps.setString(9, price);

                ps.executeUpdate();
                conn.commit();
                return true;

            }catch(SQLException ex) {
                ex.printStackTrace();
                if(conn != null) {
                    conn.rollback();
                    throw ex;
                }
            } finally {
                try {
                    if(stmt != null) stmt.close();
                    if(conn != null) conn.close();
                }catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
        return false;
    }

    public ArrayList<ReservationBean> getAllReservationList() throws SQLException {

        ArrayList<ReservationBean> reservationList = new ArrayList<ReservationBean>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset;

        try{
            Class.forName(driver);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }

        try {
            conn = DriverManager.getConnection(url,user,password);
            stmt = conn.createStatement();

            String select = "select * from Reservation";
            rset = stmt.executeQuery(select);

            while (rset.next()) {
                int transactionId = rset.getInt("transactionId");
                String checkIn = rset.getString("checkIn");
                String checkOut = rset.getString("checkOut");
                String givenName = rset.getString("givenName");
                String familyName = rset.getString("familyName");
                String emailAddress = rset.getString("emailAddress");
                String mobileNumber = rset.getString("mobileNumber");
                String room = rset.getString("room");
                String packageType = rset.getString("packageType");
                String price = rset.getString("price");

                ReservationBean row = new ReservationBean();
                row.setTransactionId(transactionId);
                row.setCheckIn(checkIn);
                row.setCheckOut(checkOut);
                row.setGivenName(givenName);
                row.setFamilyName(familyName);
                row.setEmailAddress(emailAddress);
                row.setMobileNumber(mobileNumber);
                row.setRoom(room);
                row.setPackageType(packageType);
                row.setPrice(price);

                reservationList.add(row);
            }
        }catch(SQLException ex) {
            ex.printStackTrace();
        } finally {
            if(conn != null) {
                conn.close();
            }
        }
        return reservationList;
    }

      public int countDeluxeRooms() throws SQLException
    {

        ResultSet results = null;
        int returner = 0;
        int deluxeRoomsOccupied = 0;
        try
        {

            if(Start())
            {

                String preparedSQL = "select room from Reservation";
                PreparedStatement statement = connection.prepareStatement(preparedSQL);
                results = statement.executeQuery();
                while(results.next()) {

                    if(results.getString("room").equalsIgnoreCase("Deluxe Room")) {
                        deluxeRoomsOccupied++;
                    }
                }
                Commit();
            }
        }
        catch (SQLException ex)
        {
            throw ex;
        } finally
        {
            close();

        }
     if(deluxeRoomsOccupied>=10)
      {
          returner = 1;
      }
      else
      {
          returner = 0;
      }
        return returner;
    }

    public int countPremierRooms() throws SQLException
    {

        ResultSet results = null;
        int returner = 0;
        int premierRoomsOccupied = 0;
        try
        {

            if(Start())
            {

                String preparedSQL = "select room from Reservation";
                PreparedStatement statement = connection.prepareStatement(preparedSQL);
                results = statement.executeQuery();
                while(results.next()) {

                    if(results.getString("room").equalsIgnoreCase("Premier Room")) {
                        premierRoomsOccupied++;
                    }
                }
                Commit();
            }
        }
        catch (SQLException ex)
        {
            throw ex;
        } finally
        {
            close();

        }
     if(premierRoomsOccupied>=10)
      {
          returner = 1;
      }
      else
      {
          returner = 0;
      }
        return returner;
    }

    public int countSuperiorRooms() throws SQLException
    {

        ResultSet results = null;
        int returner = 0;
        int superiorRoomsOccupied = 0;
        try
        {

            if(Start())
            {

                String preparedSQL = "select room from Reservation";
                PreparedStatement statement = connection.prepareStatement(preparedSQL);
                results = statement.executeQuery();
                while(results.next()) {

                    if(results.getString("room").equalsIgnoreCase("Superior Room")) {
                        superiorRoomsOccupied++;
                    }
                }
                Commit();
            }
        }
        catch (SQLException ex)
        {
            throw ex;
        } finally
        {
            close();

        }
     if(superiorRoomsOccupied>=10)
      {
          returner = 1;
      }
      else
      {
          returner = 0;
      }
        return returner;
    }

    /*  public String countRooms() throws SQLException
    {

        ResultSet results = null;
        String returner = "";
        int deluxeRoomsOccupied = 0;
        int premierRoomsOccupied = 0;
        int superiorRoomsOccupied = 0;
        try
        {

            if(Start())
            {

                String preparedSQL = "select room from Reservation";
                PreparedStatement statement = connection.prepareStatement(preparedSQL);
                results = statement.executeQuery();
                while(results.next()) {
                    if(results.getString("room").equalsIgnoreCase("Deluxe Room")) {
                        deluxeRoomsOccupied++;
                    } else if(results.getString("room").equalsIgnoreCase("Premier Room")) {
                        premierRoomsOccupied++;
                    }else if(results.getString("room").equalsIgnoreCase("Superior Room")) {
                        superiorRoomsOccupied++;
                    }
                }
                Commit();
            }
        }
        catch (SQLException ex)
        {
            throw ex;
        } finally
        {
            close();

        }
      if(deluxeRoomsOccupied==10)
      {
          returner = returner +  " " + "Deluxe Rooms are full!";
      }
      else
      {
          returner =  returner +  " " + "Deluxe Rooms Available!";
      }
      if(premierRoomsOccupied==10)
      {
         returner = returner +  " " + "Premier Rooms Occupied";
      }
      else
      {
        returner = returner +  " " + "Premier Rooms Available!";
      }
      if(superiorRoomsOccupied==10)
      {
          returner = returner +  " " + "Superior Rooms are full!";
      }
      else
      {
          returner =  returner +  " " + "Superior Rooms Available!";
      }
        return returner;
    }*/




}