IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Milk_Recipe]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Milk_Recipe]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2022-04-18
-- Description: Отправка информации по молочным рецептам в RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_Milk_Recipe]
    @Xml xml = null,
    @ID uniqueidentifier = null,
    @QueueName varchar(100) = 'rds.registry',
    @dateTime datetime = null,
    @User uniqueidentifier = null
AS
BEGIN

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OwnerID uniqueidentifier

    select @dateTime = ISNULL(@dateTime, GETDATE())

    IF (@Xml is null)
    BEGIN
        if(not exists(select * from akuz.T_MILK_RECIPE where MedicalRecordID = @ID))
        begin
         return
        end
        -- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'MILK_RECIPE'
              ,@OwnerID = @ID

        -- Обработка операции
        SELECT @Result = 
        ( 
            select @ID															as 'MilkRecipeID'	
				  ,dairycenter.Number											as 'DairyCenter'
				  ,lm.LpuCode													as 'LPU'
                  ,patient.LastName												as 'LastName'
                  ,patient.FirstName											as 'FirstName'
                  ,patient.FatherName											as 'FatherName'   
				  ,convert(varchar(10), patient.Birthday, 104)					as 'Birthday'
				  ,e.Code														as 'Sex'
				  ,patient.DocSeries											as 'DocSeries'
                  ,patient.DocNumber											as 'DocNumber'	
				  ,eDt.Code														as 'DocType'
				  ,pl.Number													as 'Polis'
				  ,patient.SNILS												as 'Snils'
				  ,convert(varchar(10), milkrecipe.BeginDate, 104)				as 'BeginDate'
				  ,convert(varchar(10), milkrecipe.EndDate, 104)				as 'EndDate'
                  ,case when milkrecipe.Category = 3 then 1
						when milkrecipe.Category = 5 then 2
						else 0 end												as 'Category'
				  ,milkrecipe.Number											as 'Number'
                  ,milkrecipe.Seria												as 'Seria' 
				  ,milkrecipe.MKBCode											as 'MKBCode'
				  ,milkrecipe.Additional										as 'Additional'
				  ,milkrecipe.LowIncomeRegister								    as 'LowIncomeRegister'
				  ,convert(varchar(10), ambcardrecord.PostingDate, 104)			as 'PostingDate'
				  ,case when DATEDIFF(MONTH, patient.Birthday,milkrecipe.EndDate) < 6 then 0
					   when DATEDIFF(MONTH,patient.Birthday,milkrecipe.EndDate) < 12 and DATEDIFF(MONTH, patient.Birthday, milkrecipe.EndDate) >= 6 then 1
				   else 2 end as 'Age'
                   ,case when rd.MilkRecipe is not null then 1 else 0 end       as 'RecipeDiscard'
				  ,( 
					     select 
								milkunit.MilkRecipe								as 'RecipeId'
                               ,milkunit.Amount								    as 'Amount'
					           ,milkproducts.Dose                               as 'Dose'
					           ,milkproducts.[Name]                             as 'Name'
					     from AKUZ.T_MILK_UNIT milkunit 
							 join [AKUZ].T_MILK_PRODUCTS milkproducts on milkunit.Milk = milkproducts.MilkProductsID
					     where milkunit.MilkRecipe = milkrecipe.MedicalRecordID
					     for xml path('MILK_PRODUCT'),
					     TYPE
					 ) 
            from akuz.T_MILK_RECIPE milkrecipe
                join [AKUZ].T_AMBULANCE_CARD_RECORD ambcardrecord on milkrecipe.MedicalRecordID = ambcardrecord.MedicalRecordID
				join [AKUZ].T_AMBULANCE_CARD ambcard on ambcardrecord.AmbulanceCard = ambcard.AmbulanceCardID
				join [AKUZ].T_PATIENT patient on ambcard.Patient = patient.PatientID
                join [VCLib].T_ENUMS e on e.EnumID = patient.Sex
                join [AKUZ].T_LPU_MAIN lm on lm.LpuID = ambcard.CardLpu
				join AKUZ.T_LPU_SETTINGS ls on ls.LpuMain = lm.LpuID
                left join [AKUZ].T_POLICY pl on pl.PolicyID = [dbo].fn_GetOmsPolicy(ambcard.Patient, @dateTime)
                join [VCLib].T_ENUMS eDt on eDt.EnumID = patient.DocType
				left join [AKUZ].[T_DAIRY_CENTER] dairycenter on milkrecipe.DairyCenter = dairycenter.DairyCenterID
                left join AKUZ.T_RECIPE_DISCARD rd on rd.MilkRecipe = milkrecipe.MedicalRecordID
            where milkrecipe.MedicalRecordID = @ID
            for xml path(''),
            root('MILK_RECIPE')
        ) 

        -- Отправка
        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
END
GO