select
ceo.extend_id
from
contact_search_extend_map cse,
contact_extend_options ceo
where
cse.search_id = :search_id
and ceo.extend_id = cse.extend_id
and ceo.aggregated_p = 't'
select
1
from
contact_search_extend_map
where
search_id = :search_id
and extend_id = :extend
insert into contact_search_extend_map (search_id, extend_id, attribute_id)
values (:search_id, :extend, null)
update contact_searches set aggregated_attribute = :aggregate_attribute_id
where search_id = :search_id
select
ot.option_id,
ot.option
from
ams_option_types ot
where
ot.attribute_id = :attr_id
select
c.default_name as option,
c.iso
from
countries c
select
value_id
from
ams_options
where
option_id = :option_id
select
count(parties.party_id)
from
parties
where parties.party_id in (
select
parties.party_id
from
parties
left join organizations on (parties.party_id = organizations.organization_id)
left join cr_items on (parties.party_id = cr_items.item_id)
left join cr_revisions on (cr_items.latest_revision = cr_revisions.revision_id ),
group_distinct_member_map
where parties.party_id = group_distinct_member_map.member_id
$search_clause
)
and parties.party_id in (
select
distinct
p.party_id
from
ams_attribute_values a,
cr_items i,
parties p
where
a.object_id = i.latest_revision and
i.item_id = p.party_id
and a.value_id = $value_id )
select
count(parties.party_id)
from
parties
where parties.party_id in (
select
parties.party_id
from
parties
left join organizations on (parties.party_id = organizations.organization_id)
left join cr_items on (parties.party_id = cr_items.item_id)
left join cr_revisions on (cr_items.latest_revision = cr_revisions.revision_id ),
group_distinct_member_map
where parties.party_id = group_distinct_member_map.member_id
$search_clause
)
and parties.party_id in (
select
p.party_id
from
parties p,
ams_attribute_values a,
postal_addresses pa,
cr_items i,
cr_revisions r
where
i.item_id = p.party_id
and r.revision_id = i.latest_revision
and r.revision_id = a.object_id
and a.value_id = pa.address_id
and pa.country_code = :iso
)
select
distinct
ot.pretty_name as option,
rt.rel_type
from
acs_rel_types rt,
acs_object_types ot,
parties p
where
rt.rel_type like 'contact_rels_%'
and rt.rel_type = ot.object_type
select
count(t.party_id)
from
(
select
distinct
CASE WHEN r.object_id_one = parties.party_id
THEN r.object_id_one
ELSE r.object_id_two END as party_id
from
acs_rels r
where
r.rel_type = :rel_type
) t,
cr_items ci,
cr_revisions cr
where
t.party_id = ci.item_id
and ci.latest_revision = cr.revision_id
$search_clause
select
ceo.pretty_name,
ceo.extend_id as e_id
from
contact_extend_options ceo
where
ceo.aggregated_p = 't'
and ceo.extend_id not in (
select
extend_id
from
contact_search_extend_map
where
search_id = :search_id
)
$extend_subquery
and party_id in (
select
parties.party_id
from
parties
left join organizations on (parties.party_id = organizations.organization_id)
left join cr_items on (parties.party_id = cr_items.item_id)
left join cr_revisions on (cr_items.latest_revision = cr_revisions.revision_id ),
group_distinct_member_map
where parties.party_id = group_distinct_member_map.member_id
$search_clause
)
and parties.party_id in (
select
distinct
p.party_id
from
ams_attribute_values a,
cr_items i,
parties p
where
a.object_id = i.latest_revision and
i.item_id = p.party_id
and a.value_id = $value_id )
$extend_subquery
and party_id in (
select
parties.party_id
from
parties
left join organizations on (parties.party_id = organizations.organization_id)
left join cr_items on (parties.party_id = cr_items.item_id)
left join cr_revisions on (cr_items.latest_revision = cr_revisions.revision_id ),
group_distinct_member_map
where parties.party_id = group_distinct_member_map.member_id
$search_clause
)
and parties.party_id in (
select
p.party_id
from
parties p,
ams_attribute_values a,
postal_addresses pa,
cr_items i,
cr_revisions r
where
i.item_id = p.party_id
and r.revision_id = i.latest_revision
and r.revision_id = a.object_id
and a.value_id = pa.address_id
and pa.country_code = :iso
)
$extend_subquery
and party_id in (
select
t.party_id
from
(
select
distinct
CASE WHEN r.object_id_one = parties.party_id
THEN r.object_id_one
ELSE r.object_id_two END as party_id
from
acs_rels r
where
r.rel_type = :rel_type
) t,
cr_items ci,
cr_revisions cr
where
t.party_id = ci.item_id
and ci.latest_revision = cr.revision_id
$search_clause )