92 lines
5.3 KiB
Transact-SQL
92 lines
5.3 KiB
Transact-SQL
IF OBJECT_ID('sp_FoglalkozasArchiv') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_FoglalkozasArchiv
|
|
END;
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_FoglalkozasArchiv
|
|
@intezmenyId int,
|
|
@tanevId int
|
|
AS
|
|
BEGIN
|
|
INSERT INTO T_FOGLALKOZASARCHIV (
|
|
[C_ALKALMAZOTTID] -- INT NULL,
|
|
,[C_ELFOGADASDATUMA] -- DATETIME NULL,
|
|
,[C_FELADATELLATASIHELYID] -- INT NULL,
|
|
,[C_FELADATELLATASIHELYNEVE] -- NVARCHAR (255) NULL,
|
|
,[C_ORASZAM] -- NUMERIC (10, 2) NULL,
|
|
,[C_OSZTALYCSOPORTID] -- INT NULL,
|
|
,[C_OSZTALYCSOPORTNEVE] -- NVARCHAR (255) NULL,
|
|
,[C_TANARKOTELEZOORASZAMA] -- NUMERIC (10, 2) NULL,
|
|
,[C_TANARMUNKAIDOKEDVEZMENYE] -- INT NULL,
|
|
,[C_TANARMUNKAIDOKEDVEZMENYOKA] -- NVARCHAR (255) NULL,
|
|
,[C_TANARMUNKAIDOKEDVEZMENYOKAID]-- INT NULL,
|
|
,[C_TANARMUNKAKORE] -- NVARCHAR (255) NULL,
|
|
,[C_TANARMUNKAKOREID] -- INT NULL,
|
|
,[C_TANARNEVE] -- NVARCHAR (255) NULL,
|
|
,[C_TANARPEDAGOGIAIKEPESITESE] -- NVARCHAR (255) NULL,
|
|
,[C_TANARPEDAGOGIAIKEPESITESEID]-- INT NULL,
|
|
,[C_TANEVNEVE] -- NVARCHAR (255) NULL,
|
|
,[C_TANTARGYID] -- INT NULL,
|
|
,[C_TANTARGYNEVE] -- NVARCHAR (255) NULL,
|
|
,[C_INTEZMENYID] -- INT NOT NULL,
|
|
,[C_TANEVID] -- INT NOT NULL,
|
|
,[TOROLT] -- CHAR (1) NULL,
|
|
,[SERIAL] -- INT NULL,
|
|
,[LASTCHANGED] -- DATETIME NULL,
|
|
,[CREATED] -- DATETIME NULL,
|
|
,[MODIFIER] -- INT NULL,
|
|
,[CREATOR] -- INT NULL,
|
|
) SELECT
|
|
u.ID AS [C_ALKALMAZOTTID] -- INT NULL,
|
|
,GETDATE() AS [C_ELFOGADASDATUMA] -- DATETIME NULL,
|
|
,fh.ID AS [C_FELADATELLATASIHELYID] -- INT NULL,
|
|
,'' AS [C_FELADATELLATASIHELYNEVE] -- NVARCHAR (255) NULL,
|
|
,f.C_ORASZAM AS [C_ORASZAM] -- NUMERIC (10, 2) NULL,
|
|
,ocs.ID AS [C_OSZTALYCSOPORTID] -- INT NULL,
|
|
,ocs.C_NEV AS [C_OSZTALYCSOPORTNEVE] -- NVARCHAR (255) NULL,
|
|
,m.C_KOTELEZOORASZAM AS [C_TANARKOTELEZOORASZAMA] -- NUMERIC (10, 2) NULL,
|
|
,m.C_MUNKAIDOKEDVEZMENYORASZAM AS [C_TANARMUNKAIDOKEDVEZMENYE] -- INT NULL,
|
|
,mk.C_NAME AS [C_TANARMUNKAIDOKEDVEZMENYOKA] -- NVARCHAR (255) NULL,
|
|
,m.C_MUNKAIDOKEDVEZMENYOKA AS [C_TANARMUNKAIDOKEDVEZMENYOKAID]-- INT NULL,
|
|
,k.C_NAME AS [C_TANARMUNKAKORE] -- NVARCHAR (255) NULL,
|
|
,m.C_MUNKAKORTIPUSA AS [C_TANARMUNKAKOREID] -- INT NULL,
|
|
,u.C_NYOMTATASINEV AS [C_TANARNEVE] -- NVARCHAR (255) NULL,
|
|
,kp.C_NAME AS [C_TANARPEDAGOGIAIKEPESITESE] -- NVARCHAR (255) NULL,
|
|
,m.C_BESOROLASIFOKOZAT AS [C_TANARPEDAGOGIAIKEPESITESEID]-- INT NULL,
|
|
,te.C_NEV AS [C_TANEVNEVE] -- NVARCHAR (255) NULL,
|
|
,t.ID AS [C_TANTARGYID] -- INT NULL,
|
|
,t.C_NEV AS [C_TANTARGYNEVE] -- NVARCHAR (255) NULL,
|
|
,f.C_INTEZMENYID AS [C_INTEZMENYID] -- INT NOT NULL,
|
|
,f.C_TANEVID AS [C_TANEVID] -- INT NOT NULL,
|
|
,'F' AS [TOROLT] -- CHAR (1) NULL,
|
|
,0 AS [SERIAL] -- INT NULL,
|
|
,NULL AS [LASTCHANGED] -- DATETIME NULL,
|
|
,GETDATE() AS [CREATED] -- DATETIME NULL,
|
|
,NULL AS [MODIFIER] -- INT NULL,
|
|
,NULL AS [CREATOR] -- INT NULL,
|
|
-- select count(1)
|
|
FROM T_FOGLALKOZAS f
|
|
INNER JOIN T_FELHASZNALO u ON u.ID = f.C_TANARID
|
|
INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = f.C_OSZTALYCSOPORTID and ocs.C_FELADATKATEGORIAID = (SELECT tev.C_DEFAULTFELADATKATEGORIAID FROM T_TANEV tev WHERE tev.ID = ocs.C_TANEVID)
|
|
INNER JOIN T_ALKALMAZOTT a ON a.ID = u.ID
|
|
INNER JOIN T_MUNKAUGYIADATOK m ON m.C_ALKALMAZOTTID = a.ID
|
|
INNER JOIN T_TANTARGY t ON f.C_TANTARGYID = t.ID
|
|
INNER JOIN T_TANEV te ON te.ID = f.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASE mk ON mk.ID = m.C_MUNKAIDOKEDVEZMENYOKA AND mk.C_TANEVID = f.C_TANEVID AND mk.C_INTEZMENYID = f.C_INTEZMENYID
|
|
LEFT JOIN T_DICTIONARYITEMBASE k ON k.ID = m.C_MUNKAKORTIPUSA AND k.C_TANEVID = f.C_TANEVID AND k.C_INTEZMENYID = f.C_INTEZMENYID
|
|
LEFT JOIN T_DICTIONARYITEMBASE kp ON kp.ID = m.C_BESOROLASIFOKOZAT AND kp.C_TANEVID = f.C_TANEVID AND kp.C_INTEZMENYID = f.C_INTEZMENYID
|
|
INNER JOIN T_FELADATELLATASIHELY fh ON fh.ID = m.C_FELADATELLATASIHELYID
|
|
WHERE f.TOROLT = 'F'
|
|
/*AND f.C_INTEZMENYID IN (
|
|
SELECT DISTINCT e.C_INTEZMENYID FROM T_ENTITYHISTORY e
|
|
INNER JOIN T_ENTITYATTRIBUTEHISTORY a ON a.C_ENTITYHISTORYID = e.ID
|
|
INNER JOIN T_INTEZMENYADATSZOLGALTATAS i ON i.C_INTEZMENYID = e.C_INTEZMENYID
|
|
WHERE e.C_ENTITYNAME IN ('T_INTEZMENYADATSZOLGALTATAS', 'INTEZMENYADATSZOLGALTATAS')
|
|
AND a.C_PROPERTYNAME = 'C_VEGLEGESTTF'
|
|
AND a.C_CURRENTVALUE = 'T'
|
|
AND i.C_VEGLEGESTTF = 'T'
|
|
)*/
|
|
AND f.C_INTEZMENYID = @intezmenyId AND f.C_TANEVID = @tanevId
|
|
END
|
|
GO
|