
-- Сопутствующие заболевания 

ALTER TABLE [AKUZ].[T_ACCOMPANYING_DISEASES] ADD [IsOsnCompDisease] bit NULL
GO

CREATE table #TMP_ACC  
(
	[ID] [uniqueidentifier] NULL,

	[MovementResult] [uniqueidentifier] NULL,
	[IsOsnCompDisease] [bit] NULL,

	[Post] [uniqueidentifier] NULL,
	[RecordDate] [datetime] NULL,
	[RecordTime] [datetime] NULL,

	[AmbulanceEvent] [uniqueidentifier] NULL,
	[AmbulanceCard] [uniqueidentifier] NULL,
	[PatientBase] [uniqueidentifier] NULL,

	[Mkb] [varchar](8) NULL,
	[Diagnosis] [varchar](max) NULL,
	[RegistrationDate] [datetime] NULL
)

INSERT INTO #TMP_ACC ([ID], [MovementResult], [IsOsnCompDisease], [Post], [RecordDate], [RecordTime], [AmbulanceEvent], [AmbulanceCard], [PatientBase], [Mkb], [Diagnosis], [RegistrationDate])
SELECT NEWID(), cd.MovementResult, cd.IsOsnCompDisease, mr.Post, mr.RecordDate, mr.RecordTime, acr.AmbulanceEvent, acr.AmbulanceCard, acr.PatientBase, COALESCE(d.Mkb, 'Z00.0'), d.Diagnosis, d.RegistrationDate
FROM AKUZ.T_COMPLICATION_DISEASE cd 
JOIN AKUZ.T_DIAGNOSIS d on cd.MedicalRecordID = d.MedicalRecordID
JOIN AKUZ.T_MEDICAL_RECORD mr on cd.MedicalRecordID = mr.MedicalRecordID
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr on cd.MedicalRecordID = acr.MedicalRecordID

CREATE INDEX IX_TMP_ACC ON #TMP_ACC (ID)
GO

DECLARE @newEntityID int
SELECT @newEntityID = EntityID FROM VCLib.T_ENTITIES e WHERE e.EntName = 'ACCOMPANYING_DISEASES' and e.[Schema] = 'AKUZ'

INSERT INTO AKUZ.T_MEDICAL_RECORD ([MedicalRecordID], [Post], [type_], [RecordDate], [RecordTime])
	SELECT t.ID, t.Post, @newEntityID, t.RecordDate, t.RecordTime
	FROM #TMP_ACC t
	
INSERT INTO AKUZ.T_AMBULANCE_CARD_RECORD ([MedicalRecordID], [AmbulanceCard], [AmbulanceEvent], [PostingDate], [PatientBase])
	SELECT t.ID, t.AmbulanceCard, t.AmbulanceEvent, t.RegistrationDate, t.PatientBase
	FROM #TMP_ACC t 

INSERT INTO [AKUZ].[T_OUTCOME_OF_INSPECTION] (MedicalRecordID, ActiveFindDiagnosis, MKBCode, Diagnosis) 
	SELECT t.ID, 0, t.Mkb, t.Diagnosis
	FROM #TMP_ACC t

INSERT INTO [AKUZ].[T_ACCOMPANYING_DISEASES] (MedicalRecordID, OutcomeOfInspection, TypeDisease, IsOsnCompDisease) 
	SELECT t.ID, t.MovementResult, 0, t.IsOsnCompDisease
	FROM #TMP_ACC t
	
drop table #TMP_ACC
go


CREATE table #COMP  
(
	MedicalRecordID [uniqueidentifier] NULL
) 

INSERT INTO #COMP
SELECT MedicalRecordID FROM [AKUZ].[T_COMPLICATION_DISEASE]

CREATE INDEX IX_TMP_COMP ON #COMP (MedicalRecordID)
GO

truncate table akuz.T_COMPLICATION_DISEASE;

    DROP TABLE [AKUZ].[T_COMPLICATION_DISEASE]
    GO

DELETE e
FROM AKUZ.T_DIAGNOSIS e JOIN #COMP cd on e.MedicalRecordID = cd.MedicalRecordID

DELETE e
FROM AKUZ.T_AMBULANCE_CARD_RECORD e JOIN #COMP cd on e.MedicalRecordID = cd.MedicalRecordID

DELETE e
FROM AKUZ.T_MEDICAL_RECORD e JOIN #COMP cd on e.MedicalRecordID = cd.MedicalRecordID

drop table #COMP
go


select EntityID 
INTO #ENTITY
from VCLib.T_ENTITIES e
where e.EntName in (
'COMPLICATION_DISEASE'
)
and e.[Schema] = 'AKUZ'

Delete col
From VCLib.T_USER_COLUMNS col
	Join VCLib.T_USER_CONTEXTS ctx On col.UserContextID = ctx.UserContextID 
	join #ENTITY e on  ctx.EntityID = e.EntityID

Delete ctx
From VCLib.T_USER_CONTEXTS ctx
	join #ENTITY e on  ctx.EntityID = e.EntityID

delete del 
from VCLib.T_DELETED_OBJECT del
	join #ENTITY e on  del.Entities = e.EntityID

delete fv
From VCLib.T_TEMPLATE_FIELD_VALUE fv
Join VCLib.T_TEMPLATE_FIELD fld On fv.TemplateField = fld.TemplateFieldID
	join #ENTITY e on  fld.Entity = e.EntityID

delete fld
From VCLib.T_TEMPLATE_FIELD fld
	join #ENTITY e on  fld.Entity = e.EntityID
		
delete tf
From VCLib.T_DATAFORM_TEMPLATE tf
	join #ENTITY e on  tf.Entities = e.EntityID
		
delete vc
From VCLib.T_DATAFORM_VALIDATION dv
	join #ENTITY e on  dv.Entities = e.EntityID
	join VCLib.T_VALIDATION_CONDITION vc on  vc.DataformValidation = dv.Entities
		
delete dv
From VCLib.T_DATAFORM_VALIDATION dv
	join #ENTITY e on  dv.Entities = e.EntityID

delete rep
From VCLib.T_Report rep
	join #ENTITY e on  rep.EntityID = e.EntityID
	
delete grmi
From VCLib.T_GENERIC_REPORT_MAIN_ITEM grmi
Join VCLib.T_GENERIC_REPORT g_rep On grmi.GenericReport = g_rep.GenericReportID
	join #ENTITY e on  g_rep.Entities = e.EntityID

delete gl
From VCLib.T_GENERATOR_LINK gl
Join VCLib.T_GENERIC_REPORT g_rep On gl.GenericReport = g_rep.GenericReportID
	join #ENTITY e on  g_rep.Entities = e.EntityID

delete g_rep
    FROM VCLib.T_GENERIC_REPORT g_rep
	join #ENTITY e on  g_rep.Entities = e.EntityID

delete sg
From VCLib.T_STATIC_GROUPS sg
Join VCLib.T_GENERIC_REPORT_GROUP grg On grg.GenericReportGroupID = sg.GenericReportGroupID
Join VCLib.T_GENERIC_REPORT_GENERATOR g_rep_g On grg.GrCommonGenerator = g_rep_g.GenericReportGeneratorID
	join #ENTITY e on  g_rep_g.Entities = e.EntityID

delete gr_g
From VCLib.T_GENERIC_REPORT_GROUPBY gr_g
Join VCLib.T_GENERIC_REPORT_GROUP grg On grg.GenericReportGroupID = gr_g.GenericReportGroupID
Join VCLib.T_GENERIC_REPORT_GENERATOR g_rep_g On grg.GrCommonGenerator = g_rep_g.GenericReportGeneratorID
	join #ENTITY e on  g_rep_g.Entities = e.EntityID

delete grg
From VCLib.T_GENERIC_REPORT_GROUP grg
Join VCLib.T_GENERIC_REPORT_GENERATOR g_rep_g On grg.GrCommonGenerator = g_rep_g.GenericReportGeneratorID
	join #ENTITY e on  g_rep_g.Entities = e.EntityID

delete g_rep_g
    FROM VCLib.T_GENERIC_REPORT_GENERATOR g_rep_g
	join #ENTITY e on  g_rep_g.Entities = e.EntityID

delete uoc
    FROM VCLib.T_UNITED_OBJECT_CHANGESET uoc
	join #ENTITY e on  uoc.Entities = e.EntityID

DELETE us 
FROM VCLib.T_USER_SEARCHES  us
	join #ENTITY e on  us.EntityID = e.EntityID

delete eucr
from VCLib.T_EXTRA_UI_COMMAND_ROLES eucr
    join VCLib.T_EXTRA_UI_COMMAND EUC on EUC.ExtraUiCommandID = eucr.ExtraUiCommand
    JOIN #ENTITY ET ON ET.EntityID = EUC.CommandEntity

DELETE EUC
FROM VCLib.T_EXTRA_UI_COMMAND EUC
    JOIN #ENTITY ET ON ET.EntityID = EUC.CommandEntity

delete e 
from VCLib.T_ENTITIES_ATTRIBUTES e
	join #ENTITY ee on  e.EntityID = ee.EntityID

delete e 
from VCLib.T_ENTITIES e
	join #ENTITY ee on  e.EntityID = ee.EntityID

drop table #ENTITY
go
