diff options
author | M.Gergo | 2021-06-11 03:35:05 +0200 |
---|---|---|
committer | M.Gergo | 2021-06-11 03:35:05 +0200 |
commit | 1467d3ca575dabdbc7e0255a671457d0467f422d (patch) | |
tree | da231b68bcc8b2117aa09ff20094da2c579d4c60 /mayor-orig/mayor-naplo/update | |
parent | fd57851114a515c1a35c29e733181afb7a4ee6ca (diff) | |
download | mayor-1467d3ca575dabdbc7e0255a671457d0467f422d.tar.gz mayor-1467d3ca575dabdbc7e0255a671457d0467f422d.zip |
Rev: 4790rev4790
Diffstat (limited to 'mayor-orig/mayor-naplo/update')
-rw-r--r-- | mayor-orig/mayor-naplo/update/pre004789-1-intezmeny.sql | 15 | ||||
-rw-r--r-- | mayor-orig/mayor-naplo/update/pre004789-2-intezmeny.sql | 44 |
2 files changed, 59 insertions, 0 deletions
diff --git a/mayor-orig/mayor-naplo/update/pre004789-1-intezmeny.sql b/mayor-orig/mayor-naplo/update/pre004789-1-intezmeny.sql new file mode 100644 index 00000000..cfb21fda --- /dev/null +++ b/mayor-orig/mayor-naplo/update/pre004789-1-intezmeny.sql @@ -0,0 +1,15 @@ +DELIMITER $$ +DROP PROCEDURE IF EXISTS upgrade_database_4789 $$ + +CREATE PROCEDURE upgrade_database_4789() +BEGIN +SET NAMES utf8 COLLATE utf8_hungarian_ci; + +-- https://tasz.hu/cikkek/igy-vedd-meg-a-gyermeked-az-allamositastol + +ALTER TABLE `diakJogviszony` MODIFY `statusz` enum('felvételt nyert','jogviszonyban van','magántanuló','egyéni munkarend','vendégtanuló','jogviszonya felfüggesztve','jogviszonya lezárva') COLLATE utf8_hungarian_ci NOT NULL; +ALTER TABLE `diak` MODIFY `statusz` enum('felvételt nyert','jogviszonyban van','magántanuló','egyéni munkarend','vendégtanuló','jogviszonya felfüggesztve','jogviszonya lezárva') COLLATE utf8_hungarian_ci NOT NULL; + +END $$ +DELIMITER ; +CALL upgrade_database_4789(); diff --git a/mayor-orig/mayor-naplo/update/pre004789-2-intezmeny.sql b/mayor-orig/mayor-naplo/update/pre004789-2-intezmeny.sql new file mode 100644 index 00000000..4dd6c244 --- /dev/null +++ b/mayor-orig/mayor-naplo/update/pre004789-2-intezmeny.sql @@ -0,0 +1,44 @@ +DELIMITER // + DROP FUNCTION IF EXISTS diakNaploSorszam // + CREATE function diakNaploSorszam ( thisDiakId INT, thisTanev INT, thisOsztalyId INT ) returns INT + READS SQL DATA + BEGIN + DECLARE inKezdesDt,inZarasDt DATE; + DECLARE a,i INT; -- for loop + DECLARE b DATE; -- for loop + DECLARE c DATE; + DECLARE d INT; + DECLARE e VARCHAR(255); + + DECLARE cur1 + CURSOR FOR + SELECT diakId,IF(beDt<inKezdesDt,inKezdesDt,beDt) AS tolDt,IF(ifnull(kiDt,inZarasDt)<inZarasDt,kiDt,inZarasDt) AS igDt, + (SELECT COUNT(*) FROM diakJogviszony AS ds + WHERE ds.diakId=diak.diakId AND tolDt<dt AND dt<=igDt AND ds.statusz IN ('jogviszonyban van','magántanuló','egyéni munkarend') + ) AS aktJogviszonyDb, + (SELECT statusz FROM diakJogviszony AS ds + WHERE ds.diakId=diak.diakId AND dt<=tolDt ORDER BY dt DESC LIMIT 1 + ) AS elozoStatusz + FROM osztalyDiak LEFT JOIN diak USING (diakId) + WHERE osztalyId=thisOsztalyId AND beDt<=inZarasDt AND (kiDt IS NULL OR kiDt>=inKezdesDt) + HAVING (aktJogviszonyDb>0 or elozoStatusz in ('magántanuló','jogviszonyban van','egyéni munkarend')) + ORDER BY tolDt, CONCAT_WS(' ',viseltCsaladinev,viseltUtonev) COLLATE utf8_hungarian_ci; + + DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL; + SELECT kezdesDt FROM szemeszter WHERE tanev=thisTanev AND szemeszter=1 INTO inKezdesDt; + SELECT MAX(zarasDt) FROM szemeszter WHERE tanev=thisTanev INTO inZarasDt; + + SET i := 1; + OPEN cur1; + lo: LOOP +-- FETCH cur1 INTO a,b; + FETCH cur1 INTO a,b,c,d,e; + IF a = thisDiakId THEN + LEAVE lo; + END IF; + SET i := i+1; + END LOOP; + CLOSE cur1; + return i; + END; // + DELIMITER ; // |