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

DROP PROCEDURE IF EXISTS dbo.uspGetHelyettesitesekAjanlasa
GO

CREATE PROCEDURE [dbo].[uspGetHelyettesitesekAjanlasa]
   @intezmenyId int
  ,@tanevId int
  ,@pHelyettesitettOrarendiOraId int
  ,@pHelyettesitesDatuma DATE
  ,@pSzakszeruId int = 1494
  ,@pNemSzakszeruId int = 1495
  ,@pOsszevonas int = 1496
  ,@pFokozatNa int = 3040
AS
BEGIN
SET NOCOUNT ON;
  
  DECLARE @OraKezdet DATETIME; DECLARE @Oraveg DATETIME;
  DECLARE @helyettesitettTanarId INT; DECLARE @Tulora CHAR(1);
  SELECT @OraKezdet = C_ORAKEZDETE, @Oraveg = C_ORAVEGE , @helyettesitettTanarId =  C_TANARID, @Tulora = C_TULORA FROM T_ORARENDIORA WHERE ID = @pHelyettesitettOrarendiOraId
  
  DECLARE @Datum DATETIME = (SELECT CAST(@pHelyettesitesDatuma AS date))
  DECLARE @DatumVeg DATETIME = (SELECT DATEADD(DAY, 1, (SELECT CAST(@pHelyettesitesDatuma AS date))))

  DECLARE @KezdoDateTime DATETIME = (SELECT (@OraKezdet + CAST(@pHelyettesitesDatuma AS datetime)))
  DECLARE @VegeDateTime DATETIME = (SELECT (@Oraveg + CAST(@pHelyettesitesDatuma AS datetime)))

  CREATE TABLE #OrarendTable (
     Id int
    ,ORARENDIID int
    ,OraKezdete datetime
    ,OraVege datetime
    ,Megtartott char(1)
    ,OsztalyNev nvarchar(255)
    ,HelyettesitoTanarID int
    ,HelyettesitesId int
    ,TanarID int
    ,TantargyId int
    ,OsztCsopId int
  )

  CREATE TABLE #HelyettesitoTanarok (
     ID int
    ,TanarId int
    ,HelyettesitesTipusId int
    ,TantargyId int
    ,OsztalyCsoportId int
    ,TanarNev nvarchar(550)
    ,TanarOktatasiAzonosito nvarchar(40)
    ,TantargyNev nvarchar(510)
    ,OsztalycsoportNev nvarchar(255)
    ,HelyettesitettOrarendiOraId int
    ,HelyettesitesDatuma datetime
    ,Tulora char(1)
  )
  
  INSERT INTO #OrarendTable (
     Id
    ,ORARENDIID
    ,OraKezdete
    ,OraVege
    ,Megtartott
    ,OsztalyNev
    ,HelyettesitoTanarID
    ,HelyettesitesId
    ,TanarID
    ,TantargyId
    ,OsztCsopId
  )
  EXEC sp_GetOrarend 
     @pIntezmenyId = @IntezmenyId
    ,@pTanevId = @TanevId
    ,@pIdoszakKezdete = @Datum
    ,@pIdoszakVege = @DatumVeg
    ,@pTanarId = NULL
    ,@pIsHelyettesitesNelkul  = 0
    ,@pOsztalyCsoportId  =  NULL
    ,@pTanuloId = NULL
    ,@pTantargyId  = NULL
    ,@pTeremId = NULL
    ,@pCsakOrarendiOrak  = 0
    ,@pIsNapirend = NULL
    ,@pOszlopok = N'Id,ORARENDIID,OraKezdete,OraVege,Megtartott,OsztalyNev,HelyettesitoTanarID,HelyettesitesId,TanarID,TantargyId,OsztCsopId'

  INSERT INTO #HelyettesitoTanarok
  SELECT
     NULL
    ,f.ID
    ,CASE
      MAX(CASE
        WHEN ot.OraKezdete < @VegeDateTime AND  ot.OraVege > @KezdoDateTime THEN '3'
        WHEN ot.ID IS NULL AND ma.C_BESOROLASIFOKOZAT = @pFokozatNa THEN '2'
        WHEN ot.ID IS NULL AND ma.C_BESOROLASIFOKOZAT != @pFokozatNa THEN '1'
        WHEN ot.ID IS NOT NULL AND ot.ID = ot.ORARENDIID AND ot.HelyettesitesId IS NULL AND ma.C_BESOROLASIFOKOZAT = @pFokozatNa THEN '2'
        WHEN ot.ID IS NOT NULL AND ot.ID = ot.ORARENDIID AND ot.HelyettesitesId IS NULL AND ma.C_BESOROLASIFOKOZAT != @pFokozatNa THEN '1'
        WHEN ot.ID IS NOT NULL AND ot.ID != ot.ORARENDIID AND ot.Megtartott = 'T' THEN '3'
        WHEN ot.ID IS NOT NULL AND ot.ID != ot.ORARENDIID AND ot.Megtartott = 'F' AND ma.C_BESOROLASIFOKOZAT = @pFokozatNa THEN '2'
        WHEN ot.ID IS NOT NULL AND ot.ID != ot.ORARENDIID AND ot.Megtartott = 'F' AND ma.C_BESOROLASIFOKOZAT != @pFokozatNa THEN '1'
        WHEN ot.ID IS NOT NULL AND ot.ID = ot.ORARENDIID AND ot.HelyettesitesId IS NOT NULL AND a.ID = ot.TanarID AND ma.C_BESOROLASIFOKOZAT = @pFokozatNa THEN '2'
        WHEN ot.ID IS NOT NULL AND ot.ID = ot.ORARENDIID AND ot.HelyettesitesId IS NOT NULL AND a.ID = ot.TanarID AND ma.C_BESOROLASIFOKOZAT != @pFokozatNa THEN '1'
        WHEN ot.ID IS NOT NULL AND ot.ID = ot.ORARENDIID AND ot.HelyettesitesId IS NOT NULL AND a.ID = ot.HelyettesitoTanarID THEN '3'      
      END)
      WHEN 1 THEN @pSzakszeruId
      WHEN 2 THEN @pNemSzakszeruId
      WHEN 3 THEN @pOsszevonas
    END
    ,ot.TantargyId
    ,ot.OsztCsopId
    ,f.C_NYOMTATASINEV
    ,ISNULL(f.C_OKTATASIAZONOSITO, '-')
    ,t.C_NEV
    ,ot.OsztalyNev
    ,@pHelyettesitettOrarendiOraId
    ,@pHelyettesitesDatuma
    ,@Tulora
  FROM T_ALKALMAZOTT a
    INNER JOIN T_FELHASZNALO f ON f.ID = a.ID AND f.TOROLT = 'F'
    INNER JOIN T_MUNKAUGYIADATOK ma ON ma.C_ALKALMAZOTTID = a.ID 
      AND ma.C_BETOLTETLENALLASHELY = 'F' 
      AND ma.C_TARTOSHELYETTESITES = 'F' 
      AND ((C_ALKALMAZASKEZDETE <= @Datum OR C_ALKALMAZASKEZDETE IS NULL) AND (C_ALKALMAZASMEGSZUNESE >= @Datum OR C_ALKALMAZASMEGSZUNESE IS NULL))
    LEFT JOIN #OrarendTable ot ON (ot.TanarID = a.ID or ot.HelyettesitoTanarID = a.ID)
    LEFT JOIN T_TANTARGY t ON t.ID = ot.TantargyId AND t.TOROLT = 'F'
  WHERE a.TOROLT = 'F' AND a.C_ALTANEVID = @tanevId
    AND (TanarID <> @helyettesitettTanarId OR TanarID IS NULL)
    AND (ot.TantargyId IS NULL OR (ot.OraKezdete <= @VegeDateTime AND ot.OraVege >= @KezdoDateTime))
  GROUP BY f.ID, ot.TantargyId, ot.OsztCsopId,f.C_NYOMTATASINEV,f.C_OKTATASIAZONOSITO,t.C_NEV,ot.OsztalyNev, ot.Id

  CREATE TABLE #nincsOraja(AlkalmazottId int,TanarNev nvarchar(128),HelyettesitesTipusa int)
  INSERT INTO #HelyettesitoTanarok
  SELECT 
     NULL
    ,a.ID
    ,CASE 
      WHEN ma.C_BESOROLASIFOKOZAT = @pFokozatNa THEN @pNemSzakszeruId
      WHEN ma.C_BESOROLASIFOKOZAT != @pFokozatNa THEN @pSzakszeruId
     END HelyettesitesTipusId
    ,NULL
    ,NULL
    ,fh.C_NYOMTATASINEV
    ,ISNULL(fh.C_OKTATASIAZONOSITO, '-')
    ,NULL
    ,NULL
    ,@pHelyettesitettOrarendiOraId
    ,@pHelyettesitesDatuma
    ,@Tulora AS Tulora
  FROM T_ALKALMAZOTT a
    INNER JOIN T_FELHASZNALO fh ON fh.ID = a.ID
    INNER JOIN T_MUNKAUGYIADATOK ma ON ma.C_ALKALMAZOTTID = a.ID 
    AND ma.C_BETOLTETLENALLASHELY = 'F' 
    AND ma.C_TARTOSHELYETTESITES = 'F' 
    AND ((C_ALKALMAZASKEZDETE <= @Datum OR C_ALKALMAZASKEZDETE IS NULL) AND (C_ALKALMAZASMEGSZUNESE >= @Datum OR C_ALKALMAZASMEGSZUNESE IS NULL))
  WHERE a.ID NOT IN(
    SELECT TanarId FROM #HelyettesitoTanarok 
    UNION 
    SELECT @helyettesitettTanarId
  )

  SELECT * FROM #HelyettesitoTanarok
  ORDER BY HelyettesitesTipusId, TanarNev, TanarOktatasiAzonosito

END
GO