--  =================================
--  Gondviselők, akiknek van egynél több azonos email cím vagy telefonszám adatuk
--  =================================

UPDATE E
SET 
   Torolt = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Email E 
  INNER JOIN (
    SELECT
      Id
      ,ROW_NUMBER() OVER (PARTITION BY C_Emailcim, C_Emailtipusa, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
    FROM
      T_Email E
    WHERE
      E.Torolt = 'F'
  ) X ON X.Id = E.Id
WHERE
  X.Rn > 1
  AND E.C_Gondviseloid IS NOT NULL
  AND NOT EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = E.C_Felhasznaloid)

UPDATE T
SET 
   Torolt = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Telefon T
  INNER JOIN (
    SELECT
      Id
      ,ROW_NUMBER() OVER (PARTITION BY C_Telefonszam, C_Telefontipusa, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
    FROM
      T_Telefon T
    WHERE
      T.Torolt = 'F'
  ) X ON X.Id = T.Id
WHERE
  X.Rn > 1
  AND T.C_Gondviseloid IS NOT NULL
  AND NOT EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = T.C_Felhasznaloid)


--  =================================
--  Gondviselők, akiknek van egynél több (nem feltétlenül) azonos email cím vagy telefonszám adatuk
--  =================================

UPDATE E
SET 
   Torolt = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Email E
  INNER JOIN (
    SELECT
      Id
      ,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
    FROM
      T_Email E
    WHERE
      E.Torolt = 'F'
  ) X ON X.Id = E.Id
WHERE
  X.Rn > 1
  AND E.C_Gondviseloid IS NOT NULL

UPDATE T
SET 
   Torolt = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Telefon T
  INNER JOIN (
    SELECT
      Id
      ,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
    FROM
      T_Telefon T
    WHERE
      T.Torolt = 'F'
  ) X ON X.Id = T.Id
WHERE
  X.Rn > 1
  AND T.C_Gondviseloid IS NOT NULL


--  =================================
--  Gondviselők, akiknek nincs alapértelmezett email cím vagy telefonszám adatuk
--  =================================

UPDATE E
SET 
  C_Alapertelmezett = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Email E
WHERE
  E.C_Gondviseloid IS NOT NULL 
  AND E.C_Alapertelmezett = 'F'
  AND E.Torolt = 'F'

UPDATE T
SET 
  C_Alapertelmezett = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Telefon T
WHERE
  T.C_Gondviseloid IS NOT NULL 
  AND T.C_Alapertelmezett = 'F'
  AND T.Torolt = 'F'

--  =================================
--  Tanulók, akiknek van egynél több azonos email cím vagy telefonszám adatuk
--  =================================

UPDATE E
SET 
   Torolt = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Email E
  INNER JOIN (
    SELECT
      Id
      ,ROW_NUMBER() OVER (PARTITION BY C_Emailcim, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
    FROM
      T_Email E
    WHERE
      E.Torolt = 'F'
  ) X ON X.Id = E.Id

WHERE
  X.Rn > 1
  AND E.C_Gondviseloid IS NULL
  AND EXISTS(SELECT 1 FROM T_Tanulo Ta WHERE Ta.Id = E.C_Felhasznaloid)

UPDATE T
SET 
   Torolt = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Telefon T
  INNER JOIN (
    SELECT
      Id
      ,ROW_NUMBER() OVER (PARTITION BY C_Telefonszam, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
    FROM
      T_Telefon T
    WHERE
      T.Torolt = 'F'
  ) X ON X.Id = T.Id
WHERE
  X.Rn > 1
  AND T.C_Gondviseloid IS NULL
  AND EXISTS(SELECT 1 FROM T_Tanulo Ta WHERE Ta.Id = T.C_Felhasznaloid)


--  =================================
--  Tanulók, akiknek nincs alapértelmezett email cím vagy telefonszám adatuk
--  =================================

UPDATE E
SET 
  C_Alapertelmezett = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Email E
  INNER JOIN (
    SELECT
      E.Id
      ,ROW_NUMBER () OVER (PARTITION BY e.C_FELHASZNALOID, e.C_INTEZMENYID, e.C_TANEVID ORDER BY e.CREATED DESC) RN
    FROM
      T_Tanulo Ta
      INNER JOIN T_Email E ON E.C_Felhasznaloid = Ta.Id
    WHERE
      E.C_Gondviseloid IS NULL
      AND NOT EXISTS(
        SELECT TOP 1 1
        FROM T_Email E2
        WHERE E2.C_Felhasznaloid = Ta.Id AND E2.C_Gondviseloid IS NULL AND E2.Torolt = 'F' AND E2.C_Alapertelmezett = 'T')
  ) X ON X.Id = E.Id
WHERE E.Torolt = 'F' AND X.RN = 1

UPDATE T
SET 
  C_Alapertelmezett = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Telefon T
  INNER JOIN (
    SELECT 
      T.Id
      ,ROW_NUMBER () over (partition by t.C_FELHASZNALOID, t.C_INTEZMENYID, t.C_TANEVID ORDER BY t.CREATED DESC) RN
    FROM
      T_Tanulo Ta
      INNER JOIN T_Telefon T ON T.C_Felhasznaloid = Ta.Id
    WHERE
      T.C_Gondviseloid IS NULL
      AND NOT EXISTS(
        SELECT TOP 1 1
        FROM T_Telefon T2
        WHERE T2.C_Felhasznaloid = Ta.Id AND T2.C_Alapertelmezett = 'T' AND T2.C_Gondviseloid IS NULL AND T2.Torolt = 'F')
  ) X ON X.Id = T.Id
WHERE T.Torolt = 'F' AND X.RN = 1


--  =================================
--  Tanulók, akiknek több alapértelmezett email cím vagy telefonszám adatuk van
--  =================================

UPDATE E
SET 
   C_Alapertelmezett = 'F'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Email E
  INNER JOIN (
    SELECT
      Id
      ,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY Created DESC) AS Rn
    FROM
      T_Email E
    WHERE
      E.Torolt = 'F'
      AND E.C_Alapertelmezett = 'T'
  ) X ON X.Id = E.Id

WHERE
  X.Rn > 1
  AND E.C_Gondviseloid IS NULL
  AND EXISTS(SELECT 1 FROM T_Tanulo Ta WHERE Ta.Id = E.C_Felhasznaloid)

UPDATE T
SET 
  C_Alapertelmezett = 'F'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Telefon T
  INNER JOIN (
    SELECT
      Id
      ,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY Created DESC) AS Rn
    FROM
      T_Telefon T
    WHERE
      T.Torolt = 'F'
      AND T.C_Alapertelmezett = 'T'
  ) X ON X.Id = T.Id
WHERE
  X.Rn > 1
  AND T.C_Gondviseloid IS NULL
  AND EXISTS(SELECT 1 FROM T_Tanulo Ta WHERE Ta.Id = T.C_Felhasznaloid)


--  =================================
--  Alkalmazott, akiknek van egynél több azonos email cím vagy telefonszám adatuk (de az NEM publikus!)
--  =================================

UPDATE E
SET 
   Torolt = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Email E
  INNER JOIN (
    SELECT
      Id
      ,ROW_NUMBER() OVER (PARTITION BY C_Emailcim, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
    FROM
      T_Email E
    WHERE
      E.Torolt = 'F'
      AND E.C_Ispublic = 'F'
  ) X ON X.Id = E.Id

WHERE
  X.Rn > 1
  AND E.C_Gondviseloid IS NULL
  AND EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = E.C_Felhasznaloid)

UPDATE T
SET 
   Torolt = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Telefon T
  INNER JOIN (
    SELECT
      Id
      ,ROW_NUMBER() OVER (PARTITION BY C_Telefonszam, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
    FROM
      T_Telefon T
    WHERE
      T.Torolt = 'F'
      AND T.C_Ispublic = 'F'
  ) X ON X.Id = T.Id
WHERE
  X.Rn > 1
  AND T.C_Gondviseloid IS NULL
  AND EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = T.C_Felhasznaloid)


--  =================================
--  Alkalmazott, akiknek az alapértelmezett email cím vagy telefonszám adatuk egyben publikus is
--  =================================

UPDATE E
SET 
   C_Alapertelmezett = 'F'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Email E
WHERE
  E.C_Alapertelmezett = 'T'
  AND E.C_Ispublic = 'T'
  AND E.Torolt = 'F'

UPDATE T
SET 
   C_Alapertelmezett = 'F'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Telefon T
WHERE
  T.C_Alapertelmezett = 'T'
  AND T.C_Ispublic = 'T'
  AND T.Torolt = 'F'


--  =================================
--  Alkalmazott, akiknek több alapértelmezett email cím vagy telefonszám adatuk van
--  =================================

UPDATE E
SET 
   C_Alapertelmezett = 'F'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Email E
  INNER JOIN (
    SELECT
      Id
      ,Torolt
      ,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY Created DESC) AS Rn
    FROM
      T_Email E
    WHERE
      E.Torolt = 'F'
      AND E.C_Alapertelmezett = 'T'
  ) X ON X.Id = E.Id

WHERE
  X.Rn > 1
  AND E.C_Gondviseloid IS NULL
  AND EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = E.C_Felhasznaloid)

UPDATE T
SET 
   C_Alapertelmezett = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Telefon T
  INNER JOIN (
    SELECT
      Id
      ,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY Created DESC) AS Rn
    FROM
      T_Telefon T
    WHERE
      T.Torolt = 'F'
      AND T.C_Alapertelmezett = 'T'
  ) X ON X.Id = T.Id
WHERE
  X.Rn > 1
  AND T.C_Gondviseloid IS NULL
  AND EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = T.C_Felhasznaloid)


--  =================================
--  Alkalmazott, akiknek nincs alapértelmezett email cím vagy telefonszám adatuk
--  =================================

UPDATE E
SET 
   C_Alapertelmezett = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Email E
  INNER JOIN (
    SELECT
      E.Id
      ,ROW_NUMBER () OVER (PARTITION BY e.C_FELHASZNALOID, e.C_INTEZMENYID, e.C_TANEVID ORDER BY e.CREATED DESC) RN
    FROM
      T_Alkalmazott A
      INNER JOIN T_Email E ON E.C_Felhasznaloid = A.Id
    WHERE
      A.Torolt = 'F'
      AND E.C_Gondviseloid IS NULL
      AND E.C_Ispublic = 'F'
      AND NOT EXISTS(
        SELECT TOP 1 1
        FROM T_Email E2
        WHERE E2.C_Felhasznaloid = A.Id AND E2.C_Alapertelmezett = 'T' AND E2.C_Gondviseloid IS NULL AND E2.Torolt = 'F')
  ) X ON X.Id = E.Id
WHERE E.Torolt = 'F' AND X.RN = 1

UPDATE T
SET 
   C_Alapertelmezett = 'T'
  ,Modifier = 0
  ,Lastchanged = GETDATE()
  ,Serial += 1
FROM
  T_Telefon T
  INNER JOIN (
    SELECT
      T.Id
      ,ROW_NUMBER () OVER (PARTITION BY t.C_FELHASZNALOID, t.C_INTEZMENYID, t.C_TANEVID ORDER BY t.CREATED DESC) RN
    FROM
      T_Alkalmazott A
      INNER JOIN T_Telefon T ON T.C_Felhasznaloid = A.Id
    WHERE
      A.Torolt = 'F'
      AND T.C_Ispublic = 'F'
      AND T.C_Gondviseloid IS NULL
      AND NOT EXISTS(
        SELECT TOP 1 1
        FROM T_Telefon T2
        WHERE T2.C_Felhasznaloid = A.Id AND T2.C_Alapertelmezett = 'T' AND T2.Torolt = 'F' AND T2.C_Gondviseloid IS NULL)
  ) X ON X.Id = T.Id
WHERE T.Torolt = 'F' AND X.RN = 1