| import java.sql.* ; |
| import java.math.* ; |
| |
| public class Sales |
| { |
| // The LOWERPRICE procedure determines which items are selling poorly (that |
| // is, have less than 50 orders) and lowers the price of these items in the |
| // database by 10 percent. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#lowerprice-procedure |
| // for more documentation. |
| public static void lowerPrice() throws SQLException |
| { |
| Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| PreparedStatement getParts = |
| conn.prepareStatement( "SELECT p.partnum, " |
| + "SUM(qty_ordered) AS qtyOrdered " |
| + "FROM trafodion.sales.parts p " |
| + "LEFT JOIN trafodion.sales.odetail o " |
| + "ON p.partnum = o.partnum " |
| + "GROUP BY p.partnum" |
| ) ; |
| |
| PreparedStatement updateParts = |
| conn.prepareStatement( "UPDATE trafodion.sales.parts " |
| + "SET price = price * 0.9 " |
| + "WHERE partnum = ?" |
| ) ; |
| |
| ResultSet rs = getParts.executeQuery() ; |
| while ( rs.next() ) |
| { |
| BigDecimal qtyOrdered = rs.getBigDecimal( 2 ) ; |
| |
| if (( qtyOrdered == null ) || ( qtyOrdered.intValue() < 50 ) ) |
| { |
| BigDecimal partnum = rs.getBigDecimal( 1 ) ; |
| updateParts.setBigDecimal( 1, partnum ) ; |
| updateParts.executeUpdate() ; |
| } |
| } |
| |
| rs.close() ; |
| conn.close() ; |
| |
| } |
| |
| // The DAILYORDERS procedure accepts a date and returns the number of |
| //orders on that date to an output parameter. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#dailyorders-procedure |
| // for additional documenation. |
| public static void numDailyOrders( Date date |
| , int[] numOrders |
| ) throws SQLException |
| { |
| Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| PreparedStatement getNumOrders = |
| conn.prepareStatement( "SELECT COUNT(order_date) " |
| + "FROM trafodion.sales.orders " |
| + "WHERE order_date = ?" |
| ) ; |
| |
| getNumOrders.setDate( 1, date ) ; |
| |
| ResultSet rs = getNumOrders.executeQuery() ; |
| rs.next() ; |
| |
| numOrders[0] = rs.getInt( 1 ) ; |
| |
| rs.close() ; |
| conn.close() ; |
| |
| } |
| |
| // The MONTHLYORDERS procedure accepts an integer representing the month |
| // and returns the number of orders during that month to an output parameter. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#monthlyorders-procedure |
| // for more documentation. |
| public static void numMonthlyOrders( int month |
| , int[] numOrders |
| ) throws SQLException |
| |
| { |
| if ( month < 1 || month > 12 ) |
| { |
| throw new SQLException( "Invalid value for month. " |
| + "Retry the CALL statement " |
| + "using a number from 1 to 12 " |
| + "to represent the month." |
| , "38001" |
| ) ; |
| } |
| |
| Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| PreparedStatement getNumOrders = |
| conn.prepareStatement( "SELECT COUNT( month( order_date ) ) " |
| + "FROM trafodion.sales.orders " |
| + "WHERE month( order_date ) = ?" |
| ) ; |
| |
| getNumOrders.setInt( 1, month ) ; |
| |
| ResultSet rs = getNumOrders.executeQuery() ; |
| rs.next() ; |
| |
| numOrders[0] = rs.getInt(1) ; |
| |
| rs.close() ; |
| conn.close(); |
| |
| } |
| |
| // The TOTALPRICE procedure accepts the quantity, shipping speed, and price |
| // of an item, calculates the total price, including tax and shipping |
| // charges, and returns the total price to an input/output parameter. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#totalprice-procedure |
| // for more documentation. |
| public static void totalPrice( BigDecimal qtyOrdered |
| , String shippingSpeed |
| , BigDecimal[] price |
| ) throws SQLException |
| { |
| BigDecimal shipcharge = new BigDecimal( 0 ) ; |
| |
| if ( shippingSpeed.equals( "economy" ) ) |
| { |
| shipcharge = new BigDecimal( 1.95 ) ; |
| } |
| else if ( shippingSpeed.equals( "standard" ) ) |
| { |
| shipcharge = new BigDecimal( 4.99 ) ; |
| } |
| else if ( shippingSpeed.equals( "nextday" ) ) |
| { |
| shipcharge = new BigDecimal( 14.99 ) ; |
| } |
| else |
| { |
| throw new SQLException( "Invalid value for shipping speed. " |
| + "Retry the CALL statement using " |
| + "'economy' for 7 to 9 days, " |
| + "'standard' for 3 to 5 days, or " |
| + "'nextday' for one day." |
| , "38002" |
| ) ; |
| } |
| |
| BigDecimal subtotal = price[0].multiply( qtyOrdered ) ; |
| BigDecimal tax = new BigDecimal( 0.0825 ) ; |
| BigDecimal taxcharge = subtotal.multiply( tax ) ; |
| BigDecimal charges = taxcharge.add( shipcharge ) ; |
| BigDecimal totalprice = subtotal.add( charges ) ; |
| |
| totalprice = totalprice.setScale( 2, BigDecimal.ROUND_HALF_EVEN ) ; |
| price[0] = totalprice ; |
| |
| } |
| |
| // The PARTDATA procedure accepts a part number and returns this |
| // information about the part: |
| // |
| // * Part description, price, and quantity available as output parameters. |
| // * A result set that contains rows from the ORDERS table about when this part was ordered. |
| // * A result set that contains rows from the PARTLOC table, listing locations that have this |
| // part in stock and the quantity they have on hand. |
| // * A result set that contains rows from the PARTSUPP table for suppliers who carry this part. |
| // * A result set that contains rows from the EMPLOYEE table for sales reps who have sold this part. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#partdata-procedure |
| // for more documentation. |
| public static void partData( int partNum |
| , String[] partDescription |
| , BigDecimal[] unitPrice |
| , int[] qtyAvailable |
| , ResultSet[] orders |
| , ResultSet[] locations |
| , ResultSet[] suppliers |
| , ResultSet[] reps |
| ) throws SQLException |
| { |
| |
| Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| // Retrieve detail about this part into the output parameters |
| PreparedStatement getPartInfo = |
| conn.prepareStatement( "SELECT P.partdesc, P.price, P.qty_available " |
| + "FROM trafodion.sales.parts P " |
| + "WHERE partnum = ? " |
| ) ; |
| |
| getPartInfo.setInt( 1, partNum ) ; |
| |
| ResultSet rs = getPartInfo.executeQuery() ; |
| rs.next() ; |
| |
| partDescription[0] = rs.getString( 1 ) ; |
| unitPrice[0] = rs.getBigDecimal( 2 ) ; |
| qtyAvailable[0] = rs.getInt( 3 ) ; |
| |
| rs.close(); |
| |
| // Return a result set of rows from the ORDERS table listing orders |
| // that included this part. Each ORDERS row is augmented with the |
| // quantity of this part that was ordered. |
| PreparedStatement getOrders = |
| conn.prepareStatement( "SELECT O.*, QTY.QTY_ORDERED " |
| + "FROM trafodion.sales.orders O " |
| + " , ( select ordernum, sum(qty_ordered) as QTY_ORDERED " |
| + " from trafodion.sales.odetail " |
| + " where partnum = ? " |
| + " group by ordernum ) QTY " |
| + "WHERE O.ordernum = QTY.ordernum " |
| + "ORDER BY O.ordernum " |
| ) ; |
| |
| |
| getOrders.setInt( 1, partNum ) ; |
| orders[0] = getOrders.executeQuery() ; |
| |
| // Return a result set of rows from the PARTLOC table listing |
| // locations that have this part in stock and the quantity they |
| // have on hand. |
| PreparedStatement getLocations = |
| conn.prepareStatement( "SELECT * " |
| + "FROM trafodion.invent.partloc " |
| + " WHERE partnum = ? " |
| ) ; |
| |
| getLocations.setInt( 1, partNum ) ; |
| locations[0] = getLocations.executeQuery() ; |
| |
| // Return a result set of rows from the PARTSUPP table listing |
| // suppliers who supply this part. |
| PreparedStatement getSuppliers = |
| conn.prepareStatement( "SELECT * " |
| + "FROM trafodion.invent.partsupp " |
| + "WHERE partnum = ? " |
| ) ; |
| |
| getSuppliers.setInt( 1, partNum ) ; |
| suppliers[0] = getSuppliers.executeQuery() ; |
| |
| // Return a result set of rows from the EMPLOYEE table listing |
| // sales reps that have sold this part. |
| PreparedStatement getReps = |
| conn.prepareStatement( "SELECT * " |
| + "FROM trafodion.persnl.employee " |
| + "WHERE empnum in ( SELECT O.salesrep " |
| + " FROM trafodion.sales.orders O, " |
| + " trafodion.sales.odetail D " |
| + " WHERE D.partnum = ? " |
| + " AND O.ordernum = D.ordernum ) " |
| + "ORDER BY empnum " |
| ) ; |
| |
| getReps.setInt( 1, partNum ) ; |
| reps[0] = getReps.executeQuery() ; |
| |
| } |
| |
| // The ORDERSUMMARY procedure accepts a date, which is formatted as a |
| // string, and returns this information about the orders on or after that |
| // date: |
| // |
| // * The number of orders as an output parameter |
| // * A result set that contains one row for each order. Each row contains |
| // fields for the order number, the number of parts ordered, total dollar |
| // amount, order date, and the name of the sales representative. |
| // * A result set that contains details about each order. Each order has |
| // one or more rows that provide details about the ordered parts. Each row |
| // contains fields for the order number, part number, unit price, quantity |
| // ordered, and part description. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#ordersummary-procedure |
| // for more documentation. |
| public static void orderSummary( java.lang.String onOrAfter |
| , long[] numOrders |
| , java.sql.ResultSet[] orders |
| , java.sql.ResultSet[] detail |
| ) throws SQLException |
| { |
| java.lang.String s ; |
| |
| java.sql.Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| // Get the number of orders on or after this date |
| s = "SELECT COUNT(ordernum) FROM trafodion.sales.orders " |
| + "WHERE order_date >= CAST(? AS DATE) " |
| ; |
| |
| java.sql.PreparedStatement ps1 = conn.prepareStatement( s ) ; |
| ps1.setString( 1, onOrAfter ) ; |
| |
| java.sql.ResultSet rs = ps1.executeQuery() ; |
| rs.next() ; |
| |
| numOrders[0] = rs.getLong( 1 ) ; |
| rs.close() ; |
| |
| // Open a result set for order num, order info rows |
| s = "SELECT amounts.*, orders.order_date, emps.last_name " |
| + "FROM ( SELECT " |
| + " o.ordernum " |
| + " , COUNT(d.partnum) AS num_parts " |
| + " , SUM(d.unit_price * d.qty_ordered) AS amount " |
| + " FROM trafodion.sales.orders o, trafodion.sales.odetail d " |
| + " WHERE o.ordernum = d.ordernum " |
| + " AND o.order_date >= CAST(? AS DATE) " |
| + " GROUP BY o.ordernum " |
| + " ) amounts " |
| + " , trafodion.sales.orders orders " |
| + " , trafodion.persnl.employee emps " |
| + "WHERE amounts.ordernum = orders.ordernum " |
| + " AND orders.salesrep = emps.empnum " |
| + "ORDER BY orders.ordernum " |
| ; |
| |
| java.sql.PreparedStatement ps2 = conn.prepareStatement( s ) ; |
| ps2.setString( 1, onOrAfter ) ; |
| orders[0] = ps2.executeQuery() ; |
| |
| // Open a result set for order detail rows |
| s = "SELECT d.*, p.partdesc " |
| + "FROM trafodion.sales.odetail d, trafodion.sales.parts p, trafodion.sales.orders O " |
| + "WHERE d.partnum = p.partnum AND d.ordernum = o.ordernum " |
| + " AND o.order_date >= CAST(? AS DATE) " |
| + "ORDER BY d.ordernum " |
| ; |
| |
| java.sql.PreparedStatement ps3 = conn.prepareStatement( s ) ; |
| ps3.setString( 1, onOrAfter ) ; |
| detail[0] = ps3.executeQuery() ; |
| |
| } |
| } |
