DECLARE @sql_ex NVARCHAR(1000)

WHILE (1 = 1)
BEGIN

    BEGIN TRY
        SET @sql_ex = (
			SELECT TOP 1 'drop index ' + name + ' on AKUZ.T_MOVEMENTS_DIVISION_CARD'
			FROM SYS.indexes
			WHERE object_id = OBJECT_ID('AKUZ.T_MOVEMENTS_DIVISION_CARD') AND type > 1
		)

		IF @sql_ex IS NULL
			BREAK

		EXECUTE sp_executesql @sql_ex

		PRINT @sql_ex
    END TRY
    BEGIN CATCH
        
		THROW
		BREAK;
		
    END CATCH
	
END

----- 0 min

UPDATE me
	SET me.DateIn = exam.ExaminationDate, 
		me.DateOut = exam2.ExaminationDate
--SELECT exam.ExaminationDate, exam2.ExaminationDate
FROM AKUZ.T_MEDICAL_EPISODE me
	JOIN AKUZ.T_MEDICAL_EXAMINATION_DATA med on me.MedicalEpisodeID = med.MedicalEpisodeID
	JOIN AKUZ.T_MEDICAL_EXAMINATION exam on exam.MedicalRecordID = med.MedicalExaminationMain
	JOIN AKUZ.T_MEDICAL_EXAMINATION exam2 on exam2.MedicalRecordID = med.LastMedicalExamination;

----- 1 min


DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
  WHERE parent_object_id = object_id('AKUZ.T_MEDICAL_EPISODE') 
)
SELECT @sql += N'ALTER TABLE ' + obj + N' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC sys.sp_executesql @sql;
GO

DECLARE @entityID int
SET @entityID = (SELECT top 1 e.EntityID FROM VCLib.T_ENTITIES e WHERE e.EntName = 'MOVEMENTS_DIVISION_CARD' and e.[Schema] = 'AKUZ');

INSERT INTO AKUZ.T_MEDICAL_EPISODE (MedicalEpisodeID, TNMS, SetDTClinicalDiagnosis, OutcomeRecord, AmbulanceEvent, Mkb,
                                    Diagnosis, Descr, DateIn, DateOut, CdaEpicris, VimisEpicrisis, type_)
SELECT mdc.MovementsDivisionCardID,
       mdc.TNMS,
       mdc.SetDTClinicalDiagnosis,
       mdc.MovementResult,
       m.EmergencyEvent,
       COALESCE(d.Mkb, 'Z00.0'),
       COALESCE(d.Diagnosis, ('Общий медицинский осмотр')),
       d.Descr,
       mdc.DateIn,
       mdc.DateOut,
       mdc.CdaHospitalEpicris,
       mdc.VimisHospitalEpicrisis,
       @entityID
FROM AKUZ.T_MOVEMENTS_DIVISION_CARD mdc
         LEFT JOIN AKUZ.T_DIAGNOSIS d on d.MedicalRecordID = mdc.ClinicalDiagnosis
         LEFT JOIN AKUZ.T_MOVEMENT m on m.MovementID = mdc.Movement;

DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
  WHERE parent_object_id = object_id('AKUZ.T_MEDICAL_EPISODE') 
)
SELECT @sql += N'ALTER TABLE ' + obj + N' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC sys.sp_executesql @sql;
GO

--- 1 min


CREATE table #DIAG  
(
	MedicalRecordID [uniqueidentifier] NULL
) 

INSERT INTO #DIAG
SELECT d.MedicalRecordID 
FROM AKUZ.T_DIAGNOSIS d
JOIN AKUZ.T_MOVEMENTS_DIVISION_CARD mdc on d.MedicalRecordID = mdc.ClinicalDiagnosis;

--CREATE INDEX IX_TMP_DIAG ON #DIAG (MedicalRecordID)
--GO


ALTER TABLE [AKUZ].[T_MOVEMENTS_DIVISION_CARD] DROP CONSTRAINT FK_T_DIAGNOSIS_T_MOVEMENTS_DIVISION_CARD_ClinicalDiagnosis
GO

ALTER TABLE [AKUZ].[T_MOVEMENTS_DIVISION_CARD] DROP COLUMN ClinicalDiagnosis
GO

-- 0 min

DELETE e
FROM AKUZ.T_DIAGNOSIS e JOIN #DIAG d on e.MedicalRecordID = d.MedicalRecordID

-- 0 min

DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + N' NOCHECK CONSTRAINT ' + name + ';
  ' as obj
  FROM sys.foreign_keys
  WHERE referenced_object_id = object_id('AKUZ.T_AMBULANCE_CARD_RECORD') 
)
SELECT @sql += obj
FROM x;

--select @sql

EXEC sys.sp_executesql @sql;
GO


WHILE (1 = 1)
BEGIN
    DELETE top (50000) e
	FROM AKUZ.T_AMBULANCE_CARD_RECORD e JOIN #DIAG d on e.MedicalRecordID = d.MedicalRecordID

    IF @@ROWCOUNT = 0
	BREAK

END
GO


DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + N' CHECK CONSTRAINT ' + name + ';
  ' as obj
  FROM sys.foreign_keys
  WHERE referenced_object_id = object_id('AKUZ.T_AMBULANCE_CARD_RECORD') 
)
SELECT @sql += obj
FROM x;

EXEC sys.sp_executesql @sql;
GO

---


DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + N' NOCHECK CONSTRAINT ' + name + ';
  ' as obj
  FROM sys.foreign_keys
  WHERE referenced_object_id = object_id('AKUZ.T_MEDICAL_RECORD') 
)
SELECT @sql += obj
FROM x;

EXEC sys.sp_executesql @sql;
GO

WHILE (1 = 1)
BEGIN
    DELETE top (50000) e
	FROM AKUZ.T_MEDICAL_RECORD e JOIN #DIAG d on e.MedicalRecordID = d.MedicalRecordID

    IF @@ROWCOUNT = 0
	BREAK

END
GO

DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + N' CHECK CONSTRAINT ' + name + ';
  ' as obj
  FROM sys.foreign_keys
  WHERE referenced_object_id = object_id('AKUZ.T_MEDICAL_RECORD') 
)
SELECT @sql += obj
FROM x;

EXEC sys.sp_executesql @sql;
GO

drop table #DIAG
go


UPDATE AKUZ.T_DIVISION_SIGNIFICANT_EVENTS
SET MedicalEpisode = MovementsDivisionCard
WHERE MovementsDivisionCard is not null

UPDATE AKUZ.T_POST_TREAT
SET MedicalEpisode = MovementsDivisionCard
WHERE MovementsDivisionCard is not null


INSERT INTO AKUZ.T_ASSOCIATED_AND_COMPLICATION_DISEASE (AssociatedAndComplicationDiseaseID, Type, Mkb, Diagnos, Descr, DetectionDate, MedicalEpisode)
SELECT NEWID(), cld.DiagnosisType + 1, COALESCE(d.Mkb, 'Z00.0'), COALESCE(d.Diagnosis, ('Общий медицинский осмотр')), d.Descr, d.RegistrationDate, cld.MovementsDivisionCard
FROM AKUZ.T_CARD_LINK_DIAGNOSIS cld
LEFT JOIN AKUZ.T_DIAGNOSIS d on d.MedicalRecordID = cld.Diagnosis;

EXEC sp_rename 'AKUZ.T_MOVEMENTS_DIVISION_CARD.MovementsDivisionCardID', 'MedicalEpisodeID', 'COLUMN';

ALTER TABLE [AKUZ].[T_MOVEMENTS_DIVISION_CARD] ADD CONSTRAINT [FK_T_MEDICAL_EPISODE_T_MOVEMENTS_DIVISION_CARD_MedicalEpisodeID]
FOREIGN KEY([MedicalEpisodeID]) REFERENCES [AKUZ].[T_MEDICAL_EPISODE] ([MedicalEpisodeID])
GO

DROP TABLE AKUZ.T_CARD_LINK_DIAGNOSIS
GO

DECLARE @entity int
SET @entity = 
(SELECT top 1 e.EntityID
from VCLib.T_ENTITIES e
where e.EntName in (
'CARD_LINK_DIAGNOSIS'
)
and e.[Schema] = 'AKUZ');

Delete col
From VCLib.T_USER_COLUMNS col
	Join VCLib.T_USER_CONTEXTS ctx On col.UserContextID = ctx.UserContextID 
	where ctx.EntityID = @entity

Delete ctx
From VCLib.T_USER_CONTEXTS ctx
	where ctx.EntityID = @entity


delete del 
from VCLib.T_DELETED_OBJECT del
	where del.Entities = @entity

delete fv
From VCLib.T_TEMPLATE_FIELD_VALUE fv
Join VCLib.T_TEMPLATE_FIELD fld On fv.TemplateField = fld.TemplateFieldID
	where fld.Entity = @entity

delete fld
From VCLib.T_TEMPLATE_FIELD fld
	where fld.Entity = @entity

delete rep
From VCLib.T_Report rep
	where rep.EntityID = @entity

delete g_rep
    FROM VCLib.T_GENERIC_REPORT g_rep
	where g_rep.Entities = @entity

DELETE us 
FROM VCLib.T_USER_SEARCHES  us
	where us.EntityID = @entity
		
delete vc
From VCLib.T_DATAFORM_VALIDATION dv
	join VCLib.T_VALIDATION_CONDITION vc on  vc.DataformValidation = dv.Entities
	where dv.Entities = @entity

delete dv
from VCLib.T_DATAFORM_VALIDATION dv
	where dv.Entities = @entity
	
delete dt
from VCLib.T_DATAFORM_TEMPLATE dt
	where dt.Entities = @entity

delete e 
from VCLib.T_ENTITIES_ATTRIBUTES e
	where e.EntityID = @entity

DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + N' NOCHECK CONSTRAINT ' + name + ';
  ' as obj
  FROM sys.foreign_keys
  WHERE referenced_object_id = object_id('VCLib.T_ENTITIES') 
)
SELECT @sql += obj
FROM x;

EXEC sys.sp_executesql @sql;

delete e 
from VCLib.T_ENTITIES e
	where e.EntityID = @entity

GO

DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + N' WITH CHECK CHECK CONSTRAINT ' + name + ';
  ' as obj
  FROM sys.foreign_keys
  WHERE referenced_object_id = object_id('VCLib.T_ENTITIES') 
)
SELECT @sql += obj
FROM x;

EXEC sys.sp_executesql @sql;
GO


/***** Object:  Index [IX_T_POST_TREAT_MovementsDivisionCard_Post]    Script Date: 27.05.2024 15:36:19 *****/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[AKUZ].[T_POST_TREAT]') AND name = N'IX_T_POST_TREAT_MovementsDivisionCard_Post')
BEGIN
DROP INDEX [IX_T_POST_TREAT_MovementsDivisionCard_Post] ON [AKUZ].[T_POST_TREAT]

/***** Object:  Index [IX_T_POST_TREAT_MovementsDivisionCard_Post]    Script Date: 27.05.2024 15:36:19 *****/
CREATE NONCLUSTERED INDEX [IX_T_POST_TREAT_MedicalEpisode_Post] ON [AKUZ].[T_POST_TREAT]
(
	[MedicalEpisode] ASC,
	[Post] ASC
)WITH (SORT_IN_TEMPDB = ON, MAXDOP = 0) ON [PRIMARY]
END
GO