kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20200127163504_KRETA2_2311/sp_GetOraSorszamByOsztaly.sql

DROP PROCEDURE IF EXISTS sp_GetOraSorszamByOsztaly
GO

CREATE PROCEDURE sp_GetOraSorszamByOsztaly
   @osztalyCsoportId int
  ,@datum date
  ,@intezmenyId int
  ,@tanevId int
AS
BEGIN
  
  SET NOCOUNT ON;
  DECLARE 
     @tanarSzamit bit
    ,@osztalybontasEgybe bit
    ,@osztalybontasCsoport bit
    ,@osszerendelesId int 
    ,@kezdoErtek int

  SET @tanarSzamit = dbo.fnGetRendszerbeallitasEnumBool(3075, @intezmenyId, @tanevId)  
  SET @osztalybontasEgybe = dbo.fnGetRendszerbeallitasEnumBool(3078, @intezmenyId, @tanevId)
  SET @osztalybontasCsoport = 0
  
  IF EXISTS (SELECT 1 FROM T_CSOPORT_OSSZES WHERE TOROLT='F' AND ((ID = @osztalyCsoportId AND C_OSZTALYBONTASID IS NOT NULL) OR C_OSZTALYBONTASID = @osztalyCsoportId)) 
  BEGIN
    SET @osztalybontasCsoport = 1
  END
  
  IF (@osztalybontasEgybe = 1 AND @osztalybontasCsoport = 1)
  BEGIN
    CREATE TABLE #kapcsolodoOsztalyCsoportok (OsztalyCsoportId int)
    INSERT INTO #kapcsolodoOsztalyCsoportok (OsztalyCsoportId)
    SELECT cs.ID
    FROM T_CSOPORT_OSSZES cs
    WHERE cs.C_OSZTALYBONTASID = (SELECT scs.C_OSZTALYBONTASID FROM T_CSOPORT scs WHERE scs.ID = @osztalyCsoportId) AND cs.TOROLT='F'
    UNION  
    SELECT cs.C_OSZTALYBONTASID 
    FROM T_CSOPORT_OSSZES cs
    WHERE cs.ID = @osztalyCsoportId AND cs.TOROLT='F'
    UNION
    SELECT cs.ID
    FROM T_CSOPORT_OSSZES cs
    WHERE cs.C_OSZTALYBONTASID = @osztalyCsoportId AND cs.TOROLT='F'
    UNION
    SELECT @osztalyCsoportId
  END

  SELECT 
    @osszerendelesId = h.ID 
  FROM T_ORASORSZAMOZASHALMAZ_OSSZES h
    INNER JOIN T_ORASORSZAMOZASHALMAZOSSZEREN_OSSZES oh ON h.ID = oh.C_ORASORSZAMOZASHALMAZID
      AND oh.C_OSZTALYCSOPORTID = @osztalycsoportId
      AND oh.TOROLT='F'
  WHERE h.TOROLT='F'
  --Van összerendelés
  IF @osszerendelesId IS NOT NULL BEGIN
    SELECT 
       @tanarSzamit = IIF(C_KOZOSORASORSZAMOZASELTEROTAN = 'T',1,0)
      ,@kezdoErtek = ISNULL(C_KEZDOERTEK-1,0)
    FROM T_ORASORSZAMOZASHALMAZ_OSSZES 
    WHERE ID = @osszerendelesId AND TOROLT='F'
    
      IF @tanarSzamit = 1 BEGIN
          SELECT 
             ID
            ,ROW_NUMBER() OVER(PARTITION BY C_TANARID,C_TANTARGYID ORDER BY C_ORAKEZDETE,C_NEV)+IIF(C_TANTARGYID = -1, @kezdoErtek, 0) OraSorszam
          FROM(
            SELECT
               tao.ID
              ,tao.C_ORAKEZDETE
              ,tao.C_TANTARGYID
              ,ocs.C_NEV
              ,tao.C_TANARID
            FROM T_TANITASIORA_OSSZES tao
              INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = tao.C_OSZTALYCSOPORTID AND ocs.TOROLT='F'
            WHERE tao.C_OSZTALYCSOPORTID = @osztalyCsoportId
              AND tao.C_DATUM <= @datum
              AND tao.C_SORSZAMOZANDO = 'T'
              AND tao.TOROLT='F'
              AND tao.C_MEGTARTOTT = 'T'
              AND NOT EXISTS (
                SELECT 1 
                FROM T_ORASORSZAMOZASHALMAZOSSZEREN_OSSZES
                WHERE C_ORASORSZAMOZASHALMAZID = @osszerendelesId
                  AND C_TANTARGYID = tao.C_TANTARGYID 
                  AND C_OSZTALYCSOPORTID = tao.C_OSZTALYCSOPORTID
                  AND TOROLT='F'
                )
            UNION ALL
            SELECT
               tao.ID
              ,tao.C_ORAKEZDETE
              ,-1 as C_TANTARGYID
              ,ocs.C_NEV
              ,tao.C_TANARID
            FROM T_ORASORSZAMOZASHALMAZOSSZEREN_OSSZES oh
              INNER JOIN T_TANITASIORA_OSSZES tao ON tao.C_TANTARGYID = oh.C_TANTARGYID AND tao.C_OSZTALYCSOPORTID = oh.C_OSZTALYCSOPORTID
                AND tao.C_SORSZAMOZANDO = 'T' AND tao.C_MEGTARTOTT ='T' AND tao.TOROLT='F' AND tao.C_DATUM <= @datum
              INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.TOROLT='F' AND ocs.ID = tao.C_OSZTALYCSOPORTID AND ocs.TOROLT='F'
            WHERE oh.C_ORASORSZAMOZASHALMAZID = @osszerendelesId
              AND oh.TOROLT='F'
          ) MindenOra
      END
      ELSE BEGIN
          SELECT 
             ID
            ,ROW_NUMBER() OVER(PARTITION BY C_TANTARGYID ORDER BY C_ORAKEZDETE,C_NEV)+IIF(C_TANTARGYID = -1, @kezdoErtek, 0) OraSorszam
          FROM(
            SELECT
               tao.ID
              ,tao.C_ORAKEZDETE
              ,tao.C_TANTARGYID
              ,ocs.C_NEV
            FROM T_TANITASIORA_OSSZES tao
              INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = tao.C_OSZTALYCSOPORTID AND ocs.TOROLT='F'
            WHERE tao.C_OSZTALYCSOPORTID = @osztalycsoportId 
              AND tao.C_DATUM <= @datum
              AND tao.C_SORSZAMOZANDO = 'T'
              AND tao.C_MEGTARTOTT = 'T'
              AND tao.TOROLT='F'
              AND NOT EXISTS (
                SELECT 1 
                FROM T_ORASORSZAMOZASHALMAZOSSZEREN_OSSZES
                WHERE C_ORASORSZAMOZASHALMAZID = @osszerendelesId
                  AND C_TANTARGYID = tao.C_TANTARGYID 
                  AND C_OSZTALYCSOPORTID = tao.C_OSZTALYCSOPORTID
                  AND TOROLT='F'
                )
            UNION ALL
            SELECT
               tao.ID
              ,tao.C_ORAKEZDETE
              ,-1 as C_TANTARGYID
              ,ocs.C_NEV
            FROM T_ORASORSZAMOZASHALMAZOSSZEREN_OSSZES oh
              INNER JOIN T_TANITASIORA_OSSZES tao ON tao.C_TANTARGYID = oh.C_TANTARGYID AND tao.C_OSZTALYCSOPORTID = oh.C_OSZTALYCSOPORTID
                AND tao.C_SORSZAMOZANDO = 'T' AND tao.C_MEGTARTOTT ='T'  AND tao.TOROLT='F' AND tao.C_DATUM <= @datum
              INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.TOROLT='F' AND ocs.ID = tao.C_OSZTALYCSOPORTID AND ocs.TOROLT='F'
            WHERE oh.C_ORASORSZAMOZASHALMAZID = @osszerendelesId AND oh.TOROLT='F'
          ) MindenOra
    END
  END
  ELSE 
  BEGIN
    IF @osztalybontasEgybe = 0 OR @osztalybontasCsoport = 0 BEGIN
      IF @tanarSzamit = 1 BEGIN
	      SELECT
           t.ID AS TanitasiOraId
          ,ROW_NUMBER() OVER (PARTITION BY t.C_TANARID, t.C_TANTARGYID ORDER BY t.C_ORAKEZDETE, C_BEJEGYZESIDEJE) AS OraSorszam
        FROM T_TANITASIORA_OSSZES t
        WHERE t.C_MEGTARTOTT = 'T'
          AND t.C_SORSZAMOZANDO = 'T'
          AND t.C_DATUM <= @datum
	      AND t.TOROLT = 'F'
          AND t.C_INTEZMENYID = @intezmenyid
          AND t.C_TANEVID = @tanevId
          AND t.C_OSZTALYCSOPORTID = @osztalyCsoportId
      END 
      ELSE BEGIN
        SELECT 
           t.ID AS TanitasiOraId
          ,ROW_NUMBER() OVER (PARTITION BY t.C_TANTARGYID ORDER BY C_ORAKEZDETE, C_BEJEGYZESIDEJE) AS OraSorszam
        FROM T_TANITASIORA_OSSZES t
        WHERE t.C_MEGTARTOTT = 'T' 
          AND t.C_SORSZAMOZANDO = 'T'
          AND t.C_OSZTALYCSOPORTID = @osztalyCsoportId
          AND t.C_DATUM <= @datum    
          AND t.TOROLT = 'F'
          AND t.C_INTEZMENYID = @intezmenyid
          AND t.C_TANEVID = @tanevId
      END
    END 
    ELSE BEGIN
      IF @tanarSzamit = 1 BEGIN
        SELECT 
           t.ID AS TanitasiOraId
          ,ROW_NUMBER() OVER (PARTITION BY t.C_TANTARGYID,t.C_TANARID ORDER BY C_ORAKEZDETE, C_BEJEGYZESIDEJE) AS OraSorszam
        FROM T_TANITASIORA_OSSZES t
        WHERE t.C_OSZTALYCSOPORTID IN (SELECT ID FROM #kapcsolodoOsztalyCsoportok)
          AND t.C_DATUM <= @datum
          AND t.C_SORSZAMOZANDO = 'T'
          AND t.C_MEGTARTOTT = 'T'
	        AND t.TOROLT = 'F'
          AND t.C_INTEZMENYID = @intezmenyid
          AND t.C_TANEVID = @tanevId
      END
      ELSE BEGIN
        SELECT 
           t.ID AS TanitasiOraId
          ,ROW_NUMBER() OVER (PARTITION BY t.C_TANTARGYID ORDER BY C_ORAKEZDETE, C_BEJEGYZESIDEJE) AS OraSorszam
        FROM T_TANITASIORA_OSSZES t
        WHERE t.C_OSZTALYCSOPORTID IN (SELECT ID FROM #kapcsolodoOsztalyCsoportok)
          AND t.C_DATUM <= @datum
          AND t.C_SORSZAMOZANDO = 'T'
          AND t.C_MEGTARTOTT = 'T'
	        AND t.TOROLT = 'F'
          AND t.C_INTEZMENYID = @intezmenyid
          AND t.C_TANEVID = @tanevId
      END
    END
  END
END
GO