sql/GenerateData.php

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