-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2022-04-18
-- Description: Отправка информации по молочным рецептам в RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_Milk_Recipe;

CREATE PROCEDURE sp_Milk_Recipe
(
    "Xml" xml = null,    
,	"ID" uuid DEFAULT NULL::uuid
    "QueueName" varchar(100) = 'RDSSync',
    "DateTime" TimeStamp = null,
,	"User" uuid DEFAULT NULL::uuid
)
    language plpgsql
as
$$
Begin
    Begin
        declare 
            _Result text;
            ,_ContentEncoding varchar(255) := 'utf-8';
            ,_Type varchar(255) := 'MILK_RECIPE';
            ,_OwnerID UUID := "ID";

        select "DateTime" = ISNULL("DateTime", GETDATE())

        IF ("Xml" is null)
        then
            if(not exists(select * from akuz.T_MILK_RECIPE where MedicalRecordID = "ID"))
            then
			    RAISE EXCEPTION 'Рецепт не найден!';
            end if;
			
			select
            	XMLELEMENT 
				(
					NAME MILK_RECIPE,
					xmlforest
					(
					   milkrecipe.MedicalRecordID								    as "MilkRecipeID"	
	    			  ,dairycenter.Number											as "DairyCenter"
	    			  ,lm.LpuCode													as "LPU"
                      ,patient.LastName												as "LastName"
                      ,patient.FirstName											as "FirstName"
                      ,patient.FatherName											as "FatherName"   
	    			  ,to_char(patient.Birthday,'DD.MM.YYYY')						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"
	    			  ,to_char(milkrecipe.BeginDate,'DD.MM.YYYY')					as "BeginDate"
	    			  ,to_char(milkrecipe.EndDate,'DD.MM.YYYY')						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"
	    			  ,to_char(ambcardrecord.PostingDate,'DD.MM.YYYY')				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
						xmlagg
						(
							xmlelement
							(
								name "MILK_PRODUCT"
								,xmlforest
								(
									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
						)
				)
			into _Result
			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";

		    -- Отправка
		    INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
		    select uuid_generate_v4(), "QueueName", dbo.ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, "User"::text, 0, "dateTime", _OwnerID;

        END if;
    End;
End;
$$;