2 /***********************************************************************/
4 /***********************************************************************/
5 /* Copyright (c) 2002-2010 */
6 /* Inclusive Design Institute */
9 /* This program is free software. You can redistribute it and/or */
10 /* modify it under the terms of the GNU General Public License */
11 /* as published by the Free Software Foundation. */
12 /***********************************************************************/
17 * This is developed based on the v8.1 Opensocial specification
18 * http://www.opensocial.org/Technical-Resources/opensocial-spec-v081/rpc-protocol
20 class ATutorDbFetcher {
25 function debug($var, $title='') {
26 echo '<pre style="border: 1px black solid; padding: 0px; margin: 10px;" title="debugging box">';
28 echo '<h4>'.$title.'</h4>';
33 $str = ob_get_contents();
36 $str = str_replace('<', '<', $str);
38 $str = str_replace('[', '<span style="color: red; font-weight: bold;">[', $str);
39 $str = str_replace(']', ']</span>', $str);
40 $str = str_replace('=>', '<span style="color: blue; font-weight: bold;">=></span>', $str);
41 $str = str_replace('Array', '<span style="color: purple; font-weight: bold;">Array</span>', $str);
47 private static $fetcher;
49 private function connectDb() {
50 // one of the class paths should point to ATutor's document root, abuse that fact to find our config
51 $extension_class_paths = Config::get('extension_class_paths');
52 foreach (explode(',', $extension_class_paths) as $path) {
53 if (file_exists($path . "/ATutorDbFetcher.php")) {
54 $configFile = $path . '/../../../../../include/lib/mysql_connect.inc.php';
55 if (file_exists($configFile)) {
56 define('AT_INCLUDE_PATH', $path . '/../../../../../include/');
57 include(AT_INCLUDE_PATH.'config.inc.php');
58 include(AT_INCLUDE_PATH . 'lib/constants.inc.php');
59 include(AT_INCLUDE_PATH . 'lib/mysql_connect.inc.php');
65 if (! isset($configFile)) {
66 throw new Exception("Could not locate ATutor's configuration file while scanning extension_class_paths ({$extension_class_paths})");
68 // $this->db = mysqli_connect($config['db_host'], $config['db_user'], $config['db_passwd'], $config['db_database']);
69 // mysqli_select_db($this->db, $config['db_database']);
70 // $this->url_prefix = $config['partuza_url'];
73 private function __construct() {
74 $cache = Config::get('data_cache');
75 $this->cache = new $cache();
78 private function checkDb() {
79 if (! is_resource($this->db)) {
84 private function __clone() { // private, don't allow cloning of a singleton
87 static function get() {
88 // This object is a singleton
89 if (! isset(ATutorDbFetcher::$fetcher)) {
90 ATutorDbFetcher::$fetcher = new ATutorDbFetcher();
92 return ATutorDbFetcher::$fetcher;
95 public function createActivity($member_id, $activity, $app_id = '0') {
97 $app_id = intval($app_id);
98 $member_id = intval($member_id);
99 $title = trim(isset($activity['title']) ? $activity['title'] : '');
101 throw new Exception("Invalid activity: empty title");
103 // $body = isset($activity['body']) ? $activity['body'] : '';
104 $title = mysql_real_escape_string($title);
105 // $body = mysql_real_escape_string($body);
106 $sql = "INSERT INTO ".TABLE_PREFIX."social_activities (member_id, application_id, title, created_date) values ($member_id, $app_id, '$title', NOW())";
107 mysql_query($sql, $this->db);
108 if (! ($activityId = mysql_insert_id($this->db))) {
113 * I don't have this on my system yet. -Harris
115 $mediaItems = isset($activity['mediaItems']) ? $activity['mediaItems'] : array();
116 if (count($mediaItems)) {
117 foreach ($mediaItems as $mediaItem) {
118 $type = isset($mediaItem['type']) ? $mediaItem['type'] : '';
119 $mimeType = isset($mediaItem['mimeType']) ? $mediaItem['mimeType'] : '';
120 $url = isset($mediaItem['url']) ? $mediaItem['url'] : '';
121 $type = mysqli_real_escape_string($this->db, trim($type));
122 $mimeType = mysqli_real_escape_string($this->db, trim($mimeType));
123 $url = mysqli_real_escape_string($this->db, trim($url));
124 if (! empty($mimeType) && ! empty($type) && ! empty($url)) {
125 mysqli_query($this->db, "insert into activity_media_items (id, activity_id, mime_type, media_type, url) values (0, $activityId, '$mimeType', '$type', '$url')");
126 if (! mysqli_insert_id($this->db)) {
138 public function getActivities($ids, $appId, $sortBy, $filterBy, $filterOp, $filterValue, $startIndex, $count, $fields, $activityIds) {
139 // public function getActivities($ids, $appId, $sortBy, $filterBy, $filterOp, $filterValue, $startIndex, $count, $fields) {
140 //TODO add support for filterBy, filterOp and filterValue
142 $activities = array();
143 foreach ($ids as $key => $val) {
144 $ids[$key] = mysql_real_escape_string($val);
146 $ids = implode(',', $ids);
147 if (isset($activityIds) && is_array($activityIds)) {
148 foreach ($activityIds as $key => $val) {
149 $activityIds[$key] = mysql_real_escape_string($val);
151 $activityIdQuery = " AND id IN (".implode(',', $activityIds);
153 $activityIdQuery = '';
155 // return a proper totalResults count
156 $sql = "SELECT count(id) FROM ".TABLE_PREFIX."social_activities WHERE member_id in ($ids) $activityIdQuery";
157 $res = mysql_query($sql, $this->db);
158 if ($res !== false) {
159 list($totalResults) = mysql_fetch_row($res);
163 $startIndex = (! is_null($startIndex) && $startIndex !== false && is_numeric($startIndex)) ? intval($startIndex) : '0';
164 $count = (! is_null($count) && $count !== false && is_numeric($count)) ? intval($count) : '20';
165 $activities['totalResults'] = $totalResults;
166 $activities['startIndex'] = $startIndex;
167 $activities['count'] = $count;
168 $query = "SELECT member_id, id, title, created_date FROM ".TABLE_PREFIX."social_activities WHERE member_id in ($ids) $activityIdQuery order by created_date desc limit $startIndex, $count";
169 $res = mysql_query($query, $this->db);
171 if (mysql_num_rows($res)) {
172 while ($row = mysql_fetch_assoc($res)) {
173 $activity = new Activity($row['id'], $row['member_id']);
174 $activity->setStreamTitle('activities');
175 $activity->setTitle($row['title']);
176 // $activity->setBody($row['activity_body']);
177 $activity->setPostedTime($row['created_date']);
178 $activity->setMediaItems($this->getMediaItems($row['id']));
179 $activities[] = $activity;
181 } elseif (isset($activityIds) && is_array($activityIds)) {
182 // specific activity id was specified, return a not found flag
194 * Check http://www.atutor.ca/atutor/mantis/view.php?id=4230 for details regarding to $activityId
196 public function deleteActivities($userId, $appId, $activityIds) {
198 foreach ($activityIds as $key => $val) {
199 $activityIds[$key] = intval($val);
201 $activityIds = implode(',', $activityIds);
202 $userId = intval($userId);
203 $appId = intval($appId);
204 //can use this instead: $sql = "delete from ".TABLE_PREFIX."social_activities where id in ($activityIds)";
205 $sql = "DELETE FROM ".TABLE_PREFIX."social_activities WHERE member_id = $userId and application_id = $appId AND id IN ($activityIds)";
206 mysql_query($sql, $this->db);
207 return (mysql_affected_rows($this->db) != 0);
211 * I haven't implement this yet
213 private function getMediaItems($activity_id) {
215 // $activity_id = mysqli_real_escape_string($db, $activity_id);
216 // $res = mysqli_query($this->db, "select mime_type, media_type, url from ".TABLE_PREFIX."activity_media_items where activity_id = $activity_id");
217 // while (list($mime_type, $type, $url) = @mysqli_fetch_row($res)) {
218 // $media[] = new MediaItem($mime_type, $type, $url);
224 * Retrieve all the groups of this person.
225 * reference: http://www.opensocial.org/Technical-Resources/opensocial-spec-v081/restful-protocol.html Section 2.3
226 * Group ID, and Title.
227 * @param Array member ids
229 public function getPersonGroups($ids){
233 foreach ($ids as $k=>$v){
234 $ids[$k] = intval($v);
236 $ids = implode(', ', $ids);
237 $sql = "SELECT m.member_id, g.id, g.name FROM ".TABLE_PREFIX."social_groups_members m LEFT JOIN ".TABLE_PREFIX."social_groups g ON m.group_id=g.id WHERE m.member_id IN ($ids)";
238 $result = mysql_query($sql, $this->db);
240 while(list($member_id, $group_id, $group_name) = mysql_fetch_row($result)){
241 $ret[$member_id][] = array($group_id, $group_name);
248 * @param Int Sender's member id
249 * @param appId application id
250 * @param Opensocial.Message Message Object, check http://wiki.opensocial.org/index.php?title=Opensocial.Message_%28v0.8%29
253 public function createMessage($member_id, $appId, $message){
255 $app_id = intval($app_id);
256 $member_id = intval($member_id);
257 $recipients = $message['recipients'];
258 if (empty($message)) {
259 throw new Exception("Invalid message: empty message");
261 if (empty($recipients)) {
262 throw new Exception("Invalid message: No recipients");
266 $body = mysql_real_escape_string($message['body']);
267 $subject = mysql_real_escape_string($message['title']);
268 foreach ($recipients as $key => $val) {
269 $recipients[$key] = intval($val);
272 //add message to every recipient's inbox.
273 foreach($recipients as $id){
277 $sql = "INSERT INTO ".TABLE_PREFIX."messages VALUES (NULL, 0, $member_id, $id, NOW(), 1, 0, '$subject', '$body')";
278 $result = mysql_query($sql,$this->db);
281 $sql = "INSERT INTO ".TABLE_PREFIX."messages_sent VALUES (NULL, 0, $member_id, $id, NOW(), '$subject', '$body')";
282 $result = mysql_query($sql,$this->db);
286 public function getFriendIds($member_id) {
289 $member_id = intval($member_id);
290 $sql = "SELECT member_id, friend_id from ".TABLE_PREFIX."social_friends WHERE member_id = $member_id or friend_id = $member_id";
291 $res = mysql_query($sql, $this->db);
292 while (list($mid, $fid) = mysql_fetch_row($res)) {
293 $id = ($mid == $member_id) ? $fid : $mid;
299 public function setAppData($member_id, $key, $value, $app_id) {
301 $member_id = intval($member_id);
302 $key = mysql_real_escape_string($key);
303 $value = mysql_real_escape_string($value);
304 $app_id = intval($app_id);
306 // empty key kind of became to mean "delete data" (was an old orkut hack that became part of the spec spec)
307 $sql = "DELETE FROM ".TABLE_PREFIX."social_application_settings WHERE application_id = $app_id AND member_id = $member_id AND name = '$key'";
308 if (! @mysql_query($sql, $this->db)) {
312 $sql ="INSERT INTO ".TABLE_PREFIX."social_application_settings (application_id, member_id, name, value) VALUES ($app_id, $member_id, '$key', '$value') ON DUPLICATE KEY UPDATE VALUE = '$value'";
313 if (! @mysql_query($sql, $this->db)) {
320 public function deleteAppData($member_id, $key, $app_id) {
322 $person_id = intval($member_id);
323 $app_id = intval($app_id);
325 $sql = "DELETE FROM ".TABLE_PREFIX."social_application_settings WHERE application_id = $app_id and member_id = $member_id";
326 if (! @mysql_query($sql, $this->db)) {
330 $key = mysql_real_escape_string($key);
331 $sql = "DELETE FROM ".TABLE_PREFIX."social_application_settings WHERE application_id = $app_id and member_id = $member_id and name = '$key'";
332 if (! @mysql_query($sql, $this->db)) {
339 public function getAppData($ids, $keys, $app_id) {
346 foreach ($ids as $key => $val) {
348 $ids[$key] = intval($val);
351 if (! isset($keys[0])) {
354 if ($keys[0] == '*') {
356 } elseif (is_array($keys)) {
357 foreach ($keys as $key => $val) {
358 $keys[$key] = "'" . addslashes($val) . "'";
360 $keys = "and name in (" . implode(',', $keys) . ")";
364 $sql = "SELECT member_id, name, value FROM ".TABLE_PREFIX."social_application_settings WHERE application_id = $app_id and member_id in (" . implode(',', $ids) . ") $keys";
365 $res = mysql_query($sql, $this->db);
366 while (list($member_id, $key, $value) = mysql_fetch_row($res)) {
367 if (! isset($data[$member_id])) {
368 $data[$member_id] = array();
370 $data[$member_id][$key] = $value;
375 public function getPeople($ids, $fields, $options, $token) {
376 $first = $options->getStartIndex();
377 $max = $options->getCount();
381 if ($options->getFilterBy() == 'hasApp') {
382 // remove the filterBy field, it's taken care of in the query already, otherwise filterResults will disqualify all results
383 $options->setFilterBy(null);
384 $appId = $token->getAppId();
385 $filterQuery = " and id in (select member_id from ".TABLE_PREFIX."social_applications where application_id = $appId)";
386 } elseif ($options->getFilterBy() == 'all') {
387 $options->setFilterBy(null);
389 $query = "SELECT member.*, info.interests, info.associations, info.awards FROM ".TABLE_PREFIX."members member LEFT JOIN ".TABLE_PREFIX."social_member_additional_information info ON member.member_id=info.member_id WHERE member.member_id IN (" . implode(',', $ids) . ") $filterQuery ORDER BY member.member_id ";
391 $res = mysql_query($query, $this->db);
393 while ($row = mysql_fetch_assoc($res)) {
394 $member_id = intval($row['member_id']);
395 $name = new Name($row['first_name'] . ' ' . $row['last_name']);
397 $name->setGivenName($row['first_name']);
398 $name->setFamilyName($row['last_name']);
399 $person = new Person($row['member_id'], $name);
400 $person->setDisplayName($name->getFormatted());
401 $person->setAboutMe($row['about_me']);
402 $person->setAge($row['age']);
403 $person->setChildren($row['children']);
404 $person->setBirthday(date('Y-m-d', $row['date_of_birth']));
405 $person->setEthnicity($row['ethnicity']);
406 $person->setFashion($row['fashion']);
407 $person->setHappiestWhen($row['happiest_when']);
408 $person->setHumor($row['humor']);
409 $person->setJobInterests($row['job_interests']);
410 $person->setLivingArrangement($row['living_arrangement']);
411 $person->setLookingFor($row['looking_for']);
412 $person->setNickname($row['nickname']);
413 $person->setPets($row['pets']);
414 $person->setPoliticalViews($row['political_views']);
415 $person->setProfileSong($row['profile_song']);
416 $person->setProfileUrl($this->url_prefix . '/profile/' . $row['member_id']);
417 $person->setProfileVideo($row['profile_video']);
418 $person->setRelationshipStatus($row['relationship_status']);
419 $person->setReligion($row['religion']);
420 $person->setRomance($row['romance']);
421 $person->setScaredOf($row['scared_of']);
422 $person->setSexualOrientation($row['sexual_orientation']);
423 $person->setStatus($row['status']);
424 $person->setThumbnailUrl(! empty($row['thumbnail_url']) ? $this->url_prefix . $row['thumbnail_url'] : '');
426 if (! empty($row['thumbnail_url'])) {
427 // also report thumbnail_url in standard photos field (this is the only photo supported by ATutor)
428 $person->setPhotos(array(
429 new Photo($this->url_prefix . 'get_profile_img.php?id='.$row['member_id'], 'thumbnail', true)));
431 $person->setUtcOffset(sprintf('%+03d:00', $row['time_zone'])); // force "-00:00" utc-offset format
432 if (! empty($row['drinker'])) {
433 $person->setDrinker($row['drinker']);
435 if (! empty($row['gender'])) {
436 $person->setGender(strtolower($row['gender']));
438 if (! empty($row['email'])){
439 //TODO: Assumed <static> object TYPE to be "home". Change it if ATutor starts accepting more than one email
440 $email = new Email(strtolower($row['email']), 'home');
441 $person->setEmails($email);
443 if (! empty($row['interests'])){
444 $strings = explode(',', $row['interests']);
445 $person->setInterests($strings);
448 //TODO: Not in ATutor yet, skeleton field
449 if (! empty($row['smoker'])) {
450 $person->setSmoker($row['smoker']);
452 /* the following fields require additional queries so are only executed if requested */
453 if (isset($fields['activities']) || isset($fields['@all'])) {
454 $activities = array();
455 $sql = "select title from ".TABLE_PREFIX."social_activities where member_id = " . $member_id;
456 $res2 = mysql_query($sql, $this->db);
458 while (list($activity) = mysql_fetch_row($res2)) {
459 $activities[] = $activity;
461 $person->setActivities($activities);
464 if (isset($fields['addresses']) || isset($fields['@all'])) {
465 $addresses = array();
466 $sql = "select address, postal, city, province, country from ".TABLE_PREFIX."members m where m.member_id = " . $member_id;
467 $res2 = mysql_query($sql, $this->db);
468 while ($row = mysql_fetch_assoc($res2)) {
469 if (empty($row['unstructured_address'])) {
470 $row['unstructured_address'] = trim($row['street_address'] . " " . $row['province'] . " " . $row['country']);
472 $addres = new Address($row['unstructured_address']);
473 $addres->setCountry($row['country']);
474 $addres->setLatitude($row['latitude']);
475 $addres->setLongitude($row['longitude']);
476 $addres->setLocality($row['locality']);
477 $addres->setPostalCode($row['postal_code']);
478 $addres->setRegion($row['province']);
479 $addres->setStreetAddress($row['street_address']);
480 $addres->setType($row['address_type']);
481 //FIXME quick and dirty hack to demo PC
482 $addres->setPrimary(true);
483 $addresses[] = $addres;
485 $person->setAddresses($addresses);
487 //TODO: Not in ATutor yet, skeleton field
489 if (isset($fields['bodyType']) || isset($fields['@all'])) {
490 $res2 = mysql_query($db, "select * from ".TABLE_PREFIX."person_body_type where person_id = " . $person_id);
491 if (@mysql_num_rows($res2)) {
492 $row = @mysql_fetch_assic($res2);
493 $bodyType = new BodyType();
494 $bodyType->setBuild($row['build']);
495 $bodyType->setEyeColor($row['eye_color']);
496 $bodyType->setHairColor($row['hair_color']);
497 $bodyType->setHeight($row['height']);
498 $bodyType->setWeight($row['weight']);
499 $person->setBodyType($bodyType);
504 //TODO: Not in ATutor yet, skeleton field
506 if (isset($fields['books']) || isset($fields['@all'])) {
508 $res2 = mysqli_query($db, "select book from ".TABLE_PREFIX."person_books where person_id = " . $person_id);
509 while (list($book) = @mysqli_fetch_row($res2)) {
512 $person->setBooks($books);
516 //TODO: Not in ATutor yet, skeleton field
518 if (isset($fields['cars']) || isset($fields['@all'])) {
520 $res2 = mysqli_query($db, "select car from ".TABLE_PREFIX."person_cars where person_id = " . $person_id);
521 while (list($car) = @mysqli_fetch_row($res2)) {
524 $person->setCars($cars);
528 //TODO: Not in ATutor yet, skeleton field
530 if (isset($fields['currentLocation']) || isset($fields['@all'])) {
531 $addresses = array();
532 $res2 = mysqli_query($db, "select a.* from ".TABLE_PREFIX."person_current_location pcl, ".TABLE_PREFIX."person_addresses pa, ".TABLE_PREFIX."addresses a where a.id = pcl.address_id and pa.person_id = " . $person_id);
533 if (@mysqli_num_rows($res2)) {
534 $row = mysqli_fetch_array($res2, MYSQLI_ASSOC);
535 if (empty($row['unstructured_address'])) {
536 $row['unstructured_address'] = trim($row['street_address'] . " " . $row['region'] . " " . $row['country']);
538 $addres = new Address($row['unstructured_address']);
539 $addres->setCountry($row['country']);
540 $addres->setLatitude($row['latitude']);
541 $addres->setLongitude($row['longitude']);
542 $addres->setLocality($row['locality']);
543 $addres->setPostalCode($row['postal_code']);
544 $addres->setRegion($row['region']);
545 $addres->setStreetAddress($row['street_address']);
546 $addres->setType($row['address_type']);
547 $person->setCurrentLocation($addres);
552 //TODO: Email is a singleton in ATutor, expand it. A person may have 1+ emails nowadays.
553 //added to the above with all the other member's properties
555 if (isset($fields['emails']) || isset($fields['@all'])) {
557 $sql = "select address, email_type from ".TABLE_PREFIX."person_emails where person_id = " . $person_id;
558 $res2 = mysql_query();
559 while (list($address, $type) = @mysqli_fetch_row($res2)) {
560 $emails[] = new Email(strtolower($address), $type); // TODO: better email canonicalization; remove dups
562 $person->setEmails($emails);
565 //TODO: Not in ATutor yet, skeleton field
567 if (isset($fields['food']) || isset($fields['@all'])) {
569 $res2 = mysqli_query($db, "select food from ".TABLE_PREFIX."person_foods where person_id = " . $person_id);
570 while (list($food) = @mysqli_fetch_row($res2)) {
573 $person->setFood($foods);
577 //TODO: Not in ATutor yet, skeleton field
579 if (isset($fields['heroes']) || isset($fields['@all'])) {
581 $res2 = mysqli_query($db, "select hero from ".TABLE_PREFIX."person_heroes where person_id = " . $person_id);
582 while (list($data) = @mysqli_fetch_row($res2)) {
585 $person->setHeroes($strings);
588 //Added with the above profile, interests is in CSV
590 if (isset($fields['interests']) || isset($fields['@all'])) {
592 $res2 = mysqli_query($db, "select interest from ".TABLE_PREFIX."person_interests where person_id = " . $person_id);
593 while (list($data) = @mysqli_fetch_row($res2)) {
596 $person->setInterests($strings);
599 $organizations = array();
601 if (isset($fields['jobs']) || isset($fields['@all'])) {
602 $sql = "SELECT * FROM ". TABLE_PREFIX . "social_member_position WHERE member_id = ".$member_id;
603 $res2 = mysql_query($sql, $this->db);
604 while ($row = mysql_fetch_assoc($res2)) {
605 $organization = new Organization($row['company']);
606 $organization->setDescription($row['description']);
607 $organization->setEndDate($row['to']);
608 $organization->setField($row['field']);
609 $organization->setName($row['company']);
610 $organization->setSalary($row['salary']);
611 $organization->setStartDate($row['from']);
612 $organization->setSubField($row['']);
613 $organization->setTitle($row['title']);
614 $organization->setWebpage($row['webpage']);
615 $organization->setType('job');
617 //TODO: Address: To be implemented
619 if ($row['address_id']) {
620 $res3 = mysqli_query($db, "select * from ".TABLE_PREFIX."addresses where id = " . mysqli_real_escape_string($db, $row['address_id']));
621 if (mysqli_num_rows($res3)) {
622 $row = mysqli_fetch_array($res3, MYSQLI_ASSOC);
623 if (empty($row['unstructured_address'])) {
624 $row['unstructured_address'] = trim($row['street_address'] . " " . $row['region'] . " " . $row['country']);
626 $addres = new Address($row['unstructured_address']);
627 $addres->setCountry($row['country']);
628 $addres->setLatitude($row['latitude']);
629 $addres->setLongitude($row['longitude']);
630 $addres->setLocality($row['locality']);
631 $addres->setPostalCode($row['postal_code']);
632 $addres->setRegion($row['region']);
633 $addres->setStreetAddress($row['street_address']);
634 $addres->setType($row['address_type']);
635 $organization->setAddress($address);
639 $organizations[] = $organization;
643 if (isset($fields['schools']) || isset($fields['@all'])) {
644 $res2 = mysql_query("SELECT * FROM ".TABLE_PREFIX."social_member_education WHERE member_id = " . $member_id, $this->db);
645 while ($row = mysql_fetch_assoc($res2)) {
646 $organization = new Organization($row['university']);
647 $organization->setDescription($row['description']);
648 $organization->setEndDate($row['to']);
649 $organization->setField($row['field']);
650 $organization->setName($row['university']);
651 $organization->setSalary('');
652 $organization->setStartDate($row['from']);
653 $organization->setSubField('');
654 $organization->setTitle($row['degree']);
655 $organization->setWebpage('');
656 $organization->setType('school');
657 //TODO: Address: To be implemented
659 if ($row['address_id']) {
660 $res3 = mysqli_query($db, "select * from ".TABLE_PREFIX."addresses where id = " . mysqli_real_escape_string($db, $row['address_id']));
661 if (mysqli_num_rows($res3)) {
662 $row = mysqli_fetch_array($res3, MYSQLI_ASSOC);
663 if (empty($row['unstructured_address'])) {
664 $row['unstructured_address'] = trim($row['street_address'] . " " . $row['region'] . " " . $row['country']);
666 $addres = new Address($row['unstructured_address']);
667 $addres->setCountry($row['country']);
668 $addres->setLatitude($row['latitude']);
669 $addres->setLongitude($row['longitude']);
670 $addres->setLocality($row['locality']);
671 $addres->setPostalCode($row['postal_code']);
672 $addres->setRegion($row['region']);
673 $addres->setStreetAddress($row['street_address']);
674 $addres->setType($row['address_type']);
675 $organization->setAddress($address);
679 $organizations[] = $organization;
684 $person->setOrganizations($organizations);
686 //TODO languagesSpoken, currently missing the languages / countries tables so can't do this yet
687 //TODO: Not in ATutor yet, skeleton field
689 if (isset($fields['movies']) || isset($fields['@all'])) {
691 $res2 = mysqli_query($db, "select movie from ".TABLE_PREFIX."person_movies where person_id = " . $person_id);
692 while (list($data) = @mysqli_fetch_row($res2)) {
695 $person->setMovies($strings);
697 if (isset($fields['music']) || isset($fields['@all'])) {
699 $res2 = mysqli_query($db, "select music from ".TABLE_PREFIX."person_music where person_id = " . $person_id);
700 while (list($data) = @mysqli_fetch_row($res2)) {
703 $person->setMusic($strings);
706 if (isset($fields['phoneNumbers']) || isset($fields['@all'])) {
708 $res2 = mysql_query("SELECT phone FROM ".TABLE_PREFIX."members where member_id = " . $member_id, $this->db);
710 while ($number = mysql_fetch_assoc($res2)) {
711 $numbers[] = new Phone($number, 'Home'); //default to 'Home' until ATutor supports Mobile, etc.
714 $person->setPhoneNumbers($numbers);
717 if (isset($fields['ims']) || isset($fields['@all'])) {
719 $res2 = mysqli_query($db, "select value, value_type from ".TABLE_PREFIX."person_ims where person_id = " . $person_id);
720 while (list($value, $type) = @mysqli_fetch_row($res2)) {
721 $ims[] = new Im($value, $type);
723 $person->setIms($ims);
725 if (isset($fields['accounts']) || isset($fields['@all'])) {
727 $res2 = mysqli_query($db, "select domain, userid, username from ".TABLE_PREFIX."person_accounts where person_id = " . $person_id);
728 while (list($domain, $userid, $username) = @mysqli_fetch_row($res2)) {
729 $accounts[] = new Account($domain, $userid, $username);
731 $person->setAccounts($accounts);
733 if (isset($fields['quotes']) || isset($fields['@all'])) {
735 $res2 = mysqli_query($db, "select quote from ".TABLE_PREFIX."person_quotes where person_id = " . $person_id);
736 while (list($data) = @mysqli_fetch_row($res2)) {
739 $person->setQuotes($strings);
741 if (isset($fields['sports']) || isset($fields['@all'])) {
743 $res2 = mysqli_query($db, "select sport from ".TABLE_PREFIX."person_sports where person_id = " . $person_id);
744 while (list($data) = @mysqli_fetch_row($res2)) {
747 $person->setSports($strings);
749 if (isset($fields['tags']) || isset($fields['@all'])) {
751 $res2 = mysqli_query($db, "select tag from ".TABLE_PREFIX."person_tags where person_id = " . $person_id);
752 while (list($data) = @mysqli_fetch_row($res2)) {
755 $person->setTags($strings);
758 if (isset($fields['turnOns']) || isset($fields['@all'])) {
760 $res2 = mysqli_query($db, "select turn_on from ".TABLE_PREFIX."person_turn_ons where person_id = " . $person_id);
761 while (list($data) = @mysqli_fetch_row($res2)) {
764 $person->setTurnOns($strings);
766 if (isset($fields['turnOffs']) || isset($fields['@all'])) {
768 $res2 = mysqli_query($db, "select turn_off from ".TABLE_PREFIX."person_turn_offs where person_id = " . $person_id);
769 while (list($data) = @mysqli_fetch_row($res2)) {
772 $person->setTurnOffs($strings);
775 if (isset($fields['urls']) || isset($fields['@all'])) {
777 $res2 = mysql_query("SELECT url, site_name FROM ".TABLE_PREFIX."social_member_websites WHERE member_id = " . $member_id, $this->db);
779 while ($data = mysql_fetch_assoc($res2)) {
782 * http://www.opensocial.org/Technical-Resources/opensocial-spec-v081/opensocial-reference#opensocial.Url
784 $strings[] = new Url($data['url'], null, $data['site_name']);
787 $strings[] = new Url($this->url_prefix . '/profile/' . $member_id, null, 'profile'); // always include profile URL
788 $person->setUrls($strings);
790 $ret[$member_id] = $person;
795 $ret = $this->filterResults($ret, $options);
796 $ret['totalSize'] = count($ret);
797 } catch(Exception $e) {
798 $ret['totalSize'] = count($ret) - 1;
799 $ret['filtered'] = 'false';
801 if ($first !== false && $max !== false && is_numeric($first) && is_numeric($max) && $first >= 0 && $max > 0) {
804 foreach ($ret as $id => $person) {
805 if ($id == 'totalSize' || $id == 'filtered') {
806 $result[$id] = $person;
809 if ($count >= $first && $count < $first + $max) {
810 $result[$id] = $person;
820 private function filterResults($peopleById, $options) {
821 if (! $options->getFilterBy()) {
822 return $peopleById; // no filtering specified
824 $filterBy = $options->getFilterBy();
825 $op = $options->getFilterOperation();
827 $op = CollectionOptions::FILTER_OP_EQUALS; // use this container-specific default
829 $value = $options->getFilterValue();
830 $filteredResults = array();
831 $numFilteredResults = 0;
832 foreach ($peopleById as $id => $person) {
833 if ($person instanceof Person) {
834 if ($this->passesFilter($person, $filterBy, $op, $value)) {
835 $filteredResults[$id] = $person;
836 $numFilteredResults ++;
839 $filteredResults[$id] = $person; // copy extra metadata verbatim
842 if (! isset($filteredResults['totalSize'])) {
843 $filteredResults['totalSize'] = $numFilteredResults;
845 return $filteredResults;
848 private function passesFilter($person, $filterBy, $op, $value) {
849 $fieldValue = $person->getFieldByName($filterBy);
850 if ($fieldValue instanceof ComplexField) {
851 $fieldValue = $fieldValue->getPrimarySubValue();
853 if (! $fieldValue || (is_array($fieldValue) && ! count($fieldValue))) {
854 return false; // person is missing the field being filtered for
856 if ($op == CollectionOptions::FILTER_OP_PRESENT) {
857 return true; // person has a non-empty value for the requested field
860 return false; // can't do an equals/startswith/contains filter on an empty filter value
862 // grab string value for comparison
863 if (is_array($fieldValue)) {
864 // plural fields match if any instance of that field matches
865 foreach ($fieldValue as $field) {
866 if ($field instanceof ComplexField) {
867 $field = $field->getPrimarySubValue();
869 if ($this->passesStringFilter($field, $op, $value)) {
874 return $this->passesStringFilter($fieldValue, $op, $value);
879 private function passesStringFilter($fieldValue, $op, $filterValue) {
881 case CollectionOptions::FILTER_OP_EQUALS:
882 return $fieldValue == $filterValue;
883 case CollectionOptions::FILTER_OP_CONTAINS:
884 return stripos($fieldValue, $filterValue) !== false;
885 case CollectionOptions::FILTER_OP_STARTSWITH:
886 return stripos($fieldValue, $filterValue) === 0;
888 throw new Exception('unrecognized filterOp');