89 lines
2.8 KiB
Transact-SQL
89 lines
2.8 KiB
Transact-SQL
IF OBJECT_ID('sp_GetRiportBetoltetlenAllashelyek') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_GetRiportBetoltetlenAllashelyek
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_GetRiportBetoltetlenAllashelyek
|
|
@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'
|
|
|
|
/*DECLARE @resultTable table(
|
|
ReportName nvarchar(30),
|
|
ReportKey1 nvarchar(255),
|
|
ReportKey2 nvarchar(255),
|
|
ReportValue int
|
|
)
|
|
*/
|
|
|
|
-- INSERT INTO @resultTable (ReportName, ReportKey1, ReportKey2, ReportValue)
|
|
SELECT 'BetöltetlenÁllásRiport' AS ReportName, 'Betöltetlen állás' 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
|
|
WHERE m.C_BETOLTETLENALLASHELY = 'T'
|
|
AND f.TOROLT = 'F'
|
|
-- AND m.C_MUNKAVISZONYTIPUSA = 647
|
|
UNION ALL
|
|
-- INSERT INTO @resultTable (ReportName, ReportKey1, ReportKey2, ReportValue)
|
|
SELECT 'BetöltetlenÁllásTantargyRiport' AS ReportName, d.C_NAME AS ReportKey1, NULL AS ReportKey2, SUM(f.C_ORASZAM) AS ReportValue
|
|
FROM T_FOGLALKOZAS f
|
|
INNER JOIN T_TANTARGY t ON f.C_TANTARGYID = t.ID
|
|
INNER JOIN T_MUNKAUGYIADATOK m ON f.C_TANARID = m.C_ALKALMAZOTTID
|
|
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'
|
|
-- AND m.C_MUNKAVISZONYTIPUSA = 647
|
|
AND m.C_BETOLTETLENALLASHELY = 'T'
|
|
GROUP BY d.C_NAME
|
|
|
|
--SELECT ReportName, ReportKey1, ReportKey2, ReportValue FROM @resultTable
|
|
|
|
END
|
|
GO |