create or replace function dbo.s_useringroup(vUserID bigint, vGroupID bigint, vDate timestamp without time zone) 
  returns integer 
  language  plpgsql
  stable parallel safe
as
$body$
declare
  vRes integer; 
begin 
  if vUserID = vGroupID then 
    return 1; 
  end if; 
   
  vRes := case when exists (select TOGROUPID from DEF_USERTOGROUP where USERID = vUserID and TOGROUPID = vGroupID and EXPIRED >= vDate) then 1 else 0 end;  
   
  if vRes = 0 then   
    vRes := case when exists ( 
              select B.GROUPID from DEF_USERTOGROUP A inner join DEF_LINKEDGROUPS B on A.TOGROUPID = B.USERID 
              where A.USERID = vUserID and B.GROUPID = vGroupID and A.EXPIRED >= vDate and B.EXPIRED >= vDate) 
              then 1 else 0 end;  
  end if; 
                 
  return coalesce(vRes, 0);    
end;
$body$;

CREATE OR REPLACE FUNCTION dbo.s_access(varc bigint, vuserid bigint, vaction bigint, vdate timestamp without time zone)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE PARALLEL SAFE
AS $function$
declare 
  Res integer = null;
  IsAdmin integer;
begin
  select TOGROUPID into IsAdmin from DEF_USERTOGROUP where USERID= vUserID and TOGROUPID = -1 and EXPIRED >= vDate;

  if IsAdmin = -1 then
    return 1;
  end if;
 
  if vArc is not null then
    /*личные права*/
    select max(ENABLE) into Res from DEF_ACCESS where ARCTOC = vArc and EXPIRED >= vDate and ACTION_OID = vAction and USERID = vUserID;
  
    if Res = 1 then 
      return 1; 
    elsif Res = 0 then
      return 0;
    end if;
  
    /*права из групп*/   
    select max(ENABLE) into Res from DEF_ACCESS
    where ARCTOC = vArc and EXPIRED >= vDate and ACTION_OID = vAction and USERID in
      (
       select -200 
       union all 
       select TOGROUPID from DEF_USERTOGROUP A where USERID = vUserID and EXPIRED >= vDate and (select B.S_S from DEF_USERS B where B.ID = A.TOGROUPID) = 1
       union all 
       select B.GROUPID from DEF_USERTOGROUP A 
         inner join DEF_LINKEDGROUPS B on A.TOGROUPID = B.USERID 
         where A.USERID = vUserID AND A.EXPIRED >= vDate and (select C.S_S from DEF_USERS C where C.ID = B.GROUPID) = 1
      );
  
    if Res = 1 then 
      return 1; 
    elsif Res = 0 then
      return 0;
    end if;
  end if;
 
  /*опытный пользователь*/
  select TOGROUPID into IsAdmin from DEF_USERTOGROUP where USERID = vUserID AND TOGROUPID = -10 AND EXPIRED >= vDate;
  if IsAdmin = -10 then  
    return 1;
  end if;

  /*Аудитор: просмотр, использование*/
  if (vAction = -1) or (vAction = -5) then
    select TOGROUPID into IsAdmin from DEF_USERTOGROUP where USERID = vUserID and TOGROUPID = -1000 AND EXPIRED >= vDate;
    
    if IsAdmin = -1000 then
      return 1;
    end if;
  end if;
      
  return coalesce(Res, 0);
end; 
$function$
;

CREATE OR REPLACE FUNCTION dbo.s_access2(varc bigint, vuserid bigint, vaction bigint, vdate timestamp without time zone, vdefault integer)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE PARALLEL SAFE
AS $function$
declare
  vEnable integer;  
  vAdmin integer;  
BEGIN  
  vEnable := null;
  vDate := truncate(vDate);
  
  /*группа суперадминистраторов*/  
  begin  
    select TOGROUPID into vAdmin from DEF_USERTOGROUP where TOGROUPID = -1 and USERID = vUserID and EXPIRED >= vDate;  
  exception  
    when NO_DATA_FOUND then  
      vAdmin := 0;  
  end;  
  if vAdmin = -1 then 
    return 1; 
  end If;  
  
  if vArc is not null then  
  
    /*личные права*/  
    select MAX(ENABLE) into vEnable from DEF_ACCESS where ARCTOC = vArc and EXPIRED >= vDate and ACTION_OID = vAction and USERID = vUserID;  
     
    if vEnable = 1 then return 1; elsif vEnable = 0 then return 0; end if;  
  
    /*права из групп */  
    select MAX(ENABLE) into vEnable from DEF_ACCESS  
      where ARCTOC = vArc and EXPIRED >= vDate and ACTION_OID = vAction and USERID in  
        ( 
          select -200  
          union all 
          select TOGROUPID from DEF_USERTOGROUP A where USERID = vUserID and EXPIRED >= vDate  
            and (select B.S_S from DEF_USERS B where B.ID = A.TOGROUPID) = 1 
          union all 
          select B.GROUPID from DEF_USERTOGROUP A  
            inner join DEF_LINKEDGROUPS B ON A.TOGROUPID = B.USERID 
          where A.USERID = vUserID and A.EXPIRED >= vDate  
            and (select C.S_S from DEF_USERS C where C.ID = B.GROUPID) = 1             
        );  
  
    if vEnable = 1 then return 1; elsif vEnable = 0 then return 0; end if;    
  end if;  
    
  /*опытный пользователь*/  
  begin  
    select TOGROUPID into vAdmin from DEF_USERTOGROUP where TOGROUPID = -10 and USERID=vUserID and EXPIRED>=vDate;  
  exception  
    when NO_DATA_FOUND then  
      vAdmin :=0;  
  end;      
  If vAdmin = -10 then return 1; end if;  
  
  /*Аудитор: просмотр, использование*/
  if vAction in (-1, -5) then
    begin
      select TOGROUPID into vAdmin from DEF_USERTOGROUP where TOGROUPID = -1000 and USERID=vUserID and EXPIRED>=vDate;
    exception
      when NO_DATA_FOUND then                 
        vAdmin := 0;
    end;  
    if vAdmin = -1000 then return 1; end if;
  end if;
  
  return coalesce(vEnable, vDefault);    
    
END;
$function$
;

CREATE OR REPLACE FUNCTION dbo.s_access3(varc bigint, vuserid bigint, vaction bigint, vdate timestamp without time zone, vdefault integer, vcreatorid bigint DEFAULT NULL::bigint)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE PARALLEL SAFE
AS $function$
declare
  vEnable integer;  
  vAdmin integer;  
  vInSameGroup integer;
begin
  vEnable := null;
  vDate := truncate(vDate);
  
  /*группа суперадминистраторов*/  
  begin  
    select TOGROUPID into vAdmin from DEF_USERTOGROUP where TOGROUPID = -1 and USERID = vUserID and EXPIRED >= vDate;  
  exception  
    when NO_DATA_FOUND then  
      vAdmin := 0;  
  end;  
  if vAdmin = -1 then 
    return 1; 
  end If;  
  
  if vArc is not null then  
  
    /*личные права*/  
    select MAX(ENABLE) into vEnable from DEF_ACCESS where ARCTOC = vArc and EXPIRED >= vDate and ACTION_OID = vAction and USERID = vUserID;      
    if vEnable = 1 then return 1; elsif vEnable = 0 then return 0; end if;  
  
    if vCreatorId is not null then   
      if (vUserID = vCreatorId) then/* автор */   
    
        select MAX(ENABLE) into vEnable from DEF_ACCESS where ARCTOC = vArc and EXPIRED >= vDate and ACTION_OID = vAction and USERID = -101;       
        if vEnable = 1 then return 1; elsif vEnable = 0 then return 0; end if;  
    
      else /*в одной группе с автором */  
    
        select count(A.TOGROUPID) into vInSameGroup  
          from DEF_USERTOGROUP A  
          where A.USERID = vUserID and A.EXPIRED >= vDate  
           and A.TOGROUPID in (select B.TOGROUPID from DEF_USERTOGROUP B where B.USERID = vCreatorId and B.EXPIRED >= vDate);  
        if vInSameGroup > 0 then  
          select MAX(ENABLE) into vEnable from DEF_ACCESS where ARCTOC = vArc and EXPIRED >= vDate and ACTION_OID = vAction and USERID = -100;       
          if vEnable = 1 then return 1; elsif vEnable=0 then return 0; end if;      
        end if;  
      end if;   
    end if;   
   
    /*права из групп */  
    select MAX(ENABLE) into vEnable from DEF_ACCESS  
      where ARCTOC = vArc and EXPIRED >= vDate and ACTION_OID = vAction and USERID in  
        ( 
          select -200   
          union all 
          select TOGROUPID from DEF_USERTOGROUP A where USERID = vUserID and EXPIRED >= vDate and (select B.S_S from DEF_USERS B where B.ID = A.TOGROUPID) = 1 
          union all 
          select B.GROUPID from DEF_USERTOGROUP A inner join DEF_LINKEDGROUPS B ON A.TOGROUPID = B.USERID 
          where A.USERID = vUserID and A.EXPIRED >= vDate and (select C.S_S from DEF_USERS C where C.ID = B.GROUPID) = 1             
        );  
  
    if vEnable = 1 then return 1; elsif vEnable = 0 then return 0; end if;    
  end if;  
    
  /*опытный пользователь*/  
  begin  
    select TOGROUPID into vAdmin from DEF_USERTOGROUP where TOGROUPID = -10 and USERID = vUserID and EXPIRED>=vDate;  
  exception  
    when NO_DATA_FOUND then  
      vAdmin :=0;  
  end;      
  If vAdmin = -10 then return 1; end if;  
  
  /*Аудитор: просмотр, использование*/
  if vAction in (-1, -5) then
    begin
      select TOGROUPID into vAdmin from DEF_USERTOGROUP where TOGROUPID = -1000 and USERID = vUserID and EXPIRED >= vDate;
    exception
      when NO_DATA_FOUND then                 
        vAdmin := 0;
    end;  
    if vAdmin = -1000 then return 1; end if;
  end if;
  
  return coalesce(vEnable, vDefault);    
    
end;
$function$
;

