if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sendDrugsDictionary]') and xtype = N'P')
drop procedure [dbo].[sp_sendDrugsDictionary]
GO
-- =============================================
-- Author:		ЗАО Витакор
-- Create date: 2016.02.11
-- Description:	[dbo].[sp_sendDrugsDictionary] 
-- =============================================
--CREATE PROCEDURE [dbo].[sp_sendDrugsDictionary] 
--	@doc xml	-- xml
--As
--Begin
--	SET NOCOUNT ON;

--	If @doc Is NULL
--	begin
--		Select 1 ExitCode;
--		Select 'Ошибка' Err;
--		goto ENDING;
--	end

----///////////////////////////////////////////////////
----   
----   SPDOZA
----   
----///////////////////////////////////////////////////
--;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as xsi, DEFAULT 'http://esc.ru/NSIREESTRSPTOV')
--INSERT INTO [PHARM].[T_DOSE_VIEW]([Code],[Name])
--Select distinct
--    T.c.value('./C_DLS[1]', 'int') AS C_DLS
--    ,T.c.value('./NAME_DLS_L[1]', 'varchar(255)') AS NAME_DLS_L
--FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/SPDOZA_DOC/SPDOZA') T(c)
--WHERE not exists (select * from [PHARM].[T_DOSE_VIEW] dv where dv.Code = T.c.value('./C_DLS[1]', 'int'))
--and T.c.value('./C_FINL[1]', 'int') = 1 -- это условия оттого, что в этом справочнике колонка NAME_DLS_L разная для фед. и рег. финансирования [mz]

----///////////////////////////////////////////////////
----   
----   SPEDMLF
----   
----///////////////////////////////////////////////////
--;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as xsi, DEFAULT 'http://esc.ru/NSIREESTRSPTOV')
--INSERT INTO [PHARM].[T_DOSE_VIEW]([Code],[Name])
--Select distinct
--    T.c.value('./C_MLF[1]', 'int') AS C_MLF
--    ,T.c.value('./NAME_MLF[1]', 'varchar(255)') AS NAME_MLF
--FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/SPEDMLF_DOC/SPEDMLF') T(c)
--WHERE not exists (select * from [PHARM].[T_DOSE_VIEW] dv where dv.Code = T.c.value('./C_MLF[1]', 'int'))

----///////////////////////////////////////////////////
----   
----   SPEDVLF
----   
----///////////////////////////////////////////////////
--;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as xsi, DEFAULT 'http://esc.ru/NSIREESTRSPTOV')
--INSERT INTO [PHARM].[T_DOSE_VIEW]([Code],[Name])
--Select distinct
--    T.c.value('./C_VLF[1]', 'int') AS C_VLF
--    ,T.c.value('./NAME_VLF_L[1]', 'varchar(255)') AS NAME_VLF_L
--FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/SPEDVLF_DOC/SPEDVLF') T(c)
--WHERE not exists (select * from [PHARM].[T_DOSE_VIEW] dv where dv.Code = T.c.value('./C_VLF[1]', 'int'))

----///////////////////////////////////////////////////
----   
----   SPLF
----   
----///////////////////////////////////////////////////
--;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as xsi, DEFAULT 'http://esc.ru/NSIREESTRSPTOV')
--INSERT INTO [PHARM].[T_MEDICATION_FORMS]([Code],[Name],[Active])
--Select distinct
--    T.c.value('./C_LF[1]', 'int') AS C_LF
--    ,T.c.value('./NAME_LF_L[1]', 'varchar(255)') AS NAME_LF_L
--    ,1 [Active]
--FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/SPLF_DOC/SPLF') T(c)
--WHERE not exists (select * from [PHARM].T_MEDICATION_FORMS mf where mf.Code = T.c.value('./C_LF[1]', 'int'))
--and T.c.value('./PRIZ_DUBL[1]', 'int') = 0;

----///////////////////////////////////////////////////
----   
----   SPTRN
----   
----///////////////////////////////////////////////////
--;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as xsi, DEFAULT 'http://esc.ru/NSIREESTRSPTOV')
--INSERT INTO [AKUZ].[T_TRN]
--([Code],[Name],[NameLat],[BenefitCode],[DateAdd],[DateRemove],[DateAddSN],[DateRemoveSN])
--Select distinct
--    T.c.value('./C_TRN[1]', 'int') AS C_TRN
--    ,T.c.value('./NAME_TRN_R[1]', 'varchar(2048)') AS NAME_TRN_R
--    ,T.c.value('./NAME_TRN_L[1]', 'varchar(255)') AS NAME_TRN_L
--	,NULL [BenefitCode]
--	,NULL [DateAdd]
--	,NULL [DateRemove]
--	,NULL [DateAddSN]
--	,NULL [DateRemoveSN]
--FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/SPTRN_DOC/SPTRN') T(c)
--WHERE not exists (select * from [AKUZ].[T_TRN] trn where trn.Code = T.c.value('./C_TRN[1]', 'int'))
--and T.c.value('./PRIZ_DUBL[1]', 'int') = 0;

----///////////////////////////////////////////////////
----   
----   SPMNN
----   
----///////////////////////////////////////////////////
--;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as xsi, DEFAULT 'http://esc.ru/NSIREESTRSPTOV')
--INSERT INTO [AKUZ].[T_MNN]
--([Code],[Name],[NameLat],[BenefitCode],[DateAdd],[DateRemove],[DateAddSN],[DateRemoveSN])
--Select distinct
--    T.c.value('./C_MNN[1]', 'int') AS C_MNN
--    ,T.c.value('./NAME_MNN_R[1]', 'varchar(2048)') AS NAME_MNN_R
--    ,T.c.value('./NAME_MNN_L[1]', 'varchar(255)') AS NAME_MNN_L
--	,NULL [BenefitCode]
--	,NULL [DateAdd]
--	,NULL [DateRemove]
--	,NULL [DateAddSN]
--	,NULL [DateRemoveSN]
--FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/SPMNN_DOC/SPMNN') T(c)
--WHERE not exists (select * from [AKUZ].[T_MNN] trn where trn.Code = T.c.value('./C_MNN[1]', 'int'))
--and T.c.value('./PRIZ_DUBL[1]', 'int') = 0;

----///////////////////////////////////////////////////
----   
----   SPPOST
----   
----///////////////////////////////////////////////////
--;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as xsi, DEFAULT 'http://esc.ru/NSIREESTRSPTOV')
--INSERT INTO [AKUZ].[T_DRUG_FORM_SPPOST]
--([DrugFormSppostID],[C_LSFO],[NOMK_LS],[DATE_B],[DATE_E],[NAME],[PRODUCER],[COUNTRY],[CODEPOST])
--Select distinct --top 10
--	NEWID() AS DrugFormSppostID
--    ,T.c.value('./C_LSFO[1]', 'int') AS C_LSFO
--    ,T.c.value('./NOMK_LS[1]', 'int') AS NOMK_LS
--    ,cast(replace(T.c.value('./DATE_B[1]', 'varchar(255)'), '-', '') as datetime) AS DATE_B
--    ,cast(replace(T.c.value('./DATE_E[1]', 'varchar(255)'), '-', '') as datetime) AS DATE_E
--    ,T.c.value('./NAME[1]', 'varchar(255)') AS NAME
--    ,T.c.value('./PRODUCER[1]', 'varchar(255)') AS PRODUCER
--    ,T.c.value('./COUNTRY[1]', 'varchar(64)') AS COUNTRY
--    ,T.c.value('./CODEPOST[1]', 'int') AS CODEPOST

--FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/SPPOST_DOC/SPPOST') T(c)
--where not exists 
--	(
--		select * from 
--		[AKUZ].[T_DRUG_FORM_SPPOST] dfp 
--		where dfp.C_LSFO = T.c.value('./C_LSFO[1]', 'int') 
--			AND dfp.NOMK_LS = T.c.value('./NOMK_LS[1]', 'int')
--	)

----///////////////////////////////////////////////////
----   
----   SPTOV
----   
----///////////////////////////////////////////////////
--;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as xsi, DEFAULT 'http://esc.ru/NSIREESTRSPTOV')
--INSERT INTO [dbo].[T_DRUG_FORM]
--       ([Code], [MedicationForms] ,[DoseView] ,[Dose] ,[DoseCount] ,[Doses] ,[Mnn] ,[VolumeView]
--       ,[Weigth] ,[WeightView] ,[Volume] ,Trn, [DateAddFederal] ,[DateRemoveFederal] ,[DateAddSN] ,[DateRemoveSN], [BenefitCode],[IsMI],[RRL])
--SELECT distinct --top 10 
--      T.c.value('./NOMK_LS[1]', 'int') [Code]
--      ,T.c.value('./C_LF[1]', 'int') [MedicationForms]
--      ,T.c.value('./C_DLS[1]', 'int') [DoseView]
--      ,(case when T.c.value('./D_LS[1]', 'varchar(255)') = '' then '0' else T.c.value('./D_LS[1]', 'varchar(255)') end) [Dose]
--      ,T.c.value('./N_FV[1]', 'int') [DoseCount]
--      ,cast(replace(T.c.value('./N_DOZA[1]', 'varchar(255)'),',','.') as decimal(31,10)) [Doses]
--      ,T.c.value('./C_MNN[1]', 'int') [Mnn]
--      ,T.c.value('./C_VLF[1]', 'int') [VolumeView]
--      ,case when T.c.value('./M_LF[1]', 'varchar(255)') = '0' then NULL else cast(replace(T.c.value('./M_LF[1]', 'varchar(255)'),',','.') as decimal(31,10)) end [Weigth]
--      ,T.c.value('./C_MLF[1]', 'int') [WeightView]
--      ,case when T.c.value('./V_LF[1]', 'varchar(255)') = '0' then NULL else cast(replace(T.c.value('./V_LF[1]', 'varchar(255)'),',','.') as decimal(31,10)) end [Volume]
--      ,T.c.value('./C_TRN[1]', 'int') Trn
--      ,cast(replace(T.c.value('./DATE_B[1]', 'varchar(255)'), '-', '') as datetime) [DateAddFederal] 
--      ,cast(replace(T.c.value('./DATE_E[1]', 'varchar(255)'), '-', '') as datetime) [DateRemoveFederal]
--      ,NULL [DateAddSN] 
--      ,NULL [DateRemoveSN]
--      ,NULL [BenefitCode]
--	  ,0 [IsMI]
--	  ,NULL RRL
--FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/SPTOV_DOC/SPTOV') T(c)
--Where cast(replace(T.c.value('./DATE_E[1]', 'varchar(255)'), '-', '') as datetime) > getdate()
--	and T.c.value('./PRIZ_DUBL[1]', 'int') = 0
--	and not exists (select * from [dbo].[T_DRUG_FORM] df where df.Code = T.c.value('./NOMK_LS[1]', 'int'))


--	-- результат
--	Select 0 ExitCode;
--	Select 'Ok' Msg

--ENDING:
--	return
--End
--GO
