import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ResourceBundle;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.fxml.Initializable;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.scene.control.Alert;
import javafx.scene.control.Alert.AlertType;
import javafx.scene.control.Button;
import javafx.scene.control.ChoiceBox;
import javafx.scene.control.TextField;
import javafx.stage.Stage;
import java.util.Optional;
import javafx.scene.control.ButtonBar.ButtonData;
import javafx.scene.control.ButtonType;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.cell.PropertyValueFactory;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import javafx.scene.layout.VBox;

public class MenuController implements Initializable {

Connection conn = null;
ResultSet rs = null;
PreparedStatement pst = null;

//Fxid's for my objects in the Menu.fxml and the the LibraryMember.fxml
    @FXML
    private Button addBookBtn;
    @FXML
    private Button listAllBooksBtn;
    @FXML
    private Button addLibraryMemberBtn;
    @FXML
    private Button listAllLibraryMembersBtn;
    @FXML
    private Button searchBtn;
    @FXML
    private Button borrowAndReturnBookBtn;
    @FXML
    private Button returnBookBtn;
    @FXML
    private Button closeBtn;
    @FXML
    private Button backToMenuBtn;
    @FXML
    private ChoiceBox<String> genderChoiceBox;
    @FXML
    private TextField searchMemberTextField;
    @FXML
    private TextField memberIDTextField;
    @FXML
    private TextField firstNameTextField;
    @FXML
    private TextField surnameTextField;
    @FXML
    private TextField ageTextField;
    @FXML
    private TextField contactNoTextField;
    @FXML
    private TextField addressTextField;
    @FXML
    private Button clearMemberTextFieldsButton;
    @FXML
    private Button addMemberButton;
    @FXML
    private Button updateMemberButton;
    @FXML
    private Button deleteMemberButton;

//This is what I am working with for the table
@FXML
private TableView<MemberTable> memberListTable;
@FXML
//private TableColumn <MemberTable, Integer> idMemberColumn;
private TableColumn<MemberTable, String> idMemberColumn;
@FXML
private TableColumn<MemberTable, String> firstNameColumn;
@FXML
private TableColumn<MemberTable, String> surnameColumn;
@FXML
//    private TableColumn <MemberTable, Integer> ageColumn;
private TableColumn<MemberTable, String> ageColumn;
@FXML
private TableColumn<MemberTable, String> genderColumn;
@FXML
//    private TableColumn <MemberTable, Integer> contactNoColumn;
private TableColumn<MemberTable, String> contactNoColumn;
@FXML
private TableColumn<MemberTable, String> addressColumn;

//FXid's for Book.fxml
@FXML
private ChoiceBox<String> categoryChoiceBox;
@FXML
private TextField bookTitleTextField;
@FXML
private TextField authorTextField;
@FXML
private TextField publishedTextField;
@FXML
private ChoiceBox <Integer> bookCopiesChoiceBox;
//    private ChoiceBox<String> bookCopiesChoiceBox;
@FXML
private TextField dateAddedBookTextField;
@FXML
private TextField searchBooksTextField;
@FXML
private Button clearBookFieldsBtn;
@FXML
private Button addBookDetailsBtn;
@FXML
private Button updateBookBtn;
@FXML
private Button deleteBookBtn;

//FXid's for TableView for Books in Book.fxml
//    @FXML
//    private TableView<BookTable> bookListTable;
@FXML
private TableColumn <BookTable, Integer> bookID;
//    private TableColumn<BookTable, String> bookIDColumn;
@FXML
private TableColumn<BookTable, String> bookTitleColumn;
@FXML
private TableColumn<BookTable, String> bookAuthorColumn;
@FXML
//    private TableColumn <BookTable, Integer> yearPublishedColumn;
private TableColumn<BookTable, String> yearPublishedColumn;
@FXML
private TableColumn <BookTable, Number> bookCopiesColumn;
//    private TableColumn<BookTable, String> bookCopiesColumn;
@FXML
private TableColumn<BookTable, String> bookCategoryColumn;
@FXML
private TableColumn<BookTable, String> dateAddedBookColumn;

//  FXid's for LoanBR.fxml

private int count = 1;

Stage LibraryApplication;
Stage LibraryMember;
Stage Book;

private ObservableList<BookTable> bookDetails;

@FXML
private TableView<BookTable> bookListTable;

private final ObservableList<BookTable> data;

public MenuController() {
    this.data = FXCollections.observableArrayList(
            new BookTable(1, "Test1", "Author1", "2015", 1, "Fiction", "04-04-16"),
            new BookTable(1, "Test2", "Author2", "2015", 1, "Fiction", "04-04-16"),
            new BookTable(1, "Test3", "Author3", "2015", 1, "Fiction", "04-04-16"),
            new BookTable(1, "Test4", "Author4", "2015", 1, "Fiction", "04-04-16"),
            new BookTable(1, "Test5", "Author5", "2015", 1, "Fiction", "04-04-16")
    );
}

@FXML
private void handleBookButtonAction(ActionEvent event) throws IOException {
    Stage stage;
    Parent root;
    if (event.getSource() == addBookBtn) {
        stage = (Stage) addBookBtn.getScene().getWindow();

        root = FXMLLoader.load(getClass().getResource("Book.fxml"));
    } else {
        stage = (Stage) backToMenuBtn.getScene().getWindow();
        root = FXMLLoader.load(getClass().getResource("Menu.fxml"));
    }

//        bookID.setCellValueFactory(new PropertyValueFactory<BookTable, Integer>("bookID"));
//        bookTitleColumn.setCellValueFactory(new PropertyValueFactory<BookTable, String>("bookTitle"));
//        bookAuthorColumn.setCellValueFactory(new PropertyValueFactory<BookTable, String>("author"));
//        yearPublishedColumn.setCellValueFactory(new PropertyValueFactory<BookTable, String>("published"));
//        bookCopiesColumn.setCellValueFactory(new PropertyValueFactory<BookTable, Number>("bookCopies"));
//        bookCategoryColumn.setCellValueFactory(new PropertyValueFactory<BookTable, String>("category"));
//        dateAddedBookColumn.setCellValueFactory(new PropertyValueFactory<BookTable, String>("dateAddedBook"));
//
//        bookListTable.setItems(data);
//        bookListTable.getColumns().addAll(bookID, bookTitleColumn, bookAuthorColumn, yearPublishedColumn, bookCopiesColumn, bookCategoryColumn, dateAddedBookColumn);
//
//        final VBox vbox = new VBox();
//        vbox.getChildren().addAll(bookListTable);
//
//        System.out.println("Book Database has been loaded");

    Scene scene = new Scene(root);
    stage.setScene(scene);
    stage.show();

    try{

            //ResultSet
            Statement stmt = conn.createStatement();
            String sql = "SELECT bookid, booktitle, author, published, "
                    + "bookcopies, category, dateadded FROM book";

            ResultSet rs = stmt.executeQuery(sql);

//            bookDetails.add(new BookTable(33, "bla",
//                    "bla", "bla", 3,
//                    "rrt", "4rsgsf"
//            ));

            while(rs.next()){

            }

            rs.close();


       }catch(Exception e){
                e.printStackTrace();
                    System.out.println("Error on Building Data");

            }
    System.out.println("Book Database has been loaded");

}


@FXML
private void handleClearBookTextFieldsButtonAction(ActionEvent event) {

    if (event.getSource() == clearBookFieldsBtn) {
        //memberIDTextField.setText(""); Also makes the text field empty
        clearBookTextFields();
    }

}

//Method to clear all the required text fields in Book.fxml
private void clearBookTextFields() {

    categoryChoiceBox.setValue("Non-Fiction");
    bookTitleTextField.clear();
    authorTextField.clear();
    publishedTextField.clear();
//        bookCopiesChoiceBox.setValue(1); This is not accepted since it has now become a String, temporarily
    bookCopiesChoiceBox.setValue(1);
}

//Validate the textfields in Book.fxml (Still in progress, only checking to see if it is empty or not)
private void bookDetailsValid() {

    if (bookTitleTextField.getText().equals("") == true
            || authorTextField.getText().equals("")
            || publishedTextField.getText().equals("")
            || bookCopiesChoiceBox.getValue() == null
            || categoryChoiceBox.getValue().equals("")
            || dateAddedBookTextField.getText().equals("")) {
        Alert fail = new Alert(AlertType.INFORMATION);
        fail.setHeaderText("Error!");
        fail.setContentText("Missing or Incorrect Details! Please Try Again");
        fail.showAndWait();
        System.out.println("Missing or Incorrect Details! Please Try Again");
    } else {

        bookDetails = bookListTable.getItems();
        bookDetails.add(new BookTable(count++, bookTitleTextField.getText(),
                authorTextField.getText(), publishedTextField.getText(), bookCopiesChoiceBox.getValue(),
                categoryChoiceBox.getValue(), dateAddedBookTextField.getText()
        ));

        try{
            String SQL = "SELECT * from book";
            //ResultSet
            Statement stmt = conn.createStatement();

            String booktitle = bookTitleTextField.getText();
            String author = authorTextField.getText();
            String published = publishedTextField.getText();
            int bookcopies = bookCopiesChoiceBox.getValue();
            String category = categoryChoiceBox.getValue();
            String dateaddedbook = dateAddedBookTextField.getText();




   String sql = "INSERT INTO book (booktitle, author, published, bookcopies,"
           + " category, dateadded) VALUES ('"+booktitle+"', '"+author+"', '"+published+"', '"+bookcopies+"', '"+category+"', '"+dateaddedbook+"')";
   stmt.executeUpdate(sql);
       }catch(Exception e){
                e.printStackTrace();
                    System.out.println("Error on Building Data");

            }
        System.out.println("Data has been Added");

        Double.parseDouble(publishedTextField.getText());
//            Double.parseDouble(bookCopiesChoiceBox.getValue());
        clearBookTextFields();
    }

}

private void editBookDetailsValid() {

    if (bookTitleTextField.getText().equals("") == true
            || authorTextField.getText().equals("")
            || publishedTextField.getText().equals("")
            || bookCopiesChoiceBox.getValue() == null
            || categoryChoiceBox.getValue().equals("")
            || dateAddedBookTextField.getText().equals("")) {
        Alert fail = new Alert(AlertType.INFORMATION);
        fail.setHeaderText("Error!");
        fail.setContentText("Missing or Incorrect Details! Please Try Again");
        fail.showAndWait();
        System.out.println("Missing or Incorrect Details! Please Try Again");
    } else {

        bookDetails = bookListTable.getItems();
        int selectedIndex = bookListTable.getSelectionModel().getSelectedIndex();
        BookTable selectedBook = bookListTable.getSelectionModel().getSelectedItem();

        bookDetails.add(new BookTable(count++, bookTitleTextField.getText(),
                authorTextField.getText(), publishedTextField.getText(), bookCopiesChoiceBox.getValue(),
                categoryChoiceBox.getValue(),
                dateAddedBookTextField.getText()
        ));

        bookListTable.getItems().remove(selectedIndex);

        Double.parseDouble(publishedTextField.getText());
//            Double.parseDouble(bookCopiesChoiceBox.getValue());

        clearBookTextFields();
    }

}

//Handler Button for adding a member
@FXML
private void handleAddBookDetailsButtonAction(ActionEvent event) {

    bookDetailsValid();
}

@FXML
private void handleEditBookDetailsButtonAction(ActionEvent event) {

    editBookDetailsValid();

}

//    The method below is used to allow the user to select a row of data
//    from the Member's Table. Clicking on the row of data, outputs the data
//    into their respective text fields that is used to add new library members.
@FXML
private void initialiseBookTable() {

    bookID.setCellValueFactory(new PropertyValueFactory<BookTable, Integer>("bookID"));
    bookTitleColumn.setCellValueFactory(cellData -> cellData.getValue().bookTitleProperty());
    bookAuthorColumn.setCellValueFactory(cellData -> cellData.getValue().authorProperty());
    yearPublishedColumn.setCellValueFactory(cellData -> cellData.getValue().publishedProperty());
    bookCopiesColumn.setCellValueFactory(cellData -> cellData.getValue().bookCopiesProperty());
    bookCategoryColumn.setCellValueFactory(cellData -> cellData.getValue().categoryProperty());
    dateAddedBookColumn.setCellValueFactory(cellData -> cellData.getValue().dateAddedBookProperty());

    // Clear person details.
    showBookDetails(null);

    // Listen for selection changes and show the person details when changed.
    bookListTable.getSelectionModel().selectedItemProperty().addListener(
            (observable, oldValue, newValue) -> showBookDetails(newValue));
}

private void showBookDetails(BookTable book) {
    if (book != null) {
        // Fill the labels with info from the person object.
        bookTitleTextField.setText(book.getBookTitle());
        authorTextField.setText(book.getAuthor());
        publishedTextField.setText(book.getPublished());
//            bookCopiesChoiceBox.setValue(book.getBookCopies());
        categoryChoiceBox.setValue(book.getCategory());
        dateAddedBookTextField.setText(book.getDateAddedBook());
    } else {

    }
}

/**
 * Called when the user clicks on the delete button.
 */
@FXML
private void handleDeleteBook() {

    deleteBook();

}

private void deleteBook() {

    int selectedIndex = bookListTable.getSelectionModel().getSelectedIndex();
    if (selectedIndex >= 0) {
        bookListTable.getItems().remove(selectedIndex);
        bookID.setCellValueFactory(new PropertyValueFactory<BookTable, Integer>("bookID"));
    } else {
        // Nothing selected.
        Alert alert = new Alert(AlertType.WARNING);
        alert.setTitle("No Selection");
        alert.setHeaderText("No Book Selected");
        alert.setContentText("Please select a Book from the table.");

        alert.showAndWait();
    }

}

@Override
public void initialize(URL url, ResourceBundle rb) {
    conn = DBClass.ConnecrDb();
}

}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import javafx.application.Application;
import javafx.collections.ObservableList;
import javafx.fxml.FXMLLoader;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.stage.Stage;


public class Menu extends Application {

public Menu() {


}

@Override
public void start(Stage stage) throws Exception {

    //Code to open the main menu
    Parent root = FXMLLoader.load(getClass().getResource("Menu.fxml"));

    Scene scene = new Scene(root);

    stage.setScene(scene);
    stage.setMaximized(true);
    stage.show();
}

/**
 * @param args the command line arguments
 */
public static void main(String[] args) {
    launch(args);
}

}

import javafx.beans.property.SimpleStringProperty;
import javafx.beans.property.SimpleIntegerProperty;
import javafx.beans.property.StringProperty;
import javafx.beans.property.IntegerProperty;


public class BookTable {
    private final StringProperty category;
    private final StringProperty bookTitle;
    private final StringProperty author;
    private final IntegerProperty bookCopies;
    private final StringProperty published;
    private final StringProperty dateAddedBook;
    private final IntegerProperty bookID;

//public BookTable() {
//        this(1, null, null, null, 1, null, null);
//    }

public BookTable(int bookID, String bookTitle, String author,
    String published, int bookCopies, String category, String dateAddedBook) {

        this.bookID = new SimpleIntegerProperty(bookID);
        this.category = new SimpleStringProperty(category);
        this.bookTitle = new SimpleStringProperty(bookTitle);
        this.author = new SimpleStringProperty(author);
        this.bookCopies = new SimpleIntegerProperty(bookCopies);
        this.published = new SimpleStringProperty(published);
        this.dateAddedBook = new SimpleStringProperty(dateAddedBook);
    }

    public Integer getBookID() {
        return bookID.get();
    }

    public String getCategory() {
        return category.get();
    }

    public void setCategory(String category) {
        this.category.set(category);
    }

    public StringProperty categoryProperty() {
        return category;
    }

    public String getBookTitle() {
        return bookTitle.get();
    }

    public void setBookTitle(String bookTitle) {
        this.bookTitle.set(bookTitle);
    }

    public StringProperty bookTitleProperty() {

        return bookTitle;
    }

    public String getAuthor() {
        return author.get();
    }

    public void setAuthor(String author) {
        this.author.set(author);
    }

    public StringProperty authorProperty() {
        return author;
    }

    public String getPublished() {
        return published.get();
    }

    public void setPublished(String published) {
        this.published.set(published);
    }

    public StringProperty publishedProperty() {
        return published;
    }

    public Integer getBookCopies() {
    return bookCopies.get();
    }

public void setBookCopies(int bookCopies) {
    this.bookCopies.set(bookCopies);
}

public IntegerProperty bookCopiesProperty() {
    return bookCopies;
}

public String getDateAddedBook() {
        return dateAddedBook.get();
    }

    public void setDateAddedBook(String dateAddedBook) {
        this.dateAddedBook.set(dateAddedBook);
    }

    public StringProperty dateAddedBookProperty() {
        return dateAddedBook;
    }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBClass {

    Connection conn = null;

        public static Connection ConnecrDb() {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/codexlms?zeroDateTimeBehavior=convertToNull","root","password");
                System.out.println("Connection success!");
                return conn;
            } catch(Exception e){
                System.out.println(e);
                return null;
            }
        }
}