CREATE OR REPLACE FUNCTION public.recalculatepaycategorytree(
	parentid uuid DEFAULT NULL::uuid,
	begin integer DEFAULT 1,
	OUT ende integer,
	level integer DEFAULT 0)
    RETURNS integer
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE ChCount integer;
ID  uuid; NestedLevel integer;
curs1 CURSOR FOR 
	SELECT PayCategoryID FROM FIES.T_PAY_CATEGORY WHERE ParentID IS NULL;
curs1P CURSOR FOR 
	SELECT PayCategoryID FROM FIES.T_PAY_CATEGORY WHERE ParentID=ParentID;
BEGIN
 IF (ParentID IS NULL) then   ChCount := (SELECT COUNT(ParentID) FROM FIES.T_PAY_CATEGORY WHERE ParentID IS NULL);
 ELSE ChCount := (SELECT COUNT(ParentID) FROM FIES.T_PAY_CATEGORY WHERE ParentID=ParentID);
 end if;
 
 IF (ChCount = 0 or ChCount is NULL)
	 then
	   EndE := "begin"+1;
 ELSE

	   NestedLevel := Level + 1;
	  IF ParentID IS NULL
		  then
		   SET "begin" = 1;
		   OPEN curs1;
			   FETCH NEXT FROM curs1 INTO ID;
			   WHILE (found)
				   loop
					EXECUTE public.RecalculatePayCategoryTree ID, "begin", EndE , NestedLevel;
					UPDATE FIES.T_PAY_CATEGORY 
					SET LeftIndex="begin", 
						RightIndex=EndE, 
						TreeLevel=Level 
					WHERE PayCategoryID=ID;
					"begin":= EndE + 1;
					FETCH NEXT FROM curs1 INTO ID;
				   END loop;
		   CLOSE curs1;
		   DEALLOCATE curs1;
		  
	  ELSE
		  
		   "begin" := "begin" + 1;
		   OPEN curs1P;
		   FETCH NEXT FROM curs1P INTO ID;
		   WHILE (found)
		   loop
				EXECUTE public.RecalculatePayCategoryTree ID,"begin",EndE , NestedLevel ;
				UPDATE FIES.T_PAY_CATEGORY 
				SET LeftIndex="begin", 
					RightIndex=EndE, 
					TreeLevel=Level 
				WHERE PayCategoryID =ID;
				"begin" := EndE + 1;
				FETCH NEXT FROM curs1 INTO ID;
		   END loop;
		   CLOSE curs1P;
		   DEALLOCATE curs1P;
		END if;

	 EndE := EndE + 1;
	 
END if;
end;
$BODY$;



CREATE OR REPLACE FUNCTION public.recalculatepaycategory(
	)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
declare "end" int;
BEGIN
	alter table FIES.T_PAY_CATEGORY disable trigger all;
	EXECUTE public.recalculatepaycategorytree 'null', 1, "end", 0;
	alter table FIES.T_PAY_CATEGORY enable trigger all;
end;
$BODY$;
