Task 2

import java.util.Scanner;
import java.sql.*;
import java.text.SimpleDateFormat;  
import java.util.Date;  
public class UpdateSalaryLog {
static Scanner scan;
	public static void main(String[] args) {
		scan=new Scanner(System.in);
        while (true) {
            System.out.println("----------------------------------------------");
            System.out.println("		1. Appraisal");
            System.out.println("		2. Show Log");
            System.out.println("		0. Exit");
            System.out.println("----------------------------------------------");
            System.out.println("Enter choice:");
            int ch = Integer.parseInt(scan.nextLine());
            switch (ch) {
            case 1:
            	inputApp();
                break;
            case 2:
                showLog();
                break;
            case 0:
                System.exit(0);

            }
        }

	}
	public static void inputApp() {
		System.out.println("Enter the EMP ID for appraisal: ");
		int empno=Integer.parseInt(scan.nextLine());
		try {
			//connection
			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
			//get old salary
			PreparedStatement getoldsal=con.prepareStatement("SELECT SAL FROM EMP WHERE EMPNO=?");
			getoldsal.setInt(1, empno);
			ResultSet oldsalmov=getoldsal.executeQuery();
			oldsalmov.next();
			int oldsal = oldsalmov.getInt(1);
			System.out.println("Old Salary is "+oldsal);
			//input percentage and calculate increment
			System.out.println("Enter the percentage of appraisal:");
			float percent=Float.parseFloat(scan.nextLine());
			float newsal=((percent/100)*oldsal)+oldsal;
			System.out.println("The new salary is "+newsal+"");
			//Update in log statement initialization and execution
			PreparedStatement updatelog=con.prepareStatement("INSERT INTO LOGEMP(empno, oldsal, newsal, date_of_appraisal) VALUES (?,?,?,?)");
			SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yy");
			Date date = new Date();
			String formatteddate = formatter.format(date);
			updatelog.setInt(1, empno);
			updatelog.setInt(2, oldsal);
			updatelog.setFloat(3, newsal);
			updatelog.setString(4,formatteddate);
			int status=updatelog.executeUpdate();
			if(status>0) {
				System.out.println("****Appraisal has been logged****");
			} else {
				System.out.println("****Failed Logging****");
			}
			//Update Salary in main emp table
			PreparedStatement updatemain=con.prepareStatement("UPDATE emp SET sal=? WHERE empno=?");
			updatemain.setFloat(1, newsal);
			updatemain.setInt(2, empno);
			int mainstatus=updatemain.executeUpdate();
			if(mainstatus>0) {
				System.out.println("|	EMPNO	|	OLDSAL	|	NEWSAL	|	DATE_OF_APP	|");
				System.out.println("|	"+empno+"	|	"+oldsal+"	|	"+newsal+"	|	"+formatteddate+"	|");
			} else {
				System.out.println("Main Not Updated");
			}
			//close connection
			con.close();
		}
		
		catch(SQLException e){
			if(e.getErrorCode()==17011) {
				System.out.println("EMP ID not available");
			} 
			else {
				System.out.println(e.getErrorCode()+"*"+e.getMessage());
			}
		}
		catch(Exception e){
			System.out.println(e.getStackTrace());
		}
	}
	public static void showLog() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");

            Statement existing = con.createStatement();
            ResultSet exist = existing.executeQuery("SELECT count(*) FROM LOGEMP");
            exist.next();
            int exists = exist.getInt(1);

            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM LOGEMP");

            if (exists > 0) {
            	System.out.println("|	EMPNO	|	OLDSAL	|	NEWSAL	|	DATE_OF_APP	|");
                while (rs.next())
                    System.out.println("|	" + rs.getInt(1) + "	|	" + rs.getInt(2) + "	|	" + rs.getInt(3) + "	|	" + String.format("%.10s",rs.getString(4)) + "	|");
            } else {
                System.out.println("No Records in EMP Table");
            }
        } catch (SQLException e) {
            System.out.println(e.getErrorCode() + "*" + e.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
	}

}