88 lines
No EOL
5.5 KiB
Transact-SQL
88 lines
No EOL
5.5 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS dbo.uspSzirStatKoli
|
||
GO
|
||
|
||
CREATE PROCEDURE dbo.uspSzirStatKoli
|
||
@pTanevId int
|
||
,@pIntezmenyid int
|
||
AS BEGIN
|
||
|
||
DECLARE @datum datetime, @IsSniKezelo bit
|
||
|
||
SELECT @datum = LEFT(tv.C_NEV, 4) + '1001' FROM T_TANEV tv WHERE ID = @pTanevId
|
||
SET @IsSniKezelo = dbo.fnGetRendszerbeallitasEnumBool(1666, @pIntezmenyid, @pTanevId)
|
||
|
||
SELECT
|
||
fh.ID AS TanuloId
|
||
,CONCAT(fh.C_NYOMTATASINEV, ' (', fh.C_OKTATASIAZONOSITO ,')') AS TanuloOktatasiAzonosito --A
|
||
,ia.C_OMKOD AS IntezmenyOmAzonosito --B
|
||
,ia.C_NEV AS IntezmenyNev --C
|
||
,ia.C_VAROS AS IntezenyTelepules --D
|
||
,ia.C_IRANYITOSZAM AS IntezmenyIrszam --E
|
||
,RTRIM(CONCAT(ia.C_KOZTERULETNEV, ' ', ia.C_KOZTERULETJELLEGENEV,' ', ia.C_HAZSZAM, ' ', ia.C_EMELET, ' ', ia.C_AJTO)) AS IntezmenyCim --F
|
||
,ISNULL(NULLIF(ia.C_EMAILCIM, ''), '-') AS IntezmenyEmailcim --G
|
||
,CONCAT(ia.C_OMKOD, '/', fhely.C_FELADATELLATASIHELYKOD) AS FelhelyAzonosito --H
|
||
,mhely.C_NEV AS FelhelyNev --I
|
||
,mhely.C_VAROS AS FelhelyTelepules --J
|
||
,mhely.C_IRANYITOSZAM AS FelhelyIrszam --K
|
||
,RTRIM(CONCAT(mhely.C_KOZTERULETNEV, ' ', mhely.C_KOZTERULETJELLEGENEV,' ', mhely.C_HAZSZAM, ' ', mhely.C_EMELET, ' ', mhely.C_AJTO)) AS FelhelyCim --L
|
||
,ISNULL(NULLIF(mhely.C_EMAILCIM, ''), '-') AS FelhelyEmailcim --M
|
||
,ISNULL(fhelytip.C_NAME, '-') AS FelhelyTipus --N
|
||
,ISNULL(tanulmanyok.FeladatKategoria, '-') AS Tanulmanyok --O
|
||
,DATEDIFF(YEAR, fh.C_SZULETESIDATUM, @datum) AS TanuloKorev --P
|
||
,ISNULL(nem.C_NAME, '-') AS TanuloNem --Q
|
||
,IIF(@IsSniKezelo = 1, IIF(dk.C_SAJATOSNEVELESU = 'T', 'Igen', 'Nem'), '-') AS TanuloSniZavar --R
|
||
,IIF(@IsSniKezelo = 1 AND snizavar.ID IS NOT NULL, snizavar.C_NAME, '-') AS FogyatekossagTipus --S
|
||
,IIF(dk.C_ETKEZESIKEDVEZMENY BETWEEN 1108 AND 1119, 'Igen', 'Nem') AS EtkeztetesbenReszesul --T
|
||
,IIF(dk.C_ETKEZESIKEDVEZMENY IN (1110, 1113, 1116, 1119), 'Igen', 'Nem') AS TeritesNelkulEtkezik --U
|
||
,IIF(dk.C_ETKEZESIKEDVEZMENY IN (1109, 1112, 1115, 1118), 'Igen', 'Nem') AS KedvezmenyesenEtkezik --V
|
||
,IIF(dk.C_HATRANYOSHELYZETU IS NULL OR dk.C_HATRANYOSHELYZETU IN (1120, 1121), 'Nem', 'Igen') AS HatranyosHelyzetu --W
|
||
,IIF(dk.C_HATRANYOSHELYZETU IS NULL OR dk.C_HATRANYOSHELYZETU IN (1120, 1121, 1122), 'Nem', 'Igen') AS HalmozottanHatranyos --X
|
||
,IIF(dk.C_RENDSZERESGYERMEKVEDELMIKEDV = 'T', 'Igen', 'Nem') AS RendszeresGyermekvedelmi --Y
|
||
,IIF(dk.C_ISOTTHONIELLATAS = 'T', 'Igen', 'Nem') AS OtthoniEllatas -- Z
|
||
FROM T_FELHASZNALO fh
|
||
INNER JOIN T_TANULO dk ON dk.ID = fh.ID AND dk.TOROLT = 'F'
|
||
INNER JOIN T_INTEZMENYADATOK ia ON ia.C_TANEVID = fh.C_TANEVID AND ia.TOROLT = 'F'
|
||
INNER JOIN T_INTEZMENY i ON ia.C_INTEZMENYID = i.ID AND i.TOROLT = 'F'
|
||
INNER JOIN T_FELADATELLATASIHELY fhely ON fhely.C_TANEVID = ia.C_TANEVID AND fhely.TOROLT = 'F'
|
||
INNER JOIN T_MUKODESIHELY mhely ON mhely.ID = fhely.C_MUKODESIHELYID AND mhely.TOROLT = 'F'
|
||
INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_TANULOID = dk.ID AND tcs.TOROLT = 'F'
|
||
INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F' AND ocs.C_FELADATELLATASIHELYID = fhely.ID
|
||
INNER JOIN T_OSZTALY o ON o.ID = ocs.ID AND o.TOROLT = 'F'
|
||
LEFT JOIN T_TANULOSNI sni ON sni.C_TANULOID = dk.ID AND sni.TOROLT = 'F'
|
||
LEFT JOIN T_DICTIONARYITEMBASE fhelytip ON fhelytip.ID = fhely.C_OKTATASINEVELESIFELADATTIPUS AND fhelytip.C_TANEVID = fhely.C_TANEVID AND fhelytip.C_INTEZMENYID = fhely.C_INTEZMENYID AND fhelytip.TOROLT = 'F'
|
||
LEFT JOIN T_DICTIONARYITEMBASE nem ON fh.C_NEME = nem.ID AND nem.C_TANEVID = fh.C_TANEVID AND nem.C_INTEZMENYID = fh.C_INTEZMENYID AND nem.TOROLT = 'F'
|
||
LEFT JOIN T_DICTIONARYITEMBASE snizavar ON sni.C_FOGYATEKOSSAGTIPUSID = snizavar.ID AND snizavar.C_TANEVID = sni.C_TANEVID AND snizavar.C_INTEZMENYID = sni.C_INTEZMENYID AND snizavar.TOROLT = 'F'
|
||
OUTER APPLY (
|
||
SELECT TOP 1 *
|
||
FROM (
|
||
SELECT
|
||
d.C_NAME AS FeladatKategoria,
|
||
tcs.C_BELEPESDATUM,
|
||
tcs.ID,
|
||
CASE
|
||
WHEN COUNT(tcs.ID) OVER(PARTITION BY tcs.C_TANULOID) = 1 THEN 1
|
||
WHEN tcs.C_BELEPESDATUM <= CAST(LEFT(v.C_NEV, 4) + '-10-01' AS DATE) AND ISNULL(tcs.C_KILEPESDATUM, '2999-01-01') > CAST(LEFT(v.C_NEV, 4) + '-10-01' AS DATE) THEN 2
|
||
ELSE 3
|
||
END AS row_order
|
||
FROM T_FELHASZNALO f
|
||
INNER JOIN T_TANEV v ON v.ID = f.C_TANEVID AND v.TOROLT = 'F'
|
||
INNER JOIN T_TANULO t ON t.ID = f.ID AND t.TOROLT = 'F'
|
||
INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_TANULOID = f.ID AND tcs.TOROLT = 'F'
|
||
INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID and ocs.TOROLT = 'F'
|
||
INNER JOIN T_TANULOTANUGYIADATOK tta ON tta.C_TANULOCSOPORTID = tcs.ID AND tta.TOROLT = 'F'
|
||
INNER JOIN T_FELADATELLATASIHELY fh on fh.ID = ocs.C_FELADATELLATASIHELYID and fh.TOROLT = 'F'
|
||
INNER JOIN T_DICTIONARYITEMBASE d ON d.ID = fh.C_OKTATASINEVELESIFELADATTIPUS and d.C_TANEVID = fh.C_TANEVID and d.TOROLT = 'F'
|
||
WHERE f.TOROLT = 'F'
|
||
AND f.ELOZOTANEVIREKORDID = fh.ID
|
||
AND ocs.C_FELADATKATEGORIAID = 7553 -- Oktatás
|
||
) sz
|
||
ORDER BY sz.row_order, sz.C_BELEPESDATUM DESC, sz.ID DESC
|
||
) tanulmanyok
|
||
WHERE fh.TOROLT = 'F'
|
||
AND tcs.C_BELEPESDATUM <= @datum
|
||
AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > @datum)
|
||
AND ocs.C_FELADATKATEGORIAID = 7556 -- Kollégium
|
||
AND fh.C_TANEVID = @pTanevId
|
||
AND fh.C_INTEZMENYID = @pIntezmenyid
|
||
END
|
||
GO |