41 lines
1.7 KiB
Transact-SQL
41 lines
1.7 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS dev.uspUpdateTanuloDualisSzerzodesei
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.uspUpdateTanuloDualisSzerzodesei
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE df SET
|
|
df.C_ISALKALMAZOTTALTALTOROLT = 'T'
|
|
,df.MODIFIER = 0
|
|
,df.SERIAL = df.SERIAL+1
|
|
,df.LASTCHANGED = GETDATE()
|
|
FROM T_DUALISSZERZODESFILE df
|
|
INNER JOIN T_FILE f ON f.ID = df.C_FILEID AND f.TOROLT = 'F'
|
|
INNER JOIN T_FOGLALKOZAS ttf ON ttf.C_TANARID = f.C_FELHASZNALOID AND ttf.TOROLT = 'F'
|
|
INNER JOIN T_ALKALMAZOTT alk ON alk.ID = ttf.C_TANARID AND alk.TOROLT ='F' AND ttf.TOROLT = 'F'
|
|
INNER JOIN T_INTEZMENYADATOK intAdat ON intadat.C_INTEZMENYID = df.C_INTEZMENYID AND intAdat.TOROLT = 'F'
|
|
INNER JOIN T_TANEV tanev ON tanev.ID = intAdat.C_TANEVID AND tanev.TOROLT = 'F'
|
|
INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_TANULOID = df.C_TANULOID
|
|
AND tcs.C_OSZTALYCSOPORTID = ttf.C_OSZTALYCSOPORTID
|
|
AND tcs.C_KILEPESDATUM <= GETDATE()
|
|
AND tcs.TOROLT = 'F'
|
|
ANd NOT EXISTS
|
|
(SELECT 1
|
|
FROM T_DUALISSZERZODESFILE df2
|
|
INNER JOIN T_FILE f on f.ID = df2.C_FILEID AND f.TOROLT = 'F'
|
|
INNER JOIN T_FOGLALKOZAS ttf on ttf.C_TANARID = f.C_FELHASZNALOID AND ttf.TOROLT = 'F'
|
|
INNER JOIN T_TANULOCSOPORT tcs on tcs.C_TANULOID = df2.C_TANULOID
|
|
AND tcs.C_OSZTALYCSOPORTID = ttf.C_OSZTALYCSOPORTID
|
|
AND tcs.C_BELEPESDATUM <= GETDATE()
|
|
AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > GETDATE())
|
|
AND tcs.TOROLT = 'F'
|
|
WHERE df2.ID = df.ID)
|
|
WHERE alk.C_ISSZAKOKTATASERTFELELOS = 'T'
|
|
AND intAdat.C_ISSZAKKEPZO = 'T'
|
|
AND tanev.C_AKTIV = 'T'
|
|
AND df.C_ISALKALMAZOTTALTALTOROLT = 'F'
|
|
AND df.TOROLT = 'F'
|
|
END
|
|
GO
|