drop procedure if exists dbo.sp_CreateFrlloRemainsProcess;
drop procedure if exists public.sp_CreateFrlloRemainsProcess;
-- =============================================
-- Author:      Vitacore (ñ)
-- Create date: 2024 11 25
-- Description: Âûãðóçêà äàííûõ â ÔÐËËÎ
-- =============================================
create or replace procedure rds.sp_CreateFrlloRemainsProcess
(
   "ExportId" uuid default null::uuid
);
language plpgsql
as
$$
begin
	declare _LastExport uuid := select efr.ExportFrlloRemainsID from RDS.T_EXPORT_FRLLO_REMAINS efr where efr.CreateDate < @CurrentDate limit 1 order by efr.CreateDate desc;
			_CurrentDate timestamp := (select efr.CreateDate from RDS.T_EXPORT_FRLLO_REMAINS efr where efr.ExportFrlloRemainsID = "ExportId");
			_LastId integer;
			_Count integer;
            _FileNumber integer;
			
	create temp table TMP_FRLLO 
	(
		"id" uuid not null unique,
		"num" serial
	);
	insert into TMP_FRLLO("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
		end
	)
	and not exists (
		select * from RDS.T_EXPORT_FRLLO_REMAINS_DATA ef
				where ef.ExportFrlloRemains = "ExportId"
				and ef.RemainsID = efrd.RemainsID
	);

	_Count := (select max(_Num) from TMP_FRLLO);
    select _FileNumber = (select 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 limit 1);
	if (_Count > 0)
	begin
		_LastId := (perform sp_GenerateIdForEntity('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
			)
		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
		from RDS.T_EXPORT_FRLLO_REMAINS_DATA efrd
		where exists(select 1 FROM TMP_FRLLO WHERE TMP_FRLLO.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;
$$;