144 lines
No EOL
3.9 KiB
Transact-SQL
144 lines
No EOL
3.9 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspUpdateTanuloEpJuttatas
|
|
GO
|
|
|
|
CREATE PROCEDURE uspUpdateTanuloEpJuttatas
|
|
@pTanevId int
|
|
,@pMinErdemjegy float
|
|
,@pJuttatasAlap int
|
|
,@pTanuloId int = NULL
|
|
,@pFelhasznaloId int
|
|
,@pKifizetesDatuma datetime = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @intezmenyId int
|
|
,@sorszam int
|
|
|
|
SELECT
|
|
@intezmenyId = C_INTEZMENYID
|
|
,@sorszam = C_SORSZAM
|
|
FROM T_TANEV_OSSZES WHERE ID = @pTanevId
|
|
|
|
CREATE TABLE #JuttatasOkok (
|
|
tanuloId int PRIMARY KEY
|
|
,JogviszonyTipus int
|
|
,MasodikSzakma char(1)
|
|
,Kifizetve datetime
|
|
,Szazalek int
|
|
,ElutasitasOka int
|
|
,Erdemjegy float
|
|
,EpjId int
|
|
)
|
|
|
|
INSERT INTO #JuttatasOkok (
|
|
tanuloId
|
|
,JogviszonyTipus
|
|
,MasodikSzakma
|
|
,Kifizetve
|
|
,ElutasitasOka
|
|
,Erdemjegy
|
|
,Szazalek
|
|
,EpjId
|
|
)
|
|
SELECT
|
|
t.ID
|
|
,ISNULL(o.C_JOGVISZONYTIPUSID, t.C_JOGVISZONYTIPUSID)
|
|
,ISNULL(o.C_ISMASODIKTOBBSZAKMA,t.C_ISMASODIKTOBBSZAKMA)
|
|
,epj.C_KIFIZETESDATUMA
|
|
,dbo.fnGetTanuloEpjElutasitasOka(
|
|
COALESCE(o.C_JOGVISZONYTIPUSID,t.C_JOGVISZONYTIPUSID,0)
|
|
,ISNULL(o.C_ISMASODIKTOBBSZAKMA,t.C_ISMASODIKTOBBSZAKMA)
|
|
,epj.C_ERDEMJEGY
|
|
,@pMinErdemjegy
|
|
,epj.C_KIFIZETESDATUMA
|
|
,epj.ID) ElutasitasOka
|
|
,epj.C_ERDEMJEGY
|
|
,CASE
|
|
WHEN epj.C_ERDEMJEGY >= @pMinErdemjegy AND epj.C_ERDEMJEGY < 3.00 THEN 133
|
|
WHEN epj.C_ERDEMJEGY >= 3.00 AND epj.C_ERDEMJEGY < 4.00 THEN 184
|
|
WHEN epj.C_ERDEMJEGY >= 4.00 AND epj.C_ERDEMJEGY < 4.50 THEN 243
|
|
WHEN epj.C_ERDEMJEGY >= 4.50 THEN 302
|
|
ELSE 0
|
|
END Szazalek
|
|
,epj.ID
|
|
FROM T_TANULO_OSSZES t
|
|
LEFT JOIN T_EPJTANULOIADAT_OSSZES epj ON epj.C_TANULOID = t.ID AND epj.C_ISAKTIV = 'T' AND epj.TOROLT = 'F'
|
|
OUTER APPLY (
|
|
SELECT TOP 1
|
|
tcs.C_TANULOID
|
|
,tta.C_ISMASODIKTOBBSZAKMA
|
|
,tcs.C_JOGVISZONYTIPUSID
|
|
FROM T_TANULOCSOPORT_OSSZES tcs
|
|
INNER JOIN T_OSZTALY_OSSZES osz ON osz.ID = tcs.C_OSZTALYCSOPORTID AND osz.TOROLT = 'F'
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = osz.ID AND ocs.TOROLT = 'F' AND ocs.C_FELADATKATEGORIAID = 7553
|
|
INNER JOIN T_TANULOTANUGYIADATOK_OSSZES tta ON tta.C_TANULOCSOPORTID = tcs.ID AND tta.TOROLT = 'F'
|
|
WHERE tcs.C_TANULOID = t.ID
|
|
AND tcs.TOROLT = 'F'
|
|
ORDER BY ISNULL(tcs.C_KILEPESDATUM,'22000101') DESC
|
|
) o
|
|
WHERE t.TOROLT = 'F'
|
|
AND t.C_ALTANEVID = @pTanevId
|
|
AND (@pTanuloId IS NULL OR t.ID = @pTanuloId)
|
|
|
|
IF @pKifizetesDatuma IS NOT NULL
|
|
BEGIN
|
|
UPDATE epj
|
|
SET C_KIFIZETESDATUMA = @pKifizetesDatuma
|
|
,LASTCHANGED = GETDATE()
|
|
,MODIFIER = @pFelhasznaloId
|
|
FROM T_EPJTANULOIADAT_OSSZES epj
|
|
WHERE epj.TOROLT = 'F'
|
|
AND epj.C_ISAKTIV = 'T'
|
|
AND (@pTanuloId IS NULL OR epj.C_TANULOID = @pTanuloId)
|
|
AND epj.C_TANEVID = @pTanevId
|
|
AND epj.C_KIFIZETESDATUMA IS NULL
|
|
END
|
|
|
|
UPDATE j
|
|
SET C_ISAKTIV = 'F'
|
|
,LASTCHANGED = GETDATE()
|
|
,MODIFIER = @pFelhasznaloId
|
|
FROM T_JUTTATAS_OSSZES j
|
|
WHERE (@pTanuloId IS NULL OR j.C_TANULOID = @pTanuloId)
|
|
AND j.C_JUTTATASTIPUSID = 8617
|
|
AND j.C_ISAKTIV = 'T'
|
|
AND j.C_TANEVID = @pTanevId
|
|
|
|
--SELECT * FROM #JuttatasOkok
|
|
|
|
INSERT INTO T_JUTTATAS (
|
|
C_ELUTASITASOKA
|
|
,C_ISAKTIV
|
|
,C_ISJOGOSULT
|
|
,C_JUTTATASTIPUSID
|
|
,C_OSSZEG
|
|
,C_TANULOID
|
|
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
)
|
|
SELECT
|
|
j.ElutasitasOka AS C_ELUTASITASOKA
|
|
,'T' AS C_ISAKTIV
|
|
,IIF(j.ElutasitasOka = 0, 'T', 'F') AS C_ISJOGOSULT
|
|
,8617 AS C_JUTTATASTIPUSID -- egyszeri pályakezdési juttatás
|
|
,IIF(j.ElutasitasOka = 0, @pJuttatasAlap*1.0/100*j.Szazalek, NULL) AS C_OSSZEG
|
|
,j.tanuloId AS C_TANULOID
|
|
,@intezmenyId AS C_INTEZMENYID
|
|
,@pTanevId AS C_TANEVID
|
|
,'F' AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,@pFelhasznaloId AS MODIFIER
|
|
,@pFelhasznaloId AS CREATOR
|
|
FROM #JuttatasOkok j
|
|
|
|
END |