sql/GenerateData.php

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