2018-07-03 állapot

1 files changed, 53 insertions, 0 deletions

diff --git a/mayor-orig/update/pre004297-1-naplo.sql b/mayor-orig/update/pre004297-1-naplo.sql
new file mode 100644
index 00000000..9ce4b801
--- /dev/null
+++ b/mayor-orig/update/pre004297-1-naplo.sql
@@ -0,0 +1,53 @@
+DROP FUNCTION IF EXISTS getOraTolTime;
+DELIMITER //
+ CREATE FUNCTION getOraTolTime(id int(10) unsigned)
+ RETURNS TIME DETERMINISTIC
+ BEGIN
+    DECLARE oraTolTime TIME;
+
+    SELECT DISTINCT tolTime FROM
+    (SELECT ora.*,osztalyDiak.osztalyId, osztalyDiak.diakId, %INTEZMENYDB%.csengetesiRend.csengetesiRendTipus,
+    tolTime, igTime FROM ora
+    LEFT JOIN %INTEZMENYDB%.tankorDiak ON (ora.tankorId = tankorDiak.tankorId AND tankorDiak.beDt<=ora.dt AND (tankorDiak.kiDt IS NULL or tankorDiak.kiDt>=ora.dt))
+    LEFT JOIN %INTEZMENYDB%.osztalyDiak ON (tankorDiak.diakId = osztalyDiak.diakId AND tankorDiak.beDt<=ora.dt AND (osztalyDiak.kiDt IS NULL or osztalyDiak.kiDt>=ora.dt))
+    LEFT JOIN %INTEZMENYDB%.osztaly ON (osztalyDiak.osztalyId = osztaly.osztalyId)
+    LEFT JOIN %INTEZMENYDB%.telephely USING (telephelyId)
+    LEFT JOIN %INTEZMENYDB%.csengetesiRend ON (telephely.telephelyId = csengetesiRend.telephelyId AND naplo_vmg_2017.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
+ //
+DELIMITER ; //
+
+DROP FUNCTION IF EXISTS getOraIgTime;
+DELIMITER //
+ CREATE FUNCTION getOraIgTime(id int(10) unsigned)
+ RETURNS TIME DETERMINISTIC
+ BEGIN
+    DECLARE oraIgTime TIME;
+
+    SELECT DISTINCT igTime FROM
+    (SELECT ora.*,osztalyDiak.osztalyId, osztalyDiak.diakId, %INTEZMENYDB%.csengetesiRend.csengetesiRendTipus,
+    tolTime, igTime FROM ora
+    LEFT JOIN %INTEZMENYDB%.tankorDiak ON (ora.tankorId = tankorDiak.tankorId AND tankorDiak.beDt<=ora.dt AND (tankorDiak.kiDt IS NULL or tankorDiak.kiDt>=ora.dt))
+    LEFT JOIN %INTEZMENYDB%.osztalyDiak ON (tankorDiak.diakId = osztalyDiak.diakId AND tankorDiak.beDt<=ora.dt AND (osztalyDiak.kiDt IS NULL or osztalyDiak.kiDt>=ora.dt))
+    LEFT JOIN %INTEZMENYDB%.osztaly ON (osztalyDiak.osztalyId = osztaly.osztalyId)
+    LEFT JOIN %INTEZMENYDB%.telephely USING (telephelyId)
+    LEFT JOIN %INTEZMENYDB%.csengetesiRend ON (telephely.telephelyId = csengetesiRend.telephelyId AND naplo_vmg_2017.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 ; //