package TreeTableView;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.ResourceBundle;
import com.jfoenix.controls.RecursiveTreeItem;

import com.jfoenix.controls.datamodels.treetable.RecursiveTreeObject;
import javafx.beans.property.SimpleIntegerProperty;
import javafx.beans.property.SimpleStringProperty;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.TreeItem;
import javafx.scene.control.TreeTableColumn;
import javafx.scene.control.TreeTableView;
import javafx.util.Callback;
    public class FXMLDocument implements Initializable{

    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement pst = null;
    Integer idjobs = 286; //temp
    Integer idjobs_by = 6; //temp
    Integer loginID = 4;

    @FXML
    private TreeTableView<ER_Report> tableview;
    ObservableList<ER_Report> ER_report = FXCollections.observableArrayList();

    @FXML
    private TreeTableColumn<ER_Report, Number> col1;

    @FXML
    private TreeTableColumn<ER_Report, String> col2;

    @FXML
    private TreeTableColumn<ER_Report, String> col3;

    @FXML
    private TreeTableColumn<ER_Report, String> col4;

    TreeItem<ER_Report> root;

    public void dataset() {
        String sql = "SELECT * FROM expensereport_total_withlist where job = '" + idjobs + "' and jobby = '" + idjobs_by + "' and employee  = '" + loginID + "' "
                + "order by newSort, root";
        try {
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while(rs.next()) {
                DecimalFormat df2 = new DecimalFormat("###0.00");
                DecimalFormat df3 = new DecimalFormat("###0.000");

            Integer c_rootI = 0;
            Date c_Date;
            Integer c_WorkedDayI = 0;
            String c_MealsS = "";
            String c_AirfareS = "";

    c_rootI = rs.getInt("root");
    String c_rootS = c_rootI.toString();
    c_Date = rs.getDate("newSort");
    String c_DateS = c_Date.toString();
    c_WorkedDayI = rs.getInt("workedDay");
    String c_WorkedDayS = c_WorkedDayI.toString();
    Double c_MealsD = rs.getDouble("Meals");
           if(c_MealsD > 0) {
               c_MealsS = "$ " + df2.format(c_MealsD) ;
           }
    Double c_airD = rs.getDouble("Meals");
        if(c_airD > 0) {
            c_AirfareS = "$ " + df2.format(c_airD) ;
               }

        ER_report.add(new ER_Report(c_rootI, c_DateS,c_MealsS,c_AirfareS));
            }
            final TreeItem<ER_Report> root = new RecursiveTreeItem<ER_Report>(ER_report, RecursiveTreeObject::getChildren);


            col1.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<ER_Report, Number>, ObservableValue<Number>>() {
                @Override
                public ObservableValue<Number> call(TreeTableColumn.CellDataFeatures<ER_Report, Number> param) {
                    return param.getValue().getValue().rootProperty;
                }
            });
            col2.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<ER_Report, String>, ObservableValue<String>>() {
                @Override
                public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<ER_Report, String> param) {
                    return param.getValue().getValue().newDateProperty;
                }
            });
            col3.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<ER_Report, String>, ObservableValue<String>>() {
                @Override
                public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<ER_Report, String> param) {
                    return param.getValue().getValue().mealsProperty;
                }
            });
            col4.setCellValueFactory(new Callback<TreeTableColumn.CellDataFeatures<ER_Report, String>, ObservableValue<String>>() {
                @Override
                public ObservableValue<String> call(TreeTableColumn.CellDataFeatures<ER_Report, String> param) {
                    return param.getValue().getValue().airProperty;
                }
            });


            root.getChildren().setAll(root);
            tableview.setRoot(root);
    }catch(Exception e) {
         e.printStackTrace();
    }
        root = new TreeItem<>(new ER_Report(0, "Name", "None", "None"));
    }

    @Override
    public void initialize(URL arg0, ResourceBundle arg1) {
        conn = connector.ConnectDb();

        idjobs = 286; //temp
        idjobs_by = 6; //temp
        dataset();
    }

    class ER_Report extends RecursiveTreeObject<ER_Report>{
        SimpleIntegerProperty rootProperty;
        SimpleStringProperty newDateProperty;
        SimpleStringProperty mealsProperty;
        SimpleStringProperty airProperty;

        public ER_Report(Integer root, String newDate, String meals, String air) {
            this.rootProperty = new SimpleIntegerProperty(root);
            this.newDateProperty = new SimpleStringProperty(newDate);
            this.mealsProperty = new SimpleStringProperty(meals);
            this.airProperty = new SimpleStringProperty(air);
        }

        /**
         * @return the rootProperty
         */
        public SimpleIntegerProperty getRootProperty() {
            return rootProperty;
        }

        /**
         * @param rootProperty the rootProperty to set
         */
        public void setRootProperty(SimpleIntegerProperty rootProperty) {
            this.rootProperty = rootProperty;
        }

        /**
         * @return the newDateProperty
         */
        public SimpleStringProperty getNewDateProperty() {
            return newDateProperty;
        }

        /**
         * @param newDateProperty the newDateProperty to set
         */
        public void setNewDateProperty(SimpleStringProperty newDateProperty) {
            this.newDateProperty = newDateProperty;
        }

        /**
         * @return the mealsProperty
         */
        public SimpleStringProperty getMealsProperty() {
            return mealsProperty;
        }

        /**
         * @param mealsProperty the mealsProperty to set
         */
        public void setMealsProperty(SimpleStringProperty mealsProperty) {
            this.mealsProperty = mealsProperty;
        }

        /**
         * @return the airProperty
         */
        public SimpleStringProperty getAirProperty() {
            return airProperty;
        }

        /**
         * @param airProperty the airProperty to set
         */
        public void setAirProperty(SimpleStringProperty airProperty) {
            this.airProperty = airProperty;
        }


    }
}

newSort root    Meals   Airfare
6/22/2018   1   94.16   756.46
6/22/2018   2   NULL    756.46
6/22/2018   2   38.53   NULL
6/22/2018   2   55.63   NULL
6/27/2018   1   6.92    NULL
6/27/2018   2   3.63    NULL
6/27/2018   2   3.29    NULL
6/29/2018   1   NULL    698.9
6/29/2018   2   NULL    698.9

public void rowchecker() {
        Callback<TableColumn<ExpenseReportTable, Integer>, TableCell<ExpenseReportTable, Integer>> cellFactory
        = new Callback<TableColumn<ExpenseReportTable, Integer>, TableCell<ExpenseReportTable, Integer>>() {
public TableCell call(TableColumn p) {
    TableCell cell = new TableCell<ExpenseReportTable, Integer>() {
        @Override
        public void updateItem(Integer item, boolean empty) {
            super.updateItem(item, empty);
            if (!empty) {
                  TableRow ttr = getTableRow();
                  if (item == null || empty){
                      setText(null);
                      ttr.setStyle("");
                      setStyle("");
                  } else {
                      ttr.setStyle(item.doubleValue() > 0
                              ? "-fx-background-color:lightgreen"
                              : "-fx-background-color:#FFE793");
                      setText(item.toString());
                      setStyle(item.doubleValue() > 0
                              ? "-fx-background-color:green"
                              : "-fx-background-color:lightgreen");
                  }
            } else {
                setText(null);
            }
        }

        private String getString() {
            return getItem() == null ? "" : getItem().toString();
        }
    };

    return cell;
}
};
col_NonWorkedDate.setCellFactory(cellFactory);

    }