Natour, O. 5/17/16 Csc-112 Phase 2 Final

/*
 * Omar Natour
 * 5/10/16
 * Csc-112 Java 2
 * Final Exam Phase 2
 * Convert Zipcode into city and state, Convert city and state into Zipcode. All this using databases.
 * ojnatour0001@student.stcc.edu
 */

import java.sql.*;
import javafx.scene.Node;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.TextArea;
import javafx.scene.control.TextField;
import javafx.scene.input.KeyEvent;
import javafx.scene.layout.BorderPane;
import javafx.scene.layout.HBox;
import javafx.scene.layout.VBox;
import javafx.scene.shape.Rectangle;
import javafx.scene.text.Text;
import javafx.stage.Stage;
import javafx.application.Application;
import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.geometry.Pos;

public class ZipCodeGui extends Application {

    public static void main(String[] args){
        launch(args);
    }

    BorderPane Gui = new BorderPane();

    public void start(Stage primaryStage){

        Gui.setTop(ZipToCity());
        Gui.setCenter(CityToZip());
        Gui.setBottom(Status());

        Scene sce = new Scene(Gui, 650, 300);

        primaryStage.setTitle("Zip Code");
        primaryStage.setScene(sce);
        primaryStage.show();
        primaryStage.setResizable(false);
    }

     TextField tfStatus = new TextField();
     TextField tfCity = new TextField();
     TextField tfState = new TextField();
     TextArea taZipCode = new TextArea("Zip Code(s)");

    private  Node ZipToCity() {

        VBox vbZTC = new VBox();

        HBox HBztcTop = new HBox(10);
        HBztcTop.setPrefHeight(100);

        Text tZipCode = new Text("Zip Code:");
        TextField tfZipCode = new TextField();
        Button btZTC = new Button("Zip to City");
        Text tCity = new Text("City:");
        Text tState = new Text("State:");

        tfZipCode.addEventFilter(KeyEvent.KEY_TYPED, maxLength(5));
        tfZipCode.setPrefColumnCount(6);
        tfState.setPrefColumnCount(2);

        tfCity.setEditable(false);
        tfCity.setFocusTraversable(false);

        tfState.setEditable(false);
        tfState.setFocusTraversable(false);

        EventHandler<ActionEvent> ZipToCity = e->{
            String Zip = tfZipCode.getText();
            if(Zip.matches("(\\d{5})"))
                Retrive(Zip,null,null);
            else{
                tfStatus.setText("Invalid input (Zip To City)");
                tfCity.setText("");
                tfState.setText("");
            }
        };

        btZTC.setOnAction(ZipToCity);
        tfZipCode.setOnAction(ZipToCity);

        Rectangle divider = new Rectangle();
        divider.setWidth(595);
        divider.setHeight(2);

        HBztcTop.setAlignment(Pos.CENTER);
        HBztcTop.getChildren().addAll(tZipCode, tfZipCode, btZTC, tCity, tfCity, tState, tfState);

        vbZTC.setAlignment(Pos.CENTER);
        vbZTC.getChildren().addAll(HBztcTop, divider);
        return vbZTC;
    }

    private Node CityToZip() {
        VBox vbCTZ = new VBox();

        HBox HBctzTop = new HBox(10);
        HBctzTop.setPrefHeight(140);

        Button btCTZ = new Button("City to Zip");
        Text tCity = new Text("City:");
        TextField tfCity = new TextField();
        Text tState = new Text("State:");
        TextField tfState = new TextField();

        taZipCode.setPrefColumnCount(6);
        taZipCode.setMaxHeight(120);
        tfCity.setPrefColumnCount(25);
        tfCity.addEventFilter(KeyEvent.KEY_TYPED, maxLength(25));
        tfState.setPrefColumnCount(2);
        tfState.addEventFilter(KeyEvent.KEY_TYPED, maxLength(2));

        taZipCode.setEditable(false);
        taZipCode.setFocusTraversable(false);

        EventHandler<ActionEvent> CityToZip = e -> {

            String City = tfCity.getText().trim();
            City = City.replaceAll("\\s{2,}"," ");

            String State = tfState.getText();

            if(City.matches("(^([a-zA-Z]+ ?)+$)") &&
               State.matches("^[a-zA-Z]{2}$")){
                Retrive("",City,State);
            }
            else{
                tfStatus.setText("Invalid input (City To Zip)");
                taZipCode.setText("Zip Code(s)");
            }
        };

        btCTZ.setOnAction(CityToZip);
        tfCity.setOnAction(CityToZip);
        tfState.setOnAction(CityToZip);

        Rectangle divider = new Rectangle();
        divider.setWidth(595);
        divider.setHeight(2);

        HBctzTop.getChildren().addAll( tCity, tfCity, tState, tfState, btCTZ, taZipCode);
        HBctzTop.setAlignment(Pos.CENTER);

        vbCTZ.setAlignment(Pos.TOP_CENTER);
        vbCTZ.getChildren().addAll(HBctzTop, divider);

        return vbCTZ;
    }

    private Node Status() {
        HBox hbStatus = new HBox(15);

        Text tStatus = new Text("Status:");

        tfStatus.setEditable(false);
        tfStatus.setFocusTraversable(false);
        tfStatus.setPrefColumnCount(35);

        hbStatus.getChildren().addAll(tStatus, tfStatus);
        hbStatus.setAlignment(Pos.CENTER);
        hbStatus.setPrefHeight(80);

        return hbStatus;
    }

    // Credit to "Magcus" on Stackoverflow.com (http://tinyurl.com/jqkb2ky) for this method of limiting characters in text field.
    public EventHandler<KeyEvent> maxLength(final Integer i) {
        return new EventHandler<KeyEvent>() {

            @Override
            public void handle(KeyEvent arg0) {

                TextField tx = (TextField) arg0.getSource();
                if (tx.getText().length() >= i) {
                    arg0.consume();
                }
            }
        };
    }

    private void Retrive(String Zip, String City, String State){

        String Database ="silvestri";
        String Username ="readonly";
        String Password ="readonly";

        String Driver = "com.mysql.jdbc.Driver";
        String Url = "jdbc:mysql://cs.stcc.edu/" + Database + "?user=" + Username + "&password=" + Password;

        try{
            Class.forName(Driver).newInstance();
            Connection conn = DriverManager.getConnection(Url);

            if(!(Zip.isEmpty())){

                String zipCommand = ("Select city, state from Zipcodes where zipcode ='" + Zip +"';");
                PreparedStatement statement = conn.prepareStatement(zipCommand);
                ResultSet resultset = statement.executeQuery(zipCommand);

                if(resultset.next()){

                tfCity.setText(resultset.getString(1));
                tfState.setText(resultset.getString(2));
                tfStatus.setText("Retrieval from Zipcodes table successful!");
                }
                else{
                    tfStatus.setText("Zipcode dosn't exist.");
                    tfCity.setText("");
                    tfState.setText("");
                }

            }
            else{

                String cityCommand = ("Select zipcode from Zipcodes where state ='" + State + "' && city ='" + City +"';");
                PreparedStatement statement = conn.prepareStatement(cityCommand);
                ResultSet resultset = statement.executeQuery(cityCommand);

                String Zips = "";
                int cout =0;

                if(resultset.next()){
                    resultset.previous();

                    while(resultset.next()){
                        Zips += (resultset.getString(1) + "\n");
                        cout++;
                    }

                    tfStatus.setText("Retrieval from Zipcodes table successful! " + cout + " Zipcodes found.");
                    taZipCode.setText(Zips);
                }
                else{

                    tfStatus.setText("City or State dosn't exist.");
                    taZipCode.setText("Zip Code(s)");
                }
            }

            conn.close();

        }catch(Exception e){
            tfStatus.setText(e.getMessage());
        }
    }
}