Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package database;
- import java.sql.*;
- import java.text.DecimalFormat;
- import java.text.ParseException;
- import java.util.ArrayList;
- import java.util.Calendar;
- import connection.JDBCConnection;
- import data.Admin;
- import data.ContrFin;
- import data.ContrInf;
- import data.Contract;
- import data.Course;
- import data.DBCalendar;
- import data.Degree;
- import data.Globals;
- import data.Member;
- import data.Payment;
- import data.Project;
- import data.Stuff;
- import data.Teacher;
- public class Procedure {
- // globals
- /* float Basic_Teacher_Wage = 1200f;
- float Basic_Admin_Wage = 1000f;
- float Teacher_Rise_Percentage = 0.15f;
- float Admin_Rise_Percentage = 0.15f;
- float Member_Percentage = 0.05f;
- float Reasearch_Bonus = 500f;
- float Library_Bonus = 300f;
- float Basic_Temp_Teacher_Wage = 1200f;
- float Basic_Temp_Admin_Wage = 1000f;
- int pay_count = 0;
- int contract_count = 0;
- int stuff_count = 0;*/
- Globals GValues = new Globals();
- DBCalendar DBDate = new DBCalendar(); // Current DB date
- JDBCConnection connection = new JDBCConnection();
- public void setConnection(JDBCConnection conn) {
- this.connection = conn;
- }
- public JDBCConnection getConnection() {
- return this.connection;
- }
- public DBCalendar getDBDate() {
- return DBDate;
- }
- public void setDBDate(DBCalendar dBDate) {
- DBDate = dBDate;
- }
- // true if date after current date
- public boolean isDateValid(String date) throws ParseException {
- DBCalendar tempdate = new DBCalendar();
- tempdate.setDBtime(date);
- if(tempdate.getDBtime().before(DBDate.getDBtime())) {
- return false;
- }
- return true;
- }
- /* If date day != 1 get the next valid date */
- public String getStartOfMonth(String date) throws ParseException {
- DBCalendar start = new DBCalendar();
- start.setDBtime(date);
- if(start.getDBtime().get(Calendar.DATE) > 1) {
- start.getDBtime().set(start.getDBtime().get(Calendar.YEAR), (start.getDBtime().get(Calendar.MONTH)+1), 1);
- }
- return start.getDBtimeString();
- }
- /* If date day != 31/30 get the next valid date */
- public String getEndOfMonth(String date) throws ParseException {
- DBCalendar start = new DBCalendar();
- start.setDBtime(date);
- DBCalendar tempdate = new DBCalendar();
- tempdate.getDBtime().set(start.getDBtime().get(Calendar.YEAR), (start.getDBtime().get(Calendar.MONTH)+1), 1);
- tempdate.getDBtime().add(Calendar.DAY_OF_MONTH, -1);
- if(start.getDBtime() != tempdate.getDBtime()) {
- start.setDBtime(tempdate.getDBtimeString());
- }
- return start.getDBtimeString();
- }
- void createTables() throws SQLException {
- /* Create Globals Store Stack */
- String createStore = new String(
- "CREATE TABLE Globals (" +
- "id int NOT NULL,"+
- "Basic_Teacher_Wage float(2),"+
- "Basic_Admin_Wage float(2),"+
- "Teacher_Rise_Percentage float(2),"+
- "Admin_Rise_Percentage float(2),"+
- "Member_Percentage float(2),"+
- "Reasearch_Bonus float(2),"+
- "Library_Bonus float(2),"+
- "Basic_Temp_Teacher_Wage float(2),"+
- "Basic_Temp_Admin_Wage float(2)"+
- ");");
- Statement stmt = connection.con.createStatement();
- stmt.executeUpdate(createStore);
- /* Creates Teaching_Stuff table */
- String createTeachers = new String(
- "CREATE TABLE Teaching_Stuff ("+
- "id_Stuff int NOT NULL,"+
- "FirstName varchar(100) NOT NULL,"+
- "LastName varchar(100) NOT NULL,"+
- "FatherName varchar(100) NOT NULL,"+
- "Gender char(1) NOT NULL,"+
- "DateOfBirth DATE NOT NULL,"+
- "AMKA varchar(11) NOT NULL,"+
- "AFM varchar(9) NOT NULL,"+
- "Department varchar(255) NOT NULL,"+
- "Marital_Status char(1) NOT NULL,"+
- "Address varchar(255) NOT NULL,"+
- "Bank varchar(100) NOT NULL,"+
- "IBAN varchar(33) NOT NULL,"+
- "Active char(1) NOT NULL,"+
- "Prof_Title varchar(100),"+
- "Telephone varchar(16)"+
- ");");
- stmt = connection.con.createStatement();
- stmt.executeUpdate(createTeachers);
- /* Creates Admin_Stuff table */
- String createAdmin = new String(
- "CREATE TABLE Admin_Stuff ("+
- "id_Stuff int NOT NULL,"+
- "FirstName varchar(100) NOT NULL,"+
- "LastName varchar(100) NOT NULL,"+
- "FatherName varchar(100) NOT NULL,"+
- "Gender char(1) NOT NULL,"+
- "DateOfBirth DATE NOT NULL,"+
- "AMKA varchar(11) NOT NULL,"+
- "AFM varchar(9) NOT NULL,"+
- "Department varchar(255) NOT NULL,"+
- "Marital_Status char(1) NOT NULL,"+
- "Address varchar(255) NOT NULL,"+
- "Bank varchar(100) NOT NULL,"+
- "IBAN varchar(33) NOT NULL,"+
- "Active char(1) NOT NULL,"+
- "Admin_Title varchar(100)," +
- "Duty varchar(255)," +
- "Telephone varchar(16)"+
- ");");
- stmt = connection.con.createStatement();
- stmt.executeUpdate(createAdmin);
- /* Creates Contract_Inf table */
- String createInf = new String(
- "CREATE TABLE Contract_Inf (" +
- "id_Contract int NOT NULL," +
- "Date_Begin DATE NOT NULL," +
- "Hours_Week int NOT NULL," +
- "Days_Leave int NOT NULL," +
- "In_Effect char(1) NOT NULL," +
- "Termination DATE,"+
- "id_Stuff int NOT NULL" +
- ");");
- stmt = connection.con.createStatement();
- stmt.executeUpdate(createInf);
- /* Creates Contract_Fin table */
- String createFin = new String(
- "CREATE TABLE Contract_Fin (" +
- "id_Contract int NOT NULL," +
- "Date_Begin DATE NOT NULL," +
- "Hours_Week int NOT NULL," +
- "Days_Leave int NOT NULL," +
- "In_Effect char(1) NOT NULL," +
- "Date_End DATE NOT NULL,"+
- "Termination DATE,"+
- "id_Stuff int NOT NULL" +
- ");");
- stmt = connection.con.createStatement();
- stmt.executeUpdate(createFin);
- /* Creates Family_Member table */
- String createMember = new String(
- "CREATE TABLE Family_Member (" +
- "id_Member int NOT NULL," +
- "FirstName varchar(100) NOT NULL," +
- "LastName varchar(100) NOT NULL," +
- "Relation char(1) NOT NULL," +
- "Gender char(1) NOT NULL," +
- "DateOfBirth DATE NOT NULL," +
- "AMKA varchar(11) NOT NULL," +
- "id_Stuff int NOT NULL" +
- ");");
- stmt = connection.con.createStatement();
- stmt.executeUpdate(createMember);
- /* Creates Edu_Degree table */
- String createDegree = new String(
- "CREATE TABLE Edu_Degree (" +
- "id_Degree int NOT NULL," +
- "Title varchar(255) NOT NULL," +
- "Year int NOT NULL," +
- "Mark float(2) NOT NULL," +
- "University varchar(255) NOT NULL," +
- "id_Stuff int NOT NULL" +
- ");");
- stmt = connection.con.createStatement();
- stmt.executeUpdate(createDegree);
- /* Creates Payment table */
- String createPayment = new String(
- "CREATE TABLE Payment (" +
- "id_Payment int NOT NULL," +
- "id_Stuff int NOT NULL," +
- "TypeOfPay varchar(100) NOT NULL," +
- "DateOfPay DATE NOT NULL," +
- "ValueOfPay float(2) NOT NULL," +
- "Basic_Wage float(2) NOT NULL," +
- "Family_bonus float(2) NOT NULL," +
- "Benefits float(2) NOT NULL," +
- "Comment varchar(255)" +
- ");");
- stmt = connection.con.createStatement();
- stmt.executeUpdate(createPayment);
- /* Creates Course table */
- String createCourse = new String(
- "CREATE TABLE Course (" +
- "id_Course int NOT NULL," +
- "Title varchar(255) NOT NULL," +
- "id_Stuff int NOT NULL" +
- ");");
- stmt = connection.con.createStatement();
- stmt.executeUpdate(createCourse);
- /* Creates Participates_In table */
- String createParticipates_In = new String(
- "CREATE TABLE Participates_In (" +
- "id_Project int NOT NULL," +
- "Date_In DATE NOT NULL," +
- "Date_Out DATE," + // corrected
- "id_Stuff int NOT NULL" +
- ");");
- stmt = connection.con.createStatement();
- stmt.executeUpdate(createParticipates_In);
- /* Creates Project table */
- String createProject = new String(
- "CREATE TABLE Project (" +
- "id_Project int NOT NULL," +
- "Name varchar(255) NOT NULL" +
- ");");
- stmt = connection.con.createStatement();
- stmt.executeUpdate(createProject);
- }
- /*returns Teacher object*/
- public Teacher getTeacher(int id) throws SQLException {
- Teacher t = new Teacher();
- /* Select active Teachers */
- String selectTeacher = new String(
- "SELECT * FROM Teaching_Stuff WHERE Id_Stuff='"+id+"'");
- Statement stmt1 = connection.con.createStatement();
- ResultSet rs1 = stmt1.executeQuery(selectTeacher);
- while(rs1.next()) {
- t.setId(rs1.getInt("Id_Stuff"));
- t.setFirstName(rs1.getString("FirstName"));
- t.setLastName(rs1.getString("LastName"));
- t.setFatherName(rs1.getString("FatherName"));
- t.setGender(rs1.getString("Gender").charAt(0));
- t.setDateOfBirth(rs1.getString("DateOfBirth"));
- t.setAMKA(rs1.getString("AMKA"));
- t.setAFM(rs1.getString("AFM"));
- t.setDepartment(rs1.getString("Department"));
- t.setMarital_Status(rs1.getString("Marital_Status").charAt(0));
- t.setAddress(rs1.getString("Address"));
- t.setBank(rs1.getString("Bank"));
- t.setIBAN(rs1.getString("IBAN"));
- t.setActive(rs1.getString("Active").charAt(0));
- t.setProf_Title(rs1.getString("Prof_Title"));
- t.setTelephone(rs1.getString("Telephone"));
- }
- return t;
- }
- /*returns Admin object*/
- public Admin getAdmin(int id) throws SQLException {
- Admin a = new Admin();
- /* Select active Teachers */
- String selectAdmin = new String(
- "SELECT * FROM Admin_Stuff WHERE Id_Stuff='"+id+"'");
- Statement stmt1 = connection.con.createStatement();
- ResultSet rs1 = stmt1.executeQuery(selectAdmin);
- while(rs1.next()) {
- a.setId(rs1.getInt("Id_Stuff"));
- a.setFirstName(rs1.getString("FirstName"));
- a.setLastName(rs1.getString("LastName"));
- a.setFatherName(rs1.getString("FatherName"));
- a.setGender(rs1.getString("Gender").charAt(0));
- a.setDateOfBirth(rs1.getString("DateOfBirth"));
- a.setAMKA(rs1.getString("AMKA"));
- a.setAFM(rs1.getString("AFM"));
- a.setDepartment(rs1.getString("Department"));
- a.setMarital_Status(rs1.getString("Marital_Status").charAt(0));
- a.setAddress(rs1.getString("Address"));
- a.setBank(rs1.getString("Bank"));
- a.setIBAN(rs1.getString("IBAN"));
- a.setActive(rs1.getString("Active").charAt(0));
- a.setAdmin_Title(rs1.getString("Admin_Title"));
- a.setDuty(rs1.getString("Duty"));
- a.setTelephone(rs1.getString("Telephone"));
- }
- return a;
- }
- /* Gets the most recent object_id*/
- public int getLatestId(String table, String idName) throws SQLException {
- int latest=0;
- String selectTable = new String(
- "SELECT max("+idName+") FROM "+table);
- Statement stmt1 = connection.con.createStatement();
- ResultSet rs1 = stmt1.executeQuery(selectTable);
- while(rs1.next()) {
- latest = rs1.getInt(1);
- }
- return latest;
- }
- public void updateStuff () {
- }
- /* Marks Contract to be terminated by updateContract() */
- // Stuff id goes here v as argument
- public void terminateContract(int id) throws SQLException {
- /* Get to the end of the month */
- DBCalendar adate = new DBCalendar();
- adate.getDBtime().set(DBDate.getDBtime().get(Calendar.YEAR), (DBDate.getDBtime().get(Calendar.MONTH)+1), 1);
- adate.getDBtime().add(Calendar.DAY_OF_MONTH, -1);
- String date = adate.getDBtimeString();
- /* Search for ID in permanent contracts */
- String findPermCon = new String( // contract needs to be inEffect
- "UPDATE Contract_Inf SET Termination='"+date+"' WHERE id_Stuff='"+id+"' AND In_Effect='1'");
- Statement stmt1 = connection.con.createStatement();
- stmt1.executeUpdate(findPermCon);
- /* Search for ID in temporary contracts */
- String findTempCon = new String( // contract needs to be inEffect
- "UPDATE Contract_Fin SET Termination='"+date+"' WHERE id_Stuff='"+id+"' AND In_Effect='1'");
- Statement stmt2 = connection.con.createStatement();
- stmt2.executeUpdate(findTempCon);
- }
- /* activates / deactivates contracts */
- public void updateContracts(String date) throws ParseException, SQLException {
- DBCalendar cal = new DBCalendar();
- cal.setDBtime(date);
- /* DEACTIVATION */
- /* Search in temporary contracts to deactivate */
- String deTempCon = new String(
- "UPDATE Contract_Fin SET Termination='"+date+"', In_Effect='0' "+
- "WHERE Date_End='"+date+"' OR Termination='"+date+"'");
- Statement stmt1 = connection.con.createStatement();
- stmt1.executeUpdate(deTempCon);
- /* Search in permanent contracts to deactivate */
- String dePermaCon = new String(
- "UPDATE Contract_Inf SET In_Effect='0' WHERE Termination='"+date+"'");
- stmt1.executeUpdate(dePermaCon);
- /* ACTIVATION */
- /* Search in temporary contracts to activate */
- String aTempCon = new String(
- "UPDATE Contract_Fin SET In_Effect='1' WHERE Date_Begin='"+date+"' AND In_Effect='0'");
- stmt1.executeUpdate(aTempCon);
- /* Search in permanent contracts to activate */
- String aPermaCon = new String(
- "UPDATE Contract_Inf SET In_Effect='1' WHERE Date_Begin='"+date+"' AND In_Effect='0'");
- stmt1.executeUpdate(aPermaCon);
- }
- /* PayStuff */
- public void payEmployees(String date) throws SQLException, ParseException {
- //=============================================================================================================
- /* Select active Teachers */
- String selectTeacher = new String(
- "SELECT * FROM Teaching_Stuff WHERE Active='1'");
- Statement stmt1 = connection.con.createStatement();
- ResultSet rs1 = stmt1.executeQuery(selectTeacher);
- while (rs1.next()) {
- int teach_id = rs1.getInt("id_Stuff");
- /* Search for ID in permanent contracts */
- String findPermCon = new String( // contract needs to be inEffect
- "SELECT * FROM Contract_Inf WHERE Id_Stuff='"+Integer.toString(teach_id)+"' AND In_Effect='1'");
- Statement stmt2 = connection.con.createStatement();
- ResultSet rs2 = stmt2.executeQuery(findPermCon);
- // if true teach is permanent stuff
- if(rs2.next()) {
- int member_count=0;
- float YearsOfService=0;
- DBCalendar Today = new DBCalendar();
- Today.setDBtime(date);
- // Count family members ---------------------------------------------------------
- String findMember = new String(
- "SELECT * FROM Family_Member WHERE Id_Stuff='"+teach_id+"'");
- Statement stmt3 = connection.con.createStatement();
- ResultSet rs3 = stmt3.executeQuery(findMember);
- while(rs3.next()) {
- // check if member eligible for benefits
- String rltn = rs3.getString("Relation");
- if(rltn.charAt(0)=='s') {
- member_count++;
- } else {
- String dob = rs3.getString("DateOfBirth");
- // check if member eligible for benefits
- if(Today.getRemainder(dob) < 18) {
- member_count++;
- }
- }
- }
- // Count Work experience--------------------------------------------------------
- String Date_Begin = rs2.getString("Date_Begin");
- YearsOfService = Today.getRemainder(Date_Begin);
- // Calculate current salary w8 benefits
- float expBoostWage = GValues.getBasic_Teacher_Wage();
- while(YearsOfService > 1 ) { // for first year no rise, sorry
- expBoostWage = expBoostWage*GValues.getTeacher_Rise_Percentage();
- }
- // Calculate final Salary--------------------------------------------------------
- float salary = expBoostWage + ((GValues.getBasic_Teacher_Wage()*GValues.getMember_Percentage())*member_count)
- + GValues.getReasearch_Bonus();
- Payment check = new Payment((getLatestId("Payment","id_Payment")+1),teach_id,date,"Μηνιάτικο",salary,
- GValues.getBasic_Teacher_Wage(),((GValues.getBasic_Teacher_Wage()*GValues.getMember_Percentage())*member_count),
- GValues.getReasearch_Bonus(),"Επίδομα Έρευνας");
- //finally insert check to DB ...
- check.insertToTable();
- }
- //---------------------------------------------------------------------------------------
- /* Search for ID in temporary contracts */
- String findTempCon = new String(
- "SELECT * FROM Contract_Fin WHERE Id_Stuff='"+teach_id+"' AND In_Effect='1'");
- Statement stmt3 = connection.con.createStatement();
- ResultSet rs3 = stmt3.executeQuery(findTempCon);
- // if true teach is temporary stuff
- if(rs3.next()) {
- int member_count=0;
- DBCalendar Today = new DBCalendar();
- Today.setDBtime(date);
- // Count family members -------------------------------------------------------------
- String findMember = new String(
- "SELECT * FROM Family_Member WHERE Id_Stuff='"+teach_id+"'");
- Statement stmt4 = connection.con.createStatement();
- ResultSet rs4 = stmt4.executeQuery(findMember);
- while(rs4.next()) {
- // check if member eligible for benefits
- String rltn = rs4.getString("Relation");
- if(rltn.charAt(0)=='s') {
- member_count++;
- } else {
- String dob = rs4.getString("DateOfBirth");
- // check if member eligible for benefits
- if(Today.getRemainder(dob) < 18) {
- member_count++;
- }
- }
- }
- // Calculate Final salary ----------------------------------------
- float salary = GValues.getBasic_Temp_Teacher_Wage() + ((GValues.getBasic_Temp_Teacher_Wage()*
- GValues.getMember_Percentage())*member_count) + GValues.getLibrary_Bonus();
- Payment check = new Payment((getLatestId("Payment","id_Payment")+1),teach_id,date,"Μηνιάτικο",salary,
- GValues.getBasic_Temp_Teacher_Wage(),((GValues.getBasic_Temp_Teacher_Wage()
- *GValues.getMember_Percentage())*member_count),
- GValues.getLibrary_Bonus(),"Επίδομα Βιβλιοθήκης");
- //finally insert check to DB ...
- check.insertToTable();
- }
- }
- //=============================================================================================================
- /* Select active Admins */
- String selectAdmin = new String(
- "SELECT * FROM Admin_Stuff WHERE Active='1'");
- Statement stmt1a = connection.con.createStatement();
- ResultSet rs1a = stmt1a.executeQuery(selectAdmin);
- while (rs1a.next()) {
- int admin_id = rs1a.getInt("id_Stuff");
- /* Search for ID in permanent contracts */
- String findPermCon = new String( // contract needs to be inEffect
- "SELECT * FROM Contract_Inf WHERE Id_Stuff='"+Integer.toString(admin_id)+"' AND In_Effect='1'");
- Statement stmt2a = connection.con.createStatement();
- ResultSet rs2a = stmt2a.executeQuery(findPermCon);
- // if true teach is permanent stuff
- if(rs2a.next()) {
- int member_count=0;
- float YearsOfService=0;
- DBCalendar Today = new DBCalendar();
- Today.setDBtime(date);
- // Count family members ---------------------------------------------------------
- String findMember = new String(
- "SELECT * FROM Family_Member WHERE Id_Stuff='"+admin_id+"'");
- Statement stmt3a = connection.con.createStatement();
- ResultSet rs3a = stmt3a.executeQuery(findMember);
- while(rs3a.next()) {
- // check if member eligible for benefits
- String rltn = rs3a.getString("Relation");
- if(rltn.charAt(0)=='s') {
- member_count++;
- } else {
- String dob = rs3a.getString("DateOfBirth");
- // check if member eligible for benefits
- if(Today.getRemainder(dob) < 18) {
- member_count++;
- }
- }
- }
- // Count Work experience--------------------------------------------------------
- String Date_Begin = rs2a.getString("Date_Begin");
- YearsOfService = Today.getRemainder(Date_Begin);
- // Calculate current salary w8 benefits
- float expBoostWage = GValues.getBasic_Admin_Wage();
- while(YearsOfService > 1 ) { // for first year no rise, sorry
- expBoostWage = expBoostWage*GValues.getAdmin_Rise_Percentage();
- }
- // Calculate final Salary--------------------------------------------------------
- float salary = expBoostWage + ((GValues.getBasic_Admin_Wage()*GValues.getMember_Percentage())*member_count);
- Payment check = new Payment((getLatestId("Payment","id_Payment")+1),admin_id,date,"Μηνιάτικο",salary,
- GValues.getBasic_Admin_Wage(),((GValues.getBasic_Admin_Wage()*GValues.getMember_Percentage())*member_count),
- 0f,null);
- //finally insert check to DB ...
- check.insertToTable();
- }
- //---------------------------------------------------------------------------------------
- /* Search for ID in temporary contracts */
- String findTempCon = new String(
- "SELECT * FROM Contract_Fin WHERE Id_Stuff='"+admin_id+"' AND In_Effect='1'");
- Statement stmt3a = connection.con.createStatement();
- ResultSet rs3a = stmt3a.executeQuery(findTempCon);
- // if true teach is temporary stuff
- if(rs3a.next()) {
- int member_count=0;
- DBCalendar Today = new DBCalendar();
- Today.setDBtime(date);
- // Count family members -------------------------------------------------------------
- String findMember = new String(
- "SELECT * FROM Family_Member WHERE Id_Stuff='"+admin_id+"'");
- Statement stmt4a = connection.con.createStatement();
- ResultSet rs4a = stmt4a.executeQuery(findMember);
- while(rs4a.next()) {
- // check if member eligible for benefits
- String rltn = rs4a.getString("Relation");
- if(rltn.charAt(0)=='s') {
- member_count++;
- } else {
- String dob = rs4a.getString("DateOfBirth");
- // check if member eligible for benefits
- if(Today.getRemainder(dob) < 18) {
- member_count++;
- }
- }
- }
- // Calculate Final salary ----------------------------------------
- float salary = GValues.getBasic_Temp_Admin_Wage() + ((GValues.getBasic_Temp_Admin_Wage()*
- GValues.getMember_Percentage())*member_count);
- Payment check = new Payment((getLatestId("Payment","id_Payment")+1),admin_id,date,"Μηνιάτικο",salary,
- GValues.getBasic_Temp_Admin_Wage(),((GValues.getBasic_Temp_Admin_Wage()
- *GValues.getMember_Percentage())*member_count),0f,null);
- //finally insert check to DB ...
- check.insertToTable();
- }
- }
- }
- //================== QESTIONS ===============================
- // 1. Payment period Summary
- public ArrayList<Payment> viewPayments(String from, String until, boolean teach, boolean perma) throws SQLException, ParseException {
- ArrayList<Payment> res = new ArrayList<Payment>();
- if(teach) { // true for teach, false for Admin
- /* Select Teachers */
- String selectTeacher = new String(
- "SELECT * FROM Teaching_Stuff");
- Statement stmt1 = connection.con.createStatement();
- ResultSet rs1 = stmt1.executeQuery(selectTeacher);
- while (rs1.next()) {
- if(perma) { // true for permanent, false for temporary
- int teach_id = rs1.getInt("id_Stuff");
- /* Search for ID in permanent contracts */
- String findPermCon = new String(
- "SELECT * FROM Contract_Inf WHERE Id_Stuff='"+Integer.toString(teach_id)+"'");
- Statement stmt2 = connection.con.createStatement();
- ResultSet rs2 = stmt2.executeQuery(findPermCon);
- while (rs2.next()) {
- // ------------- Date checks in case of change of permanence ---------
- String from2 = from;
- String until2 = until;
- DBCalendar start = new DBCalendar();
- start.setDBtime(rs2.getString("Date_Begin"));
- DBCalendar from1 = new DBCalendar();
- from1.setDBtime(from);
- if(start.getDBtime().after(from1)) {
- from2 = start.getDBtimeString();
- }
- if(rs2.getString("In_Effect").charAt(0)=='0') {
- DBCalendar end = new DBCalendar();
- if(!(rs2.getString("Termination")==null)) { // !!!!!!!!!
- end.setDBtime(rs2.getString("Termination"));
- }
- DBCalendar until1 = new DBCalendar();
- until1.setDBtime(until);
- if(end.getDBtime().before(until1)) {
- until2 = end.getDBtimeString();
- }
- }
- /* Get payments from DB */
- String findPayment = new String(
- "SELECT * FROM Payment WHERE Id_Stuff='"+Integer.toString(teach_id)+
- "' AND DateOfPay BETWEEN '"+from2+"' AND '"+until2+"'");
- Statement stmt3 = connection.con.createStatement();
- ResultSet rs3 = stmt3.executeQuery(findPayment);
- while(rs3.next()) {
- Payment p = new Payment(rs3.getInt("id_Payment"), rs3.getInt("id_Stuff"),
- rs3.getString("TypeOfPay"), rs3.getString("DateOfPay"), rs3.getFloat("ValueOfPay"),
- rs3.getFloat("Basic_Wage"), rs3.getFloat("Family_bonus"), rs3.getFloat("Benefits"),
- rs3.getString("Comment"));
- res.add(p);
- }
- }
- } else /* Not permanent stuff */ {
- int teach_id = rs1.getInt("id_Stuff");
- /* Search for ID in temporary contracts */
- String findTempCon = new String(
- "SELECT * FROM Contract_Fin WHERE Id_Stuff='"+teach_id+"'");
- Statement stmt2 = connection.con.createStatement();
- ResultSet rs2 = stmt2.executeQuery(findTempCon);
- // if true teach is temporary stuff
- while (rs2.next()) {
- // ------------- Date checks in case of change of permanence ---------
- String from2 = from;
- String until2 = until;
- DBCalendar start = new DBCalendar();
- start.setDBtime(rs2.getString("Date_Begin"));
- DBCalendar from1 = new DBCalendar();
- from1.setDBtime(from);
- if(start.getDBtime().after(from1)) {
- from2 = start.getDBtimeString();
- }
- if(rs2.getString("In_Effect").charAt(0)=='0') {
- DBCalendar end = new DBCalendar();
- if(!(rs2.getString("Termination")==null)) { // !!!!!!!!!
- end.setDBtime(rs2.getString("Termination"));
- }
- DBCalendar until1 = new DBCalendar();
- until1.setDBtime(until);
- if(end.getDBtime().before(until1)) {
- until2 = end.getDBtimeString();
- }
- }
- /* Get payments from DB */
- String findPayment = new String(
- "SELECT * FROM Payment WHERE Id_Stuff='"+Integer.toString(teach_id)+
- "' AND DateOfPay BETWEEN '"+from2+"' AND '"+until2+"'");
- Statement stmt3 = connection.con.createStatement();
- ResultSet rs3 = stmt3.executeQuery(findPayment);
- while(rs3.next()) {
- Payment p = new Payment(rs3.getInt("id_Payment"), rs3.getInt("id_Stuff"),
- rs3.getString("TypeOfPay"), rs3.getString("DateOfPay"), rs3.getFloat("ValueOfPay"),
- rs3.getFloat("Basic_Wage"), rs3.getFloat("Family_bonus"), rs3.getFloat("Benefits"),
- rs3.getString("Comment"));
- res.add(p);
- }
- }
- }
- }
- } else { // 1 for teach, 0 for Administrator
- /* Select Administrator */
- String selectAdmin = new String(
- "SELECT * FROM Admin_Stuff");
- Statement stmt1 = connection.con.createStatement();
- ResultSet rs1 = stmt1.executeQuery(selectAdmin);
- while (rs1.next()) {
- if(perma) { // 1 for permanent , 0 for temporary
- int admin_id = rs1.getInt("id_Stuff");
- /* Search for ID in permanent contracts */
- String findPermCon = new String(
- "SELECT * FROM Contract_Inf WHERE Id_Stuff='"+Integer.toString(admin_id)+"'");
- Statement stmt2 = connection.con.createStatement();
- ResultSet rs2 = stmt2.executeQuery(findPermCon);
- while (rs2.next()) {
- // ------------- Date checks in case of change of permanence ---------
- String from2 = from;
- String until2 = until;
- DBCalendar start = new DBCalendar();
- start.setDBtime(rs2.getString("Date_Begin"));
- DBCalendar from1 = new DBCalendar();
- from1.setDBtime(from);
- if(start.getDBtime().after(from1)) {
- from2 = start.getDBtimeString();
- }
- if(rs2.getString("In_Effect").charAt(0)=='0') {
- DBCalendar end = new DBCalendar();
- if(!(rs2.getString("Termination")==null)) { // !!!!!!!!!
- end.setDBtime(rs2.getString("Termination"));
- }
- DBCalendar until1 = new DBCalendar();
- until1.setDBtime(until);
- if(end.getDBtime().before(until1)) {
- until2 = end.getDBtimeString();
- }
- }
- /* Get payments from DB */
- String findPayment = new String(
- "SELECT * FROM Payment WHERE Id_Stuff='"+Integer.toString(admin_id)+
- "' AND DateOfPay BETWEEN '"+from2+"' AND '"+until2+"'");
- Statement stmt3 = connection.con.createStatement();
- ResultSet rs3 = stmt3.executeQuery(findPayment);
- while(rs3.next()) {
- Payment p = new Payment(rs3.getInt("id_Payment"), rs3.getInt("id_Stuff"),
- rs3.getString("TypeOfPay"), rs3.getString("DateOfPay"), rs3.getFloat("ValueOfPay"),
- rs3.getFloat("Basic_Wage"), rs3.getFloat("Family_bonus"), rs3.getFloat("Benefits"),
- rs3.getString("Comment"));
- res.add(p);
- }
- }
- } else /* Not permanent stuff */ {
- int admin_id = rs1.getInt("id_Stuff");
- /* Search for ID in temporary contracts */
- String findTempCon = new String(
- "SELECT * FROM Contract_Fin WHERE Id_Stuff='"+admin_id+"'");
- Statement stmt2 = connection.con.createStatement();
- ResultSet rs2 = stmt2.executeQuery(findTempCon);
- // if true teach is temporary stuff
- while (rs2.next()) {
- // ------------- Date checks in case of change of permanence ---------
- String from2 = from;
- String until2 = until;
- DBCalendar start = new DBCalendar();
- start.setDBtime(rs2.getString("Date_Begin"));
- DBCalendar from1 = new DBCalendar();
- from1.setDBtime(from);
- if(start.getDBtime().after(from1)) {
- from2 = start.getDBtimeString();
- }
- if(rs2.getString("In_Effect").charAt(0)=='0') {
- DBCalendar end = new DBCalendar();
- if(!(rs2.getString("Termination")==null)) { // !!!!!!!!!
- end.setDBtime(rs2.getString("Termination"));
- }
- DBCalendar until1 = new DBCalendar();
- until1.setDBtime(until);
- if(end.getDBtime().before(until1)) {
- until2 = end.getDBtimeString();
- }
- }
- /* Get payments from DB */
- String findPayment = new String(
- "SELECT * FROM Payment WHERE Id_Stuff='"+Integer.toString(admin_id)+
- "' AND DateOfPay BETWEEN '"+from2+"' AND '"+until2+"'");
- Statement stmt3 = connection.con.createStatement();
- ResultSet rs3 = stmt3.executeQuery(findPayment);
- while(rs3.next()) {
- Payment p = new Payment(rs3.getInt("id_Payment"), rs3.getInt("id_Stuff"),
- rs3.getString("TypeOfPay"), rs3.getString("DateOfPay"), rs3.getFloat("ValueOfPay"),
- rs3.getFloat("Basic_Wage"), rs3.getFloat("Family_bonus"), rs3.getFloat("Benefits"),
- rs3.getString("Comment"));
- res.add(p);
- }
- }
- }
- }
- }
- return res;
- }
- //2. MAX - MIN - Mean Salary per Category
- public ArrayList<Payment> getSalaryStats(boolean teach, boolean perma) throws SQLException, ParseException {
- ArrayList<Payment> res = new ArrayList<Payment>();
- Payment max = new Payment(-1,-1,"0","0",0f,0f,0f,0f,"0");
- Payment min= new Payment(-1,-1,"0","0",20000000f,0f,0f,0f,"0");
- Payment mean= new Payment(-1,-1,"0","0",0f,0f,0f,0f,"0");
- float meanv=0;
- int i=0;
- ArrayList<Payment> ref = viewPayments("1917-10-25",DBDate.getDBtimeString(),teach,perma);
- if((ref!=null)&&(!ref.isEmpty())) {
- max = ref.get(0);
- min = ref.get(0);
- }
- for(Payment p : ref ) {
- if(p.getValueOfPay()>max.getValueOfPay()) {
- max.setValueOfPay(p.getValueOfPay());
- max.setId_Stuff(p.getId_Stuff());
- }
- if(p.getValueOfPay()<min.getValueOfPay()) {
- min.setValueOfPay(p.getValueOfPay());
- min.setId_Stuff(p.getId_Stuff());
- }
- meanv += p.getValueOfPay();
- i++;
- }
- mean.setValueOfPay(meanv/(float)i);
- res.add(max); // first max
- res.add(min); // second min
- res.add(mean); // third mean
- return res;
- }
- // 3.
- // 4. ===============================================================================================================
- // A
- /* Returns Staff object to be used with viewStuffToString() */
- public Stuff findStuff(int id) throws SQLException { // TO DO
- Stuff res = null;
- /* Select Teachers */
- String selectTeacher = new String(
- "SELECT * FROM Teaching_Stuff WHERE id_Stuff='"+id+"'");
- Statement stmt1 = connection.con.createStatement();
- ResultSet rs1 = stmt1.executeQuery(selectTeacher);
- // Valid if the bastard is a teacher
- while (rs1.next()) {
- Teacher teach = new Teacher(rs1.getInt("id_Stuff"),rs1.getString("FirstName"),rs1.getString("LastName"),
- rs1.getString("FatherName"),rs1.getString("Gender").charAt(0),rs1.getString("DateOfBirth"),
- rs1.getString("AMKA"),rs1.getString("AFM"),rs1.getString("Department"),
- rs1.getString("Marital_Status").charAt(0),rs1.getString("Address"),rs1.getString("Bank"),
- rs1.getString("IBAN"),rs1.getString("Active").charAt(0),
- rs1.getString("Prof_Title"),rs1.getString("Telephone"));
- res = teach;
- }
- /* Select Admin */
- String selectAdmin = new String(
- "SELECT * FROM Admin_Stuff WHERE id_Stuff='"+id+"'");
- Statement stmt2 = connection.con.createStatement();
- ResultSet rs2 = stmt2.executeQuery(selectAdmin);
- // Valid if the bastard is an Admin
- while (rs2.next()) {
- Admin admin = new Admin(rs1.getInt("id_Stuff"),rs1.getString("FirstName"),rs1.getString("LastName"),
- rs1.getString("FatherName"),rs1.getString("Gender").charAt(0),rs1.getString("DateOfBirth"),
- rs1.getString("AMKA"),rs1.getString("AFM"),rs1.getString("Department"),
- rs1.getString("Marital_Status").charAt(0),rs1.getString("Address"),rs1.getString("Bank"),
- rs1.getString("IBAN"),rs1.getString("Active").charAt(0),
- rs1.getString("Admin_Title"),rs1.getString("Duty"),rs1.getString("Telephone"));
- res = admin;
- }
- return res;
- }
- // B
- /* return LAST !!! Contract to be used with viewContractToString() */
- public Contract findContract(int id) throws SQLException {
- Contract cont = null;
- // Select from permanent contracts
- /* Search for ID in permanent contracts */
- String findPermCon = new String(
- "SELECT * FROM Contract_Inf WHERE Id_Stuff='"+Integer.toString(id)+"'");
- Statement stmt1 = connection.con.createStatement();
- ResultSet rs1 = stmt1.executeQuery(findPermCon);
- while (rs1.next()) {
- ContrInf c_i = new ContrInf(rs1.getInt("id_Contract"), rs1.getString("Date_Begin"),
- rs1.getInt("Hours_Week"), rs1.getInt("Days_Leave"),
- rs1.getString("In_Effect").charAt(0), rs1.getInt("id_Stuff"));
- if((!(rs1.getString("Termination")==null)))
- c_i.setTermination(rs1.getString("Termination"));
- cont = c_i;
- }
- // Select from temporary contracts
- /* Search for ID in temporary contracts */
- String findTempCon = new String(
- "SELECT * FROM Contract_Fin WHERE Id_Stuff='"+Integer.toString(id)+"'");
- Statement stmt2 = connection.con.createStatement();
- ResultSet rs2 = stmt2.executeQuery(findTempCon);
- while (rs2.next()) {
- ContrFin c_f = new ContrFin(rs1.getInt("id_Contract"), rs1.getString("Date_Begin"),
- rs1.getInt("Hours_Week"), rs1.getInt("Days_Leave"),
- rs1.getString("In_Effect").charAt(0),rs1.getString("Date_End"), rs1.getInt("id_Stuff"));
- if((!(rs1.getString("Termination")==null)))
- c_f.setTermination(rs1.getString("Termination"));
- cont = c_f;
- }
- return cont;
- }
- // C
- /* return an ArrayList<Payment> containing all payments done so far*/
- public ArrayList<Payment> getStuffPaymentsHistory(int id) throws SQLException {
- ArrayList<Payment> res = new ArrayList<Payment>();
- String findPay = new String(
- "SELECT * FROM Payment WHERE id_Stuff='"+Integer.toString(id)+"'");
- Statement stmt = connection.con.createStatement();
- ResultSet rs = stmt.executeQuery(findPay);
- while(rs.next()) {
- Payment p = new Payment(rs.getInt("id_Payment"), rs.getInt("id_Stuff"),
- rs.getString("TypeOfPay"), rs.getString("DateOfPay"), rs.getFloat("ValueOfPay"),
- rs.getFloat("Basic_Wage"), rs.getFloat("Family_bonus"), rs.getFloat("Benefits"),
- rs.getString("Comment"));
- res.add(p);
- }
- return res;
- }
- // D
- /* returns stuffs relatives */
- public ArrayList<Member> getStuffFamilyMembers(int id) throws SQLException {
- ArrayList<Member> res = new ArrayList<Member>();
- String findMember = new String(
- "SELECT * FROM Family_Member WHERE Id_Stuff='"+Integer.toString(id)+"'");
- Statement stmt = connection.con.createStatement();
- ResultSet rs = stmt.executeQuery(findMember);
- while(rs.next()) {
- Member m = new Member(rs.getInt("id_Member"),rs.getString("First_Name"),rs.getString("LastName"),
- rs.getString("Relation").charAt(0),rs.getString("Gender").charAt(0),rs.getString("DateOfBirth"),
- rs.getString("AMKA"),rs.getInt("id_Stuff"));
- res.add(m);
- }
- return res;
- }
- // E
- /* returns ArrayList<Project> with projects he is currently active! */
- public ArrayList<Project> getStuffProjects(int id) throws SQLException {
- ArrayList<Project> res = new ArrayList<Project>();
- String findProj = new String(
- "SELECT * FROM Participates_In WHERE Id_Stuff='"+Integer.toString(id)+"'");
- Statement stmt1 = connection.con.createStatement();
- ResultSet rs1 = stmt1.executeQuery(findProj);
- while(rs1.next()) {
- String findProj2 = new String(
- "SELECT * FROM Project WHERE Id_Project='"+rs1.getInt("id_Project")+"'");
- Statement stmt2 = connection.con.createStatement();
- ResultSet rs2 = stmt2.executeQuery(findProj2);
- while(rs2.next()) {
- Project p = new Project(rs2.getInt("id_Project"),rs2.getString("Name"));
- res.add(p);
- }
- }
- return res;
- }
- // F
- /* returns ArrayList<Degree> stuff*/
- public ArrayList<Degree> getStuffDegrees(int id) throws SQLException {
- ArrayList<Degree> res = new ArrayList<Degree>();
- String findDegree = new String(
- "SELECT * FROM Edu_Degree WHERE Id_Stuff='"+Integer.toString(id)+"'");
- Statement stmt = connection.con.createStatement();
- ResultSet rs = stmt.executeQuery(findDegree);
- while(rs.next()) {
- Degree d = new Degree(rs.getInt("id_Degree"),rs.getString("Title"),rs.getInt("Year"),
- rs.getFloat("Mark"),rs.getString("University"),rs.getInt("id_Stuff"));
- res.add(d);
- }
- return res;
- }
- // G returs ArrayList<Course>
- public ArrayList<Course> getStuffCourses(int id) throws SQLException {
- ArrayList<Course> res = new ArrayList<Course>();
- String findDegree = new String(
- "SELECT * FROM Course WHERE Id_Stuff='"+Integer.toString(id)+"'");
- Statement stmt = connection.con.createStatement();
- ResultSet rs = stmt.executeQuery(findDegree);
- while(rs.next()) {
- Course c = new Course(rs.getInt("id_Course"),rs.getString("Title"),rs.getInt("id_Stuff"));
- res.add(c);
- }
- return res;
- }
- //========================================================================================================
- // returns the oldest date in appropriate DB table column
- public String findOldestDate(String table, String column) throws SQLException {
- String date=null;
- /* Get value from DB */
- String findDate = new String(
- "SELECT * FROM "+table+" WHERE "+column+" <= ALL (SELECT MIN("+column+") FROM "+table+")");
- Statement stmt3 = connection.con.createStatement();
- ResultSet rs3 = stmt3.executeQuery(findDate);
- while(rs3.next()) {
- date = rs3.getString(column);
- }
- return date;
- }
- //5. Yearly Sum of Pays per Stuff Category !!SINCE THE CREATION OF DB
- public ArrayList<String> getYearlySum (boolean teach, boolean perma) throws SQLException, ParseException {
- ArrayList<String> res = new ArrayList<String>();
- DBCalendar dbStart = new DBCalendar();
- dbStart.setDBtime(findOldestDate("Payment","DateOfPay"));
- while(!(dbStart.getDBtime().after(DBDate.getDBtime()))) {
- int year = dbStart.getDBtime().get(Calendar.YEAR);
- ArrayList<Payment> ref = viewPayments(String.valueOf(year)+"-01-01",
- String.valueOf(year)+"-12-31",teach,perma);
- double SalarySum=0;
- for(Payment p : ref) {
- SalarySum += p.getValueOfPay();
- }
- // format double to string
- DecimalFormat decimalFormat = new DecimalFormat("#,##0.00");
- String numberAsString = decimalFormat.format(SalarySum);
- String s = "\nΗ συνολική αξία των πληρωμών στην επιλεγμένη κατηγορία προσωπικού \nγια το έτος "+String.valueOf(year)+
- " ανέρχεται στις "+numberAsString+" δρχ.\n";
- // add to ArrayList
- res.add(s);
- // progress calendar variable by one year
- dbStart.getDBtime().add(Calendar.YEAR,1);
- }
- return res;
- }
- public static void main(String[] args) {
- }
- ////////////////////////////////////////////////////////////////////////////////////////
- public void progressDate(String date) throws ParseException, SQLException {
- DBCalendar tmp = new DBCalendar();
- tmp.setDBtime(date);
- if(tmp.getDBtime().after(DBDate.getDBtime())) {
- while(DBDate.getDBtime().before(tmp.getDBtime())) {
- DBDate.getDBtime().add(Calendar.DATE,1);
- updateDB(DBDate);
- }
- }
- }
- public void updateDB(DBCalendar cal) throws SQLException, ParseException {
- int lastDay = cal.getDBtime().getActualMaximum(Calendar.DAY_OF_MONTH);
- int firstDay = 1;
- // if end of the month -> pay yo!
- if(cal.getDBtime().get(Calendar.DATE)==lastDay) {
- payEmployees(cal.getDBtimeString());
- updateContracts(cal.getDBtimeString());
- }
- if(cal.getDBtime().get(Calendar.DATE)==firstDay) {
- updateContracts(cal.getDBtimeString());
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement