125 lines
4.3 KiB
Transact-SQL
125 lines
4.3 KiB
Transact-SQL
-- =============================================
|
|
-- Author: Bata-Kovács Gábor
|
|
-- Create date: 2017.06.03.
|
|
-- Description: A Mûködési Hely adatainak frissítése a következõ tanévre
|
|
-- =============================================
|
|
|
|
IF OBJECT_ID('sp_FollowUpMukodesiHely') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_FollowUpMukodesiHely
|
|
END;
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_FollowUpMukodesiHely
|
|
@intezmenyId int,
|
|
@aktTanevId int,
|
|
@kovetkezoTanevId int,
|
|
@mukodesiHelyId int
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE
|
|
@kovetkezoMukodesiHelyId int = NULL
|
|
|
|
IF (@mukodesiHelyId <> 0)
|
|
BEGIN
|
|
SET @kovetkezoMukodesiHelyId = (
|
|
SELECT TOP 1 ID FROM T_MUKODESIHELY WHERE C_INTEZMENYID = @intezmenyId AND C_TANEVID = @kovetkezoTanevId AND ELOZOTANEVIREKORDID = @mukodesiHelyId
|
|
)
|
|
END
|
|
|
|
IF (@mukodesiHelyId = 0)
|
|
BEGIN
|
|
INSERT INTO T_MUKODESIHELY (
|
|
C_AJTO
|
|
,C_CIME
|
|
,C_EMAILCIM
|
|
,C_EMELET
|
|
,C_FAX
|
|
,C_HAZSZAM
|
|
,C_INTEZMENYADATOKID
|
|
,C_INTEZMENYID
|
|
,C_IRANYITOSZAM
|
|
,C_KOZTERULETJELLEG
|
|
,C_KOZTERULETNEV
|
|
,C_MUKODESIHELYAZONOSITO
|
|
,C_MUKODESIHELYTIPUSA
|
|
,C_NEV
|
|
,C_SZEKHELY
|
|
,C_TAGINTEZMENY
|
|
,C_TAGINTEZMENYIKOD
|
|
,C_TANEVID
|
|
,C_TELEFONSZAM
|
|
,C_VAROS
|
|
,C_VEZETONEVE
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,NNID
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
C_AJTO AS C_AJTO
|
|
,C_CIME AS C_CIME
|
|
,C_EMAILCIM AS C_EMAILCIM
|
|
,C_EMELET AS C_EMELET
|
|
,C_FAX AS C_FAX
|
|
,C_HAZSZAM AS C_HAZSZAM
|
|
,(SELECT ID FROM T_INTEZMENYADATOK WHERE ELOZOTANEVIREKORDID = t.C_INTEZMENYADATOKID AND C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyID) AS C_INTEZMENYADATOKID
|
|
,C_INTEZMENYID AS C_INTEZMENYID
|
|
,C_IRANYITOSZAM AS C_IRANYITOSZAM
|
|
,IIF((SELECT COUNT(1) FROM T_KOZTERULETJELLEG WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_KOZTERULETJELLEG) = 0,911,t.C_KOZTERULETJELLEG) AS C_KOZTERULETJELLEG
|
|
,C_KOZTERULETNEV AS C_KOZTERULETNEV
|
|
,C_MUKODESIHELYAZONOSITO AS C_MUKODESIHELYAZONOSITO
|
|
,IIF((SELECT COUNT(1) FROM T_MUKODESIHELYTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = t.C_MUKODESIHELYTIPUSA) = 0,5432,t.C_MUKODESIHELYTIPUSA) AS C_MUKODESIHELYTIPUSA
|
|
,C_NEV AS C_NEV
|
|
,C_SZEKHELY AS C_SZEKHELY
|
|
,C_TAGINTEZMENY AS C_TAGINTEZMENY
|
|
,C_TAGINTEZMENYIKOD AS C_TAGINTEZMENYIKOD
|
|
,@kovetkezoTanevId AS C_TANEVID
|
|
,C_TELEFONSZAM AS C_TELEFONSZAM
|
|
,C_VAROS AS C_VAROS
|
|
,C_VEZETONEVE AS C_VEZETONEVE
|
|
,TOROLT AS TOROLT
|
|
,0 AS SERIAL
|
|
,GETDATE() AS LASTCHANGED
|
|
,GETDATE() AS CREATED
|
|
,MODIFIER AS MODIFIER
|
|
,MODIFIER AS CREATOR
|
|
,NULL AS NNID
|
|
,ID AS ELOZOTANEVIREKORDID
|
|
FROM T_MUKODESIHELY t WHERE C_TANEVID = @aktTanevId AND C_INTEZMENYID = @intezmenyId
|
|
AND 0 = (SELECT COUNT(1) FROM T_MUKODESIHELY WHERE C_TANEVID = @kovetkezoTanevId AND C_INTEZMENYID = @intezmenyId AND ELOZOTANEVIREKORDID = t.ID)
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE kovetkezo SET
|
|
kovetkezo.C_AJTO = aktiv.C_AJTO
|
|
,kovetkezo.C_CIME = aktiv.C_CIME
|
|
,kovetkezo.C_EMAILCIM = aktiv.C_EMAILCIM
|
|
,kovetkezo.C_EMELET = aktiv.C_EMELET
|
|
,kovetkezo.C_FAX = aktiv.C_FAX
|
|
,kovetkezo.C_HAZSZAM = aktiv.C_HAZSZAM
|
|
,kovetkezo.C_IRANYITOSZAM = aktiv.C_IRANYITOSZAM
|
|
,kovetkezo.C_KOZTERULETJELLEG = IIF((SELECT COUNT(1) FROM T_KOZTERULETJELLEG WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = aktiv.C_KOZTERULETJELLEG) = 0,911,aktiv.C_KOZTERULETJELLEG)
|
|
,kovetkezo.C_KOZTERULETNEV = aktiv.C_KOZTERULETNEV
|
|
,kovetkezo.C_MUKODESIHELYAZONOSITO = aktiv.C_MUKODESIHELYAZONOSITO
|
|
,kovetkezo.C_MUKODESIHELYTIPUSA = IIF((SELECT COUNT(1) FROM T_MUKODESIHELYTIPUS WHERE C_ALTANEVID = @kovetkezoTanevId AND ID = aktiv.C_MUKODESIHELYTIPUSA) = 0,5432,aktiv.C_MUKODESIHELYTIPUSA)
|
|
,kovetkezo.C_NEV = aktiv.C_NEV
|
|
,kovetkezo.C_SZEKHELY = aktiv.C_SZEKHELY
|
|
,kovetkezo.C_TAGINTEZMENY = aktiv.C_TAGINTEZMENY
|
|
,kovetkezo.C_TAGINTEZMENYIKOD = aktiv.C_TAGINTEZMENYIKOD
|
|
,kovetkezo.C_TELEFONSZAM = aktiv.C_TELEFONSZAM
|
|
,kovetkezo.C_VAROS = aktiv.C_VAROS
|
|
,kovetkezo.C_VEZETONEVE = aktiv.C_VEZETONEVE
|
|
,kovetkezo.TOROLT = aktiv.TOROLT
|
|
FROM T_MUKODESIHELY kovetkezo
|
|
INNER JOIN T_MUKODESIHELY aktiv ON aktiv.C_INTEZMENYID = kovetkezo.C_INTEZMENYID AND aktiv.C_TANEVID = @aktTanevId AND aktiv.ID = @mukodesiHelyId
|
|
WHERE kovetkezo.C_INTEZMENYID = @intezmenyId AND kovetkezo.C_TANEVID = @kovetkezoTanevId AND kovetkezo.ELOZOTANEVIREKORDID = @mukodesiHelyId
|
|
END
|
|
|
|
END
|
|
GO
|