DROP PROCEDURE IF EXISTS sp_getunreadnews;
-- =============================================
-- Author:		Marat Zigangaraev
-- Create date: 2015/05/18
-- Description:	Получить непрочитанные новости пользователя
-- =============================================
CREATE PROCEDURE sp_getunreadnews("UserID" uuid, INOUT Query text = null)
AS
$BODY$
Begin
	Select tt.html into Query
	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>'
			|| case when t0.Pinned = '1' then '' else '<td><a href="command=DocumentRead=Regular=' || 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 string_agg('<a href="command=AddDocument=' || t2.AddingDocumentID::varchar(36) || '">' || t2.Name ||'</a>', ',')
							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) || ',', '')
				end
				   || '
			</td>
		</tr>
		<tr>
			<td colspan="3" height="2"><hr style="padding: 0px; margin:0px; border:none; height:1px;" /></td>
		</tr>' html
		,	t0.Pinned
		,	t0.DateCreate
		from VCLib.T_NEWS_BOX_LINK_USERS t
		inner join
			VCLib.T_NEWS_BOX t0 
			on t.NewsBox = t0.NewsBoxID
		Where t.Users = "UserID"
		Union all
		Select 		
		'<tr style="font-style: italic; font-weight: bold;">
			<td width="10%">' || coalesce(to_char(ta.DateCreate, 'DD.MM.YYYY'), '') || '&nbsp;</td>
			<td width="90%">' || ta.Description || '&nbsp;</td>
			<td><a href="command=DocumentRead=Algom=' || ta.NewsBoxID::varchar(36) || '">Скрыть</a></td>
		</tr>
		<tr>
			<td colspan="3">
			' || ta.Html || '
			&nbsp;

			</td>
		</tr>
		<tr>
			<td colspan="3" height="2"><hr style="padding: 0px; margin:0px; border:none; height:1px;" /></td>
		</tr>'
		,	ta.Pinned
		,	ta.DateCreate
		from AKUZ.T_NEWS_BOX_ALGOM ta
		where ta.Readed = '0' 
		and ta.Recipient = "UserID"
	) tt
	Order by tt.Pinned desc, tt.DateCreate desc;
End;
$BODY$
  LANGUAGE plpgsql;
--ALTER PROCEDURE sp_getunreadnews(uuid) OWNER TO postgres;
