2018-07-03 állapot

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 ; //