apartVw.sql

# ************************************************************
# Sequel Pro SQL dump
# Version 4541
#
# http://www.sequelpro.com/
# https://github.com/sequelpro/sequelpro
#
# Host: brezen (MySQL 5.7.13-0ubuntu0.16.04.2)
# Datenbank: apartVwNeu
# Erstellt am: 2016-08-17 15:14:54 +0000
# ************************************************************


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


# Export von Tabelle apartment
# ------------------------------------------------------------

DROP TABLE IF EXISTS `apartment`;

CREATE TABLE `apartment` (
  `apt_ID` smallint(6) NOT NULL,
  `apt_bez` varchar(50) NOT NULL,
  `apt_kz` char(1) DEFAULT NULL COMMENT 'Kennzeichen Apartment/Gästewohnung',
  PRIMARY KEY (`apt_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `apartment` WRITE;
/*!40000 ALTER TABLE `apartment` DISABLE KEYS */;

INSERT INTO `apartment` (`apt_ID`, `apt_bez`, `apt_kz`)
VALUES
	(1,'Apt. 1, EZ','a'),
	(2,'Apt. 2, DZ','a'),
	(3,'Apt. 3, Whg','a'),
	(5,'Apt. 5, EZ','a'),
	(6,'Apt. 6, EZ','a'),
	(7,'Apt. 7, Whg','a'),
	(8,'Apt. 8, EZ','a'),
	(9,'Apt. 9, EZ','a'),
	(10,'Apt. 10, EZ','a'),
	(11,'Apt. 11, DZ 	','a'),
	(12,'GW 40','w'),
	(13,'GW 41','w'),
	(14,'GW 42','w'),
	(15,'GW 43','w'),
	(16,'GW 44','w'),
	(17,'GW 45','w'),
	(18,'GW 46','w'),
	(19,'GW 47','w'),
	(20,'GW 48','w'),
	(21,'GW 49','w'),
	(22,'GW 50','w'),
	(23,'GW 51','w'),
	(24,'GW 52','w');

/*!40000 ALTER TABLE `apartment` ENABLE KEYS */;
UNLOCK TABLES;


# Export von Tabelle apt_preis
# ------------------------------------------------------------

DROP TABLE IF EXISTS `apt_preis`;

CREATE TABLE `apt_preis` (
  `p_ID` smallint(6) NOT NULL AUTO_INCREMENT,
  `apt_ID` smallint(6) NOT NULL,
  `p_anzp` tinyint(4) NOT NULL COMMENT 'ab Anzahl Personen',
  `p_tag` double NOT NULL,
  `p_mon` double NOT NULL,
  `ab_dat` date NOT NULL COMMENT 'gültig ab',
  PRIMARY KEY (`p_ID`),
  KEY `fk_apt_preis_apartment_idx` (`apt_ID`),
  CONSTRAINT `fk_apt_preis_apartment` FOREIGN KEY (`apt_ID`) REFERENCES `apartment` (`apt_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `apt_preis` WRITE;
/*!40000 ALTER TABLE `apt_preis` DISABLE KEYS */;

INSERT INTO `apt_preis` (`p_ID`, `apt_ID`, `p_anzp`, `p_tag`, `p_mon`, `ab_dat`)
VALUES
	(1,1,1,28,405,'2013-01-01'),
	(2,1,2,38,475,'2013-01-01'),
	(3,2,1,32,450,'2013-01-01'),
	(4,2,2,42,523,'2013-01-01'),
	(5,2,3,52,590,'2013-01-01'),
	(6,3,1,45,522,'2013-01-01'),
	(7,3,2,62,675,'2013-01-01'),
	(8,3,3,78,843,'2013-01-01'),
	(9,3,4,88,900,'2013-01-01'),
	(10,5,1,28,405,'2013-01-01'),
	(11,5,2,38,475,'2013-01-01'),
	(12,6,1,28,405,'2013-01-01'),
	(13,6,2,38,475,'2013-01-01'),
	(14,7,1,45,675,'2013-01-01'),
	(15,7,2,62,788,'2013-01-01'),
	(16,7,3,78,843,'2013-01-01'),
	(17,7,4,88,900,'2013-01-01'),
	(18,8,1,28,405,'2013-01-01'),
	(19,8,2,38,475,'2013-01-01'),
	(20,9,1,28,380,'2013-01-01'),
	(21,9,2,38,450,'2013-01-01'),
	(22,10,1,28,380,'2013-01-01'),
	(23,10,2,38,450,'2013-01-01'),
	(24,11,1,32,450,'2013-01-01'),
	(25,11,2,42,523,'2013-01-01'),
	(26,11,3,52,590,'2013-01-01'),
	(27,12,1,140,664.64,'2014-07-01'),
	(28,13,1,100,424.66,'2014-07-01'),
	(29,14,1,100,437.28,'2014-07-01'),
	(30,15,1,140,581.2,'2014-07-01'),
	(31,16,1,140,585.93,'2014-07-01'),
	(32,17,1,140,727.22,'2016-01-01'),
	(33,18,1,140,1355.84,'2016-01-01'),
	(34,19,1,140,703.92,'2016-01-01'),
	(35,20,1,140,868.49,'2014-07-01'),
	(36,21,1,100,421.43,'2014-07-01'),
	(37,22,1,140,573.69,'2016-01-01'),
	(38,23,1,140,715.16,'2014-07-01'),
	(39,24,1,150,1828.56,'2016-01-01'),
	(42,15,1,140,585.93,'2015-10-01'),
	(43,20,1,140,912.66,'2016-01-01'),
	(44,23,1,140,726.26,'2015-10-01'),
	(45,15,2,140,591.2,'2016-01-01');

/*!40000 ALTER TABLE `apt_preis` ENABLE KEYS */;
UNLOCK TABLES;


# Export von Tabelle gast
# ------------------------------------------------------------

DROP TABLE IF EXISTS `gast`;

CREATE TABLE `gast` (
  `g_ID` smallint(6) NOT NULL AUTO_INCREMENT,
  `nname` varchar(50) NOT NULL,
  `vname` varchar(50) NOT NULL,
  `titel` varchar(30) DEFAULT NULL,
  `strasse` varchar(50) DEFAULT NULL,
  `plz` varchar(20) DEFAULT NULL,
  `ort` varchar(50) DEFAULT NULL,
  `land` varchar(50) DEFAULT NULL,
  `tel` varchar(30) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `institut` varchar(100) NOT NULL,
  `i_strasse` varchar(100) NOT NULL,
  `i_plz` varchar(20) NOT NULL,
  `i_ort` varchar(50) NOT NULL,
  `i_land` varchar(50) NOT NULL,
  `i_tel` varchar(30) DEFAULT NULL,
  `i_email` varchar(100) DEFAULT NULL,
  `mwst` double NOT NULL DEFAULT '0' COMMENT 'MPG: =0, sonst: >0',
  PRIMARY KEY (`g_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `gast` WRITE;
/*!40000 ALTER TABLE `gast` DISABLE KEYS */;

INSERT INTO `gast` (`g_ID`, `nname`, `vname`, `titel`, `strasse`, `plz`, `ort`, `land`, `tel`, `email`, `institut`, `i_strasse`, `i_plz`, `i_ort`, `i_land`, `i_tel`, `i_email`, `mwst`)
VALUES
	(1,'Jortner','Joshua','Prof.',NULL,NULL,NULL,NULL,NULL,NULL,'School of Chemistry','Tel Aviv University','69978','Tel Aviv','Israel',NULL,NULL,0.07),
	(2,'Nikolaus/Kohl','Elke',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Fritz-Haber-Institut','Faradayweg 4-6','14195','Berlin','DE',NULL,NULL,0.07),
	(3,'La Monaco','Birgit',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'FHI','Faradayweg 4-6','14195','Berlin','Deutschland',NULL,'carla@fhi-berlin.mpg.de',0.07),
	(4,'Scherbela','Michael',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TU Graz Institut für Festkörperphysik','Petersgasse 16/II','8010','Graz','Austria',NULL,'carla@fhi-berlin.mpg.de',0.07),
	(5,'Wang','Yanggang','Dr.',NULL,NULL,NULL,NULL,NULL,NULL,'Institute for Inerfacial Catalysis','Pacific Northwest National Laboratory','WA 99352','Richland','USA',NULL,'carla@fhi-berlin.mpg.de',0.07),
	(6,'Abreu','Elsa',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Boston University','One Silber Way','MA 02215','Boston','USA',NULL,NULL,0.07);

/*!40000 ALTER TABLE `gast` ENABLE KEYS */;
UNLOCK TABLES;


# Export von Tabelle kst_abt
# ------------------------------------------------------------

DROP TABLE IF EXISTS `kst_abt`;

CREATE TABLE `kst_abt` (
  `kst` varchar(10) NOT NULL,
  `abt_ID` varchar(3) NOT NULL,
  `abt_name` varchar(10) DEFAULT NULL,
  `abt_name_long` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`kst`,`abt_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Kostenstelle je Abteilung';

LOCK TABLES `kst_abt` WRITE;
/*!40000 ALTER TABLE `kst_abt` DISABLE KEYS */;

INSERT INTO `kst_abt` (`kst`, `abt_ID`, `abt_name`, `abt_name_long`)
VALUES
	('K001','4','PC','Physikalische Chemie'),
	('K100','5','TH','Theorie'),
	('K200','2','CP','Chemische Physik'),
	('K303','3','MP','Molekuelphysik'),
	('K500','1','AC','Anorganische Chemie');

/*!40000 ALTER TABLE `kst_abt` ENABLE KEYS */;
UNLOCK TABLES;


# Export von Tabelle Mitarbeiter
# ------------------------------------------------------------

DROP TABLE IF EXISTS `Mitarbeiter`;

CREATE TABLE `Mitarbeiter` (
  `ma_ID` int(11) NOT NULL,
  `userid` varchar(20) DEFAULT '',
  `nachname` varchar(50) NOT NULL,
  `vorname` varchar(50) NOT NULL,
  `abt_name` char(10) DEFAULT NULL,
  `anwesend` bit(1) DEFAULT b'0',
  `statusa` varchar(50) DEFAULT NULL,
  `message` varchar(50) DEFAULT NULL,
  `aktiv` tinyint(4) DEFAULT NULL COMMENT 'inaktiver MA=0; aktiver MA=1; Objekt=2',
  PRIMARY KEY (`ma_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `Mitarbeiter` WRITE;
/*!40000 ALTER TABLE `Mitarbeiter` DISABLE KEYS */;

INSERT INTO `Mitarbeiter` (`ma_ID`, `userid`, `nachname`, `vorname`, `abt_name`, `anwesend`, `statusa`, `message`, `aktiv`)
VALUES
	(1,'kohl','Kohl','Elke','VW',b'0',NULL,NULL,1),
	(2,'menzel_b','Menzel','Bettina','PC',b'0',NULL,NULL,1),
	(3,'paulsen','Paulsen','Jessica','VW',b'0',NULL,NULL,1),
	(4,'carla','Tschentscher','Carla','PP&B',b'0',NULL,NULL,1),
	(5,'braaker','Braaker','Andrea','MP',b'0',NULL,NULL,1),
	(6,'misch','Misch','Manuela','CP',b'0',NULL,NULL,1),
	(7,'nikolaus','Nikolaus','Daniela','CP',b'0',NULL,NULL,1),
	(8,'zentrale','Zentrale','Telefon','VW',b'0',NULL,NULL,1),
	(9,'haberland','Haberland ','Daria','PC',b'0',NULL,NULL,1);

/*!40000 ALTER TABLE `Mitarbeiter` ENABLE KEYS */;
UNLOCK TABLES;


# Export von Tabelle reserv
# ------------------------------------------------------------

DROP TABLE IF EXISTS `reserv`;

CREATE TABLE `reserv` (
  `r_ID` smallint(6) NOT NULL AUTO_INCREMENT,
  `g_ID` smallint(6) NOT NULL,
  `p_ID` smallint(6) NOT NULL,
  `von_dat` datetime NOT NULL,
  `bis_dat` datetime NOT NULL,
  `r_anzp` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'max(p_anzp) <= r_anzp',
  `resv_status` enum('vorreserviert','bestätigt','storniert') DEFAULT NULL,
  `rechn_status` enum('erstellt','bezahlt') DEFAULT NULL,
  `rechn_dat` datetime DEFAULT NULL,
  `anf_persknr` int(11) DEFAULT NULL COMMENT 'Anforderer',
  `anf_name` varchar(100) DEFAULT NULL,
  `betr_persknr` int(11) DEFAULT NULL COMMENT 'Betreuer',
  `aufschlag` double DEFAULT '0',
  `telefon_kost` double DEFAULT '0',
  `kst` varchar(10) DEFAULT NULL COMMENT 'Kostenübernahme auf KST',
  `r_bem` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`r_ID`),
  KEY `fk_reserv_gast_idx` (`g_ID`),
  KEY `fk_reserv_apt_preis_idx` (`p_ID`),
  KEY `fk_reserv_kst_abt_idx` (`kst`),
  CONSTRAINT `fk_reserv_apt_preis` FOREIGN KEY (`p_ID`) REFERENCES `apt_preis` (`p_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_reserv_gast` FOREIGN KEY (`g_ID`) REFERENCES `gast` (`g_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `reserv` WRITE;
/*!40000 ALTER TABLE `reserv` DISABLE KEYS */;

INSERT INTO `reserv` (`r_ID`, `g_ID`, `p_ID`, `von_dat`, `bis_dat`, `r_anzp`, `resv_status`, `rechn_status`, `rechn_dat`, `anf_persknr`, `anf_name`, `betr_persknr`, `aufschlag`, `telefon_kost`, `kst`, `r_bem`)
VALUES
	(1,1,12,'2016-10-03 00:00:00','2016-10-09 00:00:00',1,'vorreserviert',NULL,NULL,1402,NULL,NULL,NULL,NULL,'K303',NULL),
	(2,2,34,'2016-08-01 00:00:00','2016-09-29 00:00:00',1,'bestätigt','erstellt','2016-07-27 00:00:00',NULL,NULL,NULL,0,0,NULL,NULL),
	(3,3,3,'2016-08-01 00:00:00','2016-08-30 00:00:00',1,'vorreserviert',NULL,NULL,2937,NULL,NULL,0,0,NULL,NULL),
	(4,4,10,'2016-09-01 00:00:00','2016-09-01 00:00:00',1,'bestätigt',NULL,NULL,2937,NULL,NULL,0,0,NULL,'selbstzahler'),
	(5,5,3,'2016-09-01 00:00:00','2016-09-29 00:00:00',1,'bestätigt',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
	(6,6,14,'2016-11-01 00:00:00','2016-11-04 00:00:00',1,'storniert',NULL,NULL,837,NULL,NULL,0,0,NULL,'Testbelegung');

/*!40000 ALTER TABLE `reserv` ENABLE KEYS */;
UNLOCK TABLES;


# Export von Tabelle user_recht
# ------------------------------------------------------------

DROP TABLE IF EXISTS `user_recht`;

CREATE TABLE `user_recht` (
  `persknr` int(11) NOT NULL,
  `userid` varchar(10) NOT NULL,
  `recht` varchar(2) NOT NULL COMMENT 'Recht Apartmentverwaltung',
  `rechtwv` varchar(2) DEFAULT NULL COMMENT 'Recht Wohnungsverwaltung',
  PRIMARY KEY (`persknr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `user_recht` WRITE;
/*!40000 ALTER TABLE `user_recht` DISABLE KEYS */;

INSERT INTO `user_recht` (`persknr`, `userid`, `recht`, `rechtwv`)
VALUES
    (1,'kohl','w','w'),
	(2,'menzel_b','r',NULL),
	(3,'paulsen','r',NULL),
	(4,'carla','w','w'),
	(5,'braaker','w',NULL),
	(6,'misch','r',NULL),
	(7,'nikolaus','r',NULL),
	(8,'zentrale','w','w'),
	(9,'haberland','r',NULL),
	(76,'doelln','r',NULL),
	(233,'junkes','r','r'),
	(245,'h12k07','r',NULL),
	(398,'roeper','r',NULL),
	(422,'sylvia','w',NULL),
	(446,'hettwer','w','w'),
	(463,'stottko','w',NULL),
	(521,'wieczorek','r',NULL),
	(721,'stein','w','w'),
	(1245,'capi','w','w'),
	(1337,'mike','w',NULL),
	(1350,'gerster','w',NULL),
	(1416,'voss','r',NULL),
	(1420,'wilczek','r',NULL),
	(1449,'egri','w','w'),
	(1481,'wachmann','r',NULL),
	(2005,'pach','r',NULL),
	(2200,'andrea','r',NULL),
	(2488,'traeger','r',NULL),
	(2607,'spok','w','w'),
	(2777,'vera.bunkh','r',NULL),
	(2854,'schwarzer','w','w'),
	(2937,'monaco','r',NULL),
	(3109,'delgado','r',NULL),
	(3187,'krueger','r',NULL),
	(3307,'naedele','r','r'),
	(3425,'paulsen','r',NULL),
	(3463,'kabakci','r',NULL);

/*!40000 ALTER TABLE `user_recht` ENABLE KEYS */;
UNLOCK TABLES;



/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;