package ordering.custom;

 

import java.util.*;

import java.text.DateFormat;

import java.sql.*;

import ordering.common.vo.PODescriptorVO;

import ordering.common.vo.PurchaseOrderVO;

import ordering.common.vo.AddressVO;

import ordering.common.vo.POLineItemVO;

 

public class OrderingDAO {

 

 

    static public void addPurchaseOrder(PurchaseOrderVO po, Connection conn)

    throws SQLException

    {

        try {

        String sql = null;

        PreparedStatement stmt = null;

 

 

        sql = "INSERT INTO PO ("

            + "POID, "

            + "NUMBER, "

                     + "DATE, "

                     + "bill_to_name, "

                     + "bill_to_address, "

                     + "bill_to_city, "

            + "bill_to_state, "

            + "bill_to_zip, "

                     + "bill_to_country, "

                     + "ship_to_name, "

                     + "ship_to_address, "

                     + "ship_to_city, "

            + "ship_to_state, "

            + "ship_to_zip, "

                     + "ship_to_country, "

                     + "payment_method, "

                     + "total_cost, "

                     + "note, "

            + "STATUS"

 

 

            + ") VALUES ("

            + "?, ?, ?, ?, ?, ?, ?, ? ,?,?, ?, ? ,?, ?, ?, ?,?, ?,?"

            + ")";

 

 

        stmt = conn.prepareStatement(sql);

 

        stmt.setInt(1, po.getPoId());

        stmt.setString(2,po.getPoNumber());

        stmt.setDate(3, new java.sql.Date(po.getDate().getTime()));

        stmt.setString(4,po.getBillTo().getName());

        stmt.setString(5,po.getBillTo().getAddress());

        stmt.setString(6,po.getBillTo().getCity());

        stmt.setString(7,po.getBillTo().getState());

        stmt.setString(8,po.getBillTo().getZip());

        stmt.setString(9,po.getBillTo().getCountry());

        stmt.setString(10,po.getShipTo().getName());

        stmt.setString(11,po.getShipTo().getAddress());

        stmt.setString(12,po.getShipTo().getCity());

        stmt.setString(13,po.getShipTo().getState());

        stmt.setString(14,po.getShipTo().getZip());

        stmt.setString(15,po.getShipTo().getCountry());

        stmt.setString(16,po.getPaymentMethod());

        stmt.setDouble(17,po.getTotalCost());

        stmt.setString(18,po.getNote());

        stmt.setString(19,Constants.STATUS_PENDING);

        stmt.execute();

 

        System.out.println("insert po");

        addLineItems(po.getLineItems(),conn);

        System.out.println("insert lineitems");

        stmt.close();

 

        } catch (Exception se) {

            se.printStackTrace();

        }

    }

 

    static public void addLineItems(Collection lineItems, Connection conn)

    throws SQLException

    {

        String sql = null;

        PreparedStatement stmt = null;

 

 

        sql = "INSERT INTO PO_LINE_ITEM ("

            + "id,PURCHASEORDEREJB_POLINEITEMS, "

                        + "ITEM_NUMBER, "

                        + "ITEM_DESCRIPTION, "

                        + "QUANTITY, "

                        + "UNIT_PRICE,"

                        + "line_total"

            + ") VALUES ("

            + "?, ?, ?, ?, ?, ?, ?"

            + ")";

 

 

        stmt = conn.prepareStatement(sql);

        Iterator iterator = lineItems.iterator();

        while(iterator.hasNext()){

            POLineItemVO lineItem = (POLineItemVO)iterator.next();

            int id = (int) System.currentTimeMillis();

            stmt.setInt(1, id);

 

            stmt.setInt(2,lineItem.getPoId());

            stmt.setString(3, lineItem.getItemNumber());

            stmt.setString(4,lineItem.getUom());

            stmt.setInt(5,lineItem.getQuantity());

            stmt.setDouble(6,lineItem.getUnitPrice());

            stmt.setDouble(7,lineItem.getLineTotal());

            stmt.execute();

        }

        stmt.close();

    }

 

   static public Collection getPODescriptors(int partnerId, String status, Connection conn)

   throws SQLException

   {

        String sql = null;

        ResultSet rs = null;

        PreparedStatement stmt = null;

        Collection descs = new ArrayList();

        try {

 

            sql = "SELECT "

                + "POID, "

                     + "NUMBER, "

                     + "DATE, "

                     + "TOTAL_COST, "

                     + "STATUS "

                + "FROM PO WHERE STATUS = ? or '" + status +"'='"+Constants.STATUS_ALL+"'";

 

            System.out.println(sql);

            stmt = conn.prepareStatement(sql);

            stmt.setString(1, status);

 

            rs = stmt.executeQuery();

 

            while (rs.next()) {

                PODescriptorVO desc = restorePODesc(rs);

                descs.add(desc);

 

            }

            stmt.close();

 

        }

        catch (Exception e) {

            e.printStackTrace();

        }

        System.out.println(descs.size());

        return descs;

 

   }

 

 

   static public PurchaseOrderVO getPurchaseOrder(int id, Connection conn)

   throws SQLException

   {

        PurchaseOrderVO po = null;

        String sql = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;

        Collection descs = new ArrayList();

        sql = "SELECT * FROM PO WHERE POID = ?";

        stmt = conn.prepareStatement(sql);

        stmt.setInt(1, id);

        rs = stmt.executeQuery();

 

        while (rs.next()) {

 

            po = restorePO(rs,conn);

        }

        stmt.close();

        return po;

   }

 

   static public void acceptPurchaseOrder(int id, Connection conn)

   throws SQLException

   {

        String sql = null;

        PreparedStatement stmt = null;

        sql = "UPDATE PO"

            + "STATUS= ?"

            + "WHERE POID = ?";

        stmt = conn.prepareStatement(sql);

 

        stmt.setString(1,Constants.STATUS_ACCEPTED);

        stmt.setInt(2, id);

        stmt.executeUpdate();

        stmt.close();

        createInvoice(id,conn);

   }

 

   static public void createInvoice(int poId, Connection conn)

   throws SQLException

   {

       String sql = null;

       PreparedStatement stmt = null;

 

       sql = "INSERT INTO INVOICE ("

            + "INVOICE_ID, "

            + "INVOICE_NUMBER, "

                     + "INVOICE_DATE, "

                     + "PO_NUMBER, "

                     + "bill_to_name, "

                     + "bill_to_address, "

                     + "bill_to_city "

            + "bill_to_state, "

            + "bill_to_zip, "

                     + "bill_to_country, "

                     + "ship_to_name, "

                     + "ship_to_address, "

                     + "ship_to_city "

            + "ship_to_state, "

            + "ship_to_zip, "

                     + "ship_to_country, "

                     + "payment_method, "

                     + "total_cost, "

                     + "note "

 

            + ") select "

            + ""

            + "from PO where PO_ID = ?";

              stmt.setInt(1, poId);

        stmt = conn.prepareStatement(sql);

        stmt.execute();

        stmt.close();

   }

 

 

    static protected PODescriptorVO restorePODesc(ResultSet rs)

    throws SQLException {

 

        PODescriptorVO desc = new PODescriptorVO();

        desc.setPoId(rs.getInt("POID"));

        desc.setPoNumber(rs.getString("NUMBER"));

        desc.setDate(rs.getTimestamp("DATE"));

        desc.setTotalCost(rs.getDouble("TOTAL_COST"));

        desc.setStatus(rs.getString("STATUS"));

        return desc;

    }

 

    static protected PurchaseOrderVO restorePO(ResultSet rs, Connection conn)

    throws SQLException {

 

        PurchaseOrderVO po = new PurchaseOrderVO();

        po.setPoId(rs.getInt("POID"));

        po.setPoNumber(rs.getString("NUMBER"));

        po.setDate(rs.getTimestamp("DATE"));

        po.setTotalCost(rs.getDouble("TOTAL_COST"));

        po.setStatus(rs.getString("STATUS"));

        AddressVO billAddress = new AddressVO(rs.getString("bill_to_name"),

                rs.getString("bill_to_address"),

                rs.getString("bill_to_city"),

                rs.getString("bill_to_state"),

                rs.getString("bill_to_zip"),

                rs.getString("bill_to_country"));

        po.setBillTo(billAddress);

 

        AddressVO shipAddress = new AddressVO(rs.getString("ship_to_name"),

                rs.getString("ship_to_address"),

                rs.getString("ship_to_city"),

                rs.getString("ship_to_state"),

                rs.getString("ship_to_zip"),

                rs.getString("ship_to_country"));

 

        po.setShipTo(shipAddress);

 

        po.setLineItems(restorePOLineItems(rs.getInt("POID"),conn));

        return po;

    }

 

    static protected POLineItemVO restorePOLineItem(ResultSet rs)

    throws SQLException {

        POLineItemVO lineItem = new POLineItemVO(

                rs.getInt("ID"),

                rs.getInt("PURCHASEORDEREJB_POLINEITEMS"),

                rs.getString("ITEM_NUMBER"),

                rs.getString("ITEM_DESCRIPTION"),

                rs.getInt("QUANTITY"),

                rs.getFloat("UNIT_PRICE"),

                rs.getFloat("LINE_TOTAL"));

        return lineItem;

    }

 

    static protected Collection restorePOLineItems(int poId, Connection conn)

    throws SQLException

    {

 

            Collection lineItems  = new ArrayList();

            String sql = null;

            ResultSet rs = null;

            PreparedStatement stmt = null;

            Collection descs = new ArrayList();

            sql = "SELECT  ID, "

                + "PURCHASEORDEREJB_POLINEITEMS, "

                            + "ITEM_NUMBER, "

                            + "ITEM_DESCRIPTION, "

                            + "QUANTITY, "

                            + "UNIT_PRICE,"

                            + "line_total  "

                + " FROM PO_LINE_ITEM WHERE PURCHASEORDEREJB_POLINEITEMS = ?";

 

            stmt = conn.prepareStatement(sql);

            stmt.setInt(1, poId);

 

            rs = stmt.executeQuery();

            while (rs.next()) {

 

                POLineItemVO lineItem = restorePOLineItem(rs);

 

                lineItems.add(lineItem);

            }

            stmt.close();

            return lineItems;

 

    }

}