SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

-- =============================================
-- Author:        Marat Zigangaraev
-- Create date: 2015/05/18
-- Description:    Получить новости пользователя
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetNews] 
    @UserID uniqueidentifier
    , @Offset int
    , @Groups varchar(max) = null
As
Begin

Select top 5 tt.trs
From 
(
select ROW_NUMBER() over ( ORDER BY ttt.DateCreate DESC, ttt.NewsBoxID) rownumber_ , ttt.trs
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>
</tr>
<tr>
    <td colspan="2">
        ' + 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>' trs
, t0.DateCreate
, t0.NewsBoxID
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=@UserID
                            )
                )
        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=@UserID
            )
        UNION ALL
        SELECT t10.NewsBox
        FROM VCLib.T_NEWS_BOX_USERS_GROUP t10
            join dbo.fn_Split(@Groups, ';') g on cast(g.Split as uniqueidentifier) = t10.UsersGroup
        WHERE @Groups is not null
    )
    AND
    NOT EXISTS 
    (
        SELECT * 
        FROM VCLib.T_NEWS_BOX_LINK_USERS t12  
        WHERE t0.NewsBoxID=t12.NewsBox AND t12.Users=@UserID
    )
	and 
	(
		exists
		(
			select *
			from vclib.t_users u
			inner join vclib.t_divisions d
				on d.DivisionID = u.DivisionID
				and u.UserID = @UserID
			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
	)

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>

</tr>
<tr>
    <td colspan="2">
	'+ta.html+'
	&nbsp;

    </td>
</tr>
<tr>
    <td colspan="3" height="2"><hr style="padding: 0px; margin:0px; border:none; height:1px;" /></td>
</tr>'

, ta.DateCreate, ta.NewsBoxID

from AKUZ.T_NEWS_BOX_ALGOM ta

where ta.Readed = 1 and ta.Recipient = @UserID

) ttt
) tt
WHERE rownumber_ > @Offset
ORDER BY rownumber_

End
