Stock Fifo

gistfile1.txt

CREATE DATABASE stkfifo;
USE stkfifo;
CREATE TABLE items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  CODE VARCHAR(20) NOT NULL,
  NAME VARCHAR(100) NOT NULL,
  quantity_on_hand DECIMAL(10,2) NOT NULL DEFAULT 0,
  remarks TEXT NULL
) ;
 
CREATE TABLE transaction_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  CODE VARCHAR(20) NOT NULL,
  NAME VARCHAR(100) NOT NULL
) ;
 
CREATE TABLE transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type_id INT NOT NULL,
  trans_code VARCHAR(20) NOT NULL,
  trans_date VARCHAR(100) NOT NULL,
  remarks TEXT NULL,
  FOREIGN KEY(type_id) REFERENCES transaction_types(id)
) ;
 
CREATE TABLE transaction_details (
  id INT AUTO_INCREMENT PRIMARY KEY,
  trans_id INT NOT NULL,
  item_id INT NOT NULL,
  quantity DECIMAL(10,2) NOT NULL DEFAULT 0,
  remarks TEXT NULL, 
  FOREIGN KEY (trans_id) REFERENCES transactions(id), 
  FOREIGN KEY (item_id) REFERENCES items(id) 
) ;

-- http://pojokprogrammer.net/content/aplikasi-inventory-sederhana-analisa-dan-desain-database

gistfile2.txt

DELIMITER $$
 
DROP TRIGGER IF EXISTS trg_transaction_details_after_insert$$
-- -----------------------------
-- after insert trigger
-- -----------------------------
CREATE TRIGGER trg_transaction_details_after_insert
AFTER INSERT ON transaction_details
FOR EACH ROW
BEGIN
  -- -----------------------------
  -- ambil jenis transaksi
  -- -----------------------------
  DECLARE tipe VARCHAR(20);
  SET tipe = (
    SELECT tt.code
      FROM transactions tr
      JOIN transaction_types tt ON tr.type_id=tt.id
      WHERE tr.id=new.trans_id
  ) ;
  -- -----------------------------
  -- update sesuai jenis transaksi
  -- -----------------------------
  UPDATE items
    SET quantity_on_hand
        = quantity_on_hand
        + CASE WHEN tipe='IN' THEN new.quantity
               WHEN tipe='OUT' THEN -new.quantity
               ELSE 0 END
    WHERE items.id=new.item_id ;
END$$
  
DROP TRIGGER IF EXISTS trg_transaction_details_after_update$$
-- -----------------------------
-- after update trigger
-- -----------------------------
CREATE TRIGGER trg_transaction_details_after_update
AFTER UPDATE ON transaction_details
FOR EACH ROW
BEGIN
  -- -----------------------------
  -- ambil jenis transaksi
  -- -----------------------------
  DECLARE tipe VARCHAR(20);
  SET tipe = (
    SELECT tt.code
      FROM transactions tr
      JOIN transaction_types tt ON tr.type_id=tt.id
      WHERE tr.id=old.trans_id
  ) ;
  -- -----------------------------
  -- update sesuai jenis transaksi
  -- => kurangi dengan old quantity
  -- -----------------------------
  UPDATE items
    SET quantity_on_hand
        = quantity_on_hand
        + CASE WHEN tipe='IN' THEN -old.quantity
               WHEN tipe='OUT' THEN old.quantity
               ELSE 0 END
    WHERE items.id=old.item_id ;
  -- -----------------------------
  -- update sesuai jenis transaksi
  -- => tambahkan dengan new quantity
  -- -----------------------------
  UPDATE items
    SET quantity_on_hand
        = quantity_on_hand
        + CASE WHEN tipe='IN' THEN new.quantity
               WHEN tipe='OUT' THEN -new.quantity
               ELSE 0 END
    WHERE items.id=new.item_id ;
END$$
  
DROP TRIGGER IF EXISTS trg_transaction_details_after_delete$$
-- -----------------------------
-- after delete trigger
-- -----------------------------
CREATE TRIGGER trg_transaction_details_after_delete
AFTER DELETE ON transaction_details
FOR EACH ROW
BEGIN
  -- -----------------------------
  -- ambil jenis transaksi
  -- -----------------------------
  DECLARE tipe VARCHAR(20);
  SET tipe = (
    SELECT tt.code
      FROM transactions tr
      JOIN transaction_types tt ON tr.type_id=tt.id
      WHERE tr.id=old.trans_id
  ) ;
  -- -----------------------------
  -- update sesuai jenis transaksi
  -- -----------------------------
  UPDATE items
    SET quantity_on_hand
        = quantity_on_hand
        + CASE WHEN tipe='IN' THEN -old.quantity
               WHEN tipe='OUT' THEN old.quantity
               ELSE 0 END
    WHERE items.id=old.item_id ;
END$$
  
DELIMITER ;

-- http://pojokprogrammer.net/content/aplikasi-inventory-sederhana-penggunaan-trigger-untuk-tracking-stock-barang