﻿DROP FUNCTION IF EXISTS dbo.fnGetOsztalyCsoportTanuloinakTanugyiAdatai
GO

/*
  1) Osztály, tudjuk (AMI-nál nem kell pár mező)
  2) Csoport, bontott, tudjuk
  3) Csoport, nem bontott, fh-n 1 osztály van ,tudjuk
  4) Csoport, nem bontott, fh-n több osztály, nem tudjuk
  5) Ha nincs Osztálya a kategórián, akkor a tanulóét
*/

CREATE FUNCTION dbo.fnGetOsztalyCsoportTanuloinakTanugyiAdatai(
   @pOsztalyCsoportId int
  ,@pDatum date = NULL)
RETURNS @tanugyiAdatok TABLE(
   ID int
  ,TanuloId int
  ,C_BEIRASINAPLOSORSZAM nvarchar(100)
  ,C_BEJARO	char(1)
  ,C_DIAKSPORTKOROS	char(1)
  ,C_EGYEBORSZAGOSDONTO	char(1)
  ,C_EGYUTTMUKODESES char(1)
  ,C_ELOZOINTEZMENY	nvarchar(510)
  ,C_EVISMETLO char(1)
  ,C_ISKOLAIKERETEKKOZOTT	char(1)
  ,C_JOGVISZONYATSZUNETELTETO	char(1)
  ,C_JOGVISZONYVARHATOBEFEJEZESE datetime
  ,C_MAGANTANULO char(1)
  ,C_MAGANTANULOSAGANAKOKAID int
  ,C_MAGANTANULOSAGKEZDETE datetime
  ,C_NYILVANTARTASKEZDETE	datetime
  ,C_OSZTV char(1)
  ,C_POLGARISZERZODESES	char(1)
  ,C_SZAKMAIGYAKORLATON	char(1)
  ,C_SZETVAGAZAT char(1)
  ,C_SZINTVIZSGA numeric
  ,C_SZKTV char(1)
  ,C_TANDIJATFIZETO	char(1)
  ,C_TANKOTELEZETT char(1)
  ,C_TANKOTELEZETTSEGVEGE	datetime
  ,C_TANULOEVFOLYAMTIPUSID int
  ,C_TANULOSZERZODESES char(1)
  ,C_TECHNIKUSIEVFOLYAM	char(1)
  ,C_TERITESIDIJATFIZETO char(1)
  ,C_VENDEG	char(1)
  ,C_NAPLOSORSZAM	int
  ,C_TORZSLAPSZAM	nvarchar(1000)
  ,C_TANTERVID int
  ,C_FELVETELTANEVEID	int
  ,C_TANULOCSOPORTID int
  ,C_HOZOTTIGAZOLATLANHIANYZAS int
  ,C_HOZOTTIGAZOLATLANKESESPERCBE	int
  ,C_HOZOTTIGAZOLTHIANYZAS int
  ,C_HOZOTTIGAZOLTKESESPERCBEN int
  ,C_ISESL16EVESUTANBELEPETT char(1)
  )
AS
BEGIN
  IF @pDatum IS NULL
    SET @pDatum = GETDATE()
  
  DECLARE @TanugyiAdat TABLE (ID int,TanuloId int, Darab int)
  DECLARE @kategoriaId int = (SELECT C_FELADATKATEGORIAID FROM T_OSZTALYCSOPORT_OSSZES WHERE ID = @pOsztalyCsoportId)
  DECLARE @bontasId int = (SELECT C_OSZTALYBONTASID FROM T_CSOPORT_OSSZES WHERE ID = @pOsztalyCsoportId)
  DECLARE @isAmi bit = (SELECT IIF(@kategoriaId = 7555,1,0))
  DECLARE @isOsztaly bit = 0
  IF EXISTS(SELECT 1 FROM T_OSZTALY_OSSZES WHERE ID = @pOsztalyCsoportId)
    SET @isOsztaly=1

  IF @isOsztaly = 1 BEGIN
    INSERT INTO @TanugyiAdat (ID,TanuloId,Darab)
    SELECT tta.ID,tcs.C_TANULOID,1
    FROM T_TANULOTANUGYIADATOK_OSSZES tta
      INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.ID=tta.C_TANULOCSOPORTID AND tcs.C_OSZTALYCSOPORTID=@posztalyCsoportId
        AND tcs.C_BELEPESDATUM <= @pDatum AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM>@pDatum) AND tcs.TOROLT='F'
    WHERE tta.TOROLT='F'
  END
  ELSE IF @bontasId IS NOT NULL BEGIN
    INSERT INTO @TanugyiAdat (ID,TanuloId,Darab)
    SELECT tta.ID,tcs.C_TANULOID,1
    FROM T_TANULOTANUGYIADATOK_OSSZES tta
      INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.ID=tta.C_TANULOCSOPORTID AND tcs.C_OSZTALYCSOPORTID=@bontasId
        AND tcs.C_BELEPESDATUM <= @pDatum AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM>@pDatum) AND tcs.TOROLT='F'
  END
  ELSE BEGIN
    INSERT INTO @TanugyiAdat (ID,TanuloId,Darab)
    SELECT tta.ID, tcso.C_TANULOID, ROW_NUMBER() OVER(PARTITION BY tcso.C_TANULOID ORDER BY tta.ID)
    FROM T_TANULOTANUGYIADATOK_OSSZES tta
      INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.ID=tta.C_TANULOCSOPORTID 
        AND tcs.C_BELEPESDATUM <= @pDatum AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM>@pDatum) AND tcs.TOROLT='F'     
      INNER JOIN T_OSZTALY_OSSZES o ON o.ID=tcs.C_OSZTALYCSOPORTID AND o.TOROLT='F'
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID=o.ID AND ocs.TOROLT='F' AND ocs.C_FELADATKATEGORIAID=@kategoriaId
      INNER JOIN T_TANULOCSOPORT_OSSZES tcso ON tcso.C_TANULOID=tcs.C_TANULOID
        AND tcso.C_BELEPESDATUM <= @pDatum AND (tcso.C_KILEPESDATUM IS NULL OR tcso.C_KILEPESDATUM>@pDatum) AND tcso.TOROLT='F' AND tcso.C_OSZTALYCSOPORTID=@pOsztalyCsoportId

  END
  
  UPDATE ta SET Darab = x.DARAB FROM(
    SELECT MAX(DARAB) DARAB ,TanuloId 
    FROM @TanugyiAdat
    GROUP BY TanuloId) x 
    INNER JOIN @TanugyiAdat ta ON ta.TanuloId=x.TanuloId

  INSERT INTO @tanugyiAdatok (
     ID 
    ,TanuloId
    ,C_BEIRASINAPLOSORSZAM	
    ,C_BEJARO	
    ,C_DIAKSPORTKOROS	
    ,C_EGYEBORSZAGOSDONTO	
    ,C_EGYUTTMUKODESES	
    ,C_ELOZOINTEZMENY	
    ,C_EVISMETLO	
    ,C_ISKOLAIKERETEKKOZOTT	
    ,C_JOGVISZONYATSZUNETELTETO	
    ,C_JOGVISZONYVARHATOBEFEJEZESE	
    ,C_MAGANTANULO	
    ,C_MAGANTANULOSAGANAKOKAID	
    ,C_MAGANTANULOSAGKEZDETE	
    ,C_NYILVANTARTASKEZDETE	
    ,C_OSZTV	
    ,C_POLGARISZERZODESES	
    ,C_SZAKMAIGYAKORLATON	
    ,C_SZETVAGAZAT	
    ,C_SZINTVIZSGA	
    ,C_SZKTV
    ,C_TANDIJATFIZETO	
    ,C_TANKOTELEZETT	
    ,C_TANKOTELEZETTSEGVEGE	
    ,C_TANULOEVFOLYAMTIPUSID	
    ,C_TANULOSZERZODESES	
    ,C_TECHNIKUSIEVFOLYAM	
    ,C_TERITESIDIJATFIZETO	
    ,C_VENDEG	
    ,C_NAPLOSORSZAM	
    ,C_TORZSLAPSZAM	
    ,C_TANTERVID	
    ,C_FELVETELTANEVEID	
    ,C_TANULOCSOPORTID	
    ,C_HOZOTTIGAZOLATLANHIANYZAS	
    ,C_HOZOTTIGAZOLATLANKESESPERCBE	
    ,C_HOZOTTIGAZOLTHIANYZAS	
    ,C_HOZOTTIGAZOLTKESESPERCBEN	
    ,C_ISESL16EVESUTANBELEPETT
	) SELECT DISTINCT 
     0                     --ID 
    ,TanuloId              --TanuloId
    ,NULL                  --C_BEIRASINAPLOSORSZAM 
    ,'F'                   --C_BEJARO       
    ,'F'                   --C_DIAKSPORTKOROS  
    ,'F'                   --C_EGYEBORSZAGOSDONTO    
    ,'F'		           --C_EGYUTTMUKODESES              
    ,NULL                  --C_ELOZOINTEZMENY           
    ,'F'                   --C_EVISMETLO 
    ,'F'                   --C_ISKOLAIKERETEKKOZOTT      
    ,'F'                   --C_JOGVISZONYATSZUNETELTETO          
    ,NULL                  --C_JOGVISZONYVARHATOBEFEJEZESE            
    ,'F'                   --C_MAGANTANULO       
    ,NULL                  --C_MAGANTANULOSAGANAKOKAID 
    ,NULL                  --C_MAGANTANULOSAGKEZDETE 
    ,'19000101'            --C_NYILVANTARTASKEZDETE  
    ,'F'                   --C_OSZTV          
    ,'F'                   --C_POLGARISZERZODESES         
    ,'F'                   --C_SZAKMAIGYAKORLATON      
    ,'F'                   --C_SZETVAGAZAT           
    ,NULL                  --C_SZINTVIZSGA       
    ,'F'                   --C_SZKTV
    ,'F'                   --C_TANDIJATFIZETO     
    ,'F'                   --C_TANKOTELEZETT       
    ,NULL                  --C_TANKOTELEZETTSEGVEGE              
    ,NULL                  --C_TANULOEVFOLYAMTIPUSID         
    ,'F'                   --C_TANULOSZERZODESES          
    ,'F'                   --C_TECHNIKUSIEVFOLYAM        
    ,'F'                   --C_TERITESIDIJATFIZETO             
    ,'F'                   --C_VENDEG            
    ,NULL                  --C_NAPLOSORSZAM 
    ,NULL                  --C_TORZSLAPSZAM              
    ,0                     --C_TANTERVID 
    ,0                     --C_FELVETELTANEVEID          
    ,0                     --C_TANULOCSOPORTID         
    ,0                     --C_HOZOTTIGAZOLATLANHIANYZAS 
    ,0                     --C_HOZOTTIGAZOLATLANKESESPERCBE
    ,0                     --C_HOZOTTIGAZOLTHIANYZAS           
    ,0                     --C_HOZOTTIGAZOLTKESESPERCBEN 
    ,'F'                   --C_ISESL16EVESUTANBELEPETT 
  FROM @TanugyiAdat
  WHERE Darab>1
  
  INSERT INTO @tanugyiAdatok (
     ID 
    ,TanuloId
    ,C_BEIRASINAPLOSORSZAM   
    ,C_BEJARO     
    ,C_DIAKSPORTKOROS               
    ,C_EGYEBORSZAGOSDONTO  
    ,C_EGYUTTMUKODESES          
    ,C_ELOZOINTEZMENY               
    ,C_EVISMETLO             
    ,C_ISKOLAIKERETEKKOZOTT   
    ,C_JOGVISZONYATSZUNETELTETO      
    ,C_JOGVISZONYVARHATOBEFEJEZESE 
    ,C_MAGANTANULO   
    ,C_MAGANTANULOSAGANAKOKAID  
    ,C_MAGANTANULOSAGKEZDETE         
    ,C_NYILVANTARTASKEZDETE 
    ,C_OSZTV       
    ,C_POLGARISZERZODESES      
    ,C_SZAKMAIGYAKORLATON   
    ,C_SZETVAGAZAT       
    ,C_SZINTVIZSGA          
    ,C_SZKTV
    ,C_TANDIJATFIZETO   
    ,C_TANKOTELEZETT   
    ,C_TANKOTELEZETTSEGVEGE 
    ,C_TANULOEVFOLYAMTIPUSID            
    ,C_TANULOSZERZODESES       
    ,C_TECHNIKUSIEVFOLYAM     
    ,C_TERITESIDIJATFIZETO          
    ,C_VENDEG   
    ,C_NAPLOSORSZAM  
    ,C_TORZSLAPSZAM    
    ,C_TANTERVID             
    ,C_FELVETELTANEVEID             
    ,C_TANULOCSOPORTID           
    ,C_HOZOTTIGAZOLATLANHIANYZAS  
    ,C_HOZOTTIGAZOLATLANKESESPERCBE           
    ,C_HOZOTTIGAZOLTHIANYZAS             
    ,C_HOZOTTIGAZOLTKESESPERCBEN   
    ,C_ISESL16EVESUTANBELEPETT
  )
  SELECT 
     tta.ID 
    ,t.ID
    ,ISNULL(tta.C_BEIRASINAPLOSORSZAM,t.C_BEIRASINAPLOSORSZAM)
    ,ISNULL(tta.C_BEJARO,t.C_BEJARO)	
    ,ISNULL(tta.C_DIAKSPORTKOROS,t.C_DIAKSPORTKOROS)
    ,ISNULL(tta.C_EGYEBORSZAGOSDONTO,t.C_EGYEBDONTO)
    ,ISNULL(tta.C_EGYUTTMUKODESES,t.C_EGYUTTMUKODESES)
    ,ISNULL(tta.C_ELOZOINTEZMENY,t.C_ELOZOINTEZMENY)
    ,ISNULL(tta.C_EVISMETLO,t.C_EVISMETLO)
    ,ISNULL(tta.C_ISKOLAIKERETEKKOZOTT,t.C_ISKOLAIKERETEKKOZOTT)
    ,CASE WHEN @isAmi = 1 THEN 'F' ELSE ISNULL(tta.C_JOGVISZONYATSZUNETELTETO,t.C_JOGVISZONYATSZUNETELTETO) END
    ,ISNULL(tta.C_JOGVISZONYVARHATOBEFEJEZESE,t.C_JOGVISZONYVARHATOBEFEJEZESE)
    ,CASE WHEN @isAmi = 1 THEN 'F' ELSE ISNULL(tta.C_MAGANTANULO,t.C_MAGANTANULO)	END
    ,CASE WHEN @isAmi = 1 THEN NULL ELSE ISNULL(tta.C_MAGANTANULOSAGANAKOKAID,t.C_MAGANTANULOSAGANAKOKA)	END
    ,CASE WHEN @isAmi = 1 THEN NULL ELSE ISNULL(tta.C_MAGANTANULOSAGKEZDETE,t.C_MAGANTANULOSAGKEZDETE)	END
    ,tta.C_NYILVANTARTASKEZDETE
    ,ISNULL(tta.C_OSZTV,t.C_OSZTV)
    ,ISNULL(tta.C_POLGARISZERZODESES,t.C_POLGARISZERZODESES)
    ,ISNULL(tta.C_SZAKMAIGYAKORLATON,t.C_SZAKMAIGYAKORLATON)
    ,ISNULL(tta.C_SZETVAGAZAT,t.C_SZETVAGAZAT)
    ,ISNULL(tta.C_SZINTVIZSGA,t.C_SZINTVIZSGA)
    ,ISNULL(tta.C_SZKTV,t.C_SZKTV)
    ,ISNULL(tta.C_TANDIJATFIZETO,t.C_TANDIJATFIZETO)
    ,CASE WHEN @isAmi = 1 THEN 'F' ELSE ISNULL(tta.C_TANKOTELEZETT,t.C_TANKOTELEZETT)	END
    ,ISNULL(tta.C_TANKOTELEZETTSEGVEGE,t.C_TANKOTELEZETTSEGVEGE)
    ,tta.C_TANULOEVFOLYAMTIPUSID
    ,ISNULL(tta.C_TANULOSZERZODESES,t.C_TANULOSZERZODESES)
    ,ISNULL(tta.C_TECHNIKUSIEVFOLYAM,t.C_TECHNIKUSIEVFOLYAM)
    ,ISNULL(tta.C_TERITESIDIJATFIZETO,t.C_TERITESIDIJATFIZETO)
    ,ISNULL(tta.C_VENDEG,t.C_VENDEG)
    ,ISNULL(tta.C_NAPLOSORSZAM,t.C_NAPLOSORSZAM)
    ,ISNULL(tta.C_TORZSLAPSZAM,t.C_TORZSLAPSZAM)
    ,ISNULL(tta.C_TANTERVID,t.C_TANTERVID)
    ,ISNULL(tta.C_FELVETELTANEVEID,t.C_FELVETELTANEVEID)
    ,tta.C_TANULOCSOPORTID
    ,tta.C_HOZOTTIGAZOLATLANHIANYZAS
    ,tta.C_HOZOTTIGAZOLATLANKESESPERCBE
    ,tta.C_HOZOTTIGAZOLTHIANYZAS
    ,tta.C_HOZOTTIGAZOLTKESESPERCBEN
    ,tta.C_ISESL16EVESUTANBELEPETT
  FROM T_TANULOCSOPORT_OSSZES tcs
    LEFT JOIN @TanugyiAdat ta ON ta.TanuloId=tcs.C_TANULOID
    LEFT JOIN T_TANULOTANUGYIADATOK_OSSZES tta ON tta.ID=ta.ID AND tta.TOROLT='F'
    LEFT JOIN T_TANULO_OSSZES t ON t.ID=tcs.C_TANULOID AND t.TOROLT='F'
  WHERE tcs.C_OSZTALYCSOPORTID = @pOsztalyCsoportId AND tcs.TOROLT='F' AND tcs.C_BELEPESDATUM<=@pDatum AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM>@pDatum)
    AND ISNULL(ta.Darab,0)<2

  RETURN
END
GO