kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20170613130116_KRETA_1774/sp_MrgTanuloAdatok.sql

IF OBJECT_ID('stage.sp_MrgTanuloAdatok') IS NOT NULL BEGIN
  DROP PROCEDURE stage.sp_MrgTanuloAdatok
END
GO

CREATE PROCEDURE stage.sp_MrgTanuloAdatok
AS
BEGIN
  MERGE stage.T_TANULOADATOK_STAGE AS trg
    USING (
      SELECT 
        i.ID AS C_INTEZMENYID,
        t.C_NEV AS C_TANEVNEV,
        f.ID AS C_TANULOID,
        f.C_NEME AS C_TANULONEME, 
        f.C_SZULETESIDATUM AS C_TANULOSZULETESIDATUM,
		f.C_OKTATASIAZONOSITO AS C_OKTATASIAZONOSITO,
        tn.C_SAJATOSNEVELESU AS C_SAJATOSNEVELESU,
        tn.C_BTMPROBLEMAS AS C_BTMPROBLEMAS,
        tn.C_SNILETSZAMSULY AS C_SNILETSZAMSULY,
        tn.C_BTMLETSZAMSULY AS C_BTMLETSZAMSULY,
        tn.C_EGYEBDONTO AS C_EGYEBDONTO,
        tn.C_EVISMETLO  AS C_EVISMETLO,
        tn.C_MEGALLAPODASOS AS C_MEGALLAPODASOS,
        tn.C_OSZTV AS C_OSZTV,
        tn.C_SZETVAGAZAT AS C_SZETVAGAZAT,
        tn.C_SZINTVIZSGA AS C_SZINTVIZSGA,
        tn.C_SZKTV AS C_SZKTV,
        tn.C_TANULOSZERZODESES AS C_TANULOSZERZODESES,
        tn.C_TECHNIKUSIEVFOLYAM AS C_TECHNIKUSIEVFOLYAM,
        felh.ID AS C_FELADATELLATASIHELYID,
        f.C_NYOMTATASINEV AS C_NYOMTATASINEV,
        f.TOROLT AS TOROLT
      FROM dbo.T_FELHASZNALO f
        INNER JOIN dbo.T_TANULO tn ON tn.ID = f.ID
        INNER JOIN dbo.T_INTEZMENY i ON i.ID = f.C_INTEZMENYID
        INNER JOIN dbo.T_FELADATELLATASIHELY felh ON felh.C_INTEZMENYID = f.C_INTEZMENYID
        INNER JOIN dbo.T_TANEV t ON t.ID = f.C_TANEVID
        INNER JOIN dbo.T_TANULOCSOPORT tcs ON tcs.C_TANULOID = tn.ID
        INNER JOIN dbo.T_OSZTALY o ON o.ID = tcs.C_OSZTALYCSOPORTID
        INNER JOIN dbo.T_OSZTALYCSOPORT ocs ON ocs.ID = o.ID
      WHERE f.TOROLT = 'F' AND i.TOROLT = 'F' AND t.TOROLT = 'F' AND tcs.TOROLT = 'F' AND o.TOROLT = 'F' AND ocs.TOROLT = 'F'
        AND tcs.C_BELEPESDATUM <= GETDATE() AND (tcs.C_KILEPESDATUM > GETDATE() OR tcs.C_KILEPESDATUM IS NULL)
    ) src ON (trg.C_INTEZMENYID = src.C_INTEZMENYID AND trg.C_TANULOID = src.C_TANULOID) 
    WHEN MATCHED AND 
         (trg.C_TANULONEME <> src.C_TANULONEME OR (trg.C_TANULONEME IS NULL AND src.C_TANULONEME IS NOT NULL) OR (trg.C_TANULONEME IS NOT NULL AND src.C_TANULONEME IS NULL))
      OR (trg.C_TANULOSZULETESIDATUM <> src.C_TANULOSZULETESIDATUM OR (trg.C_TANULOSZULETESIDATUM IS NULL AND src.C_TANULOSZULETESIDATUM IS NOT NULL) OR (trg.C_TANULOSZULETESIDATUM IS NOT NULL AND src.C_TANULOSZULETESIDATUM IS NULL))    
      OR (trg.C_SAJATOSNEVELESU <> src.C_SAJATOSNEVELESU OR (trg.C_SAJATOSNEVELESU IS NULL AND src.C_SAJATOSNEVELESU IS NOT NULL) OR (trg.C_SAJATOSNEVELESU IS NOT NULL AND src.C_SAJATOSNEVELESU IS NULL))    
      OR (trg.C_BTMPROBLEMAS <> src.C_BTMPROBLEMAS OR (trg.C_BTMPROBLEMAS IS NULL AND src.C_BTMPROBLEMAS IS NOT NULL) OR (trg.C_BTMPROBLEMAS IS NOT NULL AND src.C_BTMPROBLEMAS IS NULL))    
      OR (trg.C_SNILETSZAMSULY <> src.C_SNILETSZAMSULY OR (trg.C_SNILETSZAMSULY IS NULL AND src.C_SNILETSZAMSULY IS NOT NULL) OR (trg.C_SNILETSZAMSULY IS NOT NULL AND src.C_SNILETSZAMSULY IS NULL))    
      OR (trg.C_BTMLETSZAMSULY <> src.C_BTMLETSZAMSULY OR (trg.C_BTMLETSZAMSULY IS NULL AND src.C_BTMLETSZAMSULY IS NOT NULL) OR (trg.C_BTMLETSZAMSULY IS NOT NULL AND src.C_BTMLETSZAMSULY IS NULL))
	    OR (trg.C_OKTATASIAZONOSITO <> src.C_OKTATASIAZONOSITO OR (trg.C_OKTATASIAZONOSITO IS NULL AND src.C_OKTATASIAZONOSITO IS NOT NULL) OR (trg.C_OKTATASIAZONOSITO IS NOT NULL AND src.C_OKTATASIAZONOSITO IS NULL))
      OR (trg.C_EGYEBDONTO <> src.C_EGYEBDONTO OR (trg.C_EGYEBDONTO IS NULL AND src.C_EGYEBDONTO IS NOT NULL) OR (trg.C_EGYEBDONTO IS NOT NULL AND src.C_EGYEBDONTO IS NULL))
      OR (trg.C_EVISMETLO <> src.C_EVISMETLO OR (trg.C_EVISMETLO IS NULL AND src.C_EVISMETLO IS NOT NULL) OR (trg.C_EVISMETLO IS NOT NULL AND src.C_EVISMETLO IS NULL))
      OR (trg.C_MEGALLAPODASOS <> src.C_MEGALLAPODASOS OR (trg.C_MEGALLAPODASOS IS NULL AND src.C_MEGALLAPODASOS IS NOT NULL) OR (trg.C_MEGALLAPODASOS IS NOT NULL AND src.C_MEGALLAPODASOS IS NULL))
      OR (trg.C_OSZTV <> src.C_OSZTV OR (trg.C_OSZTV IS NULL AND src.C_OSZTV IS NOT NULL) OR (trg.C_OSZTV IS NOT NULL AND src.C_OSZTV IS NULL))
      OR (trg.C_SZETVAGAZAT <> src.C_SZETVAGAZAT OR (trg.C_SZETVAGAZAT IS NULL AND src.C_SZETVAGAZAT IS NOT NULL) OR (trg.C_SZETVAGAZAT IS NOT NULL AND src.C_SZETVAGAZAT IS NULL))
      OR (trg.C_SZINTVIZSGA <> src.C_SZINTVIZSGA OR (trg.C_SZINTVIZSGA IS NULL AND src.C_SZINTVIZSGA IS NOT NULL) OR (trg.C_SZINTVIZSGA IS NOT NULL AND src.C_SZINTVIZSGA IS NULL))
      OR (trg.C_SZKTV <> src.C_SZKTV OR (trg.C_SZKTV IS NULL AND src.C_SZKTV IS NOT NULL) OR (trg.C_SZKTV IS NOT NULL AND src.C_SZKTV IS NULL))
      OR (trg.C_TANULOSZERZODESES <> src.C_TANULOSZERZODESES OR (trg.C_TANULOSZERZODESES IS NULL AND src.C_TANULOSZERZODESES IS NOT NULL) OR (trg.C_TANULOSZERZODESES IS NOT NULL AND src.C_TANULOSZERZODESES IS NULL))
      OR (trg.C_TECHNIKUSIEVFOLYAM <> src.C_TECHNIKUSIEVFOLYAM OR (trg.C_TECHNIKUSIEVFOLYAM IS NULL AND src.C_TECHNIKUSIEVFOLYAM IS NOT NULL) OR (trg.C_TECHNIKUSIEVFOLYAM IS NOT NULL AND src.C_TECHNIKUSIEVFOLYAM IS NULL))
      OR (trg.C_FELADATELLATASIHELYID <> src.C_FELADATELLATASIHELYID OR (trg.C_FELADATELLATASIHELYID IS NULL AND src.C_FELADATELLATASIHELYID IS NOT NULL) OR (trg.C_FELADATELLATASIHELYID IS NOT NULL AND src.C_FELADATELLATASIHELYID IS NULL))
      OR (trg.C_NYOMTATASINEV <> src.C_NYOMTATASINEV OR (trg.C_NYOMTATASINEV IS NULL AND src.C_NYOMTATASINEV IS NOT NULL) OR (trg.C_NYOMTATASINEV IS NOT NULL AND src.C_NYOMTATASINEV IS NULL))
    THEN
      UPDATE SET
         trg.C_TANULONEME = src.C_TANULONEME
        ,trg.C_TANULOSZULETESIDATUM = src.C_TANULOSZULETESIDATUM
        ,trg.C_SAJATOSNEVELESU = src.C_SAJATOSNEVELESU
        ,trg.C_BTMPROBLEMAS = src.C_BTMPROBLEMAS
        ,trg.C_SNILETSZAMSULY = src.C_SNILETSZAMSULY
        ,trg.C_BTMLETSZAMSULY = src.C_BTMLETSZAMSULY
		    ,trg.C_OKTATASIAZONOSITO = src.C_OKTATASIAZONOSITO

        ,trg.C_EGYEBDONTO = src.C_EGYEBDONTO
        ,trg.C_EVISMETLO = src.C_EVISMETLO
        ,trg.C_MEGALLAPODASOS = src.C_MEGALLAPODASOS
        ,trg.C_OSZTV = src.C_OSZTV
        ,trg.C_SZETVAGAZAT = src.C_SZETVAGAZAT
        ,trg.C_SZINTVIZSGA = src.C_SZINTVIZSGA
        ,trg.C_SZKTV = src.C_SZKTV
        ,trg.C_TANULOSZERZODESES = src.C_TANULOSZERZODESES
        ,trg.C_TECHNIKUSIEVFOLYAM = src.C_TECHNIKUSIEVFOLYAM
        ,trg.C_FELADATELLATASIHELYID = src.C_FELADATELLATASIHELYID
        ,trg.C_NYOMTATASINEV = src.C_NYOMTATASINEV
        ,trg.LASTCHANGED = GETDATE()
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (
         C_INTEZMENYID
        ,C_TANEVNEV
        ,C_TANULOID
        ,C_TANULONEME
        ,C_TANULOSZULETESIDATUM
        ,C_SAJATOSNEVELESU
        ,C_BTMPROBLEMAS
        ,C_SNILETSZAMSULY
        ,C_BTMLETSZAMSULY
		    ,C_OKTATASIAZONOSITO
        ,C_EGYEBDONTO
        ,C_EVISMETLO
        ,C_MEGALLAPODASOS
        ,C_OSZTV
        ,C_SZETVAGAZAT
        ,C_SZINTVIZSGA
        ,C_SZKTV
        ,C_TANULOSZERZODESES
        ,C_TECHNIKUSIEVFOLYAM
        ,C_FELADATELLATASIHELYID
        ,C_NYOMTATASINEV
        ,TOROLT
        ,LASTCHANGED
        ,CREATED
      ) VALUES (
         src.C_INTEZMENYID
        ,src.C_TANEVNEV
        ,src.C_TANULOID
        ,src.C_TANULONEME
        ,src.C_TANULOSZULETESIDATUM
        ,src.C_SAJATOSNEVELESU
        ,src.C_BTMPROBLEMAS
        ,src.C_SNILETSZAMSULY
        ,src.C_BTMLETSZAMSULY
		    ,src.C_OKTATASIAZONOSITO
        ,src.C_EGYEBDONTO
        ,src.C_EVISMETLO
        ,src.C_MEGALLAPODASOS
        ,src.C_OSZTV
        ,src.C_SZETVAGAZAT
        ,src.C_SZINTVIZSGA
        ,src.C_SZKTV
        ,src.C_TANULOSZERZODESES
        ,src.C_TECHNIKUSIEVFOLYAM
        ,src.C_FELADATELLATASIHELYID
        ,src.C_NYOMTATASINEV
        ,'F'
        ,GETDATE()
        ,GETDATE()
      )
    WHEN NOT MATCHED BY SOURCE AND trg.TOROLT = 'F' THEN
      UPDATE SET 
        trg.LASTCHANGED = GETDATE(), 
        trg.TOROLT = 'T'
  ;
END
GO