sql/GenerateData.php

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