// 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() ;

}