
use inventory;
DROP TRIGGER IF EXISTS `transaksi_stok_ruangan_before_insert`;
DELIMITER //
CREATE DEFINER=`root`@`127.0.0.1` TRIGGER `transaksi_stok_ruangan_before_insert` BEFORE INSERT ON `transaksi_stok_ruangan` FOR EACH ROW BEGIN
	DECLARE VSTOK_SEBELUMNYA DECIMAL(60,2);
	DECLARE VTAMBAH_ATAU_KURANG, VTAMBAH_ATAU_KURANGS CHAR(1);
	DECLARE VTANGGAL DATE;
	
	 
	SET NEW.ID = (
		SELECT CONCAT(LPAD(NEW.BARANG_RUANGAN, 8, '0'), CONCAT(DATE_FORMAT(NEW.TANGGAL, '%y%m%d%H%i%s')), IF(MAX(ID) IS NULL, '001', LPAD(RIGHT(MAX(ID), 3) + 1, 3, '0')))			
		  FROM inventory.transaksi_stok_ruangan
		 WHERE TANGGAL = NEW.TANGGAL
		   AND BARANG_RUANGAN = NEW.BARANG_RUANGAN
	);
	
	
	SELECT STOK INTO VSTOK_SEBELUMNYA
	  FROM inventory.transaksi_stok_ruangan
	 WHERE BARANG_RUANGAN = NEW.BARANG_RUANGAN
		AND ID < NEW.ID
	 ORDER BY ID DESC, TANGGAL DESC LIMIT 1;
	
	
	IF FOUND_ROWS() = 0 THEN
		SELECT jts.TAMBAH_ATAU_KURANG INTO VTAMBAH_ATAU_KURANGS
		  FROM inventory.jenis_transaksi_stok jts
		 WHERE jts.ID = NEW.JENIS;
		 
		IF FOUND_ROWS() > 0 THEN
			
			IF NEW.JENIS != 11 THEN
				IF NEW.JENIS != 15 THEN
					IF VTAMBAH_ATAU_KURANGS = '+' THEN
						SET NEW.STOK = NEW.JUMLAH;
					ELSEIF VTAMBAH_ATAU_KURANGS = '-' THEN
						SET NEW.STOK = (0 - NEW.JUMLAH);
					ELSE
						SET NEW.STOK = NEW.JUMLAH;
					END IF;
				ELSE
					SET NEW.STOK = 0;
				END IF;
			ELSE
				SET NEW.STOK = NEW.JUMLAH;
			END IF;
		END IF;
		
	ELSE
		
		SELECT jts.TAMBAH_ATAU_KURANG INTO VTAMBAH_ATAU_KURANG
		  FROM inventory.jenis_transaksi_stok jts
		 WHERE jts.ID = NEW.JENIS;
		 
		IF FOUND_ROWS() > 0 THEN
			
			IF NEW.JENIS != 11 THEN
				IF NEW.JENIS != 15 THEN
					IF VTAMBAH_ATAU_KURANG = '+' THEN
						SET NEW.STOK = VSTOK_SEBELUMNYA + NEW.JUMLAH;
					ELSEIF VTAMBAH_ATAU_KURANG = '-' THEN
						SET NEW.STOK = VSTOK_SEBELUMNYA - NEW.JUMLAH;
					ELSE
						SET NEW.STOK = VSTOK_SEBELUMNYA;
					END IF;
				ELSE
					SET NEW.STOK = NEW.JUMLAH;
				END IF;
			ELSE
				SET NEW.STOK = NEW.JUMLAH;
			END IF;
		END IF;
	END IF;
	
	
	IF NOT EXISTS(SELECT 1
			FROM inventory.transaksi_stok_ruangan
		  WHERE BARANG_RUANGAN = NEW.BARANG_RUANGAN
			 AND ID > NEW.ID LIMIT 1) THEN
	BEGIN
		UPDATE inventory.barang_ruangan
		   SET STOK = NEW.STOK,
		   	 TANGGAL = NEW.TANGGAL,
		   	 TRANSAKSI_STOK_RUANGAN = NEW.ID,
             STATUS = 1
		 WHERE ID = NEW.BARANG_RUANGAN;
	END;
	END IF;
END;

//

DELIMITER ;