Merge pull request #22374 from eileenmcnaughton/format
[civicrm-core.git] / CRM / Core / CodeGen / GenerateData.php
1 <?php
2
3 class CRM_Core_CodeGen_GenerateData {
4
5 /**
6 * Constants
7 */
8
9 // Set ADD_TO_DB = FALSE to do a dry run
10 const ADD_TO_DB = TRUE;
11
12 const DATA_FILENAME = "sample_data.xml";
13 const NUM_DOMAIN = 1;
14 const NUM_CONTACT = 200;
15 const INDIVIDUAL_PERCENT = 80;
16 const HOUSEHOLD_PERCENT = 10;
17 const ORGANIZATION_PERCENT = 10;
18 const NUM_INDIVIDUAL_PER_HOUSEHOLD = 4;
19 const NUM_ACTIVITY = 150;
20
21 // Location types from the table crm_location_type
22 const HOME = 1;
23 const WORK = 2;
24 const MAIN = 3;
25 const OTHER = 4;
26
27 /**
28 * Class constructor
29 *
30 * @param string|int $seed
31 * Some scalar value used as the starting point for random-number generation.
32 * @param int $time
33 * A timestamp; some facsimile of "now".
34 */
35 public function __construct($seed, $time) {
36 // initialize all the vars
37 $this->seed = $seed;
38 $this->time = $time;
39 $this->numIndividual = self::INDIVIDUAL_PERCENT * self::NUM_CONTACT / 100;
40 $this->numHousehold = self::HOUSEHOLD_PERCENT * self::NUM_CONTACT / 100;
41 $this->numOrganization = self::ORGANIZATION_PERCENT * self::NUM_CONTACT / 100;
42 $this->numStrictIndividual = $this->numIndividual - ($this->numHousehold * self::NUM_INDIVIDUAL_PER_HOUSEHOLD);
43
44 // Parse data file
45 foreach ((array) simplexml_load_file(self::getCivicrmDir() . '/sql/' . self::DATA_FILENAME) as $key => $val) {
46 $val = (array) $val;
47 $this->sampleData[$key] = (array) $val['item'];
48 }
49 // Init DB
50 $config = CRM_Core_Config::singleton();
51
52 // Relationship types indexed by name_a_b from the table civicrm_relationship_type
53 $this->relTypes = CRM_Utils_Array::index(['name_a_b'], CRM_Core_PseudoConstant::relationshipType('name'));
54 }
55
56 /**
57 * Create a full, standard set of random data.
58 */
59 public function generateAll() {
60 $this->initID();
61 $this->generate('Domain');
62 $this->generate('Contact');
63 $this->generate('Individual');
64 $this->generate('Household');
65 $this->generate('Organization');
66 $this->generate('Relationship');
67 $this->generate('EntityTag');
68 $this->generate('Group');
69 $this->generate('Note');
70 $this->generate('Activity');
71 $this->generate('Event');
72 $this->generate('Contribution');
73 $this->generate('ContributionLineItem');
74 $this->generate('Membership');
75 $this->generate('MembershipPayment');
76 $this->generate('MembershipLog');
77 $this->generate('PCP');
78 $this->generate('SoftContribution');
79 $this->generate('Pledge');
80 $this->generate('PledgePayment');
81 $this->generate('Participant');
82 $this->generate('ParticipantPayment');
83 $this->generate('LineItemParticipants');
84 $this->generate('AccountingEntries');
85 }
86
87 /**
88 * Write a log message.
89 *
90 * @param string $message
91 */
92 public function write($message) {
93 echo $message;
94 }
95
96 /**
97 * Public wrapper for calling private "add" functions
98 * Provides user feedback
99 *
100 * @param $itemName
101 */
102 public function generate($itemName) {
103 $this->write("Generating $itemName\n");
104 $fn = "add$itemName";
105 $this->$fn();
106 }
107
108 /**
109 * this function creates arrays for the following
110 *
111 * domain id
112 * contact id
113 * contact_location id
114 * contact_contact_location id
115 * contact_email uuid
116 * contact_phone_uuid
117 * contact_instant_message uuid
118 * contact_relationship uuid
119 * contact_task uuid
120 * contact_note uuid
121 */
122 public function initID() {
123 // get the domain and contact id arrays
124 $this->domain = range(1, self::NUM_DOMAIN);
125 $this->domain = $this->shuffle($this->domain);
126
127 // Get first contact id
128 $this->startCid = $cid = CRM_Core_DAO::singleValueQuery("SELECT MAX(id) FROM civicrm_contact");
129 $this->contact = range($cid + 1, $cid + self::NUM_CONTACT);
130 $this->contact = $this->shuffle($this->contact);
131
132 // get the individual, household and organizaton contacts
133 $offset = 0;
134 $this->Individual = array_slice($this->contact, $offset, $this->numIndividual);
135 $offset += $this->numIndividual;
136 $this->Household = array_slice($this->contact, $offset, $this->numHousehold);
137 $offset += $this->numHousehold;
138 $this->Organization = array_slice($this->contact, $offset, $this->numOrganization);
139
140 // get the strict individual contacts (i.e individual contacts not belonging to any household)
141 $this->strictIndividual = array_slice($this->Individual, 0, $this->numStrictIndividual);
142
143 // get the household to individual mapping array
144 $this->householdIndividual = array_slice($this->Individual, $this->numStrictIndividual);
145 $this->householdIndividual = array_chunk($this->householdIndividual, self::NUM_INDIVIDUAL_PER_HOUSEHOLD);
146 $this->householdIndividual = array_combine($this->Household, $this->householdIndividual);
147 }
148
149 /*
150 * private members
151 *
152 */
153
154 /**
155 * @var int
156 */
157 private $seed;
158
159 /**
160 * enum's from database
161 *
162 * @var array
163 */
164 private $preferredCommunicationMethod = ['1', '2', '3', '4', '5'];
165
166 private $contactType = ['Individual', 'Household', 'Organization'];
167
168 private $phoneType = ['1', '2', '3', '4'];
169
170 /**
171 * customizable enums (foreign keys)
172 *
173 * @var array
174 */
175 private $prefix = [
176 // Female
177 1 => [
178 1 => 'Mrs.',
179 2 => 'Ms.',
180 4 => 'Dr.',
181 ],
182 // Male
183 2 => [
184 3 => 'Mr.',
185 4 => 'Dr.',
186 ],
187 ];
188
189 /**
190 * @var array
191 */
192 private $suffix = [1 => 'Jr.', 2 => 'Sr.', 3 => 'II', 4 => 'III'];
193
194 private $gender = [1 => 'female', 2 => 'male'];
195
196 /**
197 * store domain id's
198 *
199 * @var array
200 */
201 private $domain = [];
202
203 /**
204 * store contact id's
205 *
206 * @var array
207 */
208 private $contact = [];
209
210 private $Individual = [];
211
212 private $Household = [];
213
214 private $Organization = [];
215
216 // store which contacts have a location entity
217
218 /**
219 * for automatic management of is_primary field
220 *
221 * @var array
222 */
223 private $location = [
224 'Email' => [],
225 'Phone' => [],
226 'Address' => [],
227 ];
228
229 /**
230 * stores the strict individual id and household id to individual id mapping
231 *
232 * @var array
233 */
234 private $strictIndividual = [];
235
236 private $householdIndividual = [];
237
238 private $householdName = [];
239
240 /**
241 * sample data in xml format
242 *
243 * @var array
244 */
245 private $sampleData = [];
246
247 /**
248 * private vars
249 *
250 * @var array
251 */
252 private $startCid;
253
254 private $numIndividual = 0;
255
256 private $numHousehold = 0;
257
258 private $numOrganization = 0;
259
260 private $numStrictIndividual = 0;
261
262 private $stateMap = [];
263
264 private $states = [];
265
266 private $groupMembershipStatus = ['Added', 'Removed', 'Pending'];
267
268 private $subscriptionHistoryMethod = ['Admin', 'Email'];
269
270 private $deceasedContactIds = [];
271
272 /*********************************
273 * private methods
274 * *******************************
275 */
276
277 /**
278 * Random number generator.
279 *
280 * All other random() functions should derive from this.
281 *
282 * This is very weak RNG. The goal is to provide a reproducible sequence of
283 * random-ish values for generating dummy-data.
284 *
285 * @param int $min
286 * @param int $max
287 *
288 * @return int
289 */
290 private function randomInt($min, $max) {
291 $range = min(1 + $max - $min, mt_getrandmax());
292 $this->seed = md5($this->seed . chr(0) . $min . chr(0) . $max);
293 return $min + (hexdec(substr($this->seed, 20, 8)) % $range);
294 }
295
296 /**
297 * Get a randomly generated string.
298 *
299 * @param int $size
300 *
301 * @return string
302 */
303 private function randomString($size = 32) {
304 $string = "";
305
306 // get an ascii code for each character
307 for ($i = 0; $i < $size; $i++) {
308 $random_int = $this->randomInt(65, 122);
309 if (($random_int < 97) && ($random_int > 90)) {
310 // if ascii code between 90 and 97 substitute with space
311 $random_int = 32;
312 }
313 $random_char = chr($random_int);
314 $string .= $random_char;
315 }
316 return $string;
317 }
318
319 /**
320 * @return string
321 */
322 private function randomChar() {
323 return chr($this->randomInt(65, 90));
324 }
325
326 /**
327 * Get a random item from the sample data or any other array
328 *
329 * @param $items (array or string) - if string, used as key for sample data,
330 * if array, used as data source
331 *
332 * @return mixed (element from array)
333 *
334 * @private
335 */
336 private function randomItem($items) {
337 if (!is_array($items)) {
338 $key = $items;
339 $items = $this->sampleData[$key];
340 }
341 if (!$items) {
342 $this->write("Error: no items found for '$key'\n");
343 return FALSE;
344 }
345 return $items[$this->randomInt(0, count($items) - 1)];
346 }
347
348 /**
349 * @param $items
350 *
351 * @return mixed
352 */
353 private function randomIndex($items) {
354 return $this->randomItem(array_keys($items));
355 }
356
357 /**
358 * @param $items
359 *
360 * @return array
361 */
362 private function randomKeyValue($items) {
363 $key = $this->randomIndex($items);
364 return [$key, $items[$key]];
365 }
366
367 private function shuffle($array) {
368 for ($i = count($array) - 1; $i >= 1; $i--) {
369 $j = $this->randomInt(0, $i);
370 $tmp = $array[$i];
371 $array[$i] = $array[$j];
372 $array[$j] = $tmp;
373 }
374 return $array;
375 }
376
377 /**
378 * @param $chance
379 *
380 * @return int
381 */
382 private function probability($chance) {
383 if ($this->randomInt(0, 100) < ($chance * 100)) {
384 return 1;
385 }
386 return 0;
387 }
388
389 /**
390 * Generate a random date.
391 *
392 * If both $startDate and $endDate are defined generate
393 * date between them.
394 *
395 * If only startDate is specified then date generated is
396 * between startDate + 1 year.
397 *
398 * if only endDate is specified then date generated is
399 * between endDate - 1 year.
400 *
401 * if none are specified - date is between today - 1year
402 * and today
403 *
404 * @param int $startDate Start Date in Unix timestamp
405 * @param int $endDate End Date in Unix timestamp
406 *
407 * @access private
408 *
409 * @return string randomly generated date in the format "Ymd"
410 *
411 */
412 private function randomDate($startDate = 0, $endDate = 0) {
413
414 // number of seconds per year
415 $numSecond = 31536000;
416 $dateFormat = "YmdHis";
417 $today = $this->time;
418
419 // both are defined
420 if ($startDate && $endDate) {
421 return date($dateFormat, $this->randomInt($startDate, $endDate));
422 }
423
424 // only startDate is defined
425 if ($startDate) {
426 return date($dateFormat, $this->randomInt($startDate, $startDate + $numSecond));
427 }
428
429 // only endDate is defined
430 if ($startDate) {
431 return date($dateFormat, $this->randomInt($endDate - $numSecond, $endDate));
432 }
433
434 // none are defined
435 return date($dateFormat, $this->randomInt($today - $numSecond, $today));
436 }
437
438 /**
439 * Automatically manage the is_primary field by tracking which contacts have
440 * each item
441 *
442 * @param $cid
443 * @param $type
444 *
445 * @return int
446 */
447 private function isPrimary($cid, $type) {
448 if (empty($this->location[$type][$cid])) {
449 $this->location[$type][$cid] = TRUE;
450 return 1;
451 }
452 return 0;
453 }
454
455 /**
456 * Execute a query unless we are doing a dry run
457 * Note: this wrapper should not be used for SELECT queries
458 *
459 * @param $query
460 * @param array $params
461 *
462 * @return \CRM_Core_DAO
463 */
464 private function _query($query, $params = []) {
465 if (self::ADD_TO_DB) {
466 return CRM_Core_DAO::executeQuery($query, $params);
467 }
468 }
469
470 /**
471 * Call dao insert method unless we are doing a dry run
472 *
473 * @param $dao
474 */
475 private function _insert(&$dao) {
476 if (self::ADD_TO_DB) {
477 $dao->insert();
478 }
479 }
480
481 /**
482 * Call dao update method unless we are doing a dry run
483 *
484 * @param $dao
485 */
486 private function _update(&$dao) {
487 if (self::ADD_TO_DB) {
488 $dao->update();
489 }
490 }
491
492 /**
493 * Add core DAO object
494 *
495 * @param $type
496 * @param $params
497 */
498 private function _addDAO($type, $params) {
499 $daoName = "CRM_Core_DAO_$type";
500 $obj = new $daoName();
501 foreach ($params as $key => $value) {
502 $obj->$key = $value;
503 }
504 if (isset($this->location[$type])) {
505 $obj->is_primary = $this->isPrimary($params['contact_id'], $type);
506 }
507 $this->_insert($obj);
508 }
509
510 /**
511 * Fetch contact type based on stored mapping
512 *
513 * @param $id
514 *
515 * @return string $type
516 */
517 private function getContactType($id) {
518 foreach (['Individual', 'Household', 'Organization'] as $type) {
519 if (in_array($id, $this->$type)) {
520 return $type;
521 }
522 }
523 }
524
525 /**
526 * This method adds NUM_DOMAIN domains and then adds NUM_REVISION
527 * revisions for each domain with the latest revision being the last one..
528 */
529 private function addDomain() {
530
531 /* Add a location for domain 1 */
532
533 $domain = new CRM_Core_DAO_Domain();
534 for ($id = 2; $id <= self::NUM_DOMAIN; $id++) {
535 // domain name is pretty simple. it is "Domain $id"
536 $domain->name = "Domain $id";
537 $domain->description = "Description $id";
538 $domain->contact_name = $this->randomName();
539
540 // insert domain
541 $this->_insert($domain);
542 }
543 }
544
545 /**
546 * @return string
547 */
548 public function randomName() {
549 $first_name = $this->randomItem(($this->probability(.5) ? 'fe' : '') . 'male_name');
550 $middle_name = ucfirst($this->randomChar());
551 $last_name = $this->randomItem('last_name');
552 return "$first_name $middle_name. $last_name";
553 }
554
555 /**
556 * This method adds data to the contact table
557 *
558 * id - from $contact
559 * contact_type 'Individual' 'Household' 'Organization'
560 * preferred_communication (random 1 to 3)
561 */
562 private function addContact() {
563 $contact = new CRM_Contact_DAO_Contact();
564 $cid = $this->startCid;
565
566 for ($id = $cid + 1; $id <= $cid + self::NUM_CONTACT; $id++) {
567 $contact->contact_type = $this->getContactType($id);
568 $contact->do_not_phone = $this->probability(.2);
569 $contact->do_not_email = $this->probability(.2);
570 $contact->do_not_post = $this->probability(.2);
571 $contact->do_not_trade = $this->probability(.2);
572 $contact->preferred_communication_method = NULL;
573 if ($this->probability(.5)) {
574 $contact->preferred_communication_method = CRM_Core_DAO::VALUE_SEPARATOR . $this->randomItem($this->preferredCommunicationMethod) . CRM_Core_DAO::VALUE_SEPARATOR;
575 }
576 $contact->source = 'Sample Data';
577 $this->_insert($contact);
578 }
579 }
580
581 /**
582 * addIndividual()
583 *
584 * This method adds individual's data to the contact table
585 *
586 * The following fields are generated and added.
587 *
588 * contact_uuid - individual
589 * contact_rid - latest one
590 * first_name 'First Name $contact_uuid'
591 * middle_name 'Middle Name $contact_uuid'
592 * last_name 'Last Name $contact_uuid'
593 * job_title 'Job Title $contact_uuid'
594 *
595 */
596 private function addIndividual() {
597
598 $contact = new CRM_Contact_DAO_Contact();
599 $year = 60 * 60 * 24 * 365.25;
600 $now = $this->time;
601
602 foreach ($this->Individual as $cid) {
603 $contact->is_deceased = $contact->gender_id = $contact->birth_date = $contact->deceased_date = $email = NULL;
604 [$gender_id, $gender] = $this->randomKeyValue($this->gender);
605 $birth_date = $this->randomInt($now - 90 * $year, $now - 10 * $year);
606
607 $contact->last_name = $this->randomItem('last_name');
608
609 // Manage household names
610 if (!in_array($contact->id, $this->strictIndividual)) {
611 // Find position in household
612 foreach ($this->householdIndividual as $householdId => $house) {
613 foreach ($house as $position => $memberId) {
614 if ($memberId == $cid) {
615 break 2;
616 }
617 }
618 }
619 // Head of household: set name
620 if (empty($this->householdName[$householdId])) {
621 $this->householdName[$householdId] = $contact->last_name;
622 }
623 // Kids get household name, spouse might get it
624 if ($position > 1 || $this->probability(.5)) {
625 $contact->last_name = $this->householdName[$householdId];
626 }
627 elseif ($this->householdName[$householdId] != $contact->last_name) {
628 // Spouse might hyphenate name
629 if ($this->probability(.5)) {
630 $contact->last_name .= '-' . $this->householdName[$householdId];
631 }
632 // Kids might hyphenate name
633 else {
634 $this->householdName[$householdId] .= '-' . $contact->last_name;
635 }
636 }
637 // Sensible ages and genders
638 $offset = $this->randomInt($now - 40 * $year, $now);
639 // Parents
640 if ($position < 2) {
641 $birth_date = $this->randomInt($offset - 35 * $year, $offset - 20 * $year);
642 if ($this->probability(.8)) {
643 $gender_id = 2 - $position;
644 $gender = $this->gender[$gender_id];
645 }
646 }
647 // Kids
648 else {
649 $birth_date = $this->randomInt($offset - 10 * $year, $offset);
650 }
651 }
652 // Non household people
653 else {
654 if ($this->probability(.6)) {
655 $this->_addAddress($cid);
656 }
657 }
658
659 $contact->first_name = $this->randomItem($gender . '_name');
660 $contact->middle_name = $this->probability(.5) ? '' : ucfirst($this->randomChar());
661 $age = intval(($now - $birth_date) / $year);
662
663 // Prefix and suffix by gender and age
664 $contact->prefix_id = $contact->suffix_id = $prefix = $suffix = NULL;
665 if ($this->probability(.5) && $age > 20) {
666 [$contact->prefix_id, $prefix] = $this->randomKeyValue($this->prefix[$gender_id]);
667 $prefix .= ' ';
668 }
669 if ($gender === 'male' && $this->probability(.50)) {
670 [$contact->suffix_id, $suffix] = $this->randomKeyValue($this->suffix);
671 $suffix = ' ' . $suffix;
672 }
673 if ($this->probability(.7)) {
674 $contact->gender_id = $gender_id;
675 }
676 if ($this->probability(.7)) {
677 $contact->birth_date = date("Ymd", $birth_date);
678 }
679
680 // Deceased probability based on age
681 if ($contact->gender_id && $contact->gender_id == 2) {
682 $checkAge = 64;
683 }
684 else {
685 $checkAge = 68;
686 }
687 if ($age > $checkAge && count($this->deceasedContactIds) < 4) {
688 $contact->is_deceased = $this->probability(($age - 30) / 100);
689 if ($contact->is_deceased && $this->probability(.7)) {
690 $contact->deceased_date = $this->randomDate();
691 }
692 }
693
694 // Add 0, 1 or 2 email address
695 $count = $this->randomInt(0, 2);
696 for ($i = 0; $i < $count; ++$i) {
697 $email = $this->_individualEmail($contact);
698 $this->_addEmail($cid, $email, self::HOME);
699 }
700
701 // Add 0, 1 or 2 phones
702 $count = $this->randomInt(0, 2);
703 for ($i = 0; $i < $count; ++$i) {
704 $this->_addPhone($cid);
705 }
706
707 // Occasionally you get contacts with just an email in the db
708 if ($this->probability(.2) && $email) {
709 $contact->first_name = $contact->last_name = $contact->middle_name = NULL;
710 $contact->is_deceased = $contact->gender_id = $contact->birth_date = $contact->deceased_date = NULL;
711 $contact->display_name = $contact->sort_name = $email;
712 $contact->postal_greeting_display = $contact->email_greeting_display = "Dear $email";
713 }
714 else {
715 $contact->display_name = $prefix . $contact->first_name . ' ' . $contact->last_name . $suffix;
716 $contact->sort_name = $contact->last_name . ', ' . $contact->first_name;
717 $contact->postal_greeting_display = $contact->email_greeting_display = 'Dear ' . $contact->first_name;
718 }
719 $contact->addressee_id = $contact->postal_greeting_id = $contact->email_greeting_id = 1;
720 $contact->addressee_display = $contact->display_name;
721 $contact->hash = crc32($contact->sort_name);
722 $contact->id = $cid;
723 $this->_update($contact);
724 if ($contact->is_deceased) {
725 $this->deceasedContactIds[] = $cid;
726 }
727 }
728 }
729
730 /**
731 * This method adds household's data to the contact table
732 *
733 * The following fields are generated and added.
734 *
735 * contact_uuid - household_individual
736 * contact_rid - latest one
737 * household_name 'household $contact_uuid primary contact
738 * $primary_contact_uuid' nick_name 'nick $contact_uuid' primary_contact_uuid
739 * = $household_individual[$contact_uuid][0];
740 *
741 */
742 private function addHousehold() {
743
744 $contact = new CRM_Contact_DAO_Contact();
745 foreach ($this->Household as $cid) {
746 // Add address
747 $this->_addAddress($cid);
748
749 $contact->id = $cid;
750 $contact->household_name = $this->householdName[$cid] . " family";
751 // need to update the sort name for the main contact table
752 $contact->display_name = $contact->sort_name = $contact->household_name;
753 $contact->postal_greeting_id = $contact->email_greeting_id = 5;
754 $contact->postal_greeting_display = $contact->email_greeting_display = 'Dear ' . $contact->household_name;
755 $contact->addressee_id = 2;
756 $contact->addressee_display = $contact->display_name;
757 $contact->hash = crc32($contact->sort_name);
758 $this->_update($contact);
759 }
760 }
761
762 /**
763 * This method adds organization data to the contact table
764 *
765 * The following fields are generated and added.
766 *
767 * contact_uuid - organization
768 * contact_rid - latest one
769 * organization_name 'organization $contact_uuid'
770 * legal_name 'legal $contact_uuid'
771 * nick_name 'nick $contact_uuid'
772 * sic_code 'sic $contact_uuid'
773 * primary_contact_id - random individual contact uuid
774 *
775 */
776 private function addOrganization() {
777
778 $org = new CRM_Contact_DAO_Contact();
779 $employees = $this->Individual;
780 $employees = $this->shuffle($employees);
781
782 foreach ($this->Organization as $key => $id) {
783 $org->primary_contact_id = $website = $email = NULL;
784 $org->id = $id;
785 $address = $this->_addAddress($id);
786
787 $namePre = $this->randomItem('organization_prefix');
788 $nameMid = $this->randomItem('organization_name');
789 $namePost = $this->randomItem('organization_suffix');
790
791 // Some orgs are named after their location
792 if ($this->probability(.7)) {
793 $place = $this->randomItem(['city', 'street_name', 'state']);
794 $namePre = $address[$place];
795 }
796 $org->organization_name = "$namePre $nameMid $namePost";
797
798 // Most orgs have a website and email
799 if ($this->probability(.8)) {
800 $website = $this->_addWebsite($id, $org->organization_name);
801 $url = str_replace('http://', '', $website['url']);
802 $email = $this->randomItem('email_address') . '@' . $url;
803 $this->_addEmail($id, $email, self::MAIN);
804 }
805
806 // current employee
807 if ($this->probability(.8)) {
808 $indiv = new CRM_Contact_DAO_Contact();
809 $org->primary_contact_id = $indiv->id = $employees[$key];
810 $indiv->organization_name = $org->organization_name;
811 $indiv->employer_id = $id;
812 $this->_update($indiv);
813 // Share address with employee
814 if ($this->probability(.8)) {
815 $this->_addAddress($indiv->id, $id);
816 }
817 // Add work email for employee
818 if ($website) {
819 $indiv->find(TRUE);
820 $email = $this->_individualEmail($indiv, $url);
821 $this->_addEmail($indiv->id, $email, self::WORK);
822 }
823 }
824
825 // need to update the sort name for the main contact table
826 $org->display_name = $org->sort_name = $org->organization_name;
827 $org->addressee_id = 3;
828 $org->addressee_display = $org->display_name;
829 $org->hash = crc32($org->sort_name);
830 $this->_update($org);
831 }
832 }
833
834 /**
835 * This method adds data to the contact_relationship table
836 */
837 private function addRelationship() {
838
839 $relationship = new CRM_Contact_DAO_Relationship();
840
841 // Household relationships
842 foreach ($this->householdIndividual as $household_id => $household_member) {
843 // Default active
844 $relationship->is_active = 1;
845
846 // add child_of relationship for each child
847 $relationship->relationship_type_id = $this->relTypes['Child of']['id'];
848 foreach ([0, 1] as $parent) {
849 foreach ([2, 3] as $child) {
850 $relationship->contact_id_a = $household_member[$child];
851 $relationship->contact_id_b = $household_member[$parent];
852 $this->_insert($relationship);
853 }
854 }
855
856 // add sibling_of relationship
857 $relationship->relationship_type_id = $this->relTypes['Sibling of']['id'];
858 $relationship->contact_id_a = $household_member[3];
859 $relationship->contact_id_b = $household_member[2];
860 $this->_insert($relationship);
861
862 // add member_of_household relationships and shared address
863 $relationship->relationship_type_id = $this->relTypes['Household Member of']['id'];
864 $relationship->contact_id_b = $household_id;
865 for ($i = 1; $i < 4; ++$i) {
866 $relationship->contact_id_a = $household_member[$i];
867 $this->_insert($relationship);
868 $this->_addAddress($household_member[$i], $household_id);
869 }
870
871 // Divorced/separated couples - end relationship and different address
872 if ($this->probability(.4)) {
873 $relationship->is_active = 0;
874 $this->_addAddress($household_member[0]);
875 }
876 else {
877 $this->_addAddress($household_member[0], $household_id);
878 }
879
880 // add head_of_household relationship 1 for head of house
881 $relationship->relationship_type_id = $this->relTypes['Head of Household for']['id'];
882 $relationship->contact_id_a = $household_member[0];
883 $relationship->contact_id_b = $household_id;
884 $this->_insert($relationship);
885
886 // add spouse_of relationship 1 for both the spouses
887 $relationship->relationship_type_id = $this->relTypes['Spouse of']['id'];
888 $relationship->contact_id_a = $household_member[1];
889 $relationship->contact_id_b = $household_member[0];
890 $this->_insert($relationship);
891 }
892
893 // Add current employer relationships
894 $this->_query("INSERT INTO civicrm_relationship
895 (contact_id_a, contact_id_b, relationship_type_id, is_active)
896 (SELECT id, employer_id, " . $this->relTypes['Employee of']['id'] . ", 1 FROM civicrm_contact WHERE employer_id IN (" . implode(',', $this->Organization) . "))"
897 );
898 }
899
900 /**
901 * Create an address for a contact
902 *
903 * @param $cid int: contact id
904 * @param $masterContactId int: set if this is a shared address
905 *
906 * @return array
907 */
908 private function _addAddress($cid, $masterContactId = NULL) {
909
910 // Share existing address
911 if ($masterContactId) {
912 $dao = new CRM_Core_DAO_Address();
913 $dao->is_primary = 1;
914 $dao->contact_id = $masterContactId;
915 $dao->find(TRUE);
916 $dao->master_id = $dao->id;
917 $dao->id = NULL;
918 $dao->contact_id = $cid;
919 $dao->is_primary = $this->isPrimary($cid, 'Address');
920 $dao->location_type_id = $this->getContactType($masterContactId) == 'Organization' ? self::WORK : self::HOME;
921 $this->_insert($dao);
922 }
923
924 // Generate new address
925 else {
926 $params = [
927 'contact_id' => $cid,
928 'location_type_id' => $this->getContactType($cid) == 'Organization' ? self::MAIN : self::HOME,
929 'street_number' => $this->randomInt(1, 1000),
930 'street_number_suffix' => ucfirst($this->randomChar()),
931 'street_name' => $this->randomItem('street_name'),
932 'street_type' => $this->randomItem('street_type'),
933 'street_number_postdirectional' => $this->randomItem('address_direction'),
934 'county_id' => 1,
935 ];
936
937 $params['street_address'] = $params['street_number'] . $params['street_number_suffix'] . " " . $params['street_name'] . " " . $params['street_type'] . " " . $params['street_number_postdirectional'];
938
939 if ($params['location_type_id'] == self::MAIN) {
940 $params['supplemental_address_1'] = $this->randomItem('supplemental_addresses_1');
941 }
942
943 // Hack to add lat/long (limited to USA based addresses)
944 $params = array_merge($params, $this->getZipCodeInfo());
945
946 $this->_addDAO('Address', $params);
947 $params['state'] = $this->states[$params['state_province_id']];
948 return $params;
949 }
950 }
951
952 /**
953 * Add a phone number for a contact
954 *
955 * @param $cid int: contact id
956 *
957 * @return array
958 */
959 private function _addPhone($cid) {
960 $area = $this->probability(.5) ? '' : $this->randomInt(201, 899);
961 $pre = $this->randomInt(201, 899);
962 $post = $this->randomInt(1000, 9999);
963 $params = [
964 'location_type_id' => $this->getContactType($cid) == 'Organization' ? self::MAIN : self::HOME,
965 'contact_id' => $cid,
966 'phone' => ($area ? "($area) " : '') . "$pre-$post",
967 'phone_numeric' => $area . $pre . $post,
968 'phone_type_id' => $this->randomInt(1, 2),
969 ];
970 $this->_addDAO('Phone', $params);
971 return $params;
972 }
973
974 /**
975 * Add an email for a contact
976 *
977 * @param $cid int: contact id
978 * @param $email
979 * @param $locationType
980 *
981 * @return array
982 */
983 private function _addEmail($cid, $email, $locationType) {
984 $params = [
985 'location_type_id' => $locationType,
986 'contact_id' => $cid,
987 'email' => $email,
988 ];
989 $this->_addDAO('Email', $params);
990 return $params;
991 }
992
993 /**
994 * Add a website based on organization name
995 * Using common naming patterns
996 *
997 * @param $cid int: contact id
998 * @param $name str: contact name
999 *
1000 * @return array
1001 */
1002 private function _addWebsite($cid, $name) {
1003 $part = array_pad(explode(' ', strtolower($name)), 3, '');
1004 if (count($part) > 3) {
1005 // Abbreviate the place name if it's two words
1006 $domain = $part[0][0] . $part[1][0] . $part[2] . $part[3];
1007 }
1008 else {
1009 // Common naming patterns
1010 switch ($this->randomInt(1, 3)) {
1011 case 1:
1012 $domain = $part[0] . $part[1] . $part[2];
1013 break;
1014
1015 case 2:
1016 $domain = $part[0] . $part[1];
1017 break;
1018
1019 case 3:
1020 $domain = $part[0] . $part[2];
1021 break;
1022 }
1023 }
1024 $params = [
1025 'website_type_id' => 1,
1026 'location_type_id' => self::MAIN,
1027 'contact_id' => $cid,
1028 'url' => "http://$domain.org",
1029 ];
1030 $this->_addDAO('Website', $params);
1031 return $params;
1032 }
1033
1034 /**
1035 * Create an email address based on a person's name
1036 * Using common naming patterns
1037 *
1038 * @param $contact obj: individual contact record
1039 * @param $domain str: supply a domain (i.e. for a work address)
1040 *
1041 * @return string
1042 */
1043 private function _individualEmail($contact, $domain = NULL) {
1044 $first = $contact->first_name;
1045 $last = $contact->last_name;
1046 $f = $first[0];
1047 $l = $last[0];
1048 $m = $contact->middle_name ? $contact->middle_name[0] . '.' : '';
1049 // Common naming patterns
1050 switch ($this->randomInt(1, 6)) {
1051 case 1:
1052 $email = $first . $last;
1053 break;
1054
1055 case 2:
1056 $email = "$last.$first";
1057 break;
1058
1059 case 3:
1060 $email = $last . $f;
1061 break;
1062
1063 case 4:
1064 $email = $first . $l;
1065 break;
1066
1067 case 5:
1068 $email = "$last.$m$first";
1069 break;
1070
1071 case 6:
1072 $email = "$f$m$last";
1073 break;
1074 }
1075 //to ensure we dont insert
1076 //invalid characters in email
1077 $email = preg_replace("([^a-zA-Z0-9_\.-]*)", "", $email);
1078
1079 // Some people have numbers in their address
1080 if ($this->probability(.4)) {
1081 $email .= $this->randomInt(1, 99);
1082 }
1083 // Generate random domain if not specified
1084 if (!$domain) {
1085 $domain = $this->randomItem('email_domain') . '.' . $this->randomItem('email_tld');
1086 }
1087 return strtolower($email) . '@' . $domain;
1088 }
1089
1090 /**
1091 * This method populates the civicrm_entity_tag table
1092 */
1093 private function addEntityTag() {
1094
1095 $entity_tag = new CRM_Core_DAO_EntityTag();
1096
1097 // add categories 1,2,3 for Organizations.
1098 for ($i = 0; $i < $this->numOrganization; $i += 2) {
1099 $org_id = $this->Organization[$i];
1100 // echo "org_id = $org_id\n";
1101 $entity_tag->entity_id = $this->Organization[$i];
1102 $entity_tag->entity_table = 'civicrm_contact';
1103 $entity_tag->tag_id = $this->randomInt(1, 3);
1104 $this->_insert($entity_tag);
1105 }
1106
1107 // add categories 4,5 for Individuals.
1108 for ($i = 0; $i < $this->numIndividual; $i += 2) {
1109 $entity_tag->entity_table = 'civicrm_contact';
1110 $entity_tag->entity_id = $this->Individual[$i];
1111 if (($entity_tag->entity_id) % 3) {
1112 $entity_tag->tag_id = $this->randomInt(4, 5);
1113 $this->_insert($entity_tag);
1114 }
1115 else {
1116 // some of the individuals are in both categories (4 and 5).
1117 $entity_tag->tag_id = 4;
1118 $this->_insert($entity_tag);
1119 $entity_tag->tag_id = 5;
1120 $this->_insert($entity_tag);
1121 }
1122 }
1123 }
1124
1125 /**
1126 * This method populates the civicrm_group_contact table
1127 */
1128 private function addGroup() {
1129 // add the 3 groups first
1130 foreach ($this->sampleData['group'] as $groupName) {
1131 $group = new CRM_Contact_BAO_Group();
1132 $group->name = $group->title = $groupName;
1133 $group->group_type = "\ 11\ 12\ 1";
1134 $group->visibility = 'Public Pages';
1135 $group->is_active = 1;
1136 $group->save();
1137 }
1138
1139 // 60 are for newsletter
1140 for ($i = 0; $i < 60; $i++) {
1141 $groupContact = new CRM_Contact_DAO_GroupContact();
1142 // newsletter subscribers
1143 $groupContact->group_id = 2;
1144 $groupContact->contact_id = $this->Individual[$i];
1145 // always add members
1146 $groupContact->status = 'Added';
1147
1148 $subscriptionHistory = new CRM_Contact_DAO_SubscriptionHistory();
1149 $subscriptionHistory->contact_id = $groupContact->contact_id;
1150
1151 $subscriptionHistory->group_id = $groupContact->group_id;
1152 $subscriptionHistory->status = $groupContact->status;
1153 // method
1154 $subscriptionHistory->method = $this->randomItem($this->subscriptionHistoryMethod);
1155 $subscriptionHistory->date = $this->randomDate();
1156 if ($groupContact->status != 'Pending') {
1157 $this->_insert($groupContact);
1158 }
1159 $this->_insert($subscriptionHistory);
1160 }
1161
1162 // 15 volunteers
1163 for ($i = 0; $i < 15; $i++) {
1164 $groupContact = new CRM_Contact_DAO_GroupContact();
1165 // Volunteers
1166 $groupContact->group_id = 3;
1167 $groupContact->contact_id = $this->Individual[$i + 60];
1168 // membership status
1169 $groupContact->status = 'Added';
1170
1171 $subscriptionHistory = new CRM_Contact_DAO_SubscriptionHistory();
1172 $subscriptionHistory->contact_id = $groupContact->contact_id;
1173 $subscriptionHistory->group_id = $groupContact->group_id;
1174 $subscriptionHistory->status = $groupContact->status;
1175 // method
1176 $subscriptionHistory->method = $this->randomItem($this->subscriptionHistoryMethod);
1177 $subscriptionHistory->date = $this->randomDate();
1178
1179 if ($groupContact->status != 'Pending') {
1180 $this->_insert($groupContact);
1181 }
1182 $this->_insert($subscriptionHistory);
1183 }
1184
1185 // 8 advisory board group
1186 for ($i = 0; $i < 8; $i++) {
1187 $groupContact = new CRM_Contact_DAO_GroupContact();
1188 // advisory board group
1189 $groupContact->group_id = 4;
1190 $groupContact->contact_id = $this->Individual[$i * 7];
1191 // membership status
1192 $groupContact->status = 'Added';
1193
1194 $subscriptionHistory = new CRM_Contact_DAO_SubscriptionHistory();
1195 $subscriptionHistory->contact_id = $groupContact->contact_id;
1196 $subscriptionHistory->group_id = $groupContact->group_id;
1197 $subscriptionHistory->status = $groupContact->status;
1198 // method
1199 $subscriptionHistory->method = $this->randomItem($this->subscriptionHistoryMethod);
1200 $subscriptionHistory->date = $this->randomDate();
1201
1202 if ($groupContact->status != 'Pending') {
1203 $this->_insert($groupContact);
1204 }
1205 $this->_insert($subscriptionHistory);
1206 }
1207
1208 //In this function when we add groups that time we are cache the contact fields
1209 //But at the end of setup we are appending sample custom data, so for consistency
1210 //reset the cache.
1211 Civi::cache('fields')->flush();
1212 CRM_Core_BAO_Cache::resetCaches();
1213 }
1214
1215 /**
1216 * This method populates the civicrm_note table
1217 */
1218 private function addNote() {
1219 $params = [
1220 'entity_table' => 'civicrm_contact',
1221 'contact_id' => 1,
1222 'privacy' => 0,
1223 ];
1224 for ($i = 0; $i < self::NUM_CONTACT; $i += 10) {
1225 $params['entity_id'] = $this->randomItem($this->contact);
1226 $params['note'] = $this->randomItem('note');
1227 $params['modified_date'] = $this->randomDate();
1228 $this->_addDAO('Note', $params);
1229 }
1230 }
1231
1232 /**
1233 * This method populates the civicrm_activity_history table
1234 */
1235 private function addActivity() {
1236 $contactDAO = new CRM_Contact_DAO_Contact();
1237 $contactDAO->contact_type = 'Individual';
1238 $contactDAO->selectAdd();
1239 $contactDAO->selectAdd('id');
1240 $contactDAO->orderBy('sort_name');
1241 $contactDAO->find();
1242
1243 $count = 0;
1244 $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
1245 while ($contactDAO->fetch()) {
1246 if ($count++ > 2) {
1247 break;
1248 }
1249 for ($i = 0; $i < self::NUM_ACTIVITY; $i++) {
1250 $activityDAO = new CRM_Activity_DAO_Activity();
1251 $activityId = CRM_Core_OptionGroup::values('activity_type', NULL, NULL, NULL, ' AND v.name IN ("Tell A Friend", "Pledge Acknowledgment")');
1252 $activityTypeID = $this->randomIndex($activityId);
1253 $activity = CRM_Core_PseudoConstant::activityType();
1254 $activityDAO->activity_type_id = $activityTypeID;
1255 $activityDAO->subject = "Subject for $activity[$activityTypeID]";
1256 $activityDAO->activity_date_time = $this->randomDate();
1257 $activityDAO->status_id = 2;
1258 $this->_insert($activityDAO);
1259
1260 $activityContactDAO = new CRM_Activity_DAO_ActivityContact();
1261 $activityContactDAO->activity_id = $activityDAO->id;
1262 $activityContactDAO->contact_id = $contactDAO->id;
1263 $activityContactDAO->record_type_id = CRM_Utils_Array::key('Activity Source', $activityContacts);
1264 $this->_insert($activityContactDAO);
1265
1266 if ($activityTypeID == 9) {
1267 $activityContactDAO = new CRM_Activity_DAO_ActivityContact();
1268 $activityContactDAO->activity_id = $activityDAO->id;
1269 $activityContactDAO->contact_id = $this->randomInt(1, 101);
1270 $activityContactDAO->record_type_id = CRM_Utils_Array::key('Activity Targets', $activityContacts);
1271 $this->_insert($activityContactDAO);
1272 }
1273 }
1274 }
1275 }
1276
1277 /**
1278 * @return array
1279 */
1280 public function getZipCodeInfo() {
1281
1282 if (!$this->stateMap) {
1283 $query = 'SELECT id, name, abbreviation FROM civicrm_state_province WHERE country_id = 1228';
1284 $dao = new CRM_Core_DAO();
1285 $dao->query($query);
1286 $this->stateMap = [];
1287 while ($dao->fetch()) {
1288 $this->stateMap[$dao->abbreviation] = $dao->id;
1289 $this->states[$dao->id] = $dao->name;
1290 }
1291 }
1292
1293 static $zipCodes = NULL;
1294 if ($zipCodes === NULL) {
1295 $zipCodes = json_decode(file_get_contents(self::getCivicrmDir() . '/sql/zipcodes.json'));
1296 }
1297
1298 $zipCode = $zipCodes[$this->randomInt(0, count($zipCodes))];
1299
1300 if ($this->stateMap[$zipCode->state]) {
1301 $stateID = $this->stateMap[$zipCode->state];
1302 }
1303 else {
1304 $stateID = 1004;
1305 }
1306
1307 $zip = str_pad($zipCode->zip, 5, '0', STR_PAD_LEFT);
1308 return [
1309 'country_id' => 1228,
1310 'state_province_id' => $stateID,
1311 'city' => $zipCode->city,
1312 'postal_code' => $zip,
1313 'geo_code_1' => $zipCode->latitude,
1314 'geo_code_2' => $zipCode->longitude,
1315 ];
1316 }
1317
1318 /**
1319 * @param $zipCode
1320 *
1321 * @return array
1322 */
1323 public static function getLatLong($zipCode) {
1324 $query = "http://maps.google.com/maps?q=$zipCode&output=js";
1325 $userAgent = "Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; en-US; rv:1.7.5) Gecko/20041107 Firefox/1.0";
1326
1327 $ch = curl_init();
1328 curl_setopt($ch, CURLOPT_URL, $query);
1329 curl_setopt($ch, CURLOPT_HEADER, FALSE);
1330 curl_setopt($ch, CURLOPT_USERAGENT, $userAgent);
1331 curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
1332
1333 // grab URL and pass it to the browser
1334 $outstr = curl_exec($ch);
1335
1336 // close CURL resource, and free up system resources
1337 curl_close($ch);
1338
1339 $preg = "/'(<\?xml.+?)',/s";
1340 preg_match($preg, $outstr, $matches);
1341 if ($matches[1]) {
1342 $xml = simplexml_load_string($matches[1]);
1343 $attributes = $xml->center->attributes();
1344 if (!empty($attributes)) {
1345 return [(float ) $attributes['lat'], (float ) $attributes['lng']];
1346 }
1347 }
1348 return [NULL, NULL];
1349 }
1350
1351 private function addMembershipType() {
1352 $organizationDAO = new CRM_Contact_DAO_Contact();
1353 $organizationDAO->id = 5;
1354 $organizationDAO->find(TRUE);
1355 $contact_id = $organizationDAO->contact_id;
1356
1357 $membershipType = "INSERT INTO civicrm_membership_type
1358 (name, description, member_of_contact_id, financial_type_id, minimum_fee, duration_unit, duration_interval, period_type, fixed_period_start_day, fixed_period_rollover_day, relationship_type_id, relationship_direction, visibility, weight, is_active)
1359 VALUES
1360 ('General', 'Regular annual membership.', " . $contact_id . ", 2, 100, 'year', 1, 'rolling',null, null, 7, 'b_a', 'Public', 1, 1),
1361 ('Student', 'Discount membership for full-time students.', " . $contact_id . ", 2, 50, 'year', 1, 'rolling', null, null, 7, 'b_a', 'Public', 2, 1),
1362 ('Lifetime', 'Lifetime membership.', " . $contact_id . ", 2, 1200, 'lifetime', 1, 'rolling', null, null, 7, 'b_a', 'Admin', 3, 1);
1363 ";
1364 $this->_query($membershipType);
1365 }
1366
1367 private function addMembership() {
1368 $contact = new CRM_Contact_DAO_Contact();
1369 $contact->query("SELECT id FROM civicrm_contact WHERE contact_type = 'Individual'");
1370 $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
1371 while ($contact->fetch()) {
1372 $contacts[] = $contact->id;
1373 }
1374 $contacts = $this->shuffle($contacts);
1375
1376 $randomContacts = array_slice($contacts, 20, 30);
1377
1378 $sources = ['Payment', 'Donation', 'Check'];
1379 $membershipTypes = [1, 2];
1380 $membershipTypeNames = ['General', 'Student'];
1381 $statuses = [3, 4];
1382
1383 $membership = "
1384 INSERT INTO civicrm_membership
1385 (contact_id, membership_type_id, join_date, start_date, end_date, source, status_id)
1386 VALUES
1387 ";
1388
1389 $activity = "
1390 INSERT INTO civicrm_activity
1391 (source_record_id, activity_type_id, subject, activity_date_time, duration, location, phone_id, phone_number, details, priority_id,parent_id, is_test, status_id)
1392 VALUES
1393 ";
1394
1395 $activityContact = "
1396 INSERT INTO civicrm_activity_contact
1397 (activity_id, contact_id, record_type_id)
1398 VALUES
1399 ";
1400
1401 $currentActivityID = CRM_Core_DAO::singleValueQuery("SELECT MAX(id) FROM civicrm_activity");
1402 $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
1403 foreach ($randomContacts as $count => $dontCare) {
1404 $source = $this->randomItem($sources);
1405 $activitySourceId = $count + 1;
1406 $currentActivityID++;
1407 $activityContact .= "( $currentActivityID, {$randomContacts[$count]}, {$sourceID} )";
1408 if ((($count + 1) % 11 == 0)) {
1409 // lifetime membership, status can be anything
1410 $startDate = date('Y-m-d', mktime(0, 0, 0, date('m'), (date('d') - $count), date('Y')));
1411 $membership .= "( {$randomContacts[$count]}, 3, '{$startDate}', '{$startDate}', null, '{$source}', 1)";
1412 $activity .= "( {$activitySourceId}, 7, 'Lifetime', '{$startDate} 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 )";
1413 }
1414 elseif (($count + 1) % 5 == 0) {
1415 // Grace or expired, memberhsip type is random of 1 & 2
1416 $randIndex = $this->randomIndex($membershipTypes);
1417 $membershipTypeId = $membershipTypes[$randIndex];
1418 $membershipStatusId = $statuses[$randIndex];
1419 $membershipTypeName = $membershipTypeNames[$randIndex];
1420 $YearFactor = $membershipTypeId * 2;
1421 //reverse the type and consider as year factor.
1422 if ($YearFactor != 2) {
1423 $YearFactor = 1;
1424 }
1425 $dateFactor = ($count * ($YearFactor) * ($YearFactor) * ($YearFactor));
1426 $startDate = date('Y-m-d', mktime(0, 0, 0,
1427 date('m'),
1428 (date('d') - ($dateFactor)),
1429 (date('Y') - ($YearFactor))
1430 ));
1431 $partOfDate = explode('-', $startDate);
1432 $endDate = date('Y-m-d', mktime(0, 0, 0,
1433 $partOfDate[1],
1434 ($partOfDate[2] - 1),
1435 ($partOfDate[0] + ($YearFactor))
1436 ));
1437
1438 $membership .= "( {$randomContacts[$count]}, {$membershipTypeId}, '{$startDate}', '{$startDate}', '{$endDate}', '{$source}', {$membershipStatusId})";
1439 $activity .= "( {$activitySourceId}, 7, '{$membershipTypeName}', '{$startDate} 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 )";
1440 }
1441 elseif (($count + 1) % 2 == 0) {
1442 // membership type 2
1443 $startDate = date('Y-m-d', mktime(0, 0, 0, date('m'), (date('d') - $count), date('Y')));
1444 $endDate = date('Y-m-d', mktime(0, 0, 0, date('m'), (date('d') - ($count + 1)), (date('Y') + 1)));
1445 $membership .= "( {$randomContacts[$count]}, 2, '{$startDate}', '{$startDate}', '{$endDate}', '{$source}', 1)";
1446 $activity .= "( {$activitySourceId}, 7, 'Student', '{$startDate} 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 )";
1447 }
1448 else {
1449 // membership type 1
1450 $startDate = date('Y-m-d', mktime(0, 0, 0, date('m'), (date('d') - $count), date('Y')));
1451 $endDate = date('Y-m-d', mktime(0, 0, 0, date('m'), (date('d') - ($count + 1)), (date('Y') + 2)));
1452 $membership .= "( {$randomContacts[$count]}, 1, '{$startDate}', '{$startDate}', '{$endDate}', '{$source}', 1)";
1453 $activity .= "( {$activitySourceId}, 7, 'General', '{$startDate} 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 )";
1454 }
1455
1456 if ($count != 29) {
1457 $membership .= ",";
1458 $activity .= ",";
1459 $activityContact .= ",";
1460 }
1461 }
1462
1463 $this->_query($membership);
1464 $this->_query($activity);
1465 $this->_query($activityContact);
1466 }
1467
1468 /**
1469 * @param $date
1470 *
1471 * @return string
1472 */
1473 public static function repairDate($date) {
1474 $dropArray = ['-' => '', ':' => '', ' ' => ''];
1475 return strtr($date, $dropArray);
1476 }
1477
1478 private function addMembershipLog() {
1479 $membership = new CRM_Member_DAO_Membership();
1480 $membership->query("SELECT id FROM civicrm_membership");
1481 while ($membership->fetch()) {
1482 $ids[] = $membership->id;
1483 }
1484 foreach ($ids as $id) {
1485 $membership = new CRM_Member_DAO_Membership();
1486 $membership->id = $id;
1487 $membershipLog = new CRM_Member_DAO_MembershipLog();
1488 if ($membership->find(TRUE)) {
1489 $membershipLog->membership_id = $membership->id;
1490 $membershipLog->status_id = $membership->status_id;
1491 $membershipLog->start_date = self::repairDate($membership->start_date);
1492 $membershipLog->end_date = self::repairDate($membership->end_date);
1493 $membershipLog->modified_id = $membership->contact_id;
1494 $membershipLog->modified_date = date("Ymd");
1495 $membershipLog->membership_type_id = $membership->membership_type_id;
1496 $membershipLog->save();
1497 }
1498 $membershipLog = NULL;
1499 }
1500 }
1501
1502 private function addEvent() {
1503 $event = "INSERT INTO civicrm_address ( contact_id, location_type_id, is_primary, is_billing, street_address, street_number, street_number_suffix, street_number_predirectional, street_name, street_type, street_number_postdirectional, street_unit, supplemental_address_1, supplemental_address_2, supplemental_address_3, city, county_id, state_province_id, postal_code_suffix, postal_code, usps_adc, country_id, geo_code_1, geo_code_2, timezone)
1504 VALUES
1505 ( NULL, 1, 1, 1, '14S El Camino Way E', 14, 'S', NULL, 'El Camino', 'Way', NULL, NULL, NULL, NULL, NULL, 'Collinsville', NULL, 1006, NULL, '6022', NULL, 1228, 41.8328, -72.9253, NULL),
1506 ( NULL, 1, 1, 1, '11B Woodbridge Path SW', 11, 'B', NULL, 'Woodbridge', 'Path', NULL, NULL, NULL, NULL, NULL, 'Dayton', NULL, 1034, NULL, '45417', NULL, 1228, 39.7531, -84.2471, NULL),
1507 ( NULL, 1, 1, 1, '581O Lincoln Dr SW', 581, 'O', NULL, 'Lincoln', 'Dr', NULL, NULL, NULL, NULL, NULL, 'Santa Fe', NULL, 1030, NULL, '87594', NULL, 1228, 35.5212, -105.982, NULL)
1508 ";
1509 $this->_query($event);
1510
1511 $sql = "SELECT id FROM civicrm_address WHERE street_address = '14S El Camino Way E'";
1512 $eventAdd1 = CRM_Core_DAO::singleValueQuery($sql);
1513 $sql = "SELECT id FROM civicrm_address WHERE street_address = '11B Woodbridge Path SW'";
1514 $eventAdd2 = CRM_Core_DAO::singleValueQuery($sql);
1515 $sql = "SELECT id FROM civicrm_address WHERE street_address = '581O Lincoln Dr SW'";
1516 $eventAdd3 = CRM_Core_DAO::singleValueQuery($sql);
1517
1518 $event = "INSERT INTO civicrm_email (contact_id, location_type_id, email, is_primary, is_billing, on_hold, hold_date, reset_date)
1519 VALUES
1520 (NULL, 1, 'development@example.org', 0, 0, 0, NULL, NULL),
1521 (NULL, 1, 'tournaments@example.org', 0, 0, 0, NULL, NULL),
1522 (NULL, 1, 'celebration@example.org', 0, 0, 0, NULL, NULL)
1523 ";
1524 $this->_query($event);
1525
1526 $sql = "SELECT id FROM civicrm_email WHERE email = 'development@example.org'";
1527 $eventEmail1 = CRM_Core_DAO::singleValueQuery($sql);
1528 $sql = "SELECT id FROM civicrm_email WHERE email = 'tournaments@example.org'";
1529 $eventEmail2 = CRM_Core_DAO::singleValueQuery($sql);
1530 $sql = "SELECT id FROM civicrm_email WHERE email = 'celebration@example.org'";
1531 $eventEmail3 = CRM_Core_DAO::singleValueQuery($sql);
1532
1533 $event = "INSERT INTO civicrm_phone (contact_id, location_type_id, is_primary, is_billing, mobile_provider_id, phone, phone_numeric, phone_type_id)
1534 VALUES
1535 (NULL, 1, 0, 0, NULL, '204 222-1000', '2042221000', '1'),
1536 (NULL, 1, 0, 0, NULL, '204 223-1000', '2042231000', '1'),
1537 (NULL, 1, 0, 0, NULL, '303 323-1000', '3033231000', '1')
1538 ";
1539 $this->_query($event);
1540
1541 $sql = "SELECT id FROM civicrm_phone WHERE phone = '204 222-1000'";
1542 $eventPhone1 = CRM_Core_DAO::singleValueQuery($sql);
1543 $sql = "SELECT id FROM civicrm_phone WHERE phone = '204 223-1000'";
1544 $eventPhone2 = CRM_Core_DAO::singleValueQuery($sql);
1545 $sql = "SELECT id FROM civicrm_phone WHERE phone = '303 323-1000'";
1546 $eventPhone3 = CRM_Core_DAO::singleValueQuery($sql);
1547
1548 $event = "INSERT INTO civicrm_loc_block ( address_id, email_id, phone_id, address_2_id, email_2_id, phone_2_id)
1549 VALUES
1550 ( $eventAdd1, $eventEmail1, $eventPhone1, NULL,NULL,NULL),
1551 ( $eventAdd2, $eventEmail2, $eventPhone2, NULL,NULL,NULL),
1552 ( $eventAdd3, $eventEmail3, $eventPhone3, NULL,NULL,NULL)
1553 ";
1554
1555 $this->_query($event);
1556
1557 $sql = "SELECT id from civicrm_loc_block where phone_id = $eventPhone1 AND email_id = $eventEmail1 AND address_id = $eventAdd1";
1558 $eventLok1 = CRM_Core_DAO::singleValueQuery($sql);
1559 $sql = "SELECT id from civicrm_loc_block where phone_id = $eventPhone2 AND email_id = $eventEmail2 AND address_id = $eventAdd2";
1560 $eventLok2 = CRM_Core_DAO::singleValueQuery($sql);
1561 $sql = "SELECT id from civicrm_loc_block where phone_id = $eventPhone3 AND email_id = $eventEmail3 AND address_id = $eventAdd3";
1562 $eventLok3 = CRM_Core_DAO::singleValueQuery($sql);
1563
1564 $event = "INSERT INTO civicrm_event
1565 ( title, summary, description, event_type_id, participant_listing_id, is_public, start_date, end_date, is_online_registration, registration_link_text, max_participants, event_full_text, is_monetary, financial_type_id, is_map, is_active, fee_label, is_show_location, loc_block_id,intro_text, footer_text, confirm_title, confirm_text, confirm_footer_text, is_email_confirm, confirm_email_text, confirm_from_name, confirm_from_email, cc_confirm, bcc_confirm, default_fee_id, thankyou_title, thankyou_text, thankyou_footer_text, is_pay_later, pay_later_text, pay_later_receipt, is_multiple_registrations, allow_same_participant_emails, currency )
1566 VALUES
1567 ( 'Fall Fundraiser Dinner', 'Kick up your heels at our Fall Fundraiser Dinner/Dance at Glen Echo Park! Come by yourself or bring a partner, friend or the entire family!', 'This event benefits our teen programs. Admission includes a full 3 course meal and wine or soft drinks. Grab your dancing shoes, bring the kids and come join the party!', 3, 1, 1, '" . date('Y-m-d 17:00:00', strtotime("+6 months", $this->time)) . "', '" . date('Y-m-d 17:00:00', strtotime("+6 months +2 days", $this->time)) . "', 1, 'Register Now', 100, 'Sorry! The Fall Fundraiser Dinner is full. Please call Jane at 204 222-1000 ext 33 if you want to be added to the waiting list.', 1, 4, 1, 1, 'Dinner Contribution', 1 ,$eventLok1,'Fill in the information below to join as at this wonderful dinner event.', NULL, 'Confirm Your Registration Information', 'Review the information below carefully.', NULL, 1, 'Contact the Development Department if you need to make any changes to your registration.', 'Fundraising Dept.', 'development@example.org', NULL, NULL, NULL, 'Thanks for Registering!', '<p>Thank you for your support. Your contribution will help us build even better tools.</p><p>Please tell your friends and colleagues about this wonderful event.</p>', '<p><a href=https://civicrm.org>Back to CiviCRM Home Page</a></p>', 1, 'I will send payment by check', 'Send a check payable to Our Organization within 3 business days to hold your reservation. Checks should be sent to: 100 Main St., Suite 3, San Francisco CA 94110', 1, 0, 'USD' ),
1568 ( 'Summer Solstice Festival Day Concert', 'Festival Day is coming! Join us and help support your parks.', 'We will gather at noon, learn a song all together, and then join in a joyous procession to the pavilion. We will be one of many groups performing at this wonderful concert which benefits our city parks.', 5, 1, 1, '" . date('Y-m-d 12:00:00', strtotime("-1 day", $this->time)) . "', '" . date('Y-m-d 17:00:00', strtotime("-1 day", $this->time)) . "', 1, 'Register Now', 50, 'We have all the singers we can handle. Come to the pavilion anyway and join in from the audience.', 1, 2, NULL, 1, 'Festival Fee', 1, $eventLok2, 'Complete the form below and click Continue to register online for the festival. Or you can register by calling us at 204 222-1000 ext 22.', '', 'Confirm Your Registration Information', '', '', 1, 'This email confirms your registration. If you have questions or need to change your registration - please do not hesitate to call us.', 'Event Dept.', 'events@example.org', '', NULL, NULL, 'Thanks for Your Joining In!', '<p>Thank you for your support. Your participation will help build new parks.</p><p>Please tell your friends and colleagues about the concert.</p>', '<p><a href=https://civicrm.org>Back to CiviCRM Home Page</a></p>', 0, NULL, NULL, 1, 0, 'USD' ),
1569 ( 'Rain-forest Cup Youth Soccer Tournament', 'Sign up your team to participate in this fun tournament which benefits several Rain-forest protection groups in the Amazon basin.', 'This is a FYSA Sanctioned Tournament, which is open to all USSF/FIFA affiliated organizations for boys and girls in age groups: U9-U10 (6v6), U11-U12 (8v8), and U13-U17 (Full Sided).', 3, 1, 1, '" . date('Y-m-d 07:00:00', strtotime("+7 months", $this->time)) . "', '" . date('Y-m-d 17:00:00', strtotime("+7 months +3 days", $this->time)) . "', 1, 'Register Now', 500, 'Sorry! All available team slots for this tournament have been filled. Contact Jill Futbol for information about the waiting list and next years event.', 1, 4, NULL, 1, 'Tournament Fees',1, $eventLok3, 'Complete the form below to register your team for this year''s tournament.', '<em>A Soccer Youth Event</em>', 'Review and Confirm Your Registration Information', '', '<em>A Soccer Youth Event</em>', 1, 'Contact our Tournament Director for eligibility details.', 'Tournament Director', 'tournament@example.org', '', NULL, NULL, 'Thanks for Your Support!', '<p>Thank you for your support. Your participation will help save thousands of acres of rainforest.</p>', '<p><a href=https://civicrm.org>Back to CiviCRM Home Page</a></p>', 0, NULL, NULL, 0, 0, 'USD' )
1570 ";
1571 $this->_query($event);
1572
1573 //CRM-4464
1574 $eventTemplates = "INSERT INTO civicrm_event
1575 ( is_template, template_title, event_type_id, default_role_id, participant_listing_id, is_public, is_monetary, is_online_registration, is_multiple_registrations, allow_same_participant_emails, is_email_confirm, financial_type_id, fee_label, confirm_title, thankyou_title, confirm_from_name, confirm_from_email, is_active, currency )
1576 VALUES
1577 ( 1, 'Free Meeting without Online Registration', 4, 1, 1, 1, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 'USD' ),
1578 ( 1, 'Free Meeting with Online Registration', 4, 1, 1, 1, 0, 1, 1, 1, 0, NULL, NULL, 'Confirm Your Registration Information', 'Thanks for Registering!', NULL, NULL, 1, 'USD' ),
1579 ( 1, 'Paid Conference with Online Registration', 1, 1, 1, 1, 1, 1, 1, 1, 1, 4, 'Conference Fee', 'Confirm Your Registration Information', 'Thanks for Registering!', 'Event Template Dept.', 'event_templates@example.org', 1, 'USD' )";
1580
1581 $this->_query($eventTemplates);
1582
1583 $ufJoinValues = $tellFriendValues = [];
1584 $profileID = CRM_Core_DAO::singleValueQuery("SELECT id FROM civicrm_uf_group WHERE name ='event_registration'");
1585
1586 // grab id's for all events and event templates
1587 $query = "
1588 SELECT id
1589 FROM civicrm_event";
1590
1591 $template = CRM_Core_DAO::executeQuery($query);
1592 while ($template->fetch()) {
1593 if ($profileID) {
1594 $ufJoinValues[] = "( 1, 'CiviEvent', 'civicrm_event', {$template->id}, 1, {$profileID} )";
1595 }
1596 $tellFriendValues[] = "( 'civicrm_event', {$template->id}, 'Tell A Friend', '<p>Help us spread the word about this event. Use the space below to personalize your email message - let your friends know why you''re attending. Then fill in the name(s) and email address(es) and click ''Send Your Message''.</p>', 'Thought you might be interested in checking out this event. I''m planning on attending.', NULL, 'Thanks for Spreading the Word', '<p>Thanks for spreading the word about this event to your friends.</p>', 1)";
1597 }
1598
1599 //insert values in civicrm_uf_join for the required event_registration profile - CRM-9587
1600 if (!empty($ufJoinValues)) {
1601 $includeProfile = "INSERT INTO civicrm_uf_join
1602 (is_active, module, entity_table, entity_id, weight, uf_group_id )
1603 VALUES " . implode(',', $ufJoinValues);
1604 $this->_query($includeProfile);
1605 }
1606
1607 //insert values in civicrm_tell_friend
1608 if (!empty($tellFriendValues)) {
1609 $tellFriend = "INSERT INTO civicrm_tell_friend
1610 (entity_table, entity_id, title, intro, suggested_message,
1611 general_link, thankyou_title, thankyou_text, is_active)
1612 VALUES " . implode(',', $tellFriendValues);
1613 $this->_query($tellFriend);
1614 }
1615 }
1616
1617 private function addParticipant() {
1618 $contact = new CRM_Contact_DAO_Contact();
1619 $contact->query("SELECT id FROM civicrm_contact");
1620 while ($contact->fetch()) {
1621 $contacts[] = $contact->id;
1622 }
1623 $contacts = $this->shuffle($contacts);
1624 $randomContacts = array_slice($contacts, 20, 50);
1625
1626 $participant = "
1627 INSERT INTO civicrm_participant
1628 (contact_id, event_id, status_id, role_id, register_date, source, fee_level, is_test, fee_amount, fee_currency)
1629 VALUES
1630 ( " . $randomContacts[0] . ", 1, 1, 1, '2009-01-21', 'Check', 'Single', 0, 50, 'USD'),
1631 ( " . $randomContacts[1] . ", 2, 2, 2, '2008-05-07', 'Credit Card', 'Soprano', 0, 50, 'USD'),
1632 ( " . $randomContacts[2] . ", 3, 3, 3, '2008-05-05', 'Credit Card', 'Tiny-tots (ages 5-8)', 0, 800, 'USD') ,
1633 ( " . $randomContacts[3] . ", 1, 4, 4, '2008-10-21', 'Direct Transfer', 'Single', 0, 50, 'USD'),
1634 ( " . $randomContacts[4] . ", 2, 1, 1, '2008-01-10', 'Check', 'Soprano', 0, 50, 'USD'),
1635 ( " . $randomContacts[5] . ", 3, 2, 2, '2008-03-05', 'Direct Transfer', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1636 ( " . $randomContacts[6] . ", 1, 3, 3, '2009-07-21', 'Direct Transfer', 'Single', 0, 50, 'USD'),
1637 ( " . $randomContacts[7] . ", 2, 4, 4, '2009-03-07', 'Credit Card', 'Soprano', 0, 50, 'USD'),
1638 ( " . $randomContacts[8] . ", 3, 1, 1, '2008-02-05', 'Direct Transfer', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1639 ( " . $randomContacts[9] . ", 1, 2, 2, '2008-02-01', 'Check', 'Single', 0, 50, 'USD'),
1640 ( " . $randomContacts[10] . ", 2, 3, 3, '2009-01-10', 'Direct Transfer', 'Soprano', 0, 50, 'USD'),
1641 ( " . $randomContacts[11] . ", 3, 4, 4, '2009-03-06', 'Credit Card', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1642 ( " . $randomContacts[12] . ", 1, 1, 2, '2008-06-04', 'Credit Card', 'Single', 0, 50, 'USD'),
1643 ( " . $randomContacts[13] . ", 2, 2, 3, '2008-01-10', 'Direct Transfer', 'Soprano', 0, 50, 'USD'),
1644 ( " . $randomContacts[14] . ", 3, 4, 1, '2008-07-04', 'Check', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1645 ( " . $randomContacts[15] . ", 1, 4, 2, '2009-01-21', 'Credit Card', 'Single', 0, 50, 'USD'),
1646 ( " . $randomContacts[16] . ", 2, 2, 3, '2008-01-10', 'Credit Card', 'Soprano', 0, 50, 'USD'),
1647 ( " . $randomContacts[17] . ", 3, 3, 1, '2009-03-05', 'Credit Card', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1648 ( " . $randomContacts[18] . ", 1, 2, 1, '2008-10-21', 'Direct Transfer', 'Single', 0, 50, 'USD'),
1649 ( " . $randomContacts[19] . ", 2, 4, 1, '2009-01-10', 'Credit Card', 'Soprano', 0, 50, 'USD'),
1650 ( " . $randomContacts[20] . ", 3, 1, 4, '2008-03-25', 'Check', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1651 ( " . $randomContacts[21] . ", 1, 2, 3, '2009-10-21', 'Direct Transfer', 'Single', 0, 50, 'USD'),
1652 ( " . $randomContacts[22] . ", 2, 4, 1, '2008-01-10', 'Direct Transfer', 'Soprano', 0, 50, 'USD'),
1653 ( " . $randomContacts[23] . ", 3, 3, 1, '2008-03-11', 'Credit Card', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1654 ( " . $randomContacts[24] . ", 3, 2, 2, '2008-04-05', 'Direct Transfer', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1655 ( " . $randomContacts[25] . ", 1, 1, 1, '2009-01-21', 'Check', 'Single', 0, 50, 'USD'),
1656 ( " . $randomContacts[26] . ", 2, 2, 2, '2008-05-07', 'Credit Card', 'Soprano', 0, 50, 'USD'),
1657 ( " . $randomContacts[27] . ", 3, 3, 3, '2009-12-12', 'Direct Transfer', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1658 ( " . $randomContacts[28] . ", 1, 4, 4, '2009-12-13', 'Credit Card', 'Single', 0, 50, 'USD'),
1659 ( " . $randomContacts[29] . ", 2, 1, 1, '2009-12-14', 'Direct Transfer', 'Soprano', 0, 50, 'USD'),
1660 ( " . $randomContacts[30] . ", 3, 2, 2, '2009-12-15', 'Credit Card', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1661 ( " . $randomContacts[31] . ", 1, 3, 3, '2009-07-21', 'Check', 'Single', 0, 50, 'USD'),
1662 ( " . $randomContacts[32] . ", 2, 4, 4, '2009-03-07', 'Direct Transfer', 'Soprano', 0, 50, 'USD'),
1663 ( " . $randomContacts[33] . ", 3, 1, 1, '2009-12-15', 'Credit Card', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1664 ( " . $randomContacts[34] . ", 1, 2, 2, '2009-12-13', 'Direct Transfer', 'Single', 0, 50, 'USD'),
1665 ( " . $randomContacts[35] . ", 2, 3, 3, '2009-01-10', 'Direct Transfer', 'Soprano', 0, 50, 'USD'),
1666 ( " . $randomContacts[36] . ", 3, 4, 4, '2009-03-06', 'Check', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1667 ( " . $randomContacts[37] . ", 1, 1, 2, '2009-12-13', 'Direct Transfer', 'Single', 0, 50, 'USD'),
1668 ( " . $randomContacts[38] . ", 2, 2, 3, '2008-01-10', 'Direct Transfer', 'Soprano', 0, 50, 'USD'),
1669 ( " . $randomContacts[39] . ", 3, 4, 1, '2009-12-14', 'Credit Card', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1670 ( " . $randomContacts[40] . ", 1, 4, 2, '2009-01-21', 'Credit Card', 'Single', 0, 50, 'USD'),
1671 ( " . $randomContacts[41] . ", 2, 2, 3, '2009-12-15', 'Credit Card', 'Soprano', 0, 50, 'USD'),
1672 ( " . $randomContacts[42] . ", 3, 3, 1, '2009-03-05', 'Credit Card', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1673 ( " . $randomContacts[43] . ", 1, 2, 1, '2009-12-13', 'Direct Transfer', 'Single', 0, 50, 'USD'),
1674 ( " . $randomContacts[44] . ", 2, 4, 1, '2009-01-10', 'Direct Transfer', 'Soprano', 0, 50, 'USD'),
1675 ( " . $randomContacts[45] . ", 3, 1, 4, '2009-12-13', 'Check', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1676 ( " . $randomContacts[46] . ", 1, 2, 3, '2009-10-21', 'Credit Card', 'Single', 0, 50, 'USD'),
1677 ( " . $randomContacts[47] . ", 2, 4, 1, '2009-12-10', 'Credit Card', 'Soprano', 0, 50, 'USD'),
1678 ( " . $randomContacts[48] . ", 3, 3, 1, '2009-03-11', 'Credit Card', 'Tiny-tots (ages 5-8)', 0, 800, 'USD'),
1679 ( " . $randomContacts[49] . ", 3, 2, 2, '2009-04-05', 'Check', 'Tiny-tots (ages 5-8)', 0, 800, 'USD');
1680 ";
1681 $this->_query($participant);
1682
1683 $query = "
1684 INSERT INTO civicrm_activity
1685 (source_record_id, activity_type_id, subject, activity_date_time, duration, location, phone_id, phone_number, details, priority_id,parent_id, is_test, status_id)
1686 VALUES
1687 (01, 5, 'NULL', '2009-01-21 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1688 (02, 5, 'NULL', '2008-05-07 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1689 (03, 5, 'NULL', '2008-05-05 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1690 (04, 5, 'NULL', '2008-10-21 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1691 (05, 5, 'NULL', '2008-01-10 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1692 (06, 5, 'NULL', '2008-03-05 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1693 (07, 5, 'NULL', '2009-07-21 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1694 (08, 5, 'NULL', '2009-03-07 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1695 (09, 5, 'NULL', '2008-02-05 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1696 (10, 5, 'NULL', '2008-02-01 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1697 (11, 5, 'NULL', '2009-01-10 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1698 (12, 5, 'NULL', '2009-03-06 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1699 (13, 5, 'NULL', '2008-06-04 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1700 (14, 5, 'NULL', '2008-01-10 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1701 (15, 5, 'NULL', '2008-07-04 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1702 (16, 5, 'NULL', '2009-01-21 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1703 (17, 5, 'NULL', '2008-01-10 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1704 (18, 5, 'NULL', '2009-03-05 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1705 (19, 5, 'NULL', '2008-10-21 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1706 (20, 5, 'NULL', '2009-01-10 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1707 (21, 5, 'NULL', '2008-03-25 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1708 (22, 5, 'NULL', '2009-10-21 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1709 (23, 5, 'NULL', '2008-01-10 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1710 (24, 5, 'NULL', '2008-03-11 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1711 (25, 5, 'NULL', '2008-04-05 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1712 (26, 5, 'NULL', '2009-01-21 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1713 (27, 5, 'NULL', '2008-05-07 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1714 (28, 5, 'NULL', '2009-12-12 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1715 (29, 5, 'NULL', '2009-12-13 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1716 (30, 5, 'NULL', '2009-12-14 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1717 (31, 5, 'NULL', '2009-12-15 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1718 (32, 5, 'NULL', '2009-07-21 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1719 (33, 5, 'NULL', '2009-03-07 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1720 (34, 5, 'NULL', '2009-12-15 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1721 (35, 5, 'NULL', '2009-12-13 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1722 (36, 5, 'NULL', '2009-01-10 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1723 (37, 5, 'NULL', '2009-03-06 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1724 (38, 5, 'NULL', '2009-12-13 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1725 (39, 5, 'NULL', '2008-01-10 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1726 (40, 5, 'NULL', '2009-12-14 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1727 (41, 5, 'NULL', '2009-01-21 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1728 (42, 5, 'NULL', '2009-12-15 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1729 (43, 5, 'NULL', '2009-03-05 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1730 (44, 5, 'NULL', '2009-12-13 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1731 (45, 5, 'NULL', '2009-01-10 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1732 (46, 5, 'NULL', '2009-12-13 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1733 (47, 5, 'NULL', '2009-10-21 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1734 (48, 5, 'NULL', '2009-12-10 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1735 (49, 5, 'NULL', '2009-03-11 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 ),
1736 (50, 5, 'NULL', '2009-04-05 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2 )
1737 ";
1738 $this->_query($query);
1739
1740 $activityContact = "
1741 INSERT INTO civicrm_activity_contact
1742 (contact_id, activity_id, record_type_id)
1743 VALUES
1744 ";
1745 $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
1746 $currentActivityID = CRM_Core_DAO::singleValueQuery("SELECT MAX(id) FROM civicrm_activity");
1747 $currentActivityID -= 50;
1748 $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
1749 for ($i = 0; $i < 50; $i++) {
1750 $currentActivityID++;
1751 $activityContact .= "({$randomContacts[$i]}, $currentActivityID, $sourceID)";
1752 if ($i != 49) {
1753 $activityContact .= ", ";
1754 }
1755 }
1756 $this->_query($activityContact);
1757 }
1758
1759 private function addPCP() {
1760 $query = "
1761 INSERT INTO `civicrm_pcp`
1762 (contact_id, status_id, title, intro_text, page_text, donate_link_text, page_id, page_type, is_thermometer, is_honor_roll, goal_amount, currency, is_active, pcp_block_id, is_notify)
1763 VALUES
1764 ({$this->Individual[3]}, 2, 'My Personal Civi Fundraiser', 'I''m on a mission to get all my friends and family to help support my favorite open-source civic sector CRM.', '<p>Friends and family - please help build much needed infrastructure for the civic sector by supporting my personal campaign!</p>\r\n<p><a href=\"https://civicrm.org\">You can learn more about CiviCRM here</a>.</p>\r\n<p>Then click the <strong>Contribute Now</strong> button to go to our easy-to-use online contribution form.</p>', 'Contribute Now', 1, 'contribute', 1, 1, 5000.00, 'USD', 1, 1, 1);
1765 ";
1766 $this->_query($query);
1767 }
1768
1769 private function addContribution() {
1770 $defaults = [
1771 'financial_type_id' => 1,
1772 'payment_instrument_id' => 4,
1773 'receive_date' => 'now',
1774 'non_deductible_amount' => 0,
1775 'total_amount' => 25,
1776 'trxn_id' => '',
1777 'check_number' => '',
1778 'currency' => 'USD',
1779 'cancel_date' => NULL,
1780 'cancel_reason' => NULL,
1781 'receipt_date' => NULL,
1782 'thankyou_date' => NULL,
1783 'source' => 'April Mailer 1',
1784 'contribution_recur_id' => NULL,
1785 ];
1786 $contributions = [
1787 1 => [
1788 'contact_id' => 2,
1789 'receive_date' => '10 years ago',
1790 'total_amount' => 125,
1791 'check_number' => '1041',
1792 ],
1793 2 => [
1794 'contact_id' => 4,
1795 'payment_instrument_id' => 1,
1796 'receive_date' => '2 years 3 months ago',
1797 'total_amount' => 50,
1798 'trxn_id' => 'P20901X1',
1799 'source' => 'Online: Save the Penguins',
1800 ],
1801 3 => [
1802 'contact_id' => 6,
1803 'receive_date' => '6 years 25 days 780 minutes ago',
1804 'total_amount' => 25,
1805 'trxn_id' => 'GBP12',
1806 'check_number' => '2095',
1807 'currency' => 'GBP',
1808 ],
1809 4 => [
1810 'contact_id' => 8,
1811 'receive_date' => '2 years 3 months ago',
1812 'total_amount' => 50,
1813 'check_number' => '10552',
1814 'source' => 'Online: Save the Penguins',
1815 ],
1816 5 => [
1817 'contact_id' => 4,
1818 'payment_instrument_id' => 1,
1819 'receive_date' => '2 years 3 months ago',
1820 'total_amount' => 50,
1821 'trxn_id' => 'Q90901X1',
1822 'source' => 'Online: Save the Penguins',
1823 ],
1824 6 => [
1825 'contact_id' => 16,
1826 'receive_date' => '85 days 42 minutes ago',
1827 'total_amount' => 500,
1828 'check_number' => '509',
1829 ],
1830 7 => [
1831 'contact_id' => 19,
1832 'payment_instrument_id' => 1,
1833 'receive_date' => '2 days ago',
1834 'total_amount' => 1750,
1835 'check_number' => '102',
1836 'source' => 'Online: Save the Penguins',
1837 ],
1838 8 => [
1839 'contact_id' => 82,
1840 'payment_instrument_id' => 1,
1841 'receive_date' => '340789 minutes ago',
1842 'total_amount' => 50,
1843 'trxn_id' => 'P20193L2',
1844 'source' => 'Online: Save the Penguins',
1845 ],
1846 9 => [
1847 'contact_id' => 92,
1848 'payment_instrument_id' => 1,
1849 'receive_date' => '11 months ago',
1850 'total_amount' => 10,
1851 'trxn_id' => 'P40232Y3',
1852 'source' => 'Online: Help CiviCRM',
1853 ],
1854 10 => [
1855 'contact_id' => 34,
1856 'payment_instrument_id' => 1,
1857 'receive_date' => '52 months 33000 minutes ago',
1858 'total_amount' => 250,
1859 'trxn_id' => 'P20193L6',
1860 'source' => 'Online: Help CiviCRM',
1861 ],
1862 11 => [
1863 'contact_id' => 71,
1864 'payment_instrument_id' => 1,
1865 'receive_date' => '28 hours ago',
1866 'total_amount' => 500,
1867 'trxn_id' => 'PL71',
1868 'source' => '',
1869 'currency' => 'JPY',
1870 ],
1871
1872 12 => [
1873 'contact_id' => 43,
1874 'payment_instrument_id' => 1,
1875 'receive_date' => '15 months 38000 seconds ago',
1876 'total_amount' => 50,
1877 'trxn_id' => 'P291X1',
1878 'source' => 'Online: Save the Penguins',
1879 ],
1880 13 => [
1881 'contact_id' => 32,
1882 'payment_instrument_id' => 1,
1883 'receive_date' => 'midnight 3 months ago',
1884 'total_amount' => 50,
1885 'trxn_id' => 'PL32I',
1886 'source' => '',
1887 ],
1888 14 => [
1889 'contact_id' => 32,
1890 'payment_instrument_id' => 1,
1891 'receive_date' => 'midnight 2 months ago',
1892 'total_amount' => 50,
1893 'trxn_id' => 'PL32II',
1894 'source' => '',
1895 ],
1896 ];
1897 $recurrings = [
1898 [
1899 'contact_id' => 59,
1900 'amount' => 25,
1901 'currency' => 'USD',
1902 'frequency_interval' => 1,
1903 'frequency_unit' => 'month',
1904 'installments' => 12,
1905 'start_date' => '15 months ago',
1906 'processor_id' => 'CLC45',
1907 'trxn_id' => '56799',
1908 'contribution_status_id' => 1,
1909 'payment_processor_id' => 1,
1910 ],
1911 [
1912 'contact_id' => 99,
1913 'amount' => 10,
1914 'currency' => 'CAD',
1915 'frequency_interval' => 1,
1916 'frequency_unit' => 'month',
1917 'installments' => 6,
1918 'start_date' => '8 months ago',
1919 'cancel_date' => '1 month ago',
1920 'cancel_reason' => 'No longer interested',
1921 'processor_id' => 'CLR35',
1922 'trxn_id' => '22799',
1923 'contribution_status_id' => 3,
1924 'payment_processor_id' => 1,
1925 ],
1926 [
1927 'contact_id' => 103,
1928 'amount' => 5,
1929 'currency' => 'EUR',
1930 'frequency_interval' => 3,
1931 'frequency_unit' => 'month',
1932 'installments' => 3,
1933 'start_date' => '1 month ago',
1934 'processor_id' => 'EGR12',
1935 'trxn_id' => '44889',
1936 'contribution_status_id' => 5,
1937 'next_sched_contribution_date' => '+ 2 months',
1938 'payment_processor_id' => 1,
1939 ],
1940 ];
1941 // The process is a bit weird & the payment processor gets added later...
1942 // so we need to disable foreign key checks here.
1943 $this->_query('SET foreign_key_checks = 0');
1944 $contributionRecurID = 1;
1945 foreach ($recurrings as $recur) {
1946 $startDate = date('Y-m-d H:i:s', strtotime($recur['start_date']));
1947 $cancelDate = empty($recur['cancel_date']) ? 'NULL' : "'" . date('Y-m-d H:i:s', strtotime($recur['cancel_date'])) . "'";
1948 $nextScheduledDate = empty($recur['next_sched_contribution_date']) ? 'NULL' : "'" . date('Y-m-d H:i:s', strtotime($recur['next_sched_contribution_date'])) . "'";
1949 $this->_query("
1950 INSERT INTO civicrm_contribution_recur (
1951 contact_id, amount, currency, frequency_unit,
1952 frequency_interval, installments,
1953 start_date, cancel_date, cancel_reason, processor_id,
1954 trxn_id, contribution_status_id, next_sched_contribution_date, payment_processor_id)
1955 VALUES (
1956 %1, %2, %3, %4, %5, %6,
1957 %7, {$cancelDate}, %8, %9,
1958 %10, %11, {$nextScheduledDate}, 1
1959 )", [
1960 1 => [$recur['contact_id'] ?? NULL, 'Integer'],
1961 2 => [$recur['amount'], 'Money'],
1962 3 => [$recur['currency'], 'String'],
1963 4 => [$recur['frequency_unit'], 'String'],
1964 5 => [$recur['frequency_interval'], 'Integer'],
1965 6 => [$recur['installments'], 'Integer'],
1966 7 => [date('Y-m-d H:i:s', strtotime($recur['start_date'])), 'String'],
1967 8 => [$recur['cancel_reason'] ?? '', 'String'],
1968 9 => [$recur['processor_id'] ?? '', 'String'],
1969 10 => [$recur['trxn_id'], 'String'],
1970 11 => [$recur['contribution_status_id'], 'Integer'],
1971 ]
1972 );
1973 $contributionNumber = 1;
1974 $receive_date = $startDate;
1975 while ($contributionNumber < $recur['installments'] && strtotime($receive_date) < time()) {
1976 if (!empty($recur['cancel_date']) && strtotime($receive_date) > strtotime($recur['cancel_date'])) {
1977 continue;
1978 }
1979 $contributions[] = [
1980 'contact_id' => $recur['contact_id'],
1981 'payment_instrument_id' => 1,
1982 'receive_date' => $receive_date,
1983 'total_amount' => $recur['amount'],
1984 'currency' => $recur['currency'],
1985 'trxn_id' => 'PL32I' . $recur['contact_id'] . $contributionNumber,
1986 'source' => 'Recurring contribution',
1987 'contribution_recur_id' => $contributionRecurID,
1988 ];
1989 $receive_date = date('Y-m-d H:i:s', strtotime("+ {$recur['frequency_interval']} {$recur['frequency_unit']}", strtotime($receive_date)));
1990 $contributionNumber++;
1991 }
1992 $contributionRecurID++;
1993 }
1994 $this->_query('SET foreign_key_checks = 1');
1995 $contributionID = 1;
1996 $currentActivityID = CRM_Core_DAO::singleValueQuery('SELECT MAX(id) FROM civicrm_activity') + 1;
1997 foreach ($contributions as $contribution) {
1998 $contribution = array_merge($defaults, $contribution);
1999 $contribution['receive_date'] = date('Y-m-d H:i:s', strtotime($contribution['receive_date']));
2000 $contributionObject = new CRM_Contribute_BAO_Contribution();
2001 $contributionObject->copyValues($contribution);
2002 $contributionObject->save();
2003
2004 $symbols = [
2005 'USD' => '$',
2006 'CAD' => '$',
2007 'EUR' => '€',
2008 'GBP' => '£',
2009 'JPY' => 'Â¥',
2010 ];
2011 $subject = $symbols[$contribution['currency']] . ' ' . $contribution['total_amount'] . ' ' . $contribution['source'];
2012 $this->_query('
2013 INSERT INTO civicrm_activity
2014 (source_record_id, activity_type_id, subject, activity_date_time, duration, location, phone_id, phone_number, details, priority_id,parent_id, is_test, status_id)
2015 VALUES (
2016 %1, 6, %2, %3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2
2017 )',
2018 [
2019 1 => [$contributionID, 'Integer'],
2020 2 => [$subject, 'String'],
2021 3 => [$receive_date, 'String'],
2022 ]
2023 );
2024 $this->_query("INSERT INTO civicrm_activity_contact
2025 (contact_id, activity_id, record_type_id)
2026 VALUES ({$contribution['contact_id']}, $currentActivityID, 2)");
2027 $contributionID++;
2028 $currentActivityID++;
2029 }
2030 }
2031
2032 private function addSoftContribution() {
2033
2034 $sql = "SELECT id FROM civicrm_contribution WHERE contact_id = 92";
2035 $contriId1 = CRM_Core_DAO::singleValueQuery($sql);
2036
2037 $sql = "SELECT id FROM civicrm_contribution WHERE contact_id = 34";
2038 $contriId2 = CRM_Core_DAO::singleValueQuery($sql);
2039
2040 $sql = "SELECT cov.value FROM civicrm_option_value cov LEFT JOIN civicrm_option_group cog ON cog.id = cov.option_group_id WHERE cov.name = 'pcp' AND cog.name = 'soft_credit_type'";
2041
2042 $pcpId = CRM_Core_DAO::singleValueQuery($sql);
2043
2044 $query = "
2045 INSERT INTO `civicrm_contribution_soft`
2046 ( contribution_id, contact_id ,amount , currency, pcp_id , pcp_display_in_roll ,pcp_roll_nickname,pcp_personal_note, soft_credit_type_id )
2047 VALUES
2048 ( $contriId1, {$this->Individual[3]}, 10.00, 'USD', 1, 1, 'Jones Family', 'Helping Hands', $pcpId),
2049 ( $contriId2, {$this->Individual[3]}, 250.00, 'USD', 1, 1, 'Annie and the kids', 'Annie Helps', $pcpId);
2050 ";
2051
2052 $this->_query($query);
2053 }
2054
2055 private function addPledge() {
2056 $pledge = "INSERT INTO civicrm_pledge
2057 (contact_id, financial_type_id, contribution_page_id, amount, original_installment_amount, currency,frequency_unit, frequency_interval, frequency_day, installments, start_date, create_date, acknowledge_date, modified_date, cancel_date, end_date, status_id, is_test)
2058 VALUES
2059 (71, 1, 1, 500.00, '500', 'USD', 'month', 1, 1, 1, '2009-07-01 00:00:00', '2009-06-26 00:00:00', NULL, NULL, NULL,'2009-07-01 00:00:00', 1, 0),
2060 (43, 1, 1, 800.00, '200', 'USD', 'month', 3, 1, 4, '2009-07-01 00:00:00', '2009-06-23 00:00:00', '2009-06-23 00:00:00', NULL, NULL, '2009-04-01 10:11:40', 5, 0),
2061 (32, 1, 1, 600.00, '200', 'USD', 'month', 1, 1, 3, '2009-10-01 00:00:00', '2009-09-14 00:00:00', '2009-09-14 00:00:00', NULL, NULL, '2009-12-01 00:00:00', 5, 0);
2062 ";
2063 $this->_query($pledge);
2064 }
2065
2066 private function addPledgePayment() {
2067 $pledgePayment = "INSERT INTO civicrm_pledge_payment
2068 ( pledge_id, contribution_id, scheduled_amount, actual_amount, currency, scheduled_date, reminder_date, reminder_count, status_id)
2069 VALUES
2070 (1, 10, 500.00, 500.00, 'USD','2009-07-01 00:00:00', NULL, 0, 1 ),
2071 (2, 11, 200.00, 200.00, 'USD','2009-07-01 00:00:00', NULL, 0, 1 ),
2072 (2, NULL, 200.00, NULL, 'USD', '2009-10-01 00:00:00', NULL, 0, 2 ),
2073 (2, NULL, 200.00, NULL, 'USD', '2009-01-01 00:00:00', NULL, 0, 2 ),
2074 (2, NULL, 200.00, NULL, 'USD', '2009-04-01 00:00:00', NULL, 0, 2 ),
2075
2076 (3, 12, 200.00, 200.00, 'USD', '2009-10-01 00:00:00', NULL, 0, 1 ),
2077 (3, 13, 200.00, 200.00, 'USD', '2009-11-01 00:0:00', '2009-10-28 00:00:00', 1, 1),
2078 (3, NULL, 200.00, NULL, 'USD', '2009-12-01 00:00:00', NULL, 0, 2 );
2079 ";
2080 $this->_query($pledgePayment);
2081 }
2082
2083 private function addContributionLineItem() {
2084 $query = " INSERT INTO civicrm_line_item (`entity_table`, `entity_id`, contribution_id, `price_field_id`, `label`, `qty`, `unit_price`, `line_total`, `participant_count`, `price_field_value_id`, `financial_type_id`)
2085 SELECT 'civicrm_contribution', cc.id, cc.id contribution_id, cpf.id AS price_field, cpfv.label, 1, cc.total_amount, cc.total_amount line_total, 0, cpfv.id AS price_field_value, cpfv.financial_type_id
2086 FROM civicrm_contribution cc
2087 LEFT JOIN civicrm_price_set cps ON cps.name = 'default_contribution_amount'
2088 LEFT JOIN civicrm_price_field cpf ON cpf.price_set_id = cps.id
2089 LEFT JOIN civicrm_price_field_value cpfv ON cpfv.price_field_id = cpf.id
2090 ORDER BY cc.id; ";
2091 $this->_query($query);
2092 }
2093
2094 private function addAccountingEntries() {
2095 $components = ['contribution', 'membership', 'participant'];
2096 $select = 'SELECT contribution.id contribution_id, cli.id as line_item_id, contribution.contact_id, contribution.receive_date, contribution.total_amount, contribution.currency, cli.label,
2097 cli.financial_type_id, cefa.financial_account_id, contribution.payment_instrument_id, contribution.check_number, contribution.trxn_id';
2098 $where = 'WHERE cefa.account_relationship = 1';
2099 $financialAccountId = CRM_Financial_BAO_FinancialTypeAccount::getInstrumentFinancialAccount(4);
2100 foreach ($components as $component) {
2101 if ($component == 'contribution') {
2102 $from = 'FROM `civicrm_contribution` contribution';
2103 }
2104 else {
2105 $from = " FROM `civicrm_{$component}` {$component}
2106 INNER JOIN civicrm_{$component}_payment cpp ON cpp.{$component}_id = {$component}.id
2107 INNER JOIN civicrm_contribution contribution on contribution.id = cpp.contribution_id";
2108 }
2109 $from .= " INNER JOIN civicrm_line_item cli ON cli.entity_id = {$component}.id and cli.entity_table = 'civicrm_{$component}'
2110 INNER JOIN civicrm_entity_financial_account cefa ON cefa.entity_id = cli.financial_type_id ";
2111 $sql = " {$select} {$from} {$where} ";
2112 $result = CRM_Core_DAO::executeQuery($sql);
2113 $this->addFinancialItem($result, $financialAccountId);
2114 }
2115 }
2116
2117 /**
2118 * @param $result
2119 * @param null $financialAccountId
2120 */
2121 private function addFinancialItem($result, $financialAccountId) {
2122 $defaultFinancialAccount = CRM_Core_DAO::singleValueQuery("SELECT id FROM civicrm_financial_account WHERE is_default = 1 AND financial_account_type_id = 1");
2123 while ($result->fetch()) {
2124 $trxnParams = [
2125 'trxn_date' => CRM_Utils_Date::processDate($result->receive_date),
2126 'total_amount' => $result->total_amount,
2127 'currency' => $result->currency,
2128 'status_id' => 1,
2129 'trxn_id' => $result->trxn_id,
2130 'contribution_id' => $result->contribution_id,
2131 'to_financial_account_id' => empty($financialAccountId[$result->payment_instrument_id]) ? $defaultFinancialAccount : $financialAccountId[$result->payment_instrument_id],
2132 'payment_instrument_id' => $result->payment_instrument_id,
2133 'check_number' => $result->check_number,
2134 'is_payment' => 1,
2135 ];
2136 $trxn = CRM_Core_BAO_FinancialTrxn::create($trxnParams);
2137 $financialItem = [
2138 'transaction_date' => CRM_Utils_Date::processDate($result->receive_date),
2139 'amount' => $result->total_amount,
2140 'currency' => $result->currency,
2141 'status_id' => 1,
2142 'entity_id' => $result->line_item_id,
2143 'contact_id' => $result->contact_id,
2144 'entity_table' => 'civicrm_line_item',
2145 'description' => $result->label,
2146 'financial_account_id' => $result->financial_account_id,
2147 ];
2148 $trxnId['id'] = $trxn->id;
2149 CRM_Financial_BAO_FinancialItem::create($financialItem, NULL, $trxnId);
2150 }
2151 }
2152
2153 private function addLineItemParticipants() {
2154 $participant = new CRM_Event_DAO_Participant();
2155 $participant->query("INSERT INTO civicrm_line_item (`entity_table`, `entity_id`, contribution_id, `price_field_id`, `label`, `qty`, `unit_price`, `line_total`, `participant_count`, `price_field_value_id`, `financial_type_id`)
2156 SELECT 'civicrm_participant', cp.id, cpp.contribution_id, cpfv.price_field_id, cpfv.label, 1, cpfv.amount, cpfv.amount AS line_total, 0, cpfv.id, cpfv.financial_type_id FROM civicrm_participant cp LEFT JOIN civicrm_participant_payment cpp ON cpp.participant_id = cp.id
2157 LEFT JOIN civicrm_price_set_entity cpe ON cpe.entity_id = cp.event_id LEFT JOIN civicrm_price_field cpf ON cpf.price_set_id = cpe.price_set_id LEFT JOIN civicrm_price_field_value cpfv ON cpfv.price_field_id = cpf.id WHERE cpfv.label = cp.fee_level");
2158 }
2159
2160 private function addMembershipPayment() {
2161 $maxContribution = CRM_Core_DAO::singleValueQuery("SELECT MAX(id) FROM civicrm_contribution");
2162 $financialTypeID = CRM_Core_DAO::singleValueQuery("SELECT id FROM civicrm_financial_type WHERE name = 'Member Dues'");
2163 $paymentInstrumentID = CRM_Core_DAO::singleValueQuery("SELECT value FROM civicrm_option_value WHERE name = 'Credit Card' AND option_group_id = (SELECT id FROM civicrm_option_group WHERE name = 'payment_instrument')");
2164 $sql = "INSERT INTO civicrm_contribution (contact_id,financial_type_id,payment_instrument_id, receive_date, total_amount, currency, source, contribution_status_id, trxn_id)
2165 SELECT cm.contact_id, $financialTypeID, $paymentInstrumentID, now(), cmt.minimum_fee, 'USD', CONCAT(cmt.name, ' Membership: Offline signup'), 1, SUBSTRING(MD5(RAND()) FROM 1 FOR 16) FROM `civicrm_membership` cm
2166 LEFT JOIN civicrm_membership_type cmt ON cmt.id = cm.membership_type_id;";
2167
2168 $this->_query($sql);
2169
2170 $sql = "INSERT INTO civicrm_membership_payment (contribution_id,membership_id)
2171 SELECT cc.id, cm.id FROM civicrm_contribution cc
2172 LEFT JOIN civicrm_membership cm ON cm.contact_id = cc.contact_id
2173 WHERE cc.id > $maxContribution;";
2174
2175 $this->_query($sql);
2176
2177 $sql = "INSERT INTO civicrm_line_item (entity_table, entity_id, contribution_id, price_field_value_id, price_field_id, label, qty, unit_price, line_total, financial_type_id)
2178 SELECT 'civicrm_membership', cm.id, cmp.contribution_id, cpfv.id, cpfv.price_field_id, cpfv.label, 1, cpfv.amount, cpfv.amount AS unit_price, cpfv.financial_type_id FROM `civicrm_membership` cm
2179 LEFT JOIN civicrm_membership_payment cmp ON cmp.membership_id = cm.id
2180 LEFT JOIN civicrm_price_field_value cpfv ON cpfv.membership_type_id = cm.membership_type_id
2181 LEFT JOIN civicrm_price_field cpf ON cpf.id = cpfv.price_field_id
2182 LEFT JOIN civicrm_price_set cps ON cps.id = cpf.price_set_id
2183 WHERE cps.name = 'default_membership_type_amount'";
2184 $this->_query($sql);
2185
2186 $sql = "INSERT INTO civicrm_activity(source_record_id, activity_type_id, subject, activity_date_time, status_id, details)
2187 SELECT id, 6, CONCAT('$ ', total_amount, ' - ', source), now(), 2, 'Membership Payment' FROM civicrm_contribution WHERE id > $maxContribution";
2188 $this->_query($sql);
2189
2190 $sql = "INSERT INTO civicrm_activity_contact(contact_id, activity_id, record_type_id)
2191 SELECT c.contact_id, a.id, 2
2192 FROM civicrm_contribution c, civicrm_activity a
2193 WHERE c.id > $maxContribution
2194 AND a.source_record_id = c.id
2195 AND a.details = 'Membership Payment'
2196 ";
2197 $this->_query($sql);
2198 }
2199
2200 private function addParticipantPayment() {
2201 $maxContribution = CRM_Core_DAO::singleValueQuery("SELECT MAX(id) FROM civicrm_contribution");
2202 $financialTypeID = CRM_Core_DAO::singleValueQuery("SELECT id FROM civicrm_financial_type WHERE name = 'Event Fee'");
2203 $paymentInstrumentID = CRM_Core_DAO::singleValueQuery("SELECT value FROM civicrm_option_value WHERE name = 'Credit Card' AND option_group_id = (SELECT id FROM civicrm_option_group WHERE name = 'payment_instrument')");
2204 $sql = "INSERT INTO civicrm_contribution (contact_id, financial_type_id, payment_instrument_id, receive_date, total_amount, currency, receipt_date, source, contribution_status_id, trxn_id)
2205 SELECT `contact_id`, $financialTypeID, $paymentInstrumentID, now(), `fee_amount`, 'USD', now(), CONCAT(ce.title, ' : Offline registration'), 1, SUBSTRING(MD5(RAND()) FROM 1 FOR 16) FROM `civicrm_participant` cp
2206 LEFT JOIN civicrm_event ce ON ce.id = cp.event_id
2207 group by `contact_id`, `fee_amount`, `title`;";
2208
2209 $this->_query($sql);
2210
2211 $sql = "INSERT INTO civicrm_participant_payment (contribution_id,participant_id)
2212 SELECT cc.id, cp.id FROM civicrm_contribution cc
2213 LEFT JOIN civicrm_participant cp ON cp.contact_id = cc.contact_id
2214 WHERE cc.id > $maxContribution";
2215
2216 $this->_query($sql);
2217
2218 $sql = "INSERT INTO civicrm_activity(source_record_id, activity_type_id, subject, activity_date_time, status_id, details)
2219 SELECT id, 6, CONCAT('$ ', total_amount, ' - ', source), now(), 2, 'Participant' FROM `civicrm_contribution` WHERE id > $maxContribution";
2220 $this->_query($sql);
2221
2222 $sql = "INSERT INTO civicrm_activity_contact(contact_id, activity_id, record_type_id)
2223 SELECT c.contact_id, a.id, 2
2224 FROM civicrm_contribution c, civicrm_activity a
2225 WHERE c.id > $maxContribution
2226 AND a.source_record_id = c.id
2227 AND a.details = 'Participant Payment'
2228 ";
2229 $this->_query($sql);
2230 }
2231
2232 /**
2233 * @return string
2234 */
2235 protected static function getCivicrmDir(): string {
2236 return dirname(dirname(dirname(__DIR__)));
2237 }
2238
2239 }