//
//   Source File Name: OrdRep.java  1.3 
//  
//   Licensed Materials -- Property of IBM 
//  
//   (c) Copyright International Business Machines Corporation, 1999. 
//       All Rights Reserved. 
//  
//   US Government Users Restricted Rights - 
//   Use, duplication or disclosure restricted by 
//   GSA ADP Schedule Contract with IBM Corp. 

//   PURPOSE : 
//     - Generates order report using multiple result sets 

//  PREREQUISITE: 
//   The CLI sample ORDER.C must be made and stored on the server  
//   to allow this program to call the user defined function 'price'. 
//   If you do not make ORDER.C, this program will not work. 

//  For more information about this sample, refer to the README file. 

//  For more information on Programming in Java, refer to the  
//  "Programming in Java" section of the Application Development Guide. 

//  For more information on creating and using user-defined functions,  
//  refer to the "Object-Relational Programming" section of the  
//  Application Development Guide. 

//  For more information on building and running Java programs for DB2, 
//  refer to the "Building Java Applets and Applications" section of the  
//  Application Building Guide. 

//  For more information on the SQL language, refer to the SQL Reference. 

import java.io.*;
import java.lang.*;
import java.sql.*;

class OrdRep {

   static {
      try {
         //  register the driver with DriverManager 
         //  The newInstance() call is needed for the sample to work with 
         //  JDK 1.1.1 on OS/2, where the Class.forName() method does not 
         //  run the static initializer. For other JDKs, the newInstance 
         //  call can be omitted. 
         Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }

   public static void main (String[] argv) {
      Connection con = null;
      int ROW_ARRAY_SIZE = 25;
      try {
         //  connect to the server of user's choice 
         //  see Tools.class on Tools.DBConnect method 
         if (argv.length == 3)
         {  //  connect using command line arguments 
            con = Tools.DBConnect(argv[0], argv[1], argv[2]); 
         }
         else
         {  //  prompt user for database name, user ID, password 
            con = Tools.DBConnect(); 
         }
         con.setAutoCommit(false);

         //  create array of the customers for whom we will generate orders  
         int[] cust = {
             10,  20,  30,  40,  50,  60,  70,  80,  90, 100, 110, 120, 130,
            140, 150, 160, 170, 180, 190, 200, 210, 220, 230, 240, 250 };
         generateCustomerList (con, cust, ROW_ARRAY_SIZE);

         //  disconnect from the connection made 
         System.out.println ("\n>Disconnecting...");
         con.commit();
         con.close();
      } catch (Exception e) { e.printStackTrace(); }
   }

   //  
   //  generateCustomerList 
   //     - creates the SQL statement and then executes the query 
   //     - prints the data from the result sets listing the information from 
   //       the recursive query 
   //  

   public static void generateCustomerList (Connection con, int[] cust, int numEntry) {
      try {
         //  prepare statement to use in for loop 
         //  Common Table expression (or Define Inline View) 
         //  NOTE: you must make the CLI sample order.c to call the "price"  
         //        function  
         String statement = "WITH " +
                            "order (ord_num, cust_num, prod_num, quantity, amount) AS " +
                              "(SELECT c.ord_num, " +
                                      "c.cust_num, " +
                                      "l.prod_num, " +
                                      "l.quantity, " +
                                      "price(char(p.price, '.'), p.units, char(l.quantity, '.')) " +
                              "FROM ord_cust c, ord_line l, product p " +
                              "WHERE c.ord_num = l.ord_num " +
                                "AND l.prod_num = p.prod_num " +
                               "AND cast (cust_num as integer) = ? " +
                              "), " +
                           "totals (ord_num, total) AS " +
                           "(SELECT ord_num, sum(decimal(amount, 10, 2)) " +
                              "FROM order GROUP BY ord_num " +
                           ") " +
                           "SELECT order.ord_num, " +
                                  "cust_num, " +
                                  "prod_num, " +
                                  "quantity, " +
                                  "DECIMAL(amount,10,2) amount, " +
                                  "total " +
                           "FROM order, totals " +
                           "WHERE order.ord_num = totals.ord_num";

         PreparedStatement pstmt = con.prepareStatement (statement);
         String ordNum = new String("");
         String custNum = new String("");
         String prodNum = new String("");
         double quantity = 0;
         String amount = new String("");
         String total = new String("");
         System.out.println ("\nORD_NUM CUST_NUM PROD_NUM   QUANTITY       AMOUNT        TOTAL");
         System.out.println ("--------------------------------------------------------------");

         //  execute prepared for the number of different customers in the array 
         for (int cnt = 0; cnt < 24; cnt = cnt + 1) {
            pstmt.setInt (1, cust[cnt]);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
               ordNum = rs.getString (1);
               custNum = rs.getString (2);
               prodNum = rs.getString (3);
               quantity = rs.getDouble (4);
               amount = rs.getString (5);
               total = rs.getString (6);

               //  formatted output information from the result set 
               System.out.println (Tools.padLength(ordNum, 8) + Tools.padLength(custNum, 9) +
                  Tools.padLength(prodNum, 11) + Tools.padLengthRight(String.valueOf (quantity), 8) +
                  Tools.padLengthRight(amount, 13) + Tools.padLengthRight(total, 13));
            }
            rs.close();
         }
         pstmt.close();
      } catch (Exception e) { e.printStackTrace(); }
   }
}