DROP PROCEDURE IF EXISTS sp_getunreadnews;
DROP FUNCTION IF EXISTS sp_getunreadnews;

CREATE PROCEDURE public.sp_getunreadnews("UserID" uuid, INOUT Query text = null)
    LANGUAGE plpgsql
    AS $$
Begin
	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>'
	    ||  case t0.Pinned
                        when true then ''
                        else '<td><a href="command=DocumentRead=' || t.NewsBoxLinkUsersID::varchar(36) ||'">Скрыть</a></td>'
                end
             || '
	</tr>
	<tr>
	    <td colspan="3">
		' || 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;
			' || coalesce((
				select 
				xmlelement
				(
					name a
				,	xmlattributes
					(
						'command=AddDocument=' || t2.AddingDocumentID::varchar(36) as href
					)
				,	t2.name
				)
				From VCLib.T_NEWS_BOX_LINK_ADD_DOCUMENT t1
				inner join 
					VCLib.T_ADDING_DOCUMENT t2 
					On t1.AddingDocument = t2.AddingDocumentID
				Where t1.NewsBox = t0.NewsBoxID
				Limit 1)::text,'')
			end
			   || '
	    </td>
	</tr>
	<tr>
	    <td colspan="3" height="2"><hr style="padding: 0px; margin:0px; border:none; height:1px;" /></td>
	</tr>' into Query
	from VCLib.T_NEWS_BOX_LINK_USERS t
	inner join 
		VCLib.T_NEWS_BOX t0 
		On t.NewsBox = t0.NewsBoxID
	Where t.Users = "UserID"
	Order by t0.Pinned desc, t0.DateCreate desc;
End;
$$;
