852 lines
30 KiB
Transact-SQL
852 lines
30 KiB
Transact-SQL
IF OBJECT_ID('[dbo].[sp_ImportASC]') IS NOT NULL BEGIN
|
|
DROP PROCEDURE [dbo].[sp_ImportASC]
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_ImportASC]
|
|
@importXml xml,
|
|
@regiTorlese bit,
|
|
@ervenyessegFilebanSzerepel bit,
|
|
@feladatellatasiHelyId int,
|
|
@tanevId int,
|
|
@intezmenyId int,
|
|
@userId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON
|
|
SET XACT_ABORT ON
|
|
DECLARE @mukodsesiHely int
|
|
|
|
DECLARE @osztalyCsoport table (
|
|
osztalyCsoportID nvarchar(32),
|
|
nev nvarchar(255),
|
|
osztalyCsoport varchar(2),
|
|
id int
|
|
)
|
|
|
|
INSERT INTO @osztalyCsoport (osztalyCsoportID, nev, osztalyCsoport)
|
|
SELECT
|
|
sor.value('(OsztalyId)[1]', 'nvarchar(32)') AS osztalcsoportId,
|
|
sor.value('(Nev)[1]', 'varchar(255)') AS nev,
|
|
'O'
|
|
FROM @importXml.nodes('/AscImport/OsztalyLista/Osztaly') as sorok(sor)
|
|
INSERT INTO @osztalyCsoport (osztalyCsoportID, nev, osztalyCsoport)
|
|
SELECT
|
|
sor.value('(Id)[1]', 'nvarchar(32)') AS osztalcsoportId,
|
|
sor.value('(Nev)[1]', 'varchar(255)') AS nev,
|
|
'CS'
|
|
FROM @importXml.nodes('/AscImport/CsoportLista/Csoport') as sorok(sor)
|
|
|
|
UPDATE x
|
|
SET x.id = ocs.ID
|
|
FROM @osztalyCsoport x
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON x.nev = ocs.C_NEV AND ocs.C_TANEVID = @tanevId
|
|
WHERE ocs.TOROLT = 'F'
|
|
|
|
DECLARE @tantargy table (
|
|
tantargyId nvarchar(32),
|
|
nev nvarchar(255),
|
|
osztalyCsoport varchar(2),
|
|
id int
|
|
)
|
|
|
|
INSERT INTO @tantargy (tantargyId, nev)
|
|
SELECT
|
|
sor.value('(TantargyId)[1]', 'nvarchar(32)') AS tantargyId,
|
|
sor.value('(Nev)[1]', 'varchar(255)') AS nev
|
|
FROM @importXml.nodes('/AscImport/TantargyLista/Tantargy') as sorok(sor)
|
|
|
|
UPDATE x
|
|
SET x.id = t.ID
|
|
FROM @tantargy x
|
|
INNER JOIN T_TANTARGY_OSSZES t ON x.nev = t.C_NEV AND t.C_TANEVID = @tanevId
|
|
WHERE t.TOROLT = 'F'
|
|
|
|
/* TEREM temp TÁBLÁBA */
|
|
DECLARE @terem TABLE (
|
|
teremId nvarchar(32),
|
|
nev nvarchar(255),
|
|
kapacitas int,
|
|
id int
|
|
)
|
|
INSERT INTO @terem (teremId, nev, kapacitas)
|
|
SELECT
|
|
sor.value('(TeremId)[1]', 'nvarchar(32)') AS teremId,
|
|
sor.value('(Nev)[1]', 'nvarchar(255)') AS nev,
|
|
sor.value('(Kapacitas)[1]', 'int') AS kapacitas
|
|
FROM @importXml.nodes('/AscImport/TeremLista/Terem') as sorok(sor)
|
|
UPDATE x
|
|
SET x.id = t.ID
|
|
FROM @terem x
|
|
INNER JOIN T_TEREM_OSSZES t ON x.nev = t.C_NEV AND t.C_TANEVID = @tanevId
|
|
WHERE t.TOROLT = 'F'
|
|
|
|
/* HETIREND temp TÁBLÁBA */
|
|
DECLARE @hetirend TABLE (
|
|
hetirendId nvarchar(32),
|
|
nev nvarchar(255),
|
|
id int
|
|
)
|
|
INSERT INTO @hetirend (hetirendId, nev)
|
|
SELECT
|
|
sor.value('(HetirendId)[1]', 'nvarchar(32)') AS hetirendId,
|
|
sor.value('(Nev)[1]', 'nvarchar(255)') AS nev
|
|
FROM @importXml.nodes('/AscImport/HetirendLista/Hetirend') as sorok(sor)
|
|
UPDATE x
|
|
SET x.id = d.ID
|
|
FROM @hetirend x
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES d ON x.nev = d.C_NAME AND d.C_TANEVID = @tanevId AND C_TYPE = 'HetiRendTipus' AND d.TOROLT = 'F'
|
|
|
|
/* CSENGETÉSIREND temp TÁBLÁBA */
|
|
DECLARE @csengetesiRendOra TABLE (
|
|
csengetesiRendOraId nvarchar(32),
|
|
starttime time,
|
|
endtime time,
|
|
sorszam int,
|
|
id int
|
|
)
|
|
|
|
INSERT INTO @csengetesiRendOra (csengetesiRendOraId, starttime, endtime, sorszam)
|
|
SELECT
|
|
sor.value('(CsengetesiRendOraId)[1]', 'nvarchar(32)') AS csengetesiRendOraId,
|
|
sor.value('(Starttime)[1]', 'time') AS starttime,
|
|
sor.value('(Endtime)[1]', 'time') AS endtime,
|
|
sor.value('(Sorszam)[1]', 'int') AS sorszam
|
|
FROM @importXml.nodes('/AscImport/CsengetesiRendOraLista/CsengetesiRendOra') as sorok(sor)
|
|
UPDATE x
|
|
SET x.id = cs.ID
|
|
FROM @csengetesiRendOra x
|
|
INNER JOIN T_CSENGETESIRENDORA_OSSZES cs ON x.sorszam = cs.C_ORASZAM AND cs.C_TANEVID = @tanevId
|
|
WHERE cs.C_CSENGETESIRENDID = (SELECT MIN(ID) FROM T_CSENGETESIREND_OSSZES csr WHERE /*csr.C_IMPORTALT = 'T' AND*/ csr.C_TANEVID = @tanevId AND csr.TOROLT = 'F')
|
|
|
|
DECLARE @tanar TABLE (
|
|
tanarId nvarchar(32),
|
|
nev nvarchar(200),
|
|
id int
|
|
)
|
|
|
|
INSERT INTO @tanar (tanarId, nev)
|
|
SELECT
|
|
sor.value('(Id)[1]', 'nvarchar(32)') AS tanarId,
|
|
sor.value('(Nev)[1]', 'varchar(200)') AS tanarNev
|
|
FROM @importXml.nodes('/AscImport/TanarLista/Tanar') as sorok(sor)
|
|
UPDATE x
|
|
SET x.id = f.ID
|
|
FROM @tanar x
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.C_NYOMTATASINEV = x.nev AND f.TOROLT = 'F'
|
|
WHERE EXISTS (SELECT 1 FROM T_ALKALMAZOTT_OSSZES a WHERE a.ID = f.ID AND a.C_ALINTEZMENYID = f.C_INTEZMENYID AND a.C_ALTANEVID = f.C_TANEVID AND a.TOROLT = 'F')
|
|
AND C_TANEVID = @tanevId
|
|
AND C_INTEZMENYID = @intezmenyId
|
|
|
|
/* ÓRARENDIÓRA temp TÁBLÁBA */
|
|
DECLARE @orarendiOra TABLE (
|
|
teremID nvarchar(32),
|
|
tanarID nvarchar(32),
|
|
hetNapja nvarchar(10),
|
|
hetirendID nvarchar(32),
|
|
osztalyID nvarchar(32),
|
|
csoportID nvarchar(32),
|
|
tantargyID nvarchar(32),
|
|
hetiOraszam float,
|
|
sorszam int,
|
|
ervenyessegKezdete datetime,
|
|
ervenyessegVege datetime
|
|
)
|
|
|
|
INSERT INTO @orarendiOra (
|
|
teremID,
|
|
tanarID,
|
|
hetNapja,
|
|
hetirendID,
|
|
osztalyID,
|
|
csoportID,
|
|
tantargyID,
|
|
hetiOraszam,
|
|
sorszam,
|
|
ervenyessegKezdete,
|
|
ervenyessegVege
|
|
)
|
|
SELECT
|
|
sor.value('(TeremId)[1]', 'nvarchar(32)') AS teremID,
|
|
sor.value('(TanarId)[1]', 'nvarchar(32)') AS tanarID,
|
|
sor.value('(HetNapja)[1]', 'nvarchar(10)') AS hetNapja,
|
|
sor.value('(HetirendId)[1]', 'nvarchar(32)') AS hetirendID,
|
|
NULLIF(sor.value('(OsztalyId)[1]', 'nvarchar(32)'), '') AS osztalyID,
|
|
NULLIF(sor.value('(CsoportId)[1]', 'nvarchar(32)'), '') AS csoportID,
|
|
sor.value('(TantargyId)[1]', 'nvarchar(32)') AS tantargyID,
|
|
sor.value('(HetiOraszam)[1]', 'float') AS hetiOraszam,
|
|
sor.value('(Sorszam)[1]', 'int') AS sorszam,
|
|
sor.value('(ErvenyessegKezdete)[1]', 'datetime') AS ervenyessegKezdete,
|
|
IIF(sor.value('(ErvenyessegVege)[1]', 'datetime') = '19000101', NULL, sor.value('(ErvenyessegVege)[1]', 'datetime')) AS ervenyessegVege
|
|
FROM @importXml.nodes('/AscImport/OrarendiOraLista/OrarendiOra') as sorok(sor)
|
|
|
|
|
|
DECLARE
|
|
@utolsoTannap datetime
|
|
,@utolsoTannapVegzos datetime
|
|
|
|
-- Utolsó tanitási nap
|
|
SELECT @utolsoTannap = C_DATUM + 1
|
|
FROM T_TANEVRENDJE_OSSZES
|
|
WHERE C_NAPTIPUSA = 1395
|
|
AND C_INTEZMENYID = @intezmenyId
|
|
AND C_TANEVID = @tanevId
|
|
AND TOROLT = 'F'
|
|
|
|
-- Utolsó tanitási nap végzősöknek
|
|
SELECT @utolsoTannapVegzos = C_DATUM + 1
|
|
FROM T_TANEVRENDJE_OSSZES
|
|
WHERE C_NAPTIPUSA = 1402
|
|
AND C_TANEVID = @tanevId
|
|
AND C_INTEZMENYID = @intezmenyId
|
|
AND TOROLT = 'F'
|
|
|
|
UPDATE o
|
|
SET ervenyessegVege = CASE WHEN ocsk.C_VEGZOSEVFOLYAM = 'F' THEN @utolsoTannap ELSE @utolsoTannapVegzos END
|
|
FROM @orarendiOra o
|
|
INNER JOIN @osztalyCsoport ocs ON o.osztalyID = ocs.osztalyCsoportID
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocsk ON ocs.id = ocsk.ID AND ocsk.TOROLT = 'F'
|
|
WHERE ocs.osztalyCsoport = 'O'
|
|
AND ervenyessegVege IS NULL
|
|
AND ocsk.C_TANEVID = @tanevId
|
|
|
|
UPDATE @orarendiOra
|
|
SET ervenyessegVege = @utolsoTannap
|
|
WHERE ervenyessegVege IS NULL
|
|
|
|
DECLARE @linkTable TABLE (
|
|
insertedID int, tempId nvarchar(32), act nvarchar(20)
|
|
)
|
|
|
|
BEGIN TRY
|
|
BEGIN TRANSACTION
|
|
SELECT @mukodsesiHely = MIN(ID) FROM T_MUKODESIHELY_OSSZES WHERE C_TANEVID = @tanevId
|
|
|
|
MERGE T_TEREM_OSSZES trg
|
|
USING (
|
|
SELECT * FROM @terem WHERE nev <> '-') src ON src.ID = trg.ID AND trg.C_TANEVID = @tanevId AND trg.TOROLT = 'F'
|
|
--WHEN MATCHED THEN
|
|
-- UPDATE SET
|
|
-- trg.C_KAPACITAS = src.kapacitas
|
|
-- trg.SERIAL = trg.SERIAL + 1
|
|
-- ,trg.LASTCHANGED = GETDATE()
|
|
-- ,trg.MODIFIER = @userId
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (
|
|
C_JELLEG
|
|
,C_NEV
|
|
,C_MUKODESIHELYID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,MODIFIER
|
|
,CREATOR
|
|
) VALUES (
|
|
181 -- osztályterem
|
|
,src.nev
|
|
,@mukodsesiHely
|
|
,@intezmenyId
|
|
,@tanevId
|
|
,@userId
|
|
,@userId
|
|
)
|
|
OUTPUT INSERTED.ID, src.teremId, $action
|
|
INTO @linkTable(insertedID, tempId, act);
|
|
|
|
UPDATE x
|
|
SET x.id = y.insertedID
|
|
FROM @terem x
|
|
INNER JOIN @linkTable y ON x.teremId = y.tempId
|
|
WHERE x.ID IS NULL
|
|
DELETE FROM @linktable
|
|
|
|
SET @feladatellatasiHelyId = ISNULL(@feladatellatasiHelyId,
|
|
(SELECT MIN(ID)
|
|
FROM T_FELADATELLATASIHELY_OSSZES
|
|
WHERE C_INTEZMENYID = @intezmenyID
|
|
AND C_TANEVID = @tanevID)
|
|
)
|
|
|
|
MERGE T_OSZTALYCSOPORT_OSSZES trg
|
|
USING (SELECT * FROM @osztalyCsoport WHERE ID IS NULL) src ON 1 = 2
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (
|
|
C_MEGJEGYZES
|
|
,C_NEV
|
|
,C_VEGZOSEVFOLYAM
|
|
,C_EVFOLYAMTIPUSA
|
|
,C_IMPORTALT
|
|
,C_TERVEZETTLETSZAM
|
|
,C_TEREMID
|
|
,C_FELADATELLATASIHELYID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,MODIFIER
|
|
,CREATOR
|
|
) VALUES (
|
|
NULL -- C_MEGJEGYZES - nvarchar(255)
|
|
,src.nev -- C_NEV - nvarchar(255)
|
|
,'F' -- C_VEGZOSEVFOLYAM - char(1)
|
|
,1296 -- C_EVFOLYAMTIPUSA - int
|
|
,'T' -- C_IMPORTALT - char(1)
|
|
,NULL -- C_TERVEZETTLETSZAM - int
|
|
,NULL -- C_TEREMID - int
|
|
,@feladatellatasiHelyId -- C_FELADATELLATASIHELYID - int
|
|
,@intezmenyId -- C_INTEZMENYID - int
|
|
,@tanevId -- C_TANEVID - int
|
|
,@userId -- MODIFIER - int
|
|
,@userId -- CREATOR - int
|
|
)
|
|
OUTPUT INSERTED.ID, src.osztalycsoportId, $action
|
|
INTO @linkTable(insertedID, tempId, act);
|
|
|
|
UPDATE x
|
|
SET x.id = y.insertedID
|
|
FROM @osztalyCsoport x
|
|
INNER JOIN @linkTable y ON x.osztalycsoportId = y.tempId
|
|
WHERE x.ID IS NULL
|
|
|
|
INSERT INTO T_OSZTALY_OSSZES (
|
|
ID
|
|
,C_KEPZESIFORMA
|
|
,C_OSZTALYNAPLOLEIRASA
|
|
,C_OSZTALYNAPLOMEGNYITASA
|
|
,C_OSZTALYNAPLOZARASA
|
|
,C_OSZTALYFONOKID
|
|
,C_OFOHELYETTESID
|
|
,C_TANTERVID
|
|
,C_ALINTEZMENYID
|
|
,C_ALTANEVID
|
|
,MODIFIER
|
|
,CREATOR
|
|
) SELECT
|
|
x.id AS ID -- ID - int
|
|
,1096 AS C_KEPZESIFORMA -- C_KEPZESIFORMA - int
|
|
,NULL AS C_OSZTALYNAPLOLEIRASA -- C_OSZTALYNAPLOLEIRASA - nvarchar(MAX)
|
|
,NULL AS C_OSZTALYNAPLOMEGNYITASA -- C_OSZTALYNAPLOMEGNYITASA - datetime
|
|
,NULL AS C_OSZTALYNAPLOZARASA -- C_OSZTALYNAPLOZARASA - datetime
|
|
,NULL AS C_OSZTALYFONOKID -- C_OSZTALYFONOKID - int
|
|
,NULL AS C_OFOHELYETTESID -- C_OFOHELYETTESID - int
|
|
,NULL AS C_TANTERVID -- C_TANTERVID - int
|
|
,@intezmenyId AS C_ALINTEZMENYID -- C_ALINTEZMENYID - int
|
|
,@tanevId AS C_ALTANEVID -- C_ALTANEVID - int
|
|
,@userId AS MODIFIER -- MODIFIER - int
|
|
,@userId AS CREATOR -- CREATOR - int
|
|
FROM @osztalyCsoport x
|
|
INNER JOIN @linktable y ON x.id = y.insertedID
|
|
WHERE osztalyCsoport = 'O'
|
|
|
|
INSERT INTO T_CSOPORT_OSSZES (
|
|
ID
|
|
,C_TIPUSA
|
|
,C_CSOPORTNAPLOLEIRAS
|
|
,C_CSOPORTNAPLOMEGNYITASA
|
|
,C_CSOPORTNAPLOZARASA
|
|
,C_CSOPORTVEZETOID
|
|
,C_OSZTALYBONTASID
|
|
,C_ALINTEZMENYID
|
|
,C_ALTANEVID
|
|
,MODIFIER
|
|
,CREATOR
|
|
) SELECT
|
|
x.id AS ID -- ID - int
|
|
,1031 AS C_TIPUSA -- C_TIPUSA - int
|
|
,NULL AS C_CSOPORTNAPLOLEIRAS -- C_CSOPORTNAPLOLEIRAS - nvarchar(MAX)
|
|
,NULL AS C_CSOPORTNAPLOMEGNYITASA -- C_CSOPORTNAPLOMEGNYITASA - datetime
|
|
,NULL AS C_CSOPORTNAPLOZARASA -- C_CSOPORTNAPLOZARASA - datetime
|
|
,NULL AS C_CSOPORTVEZETOID -- C_CSOPORTVEZETOID - int
|
|
,NULL AS C_OSZTALYBONTASID -- C_OSZTALYBONTASID - int
|
|
,@intezmenyId AS C_ALINTEZMENYID -- C_ALINTEZMENYID - int
|
|
,@tanevId AS C_ALTANEVID -- C_ALTANEVID - int
|
|
,@userId AS MODIFIER -- MODIFIER - int
|
|
,@userId AS CREATOR -- CREATOR - int
|
|
FROM @osztalyCsoport x
|
|
INNER JOIN @linktable y ON x.id = y.insertedID
|
|
WHERE osztalyCsoport = 'CS'
|
|
|
|
DELETE FROM @linktable
|
|
|
|
MERGE T_TANTARGY_OSSZES trg
|
|
USING (SELECT * FROM @tantargy WHERE ID IS NULL) src ON 1 = 2
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (
|
|
C_GYAKORLATI
|
|
,C_NEV
|
|
,C_ROVIDNEV
|
|
,C_TARGYKATEGORIA
|
|
,C_ALTANTARGYKENTNYOMTATVANYBAN
|
|
,C_NEVNYOMTATVANYBAN
|
|
,C_TANORANKIVULI
|
|
,C_IMPORTALT
|
|
,C_FOTARGYE
|
|
,C_FOTARGYID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,MODIFIER
|
|
,CREATOR
|
|
) VALUES (
|
|
'F' -- C_GYAKORLATI - char(1)
|
|
,nev -- C_NEV - nvarchar(255)
|
|
,LEFT(nev, 20) -- C_ROVIDNEV - nvarchar(20)
|
|
,1197 -- C_TARGYKATEGORIA - int
|
|
,NULL -- C_ALTANTARGYKENTNYOMTATVANYBAN - char(1)
|
|
,NULL -- C_NEVNYOMTATVANYBAN - nvarchar(255)
|
|
,'F' -- C_TANORANKIVULI - char(1)
|
|
,'T' -- C_IMPORTALT - char(1)
|
|
,'F' -- C_FOTARGYE - char(1)
|
|
,NULL -- C_FOTARGYID - int
|
|
,@intezmenyId -- C_INTEZMENYID - int
|
|
,@tanevId -- C_TANEVID - int
|
|
,@userId -- MODIFIER - int
|
|
,@userId -- CREATOR - int
|
|
)
|
|
OUTPUT INSERTED.ID, src.tantargyId, $action
|
|
INTO @linkTable(insertedID, tempId, act);
|
|
|
|
UPDATE x
|
|
SET x.id = y.insertedID
|
|
FROM @tantargy x
|
|
INNER JOIN @linkTable y ON x.tantargyId = y.tempId
|
|
WHERE x.ID IS NULL
|
|
|
|
|
|
DELETE FROM @linktable
|
|
DECLARE @foglalkozas TABLE (
|
|
foglalozasId int IDENTITY(1, 1)
|
|
,tantargyId int
|
|
,tanarId int
|
|
,osztalyCsoportId int
|
|
,hetiOraszam float
|
|
,nev nvarchar(255)
|
|
,id int
|
|
)
|
|
|
|
INSERT INTO @foglalkozas (
|
|
tantargyId
|
|
,tanarId
|
|
,osztalyCsoportId
|
|
,hetiOraszam
|
|
,nev
|
|
,id
|
|
) SELECT
|
|
tt.ID
|
|
,fh.ID
|
|
,ISNULL(cs.ID, o.ID)
|
|
,oraszam
|
|
,tt.nev + ' - ' + ISNULL(cs.nev, o.nev) + ' - ' + x.tanar AS nev
|
|
,x.existItemId AS id
|
|
FROM (
|
|
SELECT
|
|
NULLIF(sor.value('(osztaly)[1]', 'nvarchar(32)'), '') AS osztaly
|
|
,NULLIF(sor.value('(csoport)[1]', 'nvarchar(32)'), '') AS csoport
|
|
,sor.value('(tantargy)[1]', 'nvarchar(32)') AS tantargy
|
|
,sor.value('(oraszam)[1]', 'float') AS oraszam
|
|
,sor.value('(tanar)[1]', 'nvarchar(32)') AS tanar
|
|
,NULLIF(sor.value('(existItemId)[1]', 'nvarchar(32)'), 0) AS existItemId
|
|
FROM @importXml.nodes('/AscImport/TantargyfelosztasLista/Tantargyfelosztas') as sorok(sor)
|
|
) x
|
|
LEFT JOIN @osztalyCsoport cs ON cs.nev = x.csoport
|
|
LEFT JOIN @osztalyCsoport o ON o.nev = x.osztaly
|
|
INNER JOIN @tantargy tt ON tt.nev = x.tantargy
|
|
INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.C_NYOMTATASINEV = x.tanar AND fh.C_TANEVID = @tanevId AND fh.TOROLT = 'F'
|
|
WHERE EXISTS (SELECT 1 FROM T_ALKALMAZOTT_OSSZES a WHERE a.ID = fh.ID AND a.C_ALTANEVID = fh.C_TANEVID AND a.TOROLT = 'F')
|
|
|
|
|
|
/* Az importtal bekerült foglalkozások beszúrása */
|
|
MERGE T_FOGLALKOZAS_OSSZES trg
|
|
USING (select * FROM @foglalkozas WHERE ID IS NULL) src ON 1 = 2
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (
|
|
C_FOGLALKOZASTIPUSA
|
|
,C_ORASZAM
|
|
,C_NEV
|
|
,C_ERTEKELESKELL
|
|
,C_MULASZTASKELL
|
|
,C_TANARFELVEHETI
|
|
,C_IMPORTALT
|
|
,C_TANTARGYID
|
|
,C_OSZTALYCSOPORTID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,MODIFIER
|
|
,CREATOR
|
|
) VALUES (
|
|
1338 -- C_FOGLALKOZASTIPUSA - int
|
|
,hetiOraszam -- C_ORASZAM - numeric(10, 2)
|
|
,nev -- C_NEV - nvarchar(255)
|
|
,'T' -- C_ERTEKELESKELL - char(1)
|
|
,'T' -- C_MULASZTASKELL - char(1)
|
|
,'T' -- C_TANARFELVEHETI - char(1)
|
|
,'T' -- C_IMPORTALT - char(1)
|
|
,tantargyId -- C_TANTARGYID - int
|
|
,osztalyCsoportId -- C_OSZTALYCSOPORTID - int
|
|
,@intezmenyId -- C_INTEZMENYID - int
|
|
,@tanevId -- C_TANEVID - int
|
|
,@userId -- MODIFIER - int
|
|
,@userId -- CREATOR - int
|
|
)
|
|
OUTPUT INSERTED.ID, src.foglalozasId, $action
|
|
INTO @linkTable(insertedID, tempId, act);
|
|
|
|
UPDATE x
|
|
SET x.id = y.insertedID
|
|
FROM @foglalkozas x
|
|
INNER JOIN @linkTable y ON x.foglalozasId = y.tempId
|
|
WHERE x.ID IS NULL
|
|
|
|
DELETE FROM @linktable
|
|
|
|
INSERT INTO T_FOGLALKOZASOK_TANAROK (
|
|
C_FOGLALKOZASOKID
|
|
,C_TANAROKID
|
|
) SELECT
|
|
id AS C_FOGLALKOZASOKID -- C_FOGLALKOZASOKID - int
|
|
,tanarId AS C_TANAROKID -- C_TANAROKID - int
|
|
FROM @foglalkozas f
|
|
WHERE id IS NOT NULL
|
|
AND NOT EXISTS (SELECT 1 FROM T_FOGLALKOZASOK_TANAROK ft WHERE f.ID = ft.C_FOGLALKOZASOKID)
|
|
|
|
/* Az importtal bekerült foglalkozások beszúrása */
|
|
INSERT INTO @foglalkozas (
|
|
tantargyId
|
|
,tanarId
|
|
,osztalyCsoportId
|
|
,hetiOraszam
|
|
,nev
|
|
,id
|
|
) SELECT
|
|
tt.id AS tantargyId
|
|
,a.id AS tanarId
|
|
,ocs.id AS osztalyCsoportId
|
|
,COUNT(1) AS hetiOraszam
|
|
,MIN(tt.nev) + ' - ' + MIN(ocs.nev) + ' - ' + MIN(a.nev) AS nev
|
|
,MIN(ff.ID) AS id
|
|
FROM @orarendiOra o
|
|
INNER JOIN @tanar a ON a.tanarID = o.tanarID
|
|
INNER JOIN @tantargy tt ON tt.tantargyID = o.tantargyID
|
|
INNER JOIN @osztalyCsoport ocs ON (ocs.osztalyCsoportID = o.CsoportId AND ocs.osztalyCsoport = 'CS') --OR (ocs.osztalyCsoportID = o.OsztalyId AND ocs.osztalyCsoport = 'O')
|
|
LEFT JOIN (
|
|
SELECT f.ID, f.C_TANTARGYID, f.C_OSZTALYCSOPORTID, ft.C_TANAROKID
|
|
FROM T_FOGLALKOZAS_OSSZES f
|
|
INNER JOIN T_FOGLALKOZASOK_TANAROK ft ON ft.C_FOGLALKOZASOKID = f.ID
|
|
WHERE f.TOROLT = 'F'
|
|
AND f.C_TANEVID = @tanevId
|
|
) ff
|
|
ON ff.C_TANTARGYID = tt.ID
|
|
AND ff.C_TANAROKID = a.ID
|
|
AND ff.C_OSZTALYCSOPORTID = ocs.ID
|
|
GROUP BY tt.id, a.id, ocs.id
|
|
|
|
INSERT INTO @foglalkozas (
|
|
tantargyId
|
|
,tanarId
|
|
,osztalyCsoportId
|
|
,hetiOraszam
|
|
,nev
|
|
,id
|
|
) SELECT
|
|
tt.id AS tantargyId
|
|
,a.id AS tanarId
|
|
,ocs.id AS osztalyCsoportId
|
|
,COUNT(1) AS hetiOraszam
|
|
,MIN(tt.nev) + ' - ' + MIN(ocs.nev) + ' - ' + MIN(a.nev) AS nev
|
|
,MIN(ff.ID) AS id
|
|
FROM @orarendiOra o
|
|
INNER JOIN @tanar a ON a.tanarID = o.tanarID
|
|
INNER JOIN @tantargy tt ON tt.tantargyID = o.tantargyID
|
|
INNER JOIN @osztalyCsoport ocs ON (ocs.osztalyCsoportID = o.OsztalyId AND ocs.osztalyCsoport = 'O') AND o.csoportId IS NULL --OR (ocs.osztalyCsoportID = o.OsztalyId AND ocs.osztalyCsoport = 'O')
|
|
LEFT JOIN (
|
|
SELECT f.ID, f.C_TANTARGYID, f.C_OSZTALYCSOPORTID, ft.C_TANAROKID
|
|
FROM T_FOGLALKOZAS_OSSZES f
|
|
INNER JOIN T_FOGLALKOZASOK_TANAROK ft ON ft.C_FOGLALKOZASOKID = f.ID
|
|
WHERE f.TOROLT = 'F' AND f.C_TANEVID = @tanevId
|
|
) ff ON ff.C_TANTARGYID = tt.ID
|
|
AND ff.C_TANAROKID = a.ID
|
|
AND ff.C_OSZTALYCSOPORTID = ocs.ID
|
|
GROUP BY tt.id, a.id, ocs.id
|
|
|
|
/* Az órarendi órából keletkező foglalkozások beszúrása */
|
|
MERGE T_FOGLALKOZAS_OSSZES trg
|
|
USING (SELECT * FROM @foglalkozas WHERE ID IS NULL) src ON 1 = 2
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (
|
|
C_FOGLALKOZASTIPUSA
|
|
,C_ORASZAM
|
|
,C_NEV
|
|
,C_ERTEKELESKELL
|
|
,C_MULASZTASKELL
|
|
,C_TANARFELVEHETI
|
|
,C_IMPORTALT
|
|
,C_TANTARGYID
|
|
,C_OSZTALYCSOPORTID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,MODIFIER
|
|
,CREATOR
|
|
) VALUES (
|
|
1338 -- C_FOGLALKOZASTIPUSA - int
|
|
,hetiOraszam -- C_ORASZAM - numeric(10, 2)
|
|
,nev -- C_NEV - nvarchar(255)
|
|
,'T' -- C_ERTEKELESKELL - char(1)
|
|
,'T' -- C_MULASZTASKELL - char(1)
|
|
,'T' -- C_TANARFELVEHETI - char(1)
|
|
,'T' -- C_IMPORTALT - char(1)
|
|
,tantargyId -- C_TANTARGYID - int
|
|
,osztalyCsoportId -- C_OSZTALYCSOPORTID - int
|
|
,@intezmenyId -- C_INTEZMENYID - int
|
|
,@tanevId -- C_TANEVID - int
|
|
,@userId -- MODIFIER - int
|
|
,@userId -- CREATOR - int
|
|
)
|
|
OUTPUT INSERTED.ID, src.foglalozasId, $action
|
|
INTO @linkTable(insertedID, tempId, act);
|
|
|
|
UPDATE x
|
|
SET x.id = y.insertedID
|
|
FROM @foglalkozas x
|
|
INNER JOIN @linkTable y ON x.foglalozasId = y.tempId
|
|
WHERE x.ID IS NULL
|
|
|
|
|
|
DELETE FROM @linktable
|
|
|
|
INSERT INTO T_FOGLALKOZASOK_TANAROK (
|
|
C_FOGLALKOZASOKID
|
|
,C_TANAROKID
|
|
) SELECT
|
|
id AS C_FOGLALKOZASOKID -- C_FOGLALKOZASOKID - int
|
|
,tanarId AS C_TANAROKID -- C_TANAROKID - int
|
|
FROM @foglalkozas f
|
|
WHERE id IS NOT NULL
|
|
AND NOT EXISTS (SELECT 1 FROM T_FOGLALKOZASOK_TANAROK ft WHERE f.ID = ft.C_FOGLALKOZASOKID)
|
|
|
|
/* T_HETIRENDTIPUS INSERT */
|
|
SELECT @mukodsesiHely = MIN(ID) FROM T_MUKODESIHELY_OSSZES WHERE C_TANEVID = @tanevId AND TOROLT = 'F'
|
|
|
|
MERGE T_DICTIONARYITEMBASE_OSSZES trg
|
|
USING @hetirend src ON src.ID = trg.ID AND trg.C_INTEZMENYID = @intezmenyid AND trg.C_TANEVID = @tanevId AND trg.TOROLT = 'F'
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (
|
|
C_VALUE
|
|
,C_NAME
|
|
,C_VISIBLE
|
|
,C_TYPE
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,MODIFIER
|
|
,CREATOR
|
|
) VALUES (
|
|
0
|
|
,src.nev + ' (importálva: ' + CONVERT(varchar(20), GETDATE(), 120) + ')'
|
|
,'T'
|
|
,'HetirendTipus'
|
|
,@intezmenyID
|
|
,@tanevID
|
|
,NULL
|
|
,@userId
|
|
)
|
|
OUTPUT INSERTED.ID, src.hetirendID, $action
|
|
INTO @linkTable(insertedID, tempId, act);
|
|
UPDATE x
|
|
SET x.id = y.insertedID
|
|
FROM @hetirend x
|
|
INNER JOIN @linkTable y ON x.hetirendId = y.tempId
|
|
WHERE x.ID IS NULL;
|
|
|
|
INSERT INTO T_HETIRENDTIPUS_OSSZES (
|
|
ID
|
|
,C_ALINTEZMENYID
|
|
,C_ALTANEVID
|
|
,MODIFIER
|
|
,CREATOR
|
|
) SELECT
|
|
x.ID AS ID -- ID - int
|
|
,@intezmenyId AS C_ALINTEZMENYID -- C_ALINTEZMENYID - int
|
|
,@tanevId AS C_ALTANEVID -- C_ALTANEVID - int
|
|
,NULL AS MODIFIER -- MODIFIER - int
|
|
,@userId AS CREATOR -- CREATOR - int
|
|
FROM @hetirend x
|
|
WHERE NOT EXISTS (SELECT 1 FROM T_HETIRENDTIPUS_OSSZES ht WHERE ht.ID = x.ID AND C_ALTANEVID = @tanevId AND TOROLT = 'F')
|
|
DECLARE @ids TABLE (
|
|
id int
|
|
)
|
|
DECLARE @ervenyessegKezdete datetime
|
|
|
|
/* T_ORARENDIORA INSERT */
|
|
IF @regiTorlese = 1 BEGIN
|
|
IF @ervenyessegFilebanSzerepel = 0 BEGIN
|
|
|
|
SELECT TOP(1) @ervenyessegKezdete = ervenyessegKezdete
|
|
FROM @orarendiOra
|
|
|
|
UPDATE T_ORARENDIORA_OSSZES
|
|
SET
|
|
C_ORAERVENYESSEGVEGE = @ervenyessegKezdete
|
|
,SERIAL += 1
|
|
,LASTCHANGED = GETDATE()
|
|
,MODIFIER = @userId
|
|
WHERE C_ORAERVENYESSEGVEGE > @ervenyessegKezdete
|
|
AND C_TANEVID = @tanevId
|
|
AND C_ORAERVENYESSEGVEGE <> C_ORAERVENYESSEGKEZDETE
|
|
AND TOROLT = 'F'
|
|
AND C_ORAERVENYESSEGKEZDETE < @ervenyessegKezdete
|
|
END
|
|
ELSE BEGIN
|
|
UPDATE T_ORARENDIORA_OSSZES
|
|
SET TOROLT = 'T'
|
|
,SERIAL += 1
|
|
,LASTCHANGED = GETDATE()
|
|
,MODIFIER = @userId
|
|
WHERE TOROLT = 'F'
|
|
AND C_TANEVID = @tanevId
|
|
AND C_ORAERVENYESSEGVEGE <> C_ORAERVENYESSEGKEZDETE
|
|
END
|
|
END
|
|
|
|
DECLARE @csengetesiRendId int
|
|
SELECT @csengetesiRendId = ID FROM T_CSENGETESIREND_OSSZES WHERE C_AKTIV = 'T' AND C_TANEVID = @tanevId AND TOROLT = 'F'
|
|
|
|
INSERT INTO T_ORARENDIORA_OSSZES (
|
|
C_HETIREND
|
|
,C_HETNAPJA
|
|
,C_ORASZAM
|
|
,C_ORAERVENYESSEGKEZDETE
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,C_BONTOTT
|
|
,C_ORAERVENYESSEGVEGE
|
|
,C_ORAKEZDETE
|
|
,C_ORAVEGE
|
|
,C_TEREMID
|
|
,C_FOGLALKOZASID
|
|
,C_CSENGETESIRENDORAID
|
|
,C_TANTARGYID
|
|
,C_OSZTALYCSOPORTID
|
|
,C_TANARID
|
|
,C_ORATULAJDONOSID
|
|
,MODIFIER
|
|
,CREATOR
|
|
,C_IMPORTALT
|
|
,C_CSENGETESIRENDID
|
|
) SELECT --'T_ORARENDIORA',
|
|
h.id AS C_HETIREND
|
|
,CASE o.hetNapja
|
|
WHEN 'Hétfő' THEN 1408
|
|
WHEN 'Kedd' THEN 1409
|
|
WHEN 'Szerda' THEN 1410
|
|
WHEN 'Csütörtök' THEN 1411
|
|
WHEN 'Péntek' THEN 1412
|
|
WHEN 'Szombat' THEN 1413
|
|
WHEN 'Vasárnap' THEN 1414
|
|
ELSE 1407
|
|
END AS C_HETNAPJA -- ?
|
|
,csro.C_ORASZAM AS C_ORASZAM
|
|
,o.ervenyessegKezdete AS C_ORAERVENYESSEGKEZDETE
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,@tanevId AS C_TANEVID
|
|
,'F' AS C_BONTOTT
|
|
,o.ervenyessegVege AS C_ORAERVENYESSEGVEGE
|
|
,csro.C_KEZDETE AS C_ORAKEZDETE
|
|
,csro.C_VEGE AS C_ORAVEGE
|
|
,t.id AS C_TEREMID
|
|
,ff.ID AS C_FOGLALKOZASID -- ?
|
|
,csro.ID AS C_CSENGETESIRENDORAID
|
|
,tt.ID AS C_TANTARGYID -- ?
|
|
,oszt.ID AS C_OSZTALYCSOPORTID -- ?
|
|
,a.ID AS C_TANARID -- ?
|
|
,@userId AS C_ORATULAJDONOSID -- ?
|
|
,NULL AS MODIFIER
|
|
,@userId AS CREATOR
|
|
,'T' AS C_IMPORTALT
|
|
,csro.C_CSENGETESIRENDID AS C_CSENGETESIRENDID
|
|
FROM @orarendiOra o
|
|
INNER JOIN @terem t ON t.teremID = o.teremID
|
|
INNER JOIN @tanar a ON a.tanarID = o.tanarID
|
|
INNER JOIN @tantargy tt ON tt.tantargyID = o.tantargyID
|
|
INNER JOIN @osztalyCsoport oszt ON oszt.osztalyCsoportID = o.OsztalyId AND oszt.osztalyCsoport = 'O' AND o.csoportId IS NULL
|
|
INNER JOIN (
|
|
SELECT f.ID, f.C_TANTARGYID, f.C_OSZTALYCSOPORTID, ft.C_TANAROKID
|
|
FROM T_FOGLALKOZAS_OSSZES f
|
|
INNER JOIN T_FOGLALKOZASOK_TANAROK ft ON ft.C_FOGLALKOZASOKID = f.ID
|
|
WHERE f.TOROLT = 'F' AND f.C_TANEVID = @tanevId
|
|
) ff ON ff.C_TANTARGYID = tt.ID AND ff.C_TANAROKID = a.ID AND ff.C_OSZTALYCSOPORTID = oszt.ID--ISNULL(cs.ID, oszt.ID)
|
|
INNER JOIN @hetirend h ON o.hetirendID = h.hetirendId
|
|
INNER JOIN T_CSENGETESIRENDORA_OSSZES csro ON csro.C_ORASZAM = o.sorszam AND csro.C_CSENGETESIRENDID = @csengetesiRendId AND csro.TOROLT = 'F';
|
|
|
|
INSERT INTO T_ORARENDIORA_OSSZES (
|
|
C_HETIREND
|
|
,C_HETNAPJA
|
|
,C_ORASZAM
|
|
,C_ORAERVENYESSEGKEZDETE
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,C_BONTOTT
|
|
,C_ORAERVENYESSEGVEGE
|
|
,C_ORAKEZDETE
|
|
,C_ORAVEGE
|
|
,C_TEREMID
|
|
,C_FOGLALKOZASID
|
|
,C_CSENGETESIRENDORAID
|
|
,C_TANTARGYID
|
|
,C_OSZTALYCSOPORTID
|
|
,C_TANARID
|
|
,C_ORATULAJDONOSID
|
|
,MODIFIER
|
|
,CREATOR
|
|
,C_IMPORTALT
|
|
,C_CSENGETESIRENDID
|
|
) SELECT --'T_ORARENDIORA',
|
|
h.id AS C_HETIREND
|
|
,CASE o.hetNapja
|
|
WHEN 'Hétfő' THEN 1408
|
|
WHEN 'Kedd' THEN 1409
|
|
WHEN 'Szerda' THEN 1410
|
|
WHEN 'Csütörtök' THEN 1411
|
|
WHEN 'Péntek' THEN 1412
|
|
WHEN 'Szombat' THEN 1413
|
|
WHEN 'Vasárnap' THEN 1414
|
|
ELSE 1407
|
|
END AS C_HETNAPJA -- ?
|
|
,csro.C_ORASZAM AS C_ORASZAM
|
|
,o.ervenyessegKezdete AS C_ORAERVENYESSEGKEZDETE
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,@tanevId AS C_TANEVID
|
|
,'F' AS C_BONTOTT
|
|
,o.ervenyessegVege AS C_ORAERVENYESSEGVEGE
|
|
,csro.C_KEZDETE AS C_ORAKEZDETE
|
|
,csro.C_VEGE AS C_ORAVEGE
|
|
,t.id AS C_TEREMID
|
|
,ff.ID AS C_FOGLALKOZASID -- ?
|
|
,csro.ID AS C_CSENGETESIRENDORAID
|
|
,tt.ID AS C_TANTARGYID -- ?
|
|
,cs.ID AS C_OSZTALYCSOPORTID -- ?
|
|
,a.ID AS C_TANARID -- ?
|
|
,@userId AS C_ORATULAJDONOSID -- ?
|
|
,NULL AS MODIFIER
|
|
,@userId AS CREATOR
|
|
,'T' AS C_IMPORTALT
|
|
,csro.C_CSENGETESIRENDID AS C_CSENGETESIRENDID
|
|
FROM @orarendiOra o
|
|
INNER JOIN @terem t ON t.teremID = o.teremID
|
|
INNER JOIN @tanar a ON a.tanarID = o.tanarID
|
|
INNER JOIN @tantargy tt ON tt.tantargyID = o.tantargyID
|
|
INNER JOIN @osztalyCsoport cs ON cs.osztalyCsoportID = o.CsoportId AND cs.osztalyCsoport = 'CS'
|
|
INNER JOIN (
|
|
SELECT f.ID, f.C_TANTARGYID, f.C_OSZTALYCSOPORTID, ft.C_TANAROKID
|
|
FROM T_FOGLALKOZAS_OSSZES f
|
|
INNER JOIN T_FOGLALKOZASOK_TANAROK ft ON ft.C_FOGLALKOZASOKID = f.ID
|
|
WHERE f.TOROLT = 'F' AND f.C_TANEVID = @tanevId
|
|
) ff ON ff.C_TANTARGYID = tt.ID AND ff.C_TANAROKID = a.ID AND ff.C_OSZTALYCSOPORTID = cs.ID
|
|
INNER JOIN @hetirend h ON o.hetirendID = h.hetirendId
|
|
INNER JOIN T_CSENGETESIRENDORA_OSSZES csro ON csro.C_ORASZAM = o.sorszam AND csro.C_CSENGETESIRENDID = @csengetesiRendId AND csro.TOROLT = 'F';
|
|
|
|
COMMIT TRANSACTION
|
|
-- ROLLBACK TRANSACTION
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0
|
|
ROLLBACK TRANSACTION;
|
|
THROW
|
|
END CATCH
|
|
END
|
|
|
|
GO |