SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

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

--	declare @DateBegin datetime, @DateEnd datetime
--	select @DateBegin = '20160101', @DateEnd = '20991231'

--	-----------------------------------
--	--
--	--         SUBDIV
--	--
--	-----------------------------------

--	-- добавляем недостающие аптеки
--	;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as ns1, DEFAULT 'http://esc.ru/NSIREESTR')
--	INSERT INTO [AKUZ].[T_PHARMACY]([Code],[Name],[NameFull],[OGRN],[Terr],[OKATO],[DateBegin],[DateEnd],[RegisterDate],[RecipeServedTypes],[Address],[Rajon],[Pharmacy])
--	Select 
--		T.c.value('./EXTCODE[1]', 'int') AS Code
--		,T.c.value('./SHORTNAME[1]', 'varchar(255)') AS Name -- 
--		,T.c.value('./FULLNAME[1]', 'varchar(255)') AS NameFull -- 
--		,T.c.value('./OGRN[1]', 'varchar(max)') AS OGRN -- 
--		,cast('' as varchar(255)) AS Terr
--		,cast('' as varchar(255)) AS OKATO
--		,@DateBegin AS DateBegin
--		,@DateEnd AS DateEnd
--		,cast(replace(T.c.value('./DATEEDIT[1]', 'varchar(10)'), '-', '') as datetime) AS RegisterDate -- 
--		,cast(0 as int) AS RecipeServedTypes -- 
--		,T.c.value('./ADDRESS[1]', 'varchar(255)') AS [Address] -- 
--		,cast(NULL as uniqueidentifier) as Rajon
--		,cast(NULL as int) AS Pharmacy
--	FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/SUBDIV_DOC/SUBDIV') T(c)
--	where T.c.value('./DESTROYED[1]', 'int') = 0
--	and not exists (select * from [AKUZ].T_PHARMACY p where p.Code = T.c.value('./EXTCODE[1]', 'int'))

--	-- выставляем Головную аптеку в NULL, у удаленных аптек
--	;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as ns1, DEFAULT 'http://esc.ru/NSIREESTR')
--	update p
--	set p.Pharmacy = null
--	From [AKUZ].T_PHARMACY p
--	where p.Pharmacy is not null
--	and exists 
--	(
--		Select T.c.value('./EXTCODE[1]', 'int')
--		FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/SUBDIV_DOC/SUBDIV') T(c)
--		where p.Code = T.c.value('./EXTCODE[1]', 'int') and T.c.value('./DESTROYED[1]', 'int') = 1
--	)



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

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