USE `inventory`;
-- --------------------------------------------------------
-- Versi server:                 8.0.11 - MySQL Community Server - GPL
-- OS Server:                    Win64
-- HeidiSQL Versi:               9.2.0.4947
-- --------------------------------------------------------


-- Dumping structure for trigger inventory.material_request_after_insert
DROP TRIGGER IF EXISTS `material_request_after_insert`;
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `material_request_after_insert` AFTER INSERT ON `material_request` FOR EACH ROW BEGIN
	CALL inventory.generateDataPerencanaan(NEW.NO_MR, DATE_FORMAT(NEW.DATA_AWAL,'%Y-%m-%d 00:00:00'), DATE_FORMAT(NEW.DATA_AKHIR,'%Y-%m-%d 23:59:59'));
	/*
	SET @KTOTAL:=0;
	SET @VTOTAL:=0;
	SET @PAWAL = DATE_FORMAT('2021-07-20','%Y-%m-%d 00:00:00');
	SET @PAKHIR = DATE_FORMAT('2022-07-20','%Y-%m-%d 23:59:59');
	SELECT SUM((BLN5+BLN4+BLN3+BLN2)*HARGA_BELI) TOTAL INTO @VTOTAL
			FROM (
				SELECT brg.ID, brg.NAMA, SUM(tsr.JUMLAH) JUMLAH
				     , SUM(IF(MONTH(tsr.TANGGAL)=MONTH(@PAKHIR),tsr.JUMLAH,0)) BLN5
					  , SUM(IF(MONTH(tsr.TANGGAL)=MONTH(DATE_ADD(@PAKHIR, INTERVAL -1 MONTH)),tsr.JUMLAH,0)) BLN4
					  , SUM(IF(MONTH(tsr.TANGGAL)=MONTH(DATE_ADD(@PAKHIR, INTERVAL -2 MONTH)),tsr.JUMLAH,0)) BLN3
					  , SUM(IF(MONTH(tsr.TANGGAL)=MONTH(DATE_ADD(@PAKHIR, INTERVAL -3 MONTH)),tsr.JUMLAH,0)) BLN2
					  , SUM(IF(MONTH(tsr.TANGGAL)=MONTH(DATE_ADD(@PAKHIR, INTERVAL -4 MONTH)),tsr.JUMLAH,0)) BLN1
					  , brg.HARGA_BELI
					FROM inventory.transaksi_stok_ruangan tsr
				       , inventory.barang_ruangan br
				       , inventory.barang brg
					WHERE tsr.JENIS IN (30,33) AND tsr.BARANG_RUANGAN=br.ID
					  AND br.BARANG=brg.ID AND tsr.TANGGAL BETWEEN @PAWAL AND @PAKHIR
					GROUP BY brg.ID
				) trx;
				
	INSERT INTO inventory.material_request_detil (NO_MR,BARANG,SISA_STOK,KATEGORI,RATAPENJUALAN,JML_RUMUS,JUMLAH,STATUS)
	
	SELECT 
			CONCAT('12345') AS NO_MR,IDBARANG AS BARANG, STOKRS AS SISA_STOK
#			CONCAT(NEW.NO_MR) AS NO_MR,IDBARANG AS BARANG, STOKRS AS SISA_STOK
		#, @KATEGORI:=IF(((@KTOTAL/@VTOTAL)*100) < 80, 'A', IF(((@KTOTAL/@VTOTAL)*100) < 95, 'B','C')) KATEGORI
		, @KATEGORI:=(SELECT getKategoriRumusPerencanaan((@KTOTAL/@VTOTAL)*100)) KATEGORI
		, @RATAPENJUALAN:=(RATASATUAN * (PERIODE_PERENCANAAN_A/4)) RATAPENJUALAN 
		,
		
		(@RATAPENJUALAN + (
			@KENAIKANVOLUME:= (
					@RATAPENJUALAN:=(RATASATUAN * (PERIODE_PERENCANAAN_A/4))
				)* ((
					@PERSEN:=IF(@KATEGORI='A', VOLUME_PRESENTASE_A, IF(@KATEGORI='B', VOLUME_PRESENTASE_B, VOLUME_PRESENTASE_C))
				)/100)
			) + (
				@LEADTIME:=((IF(@KATEGORI='A', LEAD_TIME_A, IF(@KATEGORI='B', LEAD_TIME_B, LEAD_TIME_C))/30) * (@RATAPENJUALAN + (
				@KENAIKANVOLUME:= (
					@RATAPENJUALAN:=(RATASATUAN * (PERIODE_PERENCANAAN_A/4))
				)* ((
					@PERSEN:=IF(@KATEGORI='A', VOLUME_PRESENTASE_A, IF(@KATEGORI='B', VOLUME_PRESENTASE_B, VOLUME_PRESENTASE_C))
				)/100)
			)))
		) + STOKRS) JML_RUMUS 
		,(@RATAPENJUALAN + (
			@KENAIKANVOLUME:= (
					@RATAPENJUALAN:=(RATASATUAN * (PERIODE_PERENCANAAN_A/4))
				)* ((
					@PERSEN:=IF(@KATEGORI='A', VOLUME_PRESENTASE_A, IF(@KATEGORI='B', VOLUME_PRESENTASE_B, VOLUME_PRESENTASE_C))
				)/100)
			) + (
				@LEADTIME:=((IF(@KATEGORI='A', LEAD_TIME_A, IF(@KATEGORI='B', LEAD_TIME_B, LEAD_TIME_C))/30) * (@RATAPENJUALAN + (
				@KENAIKANVOLUME:= (
					@RATAPENJUALAN:=(RATASATUAN * (PERIODE_PERENCANAAN_A/4))
				)* ((
					@PERSEN:=IF(@KATEGORI='A', VOLUME_PRESENTASE_A, IF(@KATEGORI='B', VOLUME_PRESENTASE_B, VOLUME_PRESENTASE_C))
				)/100)
			)))
		) + STOKRS) JUMLAH 
		, 0
		
	FROM (
	
		SELECT ID, IDBARANG,NAMABARANG, SUM(JUMLAH) JUMLAH, SUM(BLN5) BLN5, SUM(BLN4) BLN4, SUM(BLN3) BLN3, SUM(BLN2) BLN2, SUM(BLN1) BLN1
				,SUM(BLN5+BLN4+BLN3+BLN2) JML, HARGA_BELI,SUM((BLN5+BLN4+BLN3+BLN2)*HARGA_BELI) TOTAL
				, SUM((BLN5+BLN4+BLN3+BLN2)*HARGA_BELI)/4 RATA,SUM((BLN5+BLN4+BLN3+BLN2))/4 RATASATUAN, SUM(STOKRS) STOKRS
				, a.PERIODE_PERENCANAAN PERIODE_PERENCANAAN_A, a.VOLUME_PRESENTASE VOLUME_PRESENTASE_A, a.LEAD_TIME LEAD_TIME_A
				, b.PERIODE_PERENCANAAN PERIODE_PERENCANAAN_B, b.VOLUME_PRESENTASE VOLUME_PRESENTASE_B, b.LEAD_TIME LEAD_TIME_B
				, c.PERIODE_PERENCANAAN PERIODE_PERENCANAAN_C, c.VOLUME_PRESENTASE VOLUME_PRESENTASE_C, c.LEAD_TIME LEAD_TIME_C
			FROM (
			
			 SELECT ibrg.ID, ibrg.BARANG AS IDBARANG,mstbrg.NAMA NAMABARANG, SUM(JUMLAH) JUMLAH, SUM(BLN5) BLN5, SUM(BLN4) BLN4, SUM(BLN3) BLN3, SUM(BLN2) BLN2, SUM(BLN1) BLN1
						,SUM(BLN5+BLN4+BLN3+BLN2) JML, mstbrg.HARGA_BELI,SUM((BLN5+BLN4+BLN3+BLN2)*mstbrg.HARGA_BELI) TOTAL
						,SUM((BLN5+BLN4+BLN3+BLN2))/4 RATASATUAN, SUM(STOKRS) STOKRS 
			 	FROM inventory.barang_ruangan ibrg 
				 		LEFT JOIN (
								SELECT br.BARANG,  SUM(tsr.JUMLAH) JUMLAH
								     , SUM(IF(MONTH(tsr.TANGGAL)=MONTH(@PAKHIR),tsr.JUMLAH,0)) BLN5
									  , SUM(IF(MONTH(tsr.TANGGAL)=MONTH(DATE_ADD(@PAKHIR, INTERVAL -1 MONTH)),tsr.JUMLAH,0)) BLN4
									  , SUM(IF(MONTH(tsr.TANGGAL)=MONTH(DATE_ADD(@PAKHIR, INTERVAL -2 MONTH)),tsr.JUMLAH,0)) BLN3
									  , SUM(IF(MONTH(tsr.TANGGAL)=MONTH(DATE_ADD(@PAKHIR, INTERVAL -3 MONTH)),tsr.JUMLAH,0)) BLN2
									  , SUM(IF(MONTH(tsr.TANGGAL)=MONTH(DATE_ADD(@PAKHIR, INTERVAL -4 MONTH)),tsr.JUMLAH,0)) BLN1
									  , (SELECT SUM(br.STOK)
													FROM inventory.barang_ruangan brs
													WHERE brs.ID=br.ID
													GROUP BY br.BARANG) STOKRS 
									FROM inventory.transaksi_stok_ruangan tsr
								       , inventory.barang_ruangan br
								      
									WHERE tsr.JENIS IN (30,33) AND tsr.BARANG_RUANGAN=br.ID
									  AND tsr.TANGGAL BETWEEN @PAWAL AND @PAKHIR
									GROUP BY br.BARANG) ab ON ibrg.BARANG=ab.BARANG
					, inventory.barang mstbrg
					WHERE ibrg.BARANG=mstbrg.ID  AND JUMLAH  IS NOT NULL
					GROUP BY mstbrg.ID
				) trx
				, (SELECT * FROM inventory.rumus_perencanaan rp WHERE rp.KATEGORI='A' AND rp.STATUS=1) a
				, (SELECT * FROM inventory.rumus_perencanaan rp WHERE rp.KATEGORI='B' AND rp.STATUS=1) b
				, (SELECT * FROM inventory.rumus_perencanaan rp WHERE rp.KATEGORI='C' AND rp.STATUS=1) c
				GROUP BY ID
	) trxx

	ORDER BY TOTAL DESC;
	*/
END//
DELIMITER ;
