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

//   Sample Program Stp - SQLJ Stored Procedure (called by StpCli) 
//   Demonstrates a PARAMETER STYLE DB2GENERAL stored procedure 

//   Steps to run the sample: 
//   (1) create and populate the SAMPLE database (db2sampl) 
//   (2) translate this sample: sqlj Stp.sqlj 
//       to get Stp.java and Stp_SJProfile0.ser 
//   (3) compile the generated Java program: javac Stp.java 
//       to get Stp.class, Stp.class,  
//       Stp_Cursor1.class, Stp_Cursor2.class and  
//       Stp_SJProfileKeys.class 
//   (4) customize the generated profile and create the package Stp in the 
//       "sample" database: 
//          db2profc -url=jdbc:db2:sample 
//                   -prepoptions="package using Stp" 
//                   Stp_SJProfile0 
//   (5) copy the resulting Stp.class, 
//                          Stp_Cursor1.class, 
//                          Stp_Cursor2.class, 
//                          Stp_SJProfileKeys.class  
//       and                Stp_SJProfile0.ser    
//       files into the function sub-directory of sqllib 
//   (6) On the client: compile and run the program StpCli 

//   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 Stp 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 
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

#sql iterator Stp_Cursor1 (double salary) ;
#sql iterator Stp_Cursor2 (String, String, String, double) ;

// ///// 
//  Java stored procedure is in this class 
// ///// 
class Stp 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 (int percentModification,
                                  String department,
                                  double totalPayrollBefore,
                                  double totalPayrollAfter,
                                  double averageSalaryBefore,
                                  double averageSalaryAfter,
                                  int numberOfUpdates,
                                  String medianEmployeeName) throws Exception
  {
    Stp_Cursor1 cursor1;
    Stp_Cursor2 cursor2;

    String str1 = null;
    String str2 = null;
    String str3 = null;

    int counter = 0;
    double sal = 0.00;
    double payrollBefore = 0;

    //  get caller's connection to the database; inherited from StoredProc 
    DefaultContext ctx = DefaultContext.getDefaultContext();
    if (ctx == null)
    {
      //  get caller's connection to the database; inherited from StoredProc 
      Connection con = getConnection ();
      ctx = new DefaultContext(con);
      DefaultContext.setDefaultContext(ctx);
      con.setAutoCommit(false);
    }

    //  calculate the total payroll and average salary before the increase 
    #sql cursor1 = { Select salary from employee };

    while (cursor1.next())
    {
      sal = cursor1.salary();

      payrollBefore += sal;
      counter++;
    }
    cursor1.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 
    #sql { Update employee set salary = salary * :realPercent
                  where workdept = :department };

    //  find out the number of employees in the requested department 
    int employeeUpdates = 0;
    #sql { Select COUNT(*) into :employeeUpdates from employee
                  where workdept = :department };

    //  Calculate the total payroll and average salary after the increase 
    //  and retrieve the median salary as well... 
    #sql cursor2 = { Select firstnme, midinit, lastname, salary from employee
                            order by salary };
    counter = 0;
    double payrollAfter = 0.00;

    while (true)
    {
      #sql { Fetch :cursor2 into :str1, :str2, :str3, :sal};
      if (cursor2.endFetch()) break;

      payrollAfter += sal;
      counter++;
      if (counter == median)
      {
        medianEmployeeName = str1 + " " + str2 + " " + str3;
      }
    }
    cursor2.close ();

    double averageAfter = payrollAfter / counter;

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