Day 29-23

package weekDay27;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class Day29G23MethodsCommit {
    private String address = "jdbc:mysql://localhost:3306/db_mng_b8";
    private String userName = "root";
    private String passWord = "";

    public void getAllRows(JTable tableIn) {
        DefaultTableModel tblModel = (DefaultTableModel) tableIn.getModel();
        tblModel.setRowCount(0);
        String sqlQuery = "SELECT * FROM tbl_infolist;";

        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            ResultSet rs = stmt.executeQuery();
            //result set starts with 1 or field name
            while (rs.next()) {
                Object[] newRow = {
                    rs.getInt(1)
                    ,rs.getString(2)
                    ,rs.getString(3)
                    ,rs.getInt(4)
                    ,rs.getDouble(5)
                    ,rs.getString(6)
                    ,rs.getString(7)};
                tblModel.addRow(newRow);
            }
            conn.close();
        } catch (Exception e) {
            Object[] error = new Object[7];
            error[0] = "Connection error:\n" + e.getMessage();
            tblModel.addRow(error);
        }
    }

    public int recordRecieptAndOrder(int rcQty, double rcTotal, JTable tblOrderSource){
        String resultCode = "";
        int rowsAffected = 0;
        boolean willCommit = true;
        int rcID = 0;

        try {
            String sqlQuery = "INSERT INTO tbl_g23_reciept(fld_rec_qty, fld_rec_qtytotal)"
                    + " VALUE (?,?);";
            Connection conn = DriverManager.getConnection(address, userName, passWord);

            conn.setAutoCommit(false);

            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            stmt.setInt(1, rcQty);
            stmt.setDouble(2, rcTotal);

            rowsAffected = stmt.executeUpdate();

            if (rowsAffected != 1) {
                willCommit = false;
                resultCode+= "A";
            }

            sqlQuery = "SELECT fld_recid FROM tbl_g23_reciept"
                    + " WHERE fld_rec_qty = ? AND fld_rec_qtytotal = ?"
                    + " ORDER BY fld_recid DESC;";

            stmt = conn.prepareStatement(sqlQuery);

            stmt.setInt(1, rcQty);
            stmt.setDouble(2, rcTotal);

            ResultSet rs = stmt.executeQuery();

            rs.next();

            rcID = rs.getInt(1);

            if (rcID == 0) {
                willCommit = false;
                resultCode+= "B";
            }

            sqlQuery = "INSERT INTO tbl_g23_orderlst(fld_id, fld_curr_price"
                + ", fld_or_qty, fld_or_qtytotal, fld_recid) VALUES (?,?,?,?,?);";

            for(int i = 0; i < tblOrderSource.getRowCount(); i++){
                int currentPID = Integer.parseInt(tblOrderSource.getValueAt(i, 0).toString());
                double currentPrice = Double.parseDouble(tblOrderSource
                        .getValueAt(i, 1).toString());
                int currentQTY = Integer.parseInt(tblOrderSource.getValueAt(i, 2).toString());
                double currentTotal = Double.parseDouble(tblOrderSource
                        .getValueAt(i, 3).toString());

                stmt = conn.prepareStatement(sqlQuery);

                stmt.setInt(1, currentPID);
                stmt.setDouble(2, currentPrice);
                stmt.setInt(3, currentQTY);
                stmt.setDouble(4, currentTotal);
                stmt.setInt(5, rcID);

                rowsAffected = stmt.executeUpdate();

                if (rowsAffected != 1) {
                    willCommit = false;
                    resultCode+= "C"+i;
                }
            }

            if (willCommit == true) {
                conn.commit();
                resultCode += "_COMPLETE_";
            }

            conn.close();
        } catch (Exception e) {
            rowsAffected = -1;
            System.out.println(e.getMessage());
        }

        System.out.println(resultCode);
        return rowsAffected;
    }


}
//--- form events-------------------------------

    private void btnAddToOrderActionPerformed(java.awt.event.ActionEvent evt) {
        // Taking inspiration from Day22AForm
        Object[] forTblPOut = new Object[5];
        forTblPOut[0] = tblProductList.getValueAt(tblProductList.getSelectedRow(), 0);
        forTblPOut[1] = tblProductList.getValueAt(tblProductList.getSelectedRow(), 4);
        forTblPOut[2] = Integer.parseInt(txtQty.getText());
        Double orTotal = Double.parseDouble(txtQty.getText())
        * Double.parseDouble(tblProductList.getValueAt(tblProductList.getSelectedRow(), 4).toString());

        forTblPOut[3] = String.format("%.4f", orTotal);

        DefaultTableModel tblModel = (DefaultTableModel) tblForOrderRecord.getModel();
        tblModel.addRow(forTblPOut);

        double totalReceipt = 0;
        int itemSold = 0;
        String printout="";
        for(int i = 0; i < tblForOrderRecord.getRowCount(); i++){
            //        JOptionPane.showMessageDialog(this, "awooo");
            itemSold+= Integer.parseInt(tblForOrderRecord.getValueAt(i, 2).toString());
            totalReceipt += Double.parseDouble(tblForOrderRecord.getValueAt(i, 3).toString());
        }
        printout += "Items Sold: " + itemSold;
        printout += "\nTotal Amount: " + totalReceipt;
        JOptionPane.showMessageDialog(this, printout);
        txtTotalQty.setText(String.valueOf(itemSold));
        txtTotalPrice.setText(String.format("%.4f",totalReceipt));
    }

    private void btnCheckOutActionPerformed(java.awt.event.ActionEvent evt) {
        int rcQty = Integer.parseInt(txtTotalQty.getText());
        double rcTotal = Double.parseDouble(txtTotalPrice.getText());

        Day29G23MethodsCommit callG23Methods = new Day29G23MethodsCommit();

        int result = callG23Methods.recordRecieptAndOrder(rcQty, rcTotal, tblForOrderRecord);

        JOptionPane.showMessageDialog(this, "record: " + result);
    }

    private void formWindowOpened(java.awt.event.WindowEvent evt) {
        Day29G23MethodsCommit callG23Methods = new Day29G23MethodsCommit();
        callG23Methods.getAllRows(tblProductList);
    }