IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateFrlloRemainsProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CreateFrlloRemainsProcess]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_CreateFrlloRemainsProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_CreateFrlloRemainsProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2023 11 24
-- Description: Выгрузка данных в ФРЛЛО
-- =============================================
CREATE PROCEDURE [rds].[sp_CreateFrlloRemainsProcess]
    @ExportId uniqueidentifier
AS
BEGIN
	declare @LastExport uniqueidentifier, @CurrentDate datetime
	
	select @CurrentDate = efr.CreateDate 
	from RDS.T_EXPORT_FRLLO_REMAINS efr 
	where efr.ExportFrlloRemainsID = @ExportId

	select top 1 @LastExport = efr.ExportFrlloRemainsID 
	from RDS.T_EXPORT_FRLLO_REMAINS efr 
	where efr.CreateDate < @CurrentDate and efr.ExportFrlloRemainsID <> @ExportId
	order by efr.CreateDate desc


CREATE TABLE #TMP_FRLO(
	 ID int UNIQUE
	,NUM INT IDENTITY(1,1) 
)

INSERT INTO #TMP_FRLO(ID)
SELECT ExportFrlloRemainsDataID
from RDS.T_EXPORT_FRLLO_REMAINS_DATA efrd
where efrd.ExportFrlloRemains = @LastExport 
	and(
		case when efrd.ItemQty > 0 then 1
		when efrd.FrlloStatus = 400 and efrd.[IsDelete] = 1 then 1
		else 0 end) = 1
	and not exists(select * 
				from RDS.T_EXPORT_FRLLO_REMAINS_DATA ef
				where ef.ExportFrlloRemains = @ExportId
				and ef.RemainsID = efrd.RemainsID)

--if(not exists(select * from T_ENTITY_IDS where EntName = 'RDS.EXPORT_FRLLO_REMAINS_DATA' and FieldName = 'PrimaryKey'))
--begin
--INSERT INTO [dbo].[T_ENTITY_IDS]
--           ([EntName]
--           ,[FieldName]
--           ,[id])
--     VALUES
--           ('RDS.EXPORT_FRLLO_REMAINS_DATA'
--           ,'PrimaryKey'
--           ,0)
--EnD

declare @LastId int, @Count int, @FileNumber int
SELECT @Count = (SELECT MAX(NUM) FROM #TMP_FRLO)
select @FileNumber = (select top 1 case when count(*) > 100000 then FileNumber + 1 else FileNumber end
                        from RDS.T_EXPORT_FRLLO_REMAINS_DATA rd
                        where rd.ExportFrlloRemains = @ExportId
                        group by FileNumber
                        order by FileNumber desc)

if(@Count > 0)
	begin
		exec @LastId = [dbo].sp_GenerateIdForEntity 'RDS.EXPORT_FRLLO_REMAINS_DATA', 'PrimaryKey', @Count
				
		INSERT INTO [RDS].[T_EXPORT_FRLLO_REMAINS_DATA]
		           ([ExportFrlloRemainsDataID]
		           ,[ExportFrlloRemains]
		           ,[FrlloId]
		           ,[Pharmacy]
		           ,[KLPCode]
		           ,[MICode]
		           ,[SpecCode]
		           ,[PackQty]
		           ,[ItemQty]
		           ,[DrugExpirationDt]
		           ,[DrugSeria]
		           ,[Name]
				   ,[IsDelete]
				   ,[RemainsID]
                   ,[FileNumber])
		select	@LastId + ROW_NUMBER() over (order by efrd.[RemainsID])
				,@ExportId
				,[FrlloId]
		        ,efrd.[Pharmacy]
		        ,efrd.[KLPCode]
		        ,efrd.[MICode]
		        ,efrd.[SpecCode]
		        ,0
		        ,0
		        ,efrd.[DrugExpirationDt]
		        ,efrd.[DrugSeria]
		        ,efrd.[Name]
				,1
				,efrd.[RemainsID]
                ,@FileNumber
		from RDS.T_EXPORT_FRLLO_REMAINS_DATA efrd
		where 
			EXISTS(SELECT 1 FROM #TMP_FRLO WHERE #TMP_FRLO.ID = efrd.ExportFrlloRemainsDataID)
		
		
		update T_ENTITY_IDS set id = (select max(ExportFrlloRemainsDataID) from RDS.T_EXPORT_FRLLO_REMAINS_DATA)
		where EntName = 'RDS.EXPORT_FRLLO_REMAINS_DATA' and FieldName = 'PrimaryKey'
	end
END				
				