Day 29-14

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 Day28G14Methods {
    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 completeProductIn(int prodID, int prodQty, String staffName) {
        String resultCode = "";
        int rowsAffected = 0;
        boolean willCommit = true;

        try {
            String sqlQuery = "INSERT INTO tbl_g14_product_in(fld_id "
                    + ", pin_qty, pin_staff)VALUES(?,?,?);";
            Connection conn = DriverManager.getConnection(address, userName, passWord);

            conn.setAutoCommit(false);

            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            stmt.setInt(1, prodID);
            stmt.setInt(2, prodQty);
            stmt.setString(3, staffName);

            rowsAffected = stmt.executeUpdate();

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

            sqlQuery = "UPDATE tbl_infolist SET fld_num_i ="
                    + " (SELECT fld_num_i + ? FROM tbl_infolist"
                    + " WHERE fld_id = ? LIMIT 1) WHERE fld_id = ? ;";

            stmt = conn.prepareStatement(sqlQuery);

            stmt.setInt(1, prodQty);
            stmt.setInt(2, prodID);
            stmt.setInt(3, prodID);

            rowsAffected = stmt.executeUpdate();

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

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

            conn.close();
        } catch (Exception e) {
            rowsAffected = -1;
            resultCode+= "Z";
            System.out.println(e.getMessage());
        }
        System.out.println(resultCode);
        return rowsAffected;

    }

    public int completeProductOut(int prodID, int prodQty, String staffName){
        String resultCode = "";
        int rowsAffected = 0;
        boolean willCommit = true;
        try {
            String sqlQuery = "INSERT INTO tbl_g14_product_out(fld_id "
                    + ", pout_qty, pout_staff)VALUES(?,?,?)";
            Connection conn = DriverManager.getConnection(address, userName, passWord);

            conn.setAutoCommit(false);

            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

            stmt.setInt(1, prodID);
            stmt.setInt(2, prodQty);
            stmt.setString(3, staffName);

            rowsAffected = stmt.executeUpdate();

            if (rowsAffected != 1) {
                willCommit = false;
                resultCode+= "X";
            }
            int productLess = prodQty * -1;

            sqlQuery = "UPDATE tbl_infolist SET fld_num_i ="
                    + " (SELECT fld_num_i + ? FROM tbl_infolist"
                    + " WHERE fld_id = ? LIMIT 1) WHERE fld_id = ? ;";

            stmt = conn.prepareStatement(sqlQuery);

            stmt.setInt(1, productLess);
            stmt.setInt(2, prodID);
            stmt.setInt(3, prodID);

            rowsAffected = stmt.executeUpdate();

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

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

            conn.close();
        } catch (Exception e) {
            rowsAffected = -1;
            resultCode+= "Z";
            System.out.println(e.getMessage());
        }
        System.out.println(resultCode);
        return rowsAffected;
    }
}

//-----Events------------------------
    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {
        Day28G14Methods callG14Methods = new Day28G14Methods();
        try {
            int prodID = Integer.parseInt(lblProductID.getText());
            int prodOutQty = Integer.parseInt(txtOutCount.getText());
            int currentProdQty = Integer.parseInt(lblCurrQty.getText());
            String staffName = lblStaffName.getText();
            int rowAffected = 0;
            if (currentProdQty > prodOutQty) {

                rowAffected = callG14Methods.completeProductOut(prodID, prodOutQty, staffName);

                if (rowAffected == 1) {
                    JOptionPane.showMessageDialog(this, "Items added", "Product IN", JOptionPane.INFORMATION_MESSAGE);
                    System.out.println("Items Taken Out of Inventory");
                }
            } else {
                JOptionPane.showMessageDialog(this, "quatity is larger than available items");
            }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(this, e.getMessage());
        }
        callG14Methods.getAllRows(tblProductList);
    }


    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
        Day28G14Methods callG14Methods = new Day28G14Methods();
        try {
            int prodID = Integer.parseInt(lblProductID.getText());
            int prodQty = Integer.parseInt(txtInCount.getText());
            String staffName = lblStaffName.getText();
            int rowAffected = 0;

            rowAffected = callG14Methods.completeProductIn(prodID, prodQty, staffName);

            if (rowAffected == 1) {
                JOptionPane.showMessageDialog(this, "Items added", "Product IN", JOptionPane.INFORMATION_MESSAGE);
                System.out.println("Items added");
            }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(this, e.getMessage());
        }
        callG14Methods.getAllRows(tblProductList);
    }