aboutsummaryrefslogtreecommitdiffstats
path: root/mayor-orig/install/module-naplo
diff options
context:
space:
mode:
Diffstat (limited to 'mayor-orig/install/module-naplo')
-rw-r--r--mayor-orig/install/module-naplo/mysql/intezmeny.sql7
-rw-r--r--mayor-orig/install/module-naplo/mysql/tanev.sql50
2 files changed, 55 insertions, 2 deletions
diff --git a/mayor-orig/install/module-naplo/mysql/intezmeny.sql b/mayor-orig/install/module-naplo/mysql/intezmeny.sql
index 05af637d..aae18fdf 100644
--- a/mayor-orig/install/module-naplo/mysql/intezmeny.sql
+++ b/mayor-orig/install/module-naplo/mysql/intezmeny.sql
@@ -115,6 +115,7 @@ CREATE TABLE `diak` (
`gondviseloId` int(10) unsigned DEFAULT NULL,
`neveloId` int(10) unsigned DEFAULT NULL,
`anyaId` int(10) unsigned DEFAULT NULL,
+ `beiratoId` int(10) unsigned DEFAULT NULL,
`allampolgarsag` varchar(16) COLLATE utf8_hungarian_ci DEFAULT 'magyar',
`lakhelyOrszag` varchar(16) COLLATE utf8_hungarian_ci DEFAULT 'Magyarország',
`lakhelyHelyseg` varchar(32) COLLATE utf8_hungarian_ci DEFAULT NULL,
@@ -161,9 +162,11 @@ CREATE TABLE `diak` (
KEY `gondviseloId` (`gondviseloId`),
KEY `apaId` (`apaId`),
CONSTRAINT `diak_ibfk_2` FOREIGN KEY (`kezdoTanev`, `kezdoSzemeszter`) REFERENCES `szemeszter` (`tanev`, `szemeszter`) ON DELETE NO ACTION ON UPDATE NO ACTION,
- CONSTRAINT `diak_ibfk_3` FOREIGN KEY (`anyaId`) REFERENCES `szulo` (`szuloId`) ON DELETE SET NULL ON UPDATE SET NULL,
+ CONSTRAINT `diak_ibfk_3` FOREIGN KEY (`anyaId`) REFERENCES `szulo` (`szuloId`) ON DELETE SET NULL ON UPDATE SET NULL,
CONSTRAINT `diak_ibfk_4` FOREIGN KEY (`gondviseloId`) REFERENCES `szulo` (`szuloId`) ON DELETE SET NULL ON UPDATE SET NULL,
- CONSTRAINT `diak_ibfk_5` FOREIGN KEY (`apaId`) REFERENCES `szulo` (`szuloId`) ON DELETE SET NULL ON UPDATE SET NULL
+ CONSTRAINT `diak_ibfk_5` FOREIGN KEY (`apaId`) REFERENCES `szulo` (`szuloId`) ON DELETE SET NULL ON UPDATE SET NULL,
+ CONSTRAINT `diak_ibfk_6` FOREIGN KEY (`neveloId`) REFERENCES `szulo` (`szuloId`) ON DELETE SET NULL ON UPDATE SET NULL,
+ CONSTRAINT `diak_ibfk_7` FOREIGN KEY (`beiratoId`) REFERENCES `szulo` (`szuloId`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;
CREATE TABLE `diakJogviszony` (
diff --git a/mayor-orig/install/module-naplo/mysql/tanev.sql b/mayor-orig/install/module-naplo/mysql/tanev.sql
index c699d2b7..b3975c7b 100644
--- a/mayor-orig/install/module-naplo/mysql/tanev.sql
+++ b/mayor-orig/install/module-naplo/mysql/tanev.sql
@@ -703,6 +703,8 @@ CREATE TABLE `jegyzetTankor` (
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;
DROP FUNCTION IF EXISTS getNev;
+DROP FUNCTION IF EXISTS getOraIgTime;
+DROP FUNCTION IF EXISTS getOraTolTime;
DELIMITER //
CREATE FUNCTION getNev(id int(10) unsigned, tipus varchar(20))
@@ -727,4 +729,52 @@ DELIMITER //
RETURN (nev);
END
//
+
+ CREATE FUNCTION getOraTolTime(id int(10) unsigned)
+ RETURNS TIME DETERMINISTIC
+ BEGIN
+ DECLARE oraTolTime TIME;
+
+ SELECT DISTINCT tolTime FROM
+ (SELECT ora.*,osztalyDiak.osztalyId, osztalyDiak.diakId, %DB%.csengetesiRend.csengetesiRendTipus,
+ tolTime, igTime FROM ora
+ LEFT JOIN %DB%.tankorDiak ON (ora.tankorId = tankorDiak.tankorId AND tankorDiak.beDt<=ora.dt AND (tankorDiak.kiDt IS NULL or tankorDiak.kiDt>=ora.dt))
+ LEFT JOIN %DB%.osztalyDiak ON (tankorDiak.diakId = osztalyDiak.diakId AND tankorDiak.beDt<=ora.dt AND (osztalyDiak.kiDt IS NULL or osztalyDiak.kiDt>=ora.dt))
+ LEFT JOIN %DB%.osztaly ON (osztalyDiak.osztalyId = osztaly.osztalyId)
+ LEFT JOIN %DB%.telephely USING (telephelyId)
+ LEFT JOIN %DB%.csengetesiRend ON (telephely.telephelyId = csengetesiRend.telephelyId AND ora.ora=csengetesiRend.ora)
+ WHERE oraId = id) AS a
+ LEFT JOIN munkatervOsztaly USING (osztalyId)
+ LEFT JOIN nap ON (nap.dt=a.dt AND nap.munkatervId=munkatervOsztaly.munkatervId)
+ WHERE nap.csengetesiRendTipus = a.csengetesiRendTipus
+ LIMIT 1
+ INTO oraTolTime;
+
+ RETURN (oraTolTime);
+ END
+ //
+
+ CREATE FUNCTION getOraIgTime(id int(10) unsigned)
+ RETURNS TIME DETERMINISTIC
+ BEGIN
+ DECLARE oraIgTime TIME;
+
+ SELECT DISTINCT igTime FROM
+ (SELECT ora.*,osztalyDiak.osztalyId, osztalyDiak.diakId, %DB%.csengetesiRend.csengetesiRendTipus,
+ tolTime, igTime FROM ora
+ LEFT JOIN %DB%.tankorDiak ON (ora.tankorId = tankorDiak.tankorId AND tankorDiak.beDt<=ora.dt AND (tankorDiak.kiDt IS NULL or tankorDiak.kiDt>=ora.dt))
+ LEFT JOIN %DB%.osztalyDiak ON (tankorDiak.diakId = osztalyDiak.diakId AND tankorDiak.beDt<=ora.dt AND (osztalyDiak.kiDt IS NULL or osztalyDiak.kiDt>=ora.dt))
+ LEFT JOIN %DB%.osztaly ON (osztalyDiak.osztalyId = osztaly.osztalyId)
+ LEFT JOIN %DB%.telephely USING (telephelyId)
+ LEFT JOIN %DB%.csengetesiRend ON (telephely.telephelyId = csengetesiRend.telephelyId AND ora.ora=csengetesiRend.ora)
+ WHERE oraId = id) AS a
+ LEFT JOIN munkatervOsztaly USING (osztalyId)
+ LEFT JOIN nap ON (nap.dt=a.dt AND nap.munkatervId=munkatervOsztaly.munkatervId)
+ WHERE nap.csengetesiRendTipus = a.csengetesiRendTipus
+ LIMIT 1
+ INTO oraIgTime;
+
+ RETURN (oraIgTime);
+ END
+ //
DELIMITER ; //