if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_HospReferralLoad_karelia]') and xtype = N'P')
drop procedure [dbo].[sp_HospReferralLoad_karelia]
GO
CREATE procedure [dbo].[sp_HospReferralLoad_karelia] @x xml
as
begin
SELECT 
   T.c.value('./DATA[1]', 'date') AS [DATA]
  ,T.c.value('./TYPE[1]', 'varchar(6)') AS [TYPE]
  ,T.c.value('./ID[1]', 'uniqueidentifier') AS [ID]
INTO #ZGLV
FROM @x.nodes('HOSPITALISATION_REFERRAL//ZGLV') T(c)


SELECT 
  T.c.value('./NOM_NAP[1]', 'varchar(16)') AS [NOM_NAP]
 ,T.c.value('./DTA_NAP[1]', 'date') AS [DTA_NAP]
 ,T.c.value('./FRM_MP[1]', 'int') AS [FRM_MP]
 ,T.c.value('./MCOD_NAP[1]', 'varchar(6)') AS [MCOD_NAP]
 ,T.c.value('./MPODR_NAP[1]', 'varchar(2)') AS [MPODR_NAP]
 ,T.c.value('./MCOD_STC[1]', 'varchar(6)') AS [MCOD_STC]
 ,T.c.value('./MPODR_STC[1]','varchar(12)') AS [MPODR_STC]
 ,T.c.value('./USL_OK[1]', 'int') AS [USL_OK]
 ,T.c.value('./VPOLIS[1]', 'int') AS [VPOLIS]
 ,T.c.value('./SPOLIS[1]', 'varchar(10)') AS [SPOLIS]
 ,T.c.value('./NPOLIS[1]', 'varchar(20)') AS [NPOLIS]
 ,T.c.value('./SMO_CODE[1]', 'varchar(5)') AS [SMO_CODE]
 ,T.c.value('./ST_OKATO[1]', 'varchar(5)') AS [ST_OKATO]
 ,T.c.value('./FAM[1]', 'varchar(40)') AS [FAM]
 ,T.c.value('./IM[1]', 'varchar(40)') AS [IM]
 ,T.c.value('./OT[1]', 'varchar(40)') AS [OT]
 ,T.c.value('./W[1]', 'int') AS [W]
 ,T.c.value('./DR[1]', 'datetime') AS [DR]
 ,T.c.value('./TLF[1]', 'varchar(36)') AS [TLF]
 ,T.c.value('./DS[1]', 'varchar(10)') AS [DS]
 ,T.c.value('./KOD_PFO[1]', 'int') AS [KOD_PFO]
 ,T.c.value('./KOD_PFK[1]', 'int') AS [KOD_PFK]
 ,T.c.value('./KOD_DCT[1]', 'varchar(14)') AS [KOD_DCT]
 ,T.c.value('./DTA_PLN[1]', 'datetime') AS [DTA_PLN]
 ,T.c.value('./insured[1]', 'varchar(36)') as [insured]
 ,T.c.value('./ANOTHER_REGION[1]', 'int') AS [ANOTHER_REGION]
 
 ,T.c.value('./profilbedid[1]', 'varchar(36)') as [profilbedid]--
INTO #ZAP
FROM @x.nodes('HOSPITALISATION_REFERRAL//ZAP') T(c)

create table #Errors
(
    CODE int,
    COMMENT varchar(250)
)

declare @type varchar(6)
set @type = (select zglv.TYPE from #ZGLV zglv)


--Ищем пациентов
update t1
set t1.[insured] =  (
    select  top 1  ins.InsuredID from ISZL.T_INSURED ins
    where ins.LastName = t1.FAM and ins.FirstName = t1.IM and ins.Patronymic = t1.OT and ins.Birthday = t1.DR
 )
from #ZAP t1
where t1.[insured] is null  and @type <> 'Delete'

--step 1 (фамилия, имя, дата рождения)
update t1
set t1.[insured] =  (
    select  top 1 ins.InsuredID from ISZL.T_INSURED ins
    where ins.LastName = t1.FAM and ins.FirstName = t1.IM and ins.Birthday  = t1.DR
 )
from #ZAP t1
where t1.[insured] is null and @type <> 'Delete'


update t1
set t1.[insured] =  (
    select top 1 ipr.Insured from ISZL.T_INSURED_POLICY_RECORD ipr
    join ISZL.T_INSURED ins with (nolock) on ins.InsuredID = ipr.Insured
    where ipr.LastName = t1.FAM and ipr.FirstName = t1.IM and (ins.ENP = t1.NPOLIS)
 )
from #ZAP t1
where t1.[insured] is null and t1.VPOLIS = 3 and @type <> 'Delete'

--step 2 (фамилия, имя, номер+серия полиса)
update t1
set t1.[insured] =  (
    select top 1 ipr.Insured from ISZL.T_INSURED_POLICY_RECORD ipr
    join ISZL.T_INSURED ins with (nolock) on ins.InsuredID = ipr.Insured
    join [ISZL].T_POLICY p with (nolock) on p.PolicyID = ipr.Policy
    where ipr.LastName = t1.FAM and ipr.FirstName = t1.IM and p.Number = t1.NPOLIS and isnull(p.Series,'') = isnull(t1.SPOLIS,'')
 )
from #ZAP t1
where t1.[insured] is null and @type <> 'Delete'

update t1
set t1.[insured] =  (
    select top 1 ipr.Insured from ISZL.T_INSURED_POLICY_RECORD ipr
    join ISZL.T_INSURED ins with (nolock) on ins.InsuredID = ipr.Insured
    join [ISZL].T_POLICY p with (nolock) on p.PolicyID = ipr.Policy
    where ipr.LastName = t1.FAM and ipr.Birthday = t1.DR and p.Number = t1.NPOLIS and isnull(p.Series,'') = isnull(t1.SPOLIS,'')
 )
from #ZAP t1
where t1.[insured] is null and @type <> 'Delete'

    insert into #Errors
    select 1, 'Запись не найдена в ранее загруженных списках'
    from #ZGLV zglv
    where not exists (select * from ISZL.T_HOSPITALIZATION_REFERRAL ref
                      where ref.HospitalizationReferralID = zglv.ID) and @type <> 'Insert'


    insert into #Errors
    select 3, 'Застрахованный не найден ни по ФИО и ДР, ни по номеру и серии полиса'
    from #ZAP zap
    where zap.insured is null and @type <> 'Delete'

    insert into #Errors
    select 4, 'Застрахованный умер'
    from #ZAP zap
    join ISZL.T_INSURED ins with (nolock) on ins.InsuredID = zap.insured
    where ins.DateDeath is not null and zap.DTA_NAP>ins.DateDeath  and @type <> 'Delete'

    insert into #Errors
    select 5, 'Найдено больше одного подразделения с кодом: ' + cast(zap.MPODR_STC as varchar) + '. Для МО:' + cast(zap.MCOD_STC as varchar)
    from #ZAP zap
    where 
    (select COUNT(*) from ISZL.T_BRANCH br 
    where br.BR_CODE = zap.MPODR_STC and br.F003Mo = zap.MCOD_STC) > 1 and @type <> 'Delete'

	  INSERT INTO #Errors
 SELECT 6, 'ошибка'
 FROM ISZL.T_V020_STRUCTURE_BED bed
 JOIN #ZAP zap ON bed.IDK_PR = zap.KOD_PFK
 JOIN ISZL.T_AMOUNT_BED ab WITH (NOLOCK) ON ab.V020_STRUCTURE_BED = bed.IDK_PR AND ab.DIVISION = zap.KOD_PFO


    insert into #Errors
    select 7, 'Найдено больше одного подразделения с кодом: ' + cast(zap.MPODR_NAP as varchar) + '. Для МО:' + cast(zap.MCOD_NAP as varchar)
    from #ZAP zap
    where 
    (select COUNT(*) from ISZL.T_BRANCH br 
    where br.BR_CODE = zap.MPODR_NAP and br.F003Mo = zap.MCOD_NAP) > 1 and @type <> 'Delete'

INSERT INTO #Errors
SELECT 8, 'Форма оказания МП обязательна к заполнению'
FROM #ZAP zap
WHERE zap.USL_OK IN (1, 2) AND zap.FRM_MP IS NULL;

	INSERT INTO #Errors
SELECT 9, 'Профиль койки обязателен к заполнению'
FROM #ZAP zap
WHERE (zap.USL_OK IN (1, 2) AND (zap.KOD_PFK IS NULL))


INSERT INTO #ZAP
SELECT hr.*
FROM ISZL.T_HOSPITALIZATION_REFERRAL hr
LEFT JOIN #ZAP z ON z.MCOD_NAP = hr.LpuMainFrom
    AND z.NOM_NAP = hr.REFERRAL_NUMBER
    AND z.DTA_NAP = hr.REFERRAL_DATE
WHERE z.MCOD_NAP IS NULL;


MERGE INTO ISZL.T_HOSPITALIZATION_REFERRAL AS ref
USING (
    SELECT
        zglv.ID AS HospitalizationReferralID,
        zap.NOM_NAP AS REFERRAL_NUMBER,
        zap.DTA_NAP AS REFERRAL_DATE,
        CASE zap.FRM_MP WHEN 3 THEN 1 WHEN 2 THEN 2 WHEN 1 THEN 3 ELSE 3 END AS HOSPITALIZATION_TYPE,
        zap.KOD_PFK AS V020_STRUCTURE_BED,
        zap.DS AS MKB,
        zap.DTA_PLN AS PLANNED_DATE,
        zap.VPOLIS AS POLICY_TYPE,
        zap.NPOLIS AS POLIS_NUMBER,
        zap.SMO_CODE AS SMO,
        zap.FAM AS LAST_NAME,
        zap.IM AS FIRST_NAME,
        zap.W AS SEX,
        zap.DR AS BIRTHDAY,
        zap.TLF AS PHONE,
        CASE WHEN zap.ANOTHER_REGION = 1 THEN 1 ELSE 0 END AS ANOTHER_REGION,
        2 AS CreatePlace,
        zap.MCOD_NAP AS LpuMainFrom,
        zap.SPOLIS AS POLIS_SERIAL,
        zap.OT AS FATHER_NAME,
        zap.MPODR_NAP AS BRANCH_FROM,
        zap.KOD_PFO AS DIVISION_TO,
        zap.MPODR_STC AS BRANCH_TO,
        zap.USL_OK,
        zap.KOD_DCT AS DOC_CODE,
        zglv.DATA AS CreateDate,
        zap.insured AS Insured,
        zap.MCOD_STC AS LpuMainTo,
        (SELECT TOP 1 PostID FROM iszl.T_POST p WHERE p.Code = zap.KOD_DCT) AS Post
    FROM #ZAP zap, #ZGLV zglv
    WHERE NOT EXISTS (SELECT * FROM #Errors)
) AS source
ON (ref.HospitalizationReferralID = source.HospitalizationReferralID
AND ref.HospitalizationReferralID IS NOT NULL
AND source.HospitalizationReferralID IS NOT NULL)
WHEN MATCHED THEN
    UPDATE SET
        ref.REFERRAL_NUMBER = source.REFERRAL_NUMBER,
        ref.REFERRAL_DATE = source.REFERRAL_DATE,
        ref.HOSPITALIZATION_TYPE = source.HOSPITALIZATION_TYPE,
        ref.V020_STRUCTURE_BED = source.V020_STRUCTURE_BED,
        ref.MKB = source.MKB,
        ref.PLANNED_DATE = source.PLANNED_DATE,
        ref.POLICY_TYPE = source.POLICY_TYPE,
        ref.POLIS_NUMBER = source.POLIS_NUMBER,
        ref.SMO = source.SMO,
        ref.LAST_NAME = source.LAST_NAME,
        ref.FIRST_NAME = source.FIRST_NAME,
        ref.SEX = source.SEX,
        ref.BIRTHDAY = source.BIRTHDAY,
        ref.PHONE = source.PHONE,
        ref.ANOTHER_REGION = source.ANOTHER_REGION,
        ref.CreatePlace = source.CreatePlace,
        ref.LpuMainFrom = source.LpuMainFrom,
        ref.POLIS_SERIAL = source.POLIS_SERIAL,
        ref.FATHER_NAME = source.FATHER_NAME,
        ref.BRANCH_FROM = source.BRANCH_FROM,
        ref.DIVISION_TO = source.DIVISION_TO,
        ref.BRANCH_TO = source.BRANCH_TO,
        ref.USL_OK = source.USL_OK,
        ref.DOC_CODE = source.DOC_CODE,
        ref.CreateDate = source.CreateDate,
        ref.Insured = source.Insured,
        ref.LpuMainTo = source.LpuMainTo,
        ref.Post = source.Post
WHEN NOT MATCHED THEN
    INSERT (
    HospitalizationReferralID,
    REFERRAL_NUMBER,
    REFERRAL_DATE,
    HOSPITALIZATION_TYPE,
    V020_STRUCTURE_BED,
    MKB,
    PLANNED_DATE,
    POLICY_TYPE,
    POLIS_NUMBER,
    SMO,
    LAST_NAME,
    FIRST_NAME,
    SEX,
    BIRTHDAY,
    PHONE,
    ANOTHER_REGION,
    CreatePlace,
    LpuMainFrom,
    POLIS_SERIAL,
    FATHER_NAME,
    BRANCH_FROM,
    DIVISION_TO,
    BRANCH_TO,
    USL_OK,
    DOC_CODE,
    CreateDate,
    Insured,
    LpuMainTo,
        Post
    )
    VALUES (
        source.HospitalizationReferralID,
        source.REFERRAL_NUMBER,
        source.REFERRAL_DATE,
        source.HOSPITALIZATION_TYPE,
        source.V020_STRUCTURE_BED,
        source.MKB,
        source.PLANNED_DATE,
        source.POLICY_TYPE,
        source.POLIS_NUMBER,
        source.SMO,
        source.LAST_NAME,
        source.FIRST_NAME,
        source.SEX,
        source.BIRTHDAY,
        source.PHONE,
        source.ANOTHER_REGION,
        source.CreatePlace,
        source.LpuMainFrom,
        source.POLIS_SERIAL,
        source.FATHER_NAME,
        source.BRANCH_FROM,
        source.DIVISION_TO,
        source.BRANCH_TO,
        source.USL_OK,
        source.DOC_CODE,
        source.CreateDate,
        source.Insured,
        source.LpuMainTo,
        source.Post
    );


if(@type = 'Delete')
begin
    delete ref
    from ISZL.T_HOSPITALIZATION_REFERRAL ref
    join #ZGLV zglv with (nolock) on ref.HospitalizationReferralID = zglv.ID
    where (select count(*) from #Errors) = 0

end

select (case 
       when (select COUNT(*) from #Errors) = 0 
       then cast(1 as bit)
       else case
            when (select COUNT(*) from #Errors) > 0
            then cast(0 as bit)
            end
       end),
       '<?xml version="1.0" encoding="Windows-1251"?>' 
        + 
        (
            SELECT 
                CODE as 'CODE'
                ,COMMENT as 'COMMENT'
            FROM #Errors 
            FOR XML PATH('ERR'),ROOT('ROOT')
        )

DROP TABLE #ZGLV
DROP TABLE #ZAP
DROP TABLE #Errors
END
