122 lines
5.9 KiB
Transact-SQL
122 lines
5.9 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetTanulokTantargyMulasztasokReszletezese]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetTanulokTantargyMulasztasokReszletezese]
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetTanulokTantargyMulasztasokReszletezese]
|
|
@OsztalyId int,
|
|
@TanevId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @Diakok TABLE (DiakId INT)
|
|
INSERT INTO @Diakok
|
|
SELECT
|
|
TanuloId
|
|
FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@TanevId, @OsztalyId, 'T')
|
|
|
|
declare @Mulasztasok table (MulasztasId int) insert into @Mulasztasok select ID from T_MULASZTASTIPUS_OSSZES where ID != 1497 and ID != 1498 and ID !=1817 and C_ALTANEVID = @TanevId
|
|
|
|
declare @tmp table (OSZTALY nvarchar(255), INT_NEV nvarchar(255), INT_OMKOD int, INT_VAROS nvarchar(255), INT_CIM nvarchar(255), INT_IGAZGATO_NEV nvarchar(255), OSZTFO_NEV nvarchar(255))
|
|
insert into @tmp select top(1) ocs.C_NEV, i.C_NEV as INT_NEV,i.C_OMKOD as INT_OMKOD,i.C_VAROS as INT_VAROS,dbo.fnGetDokumentumIntezmenyCime(@tanevId) as INT_CIM,
|
|
i.C_IGAZGATONEVE as INT_IGAZGATO_NEV,osztf.C_VEZETEKNEV + ' ' + osztf.C_UTONEV as OSZTFO_NEV
|
|
from T_OSZTALYCSOPORT_OSSZES ocs
|
|
join T_TANULOCSOPORT_OSSZES tcs on tcs.C_OSZTALYCSOPORTID = ocs.ID and tcs.TOROLT = 'F'
|
|
join T_FELHASZNALO_OSSZES f on f.ID = tcs.C_TANULOID and f.TOROLT = 'F'
|
|
left outer join T_INTEZMENYADATOK_OSSZES i on i.C_INTEZMENYID = ocs.C_INTEZMENYID and i.C_TANEVID = ocs.C_TANEVID and i.TOROLT = 'F'
|
|
left outer join T_OSZTALY_OSSZES o on o.ID = ocs.ID and o.TOROLT = 'F'
|
|
left outer join T_FELHASZNALO_OSSZES osztf on osztf.ID = o.C_OSZTALYFONOKID and osztf.TOROLT = 'F'
|
|
where
|
|
tcs.C_OSZTALYCSOPORTID = @OsztalyId
|
|
and ocs.C_TANEVID = @TanevId
|
|
and ocs.TOROLT = 'F'
|
|
and tcs.TOROLT = 'F'
|
|
and tcs.C_BELEPESDATUM < GETDATE()
|
|
and (tcs.C_KILEPESDATUM is null or tcs.C_KILEPESDATUM >= GETDATE())
|
|
|
|
declare @TenylegesMulasztasok table (DiakId int, MulasztasId int, NEV nvarchar(100), OSZTALY nvarchar(100), DATUM datetime, ORA int, MULASZTASJELLEG nvarchar(100), KESESPERC int, OSZTALYCSOPORT nvarchar(100),
|
|
TANTARGYID int, TANTARGY nvarchar(100), INT_NEV nvarchar(255), INT_OMKOD nvarchar(100), INT_VAROS nvarchar(100), INT_CIM nvarchar(255), INT_IGAZGATO_NEV nvarchar(100), OSZTFO_NEV nvarchar(100), IGAZOLT nvarchar(1),IG_TIPUS nvarchar(100))
|
|
insert into @TenylegesMulasztasok select f.Id,tm.C_TIPUS,f.C_VEZETEKNEV + ' ' + f.C_UTONEV as NEV,ocs.C_NEV as OSZTALY,tno.C_DATUM as DATUM,oo.C_ORASZAM as ORA,tm.C_TIPUS as MULASZTASJELLEG,
|
|
tm.C_KESESPERCBEN as KESESPERC,ocs.C_NEV as OSZTALYCSOPORT,tt.ID as TANTARGYID, tt.C_NEV as TANTARGY,i.C_NEV as INT_NEV,i.C_OMKOD as INT_OMKOD,i.C_VAROS as INT_VAROS,dbo.fnGetDokumentumIntezmenyCime(@tanevId) as INT_CIM,
|
|
i.C_IGAZGATONEVE as INT_IGAZGATO_NEV,osztf.C_VEZETEKNEV + ' ' + osztf.C_UTONEV as OSZTFO_NEV, tm.C_IGAZOLT as IGAZOLT, tm.C_IGAZOLASTIPUSA as IG_TIPUS
|
|
from T_OSZTALYCSOPORT_OSSZES ocs
|
|
join T_TANULOCSOPORT_OSSZES tcs on tcs.C_OSZTALYCSOPORTID = ocs.ID and tcs.TOROLT = 'F'
|
|
join T_FELHASZNALO_OSSZES f on f.ID = tcs.C_TANULOID and f.TOROLT = 'F'
|
|
left join T_TANULOMULASZTAS_OSSZES tm on tm.C_ORATANULOIID = tcs.C_TANULOID and tm.TOROLT = 'F' and tm.C_TIPUS != 1817
|
|
left join T_TANITASIORA_OSSZES tno on tno.ID = tm.C_TANITASIORAKID and tno.TOROLT = 'F'
|
|
left join T_TANTARGY_OSSZES tt on tt.ID = tno.C_TANTARGYID and tt.TOROLT = 'F'
|
|
left join T_ORARENDIORA_OSSZES oo on oo.ID = tno.C_ORARENDIORAID and oo.TOROLT = 'F'
|
|
left outer join T_INTEZMENYADATOK_OSSZES i on i.C_INTEZMENYID = ocs.C_INTEZMENYID and i.C_TANEVID = ocs.C_TANEVID and i.TOROLT = 'F'
|
|
left outer join T_OSZTALY_OSSZES o on o.ID = ocs.ID and o.TOROLT = 'F'
|
|
left outer join T_FELHASZNALO_OSSZES osztf on osztf.ID = o.C_OSZTALYFONOKID and osztf.TOROLT = 'F'
|
|
where
|
|
tcs.C_OSZTALYCSOPORTID = @OsztalyId
|
|
and ocs.C_TANEVID = @TanevId
|
|
and ocs.TOROLT = 'F'
|
|
and tcs.TOROLT = 'F'
|
|
and tcs.C_BELEPESDATUM < GETDATE()
|
|
and (tcs.C_KILEPESDATUM is null or tcs.C_KILEPESDATUM >= GETDATE())
|
|
|
|
|
|
declare @tmp2 table (DiakId int, TantargyId int, Cnt int)
|
|
insert into @tmp2
|
|
select
|
|
N.DiakId
|
|
,T.TANTARGYID
|
|
,count(*) as Cnt
|
|
from @Diakok N
|
|
cross join @Mulasztasok O
|
|
join @TenylegesMulasztasok T on T.DiakId = N.DiakId and T.MulasztasId = O.MulasztasId
|
|
group by N.DiakId,T.TANTARGYID
|
|
|
|
--select * from @tmp2
|
|
|
|
select
|
|
N.DiakId
|
|
,O.MulasztasId
|
|
,O.MulasztasId as Mulasztas
|
|
,f.C_VEZETEKNEV + ' ' + f.C_UTONEV as NEV
|
|
,(select OSZTALY from @tmp) as OSZTALY
|
|
,DATUM
|
|
,ORA
|
|
,T.MulasztasId as MULASZTASJELLEG
|
|
,IGAZOLT
|
|
,IG_TIPUS
|
|
,KESESPERC
|
|
,OSZTALYCSOPORT
|
|
,T.TANTARGYID
|
|
,TANTARGY
|
|
,(select INT_NEV from @tmp) as INT_NEV
|
|
,(select INT_OMKOD from @tmp) as INT_OMKOD
|
|
,(select INT_VAROS from @tmp) as INT_VAROS
|
|
,(select INT_CIM from @tmp) as INT_CIM
|
|
,(select INT_IGAZGATO_NEV from @tmp) as INT_IGAZGATO_NEV
|
|
,(select OSZTFO_NEV from @tmp) as OSZTFO_NEV
|
|
,isnull(Cntr.Cnt, 0) as Cntr
|
|
,f.C_NYOMTATASINEV + ' ' + cast(N.DiakId as nvarchar) as GROUPPARAMETER
|
|
from @Diakok N
|
|
cross join @Mulasztasok O
|
|
left join @TenylegesMulasztasok T on T.DiakId = N.DiakId and T.MulasztasId = O.MulasztasId
|
|
join T_FELHASZNALO_OSSZES f on f.ID = N.DiakId and f.TOROLT = 'F'
|
|
left join @tmp2 Cntr on Cntr.DiakId = N.DiakId and Cntr.TantargyId = T.TANTARGYID
|
|
where T.TANTARGYID is not null
|
|
order by f.C_NYOMTATASINEV
|
|
|
|
select
|
|
DiakId,
|
|
TANTARGY,
|
|
isnull(count(1),0) as Ossz
|
|
from @TenylegesMulasztasok
|
|
group by DiakId,TANTARGY
|
|
order by DiakId,TANTARGY
|
|
|
|
select C_OSZTALYFONOKID PartnerID From T_OSZTALY_OSSZES WHERE T_OSZTALY_OSSZES.ID = @OsztalyId AND T_OSZTALY_OSSZES.TOROLT='F' and T_OSZTALY_OSSZES.C_ALTANEVID = @TanevId
|
|
|
|
END |