-- --------------------------------------------------------
-- Server OS:                    Linux
-- HeidiSQL Version:             12.0.0.6468
-- --------------------------------------------------------


USE `laundry`;

-- Dumping structure for table laundry.detil_kegiatan
CREATE TABLE IF NOT EXISTS `detil_kegiatan` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `KEGIATAN` char(21) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `REF` tinyint NOT NULL,
  `PENCUCIAN_INFEKSI` int NOT NULL,
  `PENCUCIAN_NONINFEKSI` int NOT NULL,
  `PERENDAMAN` int NOT NULL,
  `PENGERINGAN` int NOT NULL,
  `PENYETRIKAAN` int NOT NULL,
  `PELIPATAN` int NOT NULL,
  `PENYIMPANAN` int NOT NULL,
  `PENJAHITAN` int NOT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `KEGIATAN` (`KEGIATAN`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.distribusi_detil_linen
CREATE TABLE IF NOT EXISTS `distribusi_detil_linen` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `DISTRIBUSI_LINEN` char(21) NOT NULL,
  `PENERIMAAN_DETIL_LINEN` int NOT NULL,
  `JUMLAH` decimal(10,2) NOT NULL,
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PENERIMAAN_DETIL_LINEN` (`PENERIMAAN_DETIL_LINEN`),
  KEY `DISTRIBUSI_LINEN` (`DISTRIBUSI_LINEN`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.distribusi_linen
CREATE TABLE IF NOT EXISTS `distribusi_linen` (
  `NOMOR` char(21) NOT NULL,
  `PENERIMA` char(10) NOT NULL,
  `ASAL` char(10) NOT NULL,
  `TANGGAL` datetime NOT NULL,
  `PENERIMAAN_LINEN` char(21) NOT NULL,
  `OLEH` smallint NOT NULL DEFAULT '0',
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`NOMOR`),
  KEY `TANGGAL` (`TANGGAL`),
  KEY `PENERIMAAN_LINEN` (`PENERIMAAN_LINEN`),
  KEY `PENERIMA` (`PENERIMA`),
  KEY `ASAL` (`ASAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.distribusi_no_seri_barang
CREATE TABLE IF NOT EXISTS `distribusi_no_seri_barang` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `DISTRIBUSI_DETIL_LINEN` int NOT NULL,
  `NO_SERI_BARANG_RUANGAN` int NOT NULL,
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `NO_SERI_BARANG_RUANGAN` (`NO_SERI_BARANG_RUANGAN`),
  KEY `DISTRIBUSI_DETIL_LINEN` (`DISTRIBUSI_DETIL_LINEN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.kegiatan
CREATE TABLE IF NOT EXISTS `kegiatan` (
  `NOMOR` char(21) NOT NULL,
  `TANGGAL` datetime NOT NULL,
  `OLEH` smallint NOT NULL,
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`NOMOR`) USING BTREE,
  KEY `TANGGAL` (`TANGGAL`) USING BTREE,
  KEY `STATUS` (`STATUS`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.kegiatan_laundry
CREATE TABLE IF NOT EXISTS `kegiatan_laundry` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `PENERIMAAN_LINEN` char(21) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `PENERIMAAN_LINEN` (`PENERIMAAN_LINEN`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.kegiatan_pelipatan
CREATE TABLE IF NOT EXISTS `kegiatan_pelipatan` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `KEGIATAN` int NOT NULL DEFAULT '0',
  `PEGAWAI` smallint NOT NULL COMMENT 'ID tabel master.pegawai',
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PEGAWAI` (`PEGAWAI`),
  KEY `KEGIATAN` (`KEGIATAN`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.kegiatan_pencucian
CREATE TABLE IF NOT EXISTS `kegiatan_pencucian` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `KEGIATAN` int NOT NULL DEFAULT '0',
  `PEGAWAI` smallint NOT NULL COMMENT 'ID tabel master.pegawai',
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PEGAWAI` (`PEGAWAI`),
  KEY `KEGIATAN` (`KEGIATAN`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.kegiatan_pengeringan
CREATE TABLE IF NOT EXISTS `kegiatan_pengeringan` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `KEGIATAN` int NOT NULL DEFAULT '0',
  `PEGAWAI` smallint NOT NULL COMMENT 'ID tabel master.pegawai',
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PEGAWAI` (`PEGAWAI`),
  KEY `KEGIATAN` (`KEGIATAN`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.kegiatan_penjahitan
CREATE TABLE IF NOT EXISTS `kegiatan_penjahitan` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `KEGIATAN` int NOT NULL DEFAULT '0',
  `PEGAWAI` smallint NOT NULL COMMENT 'ID tabel master.pegawai',
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PEGAWAI` (`PEGAWAI`),
  KEY `KEGIATAN` (`KEGIATAN`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.kegiatan_perendaman
CREATE TABLE IF NOT EXISTS `kegiatan_perendaman` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `KEGIATAN` int NOT NULL DEFAULT '0',
  `PEGAWAI` smallint NOT NULL COMMENT 'ID tabel master.pegawai',
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PEGAWAI` (`PEGAWAI`),
  KEY `KEGIATAN` (`KEGIATAN`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.mesin_kegiatan
CREATE TABLE IF NOT EXISTS `mesin_kegiatan` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `KEGIATAN` char(21) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `MESIN` smallint NOT NULL,
  `JUMLAH` decimal(10,2) NOT NULL,
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `KEGIATAN_MESIN` (`KEGIATAN`,`MESIN`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.penerimaan_berat_linen
CREATE TABLE IF NOT EXISTS `penerimaan_berat_linen` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `PENERIMAAN_LINEN` char(21) NOT NULL,
  `KONDISI_BARANG` smallint NOT NULL COMMENT 'Jenis Referensi: 147',
  `BERAT` decimal(10,2) NOT NULL,
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PENERIMAAN_LINEN` (`PENERIMAAN_LINEN`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.penerimaan_detil_linen
CREATE TABLE IF NOT EXISTS `penerimaan_detil_linen` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `PENERIMAAN_LINEN` char(21) NOT NULL,
  `KONDISI_BARANG` smallint NOT NULL COMMENT 'Jenis Referensi: 147',
  `BARANG` smallint NOT NULL,
  `JUMLAH` decimal(10,2) NOT NULL,
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PENERIMAAN_LINEN` (`PENERIMAAN_LINEN`),
  KEY `BARANG` (`BARANG`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.penerimaan_linen
CREATE TABLE IF NOT EXISTS `penerimaan_linen` (
  `NOMOR` char(21) NOT NULL,
  `PENERIMA` char(10) NOT NULL,
  `ASAL` char(10) NOT NULL,
  `TANGGAL` datetime NOT NULL,
  `OLEH` smallint NOT NULL DEFAULT '0',
  `STATUS` tinyint NOT NULL DEFAULT '1',
  `TERIMA` tinyint NOT NULL DEFAULT '0',
  `KEMBALI` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`NOMOR`),
  KEY `TANGGAL` (`TANGGAL`),
  KEY `PENERIMA` (`PENERIMA`),
  KEY `ASAL` (`ASAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table laundry.penerimaan_no_seri_barang
CREATE TABLE IF NOT EXISTS `penerimaan_no_seri_barang` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `PENERIMAAN_DETIL_LINEN` int NOT NULL,
  `NO_SERI_BARANG_RUANGAN` int NOT NULL,
  `STATUS` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `NO_SERI_BARANG_RUANGAN` (`NO_SERI_BARANG_RUANGAN`),
  KEY `PENERIMAAN_DETIL_LINEN` (`PENERIMAAN_DETIL_LINEN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for trigger laundry.distribusi_detil_linen_after_update
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `distribusi_detil_linen_after_update` AFTER UPDATE ON `distribusi_detil_linen` FOR EACH ROW BEGIN

	IF NEW.STATUS = 2 THEN
		UPDATE laundry.distribusi_no_seri_barang dnsb
			SET dnsb.STATUS = 2
		WHERE dnsb.DISTRIBUSI_DETIL_LINEN = OLD.ID
		AND dnsb.STATUS != 0;
	END IF;
	
	IF NEW.STATUS = 0 THEN
		UPDATE laundry.distribusi_no_seri_barang dnsb
			SET dnsb.STATUS = 0
		WHERE dnsb.DISTRIBUSI_DETIL_LINEN = OLD.ID;
	END IF;

END//
DELIMITER ;

-- Dumping structure for trigger laundry.distribusi_linen_after_update
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `distribusi_linen_after_update` AFTER UPDATE ON `distribusi_linen` FOR EACH ROW BEGIN

	DECLARE VPENERIMAANLINEN CHAR;
	
	IF NEW.STATUS = 2 THEN
		SELECT COUNT(*) INTO VPENERIMAANLINEN FROM laundry.penerimaan_linen pl
		WHERE pl.NOMOR = OLD.PENERIMAAN_LINEN;
		
		IF VPENERIMAANLINEN != 0 THEN
			UPDATE laundry.penerimaan_linen pl
				SET pl.STATUS = 2, pl.TERIMA = 2, pl.KEMBALI = 2
			WHERE pl.NOMOR = OLD.PENERIMAAN_LINEN;
		END IF; 
	
		UPDATE laundry.distribusi_detil_linen ddl
			SET ddl.STATUS = 2
		WHERE ddl.DISTRIBUSI_LINEN = OLD.NOMOR;
		
	END IF;
	
	IF NEW.STATUS = 0 THEN
		UPDATE laundry.distribusi_detil_linen ddl
			SET ddl.STATUS = 0
		WHERE ddl.DISTRIBUSI_LINEN = OLD.NOMOR;
		
	END IF;
	
END//
DELIMITER ;

-- Dumping structure for trigger laundry.distribusi_no_seri_barang_after_update
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `distribusi_no_seri_barang_after_update` AFTER UPDATE ON `distribusi_no_seri_barang` FOR EACH ROW BEGIN

	IF NEW.STATUS = 2 THEN
		UPDATE inventory.no_seri_barang_ruangan nsbr
			SET nsbr.STATUS = 1, nsbr.KONDISI_BARANG = 0
		WHERE nsbr.ID = OLD.NO_SERI_BARANG_RUANGAN;
  	END IF;
  	
  	IF NEW.STATUS = 0 THEN
  		UPDATE laundry.penerimaan_no_seri_barang pnsb
  			SET pnsb.STATUS = 0
  		WHERE pnsb.NO_SERI_BARANG_RUANGAN = OLD.NO_SERI_BARANG_RUANGAN;
  	END IF;
  	
END//
DELIMITER ;

-- Dumping structure for trigger laundry.penerimaan_detil_linen_after_update
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `penerimaan_detil_linen_after_update` AFTER UPDATE ON `penerimaan_detil_linen` FOR EACH ROW BEGIN

	IF NEW.STATUS = 2 THEN
		UPDATE laundry.penerimaan_no_seri_barang pnsb
			SET pnsb.STATUS = 2
		WHERE pnsb.PENERIMAAN_DETIL_LINEN = OLD.ID
		AND pnsb.STATUS != 0;
	END IF;
	
	IF NEW.STATUS = 0 THEN
		UPDATE laundry.penerimaan_no_seri_barang pnsb
			SET pnsb.STATUS = 0
		WHERE pnsb.PENERIMAAN_DETIL_LINEN = OLD.ID;
	END IF;

END//
DELIMITER ;

-- Dumping structure for trigger laundry.penerimaan_linen_after_update
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `penerimaan_linen_after_update` AFTER UPDATE ON `penerimaan_linen` FOR EACH ROW BEGIN
	DECLARE VNOMORLINEN CHAR;
	
	IF NEW.STATUS = 0 THEN
		SELECT COUNT(*) INTO VNOMORLINEN FROM laundry.distribusi_linen dl
		WHERE dl.PENERIMAAN_LINEN = OLD.NOMOR;
		
		IF VNOMORLINEN != 0 THEN
			UPDATE laundry.distribusi_linen dl
				SET dl.STATUS = 0
			WHERE dl.PENERIMAAN_LINEN = OLD.NOMOR;
		END IF; 
	
		UPDATE laundry.penerimaan_detil_linen pdl 
		   SET pdl.STATUS = 0
		 WHERE pdl.PENERIMAAN_LINEN = OLD.NOMOR;
		 
		UPDATE laundry.penerimaan_berat_linen pbl
		   SET pbl.STATUS = 0
		 WHERE pbl.PENERIMAAN_LINEN = OLD.NOMOR;
	END IF;
	
	IF NEW.STATUS = 2 THEN
		UPDATE laundry.penerimaan_detil_linen pdl 
		   SET pdl.STATUS = 2
		 WHERE pdl.PENERIMAAN_LINEN = OLD.NOMOR;
		 
		UPDATE laundry.penerimaan_berat_linen pbl
		   SET pbl.STATUS = 2
		 WHERE pbl.PENERIMAAN_LINEN = OLD.NOMOR;
		 
		UPDATE laundry.kegiatan_laundry ldr
		   SET ldr.STATUS = 2
		 WHERE ldr.PENERIMAAN_LINEN = OLD.NOMOR;
	END IF;
END//
DELIMITER ;

-- Dumping structure for trigger laundry.penerimaan_no_seri_barang_after_update
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `penerimaan_no_seri_barang_after_update` AFTER UPDATE ON `penerimaan_no_seri_barang` FOR EACH ROW BEGIN

	IF NEW.STATUS = 2 THEN
		UPDATE inventory.no_seri_barang_ruangan nsbr
			SET nsbr.STATUS = 1, nsbr.KONDISI_BARANG = 0
		WHERE nsbr.ID = OLD.NO_SERI_BARANG_RUANGAN;
  	END IF;
  	
  	IF NEW.STATUS = 0 THEN
		UPDATE inventory.no_seri_barang_ruangan nsbr
			SET nsbr.STATUS = 1, nsbr.KONDISI_BARANG = 0
		WHERE nsbr.ID = OLD.NO_SERI_BARANG_RUANGAN;
  	END IF;
	
END//
DELIMITER ;

