kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20200304134948_DB_2122/uspFollowUpGondviselo.sql

DROP PROCEDURE IF EXISTS uspFollowUpGondviselo
GO

CREATE PROCEDURE uspFollowUpGondviselo
   @intezmenyId int
  ,@tanevId int
  ,@kovetkezoTanevId int
  ,@gondviseloId int
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE 
     @kovGondviseloId int = (SELECT ID FROM T_GONDVISELO_OSSZES WHERE ELOZOTANEVIREKORDID=@gondviseloId)
    ,@insertColList nvarchar(max)
    ,@selectColList nvarchar(max)
    ,@updateColList nvarchar(max)
    ,@partialSql nvarchar(max)
    ,@sql nvarchar(max)


  IF (SELECT TOROLT FROM T_GONDVISELO_OSSZES WHERE ID = @gondviseloId)='T' OR (SELECT TOROLT FROM T_GONDVISELO_OSSZES WHERE ID = @kovGondviseloId)='T' BEGIN
    RETURN
  END

  CREATE TABLE #ColumnMappings(
     insertColName nvarchar(128)
    ,selectColName nvarchar(128)
    ,updateColName nvarchar(128)
    ,tableName nvarchar(128)
  )
  INSERT INTO #ColumnMappings(insertColName,selectColName,updateColName,tableName)
  SELECT insertColName,selectColName,updateColName,'T_GONDVISELO' from fnGetFollowUpColumnMapping (NULL,'T_GONDVISELO')
  UNION ALL
  SELECT insertColName,selectColName,updateColName,'T_EMAIL' FROM fnGetFollowUpColumnMapping (NULL,'T_EMAIL')
  UNION ALL
  SELECT insertColName,selectColName,updateColName,'T_TELEFON' FROM fnGetFollowUpColumnMapping (NULL,'T_TELEFON')
  
  UPDATE #ColumnMappings SET selectColName = 'gKov.ID' WHERE insertColName = 'C_GONDVISELOID' AND tableName IN ('T_EMAIL','T_TELEFON')
  UPDATE #ColumnMappings SET selectColName = 'gKov.C_TANULOID' WHERE insertColName = 'C_FELHASZNALOID' AND tableName IN ('T_EMAIL','T_TELEFON')

  IF @kovGondviseloId IS NULL BEGIN
    SELECT @insertColList = ISNULL(@insertColList+','+insertColname,insertColname) FROM #columnMappings WHERE tableName = 'T_GONDVISELO'
    SELECT @selectColList = ISNULL(@selectColList+','+selectColName,selectColName) FROM #ColumnMappings WHERE tableName = 'T_GONDVISELO'
    SET @partialSql = N'
      INSERT INTO T_GONDVISELO_OSSZES (
        '+@insertColList+'
      ) SELECT
        '+@selectColList+'
      FROM T_GONDVISELO_OSSZES s
        INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = s.C_TANULOID
        INNER JOIN T_FELHASZNALO_OSSZES fhNew ON fhNew.C_OKTATASIAZONOSITO = fh.C_OKTATASIAZONOSITO AND fhNew.TOROLT=''F'' AND fhNew.C_TANEVID = @kovetkezoTanevId
      WHERE s.ID = @gondviseloId'
  END
  ELSE BEGIN
    SELECT 
      @updateColList = ISNULL(@updateColList+','+insertColName+' = '+updateColname,insertColName+' = '+updateColname)
    FROM #columnMappings
    WHERE tableName = 'T_GONDVISELO' AND updateColname IS NOT NULL
    SET @partialSql = N'
      UPDATE t SET  
        '+@updateColList+'
      FROM T_GONDVISELO_OSSZES s
        INNER JOIN T_GONDVISELO_OSSZES t ON t.ELOZOTANEVIREKORDID=s.ID AND s.ID=@gondviseloId'
  END
  
  --PRINT @partialSql
  SET @sql = @partialSql
  SELECT @partialSql = NULL,@insertColList = NULL,@selectColList = NULL,@updateColList = NULL
  SELECT @insertColList = ISNULL(@insertColList+','+insertColname,insertColname) FROM #columnMappings WHERE tableName = 'T_TELEFON'
  SELECT @selectColList = ISNULL(@selectColList+','+selectColName,selectColName) FROM #ColumnMappings WHERE tableName = 'T_TELEFON'
  
  SET @partialSql = N'
  INSERT INTO T_TELEFON_OSSZES (
		'+@insertColList+'
  )	SELECT
		'+@selectColList+'
	FROM T_GONDVISELO_OSSZES g
		INNER JOIN T_GONDVISELO_OSSZES gKov ON gKov.ELOZOTANEVIREKORDID=g.ID
		INNER JOIN T_TELEFON_OSSZES s ON s.C_GONDVISELOID=g.ID AND s.TOROLT=''F''
	WHERE g.ID=@gondviseloId
		AND NOT EXISTS(SELECT 1 FROM T_TELEFON_OSSZES WHERE ELOZOTANEVIREKORDID=s.ID AND TOROLT=''F'')'
  
  SELECT @insertColList = NULL,@selectColList = NULL
  SELECT @insertColList = ISNULL(@insertColList+','+insertColname,insertColname) FROM #columnMappings WHERE tableName = 'T_EMAIL'
  SELECT @selectColList = ISNULL(@selectColList+','+selectColName,selectColName) FROM #ColumnMappings WHERE tableName = 'T_EMAIL'

  SET @partialSql += N'
	INSERT INTO T_EMAIL_OSSZES (
		'+@insertColList+'
  )	SELECT
		'+@selectColList+'
	FROM T_GONDVISELO_OSSZES g
		INNER JOIN T_GONDVISELO_OSSZES gKov ON gKov.ELOZOTANEVIREKORDID=g.ID
		INNER JOIN T_EMAIL_OSSZES s ON s.C_GONDVISELOID=g.ID AND s.TOROLT=''F''
	WHERE g.ID=@gondviseloId
		AND NOT EXISTS(SELECT 1 FROM T_EMAIL_OSSZES WHERE ELOZOTANEVIREKORDID=s.ID AND TOROLT=''F'')'
  
  SET @sql+=@partialSql
  --PRINT @partialSql

  SELECT 
      @updateColList = ISNULL(@updateColList+','+insertColName+' = '+updateColname,insertColName+' = '+updateColname)
  FROM #columnMappings
  WHERE tableName = 'T_TELEFON' AND updateColname IS NOT NULL
  SET @partialSql = N'
	UPDATE t SET
		'+@updateColList+'
	FROM T_TELEFON_OSSZES s
		INNER JOIN T_TELEFON_OSSZES t ON t.ELOZOTANEVIREKORDID=s.ID AND t.TOROLT=''F''
	WHERE s.TOROLT=''F'' AND s.C_GONDVISELOID=@gondviseloId'
  
  SET @updateColList = NULL
  SELECT 
      @updateColList = ISNULL(@updateColList+','+insertColName+' = '+updateColname,insertColName+' = '+updateColname)
  FROM #columnMappings
  WHERE tableName = 'T_EMAIL' AND updateColname IS NOT NULL

  SET @partialSql += N'
	UPDATE t SET
	  '+@updateColList+'
	FROM T_EMAIL_OSSZES s
		INNER JOIN T_EMAIL_OSSZES t ON t.ELOZOTANEVIREKORDID=s.ID AND t.TOROLT=''F''
	WHERE s.TOROLT=''F'' AND s.C_GONDVISELOID=@gondviseloId'

  SET @sql += @partialSql
  --PRINT @partialSql
  DECLARE @cimId int
  DECLARE cimCursor CURSOR FOR
    SELECT ID FROM T_CIM_OSSZES WHERE C_GONDVISELOID=@gondviseloId AND TOROLT='F'
  OPEN cimCursor
  FETCH NEXT FROM cimCursor INTO @cimId
  WHILE @@FETCH_STATUS=0 BEGIN
       EXEC uspFollowUpGondviseloCim  
          @intezmenyId,
          @tanevId,
          @kovetkezoTanevId,
          @cimId
    FETCH NEXT FROM cimCursor INTO @cimId
  END
  CLOSE cimCursor
  DEALLOCATE cimCursor

  EXEC sp_executesql 
     @sql,N'
     @intezmenyId int
    ,@tanevId int
    ,@kovetkezoTanevId int
    ,@gondviseloId int
    ,@kovGondviseloId int'
    ,@intezmenyId = @intezmenyId 
    ,@tanevId = @tanevId
    ,@kovetkezoTanevId = @kovetkezoTanevId
    ,@gondviseloId = @gondviseloId 
    ,@kovGondviseloId = @kovGondviseloId

END
GO