DROP PROCEDURE IF EXISTS sp_GetNewsCount;
DROP FUNCTION IF EXISTS sp_GetNewsCount;

CREATE PROCEDURE sp_GetNewsCount
(
	_UserID uuid
,	_Groups text DEFAULT NULL::text
,   INOUT cur REFCURSOR = null
)
AS
$BODY$
Begin
    open cur for select sum(c1)::varchar(11) || '(' || sum(c2)::varchar(11) || ')'
	from
	(
		select 1 as c1
		, case when NOT EXISTS(SELECT* FROM VCLib.T_NEWS_BOX_LINK_USERS t12 WHERE t0.NewsBoxID = t12.NewsBox AND t12.Users = _UserID) then 1 else 0 end as c2
		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
			inner join 
				fn_Split(_Groups, ';') g 
				on g.Split::uuid = t10.UsersGroup
			WHERE _Groups is not null
		)
	) t;
End;
$BODY$ LANGUAGE plpgsql;
--ALTER PROCEDURE sp_GetNewsCount(uuid, text) OWNER TO postgres;
