blob: 9ce4b801c2423fb38f019dfbb41b64cb5b6ac995 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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 ; //
|
