select object_id from site_nodes where tree_level(tree_sortkey) < ( select tree_level(n2.tree_sortkey) from site_nodes n2 where n2.node_id = :node_id ) and object_id in ( select package_id from apm_packages where package_key = 'acs-subsite' ) order by tree_sortkey desc limit 1 select p.package_id from site_nodes n, site_nodes n2, apm_packages p where n2.node_id = (select coalesce(:parent_node_id, site_node__node_id('/', null))) and n.tree_sortkey between n2.tree_sortkey and tree_right(n2.tree_sortkey) and n.object_id = p.package_id and p.package_key = 'contacts' and (tree_level(n.tree_sortkey) - (select tree_level(n2.tree_sortkey) from site_nodes n2 where n2.node_id = (select coalesce(:parent_node_id, site_node__node_id('/', null))))) > 1; select person_id from persons where person_id not in ( select item_id from cr_items ) select organization_id from organizations where organization_id not in ( select item_id from cr_items ) insert into contact_privacy ( party_id, email_p, mail_p, phone_p, gone_p ) select party_id, 't'::boolean, 't'::boolean, 't'::boolean, 'f'::boolean from parties where party_id not in ( select party_id from contact_privacy ) select attribute_id from ams_attributes where object_type in ( 'party', 'person' ) and attribute_id in ([template::util::tcl_to_sql_list $attribute_ids]) and widget is not null select 1 from acs_rel_types where rel_type = 'contact_rels_spouse' select ${type}_p from contact_privacy where party_id = :party_id select 1 from contact_privacy where party_id = :party_id update contact_privacy set email_p = :email_p, mail_p = :mail_p, phone_p = :phone_p, gone_p = :gone_p where party_id = :party_id insert into contact_privacy ( party_id, email_p, mail_p, phone_p, gone_p ) values ( :party_id, :email_p, :mail_p, :phone_p, :gone_p ) select name from cr_items where parent_id = :party_id select 1 from group_approved_member_map where member_id = :party_id and group_id in ([template::util::tcl_to_sql_list [contacts::default_groups -package_id $package_id]]) limit 1 select CASE WHEN object_id_one = :party_id THEN object_id_two ELSE object_id_one END from acs_rels, acs_objects where rel_type = 'contact_rels_spouse' and ( object_id_one = :party_id or object_id_two = :party_id ) and rel_id = object_id order by creation_date select acs_object__delete(rel_id) from acs_rels where ( ( object_id_one = :party_id and object_id_two = :spouse ) or ( object_id_one = :spouse and object_id_two = :party_id ) ) and rel_type = 'contact_rels_spouse' select groups.group_id, acs_objects.title as group_name, ( select count(distinct gamm.member_id) from group_approved_member_map gamm where gamm.group_id = groups.group_id ) as member_count, ( select count(distinct gcm.component_id) from group_component_map gcm where gcm.group_id = groups.group_id) as component_count, CASE WHEN contact_groups.package_id is not null THEN '1' ELSE '0' END as mapped_p, CASE WHEN default_p THEN '1' ELSE '0' END as default_p from groups left join ( select * from contact_groups where package_id = :package_id ) as contact_groups on ( groups.group_id = contact_groups.group_id ), acs_objects where groups.group_id not in ('-1','[contacts::default_group -package_id $package_id]') and groups.group_id = acs_objects.object_id and groups.group_id not in ( select gcm.component_id from group_component_map gcm where gcm.group_id != -1 ) and groups.group_id not in ( select group_id from application_groups ) $filter_clause order by mapped_p desc, CASE WHEN contact_groups.default_p THEN '000000000' ELSE upper(groups.group_name) END select groups.group_id, groups.group_name, ( select count(distinct gamm.member_id) from group_approved_member_map gamm where gamm.group_id = groups.group_id ) as member_count, CASE WHEN package_id is not null THEN '1' ELSE '0' END as mapped_p, CASE WHEN default_p THEN '1' ELSE '0' END as default_p from groups left join contact_groups on ( groups.group_id = contact_groups.group_id ), group_component_map where group_component_map.group_id = :group_id and group_component_map.component_id = groups.group_id order by upper(groups.group_name) select group_id from group_component_map where component_id = :group_id and group_id != '-1' select acs_group__new ( :group_id, 'group', now(), :creation_user, :creation_ip, :email, :url, :group_name, :join_policy, :context_id ) insert into contact_groups (group_id,default_p,package_id) values (:group_id,:default_p,:package_id)