· 8 years ago · Apr 24, 2018, 09:36 PM
1package travelmanagement;
2 // This class works to call the frame (loginFrame)
3public class TravelManagement {
4 public static void main(String[] args) {
5 loginFrame frame = new loginFrame();
6 }
7}
8
9//This class works to establish a connection between the system and the database
10public class mySqlConnect {
11
12 Connection con = null;
13 //connect with the database ‘travelmanagement’ on the localhost
14 public static Connection ConnectDC() {
15 try {
16 Class.forName("com.mysql.jdbc.Driver");
17 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3307/travelmanagement", "root", "0786");
18 return con;
19 } catch (Exception e) {
20 JOptionPane.showMessageDialog(null, "Connection Failed");
21 return null;
22 }
23 }
24 //connect with the database ‘groups’ on the localhost
25 public static Connection ConnectDCgroups() {
26 try {
27 Class.forName("com.mysql.jdbc.Driver");
28 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3307/groups", "root", "0786");
29 return con;
30 } catch (Exception e) {
31 JOptionPane.showMessageDialog(null, "Connection Failed");
32 return null;
33 }
34 }
35 }
36
37 //This class is the loginFrame which allows the user to access the data from the database if the login details are valid
38public class loginFrame extends JFrame implements ActionListener {
39
40 Connection con = mySqlConnect.ConnectDC();
41 PreparedStatement pst = null;
42 ResultSet rs = null;
43 JLabel heading, usernameLabel, passwordLabel, errorMsg;
44 JTextField usernameField;
45 JPasswordField passwordField;
46 JButton loginButton, exitButton;
47
48 loginFrame() {
49 this.setSize(350, 225);
50 this.setLocationRelativeTo(null);
51 this.setUndecorated(true);
52 this.setTitle("Login");
53 ImageIcon icon = new ImageIcon(getClass().getResource("logo.png"));
54 this.setIconImage(icon.getImage());
55 getContentPane().setBackground(Color.WHITE);
56
57 heading = new JLabel("Login");
58 usernameLabel = new JLabel("Username:");
59 usernameField = new JTextField(12);
60 passwordLabel = new JLabel("Password:");
61 passwordField = new JPasswordField(12);
62 loginButton = new JButton("Login");
63 loginButton.addActionListener(this);
64 exitButton = new JButton("Exit");
65 exitButton.addActionListener(this);
66 errorMsg = new JLabel("Incorrect Username or Password!");
67 errorMsg.setVisible(false);
68
69 // 1st x value, 2nd y value, 3rd width, 4th height
70 this.setLayout(null);
71 heading.setBounds(125, 5, 125, 25);
72 heading.setFont(heading.getFont().deriveFont(20f));
73 usernameLabel.setBounds(25, 50, 100, 50);
74 usernameField.setBounds(150, 60, 150, 30);
75 passwordLabel.setBounds(25, 100, 100, 50);
76 passwordField.setBounds(150, 105, 150, 30);
77 errorMsg.setBounds(75, 147, 200, 30);
78 errorMsg.setForeground(Color.red);
79 loginButton.setBounds(85, 185, 75, 30);
80 exitButton.setBounds(165, 185, 75, 30);
81
82 this.add(heading);
83 this.add(usernameLabel);
84 this.add(usernameField);
85 this.add(passwordLabel);
86 this.add(passwordField);
87 this.add(loginButton);
88 this.add(exitButton);
89 this.add(errorMsg);
90 this.setVisible(true);
91 }
92
93 public void clearFields() {
94 usernameField.setText("");
95 passwordField.setText("");
96 }
97
98 public void checkLogin() {
99 //Sql command that will be connected to the database
100 String Sql = "SELECT * from login where Username=? and Password=?";
101 try {
102 pst = con.prepareStatement(Sql);
103 pst.setString(1, usernameField.getText());
104 pst.setString(2, passwordField.getText());
105 //Execute the sql command
106 rs = pst.executeQuery();
107 //if username & password found, open mainFrame and close the loginFrame
108 if (rs.next()) {
109 mainFrame frame = new mainFrame();
110 this.dispose();
111 //otherwise display the error message and clear the fields
112 } else {
113 errorMsg.setVisible(true);
114 clearFields();
115 }
116 } catch (Exception e) {
117
118 }
119 }
120
121 @Override
122 public void actionPerformed(ActionEvent ae) {
123 //if loginButton pressed, call the checkLogin() subroutine
124 if (ae.getSource() == loginButton) {
125 checkLogin();
126 //if exitButton pressed, shut down the program and close the frame
127 } else if (ae.getSource() == exitButton) {
128 System.exit(0);
129 this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
130 }
131 }
132}
133
134//This frame is the mainframe of the program which allows the user to choose where to go next, either passportFrame or groupFrame
135public class mainFrame extends JFrame implements ActionListener {
136
137 JButton passport, group;
138
139 mainFrame() {
140 this.setSize(700, 450);
141 this.setLocationRelativeTo(null);
142 getContentPane().setBackground(Color.WHITE);
143 this.setTitle("Bukhari Travel Ltd");
144 ImageIcon icon = new ImageIcon(getClass().getResource("logo.png"));
145 this.setIconImage(icon.getImage());
146 this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
147
148 passport = new JButton(new ImageIcon(getClass().getResource("passport.png")));
149 group = new JButton(new ImageIcon(getClass().getResource("group.jpg")));
150
151 // 1st x value, 2nd y value, 3rd width, 4th height
152 this.setLayout(null);
153 passport.setBounds(25, 75, 300, 300);
154 passport.addActionListener(this);
155 group.setBounds(355, 75, 300, 300);
156 group.addActionListener(this);
157
158 this.add(passport);
159 this.add(group);
160 this.setVisible(true);
161 }
162
163 @Override
164 public void actionPerformed(ActionEvent ae) {
165 //if passport button pressed, open the passportFrame and close the mainFrame
166 if(ae.getSource() == passport) {
167 passportFrame frame = new passportFrame();
168 this.dispose();
169 //if group button pressed, open the chooseGroup Frame and close the mainFrame
170 } else if(ae.getSource() == group) {
171 chooseGroup frame = new chooseGroup();
172 this.dispose();
173 }
174 }
175}
176
177//This frame is for the passport section where all the data regarding passport groups can be seen, added, amended, or printed off
178public class passportFrame extends JFrame implements ActionListener {
179
180 JLabel heading, agentL, groupNoL, noPassportL, sendingDateL, postageBarcodeL, receivingDateL,
181 returnedL, commentsL, insertDataL;
182 JTextField agentTF, groupNoTF, noPassportTF, sendingDateTF, postageBarcodeTF, receivingDateTF, returnedTF,
183 commentsTF;
184 JButton backButton, addRow, insertButton, deleteButton, editButton, printButton;
185 JPanel mainTablePanel, insertDataPanel;
186 JTable mainTable;
187 JScrollPane scrollPane;
188 Connection con = mySqlConnect.ConnectDC();;
189 PreparedStatement pst = null;
190 ResultSet rs = null;
191
192 passportFrame() {
193 this.setSize(1366, 730);
194 this.setLocationRelativeTo(null);
195 this.getContentPane().setBackground(Color.WHITE);
196 this.setTitle("Bukhari Travel Ltd");
197 ImageIcon icon = new ImageIcon(getClass().getResource("logo.png"));
198 this.setIconImage(icon.getImage());
199 this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
200
201 heading = new JLabel("Manage Your Passports");
202 insertDataL = new JLabel("Add New Group");
203 agentL = new JLabel("Agent:");
204 groupNoL = new JLabel("Group No.:");
205 noPassportL = new JLabel("No. of Passports:");
206 sendingDateL = new JLabel("Sending Date:");
207 postageBarcodeL = new JLabel("Postage Barcode:");
208 receivingDateL = new JLabel("Receiving Date:");
209 returnedL = new JLabel("Returned:");
210 commentsL = new JLabel("Travelling Date:");
211
212 agentTF = new JTextField();
213 groupNoTF = new JTextField();
214 noPassportTF = new JTextField();
215 sendingDateTF = new JTextField();
216 postageBarcodeTF = new JTextField();
217 receivingDateTF = new JTextField();
218 returnedTF = new JTextField();
219 commentsTF = new JTextField();
220
221 insertButton = new JButton("Add");
222 insertButton.addActionListener(this);
223 editButton = new JButton("Edit");
224 editButton.addActionListener(this);
225 deleteButton = new JButton("Delete");
226 deleteButton.addActionListener(this);
227 printButton = new JButton("Print PDF");
228 printButton.addActionListener(this);
229 backButton = new JButton("Go Back");
230 backButton.addActionListener(this);
231
232 DefaultTableModel mainTableModel = new DefaultTableModel();
233 mainTable = new JTable(mainTableModel);
234 scrollPane = new JScrollPane(mainTable);
235
236 insertDataPanel = new JPanel();
237 mainTablePanel = new JPanel();
238
239 insertDataPanel.add(insertDataL);
240 insertDataPanel.add(agentL);
241 insertDataPanel.add(groupNoL);
242 insertDataPanel.add(noPassportL);
243 insertDataPanel.add(sendingDateL);
244 insertDataPanel.add(postageBarcodeL);
245 insertDataPanel.add(receivingDateL);
246 insertDataPanel.add(returnedL);
247 insertDataPanel.add(commentsL);
248 insertDataPanel.add(agentTF);
249 insertDataPanel.add(groupNoTF);
250 insertDataPanel.add(noPassportTF);
251 insertDataPanel.add(sendingDateTF);
252 insertDataPanel.add(postageBarcodeTF);
253 insertDataPanel.add(receivingDateTF);
254 insertDataPanel.add(returnedTF);
255 insertDataPanel.add(commentsTF);
256 insertDataPanel.add(insertButton);
257 insertDataPanel.add(editButton);
258 insertDataPanel.add(deleteButton);
259
260 mainTableModel.addColumn("Agent");
261 mainTableModel.addColumn("Group No.");
262 mainTableModel.addColumn("No. of Passports");
263 mainTableModel.addColumn("Sending Date");
264 mainTableModel.addColumn("Postage Barcode");
265 mainTableModel.addColumn("Receiving Date");
266 mainTableModel.addColumn("Returned");
267 mainTableModel.addColumn("Comments");
268
269 // 1st x value, 2nd y value, 3rd width, 4th height
270 heading.setBounds(575, 10, 300, 30);
271 heading.setFont(heading.getFont().deriveFont(25f));
272
273 insertDataPanel.setBounds(20, 50, 375, 375);
274 insertDataL.setBounds(120, 10, 200, 30);
275 insertDataL.setFont(insertDataL.getFont().deriveFont(20f));
276 agentL.setBounds(20, 62, 100, 15);
277 groupNoL.setBounds(20, 92, 100, 15);
278 noPassportL.setBounds(20, 122, 100, 15);
279 sendingDateL.setBounds(20, 152, 100, 15);
280 postageBarcodeL.setBounds(20, 182, 150, 15);
281 receivingDateL.setBounds(20, 212, 100, 15);
282 returnedL.setBounds(20, 242, 100, 15);
283 commentsL.setBounds(20, 272, 100, 15);
284
285 agentTF.setBounds(180, 55, 150, 25);
286 groupNoTF.setBounds(180, 85, 150, 25);
287 noPassportTF.setBounds(180, 115, 150, 25);
288 sendingDateTF.setBounds(180, 145, 150, 25);
289 postageBarcodeTF.setBounds(180, 175, 150, 25);
290 receivingDateTF.setBounds(180, 205, 150, 25);
291 returnedTF.setBounds(180, 235, 150, 25);
292 commentsTF.setBounds(180, 265, 150, 25);
293
294 insertButton.setBounds(55, 320, 75, 30);
295 editButton.setBounds(155, 320, 75, 30);
296 deleteButton.setBounds(255, 320, 75, 30);
297
298 mainTablePanel.setBounds(425, 50, 900, 500);
299 mainTable.setLayout(null);
300 mainTable.setBounds(475, 300, 1000, 500);
301 mainTable.setRowHeight(25);
302 mainTable.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
303
304 backButton.setBounds(675, 650, 100, 35);
305 printButton.setBounds(560, 650, 100, 35);
306
307 mainTablePanel.add(scrollPane);
308 scrollPane.setPreferredSize(new Dimension(900, 500));
309
310 this.add(heading);
311 this.add(insertDataPanel);
312 this.add(mainTablePanel);
313 this.add(printButton);
314 this.add(backButton);
315 insertDataPanel.setLayout(null);
316 this.setLayout(null);
317 retrieveData();
318 this.setVisible(true);
319 }
320
321 //This subroutine retrieves the data from the database where the table name is ‘passports’
322 private void retrieveData() {
323 try {
324 String sqlShowData = "SELECT * FROM `passports`";
325 pst = con.prepareStatement(sqlShowData);
326 rs = pst.executeQuery();
327 //display all the data from the database in the mainTable, in the format that is saved in the database
328 mainTable.setModel(DbUtils.resultSetToTableModel(rs));
329 } catch (Exception ex) {
330 System.out.println(ex);
331 }
332 }
333
334 //This subroutine clears the textFields
335 private void clearField() {
336 agentTF.setText("");
337 groupNoTF.setText("");
338 noPassportTF.setText("");
339 sendingDateTF.setText("");
340 postageBarcodeTF.setText("");
341 receivingDateTF.setText("");
342 returnedTF.setText("");
343 commentsTF.setText("");
344 }
345
346 //this subroutine works to add the group, this will add the data entered in the textFields to the database
347 private void addGroup() {
348 try {
349 String sqlInsert = "INSERT INTO `passports`(`Agent`, `GroupNo`, `NoPassports`, `SendingDate`, `Postage`, `ReceivingDate`, `Returned`, `Travelling Date`) VALUES (?,?,?,?,?,?,?,?)";
350 pst = con.prepareStatement(sqlInsert);
351 pst.setString(1, agentTF.getText());
352 pst.setString(2, groupNoTF.getText());
353 pst.setString(3, noPassportTF.getText());
354 pst.setString(4, sendingDateTF.getText());
355 pst.setString(5, postageBarcodeTF.getText());
356 pst.setString(6, receivingDateTF.getText());
357 pst.setString(7, returnedTF.getText());
358 pst.setString(8, commentsTF.getText());
359 pst.executeUpdate();
360 JOptionPane.showMessageDialog(null, "Successfully Inserted!");
361 //as soon as the data gets accepted by the system and saved in the database, the system will retrieve the data again and display the latest data available in the database
362 retrieveData();
363 //as soon as a group gets accepted and saved in the database, all the textFields get cleared
364 clearField();
365 } catch (Exception ex) {
366 JOptionPane.showMessageDialog(null, ex);
367 }
368 }
369
370 //This subroutine works to delete an existing group in the database
371 private void deleteGroup() {
372 try {
373 String sqlDelete = "DELETE FROM `passports` WHERE `GroupNo` = ?";
374 pst = con.prepareStatement(sqlDelete);
375 //only the groupNo field is required to be filled in order to delete a group
376 pst.setString(1, groupNoTF.getText());
377 pst.executeUpdate();
378 JOptionPane.showMessageDialog(null, "Successfully Deleted!");
379 retrieveData();
380 clearField();
381 } catch (Exception ex) {
382 JOptionPane.showMessageDialog(null, ex);
383 }
384 }
385
386 //this subroutine works to update an existing group in the database
387 private void updateGroup() {
388 try {
389 String sqlUpdate = "UPDATE `passports` SET `Agent`=?,`NoPassports`=?,`SendingDate`=?,`Postage`=?,`ReceivingDate`=?,`Returned`=?,`Travelling Date`=? WHERE `GroupNo`=?";
390 pst = con.prepareStatement(sqlUpdate);
391 //in order to update a group, all the textFields are required to be filled again, if any of them left blank, the will be saved empty in the database
392 pst.setString(8, groupNoTF.getText());
393 pst.setString(1, agentTF.getText());
394 pst.setString(2, noPassportTF.getText());
395 pst.setString(3, sendingDateTF.getText());
396 pst.setString(4, postageBarcodeTF.getText());
397 pst.setString(5, receivingDateTF.getText());
398 pst.setString(6, returnedTF.getText());
399 pst.setString(7, commentsTF.getText());
400 pst.executeUpdate();
401 JOptionPane.showMessageDialog(null, "Successfully Updated!");
402 retrieveData();
403 clearField();
404 } catch (Exception ex) {
405 JOptionPane.showMessageDialog(null, ex);
406 }
407 }
408
409 //This subroutine generate a PDF and saves it in the PDF format
410 private void createPDF() {
411 String path = "";
412 JFileChooser j = new JFileChooser();
413 j.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
414 int x = j.showSaveDialog(this);
415 if (x == JFileChooser.APPROVE_OPTION) {
416 path = j.getSelectedFile().getPath();
417 }
418 //create a document of a A4 size page
419 Document doc = new Document(PageSize.A4.rotate());
420 try {
421 PdfWriter writer = PdfWriter.getInstance(doc, new FileOutputStream(path +".pdf"));
422 doc.open();
423
424 //create a table with 8 columns in the pdf file
425 PdfPTable tbl = new PdfPTable(8);
426 tbl.addCell("Agent");
427 tbl.addCell("Group No.");
428 tbl.addCell("No. of Passports");
429 tbl.addCell("Sending Out Date");
430 tbl.addCell("Postage Barcode");
431 tbl.addCell("Receiving Back Date");
432 tbl.addCell("Returned");
433 tbl.addCell("Comments");
434
435 for (int i = 0; i < mainTable.getRowCount(); i++) {
436 String agent = mainTable.getValueAt(i, 0).toString();
437 String groupNo = mainTable.getValueAt(i, 1).toString();
438 String noPassport = mainTable.getValueAt(i, 2).toString();
439 String sendingDate = mainTable.getValueAt(i, 3).toString();
440 String postageBarcode = mainTable.getValueAt(i, 3).toString();
441 String receivingDate = mainTable.getValueAt(i, 4).toString();
442 String returned = mainTable.getValueAt(i, 5).toString();
443 String comments = mainTable.getValueAt(i, 6).toString();
444
445 tbl.addCell(agent);
446 tbl.addCell(groupNo);
447 tbl.addCell(noPassport);
448 tbl.addCell(sendingDate);
449 tbl.addCell(postageBarcode);
450 tbl.addCell(receivingDate);
451 tbl.addCell(returned);
452 tbl.addCell(comments);
453 }
454 doc.add(tbl);
455 doc.close();
456 writer.close();
457 } catch (Exception ex) {
458 System.out.println(ex);
459 }
460 }
461
462 @Override
463 public void actionPerformed(ActionEvent ae) {
464 if (ae.getSource() == backButton) {
465 mainFrame frame = new mainFrame();
466 this.dispose();
467 } else if (ae.getSource() == insertButton) {
468 addGroup();
469 } else if (ae.getSource() == editButton) {
470 updateGroup();
471 } else if (ae.getSource() == deleteButton) {
472 deleteGroup();
473 } else if (ae.getSource() == printButton) {
474 createPDF();
475 }
476 }
477}
478//this class allows the user to add new groups and create tables in the database and/or open and delete them
479public class chooseGroup extends JFrame implements ActionListener {
480
481 JLabel heading, addGroupL, openGroupL, deleteGroupL, printGroupL;
482 JTextField addGroupTF, openGroupTF, deleteGroupTF, printGroupTF;
483 String groupNameEntered;
484 JButton createGroupB, openGroupB, deleteGroupB, goBackB;
485 JTable mainTable;
486 JScrollPane scrollPaneMT;
487 JPanel editorP;
488 Connection con = mySqlConnect.ConnectDCgroups();
489 PreparedStatement pst = null;
490 ResultSet rs = null;
491
492 chooseGroup() {
493 this.setSize(700, 450);
494 this.setLocationRelativeTo(null);
495 this.setTitle("Login");
496 getContentPane().setBackground(Color.WHITE);
497 this.setTitle("Bukhari Travel Ltd");
498 ImageIcon icon = new ImageIcon(getClass().getResource("logo.png"));
499 this.setIconImage(icon.getImage());
500 this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
501
502 heading = new JLabel("Choose Group");
503 addGroupL = new JLabel("Group Name:");
504 addGroupTF = new JTextField();
505 createGroupB = new JButton("Create Group");
506 createGroupB.addActionListener(this);
507 openGroupL = new JLabel("Enter Group Name:");
508 openGroupTF = new JTextField();
509 openGroupB = new JButton("Open Group");
510 openGroupB.addActionListener(this);
511 deleteGroupB = new JButton("Delete Group");
512 deleteGroupB.addActionListener(this);
513 goBackB = new JButton("Go Back");
514 goBackB.addActionListener(this);
515
516 mainTable = new JTable();
517 scrollPaneMT = new JScrollPane(mainTable);
518
519 editorP = new JPanel();
520 editorP.add(openGroupL);
521 editorP.add(openGroupTF);
522 editorP.add(openGroupB);
523 editorP.add(deleteGroupB);
524
525
526 // 1st x value, 2nd y value, 3rd width, 4th height
527 //wholeFrame
528 heading.setBounds(270, 10, 200, 30);
529 heading.setFont(heading.getFont().deriveFont(20f));
530 addGroupL.setBounds(100, 50, 100, 30);
531 addGroupTF.setBounds(220, 48, 200, 30);
532 createGroupB.setBounds(450, 48, 115, 30);
533 scrollPaneMT.setBounds(70, 100, 150, 300);
534 editorP.setBounds(225, 100, 420, 150);
535 goBackB.setBounds(250, 365, 120, 30);
536 //editorPanel
537 openGroupL.setBounds(171, 15, 150, 30);
538 openGroupTF.setBounds(150, 45, 150, 30);
539 openGroupB.setBounds(100, 100, 120, 30);
540 deleteGroupB.setBounds(230, 100, 120, 30);
541
542 this.add(heading);
543 this.add(addGroupL);
544 this.add(addGroupTF);
545 this.add(createGroupB);
546 this.add(scrollPaneMT);
547 this.add(editorP);
548 this.add(goBackB);
549
550 retrieveData();
551 editorP.setLayout(null);
552 this.setLayout(null);
553 this.setVisible(true);
554 }
555
556 //this subroutine creates a table in the database if not exists already
557 private void createTable() {
558 try {
559 //group name is required in the textfield in order to create a group
560 String groupName = addGroupTF.getText();
561 String sql = "CREATE TABLE IF NOT EXISTS `" + groupName + "` (id int NOT NULL, Age int(11), Surname varchar(50) NOT NULL, Forename varchar(50) NOT NULL, Title varchar(10), PassportNo varchar(9), DOB varchar(11), ExpiryDate varchar(11), ContactNo int(15), InvoiceNo int(15), Reference varchar(255), PRIMARY KEY (id))";
562 pst = con.prepareStatement(sql);
563 pst.executeUpdate();
564 JOptionPane.showMessageDialog(null, "Group Created Successfully!");
565 //as soon as the group is created, the system retrieves the data again and show the latest list of the groups available in the database
566 retrieveData();
567 addGroupTF.setText("");
568 } catch (Exception ex) {
569 JOptionPane.showMessageDialog(null, ex);
570 }
571 }
572
573 //this subroutine retrieves the data (list of groups name) from the database
574 private void retrieveData() {
575 try {
576 //sql command select tableNames from the database where the database is called 'groups'a
577 String sqlShowData = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'GROUPS'";
578 pst = con.prepareStatement(sqlShowData);
579 rs = pst.executeQuery();
580 //show the retrieved from the database into the table
581 mainTable.setModel(DbUtils.resultSetToTableModel(rs));
582 } catch (Exception ex) {
583 JOptionPane.showMessageDialog(null, ex);
584 }
585 }
586
587 //this subroutine functions to delete an existing group in the database
588 private void deleteGroup() {
589 try {
590 groupNameEntered = this.openGroupTF.getText();
591 String sqldeleteGroup = "DROP TABLE IF EXISTS `" + groupNameEntered + "`";
592 pst = con.prepareStatement(sqldeleteGroup);
593 pst.executeUpdate();
594 JOptionPane.showMessageDialog(null, "Group Deleted Successfully!");
595 retrieveData();
596 openGroupTF.setText("");
597 } catch (Exception ex) {
598 JOptionPane.showMessageDialog(null, ex);
599 }
600 }
601
602 public void actionPerformed(ActionEvent ae) {
603 if (ae.getSource() == createGroupB) {
604 createTable();
605 } else if (ae.getSource() == openGroupB) {
606 groupFrame frame = new groupFrame(this.openGroupTF.getText());
607 this.dispose();
608 } else if (ae.getSource() == deleteGroupB) {
609 deleteGroup();
610 } else if (ae.getSource() == goBackB) {
611 mainFrame frame = new mainFrame();
612 this.dispose();
613 }
614 }
615}
616
617//this class allows the user to see, add, edit, delete or print the data of the group chosen in the previous frame
618public class groupFrame extends JFrame implements ActionListener {
619
620 Connection con = mySqlConnect.ConnectDCgroups();
621 PreparedStatement pst = null;
622 PreparedStatement pst2 = null;
623 ResultSet rs = null;
624 JLabel heading, indexL, ageL, surnameL, forenameL, titleL, passportNoL, DOBL, expiryDateL, contactNoL, invoiceNoL, referenceL, openedGroupL, openGroupNameL;
625 JTextField indexTF, ageTF, surnameTF, forenameTF, titleTF, passportNoTF, DOBTF, expiryDateTF, contactNoTF, invoiceNoTF, referenceTF;
626 JButton back, addPilgrim, deletePilgrim, updatePilgrim, savePDF;
627 JPanel mainTablePanel, insertDataPanel;
628 JTable mainTable;
629 DefaultTableModel mainTableModel;
630 JScrollPane scrollPane;
631 String groupName;
632
633 groupFrame(String groupName) {
634 this.groupName = groupName;
635 this.setSize(1366, 730);
636 this.setLocationRelativeTo(null);
637 getContentPane().setBackground(Color.WHITE);
638 this.setTitle("Bukhari Travel Ltd");
639 this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
640 ImageIcon icon = new ImageIcon(getClass().getResource("logo.png"));
641 this.setIconImage(icon.getImage());
642
643 //All the labels
644 heading = new JLabel("Manage Your Groups");
645 indexL = new JLabel("Index");
646 ageL = new JLabel("Age");
647 surnameL = new JLabel("Surname");
648 forenameL = new JLabel("Forename(s)");
649 titleL = new JLabel("Title");
650 passportNoL = new JLabel("Passport No.");
651 DOBL = new JLabel("D.O.B.");
652 expiryDateL = new JLabel("Expiry Date");
653 contactNoL = new JLabel("Contact No.");
654 invoiceNoL = new JLabel("Invoice No.");
655 referenceL = new JLabel("Reference");
656 openedGroupL = new JLabel("Group Name: ");
657 openGroupNameL = new JLabel(groupName);
658
659 //All the Text Fields
660 indexTF = new JTextField();
661 ageTF = new JTextField();
662 surnameTF = new JTextField();
663 forenameTF = new JTextField();
664 titleTF = new JTextField();
665 passportNoTF = new JTextField();
666 DOBTF = new JTextField();
667 expiryDateTF = new JTextField();
668 contactNoTF = new JTextField();
669 invoiceNoTF = new JTextField();
670 referenceTF = new JTextField();
671
672 //Buttons
673 back = new JButton("Go Back");
674 back.addActionListener(this);
675 addPilgrim = new JButton("Add Pilgrim");
676 addPilgrim.addActionListener(this);
677 updatePilgrim = new JButton("Update Pilgrim");
678 updatePilgrim.addActionListener(this);
679 deletePilgrim = new JButton("Delete Pilgrim");
680 deletePilgrim.addActionListener(this);
681 savePDF = new JButton("Save PDF");
682 savePDF.addActionListener(this);
683
684 //Jpanels
685 mainTablePanel = new JPanel();
686 insertDataPanel = new JPanel();
687
688 //Insert Data Panel additions
689 insertDataPanel.add(indexL);
690 insertDataPanel.add(indexTF);
691 insertDataPanel.add(ageL);
692 insertDataPanel.add(ageTF);
693 insertDataPanel.add(surnameL);
694 insertDataPanel.add(surnameTF);
695 insertDataPanel.add(forenameL);
696 insertDataPanel.add(forenameTF);
697 insertDataPanel.add(titleL);
698 insertDataPanel.add(titleTF);
699 insertDataPanel.add(passportNoL);
700 insertDataPanel.add(passportNoTF);
701 insertDataPanel.add(DOBL);
702 insertDataPanel.add(DOBTF);
703 insertDataPanel.add(expiryDateL);
704 insertDataPanel.add(expiryDateTF);
705 insertDataPanel.add(contactNoL);
706 insertDataPanel.add(contactNoTF);
707 insertDataPanel.add(invoiceNoL);
708 insertDataPanel.add(invoiceNoTF);
709 insertDataPanel.add(referenceL);
710 insertDataPanel.add(referenceTF);
711 insertDataPanel.add(addPilgrim);
712 insertDataPanel.add(updatePilgrim);
713 insertDataPanel.add(deletePilgrim);
714
715 //maintable
716 mainTableModel = new DefaultTableModel();
717 mainTable = new JTable(mainTableModel);
718 scrollPane = new JScrollPane(mainTable);
719
720 mainTableModel.addColumn("Index");
721 mainTableModel.addColumn("Age");
722 mainTableModel.addColumn("Surname");
723 mainTableModel.addColumn("Forename(s)");
724 mainTableModel.addColumn("Title");
725 mainTableModel.addColumn("Passport No.");
726 mainTableModel.addColumn("D.O.B.");
727 mainTableModel.addColumn("Exiry Date");
728 mainTableModel.addColumn("Contact No.");
729 mainTableModel.addColumn("Invoice No.");
730 mainTableModel.addColumn("Reference");
731 mainTable.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
732
733 heading.setBounds(575, 10, 300, 25);
734 heading.setFont(heading.getFont().deriveFont(20f));
735 openedGroupL.setBounds(605, 30, 90, 25);
736 openGroupNameL.setBounds(685, 30, 100, 25);
737 mainTablePanel.setBounds(0, 50, 1366, 400);
738 insertDataPanel.setBounds(20, 480, 1325, 150);
739 savePDF.setBounds(570, 640, 100, 35);
740 back.setBounds(700, 640, 100, 35);
741
742 indexL.setBounds(30, 10, 40, 25);
743 ageL.setBounds(90, 10, 40, 25);
744 surnameL.setBounds(170, 10, 80, 25);
745 forenameL.setBounds(330, 10, 80, 25);
746 titleL.setBounds(490, 10, 40, 25);
747 passportNoL.setBounds(560, 10, 80, 25);
748 DOBL.setBounds(680, 10, 80, 25);
749 expiryDateL.setBounds(800, 10, 80, 25);
750 contactNoL.setBounds(920, 10, 80, 25);
751 invoiceNoL.setBounds(1050, 10, 80, 25);
752 referenceL.setBounds(1170, 10, 80, 25);
753 indexTF.setBounds(20, 50, 40, 30);
754 ageTF.setBounds(80, 50, 40, 30);
755 surnameTF.setBounds(140, 50, 150, 30);
756 forenameTF.setBounds(310, 50, 150, 30);
757 titleTF.setBounds(480, 50, 40, 30);
758 passportNoTF.setBounds(540, 50, 100, 30);
759 DOBTF.setBounds(660, 50, 100, 30);
760 expiryDateTF.setBounds(780, 50, 100, 30);
761 contactNoTF.setBounds(900, 50, 110, 30);
762 invoiceNoTF.setBounds(1030, 50, 100, 30);
763 referenceTF.setBounds(1150, 50, 150, 30);
764 mainTable.setRowHeight(25);
765 addPilgrim.setBounds(460, 100, 125, 35);
766 updatePilgrim.setBounds(600, 100, 125, 35);
767 deletePilgrim.setBounds(740, 100, 125, 35);
768
769
770 mainTablePanel.add(scrollPane);
771 scrollPane.setPreferredSize(new Dimension(1356, 500));
772 this.add(heading);
773 this.add(openedGroupL);
774 this.add(openGroupNameL);
775 this.add(mainTablePanel);
776 this.add(insertDataPanel);
777 this.add(savePDF);
778 this.add(back);
779 insertDataPanel.setLayout(null);
780 this.setLayout(null);
781 retrieveData();
782 scrollPane.setVisible(true);
783 this.setVisible(true);
784 }
785
786 private void retrieveData() {
787 try {
788 String sqlShowData = "SELECT * FROM `groups`.`" + groupName + "`";
789 pst = con.prepareStatement(sqlShowData);
790 rs = pst.executeQuery();
791 mainTable.setModel(DbUtils.resultSetToTableModel(rs));
792 } catch (Exception ex) {
793 JOptionPane.showMessageDialog(null, ex);
794 }
795 }
796
797 private void clearField() {
798 indexTF.setText("");
799 ageTF.setText("");
800 surnameTF.setText("");
801 forenameTF.setText("");
802 titleTF.setText("");
803 passportNoTF.setText("");
804 DOBTF.setText("");
805 expiryDateTF.setText("");
806 contactNoTF.setText("");
807 invoiceNoTF.setText("");
808 referenceTF.setText("");
809 }
810
811 private void addPilgrim() {
812 try {
813 String sqlInsert = "INSERT INTO `groups`.`" + groupName + "` (`id`, `Age`, `Surname`, `Forename`, `Title`, `PassportNo`, `DOB`, `ExpiryDate`, `ContactNo`, `InvoiceNo`, `Reference`) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
814 pst = con.prepareStatement(sqlInsert);
815 pst.setString(1, indexTF.getText());
816 pst.setString(2, ageTF.getText());
817 pst.setString(3, surnameTF.getText());
818 pst.setString(4, forenameTF.getText());
819 pst.setString(5, titleTF.getText());
820 pst.setString(6, passportNoTF.getText());
821 pst.setString(7, DOBTF.getText());
822 pst.setString(8, expiryDateTF.getText());
823 pst.setString(9, contactNoTF.getText());
824 pst.setString(10, invoiceNoTF.getText());
825 pst.setString(11, referenceTF.getText());
826 pst.executeUpdate();
827 JOptionPane.showMessageDialog(null, "Successfully Inserted!");
828 retrieveData();
829 clearField();
830 } catch (Exception ex) {
831 JOptionPane.showMessageDialog(null, ex);
832 }
833 }
834
835 private void deletePilgrim() {
836 try {
837 String sqlDelete = "DELETE FROM `groups`.`" + groupName + "` WHERE `id` = ?";
838 pst = con.prepareStatement(sqlDelete);
839 pst.setString(1, indexTF.getText());
840 pst.executeUpdate();
841 JOptionPane.showMessageDialog(null, "Successfully Deleted!");
842 retrieveData();
843 clearField();
844 } catch (Exception ex) {
845 JOptionPane.showMessageDialog(null, ex);
846 }
847 }
848
849 private void updatePilgrim() {
850 try {
851 String sqlUpdate = "UPDATE `groups`.`" + groupName + "` SET `Age`=?,`Surname`=?,`Forename`=?,`Title`=?,`PassportNo`=?,`DOB`=?,`ExpiryDate`=?,`ContactNo`=?,`InvoiceNo`=?,`Reference`=? WHERE ?";
852 pst = con.prepareStatement(sqlUpdate);
853 pst.setString(11, indexTF.getText());
854 pst.setString(1, ageTF.getText());
855 pst.setString(2, surnameTF.getText());
856 pst.setString(3, forenameTF.getText());
857 pst.setString(4, titleTF.getText());
858 pst.setString(5, passportNoTF.getText());
859 pst.setString(6, DOBTF.getText());
860 pst.setString(7, expiryDateTF.getText());
861 pst.setString(8, contactNoTF.getText());
862 pst.setString(9, invoiceNoTF.getText());
863 pst.setString(10, referenceTF.getText());
864 pst.executeUpdate();
865 JOptionPane.showMessageDialog(null, "Successfully Updated!");
866 retrieveData();
867 clearField();
868 } catch (Exception ex) {
869 JOptionPane.showMessageDialog(null, ex);
870 }
871 }
872
873 private void createPDF() {
874 String path = "";
875 JFileChooser j = new JFileChooser();
876 j.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
877 int x = j.showSaveDialog(this);
878 if (x == JFileChooser.APPROVE_OPTION) {
879 path = j.getSelectedFile().getPath();
880 }
881
882 Document doc = new Document(PageSize.A4.rotate());
883 try {
884 PdfWriter writer = PdfWriter.getInstance(doc, new FileOutputStream(path +".pdf"));
885 doc.open();
886 Paragraph title = new Paragraph(groupName);
887 PdfPTable tbl = new PdfPTable(11);
888 tbl.addCell("Index");
889 tbl.addCell("Age");
890 tbl.addCell("Surname");
891 tbl.addCell("Forename (s)");
892 tbl.addCell("Title");
893 tbl.addCell("Passport No.");
894 tbl.addCell("DOB");
895 tbl.addCell("Expiry Date");
896 tbl.addCell("Contact No.");
897 tbl.addCell("Inovice No.");
898 tbl.addCell("Reference");
899
900 for (int i = 0; i < mainTable.getRowCount(); i++) {
901 String Index = mainTable.getValueAt(i, 0).toString();
902 String Age = mainTable.getValueAt(i, 1).toString();
903 String Surname = mainTable.getValueAt(i, 2).toString();
904 String Forename = mainTable.getValueAt(i, 3).toString();
905 String Title = mainTable.getValueAt(i, 4).toString();
906 String PassportNo = mainTable.getValueAt(i, 5).toString();
907 String DOB = mainTable.getValueAt(i, 6).toString();
908 String ExpiryDate = mainTable.getValueAt(i, 7).toString();
909 String ContactNo = mainTable.getValueAt(i, 8).toString();
910 String InvoiceNo = mainTable.getValueAt(i, 9).toString();
911 String Reference = mainTable.getValueAt(i, 10).toString();
912
913 tbl.addCell(Index);
914 tbl.addCell(Age);
915 tbl.addCell(Surname);
916 tbl.addCell(Forename);
917 tbl.addCell(Title);
918 tbl.addCell(PassportNo);
919 tbl.addCell(DOB);
920 tbl.addCell(ExpiryDate);
921 tbl.addCell(ContactNo);
922 tbl.addCell(InvoiceNo);
923 tbl.addCell(Reference);
924 }
925 doc.addTitle(groupName);
926 doc.add(title);
927 doc.add(tbl);
928 doc.close();
929 writer.close();
930 } catch (Exception ex) {
931 JOptionPane.showMessageDialog(null, ex);
932 }
933 }
934
935 @Override
936 public void actionPerformed(ActionEvent ae) {
937 if (ae.getSource() == back) {
938 chooseGroup frame = new chooseGroup();
939 this.dispose();
940 } else if (ae.getSource() == addPilgrim) {
941 addPilgrim();
942 } else if (ae.getSource() == deletePilgrim) {
943 deletePilgrim();
944 } else if (ae.getSource() == updatePilgrim) {
945 updatePilgrim();
946 } else if (ae.getSource() == savePDF) {
947 createPDF();
948 }
949 }
950}