109 lines
3.4 KiB
Transact-SQL
109 lines
3.4 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspFollowUpTanulo
|
|
GO
|
|
|
|
CREATE PROCEDURE uspFollowUpTanulo
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@tanuloId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NOT EXISTS (SELECT 1 from T_TANEV where ID = @kovetkezoTanevId and C_KOVETKEZO = 'T' and C_AKTIVALVA is null and C_ISKIVALASZTHATO = 'T')
|
|
BEGIN
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE
|
|
@kovTanuloId int
|
|
,@updateColListT nvarchar(max)
|
|
,@insertColListT nvarchar(max)
|
|
,@updateColListFH nvarchar(max)
|
|
,@insertColListFH nvarchar(max)
|
|
,@sql nvarchar(max)
|
|
|
|
CREATE TABLE #ColumnMappings(
|
|
insertColname nvarchar(max)
|
|
,updateColName nvarchar(max)
|
|
,tableName nvarchar(128)
|
|
)
|
|
INSERT INTO #ColumnMappings (
|
|
insertColname
|
|
,updateColName
|
|
,tableName
|
|
) SELECT
|
|
insertColName
|
|
,updateColName
|
|
,'T_TANULO'
|
|
FROM fnGetFollowUpColumnMapping ('C_NAPLOSORSZAM,C_TANULOEVFOLYAMTIPUS,C_ISKIFIRIMPORTALT','T_TANULO')
|
|
WHERE updateColName IS NOT NULL
|
|
UNION ALL
|
|
SELECT
|
|
insertColName
|
|
,updateColName
|
|
,'T_FELHASZNALO'
|
|
FROM fnGetFollowUpColumnMapping ('','T_FELHASZNALO')
|
|
WHERE updateColName IS NOT NULL
|
|
|
|
SELECT @kovTanuloId = kov.ID
|
|
FROM T_FELHASZNALO_OSSZES AS akt
|
|
INNER JOIN T_TANULO_OSSZES AS t ON akt.ID = t.ID -- DB4440 - Szűrés tanulóra
|
|
INNER JOIN T_FELHASZNALO_OSSZES AS kov ON --Import miatt nem elég az ELOZOTANEVIREKORDID
|
|
ISNULL(akt.C_OKTATASIAZONOSITO,'') = ISNULL(kov.C_OKTATASIAZONOSITO,'')
|
|
AND akt.C_SZULETESIDATUM = kov.C_SZULETESIDATUM
|
|
AND akt.C_SZULETESINEV = kov.C_SZULETESINEV
|
|
AND akt.C_ANYJANEVE = kov.C_ANYJANEVE
|
|
AND akt.C_SZULETESIHELY = kov.C_SZULETESIHELY
|
|
INNER JOIN T_TANULO_OSSZES tkov ON tkov.ID = kov.ID AND tkov.TOROLT = 'F'
|
|
WHERE akt.ID = @tanuloId AND kov.C_TANEVID = @kovetkezoTanevId
|
|
|
|
IF @kovTanuloId IS NULL
|
|
OR (SELECT TOROLT FROM T_FELHASZNALO_OSSZES WHERE ID = @tanuloId) = 'T'
|
|
OR (SELECT TOROLT FROM T_FELHASZNALO_OSSZES WHERE ID = @kovTanuloId) = 'T' BEGIN
|
|
RETURN
|
|
END
|
|
|
|
SELECT
|
|
@updateColListT = ISNULL(@updateColListt+','+insertColName+' = '+updateColname,insertColName+' = '+updateColname)
|
|
FROM #ColumnMappings WHERE tableName = 'T_TANULO'
|
|
SELECT
|
|
@updateColListfh = ISNULL(@updateColListfh+','+insertColName+' = '+updateColname,insertColName+' = '+updateColname)
|
|
FROM #ColumnMappings WHERE tableName = 'T_FELHASZNALO'
|
|
|
|
SET @sql = N'
|
|
|
|
UPDATE t SET
|
|
'+@updateColListT+'
|
|
FROM T_TANULO_OSSZES t
|
|
INNER JOIN T_TANULO_OSSZES s ON t.ID = @kovTanuloId AND s.ID = @tanuloId
|
|
|
|
UPDATE t SET
|
|
'+@updateColListFH+'
|
|
FROM T_FELHASZNALO_OSSZES t
|
|
INNER JOIN T_FELHASZNALO_OSSZES s ON s.C_OKTATASIAZONOSITO = t.C_OKTATASIAZONOSITO
|
|
WHERE t.ID = @kovTanuloId
|
|
AND s.ID = @tanuloId'
|
|
|
|
--PRINT @sql
|
|
|
|
EXEC sp_executesql
|
|
@sql,N'
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@tanuloId int
|
|
,@kovTanuloId int'
|
|
,@intezmenyId = @intezmenyId
|
|
,@aktTanevId = @aktTanevId
|
|
,@kovetkezoTanevId = @kovetkezoTanevId
|
|
,@tanuloId = @tanuloId
|
|
,@kovTanuloId = @kovTanuloId
|
|
|
|
EXEC uspFollowUpFelhasznaloCim @intezmenyId, @aktTanevId, @kovetkezoTanevId, @tanuloId;
|
|
EXEC uspFollowUpFelhasznaloEmail @intezmenyId, @aktTanevId, @kovetkezoTanevId, @tanuloId;
|
|
EXEC uspFollowUpTanuloFelmentes @intezmenyId, @aktTanevId, @kovetkezoTanevId, @tanuloId;
|
|
EXEC uspFollowUpFelhasznaloTelefon @intezmenyId, @aktTanevId, @kovetkezoTanevId, @tanuloId;
|
|
|
|
END
|
|
GO
|