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

//   Sample Program - Java Stored Procedure (called by V5SpCli) 

//   Steps to set up server side: 
//   (1) create and populate the SAMPLE database (db2sampl) 
//   (2) compile this java file (javac V5Stp.java) 
//   (3) copy the resulting V5Stp.class file into sqllib/function 

//   NOTES: (1) The jdk11_path database manager configuration parameter must 
//              be set 
//          (2) The CLASSPATH and shared library path environment variables 
//              must be set, as for any JDBC application. 
//          (3) Visit http://www.software.ibm.com/data/db2/java 
//              for current DB2 Java information 

//  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 stored procedures, refer to the  
//  "Writing Stored Procedures" 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. 

//   Class V5Stp contains one method: 
//   (1) salaryModification: stored procedure body 

import java.sql.*;              //  JDBC classes 
import java.math.*;             //  BigDecimal 
import COM.ibm.db2.jdbc.app.*;  //  DB2 UDB JDBC classes 
import COM.ibm.db2.app.*;       //  StoredProc and associated classes 

// ///// 
//  Java stored procedure is in this class 
// ///// 
class V5Stp extends StoredProc
{
  //  (1) stored procedure body - modify employee salaries as requested 
  //      return: 
  //       . the total payroll before the increase 
  //       . the average salary before the increase 
  //       . the median employee salary after the increase 
  //       . the total payroll after the increase 
  //       . the average salary after the increase 
  public void salaryModification (String table,
                                  int percentModification,
                                  String department,
                                  double totalPayrollBefore,
                                  double totalPayrollAfter,
                                  double averageSalaryBefore,
                                  double averageSalaryAfter,
                                  int numberOfUpdates,
                                  String medianEmployeeName) throws Exception
  {
    int counter = 0;
    double salary = 0.00;
    double payrollBefore = 0;

    //  get caller's connection to the database; inherited from StoredProc 
    Connection con = getConnection ();
    con.setAutoCommit(false);

    //  calculate the total payroll and average salary before the increase 
    Statement stmt = con.createStatement ();
    String sql = "Select * from " + table;
    ResultSet rs = stmt.executeQuery (sql);

    while (rs.next ())
      {
        salary = rs.getDouble (12);
        payrollBefore += salary;
        counter++;
      }
    rs.close();

    int median = counter / 2;
    double averageBefore = payrollBefore / counter;
    double realPercent = percentModification;
    realPercent = 1.00 + (realPercent / 100.00);

    //  update the salaries for the requested department 
    int employeeUpdates = 0;
    sql = "Update " + table + " set salary = salary * " + realPercent +
      " where workdept = '" + department + "'";
    employeeUpdates = stmt.executeUpdate (sql);

    //  Calculate the total payroll and average salary after the increase 
    //  and retrieve the median salary as well... 
    sql = "Select * from " + table + " order by salary ";
    counter = 0;
    double payrollAfter = 0.00;

    rs = stmt.executeQuery (sql);
    while (rs.next ())
      {
        salary = rs.getDouble (12);
        payrollAfter += salary;
        counter++;
        if (counter == median)
          {
            medianEmployeeName =
              rs.getString (2) + " " +
              rs.getString (3) + " " +
              rs.getString (4);
          }
      }
    rs.close ();

    double averageAfter = payrollAfter / counter;

    //  set values for the output parameters 
    set (4, payrollBefore);
    set (5, payrollAfter);
    set (6, averageBefore);
    set (7, averageAfter);
    set (8, employeeUpdates);
    set (9, medianEmployeeName);

    //  close off everything before we leave 
    stmt.close ();
    con.close ();
  }
}