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
|
DELIMITER $$
DROP PROCEDURE IF EXISTS upgrade_database_3517 $$
CREATE PROCEDURE upgrade_database_3517()
BEGIN
IF NOT EXISTS (
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='diakTorzslapszam'
) THEN
CREATE TABLE `diakTorzslapszam` (
`osztalyId` int(10) unsigned NOT NULL,
`diakId` int(10) unsigned NOT NULL,
`torzslapszam` tinyint unsigned NOT NULL,
PRIMARY KEY (`osztalyId`,`diakId`),
KEY `diakTorzslapszam_ibfk_2` (`diakId`),
CONSTRAINT `diakTorzslapszam_ibfk_1` FOREIGN KEY (`osztalyId`) REFERENCES `osztaly` (`osztalyId`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `diakTorzslapszam_ibfk_2` FOREIGN KEY (`diakId`) REFERENCES `diak` (`diakId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;
-- A névsor miatt az üres mező NULL kell legyen!
update diak set viseltNevElotag = NULL where viseltNevElotag = '';
-- A törzslapszámok feltöltése
set @oszt=0;
set @sz=0;
insert ignore into diakTorzslapszam
select osztalyId, diakId, sorsz from (
select
@sz:=if(@oszt=osztalyId,@sz:=@sz+1,1) as sorsz,
@oszt:=osztalyId as o,
osztalyId, diakId, sort, diakNev
from (
select
osztalyId, diakId,
if (month(min(beDt))>8 or month(min(beDt))<6 or (month(min(beDt))=6 and day(min(beDt))<16), min(beDt), date_format(min(beDt),'%Y-09-01')) as sort,
concat_ws(' ',viseltNevElotag, viseltCsaladinev, viseltUtonev) as diakNev
from osztalyDiak left join diak using (diakId) WHERE diak.diakId IS NOT NULL
group by osztalyId, diakId
order by osztalyId, sort, diakNev
) as t
) as k;
-- where diakId=... and osztalyId=...;
END IF;
END $$
DELIMITER ; $$
CALL upgrade_database_3517();
|
