IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_BurRecipe]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_BurRecipe]
GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:      Vitacore (с)
---- Create date: 2016-12-28
---- Description: Загрузка/выгрузка  рецепта в буряцкий РИАЦ
---- =============================================
--CREATE PROCEDURE [dbo].[sp_BurRecipe]
--	@ID uniqueidentifier = null
--	,@IsNew bit = 1
--    ,@Deleted bit = 0
--    ,@CancelDeleted bit = 0
--    ,@QueueName varchar(100) = 'Bur.RIACSync'
--    ,@User uniqueidentifier = null
--	,@OperationType  varchar(255) = ''

--AS
--BEGIN
--	SET NOCOUNT ON;
--declare 
--		@Result varchar(max)
--		,@dateTime datetime = null
		
--		,@ContentEncoding varchar(255)
--		,@Type varchar(255)
--		,@OwnerID uniqueidentifier
--		,@Serial char(4)
--		,@Number bigint
--		,@sNumber char(16)
--		,@tmp varchar(32)
--		,@tmp2 varchar(32)
--		,@tmp3 varchar(32)
--		,@SnilsPat varchar(32)
--		,@RecipeDate datetime 
--		,@OGRNCode varchar(32)
--		,@BenefitCode  varchar(32)
--		,@UNQ  varchar(32)
--		,@sUNQ varchar(32)
--		,@SnilsDoctor varchar(32)
--		,@MKBCode  varchar(32)
--		,@TYPE_FIN varchar(32)
--		,@PaymentPercent  varchar(32)
--		,@Period varchar(32)
--		,@MNN varchar(255)
--		,@DrugCode int
--		,@Brandname varchar(255)
--		,@Dosage varchar(32)
--		,@Quantity  varchar(32)
--		,@fQuantity  float
--		,@Signa varchar(32)

--		-- Operation types
--		,@RELEASE varchar(7)
--		,@TRASH varchar(7)
--		,@DETRASH varchar(7)
--		,@CONSUME varchar(7)
--		,@RETURN varchar(7)
--	    ,@TYPE_WEBSER varchar(16) 
	   
	
		

--    select @dateTime = ISNULL(@dateTime, GETDATE())
--        ,@Type = 'RECIPE'
--		,@TYPE_WEBSER = 'diabetws'

--        -- Operation types
--        ,@RELEASE = 'Release'
--        ,@TRASH = 'Trash'
--        ,@DETRASH = 'Detrash'
--        ,@CONSUME = 'Consume'
--        ,@RETURN = 'Return'
   
--        -- Инициализация
--        select @ContentEncoding = 'utf-8'
--              ,@OwnerID = @ID
			 

--        --выясняем тип операции

--		IF (@OperationType = '')
--		BEGIN
--			IF(@IsNew = 1 and @Deleted = 0)
--			BEGIN
--				select @OperationType = @RELEASE
--			END
--			ELSE IF (@IsNew = 0 and @Deleted = 1)
--			BEGIN
--				select @OperationType = @TRASH
--			END
--			ELSE IF (@IsNew = 0 and @CancelDeleted = 1)
--			BEGIN
--				select @OperationType = @DETRASH
--			END
--			ELSE raiserror('Не поддерживаемая операция', 16, 10)
--		END

--        -- собираем поля RECIPE
--		select     					
--			@RecipeDate =  CONVERT (date, r.RecipeDate)
--			,@OGRNCode = lm.OGRNCode	
--			,@BenefitCode = 	isnull(b.BenefitCode,'000')	      -- Код льготы
--			,@SnilsPat = isnull(pat.SNILS,'')		
--			,@UNQ = isnull(mdr.oid,'')	
--			,@Serial =  isnull(r.Serial ,'')	
--			,@sNumber = isnull(r.Number, '')
--			,@SnilsDoctor = isnull(w.Snils, '')		
--			,@MKBCode = isnull(r.MKBCode, '')		
--			,@TYPE_FIN = case
--					when  mp.TYPE_FIN = 1 then 1  
--					when  mp.TYPE_FIN = 2 then 2
--					else 1
--					end     
		
--			,@PaymentPercent = case 
--				when r.Discount = 1 then '50'							
--				when r.Discount = 2 then '100'	
--				end						

--			,@Period = case									
--				when r.Period = 0 then 30
--				when r.Period = 1 then 14
--				when r.Period = 2 then 90
--				when r.Period = 3 then 5
--				when r.Period = 4 then 10
--				when r.Period = 5 then 15
--			end		
--			,@Dosage = isnull(r.Dose,'') +  isnull(dv1.Name,isnull( dv2.Name,''))  -- доза с ед. изм. дозировки
--			,@fQuantity = r.UnitsCount   
--			,@Number = r.Number	
--			,@Signa = isnull(r.Signa,'') 

--			,@MNN = isnull(mnn.NameRus,'')
--  			,@DrugCode = isnull(trn.OtherID,0)
--			,@Brandname = isnull(trn.NameRus,'')
		

--		from [AKUZ].T_RECIPE r
--		join AKUZ.T_AMBULANCE_CARD_RECORD acr on acr.MedicalRecordID = r.MedicalRecordID
--		join AKUZ.T_MEDICAL_RECORD mr on mr.MedicalRecordID = r.MedicalRecordID
--		left join [AKUZ].T_EVENT_BASE ae on ae.EventID = acr.AmbulanceEvent
--		left join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = ae.AmbulanceCard
--		left join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
--		join [AKUZ].T_POST p on p.ResourceID = mr.Post
--		JOIN [AKUZ].T_WORKER w on w.WorkerID = p.Worker  -- сотрудник (получаем Идентификатор врача) 
--		join [AKUZ].T_RESOURCE rs on rs.ResourceID = p.ResourceID
--		join [AKUZ].T_DIVISION d on d.DivisionID = rs.Division
--		join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
--		join [AKUZ].T_LPU_MAIN lm on lm.LpuID = br.LpuMain
--		left join [AKUZ].T_MEDICINE_PRESENCE mp on mp.LpuMain = lm.LpuID 
					
--		LEFT JOIN -- информация о прикреплении  Текущее прикрепление
--						(Select top 1 rg.Patient, rg.[Status], rg.RegistrationDate, rg.LpuMain --  Последнее прикрепление к ЛПУ
--							From [AKUZ].T_REGISTRATION rg
--							Where rg.RegistrationDate = (Select Top 1 rr.RegistrationDate  From [AKUZ].T_REGISTRATION rr  Where rg.Patient = rr.Patient Order by rr.RegistrationDate Desc, rr.[Status])
--						) tp On pat.PatientID = tp.Patient 
--		JOIN [EHR].T_EHR_RUS_MDR308 mdr on mdr.EhrRusID = lm.EhrRusMdr308  -- Регистр медицинских организаций Российской Федерации Версия 2 
					
--		join [AKUZ].T_BENEFITS b on b.BenefitsID = r.Benefits
--		join [VCLib].T_ENUMS eBv on eBv.EnumID = b.BenefitViews
--		--join [AKUZ].T_LPU_SETTINGS lpu on lpu.LpuSettingsID = r.LpuSettings

--		LEFT JOIN [AKUZ].T_RECIPE_FEDERAL rf ON rf.MedicalRecordID = r.MedicalRecordID
--		LEFT JOIN [AKUZ].T_RECIPE_REGIONAL rr ON rr.MedicalRecordID = r.MedicalRecordID
--		LEFT JOIN [AKUZ].T_RECIPE_GENERAL rg ON rg.MedicalRecordID = r.MedicalRecordID
--		LEFT JOIN [PHARM].T_C_MNN mnn ON mnn.CMnnID = rg.CMnn 
--		LEFT JOIN [PHARM].T_C_TRN trn ON trn.CTrnID = rg.CTrn
--				--LEFT JOIN [AKUZ].S_DLO_DoseView dd ON dd.Id = rg.RGDoseView
--		LEFT JOIN [PHARM].T_DOSE_VIEW dv1 ON dv1.Code = rf.RFDoseView
--		LEFT JOIN [PHARM].T_DOSE_VIEW dv2 ON dv2.Code = rr.RRDoseView
--		where r.MedicalRecordID = @ID

--		Set @Quantity =  @fQuantity

--		-- обработка операции рецепта 
--        IF(@OperationType = @RELEASE   or @OperationType = @DETRASH )  --  Создание нового рецепта или снятие аннулирования (фактически - пересоздание рецепта)
--        BEGIN
--			Select  @Result =  		 
--		        ( 
--		        select   
--				@OperationType				as 'TYPE_OPER'
--				,@TYPE_WEBSER				as 'TYPE_WEBSER'		    -- тип веб-сервиса (для шлюза)  (diabetws или  tuberws)
--				,@ID				        as 'IDOwner'
--				,@ID				        as 'IDOwnerForm'
--				,@RecipeDate 				as 'Recipe/RecipeDate'		-- Дата выписки рецепта
--				,@OGRNCode					as 'Recipe/OGRNCode'		-- Код ОГРН
--				,@BenefitCode				as 'Recipe/BenefitCode'		-- Код льготы
--				,@SnilsPat					as 'Recipe/SNILS'			-- ID пациента (СНИЛС)
--				,@UNQ						as 'Recipe/UNQ'				-- Код МУ
--				,@SnilsDoctor				as 'Recipe/SnilsDoctor'		-- ID доктора (СНИЛС)
--				,@MKBCode					as 'Recipe/MKBCode'			-- Код МКБ
--				,@TYPE_FIN 					as 'Recipe/TYPE_FIN'		-- Код источника финансирования
--				,@PaymentPercent			as 'Recipe/PaymentPercent'  -- % оплаты из источника  (decimal)

--				,@Period					as 'Recipe/Period'			-- Срок действия рецепта
--				,@MNN						as 'Recipe/MNN'				-- МНН ЛП
--				,@DrugCode					as 'Recipe/DrugCode'		-- Код ЛП 
--				,@Brandname					as 'Recipe/Brandname'		-- Торговое наименование ЛП (0..1)
--				,@Dosage					as 'Recipe/Dosage'			-- Дозировка 
--				,@Quantity 					as 'Recipe/Quantity'		-- Количество единиц
--				,@Signa 					as 'Recipe/Signa'			-- Кратность приёма (0..1)
--				,@Serial					as 'Recipe/Serial'			-- Серия рецепта
--				,@sNumber					as 'Recipe/Number'			-- Номер рецепта
--				for xml path(''),
--				root('RIAC')
--            ) 
--        END
--        ELSE IF (@OperationType = @TRASH)
--        BEGIN

--            --  собираем данные для аннулирования рецепта
--            SELECT @Result = 
--            ( 
--                select
--					@OperationType				as 'TYPE_OPER'
--					,@TYPE_WEBSER				as 'TYPE_WEBSER'		-- тип веб-сервиса (для шлюза)  (diabetws или  tuberws)
--					,@ID				        as 'IDOwner'
--					,@ID				        as 'IDOwnerForm'
--					,@UNQ						as 'Recipe/UNQ'         -- Код МУ
--					,@RecipeDate 				as 'Recipe/RecipeDate'  -- Дата выписки рецепта
--					,@Serial					as 'Recipe/Serial'		-- Серия рецепта
--					,@sNumber					as 'Recipe/Number'		-- Номер рецепта
--				for xml path(''),
--				root('RIAC')
--            ) 
--       END
	   
--        -- Отправка
--        INSERT INTO [AKUZ].[T_RABBIT_SEND_OBJECT]
--            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
--        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
--              ,@QueueName--,<QueueName, varchar(255),>
--              ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
--              ,null--,<MessageID, varchar(255),>
--              ,null--,<Headers, varchar(255),>
--              ,@ContentEncoding--,<ContentEncoding, varchar(255),>
--              ,@Type--,<Type, varchar(255),>
--              ,@User--,<UserID, varchar(255),>
--              ,0--,<State, int,>)
--              ,@dateTime--,<CreateDateTime, datetime,>)
--              ,@OwnerID--,<OwnerID, varchar(255),>
--END
