select p.package_id
from apm_packages p right outer join
( WITH RECURSIVE site_node_tree AS (
select node_id, parent_id, object_id from site_nodes where node_id = :root_id
UNION ALL
select c.node_id, c.parent_id, c.object_id from site_node_tree tree, site_nodes as c
where c.parent_id = tree.node_id
)
select * from site_node_tree n) site_map
on site_map.object_id = p.package_id
where package_key = 'contacts'
and (site_map.object_id is null or acs_permission__permission_p(site_map.object_id, :user_id, 'read') = 't')
select count(distinct person_id) from group_member_map gmm, membership_rels mr, persons left join (select item_id from cr_items where content_type = 'contact_party_revision') items on item_id = person_id
where person_id > 0
and gmm.rel_id = mr.rel_id
and gmm.group_id = -2
and gmm.member_id = persons.person_id
and item_id is null
select distinct person_id, first_names,last_name,email
from group_member_map gmm, membership_rels mr, persons left join (select item_id from cr_items where content_type = 'contact_party_revision') items on item_id = person_id, parties
where person_id > 0
and gmm.rel_id = mr.rel_id
and gmm.group_id = -2
and gmm.member_id = persons.person_id
and person_id = party_id
and item_id is null
select member_state
from cc_users
where user_id = :person_id
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 p.party_id, 't'::boolean, 't'::boolean, 't'::boolean, 'f'::boolean
from parties p left join contact_privacy c on c.party_id = p.party_id
where c.party_id is null
delete
from group_element_index
where group_id = :group_id
and element_id in (select member_id from membership_rels m, group_member_map g where g.rel_id = m.rel_id and member_state = 'deleted' and group_id = -2)
select item_id
from cr_items, membership_rels m, group_member_map g
where g.rel_id = m.rel_id
and member_state = 'deleted'
and group_id = -2
and item_id = member_id
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 groups2.group_id,
$name_field as group_name,
( select count(distinct gamm.member_id) from group_approved_member_map gamm where gamm.group_id = groups2.group_id ) as member_count,
( select count(distinct gcm.component_id) from group_component_map gcm where gcm.group_id = groups2.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,
CASE WHEN user_change_p THEN '1' ELSE '0' END as user_change_p,
$dotlrn_community_p as dotlrn_community_p,
CASE WHEN contact_groups.notifications_p THEN '1' ELSE '0' END as notifications_p
from ( select distinct g.*
from groups g left join contact_groups cg on (g.group_id = cg.group_id) left join application_groups ag on (ag.group_id = g.group_id)
where ag.package_id is null or cg.package_id is not null) groups2
left join ( select * from contact_groups where package_id = :package_id ) as contact_groups on ( groups2.group_id = contact_groups.group_id ),
acs_objects
$additional_from
where groups2.group_id not in ('-1','[contacts::default_group -package_id $package_id]')
and groups2.group_id = acs_objects.object_id
and groups2.group_id not in ( select gcm.component_id from group_component_map gcm where gcm.group_id != -1 )
$additional_where
order by mapped_p desc, CASE WHEN contact_groups.default_p THEN '000000000' ELSE upper( $name_field ) 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,notifications_p,package_id)
values
(:group_id,:default_p,:notifications_p,:package_id)
select 1 from contact_groups
where group_id = :group_id
and package_id = :package_id
select 1 from contact_groups
where group_id = :group_id
and notifications_p
limit 1
insert into cr_items
(item_id,parent_id,name,content_type)
values
(:party_id,contact__folder_id(),:party_id,'contact_party_revision');