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