CRM/Core/CodeGen/GenerateData.php

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