-- Tables

-- Alters

ALTER TABLE [AKUZ].[T_ONK_MOVEMENT_CARD] DROP CONSTRAINT [FK_T_ONK_REGISTRY_CARD_T_ONK_MOVEMENT_CARD_OnkRegistryCard]
GO

-- Находим записи в регистре без рег карты но с рег картами в МК
select rcMK.RecordDate, rcMK.OnkRegistryCardID, r.OnkRegistryID 
into #tBaseCards
from AKUZ.T_ONK_REGISTRY r
    join AKUZ.T_ONK_MOVEMENT_CARD mc on mc.OnkRegistry = r.OnkRegistryID
    join AKUZ.T_ONK_REGISTRY_CARD rcMK on rcMK.OnkRegistryCardID = mc.OnkRegistryCard
where not exists
    (
        select * from AKUZ.T_ONK_REGISTRY_CARD rrc
            join AKUZ.T_ONK_REGISTRY rr on rr.OnkRegistryID = rrc.OnkRegistry
        where rr.OnkRegistryID = r.OnkRegistryID
    )

-- Группируем выбирая последнюю по дате рег карту из МК
SELECT t.OnkRegistryID, max(t.OnkRegistryCardID) as OnkRegistryCardID, max(t.RecordDate) as RecordDate
into #tBaseCardsGrouped
FROM #tBaseCards t 
    JOIN (
            SELECT tgrup.OnkRegistryID, max(tgrup.RecordDate) as MaxRecordDate 
            FROM #tBaseCards tgrup GROUP BY tgrup.OnkRegistryID
        ) as t1
        ON t.OnkRegistryID = t1.OnkRegistryID 
            AND t.RecordDate = t1.MaxRecordDate
GROUP BY t.OnkRegistryID

-- Записям в регистре без рег карты проставляем последнюю рег карту по дате из МК
update rc
    set rc.OnkRegistry = rcg.OnkRegistryID -- select *
from AKUZ.T_ONK_REGISTRY_CARD rc
    join #tBaseCardsGrouped rcg on rc.OnkRegistryCardID = rcg.OnkRegistryCardID

-- находим старые в рег карты прикреплёные исключительно к МК
select rc.OnkRegistryCardID, rcOrig.OnkRegistryCardID as Actual_OnkRegistryCardID
    , rc.DeregistrationDate
    , rc.DeathDate
    , rc.InvalidGroup
    , rc.DispDeregistration
    , rc.DeathMKB
    , rc.DeathDiag
    , rc.AutopsyInfo
into #tRegCards -- select *
from AKUZ.T_ONK_REGISTRY_CARD rc
    join AKUZ.T_ONK_MOVEMENT_CARD mc on mc.OnkRegistryCard = rc.OnkRegistryCardID
    join AKUZ.T_ONK_REGISTRY r on r.OnkRegistryID = mc.OnkRegistry
    join AKUZ.T_ONK_REGISTRY_CARD rcOrig on rcOrig.OnkRegistry = r.OnkRegistryID
where not exists    
    (   
        select * from AKUZ.T_ONK_REGISTRY_CARD rrc
            join AKUZ.T_ONK_REGISTRY rr on rr.OnkRegistryID = rrc.OnkRegistry
        where rc.OnkRegistryCardID = rrc.OnkRegistryCardID
    )

-- Проставляем данные из старых рег карт в актуальные рег карты
update d
    set d.OnkRegistryCard = r.Actual_OnkRegistryCardID -- select *
from AKUZ.T_ONK_DIAGNOSIS d
    join #tRegCards r on r.OnkRegistryCardID = d.OnkRegistryCard

update t
    set t.OnkRegistryCard = r.Actual_OnkRegistryCardID -- select *
from AKUZ.T_ONK_TREATMENT t
    join #tRegCards r on r.OnkRegistryCardID = t.OnkRegistryCard

update rct
    set rct.OnkRegistryCard = r.Actual_OnkRegistryCardID -- select *
from AKUZ.T_ONK_REGISTRY_CARD_TALON rct
    join #tRegCards r on r.OnkRegistryCardID = rct.OnkRegistryCard

update pyc
    set pyc.OnkRegistryCard = r.Actual_OnkRegistryCardID -- select *
from AKUZ.T_ONK_PATIENT_YEAR_CONDITION pyc
    join #tRegCards r on r.OnkRegistryCardID = pyc.OnkRegistryCard

update rcActual
    set rcActual.DeregistrationDate = ISNULL(rcActual.DeregistrationDate, rOld.DeregistrationDate)
    , rcActual.DeathDate = ISNULL(rcActual.DeathDate, rOld.DeathDate)
    , rcActual.InvalidGroup = ISNULL(rcActual.InvalidGroup, rOld.InvalidGroup)
    , rcActual.DispDeregistration = ISNULL(rcActual.DispDeregistration, rOld.DispDeregistration)
    , rcActual.DeathMKB = ISNULL(rcActual.DeathMKB, rOld.DeathMKB)
    , rcActual.DeathDiag = ISNULL(rcActual.DeathDiag, rOld.DeathDiag)
    , rcActual.AutopsyInfo = ISNULL(rcActual.AutopsyInfo, rOld.AutopsyInfo)
from AKUZ.T_ONK_REGISTRY_CARD rcActual
    join #tRegCards rOld on rcActual.OnkRegistryCardID = rOld.Actual_OnkRegistryCardID
    
-- Чистим времянки
drop table #tBaseCards
drop table #tBaseCardsGrouped
drop table #tRegCards

-- Удаляем ссылки на рег карты в МК
update mc
    set mc.OnkRegistryCard = null -- select * 
from AKUZ.T_ONK_MOVEMENT_CARD mc
where mc.OnkRegistryCard is not null

-- Удаляем чилды у не прикреплённых рег карт
delete d -- select *
from AKUZ.T_ONK_DIAGNOSIS d
    join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = d.OnkRegistryCard
where rc.OnkRegistry is null

delete so -- select *
from AKUZ.T_ONK_SURGICAL_OPERATION so
    join AKUZ.T_ONK_TREATMENT t on t.OnkTreatmentID = so.OnkTreatment
    join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = t.OnkRegistryCard
where rc.OnkRegistry is null

delete rt -- select *
from AKUZ.T_ONK_RADIATION_THERAPY rt
    join AKUZ.T_ONK_CONDITION_TREAT ct on ct.OnkConditionTreatID = rt.OnkConditionTreatID
    join AKUZ.T_ONK_TREATMENT t on t.OnkTreatmentID = ct.OnkTreatment
    join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = t.OnkRegistryCard
where rc.OnkRegistry is null

delete h -- select *
from AKUZ.T_ONK_HORMONIMMUNTHERAPIE h
    join AKUZ.T_ONK_CONDITION_TREAT ct on ct.OnkConditionTreatID = h.OnkConditionTreatID
    join AKUZ.T_ONK_TREATMENT t on t.OnkTreatmentID = ct.OnkTreatment
    join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = t.OnkRegistryCard
where rc.OnkRegistry is null

delete c -- select *
from AKUZ.T_ONK_CHEMOTHERAPY c
    join AKUZ.T_ONK_CONDITION_TREAT ct on ct.OnkConditionTreatID = c.OnkConditionTreatID
    join AKUZ.T_ONK_TREATMENT t on t.OnkTreatmentID = ct.OnkTreatment
    join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = t.OnkRegistryCard
where rc.OnkRegistry is null

delete ct -- select *
from AKUZ.T_ONK_CONDITION_TREAT ct
    join AKUZ.T_ONK_TREATMENT t on t.OnkTreatmentID = ct.OnkTreatment
    join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = t.OnkRegistryCard
where rc.OnkRegistry is null

delete t -- select *
from AKUZ.T_ONK_TREATMENT t
    join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = t.OnkRegistryCard
where rc.OnkRegistry is null

delete rct -- select *
from AKUZ.T_ONK_REGISTRY_CARD_TALON rct
    join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = rct.OnkRegistryCard
where rc.OnkRegistry is null

delete acr -- select *
from AKUZ.T_AMBULANCE_CARD_RECORD acr
    join AKUZ.T_ONK_REGISTRY_CARD_TALON rct on acr.MedicalRecordID = rct.MedicalRecordID
    join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = rct.OnkRegistryCard
where rc.OnkRegistry is null

delete mr -- select *
from AKUZ.T_MEDICAL_RECORD mr
    join AKUZ.T_ONK_REGISTRY_CARD_TALON rct on mr.MedicalRecordID = rct.MedicalRecordID
    join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = rct.OnkRegistryCard
where rc.OnkRegistry is null

delete pyc -- select *
from AKUZ.T_ONK_PATIENT_YEAR_CONDITION pyc
    join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = pyc.OnkRegistryCard
where rc.OnkRegistry is null

-- Удаляем не прикреплённые рег карты
delete rc -- select * 
from AKUZ.T_ONK_REGISTRY_CARD rc
where rc.OnkRegistry is null

ALTER TABLE [AKUZ].[T_ONK_MOVEMENT_CARD] DROP COLUMN [OnkRegistryCard]
GO