CRM/Core/CodeGen/GenerateData.php

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