# To change this template, choose Tools | Templates
# and open the template in the editor.

jdbc.drivers:   com.mysql.jdbc.Driver
user:       gast
password:   gast
url:        jdbc:mysql://localhost/classicmodels
getProducts:    select * from Products
getOrders:      select * from Orders



-----------------------------------------

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package be.hogent.iii.cmodels.bo;

import be.hogent.iii.cmodels.excepties.JDBCExceptie;
import be.hogent.iii.cmodels.interfaces.IDataStorage;
import be.hogent.iii.cmodels.interfaces.IOrder;
import be.hogent.iii.cmodels.interfaces.IOrderDetail;
import be.hogent.iii.cmodels.interfaces.ICustomer;
import be.hogent.iii.cmodels.interfaces.IProduct;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.List;
import java.util.Properties;
import java.util.PropertyResourceBundle;
import java.util.ResourceBundle;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author kvdw
 */
public class JDBCDataStorage implements IDataStorage {

    private PropertyResourceBundle properties;

    private Connection maakConnectie() {
        Connection conn = null;
        properties = (PropertyResourceBundle) PropertyResourceBundle.getBundle("be.hogent.iii.cmodels.db");

        // Every example in the JDBC world has the jdbc.drivers property set at the system level.
        Properties p = System.getProperties();
        p.setProperty("jdbc.drivers", properties.getString("jdbc.drivers"));
        System.setProperties(p);
        try {
            /* Instantiate the new driver.  This is the most reliable way to load a jdbc driver because some
            implementations do not get completely initialized by the getConnection call in the DriverManager.
            This forces the driver class to completely load and initialize a first new class instance.
             */
            System.out.println(properties.getString("jdbc.drivers"));
            try {
                Object c = Class.forName(properties.getString("jdbc.drivers")).newInstance();
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(JDBCDataStorage.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (InstantiationException ex) {
            System.out.println(ex.getMessage());
        } catch (IllegalAccessException ex) {
            System.out.println(ex.getMessage());
        }

        /* The JDBC driver has a number of connection creation calls.  Here we use the property version.  By
        using the enumeration of the keys that the owner of the properties file has listed, we can make
        this code sequence immune to the various different properties used by a particular JDBC driver
        by copying each resource bundle property into a standard property class property list.
         */
        Properties dbp = new Properties();
        for (Enumeration e = properties.getKeys(); e.hasMoreElements();) {
            String prop = (String) e.nextElement();
            dbp.setProperty(prop, properties.getString(prop));
        }
        try {
            conn = DriverManager.getConnection(properties.getString("url"), dbp);
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }

        return conn;
    }

    public JDBCDataStorage() {
    }

    private IProduct leesProduct(ResultSet result) {

        IProduct p;
        String productCode, productName, productLine, productScale, productVendor, productDescription;
        int quantityInStock;
        double buyPrice, MSRP;
        try {
            productCode = result.getString("productCode");
            productName = result.getString("productName");
            productLine = result.getString("productLine");
            productScale = result.getString("productScale");
            productVendor = result.getString("productVendor");
            productDescription = result.getString("productDescription");
            quantityInStock = result.getInt("quantityInStock");
            buyPrice = result.getDouble("buyPrice");
            MSRP = result.getDouble("MSRP");
            return new Product(productCode, productName, productLine, productScale, productVendor, productDescription,
                    quantityInStock, buyPrice, MSRP);
        } catch (SQLException ex) {
            Logger.getLogger(JDBCDataStorage.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }

    }

    private IOrder leesOrder(ResultSet result) {
        IOrder o = null;
        int orderNumber, customerNumber;
        Date orderDate, requiredDate, shippedDate;
        String status, comments;
        try {
            orderNumber = result.getInt("orderNumber");
            orderDate = result.getDate("orderDate");
            requiredDate = result.getDate("requiredDate");
            shippedDate = result.getDate("shippedDate");
            status = result.getString("stauts");
            comments = result.getString("comments");
            customerNumber = result.getInt("customerNumber");

            List<IOrderDetail> details =  null; //details inlezen

            o = new Order(orderNumber, orderDate, requiredDate, shippedDate, status, comments, customerNumber,details);
        } catch (SQLException ex) {
            Logger.getLogger(JDBCDataStorage.class.getName()).log(Level.SEVERE, null, ex);
        }
        return o;

    }

    public List<IProduct> getProducts() throws JDBCExceptie {


        //Connectie maken
        Connection conn = maakConnectie();



        //Init
        Statement st = null;
        List<IProduct> lijst = new ArrayList<IProduct>();



        //Query
        try {
            st = conn.createStatement();

            try {
                ResultSet result = st.executeQuery(properties.getString("getProducts"));

                result.first();
                lijst.add(leesProduct(result));
                while (result.next()) {
                    lijst.add(leesProduct(result));

                }

            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            } finally {
                st.close();

            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }



        //Verbindingen sluiten
        try {
            st.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(JDBCDataStorage.class.getName()).log(Level.SEVERE, null, ex);
        }


        return lijst;
    }



    public List<ICustomer> getCustomers() throws JDBCExceptie {
        List<ICustomer> customers = null;



        return customers;
    }

    //Lijst van bestellingen zonder details
    public List<IOrder> getOrders() throws JDBCExceptie {
        //Connectie
        Connection conn = maakConnectie();

        //Init
        List<IOrder> orders = null;
        Statement st = null;

        //Query
       try {
            st = conn.createStatement();

            try {
                ResultSet result = st.executeQuery(properties.getString("getrders"));

                result.first();
                orders.add(leesOrder(result));
                while (result.next()) {
                    orders.add(leesOrder(result));

                }

            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            } finally {
                st.close();

            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }



        //Verbindingen sluiten
        try {
            st.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(JDBCDataStorage.class.getName()).log(Level.SEVERE, null, ex);
        }


        return orders;
    }

    //Lijst van bestellingen van een gegeven klant (zonder details)
    public List<IOrder> getOrders(int customerNumber) throws JDBCExceptie {
        //Connectie
        Connection conn = maakConnectie();

        //Init
        List<IOrder> orders = null;
        Statement st = null;

        //Query
       try {
            st = conn.createStatement();

            try {
                ResultSet result = st.executeQuery(properties.getString("getCustomerOrders"));

                result.first();
                orders.add(leesOrder(result));
                while (result.next()) {
                    orders.add(leesOrder(result));

                }

            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            } finally {
                st.close();

            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }



        //Verbindingen sluiten
        try {
            st.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(JDBCDataStorage.class.getName()).log(Level.SEVERE, null, ex);
        }


        return orders;
    }

    public boolean addProduct(IProduct product) throws JDBCExceptie {
        boolean resultaat = false;


        return resultaat;
    }

    public boolean addOrder(IOrder order) throws JDBCExceptie {
        boolean resultaat = false;

        return resultaat;

    }

    public boolean addCustomer(ICustomer customer) throws JDBCExceptie {
        boolean resultaat = false;



        return resultaat;
    }

    public boolean modifyCustomer(ICustomer customer) throws JDBCExceptie {
        boolean resultaat = false;



        return resultaat;
    }

    public boolean deleteCustomer(int customerNumber) throws JDBCExceptie {
        boolean resultaat = false;



        return resultaat;
    }

    public double getTotal(int customerNumber) throws JDBCExceptie {
        double resultaat = 0;



        return resultaat;
    }
}