-- Internationalization of publication and archive status. Code to generate -- a human readable publish status has been moved from the news__status plsql -- function to a Tcl proc. -- -- @author Peter Marklund --- **** Recreate package to get the new status function. create or replace package news as function new ( item_id in cr_items.item_id%TYPE default null, -- locale in cr_items.locale%TYPE default null, -- publish_date in cr_revisions.publish_date%TYPE default null, text in varchar2 default null, nls_language in cr_revisions.nls_language%TYPE default null, title in cr_revisions.title%TYPE default null, mime_type in cr_revisions.mime_type%TYPE default 'text/plain', -- package_id in cr_news.package_id%TYPE default null, archive_date in cr_news.archive_date%TYPE default null, approval_user in cr_news.approval_user%TYPE default null, approval_date in cr_news.approval_date%TYPE default null, approval_ip in cr_news.approval_ip%TYPE default null, -- relation_tag in cr_child_rels.relation_tag%TYPE default null, -- item_subtype in acs_object_types.object_type%TYPE default 'content_revision', content_type in acs_object_types.object_type%TYPE default 'news', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_ip in acs_objects.creation_ip%TYPE default null, creation_user in acs_objects.creation_user%TYPE default null, -- is_live_p in varchar2 default 'f' ) return cr_news.news_id%TYPE; procedure del ( item_id in cr_items.item_id%TYPE ); procedure archive ( item_id in cr_items.item_id%TYPE, archive_date in cr_news.archive_date%TYPE default sysdate ); procedure make_permanent ( item_id in cr_items.item_id%TYPE ); procedure set_approve ( revision_id in cr_revisions.revision_id%TYPE, approve_p in varchar2 default 't', publish_date in cr_revisions.publish_date%TYPE default null, archive_date in cr_news.archive_date%TYPE default null, approval_user in cr_news.approval_user%TYPE default null, approval_date in cr_news.approval_date%TYPE default sysdate, approval_ip in cr_news.approval_ip%TYPE default null, live_revision_p in varchar2 default 't' ); function status ( publish_date in cr_revisions.publish_date%TYPE, archive_date in cr_news.archive_date%TYPE ) return varchar2; function name ( news_id in cr_news.news_id%TYPE ) return varchar2; -- -- API for revisions: e.g. when the news admin wants to revise a news item -- function revision_new ( item_id in cr_items.item_id%TYPE, -- publish_date in cr_revisions.publish_date%TYPE default null, text in varchar2 default null, title in cr_revisions.title%TYPE, -- -- here goes the revision log description in cr_revisions.description%TYPE, -- mime_type in cr_revisions.mime_type%TYPE default 'text/plain', package_id in cr_news.package_id%TYPE default null, archive_date in cr_news.archive_date%TYPE default null, approval_user in cr_news.approval_user%TYPE default null, approval_date in cr_news.approval_date%TYPE default null, approval_ip in cr_news.approval_ip%TYPE default null, -- creation_date in acs_objects.creation_date%TYPE default sysdate, creation_ip in acs_objects.creation_ip%TYPE default null, creation_user in acs_objects.creation_user%TYPE default null, -- make_active_revision_p in varchar2 default 'f' ) return cr_revisions.revision_id%TYPE; procedure revision_delete ( revision_id in cr_revisions.revision_id%TYPE ); procedure revision_set_active ( revision_id in cr_revisions.revision_id%TYPE ); procedure clone ( new_package_id in cr_news.package_id%TYPE default null, old_package_id in cr_news.package_id%TYPE default null ); end news; / show errors create or replace package body news as function new ( item_id in cr_items.item_id%TYPE default null, -- locale in cr_items.locale%TYPE default null, -- publish_date in cr_revisions.publish_date%TYPE default null, text in varchar2 default null, nls_language in cr_revisions.nls_language%TYPE default null, title in cr_revisions.title%TYPE default null, mime_type in cr_revisions.mime_type%TYPE default 'text/plain', -- package_id in cr_news.package_id%TYPE default null, archive_date in cr_news.archive_date%TYPE default null, approval_user in cr_news.approval_user%TYPE default null, approval_date in cr_news.approval_date%TYPE default null, approval_ip in cr_news.approval_ip%TYPE default null, -- relation_tag in cr_child_rels.relation_tag%TYPE default null, -- item_subtype in acs_object_types.object_type%TYPE default 'content_revision', content_type in acs_object_types.object_type%TYPE default 'news', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_ip in acs_objects.creation_ip%TYPE default null, creation_user in acs_objects.creation_user%TYPE default null, -- is_live_p in varchar2 default 'f' ) return cr_news.news_id%TYPE is v_news_id integer; v_item_id integer; v_id integer; v_revision_id integer; v_parent_id integer; v_name varchar2(200); v_log_string varchar2(400); begin select content_item.get_id('news') into v_parent_id from dual; -- -- this will be used for 2xClick protection if item_id is null then select acs_object_id_seq.nextval into v_id from dual; else v_id := item_id; end if; -- select 'news' || to_char(sysdate,'YYYYMMDD') || v_id into v_name from dual; -- v_log_string := 'initial submission'; -- v_item_id := content_item.new( item_id => v_id, name => v_name, parent_id => v_parent_id, context_id => package_id, locale => locale, item_subtype => item_subtype, content_type => content_type, mime_type => mime_type, nls_language => nls_language, relation_tag => relation_tag, creation_date => creation_date, creation_ip => creation_ip, creation_user => creation_user ); v_revision_id := content_revision.new( title => title, description => v_log_string, publish_date => publish_date, mime_type => mime_type, nls_language => nls_language, text => text, item_id => v_item_id, creation_date => creation_date, creation_ip => creation_ip, creation_user => creation_user ); insert into cr_news (news_id, package_id, archive_date, approval_user, approval_date, approval_ip) values (v_revision_id, package_id, archive_date, approval_user, approval_date, approval_ip); -- make this revision live when immediately approved if is_live_p = 't' then update cr_items set live_revision = v_revision_id, publish_status = 'ready' where item_id = v_item_id; end if; v_news_id := v_revision_id; return v_news_id; end new; -- deletes a news item along with all its revisions and possibnle attachements procedure del ( item_id in cr_items.item_id%TYPE ) is v_item_id cr_items.item_id%TYPE; cursor comment_cursor IS select message_id from acs_messages am, acs_objects ao where am.message_id = ao.object_id and ao.context_id = v_item_id; begin v_item_id := news.del.item_id; dbms_output.put_line('Deleting associated comments...'); -- delete acs_messages, images, comments to news item for v_cm in comment_cursor loop -- images delete from images where image_id in (select latest_revision from cr_items where parent_id = v_cm.message_id); acs_message.del(v_cm.message_id); delete from general_comments where comment_id = v_cm.message_id; end loop; delete from cr_news where news_id in (select revision_id from cr_revisions where item_id = v_item_id); content_item.del(v_item_id); end del; -- (re)-publish a news item out of the archive by nulling the archive_date -- this only applies to the currently active revision procedure make_permanent ( item_id in cr_items.item_id%TYPE ) is begin update cr_news set archive_date = null where news_id = content_item.get_live_revision(news.make_permanent.item_id); end make_permanent; -- archive a news item -- this only applies to the currently active revision procedure archive ( item_id in cr_items.item_id%TYPE, archive_date in cr_news.archive_date%TYPE default sysdate ) is begin update cr_news set archive_date = news.archive.archive_date where news_id = content_item.get_live_revision(news.archive.item_id); end archive; -- approve/unapprove a specific revision -- approving a revision makes it also the active revision procedure set_approve( revision_id in cr_revisions.revision_id%TYPE, approve_p in varchar2 default 't', publish_date in cr_revisions.publish_date%TYPE default null, archive_date in cr_news.archive_date%TYPE default null, approval_user in cr_news.approval_user%TYPE default null, approval_date in cr_news.approval_date%TYPE default sysdate, approval_ip in cr_news.approval_ip%TYPE default null, live_revision_p in varchar2 default 't' ) is v_item_id cr_items.item_id%TYPE; begin select item_id into v_item_id from cr_revisions where revision_id = news.set_approve.revision_id; -- unapprove an revision (does not mean to knock out active revision) if news.set_approve.approve_p = 'f' then update cr_news set approval_date = null, approval_user = null, approval_ip = null, archive_date = null where news_id = news.set_approve.revision_id; -- update cr_revisions set publish_date = null where revision_id = news.set_approve.revision_id; else -- approve a revision update cr_revisions set publish_date = news.set_approve.publish_date where revision_id = news.set_approve.revision_id; -- update cr_news set archive_date = news.set_approve.archive_date, approval_date = news.set_approve.approval_date, approval_user = news.set_approve.approval_user, approval_ip = news.set_approve.approval_ip where news_id = news.set_approve.revision_id; -- -- cannot use content_item.set_live_revision because it sets publish_date to sysdate if news.set_approve.live_revision_p = 't' then update cr_items set live_revision = news.set_approve.revision_id, publish_status = 'ready' where item_id = v_item_id; end if; -- end if; end set_approve; -- the status function returns information on the puplish or archive status -- it does not make any checks on the order of publish_date and archive_date function status ( publish_date in cr_revisions.publish_date%TYPE, archive_date in cr_news.archive_date%TYPE ) return varchar2 is begin if publish_date is not null then if publish_date > sysdate then -- to be published (2 cases) if archive_date is null then return 'going_live_no_archive'; else return 'going_live_with_archive'; end if; else -- already released or even archived (3 cases) if archive_date is null then return 'published_no_archive'; else if archive_date - sysdate > 0 then return 'published_with_archive'; else return 'archived'; end if; end if; end if; else return 'unapproved'; end if; end status; function name ( news_id in cr_news.news_id%TYPE ) return varchar2 is news_title varchar2(1000); begin select title into news_title from cr_revisions where revision_id = news.name.news_id; return news_title; end name; -- -- API for Revision management -- function revision_new ( item_id in cr_items.item_id%TYPE, -- publish_date in cr_revisions.publish_date%TYPE default null, text in varchar2 default null, title in cr_revisions.title%TYPE, -- -- here goes the revision log description in cr_revisions.description%TYPE, -- mime_type in cr_revisions.mime_type%TYPE default 'text/plain', package_id in cr_news.package_id%TYPE default null, archive_date in cr_news.archive_date%TYPE default null, approval_user in cr_news.approval_user%TYPE default null, approval_date in cr_news.approval_date%TYPE default null, approval_ip in cr_news.approval_ip%TYPE default null, -- creation_date in acs_objects.creation_date%TYPE default sysdate, creation_ip in acs_objects.creation_ip%TYPE default null, creation_user in acs_objects.creation_user%TYPE default null, -- make_active_revision_p in varchar2 default 'f' ) return cr_revisions.revision_id%TYPE is v_revision_id integer; begin -- create revision v_revision_id := content_revision.new( title => title, description => description, publish_date => publish_date, mime_type => mime_type, text => text, item_id => item_id, creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip ); -- create new news entry with new revision insert into cr_news (news_id, package_id, archive_date, approval_user, approval_date, approval_ip) values (v_revision_id, package_id, archive_date, approval_user, approval_date, approval_ip); -- make active revision if indicated if make_active_revision_p = 't' then news.revision_set_active(v_revision_id); end if; return v_revision_id; end revision_new; procedure revision_set_active ( revision_id in cr_revisions.revision_id%TYPE ) is v_news_item_p char; -- could be used to check if really a 'news' item begin update cr_items set live_revision = news.revision_set_active.revision_id, publish_status = 'ready' where item_id = (select item_id from cr_revisions where revision_id = news.revision_set_active.revision_id); end revision_set_active; procedure clone ( new_package_id in cr_news.package_id%TYPE default null, old_package_id in cr_news.package_id%TYPE default null ) is new_news_id integer; begin for one_news in (select publish_date, content.blob_to_string(cr.content) as text, cr.nls_language, cr.title as title, cr.mime_type, cn.package_id, archive_date, approval_user, approval_date, approval_ip, ao.creation_date, ao.creation_ip, ao.creation_user from cr_items ci, cr_revisions cr, cr_news cn, acs_objects ao where (ci.item_id = cr.item_id and ci.live_revision = cr.revision_id and cr.revision_id = cn.news_id and cr.revision_id = ao.object_id) or (ci.live_revision is null and ci.item_id = cr.item_id and cr.revision_id = content_item.get_latest_revision(ci.item_id) and cr.revision_id = cn.news_id and cr.revision_id = ao.object_id)) loop new_news_id := news.new( publish_date => one_news.publish_date, text => one_news.text, nls_language => one_news.nls_language, title => one_news.title, mime_type => one_news.mime_type, package_id => news.clone.new_package_id, archive_date => one_news.archive_date, approval_user => one_news.approval_user, approval_date => one_news.approval_date, approval_ip => one_news.approval_ip, creation_date => one_news.creation_date, creation_ip => one_news.creation_ip, creation_user => one_news.creation_user ); end loop; end clone; -- currently not used, because we want to audit revisions procedure revision_delete ( revision_id in cr_revisions.revision_id%TYPE ) is begin -- delete from cr_news table delete from cr_news where news_id = news.revision_delete.revision_id; -- delete revision content_revision.del( revision_id => news.revision_delete.revision_id ); end revision_delete; end news; / show errors -- **** Recreate views with calls to new status function create or replace view news_items_live_or_submitted as select ci.item_id as item_id, cn.news_id, cn.package_id, cr.publish_date, cn.archive_date, cr.title as publish_title, content.blob_to_string(cr.content) as publish_body, decode(cr.mime_type, 'text/html','t','f') as html_p, ao.creation_user, ps.first_names || ' ' || ps.last_name as item_creator, ao.creation_date, ci.live_revision, news.status(cr.publish_date, cn.archive_date) as status from cr_items ci, cr_revisions cr, cr_news cn, acs_objects ao, persons ps where (ci.item_id = cr.item_id and ci.live_revision = cr.revision_id and cr.revision_id = cn.news_id and cr.revision_id = ao.object_id and ao.creation_user = ps.person_id) or (ci.live_revision is null and ci.item_id = cr.item_id and cr.revision_id = content_item.get_latest_revision(ci.item_id) and cr.revision_id = cn.news_id and cr.revision_id = ao.object_id and ao.creation_user = ps.person_id); create or replace view news_item_revisions as select cr.item_id as item_id, cr.revision_id, ci.live_revision, cr.title as publish_title, content.blob_to_string(cr.content) as publish_body, cr.publish_date, cn.archive_date, cr.description as log_entry, decode(cr.mime_type,'text/html','t','f') as html_p, cr.mime_type as mime_type, cn.package_id, ao.creation_date as creation_date, news.status(cr.publish_date, cn.archive_date) as status, case when exists (select 1 from cr_revisions cr2 where cr2.revision_id = cn.news_id and cr2.publish_date is null ) then 1 else 0 end as approval_needed_p, ps.first_names || ' ' || ps.last_name as item_creator, ao.creation_user, ao.creation_ip, ci.name as item_name from cr_revisions cr, cr_news cn, cr_items ci, acs_objects ao, persons ps where cr.revision_id = ao.object_id and cr.revision_id = cn.news_id and ci.item_id = cr.item_id and ao.creation_user = ps.person_id; create or replace view news_item_full_active as select ci.item_id as item_id, cn.package_id as package_id, revision_id, title as publish_title, content.blob_to_string(cr.content) as publish_body, decode(cr.mime_type,'text/html','t','f') as html_p, cr.publish_date, cn.archive_date, news.status(cr.publish_date, cn.archive_date) as status, ci.name as item_name, ps.person_id as creator_id, ps.first_names || ' ' || ps.last_name as item_creator from cr_items ci, cr_revisions cr, cr_news cn, acs_objects ao, persons ps where cr.item_id = ci.item_id and (cr.revision_id = ci.live_revision or (ci.live_revision is null and cr.revision_id = content_item.get_latest_revision(ci.item_id))) and cr.revision_id = cn.news_id and ci.item_id = ao.object_id and ao.creation_user = ps.person_id;