From 9f8ebef887d2a1fa3c4ca138e28d6f732071176b Mon Sep 17 00:00:00 2001
From: M.Gergo
Date: Fri, 6 Jul 2018 11:35:38 +0200
Subject: 2018-07-03 állapot

---
 mayor-orig/update/pre004297-1-naplo.sql     | 53 +++++++++++++++++++++++++++++
 mayor-orig/update/pre004303-1-naplo.sql     | 53 +++++++++++++++++++++++++++++
 mayor-orig/update/pre004320-1-intezmeny.sql | 24 +++++++++++++
 mayor-orig/update/pre004329-1-naplo.sql     | 15 ++++++++
 mayor-orig/update/pre004330-1-naplo.sql     | 53 +++++++++++++++++++++++++++++
 mayor-orig/update/processUpdateScripts.sh   |  4 +++
 6 files changed, 202 insertions(+)
 create mode 100644 mayor-orig/update/pre004297-1-naplo.sql
 create mode 100644 mayor-orig/update/pre004303-1-naplo.sql
 create mode 100644 mayor-orig/update/pre004320-1-intezmeny.sql
 create mode 100644 mayor-orig/update/pre004329-1-naplo.sql
 create mode 100644 mayor-orig/update/pre004330-1-naplo.sql

(limited to 'mayor-orig/update')

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 ; //
diff --git a/mayor-orig/update/pre004303-1-naplo.sql b/mayor-orig/update/pre004303-1-naplo.sql
new file mode 100644
index 00000000..0f35fc37
--- /dev/null
+++ b/mayor-orig/update/pre004303-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 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 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 ; //
diff --git a/mayor-orig/update/pre004320-1-intezmeny.sql b/mayor-orig/update/pre004320-1-intezmeny.sql
new file mode 100644
index 00000000..3bae3cfd
--- /dev/null
+++ b/mayor-orig/update/pre004320-1-intezmeny.sql
@@ -0,0 +1,24 @@
+DELIMITER $$
+DROP PROCEDURE IF EXISTS upgrade_database_4320 $$
+
+CREATE PROCEDURE upgrade_database_4320()
+BEGIN
+SET NAMES utf8 COLLATE utf8_hungarian_ci;
+
+IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE TABLE_SCHEMA=DATABASE() and TABLE_NAME='diak' AND INDEX_NAME='diak_ibfk_6') THEN
+    ALTER TABLE diak ADD CONSTRAINT `diak_ibfk_6` FOREIGN KEY (`neveloId`) REFERENCES `szulo` (`szuloId`) ON DELETE SET NULL ON UPDATE SET NULL;
+END IF;
+
+IF NOT EXISTS (
+    SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() and TABLE_NAME='diak' AND COLUMN_NAME='beiratoId'
+) THEN
+    ALTER TABLE `diak` ADD `beiratoId` int(10) unsigned DEFAULT NULL AFTER neveloId;
+END IF;
+
+IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE TABLE_SCHEMA=DATABASE() and TABLE_NAME='diak' AND INDEX_NAME='diak_ibfk_7') THEN
+    ALTER TABLE diak ADD CONSTRAINT `diak_ibfk_7` FOREIGN KEY (`beiratoId`) REFERENCES `szulo` (`szuloId`) ON DELETE SET NULL ON UPDATE SET NULL;
+END IF;
+
+END $$
+DELIMITER ;
+CALL upgrade_database_4320();
diff --git a/mayor-orig/update/pre004329-1-naplo.sql b/mayor-orig/update/pre004329-1-naplo.sql
new file mode 100644
index 00000000..c7144b3d
--- /dev/null
+++ b/mayor-orig/update/pre004329-1-naplo.sql
@@ -0,0 +1,15 @@
+DELIMITER $$
+DROP PROCEDURE IF EXISTS upgrade_database_4329 $$
+
+CREATE PROCEDURE upgrade_database_4329()
+BEGIN
+SET NAMES utf8 COLLATE utf8_hungarian_ci;
+
+ALTER TABLE `hianyzas` MODIFY `igazolas` enum('orvosi','szülői','osztályfőnöki','verseny','tanulmányi verseny','vizsga','nyelvvizsga','igazgatói','hatósági','pályaválasztás','') COLLATE utf8_hungarian_ci DEFAULT NULL;
+UPDATE hianyzas SET igazolas='verseny' WHERE igazolas='tanulmányi verseny';
+UPDATE hianyzas SET igazolas='vizsga' WHERE igazolas='nyelvvizsga';
+ALTER TABLE hianyzas MODIFY `igazolas` enum('orvosi','szülői','osztályfőnöki','verseny','vizsga','igazgatói','hatósági','pályaválasztás','') COLLATE utf8_hungarian_ci DEFAULT NULL;
+
+END $$
+DELIMITER ;
+CALL upgrade_database_4329();
diff --git a/mayor-orig/update/pre004330-1-naplo.sql b/mayor-orig/update/pre004330-1-naplo.sql
new file mode 100644
index 00000000..0f35fc37
--- /dev/null
+++ b/mayor-orig/update/pre004330-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 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 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 ; //
diff --git a/mayor-orig/update/processUpdateScripts.sh b/mayor-orig/update/processUpdateScripts.sh
index 47fbb23e..a63578e6 100644
--- a/mayor-orig/update/processUpdateScripts.sh
+++ b/mayor-orig/update/processUpdateScripts.sh
@@ -12,6 +12,10 @@ host=$MYSQL_HOST
 user=$MYSQL_USER
 password=$MYSQL_PW
 " > $BASEDIR/config/my.cnf
+
+PRECHARSET="SET NAMES 'utf8' COLLATE 'utf8_hungarian_ci'; "
+#PRECHARSET="$PRECHARSET SET collation_connection = utf8_hungarian_ci; "
+
 MYSQL_CONFIG="--defaults-extra-file=$BASEDIR/config/my.cnf"
 MYSQL_PARAMETERS=""
 TEST=`$MYSQL -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PW -e exit 2>&1 >/dev/null`
-- 
cgit v1.2.3