/***************************************************
IGNORE
*/
update t_p_user_topic set b_new = '0'
where i_user_id
= :i_user_id
and i_topic_id = :i_topic_id;
update t_p_user_topic set b_new = '0'
where i_user_id =
:i_user_id
and b_new = '1'
and b_notify = '0'
and
i_topic_id = :i_topic_id;
create procedure unset_new_uf(i_user_id integer, i_forum_id integer)
as
declare i_topic_id integer;
begin
for select i_topic_id from user_topic_rights_f(:i_user_id,
:i_forum_id)
where b_r = '0'
into :i_topic_id
do
update t_p_user_topic set b_new = '0'
where i_user_id =
:i_user_id
and b_new = '1'
and b_notify = '0'
and
i_topic_id = :i_topic_id;
create procedure unset_new_ut(i_user_id integer, i_topic_id integer)
as
begin
update t_p_user_topic set b_new =
'0'
where i_user_id =
:i_user_id
and b_new = '1'
and b_notify = '0'
and
i_topic_id = :i_topic_id;
create procedure unset_new_t(i_topic_id integer)
as
begin
update t_p_user_topic set b_new = '0'
where b_new
= '1'
and b_notify = '0'
and i_topic_id = :i_topic_id
and i_user_id in (select i_user_id from topic_user_rights(:i_topic_id) where
b_r = '0');
/***************************************************
FEED UTENTE
*/
as
DECLARE i_order_v INTEGER;
DECLARE i_user_id_v INTEGER;
BEGIN
SELECT i_order,i_user_id
FROM t_s_user_feed
WHERE i_user_feed_id = :i_user_feed_id_p
INTO i_order_v, i_user_id_v;
IF (i_order_v > 1) THEN
BEGIN
UPDATE t_s_user_feed SET i_order = i_order + 1
WHERE (i_user_id = :i_user_id_v)
AND i_order = :i_order_v - 1;
UPDATE t_s_user_feed SET i_order = i_order - 1
WHERE i_user_feed_id = :i_user_feed_id_p;
END
else
exception e_feed_cantmove;
END ^
alter PROCEDURE user_feed_down(
i_user_feed_id_p integer
)
as
DECLARE i_order_v INTEGER;
DECLARE i_user_id_v INTEGER;
DECLARE max_order_v INTEGER;
BEGIN
SELECT i_order, i_user_id
FROM t_s_user_feed
WHERE i_user_feed_id = :i_user_feed_id_p
INTO i_order_v, i_user_id_v;
SELECT MAX(i_order)
FROM t_s_user_feed
WHERE (i_user_id = :i_user_id_v)
INTO max_order_v;
IF (i_order_v < max_order_v) THEN
BEGIN
UPDATE t_s_user_feed SET i_order = i_order - 1
WHERE (i_user_id = :i_user_id_v)
and i_order = :i_order_v + 1;
UPDATE
t_s_user_feed SET i_order = i_order +
1
WHERE i_user_feed_id = :i_user_feed_id_p;
END
else
exception e_feed_cantmove;
END ^
/***************************************************
BOOKMARK E FORUM BOOKMARKS
*/
as
DECLARE i_order_v INTEGER;
DECLARE i_user_id_v INTEGER;
BEGIN
SELECT i_order,i_user_id
FROM t_s_user_forum_bmk
WHERE i_user_forum_bmk_id = :i_user_forum_bmk_id_p
INTO i_order_v, i_user_id_v;
IF (i_order_v > 1) THEN
BEGIN
UPDATE t_s_user_forum_bmk SET i_order = i_order + 1
WHERE (i_user_id = :i_user_id_v)
AND i_order = :i_order_v - 1;
UPDATE t_s_user_forum_bmk SET i_order = i_order - 1
WHERE i_user_forum_bmk_id = :i_user_forum_bmk_id_p;
END
else
exception e_forum_cantmove;
END ^
create PROCEDURE
user_forum_bmk_down(
i_user_forum_bmk_id_p integer
)
as
DECLARE i_order_v INTEGER;
DECLARE i_user_id_v INTEGER;
DECLARE max_order_v INTEGER;
BEGIN
SELECT i_order, i_user_id
FROM t_s_user_forum_bmk
WHERE i_user_forum_bmk_id = :i_user_forum_bmk_id_p
INTO i_order_v, i_user_id_v;
SELECT MAX(i_order)
FROM t_s_user_forum_bmk
WHERE (i_user_id = :i_user_id_v)
INTO max_order_v;
IF (i_order_v < max_order_v) THEN
BEGIN
UPDATE t_s_user_forum_bmk SET i_order = i_order - 1
WHERE (i_user_id = :i_user_id_v)
and i_order = :i_order_v + 1;
UPDATE
t_s_user_forum_bmk
SET i_order = i_order + 1
WHERE i_user_forum_bmk_id = :i_user_forum_bmk_id_p;
END
else
exception
e_forum_cantmove;
END ^
CREATE PROCEDURE user_forum_bmks(i_user_id integer, i_parent_forum_id integer)
RETURNS(i_forum_id integer)
as BEGIN
END ^
ALTER PROCEDURE user_forum_bmks(i_user_id integer, i_parent_forum_id
integer)
RETURNS(i_forum_id integer) as
DECLARE b_bmk char(1);
DECLARE b_r char(1);
BEGIN
FOR SELECT distinct f.i_forum_id, ut.b_bookmark, ur.b_r
FROM t_a_forum f
LEFT JOIN t_s_topic t on (f.i_forum_id = t.i_forum_id)
LEFT JOIN t_p_user_topic ut on (t.i_topic_id = ut.i_topic_id and ut.i_user_id = :i_user_id)
LEFT JOIN user_topic_rights_t(:i_user_id, t.i_topic_id) ur on (ur.i_forum_id = f.i_forum_id)
WHERE (f.i_parent_forum_id = :i_parent_forum_id or f.i_parent_forum_id is null and :i_parent_forum_id is null)
AND f.i_forum_id NOT IN (select t_value from t_e_param where t_param IN('F_PM', 'F_GB'))
ORDER BY f.i_order
INTO :i_forum_id, :b_bmk, :b_r
DO BEGIN
if (:b_r = '1' and :b_bmk = '1') then suspend;
for select i_forum_id from user_forum_bmks(:i_user_id, :i_forum_id)
into :i_forum_id
do suspend;
END
END ^
/***************************************************
FEEDS
*/
CREATE PROCEDURE user_forum_new(i_user_id integer, i_parent_forum_id integer)
RETURNS(i_forum_id integer)
as BEGIN
END ^
-- i forum in cui c'e' qualcosa di nuovo - da qua nasce la pagina
di ricerca
ALTER PROCEDURE user_forum_new(i_user_id integer, i_parent_forum_id integer)
RETURNS(i_forum_id integer) as
DECLARE i_forum_child_id integer;
BEGIN
FOR SELECT distinct f.i_forum_id
FROM forum_desc(:i_parent_forum_id) f
INNER
JOIN t_a_forum ff on (f.i_forum_id = ff.i_forum_id)
INNER JOIN t_s_topic t on (f.i_forum_id = t.i_forum_id)
INNER JOIN t_p_user_topic ut on (t.i_topic_id = ut.i_topic_id and ut.i_user_id = :i_user_id)
LEFT JOIN user_forum_rights_f(:i_user_id, f.i_forum_id) ufr on (ufr.i_forum_id = f.i_forum_id)
LEFT JOIN user_topic_rights_t(:i_user_id, t.i_topic_id) utr on (utr.i_topic_id = ut.i_topic_id)
LEFT JOIN t_p_user_forum_ignore i on (f.i_forum_id = i.i_forum_id and i.i_user_id = :i_user_id)
WHERE
ufr.b_r = '1'
and utr.b_r = '1'
and ut.b_new = '1'
and (i.i_user_id is null or
ut.b_notify = '1')
ORDER BY ff.i_parent_forum_id nulls first, ff.i_order
INTO :i_forum_idDO BEGIN
suspend;
END
END ^
alter PROCEDURE user_topic_new(i_user_id integer, i_parent_forum_id integer)
RETURNS(i_topic_id integer, t_title varchar(255), i_forum_id integer, i_last_read_post_id integer, i_last_post_id integer, i_first_unread_post_id integer) as
BEGIN
FOR SELECT t.i_topic_id, t.t_title, t.i_forum_id, ut.i_last_read_post_id, t.i_last_post_id
FROM t_s_topic t
INNER
JOIN forum_desc(:i_parent_forum_id) d on (d.i_forum_id =
t.i_forum_id)
INNER JOIN t_p_user_topic ut on (t.i_topic_id = ut.i_topic_id and ut.i_user_id = :i_user_id)
LEFT JOIN user_topic_rights_t(:i_user_id, t.i_topic_id) utr on (utr.i_topic_id = t.i_topic_id)LEFT JOIN user_forum_rights_f(:i_user_id, t.i_forum_id) ufr on (ufr.i_forum_id = d.i_forum_id)
LEFT
JOIN t_p_user_forum_ignore i on (d.i_forum_id = i.i_forum_id and i.i_user_id =
:i_user_id)
WHERE
ut.b_new = '1'
AND utr.b_r = '1'
AND ufr.b_r = '1'
AND
(i.i_user_id is null or ut.b_notify = '1')
order by t.r_last_post desc
into :i_topic_id, :t_title,
:i_forum_id, :i_last_read_post_id, :i_last_post_id
DO begin
-- se non ho segnato
l'ultimo post letto, metto il primo post del topic
if (:i_last_read_post_id is null) then begin
select min(i_post_id)
from t_s_post
where i_topic_id = :i_topic_id
and r_insert = (select min(r_insert) from t_s_post where i_topic_id = :i_topic_id)
into :i_last_read_post_id;
i_first_unread_post_id = :i_last_read_post_id;
-- altrimenti ci metto
dentro il post successivo
end else begin
select min(i_post_id)
from t_s_post where
i_topic_id = :i_topic_id
and r_insert = (select min(r_insert)
from t_s_post
where i_topic_id = :i_topic_id
and r_insert > (select r_insert from t_s_post where i_post_id = :i_last_read_post_id))
into :i_first_unread_post_id;
if (:i_first_unread_post_id is null) then i_first_unread_post_id = :i_last_read_post_id;
end
SUSPEND;
end
END ^
-- tutti i post dall ultimo letto in poi ....
alter PROCEDURE user_topic_new_t(i_user_in_id integer, i_topic_in_id integer)
RETURNS(i_post_id integer, i_user_id integer, t_title varchar(255), t_post_source blob, r_insert timestamp) as
declare i_last_read_post_id integer;
BEGIN
SELECT t.t_title, ut.i_last_read_post_id
FROM t_s_topic t
INNER JOIN t_p_user_topic ut on (t.i_topic_id = ut.i_topic_id and ut.i_user_id = :i_user_in_id)
LEFT JOIN user_topic_rights_t(:i_user_in_id, :i_topic_in_id) utr on (utr.i_topic_id = t.i_topic_id)
LEFT JOIN user_forum_rights_f(:i_user_in_id, t.i_forum_id) ufr on (ufr.i_forum_id = t.i_forum_id)
WHERE
t.i_topic_id =
:i_topic_in_id
AND ut.b_new = '1'
AND ut.b_notify = '1'
AND utr.b_r = '1'
AND ufr.b_r = '1'
INTO :t_title, :i_last_read_post_id;
if (:i_last_read_post_id is null) then begin
select min(i_post_id)
from t_s_post
where
i_topic_id = :i_topic_in_id
and r_insert = (select min(r_insert) from t_s_post where i_topic_id = :i_topic_in_id)
into :i_last_read_post_id;
end
FOR SELECT p.i_post_id, p.i_user_id,
case when p.t_title is null
then :t_title else p.t_title end,
case when
p.t_censored_source is null then p.t_post_source else p.t_censored_source end,
p.r_insert
FROM t_s_post p
WHERE
p.i_topic_id = :i_topic_in_id
and
p.r_insert >= (select r_insert from t_s_post where i_post_id =
:i_last_read_post_id)
ORDER BY r_insert desc
INTO :i_post_id, :i_user_id, :t_title,
:t_post_source, :r_insert
do suspend;
END ^
alter PROCEDURE user_topic_last(i_user_id integer, i_parent_forum_id integer, i_count integer)
RETURNS(i_topic_id integer, t_title varchar(255), i_forum_id integer, i_last_read_post_id integer, i_last_post_id integer, i_first_unread_post_id integer) as
BEGIN
FOR SELECT t.i_topic_id, t.t_title,
t.i_forum_id, ut.i_last_read_post_id, t.i_last_post_id
FROM t_s_topic t
INNER
JOIN forum_desc(:i_parent_forum_id) d on (d.i_forum_id =
t.i_forum_id)
INNER JOIN t_p_user_topic ut on (t.i_topic_id = ut.i_topic_id and ut.i_user_id = :i_user_id)
LEFT JOIN user_topic_rights_t(:i_user_id, t.i_topic_id) utr on (utr.i_forum_id = d.i_forum_id)
LEFT JOIN user_forum_rights_f(:i_user_id, d.i_forum_id) ufr on (ufr.i_forum_id = d.i_forum_id)
WHERE
utr.b_r = '1'
AND ufr.b_r = '1'
order by t.r_last_post desc
into :i_topic_id, :t_title,
:i_forum_id, :i_last_read_post_id, :i_last_post_id
DO begin
-- se non ho segnato l'ultimo post letto, metto il primo post del topic
if (:i_last_read_post_id is null) then begin
select min(i_post_id)
from t_s_post
where
i_topic_id = :i_topic_id
and r_insert = (select min(r_insert) from t_s_post where i_topic_id = :i_topic_id)
into :i_last_read_post_id;
i_first_unread_post_id = :i_last_read_post_id;
-- altrimenti ci metto
dentro il post successivo
end else begin
select min(i_post_id)
from t_s_post where
i_topic_id = :i_topic_id
and r_insert = (select min(r_insert)
from t_s_post
where i_topic_id = :i_topic_id
and r_insert > (select r_insert from t_s_post where i_post_id = :i_last_read_post_id))
into :i_first_unread_post_id;
if (:i_first_unread_post_id is null) then i_first_unread_post_id = :i_last_read_post_id;
end
if (i_count > 0 or
i_count is null) then begin
SUSPEND;
i_count = i_count - 1;
end else
exit;
end
END ^
alter PROCEDURE user_topic_last_t(i_user_in_id integer, i_topic_in_id integer, i_count integer)
RETURNS(i_post_id integer, i_user_id integer, t_title varchar(255), t_post_source blob, r_insert timestamp) as
declare i_last_read_post_id integer;
BEGIN
SELECT t.t_title, ut.i_last_read_post_id
FROM t_s_topic t
INNER JOIN t_p_user_topic ut on (t.i_topic_id = ut.i_topic_id and ut.i_user_id = :i_user_in_id)
LEFT JOIN user_topic_rights_t(:i_user_in_id, t.i_topic_id) utr on (utr.i_forum_id = t.i_forum_id)
LEFT JOIN user_forum_rights_f(:i_user_in_id, t.i_forum_id) ufr on (ufr.i_forum_id = t.i_forum_id)
WHERE
t.i_topic_id = :i_topic_in_id
AND utr.b_r = '1'
AND ufr.b_r = '1'
INTO :t_title, :i_last_read_post_id;
if (:i_last_read_post_id is null) then begin
select min(i_post_id)
from t_s_post
where
i_topic_id = :i_topic_in_id
and r_insert = (select min(r_insert) from t_s_post where i_topic_id = :i_topic_in_id)
into :i_last_read_post_id;
end
FOR SELECT p.i_post_id, p.i_user_id,
case when p.t_title is null
then :t_title else p.t_title end,
case when
p.t_censored_source is null then p.t_post_source else p.t_censored_source end,
p.r_insert
FROM t_s_post p
WHERE i_topic_id = :i_topic_in_id
and r_insert >= (select
r_insert from t_s_post where i_post_id = :i_last_read_post_id)
ORDER BY r_insert desc
INTO :i_post_id, :i_user_id, :t_title,
:t_post_source, :r_insert
DO if (i_count > 0 or i_count is null) then
begin
SUSPEND;
i_count = i_count - 1;
end else
exit;
END ^
alter PROCEDURE user_topic_top(i_user_id integer, i_parent_forum_id integer, i_count integer)
RETURNS(i_topic_id integer, t_title varchar(255), i_forum_id integer, i_last_read_post_id integer, i_last_post_id integer, i_first_unread_post_id integer) as
BEGIN
FOR SELECT t.i_topic_id, t.t_title, t.i_forum_id/*, t.i_last_post_id*/
FROM t_s_topic t
INNER
JOIN forum_desc(:i_parent_forum_id) d on (d.i_forum_id =
t.i_forum_id)
LEFT JOIN user_topic_rights_t(:i_user_id, t.i_topic_id) utr on (utr.i_forum_id = d.i_forum_id)
LEFT JOIN user_forum_rights_f(:i_user_id, t.i_forum_id) ufr on (ufr.i_forum_id = d.i_forum_id)
WHERE
utr.b_r = '1'
AND ufr.b_r = '1'
order by (select r_insert from t_s_post p where p.i_topic_id = t.i_topic_id order by p.r_insert asc, p.i_post_id asc rows 1) desc
into :i_topic_id, :t_title,
:i_forum_id/*, :i_last_post_id*/
DO begin
-- qua faccio puntare
sempre al primo post del topic
select min(i_post_id)
from t_s_post
where
i_topic_id = :i_topic_id
and r_insert = (select min(r_insert) from t_s_post where i_topic_id = :i_topic_id)
into :i_last_read_post_id;
i_first_unread_post_id = :i_last_read_post_id;
i_last_post_id = :i_last_read_post_id;
if (i_count > 0 or
i_count is null) then begin
SUSPEND;
i_count = i_count - 1;
end else
exit;
end
END ^
alter PROCEDURE user_topic_top_t(i_user_in_id integer, i_topic_in_id integer, i_count integer)
RETURNS(i_post_id integer, i_user_id integer, t_title varchar(255), t_post_source blob, r_insert timestamp) as
declare i_last_read_post_id integer;
BEGIN
SELECT t.t_title
FROM t_s_topic t
LEFT JOIN user_topic_rights_t(:i_user_in_id, t.i_topic_id) utr on (utr.i_forum_id = t.i_forum_id)
LEFT JOIN user_forum_rights_f(:i_user_in_id, t.i_forum_id) ufr on (ufr.i_forum_id = t.i_forum_id)
WHERE
t.i_topic_id = :i_topic_in_id
AND utr.b_r = '1'
AND ufr.b_r = '1'
INTO :t_title;
FOR SELECT p.i_post_id, p.i_user_id,
case when p.t_title is null
then :t_title else p.t_title end,
case when
p.t_censored_source is null then p.t_post_source else p.t_censored_source end,
p.r_insert
FROM t_s_post p
WHERE i_topic_id = :i_topic_in_id
ORDER BY r_insert asc
INTO :i_post_id, :i_user_id, :t_title,
:t_post_source, :r_insert
DO if (i_count > 0 or i_count is null) then
begin
SUSPEND;
i_count = i_count - 1;
end else
exit;
END ^
/***************************************************
RECENTS
*/
CREATE PROCEDURE user_forum_recent(i_user_id integer, i_days integer, i_parent_forum_id integer)
RETURNS(i_forum_id integer)
as BEGIN
END ^
ALTER PROCEDURE user_forum_recent(i_user_id integer, i_days integer,
i_parent_forum_id integer)
RETURNS(i_forum_id integer) as
DECLARE b_r char(1);
BEGIN
FOR SELECT distinct f.i_forum_id, uf.b_r
FROM forum_desc(:i_parent_forum_id) d
INNER JOIN t_a_forum f on
(f.i_forum_id = d.i_forum_id)
LEFT JOIN user_forum_rights_f(:i_user_id, f.i_forum_id) uf on (uf.i_forum_id = f.i_forum_id)
WHERE /* (f.i_parent_forum_id = :i_parent_forum_id or f.i_parent_forum_id is null and :i_parent_forum_id is null)
AND*/ f.i_forum_id NOT IN (select t_value from t_e_param where t_param IN('F_PM', 'F_GB'))
ORDER BY f.i_parent_forum_id nulls first, f.i_order
INTO :i_forum_id, :b_r
DO BEGIN
if (:b_r = '1' and exists(
SELECT
t.i_topic_id
FROM t_s_topic t
WHERE
'1' = (SELECT b_r FROM user_topic_rights_t(:i_user_id,
t.i_topic_id))
AND t.i_forum_id = :i_forum_id
AND current_date - CAST(t.r_last_post
AS DATE) <= :i_days)) THEN
suspend;
-- into :i_forum_id
-- do suspend;
END
END ^
alter procedure topic_hit(i_user_id integer, i_topic_id integer)
as begin
UPDATE t_s_topic SET i_hit_count = i_hit_count + 1
WHERE i_topic_id = :i_topic_id;
UPDATE t_a_forum SET i_hit_count = i_hit_count + 1
WHERE i_forum_id = (SELECT i_forum_id FROM t_s_topic WHERE i_topic_id = :i_topic_id);
UPDATE t_p_user_topic SET
b_new = '0', i_last_read_post_id = (SELECT i_last_post_id FROM t_s_topic WHERE i_topic_id = :i_topic_id)
WHERE i_user_id = :i_user_id and i_topic_id = :i_topic_id;
INSERT INTO T_S_TOPIC_HIT(i_topic_id,
i_user_id) VALUES (:i_topic_id, :i_user_id);
end ^
/***************************************************
FORUM
*/
create procedure forum_desc(i_parent_forum_id integer)
returns (i_forum_id integer)
as
begin
end ^
create procedure forum_desc_link(i_parent_forum_id integer)
returns (i_forum_id integer, t_url varchar(4096))
as
begin
end ^
returns (t_name varchar(192), t_description blob, t_url
varchar(4096))
as
begin
t_url = '';
while (i_forum_id is not null) do
begin
select t_name, t_description, i_parent_forum_id from t_a_forum where i_forum_id = :i_forum_id into :t_name, t_description, :i_forum_id;
t_url = lower(t_name) ||
'/' || t_url;
end
if (i_topic_id is not null) then t_url = t_url || i_topic_id;
if (i_post_id is not null) then begin
-- select i_page from
post_page(:i_post_id) into :i_page;
-- t_url = t_url || '.' || i_page || '.' || i_post_id;
t_url = t_url || '.*.' || i_post_id;
end else if (i_page is not null) then
t_url = t_url || '.' || i_page;
suspend;
end ^
alter procedure forum_desc(i_parent_forum_id integer)
returns (i_forum_id integer)
as
declare i_forum_child_id integer;
declare i_forum_desc_id integer;
begin
if (i_parent_forum_id is not null) then begin
i_forum_id = :i_parent_forum_id;
suspend;
end
from t_a_forum
where ((i_parent_forum_id = :i_parent_forum_id)
or ((i_parent_forum_id is null) and (:i_parent_forum_id is null)))
and
i_forum_id not in (select cast(t_value as integer) from t_e_param where
t_param in ('F_PM', 'F_GB'))
order by i_order
into :i_forum_child_id do begin
for select i_forum_idfrom forum_desc(:i_forum_child_id)
into :i_forum_desc_id
do begin
i_forum_id = :i_forum_desc_id;
suspend;
end
end
end ^
alter procedure forum_desc_link(i_parent_forum_id integer)
returns (i_forum_id integer, t_url varchar(4096))
as
begin
for select i_forum_id
from forum_desc(:i_parent_forum_id)
into :i_forum_id
do begin
select t_url from forum_link(:i_forum_id, null, null, null)
into :t_url;
suspend;
end
end ^
CREATE PROCEDURE forum_up( i_forum_id_p integer )
as
DECLARE i_order_v INTEGER;
DECLARE i_parent_forum_id_v INTEGER;
BEGIN
SELECT i_order,i_parent_forum_id
FROM t_a_forum
WHERE i_forum_id = :i_forum_id_p
INTO i_order_v, i_parent_forum_id_v;
IF (i_order_v > 1) THEN
BEGIN
UPDATE t_a_forum SET i_order = i_order + 1
WHERE ((i_parent_forum_id = :i_parent_forum_id_v) or (i_parent_forum_id is null and :i_parent_forum_id_v is null))
AND i_order = :i_order_v - 1;
UPDATE t_a_forum SET i_order = i_order - 1
WHERE i_forum_id = :i_forum_id_p;
END
END ^
CREATE PROCEDURE forum_down(
i_forum_id_p integer
)
as
DECLARE i_order_v INTEGER;
DECLARE i_parent_forum_id_v INTEGER;
DECLARE max_order_v INTEGER;
BEGIN
SELECT i_order, i_parent_forum_id
FROM t_a_forum
WHERE i_forum_id = :i_forum_id_p
INTO i_order_v, i_parent_forum_id_v;
SELECT MAX(i_order)
FROM t_a_forum
WHERE (i_parent_forum_id = :i_parent_forum_id_v)
or (i_parent_forum_id is null and :i_parent_forum_id_v is null)
INTO max_order_v;
IF (i_order_v < max_order_v) THEN
BEGIN
UPDATE t_a_forum SET i_order = i_order - 1
WHERE ((i_parent_forum_id = :i_parent_forum_id_v)
or (i_parent_forum_id is null and :i_parent_forum_id_v is null))
and i_order = :i_order_v + 1;
UPDATE
t_a_forum SET i_order = i_order + 1
WHERE i_forum_id = :i_forum_id_p;
END
END ^
CREATE PROCEDURE forum_move(
i_forum_id_p integer, i_new_parent_id_p INTEGER
)
as
DECLARE i_order_v integer;
DECLARE i_old_parent_id_v integer;
DECLARE max_order_v integer;
BEGIN
SELECT i_order, i_parent_forum_id
FROM t_a_forum WHERE i_forum_id = :i_forum_id_p
INTO i_order_v, i_old_parent_id_v;
SELECT case when MAX(i_order) is null then 0 else max(i_order) end
FROM t_a_forum
WHERE (i_parent_forum_id = :i_new_parent_id_p)
or (i_parent_forum_id is null and :i_new_parent_id_p is null)
INTO max_order_v;
UPDATE t_a_forum
SET i_parent_forum_id = :i_new_parent_id_p, i_order = :max_order_v + 1
WHERE i_forum_id = :i_forum_id_p;
UPDATE t_a_forum
SET i_order = i_order - 1
WHERE ((i_parent_forum_id = :i_old_parent_id_v)
or (i_parent_forum_id is null and :i_old_parent_id_v is null))
AND i_order > :i_order_v;
END ^
-- metto i miei figli al mio posto e sparisco
create procedure forum_delete(i_forum_id integer)
as
declare i_parent_forum_id integer;
declare i_child_forum_id integer;
declare i_childs integer;
declare i_order integer;
begin
select i_parent_forum_id, i_order
from t_a_forum
where i_forum_id = :i_forum_id
into :i_parent_forum_id, :i_order;
select count(*)
from t_a_forum
where i_parent_forum_id = :i_forum_id
into :i_childs;
/* faccio posto per i miei figli */
update t_a_forum set i_order = i_order + :i_childs - 1
where (i_parent_forum_id = :i_parent_forum_id or (i_parent_forum_id is null and :i_parent_forum_id is null))
and i_order > :i_order;
/* sposto i miei figli da mio padre nel mio posto */
/* carico l'ordinale per il primo nodo da aggiungere,
poi incremento di uno man mano */
FOR SELECT i_forum_id
FROM t_a_forum
where i_parent_forum_id = :i_forum_id
into :i_child_forum_id
do begin
update t_a_forum set
i_parent_forum_id = :i_parent_forum_id,
i_order = :i_order
where i_forum_id = :i_child_forum_id;
i_order = i_order + 1;
end
/* mi cancello */
for select i_topic_id from t_s_topic where i_forum_id = :i_forum_id
into :i_topic_id
do begin
for select i_post_id from t_s_post
end
delete from t_a_forum where i_forum_id = :i_forum_id;
end ^
-- cancello me e i miei figli (prima loro, dal fondo)
alter procedure forum_delete_rec(i_forum_id integer)
as
declare i_forum_child integer;
begin
for select i_forum_id
from t_a_forum
where i_parent_forum_id = :i_forum_id
order by i_order desc
into :i_forum_child
do
execute procedure forum_delete_rec :i_forum_child;
execute procedure forum_delete :i_forum_id;
end ^
-- cancello i miei figli
create procedure forum_empty(i_forum_id integer)
as
declare i_forum_child integer;
begin
for select i_forum_id
from t_a_forum
where i_parent_forum_id = :i_forum_id
order by i_order desc
into :i_forum_child
do
execute procedure forum_delete_rec :i_forum_child;
end ^
create procedure forum_topic_empty(i_forum_id integer)
as begin
end ^
alter procedure forum_topic_empty(i_forum_id integer)
as
declare i_forum_child integer;
begin
delete from t_s_topic where i_forum_id =
:i_forum_id;
for select i_forum_id
from t_a_forum
where i_parent_forum_id = :i_forum_id
order by i_order desc
into :i_forum_child
do
execute procedure forum_topic_empty :i_forum_child;
end ^
/***************************************************
SEGNA COME LETTO
*/
alter procedure forum_read(i_user_id integer, i_forum_id integer)
as
declare i_last_post_id integer;
declare i_topic_id integer;
begin
for select t.i_topic_id, t.i_last_post_id
from t_s_topic t
inner join t_p_user_topic u on (t.i_topic_id = u.i_topic_id)
where u.b_new = '1'
and u.i_user_id = :i_user_id and t.i_forum_id = :i_forum_id
into :i_topic_id, :i_last_post_id
do
update t_p_user_topic set b_new = '0', i_last_read_post_id = :i_last_post_id
where i_user_id = :i_user_id
and
i_topic_id = :i_topic_id;
end ^
alter procedure topic_read(i_user_id integer, i_topic_id integer)
as
declare i_last_post_id integer;
begin
select i_last_post_id from t_s_topic where
i_topic_id = :i_topic_id into :i_last_post_id;
update t_p_user_topic set b_new = '0', i_last_read_post_id = :i_last_post_id
where i_user_id = :i_user_id
and i_topic_id = :i_topic_id;
end ^
alter procedure user_read_all(i_user_id integer)
as
declare i_last_post_id integer;
declare i_topic_id integer;
begin
for select t.i_topic_id, t.i_last_post_id
from t_s_topic t
inner join t_p_user_topic u on (t.i_topic_id = u.i_topic_id)
where u.b_new = '1' and u.i_user_id = :i_user_id
into :i_topic_id, :i_last_post_id
do
update t_p_user_topic set b_new = '0', i_last_read_post_id = :i_last_post_id
where i_user_id = :i_user_id
and
i_topic_id = :i_topic_id;
end ^
/***************************************************
PM
*/
alter procedure user_pm(i_user_id integer)
returns(i_forum_id integer)
as
declare i_pm_forum integer;
declare i_box integer;
declare o_box integer;
declare i_reg integer;
declare t_login varchar(192);
begin
select t_value from t_e_param where t_param = 'F_PM'
into :i_pm_forum;
select t_value from t_e_param where t_param = 'G_REGS'
into :i_reg;
select t_login from t_a_user where i_user_id = :i_user_id
into :t_login;
-- CASELLA
insert into t_a_forum(i_parent_forum_id, t_name, t_style, b_allow_post)
values(:i_pm_forum, 'Casella di ' || :t_login, 'F_PM', '0');
i_forum_id = gen_id(g_forum, 0);
-- INBOX
insert into t_a_forum(i_parent_forum_id, t_name, t_style)
values(:i_forum_id, 'inbox', 'F_PM');
i_box = gen_id(g_forum, 0);
-- io modero
insert into t_p_user_forum_access(i_forum_id, i_user_id, b_r, b_w, b_rw, b_mod)
values(:i_box, :i_user_id, '1', '1', '1', '1');
-- gli utenti registrati mi scrivono
update t_p_group_forum_access set b_w = '1'
where i_forum_id = :i_box and i_group_id = :i_reg;
-- OUTBOX
insert into t_a_forum(i_parent_forum_id, t_name, t_style)
values(:i_forum_id, 'outbox', 'F_PM');
o_box = gen_id(g_forum, 0);
-- io modero
insert into t_p_user_forum_access(i_forum_id, i_user_id, b_r, b_w, b_rw, b_mod)
values(:o_box, :i_user_id, '1', '1', '1', '1');
suspend;
end ^
create procedure
send_pm(i_user_from_id integer, i_user_to_id integer, t_title varchar(255),
t_description varchar(255), t_post_source blob)
as
declare i_ibox integer;
begin
select i_forum_id
from t_a_forum
where i_parent_forum_id = (select i_pm_forum_id from t_a_user where i_user_id = :i_user_to_id)
and t_name = 'inbox'
into :i_ibox;
-- GUESTBOOK - creazione del topic
alter procedure user_gb(i_user_id integer)
returns(i_topic_id integer)
as
declare i_gb_forum_id integer;
declare t_login varchar(192);
begin
select t_value from t_e_param where t_param = 'F_GB'
into :i_gb_forum_id;
select t_login from t_a_user where i_user_id = :i_user_id
into :t_login;
insert into t_s_topic(i_user_id, t_style, t_title,
i_forum_id)
values(:i_user_id, 'T_GB', 'Guestbook di ' || :t_login, :i_gb_forum_id);
i_topic_id = gen_id(g_topic, 0);
suspend;
end ^
/***************************************************
STATS
*/
create procedure fstat_rebuild_tree(i_forum_id integer)
as begin end ^
alter procedure fstat_rebuild_tree(i_forum_id integer)
as
declare i_forum_child_id integer;
declare i_forum_topic_count integer;
declare i_forum_post_count integer;
declare i_forum_hit_count integer;
declare i_topic_post_count integer;
declare i_topic_hit_count integer;
declare i_topic_count integer;
begin
for select i_forum_id
from t_a_forum
where i_parent_forum_id = :i_forum_id
into :i_forum_child_id
do
execute procedure fstat_rebuild_tree :i_forum_child_id;
-- totale figli
select
case when sum(i_topic_count) is null then 0 else sum(i_topic_count) end,
case when sum(i_post_count) is null then 0 else sum(i_post_count) end,
case when sum(i_hit_count) is null then 0 else sum(i_hit_count) end
from t_a_forum
where i_parent_forum_id = :i_forum_id
into :i_forum_topic_count, :i_forum_post_count, :i_forum_hit_count;
-- totale topic
select
count(*),
case when sum(i_post_count) is null then 0 else sum(i_post_count) end,
case when sum(i_hit_count) is null then 0 else sum(i_hit_count) end
from t_s_topic
where i_forum_id = :i_forum_id
into :i_topic_count, :i_topic_post_count, :i_topic_hit_count;
-- update
update t_a_forum f set
i_last_post_id =
(select tt.i_last_post_id
from t_s_topic tt
where tt.r_insert =
(select max(t.r_insert)
from t_s_topic t
where t.i_forum_id = :i_forum_id
)
)
where f.i_forum_id = :i_forum_id;
update t_a_forum f set
f.r_last_post = (select p.r_insert from t_s_post p where p.i_post_id = f.i_last_post_id)
where f.i_forum_id = :i_forum_id;
update t_a_forum f set
i_topic_count = :i_topic_count + :i_forum_topic_count,
i_post_count =:i_topic_post_count +:i_forum_post_count,
i_hit_count = :i_topic_hit_count + :i_forum_hit_count
where f.i_forum_id = :i_forum_id;
update t_a_forum f set
i_writers =
(select count(distinct p.i_user_id)
from t_s_post p
inner join t_s_topic t on (p.i_topic_id = t.i_topic_id)
where t.i_forum_id = :i_forum_id )
where f.i_forum_id = :i_forum_id;
update t_a_forum f set
i_readers =
(select count(i_user_id)
from forum_user_topic_rights(:i_forum_id) v
where v.b_r = '1' )
where f.i_forum_id = :i_forum_id;
end ^
alter procedure fstat_rebuild
as
declare i_forum_id integer;
begin
update t_s_topic t set
i_post_count =
(select count(*)
from t_s_post p
where t.i_topic_id = p.i_topic_id
);
update t_s_topic t set
i_last_post_id =
(select max(i_post_id)
from t_s_post
where r_insert =
(select max(p.r_insert)
from t_s_post p
where t.i_topic_id = p.i_topic_id
)
);
update t_s_topic t
set t.r_last_post = (select p.r_insert from t_s_post p where p.i_post_id = t.i_last_post_id);
update t_a_user u set
i_post_count = (select count(*) from t_s_post p where p.i_user_id = u.i_user_id);
for select i_forum_id
from t_a_forum
where i_parent_forum_id is null
into :i_forum_id
do
execute procedure fstat_rebuild_tree :i_forum_id;
end ^
/***************************************************
POST
*/
create procedure post_count_inc(i_post_id integer)
as
declare r_insert timestamp;
declare i_topic_id integer;
declare i_forum integer;
begin
select i_topic_id, r_insert from t_s_post where i_post_id= :i_post_idselect i_forum_id from t_s_topic where i_topic_id = :i_topic_id
into :i_forum;
i_post_count = i_post_count + 1,
i_last_post_id = :i_post_id,
r_last_post = :r_insert
where i_topic_id = :i_topic_id;
-- update forum
i_post_count = i_post_count + 1,
i_last_post_id = :i_post_id,
r_last_post = :r_insert
where i_forum_id = :i_forum;
end ^
alter procedure post_page(i_post_id integer) returns (i_page
integer)
as
declare i integer;
declare done integer;
declare ps integer;
begin
i = 0;
done = 0;
for select i_post_id from t_s_post where i_topic_id = (select i_topic_id from t_s_post where i_post_id = :i_post_id)
order by r_insert asc
into :ps
do begin
if (done = 0) then i = i +
1;
if (ps = i_post_id) then done = 1;
endselect t_value from t_e_param
where t_param = 'PS_POST' into :ps;
i_page = CEILING(cast(i as double precision) / cast(ps as double precision));
suspend;
end ^
/***************************************************
TOPIC
*/
alter procedure att_topics(i_user_id integer, i_forum_id integer)
returns (i_topic_id integer, i_post_id integer)
as begin
for select t.i_topic_id, p.i_post_id
from t_p_user_forum_access uf
inner join t_s_topic t on (uf.i_forum_id = t.i_forum_id)
inner join t_s_post p on (t.i_topic_id = p.i_topic_id)
where uf.i_user_id = :i_user_id
and uf.b_mod = '1'
and p.b_mod_att = '1'
and (:i_forum_id is null or uf.i_forum_id = :i_forum_id)
into :i_topic_id, :i_post_id
do suspend;
end ^
/***************************************************
DIRITTI
*/
-- DALLA PARTE DELL'UTENTE
-- diritti di gruppo sui forum / sul singolo
alter procedure user_group_forum_rights(i_user_in_id integer)
returns(i_user_id integer, i_parent_forum_id integer, i_forum_id integer, i_order integer, t_name varchar(192), b_r char(1), b_w char(1), b_rw char(1), b_custom char(1), t_tipo char(1))
AS BEGIN
i_user_id = :i_user_in_id;
for select f.i_parent_forum_id, s.i_forum_id, f.i_order, f.t_name,
case when sum(cast(s.b_r as integer)) > 0 then '1' else '0' end AS b_r,
case when sum(cast(s.b_w as integer)) > 0 and sum(cast(f.b_allow_post as integer)) > 0 then '1' else '0' end AS b_w,
case when sum(cast(s.b_rw as integer)) > 0 and sum(cast(f.b_allow_post as integer)) > 0 then '1' else '0' end AS b_rw,
case when f.t_custom_home is null then '0' else '1' end,
'G' as t_tipo
from t_p_group_forum_access S
inner join t_p_user_group P on (s.i_group_id = p.i_group_id)
inner join t_a_forum F on (s.i_forum_id = f.i_forum_id)
where p.i_user_id = :i_user_in_id
group by
f.i_parent_forum_id, s.i_forum_id, f.i_order, f.t_name, f.t_custom_home
into :i_parent_forum_id, :i_forum_id, :i_order, :t_name, :b_r, :b_w, :b_rw, :b_custom, :t_tipo
do suspend;
for select f.i_parent_forum_id, f.i_forum_id, f.i_order,
f.t_name,
'0', '0', '0', case when f.t_custom_home is null then '0' else '1' end, 'G'
from t_a_user u, t_a_forum f
where
u.i_user_id = :i_user_in_id and
u.i_user_id not in (select i_user_id from t_p_user_group)
into :i_parent_forum_id, :i_forum_id, :i_order, :t_name, :b_r, :b_w, :b_rw, :b_custom, :t_tipo
do suspend;
end ^
alter procedure user_group_forum_rights_f(i_user_in_id integer, i_forum_in_id
integer)
returns(i_user_id integer, i_parent_forum_id integer, i_forum_id integer, i_order integer, t_name varchar(192), b_r char(1), b_w char(1), b_rw char(1), b_custom char(1), t_tipo char(1))
AS begin
i_user_id = :i_user_in_id;
i_forum_id = :i_forum_in_id;
for select f.i_parent_forum_id, f.i_order, f.t_name,
case when sum(cast(s.b_r as integer)) > 0 then '1' else '0' end AS b_r,
case when sum(cast(s.b_w as integer)) > 0 and sum(cast(f.b_allow_post as integer)) > 0 then '1' else '0' end AS b_w,
case when sum(cast(s.b_rw as integer)) > 0 and sum(cast(f.b_allow_post as integer)) > 0 then '1' else '0' end AS b_rw,
case when f.t_custom_home is null then '0' else '1' end,
'G' as t_tipo
from t_p_group_forum_access S
inner join t_p_user_group P on (s.i_group_id = p.i_group_id)
inner join t_a_forum F on (s.i_forum_id = f.i_forum_id)
where p.i_user_id = :i_user_in_id and s.i_forum_id = :i_forum_in_id
group by f.i_parent_forum_id, f.i_order, f.t_name, f.t_custom_home
into :i_parent_forum_id, :i_order, :t_name, :b_r, :b_w, :b_rw, :b_custom, :t_tipo
do suspend;
for select f.i_parent_forum_id, f.i_order, f.t_name,
'0', '0', '0', case when f.t_custom_home is null then '0' else '1' end, 'G'
from t_a_user u, t_a_forum f
where
u.i_user_id = :i_user_in_id and
f.i_forum_id = :i_forum_in_id and
u.i_user_id not in (select i_user_id from t_p_user_group)
into :i_parent_forum_id, :i_order, :t_name, :b_r, :b_w, :b_rw, :b_custom, :t_tipo
do suspend;
end ^
-- diritti utente sui forum / sul singolo
alter procedure user_forum_rights(i_user_in_id integer)
returns(i_user_id integer, i_parent_forum_id integer, i_forum_id integer, i_order integer, t_name varchar(192), b_r char(1), b_w char(1), b_rw char(1), b_mod char(1), b_custom char(1), t_tipo char(1))
AS begin
i_user_id = :i_user_in_id;
for select g.i_parent_forum_id, g.i_forum_id, g.i_order, g.t_name,
case when u.b_r is null then g.b_r else u.b_r end as b_r,
case when u.b_w is null then g.b_w else u.b_w end as b_w,
case when u.b_rw is null then g.b_rw else u.b_rw end as b_rw,
case when u.b_mod is null then '0' else u.b_mod end as b_mod,
g.b_custom,
case when u.i_user_id is null then g.t_tipo else 'U' end as t_tipo
from user_group_forum_rights(:i_user_in_id) G
left join t_p_user_forum_access U on (g.i_forum_id = u.i_forum_id and u.i_user_id = g.i_user_id)
into :i_parent_forum_id, :i_forum_id, :i_order, :t_name, :b_r, :b_w, :b_rw, :b_mod, :b_custom, :t_tipo
do suspend;
end ^
alter procedure user_forum_rights_f(i_user_in_id integer, i_forum_in_id
integer)
returns(i_user_id integer, i_parent_forum_id integer, i_forum_id integer, i_order integer, t_name varchar(192), b_r char(1), b_w char(1), b_rw char(1), b_mod char(1), b_custom char(1), t_tipo char(1))
AS begin
i_user_id = :i_user_in_id;
i_forum_id = :i_forum_in_id;
for select g.i_parent_forum_id, g.i_order, g.t_name,
case when u.b_r is null then g.b_r else u.b_r end as b_r,
case when u.b_w is null then g.b_w else u.b_w end as b_w,
case when u.b_rw is null then g.b_rw else u.b_rw end as b_rw,
case when u.b_mod is null then '0' else u.b_mod end as b_mod,
g.b_custom,
case when u.i_user_id is null then g.t_tipo else 'U' end as t_tipo
from user_group_forum_rights_f(:i_user_in_id, :i_forum_in_id) G
left join t_p_user_forum_access U on (u.i_forum_id = g.i_forum_id and u.i_user_id = g.i_user_id)
into :i_parent_forum_id, :i_order, :t_name, :b_r, :b_w, :b_rw, :b_mod, :b_custom, :t_tipo
do suspend;
end ^
-- diritti utente sui forum/topic per ogni forum / singolo forum
alter procedure user_forum_topic_rights(i_user_in_id integer)
returns(i_user_id integer, i_parent_forum_id integer, i_forum_id integer, i_order integer, t_name varchar(192), t_description blob, b_can_join char(1), b_joined char(1), b_r char(1), b_w char(1), b_rw char(1), b_mod char(1), b_custom char(1), t_tipo char(1))
as begin
i_user_id = :i_user_in_id;
for select f.i_parent_forum_id, f.i_forum_id, f.i_order, f.t_name, f.t_description, f.b_can_join,
case when f.b_can_join = '1' and j.i_forum_id is not null then '1' else '0' end as b_joined,
case when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod else u.b_r end as b_r,
case when f.b_allow_post = '0' then u.b_mod when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod else u.b_w end as b_w,
case when f.b_allow_post = '0' then u.b_mod when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod else u.b_rw end as b_rw,
u.b_mod ,
u.b_custom,
'F' as t_tipo
from user_forum_rights(:i_user_in_id) u
inner join t_a_forum f on (u.i_forum_id = f.i_forum_id)
left join t_p_user_forum_join j on (f.i_forum_id = j.i_forum_id and j.i_user_id = u.i_user_id)
into :i_parent_forum_id, :i_forum_id, :i_order, :t_name, :t_description, :b_can_join, :b_joined, :b_r, :b_w, :b_rw, :b_mod, :b_custom, :t_tipo
do suspend;
end ^
alter procedure user_forum_topic_rights_f(i_user_in_id integer, i_forum_in_id
integer)
returns(i_user_id integer, i_parent_forum_id integer, i_forum_id integer, i_order integer, t_name varchar(192), t_description blob, b_can_join char(1), b_joined char(1), b_r char(1), b_w char(1), b_rw char(1), b_mod char(1), b_custom char(1), t_tipo char(1))
as begin
i_user_id = :i_user_in_id;
i_forum_id = :i_forum_in_id;
for select f.i_parent_forum_id, f.i_order, f.t_name, f.t_description, f.b_can_join,
case when f.b_can_join = '1' and j.i_forum_id is not null then '1' else '0' end as b_joined,
case when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod else u.b_r end as b_r,
case when f.b_allow_post = '0' then u.b_mod when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod else u.b_w end as b_w,
case when f.b_allow_post = '0' then u.b_mod when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod else u.b_rw end as b_rw,
u.b_mod ,
u.b_custom,
'F' as t_tipo
from user_forum_rights_f(:i_user_in_id, :i_forum_in_id) u
inner join t_a_forum f on (f.i_forum_id = u.i_forum_id)
left join t_p_user_forum_join j on (j.i_forum_id = u.i_forum_id and j.i_user_id = u.i_user_id)
into :i_parent_forum_id, :i_order, :t_name, :t_description, :b_can_join, :b_joined, :b_r, :b_w, :b_rw, :b_mod, :b_custom, :t_tipo
do suspend;
end ^
-- diritti utenti sui topic per ogni topic / singolo topic
alter procedure user_topic_rights(i_user_in_id integer)
returns(i_user_id integer, i_topic_id integer, i_forum_id integer, b_r char(1), b_rw char(1), b_mod char(1), t_tipo char(1))
as begin
i_user_id = :i_user_in_id;
for select t.i_topic_id, t.i_forum_id,
case when ut.b_r is null then case when uf.b_mod = '1' or t.b_r = '1' then uf.b_r else t.b_r end else ut.b_r end as b_r,
case when ut.b_rw is null then case when uf.b_mod = '1' or t.b_rw = '1' then uf.b_rw else t.b_rw end else ut.b_rw end as b_rw,
case when ut.b_mod is null then uf.b_mod else ut.b_mod end as b_mod,
case when ut.i_user_topic_access_id is null then uf.t_tipo else 'U' end as t_tipo
from user_forum_topic_rights(:i_user_in_id) uf
inner join t_s_topic t on(t.i_forum_id = uf.i_forum_id)
left join t_p_user_topic_access ut on (t.i_topic_id = ut.i_topic_id and ut.i_user_id = uf.i_user_id)
into :i_topic_id, :i_forum_id, :b_r, :b_rw, :b_mod, :t_tipo
do suspend;
end ^
alter procedure user_topic_rights_t(i_user_in_id integer, i_topic_in_id
integer)
returns(i_user_id integer, i_topic_id integer, i_forum_id integer, b_r char(1), b_rw char(1), b_mod char(1), t_tipo char(1))
as begin
i_user_id = :i_user_in_id;
i_topic_id = :i_topic_in_id;
select i_forum_id from t_s_topic where i_topic_id =
:i_topic_in_id into :i_forum_id;
for select
case when ut.b_r is null then case when uf.b_mod = '1' or t.b_r = '1' then uf.b_r else t.b_r end else ut.b_r end as b_r,
case when ut.b_rw is null then case when uf.b_mod = '1' or t.b_rw = '1' then uf.b_rw else t.b_rw end else ut.b_rw end as b_rw,
case when ut.b_mod is null then uf.b_mod else ut.b_mod end as b_mod,
case when ut.i_user_topic_access_id is null then uf.t_tipo else 'U' end as t_tipo
from user_forum_topic_rights_f(:i_user_in_id, :i_forum_id) uf
cross join t_s_topic t
left join t_p_user_topic_access ut on (ut.i_topic_id = t.i_topic_id and ut.i_user_id = uf.i_user_id)
where t.i_topic_id = :i_topic_in_id
into :b_r, :b_rw, :b_mod, :t_tipo
do suspend;
end ^
-- sui topic del forum
alter procedure user_topic_rights_f(i_user_in_id integer, i_forum_in_id integer)
returns(i_user_id integer, i_topic_id integer, i_forum_id integer, b_r char(1), b_rw char(1), b_mod char(1), t_tipo char(1))
as begin
i_user_id = :i_user_in_id;
i_forum_id = :i_forum_in_id;
for select t.i_topic_id,
case when ut.b_r is null then case when uf.b_mod = '1' or t.b_r = '1' then uf.b_r else t.b_r end else ut.b_r end as b_r,
case when ut.b_rw is null then case when uf.b_mod = '1' or t.b_rw = '1' then uf.b_rw else t.b_rw end else ut.b_rw end as b_rw,
case when ut.b_mod is null then uf.b_mod else ut.b_mod end as b_mod,
case when ut.i_user_topic_access_id is null then uf.t_tipo else 'U' end as t_tipo
from user_forum_topic_rights_f(:i_user_in_id, :i_forum_in_id) uf
cross join t_s_topic t
left join t_p_user_topic_access ut on (ut.i_topic_id = t.i_topic_id and ut.i_user_id = uf.i_user_id)
where t.i_forum_id = :i_forum_in_id
into :i_topic_id, :b_r, :b_rw, :b_mod, :t_tipo
do suspend;
end ^
-- dal punto di vista del topic
alter procedure topic_user_rights(i_topic_in_id integer)
returns(i_user_id integer, t_login varchar(192), i_topic_id integer, i_forum_id integer, b_r char(1), b_rw char(1), b_mod char(1), t_tipo char(1))
as begin
i_topic_id = :i_topic_in_id;
select i_forum_id from t_s_topic where i_topic_id =
:i_topic_in_id into :i_forum_id;
for select uf.i_user_id, uf.t_login,
case when ut.b_r is null then case when uf.b_mod = '1' or t.b_r = '1' then uf.b_r else t.b_r end else ut.b_r end as b_r,
case when ut.b_rw is null then case when uf.b_mod = '1' or t.b_rw = '1' then uf.b_rw else t.b_rw end else ut.b_rw end as b_rw,
case when ut.b_mod is null then uf.b_mod else ut.b_mod end as b_mod,
case when ut.i_user_topic_access_id is null then uf.t_tipo else 'U' end as t_tipo
from forum_user_topic_rights(:i_forum_id) uf
cross join t_s_topic t
left join t_p_user_topic_access ut on (ut.i_topic_id = :i_topic_id and ut.i_user_id = uf.i_user_id)
where t.i_topic_id = :i_topic_in_id
into :i_user_id, :t_login, :b_r, :b_rw, :b_mod, :t_tipo
do suspend;
end ^
-- questa la uso per visualizzare i diritti topic nella schermata di moderazione
alter procedure topic_rights(i_topic_in_id integer)
returns(i_user_id integer, i_topic_id integer, i_forum_id integer, b_r char(1), b_rw char(1), b_mod char(1), t_tipo char(1), t_login varchar(192))
as begin
i_topic_id = :i_topic_in_id;
select i_forum_id from t_s_topic where i_topic_id =
:i_topic_in_id into :i_forum_id;
for select uf.i_user_id, uf.t_login,
case when ut.b_r is null then case when uf.b_mod = '1' or t.b_r = '1' then uf.b_r else t.b_r end else ut.b_r end as b_r,
case when ut.b_rw is null then case when uf.b_mod = '1' or t.b_rw = '1' then uf.b_rw else t.b_rw end else ut.b_rw end as b_rw,
case when ut.b_mod is null then uf.b_mod else ut.b_mod end as b_mod,
case when ut.i_user_topic_access_id is null then uf.t_tipo else 'U' end as t_tipo
from topic_user_rights(:i_topic_in_id) uf
inner join t_s_topic t
on (uf.i_topic_id = t.i_topic_id)
left join t_p_user_topic_access ut on (ut.i_topic_id = t.i_topic_id and ut.i_user_id = uf.i_user_id)
where uf.i_user_id not in (select t_value from t_e_param where t_param in ('U_GOD', 'U_GUEST'))
into :i_user_id, :t_login, :b_r, :b_rw, :b_mod, :t_tipo
do suspend;
end ^
-- DALLA PARTE DEL FORUM
-- diritti di gruppo sul forum dato il forum
alter procedure forum_user_group_rights(i_forum_in_id integer)
returns(i_user_id integer, i_parent_forum_id integer, i_forum_id integer, i_order integer, t_name varchar(192), b_r char(1), b_w char(1), b_rw char(1), b_custom char(1), t_tipo char(1))
AS begin
i_forum_id = :i_forum_in_id;
for select p.i_user_id, f.i_parent_forum_id, f.i_order, f.t_name,
case when sum(cast(s.b_r as integer)) > 0 then '1' else '0' end AS b_r,
case when sum(cast(s.b_w as integer)) > 0 and sum(cast(f.b_allow_post as integer)) > 0 then '1' else '0' end AS b_w,
case when sum(cast(s.b_rw as integer)) > 0 and sum(cast(f.b_allow_post as integer)) > 0 then '1' else '0' end AS b_rw,
case when f.t_custom_home is null then '0' else '1' end,
'G' as t_tipo
from t_a_forum f
inner join t_p_group_forum_access S on (s.i_forum_id = f.i_forum_id)
inner join t_p_user_group P on (s.i_group_id = p.i_group_id)
where f.i_forum_id = :i_forum_in_id
group by p.i_user_id, f.i_parent_forum_id, f.i_order, f.t_name, f.t_custom_home
into :i_user_id, :i_parent_forum_id, :i_order, :t_name, :b_r, :b_w, :b_rw, :b_custom, :t_tipo
do suspend;
for select u.i_user_id, f.i_parent_forum_id, f.i_order,
f.t_name,
'0', '0', '0', case when f.t_custom_home is null then '0' else '1' end, 'G'
from t_a_user u, t_a_forum f
where
f.i_forum_id = :i_forum_in_id
and u.i_user_id not in (select i_user_id from t_p_user_group)
into :i_user_id, :i_parent_forum_id, :i_order, :t_name, :b_r, :b_w, :b_rw, :b_custom, :t_tipo
do suspend;
end ^
create procedure forum_group_rights(i_forum_in_id integer)
returns(i_group_id integer, i_parent_forum_id integer, i_forum_id integer, i_order integer, t_name varchar(192), b_r char(1), b_w char(1), b_rw char(1), b_custom char(1), t_tipo char(1))
AS begin
i_forum_id = :i_forum_in_id;
for select s.i_group_id, f.i_parent_forum_id, f.i_order, f.t_name,
case when sum(cast(s.b_r as integer)) > 0 then '1' else '0' end AS b_r,
case when sum(cast(s.b_w as integer)) > 0 and sum(cast(f.b_allow_post as integer)) > 0 then '1' else '0' end AS b_w,
case when sum(cast(s.b_rw as integer)) > 0 and sum(cast(f.b_allow_post as integer)) > 0 then '1' else '0' end AS b_rw,
case when f.t_custom_home is null then '0' else '1' end,
'G' as t_tipo
from t_a_forum f
inner join t_p_group_forum_access S on (s.i_forum_id = f.i_forum_id)
where f.i_forum_id = :i_forum_in_id
group by s.i_group_id, f.i_parent_forum_id, f.i_order, f.t_name, f.t_custom_home
into :i_group_id, :i_parent_forum_id, :i_order, :t_name, :b_r, :b_w, :b_rw, :b_custom, :t_tipo
do suspend;
end ^
-- diritti utente sul forum dato il forum
alter procedure forum_user_rights(i_forum_in_id integer)
returns(i_user_id integer, t_login varchar(192), i_parent_forum_id integer, i_forum_id integer, i_order integer, t_name varchar(192), b_r char(1), b_w char(1), b_rw char(1), b_mod char(1), b_custom char(1), t_tipo char(1))
AS begin
i_forum_id = :i_forum_in_id;
for select g.i_user_id, us.t_login, g.i_parent_forum_id, g.i_order, g.t_name,
case when u.b_r is null then g.b_r else u.b_r end as b_r,
case when u.b_w is null then g.b_w else u.b_w end as b_w,
case when u.b_rw is null then g.b_rw else u.b_rw end as b_rw,
case when u.b_mod is null then '0' else u.b_mod end as b_mod,
g.b_custom,
case when u.i_user_id is null then g.t_tipo else 'U' end as t_tipo
from forum_user_group_rights(:i_forum_in_id) G
inner join t_a_user us on (g.i_user_id = us.i_user_id)
left join t_p_user_forum_access U on (g.i_forum_id = u.i_forum_id and u.i_user_id = g.i_user_id)
into :i_user_id, :t_login, :i_parent_forum_id, :i_order, :t_name, :b_r, :b_w, :b_rw, :b_mod, :b_custom, :t_tipo
do suspend;
end ^
-- diritti utente sui topic dato il forum
alter procedure forum_user_topic_rights(i_forum_in_id integer)
returns(i_user_id integer, t_login varchar(192), i_parent_forum_id integer, i_forum_id integer, i_order integer, t_name varchar(192), t_description blob, b_can_join char(1), b_joined char(1), b_r char(1), b_w char(1), b_rw char(1), b_mod char(1), b_custom char(1), t_tipo char(1))
as begin
i_forum_id = :i_forum_in_id;
for select u.i_user_id, u.t_login, f.i_parent_forum_id, f.i_order, f.t_name, f.t_description, f.b_can_join,
case when f.b_can_join = '1' and j.i_forum_id is not null then '1' else '0' end as b_joined,
case when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod else u.b_r end as b_r,
case when f.b_allow_post = '0' then u.b_mod when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod else u.b_w end as b_w,
case when f.b_allow_post = '0' then u.b_mod when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod else u.b_rw end as b_rw,
u.b_mod ,
u.b_custom,
'F' as t_tipo
from forum_user_rights(:i_forum_in_id) u
inner join t_a_forum f on (u.i_forum_id = f.i_forum_id)
left join t_p_user_forum_join j on (f.i_forum_id = j.i_forum_id and j.i_user_id = u.i_user_id)
into :i_user_id, :t_login, :i_parent_forum_id, :i_order, :t_name, :t_description, :b_can_join, :b_joined, :b_r, :b_w, :b_rw, :b_mod, :b_custom, :t_tipo
do suspend;
end ^
-- elenco moderatori del forum
alter procedure forum_mods(i_forum_id integer)
returns(i_user_id integer, t_login varchar(192))
AS begin
for select u.i_user_id, u.t_login
from t_p_user_forum_access uf
inner join t_a_user u on (uf.i_user_id = u.i_user_id)
where u.i_user_id != (select t_value from t_e_param where t_param = 'U_GOD')
and uf.b_mod = '1'
and uf.i_forum_id = :i_forum_id
into :i_user_id, :t_login
do suspend;
end ^
create procedure forum_is_pm(i_forum_id integer)
returns (b_pm char(1))
as
declare i_parent_forum_id integer;
declare i_pm integer;
begin
select i_parent_forum_id from t_a_forum where i_forum_id = :i_forum_id into :i_parent_forum_id;
if (:i_parent_forum_id is null) then
b_pm = '0';
else begin
i_forum_id = :i_parent_forum_id;select i_parent_forum_id from t_a_forum where i_forum_id = :i_forum_id into :i_parent_forum_id;
select t_value from t_e_param where t_param = 'F_PM' into :i_pm;
if (:i_parent_forum_id = :i_pm) then
b_pm = '1';
else
b_pm = '0';
create procedure user_forum_mods(i_user_id integer)
returns(i_forum_id integer, t_name varchar(4096))
AS
begin
for select f.i_forum_id
from t_p_user_forum_access uf
inner join t_a_forum f
on (f.i_forum_id = uf.i_forum_id)
where uf.b_mod = '1'
and uf.i_user_id = :i_user_id
into :i_forum_id
do begin
if (not exists(select * from
forum_is_pm(:i_forum_id) where b_pm = '1')) then begin
select t_url
from forum_link(:i_forum_id, null, null, null) into :t_name;
suspend;
end
end
end ^
alter procedure moderators
returns (i_user_id integer, i_avatar_file_id integer, t_login varchar(192))
as
begin
for select u.i_user_id, u.i_avatar_file_id, u.t_login
from t_a_user u
inner join t_e_param p on (p.t_param = 'U_GOD' and u.i_user_id !=p.t_value)
where exists(select 1 from
user_forum_mods(u.i_user_id))
into :i_user_id, :i_avatar_file_id, :t_login
do suspend;
-- stili validi per aprire topic dato l'utente
alter procedure user_topic_style(i_user_in_id integer)
returns(i_forum_id integer, i_user_id integer, t_style char(17), t_style_type char(5), t_class varchar(15))
as begin
i_user_id = :i_user_in_id;
for select uf.i_forum_id, fs.t_topic_style, s.t_style_type,
s.t_class
from user_forum_topic_rights(:i_user_in_id) uf
inner join t_a_forum f on (uf.i_forum_id = f.i_forum_id)
inner join t_p_forum_topic_style fs on (fs.t_forum_style = f.t_style)
inner join t_e_style s on (fs.t_topic_style = s.t_style)
where
(fs.b_mod = '1' and uf.b_mod = '1')
or
(fs.b_w = '1' and uf.b_w = '1')
into :i_forum_id, :t_style, :t_style_type, :t_class
do suspend;
end ^
alter procedure user_topic_style_f(i_user_in_id integer, i_forum_in_id
integer)
returns(i_forum_id integer, i_user_id integer, t_style char(17), t_style_type char(5), t_class varchar(15))
as begin
i_user_id = :i_user_in_id;
i_forum_id = :i_forum_in_id;
for select fs.t_topic_style, s.t_style_type, s.t_class
from user_forum_topic_rights_f(:i_user_in_id, :i_forum_in_id) uf
inner join t_a_forum f on (uf.i_forum_id = f.i_forum_id)
inner join t_p_forum_topic_style fs on (fs.t_forum_style = f.t_style)
inner join t_e_style s on (fs.t_topic_style = s.t_style)
where
(fs.b_mod = '1' and uf.b_mod = '1')
or
(fs.b_w = '1' and uf.b_w = '1')
into :t_style, :t_style_type, :t_class
do suspend;
end ^
-- stili che il mod puo' usare per cambiare lo stile del singolo
post
alter procedure mod_post_style(i_user_in_id integer)
returns(i_forum_id integer, i_user_id integer, t_style char(17), t_style_type char(5), t_class varchar(15))
as begin
i_user_id = :i_user_in_id;
for select distinct uf.i_forum_id, st.t_post_style, sp.t_style_type, sp.t_class
from user_forum_topic_rights(:i_user_in_id) uf
inner join t_a_forum f on (uf.i_forum_id = f.i_forum_id)
inner join t_p_forum_topic_style fs on (fs.t_forum_style = f.t_style)
inner join t_e_style st on (fs.t_topic_style = st.t_style)
inner join t_e_style sp on (st.t_post_style = sp.t_style)
where uf.b_mod = '1' or uf.b_w = '1'
into :i_forum_id, :t_style, :t_style_type, :t_class
do suspend;
end ^
alter procedure mod_post_style_f(i_user_in_id integer, i_forum_in_id
integer)
returns(i_forum_id integer, i_user_id integer, t_style char(17), t_style_type char(5), t_class varchar(15))
as begin
i_user_id = :i_user_in_id;
i_forum_id = :i_forum_in_id;
for select distinct st.t_post_style, sp.t_style_type, sp.t_class
from user_forum_topic_rights_f(:i_user_in_id, :i_forum_in_id) uf
inner join t_a_forum f on (uf.i_forum_id = f.i_forum_id)
inner join t_p_forum_topic_style fs on (fs.t_forum_style = f.t_style)
inner join t_e_style st on (fs.t_topic_style = st.t_style)
inner join t_e_style sp on (st.t_post_style = sp.t_style)
where uf.b_mod = '1' or uf.b_w = '1'
into :t_style, :t_style_type, :t_class
do suspend;
end ^
create procedure user_post_style_f(i_user_in_id integer, i_forum_in_id integer)
returns(i_forum_id integer, i_user_id integer, t_style char(17), t_style_type char(5), t_class varchar(15))
as begin
i_user_id = :i_user_in_id;
i_forum_id = :i_forum_in_id;
for select distinct st.t_post_style, sp.t_style_type, sp.t_class
from user_forum_topic_rights_f(:i_user_in_id, :i_forum_in_id) uf
inner join t_a_forum f on (uf.i_forum_id = f.i_forum_id)
inner join t_p_forum_topic_style fs on (fs.t_forum_style = f.t_style)
inner join t_e_style st on (fs.t_topic_style = st.t_style)
inner join t_e_style sp on (st.t_post_style = sp.t_style)
where uf.b_w = '1'
into :t_style, :t_style_type, :t_class
do suspend;
end ^
/***************************************************
PERCORSI VISIBILI (MODERAZIONE)
*/
create procedure v_user_cloud_clan_base(i_group_id integer)
returns(i_user_id integer, t_login varchar(192), i_post_count
integer, b_mod char(1))
as begin
for select u.i_user_id, u.t_login, count(*), mn.b_mod
from t_a_user u
inner join t_p_user_group mn on (u.i_user_id = mn.i_user_id)
inner join t_p_group_forum_access s on (s.i_group_id = mn.i_group_id)
inner join t_s_topic t on (s.i_forum_id = t.i_forum_id)
inner join
t_s_post p on (t.i_topic_id = p.i_topic_id and p.i_user_id = u.i_user_id)
where mn.i_group_id = :i_group_id
and s.b_r = '1'
group by u.i_user_id, u.t_login,
mn.b_mod
into :i_user_id, :t_login,
:i_post_count, :b_mod
do suspend;
end ^
alter procedure v_user_cloud_clan(i_group_id integer)
returns(i_user_id integer, t_login varchar(192), i_post_count
integer, b_mod char(1), i_rank integer)
as
declare f_post_max double precision;
begin
select cast(max(i_post_count) as double precision) from v_user_cloud_clan_base(:i_group_id) into :f_post_max;
for select u.i_user_id, u.t_login, u.i_post_count, u.b_mod,
cast(ceiling(10 * (cast(i_post_count as double precision) / :f_post_max)) as integer)
from
v_user_cloud_clan_base(:i_group_id) u
into :i_user_id, :t_login,
:i_post_count, :b_mod, :i_rank
do suspend;
end ^