59 lines
2.1 KiB
Transact-SQL
59 lines
2.1 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('[dbo].[sp_PedagogusNemBejegyzettOrai]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_PedagogusNemBejegyzettOrai]
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_PedagogusNemBejegyzettOrai]
|
|
@tanevID int,
|
|
@mindenHeten int,
|
|
@tanarID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
CONVERT(NVARCHAR(10),ROW_NUMBER() OVER (ORDER BY naptar.C_NAPDATUMA ))+'.' AS COLUMN440
|
|
,FORMAT(naptar.C_NAPDATUMA, 'yyyy.MM.dd.') COLUMN273
|
|
,oo.C_ORASZAM COLUMN274
|
|
,ocs.C_NEV COLUMN673
|
|
,f.C_NYOMTATASINEV COLUMN456
|
|
,t.C_NEV COLUMN252
|
|
,ter.C_NEV COLUMN2477
|
|
,d.C_NAME COLUMN4252
|
|
FROM T_ORARENDIORA_OSSZES oo
|
|
INNER JOIN T_NAPTARINAP_OSSZES naptar ON ((naptar.C_NAPDATUMA >= oo.C_ORAERVENYESSEGKEZDETE
|
|
AND oo.C_ORAERVENYESSEGVEGE> naptar.C_NAPDATUMA) OR (naptar.C_NAPDATUMA=oo.C_ORAERVENYESSEGKEZDETE AND naptar.C_NAPDATUMA=oo.C_ORAERVENYESSEGVEGE))
|
|
AND naptar.C_ORARENDINAP='T'
|
|
AND (naptar.C_HETIREND = oo.C_HETIREND OR oo.C_HETIREND = @mindenHeten)
|
|
AND oo.C_HETNAPJA=naptar.C_HETNAPJA
|
|
AND (naptar.C_NAPTIPUSA NOT IN (1386,1387,1389,1391,1392,1396,1397,1398,1399,1401))
|
|
LEFT JOIN T_TANITASIORA_OSSZES tao ON naptar.C_NAPDATUMA=tao.C_DATUM
|
|
AND oo.ID = tao.C_ORARENDIORAID
|
|
AND tao.TOROLT='F'
|
|
LEFT JOIN T_HELYETTESITESIIDOSZAK_OSSZES hely ON hely.C_HELYETTESITETTORARENDID=oo.ID AND CAST(hely.C_HELYETTESITESNAPJA AS DATE) = naptar.C_NAPDATUMA
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = oo.C_TANARID
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID=oo.C_OSZTALYCSOPORTID
|
|
INNER JOIN T_TANTARGY_OSSZES t ON t.ID=oo.C_TANTARGYID
|
|
INNER JOIN T_TEREM_OSSZES ter ON ter.ID=oo.C_TEREMID
|
|
INNER JOIN (SELECT DISTINCT ID, C_NAME FROM T_DICTIONARYITEMBASE_OSSZES) d ON d.ID=oo.C_HETIREND
|
|
WHERE oo.C_TANEVID=@tanevId
|
|
AND oo.TOROLT='F'
|
|
AND oo.C_TANARID = @tanarID
|
|
AND naptar.C_NAPDATUMA <= GETDATE()
|
|
AND tao.ID IS NULL
|
|
AND hely.ID IS NULL
|
|
AND oo.TOROLT='F'
|
|
AND (oo.C_TANORANKIVULIFOGLALKOZAS = 'F' OR oo.C_TANORANKIVULIFOGLALKOZAS IS NULL)
|
|
AND oo.C_CSENGETESIRENDORAID IS NOT NULL
|
|
AND naptar.C_TANEVID = @tanevID
|
|
ORDER BY C_NAPDATUMA, oo.C_ORASZAM
|
|
|
|
END
|
|
|
|
GO |