import java.awt.*;
import java.sql.*;
import java.io.*;

public class Employee 
{
	
	Connection conn;
	
	public Employee()
	{
		if(loadDriver())
		{
			connectDB();
		}
	}
	public boolean loadDriver()
	{
		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			return true;
		}
		catch(ClassNotFoundException ex)
		{
			System.out.println("failed to load Driver");
		}
	return false;
	}
	public void connectDB()
	{
		try
		{
			String filename = "C:/SchoolDB.mdb";//Specity where your own database is located
          
            String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
            database += filename.trim() + ";DriverID=22;READONLY=true}"; 
            conn = DriverManager.getConnection(database, "", "");
         
            System.out.println ("DB is successfully connected");
         
		}
		catch(Exception ex)
		{
			System.out.println ("Failed to connect DB. Make sure the"+
				" file exists in a specified location.");
		}
	}
	public void displayAllEmployees()
	{
		try
		{
			String query = "SQL statement here";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			System.out.printf("%-10s%-8s%-15s%-10s%-10s%-10s","Employee#","Title","Type","Name","Surname","PhoneNo");
			System.out.println();
			System.out.println("===============================================================");
			while(rs.next())
			{
				String empNumber = rs.getString("empNumber");
				String empTitle = rs.getString("empTitle");
				String empType = rs.getString("empType");
				String empName = rs.getString("empName");
				String empSurname = rs.getString("empSurname");
				String empPhoneNo = rs.getString("empPhoneNo");
					
				System.out.printf("%-10s%-8s%-15s%-10s%-10s%-10s",empNumber,empTitle,empType,empName,empSurname,empPhoneNo);
				System.out.println();
			}
			stmt.close(); 
		}
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
		
	}
	//---------------------------------------------------------------------------1.1
	public void displayWorkshops()
	{
		try
		{
			String query = "SQL statement here";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			System.out.printf("%-10s%-10s%-15s","Employee#","AmountReq","Purpose");
			System.out.println();
			System.out.println("============================================");
			while(rs.next())
			{
				String empNumber = rs.getString("EmpNumber");
				String amountReq = rs.getString("NewAmount");
				String purpose = rs.getString("purpose");
				System.out.printf("%-10s%-10s%-15s",empNumber,"R"+amountReq,purpose);
				System.out.println();
			}
			stmt.close(); 
		}
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
	}
	//---------------------------------------------------------------------------1.2
	public void displayAllRequests()
	{
		try
		{
			String query = "SQL statement here";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			System.out.println("Type of meetings Attended\n=========================");
			while(rs.next())
			{
				String purpose = rs.getString("Purpose");
				System.out.println(purpose);
			}
			stmt.close(); 
		}
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
	}
	//---------------------------------------------------------------------------1.3	
	public void requestBeforeApril()
	{
		try
		{
			String query = "SQL statement here";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			System.out.printf("%-10s%-15s%-15s%-15s","Employee#","AmountReq","Purpose","Date Requested");
			System.out.println();
			System.out.println("======================================================");
			while(rs.next())
			{
				String empNumber = rs.getString("EmpNumber");
				String amountReq = rs.getString("Amount");
				String purpose = rs.getString("Purpose");
				String dateReq = rs.getString("RequestedDate");
					
				System.out.printf("%-10s%-10s%-20s%-20s",empNumber,"R"+amountReq,purpose,dateReq);
				System.out.println();
			}
			stmt.close(); 
		}
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
	}
	//---------------------------------------------------------------------------1.4
	public void amountRequested()
	{
		try
		{
			String query = "SQL statement here";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			System.out.printf("%-10s%-10s%-15s%-10s%-10s","Employee#","Surname","Type","Amount","Purpose");
			System.out.println();
			System.out.println("=============================================================");
			while(rs.next())
			{
				String empName = rs.getString(1);
				String empSurname = rs.getString(2);
				String empType = rs.getString(3);
				String amount = rs.getString(4);
				String purpose = rs.getString(5);
					
				System.out.printf("%-10s%-10s%-15s%-10s%-10s",empName,empSurname,empType,"R"+amount,purpose);
				System.out.println();
			}
			stmt.close(); 
		}
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
	}
	//---------------------------------------------------------------------------1.5
	public void totalAmountRequested()
	{
		try
		{
			String query = "SQL statement here";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				String totAmount = rs.getString("totAmount");
				System.out.println("The total amount requested is: R"+ totAmount);
			}
			stmt.close(); 
		}
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
	}
	//---------------------------------------------------------------------------1.6
	public void deleteEmployeeRecord() throws IOException
	{
		//Input code here
		try
		{
			String query = "SQL statement here";
			Statement stmt = conn.createStatement();
			int result = stmt.executeUpdate(query);
			
			if(result == 1)
			{
				System.out.println("Record deleted!!!");
			}
			else
			{
				System.out.println("Invalid Employee number");
			}
			stmt.close(); 
		}
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
	}
	//---------------------------------------------------------------------------1.7	
}
