diff options
Diffstat (limited to 'mayor-orig/install/module-naplo')
| -rw-r--r-- | mayor-orig/install/module-naplo/mysql/intezmeny.sql | 7 | ||||
| -rw-r--r-- | mayor-orig/install/module-naplo/mysql/tanev.sql | 50 |
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 ; // |