drop procedure if exists sp_insertNewsToBase;
drop function if exists sp_insertNewsToBase;

create procedure sp_insertNewsToBase (_date timestamp, _desc varchar, _creatortype integer, _modules varchar)
as $$
declare
_newsid uuid;
begin
_newsid:=uuid_generate_v1mc();

create temp table _m_table(
id uuid,
name varchar);

insert into _m_table(name)
select split from fn_split(_modules, '+');

update _m_table m 
set id = (select clientmoduleid from VCLib.t_client_modules where moduletype like m.name || '%');

create temp table _uu(
userid uuid);

insert into _uu(userid)
select distinct u.userid 
from VCLib.t_users u 
join VCLib.t_users_roles ur on ur.userid=u.userid
join VCLib.t_role_client_modules rcm on rcm.roleid=ur.roleid
where rcm.clientmoduleid in (select id from _m_table);

insert into VCLib.t_news_box(newsboxid, datecreate, desc, creatortype, pinned)
values (_newsid,_date,_desc,_creatortype,'0');

insert into VCLib.t_news_box_link_users(newsboxlinkusersid,users,newsbox)
select uuid_generate_v1mc(), userid, _newsid from _uu;

insert into VCLib.t_news_box_link_module (newsboxlinkmoduleid, clientmodules, newsbox)
select uuid_generate_v1mc(), rcm.clientmoduleid, _newsid
from VCLib.t_users u 
join VCLib.t_users_roles ur on ur.userid = u.userid
join VCLib.t_role_client_modules rcm on rcm.roleid=ur.roleid
where rcm.clientmoduleid in (select id from _m_table);

drop table _uu;
drop table _m_table;
end;
$$ language plpgsql;
