UPDATE m
SET m.FullMedCardNum = CONVERT(varchar(10), m.MedCardNumber) + '/' + CONVERT(varchar(10), cr.ChildNumber)
FROM AKUZ.T_MOVEMENT m
JOIN AKUZ.T_CHILD_REGISTRATION cr on cr.MovementID = m.MovementID

UPDATE m
SET m.FullMedCardNum = m.MedCardNumber
FROM AKUZ.T_MOVEMENT m
WHERE m.FullMedCardNum is null and m.MedCardNumber is not null

UPDATE ee
SET ee.NumberIB = m.MedCardNumber
FROM AKUZ.T_EMERGENCY_EVENT ee
JOIN AKUZ.T_MOVEMENT m on m.EmergencyEvent = ee.EventID




-- в AKUZ.T_GEN_NUMBERS проставление в EntityID и максимального значение в NextNumber для нынешнего года 
DECLARE @entityMRID int
SET @entityMRID = (SELECT top 1 e.EntityID FROM VCLib.T_ENTITIES e WHERE e.EntName = 'MEDICALCARD_REGISTRATION' And e.[Schema] = 'AKUZ')

DECLARE @entityEEID int
SET @entityEEID = (SELECT top 1 e.EntityID FROM VCLib.T_ENTITIES e WHERE e.EntName = 'EMERGENCY_EVENT' And e.[Schema] = 'AKUZ')

  UPDATE gn
  SET EntityID = @entityEEID
  FROM AKUZ.T_GEN_NUMBERS gn
  WHERE gn.EntityID = @entityMRID and (LEN(gn.NumberName) - LEN(REPLACE(gn.NumberName, '/', ''))) = 2 and gn.NumberName Like '%/2024/%'

CREATE table #GN  
(
    [GenNumbersID] uniqueidentifier NOT NULL,
    [EntityID] int NOT NULL,
    [NumberName] varchar(64) NOT NULL,
    [NextNumber] int NOT NULL
) 
  INSERT INTO #GN
  SELECT gn.GenNumbersID, gn.EntityID, gn.NumberName, gn.NextNumber
  FROM AKUZ.T_GEN_NUMBERS gn
  WHERE gn.EntityID = @entityEEID and (LEN(gn.NumberName) - LEN(REPLACE(gn.NumberName, '/', ''))) = 2 and gn.NumberName Like '%/2024/%'

CREATE table #PART_NAME  
(
	part varchar(64) NULL
) 
  INSERT INTO #PART_NAME
  SELECT t.c
  FROM (SELECT LEFT(gn.NumberName, LEN(gn.NumberName) - CHARINDEX('/', REVERSE(gn.NumberName))) as c
  FROM #GN gn) as t
  GROUP BY c

  CREATE table #MAXNUM  
(
	maxNextNumber int,
	partNumberName varchar(64)
) 
  INSERT INTO #MAXNUM
  SELECT max(gn.NextNumber), pn.part
  FROM #GN gn
  RIGHT JOIN #PART_NAME pn on gn.NumberName Like pn.part + '/%'
  GROUP BY pn.part
  
DROP TABLE #PART_NAME
GO
	


   DECLARE @maxNextNumber int 
   DECLARE @partNumberName varchar(64)
   
   DECLARE my_cur CURSOR FOR 
   SELECT maxNextNumber, partNumberName
   FROM #MAXNUM
   
   OPEN my_cur
   FETCH NEXT FROM my_cur INTO @maxNextNumber, @partNumberName
   	 
   DECLARE @entityID int
   SET @entityID = (SELECT top 1 e.EntityID FROM VCLib.T_ENTITIES e WHERE e.EntName = 'EMERGENCY_EVENT' And e.[Schema] = 'AKUZ')

   WHILE @@FETCH_STATUS = 0
   BEGIN

   		IF EXISTS (SELECT * FROM #GN gn WHERE gn.NumberName = @partNumberName + '/1') 
			UPDATE gn 
			SET gn.NextNumber = @maxNextNumber
			FROM AKUZ.T_GEN_NUMBERS gn
			WHERE gn.EntityID = @entityID and gn.NumberName = @partNumberName + '/1'
		ELSE
			INSERT INTO AKUZ.T_GEN_NUMBERS (
				 GenNumbersID
				,EntityID
				,NumberName
				,NextNumber)
			VALUES (NEWID(), @entityID, @partNumberName + '/1', @maxNextNumber)

        FETCH NEXT FROM my_cur INTO @maxNextNumber, @partNumberName
   END
   
   CLOSE my_cur
   DEALLOCATE my_cur


DROP TABLE #MAXNUM
DROP TABLE #GN
GO



