239 lines
No EOL
12 KiB
Transact-SQL
239 lines
No EOL
12 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS [dev].[uspGenerateUtalasiAllomany]
|
||
GO
|
||
|
||
CREATE PROCEDURE [dev].[uspGenerateUtalasiAllomany]
|
||
@pLekerdezesDatuma datetime = NULL
|
||
,@pTanevNev nvarchar(10)
|
||
,@pGeneraltHonap int = NULL
|
||
,@pOsszevonas nvarchar(100) = NULL
|
||
,@pVisszaallitasDatuma datetime = NULL
|
||
AS
|
||
BEGIN
|
||
|
||
DECLARE @tanevId nvarchar(100)
|
||
CREATE TABLE #Osszevonasok (Id int primary key)
|
||
|
||
INSERT INTO #Osszevonasok (Id)
|
||
SELECT VALUE FROM string_split(@pOsszevonas,',')
|
||
|
||
IF @pLekerdezesDatuma IS NOT NULL BEGIN
|
||
DECLARE cur CURSOR LOCAL FOR
|
||
SELECT ia.C_TANEVID
|
||
FROM T_INTEZMENY i
|
||
INNER JOIN T_INTEZMENYADATOK ia ON i.ID = ia.C_INTEZMENYID
|
||
INNER JOIN T_TANEV tv ON tv.ID = ia.C_TANEVID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
|
||
WHERE i.TOROLT = 'F'
|
||
AND ia.C_ISSZAKKEPZOJUTTATAS = 'T'
|
||
AND i.C_ISARCHIV = 'F'
|
||
|
||
OPEN cur
|
||
FETCH NEXT FROM cur INTO @tanevId
|
||
WHILE @@FETCH_STATUS = 0 BEGIN
|
||
EXEC uspUpdateTanuloSzakkepzesiJuttatasok
|
||
@pTanevId = @tanevId
|
||
,@pMaxIgazolatlanokSzama = 6
|
||
,@pMinOsztondijAtlag = 2.0
|
||
,@pOsztondijAlap = 100000
|
||
,@pLekerdezesDatuma = @pLekerdezesDatuma
|
||
,@pTanuloId = NULL
|
||
,@pTanuloIdList = NULL
|
||
,@pFelhasznaloId = 0
|
||
,@pGeneraltHonap = @pGeneraltHonap
|
||
,@pVisszaallitasDatuma = @pVisszaallitasDatuma
|
||
,@pJuttatasTipusId = 7888
|
||
|
||
FETCH NEXT FROM cur INTO @tanevId
|
||
END
|
||
CLOSE cur
|
||
DEALLOCATE cur
|
||
END
|
||
|
||
DECLARE @sql nvarchar(max) = N'';
|
||
|
||
SET @sql = N'
|
||
SELECT
|
||
i.C_AZONOSITO as ''KRÉTA Azonosító''
|
||
,ia.C_NEV as ''Intézmény neve''
|
||
,ia.C_OMKOD as ''OM kód''
|
||
,fn.C_AZONOSITO as ''Fenntartó''
|
||
,ISNULL(fn.C_NEV, ''Ismeretlen fenntartó'') AS ''Fenntartónév''
|
||
,ISNULL(lmszf.C_NEV, ''Ismeretlen legmagasabb szintű fenntartó'') AS ''Legmagasabb szintű fenntartó''
|
||
,f.C_OKTATASIAZONOSITO as ''Oktatási azonosító''
|
||
,f.C_NYOMTATASINEV as ''Diák neve''
|
||
,f.C_SZULETESIDATUM as ''Születési dátum''
|
||
,f.C_SZULETESIHELY as ''Születési hely''
|
||
,f.C_ANYJANEVE as ''Anyja neve''
|
||
,tel.C_TELEFONSZAM as ''Telefonszám''
|
||
,em.C_EMAILCIM as ''E-mail cím''
|
||
,x.C_NEV as ''Gondviselő neve''
|
||
,x.C_TELEFONSZAM as ''Telefonszám''
|
||
,x.C_EMAILCIM as ''E-mail cím''
|
||
,x2.C_NEV as ''Gondviselő neve 2''
|
||
,x2.C_TELEFONSZAM as ''Telefonszám 2''
|
||
,x2.C_EMAILCIM as ''E-mail cím 2''
|
||
,cim.C_CIM AS ''Cím''
|
||
,CONCAT(IIF(ISDATE(CONCAT(''1900/'',
|
||
CASE WHEN evtip.C_ISOSSZEVONT = ''T''
|
||
THEN IIF(dib3.C_NAME IS NOT NULL, CONCAT(dib3.C_NAME, '' ('', dib.C_NAME, '')''), dib.C_NAME)
|
||
ELSE dib.C_NAME
|
||
END)) = 1, '''''''', ''''),
|
||
CASE WHEN evtip.C_ISOSSZEVONT = ''T''
|
||
THEN IIF(dib3.C_NAME IS NOT NULL, CONCAT(dib3.C_NAME, '' ('', dib.C_NAME, '')''), dib.C_NAME)
|
||
ELSE dib.C_NAME
|
||
END) as ''Évfolyam''
|
||
,COALESCE(CAST(tt.C_SZAMITOTTATLAG as nvarchar), '''') as ''Tanulmányi átlag (számított)''
|
||
,COALESCE(CAST(tt.C_KEZIATLAG as nvarchar), '''') as ''Tanulmányi átlag (kézi)''
|
||
,COALESCE(CAST(tt.C_KEZIATLAG as nvarchar), CAST(tt.C_SZAMITOTTATLAG as nvarchar), '''') as ''Tanulmányi átlag (alkalmazott)''
|
||
,dibAgazat.C_NAME as ''Ágazat''
|
||
,dibSzakma.C_NAME as ''Szakma''
|
||
,CONCAT(mukodHely.C_NEV, '' - '', dibFeladatTipus.C_NAME) as ''Tanuló tanulási helyszíne, feladatellátási hely''
|
||
,CONCAT(mukodHely.C_IRANYITOSZAM, '' '', mukodHely.C_VAROS, '', '', mukodHely.C_KOZTERULETNEV, '' '', mukodHely.C_KOZTERULETJELLEGENEV, '' '', mukodHely.C_HAZSZAM, '', '' + mukodHely.C_EMELET + ''. emelet'', '' '' + mukodHely.C_AJTO + ''. ajtó'') as ''Működési hely címe''
|
||
,mukodHely.C_VAROS as ''Működési hely városa''
|
||
,COALESCE(CONVERT(nvarchar, tta.C_NYILVANTARTASKEZDETE, 23), '''') as ''Jogviszony kezdete dátum''
|
||
,COALESCE(CONVERT(nvarchar, tta.C_JOGVISZONYVARHATOBEFEJEZESE, 23), CAST(tt.C_JOGVISZONYVARHATOBEFEJEZESE as nvarchar), '''') as ''Jogviszony várható befejezésének dátuma''
|
||
,COALESCE(CONVERT(nvarchar, tcs.C_KILEPESDATUM, 23), '''') as ''Jogviszony befejezésének dátuma''
|
||
,IIF(ISNULL(tta.C_ISBESZAMITASOS, tt.C_ISBESZAMITASOS) = ''T'', ''Igen'', ''Nem'') as ''Beszámításos a tanuló''
|
||
,IIF(ISNULL(tta.C_ISBESZAMITASOSFIXSZAZALEK, tt.C_ISBESZAMITASOSFIXSZAZALEK) = ''T'', ''Igen'', ''Nem'') as ''Beszámításos fix százalék''
|
||
,IIF(tt.C_ISSIKERESAGAZATIVIZSGA = ''T'', ''Igen'', ''Nem'') as ''Sikeres ágazati alapvizsgát tett''
|
||
,IIF(tt.C_ISATLAGSZAKKEPZESI = ''T'', ''Igen'', ''Nem'') as ''Átlag szakképzésből''
|
||
,IIF(ISNULL(tta.C_TANULOSZERZODESES,tt.C_TANULOSZERZODESES) = ''T'', ''Igen'', ''Nem'') as ''Tanulói szerződéses''
|
||
,IIF(ISNULL(tta.C_EGYUTTMUKODESES,tt.C_EGYUTTMUKODESES) = ''T'', ''Igen'', ''Nem'') as ''Együttműködési megállapodásos''
|
||
,IIF(ISNULL(tta.C_ISSZAKKEPZESIMUNKASZERZODESS,tt.C_ISSZAKKEPZESIMUNKASZERZODESS) = ''T'', ''Igen'', ''Nem'') as ''Szakképzési munkaszerződéssel''
|
||
,IIF(ocs.C_VEGZOSEVFOLYAM = ''T'', ''Igen'', ''Nem'') as ''Végzős''
|
||
,j.C_OSSZEG as ''Ösztöndíj összege''
|
||
,'''''''' + tt.C_BANKSZAMLASZAM as ''Számlaszáma''
|
||
,tt.C_BANKSZAMLATULAJNEVE as ''Számla tulajdonosának neve''
|
||
,dib2.c_name as ''Számla típusa''
|
||
FROM t_intezmeny i
|
||
INNER JOIN T_TANEV t on t.C_INTEZMENYID = i.id and t.C_AKTIV = ''T'' and t.C_NEV = '''+@pTanevNev+''' and t.TOROLT = ''F''
|
||
INNER JOIN T_INTEZMENYADATOK ia on ia.C_TANEVID = t.ID and ia.TOROLT = ''F''';
|
||
|
||
IF (@pOsszevonas is not null)
|
||
SET @sql += N'
|
||
INNER JOIN (
|
||
SELECT
|
||
SUM(IIF(bj.C_ISJOGOSULT = ''T'', bj.C_OSSZEG, 0)) AS C_OSSZEG
|
||
,bj.C_TANULOID AS C_TANULOID
|
||
,IIF(AVG(IIF(bj.C_ISJOGOSULT = ''T'', 1.0, 0.0)) = 0, ''F'', ''T'') AS C_ISJOGOSULT
|
||
,bj.C_TANEVId AS C_TANEVId
|
||
FROM T_JUTTATAS bj
|
||
JOIN #Osszevonasok ov ON ov.Id = bj.C_GENERALTHONAP
|
||
WHERE bj.TOROLT = ''F''
|
||
AND bj.C_JUTTATASTIPUSID = 7888
|
||
GROUP BY bj.C_TANULOID, bj.C_TANEVId
|
||
) j ON j.C_TANEVId = t.id
|
||
';
|
||
ELSE
|
||
SET @sql += N'
|
||
INNER JOIN T_JUTTATAS j on j.C_TANEVId = t.id and j.C_ISAKTIV = ''T'' and j.TOROLT = ''F'' and j.C_JUTTATASTIPUSID = 7888
|
||
';
|
||
|
||
SET @sql += N'
|
||
INNER JOIN T_FELHASZNALO f on f.id = j.C_TANULOID and f.torolt =''F''
|
||
LEFT JOIN T_TANULOCSOPORT tcs on tcs.C_TANULOID = f.id and tcs.torolt =''F'' and tcs.C_TANEVID = t.ID
|
||
LEFT JOIN T_FENNTARTO fn ON fn.TOROLT = ''F'' AND fn.ID = i.C_FENNTARTOID
|
||
LEFT JOIN T_LEGMAGASABBSZINTUFENNTARTO lmszf ON lmszf.TOROLT = ''F'' AND fn.C_LEGMAGASABBSZINTUFENNTARTOID = lmszf.Id
|
||
LEFT JOIN (T_TANULOCSOPORT tcs2
|
||
INNER JOIN T_OSZTALYCSOPORT ocs2 on ocs2.id = tcs2.C_OSZTALYCSOPORTID and ocs2.torolt = ''F'' and ocs2.C_FELADATKATEGORIAID = 7553
|
||
INNER JOIN T_OSZTALY o2 ON tcs2.C_OSZTALYCSOPORTID = o2.ID
|
||
) on tcs2.C_TANULOID = f.ID
|
||
AND (tcs.C_KILEPESDATUM < tcs2.C_KILEPESDATUM OR (tcs.C_KILEPESDATUM IS NOT NULL AND tcs2.C_KILEPESDATUM IS NULL))
|
||
AND tcs2.TOROLT = ''F'' AND tcs2.C_TANEVID = t.ID
|
||
INNER JOIN t_osztaly o on o.id = tcs.C_OSZTALYCSOPORTID and o.torolt = ''F'' and o.C_ALTANEVID = t.ID
|
||
INNER JOIN T_OSZTALYCSOPORT ocs on ocs.id = o.id and ocs.C_TANEVID = t.id and ocs.torolt = ''F''
|
||
AND ocs.C_FELADATKATEGORIAID = 7553
|
||
INNER JOIN T_DICTIONARYITEMBASE dib on dib.id = ocs.C_EVFOLYAMTIPUSA and dib.C_TANEVID = t.id
|
||
INNER JOIN t_tanulo tt on tt.id = f.id and tt.torolt = ''F'' and tt.C_ALTANEVID = t.ID AND tt.C_ISOSZTONDIJBOLKIZARVA = ''F''
|
||
LEFT JOIN T_DICTIONARYITEMBASE dib2 on dib2.id = tt.C_BANKSZAMLATULAJDONOSTIPUSID and dib2.C_TANEVID = t.id
|
||
LEFT JOIN T_TANULOTANUGYIADATOK tta ON tta.C_TANULOCSOPORTID = tcs.ID and tta.TOROLT = ''F''
|
||
LEFT JOIN T_DICTIONARYITEMBASE dibAgazat on dibAgazat.id = tta.C_AGAZATUJSZKTTIPUSID and dibAgazat.C_TANEVID = t.id and dibAgazat.TOROLT = ''F''
|
||
LEFT JOIN T_DICTIONARYITEMBASE dibSzakma on dibSzakma.id = tta.C_SZAKMATIPUSID and dibSzakma.C_TANEVID = t.id and dibSzakma.TOROLT = ''F''
|
||
INNER JOIN T_FELADATELLATASIHELY felhely on felhely.ID = ocs.C_FELADATELLATASIHELYID and felhely.TOROLT = ''F''
|
||
INNER JOIN T_MUKODESIHELY_OSSZES mukodHely ON mukodHely.ID = felhely.C_MUKODESIHELYID and mukodHely.TOROLT = ''F''
|
||
INNER JOIN T_DICTIONARYITEMBASE dibFeladatTipus on dibFeladatTipus.id = felhely.C_OKTATASINEVELESIFELADATTIPUS and dibFeladatTipus.C_TANEVID = t.id and dibFeladatTipus.TOROLT = ''F''
|
||
LEFT JOIN T_DICTIONARYITEMBASE dib3 on dib3.id = tta.C_TANULOEVFOLYAMTIPUSID and dib3.C_TANEVID = t.id and dib3.TOROLT = ''F''
|
||
INNER JOIN T_EVFOLYAMTIPUS_OSSZES evtip ON evtip.ID = ocs.C_EVFOLYAMTIPUSA AND evtip.TOROLT = ''F'' AND evtip.C_ALTANEVID = ocs.C_TANEVID
|
||
';
|
||
|
||
SET @sql += N'
|
||
OUTER APPLY (
|
||
SELECT TOP(1) C_TELEFONSZAM
|
||
FROM T_TELEFON x
|
||
WHERE x.C_FELHASZNALOID = tt.ID
|
||
and x.C_ALAPERTELMEZETT = ''T''
|
||
and x.TOROLT = ''F''
|
||
and x.C_GONDVISELOID is null) tel
|
||
OUTER APPLY (
|
||
SELECT TOP(1) C_EMAILCIM
|
||
FROM T_EMAIL x
|
||
WHERE x.C_FELHASZNALOID = tt.ID
|
||
and x.C_ALAPERTELMEZETT = ''T''
|
||
and x.TOROLT = ''F''
|
||
and x.C_GONDVISELOID is null) em
|
||
OUTER APPLY (
|
||
SELECT TOP(1) * FROM T_GONDVISELO g
|
||
OUTER APPLY (
|
||
SELECT TOP(1) C_EMAILCIM
|
||
FROM T_EMAIL x
|
||
WHERE x.C_FELHASZNALOID = tt.ID
|
||
and x.C_ALAPERTELMEZETT = ''T''
|
||
and x.TOROLT = ''F''
|
||
and x.C_GONDVISELOID = g.ID
|
||
and x.C_TANEVID = t.ID) em2
|
||
OUTER APPLY (
|
||
SELECT TOP(1) C_TELEFONSZAM
|
||
FROM T_TELEFON x
|
||
WHERE x.C_FELHASZNALOID = tt.ID
|
||
and x.C_ALAPERTELMEZETT = ''T''
|
||
and x.TOROLT = ''F''
|
||
and x.C_GONDVISELOID = g.ID
|
||
and x.C_TANEVID = t.ID) tel2
|
||
WHERE g.TOROLT = ''F''
|
||
AND g.C_TANULOID = tt.ID and g.C_TANEVID = t.ID
|
||
ORDER BY g.CREATED DESC ) x';
|
||
|
||
SET @sql += N'
|
||
OUTER APPLY (
|
||
SELECT TOP(1) *FROM T_GONDVISELO g
|
||
OUTER APPLY (
|
||
SELECT TOP(1) C_EMAILCIM
|
||
FROM T_EMAIL x
|
||
WHERE x.C_FELHASZNALOID = tt.ID
|
||
and x.C_ALAPERTELMEZETT = ''T''
|
||
and x.TOROLT = ''F''
|
||
and x.C_GONDVISELOID = g.ID
|
||
and x.C_TANEVID = t.ID) em2
|
||
OUTER APPLY (
|
||
SELECT TOP(1) C_TELEFONSZAM
|
||
FROM T_TELEFON x
|
||
WHERE x.C_FELHASZNALOID = tt.ID
|
||
and x.C_ALAPERTELMEZETT = ''T''
|
||
and x.TOROLT = ''F''
|
||
and x.C_GONDVISELOID = g.ID
|
||
and x.C_TANEVID = t.ID) tel2
|
||
WHERE g.TOROLT = ''F''
|
||
AND g.C_TANULOID = tt.ID and g.C_TANEVID = t.ID
|
||
ORDER BY g.CREATED DESC
|
||
) x2
|
||
OUTER APPLY (
|
||
SELECT TOP(1) CONCAT(x.C_IRANYITOSZAM, '' '', x.C_VAROS, '', '', x.C_KOZTERULET, '' '', x.C_KOZTERULETJELLEGENEV, '' '', x.C_HAZSZAM, '', '' + x.C_EMELET + ''. emelet'', '' '' + x.C_AJTO + ''. ajtó'') AS C_CIM
|
||
FROM T_CIM x
|
||
WHERE x.C_FELHASZNALOID = tt.ID
|
||
and x.C_ALAPERTELMEZETT = ''T''
|
||
and x.TOROLT = ''F''
|
||
and x.C_GONDVISELOID IS NULL
|
||
and x.C_TANEVID = t.ID
|
||
ORDER BY x.C_ALAPERTELMEZETT DESC) cim
|
||
WHERE i.torolt = ''F''
|
||
AND fn.C_ISTESZTFENNTARTO = ''F''
|
||
AND j.C_ISJOGOSULT = ''T''
|
||
AND tcs2.ID IS NULL
|
||
AND ia.C_ISSZAKKEPZOJUTTATAS = ''T''
|
||
AND i.C_ISARCHIV = ''F''
|
||
|
||
ORDER BY i.C_AZONOSITO, f.C_NYOMTATASINEV';
|
||
|
||
exec sp_executesql @sql
|
||
|
||
END
|
||
GO |