Day 27 2n3

-- 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(?,?,?);

--Methods-------------------------------------------
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 Day27G23Methods {
    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 addNewOrder(int prodID, double prodPrice, int prodQty
            , double prodTotal, int recID){
        int rowsAffected = 0;
        String sqlQuery = "INSERT INTO tbl_g23_orderlst(fld_id, fld_curr_price"
                + ", fld_or_qty, fld_or_qtytotal, fld_recid) VALUES (?,?,?,?,?);";

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

            stmt.setInt(1, prodID);
            stmt.setDouble(2, prodPrice);
            stmt.setInt(3, prodQty);
            stmt.setDouble(4, prodTotal);
            stmt.setInt(5, recID);

            rowsAffected = stmt.executeUpdate();

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

        return rowsAffected;
    }

//-=-=-=-=-=-=-=-=-=-=-=

    public int addNewReciept(int rcQty, double rcTotal){
        int rowsAffected = 0;
        String sqlQuery = "INSERT INTO tbl_g23_reciept(fld_rec_qty, fld_rec_qtytotal) VALUE (?,?);";

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

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

            rowsAffected = stmt.executeUpdate();

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

        return rowsAffected;
    }

    public int getRecieptID(int rcQty, double rcTotal) {
        int rcID = 0;
        String sqlQuery = "SELECT fld_recid FROM tbl_g23_reciept WHERE fld_rec_qty = ? AND fld_rec_qtytotal = ? ORDER BY fld_recid DESC;";
        // kukuha ng kelangan
        try {
            Connection conn = DriverManager.getConnection(address, userName, passWord);
            PreparedStatement stmt = conn.prepareStatement(sqlQuery);

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

            ResultSet rs = stmt.executeQuery();

            rs.next();

            rcID = rs.getInt(1);

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

        return rcID;

    }
}
--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 formWindowOpened(java.awt.event.WindowEvent evt) {
        Day27G23Methods callG23Methods = new Day27G23Methods();
        callG23Methods.getAllRows(tblProductList);
    }

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

        int result= 0;
        result = callG23Methods.addNewReciept(rcQty, rcTotal);
        if (result == 1) {
            receiptNumber = callG23Methods.getRecieptID(rcQty, rcTotal);
            txtRecieptNum.setText(String.valueOf(receiptNumber));
            jTextArea1.setText(jTextArea1 + "\n-reciept " + receiptNumber);
        }
            JOptionPane.showMessageDialog(this, "reciept " + receiptNumber);
        if (receiptNumber > 0) {
            for(int i = 0; i < tblForOrderRecord.getRowCount(); i++){
    //        JOptionPane.showMessageDialog(this, "awooo");
                int currentPID = Integer.parseInt(tblForOrderRecord.getValueAt(i, 0).toString());
                double currentPrice = Double.parseDouble(tblForOrderRecord.getValueAt(i, 1).toString());
                int currentQTY = Integer.parseInt(tblForOrderRecord.getValueAt(i, 2).toString());
                double currentTotal = Double.parseDouble(tblForOrderRecord.getValueAt(i, 3).toString());
                int orderRec = callG23Methods.addNewOrder(currentPID, currentPrice, currentQTY, currentTotal, receiptNumber);
                if (orderRec == 1) {
                    jTextArea1.setText(jTextArea1.getText() + "\n-recorded " + i);
                }
            }
        }
    }