CREATE OR REPLACE FUNCTION sp_getnews("UserID" uuid, "Offset" integer, "Groups" varchar = null) RETURNS SETOF text
    LANGUAGE plpgsql
    AS $_$
Declare UserIDVar Alias for "UserID";
    OffsetVar Alias for "Offset";
    GroupsVar Alias for "Groups";
Begin
    Return Query
    Select tt.trs
    From 
    (
        Select 
        '<tr style="font-style: italic; font-weight: bold;">
            <td width="10%">' || coalesce(to_char(t0.DateCreate, 'DD.MM.YYYY'),'') || '&nbsp;</td>
            <td width="90%">' || 
            case t0.CreatorType 
                when 0 then 'Администрация учреждения' 
                when 1 then 'Администратор системы' 
                else 'Группа разработки' 
            end || '&nbsp;</td>
        </tr>
        <tr>
            <td colspan="2">
            ' || coalesce(t0.Desc,'')::text || '&nbsp;
                ' || 
                case when not exists (select * from VCLib.T_NEWS_BOX_LINK_ADD_DOCUMENT t1 Where t1.NewsBox = t0.NewsBoxID) 
                    then '' 
                    else '<br/>Прикрепленные документы:&nbsp;
                ' || (select XMLELEMENT
                    (
                        Name a,
                        XMLATTRIBUTES 
                        (
                            'command=AddDocument=' || t2.AddingDocumentID::text AS href
                        ),    
                        t2.Name
                    )
                      From VCLib.T_NEWS_BOX_LINK_ADD_DOCUMENT t1
                      Join VCLib.T_ADDING_DOCUMENT t2 On t1.AddingDocument = t2.AddingDocumentID
                      Where t1.NewsBox = t0.NewsBoxID
                      Limit 1)
                end
                   || '
            </td>
        </tr>
        <tr>
            <td colspan="3" height="2"><hr style="padding: 0px; margin:0px; border:none; height:1px;" /></td>
        </tr>' trs
        , ROW_NUMBER() over ( ORDER BY t0.DateCreate DESC, t0.NewsBoxID) rownumber_ 
        from VCLib.T_NEWS_BOX t0
        where
        t0.NewsBoxID in
        (
            SELECT t3.NewsBox 
            FROM VCLib.T_NEWS_BOX_LINK_MODULE t3  
            WHERE EXISTS 
            (
                SELECT * 
                FROM VCLib.T_ROLE_CLIENT_MODULES t5  
                WHERE t3.ClientModules=t5.ClientModuleID 
                AND  EXISTS 
                ( 
                    SELECT * 
                    FROM VCLib.T_USERS_ROLES t7  
                    WHERE t5.RoleID=t7.RoleID AND t7.UserID = UserIDVar
                )
            )
            UNION ALL
            SELECT t9.NewsBox 
            FROM VCLib.T_NEWS_BOX_LINK_ROLES t9  
            WHERE EXISTS 
            (
                SELECT * 
                FROM VCLib.T_USERS_ROLES t11  
                WHERE t9.Roles=t11.RoleID AND t11.UserID = UserIDVar
            )
            UNION ALL
            SELECT t10.NewsBox
            FROM VCLib.T_NEWS_BOX_USERS_GROUP t10
                join fn_Split(GroupsVar, ';') g on cast(g.Split as uuid) = t10.UsersGroup
            WHERE GroupsVar is not null
        )
        AND
        NOT EXISTS 
        (
            SELECT * 
            FROM VCLib.T_NEWS_BOX_LINK_USERS t12  
            WHERE t0.NewsBoxID=t12.NewsBox AND t12.Users = UserIDVar
        )
        and 
	    (
		    exists
		    (
			    select *
			    from vclib.t_users u
			    inner join vclib.t_divisions d
				    on d.DivisionID = u.DivisionID
				    and u.UserID = UserIDVar
			    inner join vclib.t_divisions d2
				    on d2.LeftIndex <= d.LeftIndex
				    and d2.RightIndex >= d.RightIndex
			    inner join vclib.t_users u2
				    on u2.DivisionID = d2.DivisionID
				    and u2.UserID = t0.Creator
		    )
		    or t0.Creator is null
	    )
    ) tt
    WHERE rownumber_ > OffsetVar
    ORDER BY rownumber_
    Limit 5;
End;
$_$;
