Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
7d61e75f | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 5 | | | |
7d61e75f TO |
6 | | This work is published under the GNU AGPLv3 license with some | |
7 | | permitted exceptions and without any warranty. For full license | | |
8 | | and copyright information, see https://civicrm.org/licensing | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 | 11 | |
6a488035 TO |
12 | /** |
13 | * Test APIv3 civicrm_report_instance_* functions | |
14 | * | |
6c6e6187 TO |
15 | * @package CiviCRM_APIv3 |
16 | * @subpackage API_Report | |
acb109b7 | 17 | * @group headless |
6a488035 | 18 | */ |
6a488035 | 19 | class api_v3_ReportTemplateTest extends CiviUnitTestCase { |
2af6f0c0 | 20 | |
21 | use CRMTraits_ACL_PermissionTrait; | |
e40ce31e | 22 | use CRMTraits_PCP_PCPTestTrait; |
d7a896b4 | 23 | use CRMTraits_Custom_CustomDataTrait; |
2af6f0c0 | 24 | |
5a14305b | 25 | protected $contactIDs = []; |
26 | ||
2c6b4783 | 27 | /** |
28 | * Our group reports use an alter so transaction cleanup won't work. | |
29 | * | |
30 | * @throws \Exception | |
31 | */ | |
32 | public function tearDown() { | |
33 | $this->quickCleanUpFinancialEntities(); | |
d7a896b4 | 34 | $this->quickCleanup(['civicrm_group', 'civicrm_saved_search', 'civicrm_group_contact', 'civicrm_group_contact_cache', 'civicrm_group'], TRUE); |
2c6b4783 | 35 | parent::tearDown(); |
6a488035 TO |
36 | } |
37 | ||
f4b20644 | 38 | /** |
39 | * Test CRUD actions on a report template. | |
40 | * | |
41 | * @throws \CRM_Core_Exception | |
42 | */ | |
6a488035 | 43 | public function testReportTemplate() { |
f4b20644 | 44 | /** @noinspection SpellCheckingInspection */ |
9099cab3 | 45 | $result = $this->callAPISuccess('ReportTemplate', 'create', [ |
6a488035 TO |
46 | 'label' => 'Example Form', |
47 | 'description' => 'Longish description of the example form', | |
48 | 'class_name' => 'CRM_Report_Form_Examplez', | |
49 | 'report_url' => 'example/path', | |
50 | 'component' => 'CiviCase', | |
9099cab3 | 51 | ]); |
1cbea43e | 52 | $this->assertAPISuccess($result); |
ba4a1892 | 53 | $this->assertEquals(1, $result['count']); |
6a488035 | 54 | $entityId = $result['id']; |
f4b20644 | 55 | $this->assertInternalType('numeric', $entityId); |
ba4a1892 | 56 | $this->assertEquals(7, $result['values'][$entityId]['component_id']); |
6a488035 TO |
57 | $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value |
58 | WHERE name = "CRM_Report_Form_Examplez" | |
0201b57f | 59 | AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") '); |
6a488035 TO |
60 | $this->assertDBQuery(1, 'SELECT is_active FROM civicrm_option_value |
61 | WHERE name = "CRM_Report_Form_Examplez"'); | |
62 | ||
63 | // change component to null | |
9099cab3 | 64 | $result = $this->callAPISuccess('ReportTemplate', 'create', [ |
92915c55 | 65 | 'id' => $entityId, |
6a488035 | 66 | 'component' => '', |
9099cab3 | 67 | ]); |
1cbea43e | 68 | $this->assertAPISuccess($result); |
ba4a1892 | 69 | $this->assertEquals(1, $result['count']); |
6a488035 TO |
70 | $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value |
71 | WHERE name = "CRM_Report_Form_Examplez" | |
0201b57f | 72 | AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") '); |
6a488035 TO |
73 | $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value |
74 | WHERE name = "CRM_Report_Form_Examplez" | |
75 | AND component_id IS NULL'); | |
76 | ||
77 | // deactivate | |
9099cab3 | 78 | $result = $this->callAPISuccess('ReportTemplate', 'create', [ |
92915c55 | 79 | 'id' => $entityId, |
6a488035 | 80 | 'is_active' => 0, |
9099cab3 | 81 | ]); |
1cbea43e | 82 | $this->assertAPISuccess($result); |
ba4a1892 | 83 | $this->assertEquals(1, $result['count']); |
6a488035 TO |
84 | $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value |
85 | WHERE name = "CRM_Report_Form_Examplez" | |
0201b57f | 86 | AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") '); |
6a488035 TO |
87 | $this->assertDBQuery(0, 'SELECT is_active FROM civicrm_option_value |
88 | WHERE name = "CRM_Report_Form_Examplez"'); | |
89 | ||
90 | // activate | |
9099cab3 | 91 | $result = $this->callAPISuccess('ReportTemplate', 'create', [ |
92915c55 | 92 | 'id' => $entityId, |
6a488035 | 93 | 'is_active' => 1, |
9099cab3 | 94 | ]); |
1cbea43e | 95 | $this->assertAPISuccess($result); |
ba4a1892 | 96 | $this->assertEquals(1, $result['count']); |
6a488035 TO |
97 | $this->assertDBQuery(1, 'SELECT count(*) FROM civicrm_option_value |
98 | WHERE name = "CRM_Report_Form_Examplez" | |
0201b57f | 99 | AND option_group_id IN (SELECT id from civicrm_option_group WHERE name = "report_template") '); |
6a488035 TO |
100 | $this->assertDBQuery(1, 'SELECT is_active FROM civicrm_option_value |
101 | WHERE name = "CRM_Report_Form_Examplez"'); | |
102 | ||
9099cab3 | 103 | $result = $this->callAPISuccess('ReportTemplate', 'delete', [ |
92915c55 | 104 | 'id' => $entityId, |
9099cab3 | 105 | ]); |
1cbea43e | 106 | $this->assertAPISuccess($result); |
ba4a1892 | 107 | $this->assertEquals(1, $result['count']); |
6a488035 TO |
108 | $this->assertDBQuery(0, 'SELECT count(*) FROM civicrm_option_value |
109 | WHERE name = "CRM_Report_Form_Examplez" | |
110 | '); | |
111 | } | |
e2779f6e | 112 | |
880f81a1 | 113 | /** |
114 | * Test api to get rows from reports. | |
115 | * | |
116 | * @dataProvider getReportTemplatesSupportingSelectWhere | |
117 | * | |
118 | * @param $reportID | |
119 | * | |
f4b20644 | 120 | * @throws \CRM_Core_Exception |
880f81a1 | 121 | */ |
122 | public function testReportTemplateSelectWhere($reportID) { | |
9099cab3 | 123 | $this->hookClass->setHook('civicrm_selectWhereClause', [$this, 'hookSelectWhere']); |
880f81a1 | 124 | $result = $this->callAPISuccess('report_template', 'getrows', [ |
125 | 'report_id' => $reportID, | |
126 | 'options' => ['metadata' => ['sql']], | |
127 | ]); | |
128 | $found = FALSE; | |
129 | foreach ($result['metadata']['sql'] as $sql) { | |
f4b20644 | 130 | if (strpos($sql, " = 'Organization' ") !== FALSE) { |
880f81a1 | 131 | $found = TRUE; |
132 | } | |
133 | } | |
134 | $this->assertTrue($found, $reportID); | |
135 | } | |
136 | ||
137 | /** | |
138 | * Get templates suitable for SelectWhere test. | |
139 | * | |
140 | * @return array | |
f4b20644 | 141 | * @throws \CiviCRM_API3_Exception |
880f81a1 | 142 | */ |
143 | public function getReportTemplatesSupportingSelectWhere() { | |
f4b20644 | 144 | $allTemplates = self::getReportTemplates(); |
880f81a1 | 145 | // Exclude all that do not work as of test being written. I have not dug into why not. |
146 | $currentlyExcluded = [ | |
147 | 'contribute/repeat', | |
880f81a1 | 148 | 'member/summary', |
149 | 'event/summary', | |
150 | 'case/summary', | |
151 | 'case/timespent', | |
152 | 'case/demographics', | |
153 | 'contact/log', | |
154 | 'contribute/bookkeeping', | |
155 | 'grant/detail', | |
156 | 'event/incomesummary', | |
157 | 'case/detail', | |
158 | 'Mailing/bounce', | |
159 | 'Mailing/summary', | |
160 | 'grant/statistics', | |
161 | 'logging/contact/detail', | |
162 | 'logging/contact/summary', | |
163 | ]; | |
164 | foreach ($allTemplates as $index => $template) { | |
165 | $reportID = $template[0]; | |
f4b20644 | 166 | if (in_array($reportID, $currentlyExcluded, TRUE) || stripos($reportID, 'has existing issues') !== FALSE) { |
880f81a1 | 167 | unset($allTemplates[$index]); |
168 | } | |
169 | } | |
170 | return $allTemplates; | |
171 | } | |
172 | ||
173 | /** | |
174 | * @param \CRM_Core_DAO $entity | |
175 | * @param array $clauses | |
176 | */ | |
177 | public function hookSelectWhere($entity, &$clauses) { | |
178 | // Restrict access to cases by type | |
f4b20644 | 179 | if ($entity === 'Contact') { |
880f81a1 | 180 | $clauses['contact_type'][] = " = 'Organization' "; |
181 | } | |
182 | } | |
183 | ||
e2779f6e | 184 | /** |
fe482240 | 185 | * Test getrows on contact summary report. |
f4b20644 | 186 | * |
187 | * @throws \CRM_Core_Exception | |
e2779f6e | 188 | */ |
00be9182 | 189 | public function testReportTemplateGetRowsContactSummary() { |
9099cab3 | 190 | $result = $this->callAPISuccess('report_template', 'getrows', [ |
e2779f6e | 191 | 'report_id' => 'contact/summary', |
9099cab3 | 192 | 'options' => ['metadata' => ['labels', 'title']], |
f4b20644 | 193 | ]); |
781d91c8 | 194 | $this->assertEquals('Contact Name', $result['metadata']['labels']['civicrm_contact_sort_name']); |
e2779f6e E |
195 | |
196 | //the second part of this test has been commented out because it relied on the db being reset to | |
197 | // it's base state | |
198 | //wasn't able to get that to work consistently | |
199 | // however, when the db is in the base state the tests do pass | |
200 | // and because the test covers 'all' contacts we can't create our own & assume the others don't exist | |
201 | /* | |
202 | $this->assertEquals(2, $result['count']); | |
203 | $this->assertEquals('Default Organization', $result[0]['civicrm_contact_sort_name']); | |
204 | $this->assertEquals('Second Domain', $result[1]['civicrm_contact_sort_name']); | |
205 | $this->assertEquals('15 Main St', $result[1]['civicrm_address_street_address']); | |
e70a7fc0 | 206 | */ |
e2779f6e E |
207 | } |
208 | ||
b736d2b6 SP |
209 | /** |
210 | * Test getrows on Mailing Opened report. | |
211 | */ | |
212 | public function testReportTemplateGetRowsMailingUniqueOpened() { | |
f4b20644 | 213 | $description = 'Retrieve rows from a mailing opened report template.'; |
214 | $this->loadXMLDataSet(__DIR__ . '/../../CRM/Mailing/BAO/queryDataset.xml'); | |
ead1a15e MD |
215 | |
216 | // Check total rows without distinct | |
217 | global $_REQUEST; | |
218 | $_REQUEST['distinct'] = 0; | |
9099cab3 | 219 | $result = $this->callAPIAndDocument('report_template', 'getrows', [ |
b736d2b6 | 220 | 'report_id' => 'Mailing/opened', |
9099cab3 CW |
221 | 'options' => ['metadata' => ['labels', 'title']], |
222 | ], __FUNCTION__, __FILE__, $description, 'Getrows'); | |
ead1a15e | 223 | $this->assertEquals(14, $result['count']); |
b736d2b6 | 224 | |
ead1a15e MD |
225 | // Check total rows with distinct |
226 | $_REQUEST['distinct'] = 1; | |
9099cab3 | 227 | $result = $this->callAPIAndDocument('report_template', 'getrows', [ |
ead1a15e | 228 | 'report_id' => 'Mailing/opened', |
9099cab3 CW |
229 | 'options' => ['metadata' => ['labels', 'title']], |
230 | ], __FUNCTION__, __FILE__, $description, 'Getrows'); | |
ead1a15e MD |
231 | $this->assertEquals(5, $result['count']); |
232 | ||
233 | // Check total rows with distinct by passing NULL value to distinct parameter | |
234 | $_REQUEST['distinct'] = NULL; | |
9099cab3 | 235 | $result = $this->callAPIAndDocument('report_template', 'getrows', [ |
ead1a15e | 236 | 'report_id' => 'Mailing/opened', |
9099cab3 CW |
237 | 'options' => ['metadata' => ['labels', 'title']], |
238 | ], __FUNCTION__, __FILE__, $description, 'Getrows'); | |
ead1a15e | 239 | $this->assertEquals(5, $result['count']); |
b736d2b6 SP |
240 | } |
241 | ||
e2779f6e | 242 | /** |
63dc1f23 | 243 | * Test api to get rows from reports. |
fe482240 | 244 | * |
e2779f6e | 245 | * @dataProvider getReportTemplates |
fe482240 | 246 | * |
1e1fdcf6 | 247 | * @param $reportID |
fe482240 | 248 | * |
f4b20644 | 249 | * @throws \CRM_Core_Exception |
e2779f6e | 250 | */ |
00be9182 | 251 | public function testReportTemplateGetRowsAllReports($reportID) { |
63dc1f23 | 252 | //$reportID = 'logging/contact/summary'; |
f4b20644 | 253 | if (stripos($reportID, 'has existing issues') !== FALSE) { |
e2779f6e E |
254 | $this->markTestIncomplete($reportID); |
255 | } | |
f4b20644 | 256 | if (strpos($reportID, 'logging') === 0) { |
63dc1f23 | 257 | Civi::settings()->set('logging', 1); |
258 | } | |
259 | ||
9099cab3 | 260 | $this->callAPISuccess('report_template', 'getrows', [ |
92915c55 | 261 | 'report_id' => $reportID, |
9099cab3 | 262 | ]); |
f4b20644 | 263 | if (strpos($reportID, 'logging') === 0) { |
63dc1f23 | 264 | Civi::settings()->set('logging', 0); |
265 | } | |
e2779f6e E |
266 | } |
267 | ||
f2f65d33 | 268 | /** |
269 | * Test logging report when a custom data table has a table removed by hook. | |
270 | * | |
271 | * Here we are checking that no fatal is triggered. | |
f4b20644 | 272 | * |
273 | * @throws \CRM_Core_Exception | |
f2f65d33 | 274 | */ |
275 | public function testLoggingReportWithHookRemovalOfCustomDataTable() { | |
276 | Civi::settings()->set('logging', 1); | |
277 | $group1 = $this->customGroupCreate(); | |
278 | $group2 = $this->customGroupCreate(['name' => 'second_one', 'title' => 'second one', 'table_name' => 'civicrm_value_second_one']); | |
9099cab3 CW |
279 | $this->customFieldCreate(['custom_group_id' => $group1['id'], 'label' => 'field one']); |
280 | $this->customFieldCreate(['custom_group_id' => $group2['id'], 'label' => 'field two']); | |
281 | $this->hookClass->setHook('civicrm_alterLogTables', [$this, 'alterLogTablesRemoveCustom']); | |
f2f65d33 | 282 | |
9099cab3 | 283 | $this->callAPISuccess('report_template', 'getrows', [ |
f2f65d33 | 284 | 'report_id' => 'logging/contact/summary', |
9099cab3 | 285 | ]); |
f2f65d33 | 286 | Civi::settings()->set('logging', 0); |
287 | $this->customGroupDelete($group1['id']); | |
288 | $this->customGroupDelete($group2['id']); | |
289 | } | |
290 | ||
291 | /** | |
292 | * Remove one log table from the logging spec. | |
293 | * | |
294 | * @param array $logTableSpec | |
295 | */ | |
296 | public function alterLogTablesRemoveCustom(&$logTableSpec) { | |
297 | unset($logTableSpec['civicrm_value_second_one']); | |
e2779f6e E |
298 | } |
299 | ||
6c8223f6 | 300 | /** |
301 | * Test api to get rows from reports with ACLs enabled. | |
302 | * | |
303 | * Checking for lack of fatal error at the moment. | |
304 | * | |
305 | * @dataProvider getReportTemplates | |
306 | * | |
307 | * @param $reportID | |
308 | * | |
a6439b6a | 309 | * @throws \PHPUnit\Framework\IncompleteTestError |
f4b20644 | 310 | * @throws \CRM_Core_Exception |
6c8223f6 | 311 | */ |
312 | public function testReportTemplateGetRowsAllReportsACL($reportID) { | |
f4b20644 | 313 | if (stripos($reportID, 'has existing issues') !== FALSE) { |
6c8223f6 | 314 | $this->markTestIncomplete($reportID); |
315 | } | |
9099cab3 CW |
316 | $this->hookClass->setHook('civicrm_aclWhereClause', [$this, 'aclWhereHookNoResults']); |
317 | $this->callAPISuccess('report_template', 'getrows', [ | |
6c8223f6 | 318 | 'report_id' => $reportID, |
9099cab3 | 319 | ]); |
6c8223f6 | 320 | } |
321 | ||
e2779f6e | 322 | /** |
fe482240 EM |
323 | * Test get statistics. |
324 | * | |
e2779f6e | 325 | * @dataProvider getReportTemplates |
fe482240 | 326 | * |
1e1fdcf6 | 327 | * @param $reportID |
fe482240 | 328 | * |
a6439b6a | 329 | * @throws \PHPUnit\Framework\IncompleteTestError |
e2779f6e | 330 | */ |
00be9182 | 331 | public function testReportTemplateGetStatisticsAllReports($reportID) { |
f4b20644 | 332 | if (stripos($reportID, 'has existing issues') !== FALSE) { |
e2779f6e E |
333 | $this->markTestIncomplete($reportID); |
334 | } | |
9099cab3 | 335 | if (in_array($reportID, ['contribute/softcredit', 'contribute/bookkeeping'])) { |
f4b20644 | 336 | $this->markTestIncomplete($reportID . ' has non enotices when calling statistics fn'); |
e2779f6e | 337 | } |
5c49fee0 | 338 | $description = "Get Statistics from a report (note there isn't much data to get in the test DB)."; |
f5cae495 SL |
339 | if ($reportID === 'contribute/summary') { |
340 | $this->hookClass->setHook('civicrm_alterReportVar', [$this, 'alterReportVarHook']); | |
341 | } | |
f4b20644 | 342 | $this->callAPIAndDocument('report_template', 'getstatistics', [ |
e2779f6e | 343 | 'report_id' => $reportID, |
f4b20644 | 344 | ], __FUNCTION__, __FILE__, $description, 'Getstatistics'); |
e2779f6e E |
345 | } |
346 | ||
f5cae495 SL |
347 | /** |
348 | * Implements hook_civicrm_alterReportVar(). | |
349 | */ | |
350 | public function alterReportVarHook($varType, &$var, &$object) { | |
351 | if ($varType === 'sql' && $object instanceof CRM_Report_Form_Contribute_Summary) { | |
f4b20644 | 352 | /* @var CRM_Report_Form $var */ |
f5cae495 | 353 | $from = $var->getVar('_from'); |
f4b20644 | 354 | $from .= ' LEFT JOIN civicrm_financial_type as temp ON temp.id = contribution_civireport.financial_type_id'; |
f5cae495 SL |
355 | $var->setVar('_from', $from); |
356 | $where = $var->getVar('_where'); | |
f4b20644 | 357 | $where .= ' AND ( temp.id IS NOT NULL )'; |
f5cae495 SL |
358 | $var->setVar('_where', $where); |
359 | } | |
360 | } | |
361 | ||
e2779f6e | 362 | /** |
fe482240 EM |
363 | * Data provider function for getting all templates. |
364 | * | |
365 | * Note that the function needs to | |
e2779f6e | 366 | * be static so cannot use $this->callAPISuccess |
f4b20644 | 367 | * |
368 | * @throws \CiviCRM_API3_Exception | |
e2779f6e E |
369 | */ |
370 | public static function getReportTemplates() { | |
f4b20644 | 371 | $reportTemplates = []; |
9099cab3 | 372 | $reportsToSkip = [ |
92915c55 | 373 | 'event/income' => 'I do no understand why but error is Call to undefined method CRM_Report_Form_Event_Income::from() in CRM/Report/Form.php on line 2120', |
92915c55 | 374 | 'contribute/history' => 'Declaration of CRM_Report_Form_Contribute_History::buildRows() should be compatible with CRM_Report_Form::buildRows($sql, &$rows)', |
9099cab3 | 375 | ]; |
e2779f6e | 376 | |
9099cab3 | 377 | $reports = civicrm_api3('report_template', 'get', ['return' => 'value', 'options' => ['limit' => 500]]); |
e2779f6e | 378 | foreach ($reports['values'] as $report) { |
22e263ad | 379 | if (empty($reportsToSkip[$report['value']])) { |
9099cab3 | 380 | $reportTemplates[] = [$report['value']]; |
e2779f6e E |
381 | } |
382 | else { | |
f4b20644 | 383 | $reportTemplates[] = [$report['value'] . ' has existing issues : ' . $reportsToSkip[$report['value']]]; |
e2779f6e E |
384 | } |
385 | } | |
386 | ||
e2779f6e E |
387 | return $reportTemplates; |
388 | } | |
96025800 | 389 | |
2c6b4783 | 390 | /** |
391 | * Get contribution templates that work with basic filter tests. | |
392 | * | |
393 | * These templates require minimal data config. | |
394 | */ | |
395 | public static function getContributionReportTemplates() { | |
9099cab3 | 396 | return [['contribute/summary'], ['contribute/detail'], ['contribute/repeat'], ['topDonor' => 'contribute/topDonor']]; |
eae0f0d9 | 397 | } |
398 | ||
399 | /** | |
400 | * Get contribution templates that work with basic filter tests. | |
401 | * | |
402 | * These templates require minimal data config. | |
403 | */ | |
404 | public static function getMembershipReportTemplates() { | |
9099cab3 | 405 | return [['member/detail']]; |
eae0f0d9 | 406 | } |
407 | ||
c69ecb12 | 408 | /** |
409 | * Get the membership and contribution reports to test. | |
410 | * | |
411 | * @return array | |
412 | */ | |
eae0f0d9 | 413 | public static function getMembershipAndContributionReportTemplatesForGroupTests() { |
414 | $templates = array_merge(self::getContributionReportTemplates(), self::getMembershipReportTemplates()); | |
415 | foreach ($templates as $key => $value) { | |
416 | if (array_key_exists('topDonor', $value)) { | |
417 | // Report is not standard enough to test here. | |
418 | unset($templates[$key]); | |
419 | } | |
420 | ||
421 | } | |
422 | return $templates; | |
2c6b4783 | 423 | } |
424 | ||
c160fde8 | 425 | /** |
426 | * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year. | |
f4b20644 | 427 | * |
428 | * @throws \CRM_Core_Exception | |
c160fde8 | 429 | */ |
430 | public function testLybuntReportWithData() { | |
431 | $inInd = $this->individualCreate(); | |
432 | $outInd = $this->individualCreate(); | |
9099cab3 CW |
433 | $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-03-01']); |
434 | $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]); | |
435 | $rows = $this->callAPISuccess('report_template', 'getrows', [ | |
c160fde8 | 436 | 'report_id' => 'contribute/lybunt', |
437 | 'yid_value' => 2015, | |
438 | 'yid_op' => 'calendar', | |
9099cab3 CW |
439 | 'options' => ['metadata' => ['sql']], |
440 | ]); | |
f4b20644 | 441 | $this->assertEquals(1, $rows['count'], 'Report failed - the sql used to generate the results was ' . print_r($rows['metadata']['sql'], TRUE)); |
c160fde8 | 442 | } |
443 | ||
33072bc7 | 444 | /** |
445 | * Test Lybunt report applies ACLs. | |
f4b20644 | 446 | * |
447 | * @throws \CRM_Core_Exception | |
33072bc7 | 448 | */ |
449 | public function testLybuntReportWithDataAndACLFilter() { | |
9099cab3 | 450 | CRM_Core_Config::singleton()->userPermissionClass->permissions = ['administer CiviCRM']; |
33072bc7 | 451 | $inInd = $this->individualCreate(); |
452 | $outInd = $this->individualCreate(); | |
9099cab3 CW |
453 | $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-03-01']); |
454 | $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]); | |
455 | $this->hookClass->setHook('civicrm_aclWhereClause', [$this, 'aclWhereHookNoResults']); | |
456 | $params = [ | |
33072bc7 | 457 | 'report_id' => 'contribute/lybunt', |
458 | 'yid_value' => 2015, | |
459 | 'yid_op' => 'calendar', | |
9099cab3 | 460 | 'options' => ['metadata' => ['sql']], |
33072bc7 | 461 | 'check_permissions' => 1, |
9099cab3 | 462 | ]; |
33072bc7 | 463 | |
464 | $rows = $this->callAPISuccess('report_template', 'getrows', $params); | |
f4b20644 | 465 | $this->assertEquals(0, $rows['count'], 'Report failed - the sql used to generate the results was ' . print_r($rows['metadata']['sql'], TRUE)); |
33072bc7 | 466 | |
467 | CRM_Utils_Hook::singleton()->reset(); | |
468 | } | |
469 | ||
c160fde8 | 470 | /** |
471 | * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year. | |
f4b20644 | 472 | * |
473 | * @throws \CRM_Core_Exception | |
c160fde8 | 474 | */ |
475 | public function testLybuntReportWithFYData() { | |
476 | $inInd = $this->individualCreate(); | |
477 | $outInd = $this->individualCreate(); | |
9099cab3 CW |
478 | $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-10-01']); |
479 | $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]); | |
480 | $this->callAPISuccess('Setting', 'create', ['fiscalYearStart' => ['M' => 7, 'd' => 1]]); | |
481 | $rows = $this->callAPISuccess('report_template', 'getrows', [ | |
c160fde8 | 482 | 'report_id' => 'contribute/lybunt', |
483 | 'yid_value' => 2015, | |
484 | 'yid_op' => 'fiscal', | |
9099cab3 CW |
485 | 'options' => ['metadata' => ['sql']], |
486 | 'order_bys' => [ | |
487 | [ | |
c160fde8 | 488 | 'column' => 'first_name', |
489 | 'order' => 'ASC', | |
9099cab3 CW |
490 | ], |
491 | ], | |
492 | ]); | |
c160fde8 | 493 | |
f4b20644 | 494 | $this->assertEquals(2, $rows['count'], 'Report failed - the sql used to generate the results was ' . print_r($rows['metadata']['sql'], TRUE)); |
15d9e604 | 495 | |
8f719ee0 | 496 | $expected = preg_replace('/\s+/', ' ', 'COLLATE utf8_unicode_ci AS |
15d9e604 | 497 | SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as cid FROM civicrm_contact contact_civireport INNER JOIN civicrm_contribution contribution_civireport USE index (received_date) ON contribution_civireport.contact_id = contact_civireport.id |
498 | AND contribution_civireport.is_test = 0 | |
499 | AND contribution_civireport.receive_date BETWEEN \'20140701000000\' AND \'20150630235959\' | |
1679e19c | 500 | WHERE contact_civireport.id NOT IN ( |
501 | SELECT cont_exclude.contact_id | |
502 | FROM civicrm_contribution cont_exclude | |
503 | WHERE cont_exclude.receive_date BETWEEN \'2015-7-1\' AND \'20160630235959\') | |
504 | AND ( contribution_civireport.contribution_status_id IN (1) ) | |
505 | GROUP BY contact_civireport.id'); | |
506 | // Exclude whitespace in comparison as we don't care if it changes & this allows us to make the above readable. | |
507 | $whitespacelessSql = preg_replace('/\s+/', ' ', $rows['metadata']['sql'][0]); | |
508 | $this->assertContains($expected, $whitespacelessSql); | |
c160fde8 | 509 | } |
510 | ||
3fd9a92a | 511 | /** |
512 | * Test Lybunt report to check basic inclusion of a contact who gave in the year before the chosen year. | |
f4b20644 | 513 | * |
514 | * @throws \CRM_Core_Exception | |
3fd9a92a | 515 | */ |
516 | public function testLybuntReportWithFYDataOrderByLastYearAmount() { | |
517 | $inInd = $this->individualCreate(); | |
518 | $outInd = $this->individualCreate(); | |
9099cab3 CW |
519 | $this->contributionCreate(['contact_id' => $inInd, 'receive_date' => '2014-10-01']); |
520 | $this->contributionCreate(['contact_id' => $outInd, 'receive_date' => '2015-03-01', 'trxn_id' => NULL, 'invoice_id' => NULL]); | |
521 | $this->callAPISuccess('Setting', 'create', ['fiscalYearStart' => ['M' => 7, 'd' => 1]]); | |
522 | $rows = $this->callAPISuccess('report_template', 'getrows', [ | |
3fd9a92a | 523 | 'report_id' => 'contribute/lybunt', |
524 | 'yid_value' => 2015, | |
525 | 'yid_op' => 'fiscal', | |
9099cab3 CW |
526 | 'options' => ['metadata' => ['sql']], |
527 | 'fields' => ['first_name'], | |
528 | 'order_bys' => [ | |
529 | [ | |
3fd9a92a | 530 | 'column' => 'last_year_total_amount', |
531 | 'order' => 'ASC', | |
9099cab3 CW |
532 | ], |
533 | ], | |
534 | ]); | |
3fd9a92a | 535 | |
f4b20644 | 536 | $this->assertEquals(2, $rows['count'], 'Report failed - the sql used to generate the results was ' . print_r($rows['metadata']['sql'], TRUE)); |
3fd9a92a | 537 | } |
538 | ||
2c6b4783 | 539 | /** |
540 | * Test the group filter works on the contribution summary (with a smart group). | |
eae0f0d9 | 541 | * |
542 | * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests | |
543 | * | |
544 | * @param string $template | |
545 | * Name of the template to test. | |
5a1f1268 | 546 | * |
547 | * @throws \CRM_Core_Exception | |
2c6b4783 | 548 | */ |
eae0f0d9 | 549 | public function testContributionSummaryWithSmartGroupFilter($template) { |
2c6b4783 | 550 | $groupID = $this->setUpPopulatedSmartGroup(); |
9099cab3 | 551 | $rows = $this->callAPISuccess('report_template', 'getrows', [ |
eae0f0d9 | 552 | 'report_id' => $template, |
2c6b4783 | 553 | 'gid_value' => $groupID, |
554 | 'gid_op' => 'in', | |
9099cab3 CW |
555 | 'options' => ['metadata' => ['sql']], |
556 | ]); | |
eae0f0d9 | 557 | $this->assertNumberOfContactsInResult(3, $rows, $template); |
558 | if ($template === 'contribute/summary') { | |
559 | $this->assertEquals(3, $rows['values'][0]['civicrm_contribution_total_amount_count']); | |
560 | } | |
2c6b4783 | 561 | } |
562 | ||
563 | /** | |
eae0f0d9 | 564 | * Test the group filter works on the contribution summary. |
565 | * | |
566 | * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests | |
f4b20644 | 567 | * |
568 | * @param string $template | |
569 | * | |
570 | * @throws \CRM_Core_Exception | |
2c6b4783 | 571 | */ |
eae0f0d9 | 572 | public function testContributionSummaryWithNotINSmartGroupFilter($template) { |
2c6b4783 | 573 | $groupID = $this->setUpPopulatedSmartGroup(); |
9099cab3 | 574 | $rows = $this->callAPISuccess('report_template', 'getrows', [ |
2c6b4783 | 575 | 'report_id' => 'contribute/summary', |
576 | 'gid_value' => $groupID, | |
43c1fa19 | 577 | 'gid_op' => 'notin', |
9099cab3 CW |
578 | 'options' => ['metadata' => ['sql']], |
579 | ]); | |
2c6b4783 | 580 | $this->assertEquals(2, $rows['values'][0]['civicrm_contribution_total_amount_count']); |
e6bab5ea | 581 | } |
2c6b4783 | 582 | |
0f880d50 | 583 | /** |
584 | * Test no fatal on order by per https://lab.civicrm.org/dev/core/issues/739 | |
f4b20644 | 585 | * |
586 | * @throws \CRM_Core_Exception | |
0f880d50 | 587 | */ |
588 | public function testCaseDetailsCaseTypeHeader() { | |
589 | $this->callAPISuccess('report_template', 'getrows', [ | |
590 | 'report_id' => 'case/detail', | |
591 | 'fields' => ['subject' => 1, 'client_sort_name' => 1], | |
39b959db SL |
592 | 'order_bys' => [ |
593 | 1 => [ | |
0f880d50 | 594 | 'column' => 'case_type_title', |
595 | 'order' => 'ASC', | |
596 | 'section' => '1', | |
597 | ], | |
598 | ], | |
599 | ]); | |
600 | } | |
601 | ||
e6bab5ea | 602 | /** |
603 | * Test the group filter works on the contribution summary. | |
f4b20644 | 604 | * |
605 | * @throws \CRM_Core_Exception | |
e6bab5ea | 606 | */ |
607 | public function testContributionDetailSoftCredits() { | |
608 | $contactID = $this->individualCreate(); | |
609 | $contactID2 = $this->individualCreate(); | |
610 | $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]); | |
611 | $template = 'contribute/detail'; | |
9099cab3 | 612 | $rows = $this->callAPISuccess('report_template', 'getrows', [ |
e6bab5ea | 613 | 'report_id' => $template, |
614 | 'contribution_or_soft_value' => 'contributions_only', | |
615 | 'fields' => ['soft_credits' => 1, 'contribution_or_soft' => 1, 'sort_name' => 1], | |
9099cab3 CW |
616 | 'options' => ['metadata' => ['sql']], |
617 | ]); | |
e6bab5ea | 618 | $this->assertEquals( |
619 | "<a href='/index.php?q=civicrm/contact/view&reset=1&cid=" . $contactID2 . "'>Anderson, Anthony</a> $ 5.00", | |
620 | $rows['values'][0]['civicrm_contribution_soft_credits'] | |
621 | ); | |
2c6b4783 | 622 | } |
623 | ||
d70ada18 | 624 | /** |
625 | * Test the amount column is populated on soft credit details. | |
f4b20644 | 626 | * |
627 | * @throws \CRM_Core_Exception | |
d70ada18 | 628 | */ |
629 | public function testContributionDetailSoftCreditsOnly() { | |
630 | $contactID = $this->individualCreate(); | |
631 | $contactID2 = $this->individualCreate(); | |
632 | $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]); | |
633 | $template = 'contribute/detail'; | |
9099cab3 | 634 | $rows = $this->callAPISuccess('report_template', 'getrows', [ |
d70ada18 | 635 | 'report_id' => $template, |
636 | 'contribution_or_soft_value' => 'soft_credits_only', | |
637 | 'fields' => [ | |
638 | 'sort_name' => '1', | |
639 | 'email' => '1', | |
640 | 'financial_type_id' => '1', | |
641 | 'receive_date' => '1', | |
642 | 'total_amount' => '1', | |
643 | ], | |
9099cab3 CW |
644 | 'options' => ['metadata' => ['sql', 'labels']], |
645 | ]); | |
d70ada18 | 646 | foreach (array_keys($rows['metadata']['labels']) as $header) { |
f4b20644 | 647 | $this->assertNotEmpty($rows['values'][0][$header]); |
d70ada18 | 648 | } |
649 | } | |
650 | ||
2c6b4783 | 651 | /** |
eae0f0d9 | 652 | * Test the group filter works on the various reports. |
2c6b4783 | 653 | * |
eae0f0d9 | 654 | * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests |
2c6b4783 | 655 | * |
656 | * @param string $template | |
657 | * Report template unique identifier. | |
f4b20644 | 658 | * |
659 | * @throws \CRM_Core_Exception | |
2c6b4783 | 660 | */ |
eae0f0d9 | 661 | public function testReportsWithNonSmartGroupFilter($template) { |
2c6b4783 | 662 | $groupID = $this->setUpPopulatedGroup(); |
9099cab3 | 663 | $rows = $this->callAPISuccess('report_template', 'getrows', [ |
2c6b4783 | 664 | 'report_id' => $template, |
9099cab3 | 665 | 'gid_value' => [$groupID], |
2c6b4783 | 666 | 'gid_op' => 'in', |
9099cab3 CW |
667 | 'options' => ['metadata' => ['sql']], |
668 | ]); | |
2c6b4783 | 669 | $this->assertNumberOfContactsInResult(1, $rows, $template); |
670 | } | |
671 | ||
672 | /** | |
673 | * Assert the included results match the expected. | |
674 | * | |
675 | * There may or may not be a group by in play so the assertion varies a little. | |
676 | * | |
677 | * @param int $numberExpected | |
678 | * @param array $rows | |
679 | * Rows returned from the report. | |
680 | * @param string $template | |
681 | */ | |
682 | protected function assertNumberOfContactsInResult($numberExpected, $rows, $template) { | |
683 | if (isset($rows['values'][0]['civicrm_contribution_total_amount_count'])) { | |
684 | $this->assertEquals($numberExpected, $rows['values'][0]['civicrm_contribution_total_amount_count'], 'wrong row count in ' . $template); | |
685 | } | |
686 | else { | |
f4b20644 | 687 | $this->assertCount($numberExpected, $rows['values'], 'wrong row count in ' . $template); |
2c6b4783 | 688 | } |
689 | } | |
690 | ||
691 | /** | |
692 | * Test the group filter works on the contribution summary when 2 groups are involved. | |
bd2913d3 | 693 | * |
694 | * @throws \CRM_Core_Exception | |
2c6b4783 | 695 | */ |
696 | public function testContributionSummaryWithTwoGroups() { | |
697 | $groupID = $this->setUpPopulatedGroup(); | |
698 | $groupID2 = $this->setUpPopulatedSmartGroup(); | |
9099cab3 | 699 | $rows = $this->callAPISuccess('report_template', 'getrows', [ |
2c6b4783 | 700 | 'report_id' => 'contribute/summary', |
9099cab3 | 701 | 'gid_value' => [$groupID, $groupID2], |
2c6b4783 | 702 | 'gid_op' => 'in', |
9099cab3 CW |
703 | 'options' => ['metadata' => ['sql']], |
704 | ]); | |
2c6b4783 | 705 | $this->assertEquals(4, $rows['values'][0]['civicrm_contribution_total_amount_count']); |
706 | } | |
707 | ||
bd2913d3 | 708 | /** |
709 | * Test we don't get a fatal grouping by only contribution status id. | |
710 | * | |
711 | * @throws \CRM_Core_Exception | |
712 | */ | |
713 | public function testContributionSummaryGroupByContributionStatus() { | |
714 | $params = [ | |
715 | 'report_id' => 'contribute/summary', | |
716 | 'fields' => ['total_amount' => 1, 'country_id' => 1], | |
717 | 'group_bys' => ['contribution_status_id' => 1], | |
718 | 'options' => ['metadata' => ['sql']], | |
719 | ]; | |
720 | $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql']; | |
721 | $this->assertContains('GROUP BY contribution_civireport.contribution_status_id WITH ROLLUP', $rowsSql[0]); | |
722 | $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql']; | |
723 | $this->assertContains('GROUP BY contribution_civireport.contribution_status_id, currency', $statsSql[2]); | |
724 | } | |
725 | ||
726 | /** | |
727 | * Test we don't get a fatal grouping by only contribution status id. | |
728 | * | |
729 | * @throws \CRM_Core_Exception | |
730 | */ | |
731 | public function testContributionSummaryGroupByYearFrequency() { | |
732 | $params = [ | |
733 | 'report_id' => 'contribute/summary', | |
734 | 'fields' => ['total_amount' => 1, 'country_id' => 1], | |
735 | 'group_bys' => ['receive_date' => 1], | |
736 | 'group_bys_freq' => ['receive_date' => 'YEAR'], | |
737 | 'options' => ['metadata' => ['sql']], | |
738 | ]; | |
739 | $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql']; | |
740 | $this->assertContains('GROUP BY YEAR(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]); | |
741 | $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql']; | |
742 | $this->assertContains('GROUP BY YEAR(contribution_civireport.receive_date), currency', $statsSql[2]); | |
743 | } | |
744 | ||
745 | /** | |
746 | * Test we don't get a fatal grouping with QUARTER frequency. | |
747 | * | |
748 | * @throws \CRM_Core_Exception | |
749 | */ | |
750 | public function testContributionSummaryGroupByYearQuarterFrequency() { | |
751 | $params = [ | |
752 | 'report_id' => 'contribute/summary', | |
753 | 'fields' => ['total_amount' => 1, 'country_id' => 1], | |
754 | 'group_bys' => ['receive_date' => 1], | |
755 | 'group_bys_freq' => ['receive_date' => 'QUARTER'], | |
756 | 'options' => ['metadata' => ['sql']], | |
757 | ]; | |
758 | $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql']; | |
759 | $this->assertContains('GROUP BY YEAR(contribution_civireport.receive_date), QUARTER(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]); | |
760 | $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql']; | |
761 | $this->assertContains('GROUP BY YEAR(contribution_civireport.receive_date), QUARTER(contribution_civireport.receive_date), currency', $statsSql[2]); | |
762 | } | |
763 | ||
764 | /** | |
765 | * Test we don't get a fatal grouping with QUARTER frequency. | |
766 | * | |
767 | * @throws \CRM_Core_Exception | |
768 | */ | |
769 | public function testContributionSummaryGroupByDateFrequency() { | |
770 | $params = [ | |
771 | 'report_id' => 'contribute/summary', | |
772 | 'fields' => ['total_amount' => 1, 'country_id' => 1], | |
773 | 'group_bys' => ['receive_date' => 1], | |
774 | 'group_bys_freq' => ['receive_date' => 'DATE'], | |
775 | 'options' => ['metadata' => ['sql']], | |
776 | ]; | |
777 | $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql']; | |
778 | $this->assertContains('GROUP BY DATE(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]); | |
779 | $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql']; | |
780 | $this->assertContains('GROUP BY DATE(contribution_civireport.receive_date), currency', $statsSql[2]); | |
781 | } | |
782 | ||
783 | /** | |
784 | * Test we don't get a fatal grouping with QUARTER frequency. | |
785 | * | |
786 | * @throws \CRM_Core_Exception | |
787 | */ | |
788 | public function testContributionSummaryGroupByWeekFrequency() { | |
789 | $params = [ | |
790 | 'report_id' => 'contribute/summary', | |
791 | 'fields' => ['total_amount' => 1, 'country_id' => 1], | |
792 | 'group_bys' => ['receive_date' => 1], | |
793 | 'group_bys_freq' => ['receive_date' => 'YEARWEEK'], | |
794 | 'options' => ['metadata' => ['sql']], | |
795 | ]; | |
796 | $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql']; | |
797 | $this->assertContains('GROUP BY YEARWEEK(contribution_civireport.receive_date) WITH ROLLUP', $rowsSql[0]); | |
798 | $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql']; | |
799 | $this->assertContains('GROUP BY YEARWEEK(contribution_civireport.receive_date), currency', $statsSql[2]); | |
800 | } | |
801 | ||
27367f58 | 802 | /** |
803 | * CRM-20640: Test the group filter works on the contribution summary when a single contact in 2 groups. | |
bd2913d3 | 804 | * |
805 | * @throws \CRM_Core_Exception | |
27367f58 | 806 | */ |
807 | public function testContributionSummaryWithSingleContactsInTwoGroups() { | |
808 | list($groupID1, $individualID) = $this->setUpPopulatedGroup(TRUE); | |
809 | // create second group and add the individual to it. | |
f4b20644 | 810 | $groupID2 = $this->groupCreate(['name' => 'test_group', 'title' => 'test_title']); |
9099cab3 | 811 | $this->callAPISuccess('GroupContact', 'create', [ |
27367f58 | 812 | 'group_id' => $groupID2, |
813 | 'contact_id' => $individualID, | |
814 | 'status' => 'Added', | |
9099cab3 | 815 | ]); |
27367f58 | 816 | |
9099cab3 | 817 | $rows = $this->callAPISuccess('report_template', 'getrows', [ |
27367f58 | 818 | 'report_id' => 'contribute/summary', |
9099cab3 | 819 | 'gid_value' => [$groupID1, $groupID2], |
27367f58 | 820 | 'gid_op' => 'in', |
9099cab3 CW |
821 | 'options' => ['metadata' => ['sql']], |
822 | ]); | |
27367f58 | 823 | $this->assertEquals(1, $rows['count']); |
824 | } | |
825 | ||
2c6b4783 | 826 | /** |
827 | * Test the group filter works on the contribution summary when 2 groups are involved. | |
5a1f1268 | 828 | * |
829 | * @throws \CRM_Core_Exception | |
2c6b4783 | 830 | */ |
831 | public function testContributionSummaryWithTwoGroupsWithIntersection() { | |
832 | $groups = $this->setUpIntersectingGroups(); | |
833 | ||
9099cab3 | 834 | $rows = $this->callAPISuccess('report_template', 'getrows', [ |
2c6b4783 | 835 | 'report_id' => 'contribute/summary', |
836 | 'gid_value' => $groups, | |
837 | 'gid_op' => 'in', | |
9099cab3 CW |
838 | 'options' => ['metadata' => ['sql']], |
839 | ]); | |
2c6b4783 | 840 | $this->assertEquals(7, $rows['values'][0]['civicrm_contribution_total_amount_count']); |
841 | } | |
842 | ||
5a1f1268 | 843 | /** |
844 | * Test date field is correctly handled. | |
845 | * | |
846 | * @throws \CRM_Core_Exception | |
847 | */ | |
848 | public function testContributionSummaryDateFields() { | |
849 | $sql = $this->callAPISuccess('report_template', 'getrows', [ | |
850 | 'report_id' => 'contribute/summary', | |
851 | 'thankyou_date_relative' => '0', | |
852 | 'thankyou_date_from' => '2020-03-01 00:00:00', | |
853 | 'thankyou_date_to' => '2020-03-31 23:59:59', | |
854 | 'options' => ['metadata' => ['sql']], | |
855 | ])['metadata']['sql']; | |
856 | $expectedSql = 'SELECT contact_civireport.id as civicrm_contact_id, DATE_SUB(contribution_civireport.receive_date, INTERVAL (DAYOFMONTH(contribution_civireport.receive_date)-1) DAY) as civicrm_contribution_receive_date_start, MONTH(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_subtotal, MONTHNAME(contribution_civireport.receive_date) AS civicrm_contribution_receive_date_interval, contribution_civireport.currency as civicrm_contribution_currency, COUNT(contribution_civireport.total_amount) as civicrm_contribution_total_amount_count, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount_sum, ROUND(AVG(contribution_civireport.total_amount),2) as civicrm_contribution_total_amount_avg, address_civireport.country_id as civicrm_address_country_id FROM civicrm_contact contact_civireport | |
857 | INNER JOIN civicrm_contribution contribution_civireport | |
858 | ON contact_civireport.id = contribution_civireport.contact_id AND | |
859 | contribution_civireport.is_test = 0 | |
860 | LEFT JOIN civicrm_contribution_soft contribution_soft_civireport | |
861 | ON contribution_soft_civireport.contribution_id = contribution_civireport.id AND contribution_soft_civireport.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = contribution_civireport.id) | |
862 | LEFT JOIN civicrm_financial_type financial_type_civireport | |
863 | ON contribution_civireport.financial_type_id =financial_type_civireport.id | |
864 | ||
865 | LEFT JOIN civicrm_address address_civireport | |
866 | ON (contact_civireport.id = | |
867 | address_civireport.contact_id) AND | |
868 | address_civireport.is_primary = 1 | |
869 | WHERE ( contribution_civireport.thankyou_date >= 20200301000000) AND ( contribution_civireport.thankyou_date <= 20200331235959) AND ( contribution_civireport.contribution_status_id IN (1) ) GROUP BY EXTRACT(YEAR_MONTH FROM contribution_civireport.receive_date), contribution_civireport.contribution_status_id LIMIT 25'; | |
870 | $this->assertLike($expectedSql, $sql[0]); | |
871 | } | |
872 | ||
2c6b4783 | 873 | /** |
874 | * Set up a smart group for testing. | |
875 | * | |
876 | * The smart group includes all Households by filter. In addition an individual | |
877 | * is created and hard-added and an individual is created that is not added. | |
878 | * | |
879 | * One household is hard-added as well as being in the filter. | |
880 | * | |
881 | * This gives us a range of scenarios for testing contacts are included only once | |
882 | * whenever they are hard-added or in the criteria. | |
883 | * | |
884 | * @return int | |
bd2913d3 | 885 | * @throws \CRM_Core_Exception |
2c6b4783 | 886 | */ |
887 | public function setUpPopulatedSmartGroup() { | |
888 | $household1ID = $this->householdCreate(); | |
889 | $individual1ID = $this->individualCreate(); | |
890 | $householdID = $this->householdCreate(); | |
891 | $individualID = $this->individualCreate(); | |
892 | $individualIDRemoved = $this->individualCreate(); | |
f4b20644 | 893 | $groupID = $this->smartGroupCreate([], ['name' => 'smart_group', 'title' => 'smart group']); |
9099cab3 | 894 | $this->callAPISuccess('GroupContact', 'create', [ |
2c6b4783 | 895 | 'group_id' => $groupID, |
896 | 'contact_id' => $individualIDRemoved, | |
897 | 'status' => 'Removed', | |
9099cab3 CW |
898 | ]); |
899 | $this->callAPISuccess('GroupContact', 'create', [ | |
2c6b4783 | 900 | 'group_id' => $groupID, |
901 | 'contact_id' => $individualID, | |
902 | 'status' => 'Added', | |
9099cab3 CW |
903 | ]); |
904 | $this->callAPISuccess('GroupContact', 'create', [ | |
2c6b4783 | 905 | 'group_id' => $groupID, |
906 | 'contact_id' => $householdID, | |
907 | 'status' => 'Added', | |
9099cab3 CW |
908 | ]); |
909 | foreach ([$household1ID, $individual1ID, $householdID, $individualID, $individualIDRemoved] as $contactID) { | |
910 | $this->contributionCreate(['contact_id' => $contactID, 'invoice_id' => '', 'trxn_id' => '']); | |
911 | $this->contactMembershipCreate(['contact_id' => $contactID]); | |
2c6b4783 | 912 | } |
913 | ||
914 | // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache. | |
0626851e | 915 | CRM_Contact_BAO_GroupContactCache::clearGroupContactCache($groupID); |
2c6b4783 | 916 | return $groupID; |
917 | } | |
918 | ||
919 | /** | |
eae0f0d9 | 920 | * Set up a static group for testing. |
2c6b4783 | 921 | * |
eae0f0d9 | 922 | * An individual is created and hard-added and an individual is created that is not added. |
2c6b4783 | 923 | * |
924 | * This gives us a range of scenarios for testing contacts are included only once | |
925 | * whenever they are hard-added or in the criteria. | |
926 | * | |
27367f58 | 927 | * @param bool $returnAddedContact |
928 | * | |
2c6b4783 | 929 | * @return int |
bd2913d3 | 930 | * @throws \CRM_Core_Exception |
2c6b4783 | 931 | */ |
27367f58 | 932 | public function setUpPopulatedGroup($returnAddedContact = FALSE) { |
2c6b4783 | 933 | $individual1ID = $this->individualCreate(); |
934 | $individualID = $this->individualCreate(); | |
935 | $individualIDRemoved = $this->individualCreate(); | |
9099cab3 CW |
936 | $groupID = $this->groupCreate(['name' => uniqid(), 'title' => uniqid()]); |
937 | $this->callAPISuccess('GroupContact', 'create', [ | |
2c6b4783 | 938 | 'group_id' => $groupID, |
939 | 'contact_id' => $individualIDRemoved, | |
940 | 'status' => 'Removed', | |
9099cab3 CW |
941 | ]); |
942 | $this->callAPISuccess('GroupContact', 'create', [ | |
2c6b4783 | 943 | 'group_id' => $groupID, |
944 | 'contact_id' => $individualID, | |
945 | 'status' => 'Added', | |
9099cab3 | 946 | ]); |
2c6b4783 | 947 | |
9099cab3 CW |
948 | foreach ([$individual1ID, $individualID, $individualIDRemoved] as $contactID) { |
949 | $this->contributionCreate(['contact_id' => $contactID, 'invoice_id' => '', 'trxn_id' => '']); | |
950 | $this->contactMembershipCreate(['contact_id' => $contactID]); | |
2c6b4783 | 951 | } |
952 | ||
953 | // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache. | |
0626851e | 954 | CRM_Contact_BAO_GroupContactCache::clearGroupContactCache($groupID); |
27367f58 | 955 | |
956 | if ($returnAddedContact) { | |
9099cab3 | 957 | return [$groupID, $individualID]; |
27367f58 | 958 | } |
959 | ||
2c6b4783 | 960 | return $groupID; |
961 | } | |
962 | ||
963 | /** | |
964 | * @return array | |
bd2913d3 | 965 | * |
966 | * @throws \CRM_Core_Exception | |
2c6b4783 | 967 | */ |
968 | public function setUpIntersectingGroups() { | |
969 | $groupID = $this->setUpPopulatedGroup(); | |
970 | $groupID2 = $this->setUpPopulatedSmartGroup(); | |
971 | $addedToBothIndividualID = $this->individualCreate(); | |
972 | $removedFromBothIndividualID = $this->individualCreate(); | |
973 | $addedToSmartGroupRemovedFromOtherIndividualID = $this->individualCreate(); | |
974 | $removedFromSmartGroupAddedToOtherIndividualID = $this->individualCreate(); | |
9099cab3 | 975 | $this->callAPISuccess('GroupContact', 'create', [ |
2c6b4783 | 976 | 'group_id' => $groupID, |
977 | 'contact_id' => $addedToBothIndividualID, | |
978 | 'status' => 'Added', | |
9099cab3 CW |
979 | ]); |
980 | $this->callAPISuccess('GroupContact', 'create', [ | |
2c6b4783 | 981 | 'group_id' => $groupID2, |
982 | 'contact_id' => $addedToBothIndividualID, | |
983 | 'status' => 'Added', | |
9099cab3 CW |
984 | ]); |
985 | $this->callAPISuccess('GroupContact', 'create', [ | |
2c6b4783 | 986 | 'group_id' => $groupID, |
987 | 'contact_id' => $removedFromBothIndividualID, | |
988 | 'status' => 'Removed', | |
9099cab3 CW |
989 | ]); |
990 | $this->callAPISuccess('GroupContact', 'create', [ | |
2c6b4783 | 991 | 'group_id' => $groupID2, |
992 | 'contact_id' => $removedFromBothIndividualID, | |
993 | 'status' => 'Removed', | |
9099cab3 CW |
994 | ]); |
995 | $this->callAPISuccess('GroupContact', 'create', [ | |
2c6b4783 | 996 | 'group_id' => $groupID2, |
997 | 'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID, | |
998 | 'status' => 'Added', | |
9099cab3 CW |
999 | ]); |
1000 | $this->callAPISuccess('GroupContact', 'create', [ | |
2c6b4783 | 1001 | 'group_id' => $groupID, |
1002 | 'contact_id' => $addedToSmartGroupRemovedFromOtherIndividualID, | |
1003 | 'status' => 'Removed', | |
9099cab3 CW |
1004 | ]); |
1005 | $this->callAPISuccess('GroupContact', 'create', [ | |
2c6b4783 | 1006 | 'group_id' => $groupID, |
1007 | 'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID, | |
1008 | 'status' => 'Added', | |
9099cab3 CW |
1009 | ]); |
1010 | $this->callAPISuccess('GroupContact', 'create', [ | |
2c6b4783 | 1011 | 'group_id' => $groupID2, |
1012 | 'contact_id' => $removedFromSmartGroupAddedToOtherIndividualID, | |
1013 | 'status' => 'Removed', | |
9099cab3 | 1014 | ]); |
2c6b4783 | 1015 | |
9099cab3 | 1016 | foreach ([ |
39b959db SL |
1017 | $addedToBothIndividualID, |
1018 | $removedFromBothIndividualID, | |
1019 | $addedToSmartGroupRemovedFromOtherIndividualID, | |
1020 | $removedFromSmartGroupAddedToOtherIndividualID, | |
9099cab3 CW |
1021 | ] as $contactID) { |
1022 | $this->contributionCreate([ | |
2c6b4783 | 1023 | 'contact_id' => $contactID, |
1024 | 'invoice_id' => '', | |
1025 | 'trxn_id' => '', | |
9099cab3 | 1026 | ]); |
2c6b4783 | 1027 | } |
9099cab3 | 1028 | return [$groupID, $groupID2]; |
2c6b4783 | 1029 | } |
1030 | ||
45f8590c PN |
1031 | /** |
1032 | * Test Deferred Revenue Report. | |
fc7106d2 | 1033 | * |
1034 | * @throws \CRM_Core_Exception | |
45f8590c PN |
1035 | */ |
1036 | public function testDeferredRevenueReport() { | |
1037 | $indv1 = $this->individualCreate(); | |
1038 | $indv2 = $this->individualCreate(); | |
fc7106d2 | 1039 | Civi::settings()->set('deferred_revenue_enabled', TRUE); |
45f8590c | 1040 | $this->contributionCreate( |
9099cab3 | 1041 | [ |
45f8590c PN |
1042 | 'contact_id' => $indv1, |
1043 | 'receive_date' => '2016-10-01', | |
1044 | 'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+3 month')), | |
1045 | 'financial_type_id' => 2, | |
9099cab3 | 1046 | ] |
45f8590c PN |
1047 | ); |
1048 | $this->contributionCreate( | |
9099cab3 | 1049 | [ |
45f8590c PN |
1050 | 'contact_id' => $indv1, |
1051 | 'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+22 month')), | |
1052 | 'financial_type_id' => 4, | |
1053 | 'trxn_id' => NULL, | |
1054 | 'invoice_id' => NULL, | |
9099cab3 | 1055 | ] |
45f8590c PN |
1056 | ); |
1057 | $this->contributionCreate( | |
9099cab3 | 1058 | [ |
45f8590c PN |
1059 | 'contact_id' => $indv2, |
1060 | 'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+1 month')), | |
1061 | 'financial_type_id' => 4, | |
1062 | 'trxn_id' => NULL, | |
1063 | 'invoice_id' => NULL, | |
9099cab3 | 1064 | ] |
45f8590c PN |
1065 | ); |
1066 | $this->contributionCreate( | |
9099cab3 | 1067 | [ |
45f8590c PN |
1068 | 'contact_id' => $indv2, |
1069 | 'receive_date' => '2016-03-01', | |
1070 | 'revenue_recognition_date' => date('Y-m-t', strtotime(date('ymd') . '+4 month')), | |
1071 | 'financial_type_id' => 2, | |
1072 | 'trxn_id' => NULL, | |
1073 | 'invoice_id' => NULL, | |
9099cab3 | 1074 | ] |
45f8590c | 1075 | ); |
9099cab3 | 1076 | $rows = $this->callAPISuccess('report_template', 'getrows', [ |
45f8590c | 1077 | 'report_id' => 'contribute/deferredrevenue', |
9099cab3 | 1078 | ]); |
f4b20644 | 1079 | $this->assertEquals(2, $rows['count'], 'Report failed to get row count'); |
9099cab3 | 1080 | $count = [2, 1]; |
45f8590c | 1081 | foreach ($rows['values'] as $row) { |
f4b20644 | 1082 | $this->assertCount(array_pop($count), $row['rows'], 'Report failed to get row count'); |
45f8590c PN |
1083 | } |
1084 | } | |
d7a896b4 | 1085 | |
1086 | /** | |
1087 | * Test the custom data order by works when not in select. | |
1088 | * | |
1089 | * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests | |
1090 | * | |
1091 | * @param string $template | |
1092 | * Report template unique identifier. | |
1093 | * | |
f4b20644 | 1094 | * @throws \API_Exception |
d7a896b4 | 1095 | * @throws \CRM_Core_Exception |
f4b20644 | 1096 | * @throws \Civi\API\Exception\UnauthorizedException |
d7a896b4 | 1097 | */ |
1098 | public function testReportsCustomDataOrderBy($template) { | |
1099 | $this->entity = 'Contact'; | |
1100 | $this->createCustomGroupWithFieldOfType(); | |
1101 | $this->callAPISuccess('report_template', 'getrows', [ | |
1102 | 'report_id' => $template, | |
1103 | 'contribution_or_soft_value' => 'contributions_only', | |
1104 | 'order_bys' => [['column' => 'custom_' . $this->ids['CustomField']['text'], 'order' => 'ASC']], | |
1105 | ]); | |
1106 | } | |
45f8590c | 1107 | |
5e3dec81 JP |
1108 | /** |
1109 | * Test the group filter works on the various reports. | |
1110 | * | |
1111 | * @dataProvider getMembershipAndContributionReportTemplatesForGroupTests | |
1112 | * | |
1113 | * @param string $template | |
1114 | * Report template unique identifier. | |
bd2913d3 | 1115 | * |
1116 | * @throws \CRM_Core_Exception | |
5e3dec81 JP |
1117 | */ |
1118 | public function testReportsWithNoTInSmartGroupFilter($template) { | |
1119 | $groupID = $this->setUpPopulatedGroup(); | |
9099cab3 | 1120 | $rows = $this->callAPISuccess('report_template', 'getrows', [ |
5e3dec81 | 1121 | 'report_id' => $template, |
9099cab3 | 1122 | 'gid_value' => [$groupID], |
5e3dec81 | 1123 | 'gid_op' => 'notin', |
9099cab3 CW |
1124 | 'options' => ['metadata' => ['sql']], |
1125 | ]); | |
5e3dec81 JP |
1126 | $this->assertNumberOfContactsInResult(2, $rows, $template); |
1127 | } | |
1128 | ||
04076b1b | 1129 | /** |
1130 | * Test we don't get a fatal grouping with various frequencies. | |
1131 | * | |
1132 | * @throws \CRM_Core_Exception | |
1133 | */ | |
1134 | public function testActivitySummaryGroupByFrequency() { | |
1135 | $this->createContactsWithActivities(); | |
1136 | foreach (['MONTH', 'YEARWEEK', 'QUARTER', 'YEAR'] as $frequency) { | |
1137 | $params = [ | |
1138 | 'report_id' => 'activitySummary', | |
1139 | 'fields' => [ | |
1140 | 'activity_type_id' => 1, | |
1141 | 'duration' => 1, | |
1142 | // id is "total activities", which is required by default(?) | |
1143 | 'id' => 1, | |
1144 | ], | |
1145 | 'group_bys' => ['activity_date_time' => 1], | |
1146 | 'group_bys_freq' => ['activity_date_time' => $frequency], | |
1147 | 'options' => ['metadata' => ['sql']], | |
1148 | ]; | |
1149 | $rowsSql = $this->callAPISuccess('report_template', 'getrows', $params)['metadata']['sql']; | |
1150 | $statsSql = $this->callAPISuccess('report_template', 'getstatistics', $params)['metadata']['sql']; | |
1151 | switch ($frequency) { | |
1152 | case 'YEAR': | |
1153 | // Year only contains one grouping. | |
1154 | // Also note the extra space. | |
1155 | $this->assertContains('GROUP BY YEAR(activity_civireport.activity_date_time)', $rowsSql[1], "Failed for frequency $frequency"); | |
1156 | $this->assertContains('GROUP BY YEAR(activity_civireport.activity_date_time)', $statsSql[1], "Failed for frequency $frequency"); | |
1157 | break; | |
1158 | ||
1159 | default: | |
1160 | $this->assertContains("GROUP BY YEAR(activity_civireport.activity_date_time), {$frequency}(activity_civireport.activity_date_time)", $rowsSql[1], "Failed for frequency $frequency"); | |
1161 | $this->assertContains("GROUP BY YEAR(activity_civireport.activity_date_time), {$frequency}(activity_civireport.activity_date_time)", $statsSql[1], "Failed for frequency $frequency"); | |
1162 | break; | |
1163 | } | |
1164 | } | |
1165 | } | |
1166 | ||
5a14305b | 1167 | /** |
d5625bc9 | 1168 | * Test activity details report - requiring all current fields to be output. |
f4b20644 | 1169 | * |
1170 | * @throws \CRM_Core_Exception | |
5a14305b | 1171 | */ |
d5625bc9 | 1172 | public function testActivityDetails() { |
5a14305b | 1173 | $this->createContactsWithActivities(); |
1174 | $fields = [ | |
1175 | 'contact_source' => '1', | |
1176 | 'contact_assignee' => '1', | |
1177 | 'contact_target' => '1', | |
1178 | 'contact_source_email' => '1', | |
1179 | 'contact_assignee_email' => '1', | |
1180 | 'contact_target_email' => '1', | |
1181 | 'contact_source_phone' => '1', | |
1182 | 'contact_assignee_phone' => '1', | |
1183 | 'contact_target_phone' => '1', | |
1184 | 'activity_type_id' => '1', | |
1185 | 'activity_subject' => '1', | |
1186 | 'activity_date_time' => '1', | |
1187 | 'status_id' => '1', | |
1188 | 'duration' => '1', | |
1189 | 'location' => '1', | |
1190 | 'details' => '1', | |
1191 | 'priority_id' => '1', | |
1192 | 'result' => '1', | |
1193 | 'engagement_level' => '1', | |
1194 | 'address_name' => '1', | |
1195 | 'street_address' => '1', | |
1196 | 'supplemental_address_1' => '1', | |
1197 | 'supplemental_address_2' => '1', | |
1198 | 'supplemental_address_3' => '1', | |
1199 | 'street_number' => '1', | |
1200 | 'street_name' => '1', | |
1201 | 'street_unit' => '1', | |
1202 | 'city' => '1', | |
1203 | 'postal_code' => '1', | |
1204 | 'postal_code_suffix' => '1', | |
1205 | 'country_id' => '1', | |
1206 | 'state_province_id' => '1', | |
1207 | 'county_id' => '1', | |
1208 | ]; | |
1209 | $params = [ | |
1210 | 'fields' => $fields, | |
1211 | 'current_user_op' => 'eq', | |
1212 | 'current_user_value' => '0', | |
1213 | 'include_case_activities_op' => 'eq', | |
1214 | 'include_case_activities_value' => 0, | |
87e5be4b | 1215 | 'order_bys' => [ |
1216 | 1 => ['column' => 'activity_date_time', 'order' => 'ASC'], | |
1217 | 2 => ['column' => 'activity_type_id', 'order' => 'ASC'], | |
1218 | ], | |
5a14305b | 1219 | ]; |
1220 | ||
1221 | $params['report_id'] = 'Activity'; | |
1222 | ||
1223 | $rows = $this->callAPISuccess('report_template', 'getrows', $params)['values']; | |
1224 | $expected = [ | |
1225 | 'civicrm_contact_contact_source' => 'Łąchowski-Roberts, Anthony', | |
1226 | 'civicrm_contact_contact_assignee' => '<a title=\'View Contact Summary for this Contact\' href=\'/index.php?q=civicrm/contact/view&reset=1&cid=4\'>Łąchowski-Roberts, Anthony</a>', | |
1227 | 'civicrm_contact_contact_target' => '<a title=\'View Contact Summary for this Contact\' href=\'/index.php?q=civicrm/contact/view&reset=1&cid=3\'>Brzęczysław, Anthony</a>; <a title=\'View Contact Summary for this Contact\' href=\'/index.php?q=civicrm/contact/view&reset=1&cid=4\'>Łąchowski-Roberts, Anthony</a>', | |
1228 | 'civicrm_contact_contact_source_id' => $this->contactIDs[2], | |
1229 | 'civicrm_contact_contact_assignee_id' => $this->contactIDs[1], | |
1230 | 'civicrm_contact_contact_target_id' => $this->contactIDs[0] . ';' . $this->contactIDs[1], | |
1231 | 'civicrm_email_contact_source_email' => 'anthony_anderson@civicrm.org', | |
1232 | 'civicrm_email_contact_assignee_email' => 'anthony_anderson@civicrm.org', | |
87e5be4b | 1233 | 'civicrm_email_contact_target_email' => 'techo@spying.com;anthony_anderson@civicrm.org', |
5a14305b | 1234 | 'civicrm_phone_contact_source_phone' => NULL, |
1235 | 'civicrm_phone_contact_assignee_phone' => NULL, | |
1236 | 'civicrm_phone_contact_target_phone' => NULL, | |
1237 | 'civicrm_activity_id' => '1', | |
1238 | 'civicrm_activity_source_record_id' => NULL, | |
1239 | 'civicrm_activity_activity_type_id' => 'Meeting', | |
1240 | 'civicrm_activity_activity_subject' => 'Very secret meeting', | |
f4b20644 | 1241 | 'civicrm_activity_activity_date_time' => date('Y-m-d 23:59:58'), |
5a14305b | 1242 | 'civicrm_activity_status_id' => 'Scheduled', |
1243 | 'civicrm_activity_duration' => '120', | |
1244 | 'civicrm_activity_location' => 'Pennsylvania', | |
1245 | 'civicrm_activity_details' => 'a test activity', | |
1246 | 'civicrm_activity_priority_id' => 'Normal', | |
1247 | 'civicrm_address_address_name' => NULL, | |
1248 | 'civicrm_address_street_address' => NULL, | |
1249 | 'civicrm_address_supplemental_address_1' => NULL, | |
1250 | 'civicrm_address_supplemental_address_2' => NULL, | |
1251 | 'civicrm_address_supplemental_address_3' => NULL, | |
1252 | 'civicrm_address_street_number' => NULL, | |
1253 | 'civicrm_address_street_name' => NULL, | |
1254 | 'civicrm_address_street_unit' => NULL, | |
1255 | 'civicrm_address_city' => NULL, | |
1256 | 'civicrm_address_postal_code' => NULL, | |
1257 | 'civicrm_address_postal_code_suffix' => NULL, | |
1258 | 'civicrm_address_country_id' => NULL, | |
1259 | 'civicrm_address_state_province_id' => NULL, | |
1260 | 'civicrm_address_county_id' => NULL, | |
1261 | 'civicrm_contact_contact_source_link' => '/index.php?q=civicrm/contact/view&reset=1&cid=' . $this->contactIDs[2], | |
1262 | 'civicrm_contact_contact_source_hover' => 'View Contact Summary for this Contact', | |
1263 | 'civicrm_activity_activity_type_id_hover' => 'View Activity Record', | |
5a14305b | 1264 | ]; |
1265 | $row = $rows[0]; | |
1266 | // This link is not relative - skip for now | |
1267 | unset($row['civicrm_activity_activity_type_id_link']); | |
ab1ce467 | 1268 | if ($row['civicrm_email_contact_target_email'] === 'anthony_anderson@civicrm.org;techo@spying.com') { |
87e5be4b | 1269 | // order is unpredictable |
1270 | $expected['civicrm_email_contact_target_email'] = 'anthony_anderson@civicrm.org;techo@spying.com'; | |
1271 | } | |
5a14305b | 1272 | |
1273 | $this->assertEquals($expected, $row); | |
1274 | } | |
1275 | ||
1abf301c JG |
1276 | /** |
1277 | * Activity Details report has some whack-a-mole to fix when filtering on null/not null. | |
f4b20644 | 1278 | * |
1279 | * @throws \CRM_Core_Exception | |
1abf301c JG |
1280 | */ |
1281 | public function testActivityDetailsNullFilters() { | |
1282 | $this->createContactsWithActivities(); | |
1283 | $params = [ | |
1284 | 'report_id' => 'activity', | |
1285 | 'location_op' => 'nll', | |
1286 | 'location_value' => '', | |
1287 | ]; | |
1288 | $rowsWithoutLocation = $this->callAPISuccess('report_template', 'getrows', $params)['values']; | |
1289 | $this->assertEmpty($rowsWithoutLocation); | |
1290 | $params['location_op'] = 'nnll'; | |
1291 | $rowsWithLocation = $this->callAPISuccess('report_template', 'getrows', $params)['values']; | |
1292 | $this->assertCount(1, $rowsWithLocation); | |
1293 | // Test for CRM-18356 - activity shouldn't appear if target contact filter is null. | |
1294 | $params = [ | |
1295 | 'report_id' => 'activity', | |
1296 | 'contact_target_op' => 'nll', | |
1297 | 'contact_target_value' => '', | |
1298 | ]; | |
1299 | $rowsWithNullTarget = $this->callAPISuccess('report_template', 'getrows', $params)['values']; | |
1300 | $this->assertEmpty($rowsWithNullTarget); | |
1301 | } | |
1302 | ||
54df7b0b | 1303 | /** |
1304 | * Test the source contact filter works. | |
1305 | * | |
1306 | * @throws \CRM_Core_Exception | |
1307 | */ | |
1308 | public function testActivityDetailsContactFilter() { | |
1309 | $this->createContactsWithActivities(); | |
1310 | $params = [ | |
1311 | 'report_id' => 'activity', | |
1312 | 'contact_source_op' => 'has', | |
1313 | 'contact_source_value' => 'z', | |
1314 | 'options' => ['metadata' => ['sql']], | |
1315 | ]; | |
1316 | $rows = $this->callAPISuccess('report_template', 'getrows', $params); | |
1317 | $this->assertContains("civicrm_contact_source.sort_name LIKE '%z%'", $rows['metadata']['sql'][3]); | |
1318 | } | |
1319 | ||
5a14305b | 1320 | /** |
1321 | * Set up some activity data..... use some chars that challenge our utf handling. | |
f4b20644 | 1322 | * |
1323 | * @throws \CRM_Core_Exception | |
5a14305b | 1324 | */ |
1325 | public function createContactsWithActivities() { | |
87e5be4b | 1326 | $this->contactIDs[] = $this->individualCreate(['last_name' => 'Brzęczysław', 'email' => 'techo@spying.com']); |
5a14305b | 1327 | $this->contactIDs[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']); |
1328 | $this->contactIDs[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']); | |
1329 | ||
1330 | $this->callAPISuccess('Activity', 'create', [ | |
1331 | 'subject' => 'Very secret meeting', | |
f4b20644 | 1332 | 'activity_date_time' => date('Y-m-d 23:59:58'), |
5a14305b | 1333 | 'duration' => 120, |
1334 | 'location' => 'Pennsylvania', | |
1335 | 'details' => 'a test activity', | |
1336 | 'status_id' => 1, | |
1337 | 'activity_type_id' => 'Meeting', | |
1338 | 'source_contact_id' => $this->contactIDs[2], | |
9099cab3 | 1339 | 'target_contact_id' => [$this->contactIDs[0], $this->contactIDs[1]], |
5a14305b | 1340 | 'assignee_contact_id' => $this->contactIDs[1], |
1341 | ]); | |
1342 | } | |
1343 | ||
17c0ca6f AS |
1344 | /** |
1345 | * Test the group filter works on the contribution summary. | |
f4b20644 | 1346 | * |
1347 | * @throws \CRM_Core_Exception | |
17c0ca6f AS |
1348 | */ |
1349 | public function testContributionDetailTotalHeader() { | |
1350 | $contactID = $this->individualCreate(); | |
1351 | $contactID2 = $this->individualCreate(); | |
1352 | $this->contributionCreate(['contact_id' => $contactID, 'api.ContributionSoft.create' => ['amount' => 5, 'contact_id' => $contactID2]]); | |
1353 | $template = 'contribute/detail'; | |
f4b20644 | 1354 | $this->callAPISuccess('report_template', 'getrows', [ |
17c0ca6f AS |
1355 | 'report_id' => $template, |
1356 | 'contribution_or_soft_value' => 'contributions_only', | |
1357 | 'fields' => [ | |
1358 | 'sort_name' => '1', | |
1359 | 'age' => '1', | |
1360 | 'email' => '1', | |
1361 | 'phone' => '1', | |
1362 | 'financial_type_id' => '1', | |
1363 | 'receive_date' => '1', | |
1364 | 'total_amount' => '1', | |
39b959db | 1365 | ], |
17c0ca6f | 1366 | 'order_bys' => [['column' => 'sort_name', 'order' => 'ASC', 'section' => '1']], |
9099cab3 CW |
1367 | 'options' => ['metadata' => ['sql']], |
1368 | ]); | |
17c0ca6f AS |
1369 | } |
1370 | ||
34496b9c | 1371 | /** |
1372 | * Test contact subtype filter on grant report. | |
f4b20644 | 1373 | * |
1374 | * @throws \CRM_Core_Exception | |
34496b9c | 1375 | */ |
1376 | public function testGrantReportSeparatedFilter() { | |
1377 | $contactID = $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]); | |
1378 | $contactID2 = $this->individualCreate(); | |
1379 | $this->callAPISuccess('Grant', 'create', ['contact_id' => $contactID, 'status_id' => 1, 'grant_type_id' => 1, 'amount_total' => 1]); | |
1380 | $this->callAPISuccess('Grant', 'create', ['contact_id' => $contactID2, 'status_id' => 1, 'grant_type_id' => 1, 'amount_total' => 1]); | |
1381 | $rows = $this->callAPISuccess('report_template', 'getrows', [ | |
1382 | 'report_id' => 'grant/detail', | |
1383 | 'contact_sub_type_op' => 'in', | |
1384 | 'contact_sub_type_value' => ['Student'], | |
1385 | ]); | |
1386 | $this->assertEquals(1, $rows['count']); | |
1387 | } | |
1388 | ||
2fdf00e9 EL |
1389 | /** |
1390 | * Test contact subtype filter on summary report. | |
1391 | * | |
1392 | * @throws \CRM_Core_Exception | |
1393 | */ | |
1394 | public function testContactSubtypeNotNull() { | |
1395 | $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]); | |
1396 | $this->individualCreate(); | |
1397 | ||
1398 | $rows = $this->callAPISuccess('report_template', 'getrows', [ | |
1399 | 'report_id' => 'contact/summary', | |
1400 | 'contact_sub_type_op' => 'nnll', | |
1401 | 'contact_sub_type_value' => [], | |
1402 | 'contact_type_op' => 'eq', | |
1403 | 'contact_type_value' => 'Individual', | |
1404 | ]); | |
1405 | $this->assertEquals(1, $rows['count']); | |
1406 | } | |
1407 | ||
1408 | /** | |
1409 | * Test contact subtype filter on summary report. | |
1410 | * | |
1411 | * @throws \CRM_Core_Exception | |
1412 | */ | |
1413 | public function testContactSubtypeNull() { | |
1414 | $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]); | |
1415 | $this->individualCreate(); | |
1416 | ||
1417 | $rows = $this->callAPISuccess('report_template', 'getrows', [ | |
1418 | 'report_id' => 'contact/summary', | |
1419 | 'contact_sub_type_op' => 'nll', | |
1420 | 'contact_sub_type_value' => [], | |
1421 | 'contact_type_op' => 'eq', | |
1422 | 'contact_type_value' => 'Individual', | |
1423 | ]); | |
1424 | $this->assertEquals(1, $rows['count']); | |
1425 | } | |
1426 | ||
f8dcf338 AS |
1427 | /** |
1428 | * Test contact subtype filter on summary report. | |
1429 | * | |
1430 | * @throws \CRM_Core_Exception | |
1431 | */ | |
1432 | public function testContactSubtypeIn() { | |
1433 | $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]); | |
1434 | $this->individualCreate(); | |
1435 | ||
1436 | $rows = $this->callAPISuccess('report_template', 'getrows', [ | |
1437 | 'report_id' => 'contact/summary', | |
1438 | 'contact_sub_type_op' => 'in', | |
1439 | 'contact_sub_type_value' => ['Student'], | |
1440 | 'contact_type_op' => 'in', | |
1441 | 'contact_type_value' => 'Individual', | |
1442 | ]); | |
1443 | $this->assertEquals(1, $rows['count']); | |
1444 | } | |
1445 | ||
1446 | /** | |
1447 | * Test contact subtype filter on summary report. | |
1448 | * | |
1449 | * @throws \CRM_Core_Exception | |
1450 | */ | |
1451 | public function testContactSubtypeNotIn() { | |
1452 | $this->individualCreate(['contact_sub_type' => ['Student', 'Parent']]); | |
1453 | $this->individualCreate(); | |
1454 | ||
1455 | $rows = $this->callAPISuccess('report_template', 'getrows', [ | |
1456 | 'report_id' => 'contact/summary', | |
1457 | 'contact_sub_type_op' => 'notin', | |
1458 | 'contact_sub_type_value' => ['Student'], | |
1459 | 'contact_type_op' => 'in', | |
1460 | 'contact_type_value' => 'Individual', | |
1461 | ]); | |
1462 | $this->assertEquals(1, $rows['count']); | |
1463 | } | |
1464 | ||
e40ce31e JM |
1465 | /** |
1466 | * Test PCP report to ensure total donors and total committed is accurate. | |
f4b20644 | 1467 | * |
1468 | * @throws \CRM_Core_Exception | |
e40ce31e JM |
1469 | */ |
1470 | public function testPcpReportTotals() { | |
1471 | $donor1ContactId = $this->individualCreate(); | |
1472 | $donor2ContactId = $this->individualCreate(); | |
1473 | $donor3ContactId = $this->individualCreate(); | |
1474 | ||
1475 | // We are going to create two PCP pages. We will create two contributions | |
1476 | // on the first PCP page and one contribution on the second PCP page. | |
1477 | // | |
1478 | // Then, we will ensure that the first PCP page reports a total of both | |
1479 | // contributions (but not the contribution made on the second PCP page). | |
1480 | ||
1481 | // A PCP page requires three components: | |
1482 | // 1. A contribution page | |
1483 | // 2. A PCP Block | |
1484 | // 3. A PCP page | |
1485 | ||
1486 | // pcpBLockParams creates a contribution page and returns the parameters | |
1487 | // necessary to create a PBP Block. | |
1488 | $blockParams = $this->pcpBlockParams(); | |
1489 | $pcpBlock = CRM_PCP_BAO_PCPBlock::create($blockParams); | |
1490 | ||
1491 | // Keep track of the contribution page id created. We will use this | |
1492 | // contribution page id for all the PCP pages. | |
1493 | $contribution_page_id = $pcpBlock->entity_id; | |
1494 | ||
1495 | // pcpParams returns the parameters needed to create a PCP page. | |
1496 | $pcpParams = $this->pcpParams(); | |
1497 | // Keep track of the owner of the page so we can properly apply the | |
1498 | // soft credit. | |
1499 | $pcpOwnerContact1Id = $pcpParams['contact_id']; | |
1500 | $pcpParams['pcp_block_id'] = $pcpBlock->id; | |
1501 | $pcpParams['page_id'] = $contribution_page_id; | |
1502 | $pcpParams['page_type'] = 'contribute'; | |
1503 | $pcp1 = CRM_PCP_BAO_PCP::create($pcpParams); | |
1504 | ||
1505 | // Nice work. Now, let's create a second PCP page. | |
1506 | $pcpParams = $this->pcpParams(); | |
1507 | // Keep track of the owner of the page. | |
1508 | $pcpOwnerContact2Id = $pcpParams['contact_id']; | |
1509 | // We're using the same pcpBlock id and contribution page that we created above. | |
1510 | $pcpParams['pcp_block_id'] = $pcpBlock->id; | |
1511 | $pcpParams['page_id'] = $contribution_page_id; | |
1512 | $pcpParams['page_type'] = 'contribute'; | |
1513 | $pcp2 = CRM_PCP_BAO_PCP::create($pcpParams); | |
1514 | ||
1515 | // Get soft credit types, with the name column as the key. | |
39868387 | 1516 | $soft_credit_types = CRM_Core_PseudoConstant::get('CRM_Contribute_BAO_ContributionSoft', 'soft_credit_type_id', ['flip' => TRUE, 'labelColumn' => 'name']); |
e40ce31e JM |
1517 | $pcp_soft_credit_type_id = $soft_credit_types['pcp']; |
1518 | ||
1519 | // Create two contributions assigned to this contribution page and | |
1520 | // assign soft credits appropriately. | |
1521 | // FIRST... | |
9099cab3 | 1522 | $contribution1params = [ |
e40ce31e JM |
1523 | 'contact_id' => $donor1ContactId, |
1524 | 'contribution_page_id' => $contribution_page_id, | |
1525 | 'total_amount' => '75.00', | |
9099cab3 | 1526 | ]; |
e40ce31e JM |
1527 | $c1 = $this->contributionCreate($contribution1params); |
1528 | // Now the soft contribution. | |
9099cab3 | 1529 | $p = [ |
e40ce31e JM |
1530 | 'contribution_id' => $c1, |
1531 | 'pcp_id' => $pcp1->id, | |
1532 | 'contact_id' => $pcpOwnerContact1Id, | |
1533 | 'amount' => 75.00, | |
1534 | 'currency' => 'USD', | |
1535 | 'soft_credit_type_id' => $pcp_soft_credit_type_id, | |
9099cab3 | 1536 | ]; |
e40ce31e JM |
1537 | $this->callAPISuccess('contribution_soft', 'create', $p); |
1538 | // SECOND... | |
9099cab3 | 1539 | $contribution2params = [ |
e40ce31e JM |
1540 | 'contact_id' => $donor2ContactId, |
1541 | 'contribution_page_id' => $contribution_page_id, | |
1542 | 'total_amount' => '25.00', | |
9099cab3 | 1543 | ]; |
e40ce31e JM |
1544 | $c2 = $this->contributionCreate($contribution2params); |
1545 | // Now the soft contribution. | |
9099cab3 | 1546 | $p = [ |
e40ce31e JM |
1547 | 'contribution_id' => $c2, |
1548 | 'pcp_id' => $pcp1->id, | |
1549 | 'contact_id' => $pcpOwnerContact1Id, | |
1550 | 'amount' => 25.00, | |
1551 | 'currency' => 'USD', | |
1552 | 'soft_credit_type_id' => $pcp_soft_credit_type_id, | |
9099cab3 | 1553 | ]; |
e40ce31e JM |
1554 | $this->callAPISuccess('contribution_soft', 'create', $p); |
1555 | ||
1556 | // Create one contributions assigned to the second PCP page | |
9099cab3 | 1557 | $contribution3params = [ |
e40ce31e JM |
1558 | 'contact_id' => $donor3ContactId, |
1559 | 'contribution_page_id' => $contribution_page_id, | |
1560 | 'total_amount' => '200.00', | |
9099cab3 | 1561 | ]; |
e40ce31e JM |
1562 | $c3 = $this->contributionCreate($contribution3params); |
1563 | // Now the soft contribution. | |
c69ecb12 | 1564 | $p = [ |
e40ce31e JM |
1565 | 'contribution_id' => $c3, |
1566 | 'pcp_id' => $pcp2->id, | |
1567 | 'contact_id' => $pcpOwnerContact2Id, | |
1568 | 'amount' => 200.00, | |
1569 | 'currency' => 'USD', | |
1570 | 'soft_credit_type_id' => $pcp_soft_credit_type_id, | |
c69ecb12 | 1571 | ]; |
e40ce31e JM |
1572 | $this->callAPISuccess('contribution_soft', 'create', $p); |
1573 | ||
1574 | $template = 'contribute/pcp'; | |
9099cab3 | 1575 | $rows = $this->callAPISuccess('report_template', 'getrows', [ |
e40ce31e JM |
1576 | 'report_id' => $template, |
1577 | 'title' => 'My PCP', | |
1578 | 'fields' => [ | |
1579 | 'amount_1' => '1', | |
1580 | 'soft_id' => '1', | |
39b959db | 1581 | ], |
9099cab3 | 1582 | ]); |
e40ce31e | 1583 | $values = $rows['values'][0]; |
f4b20644 | 1584 | $this->assertEquals(100.00, $values['civicrm_contribution_soft_amount_1_sum'], 'Total committed should be $100'); |
1585 | $this->assertEquals(2, $values['civicrm_contribution_soft_soft_id_count'], 'Total donors should be 2'); | |
e40ce31e JM |
1586 | } |
1587 | ||
38a85cdd J |
1588 | /** |
1589 | * Test a report that uses getAddressColumns(); | |
f4b20644 | 1590 | * |
1591 | * @throws \CRM_Core_Exception | |
38a85cdd J |
1592 | */ |
1593 | public function testGetAddressColumns() { | |
1594 | $template = 'event/participantlisting'; | |
c69ecb12 | 1595 | $this->callAPISuccess('report_template', 'getrows', [ |
38a85cdd J |
1596 | 'report_id' => $template, |
1597 | 'fields' => [ | |
1598 | 'sort_name' => '1', | |
1599 | 'street_address' => '1', | |
1600 | ], | |
1601 | ]); | |
1602 | } | |
1603 | ||
6a488035 | 1604 | } |