267 lines
6.5 KiB
Transact-SQL
267 lines
6.5 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspFollowUpGondviselo
|
|
GO
|
|
|
|
CREATE PROCEDURE uspFollowUpGondviselo
|
|
@intezmenyId int,
|
|
@tanevId int,
|
|
@kovTanevId int,
|
|
@gondviseloId int
|
|
AS
|
|
BEGIN
|
|
DECLARE @kovGondviseloID int = (SELECT ID FROM T_GONDVISELO_OSSZES WHERE ELOZOTANEVIREKORDID=@gondviseloId)
|
|
|
|
IF (SELECT TOROLT FROM T_GONDVISELO_OSSZES WHERE ID = @gondviseloId)='T' OR (SELECT TOROLT FROM T_GONDVISELO_OSSZES WHERE ID = @kovGondviseloID)='T' BEGIN
|
|
RETURN
|
|
END
|
|
|
|
IF @kovGondviseloID IS NULL BEGIN
|
|
INSERT INTO T_GONDVISELO_OSSZES(
|
|
C_AKTIV
|
|
,C_GONDVISELESKEZDETE
|
|
,C_GONDVISELESVEGE
|
|
,C_GYERMEKETEGYEDULNEVELO
|
|
,C_NEV
|
|
,C_NYUGDIJAS
|
|
,C_ROKONSAGFOKA
|
|
,C_ISEMAILERTESITES
|
|
,C_ISTORVENYESKEPVISELO
|
|
,C_ISCSOKKENTETTGONDVISELO
|
|
,C_TANULOID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
)
|
|
SELECT
|
|
g.C_AKTIV
|
|
,g.C_GONDVISELESKEZDETE
|
|
,g.C_GONDVISELESVEGE
|
|
,g.C_GYERMEKETEGYEDULNEVELO
|
|
,g.C_NEV
|
|
,g.C_NYUGDIJAS
|
|
,g.C_ROKONSAGFOKA
|
|
,g.C_ISEMAILERTESITES
|
|
,g.C_ISTORVENYESKEPVISELO
|
|
,g.C_ISCSOKKENTETTGONDVISELO
|
|
,fhNew.ID
|
|
,g.C_INTEZMENYID
|
|
,fhNew.C_TANEVID
|
|
,0
|
|
,GETDATE()
|
|
,GETDATE()
|
|
,NULL
|
|
,NULL
|
|
,g.ID
|
|
FROM T_GONDVISELO_OSSZES g
|
|
INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = g.C_TANULOID
|
|
INNER JOIN T_FELHASZNALO_OSSZES fhNew ON fhNew.C_OKTATASIAZONOSITO = fh.C_OKTATASIAZONOSITO AND fhNew.TOROLT='F' AND fhNew.C_TANEVID = @kovTanevId
|
|
WHERE g.ID = @gondviseloId
|
|
|
|
INSERT INTO T_FELHASZNALOBELEPES_OSSZES (
|
|
C_KOTELEZOVALTOZTATNI
|
|
,C_MEGHIUSULTBELEPESEK
|
|
,C_UTOLSOBELEPES
|
|
,C_JELSZO
|
|
,C_JELSZOVALTOZTATASIDEJE
|
|
,C_SO
|
|
,C_BEJELENTKEZESINEV
|
|
,C_NEPTUNNAPLOJELSZO
|
|
,C_GUID
|
|
,C_ELFOGADOTTGDPRNYILATKOZAT
|
|
,C_GDPRELFOGADASDATUMA
|
|
,C_IMPORTALT
|
|
,C_FELHASZNALOID
|
|
,C_GONDVISELOID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID)
|
|
SELECT
|
|
C_KOTELEZOVALTOZTATNI
|
|
,C_MEGHIUSULTBELEPESEK
|
|
,C_UTOLSOBELEPES
|
|
,C_JELSZO
|
|
,C_JELSZOVALTOZTATASIDEJE
|
|
,C_SO
|
|
,C_BEJELENTKEZESINEV
|
|
,C_NEPTUNNAPLOJELSZO
|
|
,C_GUID
|
|
,C_ELFOGADOTTGDPRNYILATKOZAT
|
|
,C_GDPRELFOGADASDATUMA
|
|
,'F'
|
|
,gKov.C_TANULOID
|
|
,gKov.ID
|
|
,gKov.C_INTEZMENYID
|
|
,@kovTanevId
|
|
,'F'
|
|
,0
|
|
,GETDATE()
|
|
,GETDATE()
|
|
,NULL
|
|
,NULL
|
|
,fhb.ID
|
|
FROM T_FELHASZNALOBELEPES_OSSZES fhb
|
|
INNER JOIN T_GONDVISELO_OSSZES gKov ON gKov.ELOZOTANEVIREKORDID=fhb.C_GONDVISELOID
|
|
WHERE fhb.C_GONDVISELOID=@gondviseloId AND fhb.TOROLT='F'
|
|
|
|
END
|
|
ELSE BEGIN
|
|
UPDATE gKov SET
|
|
C_AKTIV = gOld.C_AKTIV
|
|
,C_GONDVISELESKEZDETE = gOld.C_GONDVISELESKEZDETE
|
|
,C_GONDVISELESVEGE = gOld.C_GONDVISELESVEGE
|
|
,C_GYERMEKETEGYEDULNEVELO = gOld.C_GYERMEKETEGYEDULNEVELO
|
|
,C_NEV = gOld.C_NEV
|
|
,C_NYUGDIJAS = gOld.C_NYUGDIJAS
|
|
,C_ROKONSAGFOKA = gOld.C_ROKONSAGFOKA
|
|
,C_ISEMAILERTESITES = gOld.C_ISEMAILERTESITES
|
|
,C_ISTORVENYESKEPVISELO = gOld.C_ISTORVENYESKEPVISELO
|
|
,C_ISCSOKKENTETTGONDVISELO = gOld.C_ISCSOKKENTETTGONDVISELO
|
|
,SERIAL += 1
|
|
,LASTCHANGED = GETDATE()
|
|
,MODIFIER = gOld.MODIFIER
|
|
FROM T_GONDVISELO_OSSZES gOld
|
|
INNER JOIN T_GONDVISELO_OSSZES gKov ON gKov.ELOZOTANEVIREKORDID=gOld.ID AND gOld.ID=@gondviseloId
|
|
|
|
UPDATE kov SET
|
|
C_JELSZO = akt.C_JELSZO
|
|
,C_JELSZOVALTOZTATASIDEJE = GETDATE()
|
|
,C_BEJELENTKEZESINEV = akt.C_BEJELENTKEZESINEV
|
|
,SERIAL += 1
|
|
,LASTCHANGED = GETDATE()
|
|
,MODIFIER = akt.MODIFIER
|
|
FROM T_FELHASZNALOBELEPES_OSSZES akt
|
|
INNER JOIN T_FELHASZNALOBELEPES_OSSZES kov ON kov.ELOZOTANEVIREKORDID=akt.ID AND kov.TOROLT='F'
|
|
WHERE akt.TOROLT='F' AND akt.C_GONDVISELOID=@gondviseloId
|
|
END
|
|
|
|
DECLARE @cimId int
|
|
DECLARE cimCursor CURSOR FOR
|
|
SELECT ID FROM T_CIM_OSSZES WHERE C_GONDVISELOID=@gondviseloId AND TOROLT='F'
|
|
OPEN cimCursor
|
|
FETCH NEXT FROM cimCursor INTO @cimId
|
|
WHILE @@FETCH_STATUS=0 BEGIN
|
|
EXEC uspFollowUpGondviseloCim
|
|
@intezmenyId,
|
|
@tanevId,
|
|
@kovTanevId,
|
|
@cimId
|
|
FETCH NEXT FROM cimCursor INTO @cimId
|
|
END
|
|
CLOSE cimCursor
|
|
DEALLOCATE cimCursor
|
|
|
|
INSERT INTO T_TELEFON_OSSZES (
|
|
C_TELEFONTIPUSA
|
|
,C_TELEFONSZAM
|
|
,C_LEIRAS
|
|
,C_ALAPERTELMEZETT
|
|
,C_ISPUBLIC
|
|
,C_GONDVISELOID
|
|
,C_FELHASZNALOID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID)
|
|
SELECT
|
|
C_TELEFONTIPUSA
|
|
,C_TELEFONSZAM
|
|
,C_LEIRAS
|
|
,C_ALAPERTELMEZETT
|
|
,C_ISPUBLIC
|
|
,gKov.ID
|
|
,gKov.C_TANULOID
|
|
,gKov.C_INTEZMENYID
|
|
,@kovTanevId
|
|
,'F'
|
|
,0
|
|
,GETDATE()
|
|
,GETDATE()
|
|
,NULL
|
|
,NULL
|
|
,t.ID
|
|
FROM T_GONDVISELO_OSSZES g
|
|
INNER JOIN T_GONDVISELO_OSSZES gKov ON gKov.ELOZOTANEVIREKORDID=g.ID
|
|
INNER JOIN T_TELEFON_OSSZES t ON t.C_GONDVISELOID=g.ID AND t.TOROLT='F'
|
|
WHERE g.ID=@gondviseloId
|
|
AND NOT EXISTS(SELECT 1 FROM T_TELEFON_OSSZES WHERE ELOZOTANEVIREKORDID=t.ID AND TOROLT='F')
|
|
|
|
INSERT INTO T_EMAIL_OSSZES (
|
|
C_EMAILTIPUSA
|
|
,C_EMAILCIM
|
|
,C_ALAPERTELMEZETT
|
|
,C_ISPUBLIC
|
|
,C_GONDVISELOID
|
|
,C_FELHASZNALOID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID)
|
|
SELECT
|
|
C_EMAILTIPUSA
|
|
,C_EMAILCIM
|
|
,C_ALAPERTELMEZETT
|
|
,C_ISPUBLIC
|
|
,gKov.ID
|
|
,gKov.C_TANULOID
|
|
,gKov.C_INTEZMENYID
|
|
,@kovTanevId
|
|
,'F'
|
|
,0
|
|
,GETDATE()
|
|
,GETDATE()
|
|
,NULL
|
|
,NULL
|
|
,e.ID
|
|
FROM T_GONDVISELO_OSSZES g
|
|
INNER JOIN T_GONDVISELO_OSSZES gKov ON gKov.ELOZOTANEVIREKORDID=g.ID
|
|
INNER JOIN T_EMAIL_OSSZES e ON e.C_GONDVISELOID=g.ID AND e.TOROLT='F'
|
|
WHERE g.ID=@gondviseloId
|
|
AND NOT EXISTS(SELECT 1 FROM T_EMAIL_OSSZES WHERE ELOZOTANEVIREKORDID=e.ID AND TOROLT='F')
|
|
|
|
UPDATE kov SET
|
|
C_TELEFONTIPUSA = akt.C_TELEFONTIPUSA
|
|
,C_TELEFONSZAM = akt.C_TELEFONSZAM
|
|
,C_LEIRAS = akt.C_LEIRAS
|
|
,C_ALAPERTELMEZETT = akt.C_ALAPERTELMEZETT
|
|
,C_ISPUBLIC = akt.C_ISPUBLIC
|
|
,SERIAL += 1
|
|
,LASTCHANGED = GETDATE()
|
|
,MODIFIER = akt.MODIFIER
|
|
FROM T_TELEFON_OSSZES akt
|
|
INNER JOIN T_TELEFON_OSSZES kov ON kov.ELOZOTANEVIREKORDID=akt.ID AND kov.TOROLT='F'
|
|
WHERE akt.TOROLT='F' AND akt.C_GONDVISELOID=@gondviseloId
|
|
|
|
UPDATE kov SET
|
|
C_EMAILTIPUSA = akt.C_EMAILTIPUSA
|
|
,C_EMAILCIM = akt.C_EMAILCIM
|
|
,C_ALAPERTELMEZETT = akt.C_ALAPERTELMEZETT
|
|
,C_ISPUBLIC = akt.C_ISPUBLIC
|
|
,SERIAL += 1
|
|
,LASTCHANGED = GETDATE()
|
|
,MODIFIER = akt.MODIFIER
|
|
FROM T_EMAIL_OSSZES akt
|
|
INNER JOIN T_EMAIL_OSSZES kov ON kov.ELOZOTANEVIREKORDID=akt.ID AND kov.TOROLT='F'
|
|
WHERE akt.TOROLT='F' AND akt.C_GONDVISELOID=@gondviseloId
|
|
|
|
END
|
|
GO |