451 lines
17 KiB
Transact-SQL
451 lines
17 KiB
Transact-SQL
-- =============================================
|
|
-- Author: Bata-Kovács Gábor
|
|
-- Create date: 2017.06.03.
|
|
-- Description: Az Alkalmazott adatainak frissítése a következõ tanévre
|
|
-- =============================================
|
|
|
|
IF OBJECT_ID('sp_FollowUpAlkalmazott') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_FollowUpAlkalmazott
|
|
END;
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_FollowUpAlkalmazott
|
|
@intezmenyId int,
|
|
@aktTanevId int,
|
|
@kovetkezoTanevId int,
|
|
@alkalmazottId int
|
|
AS
|
|
BEGIN
|
|
|
|
IF (@alkalmazottId <> 0)
|
|
BEGIN
|
|
|
|
DELETE FROM T_CIM WHERE C_FELHASZNALOID = @alkalmazottId AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId
|
|
DELETE FROM T_EMAIL WHERE C_FELHASZNALOID = @alkalmazottId AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId
|
|
DELETE FROM T_TOVABBIMUNKAUGYIADATOK WHERE C_ALKALMAZOTTID = @alkalmazottId AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId
|
|
DELETE FROM T_MUNKAUGYIADATOK WHERE C_ALKALMAZOTTID = @alkalmazottId AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId
|
|
DELETE FROM T_PEDAGOGUSELETPALYAMODELL WHERE C_TANARID = @alkalmazottId AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId
|
|
DELETE FROM T_FELHASZNALOBELEPES WHERE C_FELHASZNALOID = @alkalmazottId AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId
|
|
DELETE FROM T_ALKALMAZOTT WHERE ID = @alkalmazottId AND C_ALTANEVID = @kovetkezoTanevId AND C_ALINTEZMENYID = @intezmenyId
|
|
DELETE FROM T_FELHASZNALO WHERE ID = @alkalmazottId AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId
|
|
|
|
END
|
|
|
|
INSERT INTO T_FELHASZNALO(
|
|
C_ADOSZAM
|
|
,C_ALAIRAS
|
|
,C_ALAPERTELMEZETTLCID
|
|
,C_ALLAMPOLGARSAGA
|
|
,C_ALLAMPOLGARSAGA2
|
|
,C_ANYANYELVE
|
|
,C_ANYJANEVE
|
|
,C_ANYJANEVEELVALASZTO
|
|
,C_ANYJANEVESORREND
|
|
,C_DMSTARTTIME
|
|
,C_ELOTAG
|
|
,C_FENNTARTOAZONOSITO
|
|
,C_IGAZOLVANYSZAM
|
|
,C_IGAZOLVANYTIPUSA
|
|
,C_INTEZMENYID
|
|
,C_KELLADSZINKRONIZALAS
|
|
,C_KERESESINEV
|
|
,C_KIRAZONOSITO
|
|
,C_KRETAKOD
|
|
,C_LATASSERULTWEBHASZNALATA
|
|
,C_LEIRAS
|
|
,C_NEME
|
|
,C_NEVSORREND
|
|
,C_NYILVANTARTASISZAM
|
|
,C_NYILVANTARTASKEZDETE
|
|
,C_NYOMTATASINEV
|
|
,C_OKTATASIAZONOSITO
|
|
,C_OMKOD
|
|
,C_PROFILKEP
|
|
,C_SMSSZAM
|
|
,C_SMSSZINKRONIZALT
|
|
,C_SZULETESIDATUM
|
|
,C_SZULETESIHELY
|
|
,C_SZULETESINEV
|
|
,C_SZULETESINEVELVALASZTO
|
|
,C_SZULETESINEVSORREND
|
|
,C_SZULETESIORSZAG
|
|
,C_TAJSZAM
|
|
,C_TANEVID
|
|
,C_UTONEV
|
|
,C_VEZETEKNEV
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,NNID
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
C_ADOSZAM AS C_ADOSZAM
|
|
,C_ALAIRAS AS C_ALAIRAS
|
|
,C_ALAPERTELMEZETTLCID AS C_ALAPERTELMEZETTLCID
|
|
,IIF((SELECT COUNT(1) FROM T_ALLAMPOLGARSAG WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_ALLAMPOLGARSAGA) = 0,216,t.C_ALLAMPOLGARSAGA) AS C_ALLAMPOLGARSAGA
|
|
,IIF((SELECT COUNT(1) FROM T_ALLAMPOLGARSAG WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_ALLAMPOLGARSAGA2) = 0,216,t.C_ALLAMPOLGARSAGA2) AS C_ALLAMPOLGARSAGA2
|
|
,IIF((SELECT COUNT(1) FROM T_ANYANYELV WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_ANYANYELVE) = 0,493,t.C_ANYANYELVE) AS C_ANYANYELVE
|
|
,C_ANYJANEVE AS C_ANYJANEVE
|
|
,C_ANYJANEVEELVALASZTO AS C_ANYJANEVEELVALASZTO
|
|
,C_ANYJANEVESORREND AS C_ANYJANEVESORREND
|
|
,C_DMSTARTTIME AS C_DMSTARTTIME
|
|
,C_ELOTAG AS C_ELOTAG
|
|
,C_FENNTARTOAZONOSITO AS C_FENNTARTOAZONOSITO
|
|
,C_IGAZOLVANYSZAM AS C_IGAZOLVANYSZAM
|
|
,IIF((SELECT COUNT(1) FROM T_IGAZOLVANYTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_IGAZOLVANYTIPUSA) = 0,543,t.C_IGAZOLVANYTIPUSA) AS C_IGAZOLVANYTIPUSA
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,C_KELLADSZINKRONIZALAS AS C_KELLADSZINKRONIZALAS
|
|
,C_KERESESINEV AS C_KERESESINEV
|
|
,C_KIRAZONOSITO AS C_KIRAZONOSITO
|
|
,C_KRETAKOD AS C_KRETAKOD
|
|
,C_LATASSERULTWEBHASZNALATA AS C_LATASSERULTWEBHASZNALATA
|
|
,C_LEIRAS AS C_LEIRAS
|
|
,C_NEME AS C_NEME
|
|
,C_NEVSORREND AS C_NEVSORREND
|
|
,C_NYILVANTARTASISZAM AS C_NYILVANTARTASISZAM
|
|
,C_NYILVANTARTASKEZDETE AS C_NYILVANTARTASKEZDETE
|
|
,C_NYOMTATASINEV AS C_NYOMTATASINEV
|
|
,C_OKTATASIAZONOSITO AS C_OKTATASIAZONOSITO
|
|
,C_OMKOD AS C_OMKOD
|
|
,C_PROFILKEP AS C_PROFILKEP
|
|
,C_SMSSZAM AS C_SMSSZAM
|
|
,C_SMSSZINKRONIZALT AS C_SMSSZINKRONIZALT
|
|
,C_SZULETESIDATUM AS C_SZULETESIDATUM
|
|
,C_SZULETESIHELY AS C_SZULETESIHELY
|
|
,C_SZULETESINEV AS C_SZULETESINEV
|
|
,C_SZULETESINEVELVALASZTO AS C_SZULETESINEVELVALASZTO
|
|
,C_SZULETESINEVSORREND AS C_SZULETESINEVSORREND
|
|
,C_SZULETESIORSZAG AS C_SZULETESIORSZAG
|
|
,C_TAJSZAM AS C_TAJSZAM
|
|
,@kovetkezoTanevId AS C_TANEVID
|
|
,C_UTONEV AS C_UTONEV
|
|
,C_VEZETEKNEV AS C_VEZETEKNEV
|
|
,TOROLT AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,MODIFIER AS MODIFIER
|
|
,MODIFIER AS CREATOR
|
|
,NULL AS NNID
|
|
,t.ID AS ELOZOTANEVIREKORDID
|
|
FROM T_FELHASZNALO t WHERE C_TANEVID = @aktTanevId AND C_INTEZMENYID = @intezmenyId
|
|
AND 0 = (SELECT COUNT(1) FROM T_FELHASZNALO WHERE C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId AND ELOZOTANEVIREKORDID = t.ID)
|
|
AND 0 <> (SELECT COUNT(1) FROM T_ALKALMAZOTT WHERE T_ALKALMAZOTT.ID = t.ID)
|
|
|
|
INSERT INTO T_ALKALMAZOTT(
|
|
C_ALINTEZMENYID
|
|
,C_ALLANDOHELYETTESITESRE
|
|
,C_ALTANEVID
|
|
,C_FOALLAS
|
|
,C_TANARIAUTOMATAMEGAJANLASENGE
|
|
,C_TANARIERTEKELESHATARERTEK
|
|
,ID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,NNID
|
|
,ELOZOTANEVIREKORDIDA
|
|
) SELECT
|
|
@intezmenyID AS C_ALINTEZMENYID
|
|
,C_ALLANDOHELYETTESITESRE AS C_ALLANDOHELYETTESITESRE
|
|
,@kovetkezoTanevId AS C_ALTANEVID
|
|
,C_FOALLAS AS C_FOALLAS
|
|
,C_TANARIAUTOMATAMEGAJANLASENGE AS C_TANARIAUTOMATAMEGAJANLASENGE
|
|
,C_TANARIERTEKELESHATARERTEK AS C_TANARIERTEKELESHATARERTEK
|
|
,(SELECT ID FROM T_FELHASZNALO WHERE ELOZOTANEVIREKORDID = t.ID AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyID) AS ID
|
|
,TOROLT AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,MODIFIER AS MODIFIER
|
|
,MODIFIER AS CREATOR
|
|
,NULL AS NNID
|
|
,t.ID AS ELOZOTANEVIREKORDIDA
|
|
FROM T_ALKALMAZOTT t WHERE C_ALTANEVID = @aktTanevId AND C_ALINTEZMENYID = @intezmenyId
|
|
AND 0 = (SELECT COUNT(1) FROM T_ALKALMAZOTT WHERE C_ALTANEVID = @kovetkezoTanevId AND C_ALINTEZMENYID = @intezmenyId AND ELOZOTANEVIREKORDIDA = t.ID)
|
|
|
|
INSERT INTO T_FELHASZNALOBELEPES(
|
|
C_BEJELENTKEZESINEV
|
|
,C_FELHASZNALOID
|
|
,C_INTEZMENYID
|
|
,C_JELSZO
|
|
,C_JELSZOVALTOZTATASIDEJE
|
|
,C_KOTELEZOVALTOZTATNI
|
|
,C_MEGHIUSULTBELEPESEK
|
|
,C_NEPTUNNAPLOJELSZO
|
|
,C_SO
|
|
,C_TANEVID
|
|
,C_UTOLSOBELEPES
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,NNID
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
C_BEJELENTKEZESINEV AS C_BEJELENTKEZESINEV
|
|
,(SELECT ID FROM T_FELHASZNALO WHERE ELOZOTANEVIREKORDID = t.C_FELHASZNALOID AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyID) AS C_FELHASZNALOID
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,C_JELSZO AS C_JELSZO
|
|
,C_JELSZOVALTOZTATASIDEJE AS C_JELSZOVALTOZTATASIDEJE
|
|
,C_KOTELEZOVALTOZTATNI AS C_KOTELEZOVALTOZTATNI
|
|
,C_MEGHIUSULTBELEPESEK AS C_MEGHIUSULTBELEPESEK
|
|
,C_NEPTUNNAPLOJELSZO AS C_NEPTUNNAPLOJELSZO
|
|
,C_SO AS C_SO
|
|
,@kovetkezoTanevId AS C_TANEVID
|
|
,C_UTOLSOBELEPES AS C_UTOLSOBELEPES
|
|
,TOROLT AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,MODIFIER AS MODIFIER
|
|
,MODIFIER AS CREATOR
|
|
,NULL AS NNID
|
|
,t.ID AS ELOZOTANEVIREKORDID
|
|
FROM T_FELHASZNALOBELEPES t WHERE C_TANEVID = @aktTanevId AND C_INTEZMENYID = @intezmenyId
|
|
AND 0 = (SELECT COUNT(1) FROM T_FELHASZNALOBELEPES WHERE C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId AND ELOZOTANEVIREKORDID = t.ID)
|
|
AND 0 <> (SELECT COUNT(1) FROM T_ALKALMAZOTT WHERE T_ALKALMAZOTT.ID = t.C_FELHASZNALOID)
|
|
|
|
INSERT INTO T_MUNKAUGYIADATOK(
|
|
C_ALKALMAZASKEZDETE
|
|
,C_ALKALMAZASMEGSZUNESE
|
|
,C_ALKALMAZOTTID
|
|
,C_BESOROLASIFOKOZAT
|
|
,C_BETOLTETLENALLASHELY
|
|
,C_FELADATELLATASIHELYID
|
|
,C_FOGLALKOZTATASTIPUS
|
|
,C_INTEZMENYID
|
|
,C_KOTELEZOORASZAM
|
|
,C_MUNKAIDOKEDVEZMENYOKA
|
|
,C_MUNKAIDOKEDVEZMENYOKASZOVEG
|
|
,C_MUNKAIDOKEDVEZMENYORASZAM
|
|
,C_MUNKAKORTIPUSA
|
|
,C_MUNKAVISZONYTIPUSA
|
|
,C_NYUGDIJAS
|
|
,C_PEDAGOGUSSTATUSZA
|
|
,C_RESZMUNKAIDOSZAZALEK
|
|
,C_TANEVID
|
|
,C_TARTOSHELYETTESITES
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
C_ALKALMAZASKEZDETE AS C_ALKALMAZASKEZDETE
|
|
,C_ALKALMAZASMEGSZUNESE AS C_ALKALMAZASMEGSZUNESE
|
|
,(SELECT ID FROM T_FELHASZNALO WHERE ELOZOTANEVIREKORDID = t.C_ALKALMAZOTTID AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyID) AS C_ALKALMAZOTTID
|
|
,IIF((SELECT COUNT(1) FROM T_BESOROLASIFOKOZATTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_BESOROLASIFOKOZAT) = 0,3040,t.C_BESOROLASIFOKOZAT) AS C_BESOROLASIFOKOZAT
|
|
,C_BETOLTETLENALLASHELY AS C_BETOLTETLENALLASHELY
|
|
,(SELECT ID FROM T_FELADATELLATASIHELY WHERE ELOZOTANEVIREKORDID = t.C_FELADATELLATASIHELYID AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyID) AS C_FELADATELLATASIHELYID
|
|
,IIF((SELECT COUNT(1) FROM T_FOGLALKOZTATASTIPUSA WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_FOGLALKOZTATASTIPUS) = 0,6465,t.C_FOGLALKOZTATASTIPUS) AS C_FOGLALKOZTATASTIPUS
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,C_KOTELEZOORASZAM AS C_KOTELEZOORASZAM
|
|
,IIF((SELECT COUNT(1) FROM T_MUNKAIDOKEDVEZMENYOKA WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_MUNKAIDOKEDVEZMENYOKA) = 0,3073,t.C_MUNKAIDOKEDVEZMENYOKA) AS C_MUNKAIDOKEDVEZMENYOKA
|
|
,C_MUNKAIDOKEDVEZMENYOKASZOVEG AS C_MUNKAIDOKEDVEZMENYOKASZOVEG
|
|
,C_MUNKAIDOKEDVEZMENYORASZAM AS C_MUNKAIDOKEDVEZMENYORASZAM
|
|
,IIF((SELECT COUNT(1) FROM T_MUNKAKORTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_MUNKAKORTIPUSA) = 0,548,t.C_MUNKAKORTIPUSA) AS C_MUNKAKORTIPUSA
|
|
,IIF((SELECT COUNT(1) FROM T_MUNKAVISZONYTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_MUNKAVISZONYTIPUSA) = 0,643,t.C_MUNKAVISZONYTIPUSA) AS C_MUNKAVISZONYTIPUSA
|
|
,C_NYUGDIJAS AS C_NYUGDIJAS
|
|
,IIF((SELECT COUNT(1) FROM T_PEDAGOGUSSTATUSZ WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_PEDAGOGUSSTATUSZA) = 0,3032,t.C_PEDAGOGUSSTATUSZA) AS C_PEDAGOGUSSTATUSZA
|
|
,C_RESZMUNKAIDOSZAZALEK AS C_RESZMUNKAIDOSZAZALEK
|
|
,@kovetkezoTanevId AS C_TANEVID
|
|
,C_TARTOSHELYETTESITES AS C_TARTOSHELYETTESITES
|
|
,TOROLT AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,MODIFIER AS MODIFIER
|
|
,MODIFIER AS CREATOR
|
|
,t.ID AS ELOZOTANEVIREKORDID
|
|
FROM T_MUNKAUGYIADATOK t WHERE C_TANEVID = @aktTanevId AND C_INTEZMENYID = @intezmenyId
|
|
AND 0 = (SELECT COUNT(1) FROM T_MUNKAUGYIADATOK WHERE C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId AND ELOZOTANEVIREKORDID = t.ID)
|
|
|
|
INSERT INTO T_TOVABBIMUNKAUGYIADATOK(
|
|
C_ALKALMAZASKEZDETE
|
|
,C_ALKALMAZASMEGSZUNESE
|
|
,C_ALKALMAZOTTID
|
|
,C_BESOROLASIFOKOZAT
|
|
,C_FELADATELLATASIHELYID
|
|
,C_FOGLALKOZTATASTIPUS
|
|
,C_INTEZMENYID
|
|
,C_KOTELEZOORASZAM
|
|
,C_MUNKAIDOKEDVEZMENYOKA
|
|
,C_MUNKAIDOKEDVEZMENYOKASZOVEG
|
|
,C_MUNKAIDOKEDVEZMENYORASZAM
|
|
,C_MUNKAKORTIPUSA
|
|
,C_MUNKAVISZONYTIPUSA
|
|
,C_NYUGDIJAS
|
|
,C_RESZMUNKAIDOSZAZALEK
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
C_ALKALMAZASKEZDETE AS C_ALKALMAZASKEZDETE
|
|
,C_ALKALMAZASMEGSZUNESE AS C_ALKALMAZASMEGSZUNESE
|
|
,(SELECT ID FROM T_FELHASZNALO WHERE ELOZOTANEVIREKORDID = t.C_ALKALMAZOTTID AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyID) AS C_ALKALMAZOTTID
|
|
,IIF((SELECT COUNT(1) FROM T_BESOROLASIFOKOZATTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_BESOROLASIFOKOZAT) = 0,3040,t.C_BESOROLASIFOKOZAT) AS C_BESOROLASIFOKOZAT
|
|
,(SELECT ID FROM T_FELADATELLATASIHELY WHERE ELOZOTANEVIREKORDID = t.C_FELADATELLATASIHELYID AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyID) AS C_FELADATELLATASIHELYID
|
|
,IIF((SELECT COUNT(1) FROM T_FOGLALKOZTATASTIPUSA WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_FOGLALKOZTATASTIPUS) = 0,6465,t.C_FOGLALKOZTATASTIPUS) AS C_FOGLALKOZTATASTIPUS
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,C_KOTELEZOORASZAM AS C_KOTELEZOORASZAM
|
|
,IIF((SELECT COUNT(1) FROM T_MUNKAIDOKEDVEZMENYOKA WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_MUNKAIDOKEDVEZMENYOKA) = 0,3073,t.C_MUNKAIDOKEDVEZMENYOKA) AS C_MUNKAIDOKEDVEZMENYOKA
|
|
,C_MUNKAIDOKEDVEZMENYOKASZOVEG AS C_MUNKAIDOKEDVEZMENYOKASZOVEG
|
|
,C_MUNKAIDOKEDVEZMENYORASZAM AS C_MUNKAIDOKEDVEZMENYORASZAM
|
|
,IIF((SELECT COUNT(1) FROM T_MUNKAKORTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_MUNKAKORTIPUSA) = 0,548,t.C_MUNKAKORTIPUSA) AS C_MUNKAKORTIPUSA
|
|
,IIF((SELECT COUNT(1) FROM T_MUNKAVISZONYTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_MUNKAVISZONYTIPUSA) = 0,643,t.C_MUNKAVISZONYTIPUSA) AS C_MUNKAVISZONYTIPUSA
|
|
,C_NYUGDIJAS AS C_NYUGDIJAS
|
|
,C_RESZMUNKAIDOSZAZALEK AS C_RESZMUNKAIDOSZAZALEK
|
|
,@kovetkezoTanevId AS C_TANEVID
|
|
,TOROLT AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,MODIFIER AS MODIFIER
|
|
,MODIFIER AS CREATOR
|
|
,t.ID AS ELOZOTANEVIREKORDID
|
|
FROM T_TOVABBIMUNKAUGYIADATOK t WHERE C_TANEVID = @aktTanevId AND C_INTEZMENYID = @intezmenyId
|
|
AND 0 = (SELECT COUNT(1) FROM T_TOVABBIMUNKAUGYIADATOK WHERE C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId AND ELOZOTANEVIREKORDID = t.ID)
|
|
|
|
INSERT INTO T_CIM(
|
|
C_AJTO
|
|
,C_ALAPERTELMEZETT
|
|
,C_CIMTIPUSA
|
|
,C_EMELET
|
|
,C_EPULET
|
|
,C_FELHASZNALOID
|
|
,C_HAZSZAM
|
|
,C_INTEZMENYID
|
|
,C_IRANYITOSZAM
|
|
,C_KOZTERULET
|
|
,C_KOZTERULETJELLEGE
|
|
,C_LEPCSOHAZ
|
|
,C_MEGYE
|
|
,C_ORSZAG
|
|
,C_OSSZETETTCIM
|
|
,C_TANEVID
|
|
,C_VAROS
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,NNID
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
C_AJTO AS C_AJTO
|
|
,C_ALAPERTELMEZETT AS C_ALAPERTELMEZETT
|
|
,IIF((SELECT COUNT(1) FROM T_CIMTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_CIMTIPUSA) = 0,906,t.C_CIMTIPUSA) AS C_CIMTIPUSA
|
|
,C_EMELET AS C_EMELET
|
|
,C_EPULET AS C_EPULET
|
|
,(SELECT ID FROM T_FELHASZNALO WHERE ELOZOTANEVIREKORDID = t.C_FELHASZNALOID AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyID) AS C_FELHASZNALOID
|
|
,C_HAZSZAM AS C_HAZSZAM
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,C_IRANYITOSZAM AS C_IRANYITOSZAM
|
|
,C_KOZTERULET AS C_KOZTERULET
|
|
,IIF((SELECT COUNT(1) FROM T_KOZTERULETJELLEG WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_KOZTERULETJELLEGE) = 0,911,t.C_KOZTERULETJELLEGE) AS C_KOZTERULETJELLEGE
|
|
,C_LEPCSOHAZ AS C_LEPCSOHAZ
|
|
,C_MEGYE AS C_MEGYE
|
|
,C_ORSZAG AS C_ORSZAG
|
|
,C_OSSZETETTCIM AS C_OSSZETETTCIM
|
|
,@kovetkezoTanevId AS C_TANEVID
|
|
,C_VAROS AS C_VAROS
|
|
,TOROLT AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,MODIFIER AS MODIFIER
|
|
,MODIFIER AS CREATOR
|
|
,NULL AS NNID
|
|
,t.ID AS ELOZOTANEVIREKORDID
|
|
FROM T_CIM t WHERE C_TANEVID = @aktTanevId AND C_INTEZMENYID = @intezmenyId
|
|
AND 0 = (SELECT COUNT(1) FROM T_CIM WHERE C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId AND ELOZOTANEVIREKORDID = t.ID)
|
|
AND 0 <> (SELECT COUNT(1) FROM T_ALKALMAZOTT WHERE T_ALKALMAZOTT.ID = t.C_FELHASZNALOID)
|
|
|
|
INSERT INTO T_EMAIL(
|
|
C_EMAILTIPUSA
|
|
,C_EMAILCIM
|
|
,C_ALAPERTELMEZETT
|
|
,C_FELHASZNALOID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,NNID
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
IIF((SELECT COUNT(1) FROM T_EMAILTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_EMAILTIPUSA) = 0,1024,t.C_EMAILTIPUSA) AS C_EMAILTIPUSA
|
|
,C_EMAILCIM AS C_EMAILCIM
|
|
,C_ALAPERTELMEZETT AS C_ALAPERTELMEZETT
|
|
,(SELECT ID FROM T_FELHASZNALO WHERE ELOZOTANEVIREKORDID = t.C_FELHASZNALOID AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyID) AS C_FELHASZNALOID
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,@kovetkezoTanevId AS C_TANEVID
|
|
,TOROLT AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,MODIFIER AS MODIFIER
|
|
,MODIFIER AS CREATOR
|
|
,NULL AS NNID
|
|
,t.ID AS ELOZOTANEVIREKORDID
|
|
FROM T_EMAIL t WHERE C_TANEVID = @aktTanevId AND C_INTEZMENYID = @intezmenyId
|
|
AND 0 = (SELECT COUNT(1) FROM T_EMAIL WHERE C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId AND ELOZOTANEVIREKORDID = t.ID)
|
|
AND 0 <> (SELECT COUNT(1) FROM T_ALKALMAZOTT WHERE T_ALKALMAZOTT.ID = t.C_FELHASZNALOID)
|
|
|
|
INSERT INTO T_PEDAGOGUSELETPALYAMODELL(
|
|
C_FOKOZAT
|
|
,C_MINOSITESDATUM
|
|
,C_PORTFOLIOSTATUSZ
|
|
,C_TANARID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,NNID
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
IIF((SELECT COUNT(1) FROM T_ELETPALYAMODELLFOKOZATTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_FOKOZAT) = 0,6462,t.C_FOKOZAT) AS C_FOKOZAT
|
|
,C_MINOSITESDATUM AS C_MINOSITESDATUM
|
|
,IIF((SELECT COUNT(1) FROM T_ELETPALYAMODELLSTATUSZOK WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_FOKOZAT) = 0,6521,t.C_PORTFOLIOSTATUSZ) AS C_PORTFOLIOSTATUSZ
|
|
,(SELECT ID FROM T_FELHASZNALO WHERE ELOZOTANEVIREKORDID = t.C_TANARID AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyID) AS C_TANARID
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,@kovetkezoTanevId AS C_TANEVID
|
|
,TOROLT AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,MODIFIER AS MODIFIER
|
|
,MODIFIER AS CREATOR
|
|
,NULL AS NNID
|
|
,t.ID AS ELOZOTANEVIREKORDID
|
|
FROM T_PEDAGOGUSELETPALYAMODELL t WHERE C_TANEVID = @aktTanevId AND C_INTEZMENYID = @intezmenyId
|
|
AND 0 = (SELECT COUNT(1) FROM T_PEDAGOGUSELETPALYAMODELL WHERE C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId AND ELOZOTANEVIREKORDID = t.ID)
|
|
|
|
|
|
END
|
|
GO
|