-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             11.0.0.5919
-- --------------------------------------------------------



-- Dumping database structure for cssd
CREATE DATABASE IF NOT EXISTS `cssd` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `cssd`;

-- Dumping structure for table cssd.mapping_set_alat_baru
CREATE TABLE IF NOT EXISTS `mapping_set_alat_baru` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `SET_ALAT_BARU` int(11) NOT NULL,
  `BARANG` int(11) NOT NULL,
  `JUMLAH` decimal(10,2) NOT NULL,
  `TANGGAL` datetime NOT NULL,
  `STATUS` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `SET_ALAT_BARU` (`SET_ALAT_BARU`),
  KEY `BARANG` (`BARANG`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table cssd.penerimaan_alat_kotor
CREATE TABLE IF NOT EXISTS `penerimaan_alat_kotor` (
  `NOMOR` char(21) NOT NULL,
  `PENERIMA` char(10) NOT NULL,
  `ASAL` char(10) NOT NULL,
  `TOTAL_BERAT` decimal(10,2) NOT NULL,
  `CATATAN` text NOT NULL,
  `TANGGAL` datetime NOT NULL,
  `PENGANTAR` smallint(6) NOT NULL,
  `OLEH` smallint(6) NOT NULL DEFAULT '0',
  `STATUS` tinyint(4) NOT NULL DEFAULT '1',
  `KEMBALI` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`NOMOR`),
  KEY `TANGGAL` (`TANGGAL`),
  KEY `PENERIMA` (`PENERIMA`),
  KEY `ASAL` (`ASAL`),
  KEY `PENGANTAR` (`PENGANTAR`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table cssd.penerimaan_alat_kotor_detil
CREATE TABLE IF NOT EXISTS `penerimaan_alat_kotor_detil` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PENERIMAAN_ALAT_KOTOR` char(21) NOT NULL,
  `MAPPING_SET_ALAT_BARU` int(11) NOT NULL,
  `SET_ALAT_BARU` int(11) NOT NULL,
  `JUMLAH_PACKING` decimal(10,2) NOT NULL,
  `STATUS` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PENERIMAAN_ALAT_KOTOR` (`PENERIMAAN_ALAT_KOTOR`),
  KEY `SET_ALAT_BARU` (`SET_ALAT_BARU`),
  KEY `MAPPING_SET_ALAT_BARU` (`MAPPING_SET_ALAT_BARU`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table cssd.penerimaan_alat_kotor_detil_barangruangan
CREATE TABLE IF NOT EXISTS `penerimaan_alat_kotor_detil_barangruangan` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PENERIMAAN_ALAT_KOTOR` char(21) NOT NULL,
  `BARANG_RUANGAN` int(11) NOT NULL,
  `TOTAL` decimal(10,2) NOT NULL,
  `STATUS` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PENERIMAAN_ALAT_KOTOR` (`PENERIMAAN_ALAT_KOTOR`),
  KEY `BARANG_RUANGAN` (`BARANG_RUANGAN`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table cssd.pengembalian_alat
CREATE TABLE IF NOT EXISTS `pengembalian_alat` (
  `NOMOR` char(21) NOT NULL,
  `PENERIMA` char(10) NOT NULL,
  `ASAL` char(10) NOT NULL,
  `TOTAL_BERAT` decimal(10,2) NOT NULL,
  `CATATAN` text NOT NULL,
  `TANGGAL` datetime NOT NULL,
  `PENERIMAAN_ALAT_KOTOR` char(21) NOT NULL,
  `OLEH` smallint(6) NOT NULL DEFAULT '0',
  `STATUS` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`NOMOR`),
  KEY `TANGGAL` (`TANGGAL`),
  KEY `PENERIMAAN_ALAT_KOTOR` (`PENERIMAAN_ALAT_KOTOR`),
  KEY `PENERIMA` (`PENERIMA`),
  KEY `ASAL` (`ASAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table cssd.pengembalian_alat_detil
CREATE TABLE IF NOT EXISTS `pengembalian_alat_detil` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PENGEMBALIAN_ALAT` char(21) NOT NULL,
  `PENERIMAAN_ALAT_KOTOR_DETIL` int(10) NOT NULL,
  `SET_ALAT_BARU` int(11) NOT NULL,
  `RETAK` decimal(10,2) NOT NULL,
  `KOROSIF` decimal(10,2) NOT NULL,
  `AUS` decimal(10,2) NOT NULL,
  `STATUS` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PENERIMAAN_ALAT_KOTOR_DETIL` (`PENERIMAAN_ALAT_KOTOR_DETIL`),
  KEY `PENGEMBALIAN_ALAT` (`PENGEMBALIAN_ALAT`),
  KEY `SET_ALAT_BARU` (`SET_ALAT_BARU`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table cssd.pengembalian_alat_detil_barangruangan
CREATE TABLE IF NOT EXISTS `pengembalian_alat_detil_barangruangan` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PENGEMBALIAN_ALAT` char(21) NOT NULL,
  `PENERIMAAN_ALAT_KOTOR_DETIL_BARANGRUANGAN` int(10) NOT NULL,
  `RETAK` decimal(10,2) NOT NULL,
  `KOROSIF` decimal(10,2) NOT NULL,
  `AUS` decimal(10,2) NOT NULL,
  `STATUS` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PENERIMAAN_ALAT_KOTOR_DETIL_BARANGRUANGAN` (`PENERIMAAN_ALAT_KOTOR_DETIL_BARANGRUANGAN`),
  KEY `PENGEMBALIAN_ALAT` (`PENGEMBALIAN_ALAT`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table cssd.petugas_kegiatan
CREATE TABLE IF NOT EXISTS `petugas_kegiatan` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PENERIMAAN_ALAT_KOTOR` char(21) NOT NULL,
  `PETUGAS_PACKING` smallint(6) NOT NULL COMMENT 'ID tabel master.pegawai',
  `OPERATOR_AUTOCLAVE` smallint(6) NOT NULL,
  `PETUGAS_PENYIMPANAN` smallint(6) NOT NULL COMMENT 'ID tabel master.pegawai',
  `PETUGAS_MENERIMA` smallint(6) NOT NULL COMMENT 'ID tabel master.pegawai',
  `PETUGAS_MENYERAHKAN` smallint(6) NOT NULL COMMENT 'ID tabel master.pegawai',
  `SHINVA_I` tinyint(4) NOT NULL,
  `SHINVA_II` tinyint(4) NOT NULL,
  `BELIMED_I` tinyint(4) NOT NULL,
  `BELIMED_II` tinyint(4) NOT NULL,
  `DTT` tinyint(4) NOT NULL,
  `STATUS` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `PENERIMAAN_ALAT_KOTOR` (`PENERIMAAN_ALAT_KOTOR`),
  KEY `PETUGAS_PACKING` (`PETUGAS_PACKING`),
  KEY `PETUGAS_PENYIMPANAN` (`PETUGAS_PENYIMPANAN`),
  KEY `PETUGAS_MENERIMA` (`PETUGAS_MENERIMA`),
  KEY `PETUGAS_MENYERAHKAN` (`PETUGAS_MENYERAHKAN`),
  KEY `OPERATOR_AUTOCLAVE` (`OPERATOR_AUTOCLAVE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table cssd.set_alat_baru
CREATE TABLE IF NOT EXISTS `set_alat_baru` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAMA` varchar(50) NOT NULL,
  `KETERANGAN` text NOT NULL,
  `STATUS` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

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

	IF NEW.STATUS = 0 THEN
		UPDATE cssd.pengembalian_alat_detil pad 
		   SET pad.STATUS = 0
		 WHERE pad.PENGEMBALIAN_ALAT = OLD.NOMOR;
		 
		UPDATE cssd.pengembalian_alat_detil_barangruangan padbr
		   SET padbr.STATUS = 0
		 WHERE padbr.PENGEMBALIAN_ALAT = OLD.NOMOR;
	END IF;

	IF NEW.STATUS = 2 THEN
		UPDATE cssd.pengembalian_alat_detil pad 
		   SET pad.STATUS = 2
		 WHERE pad.PENGEMBALIAN_ALAT = OLD.NOMOR;
		 
		UPDATE cssd.pengembalian_alat_detil_barangruangan padbr
		   SET padbr.STATUS = 2
		 WHERE padbr.PENGEMBALIAN_ALAT = OLD.NOMOR;
		 
		UPDATE cssd.penerimaan_alat_kotor pak
		   SET pak.STATUS = 2, pak.KEMBALI = 2
		 WHERE pak.NOMOR = OLD.PENERIMAAN_ALAT_KOTOR;
	END IF;
	
END//
DELIMITER ;

