Provider

package application;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

import javafx.beans.property.SimpleObjectProperty;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.scene.Scene;
import javafx.scene.control.Alert;
import javafx.scene.control.Button;
import javafx.scene.control.ButtonType;
import javafx.scene.control.CheckBox;
import javafx.scene.control.ChoiceBox;
import javafx.scene.control.ChoiceDialog;
import javafx.scene.control.ScrollPane;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextField;
import javafx.scene.control.TextInputDialog;
import javafx.scene.control.Alert.AlertType;
import javafx.scene.control.ButtonBar.ButtonData;
import javafx.scene.control.TableColumn.CellDataFeatures;
import javafx.scene.input.MouseEvent;
import javafx.scene.layout.HBox;
import javafx.scene.layout.VBox;
import javafx.stage.Stage;
import javafx.util.Callback;

public class Provider extends Stage {
    public Provider(Stage prev) {
        Stage current = this;
        ObservableList<ObservableList> data;
        TableView tableview = new TableView();
        java.sql.Connection c;
        data = FXCollections.observableArrayList();
        try{
          c = DriverManager.getConnection("jdbc:mysql://localhost:3306/company?useSSL=false","root", "ahmed666");
          //SQL FOR SELECTING ALL OF CUSTOMER
          String SQL;
          PreparedStatement stm ;

          SQL = "select * from provider ";
          stm = (PreparedStatement) c.prepareStatement(SQL);



          //ResultSet
          ResultSet rs = stm.executeQuery();

          /**********************************
           * TABLE COLUMN ADDED DYNAMICALLY *
           **********************************/
          for(int i=0 ; i<rs.getMetaData().getColumnCount(); i++){
              //We are using non property style for making dynamic table
              final int j = i;
              TableColumn col = new TableColumn(rs.getMetaData().getColumnName(i+1));
              col.setCellValueFactory(new Callback<CellDataFeatures<ObservableList,String>,ObservableValue<String>>(){
                  public ObservableValue<String> call(CellDataFeatures<ObservableList, String> param) {
                      return new SimpleObjectProperty(param.getValue().get(j));
                  }
              });

              tableview.getColumns().addAll(col);
              System.out.println("Column ["+i+"] ");
          }

          /********************************
           * Data added to ObservableList *
           ********************************/
          while(rs.next()){
              //Iterate Row
              ObservableList<String> row = FXCollections.observableArrayList();
              for(int i=1 ; i<=rs.getMetaData().getColumnCount(); i++){
                  //Iterate Column
                  row.add(rs.getString(i));
              }

              System.out.println("Row [1] added "+row );
              data.add(row);

          }

          //FINALLY ADDED TO TableView
          tableview.setItems(data);

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

        VBox root = new VBox();
        tableview.setMinSize(800, 550);
        TextField providerName = new TextField();
        Button back = new Button("back");
        back.setStyle("-fx-background-color: pink; -fx-text-fill: black;");
        Button addProvider = new Button("Add Provider");
        addProvider.setStyle("-fx-background-color: pink; -fx-text-fill: black;");
        HBox box = new HBox(50);
        box.getChildren().add(providerName);
        box.getChildren().add(addProvider);
        root.getChildren().add(box);
        root.getChildren().add(tableview);
        root.getChildren().add(back);

        ScrollPane sp  = new ScrollPane();
        sp.setFitToWidth(true);
        sp.setFitToHeight(true);
        sp.setPrefSize(800, 600);
        sp.setContent(root);
        Scene scene = new Scene(sp);

        setScene(scene);




        back.setOnAction(new EventHandler<ActionEvent>() {
            @Override
            public void handle(ActionEvent event) {
                prev.show();
                current.close();
                // Displaying the contents of the stage



            }
        });

        tableview.setOnMousePressed(new EventHandler<MouseEvent>() {
            @Override
            public void handle(MouseEvent event) {
                if (event.isPrimaryButtonDown() && event.getClickCount() == 2) {




                    ObservableList row  = (ObservableList) tableview.getSelectionModel().getSelectedItem();
                    String ID =  (String) row.get(0);
                    String status =(String) row.get(2);

                    java.sql.Connection c;

                    try {
                          c = DriverManager.getConnection("jdbc:mysql://localhost:3306/company?useSSL=false","root", "ahmed666");
                          String SQL;
                          PreparedStatement stm ;
                          if(status.equals("Active")) {
                             Alert alert = new Alert(AlertType.CONFIRMATION);
                            //alert.setTitle("Confirmation Dialog with Custom Actions");
                            alert.setHeaderText("provider options");
                            alert.setContentText("Choose your option.");

                            ButtonType buttonTypeOne = new ButtonType("Stop provider");
                            ButtonType buttonTypeCancel = new ButtonType("Cancel", ButtonData.CANCEL_CLOSE);
                            ButtonType buttonTypeRename = new ButtonType("Rename");
                            ButtonType buttonTypeProvider = new ButtonType("More...");
                            alert.getButtonTypes().setAll(buttonTypeOne,buttonTypeProvider
                                    ,buttonTypeRename,buttonTypeCancel);

                            Optional<ButtonType> result = alert.showAndWait();
                            if (result.get() == buttonTypeOne){
                                // ... user chose "One"
                                SQL = "delete from patientServiceProvider where providerID = "+ID;
                                stm = (PreparedStatement) c.prepareStatement(SQL);
                                stm.execute();
                                SQL = "update provider set status ='Stop' where PID = "+ID;
                                stm = (PreparedStatement) c.prepareStatement(SQL);
                                stm.execute();
                                Provider s = new Provider(prev);
                                s.show();
                                current.close();
                            }else if(result.get() == buttonTypeProvider) {
                                // More....
                                 ProviderInfo obj;
                                 obj = new ProviderInfo(current,Integer.parseInt(ID));
                                 current.close();
                                 obj.show();
                                 System.out.println("The Id is "+ID);




                            }else if(result.get() == buttonTypeRename) {
                                TextInputDialog dialog = new TextInputDialog("Provider Name");
                                dialog.setTitle("New Name");
                                dialog.setHeaderText("Look, a Text Input Dialog");
                                dialog.setContentText("Please enter new name:");

                                // Traditional way to get the response value.
                                Optional<String> result2 = dialog.showAndWait();
                                if (result2.isPresent()){
                                    SQL = "select * from provider where PName = '"+result2.get()+"'";
                                    stm = (PreparedStatement) c.prepareStatement(SQL);
                                    ResultSet rs = stm.executeQuery();
                                    if(rs.next()) {

                                    }
                                    else {
                                         SQL = "update provider set PName = '"+result2.get()+"' where PID ="+ID;
                                        stm = (PreparedStatement) c.prepareStatement(SQL);
                                        stm.execute();
                                       Provider s = new Provider(prev);
                                        s.show();
                                        current.close();

                                    }

                                }

                            }
                            else {
                                // ... user chose CANCEL or closed the dialog
                            }
                          }else {
                            Alert alert = new Alert(AlertType.CONFIRMATION);
                            //alert.setTitle("Confirmation Dialog with Custom Actions");
                            alert.setHeaderText("Provider options");
                            alert.setContentText("Choose your option.");

                            ButtonType buttonTypeOne = new ButtonType("activate provider");
                            ButtonType buttonTypeProvider = new ButtonType("More...");
                            ButtonType buttonTypeCancel = new ButtonType("Cancel", ButtonData.CANCEL_CLOSE);

                            alert.getButtonTypes().setAll(buttonTypeOne,buttonTypeProvider,buttonTypeCancel);

                            Optional<ButtonType> result = alert.showAndWait();
                            if (result.get() == buttonTypeOne){
                                // ... user chose "One"
                                SQL = "update provider set status ='Active' where PID = "+ID;
                                stm = (PreparedStatement) c.prepareStatement(SQL);
                                stm.execute();
                                Provider s = new Provider(prev);
                                s.show();
                                current.close();
                            }
                            else if (result.get() == buttonTypeProvider) {
                                 ProviderInfo obj;
                                 obj = new ProviderInfo(current,Integer.parseInt(ID));
                                 current.close();
                                 obj.show();
                                 System.out.println("The Id is "+ID);
                            }
                            else {
                                // ... user chose CANCEL or closed the dialog
                            }

                          }

                    }catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }




                }
            }
        });















        addProvider.setOnAction(new EventHandler<ActionEvent>() {
            @Override
            public void handle(ActionEvent event) {
                String Name = providerName.getText();
                try {
                    int ID = idNumber(Name);
                    if(ID == -1) {
                        Alert alert = new Alert(AlertType.WARNING, "This name is already taken", ButtonType.OK);
                        alert.showAndWait();
                    }else {
                        java.sql.Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/company?useSSL=false","root", "ahmed666");;
                        String SQL = "Insert into provider values ( "+ID+" , '"+Name+"' ,'Active' )";
                        PreparedStatement stm  = (PreparedStatement) conn.prepareStatement(SQL);
                        stm.executeUpdate();
                        Provider provider = new Provider(prev);
                        provider.show();
                        current.close();

                    }
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                // Displaying the contents of the stage



            }
        });


    }

    private int idNumber(String Name) throws SQLException {
        boolean found=false;
        int id=-1;
        java.sql.Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/company?useSSL=false","root", "ahmed666");
        String search="select * from provider where PNAME = ?";
        PreparedStatement searchStmt = conn.prepareStatement(search);
        searchStmt.setString(1, Name);
        ResultSet result=searchStmt.executeQuery();

        while(  result.next()) {
             found= true ;
             break;

        }
        if(!found) {
            String getMaxId="select MAX(PID) as MID from provider";
            PreparedStatement getMaxStmt = conn.prepareStatement(getMaxId);
            ResultSet getMaxRes=getMaxStmt.executeQuery();

            while(  getMaxRes.next()) {
                id= getMaxRes.getInt("MID")+1 ;

            }
        }
        return id;


    }

}