kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20180523101450_OM_632/uspGetOsztalycsoportTantargyiMulasztasai.sql

IF OBJECT_ID('dbo.uspGetOsztalycsoportTantargyiMulasztasai') IS NOT NULL BEGIN
  DROP PROCEDURE dbo.uspGetOsztalycsoportTantargyiMulasztasai
END
GO

CREATE PROCEDURE dbo.uspGetOsztalycsoportTantargyiMulasztasai 
   @pOsztalyId int
  ,@pTantargyId int
  ,@pTanevid int
  ,@pIntezmenyId int
  ,@pCsakMegtartott bit /*0 - nem, 1 - igen */
AS
BEGIN
  SET NOCOUNT ON;
  
  DECLARE @createTmpTable bit = 0;

  IF OBJECT_ID('tempdb..#OsztalyTanuloi') IS NOT NULL BEGIN
    DROP TABLE #OsztalyTanuloi
  END 
  CREATE TABLE #OsztalyTanuloi (
     TanuloId int PRIMARY KEY
    ,Belepes datetime
    ,Kilepes datetime
    --,CONSTRAINT PK_OsztalyTanuloi PRIMARY KEY (TanuloId)
  )

  INSERT INTO #OsztalyTanuloi (TanuloId/*, Belepes, Kilepes*/)
  SELECT DISTINCT C_TANULOID--, C_BELEPESDATUM, ISNULL(C_KILEPESDATUM, '21000101') 
  FROM T_TANULOCSOPORT_OSSZES 
  WHERE C_OSZTALYCSOPORTID = @pOsztalyId
    AND C_TANEVID = @pTanevId
    AND TOROLT = 'F'

  IF OBJECT_ID('tempdb..#Result') IS NULL BEGIN
	SET @createTmpTable = 1;
    CREATE TABLE #Result (
     TanuloId int PRIMARY KEY
    ,Mulasztas int
    ,MegtartottOra int
    ,HatralevoOra int
    --,CONSTRAINT PK_Result PRIMARY KEY (TanuloId)
  )
  END 

  INSERT INTO #Result (     
     TanuloId
    ,Mulasztas
    ,MegtartottOra
    ,HatralevoOra
  ) SELECT 
     ot.TanuloId
    ,SUM(IIF(C_TIPUS = 1500, 1, 0)) + (SUM(IIF(C_TIPUS = 1499, C_KESESPERCBEN, 0)) / 45) AS Mulasztas
    ,COUNT(DISTINCT tn.ID) AS MegtartottOra
    ,NULL AS HatralevoOra
  FROM T_TANULOCSOPORT_OSSZES tcs
    INNER JOIN #OsztalyTanuloi ot ON tcs.C_TANULOID = ot.TanuloId
    INNER JOIN T_TANITASIORA_OSSZES tn 
       ON tn.C_OSZTALYCSOPORTID = tcs.C_OSZTALYCSOPORTID 
      AND tn.C_BEJEGYZESIDEJE >= tcs.C_BELEPESDATUM 
      AND tn.C_BEJEGYZESIDEJE < ISNULL(tcs.C_KILEPESDATUM, '21000101')
    LEFT JOIN T_TANULOMULASZTAS_OSSZES tm 
       ON tm.C_TANITASIORAKID = tn.ID 
      AND tm.C_ORATANULOIID = ot.TanuloId 
      AND tm.C_TIPUS BETWEEN 1499 AND 1500
  WHERE tn.C_TANTARGYID = @pTantargyId
  GROUP BY ot.TanuloId

  IF @pCsakMegtartott = 1 BEGIN
	IF(@createTmpTable = 1)
		BEGIN
			SELECT
				TanuloId
				,Mulasztas
				,MegtartottOra
				,HatralevoOra
			FROM #Result
		END
  END 
  ELSE BEGIN
    CREATE TABLE #OrarendTable (
  		 Id int
      ,ORARENDIID INT
      ,Datum datetime
      ,Bontott char(1)
      ,Hetirend int
      ,HetNapja int
      ,HetSorszam int
      ,ErvenyessegKezdete datetime
      ,ErvenyessegVege datetime
      ,OraKezdete datetime
      ,OraVege datetime
      ,Oraszam int
      ,TargyNev nvarchar(300)
      ,TargyNevForMobile nvarchar(300)
      ,TargykategoriaID int
      ,Megtartott char(1)
      ,CsengetesiRendOraID int
      ,CsengetesiRendID int
      ,MaxNapiOraszam int
      ,OsztalyNev nvarchar(255)
      ,TanevRendOsztalyCsoportId int
      ,MegjelenesOka nvarchar(300)
      ,TanarNev nvarchar(255)
      ,TeremNev nvarchar(255)
      ,OraTipus nvarchar(200)
      ,Hianyzas char(1)
      ,Keses char(1)
      ,Ures char(1)
      ,HelyettesitoTanarID int
      ,HelyettesitoTanarNev nvarchar(255)
      ,TanarID int
      ,HelyettesitesId int
      ,AdminAltalKiirt  char(1)
      ,GroupId int
      ,Tema nvarchar(max)
      ,TantargyId int
      ,OsztCsopId int
	  ,INDEX IX_TanevRendOsztalyCsoportId CLUSTERED (TanevRendOsztalyCsoportId)
    )
    
    DECLARE @idoszakKezdete datetime = GETDATE()
    DECLARE @idoszakVege datetime
    
    SELECT @idoszakVege = C_NAPDATUMA 
    FROM T_NAPTARINAP
    WHERE C_NAPTIPUSA = 1395 
      AND C_TANEVID = @pTanevId
    
  	INSERT INTO #OrarendTable (
  		Id,ORARENDIID,Datum,Bontott,Hetirend,HetNapja,HetSorszam,ErvenyessegKezdete,ErvenyessegVege,OraKezdete,OraVege,Oraszam,
  		TargyNev,TargyNevForMobile,TargykategoriaID,Megtartott,CsengetesiRendOraID,CsengetesiRendID,MaxNapiOraszam,OsztalyNev,TanevRendOsztalyCsoportId,
  		MegjelenesOka,TanarNev,TeremNev,OraTipus,Hianyzas,Keses,Ures,HelyettesitoTanarID,HelyettesitoTanarNev,TanarID,AdminAltalKiirt,GroupId,Tema,TantargyId,OsztCsopId)
  	EXEC sp_GetOrarend 
       @pIntezmenyId = @pIntezmenyId
      ,@pTanevId = @pTanevId
      ,@pIdoszakKezdete = @idoszakKezdete
      ,@pIdoszakVege = @idoszakVege 
      ,@pTanarId = NULL
      ,@pOsztalyCsoportId = @pOsztalyId
      ,@pTanuloId = NULL
      ,@pCsakOrarendiOrak = 0
      ,@pIsNapirend  = 0 
	
	UPDATE r
	SET
      r.HatralevoOra = ISNULL(o.HatralevoOra, 0)
    FROM #Result r
    LEFT JOIN (
      SELECT
        ot.TanuloId, COUNT(1) AS HatralevoOra
      FROM T_TANULOCSOPORT tcs 
        INNER JOIN #OrarendTable oo ON tcs.C_OSZTALYCSOPORTID = oo.TanevRendOsztalyCsoportId
        INNER JOIN #OsztalyTanuloi ot ON tcs.C_TANULOID = ot.TanuloId AND oo.Datum >= tcs.C_BELEPESDATUM AND oo.Datum < ISNULL(tcs.C_KILEPESDATUM, '21000101') 
      WHERE oo.TantargyId = @pTantargyId
      GROUP BY ot.TanuloId
    ) o ON o.TanuloId = r.TanuloId

	IF(@createTmpTable = 1)
		BEGIN
			SELECT
				TanuloId
				,Mulasztas
				,MegtartottOra
				,HatralevoOra
			FROM #Result
		END
  END 

  IF OBJECT_ID('tempdb..#OsztalyTanuloi') IS NOT NULL BEGIN
    DROP TABLE #OsztalyTanuloi
  END  
  IF OBJECT_ID('tempdb..#OrarendTable') IS NOT NULL BEGIN
    DROP TABLE #OrarendTable
  END    
  
END
GO