function get_not_forums($_status = -1, $_limit = -1, $_offset = 0)
{
$sql = "SELECT * FROM {t_forum}";
if ($_status > -1) {
$sql .= " WHERE status != {status}";
}
//NOTE: invertation
$sql .= " ORDER BY priority DESC, id";
$query = new FreechSqlQuery($sql);
$query->set_int('status', $_status);
$res = $this->db->SelectLimit($query->sql(), (int) $_limit, (int) $_offset) or die('ForumDB::get_forums()');
$forums = array();
while (!$res->EOF) {
$forum = new Forum($res->fields);
array_push($forums, $forum);
$res->MoveNext();
}
return $forums;
}
/**
* Returns all items that match the given criteria.
* $_search: The search values.
*/
function &get_items_from_query(&$_search, $_limit = -1, $_offset = 0)
{
// Get a list of item ids.
$query = new FreechSqlQuery();
$sql = 'SELECT m.id';
$sql .= ' FROM {t_modlog} m';
$sql .= ' WHERE 1';
foreach ($_search as $key => $value) {
if (is_int($value)) {
$sql .= " AND m.{$key}={" . $key . '}';
} else {
$sql .= " AND m.{$key} LIKE {" . $key . '}';
}
$query->set_var($key, $value);
}
$sql .= ' ORDER BY m.id DESC';
$query->set_sql($sql);
$res = $this->db->SelectLimit($query->sql(), (int) $_limit, (int) $_offset);
// Now fetch the items, including attributes.
$sql = 'SELECT m.*,';
$sql .= ' a.attribute_name,a.attribute_type,a.attribute_value,';
$sql .= ' UNIX_TIMESTAMP(m.created) created';
$sql .= ' FROM {t_modlog} m';
$sql .= ' LEFT JOIN {t_modlog_attribute} a ON a.modlog_id=m.id';
$sql .= ' WHERE 0';
while (!$res->EOF) {
$row = $res->FetchObj();
$sql .= ' OR m.id=' . $row->id;
$res->MoveNext();
}
$sql .= ' ORDER BY m.id DESC';
$query = new FreechSqlQuery($sql);
$res = $this->db->_Execute($query->sql());
$list = array();
while ($item = $this->_pop_item_from_result($res)) {
array_push($list, $item);
}
return $list;
}
function util_get_attribute($_dbn, $_name, $_default = NULL)
{
if (empty($db_base)) {
$db_base = cfg('db_tablebase');
}
$caption = 'Get version number of the Freech installation';
// Connect to the database.
$db = ADONewConnection($_dbn);
if (!$db) {
return new Result($caption, FALSE, 'Database connection failed. The table is possibly absent. ' . 'Check the installation by hand.');
}
if ($_POST['adodb_debug']) {
$db->debug = TRUE;
}
// Insert or update.
$sql = 'SELECT value FROM ' . $db_base . 'info WHERE name={name}';
$query = new FreechSqlQuery($sql);
$query->set_string('name', $_name);
// Run,.
$res = $db->execute($query->sql());
if (!$res) {
return $_default;
}
$row = $res->FetchObj();
return $row->value;
}
/**
* Returns the users who wrote the highest number of postings.
* $_limit: The maximum number of results.
*/
function get_top_users($_limit, $_since = 0)
{
$sql = "SELECT u.*, g.name icon_name, COUNT(*) n_postings,";
$sql .= "UNIX_TIMESTAMP(u.updated) updated,";
$sql .= "UNIX_TIMESTAMP(u.created) created";
$sql .= " FROM {t_user} u";
$sql .= " JOIN {t_group} g ON g.id=u.group_id";
$sql .= " JOIN {t_posting} m ON m.user_id=u.id";
$sql .= " WHERE u.id != {anonymous}";
if ($_since > 0) {
$sql .= " AND m.created>FROM_UNIXTIME({since})";
}
$sql .= " GROUP BY u.id";
$sql .= " ORDER BY n_postings DESC";
$query = new FreechSqlQuery($sql);
$query->set_int('anonymous', cfg('anonymous_user_id'));
$query->set_int('since', $_since);
$res = $this->db->SelectLimit($query->sql(), (int) $_limit) or die("UserDB::get_top_users()");
$users = array();
while (!$res->EOF) {
$user = $this->_get_user_from_row($res->fields);
$user->n_postings = $res->fields['n_postings'];
array_push($users, $user);
$res->MoveNext();
}
return $users;
}
function set_user_rating(&$api, $_msg, $rating)
{
$_forum_id = $_msg->get_forum_id();
$_posting_id = $_msg->get_id();
$_thread_id = $_msg->get_thread_id();
$_user_id = $api->user()->get_id();
$_rating = $rating;
$db = $api->db();
$db->StartTrans();
// first insert new user rating
$sql = "INSERT {t_rating_vote}";
$sql .= " (posting_id, thread_id, user_id, rating)";
$sql .= " VALUES ({posting_id}, {thread_id}, {user_id}, {rating})";
$query = new FreechSqlQuery($sql);
$query->set_int('posting_id', $_posting_id);
$query->set_int('thread_id', $_thread_id);
$query->set_int('user_id', $_user_id);
$query->set_int('rating', $_rating);
$db->_Execute($query->sql()) or die('ForumDB::save_rating(): user_rating');
// then select all existing ratings to compute new average rating
$sql = "SELECT posting_id as id, count(rating) as count,";
$sql .= " AVG(rating) as avg_rating FROM {t_rating_vote}";
$sql .= " WHERE posting_id={posting_id} GROUP BY thread_id";
$query = new FreechSqlQuery($sql);
$query->set_int('posting_id', $_posting_id);
$res = $db->_Execute($query->sql()) or die('ForumDB::save_rating(): query');
// save average rating into table
$sql = "UPDATE {t_posting}";
$sql .= " SET rating={rating}, rating_count={count}";
$sql .= " WHERE id={posting_id}";
$query = new FreechSqlQuery($sql);
$query->set_int('posting_id', $res->fields['id']);
$query->set_int('rating', $res->fields['avg_rating']);
$query->set_int('count', $res->fields['count']);
$db->_Execute($query->sql()) or die('ForumDB::save_rating(): rating');
$db->CompleteTrans();
}
function _poll_cast(&$db, &$user, $option_id)
{
$sql = 'INSERT INTO {t_poll_vote}';
$sql .= ' (option_id, user_id)';
$sql .= ' VALUES (';
$sql .= ' {option_id}, {user_id}';
$sql .= ')';
$query = new FreechSqlQuery($sql);
$query->set_int('option_id', $option_id);
$query->set_int('user_id', $user->get_id());
$db->Execute($query->sql()) or die('_poll_cast(): Insert');
}
/**
* Insert a new group or save an existing one.
*
* $_group: The group to be saved.
* Returns: The id of the (maybe newly inserted) group.
*/
function save_group(&$_group)
{
if (!is_object($_group)) {
die('GroupDB::save_group(): Invalid arg.');
}
$query = new FreechSqlQuery();
$query->set_int('id', $_group->get_id());
$query->set_string('name', $_group->get_name());
$query->set_bool('is_special', $_group->is_special());
$query->set_int('status', $_group->get_status());
if ($_group->get_id() < 1) {
$sql = "INSERT INTO {t_group}";
$sql .= " (";
$sql .= " id, name, is_special, status, created";
$sql .= " )";
$sql .= " VALUES (";
$sql .= " {id}, {name}, {is_special}, {status}, NULL";
$sql .= " )";
$query->set_sql($sql);
$this->db->StartTrans();
$this->db->_Execute($query->sql()) or die('GroupDB::save_group: Ins');
$newid = $this->db->Insert_ID();
$_group->set_id($newid);
$this->_save_permissions($_group);
$this->db->CompleteTrans();
return $newid;
}
$sql = "UPDATE {t_group} SET";
$sql .= " id={id},";
$sql .= " name={name},";
$sql .= " is_special={is_special},";
$sql .= " status={status}";
$sql .= " WHERE id={id}";
$query->set_sql($sql);
$this->db->StartTrans();
$this->db->_Execute($query->sql()) or die('GroupDB::save_group(): Upd');
$this->_save_permissions($_group);
$this->db->CompleteTrans();
return $_group->get_id();
}
function get_n_visitors($_since = 0)
{
trace('Enter');
if ($_since == 0) {
// IDX: visitor:counter
$query = new FreechSqlQuery("SELECT MAX(counter) FROM {t_visitor}");
} else {
// IDX: visitor:visit
$sql = "SELECT COUNT(*) FROM {t_visitor}";
$sql .= " WHERE visit > {start}";
$query = new FreechSqlQuery($sql);
$query->set_int('start', $_since);
}
$n = $this->db->GetOne($query->sql());
trace('Leave');
if (!$n) {
return 0;
}
return $n;
}
