258 lines
12 KiB
Transact-SQL
258 lines
12 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspInsertEgyediNap
|
|
GO
|
|
|
|
CREATE PROCEDURE uspInsertEgyediNap
|
|
@pIntezmenyId INT
|
|
,@pTanevId INT
|
|
,@pTanevRendjeId INT
|
|
AS BEGIN
|
|
SET NOCOUNT ON
|
|
|
|
DECLARE
|
|
@elteroNapDatuma DATETIME
|
|
,@ujDatum DATETIME
|
|
,@csengetesiRendId INT
|
|
,@isOsszesCsoportraVonatkozik CHAR(1)
|
|
,@creator INT
|
|
,@hetnapja INT
|
|
,@hetirend INT
|
|
|
|
SELECT
|
|
@elteroNapDatuma = C_ELTERONAPDATUMA
|
|
,@ujDatum = C_DATUM
|
|
,@csengetesiRendId = C_CSENGETESIRENDID
|
|
,@isOsszesCsoportraVonatkozik = C_OSSZESCSOPORTRAVONATKOZIK
|
|
,@creator = CREATOR
|
|
,@hetnapja = C_HETNAPJA
|
|
,@hetirend = C_HETIREND
|
|
FROM T_TANEVRENDJE_OSSZES
|
|
WHERE ID = @pTanevRendjeId
|
|
|
|
IF @isOsszesCsoportraVonatkozik = 'T' BEGIN
|
|
INSERT INTO T_ORARENDIORA (
|
|
C_DIFFERENCIALT
|
|
,C_DRAMA
|
|
,C_HETIREND
|
|
,C_HETNAPJA
|
|
,C_IKTTANORA
|
|
,C_IPRTANORA
|
|
,C_KOOPERATIV
|
|
,C_MERES
|
|
,C_MINDENNAPOSTESTNEVELES
|
|
,C_NEMSZAKRENDSZERUORA
|
|
,C_NEMZETISEGIORA
|
|
,C_ORASZAM
|
|
,C_SORSZAMOZANDO
|
|
,C_TAMOPORA
|
|
,C_TULORA
|
|
,C_BONTOTT
|
|
,C_ORAERVENYESSEGKEZDETE
|
|
,C_ORAERVENYESSEGVEGE
|
|
,C_PARHUZAMOSORA
|
|
,C_TIOP12
|
|
,C_ORAKEZDETE
|
|
,C_ORAVEGE
|
|
,C_TANORANKIVULIFOGLALKOZAS
|
|
,C_IMPORTALT
|
|
,C_CSENGETESIRENDID
|
|
,C_CSENGETESIRENDORAID
|
|
,C_ORARENDIORAGROUPID
|
|
,C_EFOP32317
|
|
,C_MULTIKULTURALISORA
|
|
,C_KOMPLEXORA
|
|
,C_KIPORA
|
|
,C_VEKOP73317
|
|
,C_KAPORA
|
|
,C_TEREMID
|
|
,C_FOGLALKOZASID
|
|
,C_TANTARGYID
|
|
,C_OSZTALYCSOPORTID
|
|
,C_TANARID
|
|
,C_ORATULAJDONOSID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
,NNID
|
|
,C_EGYEDINAP
|
|
) SELECT
|
|
oo.C_DIFFERENCIALT AS C_DIFFERENCIALT -- C_DIFFERENCIALT - char(1)
|
|
,oo.C_DRAMA AS C_DRAMA -- C_DRAMA - char(1)
|
|
,1554 AS C_HETIREND -- C_HETIREND - int
|
|
,oo.C_HETNAPJA AS C_HETNAPJA -- C_HETNAPJA - int
|
|
,oo.C_IKTTANORA AS C_IKTTANORA -- C_IKTTANORA - char(1)
|
|
,oo.C_IPRTANORA AS C_IPRTANORA -- C_IPRTANORA - char(1)
|
|
,oo.C_KOOPERATIV AS C_KOOPERATIV -- C_KOOPERATIV - char(1)
|
|
,oo.C_MERES AS C_MERES -- C_MERES - char(1)
|
|
,oo.C_MINDENNAPOSTESTNEVELES AS C_MINDENNAPOSTESTNEVELES -- C_MINDENNAPOSTESTNEVELES - char(1)
|
|
,oo.C_NEMSZAKRENDSZERUORA AS C_NEMSZAKRENDSZERUORA -- C_NEMSZAKRENDSZERUORA - char(1)
|
|
,oo.C_NEMZETISEGIORA AS C_NEMZETISEGIORA -- C_NEMZETISEGIORA - char(1)
|
|
,oo.C_ORASZAM AS C_ORASZAM -- C_ORASZAM - int
|
|
,oo.C_SORSZAMOZANDO AS C_SORSZAMOZANDO -- C_SORSZAMOZANDO - char(1)
|
|
,oo.C_TAMOPORA AS C_TAMOPORA -- C_TAMOPORA - char(1)
|
|
,oo.C_TULORA AS C_TULORA -- C_TULORA - char(1)
|
|
,oo.C_BONTOTT AS C_BONTOTT -- C_BONTOTT - char(1)
|
|
,@ujDatum AS C_ORAERVENYESSEGKEZDETE -- C_ORAERVENYESSEGKEZDETE - datetime
|
|
,@ujDatum AS C_ORAERVENYESSEGVEGE -- C_ORAERVENYESSEGVEGE - datetime
|
|
,oo.C_PARHUZAMOSORA AS C_PARHUZAMOSORA -- C_PARHUZAMOSORA - char(1)
|
|
,oo.C_TIOP12 AS C_TIOP12 -- C_TIOP12 - char(1)
|
|
,ISNULL(csro.C_KEZDETE, oo.C_ORAKEZDETE) AS C_ORAKEZDETE -- C_ORAKEZDETE - datetime
|
|
,ISNULL(csro.C_VEGE, oo.C_ORAVEGE) AS C_ORAVEGE -- C_ORAVEGE - datetime
|
|
,oo.C_TANORANKIVULIFOGLALKOZAS AS C_TANORANKIVULIFOGLALKOZAS -- C_TANORANKIVULIFOGLALKOZAS - char(1)
|
|
,'F' AS C_IMPORTALT -- C_IMPORTALT - char(1)
|
|
,ISNULL(csro.C_CSENGETESIRENDID, oo.C_CSENGETESIRENDID) AS C_CSENGETESIRENDID -- C_CSENGETESIRENDID - int
|
|
,ISNULL(csro.ID, oo.C_CSENGETESIRENDORAID) AS C_CSENGETESIRENDORAID -- C_CSENGETESIRENDORAID - int
|
|
,oo.C_ORARENDIORAGROUPID AS C_ORARENDIORAGROUPID -- C_ORARENDIORAGROUPID - int
|
|
,oo.C_EFOP32317 AS C_EFOP32317 -- C_EFOP32317 - char(1)
|
|
,oo.C_MULTIKULTURALISORA AS C_MULTIKULTURALISORA -- C_MULTIKULTURALISORA - char(1)
|
|
,oo.C_KOMPLEXORA AS C_KOMPLEXORA -- C_KOMPLEXORA - char(1)
|
|
,oo.C_KIPORA AS C_KIPORA -- C_KIPORA - char(1)
|
|
,oo.C_VEKOP73317 AS C_VEKOP73317 -- C_VEKOP73317 - char(1)
|
|
,oo.C_KAPORA AS C_KAPORA -- C_KAPORA - char(1)
|
|
,oo.C_TEREMID AS C_TEREMID -- C_TEREMID - int
|
|
,oo.C_FOGLALKOZASID AS C_FOGLALKOZASID -- C_FOGLALKOZASID - int
|
|
,oo.C_TANTARGYID AS C_TANTARGYID -- C_TANTARGYID - int
|
|
,oo.C_OSZTALYCSOPORTID AS C_OSZTALYCSOPORTID -- C_OSZTALYCSOPORTID - int
|
|
,oo.C_TANARID AS C_TANARID -- C_TANARID - int
|
|
,oo.C_ORATULAJDONOSID AS C_ORATULAJDONOSID -- C_ORATULAJDONOSID - int
|
|
,oo.C_INTEZMENYID AS C_INTEZMENYID -- C_INTEZMENYID - int
|
|
,oo.C_TANEVID AS C_TANEVID -- C_TANEVID - int
|
|
,'F' AS TOROLT -- TOROLT - char(1)
|
|
,0 AS SERIAL -- SERIAL - int
|
|
,GETDATE() AS LASTCHANGED -- LASTCHANGED - datetime
|
|
,GETDATE() AS CREATED -- CREATED - datetime
|
|
,@creator AS MODIFIER -- MODIFIER - int
|
|
,@creator AS CREATOR -- CREATOR - int
|
|
,NULL AS ELOZOTANEVIREKORDID -- ELOZOTANEVIREKORDID - int
|
|
,NULL AS NNID -- NNID - int
|
|
,'T' AS C_EGYEDINAP -- C_EGYEDINAP - char(1)
|
|
FROM T_ORARENDIORA_OSSZES oo
|
|
LEFT JOIN T_CSENGETESIRENDORA_OSSZES csro ON csro.C_ORASZAM = oo.C_ORASZAM AND csro.C_CSENGETESIRENDID = @csengetesiRendId AND csro.TOROLT = 'F'
|
|
WHERE ((C_ORAERVENYESSEGKEZDETE <= @elteroNapDatuma AND (C_ORAERVENYESSEGVEGE IS NULL OR C_ORAERVENYESSEGVEGE > @elteroNapDatuma)) OR (C_ORAERVENYESSEGKEZDETE = @elteroNapDatuma AND C_ORAERVENYESSEGVEGE = @elteroNapDatuma))
|
|
AND oo.C_TANEVID = @pTanevId
|
|
AND oo.C_INTEZMENYID = @pIntezmenyId
|
|
AND oo.TOROLT = 'F'
|
|
AND oo.C_EGYEDINAP = 'F'
|
|
AND oo.C_HETNAPJA = @hetnapja AND (oo.C_HETIREND = 1554 OR oo.C_HETIREND = @hetirend)
|
|
END
|
|
ELSE BEGIN
|
|
INSERT INTO T_ORARENDIORA (
|
|
C_DIFFERENCIALT
|
|
,C_DRAMA
|
|
,C_HETIREND
|
|
,C_HETNAPJA
|
|
,C_IKTTANORA
|
|
,C_IPRTANORA
|
|
,C_KOOPERATIV
|
|
,C_MERES
|
|
,C_MINDENNAPOSTESTNEVELES
|
|
,C_NEMSZAKRENDSZERUORA
|
|
,C_NEMZETISEGIORA
|
|
,C_ORASZAM
|
|
,C_SORSZAMOZANDO
|
|
,C_TAMOPORA
|
|
,C_TULORA
|
|
,C_BONTOTT
|
|
,C_ORAERVENYESSEGKEZDETE
|
|
,C_ORAERVENYESSEGVEGE
|
|
,C_PARHUZAMOSORA
|
|
,C_TIOP12
|
|
,C_ORAKEZDETE
|
|
,C_ORAVEGE
|
|
,C_TANORANKIVULIFOGLALKOZAS
|
|
,C_IMPORTALT
|
|
,C_CSENGETESIRENDID
|
|
,C_CSENGETESIRENDORAID
|
|
,C_ORARENDIORAGROUPID
|
|
,C_EFOP32317
|
|
,C_MULTIKULTURALISORA
|
|
,C_KOMPLEXORA
|
|
,C_KIPORA
|
|
,C_VEKOP73317
|
|
,C_KAPORA
|
|
,C_TEREMID
|
|
,C_FOGLALKOZASID
|
|
,C_TANTARGYID
|
|
,C_OSZTALYCSOPORTID
|
|
,C_TANARID
|
|
,C_ORATULAJDONOSID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
,NNID
|
|
,C_EGYEDINAP
|
|
) SELECT
|
|
oo.C_DIFFERENCIALT AS C_DIFFERENCIALT -- C_DIFFERENCIALT - char(1)
|
|
,oo.C_DRAMA AS C_DRAMA -- C_DRAMA - char(1)
|
|
,1554 AS C_HETIREND -- C_HETIREND - int
|
|
,oo.C_HETNAPJA AS C_HETNAPJA -- C_HETNAPJA - int
|
|
,oo.C_IKTTANORA AS C_IKTTANORA -- C_IKTTANORA - char(1)
|
|
,oo.C_IPRTANORA AS C_IPRTANORA -- C_IPRTANORA - char(1)
|
|
,oo.C_KOOPERATIV AS C_KOOPERATIV -- C_KOOPERATIV - char(1)
|
|
,oo.C_MERES AS C_MERES -- C_MERES - char(1)
|
|
,oo.C_MINDENNAPOSTESTNEVELES AS C_MINDENNAPOSTESTNEVELES -- C_MINDENNAPOSTESTNEVELES - char(1)
|
|
,oo.C_NEMSZAKRENDSZERUORA AS C_NEMSZAKRENDSZERUORA -- C_NEMSZAKRENDSZERUORA - char(1)
|
|
,oo.C_NEMZETISEGIORA AS C_NEMZETISEGIORA -- C_NEMZETISEGIORA - char(1)
|
|
,oo.C_ORASZAM AS C_ORASZAM -- C_ORASZAM - int
|
|
,oo.C_SORSZAMOZANDO AS C_SORSZAMOZANDO -- C_SORSZAMOZANDO - char(1)
|
|
,oo.C_TAMOPORA AS C_TAMOPORA -- C_TAMOPORA - char(1)
|
|
,oo.C_TULORA AS C_TULORA -- C_TULORA - char(1)
|
|
,oo.C_BONTOTT AS C_BONTOTT -- C_BONTOTT - char(1)
|
|
,@ujDatum AS C_ORAERVENYESSEGKEZDETE -- C_ORAERVENYESSEGKEZDETE - datetime
|
|
,@ujDatum AS C_ORAERVENYESSEGVEGE -- C_ORAERVENYESSEGVEGE - datetime
|
|
,oo.C_PARHUZAMOSORA AS C_PARHUZAMOSORA -- C_PARHUZAMOSORA - char(1)
|
|
,oo.C_TIOP12 AS C_TIOP12 -- C_TIOP12 - char(1)
|
|
,ISNULL(csro.C_KEZDETE, oo.C_ORAKEZDETE) AS C_ORAKEZDETE -- C_ORAKEZDETE - datetime
|
|
,ISNULL(csro.C_VEGE, oo.C_ORAVEGE) AS C_ORAVEGE -- C_ORAVEGE - datetime
|
|
,oo.C_PARHUZAMOSORA AS C_TANORANKIVULIFOGLALKOZAS -- C_TANORANKIVULIFOGLALKOZAS - char(1)
|
|
,'F' AS C_IMPORTALT -- C_IMPORTALT - char(1)
|
|
,ISNULL(csro.C_CSENGETESIRENDID, oo.C_CSENGETESIRENDID) AS C_CSENGETESIRENDID -- C_CSENGETESIRENDID - int
|
|
,ISNULL(csro.ID, oo.C_CSENGETESIRENDORAID) AS C_CSENGETESIRENDORAID -- C_CSENGETESIRENDORAID - int
|
|
,oo.C_ORARENDIORAGROUPID AS C_ORARENDIORAGROUPID -- C_ORARENDIORAGROUPID - int
|
|
,oo.C_EFOP32317 AS C_EFOP32317 -- C_EFOP32317 - char(1)
|
|
,oo.C_MULTIKULTURALISORA AS C_MULTIKULTURALISORA -- C_MULTIKULTURALISORA - char(1)
|
|
,oo.C_KOMPLEXORA AS C_KOMPLEXORA -- C_KOMPLEXORA - char(1)
|
|
,oo.C_KIPORA AS C_KIPORA -- C_KIPORA - char(1)
|
|
,oo.C_VEKOP73317 AS C_VEKOP73317 -- C_VEKOP73317 - char(1)
|
|
,oo.C_KAPORA AS C_KAPORA -- C_KAPORA - char(1)
|
|
,oo.C_TEREMID AS C_TEREMID -- C_TEREMID - int
|
|
,oo.C_FOGLALKOZASID AS C_FOGLALKOZASID -- C_FOGLALKOZASID - int
|
|
,oo.C_TANTARGYID AS C_TANTARGYID -- C_TANTARGYID - int
|
|
,oo.C_OSZTALYCSOPORTID AS C_OSZTALYCSOPORTID -- C_OSZTALYCSOPORTID - int
|
|
,oo.C_TANARID AS C_TANARID -- C_TANARID - int
|
|
,oo.C_ORATULAJDONOSID AS C_ORATULAJDONOSID -- C_ORATULAJDONOSID - int
|
|
,oo.C_INTEZMENYID AS C_INTEZMENYID -- C_INTEZMENYID - int
|
|
,oo.C_TANEVID AS C_TANEVID -- C_TANEVID - int
|
|
,'F' AS TOROLT -- TOROLT - char(1)
|
|
,0 AS SERIAL -- SERIAL - int
|
|
,GETDATE() AS LASTCHANGED -- LASTCHANGED - datetime
|
|
,GETDATE() AS CREATED -- CREATED - datetime
|
|
,@creator AS MODIFIER -- MODIFIER - int
|
|
,@creator AS CREATOR -- CREATOR - int
|
|
,NULL AS ELOZOTANEVIREKORDID -- ELOZOTANEVIREKORDID - int
|
|
,NULL AS NNID -- NNID - int
|
|
,'T' AS C_EGYEDINAP -- C_EGYEDINAP - char(1)
|
|
FROM T_ORARENDIORA_OSSZES oo
|
|
INNER JOIN T_OSZTALYCSOPORT_TANEVRENDJE ocstr ON oo.C_OSZTALYCSOPORTID = ocstr.C_OSZTALYCSOPORTID AND ocstr.C_TANEVRENDJEID = @pTanevRendjeId
|
|
LEFT JOIN T_CSENGETESIRENDORA_OSSZES csro ON csro.C_ORASZAM = oo.C_ORASZAM AND csro.C_CSENGETESIRENDID = @csengetesiRendId AND csro.TOROLT = 'F'
|
|
WHERE ((C_ORAERVENYESSEGKEZDETE <= @elteroNapDatuma AND (C_ORAERVENYESSEGVEGE IS NULL OR C_ORAERVENYESSEGVEGE > @elteroNapDatuma)) OR (C_ORAERVENYESSEGKEZDETE = @elteroNapDatuma AND C_ORAERVENYESSEGVEGE = @elteroNapDatuma))
|
|
AND oo.C_TANEVID = @pTanevId
|
|
AND oo.C_INTEZMENYID = @pIntezmenyId
|
|
AND oo.TOROLT = 'F'
|
|
AND oo.C_EGYEDINAP = 'F'
|
|
AND oo.C_HETNAPJA = @hetnapja AND (oo.C_HETIREND = 1554 OR oo.C_HETIREND = @hetirend)
|
|
END
|
|
|
|
|
|
END |