sql/GenerateData.php

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