113 lines
3.9 KiB
Transact-SQL
113 lines
3.9 KiB
Transact-SQL
IF OBJECT_ID('sp_GetRiportTanariAdatok') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_GetRiportTanariAdatok
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_GetRiportTanariAdatok
|
|
@fenntartoId int,
|
|
@intezmenyId int,
|
|
@tanevNev nvarchar(20)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON
|
|
DECLARE @intezmenyTable table (
|
|
id int primary key,
|
|
tanevId int
|
|
)
|
|
|
|
IF @fenntartoId IS NOT NULL AND @intezmenyId IS NOT NULL BEGIN
|
|
INSERT INTO @intezmenyTable (id)
|
|
SELECT i.ID FROM T_INTEZMENY i
|
|
WHERE i.C_FENNTARTOAZONOSITO = @fenntartoId
|
|
AND i.ID = @intezmenyId
|
|
AND i.TOROLT = 'F'
|
|
END
|
|
|
|
IF @fenntartoId IS NOT NULL AND @intezmenyId IS NULL BEGIN
|
|
INSERT INTO @intezmenyTable (id)
|
|
SELECT i.ID FROM T_INTEZMENY i
|
|
WHERE i.C_FENNTARTOAZONOSITO = @fenntartoId
|
|
AND i.TOROLT = 'F'
|
|
END
|
|
|
|
IF @fenntartoId IS NULL AND @intezmenyId IS NOT NULL BEGIN
|
|
INSERT INTO @intezmenyTable (id)
|
|
SELECT i.ID FROM T_INTEZMENY i
|
|
WHERE i.ID = @intezmenyId
|
|
AND i.TOROLT = 'F'
|
|
END
|
|
|
|
IF @fenntartoId IS NULL AND @intezmenyId IS NULL BEGIN
|
|
INSERT INTO @intezmenyTable (id)
|
|
SELECT i.ID FROM T_INTEZMENY i
|
|
WHERE i.TOROLT = 'F'
|
|
END
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM @intezmenyTable) BEGIN
|
|
RETURN
|
|
END
|
|
|
|
UPDATE i
|
|
SET i.tanevId = t.ID
|
|
FROM @intezmenyTable i
|
|
INNER JOIN T_TANEV t ON t.C_INTEZMENYID = i.id
|
|
WHERE t.C_NEV = @tanevNev
|
|
AND t.TOROLT = 'F'
|
|
|
|
SELECT 'NemRiport' AS ReportName,
|
|
CASE C_NEME
|
|
WHEN 4 THEN 'Férfi'
|
|
WHEN 5 THEN 'Nő'
|
|
ELSE 'Nincs adat'
|
|
END AS ReportKey1, NULL AS ReportKey2, COUNT(1) AS ReportValue
|
|
FROM T_FELHASZNALO f
|
|
INNER JOIN @intezmenyTable i ON f.C_INTEZMENYID = i.id AND f.C_TANEVID = i.tanevId
|
|
WHERE EXISTS (SELECT 1 FROM T_MUNKAUGYIADATOK m WHERE m.C_ALKALMAZOTTID = f.ID AND m.C_BETOLTETLENALLASHELY = 'F')
|
|
AND f.TOROLT = 'F'
|
|
GROUP BY C_NEME
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'MunkaviszonyRiport' AS ReportName, d.C_NAME AS ReportKey1, NULL AS ReportKey2, COUNT(1) AS ReportValue
|
|
FROM T_FELHASZNALO f
|
|
INNER JOIN T_MUNKAUGYIADATOK m ON f.ID = m.C_ALKALMAZOTTID
|
|
INNER JOIN @intezmenyTable i ON f.C_INTEZMENYID = i.id AND f.C_TANEVID = i.tanevId
|
|
LEFT JOIN T_DICTIONARYITEMBASE d ON d.C_INTEZMENYID = m.C_INTEZMENYID AND m.C_TANEVID = d.C_TANEVID AND d.ID = m.C_MUNKAVISZONYTIPUSA
|
|
WHERE m.C_BETOLTETLENALLASHELY = 'F'
|
|
AND f.TOROLT = 'F'
|
|
GROUP BY d.C_NAME
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'ÖtévRiport',
|
|
CASE f.C_NEME
|
|
WHEN 4 THEN 'Férfi'
|
|
WHEN 5 THEN 'Nő'
|
|
ELSE 'Nincs adat'
|
|
END, p.nev, COUNT(1)
|
|
FROM T_FELHASZNALO f
|
|
LEFT JOIN (VALUES
|
|
('11-15', 11, 15), ('16-20', 16, 20), ('21-25', 21, 25), ('26-30', 26, 30), ('31-35', 31, 35), ('36-40', 36, 40), ('41-45', 41, 45),
|
|
('46-50', 46, 50), ('51-55', 51, 55), ('56-60', 56, 60), ('61-65', 61, 65), ('66-70', 66, 70), ('71-75', 71, 75),
|
|
('76-80', 76, 80), ('81-85', 81, 85), ('86-90', 86, 90), ('81-95', 81, 95), ('96-100', 96, 100), ('101-105', 101, 105)
|
|
) p (nev, also, felso) ON
|
|
CASE
|
|
WHEN DATEADD(YY, DATEDIFF(yy, f.C_SZULETESIDATUM, GETDATE()), f.C_SZULETESIDATUM) < GETDATE() THEN DATEDIFF(yy, f.C_SZULETESIDATUM, GETDATE())
|
|
ELSE DATEDIFF(yy, f.C_SZULETESIDATUM,GETDATE()) - 1
|
|
END BETWEEN p.also AND p.felso
|
|
INNER JOIN @intezmenyTable i ON f.C_INTEZMENYID = i.id AND f.C_TANEVID = i.tanevId
|
|
WHERE EXISTS (SELECT 1 FROM T_MUNKAUGYIADATOK m WHERE m.C_ALKALMAZOTTID = f.ID AND m.C_BETOLTETLENALLASHELY = 'F')
|
|
AND f.TOROLT = 'F'
|
|
GROUP BY f.C_NEME, p.nev
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'TantargyRiport' AS ReportName, d.C_NAME AS ReportKey1, NULL AS ReportKey2, COUNT(DISTINCT f.C_TANARID) AS ReportValue
|
|
FROM T_FOGLALKOZAS f
|
|
INNER JOIN T_TANTARGY t ON f.C_TANTARGYID = t.ID
|
|
INNER JOIN T_DICTIONARYITEMBASE d ON d.C_INTEZMENYID = t.C_INTEZMENYID AND t.C_TANEVID = d.C_TANEVID AND d.ID = t.C_TARGYKATEGORIA
|
|
INNER JOIN @intezmenyTable i ON f.C_INTEZMENYID = i.id AND f.C_TANEVID = i.tanevId
|
|
WHERE f.TOROLT = 'F'
|
|
GROUP BY d.C_NAME
|
|
|
|
END |