201 lines
12 KiB
Transact-SQL
201 lines
12 KiB
Transact-SQL
-- =============================================
|
|
-- Description: Lekérdezzük a részletes hiányzási adatokat egy osztályra nézve.
|
|
-- =============================================
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
DROP PROCEDURE IF EXISTS uspGetTanulokMulasztasaiKeseseiReszletes
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetTanulokMulasztasaiKeseseiReszletes
|
|
@intezmenyId INT,
|
|
@tanevId INT,
|
|
@osztalyId INT,
|
|
@elmeletGyakorlat BIT
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE
|
|
@elmeletiOraPercben INT = 45
|
|
,@gyakorlatiOraPercben INT = 45
|
|
,@osztalyNev NVARCHAR(200)
|
|
|
|
SELECT DISTINCT
|
|
@osztalyId OsztalyId
|
|
,ocs.C_NEV OsztalyNev
|
|
,o.C_OSZTALYNAPLOLEIRASA OsztalyMegjegyzes
|
|
,o.C_OSZTALYFONOKID PartnerID
|
|
,ofo.C_NYOMTATASINEV Osztalyfonok
|
|
,i.C_IGAZGATONEVE IntezmenyVezeto
|
|
,i.C_NEV IntezmenyNeve
|
|
,i.C_IRANYITOSZAM IntezmenyIranyitoszam
|
|
,i.C_VAROS IntezmenyVaros
|
|
,dbo.fnGetDokumentumIntezmenyCime(@tanevId) IntezmenyCime
|
|
,i.C_OMKOD IntezmenyOMKod
|
|
FROM T_OSZTALYCSOPORT_OSSZES ocs
|
|
INNER JOIN T_OSZTALY_OSSZES o ON ocs.ID = o.ID
|
|
LEFT JOIN T_FELHASZNALO_OSSZES ofo ON ofo.ID = o.C_OSZTALYFONOKID
|
|
INNER JOIN T_INTEZMENYADATOK_OSSZES i on i.C_INTEZMENYID=ocs.C_INTEZMENYID AND i.C_TANEVID = ocs.C_TANEVID
|
|
WHERE
|
|
o.TOROLT = 'F'
|
|
AND i.TOROLT = 'F'
|
|
AND ofo.TOROLT = 'F'
|
|
AND ocs.ID = @osztalyId
|
|
AND ocs.C_TANEVID = @tanevId
|
|
|
|
SELECT
|
|
@osztalyNev = C_NEV
|
|
FROM
|
|
T_OSZTALYCSOPORT_OSSZES ocs
|
|
WHERE
|
|
ID = @osztalyId
|
|
AND ocs.TOROLT = 'F'
|
|
AND ocs.C_TANEVID = @tanevId
|
|
|
|
DECLARE @sql NVARCHAR(MAX) = ''
|
|
|
|
SET @sql +=
|
|
'SELECT
|
|
tcs.C_TANULOID TanuloId
|
|
,C_SZULETESINEV TanuloNev
|
|
,@osztalyNev OsztalyNev
|
|
,@osztalyId OsztalyId'
|
|
|
|
IF @elmeletgyakorlat = 1
|
|
BEGIN
|
|
SET @sql +=
|
|
',SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''T'' THEN 1 ELSE 0 END) TanoraiHianyzasIgazolandoGyakorlati
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' THEN 1 ELSE 0 END) TanoraiHianyzasIgazoltGyakorlati
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' THEN 1 ELSE 0 END) TanoraiHianyzasIgazolatlanGyakorlati
|
|
,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''F'' AND Gyakorlati = ''T'' THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazolandoGyakorlati
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''F'' AND Gyakorlati = ''T'' THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazoltGyakorlati
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''F'' AND Gyakorlati = ''T'' THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazolatlanGyakorlati
|
|
|
|
,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazolandoGyakorlati
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazoltGyakorlati
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazolatlanGyakorlati
|
|
|
|
,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END) TanoraiKesesIgazolandoGyakorlatiPerc
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END) TanoraiKesesIgazoltGyakorlatiPerc
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END) TanoraiKesesIgazolatlanGyakorlatiPerc
|
|
,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben SzamitottKesesIgazolandoGyakorlati
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben SzamitottKesesIgazoltGyakorlati
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben SzamitottKesesIgazolatlanGyakorlati'
|
|
|
|
SET @sql +=
|
|
',SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''F'' THEN 1 ELSE 0 END) TanoraiHianyzasIgazolandoElmeleti
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' THEN 1 ELSE 0 END) TanoraiHianyzasIgazoltElmeleti
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' THEN 1 ELSE 0 END) TanoraiHianyzasIgazolatlanElmeleti
|
|
,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''F'' AND Gyakorlati = ''F'' THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazolandoElmeleti
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''F'' AND Gyakorlati = ''F'' THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazoltElmeleti
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''F'' AND Gyakorlati = ''F'' THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazolatlanElmeleti
|
|
|
|
,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazolandoElmeleti
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazoltElmeleti
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazolatlanElmeleti
|
|
|
|
,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END) TanoraiKesesIgazolandoElmeletiPerc
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END) TanoraiKesesIgazoltElmeletiPerc
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END) TanoraiKesesIgazolatlanElmeletiPerc
|
|
,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben SzamitottKesesIgazolandoElmeleti
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben SzamitottKesesIgazoltElmeleti
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben SzamitottKesesIgazolatlanElmeleti'
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @sql +=
|
|
',SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' THEN 1 ELSE 0 END) TanoraiHianyzasIgazolando
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' THEN 1 ELSE 0 END) + ISNULL(h.[hozottIgazoltHianyzas], 0) TanoraiHianyzasIgazolt
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' THEN 1 ELSE 0 END) + ISNULL(h.[hozottIgazolatlanHianyzas], 0) TanoraiHianyzasIgazolatlan
|
|
,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''F'' THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazolando
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''F'' THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazolt
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''F'' THEN 1 ELSE 0 END) TanoranKivuliHianyzasIgazolatlan
|
|
|
|
,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazolando
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazolt
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN 1 ELSE 0 END) TanoraiKesesDarabszamIgazolatlan
|
|
|
|
,SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END) TanoraiKesesIgazolandoPerc
|
|
,SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END) TanoraiKesesIgazoltPerc
|
|
,SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END) TanoraiKesesIgazolatlanPerc
|
|
|
|
,(SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben)
|
|
+ (SUM(CASE WHEN Igazolt IS NULL AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben) SzamitottKesesIgazolando
|
|
,(SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben)
|
|
+ (SUM(CASE WHEN Igazolt =''T'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben) SzamitottKesesIgazolt
|
|
,(SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''T'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@gyakorlatiOraPercben)
|
|
+ SUM(CASE WHEN Igazolt =''F'' AND Tanorai = ''T'' AND Gyakorlati = ''F'' AND KesesPercben IS NOT NULL THEN KesesPercben ELSE 0 END)/@elmeletiOraPercben SzamitottKesesIgazolatlan
|
|
'
|
|
END
|
|
|
|
SET @sql +=
|
|
',ISNULL(h.[hozottIgazolatlanHianyzas], 0) HozottIgazolatlanHianyzas
|
|
,ISNULL(h.[hozottIgazolatlanKesesPerc], 0) HozottIgazolatlanKesesPerc
|
|
,ISNULL(h.[hozottIgazoltHianyzas], 0) HozottIgazoltHianyzas
|
|
,ISNULL(h.[hozottIgazoltHianyzasPerc], 0) HozottIgazoltHianyzasPerc
|
|
|
|
,SUM(CASE IgazolasTipusa WHEN 1530 THEN 1 ELSE 0 END) OrvosiIgazolas
|
|
,SUM(CASE IgazolasTipusa WHEN 1531 THEN 1 ELSE 0 END) SzuloiIgazolas
|
|
,SUM(CASE IgazolasTipusa WHEN 1532 THEN 1 ELSE 0 END) HivatalosTavolletIgazolas
|
|
,SUM(CASE IgazolasTipusa WHEN 1533 THEN 1 ELSE 0 END) IskolaerdekuTavolletIgazolas
|
|
,SUM(CASE IgazolasTipusa WHEN 6494 THEN 1 ELSE 0 END) SzolgaltatoiIgazolas
|
|
,SUM(CASE IgazolasTipusa WHEN 6495 THEN 1 ELSE 0 END) IskolaiEngedelyIgazolas
|
|
,SUM(CASE IgazolasTipusa WHEN 6967 THEN 1 ELSE 0 END) PalyavalasztasiCeluIgazolas
|
|
,SUM(CASE IgazolasTipusa WHEN 6834 THEN 1 ELSE 0 END) KikeroIgazolas
|
|
,SUM(CASE IgazolasTipusa WHEN 6853 THEN 1 ELSE 0 END) TappenzIgazolas
|
|
,SUM(CASE IgazolasTipusa WHEN 1529 THEN 1 ELSE 0 END) EgyebIgazolas
|
|
|
|
FROM dbo.fnGetDokumentumMulasztasokOsztalyonkentReszletes(@tanevId, @osztalyId, @intezmenyId, DEFAULT, 0, DEFAULT, DEFAULT, 0) TanulokMulasztasai
|
|
INNER JOIN T_TANTARGY_OSSZES Tantargy ON Tantargy.Id = TanulokMulasztasai.TantargyId
|
|
LEFT JOIN (
|
|
SELECT
|
|
C_TANULOID tanuloId
|
|
,[C_HOZOTTIGAZOLATLANHIANYZAS] hozottIgazolatlanHianyzas
|
|
,[C_HOZOTTIGAZOLATLANKESESPERCBE] hozottIgazolatlanKesesPerc
|
|
,[C_HOZOTTIGAZOLTHIANYZAS] hozottIgazoltHianyzas
|
|
,[C_HOZOTTIGAZOLTKESESPERCBEN] hozottIgazoltHianyzasPerc
|
|
FROM
|
|
T_TANULOTANUGYIADATOK_OSSZES ta
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.ID = ta.C_TANULOCSOPORTID
|
|
WHERE
|
|
tcs.TOROLT = ''F''
|
|
AND ta.TOROLT = ''F''
|
|
AND ta.C_TANEVID = @tanevId
|
|
) h ON h.tanuloId = TanulokMulasztasai.TanuloId
|
|
RIGHT OUTER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID = TanulokMulasztasai.TanuloId
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = tcs.C_TANULOID
|
|
WHERE
|
|
tcs.C_TANEVID = @tanevId
|
|
AND tcs.C_OSZTALYCSOPORTID = @osztalyId
|
|
AND Tantargy.TOROLT = ''F''
|
|
AND tcs.TOROLT = ''F''
|
|
AND f.TOROLT = ''F''
|
|
GROUP BY
|
|
TanulokMulasztasai.TanuloId
|
|
,tcs.C_TANULOID
|
|
,C_SZULETESINEV
|
|
,ISNULL(h.[hozottIgazolatlanHianyzas], 0)
|
|
,ISNULL(h.[hozottIgazolatlanKesesPerc], 0)
|
|
,ISNULL(h.[hozottIgazoltHianyzas], 0)
|
|
,ISNULL(h.[hozottIgazoltHianyzasPerc], 0)
|
|
ORDER BY C_SZULETESINEV'
|
|
|
|
EXEC sp_executesql @sql, N'
|
|
@intezmenyId INT
|
|
,@tanevId INT
|
|
,@osztalyId INT
|
|
,@elmeletiOraPercben INT
|
|
,@gyakorlatiOraPercben INT
|
|
,@osztalyNev NVARCHAR(200)'
|
|
,@intezmenyId = @intezmenyId
|
|
,@tanevId = @tanevId
|
|
,@osztalyId = @osztalyId
|
|
,@elmeletiOraPercben = @elmeletiOraPercben
|
|
,@gyakorlatiOraPercben = @gyakorlatiOraPercben
|
|
,@osztalyNev = @osztalyNev
|
|
|
|
END
|
|
GO
|