# OrderDAOImpl.java

    package org.example.customerdao;

    import java.sql.Connection;
    import java.sql.Date;
    import java.sql.PreparedStatement;

    /*
    * This is free and unencumbered software released into the public domain.
    * Anyone is free to copy, modify, publish, use, compile, sell, or distribute this software,
    * either in source code form or as a compiled binary, for any purpose, commercial or
    * non-commercial, and by any means.
    *
    * In jurisdictions that recognize copyright laws, the author or authors of this
    * software dedicate any and all copyright interest in the software to the public domain.
    * We make this dedication for the benefit of the public at large and to the detriment of
    * our heirs and successors. We intend this dedication to be an overt act of relinquishment in
    * perpetuity of all present and future rights to this software under copyright law.
    *
    * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
    * INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
    * PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES
    * OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
    * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
    *
    * For more information, please refer to: https://unlicense.org/
    */

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.time.LocalDate;
    import java.util.ArrayList;
    import java.util.List;

    import javax.sql.DataSource;

    import org.apache.commons.dbutils.DbUtils;
    import org.apache.logging.log4j.LogManager;
    import org.apache.logging.log4j.Logger;
    import org.example.customer.Customer;
    import org.example.customer.Order;
    import org.example.customer.OrderItem;
    import org.example.customer.utility.CustomerEntity;
    import org.example.customerdao.utility.ErrorFormatter;
    import org.example.websecurity.UserCredentials;

    /**
     * This is the OrderDAO Implementation for the Customer DAO component of the
     * Customer Web Application. This will be the primary Order database exposure
     * for the Customer Web Layer.
     *
     * @author Jonathan Earl
     * @since 1.0
     *
     */
    public class OrderDAOImpl
        implements OrderDAO
    {

        private static final Logger LOG = LogManager.getLogger();

        private DataSource myReadOnlyDS = null;
        private DataSource myReadWriteDS = null;

        private static final String FIND_ALL_SQL = "Select * from Orders";
        private static final String FIND_BY_ID_SQL = "Select * from Orders where ID = ?";
        private static final String FIND_ORDER_ITEM_BY_ID_SQL = "Select * from Order_Item where ORDER_ID = ?";
        private static final String FIND_BY_CUSTOMER_SQL = "Select * from Orders where CUSTOMER_ID = ?";
        private static final String ADD_ORDER_SQL = "Insert into Orders(ORDER_DATE,CUSTOMER_ID,TOTAL_AMOUNT,ORDER_NUMBER)"
                + " VALUES(?,?,?,?)";
        private static final String ADD_ORDER_ITEM_SQL = "Insert into Order_Item(ORDER_ID,PRODUCT_ID,UNIT_PRICE,QUANTITY)"
                + " VALUES(?,?,?,?)";
        private static final String UPDATE_SQL = "Update Orders set ORDER_DATE = ?, CUSTOMER_ID = ?, "
                + "TOTAL_AMOUNT = ?, ORDER_NUMBER = ?" + " WHERE ID = ?";
        private static final String DELETE_ORDER_SQL = "Delete from Orders where id = ?";
        private static final String DELETE_ORDER_ITEM_SQL = "Delete from Order_Item where ORDER_ID = ?";

        /**
         * {@inheritDoc}
         */
        @Override
        public void setReadOnlyDS(final DataSource readOnlyDS)
        {
            LOG.debug("Setting the ReadOnly DataSource");
            this.myReadOnlyDS = readOnlyDS;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public void setReadWriteDS(final DataSource readWriteDS)
        {
            LOG.debug("Setting the ReadWrite DataSource");
            this.myReadWriteDS = readWriteDS;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public List<Order> findAllOrders(final UserCredentials credentials)
        {
            LOG.debug("findAllOrders");

            if (credentials == null || !credentials.hasRole("worker"))
            {
                LOG.error("findAllOrders - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            PreparedStatement findAll = null;
            Connection conn = null;
            ResultSet results = null;
            List<Order> data = new ArrayList<Order>();
            try
            {
                conn = myReadOnlyDS.getConnection();
                findAll = conn.prepareStatement(FIND_ALL_SQL);

                results = findAll.executeQuery();

                while (results.next())
                {
                    Order order = buildOrder(results);
                    List<OrderItem> items = findAllOrderItems(credentials, order);
                    order.setOrderItems(items);
                    data.add(order);
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error finding all Orders");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, findAll, results);
            }
            return data;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public List<OrderItem> findAllOrderItems(final UserCredentials credentials, final Order order)
        {
            LOG.debug("findAllOrderItems");

            if (credentials == null || !credentials.hasRole("worker"))
            {
                LOG.error("findAllOrderItems - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            if (order == null)
            {
                LOG.error("findAllOrderItems - Order required");
                throw new IllegalArgumentException("Order required");
            }

            PreparedStatement findAll = null;
            Connection conn = null;
            ResultSet results = null;
            List<OrderItem> data = new ArrayList<OrderItem>();
            try
            {
                conn = myReadOnlyDS.getConnection();
                findAll = conn.prepareStatement(FIND_ORDER_ITEM_BY_ID_SQL);
                findAll.setInt(1, order.getId());

                results = findAll.executeQuery();

                while (results.next())
                {
                    data.add(buildOrderItem(results));
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error finding all OderItems");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, findAll, results);
            }
            return data;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public Order findOrderById(final UserCredentials credentials, final int id)
        {
            LOG.debug("findOrderById");

            if (credentials == null || !credentials.hasRole("worker"))
            {
                LOG.error("findOrderById - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            PreparedStatement findById = null;
            Connection conn = null;
            ResultSet results = null;
            Order order = null;
            try
            {
                conn = myReadOnlyDS.getConnection();
                findById = conn.prepareStatement(FIND_BY_ID_SQL);

                findById.setInt(1, id);
                results = findById.executeQuery();

                if (results.next())
                {
                    order = buildOrder(results);
                    List<OrderItem> items = findAllOrderItems(credentials, order);
                    order.setOrderItems(items);
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error finding Product by id");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, findById, results);
            }
            return order;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public List<Order> findOrdersByCustomer(final UserCredentials credentials, final Customer customer)
        {
            LOG.debug("findOrdersByCustomer");

            if (credentials == null || !credentials.hasRole("worker"))
            {
                LOG.error("findOrdersByCustomer - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            if (customer == null)
            {
                LOG.error("findOrdersByCustomer - Customer required");
                throw new IllegalArgumentException("Customer required");
            }

            PreparedStatement findAll = null;
            Connection conn = null;
            ResultSet results = null;
            List<Order> data = new ArrayList<Order>();
            try
            {
                conn = myReadOnlyDS.getConnection();
                findAll = conn.prepareStatement(FIND_BY_CUSTOMER_SQL);
                findAll.setInt(1, customer.getId());

                results = findAll.executeQuery();

                while (results.next())
                {
                    Order order = buildOrder(results);
                    List<OrderItem> items = findAllOrderItems(credentials, order);
                    order.setOrderItems(items);
                    data.add(order);
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error finding all Orders by Customer");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, findAll, results);
            }
            return data;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public int addOrder(final UserCredentials credentials, final Order order)
        {
            LOG.debug("addOrder");

            if (credentials == null || !credentials.hasRole("worker"))
            {
                LOG.error("addOrder - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            if (order == null)
            {
                LOG.error("addOrder - null Order was provided");
                throw new IllegalArgumentException("A Order must be provided");
            }

            PreparedStatement addOrder = null;
            Connection conn = null;
            ResultSet results = null;
            int generatedOrderId = 0;
            try
            {
                conn = myReadWriteDS.getConnection();
                addOrder = conn.prepareStatement(ADD_ORDER_SQL, PreparedStatement.RETURN_GENERATED_KEYS);

                LocalDate orderDate = order.getOrderDate();
                int customerId = order.getCustomerId();
                double totalAmount = order.getTotalAmount();
                String orderNumber = order.getOrderNumber();

                addOrder.setDate(1, Date.valueOf(orderDate));
                addOrder.setInt(2, customerId);
                addOrder.setDouble(3, totalAmount);
                addOrder.setString(4, orderNumber);
                addOrder.executeUpdate();
                results = addOrder.getGeneratedKeys();
                if (results.next())
                {
                    generatedOrderId = results.getInt(1);
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error adding Product");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(results);
                DbUtils.closeQuietly(addOrder);
            }

            PreparedStatement addOrderItem = null;
            try
            {
                addOrderItem = conn.prepareStatement(ADD_ORDER_ITEM_SQL);
                List<OrderItem> items = order.getOrderItems();
                for (OrderItem current : items)
                {
                    int orderId = generatedOrderId;
                    int productId = current.getProductId();
                    double unitPrice = current.getUnitPrice();
                    int quantity = current.getQuantity();

                    addOrderItem.setInt(1, orderId);
                    addOrderItem.setInt(2, productId);
                    addOrderItem.setDouble(3, unitPrice);
                    addOrderItem.setInt(4, quantity);
                    addOrderItem.executeUpdate();
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error adding Product");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(addOrderItem);
                DbUtils.closeQuietly(conn);
            }
            return generatedOrderId;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public void updateOrder(final UserCredentials credentials, final Order order)
        {
            LOG.debug("updateOrder");

            if (credentials == null || !credentials.hasRole("manager"))
            {
                LOG.error("updateProduct - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            if (order == null)
            {
                LOG.error("updateOrder - null Order was provided");
                throw new IllegalArgumentException("A Order must be provided");
            }

            LOG.debug("Looking up Order");
            Order temp = findOrderById(credentials, order.getId());
            if (temp == null)
            {
                LOG.error("updateOrder - Order not found");
                throw new IllegalArgumentException("Order not found");
            }

            PreparedStatement updateOrder = null;
            PreparedStatement addOrderItem = null;
            PreparedStatement deleteOrderItem = null;
            Connection conn = null;
            try
            {
                conn = myReadWriteDS.getConnection();
                updateOrder = conn.prepareStatement(UPDATE_SQL);
                addOrderItem = conn.prepareStatement(ADD_ORDER_ITEM_SQL);
                deleteOrderItem = conn.prepareStatement(DELETE_ORDER_ITEM_SQL);

                int id = order.getId();
                LocalDate orderDate = order.getOrderDate();
                int customerId = order.getCustomerId();
                double totalAmount = order.getTotalAmount();
                String orderNumber = order.getOrderNumber();

                updateOrder.setDate(1, Date.valueOf(orderDate));
                updateOrder.setInt(2, customerId);
                updateOrder.setDouble(3, totalAmount);
                updateOrder.setString(4, orderNumber);
                updateOrder.setInt(5, id);

                updateOrder.executeUpdate();

                deleteOrderItem.setInt(1, id);
                deleteOrderItem.executeUpdate();

                List<OrderItem> items = order.getOrderItems();

                for (OrderItem current : items)
                {
                    int orderId = id;
                    int productId = current.getProductId();
                    double unitPrice = current.getUnitPrice();
                    int quantity = current.getQuantity();

                    addOrderItem.setInt(1, orderId);
                    addOrderItem.setInt(2, productId);
                    addOrderItem.setDouble(3, unitPrice);
                    addOrderItem.setInt(4, quantity);

                    addOrderItem.executeUpdate();
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error updating Product");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(updateOrder);
                DbUtils.closeQuietly(deleteOrderItem);
                DbUtils.closeQuietly(addOrderItem);
                DbUtils.closeQuietly(conn);
            }
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public void deleteEntity(final UserCredentials credentials, final CustomerEntity entity)
        {
            LOG.debug("deleteOrder");

            if (entity == null)
            {
                LOG.error("deleteOrder - null Order was provided");
                throw new IllegalArgumentException("An Order must be provided");
            }
            if (!(entity instanceof Order))
            {
                LOG.error("deleteOrder - invalid CustomerEntity was provided");
                throw new IllegalArgumentException("An Order must be provided");
            }
            deleteEntity(credentials, entity.getId());
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public void deleteEntity(final UserCredentials credentials, final int id)
        {
            LOG.debug("deleteOrder");

            if (credentials == null || !credentials.hasRole("manager"))
            {
                LOG.error("deleteOrder - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            PreparedStatement deleteOrderItems = null;
            PreparedStatement deleteOrder = null;
            Connection conn = null;
            try
            {
                conn = myReadWriteDS.getConnection();
                deleteOrderItems = conn.prepareStatement(DELETE_ORDER_ITEM_SQL);
                deleteOrder = conn.prepareStatement(DELETE_ORDER_SQL);

                deleteOrderItems.setInt(1, id);
                deleteOrderItems.executeUpdate();

                deleteOrder.setInt(1, id);
                deleteOrder.executeUpdate();
            }
            catch (SQLException sqle)
            {
                LOG.error("Error updating Product");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(deleteOrderItems);
                DbUtils.closeQuietly(deleteOrder);
                DbUtils.closeQuietly(conn);
            }
        }

        /**
         * {@inheritDoc}
         * <P>
         * Order are deleteable.
         */
        @Override
        public boolean isDeleteable(final UserCredentials credentials, final int id)
        {
            LOG.debug("isDeleteable");

            if (credentials == null || !credentials.hasRole("manager"))
            {
                LOG.error("isDeleteable - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }
            return true;
        }

        /**
         * {@inheritDoc}
         * <P>
         * Order are deleteable.
         */
        @Override
        public boolean isDeleteable(final UserCredentials credentials, final CustomerEntity entity)
        {
            LOG.debug("isDeleteable");

            if (credentials == null || !credentials.hasRole("manager"))
            {
                LOG.error("isDeleteable - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }
            if (entity == null)
            {
                LOG.error("isDeleteable - null Order was provided");
                throw new IllegalArgumentException("An Order must be provided");
            }
            if (!(entity instanceof Order))
            {
                LOG.error("deleteOrder - invalid CustomerEntity was provided");
                throw new IllegalArgumentException("An Order must be provided");
            }
            return true;
        }

        private Order buildOrder(final ResultSet results) throws SQLException
        {
            Order current = new Order();

            current.setId(results.getInt("ID"));
            current.setOrderDate(results.getDate("ORDER_DATE").toLocalDate());
            //  java.sql.Date sqlDate = java.sql.Date.valueOf( localDate )
            current.setCustomerId(results.getInt("CUSTOMER_ID"));
            current.setTotalAmount(results.getDouble("TOTAL_AMOUNT"));
            current.setOrderNumber(results.getString("ORDER_NUMBER"));

            LOG.trace("Found Order: " + current.toString());

            return current;
        }

        private OrderItem buildOrderItem(final ResultSet results) throws SQLException
        {
            OrderItem current = new OrderItem();

            current.setId(results.getInt("ID"));
            current.setOrderId(results.getInt("ORDER_ID"));
            current.setProductId(results.getInt("PRODUCT_ID"));
            current.setUnitPrice(results.getDouble("UNIT_PRICE"));
            current.setQuantity(results.getInt("QUANTITY"));

            LOG.trace("Found Order: " + current.toString());

            return current;
        }
    }