kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20200326104900_KRETA2_3368/uspGetTanulokSzamonkeresElorejelzesei.sql

-- =============================================
-- Description:  <Előszedjük a tanuló értékeléseket a megadott paraméterek alapján>
-- =============================================
DROP PROCEDURE IF EXISTS uspGetTanulokSzamonkeresElorejelzesei
GO

CREATE PROCEDURE uspGetTanulokSzamonkeresElorejelzesei
   @IntezmenyId int
  ,@TanevId int
  ,@pDatum datetime
  ,@pOraGroupId int
  ,@pOsztalyCsoportId int
  ,@pNapiSzamonkeresLimit int
  ,@pErtekelesModokLimithez nvarchar (max)
AS BEGIN
  SET NOCOUNT ON;

  DECLARE @kategoriaId int = (SELECT C_FELADATKATEGORIAID FROM T_OSZTALYCSOPORT_OSSZES WHERE ID = @pOsztalyCsoportId)

  CREATE TABLE #OsztalyCsoport (Id int PRIMARY KEY)

  CREATE TABLE #Tanulo (
     Id int PRIMARY KEY
    ,Nev nvarchar (300)
  )

  CREATE TABLE #NaptariNap (
     Datum datetime
    ,OsztalyCsoportId int
    ,IsEgyediNap char(1) COLLATE database_default
    ,PRIMARY KEY (Datum, OsztalyCsoportId)
  )

  CREATE TABLE #TanuloSzamonkeresei (
     Id int
    ,Nev nvarchar (300)
    ,SzamonkeresekSzama int
    ,OsztalyNev nvarchar (300)
    ,PRIMARY KEY (Id, OsztalyNev))

  INSERT INTO #OsztalyCsoport (Id)
  SELECT ID
  FROM  dbo.fnGetKapcsolodoOsztalycsoportokByDate (@pOsztalyCsoportId, @pDatum, @pDatum)

  INSERT INTO #Tanulo (
     Id
    ,Nev
  )
  SELECT
     fh.ID
    ,fh.C_NYOMTATASINEV AS Nev
  FROM T_TANULOCSOPORT tcs
    INNER JOIN T_FELHASZNALO fh ON fh.ID = tcs.C_TANULOID
  WHERE tcs.C_OSZTALYCSOPORTID = @pOsztalyCsoportId
    AND (tcs.C_BELEPESDATUM <= @pDatum AND (tcs.C_KILEPESDATUM > @pDatum OR tcs.C_KILEPESDATUM IS NULL))

  INSERT INTO #NaptariNap (
     Datum
    ,OsztalyCsoportId
    ,IsEgyediNap
  )
  SELECT
     tr.Datum
    ,tr.OsztalyId AS OsztalyCsoportId
    ,ISNULL(tro.IsEgyediNap, tr.IsEgyediNap) AS IsEgyediNap
  FROM (
    SELECT
       nn.C_NAPDATUMA AS Datum
      ,ocs.ID AS OsztalyId
      ,'F' AS IsEgyediNap
    FROM T_NAPTARINAP_OSSZES nn
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.C_TANEVID = @TanevId
        AND ocs.C_FELADATKATEGORIAID = @kategoriaId
    WHERE nn.C_TANEVID = @TanevId
      AND nn.TOROLT = 'F'
      AND nn.C_ORARENDINAP = 'T'
  ) tr
  LEFT JOIN (
    SELECT
       nn.C_NAPDATUMA AS Datum
      ,ocstr.C_OSZTALYCSOPORTID AS OsztalyId
      ,tr.C_EGYEDINAP AS IsEgyediNap
    FROM T_NAPTARINAP_OSSZES nn
      INNER JOIN T_TANEVRENDJE_OSSZES tr ON tr.C_DATUM = nn.C_NAPDATUMA
        AND tr.C_OSSZESCSOPORTRAVONATKOZIK = 'F'
        AND tr.TOROLT = 'F'
      INNER JOIN T_OSZTALYCSOPORT_TANEVRENDJE ocstr ON ocstr.C_TANEVRENDJEID = tr.ID
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = ocstr.C_OSZTALYCSOPORTID
        AND ocs.C_FELADATKATEGORIAID = @kategoriaId
      INNER JOIN T_NAPTIPUS_OSSZES nt ON nt.ID = tr.C_NAPTIPUSA
        AND nt.C_ALTANEVID = tr.C_TANEVID
        AND nt.TOROLT = 'F'
    WHERE nn.C_TANEVID = @TanevId
      AND nn.TOROLT = 'F'
      AND tr.C_ORARENDINAP = 'T'
  ) tro ON tr.Datum = tro.Datum
    AND tr.OsztalyId = tro.OsztalyId

  INSERT INTO  #TanuloSzamonkeresei (
     Id
    ,Nev
    ,SzamonkeresekSzama
    ,OsztalyNev)
  SELECT
     t.Id
    ,t.Nev
    ,COUNT(1) AS SzamonkeresekSzama
    ,ocso.C_NEV AS OsztalyNev
  FROM T_SZAMONKERESELOREJELZES szk
    INNER JOIN T_ORARENDIORA oo ON oo.C_ORARENDIORAGROUPID = szk.C_ORARENDIORAGROUPID
      AND szk.C_SZAMONKERESDATUMA >= oo.C_ORAERVENYESSEGKEZDETE
      AND szk.C_SZAMONKERESDATUMA <= oo.C_ORAERVENYESSEGVEGE
      AND oo.TOROLT = 'F'
    INNER JOIN #NaptariNap nn ON nn.IsEgyediNap = oo.C_EGYEDINAP
      AND nn.OsztalyCsoportId = oo.C_OSZTALYCSOPORTID
      AND nn.Datum = szk.C_SZAMONKERESDATUMA
    INNER JOIN #OsztalyCsoport ocs ON ocs.Id = oo.C_OSZTALYCSOPORTID
    INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_OSZTALYCSOPORTID = oo.C_OSZTALYCSOPORTID
      AND (tcs.C_BELEPESDATUM <= @pDatum AND (tcs.C_KILEPESDATUM > @pDatum OR tcs.C_KILEPESDATUM IS NULL))
    INNER JOIN #Tanulo t ON t.Id = tcs.C_TANULOID
    INNER JOIN T_TANULOCSOPORT tcs2 ON tcs2.C_TANULOID = t.ID
      AND (tcs2.C_BELEPESDATUM <= @pDatum AND (tcs2.C_KILEPESDATUM > @pDatum OR tcs2.C_KILEPESDATUM IS NULL))
    INNER JOIN T_OSZTALYCSOPORT ocso ON tcs2.C_OSZTALYCSOPORTID = ocso.ID
      AND C_FELADATKATEGORIAID = @kategoriaId
    INNER JOIN T_OSZTALY o ON o.ID = ocso.ID
  WHERE szk.C_SZAMONKERESDATUMA = @pDatum
    AND szk.C_SZAMONKERESMODID IN (SELECT value FROM STRING_SPLIT(@pErtekelesModokLimithez ,','))
    AND szk.C_INTEZMENYID = @IntezmenyId
    AND szk.C_TANEVID = @TanevId
    AND szk.TOROLT = 'F'
  GROUP BY
     t.id
    ,t.Nev
    ,ocso.C_NEV

  SELECT *
  FROM #TanuloSzamonkeresei
  WHERE SzamonkeresekSzama >= @pNapiSzamonkeresLimit

END
GO