/**
* Updated search method for users. Search type can also be specified with every field one is searching for
* e.g $search_item['first_name'] = array('value'=> 'test', 'type'=> LIKE_SEARCH) here type defines the search type
* in this case it is LIKE SEARCH (constant defined in api_constants.php) means value
* will be searched field_value = '%test%'
* For date of birth we can specify date range like $search_item['dob'] = array('value'=>
* array('lower_limit'=>200400, 'upper_limit'=>300500), 'type'=> RANGE_SEARCH)
* This method will help us to search for value given in range and will give more freedom than the
* method load_info_by_search which uses only LIKE to search for values.
*/
public static function user_search($search_item, $user_id, $network_id = NULL, $cnt = FALSE, $show = 'ALL', $page = 0, $sort_by = 'U.created', $direction = 'DESC', $condition = NULL)
{
Logger::log("Enter: User::user_search");
$db = Dal::get_connection();
$data = array();
$uids = array();
$i = 0;
$j = 1;
$PEARObject = new PEAR();
$order_by = $sort_by . ' ' . $direction;
if ($show == 'ALL' || $cnt == TRUE) {
$limit = '';
} else {
$start = ($page - 1) * $show;
$limit = 'LIMIT ' . $start . ',' . $show;
}
// ---- fix by Z.Hron: We don't need to read all data to count rows! Use MySQL function COUNT() in future!
$search_string = "SELECT (U.user_id) as uid, U.first_name as first_name, U.login_name as login_name, U.picture as picture, UP.field_perm as field_perm, count(U.user_id) as counts, U.created";
if ($cnt) {
$search_string = "SELECT count(U.user_id) as counts, (U.user_id) as uid";
}
// ---- EOF
if ($network_id) {
$search_string .= " FROM users AS U LEFT OUTER JOIN user_profile_data AS UP ON UP.user_id = U.user_id INNER JOIN networks_users AS NU ON U.user_id = NU.user_id AND NU.network_id = ? ";
$data[] = $network_id;
} else {
$search_string .= " FROM users AS U LEFT OUTER JOIN user_profile_data AS UP ON UP.user_id = U.user_id ";
}
if (!empty($search_item['group_id'])) {
$group_id = $search_item['group_id']['value'];
unset($search_item['group_id']);
$search_string .= " INNER JOIN {groups_users} AS GU ON U.user_id = GU.user_id AND GU.group_id = ? ";
$data[] = $group_id;
}
if (!empty($search_item['in_relation'])) {
$relation_id = $search_item['in_relation']['value'];
$search_string .= " INNER JOIN {relations} AS RU\n ON U.user_id = RU.relation_id\n AND RU.user_id = ? ";
$data[] = $relation_id;
if (!empty($search_item['in_relation']['type'])) {
$status = $search_item['in_relation']['type'];
$search_string .= " AND RU.status = ? ";
$data[] = $status;
}
unset($search_item['in_relation']);
}
if (!empty($condition)) {
$search_string .= " WHERE {$condition} AND ";
} else {
$search_string .= " WHERE 1 AND ";
// field_perm > 0 AND "; // removed - field_perm checked for each field in code bellow!
}
$search_items_count = count($search_item);
if ($search_items_count > 0) {
$counter = 0;
foreach ($search_item as $field_name => $field_details) {
$counter++;
switch ($field_details['type']) {
case AGE_SEARCH:
//date of birth will be saved in the formay YYYY-MM-DD
$search_string .= '( UP.field_name = ? AND UP.field_value BETWEEN DATE(DATE_ADD(NOW(), INTERVAL ? YEAR)) AND DATE(DATE_ADD(NOW(), INTERVAL ? YEAR)) AND UP.field_perm <> ?)';
$data[] = $field_name;
$data[] = $field_details['value']['upper_limit'] * -1;
$data[] = $field_details['value']['lower_limit'] * -1;
$data[] = NONE;
break;
case GREATER_THAN:
$search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )';
$data[] = $field_name;
$data[] = $field_details['value'];
$data[] = NONE;
break;
case RANGE_SEARCH:
$search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND ? AND UP.field_perm <> ? )';
$data[] = $field_name;
$data[] = $field_details['value']['lower_limit'];
$data[] = $field_details['value']['upper_limit'];
$data[] = NONE;
break;
case LIKE_SEARCH:
if (!empty($field_details['ignore_perm']) && $field_details['ignore_perm'] == true) {
$search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? )';
$data[] = $field_name;
$data[] = '%' . $field_details['value'] . '%';
} else {
$search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )';
$data[] = $field_name;
$data[] = '%' . $field_details['value'] . '%';
$data[] = NONE;
}
break;
case GLOBAL_SEARCH:
if (!empty($field_details['ignore_perm']) && $field_details['ignore_perm'] == true) {
$search_string .= '( UP.field_value LIKE ? )';
$data[] = '%' . $field_details['value'] . '%';
} else {
$search_string .= '( UP.field_value LIKE ? AND UP.field_perm <> ? )';
$data[] = '%' . $field_details['value'] . '%';
$data[] = NONE;
}
break;
case IN_SEARCH:
$search_string .= '( UP.field_name = ? AND UP.field_value IN ( ' . $field_details['value'] . ' ) AND UP.field_perm <> ? )';
$data[] = $field_name;
$data[] = NONE;
break;
default:
$search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )';
$data[] = $field_name;
$data[] = $field_details['value'];
$data[] = NONE;
}
$search_string .= $search_items_count == $counter ? ' AND ' : ' OR ';
}
}
$search_string .= " U.is_active = ? GROUP BY uid HAVING counts >= ? ORDER BY {$order_by} {$limit}";
$data[] = ACTIVE;
$data[] = $search_items_count;
$res = Dal::query($search_string, $data);
if ($PEARObject->isError($res)) {
Logger::log(" Throwing exception DB_QUERY_FAILED | Message: {$res->getMessage}()", LOGGER_ERROR);
throw new PAException(DB_QUERY_FAILED, $res->getMessage());
}
if ($cnt) {
// fix by Z.Hron: We don't need to read all data to count rows! Use MySQL function COUNT() in future!
$u_data = $res->fetchRow(DB_FETCHMODE_OBJECT);
return !empty($u_data) ? $u_data->counts : 0;
// return $res->numRows();
}
$uid_array = array();
if ($res->numrows() > 0) {
$i = 0;
while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
$uid_array[$i]['user_id'] = $row->uid;
$uid_array[$i]['login_name'] = $row->login_name;
$uid_array[$i]['picture'] = $row->picture;
$uid_array[$i]['first_name'] = $row->first_name;
$uid_array[$i]['field_perm'] = $row->field_perm;
$uid_array[$i]['created'] = $row->created;
$i++;
}
}
// search according to perm
$sql = "Select user_id from {relations} where relation_id = {$user_id}";
$res = Dal::query($sql);
if ($PEARObject->isError($res)) {
Logger::log(" Throwing exception DB_QUERY_FAILED | Message: {$res->getMessage}()", LOGGER_ERROR);
throw new PAException(DB_QUERY_FAILED, $res->getMessage());
}
if ($res->numrows() > 0) {
while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
$user_is_friend[] = $row->user_id;
}
}
$j = 0;
$user_ids = array();
for ($i = 0; $i < count($uid_array); $i++) {
if ($uid_array[$i]['field_perm'] == WITH_IN_DEGREE_1) {
if (!empty($user_is_friend)) {
if (in_array($uid_array[$i]['user_id'], $user_is_friend) || $user_id == $uid_array[$i]['user_id']) {
$user_ids[$j]['user_id'] = $uid_array[$i]['user_id'];
$user_ids[$j]['login_name'] = $uid_array[$i]['login_name'];
$user_ids[$j]['first_name'] = $uid_array[$i]['first_name'];
$user_ids[$j]['picture'] = $uid_array[$i]['picture'];
$user_ids[$j]['created'] = $uid_array[$i]['created'];
$j++;
}
}
} else {
if ($uid_array[$i]['field_perm'] == NONE) {
// used when field_perm attribute ignored
$user_ids[$j]['user_id'] = $uid_array[$i]['user_id'];
$user_ids[$j]['login_name'] = $uid_array[$i]['login_name'];
$user_ids[$j]['first_name'] = $uid_array[$i]['first_name'];
$user_ids[$j]['picture'] = $uid_array[$i]['picture'];
$user_ids[$j]['created'] = $uid_array[$i]['created'];
$j++;
} else {
$user_ids[$j]['user_id'] = $uid_array[$i]['user_id'];
$user_ids[$j]['login_name'] = $uid_array[$i]['login_name'];
$user_ids[$j]['first_name'] = $uid_array[$i]['first_name'];
$user_ids[$j]['picture'] = $uid_array[$i]['picture'];
$user_ids[$j]['created'] = $uid_array[$i]['created'];
$j++;
}
}
}
$users_with_data = array('users_data' => $user_ids, 'total_users' => count($user_ids));
Logger::log("Exit: User::user_search");
return $users_with_data;
}
<?php
require_once dirname(__FILE__) . '/../config.inc';
require_once "{$path_prefix}/db/Dal/Dal.php";
require_once "{$path_prefix}/db/Dal/DbUpdate.php";
require_once "{$path_prefix}/api/Network/Network.php";
// Re-include constants.php to make sure we have the most up to date
// constants. If we are in the middle of an update and this script is
// being included by web/update/run_scripts.php, we might not have all
// the constants.
include "{$path_prefix}/web/includes/constants.php";
// $settings_new contains the mapping of page names to modules they contain.
global $settings_new;
$db = Dal::get_connection();
foreach (DbUpdate::get_valid_networks() as $net_address) {
set_time_limit(30);
$net = Network::get_network_by_address($net_address);
$table_name = 'page_default_settings';
if ($net->type != MOTHER_NETWORK_TYPE) {
// 1 for home network
$table_name = $net->address . '_' . $table_name;
}
$sql = ' TRUNCATE TABLE ' . $table_name;
$res = Dal::query($sql);
foreach ($settings_new as $page_id => $v1) {
$page_name = $v1['page_name'];
$data = $v1['data'];
$settings_data = serialize($data);
$is_configurable = isset($v1['is_configurable']) ? $v1['is_configurable'] : FALSE;
//default value will be false is not specified
$sql = "INSERT INTO {$table_name} (page_id, page_name, default_settings, is_configurable) VALUES (?, ?, ?, ?)";
public static function quote($s)
{
return Dal::get_connection()->escapeSimple($s);
}
function main()
{
$this->db = Dal::get_connection();
$this->note("Doing database update");
// We use $this->db->getOne() below instead of Dal::query_one() as
// the first time this script is run, the mc_db_status table will
// not exist, which will fire an exception with Dal::query_one()
// and break the installation. Please don't change this to
// Dal::query_one()! -PP 2006-11-15
$db_status = $this->db->getOne("SELECT * FROM mc_db_status LIMIT 1");
if (!DB::isError($db_status))
{
$this->note("mc_db_status table in place");
}
else
{
$this->note("Creating mc_db_status table");
$this->query("CREATE TABLE mc_db_status (stmt_key VARCHAR(255) NOT NULL, PRIMARY KEY(stmt_key))");
}
// add network column
if (!$this->column_exists("mc_db_status", "network")) {
$this->query("ALTER TABLE mc_db_status ADD COLUMN network VARCHAR(50) NOT NULL DEFAULT ''");
$this->query("ALTER TABLE mc_db_status DROP PRIMARY KEY");
$this->query("ALTER TABLE mc_db_status ADD PRIMARY KEY(stmt_key, network)");
}
/* 'broken' col disabled for now - use $this->broken_networks instead.
// make sure the network table has the 'broken' column before we get started
if (!$this->column_exists("networks", "broken")) {
Dal::query("ALTER TABLE networks ADD COLUMN broken BOOLEAN DEFAULT '0'");
}*/
// find networks which have their tables (i.e. skip over broken networks)
$this->networks = DbUpdate::get_valid_networks();
$override = @$_GET['override'];
if (!empty($override))
{
try {
Dal::query("INSERT INTO mc_db_status SET stmt_key=?", Array($override));
} catch (PAException $e) {
echo "<p>exception trying to override: ".$e->getMessage()."</p>";
}
}
$this->do_updates();
if (!$this->quiet) {
// $this->dump_schema();
$this->note("db update done.");
}
}
function main()
{
$this->db = Dal::get_connection();
$this->write('<table>');
$this->note('Doing database update');
// We use $this->db->getOne() below instead of Dal::query_one() as
// the first time this script is run, the mc_db_status table will
// not exist, which will fire an exception with Dal::query_one()
// and break the installation. Please don't change this to
// Dal::query_one()! -PP 2006-11-15
$db_status = $this->db->getOne('SELECT * FROM mc_db_status LIMIT 1');
if (!DB::isError($db_status)) {
$this->note('mc_db_status table in place');
} else {
$this->note('Creating mc_db_status table');
$this->query('CREATE TABLE mc_db_status (stmt_key VARCHAR(255) NOT NULL, PRIMARY KEY(stmt_key))');
}
// add network column
if (!$this->column_exists('mc_db_status', 'network')) {
$this->query('ALTER TABLE mc_db_status ADD COLUMN network VARCHAR(50) NOT NULL DEFAULT \'\'');
$this->query('ALTER TABLE mc_db_status DROP PRIMARY KEY');
$this->query('ALTER TABLE mc_db_status ADD PRIMARY KEY(stmt_key, network)');
}
// find networks which have their tables (i.e. skip over broken networks)
$this->networks = DbUpdate::get_valid_networks();
$override = @$_GET['override'];
if (!empty($override)) {
try {
Dal::query('INSERT INTO mc_db_status SET stmt_key=?', array($override));
} catch (PAException $e) {
echo '<p>exception trying to override: ', $e->getMessage(), '</p>';
}
}
$this->do_updates();
if (!$this->quiet) {
$this->note('CORE db updates done.');
$this->write('</table>');
}
}
/**
* Updated search method for users. Search type can also be specified with every field one is searching for
* e.g $search_item['first_name'] = array('value'=> 'test', 'type'=> LIKE_SEARCH) here type defines the search type
* in this case it is LIKE SEARCH (constant defined in api_constants.php) means value
* will be searched field_value = '%test%'
* For date of birth we can specify date range like $search_item['dob'] = array('value'=>
* array('lower_limit'=>200400, 'upper_limit'=>300500), 'type'=> RANGE_SEARCH)
* This method will help us to search for value given in range and will give more freedom than the
* method load_info_by_search which uses only LIKE to search for values.
*/
public static function user_search($search_item, $user_id, $network_id = NULL, $cnt = FALSE, $show = 'ALL', $page = 0, $sort_by = 'U.created', $direction = 'DESC')
{
Logger::log("Enter: User::user_search");
$db = Dal::get_connection();
$data = array();
$uids = array();
$i = 0;
$j = 1;
$order_by = $sort_by . ' ' . $direction;
if ($show == 'ALL' || $cnt == TRUE) {
$limit = '';
} else {
$start = ($page - 1) * $show;
$limit = 'LIMIT ' . $start . ',' . $show;
}
if ($network_id) {
$search_string = "SELECT (U.user_id) as uid, U.first_name as first_name, U.login_name as login_name, U.picture as picture, UP.field_perm as field_perm, count(U.user_id) as counts FROM users AS U LEFT OUTER JOIN user_profile_data AS UP ON UP.user_id = U.user_id INNER JOIN networks_users AS NU ON U.user_id = NU.user_id AND NU.network_id = ? WHERE ";
$data[] = $network_id;
} else {
$search_string = "SELECT (U.user_id) as uid, U.first_name as first_name, U.login_name as login_name, U.picture as picture, UP.field_perm as field_perm, count(U.user_id) as counts FROM users AS U LEFT OUTER JOIN user_profile_data AS UP ON UP.user_id = U.user_id WHERE ";
}
$search_items_count = count($search_item);
if ($search_items_count > 0) {
$counter = 0;
foreach ($search_item as $field_name => $field_details) {
$counter++;
switch ($field_details['type']) {
case AGE_SEARCH:
//date of birth will be saved in the formay YYYY-MM-DD
$search_string .= '( UP.field_name = ? AND UP.field_value BETWEEN DATE(DATE_ADD(NOW(), INTERVAL ? YEAR)) AND DATE(DATE_ADD(NOW(), INTERVAL ? YEAR)) AND UP.field_perm <> ?)';
$data[] = $field_name;
$data[] = $field_details['value']['upper_limit'] * -1;
$data[] = $field_details['value']['lower_limit'] * -1;
$data[] = NONE;
break;
case GREATER_THAN:
$search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )';
$data[] = $field_name;
$data[] = $field_details['value'];
$data[] = NONE;
break;
case RANGE_SEARCH:
$search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND ? AND UP.field_perm <> ? )';
$data[] = $field_name;
$data[] = $field_details['value']['lower_limit'];
$data[] = $field_details['value']['upper_limit'];
$data[] = NONE;
break;
case LIKE_SEARCH:
$search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )';
$data[] = $field_name;
$data[] = '%' . $field_details['value'] . '%';
$data[] = NONE;
break;
default:
$search_string .= '( UP.field_name = ? AND UP.field_value ' . $field_details['type'] . ' ? AND UP.field_perm <> ? )';
$data[] = $field_name;
$data[] = $field_details['value'];
$data[] = NONE;
}
$search_string .= $search_items_count == $counter ? ' AND ' : ' OR ';
}
}
$search_string .= " U.is_active = ? GROUP BY uid HAVING counts = ? ORDER BY {$order_by} {$limit}";
$data[] = ACTIVE;
$data[] = $search_items_count;
// Logger::log(print_r($search_item,1)."\n$search_string\n".print_r($data,1), LOGGER_ACTION);
$res = Dal::query($search_string, $data);
if (PEAR::isError($res)) {
Logger::log(" Throwing exception DB_QUERY_FAILED | Message: {$res->getMessage}()", LOGGER_ERROR);
throw new PAException(DB_QUERY_FAILED, $res->getMessage());
}
if ($cnt) {
return $res->numRows();
}
$uid_array = array();
if ($res->numrows() > 0) {
$i = 0;
while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
$uid_array[$i]['user_id'] = $row->uid;
$uid_array[$i]['login_name'] = $row->login_name;
$uid_array[$i]['picture'] = $row->picture;
$uid_array[$i]['first_name'] = $row->first_name;
$uid_array[$i]['field_perm'] = $row->field_perm;
$i++;
}
}
// search according to perm
$sql = "Select user_id from {relations} where relation_id = {$user_id}";
$res = Dal::query($sql);
if (PEAR::isError($res)) {
Logger::log(" Throwing exception DB_QUERY_FAILED | Message: {$res->getMessage}()", LOGGER_ERROR);
throw new PAException(DB_QUERY_FAILED, $res->getMessage());
}
if ($res->numrows() > 0) {
while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
$user_is_friend[] = $row->user_id;
}
}
$j = 0;
$user_ids = array();
for ($i = 0; $i < count($uid_array); $i++) {
if ($uid_array[$i]['field_perm'] == WITH_IN_DEGREE_1) {
if (!empty($user_is_friend)) {
if (in_array($uid_array[$i]['user_id'], $user_is_friend) || $user_id == $uid_array[$i]['user_id']) {
$user_ids[$j]['user_id'] = $uid_array[$i]['user_id'];
$user_ids[$j]['login_name'] = $uid_array[$i]['login_name'];
$user_ids[$j]['first_name'] = $uid_array[$i]['first_name'];
$user_ids[$j]['picture'] = $uid_array[$i]['picture'];
$j++;
}
}
} else {
if ($uid_array[$i]['field_perm'] == NONE) {
// Do nothing.
} else {
$user_ids[$j]['user_id'] = $uid_array[$i]['user_id'];
$user_ids[$j]['login_name'] = $uid_array[$i]['login_name'];
$user_ids[$j]['first_name'] = $uid_array[$i]['first_name'];
$user_ids[$j]['picture'] = $uid_array[$i]['picture'];
$j++;
}
}
}
$users_with_data = array('users_data' => $user_ids, 'total_users' => count($user_ids));
Logger::log("Exit: User::user_search");
return $users_with_data;
}