sql/GenerateData.php

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