USE `inventory`;

DROP PROCEDURE IF EXISTS `inventory`.`onInsertNewDataStokOpname`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `inventory`.`onInsertNewDataStokOpname`(IN `PID` INT, IN `PRUANGAN` INT, IN `PTANGGAL` DATETIME)
BEGIN
	DECLARE VTANGGAL DATETIME;
	DECLARE VKATEGORI INT;
	DECLARE VJENIS, VATURAN INT;
	
	SELECT ID INTO VATURAN FROM aplikasi.properti_config WHERE ID = 900603 AND VALUE = 'TRUE';
	IF FOUND_ROWS() > 0 THEN
		SET VJENIS = 1;
	ELSE
		SET VJENIS = 0;
	END IF;
	
	SELECT DATE_FORMAT(CONCAT(TANGGAL,' ',TIME),'%Y-%m-%d %H:%i:%s') INTO VTANGGAL
	  FROM inventory.stok_opname
	 WHERE NOT ID IN (PID)
	   AND RUANGAN = PRUANGAN
	   AND STATUS = 'Final' ORDER BY TANGGAL DESC LIMIT 1;
	
	SELECT KATEGORI INTO VKATEGORI
	  FROM inventory.stok_opname
	 WHERE ID = PID;
	 
	SET VTANGGAL = IF(VTANGGAL IS NULL, PTANGGAL, VTANGGAL);
	SET @PKATEGORI:=CONCAT(VKATEGORI, '%');
	
	INSERT INTO inventory.stok_opname_detil(STOK_OPNAME, BARANG_RUANGAN, AWAL, SISTEM, MANUAL, TANGGAL, BARANG_MASUK, BARANG_KELUAR, OLEH, STATUS)
		SELECT PID, br.ID, 
				 inventory.getStokAwal(br.ID, VTANGGAL),
				 IF(tsr.STOK IS NULL, NULL, tsr.STOK)
				 , 0 MANUAL
				 , tsr.TANGGAL,
				 inventory.getJumlahBarangMasuk(br.ID, VTANGGAL, PTANGGAL), 
				 inventory.getJumlahBarangKeluar(br.ID, VTANGGAL, PTANGGAL), 0, 1 
		FROM (
			SELECT br.*, inventory.getIdTransaksiStokRuangan(br.ID, PTANGGAL) ID_TRANSAKSI
			  FROM inventory.barang_ruangan br, inventory.barang b
			 WHERE b.ID = br.BARANG AND b.KATEGORI LIKE @PKATEGORI AND br.RUANGAN = PRUANGAN
			   AND br.STATUS = 1
		) br
		LEFT JOIN inventory.stok_opname_detil sod ON sod.BARANG_RUANGAN = br.ID AND sod.STOK_OPNAME = PID
		LEFT JOIN inventory.transaksi_stok_ruangan tsr ON tsr.ID = br.ID_TRANSAKSI
		WHERE sod.ID IS NULL;
END//
DELIMITER ;