51 lines
2.9 KiB
Transact-SQL
51 lines
2.9 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetAdminDashboardStatisztika]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetAdminDashboardStatisztika]
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetAdminDashboardStatisztika]
|
|
|
|
@pTanevId INT
|
|
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
intezmenyAdatok.C_NEV IntezmenyNev,
|
|
intezmeny.C_AZONOSITO IntezmenyAzonosito,
|
|
intezmeny.C_ALTERNATIVAZONOSITO IntezmenyAlternativAzonosito,
|
|
tanev.C_NEV TanevNev,
|
|
(SELECT C_NEV FROM T_TANEV_OSSZES adat
|
|
WHERE adat.C_INTEZMENYID = intezmeny.ID AND adat.TOROLT = 'F' AND adat.C_AKTIV = 'T') AktivTanevNeve,
|
|
(SELECT COUNT(1) FROM T_ALKALMAZOTT_OSSZES adat WHERE adat.C_ALTANEVID = @pTanevId AND adat.TOROLT = 'F') AlkalmazottakSzama,
|
|
(SELECT COUNT(1) 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') AktivTanulokSzama,
|
|
(SELECT COUNT(1) FROM T_TANULO_OSSZES adat WHERE adat.C_ALTANEVID = @pTanevId AND adat.TOROLT = 'F') TanulokSzama,
|
|
(SELECT COUNT(1) FROM T_TEREM_OSSZES adat WHERE adat.C_TANEVID = @pTanevId AND adat.TOROLT = 'F') TermekSzama,
|
|
(SELECT COUNT(1) FROM T_ESZKOZ_OSSZES adat WHERE adat.C_TANEVID = @pTanevId AND adat.TOROLT = 'F') EszkozokSzama,
|
|
(SELECT SUM(C_ORASZAM) FROM T_FOGLALKOZAS_OSSZES adat WHERE adat.C_TANEVID = @pTanevId AND adat.TOROLT = 'F') TtfOrakSzama,
|
|
(SELECT COUNT(1) FROM T_OSZTALY_OSSZES adat WHERE adat.C_ALTANEVID = @pTanevId AND adat.TOROLT = 'F') OsztalyokSzama,
|
|
(SELECT COUNT(1) FROM T_CSOPORT_OSSZES adat WHERE adat.C_ALTANEVID = @pTanevId AND adat.TOROLT = 'F') CsoportokSzama
|
|
FROM T_TANEV_OSSZES tanev
|
|
INNER JOIN T_INTEZMENY_OSSZES intezmeny ON intezmeny.ID = tanev.C_INTEZMENYID AND intezmeny.TOROLT = 'F'
|
|
INNER JOIN T_INTEZMENYADATOK_OSSZES intezmenyAdatok ON intezmenyAdatok.C_INTEZMENYID = tanev.C_INTEZMENYID AND intezmenyAdatok.C_TANEVID = tanev.ID AND intezmenyAdatok.TOROLT = 'F'
|
|
WHERE
|
|
tanev.TOROLT = 'F' AND tanev.ID = @pTanevId
|
|
|
|
END
|
|
GO
|
|
|