149 lines
5.8 KiB
Transact-SQL
149 lines
5.8 KiB
Transact-SQL
----------------------------------------------------------------------------------------------------------------
|
|
--Globális Trimm/Excel import javítja ezeket már, DB-ben ragadt hibák egyszeri javítása (LTRIM/RTIM)
|
|
----------------------------------------------------------------------------------------------------------------
|
|
--Lejavítjuk a hibásan mentett tantárgy neveket, illetve a logikai hibákat sorszámozzuk
|
|
WITH tantargy AS (
|
|
SELECT
|
|
t.*
|
|
,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(t.C_NEV,CHAR(160),CHAR(32)),CHAR(9),CHAR(32)),CHAR(32),'(¤¤)'),'¤)(¤',''),'(¤¤)',CHAR(32)))) AS FIXNEV
|
|
,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(t.C_NEVNYOMTATVANYBAN,CHAR(160),CHAR(32)),CHAR(9),CHAR(32)),CHAR(32),'(¤¤)'),'¤)(¤',''),'(¤¤)',CHAR(32)))) AS FIXNEVNYOMTATVANYBAN
|
|
FROM T_TANTARGY t
|
|
INNER JOIN T_TANEV v ON v.ID = t.C_TANEVID AND v.TOROLT = 'F' AND v.C_AKTIV = 'T'
|
|
WHERE t.TOROLT = 'F'
|
|
),
|
|
sorszam AS (
|
|
SELECT
|
|
*
|
|
,ROW_NUMBER() OVER(PARTITION BY C_INTEZMENYID, C_TANEVID, FIXNEV ORDER BY ID) AS SORSZAM
|
|
,COUNT(*) OVER(PARTITION BY C_INTEZMENYID, C_TANEVID, FIXNEV) AS DARAB
|
|
FROM tantargy
|
|
),
|
|
fix AS (
|
|
SELECT
|
|
*
|
|
,CASE WHEN DARAB > 1 THEN CONCAT(FIXNEV, ' [', SORSZAM, ']') ELSE FIXNEV END AS NEV
|
|
FROM sorszam
|
|
)
|
|
|
|
UPDATE t
|
|
SET t.C_NEV = t.NEV
|
|
,t.C_NEVNYOMTATVANYBAN = t.FIXNEVNYOMTATVANYBAN
|
|
,t.MODIFIER = 0
|
|
,t.LASTCHANGED = GETDATE()
|
|
,t.SERIAL += 1
|
|
FROM fix t
|
|
WHERE C_NEV <> NEV OR C_NEV <> FIXNEV
|
|
|
|
--Lejavítjuk a hibásan mentett felhasználó neveket
|
|
UPDATE f
|
|
SET
|
|
f.C_VEZETEKNEV = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(f.C_VEZETEKNEV,CHAR(160),CHAR(32)),CHAR(9),CHAR(32)),CHAR(32),'(¤¤)'),'¤)(¤',''),'(¤¤)',CHAR(32))))
|
|
,f.C_UTONEV = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(f.C_UTONEV,CHAR(160),CHAR(32)),CHAR(9),CHAR(32)),CHAR(32),'(¤¤)'),'¤)(¤',''),'(¤¤)',CHAR(32))))
|
|
,f.C_SZULETESIHELY = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(f.C_SZULETESIHELY,CHAR(160),CHAR(32)),CHAR(9),CHAR(32)),CHAR(32),'(¤¤)'),'¤)(¤',''),'(¤¤)',CHAR(32))))
|
|
,f.MODIFIER = 0
|
|
,f.LASTCHANGED = GETDATE()
|
|
,f.SERIAL += 1
|
|
FROM T_FELHASZNALO_OSSZES f
|
|
INNER JOIN T_TANEV_OSSZES tn ON tn.ID = f.C_TANEVID AND tn.TOROLT = 'F' AND tn.C_AKTIV = 'T'
|
|
WHERE (f.C_VEZETEKNEV LIKE CONCAT('%', CHAR(9),'%')
|
|
OR f.C_VEZETEKNEV LIKE ' %'
|
|
OR f.C_VEZETEKNEV LIKE '% '
|
|
OR f.C_UTONEV LIKE CONCAT('%', CHAR(9),'%')
|
|
OR f.C_UTONEV LIKE ' %'
|
|
OR f.C_UTONEV LIKE '% '
|
|
OR f.C_SZULETESIHELY LIKE CONCAT('%', CHAR(9),'%')
|
|
OR f.C_SZULETESIHELY LIKE ' %'
|
|
OR f.C_SZULETESIHELY LIKE '% ')
|
|
AND f.TOROLT = 'F'
|
|
|
|
--Lejavítjuk a hibásan mentett osztálycsoport neveket
|
|
UPDATE ocs
|
|
SET
|
|
ocs.C_NEV = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ocs.C_NEV,CHAR(160),CHAR(32)),CHAR(9),CHAR(32)),CHAR(32),'(¤¤)'),'¤)(¤',''),'(¤¤)',CHAR(32))))
|
|
,ocs.MODIFIER = 0
|
|
,ocs.LASTCHANGED = GETDATE()
|
|
,ocs.SERIAL += 1
|
|
FROM T_OSZTALYCSOPORT_OSSZES ocs
|
|
INNER JOIN T_TANEV_OSSZES tn ON tn.ID = ocs.C_TANEVID AND tn.TOROLT = 'F' AND tn.C_AKTIV = 'T'
|
|
WHERE (ocs.C_NEV LIKE CONCAT('%', CHAR(9),'%')
|
|
OR ocs.C_NEV LIKE ' %'
|
|
OR ocs.C_NEV LIKE '% ')
|
|
AND ocs.TOROLT = 'F'
|
|
|
|
--Lejavítjuk a hibásan mentett óraterv neveket
|
|
UPDATE ot
|
|
SET
|
|
ot.C_NEV = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ot.C_NEV,CHAR(160),CHAR(32)),CHAR(9),CHAR(32)),CHAR(32),'(¤¤)'),'¤)(¤',''),'(¤¤)',CHAR(32))))
|
|
,ot.MODIFIER = 0
|
|
,ot.LASTCHANGED = GETDATE()
|
|
,ot.SERIAL += 1
|
|
FROM T_ORATERV_OSSZES ot
|
|
INNER JOIN T_TANEV_OSSZES tn ON tn.ID = ot.C_TANEVID AND tn.TOROLT = 'F' AND tn.C_AKTIV = 'T'
|
|
WHERE (ot.C_NEV LIKE CONCAT('%', CHAR(9),'%')
|
|
OR ot.C_NEV LIKE ' %'
|
|
OR ot.C_NEV LIKE '% ')
|
|
AND ot.TOROLT = 'F'
|
|
|
|
--Lejavítjuk a hibásan mentett terem neveket
|
|
UPDATE t
|
|
SET
|
|
t.C_NEV = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(t.C_NEV,CHAR(160),CHAR(32)),CHAR(9),CHAR(32)),CHAR(32),'(¤¤)'),'¤)(¤',''),'(¤¤)',CHAR(32))))
|
|
,t.MODIFIER = 0
|
|
,t.LASTCHANGED = GETDATE()
|
|
,t.SERIAL += 1
|
|
FROM T_TEREM_OSSZES t
|
|
INNER JOIN T_TANEV_OSSZES tn ON tn.ID = t.C_TANEVID AND tn.TOROLT = 'F' AND tn.C_AKTIV = 'T'
|
|
WHERE (t.C_NEV LIKE CONCAT('%', CHAR(9),'%')
|
|
OR t.C_NEV LIKE ' %'
|
|
OR t.C_NEV LIKE '% ')
|
|
AND t.TOROLT = 'F'
|
|
|
|
--Lejavítjuk a hibásan mentett gondviselő neveket
|
|
UPDATE g
|
|
SET
|
|
g.C_NEV = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(g.C_NEV,CHAR(160),CHAR(32)),CHAR(9),CHAR(32)),CHAR(32),'(¤¤)'),'¤)(¤',''),'(¤¤)',CHAR(32))))
|
|
,g.MODIFIER = 0
|
|
,g.LASTCHANGED = GETDATE()
|
|
,g.SERIAL += 1
|
|
FROM T_GONDVISELO_OSSZES g
|
|
INNER JOIN T_TANEV_OSSZES tn ON tn.ID = g.C_TANEVID AND tn.TOROLT = 'F' AND tn.C_AKTIV = 'T'
|
|
WHERE (g.C_NEV LIKE CONCAT('%', CHAR(9),'%')
|
|
OR g.C_NEV LIKE ' %'
|
|
OR g.C_NEV LIKE '% ')
|
|
AND g.TOROLT = 'F'
|
|
|
|
----------------------------------------------------------------------------------------------------------------
|
|
--Hibás/Char(9)-es importnál keletkezett inkonzisztencia javítása
|
|
----------------------------------------------------------------------------------------------------------------
|
|
--Töröljük a hibásan tárolt T_ORARENDIORA_OSSZES relációkat
|
|
UPDATE oo
|
|
SET oo.TOROLT = 'T'
|
|
,oo.MODIFIER = 0
|
|
,oo.LASTCHANGED = GETDATE()
|
|
,oo.SERIAL += 1
|
|
FROM T_ORARENDIORA_OSSZES oo
|
|
WHERE oo.C_TANTARGYID IS NULL
|
|
AND oo.C_FOGLALKOZASID IS NULL
|
|
AND TOROLT = 'F'
|
|
|
|
-- Újra generáljuk az órarendeket
|
|
DECLARE @IntezmenyId int
|
|
DECLARE @TanevId int
|
|
|
|
DECLARE IntezmenyTanevCursor CURSOR FOR
|
|
SELECT C_INTEZMENYID, ID FROM T_TANEV WHERE TOROLT = 'F' AND C_NEV = '2021/2022'
|
|
|
|
OPEN IntezmenyTanevCursor
|
|
FETCH NEXT FROM IntezmenyTanevCursor INTO @IntezmenyId, @TanevId
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
EXEC uspGenerateOrarend @IntezmenyId, @TanevId
|
|
|
|
FETCH NEXT FROM IntezmenyTanevCursor INTO @IntezmenyId, @TanevId
|
|
END
|
|
|
|
CLOSE IntezmenyTanevCursor
|
|
DEALLOCATE IntezmenyTanevCursor
|