51 lines
2.4 KiB
Transact-SQL
51 lines
2.4 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS [dbo].[sp_GetAdminDashboardStatisztika]
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetAdminDashboardStatisztika]
|
|
@pTanevId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
ia.C_NEV AS IntezmenyNev
|
|
,i.C_AZONOSITO AS IntezmenyAzonosito
|
|
,i.C_ALTERNATIVAZONOSITO AS IntezmenyAlternativAzonosito
|
|
,tv.C_NEV AS TanevNev
|
|
,(SELECT C_NEV
|
|
FROM T_TANEV_OSSZES adat
|
|
WHERE adat.C_INTEZMENYID = i.ID AND adat.TOROLT = 'F' AND adat.C_AKTIV = 'T'
|
|
) AS AktivTanevNeve
|
|
,(SELECT COUNT(1)
|
|
FROM T_ALKALMAZOTT_OSSZES alkalmazott
|
|
inner join T_MUNKAUGYIADATOK munkaugyiadat ON munkaugyiadat.C_ALKALMAZOTTID = alkalmazott.ID
|
|
AND ISNULL(munkaugyiadat.C_ALKALMAZASKEZDETE,getdate()) <= getdate()
|
|
AND ISNULL(munkaugyiadat.C_ALKALMAZASMEGSZUNESE,getdate()) >= getdate() AND munkaugyiadat.TOROLT = 'F'
|
|
AND munkaugyiadat.C_TARTOSHELYETTESITES = 'F'
|
|
WHERE alkalmazott.TOROLT = 'F' AND C_ALTANEVID = @pTanevId
|
|
) AS AlkalmazottakSzama
|
|
,(SELECT COUNT(DISTINCT adat.ID)
|
|
FROM T_TANULO_OSSZES adat
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID = adat.ID AND tcs.TOROLT = 'F'
|
|
AND tcs.C_BELEPESDATUM <= GETDATE()
|
|
AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > GETDATE())
|
|
INNER JOIN T_OSZTALY_OSSZES o ON o.ID = tcs.C_OSZTALYCSOPORTID AND o.TOROLT = 'F'
|
|
WHERE adat.C_ALTANEVID = @pTanevId
|
|
AND adat.TOROLT = 'F'
|
|
) AS AktivTanulokSzama
|
|
,(SELECT COUNT(1) FROM T_TANULO_OSSZES adat WHERE adat.C_ALTANEVID = @pTanevId AND adat.TOROLT = 'F') AS TanulokSzama
|
|
,(SELECT COUNT(1) FROM T_TEREM_OSSZES adat WHERE adat.C_TANEVID = @pTanevId AND adat.TOROLT = 'F') AS TermekSzama
|
|
,(SELECT COUNT(1) FROM T_ESZKOZ_OSSZES adat WHERE adat.C_TANEVID = @pTanevId AND adat.TOROLT = 'F') AS EszkozokSzama
|
|
,(SELECT SUM(C_ORASZAM) FROM T_FOGLALKOZAS_OSSZES adat WHERE adat.C_TANEVID = @pTanevId AND adat.TOROLT = 'F') AS TtfOrakSzama
|
|
,(SELECT COUNT(1) FROM T_OSZTALY_OSSZES adat WHERE adat.C_ALTANEVID = @pTanevId AND adat.TOROLT = 'F') AS OsztalyokSzama
|
|
,(SELECT COUNT(1) FROM T_CSOPORT_OSSZES adat WHERE adat.C_ALTANEVID = @pTanevId AND adat.TOROLT = 'F') AS CsoportokSzama
|
|
FROM T_TANEV_OSSZES tv
|
|
INNER JOIN T_INTEZMENY_OSSZES i ON i.ID = tv.C_INTEZMENYID AND i.TOROLT = 'F'
|
|
INNER JOIN T_INTEZMENYADATOK_OSSZES ia ON ia.C_INTEZMENYID = tv.C_INTEZMENYID AND ia.C_TANEVID = tv.ID AND ia.TOROLT = 'F'
|
|
WHERE tv.TOROLT = 'F'
|
|
AND tv.ID = @pTanevId
|
|
|
|
END
|
|
GO
|
|
|