﻿--GUID AZONOS TANÉVEKEN ÁTÍVELŐEN
update [dbo].T_FELHASZNALOBELEPES
SET
  C_GUID = userneedupdate.newGuid
FROM
(
  select 
    new.LoginName as newLoginName,
    new.[Guid] as newGuid,
    new.InstituteId as newAndOldInstituteId
 from
  (
    SELECT
		  felhasznalobelepes.C_GUID AS [Guid],
		  felhasznalobelepes.ID AS UserId,
		  intezmeny.ID AS InstituteId,
		  tanev.ID AS tanevId,
		  felhasznalobelepes.C_BEJELENTKEZESINEV AS LoginName
	  FROM
		  [dbo].T_FELHASZNALOBELEPES AS felhasznalobelepes
	  INNER JOIN [dbo].T_INTEZMENY intezmeny ON intezmeny.id = felhasznalobelepes.C_INTEZMENYID
	  INNER JOIN [dbo].T_TANEV AS tanev ON felhasznalobelepes.C_TANEVID = tanev.ID AND felhasznalobelepes.C_INTEZMENYID = tanev.C_INTEZMENYID AND tanev.TOROLT='F' AND tanev.C_AKTIV='T'
    WHERE
      felhasznalobelepes.TOROLT = 'F'
  ) AS new
	INNER JOIN 
	(    SELECT
		  felhasznalobelepes.C_GUID AS [Guid],
		  felhasznalobelepes.ID AS UserId,
		  intezmeny.ID AS InstituteId,
		  tanev.ID AS tanevId,
		  felhasznalobelepes.C_BEJELENTKEZESINEV AS LoginName
	  FROM
		  [dbo].T_FELHASZNALOBELEPES AS felhasznalobelepes
	  INNER JOIN [dbo].T_INTEZMENY intezmeny ON intezmeny.id = felhasznalobelepes.C_INTEZMENYID
	  INNER JOIN [dbo].T_TANEV AS tanev ON felhasznalobelepes.C_TANEVID = tanev.ID AND felhasznalobelepes.C_INTEZMENYID = tanev.C_INTEZMENYID AND tanev.C_AKTIV='F'
    WHERE
      felhasznalobelepes.TOROLT = 'F') as old
	  ON new.LoginName = old.LoginName AND new.InstituteId = old.InstituteId
	  WHERE new.[Guid] <> old.[Guid]
    ) as userneedupdate

WHERE
C_BEJELENTKEZESINEV = userneedupdate.newLoginName AND
C_INTEZMENYID = userneedupdate.newAndOldInstituteId

--NEM AKTÍV TANÉVES USER-EK TÖRLÉSE
UPDATE fb
SET
  fb.TOROLT = 'T'
FROM [dbo].T_FELHASZNALOBELEPES AS fb
INNER JOIN [dbo].T_TANEV AS tanev ON fb.C_TANEVID = tanev.ID
WHERE tanev.C_AKTIV = 'F'