DB.sql

-- --------------------------------------------------------
-- Host:                         db2.rz-berlin.mpg.de
-- Server Version:               5.5.47-0ubuntu0.14.04.1 - (Ubuntu)
-- Server Betriebssystem:        debian-linux-gnu
-- HeidiSQL Version:             9.3.0.4984
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!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' */;

-- Exportiere Datenbank Struktur für zeit
DROP DATABASE IF EXISTS `zeit`;
CREATE DATABASE IF NOT EXISTS `zeit` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `zeit`;


-- Exportiere Struktur von Tabelle zeit.Abteilung
DROP TABLE IF EXISTS `Abteilung`;
CREATE TABLE IF NOT EXISTS `Abteilung` (
  `abt_ID` varchar(3) NOT NULL,
  `abt_name` varchar(20) NOT NULL COMMENT 'Abteilungsbezeichnung',
  `abt_long` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`abt_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Tabelle zeit.Arb_Gruppe
DROP TABLE IF EXISTS `Arb_Gruppe`;
CREATE TABLE IF NOT EXISTS `Arb_Gruppe` (
  `ag_ID` smallint(6) NOT NULL AUTO_INCREMENT,
  `ag_bez` varchar(100) NOT NULL,
  `ag_bezk` varchar(10) DEFAULT NULL,
  `abt_ID` varchar(3) NOT NULL,
  `gn_maID` int(11) DEFAULT NULL COMMENT 'Genehmiger',
  `vg_maID` int(11) DEFAULT NULL COMMENT 'Vertreter Genehmiger',
  `pr_maID` int(11) DEFAULT NULL COMMENT 'Prüfer',
  `vp_maID` int(11) DEFAULT NULL COMMENT 'Vertreter Prüfer',
  PRIMARY KEY (`ag_ID`),
  KEY `fk_abt_arbgruppe` (`abt_ID`),
  KEY `fkt_ma_arbgruppe_idx` (`gn_maID`),
  KEY `fkt_ma1_arbgruppe_idx` (`vg_maID`),
  KEY `fk_ma2_arbgruppe_idx` (`pr_maID`),
  KEY `fk_ma3_arbgruppe_idx` (`vp_maID`),
  CONSTRAINT `fkt_ma1_arbgruppe` FOREIGN KEY (`vg_maID`) REFERENCES `Mitarbeiter` (`maID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `fkt_ma_arbgruppe` FOREIGN KEY (`gn_maID`) REFERENCES `Mitarbeiter` (`maID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `fk_abt_arbgruppe` FOREIGN KEY (`abt_ID`) REFERENCES `Abteilung` (`abt_ID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `fk_ma2_arbgruppe` FOREIGN KEY (`pr_maID`) REFERENCES `Mitarbeiter` (`maID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `fk_ma3_arbgruppe` FOREIGN KEY (`vp_maID`) REFERENCES `Mitarbeiter` (`maID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Tabelle zeit.AZModel
DROP TABLE IF EXISTS `AZModel`;
CREATE TABLE IF NOT EXISTS `AZModel` (
  `model_nr` smallint(5) unsigned NOT NULL,
  `tag_nr` tinyint(3) unsigned NOT NULL,
  `model_bez` varchar(50) DEFAULT NULL,
  `tagstd` time NOT NULL,
  `maxstd` time DEFAULT NULL,
  `min_start` time DEFAULT NULL,
  `max_ende` time DEFAULT NULL,
  `kernz_start` time DEFAULT NULL,
  `kernz_ende` time DEFAULT NULL,
  `pausestd` time DEFAULT NULL,
  `pause_start` time DEFAULT NULL,
  `pause_ende` time DEFAULT NULL,
  `no_btag` tinyint(3) unsigned DEFAULT NULL COMMENT 'keine Brueckentage zulassen',
  `pause_fix` tinyint(3) unsigned DEFAULT NULL COMMENT 'Pause in fixem Zeitraum',
  PRIMARY KEY (`model_nr`,`tag_nr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Prozedur zeit.azmodel_flist
DROP PROCEDURE IF EXISTS `azmodel_flist`;
DELIMITER //
CREATE DEFINER=`root`@`hyperion.rz-berlin.mpg.de` PROCEDURE `azmodel_flist`(
    IN sort INT,        
    IN dir VARCHAR(4),  
    IN ffnr INT,        
    IN filter VARCHAR(50),
    IN stt INT,         
    IN zeil INT,        
    OUT anz INT        
)
BEGIN

-- Author:      Bettina Schwarzer, FHI
-- Create Date: 08.12.2014
-- Sort, Filtern nach ausgewählten Feldern in SELECT-Liste:
-- model_nr,tag_nr,model_bez,tagstd

	DECLARE strsql VARCHAR(500);
	DECLARE wher VARCHAR(100);
	DECLARE sot VARCHAR(10);

	SET @strsql = 'SELECT model_nr,model_nr,tag_nr,model_bez,tagstd,maxstd,min_start,max_ende,
					kernz_start,kernz_ende,pausestd,pause_start,pause_ende,no_btag,pause_fix
					FROM AZModel';

    IF (sort<2 OR sort>5) THEN SET sort = 2; END IF;
    IF (dir > '!') THEN SET dir = dir; ELSE SET dir = ''; END IF;
    
    
    IF (ffnr<2 OR ffnr>5 OR ISNULL(filter) OR filter<='!') THEN
        SET @strsql = @strsql;
    ELSE
    BEGIN
        IF (ffnr>1 AND ffnr<6 AND filter>'!') THEN
            CASE ffnr
                WHEN 2 THEN SET wher = CONCAT(' model_nr = ',filter);  
                WHEN 3 THEN SET wher = CONCAT(' tag_nr = ',filter);  
                WHEN 4 THEN SET wher = CONCAT(' model_bez LIKE \'',filter, '%\'');  
                WHEN 5 THEN SET wher = CONCAT(' tagstd LIKE \'%',filter, '%\'');  
            END CASE;
        END IF;
        IF NOT ISNULL(wher) THEN
            SET @strsql = CONCAT(@strsql, ' WHERE ', wher);
        END IF;
	END;
	END IF;
    SET sot=CONCAT(sort, ' ' ,dir);

    SET @strsql = CONCAT(@strsql, ' ORDER BY ', CAST(sot AS CHAR));
    
    
    SET @strsql = CONCAT(@strsql, ' LIMIT ', stt, ', ', zeil);


	PREPARE strsql FROM @strsql;
    EXECUTE strsql;
    DEALLOCATE PREPARE strsql;

	SET @strsql = 'SELECT COUNT(*) FROM AZModel';
	IF NOT ISNULL(wher) THEN
		SET @strsql = CONCAT(@strsql, ' WHERE ', wher , ' INTO @cnt');

		PREPARE strsql FROM @strsql;
        EXECUTE strsql;
        SET anz = @cnt;
        DEALLOCATE PREPARE strsql;
	ELSE
		SELECT COUNT(*) FROM AZModel INTO anz;
	END IF;

END//
DELIMITER ;


-- Exportiere Struktur von Tabelle zeit.Feiertag
DROP TABLE IF EXISTS `Feiertag`;
CREATE TABLE IF NOT EXISTS `Feiertag` (
  `ft_ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `daymon` varchar(4) NOT NULL,
  `year` varchar(4) NOT NULL,
  PRIMARY KEY (`ft_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Tabelle zeit.MAZeit_Status
DROP TABLE IF EXISTS `MAZeit_Status`;
CREATE TABLE IF NOT EXISTS `MAZeit_Status` (
  `mzs_ID` int(11) NOT NULL AUTO_INCREMENT,
  `maID` int(11) NOT NULL,
  `datum` date NOT NULL,
  `zt_ID` smallint(5) unsigned NOT NULL,
  `zstatus` enum('beantragt','geprueft','genehmigt','abgelehnt','storniert') DEFAULT NULL,
  `abst` varchar(100) DEFAULT NULL COMMENT 'Urlaub/GZ: abgesprochen mit',
  `bem` varchar(100) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `userid` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`mzs_ID`),
  KEY `fk_mazeit_status_mitarbeiter_idx` (`maID`),
  KEY `fk_mazeit_status_zeittyp_idx` (`zt_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Tabelle zeit.MAZeit_Status_alt
DROP TABLE IF EXISTS `MAZeit_Status_alt`;
CREATE TABLE IF NOT EXISTS `MAZeit_Status_alt` (
  `mzs_ID` int(11) NOT NULL AUTO_INCREMENT,
  `maID` int(11) NOT NULL,
  `datum_ab` datetime NOT NULL,
  `datum_bis` datetime NOT NULL,
  `zt_ID` smallint(5) unsigned NOT NULL,
  `zstatus` enum('beantragt','geprueft','genehmigt','abgelehnt','storniert') DEFAULT NULL,
  `utg_gen` tinyint(3) unsigned DEFAULT NULL COMMENT 'genehmigte Urlaubstage',
  `bem_ug` varchar(100) DEFAULT NULL COMMENT 'Urlaub/GZ: abgesprochen mit',
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `userid` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`mzs_ID`),
  KEY `fk_mazeit_status_mitarbeiter_idx` (`maID`),
  KEY `fk_mazeit_status_zeittyp_idx` (`zt_ID`),
  CONSTRAINT `fk_mazeit_status_mitarbeiter` FOREIGN KEY (`maID`) REFERENCES `Mitarbeiter` (`maID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_mazeit_status_zeittyp` FOREIGN KEY (`zt_ID`) REFERENCES `Zeittyp` (`zt_ID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Tabelle zeit.MA_AZModel
DROP TABLE IF EXISTS `MA_AZModel`;
CREATE TABLE IF NOT EXISTS `MA_AZModel` (
  `maID` int(11) NOT NULL,
  `model_nr` smallint(5) unsigned NOT NULL,
  `datum_ab` date NOT NULL,
  `utage` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`maID`,`model_nr`,`datum_ab`),
  KEY `fk_ma_azmodel_mitarbeiter_idx` (`maID`),
  KEY `fk_ma_azmodel_azmodel_idx` (`model_nr`),
  CONSTRAINT `fk_ma_azmodel_azmodel` FOREIGN KEY (`model_nr`) REFERENCES `AZModel` (`model_nr`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ma_azmodel_mitarbeiter` FOREIGN KEY (`maID`) REFERENCES `Mitarbeiter` (`maID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Prozedur zeit.ma_flist
DROP PROCEDURE IF EXISTS `ma_flist`;
DELIMITER //
CREATE DEFINER=`root`@`hyperion.rz-berlin.mpg.de` PROCEDURE `ma_flist`(
    IN sort INT,        
    IN dir VARCHAR(4),  
    IN ffnr INT,        
    IN filter VARCHAR(50),
    IN stt INT,         
    IN zeil INT,        
    OUT anz INT        
)
BEGIN

-- Author:      Bettina Schwarzer, FHI
-- Create Date: 11.03.2015
-- Sort, Filtern nach ausgewählten Feldern in SELECT-Liste:
-- name,abteilung,arbeitsgruppe

	DECLARE strsql VARCHAR(500);
	DECLARE wher VARCHAR(100);
	DECLARE sot VARCHAR(10);

	DROP TABLE IF EXISTS __maz;
	DROP TABLE IF EXISTS __mazmod;

	CREATE TEMPORARY TABLE __maz
	(   maID INT,
		datum_ab datetime,
        PRIMARY KEY (`maID`)) ENGINE=MEMORY;

	INSERT INTO __maz 
	SELECT maID, MAX(datum_ab) FROM MA_AZModel
	GROUP BY maID;

	CREATE TEMPORARY TABLE __mazmod
	(   maID INT,
		model_nr SMALLINT,
		datum_ab datetime,
        PRIMARY KEY (`maID`)) ENGINE=MEMORY;
	INSERT INTO __mazmod
	SELECT m.maID,m.model_nr,m.datum_ab FROM MA_AZModel m 
		INNER JOIN __maz d ON (m.maID=d.maID AND m.datum_ab=d.datum_ab);
	

	SET @strsql = 'SELECT m.maID,CONCAT (nachname, IF (vorname>'''',CONCAT('', '',vorname), '''')) AS nvname, 
            abt_name, ag_bez, userid, rf_nr, model_nr, CASE editall WHEN 1 THEN '' x '' ELSE '''' END AS editall, m.datum_bis
            FROM Mitarbeiter m LEFT OUTER JOIN Arb_Gruppe g ON m.ag_ID=g.ag_ID
			LEFT OUTER JOIN Abteilung a ON g.abt_ID=a.abt_ID 
            LEFT OUTER JOIN Transponder t ON m.rfid=t.rfid
			LEFT OUTER JOIN __mazmod z ON m.maID=z.maID';	


    IF (sort<2 OR sort>8) THEN SET sort = 2; END IF;
    IF (dir > '!') THEN SET dir = dir; ELSE SET dir = ''; END IF;
    
    
    IF (ffnr<2 OR ffnr>4 OR ISNULL(filter) OR filter<='!') THEN
        SET @strsql = @strsql;
    ELSE
    BEGIN
        IF (ffnr>1 AND ffnr<5 AND filter>'!') THEN
            CASE ffnr
                WHEN 2 THEN SET wher = CONCAT(' CONCAT(nachname, IF (vorname>'''',CONCAT('', '',vorname), '''')) LIKE \'',filter, '%\'');  
                WHEN 3 THEN SET wher = CONCAT(' abt_name LIKE \'',filter,'%\'');  
                WHEN 4 THEN SET wher = CONCAT(' ag_bez LIKE \'',filter, '%\'');  
            END CASE;
        END IF;
        IF NOT ISNULL(wher) THEN
            SET @strsql = CONCAT(@strsql, ' WHERE ', wher);
        END IF;
	END;
	END IF;
    SET sot=CONCAT(sort, ' ' ,dir);
	IF (sort=3) THEN SET sot=CONCAT(sort, ' ' ,dir, ', 4'); END IF;

    SET @strsql = CONCAT(@strsql, ' ORDER BY ', CAST(sot AS CHAR));
    
    
    SET @strsql = CONCAT(@strsql, ' LIMIT ', stt, ', ', zeil);

	PREPARE strsql FROM @strsql;
    EXECUTE strsql;
    DEALLOCATE PREPARE strsql;
	
 	SET @strsql = 'SELECT COUNT(*) FROM Mitarbeiter m LEFT OUTER JOIN Arb_Gruppe g ON m.ag_ID=g.ag_ID
			LEFT OUTER JOIN Abteilung a ON g.abt_ID=a.abt_ID'; 

	IF NOT ISNULL(wher) THEN
		SET @strsql = CONCAT(@strsql, ' WHERE ', wher , ' INTO @cnt');

		PREPARE strsql FROM @strsql;
        EXECUTE strsql;
        SET anz = @cnt;
        DEALLOCATE PREPARE strsql;
	ELSE
		SELECT COUNT(*) FROM Mitarbeiter INTO anz;
	END IF;

END//
DELIMITER ;


-- Exportiere Struktur von Prozedur zeit.ma_flist1
DROP PROCEDURE IF EXISTS `ma_flist1`;
DELIMITER //
CREATE DEFINER=`root`@`hyperion.rz-berlin.mpg.de` PROCEDURE `ma_flist1`(IN `sort` INT, IN `dir` VARCHAR(4), IN `ffnr` INT, IN `filter` VARCHAR(50), IN `stt` INT, IN `zeil` INT, OUT `anz` INT        
)
BEGIN

-- Author:      Bettina Schwarzer, FHI
-- Create Date: 11.02.2016
-- Sort, Filtern nach ausgewählten Feldern in SELECT-Liste:
-- name,abteilung,arbeitsgruppe

	DECLARE strsql VARCHAR(500);
	DECLARE wher VARCHAR(100);
	DECLARE sot VARCHAR(10);
	DECLARE vid,mid SMALLINT;
	DECLARE rfn,rfs VARCHAR(100);
    DECLARE done INT DEFAULT 0;
    

	DROP TABLE IF EXISTS __maz;
	DROP TABLE IF EXISTS __mat;
	DROP TABLE IF EXISTS __mazmod;

	CREATE TEMPORARY TABLE __maz
	(   maID INT,
		datum_ab datetime,
        PRIMARY KEY (`maID`)) ENGINE=MEMORY;

	INSERT INTO __maz 
	SELECT maID, MAX(datum_ab) FROM MA_AZModel
	GROUP BY maID;

	CREATE TEMPORARY TABLE __mazmod
	(   maID INT,
		model_nr SMALLINT,
		datum_ab datetime,
        PRIMARY KEY (`maID`)) ENGINE=MEMORY;
	INSERT INTO __mazmod
	SELECT m.maID,m.model_nr,m.datum_ab FROM MA_AZModel m 
		INNER JOIN __maz d ON (m.maID=d.maID AND m.datum_ab=d.datum_ab);
	

	CREATE TEMPORARY TABLE __mat
	(	maID INT,
		nvname VARCHAR(100),
		abt_name VARCHAR(20),
		ag_bez VARCHAR(100),
		userid VARCHAR(15),
		rf_nr VARCHAR(100),
		model_nr SMALLINT,
		editall CHAR(1),
		datum_bis datetime,
        PRIMARY KEY (`maID`)) ENGINE=MEMORY;
	INSERT INTO __mat
	SELECT m.maID,CONCAT (nachname, IF (vorname>'',CONCAT(', ',vorname), '')) AS nvname, 
            abt_name, ag_bez, userid, NULL, model_nr, CASE editall WHEN 1 THEN 'x' ELSE '' END AS editall, m.datum_bis
            FROM Mitarbeiter m LEFT OUTER JOIN Arb_Gruppe g ON m.ag_ID=g.ag_ID
			LEFT OUTER JOIN Abteilung a ON g.abt_ID=a.abt_ID
			LEFT OUTER JOIN __mazmod z ON m.maID=z.maID;	


/*	SET @strsql = 'SELECT m.maID,CONCAT (nachname, IF (vorname>'''',CONCAT('', '',vorname), '''')) AS nvname, 
            abt_name, ag_bez, userid, rf_nr, model_nr, CASE editall WHEN 1 THEN '' x '' ELSE '''' END AS editall, m.datum_bis
            FROM Mitarbeiter m LEFT OUTER JOIN Arb_Gruppe g ON m.ag_ID=g.ag_ID
			LEFT OUTER JOIN Abteilung a ON g.abt_ID=a.abt_ID 
            LEFT OUTER JOIN Transponder t ON m.maID=t.maID
			LEFT OUTER JOIN __mazmod z ON m.maID=z.maID';	
*/
	BEGIN
		DECLARE acurs CURSOR FOR
		SELECT maID,rf_nr FROM Transponder WHERE NOT (maID IS NULL) ORDER BY maID;

		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

			SET done=0;
			OPEN acurs;
			FETCH acurs INTO mid, rfn;
			IF done = 0 THEN
				SET vid = mid;
				SET rfs = IFNULL(rfn,'');
				WHILE done=0 DO
					FETCH acurs INTO mid, rfn;
					IF (mid=vid AND done=0) THEN
						SET rfs = CONCAT(IF(rfs>'',CONCAT(rfs,', '),''),IFNULL(rfn,''));
					ELSE
					BEGIN
						UPDATE __mat SET rf_nr = rfs WHERE maID=vid;
						SET rfs = IFNULL(rfn,'');
						SET vid = mid;
					END;
					END IF;
				END WHILE;
			END IF;
			CLOSE acurs;
	END;
    IF (sort<2 OR sort>8) THEN SET sort = 2; END IF;
    IF (dir > '!') THEN SET dir = dir; ELSE SET dir = ''; END IF;
    
	SET @strsql = 'SELECT maID,nvname, 
            abt_name, ag_bez, userid, rf_nr, model_nr, editall, datum_bis
            FROM __mat';
    
    IF (ffnr<2 OR ffnr>4 OR ISNULL(filter) OR filter<='!') THEN
        SET @strsql = @strsql;
    ELSE
    BEGIN
        IF (ffnr>1 AND ffnr<5 AND filter>'!') THEN
            CASE ffnr
                WHEN 2 THEN SET wher = CONCAT(' nvname LIKE \'',filter, '%\'');  
                WHEN 3 THEN SET wher = CONCAT(' abt_name LIKE \'',filter,'%\'');  
                WHEN 4 THEN SET wher = CONCAT(' ag_bez LIKE \'',filter, '%\'');  
            END CASE;
        END IF;
        IF NOT ISNULL(wher) THEN
            SET @strsql = CONCAT(@strsql, ' WHERE ', wher);
        END IF;
	END;
	END IF;
    SET sot=CONCAT(sort, ' ' ,dir);
	IF (sort=3) THEN SET sot=CONCAT(sort, ' ' ,dir, ', 4'); END IF;

    SET @strsql = CONCAT(@strsql, ' ORDER BY ', CAST(sot AS CHAR));
    
    
    SET @strsql = CONCAT(@strsql, ' LIMIT ', stt, ', ', zeil);
	PREPARE strsql FROM @strsql;
    EXECUTE strsql;
    DEALLOCATE PREPARE strsql;
	
 	SET @strsql = 'SELECT COUNT(*) FROM __mat'; 

	IF NOT ISNULL(wher) THEN
		SET @strsql = CONCAT(@strsql, ' WHERE ', wher , ' INTO @cnt');

		PREPARE strsql FROM @strsql;
        EXECUTE strsql;
        SET anz = @cnt;
        DEALLOCATE PREPARE strsql;
	ELSE
		SELECT COUNT(*) FROM Mitarbeiter INTO anz;
	END IF;

END//
DELIMITER ;


-- Exportiere Struktur von Tabelle zeit.MA_Saldo
DROP TABLE IF EXISTS `MA_Saldo`;
CREATE TABLE IF NOT EXISTS `MA_Saldo` (
  `maID` int(11) NOT NULL DEFAULT '0',
  `datum_saldo` date NOT NULL COMMENT '1. des Folgemonats',
  `saldostd` time DEFAULT NULL,
  `saldosec` int(11) DEFAULT NULL COMMENT 'Saldosekunden',
  `saldovz` char(1) DEFAULT NULL,
  `urlaub_akttg` tinyint(3) unsigned DEFAULT NULL COMMENT 'aktuelle Urlaubstage',
  `kz_edit` tinyint(3) unsigned DEFAULT NULL COMMENT 'manuell eingetragen',
  PRIMARY KEY (`maID`,`datum_saldo`),
  CONSTRAINT `fk_ma_ma_saldo` FOREIGN KEY (`maID`) REFERENCES `Mitarbeiter` (`maID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Tabelle zeit.MA_Zeit
DROP TABLE IF EXISTS `MA_Zeit`;
CREATE TABLE IF NOT EXISTS `MA_Zeit` (
  `mz_ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `datumzeit` datetime NOT NULL,
  `maID` int(11) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  `zt_ID` smallint(5) unsigned DEFAULT NULL,
  `mz_bem` varchar(100) DEFAULT NULL COMMENT 'Bemerkung zu Korrektur',
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `userid` varchar(10) DEFAULT NULL,
  `kok` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`mz_ID`),
  KEY `fk datzei_mitarbeiter_idx` (`maID`),
  KEY `fk_datzeit_zeittyp_idx` (`zt_ID`),
  CONSTRAINT `fk_datzeit_mitarbeiter` FOREIGN KEY (`maID`) REFERENCES `Mitarbeiter` (`maID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_datzeit_zeittyp` FOREIGN KEY (`zt_ID`) REFERENCES `Zeittyp` (`zt_ID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Tabelle zeit.MA_Zeit_History
DROP TABLE IF EXISTS `MA_Zeit_History`;
CREATE TABLE IF NOT EXISTS `MA_Zeit_History` (
  `h_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mz_ID` int(11) unsigned NOT NULL,
  `datumzeit` datetime NOT NULL,
  `maID` int(11) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  `zt_ID` smallint(5) unsigned DEFAULT NULL,
  `mz_bem` varchar(100) DEFAULT NULL COMMENT 'Bemerkung zu Korrektur',
  `timestamp` timestamp NULL DEFAULT NULL,
  `userid` varchar(10) DEFAULT NULL,
  `kern_ok` int(1) DEFAULT NULL,
  PRIMARY KEY (`h_ID`),
  KEY `fk datzei_mitarbeiter_idx` (`maID`),
  KEY `fk_datzeit_zeittyp_idx` (`zt_ID`),
  CONSTRAINT `MA_Zeit_History_ibfk_1` FOREIGN KEY (`maID`) REFERENCES `Mitarbeiter` (`maID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `MA_Zeit_History_ibfk_2` FOREIGN KEY (`zt_ID`) REFERENCES `Zeittyp` (`zt_ID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Tabelle zeit.Mitarbeiter
DROP TABLE IF EXISTS `Mitarbeiter`;
CREATE TABLE IF NOT EXISTS `Mitarbeiter` (
  `maID` int(11) NOT NULL,
  `nachname` varchar(50) NOT NULL,
  `vorname` varchar(50) NOT NULL,
  `rfid` varchar(20) NOT NULL DEFAULT '',
  `anwesend` char(1) NOT NULL DEFAULT '0',
  `statusa` varchar(50) NOT NULL DEFAULT '',
  `message` varchar(50) NOT NULL DEFAULT '',
  `userid` varchar(15) DEFAULT '',
  `ag_ID` smallint(6) DEFAULT NULL,
  `editall` tinyint(4) DEFAULT NULL COMMENT '1 = Recht Daten aller MA editierbar',
  `pers_uid` varchar(10) DEFAULT NULL COMMENT 'userid personalbearb.',
  `datum_bis` date DEFAULT NULL,
  PRIMARY KEY (`maID`),
  KEY `fk_Mitarbeiter_Transponder_idx` (`rfid`),
  KEY `fk_Mitarbeiter_Arb_Gruppe_idx` (`ag_ID`),
  CONSTRAINT `fk_Mitarbeiter_Arb_Gruppe` FOREIGN KEY (`ag_ID`) REFERENCES `Arb_Gruppe` (`ag_ID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `fk_Mitarbeiter_Transponder` FOREIGN KEY (`rfid`) REFERENCES `Transponder` (`rfid`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Prozedur zeit.mitarbeiter_aktualisieren
DROP PROCEDURE IF EXISTS `mitarbeiter_aktualisieren`;
DELIMITER //
CREATE DEFINER=`root`@`hyperion.rz-berlin.mpg.de` PROCEDURE `mitarbeiter_aktualisieren`()
BEGIN

-- Author:      Bettina Schwarzer, FHI
-- Create Date: 01.08.2014, 17.09.2014, 24.10.2014, 30.01.2015(nach 1 Jahr delete)
-- Aktualisiert zeit.Mitarbeiter aus fhiiqm.Mitarbeiter

	INSERT INTO zeit.Mitarbeiter (maID,nachname,vorname,userid)
	SELECT persknr,fm.nachname, fm.vorname, substring_index(email,'@',1) AS userid
	FROM fhiiqm.Mitarbeiter fm LEFT OUTER JOIN zeit.Mitarbeiter zm ON fm.persknr=zm.maID
	WHERE aktiv=1 AND maID IS NULL;

	DELETE FROM zeit.Mitarbeiter
	WHERE maID IN
	(SELECT persknr FROM fhiiqm.Mitarbeiter WHERE (aktiv <> 1 AND timestamp + INTERVAL 1 YEAR < NOW()));

	DELETE FROM zeit.Mitarbeiter
	WHERE maID NOT IN
	(SELECT persknr FROM fhiiqm.Mitarbeiter);

	UPDATE zeit.Mitarbeiter zm LEFT JOIN fhiiqm.Mitarbeiter fm ON fm.persknr=zm.maID
	SET zm.nachname=fm.nachname,
	zm.Vorname=fm.vorname;
	
	
	SELECT * FROM zeit.Mitarbeiter ORDER BY nachname, vorname;
END//
DELIMITER ;


-- Exportiere Struktur von Prozedur zeit.open_items
DROP PROCEDURE IF EXISTS `open_items`;
DELIMITER //
CREATE DEFINER=`root`@`hyperion.rz-berlin.mpg.de` PROCEDURE `open_items`()
BEGIN

-- Author:      Bettina Schwarzer, FHI
-- Create Date: 09.07.2015
-- findet alle beantragten und geprüften Anträge
-- jeweils letzer Eintrag des selben Datums aus 'MAZeit_Status' wird aus gewählt

	DECLARE strsql VARCHAR(500);

	DROP TABLE IF EXISTS __mazst;
	DROP TABLE IF EXISTS __mazstv;

	CREATE TEMPORARY TABLE __mazst
	(   mzsID INT,
		maID INT,
		datum datetime,
        PRIMARY KEY (`mzsID`)) ENGINE=MEMORY;

	INSERT INTO __mazst 
	SELECT max(mzs_ID),maID,datum FROM zeit.MAZeit_Status
		GROUP BY maID,datum;
-- SELECT * FROM __mazst;

	CREATE TEMPORARY TABLE __mazstv
	(   mzsID INT,
		maID INT,
		datum datetime,
		zt_ID SMALLINT,
		zstatus VARCHAR(10),
		abst VARCHAR(100),
		timestamp datetime,
        PRIMARY KEY (`mzsID`)) ENGINE=MEMORY;

	INSERT INTO __mazstv 
	SELECT mzs_ID,z.maID,z.datum,zt_ID,zstatus,abst, timestamp FROM zeit.MAZeit_Status z RIGHT OUTER JOIN __mazst s
		ON z.mzs_ID = s.mzsID
		group by timestamp
		ORDER BY maID,datum;
-- SELECT * FROM __mazstv;

SELECT s.maID,CONCAT(nachname,IF (vorname>'',CONCAT(', ',vorname), '') ) AS nvname, datum,s.zt_ID,zt_bez,zstatus,abst,ag_ID, timestamp
                FROM zeit.Mitarbeiter m RIGHT OUTER JOIN __mazstv s ON m.maID=s.maID
                JOIN zeit.Zeittyp t ON s.zt_ID=t.zt_ID
                WHERE (zstatus = 'beantragt' OR zstatus = 'geprueft') 
                ORDER BY CONCAT(nachname,IF (vorname>'',CONCAT(', ',vorname), '') ),datum;

END//
DELIMITER ;


-- Exportiere Struktur von Tabelle zeit.Transponder
DROP TABLE IF EXISTS `Transponder`;
CREATE TABLE IF NOT EXISTS `Transponder` (
  `rfid` varchar(20) NOT NULL,
  `maID` int(11) DEFAULT NULL,
  `rf_nr` varchar(10) NOT NULL,
  `rf_bem` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`rfid`),
  KEY `fk_ma_tra` (`maID`),
  CONSTRAINT `fk_ma_tra` FOREIGN KEY (`maID`) REFERENCES `Mitarbeiter` (`maID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt


-- Exportiere Struktur von Prozedur zeit.urlaub_anspruch
DROP PROCEDURE IF EXISTS `urlaub_anspruch`;
DELIMITER //
CREATE DEFINER=`root`@`hyperion.rz-berlin.mpg.de` PROCEDURE `urlaub_anspruch`(IN `maid` INTEGER, IN `yearalt` INTEGER)
BEGIN

-- Author:      Bettina Schwarzer, FHI
-- Create Date: 28.08.2015
-- berechnet Urlaubsanspruch für Folgejahr (yearalt+1) und Mitarbeiter (maid)
-- wird bei Saldoberechnung immer ausgeführt -> conjob saldo.php auf 'achilleus'

	DECLARE urlgen, urlstorn, urlgesamt, urlstd, uaspalt, uasp INTEGER;
	DECLARE maxdat DATETIME;
	DECLARE strsql VARCHAR(500);

	-- genehmigter Urlaub im Jahr 'yearalt'
	SET @strsql = CONCAT('SELECT COUNT(distinct datum) FROM MAZeit_Status
		            			WHERE maID=',maid,' AND YEAR(datum) = ',yearalt,' AND zt_ID=10 AND zstatus=\'genehmigt\' INTO @ug');
	PREPARE strsql FROM @strsql;
	EXECUTE strsql;
	SET urlgen = @ug;
	DEALLOCATE PREPARE strsql;
	IF ISNULL(urlgen) THEN SET urlgen=0; END IF;
	-- SELECT urlgen;
	
	-- stornierter und abgelehnter Urlaub im Jahr 'yearalt'
	SET @strsql = CONCAT('select count(distinct a.datum) from MAZeit_Status a 
									join MAZeit_Status b on a.datum = b.datum 
									where a.mzs_ID <> b.mzs_ID and year(a.datum) = ',yearalt,' and a.zt_ID = 10 
									and a.zstatus = \'genehmigt\' and (b.zt_ID = 9 or b.zstatus = \'abgelehnt\') and a.maID = ',maid,' and b.maID = ',maid,' and b.timestamp > a.timestamp INTO @ust');
	PREPARE strsql FROM @strsql;
	EXECUTE strsql;
	SET urlstorn = @ust;
	DEALLOCATE PREPARE strsql;
	IF ISNULL(urlstorn) THEN SET urlstorn=0; END IF;
	-- SELECT urlstorn;
	
	SET urlgesamt = urlgen - urlstorn;
	-- SELECT urlgesamt;

	-- Standard-Urlaub ab max. 'yearalt'+1
	SET @strsql = CONCAT('SELECT MAX(datum_ab) FROM MA_AZModel WHERE YEAR(datum_ab) <=',yearalt+1, ' AND maID=',maid,' INTO @maxdat');
	PREPARE strsql FROM @strsql;
	EXECUTE strsql;
	SET maxdat = @maxdat;
	DEALLOCATE PREPARE strsql;
	-- SELECT maxdat;

	IF NOT ISNULL(maxdat) THEN
		SET @strsql = CONCAT('SELECT utage FROM MA_AZModel WHERE maID=',maid, ' AND datum_ab=\'',maxdat,'\' INTO @us');
		PREPARE strsql FROM @strsql;
		EXECUTE strsql;
		SET urlstd = @us;
		DEALLOCATE PREPARE strsql;
	END IF;
	IF ISNULL(urlstd) THEN SET urlstd=30; END IF;
	-- SELECT urlstd;

	-- aktuelle Urlaubstage <= Jahr 'yearalt' und manuell eingetragen
	SET @strsql = CONCAT('SELECT MAX(datum_saldo) FROM MA_Saldo WHERE YEAR(datum_saldo) <=',yearalt, ' AND maID=',maid,' AND kz_edit=1 INTO @maxdat');
	PREPARE strsql FROM @strsql;
	EXECUTE strsql;
	SET maxdat = @maxdat;
	DEALLOCATE PREPARE strsql;
	-- SELECT maxdat;

	IF NOT ISNULL(maxdat) THEN
		SET @strsql = CONCAT('SELECT urlaub_akttg FROM MA_Saldo WHERE datum_saldo=\'',maxdat,'\' AND maID=',maid,' INTO @uaspa');
		PREPARE strsql FROM @strsql;
		EXECUTE strsql;
		SET uaspalt = @uaspa;
		DEALLOCATE PREPARE strsql;
	END IF;
	IF ISNULL(uaspalt) THEN SET uaspalt=0; END IF;
	-- SELECT uaspalt;

	SET uasp = urlstd + (uaspalt - urlgesamt);
	-- Urlaubsanspruch im Jahr yearalt+1
	SELECT uasp;

END//
DELIMITER ;


-- Exportiere Struktur von Tabelle zeit.Zeittyp
DROP TABLE IF EXISTS `Zeittyp`;
CREATE TABLE IF NOT EXISTS `Zeittyp` (
  `zt_ID` smallint(6) unsigned NOT NULL,
  `zt_bez` varchar(50) DEFAULT NULL,
  `zt_op` varchar(5) DEFAULT NULL,
  `zt_bezk` varchar(3) DEFAULT NULL,
  `zt_owbtag` tinyint(3) unsigned DEFAULT NULL COMMENT 'Brueckentag ueberschreibbar',
  PRIMARY KEY (`zt_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Typ der erfassten Zeit';

-- Daten Export vom Benutzer nicht ausgewählt
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;