108 lines
8.4 KiB
Transact-SQL
108 lines
8.4 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
DROP PROCEDURE IF EXISTS dbo.uspGenerateTeljesOrarend;
|
|
GO
|
|
|
|
CREATE PROCEDURE dbo.uspGenerateTeljesOrarend
|
|
@pIntezmenyId int
|
|
,@pTanevId int
|
|
,@pOrarendioraId int = null
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
DELETE FROM T_ORARENDTELJES
|
|
WHERE C_INTEZMENYID = @pIntezmenyId
|
|
AND C_TANEVID = @pTanevId
|
|
AND C_ORARENDIORAID = CASE WHEN @pOrarendioraId IS NULL THEN C_ORARENDIORAID ELSE @pOrarendioraId END;
|
|
|
|
INSERT INTO T_ORARENDTELJES(
|
|
C_INTEZMENYID
|
|
,C_TANEVID
|
|
,C_DATUM
|
|
,C_ORARENDIORAID
|
|
,C_ORARENDIORAGROUPID
|
|
,C_OSZTALYCSOPORTID
|
|
,C_FELADATKATEGORIAID
|
|
,C_TANTARGYID
|
|
,C_ISOSZTALYORARENDBENEMLATSZIK
|
|
,C_TANARID
|
|
,C_TEREMID
|
|
,C_ORASZAM
|
|
,C_CSENGETESIRENDID
|
|
,C_BONTOTT
|
|
,C_HETIREND
|
|
,C_HETNAPJA
|
|
,C_HETSORSZAMA
|
|
,C_NAPDATUMA
|
|
,C_ORAERVENYESSEGKEZDETE
|
|
,C_ORAERVENYESSEGVEGE
|
|
,C_ORAKEZDETE
|
|
,C_ORAVEGE
|
|
,C_EGYEDINAP
|
|
,C_KOZPONTIORAGROUPID
|
|
,C_KOZPONTILAGTOROLTORAGROUPID
|
|
,C_HELYETTESTANAROKID
|
|
,C_HELYETTESITESID
|
|
,CREATED
|
|
,LASTCHANGED
|
|
)
|
|
SELECT
|
|
orr.C_INTEZMENYID
|
|
,orr.C_TANEVID
|
|
,orr.C_DATUM
|
|
,orr.C_ORARENDIORAID
|
|
,oo.C_ORARENDIORAGROUPID
|
|
,oo.C_OSZTALYCSOPORTID
|
|
,ocs.C_FELADATKATEGORIAID
|
|
,oo.C_TANTARGYID
|
|
,tt.C_ISOSZTALYORARENDBENEMLATSZIK
|
|
,oo.C_TANARID
|
|
,oo.C_TEREMID
|
|
,oo.C_ORASZAM
|
|
,oo.C_CSENGETESIRENDID
|
|
,oo.C_BONTOTT
|
|
,oo.C_HETIREND
|
|
,oo.C_HETNAPJA
|
|
,nn.C_HETSORSZAMA
|
|
,nn.C_NAPDATUMA
|
|
,oo.C_ORAERVENYESSEGKEZDETE
|
|
,oo.C_ORAERVENYESSEGVEGE
|
|
,oo.C_ORAKEZDETE
|
|
,oo.C_ORAVEGE
|
|
,oo.C_EGYEDINAP
|
|
,oo.C_KOZPONTIORAGROUPID
|
|
,oo.C_KOZPONTILAGTOROLTORAGROUPID
|
|
,hisz.C_HELYETTESTANAROKID
|
|
,hisz.ID AS C_HELYETTESITESID
|
|
,oo.CREATED
|
|
,ISNULL(oo.LASTCHANGED, oo.CREATED) AS LASTCHANGED
|
|
FROM T_ORAREND_OSSZES orr
|
|
INNER JOIN T_ORARENDIORA_OSSZES oo ON oo.ID = orr.C_ORARENDIORAID
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON oo.C_OSZTALYCSOPORTID = ocs.ID AND ocs.TOROLT = 'F'
|
|
INNER JOIN T_NAPTARINAP_OSSZES nn ON nn.C_NAPDATUMA = orr.C_DATUM AND nn.C_INTEZMENYID = orr.C_INTEZMENYID AND nn.C_TANEVID = orr.C_TANEVID AND nn.TOROLT = 'F'
|
|
LEFT JOIN T_HELYETTESITESIIDOSZAK_OSSZES hisz ON oo.ID = hisz.C_HELYETTESITETTORARENDID AND hisz.C_HELYETTESITESNAPJA = nn.C_NAPDATUMA AND hisz.TOROLT = 'F'
|
|
LEFT JOIN T_TANTARGY_OSSZES tt on tt.ID = oo.C_TANTARGYID AND tt.TOROLT = 'F'
|
|
WHERE oo.C_INTEZMENYID = @pIntezmenyId
|
|
AND oo.C_TANEVID = @pTanevId
|
|
AND oo.ID = CASE WHEN @pOrarendioraId IS NULL THEN oo.ID ELSE @pOrarendioraId END
|
|
AND oo.TOROLT = 'F';
|
|
|
|
UPDATE ot
|
|
SET ot.C_MAXORASZAM = ISNULL(o.C_MAXORASZAM, 12)
|
|
FROM T_ORARENDTELJES ot
|
|
LEFT JOIN (
|
|
SELECT C_INTEZMENYID, C_TANEVID, C_DATUM, MAX(C_ORASZAM) AS C_MAXORASZAM
|
|
FROM T_ORARENDTELJES t
|
|
WHERE t.C_INTEZMENYID = @pIntezmenyId
|
|
AND t.C_TANEVID = @pTanevId
|
|
GROUP BY C_INTEZMENYID, C_TANEVID, C_DATUM
|
|
) o ON o.C_INTEZMENYID = ot.C_INTEZMENYID
|
|
AND o.C_TANEVID = ot.C_TANEVID
|
|
AND o.C_DATUM = ot.C_DATUM
|
|
WHERE ot.C_INTEZMENYID = @pIntezmenyId
|
|
AND ot.C_TANEVID = @pTanevId
|
|
AND ISNULL(ot.C_MAXORASZAM, 0) <> ISNULL(o.C_MAXORASZAM, 12);
|
|
GO |