package com.abc.hotel;

import java.sql.*;
import javax.ejb.*;
import javax.naming.*;
import javax.sql.*;

import sun.jdbc.rowset.*;

//import weblogic.jdbc.rowset.CachedRowSet;

//import sun.jdbc.

public class SearchHotelBean
    implements SessionBean {
    SessionContext sessionContext;
    public void ejbCreate() throws CreateException {
        /**@todo Complete this method*/
    }

    public void ejbRemove() {
        /**@todo Complete this method*/
    }

    public void ejbActivate() {
        /**@todo Complete this method*/
    }

    public void ejbPassivate() {
        /**@todo Complete this method*/
    }

    public void setSessionContext(SessionContext sessionContext) {
        this.sessionContext = sessionContext;
    }

    public float getQuote(String stockName) {
        /**@todo Complete this method*/
        if (stockName.equalsIgnoreCase("msft")) {
            return 23.50f;
        }
        else {
            return 0.0f;
        }

    }

    public javax.sql.RowSet getMainPageContent(String price_str, String city, String name) {
        /**@todo Complete this method*/

        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        CachedRowSet crs = null;


        String price_sql = "select distinct hotel.hotelid,hotel.pic,hotel.name,hotel.hotel_level,hotel.hotel_desc,hotel.address from house_type,hotel where " +
            price_str +
            " and hotel.hotelid in (select hotelid from hotel where city = '" +
            city +
            "') and hotel.name in (select name from hotel where name like '%" +
            name + "%') and hotel.hotelid = house_type.hotelid";
        //System.out.println(price_sql);
        try {
            conn = getConnection();
            ps = conn.prepareStatement(price_sql);
            rs = ps.executeQuery();

            crs = new CachedRowSet();
            crs.populate(rs);
            rs.close();

        }
        catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        finally {
            if (rs != null) {
                try {
                    rs.close();
                }
                catch (SQLException ex) {
                    System.out.println("Resultset closing error:" + ex);
                }
            }
        }
        if (ps != null) {
            try {
                ps.close();
            }
            catch (SQLException ex) {
                System.out.println("prepare Statement closing error:" + ex);
            }
        }
        if (conn != null) {
            try {
                conn.close();

            }
            catch (SQLException ex) {
                System.out.println("connection closing error:" + ex);
            }
        }

        return crs;
    }

    private Connection getConnection() throws SQLException {
        Context ctx = null;
        DataSource ds = null;
        try {
            ctx = new InitialContext();
            ds = (DataSource) ctx.lookup("oracleds");

        }
        catch (Exception ex) {
            System.out.println("Unable to get a connection from!");
        }
        return ds.getConnection();
    }
    public javax.sql.RowSet getHouseTypeContent(String hotelid,String price_str) {
        /**@todo Complete this method*/

        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        CachedRowSet crs = null;


        String house_type_sql = "select * from house_type where hotelid = "+hotelid+" and "+price_str;
        //System.out.println(price_sql);
        try {
            conn = getConnection();
            ps = conn.prepareStatement(house_type_sql);
            rs = ps.executeQuery();

            crs = new CachedRowSet();
            crs.populate(rs);
            rs.close();

        }
        catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        finally {
            if (rs != null) {
                try {
                    rs.close();
                }
                catch (SQLException ex) {
                    System.out.println("Resultset closing error:" + ex);
                }
            }
        }
        if (ps != null) {
            try {
                ps.close();
            }
            catch (SQLException ex) {
                System.out.println("prepare Statement closing error:" + ex);
            }
        }
        if (conn != null) {
            try {
                conn.close();

            }
            catch (SQLException ex) {
                System.out.println("connection closing error:" + ex);
            }
        }

        return crs;


    }
}