· 5 years ago · Sep 15, 2020, 08:58 AM
1select
2c.id_pk,
3c.uuid,
4c.constituent_type,
5c.title, c.contact_name,
6c.first_name, c.middle_name, c.last_or_org_name,
7c.name_suffix, c.access_level,
8c.head_of_household_id,
9c.gift_aid_declaration,
10c.formal_salutation,
11c.informal_salutation,
12c.addressee,
13c.list_as_name,
14c.spouse_formal_salutation,
15c.spouse_informal_salutation,
16c.spouse_addressee,
17c.spouse_list_as_name,
18AES_DECRYPT(c.birth_date, @secretKey, c.salt),
19c.deceased_date,
20c.email_address, c.is_vip,
21c.nickname, c.maiden_name, AES_DECRYPT(c.birth_gender, @secretKey, c.salt),
22AES_DECRYPT(c.govt_id, @secretKey, c.salt),
23AES_DECRYPT(c.tax_id_number, @secretKey, c.salt)
24,
25a.prefix_1,
26a.prefix_2,
27a.prefix_3,
28a.line_1,
29a.line_2,
30a.line_3,
31a.postal_code_suffix,
32a.dpc,
33y.id_pk,
34y.name, y.state_province_id, y.postal_code, y.county_name, y.country_id,
35a.ward,
36p.country_code,
37p.area,
38p.number
39from constituent c
40left join address a on (a.constituent_id = c.id_pk and a.primary_ind = 'y')
41left join city y on (y.id_pk = a.city_id)
42left join contact p on (p.constituent_id = c.id_pk and p.is_primary = 't' and p.contact_method = 0 and p.record_status <> '2')
43
44left join custom_constit_fields ccf on (ccf.constituent_id=c.id_pk)
45left join custom_my_fields cmf on (cmf.constituent_id=c.id_pk)
46left join form_header fh on (fh.owning_record_id=c.id_pk and fh.form_type=1)
47where c.record_status in ('1','6','7','9')
48and
49(
50 (AES_DECRYPT(c.govt_id, @secretKey, c.salt) like null)
51 or
52 (null is null)
53 or
54 (AES_DECRYPT(c.tax_id_number, @secretKey, c.salt) like null)
55)
56and
57(
58 (c.first_name like 'Chris%')
59 or
60 (c.nickname like 'Chris%')
61 #or (c.contact_name like ?)
62 or
63 ('Chris%' is null)
64)
65and
66(
67 (c.middle_name like null)
68 or
69 (null is null)
70)
71and
72(
73 (c.last_or_org_name like 'Vaugh%')
74 or
75 (c.maiden_name like 'Vaugh%')
76 #or (c.contact_name like ?)
77 or
78 ('Vaugh%' is null)
79)
80and
81(
82 (c.uuid = null)
83 or
84 (null is null)
85)
86and
87(
88 (c.email_address like null)
89 or
90 (null is null)
91)
92and
93(
94 (c.contact_name like null)
95 or
96 (null is null)
97)
98and
99(
100 (a.line_1 like null and a.record_status='1')
101 or
102 (a.line_2 like null and a.record_status='1')
103 or
104 (null is null)
105)
106and
107(
108 (y.name like null)
109 or
110 (null is null)
111)
112and
113(
114 (y.state_province_id like null)
115 or
116 (null is null)
117)
118and
119(
120 (y.postal_code like null)
121 or
122 (null is null)
123)
124and
125((
126 (p.number like null)
127 or
128 (null is null)
129)
130or
131(
132 (p.number like null)
133 or
134 (null is null)
135))
136and
137(c.constituent_type = 1 or not 0)
138#custom_field_filter
139#custom_my_filter
140#form_filter
141group by c.id_pk
142order by c.last_or_org_name, c.first_name, c.middle_name