SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetUnreadNews]') and xtype = N'P')
drop procedure [dbo].[sp_GetUnreadNews]
GO

-- =============================================
-- Author:		Marat Zigangaraev
-- Create date: 2015/05/18
-- Description:	Получить непрочитанные новости пользователя
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetUnreadNews] 
	@UserID uniqueidentifier
As
Begin

Select 
'<tr style="font-style: italic; font-weight: bold;">
    <td width="10%">' + isnull(convert(varchar(10), t0.DateCreate, 104),'') + '&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=' + cast(t.NewsBoxLinkUsersID as varchar(36)) +'">Скрыть</a></td>' end + '
</tr>
<tr>
    <td colspan="3">
        ' + cast(isnull(t0.[Desc],'') as varchar(max)) + '&nbsp;
		' + 
		case when not exists (select * from VCLib.T_NEWS_BOX_LINK_ADD_DOCUMENT t1 Where t1.NewsBox = t0.NewsBoxID) 
			then '' 
			else '<br/>Прикрепленные документы:&nbsp;
		' + isnull(Cast((
		   Select 
		          (select 
		            ( select top 1 'command=AddDocument='+ cast(t2.AddingDocumentID as varchar(36))) as '@href'
				  , ( select cast(t2.Name as varchar(128)) for xml path(''), type) 
				  For xml path('a'), type
				  )
				  , (select ',' for xml path(''), type)
		   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
		   For xml path(''), type
		   ) as varchar(max)),'')
		end
		   + '
    </td>
</tr>
<tr>
    <td colspan="3" height="2"><hr style="padding: 0px; margin:0px; border:none; height:1px;" /></td>
</tr>'
from VCLib.T_NEWS_BOX_LINK_USERS t
	Join VCLib.T_NEWS_BOX t0 On t.NewsBox = t0.NewsBoxID

Where t.Users = @UserID
Order by t0.Pinned desc, t0.DateCreate desc

End
GO
