sql/GenerateData.php

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