kreta/Kreta.DataAccess.Migrations/DBScripts/Database/dbo/Stored procedures/uspValidateOsztalybaSorolasModositasa.sql

DROP PROCEDURE IF EXISTS uspValidateOsztalybaSorolasModositasa
GO

CREATE PROCEDURE dbo.uspValidateOsztalybaSorolasModositasa
   @pTanevId int
  ,@pFromOsztalyId int
  ,@pToOsztalyId int
  ,@pTanuloId int
  ,@pDatumTol datetime
  ,@pDatumIg datetime
  ,@pFeladatKategoriaId int
  ,@pIsTorolhetosegVizsgalat bit
  ,@pIsCheckMaxOsztalyReturnKilepes bit
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @maxDate datetime
    ,@darab int
    ,@tanevUtolsoNap datetime = (SELECT C_UTOLSONAP FROM T_TANEV_OSSZES WHERE ID = @pTanevId)
    ,@rendszeruzenetId int = (SELECT ID FROM T_FELHASZNALO_OSSZES WHERE C_NYOMTATASINEV = 'Rendszerüzenet' AND C_TANEVID = @pTanevId AND TOROLT = 'F')

  CREATE TABLE #hibak (Oka int, OsztalyId int, OsztalybontottCsoportId int, Datum datetime, Darab int)
  -- Okok:
  --  1 - ha a vizsgált időszakban van akár csak egyetlen nap is (a legkorábbi ilyen dátumot nevezzük ’X’-nek), 
  --      ahol a tanuló be van sorolva egy olyan csoportba, ami az érintett osztály osztálybontásának van jelölve
  --  2 - ha a vizsgált időszakban van akár csak egyetlen nap is (a legkorábbi ilyen dátumot nevezzük ’X’-nek), 
  --      ahol a tanuló be van sorolva legalább egy nem osztálybontott csoportba, de nincs egyetlen osztályhoz sem rendelve
  --  3 - ha a vizsgált időszakban van akár csak egyetlen mulasztás is rögzítve a megadott osztályhoz 
  --  4 - ha a vizsgált időszakban van akár csak egyetlen értékelés is rögzítve a megadott osztályhoz 
  --  5 - ha a vizsgált időszakban van akár csak egyetlen feljegyzés is rögzítve az érintett tanulónak olyan napra,
  --      ahol nincs egyetlen további osztályba sem besorolva a tanuló bármely modulban
  --  6 - ha a vizsgált időszakban van akár csak egyetlen igazolás is rögzítve az érintett tanulónak olyan napra,
  --      ahol nincs egyetlen további osztályba sem besorolva a tanuló bármely modulban
  --  7 - ha a tanuló már tagja az adott osztálynak
  --  8 - ha a tanuló már annyi osztályba van besorolva, amennyi az érintett oktatási-nevelési feladatcsoportban megengedett
  --
  -- NOTE: 5-ös és 6-os esetében, ha lehet majd feladat kategóriához kötni, akkor azokat módosítani kell !!!

  IF @pIsTorolhetosegVizsgalat = 1 
  BEGIN
    INSERT INTO #hibak
    SELECT
       1 AS oka -- 'van osztálybontott csoport besorolása'
      ,ocso.ID AS OsztalyId
      ,ocscs.ID AS OsztalybontottCsoportId
      ,IIF(tcs.C_BELEPESDATUM <= @pDatumTol, @pDatumTol, tcs.C_BELEPESDATUM) AS Datum
      ,NULL AS Darab
    FROM T_TANULOCSOPORT_OSSZES tcs
      INNER JOIN T_CSOPORT_OSSZES cs ON cs.ID = tcs.C_OSZTALYCSOPORTID 
        AND cs.TOROLT = 'F'
        AND cs.C_OSZTALYBONTASID = @pFromOsztalyId
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocso ON ocso.ID = @pFromOsztalyId
        AND ocso.TOROLT = 'F'
        AND (ocso.C_FELADATKATEGORIAID = @pFeladatKategoriaId OR @pFeladatKategoriaId IS NULL)
      LEFT JOIN T_OSZTALYCSOPORT_OSSZES ocscs ON ocscs.ID = tcs.C_OSZTALYCSOPORTID
        AND ocscs.TOROLT = 'F'
    WHERE tcs.TOROLT = 'F'
      AND tcs.C_TANEVID = @pTanevId
      AND tcs.C_TANULOID = @pTanuloId
      AND (tcs.C_BELEPESDATUM <= @pDatumIg AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @pDatumTol))

    INSERT INTO #hibak
    SELECT
       2 AS oka -- 'van csoport besorolása osztálybesorolás nélkül'
      ,ocso.ID AS OsztalyId
      ,NULL AS OsztalybontottCsoportId
      ,IIF(tcs.C_BELEPESDATUM <= @pDatumTol, @pDatumTol, tcs.C_BELEPESDATUM) AS Datum
      ,NULL AS Darab
    FROM T_TANULOCSOPORT_OSSZES tcs
      INNER JOIN T_CSOPORT_OSSZES cs ON cs.ID = tcs.C_OSZTALYCSOPORTID 
        AND cs.TOROLT = 'F'
        AND cs.C_OSZTALYBONTASID IS NULL
      LEFT JOIN (
        SELECT 
          tcs2.* 
        FROM T_TANULOCSOPORT_OSSZES tcs2
          INNER JOIN T_OSZTALY_OSSZES o ON o.ID = tcs2.C_OSZTALYCSOPORTID
            AND o.TOROLT = 'F'
          INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = o.ID
            AND ocs.TOROLT = 'F'
            AND (ocs.C_FELADATKATEGORIAID = @pFeladatKategoriaId OR @pFeladatKategoriaId IS NULL)
        WHERE tcs2.TOROLT = 'F'
          AND tcs2.C_TANEVID = @pTanevId
          AND tcs2.C_TANULOID = @pTanuloId
          AND (tcs2.C_BELEPESDATUM <= @pDatumIg AND (tcs2.C_KILEPESDATUM IS NULL OR tcs2.C_KILEPESDATUM >= @pDatumTol))
          AND tcs2.C_OSZTALYCSOPORTID <> @pFromOsztalyId
      ) tcso ON tcso.C_TANULOID = tcso.C_TANULOID
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocso ON ocso.ID = tcs.C_OSZTALYCSOPORTID
        AND ocso.TOROLT = 'F'
        AND (ocso.C_FELADATKATEGORIAID = @pFeladatKategoriaId OR @pFeladatKategoriaId IS NULL)
    WHERE tcs.TOROLT = 'F'
      AND tcs.C_TANEVID = @pTanevId
      AND tcs.C_TANULOID = @pTanuloId
      AND (tcs.C_BELEPESDATUM <= @pDatumIg AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @pDatumTol))
      AND tcso.ID IS NULL

    INSERT INTO #hibak
    SELECT
       3 AS oka -- 'van az osztályhoz mulasztása' 
      ,ocso.ID AS OsztalyId
      ,NULL AS OsztalybontottCsoportId
      ,NULL AS Datum
      ,COUNT(mul.ID) AS Darab
      --,tcs.*
    FROM T_TANULOCSOPORT_OSSZES tcs
      INNER JOIN T_TANULOMULASZTAS_OSSZES mul ON mul.C_ORATANULOIID = tcs.C_TANULOID 
        AND mul.TOROLT = 'F'
      INNER JOIN T_TANITASIORA_OSSZES tano ON tano.ID = mul.C_TANITASIORAKID
        AND tano.TOROLT = 'F'
        AND tano.C_OSZTALYCSOPORTID = tcs.C_OSZTALYCSOPORTID
        AND tano.C_DATUM >= @pDatumTol
        AND tano.C_DATUM <= @pDatumIg
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocso ON ocso.ID = tcs.C_OSZTALYCSOPORTID
        AND ocso.TOROLT = 'F'
        AND (ocso.C_FELADATKATEGORIAID = @pFeladatKategoriaId OR @pFeladatKategoriaId IS NULL)
    WHERE tcs.TOROLT = 'F'
      AND tcs.C_TANEVID = @pTanevId
      AND tcs.C_TANULOID = @pTanuloId
      AND tcs.C_OSZTALYCSOPORTID = @pFromOsztalyId
      AND (tcs.C_BELEPESDATUM <= @pDatumIg AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @pDatumTol))
    GROUP BY ocso.ID

    INSERT INTO #hibak
    SELECT
       4 AS oka -- 'van az osztályhoz értékelés'
      ,ocso.ID AS OsztalyId
      ,NULL AS OsztalybontottCsoportId
      ,NULL AS Datum
      ,COUNT(ert.ID) AS Darab
    FROM T_TANULOCSOPORT_OSSZES tcs
      INNER JOIN T_TANULOERTEKELES_OSSZES ert ON ert.C_TANULOID = tcs.C_TANULOID 
        AND ert.C_OSZTALYCSOPORTID = tcs.C_OSZTALYCSOPORTID
        AND ert.TOROLT = 'F'
        AND ert.C_DATUM >= @pDatumTol
        AND ert.C_DATUM <= @pDatumIg
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocso ON ocso.ID = tcs.C_OSZTALYCSOPORTID
        AND ocso.TOROLT = 'F'
        AND (ocso.C_FELADATKATEGORIAID = @pFeladatKategoriaId OR @pFeladatKategoriaId IS NULL)
    WHERE tcs.TOROLT = 'F'
      AND tcs.C_TANEVID = @pTanevId
      AND tcs.C_TANULOID = @pTanuloId
      AND tcs.C_OSZTALYCSOPORTID = @pFromOsztalyId
      AND (tcs.C_BELEPESDATUM <= @pDatumIg AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @pDatumTol))
    GROUP BY ocso.ID

    SELECT 
       @maxDate = ISNULL(MAX(te.C_ESEMENYDATUMA), @tanevUtolsoNap)
      ,@darab = COUNT(te.ID)
    FROM T_TANULOESEMENY_OSSZES te
      INNER JOIN T_TANULO_TANULOESEMENY tte ON tte.C_TANULOESEMENYID = te.ID
    WHERE te.TOROLT = 'F'
      AND tte.C_TANULOID = @pTanuloId
      AND te.C_ESEMENYDATUMA >= @pDatumTol
      AND te.C_ESEMENYDATUMA <= @pDatumIg
      AND te.C_FELJEGYZOID <> @rendszeruzenetId

    IF @darab > 0 
    BEGIN
      IF NOT EXISTS (
      SELECT
        1
      FROM T_TANULOCSOPORT_OSSZES tcs
        INNER JOIN T_OSZTALY_OSSZES o ON o.ID = tcs.C_OSZTALYCSOPORTID 
          AND o.TOROLT = 'F'
      WHERE tcs.C_TANULOID = @pTanuloId
        AND tcs.TOROLT = 'F'
        AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @maxDate)
        AND tcs.C_OSZTALYCSOPORTID <> @pFromOsztalyId
      )
      BEGIN
        INSERT INTO #hibak
        SELECT 
           5 AS oka -- 'van feljegyzés a tanulónak, nincs aktív osztály besorolása'
          ,NULL AS OsztalyId
          ,NULL AS OsztalybontottCsoportId
          ,NULL AS Datum
          ,@darab AS Darab
      END
    END

    SELECT 
       @maxDate = ISNULL(MAX(i.C_VEGE), @tanevUtolsoNap)
      ,@darab = COUNT(i.ID)
    FROM T_IGAZOLAS i
    WHERE i.TOROLT = 'F'
      AND i.C_TANULOID = @pTanuloId
      AND i.C_VEGE >= @pDatumTol
      AND i.C_KEZDETE <= DATEADD(DAY, 1 ,@pDatumIg) --Egy nappal korábbi dátumot kapunk felületről így ez kell, különben az aznapi igazolásokat nem venné figyelembe
    IF @darab > 0 
    BEGIN
      IF NOT EXISTS (
      SELECT
        1
      FROM T_TANULOCSOPORT_OSSZES tcs
        INNER JOIN T_OSZTALY_OSSZES o ON o.ID = tcs.C_OSZTALYCSOPORTID 
          AND o.TOROLT = 'F'
      WHERE tcs.C_TANULOID = @pTanuloId
        AND tcs.TOROLT = 'F'
        AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @maxDate)
        AND tcs.C_OSZTALYCSOPORTID <> @pFromOsztalyId
      )
      BEGIN
        INSERT INTO #hibak
        SELECT 
           6 AS oka -- 'van igazolás a tanulónak, nincs aktív osztály besorolása'
          ,NULL AS OsztalyId
          ,NULL AS OsztalybontottCsoportId
          ,NULL AS Datum
          ,@darab AS Darab
      END
    END

    IF @pFromOsztalyId <> @pToOsztalyId
    BEGIN
      INSERT INTO #hibak
      SELECT 
         7 AS oka -- 'a tanuló már tagja az adott osztálynak'
        ,ocso.ID AS OsztalyId
        ,NULL AS OsztalybontottCsoportId
        ,IIF(tcs.C_BELEPESDATUM <= @pDatumTol, @pDatumTol, tcs.C_BELEPESDATUM) AS Datum
        ,NULL AS Darab
      FROM T_TANULOCSOPORT_OSSZES tcs
        INNER JOIN T_OSZTALYCSOPORT_OSSZES ocso ON ocso.ID = tcs.C_OSZTALYCSOPORTID
          AND ocso.TOROLT = 'F'
          AND (ocso.C_FELADATKATEGORIAID = @pFeladatKategoriaId OR @pFeladatKategoriaId IS NULL)
      WHERE tcs.TOROLT = 'F'
        AND tcs.C_TANEVID = @pTanevId
        AND tcs.C_TANULOID = @pTanuloId
        AND tcs.C_OSZTALYCSOPORTID = @pFromOsztalyId
        AND (tcs.C_BELEPESDATUM <= @pDatumTol AND tcs.C_KILEPESDATUM >= @pDatumTol)
    END
  END

  IF @pIsTorolhetosegVizsgalat = 0
  BEGIN
    IF @pFromOsztalyId <> @pToOsztalyId
    BEGIN
      INSERT INTO #hibak
      SELECT 
         7 AS oka -- 'a tanuló már tagja az adott osztálynak'
        ,ocso.ID AS OsztalyId
        ,NULL AS OsztalybontottCsoportId
        ,IIF(tcs.C_BELEPESDATUM <= @pDatumTol, @pDatumTol, tcs.C_BELEPESDATUM) AS Datum
        ,NULL AS Darab
      FROM T_TANULOCSOPORT_OSSZES tcs
        INNER JOIN T_OSZTALYCSOPORT_OSSZES ocso ON ocso.ID = tcs.C_OSZTALYCSOPORTID
          AND ocso.TOROLT = 'F'
          AND (ocso.C_FELADATKATEGORIAID = @pFeladatKategoriaId OR @pFeladatKategoriaId IS NULL)
      WHERE tcs.TOROLT = 'F'
        AND tcs.C_TANEVID = @pTanevId
        AND tcs.C_TANULOID = @pTanuloId
        AND tcs.C_OSZTALYCSOPORTID = @pToOsztalyId
        AND (tcs.C_BELEPESDATUM <= @pDatumIg AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @pDatumIg))
    END
    ELSE
    BEGIN
      INSERT INTO #hibak
      SELECT 
         7 AS oka 
        ,ocso.ID AS OsztalyId
        ,NULL AS OsztalybontottCsoportId
        ,IIF(tcs.C_BELEPESDATUM <= @pDatumTol, @pDatumTol, tcs.C_BELEPESDATUM) AS Datum
        ,NULL AS Darab
      FROM T_TANULOCSOPORT_OSSZES tcs
        INNER JOIN T_OSZTALY o ON o.ID = tcs.C_OSZTALYCSOPORTID
          AND o.TOROLT = 'F'
        INNER JOIN T_OSZTALYCSOPORT_OSSZES ocso ON ocso.ID = tcs.C_OSZTALYCSOPORTID
          AND ocso.TOROLT = 'F'
          AND (ocso.C_FELADATKATEGORIAID = @pFeladatKategoriaId OR @pFeladatKategoriaId IS NULL)
      WHERE tcs.TOROLT = 'F'
        AND tcs.C_TANEVID = @pTanevId
        AND tcs.C_TANULOID = @pTanuloId
        AND tcs.C_OSZTALYCSOPORTID = @pToOsztalyId
        AND (tcs.C_BELEPESDATUM <= @pDatumIg AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @pDatumTol))
    END

    INSERT INTO #hibak
    SELECT 
       8 AS oka -- 'a tanuló már tagja a max számú osztálynak'
      ,NULL AS OsztalyId
      ,NULL AS OsztalybontottCsoportId
      ,tcs.MinBesorolasDatum AS Datum
      ,NULL AS Darab
    FROM (
      SELECT 
         COUNT(tcs.ID) AS Darab
        ,ISNULL(IIF(@pIsCheckMaxOsztalyReturnKilepes = 1, MIN(tcs.C_KILEPESDATUM), MIN(tcs.C_BELEPESDATUM)), @pDatumTol) AS MinBesorolasDatum
      FROM T_TANULOCSOPORT_OSSZES tcs
        INNER JOIN T_OSZTALY o ON o.ID = tcs.C_OSZTALYCSOPORTID
          AND o.TOROLT = 'F'
        INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID
          AND ocs.TOROLT = 'F'
          AND (ocs.C_FELADATKATEGORIAID = @pFeladatKategoriaId OR @pFeladatKategoriaId IS NULL)
      WHERE tcs.TOROLT = 'F'
        AND tcs.C_TANEVID = @pTanevId
        AND tcs.C_TANULOID = @pTanuloId
        AND (tcs.C_BELEPESDATUM <= @pDatumIg AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= @pDatumTol))
        AND tcs.C_OSZTALYCSOPORTID <> @pFromOsztalyId
    ) tcs
      INNER JOIN T_OKTNEVELESIKATEGORIA_OSSZES onk ON onk.ID = @pFeladatKategoriaId
        AND onk.TOROLT = 'F'
        AND onk.C_ALTANEVID = @pTanevId
    WHERE tcs.Darab >= onk.C_FELADATCSOPORTTANULOOSZTALYK
  END

  SELECT * FROM #hibak
END
GO