package portlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ProductsService {
    public ProductsService() {
    }

    public ProductsBean getProducts() throws ClassNotFoundException {
        Connection conn = getConnection();
        ProductsBean products = new ProductsBean();
        if (conn != null) {
            try {
                Statement stmt = conn.createStatement();
                String query =
                    "SELECT DISTINCT product_id, product_name name, cost_price price, 'http://localhost:7101/MyTutorialApplication/' || external_url image_url, category_description, supplier_name " + 
                    "FROM category_translations, products_base, suppliers" + 
                    "    WHERE products_base.category_id = category_translations.category_id" + 
                    "    AND products_base.supplier_id = suppliers.supplier_id " + 
                    "    AND cost_price between 25 and 75 " + 
                    "    order by product_id";
                ResultSet rs = stmt.executeQuery(query);
                while (rs.next()) {
                    ProductDetailsBean productDetails = new ProductDetailsBean();
                    productDetails.setProductId(rs.getString(1));
                    productDetails.setProductName(rs.getString(2));
                    productDetails.setProductPrice(rs.getString(3));
                    productDetails.setImageURL(rs.getString(4));
                    productDetails.setCategoryDescription(rs.getString(5));
                    productDetails.setSupplierName(rs.getString(6));
                    
                    products.addProduct(productDetails);
                }
                conn.close();
            } catch (SQLException sqle) {
                System.out.println("======== Oracle Fusion Middleware Tutorial for WebCenter Developers ======== ");
                System.out.println("Database Connection established successfully but encountered an error while working with the DB:" +
                                   sqle);
                System.out.println("============================================================================ ");
            } catch (Throwable t) {
                System.out.println("======== Oracle Fusion Middleware Tutorial for WebCenter Developers ======== ");
                System.out.println("Error while trying to get Product Details: " +
                                   t);
                System.out.println("============================================================================ ");
            }

        }
        return products;
    }

    public static Connection getConnection() throws ClassNotFoundException {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "fod",
                            "fusion");
        } catch (SQLException sqle) {
            conn = null;
            System.out.println("======== Oracle Fusion Middleware Tutorial for WebCenter Developers ======== ");
            System.out.println("SQL error while trying to  get connection to DB: " +
                               sqle);
            System.out.println("============================================================================ ");
        } catch (Throwable t) {
            conn = null;
            System.out.println("======== Oracle Fusion Middleware Tutorial for WebCenter Developers ======== ");
            System.out.println("Error while trying to get the connection to DB: " +
                               t);
            System.out.println("============================================================================ ");
        }
        return conn;
    }
}
