//-----------------------------------------------------------------------------
// (c) Copyright IBM Corp. 2008 All rights reserved.
//
// The following sample of source code ("Sample") is owned by International
// Business Machines Corporation or one of its subsidiaries ("IBM") and is
// copyrighted and licensed, not sold. You may use, copy, modify, and
// distribute the Sample in any form without payment to IBM, for the purpose of
// assisting you in the development of your applications.
//
// The Sample code is provided to you on an "AS IS" basis, without warranty of
// any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
// IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
// not allow for the exclusion or limitation of implied warranties, so the above
// limitations or exclusions may not apply to you. IBM shall not be liable for
// any damages you suffer as a result of using, copying, modifying or
// distributing the Sample, even if IBM has been advised of the possibility of
// such damages.
//-----------------------------------------------------------------------------
//
// SOURCE FILE NAME: Cgtt.java
//
// SAMPLE: 
//      The sample demonstrates the following:
//       i) Use of Created Temporary table (CGTT) to store intermediate   
//          results. 
//      ii) Use of Created Temporary table with Procedures, Functions, Triggers 
//       and Views. 
//
// PREREQUISITE:
//       1) Sample database is setup on the machine.
//       2) Valid system authorization IDs and password.
//               bob with "bob12345"
//               joe with "joe12345"       
//       3) Execute 'CreateCGTT.db2' script. This script creates all database  
//       objects required for the executing this sample and GRANTS required  
//       privileges to bob an joe.
// 
//
// USAGE SCENARIO:
//                The scenario deals with the employee tax computation process.  
//       At the end of a financial year, the payroll department computes tax 
//       payable by all the employees. The sample demonstrates the use of 
//       created temporary table to store intermediate results during the tax 
//       calculation process. The database contains employee, and payroll tables. 
//       The employee table contains employee details and the payroll table 
//       contains details of employee salary and the total tax payable by the  
//       employee based on his income (salary + bonus) in a finnancial year.
//
//       		Each employee gets tax exemption for salary upto 100,000  
//       the proof for exemption is submitted at the end of the financial 
//       year. At the beginning of financial year the tax payable by an employee 
//       is calculated based on the employees income and the 100,000 exemption 
//       limit. At the end of the year after all the employees of a department 
//       have submitted their tax proofs, the tax calculation process for the 
//       department is trigered. The tax process updates the payroll table with
//       the total tax and the balance tax payable. An Income Tax(IT) statement 
//       is also generated for each employee of the department. 
//
//       	      A TRIGGER is invoked after tax proofs is submitted by all 
//       the employee of a department. The trigger populates the created temporary 
//       table with the details of the employee and his income (salary + any bonus)
//       details and calls a procedure to calculate the tax. All the intermediate
//       results of tax calculation are updated in the CGTT. After the tax calculation
//       is complete another procedure updates the payrool table with the tax data 
//       from the created temporary table. A function generates the IT sheet for  
//       all the employees.
// 
// SAMPLE EXECUTION:
//
//     1) Execute 'CreateCGTT.db2' to create required database objects using 
//        command :
//             db2 -td@ -vf CreateCGTT.db2
//     2) Compile and Execute this sample using commands :       
//             i) javac Cgtt.java
//            ii) java Cgtt.java <Server-name> <Port-number> 
//     3) Execute 'DropCGTT.db2' to drop all the database objects created by the 
//        sample :
//             db2 -td@ -vf DropCGTT.db2
//
// SQL STATEMENTS USED:
//       1) CREATE GLOBAL TEMPORARY TABLE		
//       2) CREATE INDEX
//       3) CREATE VIEW
//       4) CREATE TRIGGER
//       5) CREATE PROCEDURE
//       6) CREATE FUNCTION
//       7) CALL
//       8) RUNSTATS
//       9) TRUNCATE TABLE
//
//-----------------------------------------------------------------------------
// The sample has seven major steps:
//
// ************************************************************************* //
//       Step1 to Step 5 creates all the database objects required for the   //
//       execution of the sample.                                            //
//       Execute 'CreateCGTT.db2' script to create the required database     //
//       objects.                                                            //
//       To execute 'CreateCGTT.db2' issue the command :                     //
//                 db2 -td@ -vf CreateCGTT.db2                               //
// ************************************************************************* //
//       Step1: Create table 'payroll' and populate it.
//       Step2: Create the following objects:
//              - Create a CGTT 'tax_cal'.
//              - View 'ViewOnCgtt' based on 'tax_cal'.
//              - Index 'IndexOnCgtt' based on 'tax_cal'.
//       Step3: Create three procedures and a function 
//                1) tax_compute        : Calculates the tax payable by an 
//                                        employee and returns the value to the 
//                                        CALLER. 
//                2) initial_tax_compute: Calculates the tax payable by an 
//                                        employee initially with a tax   
//                                        exemption of 100,000. This procedure 
//                                        calls the function 'tax_compute' to  
//                                        do the calculation.
//                3) final_tax_compute  : Calculates the tax payable by an 
//                                        employee based on his or her total   
//                                        income (salary + any bonus) and the  
//                                        tax proofs he or she submits. This 
//                                        procedure calls the function 
//                                        'tax_compute' to do the calculation. 
//                4) update             : Updates the created temporary table  
//                                        'tax_cal' with the final results, To  
//                                        update the 'payroll' table to reflect 
//                                        the created temporary table.
//       Step4: Create a function 'printITSheet' to print the IT sheet for the 
//              employees, using the data in the created temporary table 'tax_cal'  
//              through the view 'ViewOnCgtt'.
//       Step5: Create a Trigger 'tax_update' on 'Payroll' table to start the
//              tax calculation process.
//
// ************************************************************************* //
//       Step6 - calculation of tax to be paid by the employees of two       //
//       departments 'D11' and 'D21' is done by this sample 'Cgtt.java'.     //
// ************************************************************************* //
//       Step6: Start the tax computation process for two departments. 
//
// ************************************************************************* //
//       Step7 cleans up all the database objects created by 'CreateCGTT.db2'//
//       Execute 'DropCGTT.db2' script to drop all the database objects      //
//                                                                           //  
//       To execute 'DropCGTT.db2' issue the command :                       //
//                 db2 -td@ -vf DropCGTT.db2                                 //
// ************************************************************************* //
//       Step7: Run the Clean up scripts.
//-----------------------------------------------------------------------------

import java.sql.*;

class Cgtt
{
   static Db db; 
   public static void main(String argv[])
   {
      try
      {
         Connection con1 = null, con2 = null, con3 = null;
         String user1 = "joe";
         String password1 = "joe12345";
         String server = argv[0];
         String port=argv[1];
         
         
         // connect to sample database
         con1 = ConHandler(user1,password1,server,port); 
         con1.setAutoCommit(false); 
         CalTax(con1,"D11",user1);
         
         String user2 = "bob";
         String password2 = "bob12345";
         con2 = ConHandler(user2,password2,server,port); 
         con2.setAutoCommit(false);
         CalTax(con2,"D21",user2);
         db.disconnect();
      }
      catch (Exception e)
      {
         System.out.println("Error Msg: "+ e.getMessage());
      }
   }
  


   public static Connection ConHandler(String user, String password, String serverAdd, String port)
   {
      Connection con = null;	
      String args[]=new String[4];
      args[1]=port;
      args[0]=serverAdd;
      args[2]=user;
      args[3]=password;
      try
      {
         db=new Db(args);
      }
      catch (Exception e)
      {
         System.out.println("  Error loading DB2 Driver...\n");
         System.out.println(e);
         System.exit(1);
      }
         
      try
      {
         con = db.connect();
         con.setAutoCommit(false);
      }
      catch (Exception e)
      {
         System.out.println("Error while Connecting to sample database.");
         System.err.println(e) ;
         System.exit(1);
      }
      return con; 
   }


   static void CalTax(Connection con, String deptnum, String user)
   {
      try
      {
         System.out.print("\n-----------------------------------------------------"+
         "----------------------------------------------------------------------\n");
         System.out.print("Employee of the company '"+user+"' calculates "+ 
                          "the tax for department '"+deptnum+"'...\n");
         System.out.print("'"+user+"' updates the 'payroll' table as per "+
                          "the tax proof submitted by the employees of "+
                          "department '"+deptnum+"'...\n");
         System.out.print("As per the scenario, all employees of '"+deptnum+"' submit "+
                          "tax proofs for '50000'...\n");
         System.out.print("\n------------------------------------------------------"+
         "----------------------------------------------------------------------\n\n");

         String st1 = "UPDATE cgtt.payroll SET tax_proof = 50000 "+
                      "WHERE deptno = '"+deptnum+"'";


         System.out.print("\n'"+user+"' triggers the process of tax "+
                          "calculation by updating the 'calculate_tax' to one "+
                          "for all employees of \n department '"+deptnum+"' after all the "+
                          "employees of the department submit the tax proof\n");
         String st2 = "UPDATE cgtt.payroll SET calculate_tax = 1 "+
                      "WHERE deptno = '"+deptnum+"'";


         System.out.print("Once tax calculation is complete, '"+user+"' "+
                          "set the 'calculate_tax' column back to zero\n"); 
         String stn = "UPDATE cgtt.payroll SET calculate_tax = 0 ";
         

         System.out.println("\nUPDATE payroll SET tax_proof = 50000 "+ 
                               "WHERE deptno = '"+deptnum+"'\n ");

         Statement stmt1 = con.createStatement();
         stmt1.executeUpdate(st1);


         System.out.println("\nUPDATE payroll SET calculate_tax = 1 "+ 
                               "WHERE deptno = '"+deptnum+"'\n \n");

         Statement stmt2 = con.createStatement();
         stmt2.executeUpdate(st2);

         
         Statement stmt3 = con.createStatement();
         stmt3.executeUpdate(stn);

         Process(con);
         PrintITReport(con);         
         con.commit();
      }
      catch(Exception e)
      {
         String Message = e.getMessage();
         System.out.println(Message);
      }
   }


 static void Process(Connection con)
 {
    try 
    {
       java.sql.CallableStatement cstmt;
       String Proc = "CALL cgtt.update()";
       cstmt = con.prepareCall(Proc);
       cstmt.execute(); 
    }
    catch(Exception e)
    {
       String Message = e.getMessage();
       System.out.println(Message);
    }
 }

 static void PrintITReport(Connection con)
 {
    try
    {
       Statement stmt = con.createStatement();
       ResultSet rs = stmt.executeQuery("SELECT * FROM "+
                           "TABLE(cgtt.printITSheet()) as ITSheet");

       ResultSetMetaData rsm = rs.getMetaData();				
       int columnCount = rsm.getColumnCount();
       int i=1;	
       while(i <= columnCount )
       {
          System.out.print(rsm.getColumnName(i)+"\t     ");
          i++;
       }

       System.out.print("\n-----------------------------------------------------"+
       "----------------------------------------------------------------------\n");
       while(rs.next()){
          
          int j=1;
          while(j <= columnCount )
          {
             System.out.print(rs.getString(rsm.getColumnName(j))+"\t     ");
             j++;
          }
             System.out.print("\n");
        }	
        rs.close();
        stmt.close();
       System.out.print("\n-----------------------------------------------------"+
       "----------------------------------------------------------------------\n");
    }
   catch(Exception e)
    {
       String Message = e.getMessage();
       System.out.println(Message);
    }
 }

}