package travelmanagement;
    // This class works to call the frame (loginFrame)
public class TravelManagement {
    public static void main(String[] args) {
        loginFrame frame = new loginFrame();
    }
}

//This class works to establish a connection between the system and the database
public class mySqlConnect {

    Connection con = null;
        //connect with the database ‘travelmanagement’ on the localhost
        public static Connection ConnectDC() {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3307/travelmanagement", "root", "0786");
                return con;
            } catch (Exception e) {
                JOptionPane.showMessageDialog(null, "Connection Failed");
                return null;
            }
        }
        //connect with the database ‘groups’ on the localhost
        public static Connection ConnectDCgroups() {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3307/groups", "root", "0786");
                return con;
            } catch (Exception e) {
                JOptionPane.showMessageDialog(null, "Connection Failed");
                return null;
            }
        }
    }

    //This class is the loginFrame which allows the user to access the data from the database if the login details are valid
public class loginFrame extends JFrame implements ActionListener {

    Connection con = mySqlConnect.ConnectDC();
    PreparedStatement pst = null;
    ResultSet rs = null;
    JLabel heading, usernameLabel, passwordLabel, errorMsg;
    JTextField usernameField;
    JPasswordField passwordField;
    JButton loginButton, exitButton;

    loginFrame() {
        this.setSize(350, 225);
        this.setLocationRelativeTo(null);
        this.setUndecorated(true);
        this.setTitle("Login");
        ImageIcon icon = new ImageIcon(getClass().getResource("logo.png"));
        this.setIconImage(icon.getImage());
        getContentPane().setBackground(Color.WHITE);

        heading = new JLabel("Login");
        usernameLabel = new JLabel("Username:");
        usernameField = new JTextField(12);
        passwordLabel = new JLabel("Password:");
        passwordField = new JPasswordField(12);
        loginButton = new JButton("Login");
        loginButton.addActionListener(this);
        exitButton = new JButton("Exit");
        exitButton.addActionListener(this);
        errorMsg = new JLabel("Incorrect Username or Password!");
        errorMsg.setVisible(false);

        // 1st x value, 2nd y value, 3rd width, 4th height
        this.setLayout(null);
        heading.setBounds(125, 5, 125, 25);
        heading.setFont(heading.getFont().deriveFont(20f));
        usernameLabel.setBounds(25, 50, 100, 50);
        usernameField.setBounds(150, 60, 150, 30);
        passwordLabel.setBounds(25, 100, 100, 50);
        passwordField.setBounds(150, 105, 150, 30);
        errorMsg.setBounds(75, 147, 200, 30);
        errorMsg.setForeground(Color.red);
        loginButton.setBounds(85, 185, 75, 30);
        exitButton.setBounds(165, 185, 75, 30);

        this.add(heading);
        this.add(usernameLabel);
        this.add(usernameField);
        this.add(passwordLabel);
        this.add(passwordField);
        this.add(loginButton);
        this.add(exitButton);
        this.add(errorMsg);
        this.setVisible(true);
    }

    public void clearFields() {
        usernameField.setText("");
        passwordField.setText("");
    }

    public void checkLogin() {
    //Sql command that will be connected to the database
        String Sql = "SELECT * from login where Username=? and Password=?";
            try {
                pst = con.prepareStatement(Sql);
                pst.setString(1, usernameField.getText());
                pst.setString(2, passwordField.getText());
                //Execute the sql command
        rs = pst.executeQuery();
        //if username & password found, open mainFrame and close the loginFrame
                if (rs.next()) {
                    mainFrame frame = new mainFrame();
                    this.dispose();
                //otherwise display the error message and clear the fields
                } else {
                    errorMsg.setVisible(true);
                    clearFields();
                }
            } catch (Exception e) {

            }
    }

    @Override
    public void actionPerformed(ActionEvent ae) {
        //if loginButton pressed, call the checkLogin() subroutine
        if (ae.getSource() == loginButton) {
            checkLogin();
        //if exitButton pressed, shut down the program and close the frame
        } else if (ae.getSource() == exitButton) {
            System.exit(0);
            this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        }
    }
}

//This frame is the mainframe of the program which allows the user to choose where to go next, either passportFrame or groupFrame
public class mainFrame extends JFrame implements ActionListener {

    JButton passport, group;

    mainFrame() {
        this.setSize(700, 450);
        this.setLocationRelativeTo(null);
        getContentPane().setBackground(Color.WHITE);
        this.setTitle("Bukhari Travel Ltd");
        ImageIcon icon = new ImageIcon(getClass().getResource("logo.png"));
        this.setIconImage(icon.getImage());
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        passport = new JButton(new ImageIcon(getClass().getResource("passport.png")));
        group = new JButton(new ImageIcon(getClass().getResource("group.jpg")));

        // 1st x value, 2nd y value, 3rd width, 4th height
        this.setLayout(null);
        passport.setBounds(25, 75, 300, 300);
        passport.addActionListener(this);
        group.setBounds(355, 75, 300, 300);
        group.addActionListener(this);

        this.add(passport);
        this.add(group);
        this.setVisible(true);
    }

    @Override
    public void actionPerformed(ActionEvent ae) {
        //if passport button pressed, open the passportFrame and close the mainFrame
        if(ae.getSource() == passport) {
            passportFrame frame = new passportFrame();
            this.dispose();
        //if group button pressed, open the chooseGroup Frame and close the mainFrame
        } else if(ae.getSource() == group) {
            chooseGroup frame = new chooseGroup();
            this.dispose();
        }
    }
}

//This frame is for the passport section where all the data regarding passport groups can be seen, added, amended, or printed off
public class passportFrame extends JFrame implements ActionListener {

    JLabel heading, agentL, groupNoL, noPassportL, sendingDateL, postageBarcodeL, receivingDateL,
            returnedL, commentsL, insertDataL;
    JTextField agentTF, groupNoTF, noPassportTF, sendingDateTF, postageBarcodeTF, receivingDateTF, returnedTF,
            commentsTF;
    JButton backButton, addRow, insertButton, deleteButton, editButton, printButton;
    JPanel mainTablePanel, insertDataPanel;
    JTable mainTable;
    JScrollPane scrollPane;
    Connection con = mySqlConnect.ConnectDC();;
    PreparedStatement pst = null;
    ResultSet rs = null;

    passportFrame() {
        this.setSize(1366, 730);
        this.setLocationRelativeTo(null);
        this.getContentPane().setBackground(Color.WHITE);
        this.setTitle("Bukhari Travel Ltd");
        ImageIcon icon = new ImageIcon(getClass().getResource("logo.png"));
        this.setIconImage(icon.getImage());
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        heading = new JLabel("Manage Your Passports");
        insertDataL = new JLabel("Add New Group");
        agentL = new JLabel("Agent:");
        groupNoL = new JLabel("Group No.:");
        noPassportL = new JLabel("No. of Passports:");
        sendingDateL = new JLabel("Sending Date:");
        postageBarcodeL = new JLabel("Postage Barcode:");
        receivingDateL = new JLabel("Receiving Date:");
        returnedL = new JLabel("Returned:");
        commentsL = new JLabel("Travelling Date:");

        agentTF = new JTextField();
        groupNoTF = new JTextField();
        noPassportTF = new JTextField();
        sendingDateTF = new JTextField();
        postageBarcodeTF = new JTextField();
        receivingDateTF = new JTextField();
        returnedTF = new JTextField();
        commentsTF = new JTextField();

        insertButton = new JButton("Add");
        insertButton.addActionListener(this);
        editButton = new JButton("Edit");
        editButton.addActionListener(this);
        deleteButton = new JButton("Delete");
        deleteButton.addActionListener(this);
        printButton = new JButton("Print PDF");
        printButton.addActionListener(this);
        backButton = new JButton("Go Back");
        backButton.addActionListener(this);

        DefaultTableModel mainTableModel = new DefaultTableModel();
        mainTable = new JTable(mainTableModel);
        scrollPane = new JScrollPane(mainTable);

        insertDataPanel = new JPanel();
        mainTablePanel = new JPanel();

        insertDataPanel.add(insertDataL);
        insertDataPanel.add(agentL);
        insertDataPanel.add(groupNoL);
        insertDataPanel.add(noPassportL);
        insertDataPanel.add(sendingDateL);
        insertDataPanel.add(postageBarcodeL);
        insertDataPanel.add(receivingDateL);
        insertDataPanel.add(returnedL);
        insertDataPanel.add(commentsL);
        insertDataPanel.add(agentTF);
        insertDataPanel.add(groupNoTF);
        insertDataPanel.add(noPassportTF);
        insertDataPanel.add(sendingDateTF);
        insertDataPanel.add(postageBarcodeTF);
        insertDataPanel.add(receivingDateTF);
        insertDataPanel.add(returnedTF);
        insertDataPanel.add(commentsTF);
        insertDataPanel.add(insertButton);
        insertDataPanel.add(editButton);
        insertDataPanel.add(deleteButton);

        mainTableModel.addColumn("Agent");
        mainTableModel.addColumn("Group No.");
        mainTableModel.addColumn("No. of Passports");
        mainTableModel.addColumn("Sending Date");
        mainTableModel.addColumn("Postage Barcode");
        mainTableModel.addColumn("Receiving Date");
        mainTableModel.addColumn("Returned");
        mainTableModel.addColumn("Comments");

        // 1st x value, 2nd y value, 3rd width, 4th height
        heading.setBounds(575, 10, 300, 30);
        heading.setFont(heading.getFont().deriveFont(25f));

        insertDataPanel.setBounds(20, 50, 375, 375);
        insertDataL.setBounds(120, 10, 200, 30);
        insertDataL.setFont(insertDataL.getFont().deriveFont(20f));
        agentL.setBounds(20, 62, 100, 15);
        groupNoL.setBounds(20, 92, 100, 15);
        noPassportL.setBounds(20, 122, 100, 15);
        sendingDateL.setBounds(20, 152, 100, 15);
        postageBarcodeL.setBounds(20, 182, 150, 15);
        receivingDateL.setBounds(20, 212, 100, 15);
        returnedL.setBounds(20, 242, 100, 15);
        commentsL.setBounds(20, 272, 100, 15);

        agentTF.setBounds(180, 55, 150, 25);
        groupNoTF.setBounds(180, 85, 150, 25);
        noPassportTF.setBounds(180, 115, 150, 25);
        sendingDateTF.setBounds(180, 145, 150, 25);
        postageBarcodeTF.setBounds(180, 175, 150, 25);
        receivingDateTF.setBounds(180, 205, 150, 25);
        returnedTF.setBounds(180, 235, 150, 25);
        commentsTF.setBounds(180, 265, 150, 25);

        insertButton.setBounds(55, 320, 75, 30);
        editButton.setBounds(155, 320, 75, 30);
        deleteButton.setBounds(255, 320, 75, 30);

        mainTablePanel.setBounds(425, 50, 900, 500);
        mainTable.setLayout(null);
        mainTable.setBounds(475, 300, 1000, 500);
        mainTable.setRowHeight(25);
        mainTable.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);

        backButton.setBounds(675, 650, 100, 35);
        printButton.setBounds(560, 650, 100, 35);

        mainTablePanel.add(scrollPane);
        scrollPane.setPreferredSize(new Dimension(900, 500));

        this.add(heading);
        this.add(insertDataPanel);
        this.add(mainTablePanel);
        this.add(printButton);
        this.add(backButton);
        insertDataPanel.setLayout(null);
        this.setLayout(null);
        retrieveData();
        this.setVisible(true);
    }

   //This subroutine retrieves the data from the database where the table name is ‘passports’
    private void retrieveData() {
        try {
            String sqlShowData = "SELECT * FROM `passports`";
            pst = con.prepareStatement(sqlShowData);
            rs = pst.executeQuery();
        //display all the data from the database in the mainTable, in the format that is saved in the database
            mainTable.setModel(DbUtils.resultSetToTableModel(rs));
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }

    //This subroutine clears the textFields
    private void clearField() {
        agentTF.setText("");
        groupNoTF.setText("");
        noPassportTF.setText("");
        sendingDateTF.setText("");
        postageBarcodeTF.setText("");
        receivingDateTF.setText("");
        returnedTF.setText("");
        commentsTF.setText("");
    }

    //this subroutine works to add the group, this will add the data entered in the textFields to the database
    private void addGroup() {
        try {
            String sqlInsert = "INSERT INTO `passports`(`Agent`, `GroupNo`, `NoPassports`, `SendingDate`, `Postage`, `ReceivingDate`, `Returned`, `Travelling Date`) VALUES (?,?,?,?,?,?,?,?)";
            pst = con.prepareStatement(sqlInsert);
            pst.setString(1, agentTF.getText());
            pst.setString(2, groupNoTF.getText());
            pst.setString(3, noPassportTF.getText());
            pst.setString(4, sendingDateTF.getText());
            pst.setString(5, postageBarcodeTF.getText());
            pst.setString(6, receivingDateTF.getText());
            pst.setString(7, returnedTF.getText());
            pst.setString(8, commentsTF.getText());
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully Inserted!");
            //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
            retrieveData();
            //as soon as a group gets accepted and saved in the database, all the textFields get cleared
            clearField();
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

    //This subroutine works to delete an existing group in the database
    private void deleteGroup() {
        try {
            String sqlDelete = "DELETE FROM `passports` WHERE `GroupNo` = ?";
            pst = con.prepareStatement(sqlDelete);
            //only the groupNo field is required to be filled in order to delete a group
            pst.setString(1, groupNoTF.getText());
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully Deleted!");
            retrieveData();
            clearField();
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

    //this subroutine works to update an existing group in the database
    private void updateGroup() {
        try {
            String sqlUpdate = "UPDATE `passports` SET `Agent`=?,`NoPassports`=?,`SendingDate`=?,`Postage`=?,`ReceivingDate`=?,`Returned`=?,`Travelling Date`=? WHERE `GroupNo`=?";
            pst = con.prepareStatement(sqlUpdate);
            //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
            pst.setString(8, groupNoTF.getText());
            pst.setString(1, agentTF.getText());
            pst.setString(2, noPassportTF.getText());
            pst.setString(3, sendingDateTF.getText());
            pst.setString(4, postageBarcodeTF.getText());
            pst.setString(5, receivingDateTF.getText());
            pst.setString(6, returnedTF.getText());
            pst.setString(7, commentsTF.getText());
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully Updated!");
            retrieveData();
            clearField();
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

    //This subroutine generate a PDF and saves it in the PDF format
    private void createPDF() {
        String path = "";
        JFileChooser j = new JFileChooser();
        j.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
        int x = j.showSaveDialog(this);
        if (x == JFileChooser.APPROVE_OPTION) {
            path = j.getSelectedFile().getPath();
        }
        //create a document of a A4 size page
        Document doc = new Document(PageSize.A4.rotate());
        try {
            PdfWriter writer = PdfWriter.getInstance(doc, new FileOutputStream(path +".pdf"));
            doc.open();

            //create a table with 8 columns in the pdf file
            PdfPTable tbl = new PdfPTable(8);
            tbl.addCell("Agent");
            tbl.addCell("Group No.");
            tbl.addCell("No. of Passports");
            tbl.addCell("Sending Out Date");
            tbl.addCell("Postage Barcode");
            tbl.addCell("Receiving Back Date");
            tbl.addCell("Returned");
            tbl.addCell("Comments");

            for (int i = 0; i < mainTable.getRowCount(); i++) {
                String agent = mainTable.getValueAt(i, 0).toString();
                String groupNo = mainTable.getValueAt(i, 1).toString();
                String noPassport = mainTable.getValueAt(i, 2).toString();
                String sendingDate = mainTable.getValueAt(i, 3).toString();
                String postageBarcode = mainTable.getValueAt(i, 3).toString();
                String receivingDate = mainTable.getValueAt(i, 4).toString();
                String returned = mainTable.getValueAt(i, 5).toString();
                String comments = mainTable.getValueAt(i, 6).toString();

                tbl.addCell(agent);
                tbl.addCell(groupNo);
                tbl.addCell(noPassport);
                tbl.addCell(sendingDate);
                tbl.addCell(postageBarcode);
                tbl.addCell(receivingDate);
                tbl.addCell(returned);
                tbl.addCell(comments);
            }
            doc.add(tbl);
            doc.close();
            writer.close();
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }

    @Override
    public void actionPerformed(ActionEvent ae) {
        if (ae.getSource() == backButton) {
            mainFrame frame = new mainFrame();
            this.dispose();
        } else if (ae.getSource() == insertButton) {
            addGroup();
        } else if (ae.getSource() == editButton) {
            updateGroup();
        } else if (ae.getSource() == deleteButton) {
            deleteGroup();
        } else if (ae.getSource() == printButton) {
            createPDF();
        }
    }
}
//this class allows the user to add new groups and create tables in the database and/or open and delete them
public class chooseGroup extends JFrame implements ActionListener {

    JLabel heading, addGroupL, openGroupL, deleteGroupL, printGroupL;
    JTextField addGroupTF, openGroupTF, deleteGroupTF, printGroupTF;
    String groupNameEntered;
    JButton createGroupB, openGroupB, deleteGroupB, goBackB;
    JTable mainTable;
    JScrollPane scrollPaneMT;
    JPanel editorP;
    Connection con = mySqlConnect.ConnectDCgroups();
    PreparedStatement pst = null;
    ResultSet rs = null;

    chooseGroup() {
        this.setSize(700, 450);
        this.setLocationRelativeTo(null);
        this.setTitle("Login");
        getContentPane().setBackground(Color.WHITE);
        this.setTitle("Bukhari Travel Ltd");
        ImageIcon icon = new ImageIcon(getClass().getResource("logo.png"));
        this.setIconImage(icon.getImage());
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        heading = new JLabel("Choose Group");
        addGroupL = new JLabel("Group Name:");
        addGroupTF = new JTextField();
        createGroupB = new JButton("Create Group");
        createGroupB.addActionListener(this);
        openGroupL = new JLabel("Enter Group Name:");
        openGroupTF = new JTextField();
        openGroupB = new JButton("Open Group");
        openGroupB.addActionListener(this);
        deleteGroupB = new JButton("Delete Group");
        deleteGroupB.addActionListener(this);
        goBackB = new JButton("Go Back");
        goBackB.addActionListener(this);

        mainTable = new JTable();
        scrollPaneMT = new JScrollPane(mainTable);

        editorP = new JPanel();
        editorP.add(openGroupL);
        editorP.add(openGroupTF);
        editorP.add(openGroupB);
        editorP.add(deleteGroupB);


        // 1st x value, 2nd y value, 3rd width, 4th height
        //wholeFrame
        heading.setBounds(270, 10, 200, 30);
        heading.setFont(heading.getFont().deriveFont(20f));
        addGroupL.setBounds(100, 50, 100, 30);
        addGroupTF.setBounds(220, 48, 200, 30);
        createGroupB.setBounds(450, 48, 115, 30);
        scrollPaneMT.setBounds(70, 100, 150, 300);
        editorP.setBounds(225, 100, 420, 150);
        goBackB.setBounds(250, 365, 120, 30);
        //editorPanel
        openGroupL.setBounds(171, 15, 150, 30);
        openGroupTF.setBounds(150, 45, 150, 30);
        openGroupB.setBounds(100, 100, 120, 30);
        deleteGroupB.setBounds(230, 100, 120, 30);

        this.add(heading);
        this.add(addGroupL);
        this.add(addGroupTF);
        this.add(createGroupB);
        this.add(scrollPaneMT);
        this.add(editorP);
        this.add(goBackB);

        retrieveData();
        editorP.setLayout(null);
        this.setLayout(null);
        this.setVisible(true);
    }

    //this subroutine creates a table in the database if not exists already
    private void createTable() {
        try {
            //group name is required in the textfield in order to create a group
            String groupName = addGroupTF.getText();
            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))";
            pst = con.prepareStatement(sql);
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Group Created Successfully!");
            //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
            retrieveData();
            addGroupTF.setText("");
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

    //this subroutine retrieves the data (list of groups name) from the database
    private void retrieveData() {
        try {
            //sql command select tableNames from the database where the database is called 'groups'a
            String sqlShowData = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'GROUPS'";
            pst = con.prepareStatement(sqlShowData);
            rs = pst.executeQuery();
            //show the retrieved from the database into the table
            mainTable.setModel(DbUtils.resultSetToTableModel(rs));
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

    //this subroutine functions to delete an existing group in the database
    private void deleteGroup() {
        try {
            groupNameEntered = this.openGroupTF.getText();
            String sqldeleteGroup = "DROP TABLE IF EXISTS `" + groupNameEntered + "`";
            pst = con.prepareStatement(sqldeleteGroup);
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Group Deleted Successfully!");
            retrieveData();
            openGroupTF.setText("");
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

    public void actionPerformed(ActionEvent ae) {
        if (ae.getSource() == createGroupB) {
            createTable();
        } else if (ae.getSource() == openGroupB) {
            groupFrame frame = new groupFrame(this.openGroupTF.getText());
            this.dispose();
        } else if (ae.getSource() == deleteGroupB) {
            deleteGroup();
        }  else if (ae.getSource() == goBackB) {
            mainFrame frame = new mainFrame();
            this.dispose();
        }
    }
}

//this class allows the user to see, add, edit, delete or print the data of the group chosen in the previous frame
public class groupFrame extends JFrame implements ActionListener {

    Connection con = mySqlConnect.ConnectDCgroups();
    PreparedStatement pst = null;
    PreparedStatement pst2 = null;
    ResultSet rs = null;
    JLabel heading, indexL, ageL, surnameL, forenameL, titleL, passportNoL, DOBL, expiryDateL, contactNoL, invoiceNoL, referenceL, openedGroupL, openGroupNameL;
    JTextField indexTF, ageTF, surnameTF, forenameTF, titleTF, passportNoTF, DOBTF, expiryDateTF, contactNoTF, invoiceNoTF, referenceTF;
    JButton back, addPilgrim, deletePilgrim, updatePilgrim, savePDF;
    JPanel mainTablePanel, insertDataPanel;
    JTable mainTable;
    DefaultTableModel mainTableModel;
    JScrollPane scrollPane;
    String groupName;

    groupFrame(String groupName) {
        this.groupName = groupName;
        this.setSize(1366, 730);
        this.setLocationRelativeTo(null);
        getContentPane().setBackground(Color.WHITE);
        this.setTitle("Bukhari Travel Ltd");
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        ImageIcon icon = new ImageIcon(getClass().getResource("logo.png"));
        this.setIconImage(icon.getImage());

        //All the labels
        heading = new JLabel("Manage Your Groups");
        indexL = new JLabel("Index");
        ageL = new JLabel("Age");
        surnameL = new JLabel("Surname");
        forenameL = new JLabel("Forename(s)");
        titleL = new JLabel("Title");
        passportNoL = new JLabel("Passport No.");
        DOBL = new JLabel("D.O.B.");
        expiryDateL = new JLabel("Expiry Date");
        contactNoL = new JLabel("Contact No.");
        invoiceNoL = new JLabel("Invoice No.");
        referenceL = new JLabel("Reference");
        openedGroupL = new JLabel("Group Name: ");
        openGroupNameL = new JLabel(groupName);

        //All the Text Fields
        indexTF = new JTextField();
        ageTF = new JTextField();
        surnameTF = new JTextField();
        forenameTF = new JTextField();
        titleTF = new JTextField();
        passportNoTF = new JTextField();
        DOBTF = new JTextField();
        expiryDateTF = new JTextField();
        contactNoTF = new JTextField();
        invoiceNoTF = new JTextField();
        referenceTF = new JTextField();

        //Buttons
        back = new JButton("Go Back");
        back.addActionListener(this);
        addPilgrim = new JButton("Add Pilgrim");
        addPilgrim.addActionListener(this);
        updatePilgrim = new JButton("Update Pilgrim");
        updatePilgrim.addActionListener(this);
        deletePilgrim = new JButton("Delete Pilgrim");
        deletePilgrim.addActionListener(this);
        savePDF = new JButton("Save PDF");
        savePDF.addActionListener(this);

        //Jpanels
        mainTablePanel = new JPanel();
        insertDataPanel = new JPanel();

        //Insert Data Panel additions
        insertDataPanel.add(indexL);
        insertDataPanel.add(indexTF);
        insertDataPanel.add(ageL);
        insertDataPanel.add(ageTF);
        insertDataPanel.add(surnameL);
        insertDataPanel.add(surnameTF);
        insertDataPanel.add(forenameL);
        insertDataPanel.add(forenameTF);
        insertDataPanel.add(titleL);
        insertDataPanel.add(titleTF);
        insertDataPanel.add(passportNoL);
        insertDataPanel.add(passportNoTF);
        insertDataPanel.add(DOBL);
        insertDataPanel.add(DOBTF);
        insertDataPanel.add(expiryDateL);
        insertDataPanel.add(expiryDateTF);
        insertDataPanel.add(contactNoL);
        insertDataPanel.add(contactNoTF);
        insertDataPanel.add(invoiceNoL);
        insertDataPanel.add(invoiceNoTF);
        insertDataPanel.add(referenceL);
        insertDataPanel.add(referenceTF);
        insertDataPanel.add(addPilgrim);
        insertDataPanel.add(updatePilgrim);
        insertDataPanel.add(deletePilgrim);

        //maintable
        mainTableModel = new DefaultTableModel();
        mainTable = new JTable(mainTableModel);
        scrollPane = new JScrollPane(mainTable);

        mainTableModel.addColumn("Index");
        mainTableModel.addColumn("Age");
        mainTableModel.addColumn("Surname");
        mainTableModel.addColumn("Forename(s)");
        mainTableModel.addColumn("Title");
        mainTableModel.addColumn("Passport No.");
        mainTableModel.addColumn("D.O.B.");
        mainTableModel.addColumn("Exiry Date");
        mainTableModel.addColumn("Contact No.");
        mainTableModel.addColumn("Invoice No.");
        mainTableModel.addColumn("Reference");
        mainTable.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);

        heading.setBounds(575, 10, 300, 25);
        heading.setFont(heading.getFont().deriveFont(20f));
        openedGroupL.setBounds(605, 30, 90, 25);
        openGroupNameL.setBounds(685, 30, 100, 25);
        mainTablePanel.setBounds(0, 50, 1366, 400);
        insertDataPanel.setBounds(20, 480, 1325, 150);
        savePDF.setBounds(570, 640, 100, 35);
        back.setBounds(700, 640, 100, 35);

        indexL.setBounds(30, 10, 40, 25);
        ageL.setBounds(90, 10, 40, 25);
        surnameL.setBounds(170, 10, 80, 25);
        forenameL.setBounds(330, 10, 80, 25);
        titleL.setBounds(490, 10, 40, 25);
        passportNoL.setBounds(560, 10, 80, 25);
        DOBL.setBounds(680, 10, 80, 25);
        expiryDateL.setBounds(800, 10, 80, 25);
        contactNoL.setBounds(920, 10, 80, 25);
        invoiceNoL.setBounds(1050, 10, 80, 25);
        referenceL.setBounds(1170, 10, 80, 25);
        indexTF.setBounds(20, 50, 40, 30);
        ageTF.setBounds(80, 50, 40, 30);
        surnameTF.setBounds(140, 50, 150, 30);
        forenameTF.setBounds(310, 50, 150, 30);
        titleTF.setBounds(480, 50, 40, 30);
        passportNoTF.setBounds(540, 50, 100, 30);
        DOBTF.setBounds(660, 50, 100, 30);
        expiryDateTF.setBounds(780, 50, 100, 30);
        contactNoTF.setBounds(900, 50, 110, 30);
        invoiceNoTF.setBounds(1030, 50, 100, 30);
        referenceTF.setBounds(1150, 50, 150, 30);
        mainTable.setRowHeight(25);
        addPilgrim.setBounds(460, 100, 125, 35);
        updatePilgrim.setBounds(600, 100, 125, 35);
        deletePilgrim.setBounds(740, 100, 125, 35);


        mainTablePanel.add(scrollPane);
        scrollPane.setPreferredSize(new Dimension(1356, 500));
        this.add(heading);
        this.add(openedGroupL);
        this.add(openGroupNameL);
        this.add(mainTablePanel);
        this.add(insertDataPanel);
        this.add(savePDF);
        this.add(back);
        insertDataPanel.setLayout(null);
        this.setLayout(null);
        retrieveData();
        scrollPane.setVisible(true);
        this.setVisible(true);
    }

    private void retrieveData() {
        try {
            String sqlShowData = "SELECT * FROM `groups`.`" + groupName + "`";
            pst = con.prepareStatement(sqlShowData);
            rs = pst.executeQuery();
            mainTable.setModel(DbUtils.resultSetToTableModel(rs));
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

    private void clearField() {
        indexTF.setText("");
        ageTF.setText("");
        surnameTF.setText("");
        forenameTF.setText("");
        titleTF.setText("");
        passportNoTF.setText("");
        DOBTF.setText("");
        expiryDateTF.setText("");
        contactNoTF.setText("");
        invoiceNoTF.setText("");
        referenceTF.setText("");
    }

    private void addPilgrim() {
        try {
            String sqlInsert = "INSERT INTO `groups`.`" + groupName + "` (`id`, `Age`, `Surname`, `Forename`, `Title`, `PassportNo`, `DOB`, `ExpiryDate`, `ContactNo`, `InvoiceNo`, `Reference`) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
            pst = con.prepareStatement(sqlInsert);
            pst.setString(1, indexTF.getText());
            pst.setString(2, ageTF.getText());
            pst.setString(3, surnameTF.getText());
            pst.setString(4, forenameTF.getText());
            pst.setString(5, titleTF.getText());
            pst.setString(6, passportNoTF.getText());
            pst.setString(7, DOBTF.getText());
            pst.setString(8, expiryDateTF.getText());
            pst.setString(9, contactNoTF.getText());
            pst.setString(10, invoiceNoTF.getText());
            pst.setString(11, referenceTF.getText());
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully Inserted!");
            retrieveData();
            clearField();
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

    private void deletePilgrim() {
        try {
            String sqlDelete = "DELETE FROM `groups`.`" + groupName + "` WHERE `id` = ?";
            pst = con.prepareStatement(sqlDelete);
            pst.setString(1, indexTF.getText());
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully Deleted!");
            retrieveData();
            clearField();
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

    private void updatePilgrim() {
        try {
            String sqlUpdate = "UPDATE `groups`.`" + groupName + "` SET `Age`=?,`Surname`=?,`Forename`=?,`Title`=?,`PassportNo`=?,`DOB`=?,`ExpiryDate`=?,`ContactNo`=?,`InvoiceNo`=?,`Reference`=? WHERE ?";
            pst = con.prepareStatement(sqlUpdate);
            pst.setString(11, indexTF.getText());
            pst.setString(1, ageTF.getText());
            pst.setString(2, surnameTF.getText());
            pst.setString(3, forenameTF.getText());
            pst.setString(4, titleTF.getText());
            pst.setString(5, passportNoTF.getText());
            pst.setString(6, DOBTF.getText());
            pst.setString(7, expiryDateTF.getText());
            pst.setString(8, contactNoTF.getText());
            pst.setString(9, invoiceNoTF.getText());
            pst.setString(10, referenceTF.getText());
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully Updated!");
            retrieveData();
            clearField();
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

    private void createPDF() {
        String path = "";
        JFileChooser j = new JFileChooser();
        j.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
        int x = j.showSaveDialog(this);
        if (x == JFileChooser.APPROVE_OPTION) {
            path = j.getSelectedFile().getPath();
        }

        Document doc = new Document(PageSize.A4.rotate());
        try {
            PdfWriter writer = PdfWriter.getInstance(doc, new FileOutputStream(path +".pdf"));
            doc.open();
            Paragraph title = new Paragraph(groupName);
            PdfPTable tbl = new PdfPTable(11);
            tbl.addCell("Index");
            tbl.addCell("Age");
            tbl.addCell("Surname");
            tbl.addCell("Forename (s)");
            tbl.addCell("Title");
            tbl.addCell("Passport No.");
            tbl.addCell("DOB");
            tbl.addCell("Expiry Date");
            tbl.addCell("Contact No.");
            tbl.addCell("Inovice No.");
            tbl.addCell("Reference");

            for (int i = 0; i < mainTable.getRowCount(); i++) {
                String Index = mainTable.getValueAt(i, 0).toString();
                String Age = mainTable.getValueAt(i, 1).toString();
                String Surname = mainTable.getValueAt(i, 2).toString();
                String Forename = mainTable.getValueAt(i, 3).toString();
                String Title = mainTable.getValueAt(i, 4).toString();
                String PassportNo = mainTable.getValueAt(i, 5).toString();
                String DOB = mainTable.getValueAt(i, 6).toString();
                String ExpiryDate = mainTable.getValueAt(i, 7).toString();
                String ContactNo = mainTable.getValueAt(i, 8).toString();
                String InvoiceNo = mainTable.getValueAt(i, 9).toString();
                String Reference = mainTable.getValueAt(i, 10).toString();

                tbl.addCell(Index);
                tbl.addCell(Age);
                tbl.addCell(Surname);
                tbl.addCell(Forename);
                tbl.addCell(Title);
                tbl.addCell(PassportNo);
                tbl.addCell(DOB);
                tbl.addCell(ExpiryDate);
                tbl.addCell(ContactNo);
                tbl.addCell(InvoiceNo);
                tbl.addCell(Reference);
            }
            doc.addTitle(groupName);
            doc.add(title);
            doc.add(tbl);
            doc.close();
            writer.close();
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

    @Override
    public void actionPerformed(ActionEvent ae) {
        if (ae.getSource() == back) {
            chooseGroup frame = new chooseGroup();
            this.dispose();
        } else if (ae.getSource() == addPilgrim) {
            addPilgrim();
        } else if (ae.getSource() == deletePilgrim) {
            deletePilgrim();
        } else if (ae.getSource() == updatePilgrim) {
            updatePilgrim();
        } else if (ae.getSource() == savePDF) {
            createPDF();
        }
    }
}