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

    SET NOCOUNT ON;

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

    -- Инициализация
    select @dateTime = ISNULL(@dateTime, GETDATE())
    -- Получаем тип операции
    select @ContentEncoding = 'utf-8'
          ,@Type = 'MILK_RECIPE'
    from @Xml.nodes('MILK_RECIPE') T(c)

    -- Generate table #MILK_RECIPE
        select t.c.value('./MilkRecipeID[1]', 'uniqueidentifier')                    as [MilkRecipeID]
			  ,t.c.value('./DairyCenter[1]', 'int')                                  as [DairyCenter]
			  ,t.c.value('./LPU[1]', 'varchar(6)')                                   as [LPU]
              ,t.c.value('./LastName[1]', 'varchar(40)')                             as [LastName]
              ,t.c.value('./FirstName[1]', 'varchar(40)')                            as [FirstName]
              ,t.c.value('./FatherName[1]', 'varchar(40)')                           as [FatherName]
              ,convert(datetime, t.c.value('./Birthday[1]', 'varchar(10)'), 104)     as [Birthday]
			  ,t.c.value('./Sex[1]', 'int')                                          as [Sex]
              ,t.c.value('./DocSeries[1]', 'varchar(16)')                            as [DocSeries]
              ,t.c.value('./DocNumber[1]', 'varchar(16)')                            as [DocNumber]
			  ,t.c.value('./DocType[1]', 'int')                                      as [DocType]
			  ,t.c.value('./Polis[1]', 'varchar(16)')								 as [Polis] 
              ,t.c.value('./Snils[1]', 'varchar(14)')                                as [SNILS]
              ,convert(datetime, t.c.value('./BeginDate[1]', 'varchar(10)'), 104)	 as [BeginDate]
              ,convert(datetime, t.c.value('./EndDate[1]', 'varchar(10)'), 104)		 as [EndDate]
              ,convert(datetime, t.c.value('./PostingDate[1]', 'varchar(10)'), 104)	 as [PostingDate]
			  ,t.c.value('./Category[1]', 'int')  									 as [Category]
			  ,t.c.value('./Number[1]', 'int')  									 as [Number]
			  ,t.c.value('./Seria[1]', 'varchar(255)')								 as [Seria] 
			  ,t.c.value('./MKBCode[1]', 'varchar(255)')          					 as [MKBCode]
			  ,t.c.value('./Additional[1]', 'bit')									 as [Additional]
			  ,t.c.value('./LowIncomeRegister[1]', 'int')							 as [LowIncomeRegister]
			  ,t.c.value('./RecipeDiscard[1]', 'bit')								 as [RecipeDiscard] 
			  ,t.c.value('./Age[1]', 'int')											 as [Age]
            INTO #MILK_RECIPE
        from @Xml.nodes('MILK_RECIPE') T(c)

		-- Generate table #MILK_PRODUCT
		select t.c.value('./RecipeId[1]', 'uniqueidentifier')						as [RecipeId]
              ,t.c.value('./Amount[1]', 'int')										as [Amount]
              ,t.c.value('./Dose[1]', 'int')										as [Dose]
              ,t.c.value('./Name[1]', 'varchar(255)')                               as [Name]
            INTO #MILK_PRODUCT
        from @Xml.nodes('MILK_RECIPE/MILK_PRODUCT') T(c)

		
INSERT INTO [RDS].[T_MILK_RECIPE]
           (
			MilkRecipeID
		   ,[DairyCenter]
           ,[LpuMain]
           ,[LastName]
           ,[FirstName]
           ,[FatherName]
           ,[Birthday]
           ,[Sex]
           ,[DocSeries]
           ,[DocNumber]
           ,[DocType]
           ,[Polis] 
           ,[SNILS]
           ,[BeginDate]
           ,[EndDate]
           ,[Category]
		   ,[Number]
		   ,[Seria] 
		   ,[MKBCode]
		   ,[Additional]
		   ,[LowIncomeRegister]
		   ,[Age]
		   ,[PostingDate]
		   )
	select	    r.MilkRecipeID
	           ,dc.[DairyCenterID]
	           ,r.[LPU]
	           ,r.[LastName]
	           ,r.[FirstName]
	           ,r.[FatherName]
	           ,r.[Birthday]
	           ,eSex.EnumID
	           ,r.[DocSeries]
	           ,r.[DocNumber]
	           ,eDoc.EnumID
			   ,r.[Polis]
			   ,r.[SNILS]
			   ,r.[BeginDate]
			   ,r.[EndDate]
			   ,r.[Category]
			   ,r.[Number]
			   ,r.[Seria]
			   ,r.[MKBCode]
			   ,r.[Additional]
			   ,r.[LowIncomeRegister]
			   ,r.[Age]
			   ,r.[PostingDate]
	from #MILK_RECIPE r
        join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = r.Sex
		join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = r.DocType
		outer apply(
			select top 1 dc.DairyCenterId from RDS.T_DAIRY_CENTER dc where dc.Number = r.DairyCenter and dc.IsActual = 1
			) dc
	where r.RecipeDiscard = 0 and not exists (select * from RDS.T_MILK_RECIPE mr where  mr.MilkRecipeID = r.MilkRecipeID)

 INSERT INTO [RDS].[T_MILK_PRODUCT]
	           (MilkProductID
			   ,MilkRecipe
	           ,[Amount]
			   ,[Dose]
               ,[Name])
	 select		newid()
	           ,r.[MilkRecipeID]
	           ,[Amount]
	           ,[Dose]
			   ,[Name]
	from #MILK_PRODUCT mp
	join #MILK_RECIPE r on r.MilkRecipeID = mp.RecipeId
	where r.RecipeDiscard = 0 and  not exists (select * from RDS.T_MILK_PRODUCT mpp where  mpp.MilkRecipe = r.MilkRecipeID)

delete mp 
from RDS.T_MILK_PRODUCT mp
        join #MILK_RECIPE r on r.MilkRecipeID = mp.MilkRecipe
where r.RecipeDiscard = 1

--if exists (select * from RDS.T_MILK_RECIPE mr
--				join #MILK_RECIPE r on r.MilkRecipeID = mr.MilkRecipeID
--					where mr.MilkRecipeRequest is not null)
--begin
--	raiserror('Невозможно удалить рецепт, так как он имеется в заявке на поставку молочного питания!', 18, 1)
--end
delete mr 
from RDS.T_MILK_RECIPE mr
        join #MILK_RECIPE r on r.MilkRecipeID = mr.MilkRecipeID
where r.RecipeDiscard = 1 --and r.MilkRecipeRequest is not null

END
