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

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

--declare  @today datetime
--set @today = cast(cast(cast(getdate() as float) as int) as datetime)

---- 
--;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as xsi, DEFAULT 'http://esc.ru/PHARMACYREESTR')
--INSERT INTO [AKUZ].T_RELEASED_RECIPE(
--	rr.ReleasedRecipeID,rr.[Pharmacy],rr.[VID] ,rr.[GR],rr.[V_SERIA] ,rr.[NOMER],rr.[DAT_VI]
--	,rr.[DAT_OB],rr.[VID_OBSL],rr.[DAT_GAR] ,rr.[DAT_REZ] ,rr.[DAT_OTM] 
--	,rr.[DAT_OTP] ,rr.[C_PFS],rr.[VOSM],rr.[P_KEK],rr.[D_TYPE],rr.[UNIQECOD]
--	,rr.[DAT_INF] ,rr.[LpuMain] ,rr.[KOL_OT])
--Select --top 100
--	newid() as ReleasedRecipeID
--    ,T.c.value('./PHARMACYRECIPE_TITLE[1]/A_CODE[1]', 'int') AS [Pharmacy]
--    ,T.c.value('./PHARMACYRECIPE_TITLE[1]/C_KAT[1]', 'varchar(4)') AS [VID]
--    , case when T.c.value('./PHARMACYRECIPE_TITLE[1]/PROG_ONLS[1]', 'int') = 1 then 6  -- -- Программа ЛЛО 0 -“ДЛО+Субъект”, 1 - Нозологии, 2- Трансферт 
--		else T.c.value('./PHARMACYRECIPE_TITLE[1]/C_FINL[1]', 'int') end AS [GR] -- BenefitViews.Code
--    ,T.c.value('./PHARMACYRECIPE_TITLE[1]/S_LR[1]', 'varchar(4)') AS [V_SERIA]
--    ,T.c.value('./PHARMACYRECIPE_TITLE[1]/N_LR[1]', 'varchar(32)') AS [NOMER]
--    ,cast(replace(T.c.value('./PHARMACYRECIPE_TITLE[1]/DATE_VR[1]', 'varchar(255)'), '-', '') as datetime) AS [DAT_VI]
--    ,cast(replace(T.c.value('./PHARMACYRECIPE_TITLE[1]/DATE_OBR[1]', 'varchar(255)'), '-', '') as datetime) AS [DAT_OB]
--	, T.c.value('./PHARMACYRECIPE_TITLE[1]/Delayed_Service[1]', 'varchar(4)') [VID_OBSL]
--	, cast(replace(T.c.value('./PHARMACYRECIPE_TITLE[1]/DATE_OTP[1]', 'varchar(255)'), '-', '') as datetime) [DAT_GAR]
--	, cast(replace(T.c.value('./PHARMACYRECIPE_TITLE[1]/DATE_OTP[1]', 'varchar(255)'), '-', '') as datetime) [DAT_REZ]
--	, cast(replace(T.c.value('./PHARMACYRECIPE_TITLE[1]/DATE_OTP[1]', 'varchar(255)'), '-', '') as datetime) [DAT_OTM]
--    ,cast(replace(T.c.value('./PHARMACYRECIPE_TITLE[1]/DATE_OTP[1]', 'varchar(255)'), '-', '') as datetime) AS [DAT_OTP]
--    ,T.c.value('./PHARMACYRECIPE_DATA[1]/C_PFS[1]', 'varchar(10)') AS [C_PFS]
--    ,isnull(T.c.value('./PHARMACYRECIPE_DATA[1]/SL_ALL[1]', 'varchar(255)'),'0') AS [VOSM]
--    ,T.c.value('./PHARMACYRECIPE_TITLE[1]/P_KEK[1]', 'int') AS [P_KEK]
--	,'0' [D_TYPE]
--    ,T.c.value('./PHARMACYRECIPE_TITLE[1]/RecipeGUID[1]', 'varchar(255)') AS [UNIQECOD]
--	,@today [DAT_INF]
--	,(select top 1 LpuId From [AKUZ].t_lpu_main lm where lm.Code =  T.c.value('./PHARMACYRECIPE_TITLE[1]/MCOD[1]', 'varchar(255)')) [LpuMain]
--    ,cast(replace(T.c.value('./PHARMACYRECIPE_TITLE[1]/KV_ALL[1]', 'varchar(255)'),',','.') as decimal(31,10)) AS [KOL_OT]
--FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/PHARMACYRECIPE_DOC/PHARMACYRECIPE') T(c)
--WHERE exists (select * from [AKUZ].T_PHARMACY p where p.Code = T.c.value('./PHARMACYRECIPE_TITLE[1]/A_CODE[1]', 'int'))
--and not exists (select * from [AKUZ].T_RELEASED_RECIPE rr where rr.[UNIQECOD] = T.c.value('./PHARMACYRECIPE_TITLE[1]/RecipeGUID[1]', 'varchar(255)'))

---- обновление удаленных
--;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as xsi, DEFAULT 'http://esc.ru/PHARMACYREESTR')
--update rr
--set rr.Deleted = 1
--from [AKUZ].t_released_recipe rr
--where rr.[UNIQECOD] in 
--(
--Select
--    T.c.value('./RecipeGUID[1]', 'varchar(255)') AS [UNIQECOD]
--FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/PHARMACYRECIPE_DOC/PHARMACYRECIPE_DELETE/PHARMACYRECIPE_DELETE_TITLE') T(c)
--);


---- обновление ссылки на рецепт
--update rr
--set rr.Recipe = 
--(
--	select top 1 RecipeID 
--	from [AKUZ].t_recipe r
--		join [AKUZ].t_event_base e on r.ambulanceevent = e.eventid
--		join [AKUZ].t_ambulance_card c on e.ambulancecard = c.ambulancecardid
--	where c.CardLpu = rr.LpuMain
--	and rr.Nomer = cast(r.Number as varchar(max))
--	and rr.[DAT_VI] = r.RecipeDate
--	order by r.Deleted
--)
--from [AKUZ].t_released_recipe rr
--where rr.Recipe is null


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

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


