DROP PROCEDURE IF EXISTS sp_AKUZGetNews;
-- =============================================
-- Author:        Arseniy Gadelshin
-- Create date: 2025/08/11
-- Description:    Получить новости пользователя
-- =============================================
CREATE PROCEDURE sp_AKUZGetNews
(
    "Lpu" uuid
,	"UserID" uuid
,	"Offset" integer
,	"Groups" text = null
,   INOUT Query text = null
)
AS
$BODY$
Begin
    Select tt.trs into Query
	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%">' || 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>
			</tr>
			<tr>
				<td colspan="2">
					' || 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::text || '">' || 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>' trs
			, t0.DateCreate
			, t0.NewsBoxID
			from AKUZ.T_AKUZ_NEWS_BOX t00 inner join VCLib.T_NEWS_BOX t0  on t00.NewsBoxID = t0.NewsBoxID
			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 fn_Split("Groups", ';') g on g.Split::uuid = 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 
                (
                    t00.LpuMain = "Lpu"
                    or t00.LpuMain is null
                )

			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>
			</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_
	limit 5;
End;
$BODY$
  LANGUAGE plpgsql;
--ALTER PROCEDURE sp_GetNews(uuid, integer, text) OWNER TO postgres;
