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 tt.html
From
(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=Regular=' + 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>' html,
t0.Pinned,
t0.DateCreate
from VCLib.T_NEWS_BOX_LINK_USERS t
	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%">'+ isnull(convert(varchar(10), ta.DateCreate, 104),'') +'&nbsp;</td>
    <td width="90%">'+ ta.Description +'&nbsp;</td>
    <td><a href="command=DocumentRead=Algom='+ cast(ta.NewsBoxID as 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
