DAY 27

-- para sa group 2 and 3 sample

CREATE TABLE tbl_g23_orderlst (
    fld_orid INT AUTO_INCREMENT PRIMARY KEY
    , fld_id INT NOT NULL
    , fld_curr_price DECIMAL(20,4) NOT NULL
    , fld_or_qty INT NOT NULL
    , fld_or_qtytotal DECIMAL(20,4) NOT NULL
    , fld_recid INT NOT NULL
);

CREATE TABLE tbl_g23_reciept (
    fld_recid INT AUTO_INCREMENT PRIMARY KEY
    , fld_tr_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
    , fld_rec_qty INT NOT NULL
    , fld_rec_qtytotal DECIMAL(20,4) NOT NULL
);

INSERT INTO tbl_g23_orderlst(fld_id, fld_curr_price, fld_or_qty, fld_or_qtytotal, fld_recid) VALUES (?,?,?,?);

INSERT INTO tbl_g23_reciept(fld_rec_qty, fld_rec_qtytotal) VALUE (?,?);

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

-- RECORD RESIBO
-- KUKUNIN NUMBER NG RESIBO
-- RECORD BINILI


-- para sa group 1 and 4 sample
CREATE TABLE tbl_g14_product_in(
    pIN_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  ,fld_id INT NOT NULL
  ,pin_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
  ,pin_qty INT NOT NULL
  ,pin_staff VARCHAR(30) NOT NULL
);


CREATE TABLE tbl_g14_product_out(
  pOUT_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  ,fld_id INT NOT NULL
  ,pout_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
  ,pout_qty INT NOT NULL
  ,pout_staff VARCHAR(30) NOT NULL
  );

-- tbl_infolist is proxy for your main table
-- group 1 fld_num_i is PROXY for your tbl_Product_list quantity_in_stock field
-- kayo na bahala sa date
INSERT INTO tbl_g14_product_in(fld_id ,pin_qty, pin_staff)VALUES(?,?,?,?);
-- group 1 follow up for INSERT INTO tbl_product_in
UPDATE tbl_infolist SET fld_num_i = (SELECT fld_num_i + ? FROM tbl_infolist WHERE fld_id = ? LIMIT 1) WHERE fld_id = ?;

--sample in action assuming product id 12 have incoming 20 items
INSERT INTO tbl_g14_product_in(fld_id, pin_qty, pin_staff)VALUES(12,20,'Elbert');
UPDATE tbl_infolist
SET fld_num_i = (SELECT fld_num_i + 20 FROM tbl_infolist WHERE fld_id = 12 LIMIT 1))
WHERE fld_id = 12;
-- end sample

-- what about product going out
-- need check of there is available number of items
SELECT fld_num_i FROM tbl_infolist WHERE fld_id = ?;
-- gagawa ka ng if statement sa java if fld_num_i > pout_qty
-- di gagana ang out kung mag ne negative ang quantity
-- kayo na bahala sa date
INSERT INTO tbl_g14_product_out(fld_id , pout_qty, pout_staff)VALUES(?,?,?);

-----------------------------------------------
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 Day27G14Methods {
    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);
        }
    }

// for incoming
    public int recordProductIn(int prodID, int prodQty, String staffName) {
        int rowsAffected = 0;
        String sqlQuery = "INSERT INTO tbl_g14_product_in(fld_id ,pin_qty, pin_staff)VALUES(?,?,?);";

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

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

            rowsAffected = stmt.executeUpdate();

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


        return rowsAffected;
    }

    public int updateQuantityField (int prodID, int prodQty) {
        int rowsAffected = 0;
        String sqlQuery = "UPDATE tbl_infolist"
                + " SET fld_num_i = (SELECT fld_num_i + ? FROM tbl_infolist WHERE fld_id = ? LIMIT 1)"
                + " WHERE fld_id = ?;";
        System.out.println(sqlQuery);
        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

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

            rowsAffected = stmt.executeUpdate();

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

        return rowsAffected;
    }

// for outgoing
    public int recordProductOut(int prodID, int prodQty, String staffName) {
        int rowsAffected = 0;
        String sqlQuery = "INSERT INTO tbl_g14_product_out(fld_id , pout_qty, pout_staff)VALUES(?,?,?)";
        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

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

            rowsAffected = stmt.executeUpdate();

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

        return rowsAffected;
    }

}

------------------------------------------------

    private void formWindowOpened(java.awt.event.WindowEvent evt) {
        randoName();
        Day27G14Methods callG14Methods = new Day27G14Methods();
        callG14Methods.getAllRows(tblProductList);
    }

    private void tblProductListMouseReleased(java.awt.event.MouseEvent evt) {
        lblProductID.setText(tblProductList.getValueAt(tblProductList.getSelectedRow(), 0).toString());
        lblCurrQty.setText(tblProductList.getValueAt(tblProductList.getSelectedRow(), 3).toString());
    }

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

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

            if (rowAffected == 1) {
                rowAffected = callG14Methods.updateQuantityField(prodID, prodQty);
                if (rowAffected == 1) {
                    JOptionPane.showMessageDialog(this, "Incoming product recorded");
                } else {
                    JOptionPane.showMessageDialog(this, "update fail");
                }
            } else {
                JOptionPane.showMessageDialog(this, "insert fail");
            }

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

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {
        Day27G14Methods callG14Methods = new Day27G14Methods();
        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.recordProductOut(prodID, prodOutQty, staffName);

                if (rowAffected == 1) {
                    int productLess = prodOutQty * -1;
                    rowAffected = callG14Methods.updateQuantityField(prodID, productLess);
                    if (rowAffected == 1) {
                        JOptionPane.showMessageDialog(this, "Outgoing product recorded");
                    } else {
                        JOptionPane.showMessageDialog(this, "update fail");
                    }
                } else {
                    JOptionPane.showMessageDialog(this, "insert fail");
                }
            }

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