Advertisement
DeafCat

procedure

Jan 17th, 2020
108
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 42.80 KB | None | 0 0
  1. package database;
  2.  
  3. import java.sql.*;
  4. import java.text.DecimalFormat;
  5. import java.text.ParseException;
  6. import java.util.ArrayList;
  7. import java.util.Calendar;
  8.  
  9. import connection.JDBCConnection;
  10. import data.Admin;
  11. import data.ContrFin;
  12. import data.ContrInf;
  13. import data.Contract;
  14. import data.Course;
  15. import data.DBCalendar;
  16. import data.Degree;
  17. import data.Globals;
  18. import data.Member;
  19. import data.Payment;
  20. import data.Project;
  21. import data.Stuff;
  22. import data.Teacher;
  23.  
  24.  
  25. public class Procedure {
  26.  
  27. // globals
  28. /* float Basic_Teacher_Wage = 1200f;
  29. float Basic_Admin_Wage = 1000f;
  30. float Teacher_Rise_Percentage = 0.15f;
  31. float Admin_Rise_Percentage = 0.15f;
  32. float Member_Percentage = 0.05f;
  33. float Reasearch_Bonus = 500f;
  34. float Library_Bonus = 300f;
  35. float Basic_Temp_Teacher_Wage = 1200f;
  36. float Basic_Temp_Admin_Wage = 1000f;
  37. int pay_count = 0;
  38. int contract_count = 0;
  39. int stuff_count = 0;*/
  40.  
  41. Globals GValues = new Globals();
  42.  
  43. DBCalendar DBDate = new DBCalendar(); // Current DB date
  44.  
  45. JDBCConnection connection = new JDBCConnection();
  46.  
  47. public void setConnection(JDBCConnection conn) {
  48. this.connection = conn;
  49. }
  50.  
  51. public JDBCConnection getConnection() {
  52. return this.connection;
  53. }
  54.  
  55. public DBCalendar getDBDate() {
  56. return DBDate;
  57. }
  58.  
  59. public void setDBDate(DBCalendar dBDate) {
  60. DBDate = dBDate;
  61. }
  62.  
  63. // true if date after current date
  64. public boolean isDateValid(String date) throws ParseException {
  65.  
  66. DBCalendar tempdate = new DBCalendar();
  67. tempdate.setDBtime(date);
  68.  
  69. if(tempdate.getDBtime().before(DBDate.getDBtime())) {
  70. return false;
  71. }
  72.  
  73. return true;
  74. }
  75.  
  76. /* If date day != 1 get the next valid date */
  77. public String getStartOfMonth(String date) throws ParseException {
  78. DBCalendar start = new DBCalendar();
  79. start.setDBtime(date);
  80.  
  81. if(start.getDBtime().get(Calendar.DATE) > 1) {
  82. start.getDBtime().set(start.getDBtime().get(Calendar.YEAR), (start.getDBtime().get(Calendar.MONTH)+1), 1);
  83. }
  84.  
  85.  
  86. return start.getDBtimeString();
  87. }
  88.  
  89. /* If date day != 31/30 get the next valid date */
  90. public String getEndOfMonth(String date) throws ParseException {
  91. DBCalendar start = new DBCalendar();
  92. start.setDBtime(date);
  93.  
  94. DBCalendar tempdate = new DBCalendar();
  95. tempdate.getDBtime().set(start.getDBtime().get(Calendar.YEAR), (start.getDBtime().get(Calendar.MONTH)+1), 1);
  96. tempdate.getDBtime().add(Calendar.DAY_OF_MONTH, -1);
  97.  
  98.  
  99. if(start.getDBtime() != tempdate.getDBtime()) {
  100. start.setDBtime(tempdate.getDBtimeString());
  101. }
  102.  
  103.  
  104. return start.getDBtimeString();
  105. }
  106.  
  107.  
  108.  
  109. void createTables() throws SQLException {
  110.  
  111. /* Create Globals Store Stack */
  112. String createStore = new String(
  113. "CREATE TABLE Globals (" +
  114. "id int NOT NULL,"+
  115. "Basic_Teacher_Wage float(2),"+
  116. "Basic_Admin_Wage float(2),"+
  117. "Teacher_Rise_Percentage float(2),"+
  118. "Admin_Rise_Percentage float(2),"+
  119. "Member_Percentage float(2),"+
  120. "Reasearch_Bonus float(2),"+
  121. "Library_Bonus float(2),"+
  122. "Basic_Temp_Teacher_Wage float(2),"+
  123. "Basic_Temp_Admin_Wage float(2)"+
  124. ");");
  125.  
  126. Statement stmt = connection.con.createStatement();
  127. stmt.executeUpdate(createStore);
  128.  
  129. /* Creates Teaching_Stuff table */
  130. String createTeachers = new String(
  131. "CREATE TABLE Teaching_Stuff ("+
  132. "id_Stuff int NOT NULL,"+
  133. "FirstName varchar(100) NOT NULL,"+
  134. "LastName varchar(100) NOT NULL,"+
  135. "FatherName varchar(100) NOT NULL,"+
  136. "Gender char(1) NOT NULL,"+
  137. "DateOfBirth DATE NOT NULL,"+
  138. "AMKA varchar(11) NOT NULL,"+
  139. "AFM varchar(9) NOT NULL,"+
  140. "Department varchar(255) NOT NULL,"+
  141. "Marital_Status char(1) NOT NULL,"+
  142. "Address varchar(255) NOT NULL,"+
  143. "Bank varchar(100) NOT NULL,"+
  144. "IBAN varchar(33) NOT NULL,"+
  145. "Active char(1) NOT NULL,"+
  146. "Prof_Title varchar(100),"+
  147. "Telephone varchar(16)"+
  148. ");");
  149.  
  150. stmt = connection.con.createStatement();
  151. stmt.executeUpdate(createTeachers);
  152.  
  153. /* Creates Admin_Stuff table */
  154. String createAdmin = new String(
  155. "CREATE TABLE Admin_Stuff ("+
  156. "id_Stuff int NOT NULL,"+
  157. "FirstName varchar(100) NOT NULL,"+
  158. "LastName varchar(100) NOT NULL,"+
  159. "FatherName varchar(100) NOT NULL,"+
  160. "Gender char(1) NOT NULL,"+
  161. "DateOfBirth DATE NOT NULL,"+
  162. "AMKA varchar(11) NOT NULL,"+
  163. "AFM varchar(9) NOT NULL,"+
  164. "Department varchar(255) NOT NULL,"+
  165. "Marital_Status char(1) NOT NULL,"+
  166. "Address varchar(255) NOT NULL,"+
  167. "Bank varchar(100) NOT NULL,"+
  168. "IBAN varchar(33) NOT NULL,"+
  169. "Active char(1) NOT NULL,"+
  170. "Admin_Title varchar(100)," +
  171. "Duty varchar(255)," +
  172. "Telephone varchar(16)"+
  173. ");");
  174.  
  175. stmt = connection.con.createStatement();
  176. stmt.executeUpdate(createAdmin);
  177.  
  178. /* Creates Contract_Inf table */
  179. String createInf = new String(
  180. "CREATE TABLE Contract_Inf (" +
  181. "id_Contract int NOT NULL," +
  182. "Date_Begin DATE NOT NULL," +
  183. "Hours_Week int NOT NULL," +
  184. "Days_Leave int NOT NULL," +
  185. "In_Effect char(1) NOT NULL," +
  186. "Termination DATE,"+
  187. "id_Stuff int NOT NULL" +
  188. ");");
  189.  
  190. stmt = connection.con.createStatement();
  191. stmt.executeUpdate(createInf);
  192.  
  193. /* Creates Contract_Fin table */
  194. String createFin = new String(
  195. "CREATE TABLE Contract_Fin (" +
  196. "id_Contract int NOT NULL," +
  197. "Date_Begin DATE NOT NULL," +
  198. "Hours_Week int NOT NULL," +
  199. "Days_Leave int NOT NULL," +
  200. "In_Effect char(1) NOT NULL," +
  201. "Date_End DATE NOT NULL,"+
  202. "Termination DATE,"+
  203. "id_Stuff int NOT NULL" +
  204. ");");
  205.  
  206. stmt = connection.con.createStatement();
  207. stmt.executeUpdate(createFin);
  208.  
  209. /* Creates Family_Member table */
  210. String createMember = new String(
  211. "CREATE TABLE Family_Member (" +
  212. "id_Member int NOT NULL," +
  213. "FirstName varchar(100) NOT NULL," +
  214. "LastName varchar(100) NOT NULL," +
  215. "Relation char(1) NOT NULL," +
  216. "Gender char(1) NOT NULL," +
  217. "DateOfBirth DATE NOT NULL," +
  218. "AMKA varchar(11) NOT NULL," +
  219. "id_Stuff int NOT NULL" +
  220. ");");
  221.  
  222. stmt = connection.con.createStatement();
  223. stmt.executeUpdate(createMember);
  224.  
  225. /* Creates Edu_Degree table */
  226. String createDegree = new String(
  227. "CREATE TABLE Edu_Degree (" +
  228. "id_Degree int NOT NULL," +
  229. "Title varchar(255) NOT NULL," +
  230. "Year int NOT NULL," +
  231. "Mark float(2) NOT NULL," +
  232. "University varchar(255) NOT NULL," +
  233. "id_Stuff int NOT NULL" +
  234. ");");
  235.  
  236. stmt = connection.con.createStatement();
  237. stmt.executeUpdate(createDegree);
  238.  
  239. /* Creates Payment table */
  240. String createPayment = new String(
  241. "CREATE TABLE Payment (" +
  242. "id_Payment int NOT NULL," +
  243. "id_Stuff int NOT NULL," +
  244. "TypeOfPay varchar(100) NOT NULL," +
  245. "DateOfPay DATE NOT NULL," +
  246. "ValueOfPay float(2) NOT NULL," +
  247. "Basic_Wage float(2) NOT NULL," +
  248. "Family_bonus float(2) NOT NULL," +
  249. "Benefits float(2) NOT NULL," +
  250. "Comment varchar(255)" +
  251. ");");
  252.  
  253. stmt = connection.con.createStatement();
  254. stmt.executeUpdate(createPayment);
  255.  
  256. /* Creates Course table */
  257. String createCourse = new String(
  258. "CREATE TABLE Course (" +
  259. "id_Course int NOT NULL," +
  260. "Title varchar(255) NOT NULL," +
  261. "id_Stuff int NOT NULL" +
  262. ");");
  263.  
  264. stmt = connection.con.createStatement();
  265. stmt.executeUpdate(createCourse);
  266.  
  267. /* Creates Participates_In table */
  268. String createParticipates_In = new String(
  269. "CREATE TABLE Participates_In (" +
  270. "id_Project int NOT NULL," +
  271. "Date_In DATE NOT NULL," +
  272. "Date_Out DATE," + // corrected
  273. "id_Stuff int NOT NULL" +
  274. ");");
  275.  
  276. stmt = connection.con.createStatement();
  277. stmt.executeUpdate(createParticipates_In);
  278.  
  279. /* Creates Project table */
  280. String createProject = new String(
  281. "CREATE TABLE Project (" +
  282. "id_Project int NOT NULL," +
  283. "Name varchar(255) NOT NULL" +
  284. ");");
  285.  
  286. stmt = connection.con.createStatement();
  287. stmt.executeUpdate(createProject);
  288.  
  289.  
  290. }
  291.  
  292. /*returns Teacher object*/
  293. public Teacher getTeacher(int id) throws SQLException {
  294.  
  295. Teacher t = new Teacher();
  296.  
  297. /* Select active Teachers */
  298. String selectTeacher = new String(
  299. "SELECT * FROM Teaching_Stuff WHERE Id_Stuff='"+id+"'");
  300.  
  301. Statement stmt1 = connection.con.createStatement();
  302. ResultSet rs1 = stmt1.executeQuery(selectTeacher);
  303.  
  304. while(rs1.next()) {
  305.  
  306. t.setId(rs1.getInt("Id_Stuff"));
  307. t.setFirstName(rs1.getString("FirstName"));
  308. t.setLastName(rs1.getString("LastName"));
  309. t.setFatherName(rs1.getString("FatherName"));
  310. t.setGender(rs1.getString("Gender").charAt(0));
  311. t.setDateOfBirth(rs1.getString("DateOfBirth"));
  312. t.setAMKA(rs1.getString("AMKA"));
  313. t.setAFM(rs1.getString("AFM"));
  314. t.setDepartment(rs1.getString("Department"));
  315. t.setMarital_Status(rs1.getString("Marital_Status").charAt(0));
  316. t.setAddress(rs1.getString("Address"));
  317. t.setBank(rs1.getString("Bank"));
  318. t.setIBAN(rs1.getString("IBAN"));
  319. t.setActive(rs1.getString("Active").charAt(0));
  320. t.setProf_Title(rs1.getString("Prof_Title"));
  321. t.setTelephone(rs1.getString("Telephone"));
  322.  
  323. }
  324.  
  325. return t;
  326.  
  327. }
  328.  
  329. /*returns Admin object*/
  330. public Admin getAdmin(int id) throws SQLException {
  331.  
  332. Admin a = new Admin();
  333.  
  334. /* Select active Teachers */
  335. String selectAdmin = new String(
  336. "SELECT * FROM Admin_Stuff WHERE Id_Stuff='"+id+"'");
  337.  
  338. Statement stmt1 = connection.con.createStatement();
  339. ResultSet rs1 = stmt1.executeQuery(selectAdmin);
  340.  
  341. while(rs1.next()) {
  342.  
  343. a.setId(rs1.getInt("Id_Stuff"));
  344. a.setFirstName(rs1.getString("FirstName"));
  345. a.setLastName(rs1.getString("LastName"));
  346. a.setFatherName(rs1.getString("FatherName"));
  347. a.setGender(rs1.getString("Gender").charAt(0));
  348. a.setDateOfBirth(rs1.getString("DateOfBirth"));
  349. a.setAMKA(rs1.getString("AMKA"));
  350. a.setAFM(rs1.getString("AFM"));
  351. a.setDepartment(rs1.getString("Department"));
  352. a.setMarital_Status(rs1.getString("Marital_Status").charAt(0));
  353. a.setAddress(rs1.getString("Address"));
  354. a.setBank(rs1.getString("Bank"));
  355. a.setIBAN(rs1.getString("IBAN"));
  356. a.setActive(rs1.getString("Active").charAt(0));
  357. a.setAdmin_Title(rs1.getString("Admin_Title"));
  358. a.setDuty(rs1.getString("Duty"));
  359. a.setTelephone(rs1.getString("Telephone"));
  360.  
  361. }
  362.  
  363. return a;
  364.  
  365. }
  366.  
  367. /* Gets the most recent object_id*/
  368. public int getLatestId(String table, String idName) throws SQLException {
  369. int latest=0;
  370. String selectTable = new String(
  371. "SELECT max("+idName+") FROM "+table);
  372.  
  373. Statement stmt1 = connection.con.createStatement();
  374. ResultSet rs1 = stmt1.executeQuery(selectTable);
  375.  
  376. while(rs1.next()) {
  377. latest = rs1.getInt(1);
  378. }
  379.  
  380. return latest;
  381. }
  382.  
  383. public void updateStuff () {
  384.  
  385.  
  386.  
  387. }
  388.  
  389. /* Marks Contract to be terminated by updateContract() */
  390. // Stuff id goes here v as argument
  391. public void terminateContract(int id) throws SQLException {
  392.  
  393. /* Get to the end of the month */
  394. DBCalendar adate = new DBCalendar();
  395. adate.getDBtime().set(DBDate.getDBtime().get(Calendar.YEAR), (DBDate.getDBtime().get(Calendar.MONTH)+1), 1);
  396. adate.getDBtime().add(Calendar.DAY_OF_MONTH, -1);
  397.  
  398. String date = adate.getDBtimeString();
  399.  
  400. /* Search for ID in permanent contracts */
  401. String findPermCon = new String( // contract needs to be inEffect
  402. "UPDATE Contract_Inf SET Termination='"+date+"' WHERE id_Stuff='"+id+"' AND In_Effect='1'");
  403.  
  404. Statement stmt1 = connection.con.createStatement();
  405. stmt1.executeUpdate(findPermCon);
  406.  
  407.  
  408. /* Search for ID in temporary contracts */
  409. String findTempCon = new String( // contract needs to be inEffect
  410. "UPDATE Contract_Fin SET Termination='"+date+"' WHERE id_Stuff='"+id+"' AND In_Effect='1'");
  411.  
  412. Statement stmt2 = connection.con.createStatement();
  413. stmt2.executeUpdate(findTempCon);
  414. }
  415.  
  416.  
  417. /* activates / deactivates contracts */
  418. public void updateContracts(String date) throws ParseException, SQLException {
  419.  
  420. DBCalendar cal = new DBCalendar();
  421. cal.setDBtime(date);
  422.  
  423. /* DEACTIVATION */
  424. /* Search in temporary contracts to deactivate */
  425. String deTempCon = new String(
  426. "UPDATE Contract_Fin SET Termination='"+date+"', In_Effect='0' "+
  427. "WHERE Date_End='"+date+"' OR Termination='"+date+"'");
  428.  
  429.  
  430. Statement stmt1 = connection.con.createStatement();
  431. stmt1.executeUpdate(deTempCon);
  432.  
  433. /* Search in permanent contracts to deactivate */
  434. String dePermaCon = new String(
  435. "UPDATE Contract_Inf SET In_Effect='0' WHERE Termination='"+date+"'");
  436.  
  437. stmt1.executeUpdate(dePermaCon);
  438.  
  439. /* ACTIVATION */
  440. /* Search in temporary contracts to activate */
  441. String aTempCon = new String(
  442. "UPDATE Contract_Fin SET In_Effect='1' WHERE Date_Begin='"+date+"' AND In_Effect='0'");
  443.  
  444. stmt1.executeUpdate(aTempCon);
  445.  
  446. /* Search in permanent contracts to activate */
  447. String aPermaCon = new String(
  448. "UPDATE Contract_Inf SET In_Effect='1' WHERE Date_Begin='"+date+"' AND In_Effect='0'");
  449.  
  450. stmt1.executeUpdate(aPermaCon);
  451.  
  452. }
  453.  
  454.  
  455. /* PayStuff */
  456. public void payEmployees(String date) throws SQLException, ParseException {
  457.  
  458. //=============================================================================================================
  459. /* Select active Teachers */
  460. String selectTeacher = new String(
  461. "SELECT * FROM Teaching_Stuff WHERE Active='1'");
  462.  
  463. Statement stmt1 = connection.con.createStatement();
  464. ResultSet rs1 = stmt1.executeQuery(selectTeacher);
  465.  
  466. while (rs1.next()) {
  467.  
  468.  
  469. int teach_id = rs1.getInt("id_Stuff");
  470.  
  471. /* Search for ID in permanent contracts */
  472. String findPermCon = new String( // contract needs to be inEffect
  473. "SELECT * FROM Contract_Inf WHERE Id_Stuff='"+Integer.toString(teach_id)+"' AND In_Effect='1'");
  474.  
  475. Statement stmt2 = connection.con.createStatement();
  476. ResultSet rs2 = stmt2.executeQuery(findPermCon);
  477.  
  478. // if true teach is permanent stuff
  479. if(rs2.next()) {
  480.  
  481. int member_count=0;
  482. float YearsOfService=0;
  483. DBCalendar Today = new DBCalendar();
  484. Today.setDBtime(date);
  485.  
  486. // Count family members ---------------------------------------------------------
  487. String findMember = new String(
  488. "SELECT * FROM Family_Member WHERE Id_Stuff='"+teach_id+"'");
  489.  
  490. Statement stmt3 = connection.con.createStatement();
  491. ResultSet rs3 = stmt3.executeQuery(findMember);
  492.  
  493. while(rs3.next()) {
  494.  
  495. // check if member eligible for benefits
  496. String rltn = rs3.getString("Relation");
  497.  
  498. if(rltn.charAt(0)=='s') {
  499. member_count++;
  500.  
  501. } else {
  502.  
  503. String dob = rs3.getString("DateOfBirth");
  504.  
  505. // check if member eligible for benefits
  506. if(Today.getRemainder(dob) < 18) {
  507. member_count++;
  508. }
  509. }
  510. }
  511. // Count Work experience--------------------------------------------------------
  512.  
  513. String Date_Begin = rs2.getString("Date_Begin");
  514.  
  515. YearsOfService = Today.getRemainder(Date_Begin);
  516.  
  517. // Calculate current salary w8 benefits
  518. float expBoostWage = GValues.getBasic_Teacher_Wage();
  519. while(YearsOfService > 1 ) { // for first year no rise, sorry
  520. expBoostWage = expBoostWage*GValues.getTeacher_Rise_Percentage();
  521. }
  522.  
  523. // Calculate final Salary--------------------------------------------------------
  524. float salary = expBoostWage + ((GValues.getBasic_Teacher_Wage()*GValues.getMember_Percentage())*member_count)
  525. + GValues.getReasearch_Bonus();
  526.  
  527. Payment check = new Payment((getLatestId("Payment","id_Payment")+1),teach_id,date,"Μηνιάτικο",salary,
  528. GValues.getBasic_Teacher_Wage(),((GValues.getBasic_Teacher_Wage()*GValues.getMember_Percentage())*member_count),
  529. GValues.getReasearch_Bonus(),"Επίδομα Έρευνας");
  530.  
  531. //finally insert check to DB ...
  532. check.insertToTable();
  533.  
  534. }
  535. //---------------------------------------------------------------------------------------
  536.  
  537.  
  538. /* Search for ID in temporary contracts */
  539. String findTempCon = new String(
  540. "SELECT * FROM Contract_Fin WHERE Id_Stuff='"+teach_id+"' AND In_Effect='1'");
  541.  
  542.  
  543. Statement stmt3 = connection.con.createStatement();
  544. ResultSet rs3 = stmt3.executeQuery(findTempCon);
  545.  
  546. // if true teach is temporary stuff
  547. if(rs3.next()) {
  548.  
  549. int member_count=0;
  550. DBCalendar Today = new DBCalendar();
  551. Today.setDBtime(date);
  552.  
  553. // Count family members -------------------------------------------------------------
  554. String findMember = new String(
  555. "SELECT * FROM Family_Member WHERE Id_Stuff='"+teach_id+"'");
  556.  
  557. Statement stmt4 = connection.con.createStatement();
  558. ResultSet rs4 = stmt4.executeQuery(findMember);
  559.  
  560. while(rs4.next()) {
  561.  
  562. // check if member eligible for benefits
  563. String rltn = rs4.getString("Relation");
  564.  
  565. if(rltn.charAt(0)=='s') {
  566. member_count++;
  567.  
  568. } else {
  569.  
  570. String dob = rs4.getString("DateOfBirth");
  571.  
  572. // check if member eligible for benefits
  573. if(Today.getRemainder(dob) < 18) {
  574. member_count++;
  575. }
  576. }
  577. }
  578. // Calculate Final salary ----------------------------------------
  579. float salary = GValues.getBasic_Temp_Teacher_Wage() + ((GValues.getBasic_Temp_Teacher_Wage()*
  580. GValues.getMember_Percentage())*member_count) + GValues.getLibrary_Bonus();
  581.  
  582. Payment check = new Payment((getLatestId("Payment","id_Payment")+1),teach_id,date,"Μηνιάτικο",salary,
  583. GValues.getBasic_Temp_Teacher_Wage(),((GValues.getBasic_Temp_Teacher_Wage()
  584. *GValues.getMember_Percentage())*member_count),
  585. GValues.getLibrary_Bonus(),"Επίδομα Βιβλιοθήκης");
  586.  
  587. //finally insert check to DB ...
  588. check.insertToTable();
  589. }
  590.  
  591. }
  592.  
  593. //=============================================================================================================
  594. /* Select active Admins */
  595. String selectAdmin = new String(
  596. "SELECT * FROM Admin_Stuff WHERE Active='1'");
  597.  
  598. Statement stmt1a = connection.con.createStatement();
  599. ResultSet rs1a = stmt1a.executeQuery(selectAdmin);
  600.  
  601. while (rs1a.next()) {
  602.  
  603.  
  604. int admin_id = rs1a.getInt("id_Stuff");
  605.  
  606. /* Search for ID in permanent contracts */
  607. String findPermCon = new String( // contract needs to be inEffect
  608. "SELECT * FROM Contract_Inf WHERE Id_Stuff='"+Integer.toString(admin_id)+"' AND In_Effect='1'");
  609.  
  610. Statement stmt2a = connection.con.createStatement();
  611. ResultSet rs2a = stmt2a.executeQuery(findPermCon);
  612.  
  613. // if true teach is permanent stuff
  614. if(rs2a.next()) {
  615.  
  616. int member_count=0;
  617. float YearsOfService=0;
  618. DBCalendar Today = new DBCalendar();
  619. Today.setDBtime(date);
  620.  
  621. // Count family members ---------------------------------------------------------
  622. String findMember = new String(
  623. "SELECT * FROM Family_Member WHERE Id_Stuff='"+admin_id+"'");
  624.  
  625. Statement stmt3a = connection.con.createStatement();
  626. ResultSet rs3a = stmt3a.executeQuery(findMember);
  627.  
  628. while(rs3a.next()) {
  629.  
  630. // check if member eligible for benefits
  631. String rltn = rs3a.getString("Relation");
  632.  
  633. if(rltn.charAt(0)=='s') {
  634. member_count++;
  635.  
  636. } else {
  637.  
  638. String dob = rs3a.getString("DateOfBirth");
  639.  
  640. // check if member eligible for benefits
  641. if(Today.getRemainder(dob) < 18) {
  642. member_count++;
  643. }
  644. }
  645. }
  646. // Count Work experience--------------------------------------------------------
  647.  
  648. String Date_Begin = rs2a.getString("Date_Begin");
  649.  
  650. YearsOfService = Today.getRemainder(Date_Begin);
  651.  
  652. // Calculate current salary w8 benefits
  653. float expBoostWage = GValues.getBasic_Admin_Wage();
  654. while(YearsOfService > 1 ) { // for first year no rise, sorry
  655. expBoostWage = expBoostWage*GValues.getAdmin_Rise_Percentage();
  656. }
  657.  
  658. // Calculate final Salary--------------------------------------------------------
  659. float salary = expBoostWage + ((GValues.getBasic_Admin_Wage()*GValues.getMember_Percentage())*member_count);
  660.  
  661. Payment check = new Payment((getLatestId("Payment","id_Payment")+1),admin_id,date,"Μηνιάτικο",salary,
  662. GValues.getBasic_Admin_Wage(),((GValues.getBasic_Admin_Wage()*GValues.getMember_Percentage())*member_count),
  663. 0f,null);
  664.  
  665. //finally insert check to DB ...
  666. check.insertToTable();
  667.  
  668. }
  669. //---------------------------------------------------------------------------------------
  670.  
  671.  
  672. /* Search for ID in temporary contracts */
  673. String findTempCon = new String(
  674. "SELECT * FROM Contract_Fin WHERE Id_Stuff='"+admin_id+"' AND In_Effect='1'");
  675.  
  676.  
  677. Statement stmt3a = connection.con.createStatement();
  678. ResultSet rs3a = stmt3a.executeQuery(findTempCon);
  679.  
  680. // if true teach is temporary stuff
  681. if(rs3a.next()) {
  682.  
  683. int member_count=0;
  684. DBCalendar Today = new DBCalendar();
  685. Today.setDBtime(date);
  686.  
  687. // Count family members -------------------------------------------------------------
  688. String findMember = new String(
  689. "SELECT * FROM Family_Member WHERE Id_Stuff='"+admin_id+"'");
  690.  
  691. Statement stmt4a = connection.con.createStatement();
  692. ResultSet rs4a = stmt4a.executeQuery(findMember);
  693.  
  694. while(rs4a.next()) {
  695.  
  696. // check if member eligible for benefits
  697. String rltn = rs4a.getString("Relation");
  698.  
  699. if(rltn.charAt(0)=='s') {
  700. member_count++;
  701.  
  702. } else {
  703.  
  704. String dob = rs4a.getString("DateOfBirth");
  705.  
  706. // check if member eligible for benefits
  707. if(Today.getRemainder(dob) < 18) {
  708. member_count++;
  709. }
  710. }
  711. }
  712. // Calculate Final salary ----------------------------------------
  713. float salary = GValues.getBasic_Temp_Admin_Wage() + ((GValues.getBasic_Temp_Admin_Wage()*
  714. GValues.getMember_Percentage())*member_count);
  715.  
  716. Payment check = new Payment((getLatestId("Payment","id_Payment")+1),admin_id,date,"Μηνιάτικο",salary,
  717. GValues.getBasic_Temp_Admin_Wage(),((GValues.getBasic_Temp_Admin_Wage()
  718. *GValues.getMember_Percentage())*member_count),0f,null);
  719.  
  720. //finally insert check to DB ...
  721. check.insertToTable();
  722. }
  723.  
  724. }
  725.  
  726. }
  727.  
  728.  
  729. //================== QESTIONS ===============================
  730.  
  731. // 1. Payment period Summary
  732.  
  733. public ArrayList<Payment> viewPayments(String from, String until, boolean teach, boolean perma) throws SQLException, ParseException {
  734.  
  735. ArrayList<Payment> res = new ArrayList<Payment>();
  736.  
  737. if(teach) { // true for teach, false for Admin
  738.  
  739. /* Select Teachers */
  740. String selectTeacher = new String(
  741. "SELECT * FROM Teaching_Stuff");
  742.  
  743. Statement stmt1 = connection.con.createStatement();
  744. ResultSet rs1 = stmt1.executeQuery(selectTeacher);
  745.  
  746. while (rs1.next()) {
  747.  
  748. if(perma) { // true for permanent, false for temporary
  749. int teach_id = rs1.getInt("id_Stuff");
  750.  
  751. /* Search for ID in permanent contracts */
  752. String findPermCon = new String(
  753. "SELECT * FROM Contract_Inf WHERE Id_Stuff='"+Integer.toString(teach_id)+"'");
  754.  
  755. Statement stmt2 = connection.con.createStatement();
  756. ResultSet rs2 = stmt2.executeQuery(findPermCon);
  757.  
  758. while (rs2.next()) {
  759.  
  760. // ------------- Date checks in case of change of permanence ---------
  761. String from2 = from;
  762. String until2 = until;
  763.  
  764. DBCalendar start = new DBCalendar();
  765. start.setDBtime(rs2.getString("Date_Begin"));
  766. DBCalendar from1 = new DBCalendar();
  767. from1.setDBtime(from);
  768. if(start.getDBtime().after(from1)) {
  769. from2 = start.getDBtimeString();
  770. }
  771.  
  772. if(rs2.getString("In_Effect").charAt(0)=='0') {
  773.  
  774. DBCalendar end = new DBCalendar();
  775. if(!(rs2.getString("Termination")==null)) { // !!!!!!!!!
  776. end.setDBtime(rs2.getString("Termination"));
  777. }
  778. DBCalendar until1 = new DBCalendar();
  779. until1.setDBtime(until);
  780. if(end.getDBtime().before(until1)) {
  781. until2 = end.getDBtimeString();
  782. }
  783. }
  784.  
  785. /* Get payments from DB */
  786. String findPayment = new String(
  787. "SELECT * FROM Payment WHERE Id_Stuff='"+Integer.toString(teach_id)+
  788. "' AND DateOfPay BETWEEN '"+from2+"' AND '"+until2+"'");
  789.  
  790. Statement stmt3 = connection.con.createStatement();
  791. ResultSet rs3 = stmt3.executeQuery(findPayment);
  792.  
  793. while(rs3.next()) {
  794.  
  795. Payment p = new Payment(rs3.getInt("id_Payment"), rs3.getInt("id_Stuff"),
  796. rs3.getString("TypeOfPay"), rs3.getString("DateOfPay"), rs3.getFloat("ValueOfPay"),
  797. rs3.getFloat("Basic_Wage"), rs3.getFloat("Family_bonus"), rs3.getFloat("Benefits"),
  798. rs3.getString("Comment"));
  799.  
  800. res.add(p);
  801.  
  802. }
  803.  
  804. }
  805.  
  806. } else /* Not permanent stuff */ {
  807.  
  808. int teach_id = rs1.getInt("id_Stuff");
  809.  
  810. /* Search for ID in temporary contracts */
  811. String findTempCon = new String(
  812. "SELECT * FROM Contract_Fin WHERE Id_Stuff='"+teach_id+"'");
  813.  
  814.  
  815. Statement stmt2 = connection.con.createStatement();
  816. ResultSet rs2 = stmt2.executeQuery(findTempCon);
  817.  
  818. // if true teach is temporary stuff
  819. while (rs2.next()) {
  820.  
  821. // ------------- Date checks in case of change of permanence ---------
  822. String from2 = from;
  823. String until2 = until;
  824.  
  825. DBCalendar start = new DBCalendar();
  826. start.setDBtime(rs2.getString("Date_Begin"));
  827. DBCalendar from1 = new DBCalendar();
  828. from1.setDBtime(from);
  829. if(start.getDBtime().after(from1)) {
  830. from2 = start.getDBtimeString();
  831. }
  832.  
  833. if(rs2.getString("In_Effect").charAt(0)=='0') {
  834.  
  835. DBCalendar end = new DBCalendar();
  836. if(!(rs2.getString("Termination")==null)) { // !!!!!!!!!
  837. end.setDBtime(rs2.getString("Termination"));
  838. }
  839. DBCalendar until1 = new DBCalendar();
  840. until1.setDBtime(until);
  841. if(end.getDBtime().before(until1)) {
  842. until2 = end.getDBtimeString();
  843. }
  844. }
  845.  
  846. /* Get payments from DB */
  847. String findPayment = new String(
  848. "SELECT * FROM Payment WHERE Id_Stuff='"+Integer.toString(teach_id)+
  849. "' AND DateOfPay BETWEEN '"+from2+"' AND '"+until2+"'");
  850.  
  851. Statement stmt3 = connection.con.createStatement();
  852. ResultSet rs3 = stmt3.executeQuery(findPayment);
  853.  
  854. while(rs3.next()) {
  855.  
  856. Payment p = new Payment(rs3.getInt("id_Payment"), rs3.getInt("id_Stuff"),
  857. rs3.getString("TypeOfPay"), rs3.getString("DateOfPay"), rs3.getFloat("ValueOfPay"),
  858. rs3.getFloat("Basic_Wage"), rs3.getFloat("Family_bonus"), rs3.getFloat("Benefits"),
  859. rs3.getString("Comment"));
  860.  
  861. res.add(p);
  862.  
  863. }
  864.  
  865. }
  866.  
  867.  
  868. }
  869. }
  870. } else { // 1 for teach, 0 for Administrator
  871.  
  872. /* Select Administrator */
  873. String selectAdmin = new String(
  874. "SELECT * FROM Admin_Stuff");
  875.  
  876. Statement stmt1 = connection.con.createStatement();
  877. ResultSet rs1 = stmt1.executeQuery(selectAdmin);
  878.  
  879. while (rs1.next()) {
  880.  
  881. if(perma) { // 1 for permanent , 0 for temporary
  882. int admin_id = rs1.getInt("id_Stuff");
  883.  
  884. /* Search for ID in permanent contracts */
  885. String findPermCon = new String(
  886. "SELECT * FROM Contract_Inf WHERE Id_Stuff='"+Integer.toString(admin_id)+"'");
  887.  
  888. Statement stmt2 = connection.con.createStatement();
  889. ResultSet rs2 = stmt2.executeQuery(findPermCon);
  890.  
  891. while (rs2.next()) {
  892.  
  893. // ------------- Date checks in case of change of permanence ---------
  894. String from2 = from;
  895. String until2 = until;
  896.  
  897. DBCalendar start = new DBCalendar();
  898. start.setDBtime(rs2.getString("Date_Begin"));
  899. DBCalendar from1 = new DBCalendar();
  900. from1.setDBtime(from);
  901. if(start.getDBtime().after(from1)) {
  902. from2 = start.getDBtimeString();
  903. }
  904.  
  905. if(rs2.getString("In_Effect").charAt(0)=='0') {
  906.  
  907. DBCalendar end = new DBCalendar();
  908. if(!(rs2.getString("Termination")==null)) { // !!!!!!!!!
  909. end.setDBtime(rs2.getString("Termination"));
  910. }
  911. DBCalendar until1 = new DBCalendar();
  912. until1.setDBtime(until);
  913. if(end.getDBtime().before(until1)) {
  914. until2 = end.getDBtimeString();
  915. }
  916. }
  917.  
  918. /* Get payments from DB */
  919. String findPayment = new String(
  920. "SELECT * FROM Payment WHERE Id_Stuff='"+Integer.toString(admin_id)+
  921. "' AND DateOfPay BETWEEN '"+from2+"' AND '"+until2+"'");
  922.  
  923. Statement stmt3 = connection.con.createStatement();
  924. ResultSet rs3 = stmt3.executeQuery(findPayment);
  925.  
  926. while(rs3.next()) {
  927.  
  928. Payment p = new Payment(rs3.getInt("id_Payment"), rs3.getInt("id_Stuff"),
  929. rs3.getString("TypeOfPay"), rs3.getString("DateOfPay"), rs3.getFloat("ValueOfPay"),
  930. rs3.getFloat("Basic_Wage"), rs3.getFloat("Family_bonus"), rs3.getFloat("Benefits"),
  931. rs3.getString("Comment"));
  932.  
  933. res.add(p);
  934.  
  935. }
  936.  
  937. }
  938.  
  939. } else /* Not permanent stuff */ {
  940.  
  941. int admin_id = rs1.getInt("id_Stuff");
  942.  
  943. /* Search for ID in temporary contracts */
  944. String findTempCon = new String(
  945. "SELECT * FROM Contract_Fin WHERE Id_Stuff='"+admin_id+"'");
  946.  
  947.  
  948. Statement stmt2 = connection.con.createStatement();
  949. ResultSet rs2 = stmt2.executeQuery(findTempCon);
  950.  
  951. // if true teach is temporary stuff
  952. while (rs2.next()) {
  953.  
  954. // ------------- Date checks in case of change of permanence ---------
  955. String from2 = from;
  956. String until2 = until;
  957.  
  958. DBCalendar start = new DBCalendar();
  959. start.setDBtime(rs2.getString("Date_Begin"));
  960. DBCalendar from1 = new DBCalendar();
  961. from1.setDBtime(from);
  962. if(start.getDBtime().after(from1)) {
  963. from2 = start.getDBtimeString();
  964. }
  965.  
  966. if(rs2.getString("In_Effect").charAt(0)=='0') {
  967.  
  968. DBCalendar end = new DBCalendar();
  969. if(!(rs2.getString("Termination")==null)) { // !!!!!!!!!
  970. end.setDBtime(rs2.getString("Termination"));
  971. }
  972. DBCalendar until1 = new DBCalendar();
  973. until1.setDBtime(until);
  974. if(end.getDBtime().before(until1)) {
  975. until2 = end.getDBtimeString();
  976. }
  977. }
  978.  
  979. /* Get payments from DB */
  980. String findPayment = new String(
  981. "SELECT * FROM Payment WHERE Id_Stuff='"+Integer.toString(admin_id)+
  982. "' AND DateOfPay BETWEEN '"+from2+"' AND '"+until2+"'");
  983.  
  984. Statement stmt3 = connection.con.createStatement();
  985. ResultSet rs3 = stmt3.executeQuery(findPayment);
  986.  
  987. while(rs3.next()) {
  988.  
  989. Payment p = new Payment(rs3.getInt("id_Payment"), rs3.getInt("id_Stuff"),
  990. rs3.getString("TypeOfPay"), rs3.getString("DateOfPay"), rs3.getFloat("ValueOfPay"),
  991. rs3.getFloat("Basic_Wage"), rs3.getFloat("Family_bonus"), rs3.getFloat("Benefits"),
  992. rs3.getString("Comment"));
  993.  
  994. res.add(p);
  995.  
  996. }
  997. }
  998. }
  999. }
  1000. }
  1001.  
  1002. return res;
  1003. }
  1004.  
  1005.  
  1006. //2. MAX - MIN - Mean Salary per Category
  1007.  
  1008. public ArrayList<Payment> getSalaryStats(boolean teach, boolean perma) throws SQLException, ParseException {
  1009.  
  1010. ArrayList<Payment> res = new ArrayList<Payment>();
  1011. Payment max = new Payment(-1,-1,"0","0",0f,0f,0f,0f,"0");
  1012. Payment min= new Payment(-1,-1,"0","0",20000000f,0f,0f,0f,"0");
  1013. Payment mean= new Payment(-1,-1,"0","0",0f,0f,0f,0f,"0");
  1014. float meanv=0;
  1015. int i=0;
  1016.  
  1017. ArrayList<Payment> ref = viewPayments("1917-10-25",DBDate.getDBtimeString(),teach,perma);
  1018.  
  1019. if((ref!=null)&&(!ref.isEmpty())) {
  1020. max = ref.get(0);
  1021. min = ref.get(0);
  1022. }
  1023.  
  1024.  
  1025. for(Payment p : ref ) {
  1026.  
  1027. if(p.getValueOfPay()>max.getValueOfPay()) {
  1028. max.setValueOfPay(p.getValueOfPay());
  1029. max.setId_Stuff(p.getId_Stuff());
  1030. }
  1031. if(p.getValueOfPay()<min.getValueOfPay()) {
  1032. min.setValueOfPay(p.getValueOfPay());
  1033. min.setId_Stuff(p.getId_Stuff());
  1034. }
  1035. meanv += p.getValueOfPay();
  1036. i++;
  1037. }
  1038.  
  1039. mean.setValueOfPay(meanv/(float)i);
  1040.  
  1041. res.add(max); // first max
  1042. res.add(min); // second min
  1043. res.add(mean); // third mean
  1044.  
  1045. return res;
  1046. }
  1047.  
  1048.  
  1049. // 3.
  1050.  
  1051. // 4. ===============================================================================================================
  1052.  
  1053. // A
  1054.  
  1055. /* Returns Staff object to be used with viewStuffToString() */
  1056. public Stuff findStuff(int id) throws SQLException { // TO DO
  1057. Stuff res = null;
  1058.  
  1059. /* Select Teachers */
  1060. String selectTeacher = new String(
  1061. "SELECT * FROM Teaching_Stuff WHERE id_Stuff='"+id+"'");
  1062.  
  1063. Statement stmt1 = connection.con.createStatement();
  1064. ResultSet rs1 = stmt1.executeQuery(selectTeacher);
  1065.  
  1066. // Valid if the bastard is a teacher
  1067. while (rs1.next()) {
  1068.  
  1069. Teacher teach = new Teacher(rs1.getInt("id_Stuff"),rs1.getString("FirstName"),rs1.getString("LastName"),
  1070. rs1.getString("FatherName"),rs1.getString("Gender").charAt(0),rs1.getString("DateOfBirth"),
  1071. rs1.getString("AMKA"),rs1.getString("AFM"),rs1.getString("Department"),
  1072. rs1.getString("Marital_Status").charAt(0),rs1.getString("Address"),rs1.getString("Bank"),
  1073. rs1.getString("IBAN"),rs1.getString("Active").charAt(0),
  1074. rs1.getString("Prof_Title"),rs1.getString("Telephone"));
  1075.  
  1076. res = teach;
  1077. }
  1078.  
  1079. /* Select Admin */
  1080. String selectAdmin = new String(
  1081. "SELECT * FROM Admin_Stuff WHERE id_Stuff='"+id+"'");
  1082.  
  1083. Statement stmt2 = connection.con.createStatement();
  1084. ResultSet rs2 = stmt2.executeQuery(selectAdmin);
  1085.  
  1086. // Valid if the bastard is an Admin
  1087. while (rs2.next()) {
  1088.  
  1089. Admin admin = new Admin(rs1.getInt("id_Stuff"),rs1.getString("FirstName"),rs1.getString("LastName"),
  1090. rs1.getString("FatherName"),rs1.getString("Gender").charAt(0),rs1.getString("DateOfBirth"),
  1091. rs1.getString("AMKA"),rs1.getString("AFM"),rs1.getString("Department"),
  1092. rs1.getString("Marital_Status").charAt(0),rs1.getString("Address"),rs1.getString("Bank"),
  1093. rs1.getString("IBAN"),rs1.getString("Active").charAt(0),
  1094. rs1.getString("Admin_Title"),rs1.getString("Duty"),rs1.getString("Telephone"));
  1095.  
  1096. res = admin;
  1097. }
  1098.  
  1099.  
  1100. return res;
  1101. }
  1102.  
  1103. // B
  1104.  
  1105. /* return LAST !!! Contract to be used with viewContractToString() */
  1106. public Contract findContract(int id) throws SQLException {
  1107. Contract cont = null;
  1108.  
  1109. // Select from permanent contracts
  1110. /* Search for ID in permanent contracts */
  1111. String findPermCon = new String(
  1112. "SELECT * FROM Contract_Inf WHERE Id_Stuff='"+Integer.toString(id)+"'");
  1113.  
  1114. Statement stmt1 = connection.con.createStatement();
  1115. ResultSet rs1 = stmt1.executeQuery(findPermCon);
  1116.  
  1117. while (rs1.next()) {
  1118.  
  1119. ContrInf c_i = new ContrInf(rs1.getInt("id_Contract"), rs1.getString("Date_Begin"),
  1120. rs1.getInt("Hours_Week"), rs1.getInt("Days_Leave"),
  1121. rs1.getString("In_Effect").charAt(0), rs1.getInt("id_Stuff"));
  1122.  
  1123. if((!(rs1.getString("Termination")==null)))
  1124. c_i.setTermination(rs1.getString("Termination"));
  1125.  
  1126. cont = c_i;
  1127.  
  1128. }
  1129.  
  1130. // Select from temporary contracts
  1131. /* Search for ID in temporary contracts */
  1132. String findTempCon = new String(
  1133. "SELECT * FROM Contract_Fin WHERE Id_Stuff='"+Integer.toString(id)+"'");
  1134.  
  1135. Statement stmt2 = connection.con.createStatement();
  1136. ResultSet rs2 = stmt2.executeQuery(findTempCon);
  1137.  
  1138. while (rs2.next()) {
  1139.  
  1140. ContrFin c_f = new ContrFin(rs1.getInt("id_Contract"), rs1.getString("Date_Begin"),
  1141. rs1.getInt("Hours_Week"), rs1.getInt("Days_Leave"),
  1142. rs1.getString("In_Effect").charAt(0),rs1.getString("Date_End"), rs1.getInt("id_Stuff"));
  1143.  
  1144. if((!(rs1.getString("Termination")==null)))
  1145. c_f.setTermination(rs1.getString("Termination"));
  1146.  
  1147. cont = c_f;
  1148.  
  1149. }
  1150.  
  1151. return cont;
  1152. }
  1153.  
  1154. // C
  1155.  
  1156. /* return an ArrayList<Payment> containing all payments done so far*/
  1157. public ArrayList<Payment> getStuffPaymentsHistory(int id) throws SQLException {
  1158. ArrayList<Payment> res = new ArrayList<Payment>();
  1159.  
  1160. String findPay = new String(
  1161. "SELECT * FROM Payment WHERE id_Stuff='"+Integer.toString(id)+"'");
  1162.  
  1163. Statement stmt = connection.con.createStatement();
  1164. ResultSet rs = stmt.executeQuery(findPay);
  1165.  
  1166. while(rs.next()) {
  1167.  
  1168.  
  1169. Payment p = new Payment(rs.getInt("id_Payment"), rs.getInt("id_Stuff"),
  1170. rs.getString("TypeOfPay"), rs.getString("DateOfPay"), rs.getFloat("ValueOfPay"),
  1171. rs.getFloat("Basic_Wage"), rs.getFloat("Family_bonus"), rs.getFloat("Benefits"),
  1172. rs.getString("Comment"));
  1173.  
  1174. res.add(p);
  1175.  
  1176. }
  1177.  
  1178. return res;
  1179. }
  1180.  
  1181. // D
  1182. /* returns stuffs relatives */
  1183. public ArrayList<Member> getStuffFamilyMembers(int id) throws SQLException {
  1184.  
  1185. ArrayList<Member> res = new ArrayList<Member>();
  1186.  
  1187. String findMember = new String(
  1188. "SELECT * FROM Family_Member WHERE Id_Stuff='"+Integer.toString(id)+"'");
  1189.  
  1190. Statement stmt = connection.con.createStatement();
  1191. ResultSet rs = stmt.executeQuery(findMember);
  1192.  
  1193. while(rs.next()) {
  1194.  
  1195. Member m = new Member(rs.getInt("id_Member"),rs.getString("First_Name"),rs.getString("LastName"),
  1196. rs.getString("Relation").charAt(0),rs.getString("Gender").charAt(0),rs.getString("DateOfBirth"),
  1197. rs.getString("AMKA"),rs.getInt("id_Stuff"));
  1198.  
  1199. res.add(m);
  1200. }
  1201.  
  1202.  
  1203. return res;
  1204. }
  1205.  
  1206. // E
  1207.  
  1208. /* returns ArrayList<Project> with projects he is currently active! */
  1209. public ArrayList<Project> getStuffProjects(int id) throws SQLException {
  1210. ArrayList<Project> res = new ArrayList<Project>();
  1211.  
  1212. String findProj = new String(
  1213. "SELECT * FROM Participates_In WHERE Id_Stuff='"+Integer.toString(id)+"'");
  1214.  
  1215. Statement stmt1 = connection.con.createStatement();
  1216. ResultSet rs1 = stmt1.executeQuery(findProj);
  1217.  
  1218. while(rs1.next()) {
  1219.  
  1220. String findProj2 = new String(
  1221. "SELECT * FROM Project WHERE Id_Project='"+rs1.getInt("id_Project")+"'");
  1222.  
  1223. Statement stmt2 = connection.con.createStatement();
  1224. ResultSet rs2 = stmt2.executeQuery(findProj2);
  1225.  
  1226. while(rs2.next()) {
  1227.  
  1228. Project p = new Project(rs2.getInt("id_Project"),rs2.getString("Name"));
  1229.  
  1230. res.add(p);
  1231. }
  1232. }
  1233.  
  1234. return res;
  1235. }
  1236.  
  1237. // F
  1238. /* returns ArrayList<Degree> stuff*/
  1239. public ArrayList<Degree> getStuffDegrees(int id) throws SQLException {
  1240. ArrayList<Degree> res = new ArrayList<Degree>();
  1241.  
  1242. String findDegree = new String(
  1243. "SELECT * FROM Edu_Degree WHERE Id_Stuff='"+Integer.toString(id)+"'");
  1244.  
  1245. Statement stmt = connection.con.createStatement();
  1246. ResultSet rs = stmt.executeQuery(findDegree);
  1247.  
  1248. while(rs.next()) {
  1249.  
  1250. Degree d = new Degree(rs.getInt("id_Degree"),rs.getString("Title"),rs.getInt("Year"),
  1251. rs.getFloat("Mark"),rs.getString("University"),rs.getInt("id_Stuff"));
  1252.  
  1253. res.add(d);
  1254. }
  1255.  
  1256. return res;
  1257. }
  1258.  
  1259. // G returs ArrayList<Course>
  1260. public ArrayList<Course> getStuffCourses(int id) throws SQLException {
  1261. ArrayList<Course> res = new ArrayList<Course>();
  1262.  
  1263. String findDegree = new String(
  1264. "SELECT * FROM Course WHERE Id_Stuff='"+Integer.toString(id)+"'");
  1265.  
  1266. Statement stmt = connection.con.createStatement();
  1267. ResultSet rs = stmt.executeQuery(findDegree);
  1268.  
  1269. while(rs.next()) {
  1270.  
  1271. Course c = new Course(rs.getInt("id_Course"),rs.getString("Title"),rs.getInt("id_Stuff"));
  1272.  
  1273. res.add(c);
  1274. }
  1275.  
  1276. return res;
  1277.  
  1278.  
  1279. }
  1280.  
  1281. //========================================================================================================
  1282. // returns the oldest date in appropriate DB table column
  1283. public String findOldestDate(String table, String column) throws SQLException {
  1284.  
  1285. String date=null;
  1286.  
  1287. /* Get value from DB */
  1288. String findDate = new String(
  1289. "SELECT * FROM "+table+" WHERE "+column+" <= ALL (SELECT MIN("+column+") FROM "+table+")");
  1290.  
  1291. Statement stmt3 = connection.con.createStatement();
  1292. ResultSet rs3 = stmt3.executeQuery(findDate);
  1293.  
  1294. while(rs3.next()) {
  1295. date = rs3.getString(column);
  1296. }
  1297. return date;
  1298. }
  1299.  
  1300.  
  1301. //5. Yearly Sum of Pays per Stuff Category !!SINCE THE CREATION OF DB
  1302. public ArrayList<String> getYearlySum (boolean teach, boolean perma) throws SQLException, ParseException {
  1303.  
  1304. ArrayList<String> res = new ArrayList<String>();
  1305. DBCalendar dbStart = new DBCalendar();
  1306. dbStart.setDBtime(findOldestDate("Payment","DateOfPay"));
  1307.  
  1308. while(!(dbStart.getDBtime().after(DBDate.getDBtime()))) {
  1309.  
  1310. int year = dbStart.getDBtime().get(Calendar.YEAR);
  1311.  
  1312. ArrayList<Payment> ref = viewPayments(String.valueOf(year)+"-01-01",
  1313. String.valueOf(year)+"-12-31",teach,perma);
  1314.  
  1315. double SalarySum=0;
  1316.  
  1317. for(Payment p : ref) {
  1318.  
  1319. SalarySum += p.getValueOfPay();
  1320. }
  1321. // format double to string
  1322. DecimalFormat decimalFormat = new DecimalFormat("#,##0.00");
  1323. String numberAsString = decimalFormat.format(SalarySum);
  1324.  
  1325. String s = "\nΗ συνολική αξία των πληρωμών στην επιλεγμένη κατηγορία προσωπικού \nγια το έτος "+String.valueOf(year)+
  1326. " ανέρχεται στις "+numberAsString+" δρχ.\n";
  1327. // add to ArrayList
  1328. res.add(s);
  1329. // progress calendar variable by one year
  1330. dbStart.getDBtime().add(Calendar.YEAR,1);
  1331.  
  1332. }
  1333.  
  1334. return res;
  1335. }
  1336.  
  1337. public static void main(String[] args) {
  1338.  
  1339.  
  1340. }
  1341.  
  1342. ////////////////////////////////////////////////////////////////////////////////////////
  1343.  
  1344. public void progressDate(String date) throws ParseException, SQLException {
  1345.  
  1346. DBCalendar tmp = new DBCalendar();
  1347. tmp.setDBtime(date);
  1348.  
  1349. if(tmp.getDBtime().after(DBDate.getDBtime())) {
  1350.  
  1351. while(DBDate.getDBtime().before(tmp.getDBtime())) {
  1352.  
  1353. DBDate.getDBtime().add(Calendar.DATE,1);
  1354. updateDB(DBDate);
  1355. }
  1356. }
  1357.  
  1358. }
  1359.  
  1360. public void updateDB(DBCalendar cal) throws SQLException, ParseException {
  1361.  
  1362. int lastDay = cal.getDBtime().getActualMaximum(Calendar.DAY_OF_MONTH);
  1363. int firstDay = 1;
  1364.  
  1365. // if end of the month -> pay yo!
  1366. if(cal.getDBtime().get(Calendar.DATE)==lastDay) {
  1367.  
  1368. payEmployees(cal.getDBtimeString());
  1369. updateContracts(cal.getDBtimeString());
  1370.  
  1371. }
  1372.  
  1373. if(cal.getDBtime().get(Calendar.DATE)==firstDay) {
  1374.  
  1375. updateContracts(cal.getDBtimeString());
  1376. }
  1377.  
  1378.  
  1379. }
  1380.  
  1381.  
  1382.  
  1383. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement