aboutsummaryrefslogtreecommitdiffstats
path: root/mayor-orig/update
diff options
context:
space:
mode:
authorM.Gergo2018-07-06 11:35:38 +0200
committerM.Gergo2018-07-06 11:35:38 +0200
commit9f8ebef887d2a1fa3c4ca138e28d6f732071176b (patch)
tree60bac3ca437d60d4f82d6f23e8b10e765b3e803b /mayor-orig/update
parent095e5987eadbe3786be5e12ef385bda9640155b0 (diff)
downloadmayor-9f8ebef887d2a1fa3c4ca138e28d6f732071176b.tar.gz
mayor-9f8ebef887d2a1fa3c4ca138e28d6f732071176b.zip
2018-07-03 állapot
Diffstat (limited to 'mayor-orig/update')
-rw-r--r--mayor-orig/update/pre004297-1-naplo.sql53
-rw-r--r--mayor-orig/update/pre004303-1-naplo.sql53
-rw-r--r--mayor-orig/update/pre004320-1-intezmeny.sql24
-rw-r--r--mayor-orig/update/pre004329-1-naplo.sql15
-rw-r--r--mayor-orig/update/pre004330-1-naplo.sql53
-rw-r--r--mayor-orig/update/processUpdateScripts.sh4
6 files changed, 202 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 ; //
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`