﻿DISABLE TRIGGER ALL ON [dbo].[T_TANULOESEMENY];

SET NOCOUNT ON;
DECLARE @now DATETIME = GETDATE();

-- C_Tanitasioraid IS NULL = FELJEGYZÉSEK
WITH Cte AS (
  SELECT
    Te.Id AS Teid,
    Tte.C_Tanuloid AS tid,
    MIN(O.Id) AS oid
  FROM
    T_Tanuloesemeny AS Te
    INNER JOIN T_Tanulo_Tanuloesemeny AS Tte ON Te.Id = Tte.C_Tanuloesemenyid
    INNER JOIN T_Tanulocsoport AS Tcs ON (Tcs.Torolt = 'F' AND tte.C_Tanuloid = Tcs.C_Tanuloid AND Tcs.C_Belepesdatum <= Te.C_Esemenydatuma AND (Tcs.C_Kilepesdatum IS NULL OR Tcs.C_Kilepesdatum > Te.C_Esemenydatuma))
    INNER JOIN T_Osztaly AS O ON (O.Torolt = 'F' AND Tcs.C_Osztalycsoportid = O.Id)
  WHERE Te.Torolt = 'F'
    AND Te.C_Tipus NOT IN (5482, 5483)
    AND Te.C_Tanitasioraid IS NULL
  GROUP BY Te.Id, tte.C_TANULOID
  HAVING COUNT(1) = 1)
UPDATE Te
SET
  C_Rogziteskoriosztalyid = cte.oid,
  Serial = Te.Serial + 1,
  Modifier = NULL,
  Lastchanged = @Now
FROM
  T_Tanuloesemeny AS Te
  INNER JOIN Cte ON Te.Id = Cte.Teid
WHERE
    Te.C_Rogziteskoriosztalyid IS NULL;

-- C_Tanitasioraid IS NOT NULL = GYORSFELJEGYZÉSEK
WITH Cte AS (
    SELECT
      Te.Id AS Teid,
      Tte.C_Tanuloid AS tid,
      MIN(O.Id) AS oid,
      ocs.C_Feladatkategoriaid AS fkid
    FROM
      T_Tanuloesemeny AS Te
      INNER JOIN T_Tanulo_Tanuloesemeny AS Tte ON Te.Id = Tte.C_Tanuloesemenyid
      INNER JOIN T_Tanitasiora AS tto ON (tto.Torolt = 'F' AND Te.C_Tanitasioraid = Tto.Id)
      INNER JOIN T_Osztalycsoport AS ocs ON (ocs.Torolt = 'F' AND tto.C_Osztalycsoportid = ocs.Id)
      INNER JOIN T_Tanulocsoport AS tcs ON (tcs.Torolt = 'F' AND tcs.C_Osztalycsoportid = ocs.Id AND tte.C_Tanuloid = tcs.C_Tanuloid)
      INNER JOIN T_Tanulocsoport AS tcs2 ON (tcs2.Torolt = 'F' AND tcs2.C_TANULOID = tcs.C_TANULOID)
      INNER JOIN T_Osztalycsoport AS ocs2 ON (ocs2.Torolt = 'F' AND ocs2.Id = tcs2.C_Osztalycsoportid AND ocs.C_Feladatkategoriaid = ocs2.C_Feladatkategoriaid)
      INNER JOIN T_Osztaly AS O ON (O.Torolt = 'F' AND ocs2.Id = O.Id)
    WHERE Te.Torolt = 'F'
      AND Te.C_Tipus NOT IN (5482, 5483)
      AND Te.C_Tanitasioraid IS NOT NULL
    GROUP BY Te.Id, tte.C_TANULOID, ocs.C_Feladatkategoriaid
    HAVING COUNT(1) = 1)
UPDATE Te
SET
  C_Rogziteskoriosztalyid = cte.oid,
  Serial = Te.Serial + 1,
  Modifier = NULL,
  Lastchanged = @Now
FROM
  T_Tanuloesemeny AS Te
  INNER JOIN Cte ON Te.Id = Cte.Teid
WHERE
    Te.C_ROGZITESKORIOSZTALYID IS NULL;

ENABLE TRIGGER ALL ON [dbo].[T_TANULOESEMENY];