import jora.*;
import java.math.*;
import java.sql.*;

public class Test { 
    public static Session session = new Session();

    public static final String dataSource = "jdbc:odbc:LocalServer";
    public static final String user = "sa";
    public static final String password = "";
    
    public static void main(String[] args) 
    {
        Employee   emp;
	Programmer p;
	Manager    m;
	Cursor     c;
	int        i;

        if (!session.open(dataSource, user, password)) { 
	    System.out.println("Failed to connect to database");
	    return;
	}

	session.execute("create table Manager ("
			+"code INTEGER PRIMARY KEY,"
			+"name VARCHAR(50) NOT NULL,"
			+"age INTEGER,"
			+"hire DATETIME,"
			+"salary NUMERIC(6,2),"
			+"projectId INTEGER,"
			+"experience IMAGE)");

	session.execute("create table Programmer ("
			+"code INTEGER PRIMARY KEY,"
			+"name VARCHAR(50) NOT NULL,"
			+"age INTEGER,"
			+"hire DATETIME,"
			+"salary NUMERIC(6,2),"
			+"language CHAR(8),"
			+"project_projectId INTEGER,"
			+"project_percentOfTime REAL," 
			+"project_role VARCHAR(100),"
			+"skill SMALLINT)");
	p = new Programmer();
	p.code = 100;
	p.name = "Jons";
	p.age = 34;
	p.hire = Date.valueOf("1990-5-1");
	p.salary = new BigDecimal("3000.00");
	p.language = "C++";
	p.project = new Activity(123, 0.5f, "coder");
	p.skill = 5;

	Programmer.table.insert(p);
	
	p = new Programmer();
	p.code = 101;
	p.name = "Smith";
	p.age = 25;
	p.hire = Date.valueOf("1995-1-1");
	p.salary = new BigDecimal("2000.00");
	p.language = "C++";
	p.project = new Activity(123, 1.0f, "tester");
	p.skill = 3;
	
	Programmer.table.insert(p);

	m = new Manager();
	m.code = 201;
	m.name = "Boers";
	m.age = 43;
	m.hire = Date.valueOf("1997-1-1");
	m.salary = new BigDecimal("5000.99");
	m.projectId = 123;
	m.experience = new Experience("DECbank", m.experience);
	m.experience = new Experience("Teleform", m.experience);

	
	Manager.table.insert(m);

	p.language = "Java";
	Programmer.table.update(p);
    
	Object[] ra = Employee.table.selectAll("").toArray(100);
	System.out.println("All employees");
	System.out.println("------------------------");
	for (i = 0; i < ra.length; i++) { 
	    emp = (Employee)ra[i];
	    System.out.println(emp.toString());
	}
	System.out.println("------------------------\n\n");
	System.out.println("Well paid employess");
	System.out.println("------------------------");
	ra = Employee.table.selectAll("where salary > 2000").toArray();
	for (i = 0; i < ra.length; i++) { 
	    emp = (Employee)ra[i];
	    System.out.println(emp.name + "\t" + emp.salary);
	}

	// Discharage all testers
	c = Programmer.table.select("where project_role='tester' for update");
	while ((p = (Programmer)c.next()) != null) { 
	    c.delete();
	}
	
	// ...and let other programmers do thier job
	c = Programmer.table.select("for update");
	while ((p = (Programmer)c.next()) != null) { 
	    if (p.project.percentOfTime < 0.8f) { 
	        p.project.percentOfTime = 0.8f;
		c.update();
	    }
	}
	
	Manager.table.delete(m); // Reduce number of managers
    

	System.out.println("------------------------\n\n");
	System.out.println("Employees with name Jons");
	System.out.println("------------------------");
	emp = new Employee();
	emp.name = "Jons"; 
	c = Employee.table.queryAllByExample(emp);
	while ((emp = (Employee)c.next()) != null) { 
	    System.out.println("#" + emp.code + " " + emp.name);
	}
	System.out.println("------------------------");	
	session.execute("drop table Manager");
	session.execute("drop table Programmer");

	session.close();
    }
}