USE `sakti`;

DROP PROCEDURE IF EXISTS `sakti`.`GenerateDataPersediaan`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sakti`.`GenerateDataPersediaan`(IN `PAWAL` DATETIME, IN `PAKHIR` DATETIME, IN `PJENIS` CHAR(50), IN `PKATEGORI` CHAR(50), IN `PPERIODE` INT)
BEGIN
	DECLARE VKODE VARCHAR(30);
	DECLARE VJUMLAH DECIMAL(60,2);
	DECLARE vKATEGORI VARCHAR(50);
	DECLARE VSTATUS INT(11);
    DECLARE VBARANG SMALLINT(6);
	
	DECLARE DATA_NOT_FOUND TINYINT DEFAULT FALSE;
	
	DECLARE CR_PERSEDIAAN CURSOR FOR
		SELECT 
		 b.KODE_PSEDIA, b.ID, (SUM(IF(jts.TAMBAH_ATAU_KURANG='-',t.JUMLAH,0)) -  SUM(IF(jts.TAMBAH_ATAU_KURANG='+',t.JUMLAH,0))) JUMLAH
		FROM 
		inventory.jenis_transaksi_psedia jt
		, inventory.mapping_jenis_transaksi_psedia mpj
		, inventory.jenis_transaksi_stok jts
		, inventory.transaksi_stok_ruangan t
		, inventory.barang_ruangan br
		, inventory.barang b
		WHERE 
		b.KATEGORI LIKE CONCAT(PKATEGORI,'%') AND b.ID = br.BARANG AND br.ID = t.BARANG_RUANGAN AND t.JENIS = jts.ID AND t.TANGGAL BETWEEN PAWAL and PAKHIR
		AND jts.ID = mpj.JENIS_SIMRS AND mpj.JENIS_PSEDIA = jt.ID AND jt.ID = PJENIS #AND LENGTH(b.KODE_PSEDIA) = 16
	GROUP BY b.ID;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET DATA_NOT_FOUND = TRUE;
			
	OPEN CR_PERSEDIAAN;
	EOF: LOOP
		FETCH CR_PERSEDIAAN INTO VKODE, VBARANG, VJUMLAH;
		
		IF DATA_NOT_FOUND THEN
			UPDATE temp.temp SET ID = 0 WHERE ID = 0;
			LEAVE EOF;
		END IF;
		
		SELECT COUNT(*) INTO VSTATUS FROM sakti.sakti_persediaan_detail WHERE PERIODE = PPERIODE AND BARANG_SIMRS = VBARANG;
		IF VSTATUS = 0 THEN
            IF VJUMLAH > 0 THEN
			    INSERT INTO sakti.sakti_persediaan_detail (PERIODE, BARANG, BARANG_SIMRS, JUMLAH, TANGGAL) VALUES (PPERIODE, VKODE, VBARANG, VJUMLAH, PAKHIR);
            END IF;
        ELSE
            IF VJUMLAH > 0 THEN
			    UPDATE sakti.sakti_persediaan_detail SET JUMLAH =  VJUMLAH, BARANG = VKODE, STATUS = 1 WHERE PERIODE = PPERIODE AND BARANG_SIMRS = VBARANG;
            ELSE
                UPDATE sakti.sakti_persediaan_detail SET STATUS = 0 WHERE PERIODE = PPERIODE AND BARANG_SIMRS = VBARANG;
            END IF;
		END IF;
		
	END LOOP;
	CLOSE CR_PERSEDIAAN;
END//
DELIMITER ;