78 lines
3.2 KiB
Transact-SQL
78 lines
3.2 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetAdminDashboardNaplo]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetAdminDashboardNaplo]
|
|
END
|
|
GO
|
|
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetAdminDashboardNaplo]
|
|
@pTanevId INT
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
|
|
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 IIF(LEFT(REPLACE(JSON_VALUE(C_ERTEK, '$.Date'),'-',''), 8) > GETDATE(), 1, 0)
|
|
FROM T_RENDSZERBEALLITAS_OSSZES rb
|
|
WHERE C_BEALLITASTIPUS = 3082
|
|
AND rb.C_TANEVID = @pTanevId
|
|
AND rb.TOROLT = 'F'
|
|
) AS ZarasiGond
|
|
,(SELECT COUNT(1)
|
|
FROM T_ALKALMAZOTT adat
|
|
WHERE NOT EXISTS (SELECT 1 FROM T_FELHASZNALOBELEPES fb WHERE adat.ID = fb.C_FELHASZNALOID AND fb.TOROLT = 'F')
|
|
AND adat.C_ALTANEVID = @pTanevId
|
|
AND adat.TOROLT = 'F'
|
|
) AS NincsBelepesAlkalmazott
|
|
,(SELECT COUNT(1)
|
|
FROM T_TANULO adat
|
|
WHERE NOT EXISTS (SELECT 1 FROM T_FELHASZNALOBELEPES fb WHERE adat.ID = fb.C_FELHASZNALOID AND fb.TOROLT = 'F' AND fb.C_GONDVISELOID IS NULL)
|
|
AND adat.C_ALTANEVID = @pTanevId
|
|
AND adat.TOROLT = 'F'
|
|
) AS NincsBelepesTanulo
|
|
,(SELECT COUNT(1)
|
|
FROM T_GONDVISELO adat
|
|
WHERE NOT EXISTS (SELECT 1 FROM T_FELHASZNALOBELEPES fb WHERE adat.ID = fb.C_GONDVISELOID AND fb.TOROLT = 'F')
|
|
AND adat.C_TANEVID = @pTanevId
|
|
AND adat.TOROLT = 'F'
|
|
) AS NincsBelepesGondviselo
|
|
,(SELECT COUNT(1)
|
|
FROM T_ALKALMAZOTT_OSSZES adat
|
|
LEFT JOIN T_FELHASZNALOBELEPES_OSSZES fb ON fb.C_FELHASZNALOID = adat.ID AND fb.TOROLT = 'F'
|
|
INNER JOIN T_MUNKAUGYIADATOK_OSSZES m ON m.C_ALKALMAZOTTID = adat.ID AND m.C_TANEVID = adat.C_ALTANEVID AND m.TOROLT = 'F'
|
|
WHERE adat.TOROLT = 'F'
|
|
AND adat.C_ALTANEVID = @pTanevId
|
|
AND (fb.C_UTOLSOBELEPES <= DATEADD(DAY,-28,GETDATE()) OR fb.C_UTOLSOBELEPES IS NULL)
|
|
AND m.C_TARTOSHELYETTESITES = 'F'
|
|
AND (m.C_ALKALMAZASMEGSZUNESE IS NULL or m.C_ALKALMAZASMEGSZUNESE >= GETDATE())
|
|
) AS NemLepettBeHonapAlkalmazott
|
|
,(SELECT COUNT(1)
|
|
FROM T_ALKALMAZOTT_OSSZES adat
|
|
LEFT JOIN T_FELHASZNALOBELEPES_OSSZES fb ON fb.C_FELHASZNALOID = adat.ID AND fb.TOROLT = 'F'
|
|
INNER JOIN T_MUNKAUGYIADATOK_OSSZES m ON m.C_ALKALMAZOTTID = adat.ID AND m.C_TANEVID = adat.C_ALTANEVID AND m.TOROLT = 'F'
|
|
WHERE adat.TOROLT = 'F'
|
|
AND adat.C_ALTANEVID = @pTanevId
|
|
AND (fb.C_UTOLSOBELEPES <= DATEADD(DAY,-14,GETDATE()) OR fb.C_UTOLSOBELEPES IS NULL)
|
|
AND m.C_TARTOSHELYETTESITES = 'F'
|
|
AND (m.C_ALKALMAZASMEGSZUNESE IS NULL or m.C_ALKALMAZASMEGSZUNESE >= GETDATE())
|
|
) AS NemLepettBeKetHetAlkalmazott
|
|
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
|
|
|