63 lines
2.7 KiB
Transact-SQL
63 lines
2.7 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('sp_GetTantargyStatuszRiport', 'P') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_GetTantargyStatuszRiport
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_GetTantargyStatuszRiport
|
|
@fenntartoId nvarchar(max) = null,
|
|
@intezmenyId int = null,
|
|
@tanevId int = null,
|
|
@statuszSzam int = null
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
select
|
|
ROW_NUMBER() OVER(ORDER BY TT.C_TARGYKATEGORIA) AS ID,
|
|
TT.C_TARGYKATEGORIA TargyKategoriaTipus,
|
|
SUM(F.C_ORASZAM) OsszesOraszam,
|
|
cast(SUM(F.C_ORASZAM) / @statuszSzam as decimal(10,2)) ElmeletiStatuszSzam,
|
|
isnull(STATUSZ.StatuszSzam, 0) StatuszSzam,
|
|
count(DISTINCT F.C_TANARID) PedagogusSzam
|
|
from T_FOGLALKOZAS F
|
|
inner join T_TANTARGY TT on TT.ID = F.C_TANTARGYID and TT.TOROLT = 'F'
|
|
inner join T_INTEZMENYADATOK IA on IA.C_TANEVID = F.C_TANEVID and IA.C_INTEZMENYID = F.C_INTEZMENYID and IA.TOROLT = 'F'
|
|
inner join T_INTEZMENY I on I.ID = IA.C_INTEZMENYID and I.TOROLT = 'F'
|
|
inner join
|
|
(select
|
|
SZUM.TargyKategoria,
|
|
cast(sum(SZUM.StatuszSzam) as decimal(10,2)) StatuszSzam
|
|
from
|
|
(
|
|
select
|
|
TT.C_TARGYKATEGORIA TargyKategoria,
|
|
IIF(SUM(T_FOGLALKOZAS.C_ORASZAM) <= Kotelezo - Kedvezmeny,
|
|
CAST(SUM(T_FOGLALKOZAS.C_ORASZAM) AS DECIMAL) / CAST((Kotelezo - Kedvezmeny)AS DECIMAL),
|
|
1 + (cast((SUM(T_FOGLALKOZAS.C_ORASZAM) - (Kotelezo - Kedvezmeny)) as integer) / @statuszSzam)) StatuszSzam
|
|
from T_FOGLALKOZAS
|
|
inner join T_ALKALMAZOTT on T_ALKALMAZOTT.ID = T_FOGLALKOZAS.C_TANARID and T_ALKALMAZOTT.TOROLT = 'F'
|
|
inner join T_MUNKAUGYIADATOK on T_MUNKAUGYIADATOK.C_ALKALMAZOTTID = T_ALKALMAZOTT.ID and T_MUNKAUGYIADATOK.TOROLT ='F'
|
|
INNER JOIN (SELECT C_ALKALMAZOTTID, C_KOTELEZOORASZAM Kotelezo, IIF(C_MUNKAIDOKEDVEZMENYORASZAM IS NULL, 0, C_MUNKAIDOKEDVEZMENYORASZAM) Kedvezmeny FROM T_MUNKAUGYIADATOK
|
|
WHERE TOROLT = 'F' and C_KOTELEZOORASZAM > 0 and C_KOTELEZOORASZAM - isnull(C_MUNKAIDOKEDVEZMENYORASZAM, 0) > 0
|
|
) MunkaU ON
|
|
MunkaU.C_ALKALMAZOTTID = T_ALKALMAZOTT.ID
|
|
inner join T_TANTARGY TT on TT.ID = T_FOGLALKOZAS.C_TANTARGYID and TT.TOROLT = 'F'
|
|
where T_FOGLALKOZAS.TOROLT = 'F'
|
|
and T_MUNKAUGYIADATOK.C_KOTELEZOORASZAM is not null
|
|
and (T_FOGLALKOZAS.C_INTEZMENYID = @intezmenyId or @intezmenyId is null)
|
|
and (T_FOGLALKOZAS.C_TANEVID = @tanevId or @tanevId is null)
|
|
group by T_FOGLALKOZAS.C_INTEZMENYID, T_FOGLALKOZAS.C_TANEVID,TT.C_TARGYKATEGORIA, MunkaU.Kotelezo,MunkaU.Kedvezmeny
|
|
) SZUM
|
|
group by SZUM.TargyKategoria ) STATUSZ
|
|
on STATUSZ.TargyKategoria = TT.C_TARGYKATEGORIA
|
|
where F.TOROLT = 'F'
|
|
and (I.C_FENNTARTOAZONOSITO = @fenntartoId or @fenntartoId is null)
|
|
and (I.ID = @intezmenyId or @intezmenyId is null)
|
|
and (IA.C_TANEVID = @tanevId or @tanevId is null)
|
|
group by TT.C_TARGYKATEGORIA, STATUSZ.StatuszSzam
|
|
END |