tests/phpunit/api/v4/Action/DateTest.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | Copyright CiviCRM LLC. All rights reserved.                        |
   6  |                                                                    |
   7  | This work is published under the GNU AGPLv3 license with some      |
   8  | permitted exceptions and without any warranty. For full license    |
   9  | and copyright information, see https://civicrm.org/licensing       |
  10  +--------------------------------------------------------------------+
  11  */
  12
  13 /**
  14  *
  15  * @package CRM
  16  * @copyright CiviCRM LLC https://civicrm.org/licensing
  17  */
  18
  19
  20 namespace api\v4\Action;
  21
  22 use Civi\Api4\Activity;
  23 use Civi\Api4\Contact;
  24 use Civi\Api4\Contribution;
  25 use Civi\Api4\Relationship;
  26 use api\v4\UnitTestCase;
  27
  28 /**
  29  * @group headless
  30  */
  31 class DateTest extends UnitTestCase {
  32
  33   public function testRelationshipDate() {
  34     $c1 = Contact::create()
  35       ->addValue('first_name', 'c')
  36       ->addValue('last_name', 'one')
  37       ->execute()
  38       ->first()['id'];
  39     $c2 = Contact::create()
  40       ->addValue('first_name', 'c')
  41       ->addValue('last_name', 'two')
  42       ->execute()
  43       ->first()['id'];
  44     $r = Relationship::create()
  45       ->addValue('contact_id_a', $c1)
  46       ->addValue('contact_id_b', $c2)
  47       ->addValue('relationship_type_id', 1)
  48       ->addValue('start_date', 'now')
  49       ->addValue('end_date', 'now + 1 week')
  50       ->execute()
  51       ->first()['id'];
  52     $result = Relationship::get()
  53       ->addWhere('start_date', '=', 'now')
  54       ->addWhere('end_date', '>', 'now + 1 day')
  55       ->execute()
  56       ->indexBy('id');
  57     $this->assertArrayHasKey($r, $result);
  58     $result = Relationship::get()
  59       ->addWhere('start_date', '<', 'now')
  60       ->execute()
  61       ->indexBy('id');
  62     $this->assertArrayNotHasKey($r, $result);
  63   }
  64
  65   public function testRelativeDateRanges() {
  66     $c1 = Contact::create()
  67       ->addValue('first_name', 'c')
  68       ->addValue('last_name', 'one')
  69       ->execute()
  70       ->first()['id'];
  71
  72     // Avoid problems with `strtotime(<date arithmetic expression>)` giving
  73     // impossible dates like April 31 which roll over and then don't match.
  74     $thisMonth = (int) date('m');
  75     $lastMonth = ($thisMonth === 1 ? 12 : $thisMonth - 1);
  76     $nextMonth = ($thisMonth === 12 ? 1 : $thisMonth + 1);
  77     $lastMonthsYear = ($thisMonth === 1 ? date('Y') - 1 : date('Y'));
  78     $nextMonthsYear = ($thisMonth === 12 ? date('Y') + 1 : date('Y'));
  79
  80     $act = Activity::save()
  81       ->setDefaults(['activity_type_id:name' => 'Meeting', 'source_contact_id' => $c1])
  82       ->addRecord(['activity_date_time' => (date('Y') - 3) . '-' . date('m-01 H:i:s')])
  83       ->addRecord(['activity_date_time' => (date('Y') - 1) . '-' . date('m-01 H:i:s')])
  84       ->addRecord(['activity_date_time' => "{$lastMonthsYear}-{$lastMonth}-01 " . date('H:i:s')])
  85       ->addRecord(['activity_date_time' => 'now'])
  86       ->addRecord(['activity_date_time' => "{$nextMonthsYear}-{$nextMonth}-01 " . date('H:i:s')])
  87       ->addRecord(['activity_date_time' => (date('Y') + 1) . '-' . date('m-01 H:i:s')])
  88       ->addRecord(['activity_date_time' => (date('Y') + 3) . '-' . date('m-01 H:i:s')])
  89       ->execute()->column('id');
  90
  91     $result = Activity::get(FALSE)->addSelect('id')
  92       ->addWhere('activity_date_time', '>', 'previous.year')
  93       ->execute()->column('id');
  94     $this->assertNotContains($act[0], $result);
  95     $this->assertContains($act[3], $result);
  96     $this->assertContains($act[4], $result);
  97     $this->assertContains($act[5], $result);
  98     $this->assertContains($act[6], $result);
  99
 100     $result = Activity::get(FALSE)->addSelect('id')
 101       ->addWhere('activity_date_time', '>', 'this.year')
 102       ->execute()->column('id');
 103     $this->assertNotContains($act[0], $result);
 104     $this->assertNotContains($act[1], $result);
 105     $this->assertNotContains($act[2], $result);
 106     $this->assertNotContains($act[3], $result);
 107     $this->assertContains($act[5], $result);
 108     $this->assertContains($act[6], $result);
 109
 110     // Ensure it also works if the DATE() function is used
 111     $result = Activity::get(FALSE)->addSelect('id')
 112       ->addWhere('DATE(activity_date_time)', '>=', 'this.year')
 113       ->execute()->column('id');
 114     $this->assertNotContains($act[0], $result);
 115     $this->assertNotContains($act[1], $result);
 116     $this->assertContains($act[3], $result);
 117     $this->assertContains($act[4], $result);
 118     $this->assertContains($act[5], $result);
 119     $this->assertContains($act[6], $result);
 120
 121     $result = Activity::get(FALSE)->addSelect('id')
 122       ->addWhere('activity_date_time', '<', 'previous.year')
 123       ->execute()->column('id');
 124     $this->assertContains($act[0], $result);
 125     $this->assertNotContains($act[4], $result);
 126     $this->assertNotContains($act[5], $result);
 127     $this->assertNotContains($act[6], $result);
 128
 129     $result = Activity::get(FALSE)->addSelect('id')
 130       ->addWhere('activity_date_time', '=', 'next.month')
 131       ->execute()->column('id');
 132     $this->assertNotContains($act[0], $result);
 133     $this->assertNotContains($act[1], $result);
 134     $this->assertNotContains($act[2], $result);
 135     $this->assertContains($act[4], $result);
 136     $this->assertNotContains($act[5], $result);
 137     $this->assertNotContains($act[6], $result);
 138
 139     $result = Activity::get(FALSE)->addSelect('id')
 140       ->addWhere('activity_date_time', 'BETWEEN', ['previous.year', 'this.year'])
 141       ->execute()->column('id');
 142     $this->assertContains($act[2], $result);
 143     $this->assertContains($act[3], $result);
 144     $this->assertNotContains($act[0], $result);
 145     $this->assertNotContains($act[6], $result);
 146   }
 147
 148   public function testJoinOnRelativeDate() {
 149     $c1 = Contact::create(FALSE)
 150       ->addValue('first_name', 'Contributor')
 151       ->addValue('last_name', 'One')
 152       ->execute()
 153       ->first()['id'];
 154
 155     // Contribution from last year
 156     Contribution::create(FALSE)
 157       ->addValue('contact_id', $c1)
 158       ->addValue('receive_date', (date('Y') - 1) . '-06-01')
 159       ->addValue('financial_type_id', 1)
 160       ->addValue('total_amount', 12)
 161       ->execute();
 162
 163     // Contribution from this year
 164     Contribution::create(FALSE)
 165       ->addValue('contact_id', $c1)
 166       ->addValue('receive_date', date('Y') . '-06-01')
 167       ->addValue('financial_type_id', 1)
 168       ->addValue('total_amount', 6)
 169       ->execute();
 170
 171     // Contribution from 2 years ago
 172     Contribution::create(FALSE)
 173       ->addValue('contact_id', $c1)
 174       ->addValue('receive_date', (date('Y') - 2) . '-06-01')
 175       ->addValue('financial_type_id', 1)
 176       ->addValue('total_amount', 24)
 177       ->execute();
 178
 179     // Find contribution from last year
 180     $contact = \Civi\Api4\Contact::get()
 181       ->addSelect('id', 'contribution.total_amount')
 182       ->setJoin([
 183         ['Contribution AS contribution', FALSE, NULL, ['contribution.receive_date', '=', '"previous.year"']],
 184       ])
 185       ->addWhere('id', '=', $c1)
 186       ->execute();
 187     $this->assertCount(1, $contact);
 188     $this->assertEquals(12, $contact[0]['contribution.total_amount']);
 189
 190     // Find contributions not from last year
 191     $contact = \Civi\Api4\Contact::get()
 192       ->addSelect('id', 'contribution.total_amount')
 193       ->setJoin([
 194         ['Contribution AS contribution', FALSE, NULL, ['contribution.receive_date', '!=', '"previous.year"']],
 195       ])
 196       ->addWhere('id', '=', $c1)
 197       ->execute();
 198     $this->assertCount(2, $contact);
 199   }
 200
 201 }