public function modify_posting($event) { if ($event['mode'] == 'post' && !$event['forum_id']) { $forum_ary = array(); $forum_read_ary = $this->auth->acl_getf('f_read'); foreach ($forum_read_ary as $forum_id => $allowed) { if ($allowed['f_read'] && $this->auth->acl_get('f_post', $forum_id)) { if (!$this->exclude_forum($forum_id, $this->config['newtopic_forum'])) { continue; } $forum_ary[] = (int) $forum_id; } } if (sizeof($forum_ary)) { // Fetching topics of public forums $sql = 'SELECT forum_id, forum_name, forum_type FROM ' . FORUMS_TABLE . "\n\t\t\t\t\tWHERE " . $this->db->sql_in_set('forum_id', $forum_ary) . "\n\t\t\t\t\t\tAND forum_type != " . FORUM_LINK; $result = $this->db->sql_query($sql); $forumrow = $this->db->sql_fetchrowset($result); $this->db->sql_freeresult($result); $s_forum_options = '<select id="f" name="f" onchange="this.form.submit();">'; foreach ($forumrow as $row) { $s_forum_options .= '<option value="' . $row['forum_id'] . '"' . ($row['forum_id'] == $forum_id ? ' selected="selected"' : '') . '' . ($row['forum_type'] == FORUM_CAT ? ' disabled="disabled" class="disabled-option"' : '') . '>' . ($row['forum_type'] != FORUM_CAT ? ' ' : '') . $row['forum_name'] . '</option>'; $forum_id = $row['forum_type'] == FORUM_POST ? $row['forum_id'] : ''; } $s_forum_options .= '</select>'; $this->template->assign_vars(array('S_FORUM_OPTIONS' => $s_forum_options, 'S_FORUM_OPT_TRUE' => $forum_id ? true : false)); $event['forum_id'] = $forum_id; } } }
/** * Update BBCode order fields in the db on move up/down * * @param string $action The action move_up|move_down * @return null * @access public */ public function move($action) { $bbcode_id = $this->request->variable('id', 0); if (!check_link_hash($this->request->variable('hash', ''), $action . $bbcode_id)) { trigger_error($this->user->lang('FORM_INVALID'), E_USER_WARNING); } // Get current order $sql = 'SELECT bbcode_order FROM ' . BBCODES_TABLE . "\n\t\t\tWHERE bbcode_id = {$bbcode_id}"; $result = $this->db->sql_query($sql); $current_order = (int) $this->db->sql_fetchfield('bbcode_order'); $this->db->sql_freeresult($result); // First one can't be moved up if ($current_order <= 1 && $action == 'move_up') { return; } $order_total = $current_order * 2 + $this->increment($action); // Update the db $sql = 'UPDATE ' . BBCODES_TABLE . ' SET bbcode_order = ' . $order_total . ' - bbcode_order WHERE ' . $this->db->sql_in_set('bbcode_order', array($current_order, $current_order + $this->increment($action))); $this->db->sql_query($sql); // Resync bbcode_order $this->resynchronize_bbcode_order(); // return a JSON response if this was an AJAX request if ($this->request->is_ajax()) { $json_response = new \phpbb\json_response(); $json_response->send(array('success' => (bool) $this->db->sql_affectedrows())); } }
public function add_page_header_links($event) { if (!empty($this->config['allow_visits_counter'])) { $this->language->add_lang('common', 'dmzx/counter'); $sql = 'SELECT COUNT(*) AS visits_counter FROM ' . $this->visits_counter_table . ' WHERE ' . $this->db->sql_in_set('uvc_ip', $this->user->ip); $result = $this->db->sql_query($sql); $visits_counter = (int) $this->db->sql_fetchfield('visits_counter'); $this->db->sql_freeresult($result); $visits = $this->config['visits_counter']; if ($visits_counter == 0) { $sql_ary = array('uvc_ip' => $this->user->ip, 'uvc_timestamp' => time()); $sql = 'INSERT INTO ' . $this->visits_counter_table . ' ' . $this->db->sql_build_array('INSERT', $sql_ary); $this->db->sql_query($sql); $this->config->increment('visits_counter', 1, true); } else { $sql_ary = array('uvc_timestamp' => time()); $sql = 'UPDATE ' . $this->visits_counter_table . ' SET ' . $this->db->sql_build_array('UPDATE', $sql_ary) . ' WHERE ' . $this->db->sql_in_set('uvc_ip', $this->user->ip); $this->db->sql_query($sql); } $timestamp = time() - 3600 * 24; $sql_ary = array($timestamp); $sql = 'DELETE FROM ' . $this->visits_counter_table . ' WHERE uvc_timestamp < ' . $timestamp; $this->db->sql_query($sql); $sql = 'SELECT COUNT(*) AS num_del FROM ' . $this->visits_counter_table . ' '; $result = $this->db->sql_query($sql); $visitsok = (int) $this->db->sql_fetchfield('num_del'); $this->template->assign_vars(array('UNIQUE_VISITS_COUNTER' => $this->language->lang('UNIQUE_VISITS_COUNTER', $visitsok))); } }
/** * Run the cronjob. */ public function run() { $time = strtotime('- ' . $this->config['ajaxshoutbox_prune_days'] . ' days'); $sql = 'SELECT * FROM ' . $this->table . ' WHERE post_time <= ' . $time; $result = $this->db->sql_query($sql); $canpush = $this->push->canPush(); $delete = array(); while ($row = $this->db->sql_fetchrow($result)) { if ($canpush) { if ($this->push->delete($row['shout_id']) !== false) { $delete[] = $row['shout_id']; } } else { $delete[] = $row['shout_id']; } } $this->db->sql_freeresult(); if (sizeof($delete)) { $sql = 'DELETE FROM ' . $this->table . ' WHERE ' . $this->db->sql_in_set('shout_id', $delete); $this->db->sql_query($sql); $uuid = $this->user->data['user_id']; if (!$uuid) { $uuid = ANONYMOUS; } $this->log->add('admin', $uuid, $this->user->ip, 'LOG_AJAX_SHOUTBOX_PRUNED', time(), array(sizeof($delete))); } $this->config->set('shoutbox_prune_gc', time(), false); }
/** * @param array $sql_array */ private function _limit_by_group(array &$sql_array) { if (!empty($this->settings['group_ids'])) { $sql_array['FROM'][USER_GROUP_TABLE] = 'ug'; $sql_array['WHERE'][] = 't.topic_poster = ug.user_id'; $sql_array['WHERE'][] = $this->db->sql_in_set('ug.group_id', $this->settings['group_ids']); } }
public function submit_attachments($data) { if (empty($data['attachment_data'])) { return; } $space_taken = $files_added = 0; $orphan_rows = array(); foreach ($data['attachment_data'] as $pos => $attach_row) { $orphan_rows[(int) $attach_row['attach_id']] = array(); } if (sizeof($orphan_rows)) { $sql = 'SELECT attach_id, filesize, physical_filename FROM ' . ATTACHMENTS_TABLE . ' WHERE ' . $this->db->sql_in_set('attach_id', array_keys($orphan_rows)) . ' AND is_orphan = 1 AND poster_id = ' . (int) $this->user->data['user_id']; $result = $this->db->sql_query($sql); $orphan_rows = array(); while ($row = $this->db->sql_fetchrow($result)) { $orphan_rows[$row['attach_id']] = $row; } $this->db->sql_freeresult($result); } foreach ($data['attachment_data'] as $pos => $attach_row) { if ($attach_row['is_orphan'] && !in_array($attach_row['attach_id'], array_keys($orphan_rows))) { continue; } if (!$attach_row['is_orphan']) { // update entry in db if attachment already stored in db and filespace $sql = 'UPDATE ' . ATTACHMENTS_TABLE . "\n\t\t\t\t\tSET attach_comment = '" . $this->db->sql_escape($attach_row['attach_comment']) . "'\n\t\t\t\t\tWHERE attach_id = " . (int) $attach_row['attach_id'] . ' AND is_orphan = 0'; $this->db->sql_query($sql); } else { // insert attachment into db if (!@file_exists($this->phpbb_root_path . $this->config['upload_path'] . '/' . basename($orphan_rows[$attach_row['attach_id']]['physical_filename']))) { continue; } $space_taken += $orphan_rows[$attach_row['attach_id']]['filesize']; $files_added++; $attach_sql = array('post_msg_id' => $data['post_id'], 'topic_id' => $data['topic_id'], 'is_orphan' => 0, 'poster_id' => (int) $this->user->data['user_id'], 'attach_comment' => $attach_row['attach_comment']); $sql = 'UPDATE ' . ATTACHMENTS_TABLE . ' SET ' . $this->db->sql_build_array('UPDATE', $attach_sql) . ' WHERE attach_id = ' . $attach_row['attach_id'] . ' AND is_orphan = 1 AND poster_id = ' . (int) $this->user->data['user_id']; $this->db->sql_query($sql); } } if ($space_taken && $files_added) { $this->config->set('upload_dir_size', $this->config['upload_dir_size'] + $space_taken, true); $this->config->set('num_files', $this->config['num_files'] + $files_added, true); } }
public function users_online_string_sql($event) { $string_sql = $event['sql']; $online_users = $event['online_users']['online_users']; $sql = 'SELECT u.username, u.username_clean, u.user_id, u.user_type, u.user_allow_viewonline, u.user_colour, s.session_browser FROM ' . USERS_TABLE . ' u JOIN ' . SESSIONS_TABLE . ' s ON u.user_id = s.session_user_id WHERE ' . $this->db->sql_in_set('u.user_id', $event['online_users']['online_users']) . ' AND s.session_time >= ' . (time() - $this->config['load_online_time'] * 60) . ' GROUP BY u.user_id ORDER BY u.username_clean ASC'; $event['sql'] = $sql; }
/** * Get user's group ids * * @param array $user_id_ary An array of user ids to check * @return array An array of usergroup ids each user belongs to * @access public */ public function get_users_groups($user_id_ary) { $group_id_ary = array(); $sql = 'SELECT user_id, group_id FROM ' . USER_GROUP_TABLE . ' WHERE ' . $this->db->sql_in_set('user_id', $user_id_ary, false, true); $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { $group_id_ary[$row['user_id']][] = $row['group_id']; } $this->db->sql_freeresult($result); return $group_id_ary; }
/** * Build a cache of group names * * @param object $event The event object * @return null * @access public */ public function build_group_name_cache($event) { if ($this->cache->get('_user_groups') === false) { $sql_ary = array('SELECT' => 'ug.user_id, g.group_name, g.group_colour, g.group_type, g.group_id', 'FROM' => array(USERS_TABLE => 'u'), 'LEFT_JOIN' => array(array('FROM' => array(USER_GROUP_TABLE => 'ug'), 'ON' => 'ug.user_id = u.user_id'), array('FROM' => array(GROUPS_TABLE => 'g'), 'ON' => 'ug.group_id = g.group_id')), 'WHERE' => $this->db->sql_in_set('u.user_type', array(USER_FOUNDER, USER_NORMAL)) . ' AND ug.user_pending = 0', 'ORDER_BY' => 'u.user_id ASC, g.group_name'); $result = $this->db->sql_query($this->db->sql_build_query('SELECT', $sql_ary)); $user_groups = array(); while ($row = $this->db->sql_fetchrow($result)) { $user_groups[$row['user_id']][] = array('group_name' => (string) $row['group_name'], 'group_colour' => $row['group_colour'], 'group_id' => $row['group_id'], 'group_type' => $row['group_type']); } $this->db->sql_freeresult($result); // cache this data for 5 minutes $this->cache->put('_user_groups', $user_groups, 300); } }
/** * Processes the users's profile-field data as soon as it is grabbed from the DB. * It will use the profile-field data to try to grab info from the Battle.net API. * * @var int|array $user_ids Single user id or an array of ids * @var array $field_data Array with profile fields data * * @return array $field_data Array with modified profile fields data */ public function process_pf_grab($user_ids, $field_data) { $pbwow_config = $this->pbwow_config; if (isset($pbwow_config['bnetchars_enable']) && $pbwow_config['bnetchars_enable'] && $this->avatars_enabled_full) { $cachelife = isset($pbwow_config['bnetchars_cachetime']) ? intval($pbwow_config['bnetchars_cachetime']) : 86400; $apitimeout = isset($pbwow_config['bnetchars_timeout']) ? intval($pbwow_config['bnetchars_timeout']) : 1; $apikey = isset($pbwow_config['bnet_apikey']) ? $pbwow_config['bnet_apikey'] : false; // No API key? Cancel everything if (!$apikey) { return $field_data; } // Get all the characters of the requested users $sql = 'SELECT * FROM ' . $this->pbwow_chars_table . ' WHERE ' . $this->db->sql_in_set('user_id', $user_ids); $result = $this->db->sql_query($sql); $char_data = $no_call_list = array(); while ($row = $this->db->sql_fetchrow($result)) { $char_data[$row['user_id']] = $row; } $this->db->sql_freeresult($result); // Get a user list with all the API calls to be made $call_list = $this->generate_api_call_list($user_ids, $field_data, $char_data, $cachelife, $apikey); // Extract the users that have valid CPF input values, but don't need an API call if (isset($call_list['no_call'])) { $no_call_list = $call_list['no_call']; unset($call_list['no_call']); } // Get the character data from the Battle.net API $api_data = $this->call_bnet_api($call_list, $apitimeout); // Use the data from the API to save and merge with CPF data $field_data = $this->process_api_data($api_data, $no_call_list, $char_data, $field_data); } return $field_data; }
/** * The actual validation */ public function check_answer() { // Well how did the user sorted it $options_left = $this->request->variable('sortables_options_left', array(0)); $options_right = $this->request->variable('sortables_options_right', array(0)); // Make sure the didn't submitted more options then it should (like trying everything... left/right: options ^ 2 ) if ($this->total_options === sizeof($options_left) + sizeof($options_right)) { // Let's count how many options the user sorted correctly $sql = 'SELECT COUNT(*) AS total FROM ' . $this->table_sortables_answers . ' WHERE question_id = ' . (int) $this->question . ' AND ((answer_sort = 0 AND ' . $this->db->sql_in_set('answer_id', $options_left, false, true) . ') OR (answer_sort = 1 AND ' . $this->db->sql_in_set('answer_id', $options_right, false, true) . '))'; $result = $this->db->sql_query($sql); $total_options_good = (int) $this->db->sql_fetchfield('total'); // Now compare that amount with the total amount of options for this question if ($this->total_options === $total_options_good) { $this->solved = $this::SOLVED; // Remember this for the hidden fields $this->options_left = $options_left; $this->options_right = $options_right; } $this->db->sql_freeresult($result); } return $this->solved === $this::SOLVED; }
/** * Synchronize queue topic url values. * * @return null */ protected function sync_contrib_topics($start) { $i = 0; $limit = 250; $topic_type_where = $this->db->sql_in_set('topic_type', array(TITANIA_SUPPORT, TITANIA_QUEUE_DISCUSSION)); $sql = 'SELECT contrib_id, contrib_type, contrib_name_clean FROM ' . $this->contribs_table; $result = $this->db->sql_query_limit($sql, $limit, $start); while ($row = $this->db->sql_fetchrow($result)) { $url = serialize(array('contrib_type' => $this->contrib_types->get($row['contrib_type'])->url, 'contrib' => $row['contrib_name_clean'])); $where = 'parent_id = ' . (int) $row['contrib_id'] . ' AND ' . $topic_type_where; $this->update_field($this->topics_table, 'topic', $url, $where); $i++; } $this->db->sql_freeresult(); $sql = "SELECT topic_id, topic_url\n\t\t\tFROM {$this->topics_table}\n\t\t\tWHERE {$topic_type_where}"; $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { $where = 'topic_id = ' . (int) $row['topic_id']; $this->update_field($this->posts_table, 'post', $row['topic_url'], $where); } $this->db->sql_freeresult($result); if ($i === $limit) { return $start + $limit; } }
/** * {@inheritdoc} */ public function delete_notifications($notification_type_id, $item_id, $parent_id = false, $user_id = false) { $sql = 'DELETE FROM ' . $this->notifications_table . ' WHERE notification_type_id = ' . (int) $notification_type_id . ' AND ' . (is_array($item_id) ? $this->db->sql_in_set('item_id', $item_id) : 'item_id = ' . (int) $item_id) . ($parent_id !== false ? ' AND ' . (is_array($parent_id) ? $this->db->sql_in_set('item_parent_id', $parent_id) : 'item_parent_id = ' . (int) $parent_id) : '') . ($user_id !== false ? ' AND ' . (is_array($user_id) ? $this->db->sql_in_set('user_id', $user_id) : 'user_id = ' . (int) $user_id) : ''); $this->db->sql_query($sql); }
/** * Delete attachments from database table */ protected function delete_attachments_from_db() { /** * Perform additional actions before attachment(s) deletion * * @event core.delete_attachments_before * @var string mode Variable containing attachments deletion mode, can be: post|message|topic|attach|user * @var mixed ids Array or comma separated list of ids corresponding to the mode * @var bool resync Flag indicating if posts/messages/topics should be synchronized * @var string sql_id The field name to collect/delete data for depending on the mode * @var array post_ids Array with post ids for deleted attachment(s) * @var array topic_ids Array with topic ids for deleted attachment(s) * @var array message_ids Array with private message ids for deleted attachment(s) * @var array physical Array with deleted attachment(s) physical file(s) data * @since 3.1.7-RC1 */ $vars = array('mode', 'ids', 'resync', 'sql_id', 'post_ids', 'topic_ids', 'message_ids', 'physical'); extract($this->dispatcher->trigger_event('core.delete_attachments_before', compact($vars))); // Delete attachments $sql = 'DELETE FROM ' . ATTACHMENTS_TABLE . ' WHERE ' . $this->db->sql_in_set($this->sql_id, $this->ids); $sql .= $this->sql_where; $this->db->sql_query($sql); $this->num_deleted = $this->db->sql_affectedrows(); }
/** * @param array $allowed_extensions * @param bool $exclude_in_message * @param string $order_by * @return string */ private function _get_attachment_sql($allowed_extensions, $exclude_in_message, $order_by) { return 'SELECT * FROM ' . ATTACHMENTS_TABLE . ' WHERE ' . $this->db->sql_in_set('post_msg_id', $this->store['attachments']) . ($exclude_in_message ? ' AND in_message = 0' : '') . (sizeof($allowed_extensions) ? ' AND ' . $this->db->sql_in_set('extension', $allowed_extensions) : '') . ' ORDER BY ' . $order_by; }
/** * Get batch to process. * * @param bool $fetch_attach_data Whether to fetch attachment data. * @return array */ protected function get_batch($fetch_attach_data) { $types = $this->types->use_composer(); if (empty($types)) { return array(); } $attach_fields = $attach_table = $attach_where = ''; if ($fetch_attach_data) { $attach_fields = ', a.attachment_directory, a.physical_filename'; $attach_table = ", {$this->attachments_table} a"; $attach_where = 'AND a.attachment_id = r.attachment_id'; } $sql = 'SELECT c.contrib_id, c.contrib_name_clean, c.contrib_type, r.revision_id, r.attachment_id, r.revision_composer_json' . $attach_fields . ' FROM ' . $this->contribs_table . ' c, ' . $this->revisions_table . ' r ' . $attach_table . ' WHERE c.contrib_id = r.contrib_id ' . $attach_where . ' AND c.contrib_status = ' . TITANIA_CONTRIB_APPROVED . ' AND r.revision_status = ' . TITANIA_REVISION_APPROVED . ' AND ' . $this->db->sql_in_set('c.contrib_type', $types) . ' ORDER BY c.contrib_id ASC, r.revision_id ASC'; $result = $this->db->sql_query_limit($sql, $this->limit, $this->start); $contribs = array(); while ($row = $this->db->sql_fetchrow($result)) { $contrib_id = (int) $row['contrib_id']; if (!isset($contribs[$contrib_id])) { $contribs[$contrib_id] = array(); } $contribs[$contrib_id][] = $row; } $this->db->sql_freeresult($result); return $contribs; }
/** * Get topics count by type * * @return array Topics count array with type in array keys and count * in array values */ public function get_topics_count() { if (($return_ary = $this->cache->get('_b3p_topics_type_count')) === false) { $return_ary = array(POST_ANNOUNCE => 0, POST_STICKY => 0); $sql_in = array(POST_ANNOUNCE, POST_STICKY); $sql = 'SELECT DISTINCT(topic_id) AS topic_id, topic_type AS type FROM ' . TOPICS_TABLE . ' WHERE ' . $this->db->sql_in_set('topic_type', $sql_in, false); $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { switch ($row['type']) { case POST_ANNOUNCE: ++$return_ary[POST_ANNOUNCE]; break; case POST_STICKY: ++$return_ary[POST_STICKY]; break; } } $this->db->sql_freeresult($result); // cache topics type count for 1 hour $this->cache->put('_b3p_topics_type_count', $return_ary, 3600); } return $return_ary; }
/** * Submit all attachments. * * @param int $access * @param array $comments */ public function submit($access = access::PUBLIC_LEVEL, $comments = array()) { if (!$this->get_count()) { return; } // Update access and is_orphan $sql_ary = array('object_id' => $this->object_id, 'attachment_access' => $access, 'is_orphan' => 0); $sql = 'UPDATE ' . $this->attachments_table . ' SET ' . $this->db->sql_build_array('UPDATE', $sql_ary) . ' WHERE ' . $this->db->sql_in_set('attachment_id', $this->get_all_ids()); $this->db->sql_query($sql); foreach ($this->get_all() as $id => $attach) { $attach->__set_array($sql_ary); $update = array(); if (isset($comments[$id]) && $this->get('attachment_comment') != $comments[$id]) { $update['attachment_comment'] = $comments[$id]; } if (isset($this->custom_order[$id]) && $attach->get('attachment_order') != $this->custom_order[$id]) { $update['attachment_order'] = (int) $this->custom_order[$id]; } if ($update) { $attach->submit($update); } } }
/** * Resync specified type * * @param string $type Type of resync * @param array $ids IDs to resync */ public function resync($type, $ids) { if (empty($type) || !is_array($ids) || !sizeof($ids) || !in_array($type, array('post', 'topic', 'message'))) { return; } $this->set_type_constraints($type); // Just check which elements are still having an assigned attachment // not orphaned by querying the attachments table $sql = 'SELECT ' . $this->attach_sql_id . ' FROM ' . ATTACHMENTS_TABLE . ' WHERE ' . $this->db->sql_in_set($this->attach_sql_id, $ids) . $this->sql_where; $result = $this->db->sql_query($sql); $remaining_ids = array(); while ($row = $this->db->sql_fetchrow($result)) { $remaining_ids[] = $row[$this->attach_sql_id]; } $this->db->sql_freeresult($result); // Now only unset those ids remaining $ids = array_diff($ids, $remaining_ids); if (sizeof($ids)) { $sql = 'UPDATE ' . $this->resync_table . ' SET ' . $type . '_attachment = 0 WHERE ' . $this->db->sql_in_set($this->resync_sql_id, $ids); $this->db->sql_query($sql); } }
/** * Delete category content * * @return array */ private function _delete_cat_content() { $this->db->sql_transaction('begin'); // Before we remove anything we make sure we are able to adjust the post counts later. ;) $sql = 'SELECT link_id, link_banner FROM ' . DIR_LINK_TABLE . ' WHERE link_cat = ' . (int) $this->cat_id; $result = $this->db->sql_query($sql); $link_ids = array(); while ($row = $this->db->sql_fetchrow($result)) { $link_ids[] = $row['link_id']; if ($row['link_banner'] && !preg_match('/^(http:\\/\\/|https:\\/\\/|ftp:\\/\\/|ftps:\\/\\/|www\\.).+/si', $row['link_banner'])) { $banner_img = $this->dir_helper->get_banner_path(basename($row['link_banner'])); if (file_exists($banner_img)) { @unlink($banner_img); } } } $this->db->sql_freeresult($result); if (sizeof($link_ids)) { // Delete links datas $link_datas_ary = array(DIR_COMMENT_TABLE => 'comment_link_id', DIR_VOTE_TABLE => 'vote_link_id'); foreach ($link_datas_ary as $table => $field) { $this->db->sql_query("DELETE FROM {$table} WHERE " . $this->db->sql_in_set($field, $link_ids)); } } // Delete cats datas $cat_datas_ary = array(DIR_LINK_TABLE => 'link_cat', DIR_WATCH_TABLE => 'cat_id'); foreach ($cat_datas_ary as $table => $field) { $this->db->sql_query("DELETE FROM {$table} WHERE {$field} = " . (int) $this->cat_id); } $this->db->sql_transaction('commit'); return array(); }
protected function get_last_visit($user_id, $author = 0) { if ($user_id == $author) { $last_visit = ''; } else { $sql = 'SELECT session_user_id, MAX(session_time) AS session_time FROM ' . SESSIONS_TABLE . ' WHERE session_time >= ' . (time() - $this->config['session_length']) . ' AND ' . $this->db->sql_in_set('session_user_id', $user_id) . ' GROUP BY session_user_id'; $result = $this->db->sql_query($sql); $session_times = array(); while ($row = $this->db->sql_fetchrow($result)) { $session_times[$row['session_user_id']] = $row['session_time']; } $this->db->sql_freeresult($result); $sql = 'SELECT user_lastvisit FROM ' . USERS_TABLE . ' WHERE ' . $this->db->sql_in_set('user_id', $user_id); $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { $session_time = !empty($session_times[$user_id]) ? $session_times[$user_id] : 0; $last_visit = !empty($session_time) ? $session_time : $row['user_lastvisit']; $last_visit = $this->user->format_date($last_visit); } $this->db->sql_freeresult($result); } return $last_visit; }
/** * Mass delete configuration options. * * @param array $keys Set of configuration option names * * @return null */ public function delete_array(array $keys) { $sql = 'DELETE FROM ' . $this->table . ' WHERE ' . $this->db->sql_in_set('config_name', $keys, false, true); $this->db->sql_query($sql); }
/** * Update website verification number after a missing backlink, and send notificaton * * @param array $u_array Information about website * @param int $next_prune Date of next auto check * @return null */ private function _update_check($u_array, $next_prune) { if (!class_exists('messenger')) { include $this->root_path . 'includes/functions_messenger.' . $this->php_ext; } $messenger = new \messenger(false); // cron.php don't call $user->setup(), so $this->timezone is unset. // We need to define it, because we use user->format_date below $this->user->timezone = new \DateTimeZone($this->config['board_timezone']); $sql = 'UPDATE ' . DIR_LINK_TABLE . ' SET link_nb_check = link_nb_check + 1 WHERE ' . $this->db->sql_in_set('link_id', array_keys($u_array)); $this->db->sql_query($sql); foreach ($u_array as $data) { strip_bbcode($data['link_description']); $notification_data = array('cat_name' => \ernadoo\phpbbdirectory\core\categorie::getname((int) $data['link_cat']), 'link_id' => $data['link_id'], 'link_name' => $data['link_name'], 'link_url' => $data['link_url'], 'link_description' => $data['link_description'], 'next_cron' => $this->user->format_date($next_prune, $data['user_dateformat'])); if ($data['link_nb_check']) { $this->notification->delete_notifications('ernadoo.phpbbdirectory.notification.type.directory_website_error_cron', $notification_data); } // New notification system can't send mail to an anonymous user with an email address stored in another table than phpbb_users if ($data['link_user_id'] == ANONYMOUS) { $username = $email = $data['link_guest_email']; $messenger->template('@ernadoo_phpbbdirectory/directory_website_error_cron', $data['user_lang']); $messenger->to($email, $username); $messenger->assign_vars(array('USERNAME' => htmlspecialchars_decode($username), 'LINK_NAME' => $data['link_name'], 'LINK_URL' => $data['link_url'], 'LINK_DESCRIPTION' => $data['link_description'], 'NEXT_CRON' => $this->user->format_date($next_prune, $data['user_dateformat']))); $messenger->send(NOTIFY_EMAIL); } else { $this->notification->add_notifications('ernadoo.phpbbdirectory.notification.type.directory_website_error_cron', $notification_data); } } }
/** * Commits the changes to the database * * @param array $thumbnail_created */ protected function commit_changes(array $thumbnail_created) { $sql = 'UPDATE ' . ATTACHMENTS_TABLE . ' SET thumbnail = 1 WHERE ' . $this->db->sql_in_set('attach_id', $thumbnail_created); $this->db->sql_query($sql); }
/** * Performs a search on keywords depending on display specific params. You have to run split_keywords() first * * @param array $keywords_ary contains each words to search * @param string $fields contains either titleonly (link titles should be searched), desconly (only description bodies should be searched) * @param string $terms is either 'all' (use query as entered, words without prefix should default to "have to be in field") or 'any' (ignore search query parts and just return all posts that contain any of the specified words) * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query * @param string $sort_key is the key of $sort_by_sql for the selected sorting * @param string $sort_dir is either a or d representing ASC and DESC * @param string $sort_days specifies the maximum amount of days a post may be old * @param array $ex_cid_ary specifies an array of category ids which should not be searched * @param int $cat_id is set to 0 or a topic id, if it is not 0 then only posts in this topic should be searched * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered * @param int $start indicates the first index of the page * @param int $per_page number of ids each page is supposed to contain * @return int total number of results */ public function keyword_search($keywords_ary, $fields, $terms, $sort_by_sql, $sort_key, $sort_dir, $sort_days, $ex_cid_ary, $cat_id, &$id_ary, $start, $per_page) { $matches = array(); switch ($fields) { case 'titleonly': $matches[] = 'l.link_name'; break; case 'desconly': $matches[] = 'l.link_description'; break; default: $matches = array('l.link_name', 'l.link_description'); } $search_query = ''; foreach ($keywords_ary as $word) { $match_search_query = ''; foreach ($matches as $match) { $match_search_query .= ($match_search_query ? ' OR ' : '') . 'LOWER(' . $match . ') '; $match_search_query .= $this->db->sql_like_expression(str_replace('*', $this->db->get_any_char(), $this->db->get_any_char() . strtolower($word) . $this->db->get_any_char())); } $search_query .= (!$search_query ? '' : ($terms == 'all' ? ' AND ' : ' OR ')) . '(' . $match_search_query . ')'; } $direction = $sort_dir == 'd' ? 'DESC' : 'ASC'; if (is_array($sort_by_sql[$sort_key])) { $sql_sort_order = implode(' ' . $direction . ', ', $sort_by_sql[$sort_key]) . ' ' . $direction; } else { $sql_sort_order = $sort_by_sql[$sort_key] . ' ' . $direction; } $sql_array = array('SELECT' => 'l.link_id', 'FROM' => array(DIR_LINK_TABLE => 'l'), 'WHERE' => 'l.link_active = 1 ' . ($search_query ? 'AND (' . $search_query . ')' : '') . ' ' . (sizeof($ex_cid_ary) ? ' AND ' . $this->db->sql_in_set('l.link_cat', $ex_cid_ary, true) : '') . ' ' . ($cat_id ? ' AND ' . $this->db->sql_in_set('l.link_cat', $cat_id) : '') . ' ' . ($sort_days ? ' AND l.link_time >= ' . (time() - $sort_days * 86400) : ''), 'ORDER_BY' => $sql_sort_order); if ($sql_sort_order[0] == 'u') { $sql_array['LEFT_JOIN'][] = array('FROM' => array(USERS_TABLE => 'u'), 'ON' => 'u.user_id = l.link_user_id'); } $sql = $this->db->sql_build_query('SELECT', $sql_array); $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { $id_ary[] = $row['link_id']; } $this->db->sql_freeresult($result); $total_match_count = sizeof($id_ary); $id_ary = array_slice($id_ary, $start, (int) $per_page); return $total_match_count; }
/** * Adds forbidden BBCodes to the passed SQL where statement */ public function mchat_sql_append_forbidden_bbcodes($sql_where) { $disallowed_bbcodes = explode('|', strtoupper($this->config['mchat_bbcode_disallowed'])); if (!empty($disallowed_bbcodes)) { $sql_where .= ' AND ' . $this->db->sql_in_set('UPPER(b.bbcode_tag)', $disallowed_bbcodes, true); } return $sql_where; }
protected function get_sql_statement() { $sql_ary = array('SELECT' => 'u.user_id, u.username, u.user_colour, u.user_avatar, u.user_avatar_type, u.user_avatar_width, u.user_avatar_height', 'FROM' => array(USERS_TABLE => 'u'), 'WHERE' => $this->db->sql_in_set('u.user_type', array(USER_NORMAL, USER_FOUNDER))); $sql_method = '_set_' . $this->settings['query_type'] . '_sql'; call_user_func_array(array($this, $sql_method), array(&$sql_ary)); $this->_set_range_sql($sql_ary); return $this->db->sql_build_query('SELECT', $sql_ary); }
/** * Find the users who want to receive notifications (helper) * * @param array|bool $user_ids User IDs to check if they want to receive notifications * (Bool False to check all users besides anonymous and bots (USER_IGNORE)) * @param array $options * @return array */ protected function check_user_notification_options($user_ids = false, $options = array()) { $options = array_merge(array('ignore_users' => array(), 'item_type' => $this->get_type(), 'item_id' => 0), $options); if ($user_ids === false) { $user_ids = array(); $sql = 'SELECT user_id FROM ' . USERS_TABLE . ' WHERE user_id <> ' . ANONYMOUS . ' AND user_type <> ' . USER_IGNORE; $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { $user_ids[] = $row['user_id']; } $this->db->sql_freeresult($result); } if (empty($user_ids)) { return array(); } $rowset = $output = array(); $sql = 'SELECT user_id, method, notify FROM ' . $this->user_notifications_table . ' WHERE ' . $this->db->sql_in_set('user_id', $user_ids) . "\n\t\t\t\tAND item_type = '" . $this->db->sql_escape($options['item_type']) . "'\n\t\t\t\tAND item_id = " . (int) $options['item_id']; $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { if (isset($options['ignore_users'][$row['user_id']]) && in_array($row['method'], $options['ignore_users'][$row['user_id']])) { continue; } if (!isset($rowset[$row['user_id']])) { $rowset[$row['user_id']] = array(); } $rowset[$row['user_id']][$row['method']] = $row['notify']; if (!isset($output[$row['user_id']])) { $output[$row['user_id']] = array(); } if ($row['notify']) { $output[$row['user_id']][] = $row['method']; } } $this->db->sql_freeresult($result); $default_methods = $this->notification_manager->get_default_methods(); foreach ($user_ids as $user_id) { if (isset($options['ignore_users'][$user_id])) { continue; } if (!array_key_exists($user_id, $rowset)) { // No rows at all for this user, use the default methods $output[$user_id] = $default_methods; } else { foreach ($default_methods as $default_method) { if (!array_key_exists($default_method, $rowset[$user_id])) { // No user preference for this type recorded, but it should be enabled by default. $output[$user_id][] = $default_method; } } } } return $output; }
/** * Parse template variables for module * * @param int $module_id Module ID * @param string $type Module type (center or side) * * @return string Template file name or false if nothing should * be displayed */ protected function parse_template($module_id, $type) { $attach_forums = false; $where = ''; // Get filetypes and put them into an array $filetypes = $this->get_selected_filetypes($module_id); if ($this->config['board3_attachments_forum_ids_' . $module_id] !== '') { $attach_forums_config = strpos($this->config['board3_attachments_forum_ids_' . $module_id], ',') !== false ? explode(',', $this->config['board3_attachments_forum_ids_' . $module_id]) : array($this->config['board3_attachments_forum_ids_' . $module_id]); $forum_list = array_unique(array_keys($this->auth->acl_getf('f_read', true))); if ($this->config['board3_attachments_forum_exclude_' . $module_id]) { $forum_list = array_unique(array_diff($forum_list, $attach_forums_config)); } else { $forum_list = array_unique(array_intersect($attach_forums_config, $forum_list)); } } else { $forum_list = array_unique(array_keys($this->auth->acl_getf('f_read', true))); } if (sizeof($forum_list)) { $attach_forums = true; $where = 'AND ' . $this->db->sql_in_set('t.forum_id', $forum_list); } if (sizeof($filetypes)) { if ($this->config['board3_attachments_exclude_' . $module_id]) { $where .= ' AND ' . $this->db->sql_in_set('a.extension', $filetypes, true); } else { $where .= ' AND ' . $this->db->sql_in_set('a.extension', $filetypes); } } if ($attach_forums === true) { // Just grab all attachment info from database $sql = 'SELECT a.*, t.forum_id FROM ' . ATTACHMENTS_TABLE . ' a, ' . TOPICS_TABLE . ' t WHERE a.topic_id <> 0 AND a.topic_id = t.topic_id ' . $where . ' ORDER BY filetime ' . (!$this->config['display_order'] ? 'DESC' : 'ASC') . ', post_msg_id ASC'; $result = $this->db->sql_query_limit($sql, $this->config['board3_attachments_number_' . $module_id], 0, 600); while ($row = $this->db->sql_fetchrow($result)) { $size_lang = $row['filesize'] >= 1048576 ? $this->user->lang['MIB'] : ($row['filesize'] >= 1024 ? $this->user->lang['KIB'] : $this->user->lang['BYTES']); $row['filesize'] = $row['filesize'] >= 1048576 ? round(round($row['filesize'] / 1048576 * 100) / 100, 2) : ($row['filesize'] >= 1024 ? round(round($row['filesize'] / 1024 * 100) / 100, 2) : $row['filesize']); $raw_filename = utf8_substr($row['real_filename'], 0, strrpos($row['real_filename'], '.')); $replace = character_limit($raw_filename, $this->config['board3_attach_max_length_' . $module_id]); $this->template->assign_block_vars('attach_' . $type, array('FILESIZE' => $row['filesize'] . ' ' . $size_lang, 'FILETIME' => $this->user->format_date($row['filetime']), 'DOWNLOAD_COUNT' => (int) $row['download_count'], 'FILENAME' => $replace, 'REAL_FILENAME' => $row['real_filename'], 'PHYSICAL_FILENAME' => basename($row['physical_filename']), 'ATTACH_ID' => $row['attach_id'], 'POST_IDS' => !empty($post_ids[$row['attach_id']]) ? $post_ids[$row['attach_id']] : '', 'POST_MSG_ID' => $row['post_msg_id'], 'U_FILE' => append_sid($this->phpbb_root_path . 'download/file.' . $this->php_ext, 'id=' . $row['attach_id']), 'U_TOPIC' => append_sid($this->phpbb_root_path . 'viewtopic.' . $this->php_ext, 'p=' . $row['post_msg_id'] . '#p' . $row['post_msg_id']))); } $this->db->sql_freeresult($result); $this->template->assign_var('S_DISPLAY_ATTACHMENTS', true); } else { $this->template->assign_var('S_DISPLAY_ATTACHMENTS', false); } return 'attachments_' . $type . '.html'; }
/** * Load user helper * * @param array $user_ids */ public function load_users(array $user_ids) { $user_ids[] = ANONYMOUS; // Make user_ids unique and convert to integer. $user_ids = array_map('intval', array_unique($user_ids)); // Do not load users we already have in $this->users $user_ids = array_diff($user_ids, array_keys($this->users)); if (sizeof($user_ids)) { $sql = 'SELECT * FROM ' . $this->users_table . ' WHERE ' . $this->db->sql_in_set('user_id', $user_ids); $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { $this->users[$row['user_id']] = $row; } $this->db->sql_freeresult($result); } }