/****** Object:  StoredProcedure [dbo].[sp_SchetLoad_karelia]    Script Date: 26.06.2023 14:53:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SchetLoad_karelia]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SchetLoad_karelia]
GO
-- Author:      Vitacore (с)
-- Create date: 2019-12-19
-- Description: Загрузка счета
-- =============================================
CREATE PROCEDURE [dbo].[sp_SchetLoad_karelia]
    @FileID uniqueidentifier,
    @Xml xml
AS
BEGIN
set @Xml = cast (replace (cast(@xml as nvarchar(max)), '<?xml version="1.0" encoding="Windows-1251" ?>','') as xml) 
--Проверки можно не писать тк счета из МЭ

--Счет
select NEWID()								as SchetID
,T.c.value('../ZGLV[1]/VERSION[1]', 'varchar(5)')	as [VERSION]
,T.c.value('../ZGLV[1]/DATA[1]', 'datetime')	as [DATA]
,T.c.value('../ZGLV[1]/FILENAME[1]', 'varchar(26)')	as [FILENAME]

,T.c.value('./YEAR[1]', 'int')				as [YEAR]
,T.c.value('./MONTH[1]', 'int')				as [MONTH]
,T.c.value('./NSCHET[1]', 'varchar(15)')	as NSCHET
,T.c.value('./DSCHET[1]', 'datetime')		as DSCHET
,T.c.value('./CODE_MO[1]', 'varchar(6)')	as CODE_MO
,T.c.value('./CODE[1]', 'varchar(50)')		as CODE
,T.c.value('./PLAT[1]', 'varchar(5)')		as PLAT

,T.c.value('./DISP[1]', 'varchar(3)')		as DISP
INTO #SCHET
from @Xml.nodes('ZL_LIST/SCHET') T(c)
 
DECLARE cur_SchRec CURSOR
FOR
SELECT t1.SchetID
FROM #SCHET s
JOIN [ISZL].[T_SCHET] t1 ON s.NSCHET = t1.NSCHET
	AND s.[YEAR] = t1.[YEAR]
	AND s.[MONTH] = t1.[MONTH] --and s.[DSCHET] = t1.[DSCHET]
	AND s.[CODE_MO] = t1.[CODE_MO]
	AND s.[CODE] = t1.[CODE]
	AND isnull(s.[FILENAME],convert(varchar(36), newid())) = isnull(t1.[FILENAME],convert(varchar(36), newid()))
GROUP BY t1.SchetID

open cur_SchRec
declare @SchID uniqueidentifier
fetch next from cur_SchRec INTO @SchID
while @@FETCH_STATUS = 0
begin
exec [dbo].[sp_SchetDelete] @SchetID = @SchID
fetch next from cur_SchRec INTO  @SchID
end
close cur_SchRec
deallocate cur_SchRec


--грузим SCHET
insert into [ISZL].[T_SCHET]
	([SchetID],[VERSION],[DATA],[YEAR],[MONTH],[NSCHET],[DSCHET],[ReceivedDate],[FILENAME],[CODE_MO],[PLAT],[SchetFile],[CODE],[DISP])
select 
	[SchetID],[VERSION],[DATA],[YEAR],[MONTH],[NSCHET],[DSCHET],getdate(),[FILENAME],[CODE_MO],[PLAT],@FileID,[CODE],[DISP]
from #SCHET s


--грузим SCHET_ZAP
select NEWID()											as SchetZapID
,T.c.value('(./N_ZAP/text())[1]', 'int')				as N_ZAP
,Feld.N.value('(./ID_PAC/text())[1]', 'varchar(36)')	AS [ID_PAC]
,Feld.N.value('(./VPOLIS/text())[1]', 'int')			AS [VPOLIS]
,Feld.N.value('(./SPOLIS/text())[1]', 'varchar(10)')	AS [SPOLIS]
,Feld.N.value('(./NPOLIS/text())[1]', 'varchar(20)')	AS [NPOLIS] 
,Feld.N.value('(./SMO/text())[1]', 'varchar(5)')		AS [SMO]
,Feld.N.value('(./SS_DOCTOR/text())[1]', 'varchar(11)')		AS SS_DOCTOR
,Feld.N.value('(./ENP/text())[1]', 'varchar(20)')		AS [ENP] 
,Feld.N.value('(./INV/text())[1]', 'int')		AS [INV]
INTO #SCHET_ZAP
from @Xml.nodes('ZL_LIST/ZAP') T(c)
cross apply T.c.nodes('PACIENT') as Feld(N)
--во втором файле
    --<Attribute Name="FAM" Type="string" Description="Фамилия пациента" CharacterMaximumLength="40" />
    --<Attribute Name="IM" Type="string" Description="Имя пациента" CharacterMaximumLength="40" />
    --<Attribute Name="OT" Type="string" Description="Отчество пациента" CharacterMaximumLength="40" />
    --<Attribute Name="DR" Type="date" Description="Дата рождения пациента" Required="1" />
    --<Attribute Name="DOCSER" Type="string" Description="Серия документа" CharacterMaximumLength="10" />
    --<Attribute Name="DOCNUM" Type="string" Description="Номер документа" CharacterMaximumLength="20" />
    --<Attribute Name="SNILS" Type="string" Description="СНИЛС" CharacterMaximumLength="14" />
    --<Attribute Name="DOCDATE" Type="datetime" Description="Дата выдачи документа" />
    --<Attribute Name="DOCORG" Type="text" Description="Наименование органа, выдавшего документ" />
    --<Attribute Name="TEL" Type="string" Description="Номер телефона пациента" CharacterMaximumLength="10" />
    --<Parent Name="W" Type="ISZL.V005_SEX" Description="Пол пациента" Required="1" />
    --<Parent Name="DOCTYPE" Type="ISZL.F011_DOCUMENT_TYPE" Description="Тип документа" CharacterMaximumLength="2" />

--надо найти
    --<Parent Name="InsuredPolicyRecord" Type="ISZL.INSURED_POLICY_RECORD" Description="Полис застрахованного" />
    --<Parent Name="Insured" Type="ISZL.INSURED" Description="Застрахованный" />
    --<Attribute Name="ENP" Type="string" Description="ЕНП" CharacterMaximumLength="16" />

insert into [ISZL].[T_SCHET_ZAP]
([SchetZapID],[SPOLIS],[NPOLIS],[FAM],[IM],[OT],[DR],[DOCSER],[DOCNUM],[SNILS],[VPOLIS]
	,[W],[DOCTYPE],[Schet],[InsuredPolicyRecord],[F002smo],[DOCDATE],[DOCORG],[ID_PAC],[ENP],[TEL],[Insured],[N_ZAP],SS_DOCTOR,[INV])
select sz.SchetZapID,sz.SPOLIS,ISNULL(sz.NPOLIS, sz.ENP),null,null,null,null,null,null,null,sz.VPOLIS
	,null,null,(select SchetID from #SCHET), null,sz.SMO,null,null,sz.ID_PAC, null,null,null,sz.N_ZAP,SS_DOCTOR,sz.INV
from #SCHET_ZAP sz

create index IX_#SCHET_ZAP_N_ZAP on #SCHET_ZAP(N_ZAP, ID_PAC, NPOLIS) include(SchetZapID)


--грузим SCHET_SLUCH_ACCOMPLISHED
select NEWID()                                                      as SchetSluchAccomplishedAID
,T.c.value('(./N_ZAP/text())[1]', 'int')                                    as N_ZAP
,ZSL.N.value('(./IDCASE/text())[1]', 'varchar(36)')                                    as IDCASE
,ZSL.N.value('(./USL_OK/text())[1]', 'int')                                    as USL_OK
,T.c.value('../SCHET[1]/CODE_MO[1]', 'varchar(6)')               as MO
,case when len(ZSL.N.value('(./DATE_Z_1/text())[1]', 'varchar(20)')) > 8 then ZSL.N.value('(./DATE_Z_1/text())[1]', 'datetime') end AS [DATE_Z_1]
,case when len(ZSL.N.value('(./DATE_Z_2/text())[1]', 'varchar(20)')) > 8 then ZSL.N.value('(./DATE_Z_2/text())[1]', 'datetime') end  AS [DATE_Z_2]
,ZSL.N.value('(./P_OTK/text())[1]', 'int')										as P_OTK
,ZSL.N.value('(./RSLT_D/text())[1]', 'int')									as RSLT_D
,ZSL.N.value('(./RSLT/text())[1]', 'int')                                      as RSLT
,ZSL.N.value('(./ISHOD/text())[1]', 'int')                                     as ISHOD
,ZSL.N.value('(./VIDPOM/text())[1]', 'int')                                    as VIDPOM
,ZSL.N.value('(./FOR_POM/text())[1]', 'int')                                   as FOR_POM
,ZSL.N.value('(./NPR_MO/text())[1]', 'varchar(6)')                             as NPR_MO
,case when len(ZSL.N.value('(./NPR_DATE/text())[1]', 'varchar(20)')) > 8 then ZSL.N.value('(./NPR_DATE/text())[1]', 'datetime') end AS [NPR_DATE]
,ZSL.N.value('(./NPR_N/text())[1]', 'varchar(10)')                             as NPR_N --этих сведений нету в формате по 79 приказу
,ZSL.N.value('(./P_DISP2/text())[1]', 'int')									as P_DISP2 --этих сведений нету в формате по 79 приказу
,Pac.N.value('(./ID_PAC/text())[1]', 'varchar(36)')                as ID_PAC
,Pac.N.value('(./VPOLIS/text())[1]', 'int')                        as VPOLIS
,Pac.N.value('(./SPOLIS/text())[1]', 'varchar(10)')                as SPOLIS
,Pac.N.value('(./NPOLIS/text())[1]', 'varchar(20)')                as NPOLIS
,Pac.N.value('(./SMO/text())[1]', 'varchar(5)')                    as SMO

,ZSL.N.value('(./NOM_NAP/text())[1]', 'varchar(16)')							    as NOM_NAP
,ZSL.N.value('(./VID_NAP_IS/text())[1]', 'int')										as VID_NAP_IS
,ZSL.N.value('(./NOM_NAP_IS/text())[1]', 'varchar(16)')							    as NOM_NAP_IS
INTO #SSA
from @Xml.nodes('ZL_LIST/ZAP') T(c)
cross apply T.c.nodes('PACIENT') as Pac(N)
cross apply T.c.nodes('Z_SL') as ZSL(N);

--create index IX_#SSA_N_ZAP on #SSA(N_ZAP, ID_PAC, NPOLIS)
create index IX_#SSA_IDCASE on #SSA(IDCASE, N_ZAP)

insert into [ISZL].[T_SCHET_SLUCH_ACCOMPLISHED]
([SchetSluchAccomplishedID],[IDCASE],[USL_OK],[DATE_Z_1]
,[DATE_Z_2],[RSLT],[ISHOD],[SchetZap],[P_OTK],[RSLT_D],[MO]
,[VIDPOM],[FOR_POM],[NPR_MO],[NPR_DATE],[NPR_N],[P_DISP2],[NOM_NAP],[VID_NAP_IS],[NOM_NAP_IS])
select ssa.SchetSluchAccomplishedAID,ssa.IDCASE,ssa.USL_OK,ssa.DATE_Z_1
,ssa.DATE_Z_2,ssa.RSLT,ssa.ISHOD,sz.SchetZapID,ssa.P_OTK,ssa.RSLT_D,ssa.MO
,ssa.[VIDPOM],ssa.[FOR_POM],ssa.[NPR_MO],ssa.[NPR_DATE],ssa.[NPR_N],ssa.[P_DISP2],ssa.[NOM_NAP],ssa.[VID_NAP_IS],ssa.[NOM_NAP_IS]
from #SSA ssa
	join #SCHET_ZAP sz on sz.N_ZAP=ssa.N_ZAP --and sz.ID_PAC = ssa.ID_PAC and sz.NPOLIS = ssa.NPOLIS


--грузим SCHET_SLUCH
select NEWID()																	as SchetSluchID
,T.c.value('(./N_ZAP/text())[1]', 'int')										as N_ZAP
--,ZSL.N.value('(../IDCASE/text())[1]', 'int')									as IDCASE
,ZSL.N.value('(./IDCASE/text())[1]', 'varchar(11)')								as IDCASE
,SL.N.value('(./IDCASE/text())[1]', 'bigint')									as IDCASE_OLDID
,SL.N.value('(./SL_ID/text())[1]', 'varchar(36)')								as SL_ID

,SL.N.value('(./DN_NAZ/text())[1]', 'datetime')							    	as DN_NAZ   --new

,SL.N.value('(./NHISTORY/text())[1]', 'varchar(50)')							as NHISTORY
,CASE WHEN LEN(SL.N.value('(./DATE_1/text())[1]', 'varchar(20)'))>8 THEN SL.N.value('(./DATE_1/text())[1]', 'datetime') END AS [DATE_1]
,CASE WHEN LEN(SL.N.value('(./DATE_2/text())[1]', 'varchar(20)'))>8 THEN SL.N.value('(./DATE_2/text())[1]', 'datetime') END  AS [DATE_2]
,SL.N.value('(./DS1/text())[1]', 'varchar(10)')									as DS1
,SL.N.value('(./DS2/text())[1]', 'varchar(10)')									as DS2
,T.c.value('../SCHET[1]/CODE_MO[1]', 'varchar(6)')								as MO
,ZSL.N.value('(./RSLT/text())[1]', 'int')										as RSLT -- ?
,ZSL.N.value('(./ISHOD/text())[1]', 'int')										as ISHOD -- ?
,SL.N.value('(./PROFIL_K/text())[1]', 'int')									as PROFIL_K
,SL.N.value('(./PR_D_N/text())[1]', 'int')										as PR_D_N --д-счета--?
,SL.N.value('(./DN/text())[1]', 'int')											as DN --остальные счета--?
,SL.N.value('(./RSLT_D/text())[1]', 'int')										as RSLT_D -- ?
,SL.N.value('(./DISP/text())[1]', 'bit')										as DISP -- ?

,SL.N.value('(./C_ZAB/text())[1]', 'int')										as C_ZAB
,SL.N.value('(./PRVS/text())[1]', 'int')										as PRVS3
,SL.N.value('(./DS_ONK/text())[1]', 'bit')										as DSONK
,SL.N.value('(./CODE_MES1/text())[1]', 'varchar(20)')							as CODE_MES1
,SL.N.value('(./CODE_MES2/text())[1]', 'varchar(20)')							as CODE_MES2
,SL.N.value('(./VID_HMP/text())[1]', 'varchar(16)')								as VID_HMP
,SL.N.value('(./METOD_HMP/text())[1]', 'int')							        as METOD_HMP
,SL.N.value('(./P_CEL/text())[1]', 'varchar(3)')							    as P_CEL
,SL.N.value('(./IDDOKT/text())[1]', 'varchar(25)')							    as IDDOKT
INTO #SS
from @Xml.nodes('ZL_LIST/ZAP') T(c)
cross apply T.c.nodes('Z_SL') as ZSL(N)
cross apply ZSL.N.nodes('SL') as SL(N)

create index IX_#SS_N_ZAP on #SS(N_ZAP) include(IDCASE)
create index IX_#SS_IDCASE on #SS(IDCASE, SL_ID) include(SchetSluchID)

--посоветоваться!!!
update ss
set ss.DISP = case when (select [FILENAME] from #SCHET) like 'D%' then cast(1 as bit) else cast(0 as bit) end
from #SS ss
where ss.DISP is null
--Для старых счетов?    <Attribute Name="IDCASE" Type="bigint" Description="Номер записи в реестре случаев" Required="1" />
--    <Parent Name="SchetZap" Type="ISZL.SCHET_ZAP" Description="Запись счет-реестра" Required="1" />
--    <Parent Name="Mkb" Type="ISZL.MKB" Description="МКБ-10 (по справочнику)" />
--    <Child Name="SchetUsls" Type="ISZL.SCHET_USL" Description="Услуги по случаю" ChildAttribute="SchetSluch" />
--    <Child Name="SchetSluchNazs" Type="ISZL.SCHET_SLUCH_NAZ" Description="Назначения(v3.0)" ChildAttribute="SchetSluch" />
--    <Parent Name="SchetSluchAccomplished" Type="ISZL.SCHET_SLUCH_ACCOMPLISHED" Description="Законченный случай" />
--    <Child Name="NAZR" Type="ISZL.SCHET_SLUCH_DEST" Description="Назначения(v2.1)" ChildAttribute="OmsSchetSluch" />
--    <Attribute Name="DISP" Type="int" Description="Признак диспансеризации" />--пока хз

--<Child Name="SchetSluchDss" Type="ISZL.SCHET_SLUCH_DS" Description="Диагнозы в случае" ChildAttribute="SchetSluch" />
--<Child Name="SchetUslNaprs" Type="ISZL.SCHET_USL_NAPR" Description="Направления по онкологии" ChildAttribute="SchetSluch" />
--<Child Name="SchetSluchConss" Type="ISZL.SCHET_SLUCH_CONS" Description="Сведения о проведении консилиума" ChildAttribute="SchetSluch" />
--<Child Name="SchetSluchSanks" Type="ISZL.SCHET_SLUCH_SANK" Description="Санкция по случаю" ChildAttribute="SchetSluch" />
--<Link Name="SchetSluchOnk" Type="ISZL.SCHET_SLUCH_ONK" Description="Сведения о случае лечения онкологического заболевания" LinkAttribute="SchetSluch" />
insert into [ISZL].[T_SCHET_SLUCH]
([SchetSluchID],[IDCASE],[SL_ID],[NHISTORY],[DATE_1],[DATE_2],[DS1],[DS2],[LPU],[RSLT],[ISHOD],[SchetZap]
,[Mkb],[SchetSluchAccomplished],[PROFIL_K],[DN],[RSLT_D],[DISP]
,[C_ZAB],[PRVS3],[DSONK],[CODE_MES1],[CODE_MES2],[VID_HMP],[METOD_HMP],[P_CEL],[IDDOKT],[DN_NAZ])--,[PRVS],[PRVS2]
select ss.SchetSluchID,ss.IDCASE_OLDID,ss.SL_ID,ss.NHISTORY,ss.DATE_1,ss.DATE_2,ss.DS1,ss.DS2,ss.MO,ss.RSLT,ss.ISHOD,sz.SchetZapID
,(select top 1 m.MkbID from ISZL.T_MKB m where m.MCBCode = ss.[DS1]),ssa.SchetSluchAccomplishedAID,ss.PROFIL_K,isnull(ss.DN,ss.PR_D_N),ss.RSLT_D,ss.DISP
,[C_ZAB],[PRVS3],isnull([DSONK],0),[CODE_MES1],[CODE_MES2],[VID_HMP],[METOD_HMP],[P_CEL],[IDDOKT],[DN_NAZ]--,[PRVS],[PRVS2]
from #SS ss
	join #SCHET_ZAP sz on sz.N_ZAP=ss.N_ZAP --and sz.ID_PAC = ss.ID_PAC and sz.NPOLIS = ss.NPOLIS
	join #SSA ssa on ssa.N_ZAP=ss.N_ZAP  and ssa.IDCASE = ss.IDCASE--and ssa.ID_PAC = ss.ID_PAC and ssa.NPOLIS = ss.NPOLIS


--грузим SchetSluchOnk Сведения о случае лечения онкологического заболевания 
select NEWID()															as SchetSluchOnkID
--,T.c.value('../../../N_ZAP[1]', 'int')									as N_ZAP
,T.c.value('../../IDCASE[1]', 'varchar(11)')									as IDCASE
,T.c.value('../IDCASE[1]', 'bigint')										as IDCASE_OLDID
,T.c.value('../SL_ID[1]', 'varchar(36)')								as SL_ID
,T.c.value('../NHISTORY[1]', 'varchar(50)')								as NHISTORY

,T.c.value('./WEI[1]', 'decimal(4,1)')								as WEI
,T.c.value('./K_FR[1]', 'int')										as K_FR
,T.c.value('./SOD[1]', 'decimal(6,2)')								as SOD
,T.c.value('./STAD[1]', 'int')										as STAD
,T.c.value('./DS1_T[1]', 'int')										as DS1_T
,T.c.value('./ONK_T[1]', 'int')										as ONK_T
,T.c.value('./ONK_N[1]', 'int')										as ONK_N
,T.c.value('./ONK_M[1]', 'int')										as ONK_M
,T.c.value('./MTSTZ[1]', 'bit')										as MTSTZ
,T.c.value('./HEI[1]', 'int')										as HEI
,T.c.value('./BSA[1]', 'decimal(5,2)')								as BSA

--,T.c.value('../../../PACIENT[1]/ID_PAC[1]', 'varchar(36)')              as ID_PAC
--,T.c.value('../../../PACIENT[1]/NPOLIS[1]', 'varchar(20)')              as NPOLIS
INTO #SSONK
from @Xml.nodes('ZL_LIST/ZAP/Z_SL/SL/ONK_SL') T(c)

insert into [ISZL].[T_SCHET_SLUCH_ONK]
([SchetSluchOnkID],[WEI],[K_FR],[SOD],[Diagnosis],[STAD],[DS1_T]
,[ONK_T],[ONK_N],[ONK_M],[MTSTZ],[SchetSluch],[HEI],[BSA])
select ssonk.[SchetSluchOnkID],ssonk.[WEI],ssonk.[K_FR],ssonk.[SOD],(select top 1 m.Name from ISZL.T_MKB m where m.MCBCode = ss.DS1),ssonk.[STAD],ssonk.[DS1_T]
,ssonk.[ONK_T],ssonk.[ONK_N],ssonk.[ONK_M],isnull(ssonk.[MTSTZ],0),ss.[SchetSluchID],ssonk.[HEI],ssonk.[BSA]
from #SSONK ssonk
	join #SS ss on ss.IDCASE = ssonk.IDCASE and ss.SL_ID = ssonk.SL_ID --ss.N_ZAP=ssonk.N_ZAP and ss.ID_PAC = ssonk.ID_PAC and ss.NPOLIS = ssonk.NPOLIS and ss.NHISTORY = bd.NHISTORY

--грузим SCHET_SLUCH_ONK_DIAG Диагностический блок 
select NEWID()															as SchetSluchOnkDiagID
--,T.c.value('../../../../N_ZAP[1]', 'int')								as N_ZAP
,T.c.value('../../../IDCASE[1]', 'varchar(11)')									as IDCASE
--,T.c.value('../../IDCASE[1]', 'bigint')									as IDCASE_OLDID
,T.c.value('../../SL_ID[1]', 'varchar(36)')								as SL_ID
--,T.c.value('../../NHISTORY[1]', 'varchar(50)')							as NHISTORY

,T.c.value('./DIAG_DATE[1]', 'datetime')								as DIAG_DATE
,T.c.value('./DIAG_TIP[1]', 'int')										as DIAG_TIP
,T.c.value('./DIAG_CODE[1]', 'int')										as DIAG_CODE
,T.c.value('./DIAG_RSLT[1]', 'int')										as DIAG_RSLT
,T.c.value('./REC_RSLT[1]', 'bit')										as REC_RSLT

--,T.c.value('../../../../PACIENT[1]/ID_PAC[1]', 'varchar(36)')           as ID_PAC
--,T.c.value('../../../../PACIENT[1]/NPOLIS[1]', 'varchar(20)')           as NPOLIS
INTO #B_DIAG
from @Xml.nodes('ZL_LIST/ZAP/Z_SL/SL/ONK_SL/B_DIAG') T(c)

insert into [ISZL].[T_SCHET_SLUCH_ONK_DIAG]
([SchetSluchOnkDiagID],[DIAG_DATE],[DIAG_TIP],[DIAG_CODE],[DIAG_RSLT],[REC_RSLT],[SchetSluchOnk])
select bd.SchetSluchOnkDiagID,bd.[DIAG_DATE],bd.[DIAG_TIP],bd.[DIAG_CODE],bd.[DIAG_RSLT],isnull(bd.[REC_RSLT],0),ssonk.SchetSluchOnkID
from #B_DIAG bd
	join #SS ss on ss.IDCASE = bd.IDCASE and ss.SL_ID = bd.SL_ID --ss.N_ZAP=bd.N_ZAP and ss.ID_PAC = bd.ID_PAC and ss.NPOLIS = bd.NPOLIS and ss.NHISTORY = bd.NHISTORY 
	join [ISZL].[T_SCHET_SLUCH_ONK] ssonk on ssonk.SchetSluch = ss.SchetSluchID

--грузим SCHET_SLUCH_ONK_PROT Сведения об имеющихся противопоказаниях и отказах 
select NEWID()															as SchetSluchOnkProtID
--,T.c.value('../../../../N_ZAP[1]', 'int')								as N_ZAP
,T.c.value('../../../IDCASE[1]', 'varchar(11)')									as IDCASE
--,T.c.value('../../IDCASE[1]', 'bigint')									as IDCASE_OLDID
,T.c.value('../../SL_ID[1]', 'varchar(36)')								as SL_ID
--,T.c.value('../../NHISTORY[1]', 'varchar(50)')							as NHISTORY

,T.c.value('./D_PROT[1]', 'datetime')								as D_PROT
,T.c.value('./PROT[1]', 'int')										as PROT

--,T.c.value('../../../../PACIENT[1]/ID_PAC[1]', 'varchar(36)')           as ID_PAC
--,T.c.value('../../../../PACIENT[1]/NPOLIS[1]', 'varchar(20)')           as NPOLIS
INTO #B_PROT
from @Xml.nodes('ZL_LIST/ZAP/Z_SL/SL/ONK_SL/B_PROT') T(c)

insert into [ISZL].[T_SCHET_SLUCH_ONK_PROT]
([SchetSluchOnkProtID],[D_PROT],[PROT],[SchetSluchOnk])
select bp.SchetSluchOnkProtID,bp.D_PROT,bp.[PROT],ssonk.SchetSluchOnkID
from #B_PROT bp
	join #SS ss on ss.IDCASE = bp.IDCASE and ss.SL_ID = bp.SL_ID--ss.N_ZAP=bp.N_ZAP and ss.ID_PAC = bp.ID_PAC and ss.NPOLIS = bp.NPOLIS and ss.NHISTORY = bp.NHISTORY 
	join [ISZL].[T_SCHET_SLUCH_ONK] ssonk on ssonk.SchetSluch = ss.SchetSluchID

--грузим SCHET_USL_ONK Сведения об услугах при лечении ЗНО 
select NEWID()															as SchetUslOnkID
--,T.c.value('../../../../N_ZAP[1]', 'int')								as N_ZAP
,T.c.value('../../../IDCASE[1]', 'varchar(11)')									as IDCASE
--,T.c.value('../../IDCASE[1]', 'bigint')									as IDCASE_OLDID
,T.c.value('../../SL_ID[1]', 'varchar(36)')								as SL_ID
--,T.c.value('../../NHISTORY[1]', 'varchar(50)')							as NHISTORY

,T.c.value('./USL_TIP[1]', 'int')										as USL_TIP
,T.c.value('./HIR_TIP[1]', 'int')										as HIR_TIP
,T.c.value('./LEK_TIP_L[1]', 'int')										as LEK_TIP_L
,T.c.value('./LEK_TIP_V[1]', 'int')										as LEK_TIP_V
,T.c.query('./LEK_PR')                                                  as LEK_PR
,T.c.value('./PPTR[1]', 'bit')											as PPTR
,T.c.value('./LUCH_TIP[1]', 'int')										as LUCH_TIP

--,T.c.value('../../../../PACIENT[1]/ID_PAC[1]', 'varchar(36)')           as ID_PAC
--,T.c.value('../../../../PACIENT[1]/NPOLIS[1]', 'varchar(20)')           as NPOLIS
INTO #ONK_USL
from @Xml.nodes('ZL_LIST/ZAP/Z_SL/SL/ONK_SL/ONK_USL') T(c)

insert into [ISZL].[T_SCHET_USL_ONK]
([SchetUslOnkID],[USL_TIP],[HIR_TIP],[LEK_TIP_L],[LEK_TIP_V],[PPTR],[SchetSluchOnk],[LUCH_TIP])
select ou.[SchetUslOnkID],ou.[USL_TIP],ou.[HIR_TIP],ou.[LEK_TIP_L],ou.[LEK_TIP_V],isnull(ou.[PPTR],0),ssonk.SchetSluchOnkID,ou.[LUCH_TIP]
from #ONK_USL ou
	join #SS ss on  ss.IDCASE = ou.IDCASE and ss.SL_ID = ou.SL_ID --ss.N_ZAP=ou.N_ZAP and ss.ID_PAC = ou.ID_PAC and ss.NPOLIS = ou.NPOLIS andss.NHISTORY = ou.NHISTORY 
	join [ISZL].[T_SCHET_SLUCH_ONK] ssonk on ssonk.SchetSluch = ss.SchetSluchID

--грузим сведения о введенном противоопухолевом лекарственном препарате
select newid() as SchetUslOnkLekPrID, tr.SchetUslOnkID, lp.REGNUM, lp.CODE_SH, tr.LEK_PR--, lp.DATE_INJ
into #LEK_PR
from #ONK_USL tr
cross apply
(
	select --CASE WHEN LEN(T.c.value('./DATE_INJ[1]', 'varchar(20)'))>8 THEN T.c.value('./DATE_INJ[1]', 'datetime') ELSE NULL END   AS DATE_INJ
	--,
	 T.c.value('./REGNUM[1]', 'varchar(6)')											as REGNUM
	,T.c.value('./CODE_SH[1]', 'varchar(10)')										as CODE_SH
	from tr.LEK_PR.nodes('LEK_PR') T(c)
) lp

INSERT INTO [ISZL].T_SCHET_USL_ONK_LEK_PR
(SchetUslOnkLekPrID /*,[DATE_INJ]*/ ,[SchetUslOnk] ,[REGNUM] ,[CODE_SH])
SELECT SchetUslOnkLekPrID/*,lp.DATE_INJ*/,lp.SchetUslOnkID,lp.REGNUM,lp.CODE_SH
from #LEK_PR lp

insert into ISZL.T_SCHET_USL_ONK_LEK_PR_DATE(SchetUslOnkLekPrDateID, DATE_INJ, SchetUslOnkLekPr)
select newid(), d.DATE_INJ, lp.SchetUslOnkLekPrID
from #LEK_PR lp
cross apply
(
	
	select CASE WHEN LEN(m.n.value('.[1]', 'varchar(20)'))>8 THEN m.n.value('.[1]', 'datetime') END AS DATE_INJ
	from lp.LEK_PR.nodes('LEK_PR/DATE_INJ') as m(n)
) d

--грузим SCHET_SLUCH_SANK Санкция по случаю 
select  NEWID()                                                     as SchetSluchSankID
,T.c.value('../IDCASE[1]', 'varchar(11)')                                   as IDCASE
,T.c.value('(./SL_ID/text())[1]', 'varchar(36)')								as SL_ID
,T.c.value('(./S_CODE/text())[1]', 'varchar(36)')                            as S_CODE
,T.c.value('(./S_TIP/text())[1]', 'int')                                     as S_TIP
,T.c.value('(./S_VID/text())[1]', 'int')                                     as S_VID--в 79 нету этого тега. мб в страых форматах?
,CASE WHEN LEN(T.c.value('(./DATE_ACT/text())[1]', 'varchar(20)'))>8 THEN T.c.value('(./DATE_ACT/text())[1]', 'datetime') ELSE NULL END   AS [DATE_ACT]
,T.c.value('(./NUM_ACT/text())[1]', 'varchar(30)')                           as NUM_ACT
,T.c.value('(./S_IST/text())[1]', 'int')                                     as S_IST
,T.c.value('./S_OSN[1]', 'int')												as S_OSN
INTO #SANK
from @Xml.nodes('ZL_LIST/ZAP/Z_SL/SANK') T(c)

INSERT INTO [ISZL].[T_SCHET_SLUCH_SANK]
([SchetSluchSankID],[S_CODE],[SchetSluch],
[S_TIP],[S_IST],[S_VID],[SchetSluchAccomplished],[DATE_ACT],[NUM_ACT],[S_OSN])
SELECT sank.SchetSluchSankID, sank.[S_CODE],ss.SchetSluchID,
sank.[S_TIP],sank.[S_IST],sank.[S_VID],ssa.SchetSluchAccomplishedAID,sank.[DATE_ACT],sank.[NUM_ACT],sank.S_OSN
from #SANK sank
    --join #SCHET_ZAP sz on sz.N_ZAP=sank.N_ZAP and sz.ID_PAC = sank.ID_PAC and sz.NPOLIS = sank.NPOLIS
	join #SSA ssa on  ssa.IDCASE = sank.IDCASE --ssa.N_ZAP=sank.N_ZAP and ssa.ID_PAC = sank.ID_PAC and ssa.NPOLIS = sank.NPOLIS and
	left join #SS ss on ss.SL_ID = sank.SL_ID and ss.IDCASE = ssa.IDCASE --and ss.N_ZAP = sz.N_ZAP 

	
--грузим SCHET_SLUCH_SANK 2
select  NEWID()                                                     as SchetSluchSankID
--,T.c.value('../../N_ZAP[1]', 'int')									as N_ZAP
,T.c.value('../../IDCASE[1]', 'bigint')								AS [IDCASE]
,T.c.value('../SL_ID[1]', 'varchar(36)')								as SL_ID
,T.c.value('./S_CODE[1]', 'varchar(36)')                            as S_CODE
,T.c.value('./S_TIP[1]', 'int')                                     as S_TIP
,T.c.value('./S_VID[1]', 'int')                                     as S_VID--в 79 нету этого тега. мб в страых форматах?
,CASE WHEN LEN(T.c.value('./DATE_ACT[1]', 'varchar(20)'))>8 THEN T.c.value('./DATE_ACT[1]', 'datetime') ELSE NULL END   AS [DATE_ACT]
,T.c.value('./NUM_ACT[1]', 'varchar(30)')                           as NUM_ACT
,T.c.value('./S_IST[1]', 'int')                                     as S_IST
,T.c.value('./S_OSN[1]', 'int')												as S_OSN
INTO #SANK_SL
from @Xml.nodes('ZL_LIST/ZAP/Z_SL/SL/SANK') T(c)

INSERT INTO [ISZL].[T_SCHET_SLUCH_SANK]
([SchetSluchSankID],[S_CODE],[SchetSluch],
[S_TIP],[S_IST],[S_VID],[SchetSluchAccomplished],[DATE_ACT],[NUM_ACT],[S_OSN])
SELECT sank.SchetSluchSankID, sank.[S_CODE],ss.SchetSluchID,
sank.[S_TIP],sank.[S_IST],sank.[S_VID],ssa.SchetSluchAccomplishedAID,sank.[DATE_ACT],sank.[NUM_ACT],sank.S_OSN
from #SANK_SL sank
    --join #SCHET_ZAP sz on sz.N_ZAP=sank.N_ZAP and sz.ID_PAC = sank.ID_PAC and sz.NPOLIS = sank.NPOLIS
	join #SSA ssa on  ssa.IDCASE = sank.IDCASE --ssa.N_ZAP=sank.N_ZAP and ssa.ID_PAC = sank.ID_PAC and ssa.NPOLIS = sank.NPOLIS and
	left join #SS ss on ss.SL_ID = sank.SL_ID and ss.IDCASE = ssa.IDCASE --and ss.N_ZAP = sz.N_ZAP 

--грузим SCHET_SLUCH_DS Диагнозы в случае 
SELECT
  T.c.value('../../IDCASE[1]', 'bigint') AS [IDCASE]
 ,T.c.value('../SL_ID[1]', 'varchar(36)') AS [SL_ID] 
 ,T.c.value('.', 'varchar(10)') AS [DS]
 , cast(null as int) as [DS2_PR]
 , cast(null as int) as [PR_DS2_N]
 ,0 AS DS_TYPE
 INTO #DS
FROM   @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/DS2') T(c)
union all
SELECT
  T.c.value('../../IDCASE[1]', 'bigint') AS [IDCASE]
 ,T.c.value('../SL_ID[1]', 'varchar(36)') AS [SL_ID] 
 ,T.c.value('./DS2[1]', 'varchar(10)') AS [DS]
 ,T.c.value('./DS2_PR[1]', 'int') AS [DS2_PR]
 ,T.c.value('./PR_DS2_N[1]', 'int') AS [PR_DS2_N]
 ,0 AS DS_TYPE
FROM   @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/DS2_N') T(c)
union all
SELECT
  T.c.value('../../IDCASE[1]', 'bigint') AS [IDCASE]
 ,T.c.value('../SL_ID[1]', 'varchar(36)') AS [SL_ID] 
 ,T.c.value('.', 'varchar(10)') AS [DS]
 , cast(null as int) as [DS2_PR]
 ,T.c.value('./PR_DS2_N[1]', 'int') AS [PR_DS2_N]
 ,1 AS DS_TYPE
FROM   @xml.nodes('/ZL_LIST/ZAP/Z_SL/SL/DS3') T(c)
INSERT INTO [ISZL].[T_SCHET_SLUCH_DS]
([SchetSluchDsID],[MKB],[DS2_PR],[SchetSluch],[MKBType],[PR_DS2_N])
SELECT NEWID(),ds.[DS],ds.DS2_PR,ss.[SchetSluchID],ds.DS_TYPE,ds.PR_DS2_N
FROM #DS ds
JOIN #SS ss on ss.[SL_ID] = ds.[SL_ID] and ss.IDCASE = ds.IDCASE-- and ss.N_ZAP = sz.N_ZAP

--грузим SchetUslNaprs Направления по онкологии 
select  NEWID()                                                     as SchetUslNaprID
--,T.c.value('../../../N_ZAP[1]', 'int')								as N_ZAP
,T.c.value('../../IDCASE[1]', 'varchar(11)')                                as IDCASE
,T.c.value('../SL_ID[1]', 'varchar(36)')							as SL_ID

,CASE WHEN LEN(T.c.value('./NAPR_DATE[1]', 'varchar(20)'))>8 THEN T.c.value('./NAPR_DATE[1]', 'datetime') ELSE NULL END   AS NAPR_DATE
,T.c.value('./NAPR_MO[1]', 'varchar(6)')						as NAPR_MO
,T.c.value('./NAPR_V[1]', 'int')								as NAPR_V
,T.c.value('./MET_ISSL[1]', 'int')								as MET_ISSL
,T.c.value('./NAPR_USL[1]', 'varchar(16)')                      as NAPR_USL
--,T.c.value('../../../PACIENT[1]/ID_PAC[1]', 'varchar(36)')                as ID_PAC
--,T.c.value('../../../PACIENT[1]/NPOLIS[1]', 'varchar(20)')                as NPOLIS
INTO #NAPR
from @Xml.nodes('ZL_LIST/ZAP/Z_SL/SL/NAPR') T(c)

INSERT INTO [ISZL].[T_SCHET_USL_NAPR]
([SchetUslNaprID],[NAPR_DATE],[NAPR_MO],[NAPR_V],[MET_ISSL],[NAPR_USL],[SchetSluch])
SELECT napr.SchetUslNaprID,napr.[NAPR_DATE],napr.[NAPR_MO],napr.[NAPR_V],napr.[MET_ISSL],napr.[NAPR_USL],ss.SchetSluchID
from #NAPR napr
	join #SS ss on ss.SL_ID = napr.SL_ID and ss.IDCASE = napr.IDCASE and ss.SL_ID = napr.SL_ID --and ss.N_ZAP = napr.N_ZAP
--грузим SchetSluchConss Сведения о проведении консилиума 
select  NEWID()                                                     as SchetSluchConsID
--,T.c.value('../../../N_ZAP[1]', 'int')								as N_ZAP
,T.c.value('../../IDCASE[1]', 'varchar(11)')                                as IDCASE
,T.c.value('../SL_ID[1]', 'varchar(36)')							as SL_ID

,CASE WHEN LEN(T.c.value('./DT_CONS[1]', 'varchar(20)'))>8 THEN T.c.value('./DT_CONS[1]', 'datetime') ELSE NULL END   AS DT_CONS
,T.c.value('./PR_CONS[1]', 'int')							as PR_CONS

--,T.c.value('../../../PACIENT[1]/ID_PAC[1]', 'varchar(36)')                as ID_PAC
--,T.c.value('../../../PACIENT[1]/NPOLIS[1]', 'varchar(20)')                as NPOLIS
INTO #CONS
from @Xml.nodes('ZL_LIST/ZAP/Z_SL/SL/CONS') T(c)

INSERT INTO [ISZL].[T_SCHET_SLUCH_CONS]
([SchetSluchConsID],[SchetSluch],[DT_CONS],[PR_CONS])
SELECT cons.SchetSluchConsID,ss.SchetSluchID,cons.DT_CONS,cons.PR_CONS
from #CONS cons
	join #SS ss on ss.SL_ID = cons.SL_ID and ss.IDCASE = cons.IDCASE and ss.SL_ID = cons.SL_ID --and ss.N_ZAP = cons.N_ZAP


--грузим SCHET_USL
select NEWID()                                                      as SchetUslID
,T.c.value('../IDCASE[1]', 'varchar(11)')                                as IDCASE
,T.c.value('(./SL_ID/text())[1]', 'varchar(36)')							as SL_ID
,Usl.N.value('(./IDSERV/text())[1]', 'varchar(36)')							as IDSERV
,Usl.N.value('(./LPU/text())[1]', 'varchar(6)')								as LPU
,CASE WHEN LEN(Usl.N.value('(./DATE_IN/text())[1]', 'varchar(20)'))>8 THEN Usl.N.value('(./DATE_IN/text())[1]', 'datetime') ELSE NULL END   AS [DATE_IN]
,CASE WHEN LEN(Usl.N.value('(./DATE_OUT/text())[1]', 'varchar(20)'))>8 THEN Usl.N.value('(./DATE_OUT/text())[1]', 'datetime') ELSE NULL END   AS [DATE_OUT]
,Usl.N.value('(./DS/text())[1]', 'varchar(10)')								as DS
,Usl.N.value('(./KOL_USL/text())[1]', 'decimal(6,2)')							as KOL_USL
,Usl.N.value('(./USL/text())[1]', 'varchar(254)')								as USL
,Usl.N.value('(./VID_VME/text())[1]', 'varchar(254)')							as VID_VME
,Usl.N.value('(./CODE_USL/text())[1]', 'varchar(20)')							as CODE_USL
,Usl.N.value('(./P_OTK/text())[1]', 'int')										as P_OTK
,Usl.N.value('(./CODE_MD/text())[1]', 'varchar(25)')							as CODE_MD
INTO #USL
from @Xml.nodes('ZL_LIST/ZAP/Z_SL/SL') T(c)
cross apply T.c.nodes('USL') as Usl(N);

create index IX_#USL_IDCASE on #USL(IDCASE, SL_ID);

insert into [ISZL].[T_SCHET_USL]
([SchetUslID],[IDSERV],[LPU],[DATE_IN],[DATE_OUT],[DS],[KOL_USL],[USL],[VID_VME],[SchetSluch],[CODE_USL],[P_OTK],[CODE_MD])
select u.SchetUslID, u.IDSERV, u.LPU, u.DATE_IN, u.DATE_OUT, u.DS,u.KOL_USL,u.USL,u.VID_VME,ss.SchetSluchID,u.CODE_USL,u.P_OTK,u.[CODE_MD]
from #USL u
	join #SS ss on  ss.SL_ID = u.SL_ID and ss.IDCASE = u.IDCASE --ss.N_ZAP=u.N_ZAP and ss.ID_PAC = u.ID_PAC and ss.NPOLIS = u.NPOLIS and ss.NHISTORY = u.NHISTORY 

	
--грузим SCHET_SLUCH_NAZ
select NEWID()                                                      as SchetSluchNazID
--,T.c.value('../../../N_ZAP[1]', 'int')                              as N_ZAP
,T.c.value('../../IDCASE[1]', 'varchar(11)')                                as IDCASE
,T.c.value('../SL_ID[1]', 'varchar(36)')							as SL_ID
--,T.c.value('../NHISTORY[1]', 'varchar(50)')							as NHISTORY
,T.c.value('./NAZ_N[1]', 'int')											as NAZ_N
,T.c.value('./NAZR[1]', 'int')											as NAZR
,T.c.value('./NAZ_SP[1]', 'int')										as NAZ_SP
,T.c.value('./NAZ_V[1]', 'int')											as NAZ_V
,T.c.value('./NAZ_PK[1]', 'int')										as NAZ_PK
,T.c.value('./NAZ_USL[1]', 'varchar(15)')								as NAZ_USL
,CASE WHEN LEN(T.c.value('./NAPR_DATE[1]', 'varchar(20)'))>8 THEN T.c.value('./NAPR_DATE[1]', 'datetime') ELSE NULL END   AS NAPR_DATE
,T.c.value('./NAPR_MO[1]', 'varchar(6)')								as NAPR_MO

--,T.c.value('../../../PACIENT[1]/ID_PAC[1]', 'varchar(36)')                as ID_PAC
--,T.c.value('../../../PACIENT[1]/VPOLIS[1]', 'int')                        as VPOLIS
--,T.c.value('../../../PACIENT[1]/SPOLIS[1]', 'varchar(10)')                as SPOLIS
--,T.c.value('../../../PACIENT[1]/NPOLIS[1]', 'varchar(20)')                as NPOLIS
--,T.c.value('../../../PACIENT[1]/SMO[1]', 'varchar(5)')                    as SMO
INTO #NAZ
from @Xml.nodes('ZL_LIST/ZAP/Z_SL/SL/NAZ') T(c)

insert into [ISZL].[T_SCHET_SLUCH_NAZ]
([SchetSluchNazID],[NAZ_N],[NAZR],[NAZ_SP],[NAZ_V],[NAZ_PK],[SchetSluch],[NAZ_USL],[NAPR_DATE],[NAPR_MO])
select n.SchetSluchNazID,n.NAZ_N,n.NAZR,n.NAZ_SP,n.NAZ_V,n.NAZ_PK,ss.SchetSluchID,n.NAZ_USL,n.NAPR_DATE,n.NAPR_MO
from #NAZ n
	join #SS ss on  ss.SL_ID = n.SL_ID and ss.IDCASE = n.IDCASE--ss.N_ZAP=n.N_ZAP and ss.ID_PAC = n.ID_PAC and ss.NPOLIS = n.NPOLIS and ss.NHISTORY = n.NHISTORY


drop table #NAZ
drop table #USL
drop table #CONS
drop table #NAPR
drop table #DS
drop table #SANK_SL
drop table #SANK
drop table #LEK_PR
drop table #ONK_USL
drop table #B_PROT
drop table #B_DIAG
drop table #SSONK
drop table #SS
drop table #SSA
drop table #SCHET_ZAP
drop table #SCHET
END
