if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RecomendationsLoad_folder]') and xtype = N'P')
drop procedure [dbo].[sp_RecomendationsLoad_folder]
GO
create procedure [dbo].[sp_RecomendationsLoad_folder] @x xml
 ,@FileID uniqueidentifier = null
 ,@Worker uniqueidentifier = null
as
begin

SELECT 
  T.c.value('./Type[1]', 'varchar(6)') AS [TYPE]
  ,T.c.value('./LPU_T[1]', 'varchar(6)') AS [MO]
INTO #ZGLV
FROM @x.nodes('RECOMENDATIONS/Header') T(c)

SELECT 

 T.c.value('../header[1]/LPU_T[1]', 'varchar(6)') AS [MO]
 ,T.c.value('../header[1]/Type[1]', 'varchar(6)') AS [TYPE]
 ,T.c.value('./Date_K[1]', 'date') AS [Date_K]
 ,T.c.value('./Code_NMIC[1]', 'varchar(2)') AS [Code_NMIC]
 ,T.c.value('./Theme_c[1]', 'int') AS [Theme_c]
 ,T.c.value('./Type_c[1]', 'int') AS [Type_c]
 ,T.c.value('./Code_TMK[1]', 'varchar(100)') AS [Code_TMK]
 ,T.c.value('./Code_Pat[1]', 'varchar(100)') AS [Code_Pat]
 ,T.c.value('./Date_Z[1]', 'date') AS [Date_Z]
 ,T.c.value('./Time_Z[1]', 'varchar(5)') AS [Time_Z]
 ,T.c.value('./Rec[1]', 'varchar(4000)') AS [Rec]
 ,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]', 'date') AS [Dr]
 ,T.c.value('./SS[1]', 'varchar(14)') AS [SS]
 ,T.c.value('./Doctype[1]', 'int') AS [Doctype]
 ,T.c.value('./Docser[1]', 'varchar(20)') AS [Docser]
 ,T.c.value('./Docnum[1]', 'varchar(20)') AS [Docnum]
 ,T.c.value('./ENP[1]', 'varchar(16)') AS [ENP]
 ,T.c.value('./TPOL[1]', 'int') AS [TPOL]
 ,T.c.value('./Spolis[1]', 'varchar(20)') AS [Spolis]
 ,T.c.value('./Npolis[1]', 'varchar(20)') AS [Npolis]
 ,T.c.value('./Din[1]', 'date') AS [Din]
 ,T.c.value('./Dout[1]', 'date') AS [Dout]
 ,T.c.value('./USL_OK[1]', 'int') AS [USL_OK]
 ,T.c.value('./DS1[1]', 'varchar(10)') AS [DS1]
 ,T.c.value('./Date_p[1]', 'date') AS [Date_p]
 ,T.c.value('./Date_v[1]', 'date') AS [Date_v]
 ,T.c.value('./insured[1]', 'uniqueidentifier') AS [insured]--Заглушка
 ,T.c.value('./recomendation[1]', 'uniqueidentifier') AS [recomendation]--Заглушка
INTO #ZAP
FROM @x.nodes('RECOMENDATIONS/Body') T(c)


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


--Ищем пациентов
--step (ENP)
update t1
set t1.[insured] =  (
	select ins.InsuredID from ISZL.T_INSURED ins
	where ins.ENP = t1.ENP
 )
from #ZAP t1
where t1.[insured] is null 

--step (фамилия, имя, отчество, дата рождения)
update t1
set t1.[insured] =  (
	select ins.InsuredID from ISZL.T_INSURED ins
	where ins.LastName = t1.[Fam] and ins.FirstName = t1.[Im] and ins.Patronymic = t1.[Ot] and cast(ins.Birthday as date) = t1.[Dr]
 )
from #ZAP t1
where t1.[insured] is null 

--step (фамилия, имя, дата рождения)
update t1
set t1.[insured] =  (
	select ins.InsuredID from ISZL.T_INSURED ins
	where ins.LastName = t1.[Fam] and ins.FirstName = t1.[Im] and cast(ins.Birthday as date) = t1.[Dr]
 )
from #ZAP t1
where t1.[insured] is null 

--step (фамилия, имя, номер+серия полиса)
update t1
set t1.[insured] =  (
	select ins.InsuredID from ISZL.T_INSURED ins
	left join [ISZL].T_INSURED_POLICY_RECORD record1 on record1.Insured =
	(select top 1 record.Insured from [ISZL].T_INSURED_POLICY_RECORD record where ins.InsuredID = record.Insured and (cast(record.DateBegin as date) <= t1.Date_K and (record.DateEnd is null or cast(record.DateEnd as date) >= t1.Date_K)))
	left join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record1.Policy
	where ins.LastName = t1.[Fam] and ins.FirstName = t1.[Im] and iszlpolicy.Number = t1.[Npolis] and isnull(iszlpolicy.Series,'') = isnull(t1.[Spolis],'')
 )
from #ZAP t1
where t1.[insured] is null 

--step (фамилия, дата рождения, номер+серия полиса)
update t1
set t1.[insured] =  (
	select ins.InsuredID from ISZL.T_INSURED ins
	left join [ISZL].T_INSURED_POLICY_RECORD record1 on record1.Insured =
	(select top 1 record.Insured from [ISZL].T_INSURED_POLICY_RECORD record where ins.InsuredID = record.Insured and (cast(record.DateBegin as date) <= t1.Date_K and (record.DateEnd is null or cast(record.DateEnd as date) >= t1.Date_K)))
	left join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record1.Policy
	where ins.LastName = t1.Fam and cast(ins.Birthday as date) = cast(t1.Dr as date) and iszlpolicy.Number = t1.[Npolis] and isnull(iszlpolicy.Series,'') = isnull(t1.[Spolis],'')
 )
from #ZAP t1
where t1.[insured] is null 

--insert into #Errors
--select 0, 'Ошибка идентификации: застрахованный умер'
--from #ZAP zap
--join ISZL.T_INSURED ins on ins.InsuredID = zap.insured
--where ins.DateDeath is not null

insert into #Errors
select 1, 'Застрахованный не найден в БД. ЕНП ('+ ENP+'). Code_TMK ('+ Code_TMK +')'
from #ZAP 
where insured is null 

update z
set z.[recomendation] = (select top 1 r.ID from iszl.T_RECOMMENDATION r where r.Insured = z.insured and r.Code_TMK = z.Code_TMK and r.[REFERRAL_MO] = z.MO)
from #ZAP z

insert into #Errors
select 2, 'Дубль рекомендации. ЕНП ('+ z.ENP+'). Code_TMK ('+ z.Code_TMK +')'
from #ZAP z
where [TYPE] = 'Insert' and z.[recomendation] is not null

insert into #Errors
select 3, 'Не найдена рекомендация. ЕНП ('+ z.ENP+'). Code_TMK ('+ z.Code_TMK +')'
from #ZAP z
where ([TYPE] = 'Update' or [TYPE] = 'Delete')  and z.recomendation is null

---- DEBUG
--select* from #ZGLV
--select * from #ZAP zap
--select* from #Errors

--DROP TABLE #ZGLV
--DROP TABLE #ZAP
--DROP TABLE #Errors
---- end DEBUG

--если нет ошибок
if ((select [TYPE] from #ZGLV) = 'Insert' and ((select COUNT(*) from #Errors)=0))
begin
	insert into ISZL.T_RECOMMENDATION
	(
		[ID],[DateRec],[DateFact],[Result],[TypesOfConsultations],[MedicalResearchCenters],[ConsultationPurposes]
		,[Insured],[MedicalRecommendation],[NumberOfPatient],[REFERRAL_MO],[USL_OK],[MKB]
		,[Diagnosis],[Date_Z],[Time_Z],[Din],[Dout],[Date_p]
		,[Fam],[Im],[Ot],[W],[Dr],[SS],[Doctype],[Docser],[Docnum],[ENP],[TPOL],[Spolis],[Npolis],[Code_TMK],[FileId],[DateTimeCreate]
	)
	select
		newid(),Date_K,Date_v,null,Type_c,mrc.MedicalResearchCentersID,Theme_c
		,[insured],Rec,Code_Pat,[MO],[USL_OK],DS1
		,null,[Date_Z],[Time_Z],[Din],[Dout],[Date_p]
		,[Fam],[Im],[Ot],[W],[Dr],[SS],[Doctype],[Docser],[Docnum],[ENP],[TPOL],[Spolis],[Npolis],[Code_TMK], @FileID, GetDate()
	from #ZAP z
	left join iszl.T_MEDICAL_RESEARCH_CENTERS mrc on mrc.Code = z.Code_NMIC
end

if ((select [TYPE] from #ZGLV) = 'Update' and ((select COUNT(*) from #Errors)=0))
begin
	update r
	set
      r.[DateRec] = z.Date_K
      ,r.[DateFact] = z.Date_v
      ,r.[Result] = null
      ,r.[TypesOfConsultations] = z.Type_c
      ,r.[MedicalResearchCenters] = mrc.MedicalResearchCentersID
      ,r.[ConsultationPurposes] = z.Theme_c
      ,r.[MedicalRecommendation] = z.Rec
      ,r.[NumberOfPatient] = z.Code_Pat
      ,r.[REFERRAL_MO] = z.[MO]
      ,r.[USL_OK] = z.[USL_OK]
      ,r.[MKB] = z.DS1
      ,r.[Diagnosis] = null
      ,r.[Date_Z] = z.[Date_Z]
      ,r.[Time_Z] = z.[Time_Z]
      ,r.[Din] = z.[Din]
      ,r.[Dout] = z.[Dout]
      ,r.[Date_p] = z.[Date_p]
      ,r.[Fam] = z.[Fam]
      ,r.[Im] = z.[Im]
      ,r.[Ot] = z.[Ot]
      ,r.[W] = z.[W]
      ,r.[Dr] = z.[Dr]
      ,r.[SS] = z.[SS]
      ,r.[Doctype] = z.[Doctype]
      ,r.[Docser] = z.[Docser]
      ,r.[Docnum] = z.[Docnum]
      ,r.[TPOL] = z.[TPOL]
      ,r.[Spolis] = z.[Spolis]
      ,r.[Npolis] = z.[Npolis]
      ,r.[Npolis] = z.[Npolis]
	from ISZL.T_RECOMMENDATION r
	join #ZAP z on z.recomendation = r.ID
	left join iszl.T_MEDICAL_RESEARCH_CENTERS mrc on mrc.Code = z.Code_NMIC

end

if ((select [TYPE] from #ZGLV) = 'Delete' and ((select COUNT(*) from #Errors)=0))
begin
	delete r from ISZL.T_RECOMMENDATION r
	join #ZAP z on z.recomendation = r.ID
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
