66 lines
2.3 KiB
Transact-SQL
66 lines
2.3 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetJsonForMobileTanuloLista]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetJsonForMobileTanuloLista]
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetJsonForMobileTanuloLista]
|
|
@tanarId INT,
|
|
@foglalkozasId INT = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
osztalycsoport.ID AS 'osztalycsoportId'
|
|
,osztalycsoport.C_NEV AS 'osztalycsoportNev'
|
|
,tantargy.ID AS 'tantargyId'
|
|
,tantargy.C_NEV AS 'tantargyNev'
|
|
,dic.C_NAME AS 'tantargyKategoria'
|
|
,'tanulok' =
|
|
(
|
|
SELECT
|
|
tanulo.ID AS 'tanuloid'
|
|
,tanulo.C_NYOMTATASINEV AS 'tanuloNev'
|
|
,tanulo.C_ANYJANEVE AS 'tanuloAnyjaNev'
|
|
,tanulo.C_SZULETESIDATUM AS 'tanuloSzuletesiDatum'
|
|
,'ertekelesek' =
|
|
(
|
|
SELECT
|
|
te.ID AS 'Id'
|
|
,te.C_ERTEKELESOSZTALYZATID AS 'ertekelesOsztalyzatEnumId'
|
|
,te.C_ERTEKELESSZAZALEK AS 'ertekelesSzazalek'
|
|
,ISNULL(te.C_ERTEKELESSZOVEG, '') AS 'ertekelesSzoveg'
|
|
,ISNULL(te.C_ERTEKELESTEMA, '') AS 'ertekelesTema'
|
|
,te.C_TIPUSID AS 'ertekelesTipusEnumId'
|
|
,te.C_ERTEKELESMODID AS 'ertekelesModEnumId'
|
|
,te.C_DATUM AS 'ertekelesDatum'
|
|
FROM T_TANULOERTEKELES te
|
|
WHERE
|
|
te.C_TANULOID = tanulo.ID AND te.TOROLT = 'F' AND te.C_TANTARGYID = tantargy.ID
|
|
FOR JSON PATH
|
|
)
|
|
FROM T_TANULOCSOPORT tcs
|
|
INNER JOIN T_FELHASZNALO tanulo ON tanulo.TOROLT = 'F' AND tcs.C_TANULOID = tanulo.ID
|
|
WHERE
|
|
tcs.TOROLT = 'F' AND tcs.C_OSZTALYCSOPORTID = osztalycsoport.ID AND
|
|
tcs.C_BELEPESDATUM < GETDATE() AND (tcs.C_KILEPESDATUM > GETDATE() OR tcs.C_KILEPESDATUM IS NULL)
|
|
FOR JSON PATH
|
|
)
|
|
FROM T_OSZTALYCSOPORT osztalycsoport
|
|
JOIN T_FOGLALKOZAS foglalkozas ON foglalkozas.TOROLT = 'F' AND foglalkozas.C_OSZTALYCSOPORTID = osztalycsoport.ID
|
|
JOIN T_TANTARGY tantargy ON tantargy.TOROLT = 'F' AND foglalkozas.C_TANTARGYID = tantargy.ID
|
|
JOIN T_FELHASZNALO felhasznalo ON felhasznalo.TOROLT = 'F' AND foglalkozas.C_TANARID = felhasznalo.ID
|
|
JOIN T_DICTIONARYITEMBASE dic on dic.ID = tantargy.C_TARGYKATEGORIA AND dic.C_TANEVID = tantargy.C_TANEVID AND dic.C_INTEZMENYID = tantargy.C_INTEZMENYID
|
|
WHERE
|
|
osztalycsoport.TOROLT = 'F' AND
|
|
felhasznalo.ID = @tanarId AND
|
|
((foglalkozas.ID = @foglalkozasId) OR (@foglalkozasId IS NULL))
|
|
FOR JSON PATH
|
|
|
|
END |