function geoserverGetTilelayerList(&$pListHash)
{
global $gBitSystem;
require_once LIBERTY_PKG_PATH . "LibertyContent.php";
$ret = array();
$bindVars = array();
$selectSql = '';
$joinSql = '';
$whereSql = '';
if (empty($pListHash['sort_mode'])) {
// $pListHash['sort_mode'] = array( 'gtl.`tiles_name_asc`' );
$pListHash['sort_mode'] = array('gtt.`theme_title_asc`', 'gtl.`tiles_name_asc`');
}
@LibertyContent::prepGetList($pListHash);
$joinSql .= " INNER JOIN `" . BIT_DB_PREFIX . "geoserver_tilelayers_meta` gtm ON( gtm.`tilelayer_id` = gtl.`tilelayer_id` ) " . " LEFT JOIN `" . BIT_DB_PREFIX . "geoserver_tilelayers_themes` gtt ON( gtt.`theme_id` = gtm.`theme_id` ) ";
$selectSql .= ", gtm.*, gtt.theme_title";
if (@BitBase::verifyId($pListHash['theme_id'])) {
$whereSql = " WHERE gtm.`theme_id` = ? ";
$bindVars[] = $pListHash['theme_id'];
}
$sql = "SELECT gtl.* {$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "gmaps_tilelayers` gtl {$joinSql}\n\t\t\t{$whereSql}\n\t\t\tORDER BY " . $gBitSystem->mDb->convertSortmode($pListHash['sort_mode']);
$result = $gBitSystem->mDb->query($sql, $bindVars, $pListHash['max_records'], $pListHash['offset']);
while ($aux = $result->fetchRow()) {
$ret[$aux['tilelayer_id']] = $aux;
}
$pListHash['cant'] = $gBitSystem->mDb->getOne("SELECT COUNT( gtl.`tilelayer_id` ) FROM `" . BIT_DB_PREFIX . "gmaps_tilelayers` gtl {$joinSql} {$whereSql}", $bindVars);
@LibertyContent::postGetList($pListHash);
return $ret;
}
/**
* getRefererList gets a list of referers
*
* @param array $pListHash
* @access public
* @return array of referers
*/
function getRefererList(&$pListHash)
{
global $gBitSystem;
$hashKey = '';
$ret = $bindVars = array();
$selectSql = $joinSql = $whereSql = $groupSql = "";
$hashSql = "uu.`user_id` AS `hash_key`, ";
if (empty($pListHash['sort_mode'])) {
$pListHash['sort_mode'] = 'uu.`registration_date_desc`';
}
static::prepGetList($pListHash);
if (empty($pListHash['period_format'])) {
$pListHash['period_format'] = 'Y-W';
}
if (!empty($pListHash['period_format']) && !empty($pListHash['timeframe'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= $this->mDb->SQLDate($pListHash['period_format'], $this->mDb->SQLIntToTimestamp('registration_date')) . '=?';
$bindVars[] = $pListHash['timeframe'];
$hashKey = 'host';
} else {
$hashSql = $this->mDb->SQLDate($pListHash['period_format'], $this->mDb->SQLIntToTimestamp('registration_date')) . ' AS `hash_key`,';
$hashKey = 'period';
}
if (!empty($pListHash['find']) && is_string($pListHash['find'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " UPPER( `referer_url` ) LIKE ?";
$bindVars[] = '%' . strtoupper($pListHash['find']) . '%';
if (empty($pListHash['timeframe']) && !empty($pListHash['period_format'])) {
$hashSql = $this->mDb->SQLDate($pListHash['period_format'], $this->mDb->SQLIntToTimestamp('registration_date')) . ' AS `hash_key`,';
$hashKey = 'period';
}
}
$query = "SELECT {$hashSql} uu.*, sru.`referer_url`\n\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "users_users` uu\n\t\t\t\t\t \tLEFT JOIN `" . BIT_DB_PREFIX . "stats_referer_users_map` srum ON(uu.`user_id`=srum.`user_id`)\n\t\t\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "stats_referer_urls` sru ON (sru.`referer_url_id`=srum.`referer_url_id`)\n\t\t\t\t{$whereSql} ORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']);
if ($rs = $this->mDb->query($query, $bindVars, -1, $pListHash['offset'], $gBitSystem->isLive() ? 3600 : BIT_QUERY_DEFAULT)) {
while ($row = $rs->fetchRow()) {
$key = $row['hash_key'];
if ($hashKey == 'host') {
$key = 'none';
if (!empty($row['referer_url'])) {
$parseUrl = parse_url($row['referer_url']);
if (!empty($parseUrl['query'])) {
parse_str($parseUrl['query'], $params);
if (!empty($params['adurl'])) {
parse_str($params['adurl'], $params);
}
}
$key = $parseUrl['host'];
}
}
$ret[$key][$row['user_id']] = $row;
}
}
LibertyContent::postGetList($pListHash);
if ($hashKey == 'host') {
uasort($ret, array($this, 'sortRefererHash'));
}
return $ret;
}
/**
* getList
*
* @param array $pListHash
* @access public
* @return TRUE on success, FALSE on failure - mErrors will contain reason for failure
*/
function getList(&$pListHash)
{
global $gLibertySystem;
if (empty($pListHash['sort_mode'])) {
$pListHash['sort_mode'] = 'tagpos_asc';
}
LibertyBase::prepGetList($pListHash);
$bindvars = array();
if (!empty($pListHash['find'])) {
$findesc = '%' . $pListHash['find'] . '%';
$mid = " WHERE (`tagname` LIKE ?)";
$bindvars[] = $findesc;
if (!empty($pListHash['format_guid'])) {
$mid = " AND `format_guid`=?";
$bindvars[] = $pListHash['format_guid'];
}
} elseif (!empty($pListHash['format_guid'])) {
$mid = " WHERE `format_guid`=?";
$bindvars[] = $pListHash['format_guid'];
} else {
$mid = '';
}
$query = "SELECT * FROM `" . BIT_DB_PREFIX . "quicktags` {$mid} ORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']);
$result = $this->mDb->query($query, $bindvars, $pListHash['max_records'], $pListHash['offset']);
$tmp = array();
while ($res = $result->fetchRow()) {
$res['iconpath'] = $res['tagicon'];
$tmp[] = $res;
}
$ret = array();
foreach ($gLibertySystem->mPlugins as $plugin) {
if ($plugin['plugin_type'] == 'format') {
foreach ($tmp as $qt) {
if ($qt['format_guid'] == $plugin['plugin_guid']) {
$ret[$plugin['plugin_guid']][] = $qt;
}
}
}
}
$pListHash["cant"] = $this->mDb->getOne("SELECT COUNT(*) FROM `" . BIT_DB_PREFIX . "quicktags` {$mid}", $bindvars);
LibertyContent::postGetList($pListHash);
return $ret;
}
/**
* get list of all recommended content
*
* @param $pListHash contains array of items used to limit search results
* @param $pListHash[sort_mode] column and orientation by which search results are sorted
* @param $pListHash[find] search for a pigeonhole title - case insensitive
* @param $pListHash[max_records] maximum number of rows to return
* @param $pListHash[offset] number of results data is offset by
* @access public
* @return array of recommended content
**/
function getList(&$pListHash)
{
global $gBitSystem, $gBitUser, $gLibertySystem;
$ret = $bindVars = array();
$where = $join = $select = '';
// set custom sorting before we call prepGetList()
if (!empty($pListHash['sort_mode'])) {
$order = " ORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']) . " ";
} else {
// set a default sort_mode
$order = " ORDER BY rcm.`qualified_time` DESC";
}
LibertyContent::prepGetList($pListHash);
if (!empty($pListHash['user_id'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " rcmh.`user_id`=? ";
$bindVars[] = $pListHash['user_id'];
$select .= ", rcmh.`recommending` AS `user_recommending`";
$join .= " LEFT JOIN `" . BIT_DB_PREFIX . "recommends` rcmh ON( rcm.`content_id` = rcmh.`content_id` AND rcmh.`recommending` != 0) ";
$order = " ORDER BY rcmh.`recommending` DESC";
}
if (!empty($pListHash['timeout'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " rcm.`qualified_time` >= ? ";
$bindVars[] = $pListHash['timeout'];
}
if (!empty($pListHash['recommends'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " rcm.recommending >= ? ";
$bindVars[] = $pListHash['recommends'];
}
if (!empty($pListHash['find'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " UPPER( lc.`title` ) LIKE ? ";
$bindVars[] = '%' . strtoupper($pListHash['find']) . '%';
}
if (!empty($pListHash['content_type'])) {
if (is_array($pListHash['content_type'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " lc.`content_type_guid` IN( " . implode(',', array_fill(0, count($pListHash['content_type']), '?')) . " )";
$bindVars = array_merge($bindVars, $pListHash['content_type_guid']);
} else {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " lc.`content_type_guid` = ? ";
$bindVars[] = $pListHash['content_type'];
}
}
$query = "\n\t\t\tSELECT rcm.*, lch.`hits`, lch.`last_hit`, lc.`event_time`, lc.`title`,\n\t\t\tlc.`last_modified`, lc.`content_type_guid`, lc.`ip`, lc.`created`, uu.`email`, uu.`login`, uu.`real_name` {$select}\n\t\t\tFROM `" . BIT_DB_PREFIX . "recommends_sum` rcm\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = rcm.`content_id` )\n\t\t\t\tINNER JOIN\t\t`" . BIT_DB_PREFIX . "users_users`\t\t\t uu ON ( uu.`user_id`\t\t\t = lc.`user_id` )\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON ( lc.`content_id` = lch.`content_id` )\n\t\t\t{$join} {$where} {$order}";
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
while ($aux = $result->fetchRow()) {
$type =& $gLibertySystem->mContentTypes[$aux['content_type_guid']];
if (empty($type['content_object'])) {
include_once $gBitSystem->mPackages[$type['handler_package']]['path'] . $type['handler_file'];
$type['content_object'] = new $type['handler_class']();
}
if (!empty($gBitSystem->mPackages[$type['handler_package']])) {
$aux['display_link'] = $type['content_object']->getDisplayLink($aux['title'], $aux);
$aux['title'] = $type['content_object']->getTitleFromHash($aux);
$aux['display_url'] = $type['content_object']->getDisplayUrlFromHash($aux);
}
$ret[] = $aux;
}
$query = "\n\t\t\tSELECT COUNT( rcm.`content_id` )\n\t\t\tFROM `" . BIT_DB_PREFIX . "recommends_sum` rcm\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = rcm.`content_id` )\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON ( lc.`content_id` = lch.`content_id` )\n\t\t\t{$join} {$where}";
$pListHash['cant'] = $this->mDb->getOne($query, $bindVars);
LibertyContent::postGetList($pListHash);
return $ret;
}
function getList(&$pListHash)
{
global $gBitUser, $gBitSystem;
$this->prepGetList($pListHash);
$selectSql = '';
$joinSql = '';
$whereSql = '';
$bindVars = array();
array_push($bindVars, $this->mContentTypeGuid);
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars, NULL, $pListHash);
if (@$this->verifyId($pListHash['blog_id'])) {
$selectSql .= ', bpm.crosspost_note';
array_push($bindVars, (int) $pListHash['blog_id']);
$joinSql .= " LEFT OUTER JOIN `" . BIT_DB_PREFIX . "blogs_posts_map` bpm ON ( bpm.`post_content_id` = bp.`content_id` ) ";
$joinSql .= " LEFT OUTER JOIN `" . BIT_DB_PREFIX . "blogs` b ON ( bpm.`blog_content_id`=b.`content_id` ) ";
// " ON ( b.`content_id` = bpm.`blog_content_id` AND bp.`content_id` = bpm.`post_content_id` )";
$whereSql .= ' AND b.`blog_id` = ? ';
$pListHash['sort_mode'] = 'publish_date_desc';
}
if (@$this->verifyId($pListHash['post_id_gt'])) {
array_push($bindVars, (int) $pListHash['post_id_gt']);
$whereSql .= ' AND bp.`post_id` > ? ';
}
if (@$this->verifyId($pListHash['post_id_lt'])) {
array_push($bindVars, (int) $pListHash['post_id_lt']);
$whereSql .= ' AND bp.`post_id` < ? ';
}
if (@$this->verifyId($pListHash['user_id'])) {
array_push($bindVars, (int) $pListHash['user_id']);
$whereSql .= ' AND lc.`user_id` = ? ';
}
$this->getServicesSql('content_user_collection_function', $selectSql, $joinSql, $whereSql, $bindVars, NULL, $pListHash);
// map user to login in case we used one instead of the other
if (!empty($pListHash['user'])) {
$pListHash['login'] = $pListHash['user'];
}
if (!empty($pListHash['login'])) {
array_push($bindVars, $pListHash['login']);
$whereSql .= ' AND uu.`login` = ? ';
}
if ($pListHash['find']) {
$findesc = '%' . strtoupper($pListHash['find']) . '%';
$whereSql .= "AND (UPPER(lc.`data`) like ?) ";
$bindVars[] = $findesc;
}
if (!empty($pListHash['date']) && is_numeric($pListHash['date'])) {
$whereSql .= " AND lc.`created`<=? ";
$bindVars[] = $pListHash['date'];
}
if (!empty($pListHash['date_start']) && is_numeric($pListHash['date_start'])) {
$whereSql .= " AND lc.`created`>=? ";
$bindVars[] = $pListHash['date_start'];
}
if (!empty($pListHash['date_end']) && is_numeric($pListHash['date_end'])) {
$whereSql .= " AND lc.`created`<=? ";
$bindVars[] = $pListHash['date_end'];
}
if (!empty($pListHash['content_perm_name'])) {
$this->getContentListPermissionsSql($pListHash['content_perm_name'], $selectSql, $joinSql, $whereSql, $bindVars);
}
/* Check if the post wants to be viewed before / after respective dates
* Note: expiring posts are determined by the expired date being greater than the publish date
*/
static::getDateRestrictions($pListHash, $whereSql, $bindVars);
/* sort_mode is never empty due to call to prepGetList above
* I think this will have to be perminently removed and default
* set before passing the list hash in if a different default is
* desired from that in prepGetList. -wjames5
*/
/*
if( empty( $pListHash['sort_mode'] ) ) {
$pListHash['sort_mode'] = 'publish_date_desc';
$sortModePrefix = 'bp';
//$pListHash['sort_mode'] = 'created_desc';
} else {
*/
$sortModePrefix = '';
if (!empty($pListHash['sort_mode']) && !strpos($pListHash['sort_mode'], '.')) {
switch ($pListHash['sort_mode']) {
case 'publish_date_asc':
case 'publish_date_desc':
case 'post_id_desc':
case 'post_id_asc':
$sortModePrefix = 'bp.';
break;
case 'date_added_desc':
$sortModePrefix = 'bpm.';
break;
case 'hits_asc':
case 'hits_desc':
$sortModePrefix = 'lch.';
break;
case 'sort_date_asc':
case 'sort_date_desc':
break;
case 'real_name_asc':
case 'real_name_desc':
$sortModePrefix = 'uu.';
break;
// these technicall are not correct, however, we do not double join on users_users, so we sort by creator real_name
// these technicall are not correct, however, we do not double join on users_users, so we sort by creator real_name
case 'creator_real_name_asc':
case 'modifier_real_name_asc':
$sortModePrefix = 'uu.';
$pListHash['sort_mode'] = 'real_name_asc';
break;
case 'registration_date_desc':
$sortModePrefix = 'uu.';
$pListHash['sort_mode'] = 'registration_date_desc';
break;
case 'creator_real_name_desc':
case 'modifier_real_name_desc':
$sortModePrefix = 'uu.';
$pListHash['sort_mode'] = 'real_name_desc';
break;
default:
$sortModePrefix = 'lc.';
break;
}
}
$secondarySortMode = $pListHash['sort_mode'] != 'last_modified_desc' ? ', last_modified DESC' : '';
$sort_mode = $sortModePrefix . $this->mDb->convertSortmode($pListHash['sort_mode']) . $secondarySortMode;
$query = "\n\t\t\tSELECT\n\t\t\t\tbp.`post_id`, bp.`publish_date`, bp.`expire_date`, bp.`trackbacks_to`, bp.`trackbacks_from`,\n\t\t\t\tlc.*, lch.`hits`, lcds.`data` AS `summary`, COALESCE( bp.`publish_date`, lc.`last_modified` ) AS sort_date,\n\t\t\t\tuu.`email`, uu.`login`, uu.`real_name`,\n\t\t\t\t\tlfa.`file_name` as `avatar_file_name`, lfa.`mime_type` AS `avatar_mime_type`, laa.`attachment_id` AS `avatar_attachment_id`,\n\t\t\t\t\tlfp.`file_name` AS `image_file_name`, lfp.`mime_type` AS `image_mime_type`, lap.`attachment_id` AS `image_attachment_id`\n\t\t\tFROM `" . BIT_DB_PREFIX . "blog_posts` bp\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON lc.`content_id` = bp.`content_id`\n\t\t\t\tINNER JOIN\t\t`" . BIT_DB_PREFIX . "users_users`\t\t\t uu ON uu.`user_id`\t\t\t = lc.`user_id`\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON lc.`content_id` = lch.`content_id`\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_data` lcds ON (lc.`content_id` = lcds.`content_id` AND lcds.`data_type`='summary')\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_attachments`\tlaa ON (uu.`user_id` = laa.`user_id` AND laa.`attachment_id` = uu.`avatar_attachment_id`)\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_files`\t lfa ON lfa.`file_id`\t\t = laa.`foreign_id`\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_attachments` lap ON lap.`content_id` = lc.`content_id` AND lap.`is_primary` = 'y'\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_files` lfp ON lfp.`file_id` = lap.`foreign_id`\n\t\t\t\t{$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}\n\t\t\tORDER BY {$sort_mode}";
# Get count of total number of items available
$query_cant = "\n\t\t\tSELECT COUNT( * )\n\t\t\tFROM `" . BIT_DB_PREFIX . "blog_posts` bp\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON lc.`content_id` = bp.`content_id`\n\t\t\t\tINNER JOIN\t\t`" . BIT_DB_PREFIX . "users_users`\t\t\t uu ON uu.`user_id`\t\t\t = lc.`user_id`\n\t\t\t\t{$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql} ";
$cant = $this->mDb->getOne($query_cant, $bindVars);
$pListHash["cant"] = $cant;
# Check for offset out of range
if ($pListHash['offset'] < 0) {
$pListHash['offset'] = 0;
} elseif ($pListHash['offset'] > $pListHash["cant"]) {
$lastPageNumber = ceil($pListHash["cant"] / $pListHash['max_records']) - 1;
$pListHash['offset'] = $pListHash['max_records'] * $lastPageNumber;
}
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
$ret = array();
$comment = new LibertyComment();
while ($res = $result->fetchRow()) {
$res['no_fatal'] = TRUE;
$accessError = $this->invokeServices('content_verify_access', $res, FALSE);
if (empty($accessError)) {
foreach (array('avatar', 'image') as $img) {
$res[$img] = liberty_fetch_thumbnails(array('source_file' => liberty_mime_get_source_file(array('user_id' => $res['user_id'], 'package' => liberty_mime_get_storage_sub_dir_name(array('mime_type' => $res[$img . '_mime_type'], 'name' => $res[$img . '_file_name'])), 'file_name' => basename($res[$img . '_file_name']), 'sub_dir' => $res[$img . '_attachment_id']))));
}
$res['thumbnail_url'] = BitBlogPost::getImageThumbnails($res);
$res['num_comments'] = $comment->getNumComments($res['content_id']);
$res['post_url'] = BitBlogPost::getDisplayUrlFromHash($res);
$res['display_url'] = $res['post_url'];
$res['display_link'] = $this->getDisplayLink($res['title'], $res);
$res['blogs'] = $this->getBlogMemberships($res['content_id']);
// trackbacks
if ($res['trackbacks_from'] != null) {
$res['trackbacks_from'] = unserialize($res['trackbacks_from']);
}
if (!is_array($res['trackbacks_from'])) {
$res['trackbacks_from'] = array();
}
$res['trackbacks_from_count'] = count(array_keys($res['trackbacks_from']));
if ($res['trackbacks_to'] != null) {
$res['trackbacks_to'] = unserialize($res['trackbacks_to']);
}
if ($res['user_id'] == $gBitUser->mUserId) {
$res['ownsblog'] = 'y';
} else {
$res['ownsblog'] = 'n';
}
$res['trackbacks_to_count'] = count($res['trackbacks_to']);
$res['pages'] = $this->getNumberOfPages($res['data']);
// deal with the parsing
$parseHash['format_guid'] = $res['format_guid'];
$parseHash['content_id'] = $res['content_id'];
$parseHash['user_id'] = $res['user_id'];
// support for ...split... and auto split
if (!empty($pListHash['full_data'])) {
$parseHash['data'] = $res['data'];
$res['parsed'] = $this->parseData($parseHash);
} else {
$parseHash['data'] = $res['data'];
$parseHash['no_cache'] = TRUE;
$splitArray = $this->parseSplit($parseHash, $gBitSystem->getConfig('blog_posts_description_length', 500));
$res = array_merge($res, $splitArray);
}
if (!empty($this->mInfo['summary'])) {
$res['summary'] = $parseHash['data'] = $this->mInfo['summary'];
$parseHash['no_cache'] = TRUE;
$res['parsed_summary'] = $this->parsedData($parseHash);
}
if (!empty($res['crosspost_note'])) {
$res['crosspost_note_raw'] = $parseHash['data'] = $res['crosspost_note'];
$parseHash['no_cache'] = TRUE;
$res['crosspost_note'] = $this->parseData($parseHash);
}
$ret[] = $res;
} elseif (!empty($accessError)) {
if (!empty($accessError['access_control'])) {
$res['post_url'] = BitBlogPost::getDisplayUrlFromHash($res['content_id'], $res);
$res['display_url'] = $res['post_url'];
/* this needs to be part of loop that gets all blogs post is in
$res['blog_url'] = BitBlog::getDisplayUrlFromHash( $res['blog_content_id'] );
*/
$res["parsed_data"] = $accessError['access_control'];
$ret[] = $res;
}
} else {
}
}
LibertyContent::postGetList($pListHash);
return $ret;
}
function getList(&$pListHash)
{
global $gBitUser, $gBitSystem;
$this->prepGetList($pListHash);
$ret = NULL;
$bindVars = array();
$selectSql = '';
$joinSql = '';
$whereSql = '';
array_push($bindVars, $this->mContentTypeGuid);
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
if (@$this->verifyId($pListHash['gmap_id']) || isset($pListHash['set_id'])) {
$selectSql .= ", gpk.*, gps.`set_id`, gps.`style_id`, gps.`polylinestyle_id` ";
$joinSql .= " INNER JOIN `" . BIT_DB_PREFIX . "gmaps_polygon_keychain` gpk ON (gp.`polygon_id` = gpk.`polygon_id`) ";
$joinSql .= " INNER JOIN `" . BIT_DB_PREFIX . "gmaps_polygon_sets` gps ON (gpk.`set_id` = gps.`set_id`) ";
}
if (isset($pListHash['set_id'])) {
if (!is_array($pListHash['set_id']) && is_numeric($pListHash['set_id'])) {
$sets = array($pListHash['set_id']);
} elseif (is_array($pListHash['set_id'])) {
$sets = $pListHash['set_id'];
}
$hasOne = FALSE;
foreach ($sets as $value) {
if (@$this->verifyId($value)) {
if ($hasOne != TRUE) {
$whereSql .= " AND ( gpk.`set_id` = ? ";
$hasOne = TRUE;
} else {
$whereSql .= " OR gpk.`set_id` = ? ";
}
array_push($bindVars, (int) $value);
}
}
if ($hasOne == TRUE) {
$whereSql .= " ) ";
}
}
if (@$this->verifyId($pListHash['gmap_id'])) {
$selectSql .= ", gsk.* ";
$joinSql .= " INNER JOIN `" . BIT_DB_PREFIX . "gmaps_sets_keychain` gsk ON( gps.`set_id` = gsk.`set_id`) ";
$whereSql .= " AND gsk.`set_type` = 'polygons' AND gsk.`gmap_id` = ? ";
array_push($bindVars, (int) $pListHash['gmap_id']);
}
switch ($pListHash['sort_mode']) {
case 'pos_desc':
case 'pos_asc':
$sortModePrefix = 'gpk.';
break;
default:
$sortModePrefix = 'lc.';
break;
}
$secondarySortMode = $pListHash['sort_mode'] != 'title_asc' ? ', title ASC' : '';
$sort_mode = $sortModePrefix . $this->mDb->convertSortmode($pListHash['sort_mode']) . $secondarySortMode;
$query = "SELECT lc.*, gp.*, \n\t\t\t\t uue.`login` AS modifier_user, uue.`real_name` AS modifier_real_name,\n\t\t\t\t uuc.`login` AS creator_user, uuc.`real_name` AS creator_real_name {$selectSql}\n\t\t\t\t FROM `" . BIT_DB_PREFIX . "gmaps_polygons` gp \n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( gp.`content_id`=lc.`content_id` ) {$joinSql}\n\t\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "users_users` uue ON (uue.`user_id` = lc.`modifier_user_id`)\n\t\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "users_users` uuc ON (uuc.`user_id` = lc.`user_id`)\n\t\t\t\t WHERE lc.`content_type_guid` = ? {$whereSql}\n\t\t\t\t ORDER BY {$sort_mode}";
$query_cant = "\n\t\t\tSELECT COUNT( * )\n\t\t FROM `" . BIT_DB_PREFIX . "gmaps_polygons` gp \n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON lc.`content_id` = gp.`content_id`\n\t\t\t\tINNER JOIN\t\t`" . BIT_DB_PREFIX . "users_users`\t\t\t uu ON uu.`user_id`\t\t\t = lc.`user_id`\n\t\t\t\t{$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql} ";
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
$cant = $this->mDb->getOne($query_cant, $bindVars);
$ret = array();
while ($res = $result->fetchRow()) {
$res['display_url'] = $this->getDisplayUrlFromHash($res);
$ret[] = $res;
}
$pListHash["data"] = $ret;
$pListHash["cant"] = $cant;
LibertyContent::postGetList($pListHash);
return $pListHash;
}
/**
* get list of all rated content
*
* @param $pListHash contains array of items used to limit search results
* @param $pListHash[sort_mode] column and orientation by which search results are sorted
* @param $pListHash[find] search for a pigeonhole title - case insensitive
* @param $pListHash[max_records] maximum number of rows to return
* @param $pListHash[offset] number of results data is offset by
* @access public
* @return array of rated content
**/
function getList(&$pListHash)
{
global $gBitSystem, $gBitUser, $gLibertySystem;
$ret = $bindVars = array();
$where = $join = $select = '';
// set custom sorting before we call prepGetList()
if (!empty($pListHash['sort_mode'])) {
$order = " ORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']) . " ";
} else {
// set a default sort_mode
$order = " ORDER BY sts.`rating` DESC";
}
LibertyBase::prepGetList($pListHash);
if (!empty($pListHash['user_id'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " sth.`user_id`=? ";
$bindVars[] = $pListHash['user_id'];
$select .= ", sth.`rating` AS `user_rating`";
$join .= " LEFT OUTER JOIN `" . BIT_DB_PREFIX . "stars_history` sth ON( sts.`content_id` = sth.`content_id` ) ";
$order = " ORDER BY sth.`rating` DESC";
}
if (!empty($pListHash['find'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " UPPER( lc.`title` ) LIKE ? ";
$bindVars[] = '%' . strtoupper($pListHash['find']) . '%';
}
$query = "\n\t\t\tSELECT sts.*, lch.`hits`, lch.`last_hit`, lc.`event_time`, lc.`title`,\n\t\t\tlc.`last_modified`, lc.`content_type_guid`, lc.`ip`, lc.`created` {$select}\n\t\t\tFROM `" . BIT_DB_PREFIX . "stars` sts\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = sts.`content_id` )\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON ( lc.`content_id` = lch.`content_id` )\n\t\t\t{$join} {$where} {$order}";
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
$stars = $gBitSystem->getConfig('stars_used_in_display', 5);
$pixels = $stars * $gBitSystem->getConfig('stars_icon_width', 22);
while ($aux = $result->fetchRow()) {
$type =& $gLibertySystem->mContentTypes[$aux['content_type_guid']];
if (empty($type['content_object'])) {
include_once $gBitSystem->mPackages[$type['handler_package']]['path'] . $type['handler_file'];
$type['content_object'] = new $type['handler_class']();
}
if (!empty($gBitSystem->mPackages[$type['handler_package']])) {
$aux['display_link'] = $type['content_object']->getDisplayLink($aux['title'], $aux);
$aux['title'] = $type['content_object']->getTitleFromHash($aux);
$aux['display_url'] = $type['content_object']->getDisplayUrl($aux);
$aux['stars_pixels'] = $aux['rating'] * $pixels / 100;
}
$ret[] = $aux;
}
$query = "\n\t\t\tSELECT COUNT( sts.`content_id` )\n\t\t\tFROM `" . BIT_DB_PREFIX . "stars` sts\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = sts.`content_id` )\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON ( lc.`content_id` = lch.`content_id` )\n\t\t\t{$join} {$where}";
$pListHash['cant'] = $this->mDb->getOne($query, $bindVars);
LibertyContent::postGetList($pListHash);
return $ret;
}
/**
* getIconList
*
* @param array $pListHash
* @access public
* @return array of icons
*/
function getIconList(&$pListHash)
{
$whereSql = "";
$bindVars = $ret = array();
if (empty($pListHash['sort_mode'])) {
$pListHash['sort_mode'] = array('gmit.`theme_title_asc`', 'gmis.`name_asc`');
}
$this->prepGetList($pListHash);
if (!empty($pListHash['theme_title'])) {
$pListHash['theme_id'] = $this->getIconThemeId($pListHash['theme_title']);
}
if (@BitBase::verifyId($pListHash['theme_id'])) {
$whereSql = " WHERE gmis.`theme_id` = ? ";
$bindVars[] = $pListHash['theme_id'];
}
$sql = "SELECT gmis.`icon_id` AS `key`, gmis.*, gmit.`theme_title`\n\t\t\tFROM `" . BIT_DB_PREFIX . "gmaps_icon_styles` gmis\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "gmaps_icon_themes` gmit ON( gmit.`theme_id` = gmis.`theme_id` )\n\t\t\t{$whereSql}\n\t\t\tORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']);
$result = $this->mDb->query($sql, $bindVars, $pListHash['max_records'], $pListHash['offset']);
while ($aux = $result->fetchRow()) {
// convert relative path to valid url
$aux['image'] = BIT_ROOT_URL . str_replace('//', '/', str_replace('+', '%20', str_replace('%2F', '/', urlencode($aux['image']))));
$ret[] = $aux;
}
$pListHash['cant'] = $this->mDb->getOne("SELECT COUNT( `icon_id` ) FROM `" . BIT_DB_PREFIX . "gmaps_icon_styles` gmis {$whereSql}", $bindVars);
LibertyContent::postGetList($pListHash);
return $ret;
}
/**
* getPropertyList( &$pParamHash );
* Get list of property records
*/
function getPropertyList(&$pParamHash)
{
global $gBitSystem, $gBitUser;
if (empty($pParamHash['sort_mode'])) {
if (empty($_REQUEST["sort_mode"])) {
$pParamHash['sort_mode'] = 'title_asc';
} else {
$pParamHash['sort_mode'] = $_REQUEST['sort_mode'];
}
}
LibertyContent::prepGetList($pParamHash);
$findSql = '';
$selectSql = '';
$joinSql = '';
$whereSql = '';
$bindVars = array();
// this will set $find, $sort_mode, $max_records and $offset
extract($pParamHash);
$where = ' WHERE ';
if (isset($find_org) and is_string($find_org) and $find_org != '') {
$findSql .= $where . "UPPER( c.`organisation` ) like ? ";
$bindVars[] = '%' . strtoupper($find_org) . '%';
$where = ' AND ';
}
if (isset($find_xao) and is_string($find_xao) and $find_xao != '') {
$findSql .= $where . " ( UPPER( d.`sao` ) like ? OR UPPER( d.`pao` ) like ? ) ";
$bindVars[] = '%' . strtoupper($find_xao) . '%';
$bindVars[] = '%' . strtoupper($find_xao) . '%';
$where = ' AND ';
}
if (isset($find_street) and is_string($find_street) and $find_street != '') {
$findSql .= $where . "UPPER( d.`add2` ) like ? ";
$bindVars[] = '%' . strtoupper($find_street) . '%';
$where = ' AND ';
}
if (isset($find_postcode) and is_string($find_postcode) and $find_postcode != '') {
$findSql .= $where . "UPPER( `d.postcode` ) LIKE ? ";
$bindVars[] = strtoupper($find_postcode) . '%';
$where = ' AND ';
}
// If no selected filter then reduce result set artificially - use street starting A
if ($where == ' WHERE ') {
$findSql .= $where . "UPPER( d.`add2` ) like ? ";
$bindVars[] = 'A%';
$pParamHash['find_street'] = 'A';
}
$query = "SELECT CASE WHEN c.uprn = 0 THEN 'Private' ELSE 'Business' END AS p_type, p.*, d.add2, d.add3 AS title, d.postcode, c.* {$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "property` p\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "postcode` d ON d.`postcode` = p.`postcode`\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "contact` c ON c.`content_id` = p.`owner_id` {$findSql}\n\t\t\t{$joinSql} {$whereSql} ORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "SELECT COUNT( * )\n\t\t\tFROM `" . BIT_DB_PREFIX . "property` p\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "postcode` d ON d.`postcode` = p.`postcode`\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "contact` c ON c.`content_id` = p.`owner_id` {$findSql}\n\t\t\t{$joinSql} {$whereSql}";
$result = $this->mDb->query($query, $bindVars, $max_records, $offset);
$ret = array();
while ($res = $result->fetchRow()) {
if (!empty($parse_split)) {
$res = array_merge($this->parseSplit($res), $res);
}
/* $os1 = new OSRef($res['x_coordinate'], $res['y_coordinate']);
$ll1 = $os1->toLatLng();
$res['prop_lat'] = $ll1->lat;
$res['prop_lng'] = $ll1->lng;
$res['display_usrn'] = $this->getUsrnEntryUrl( $res['usrn'] );
$res['display_uprn'] = $this->getUprnEntryUrl( $res['uprn'] );
*/
$ret[] = $res;
}
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
LibertyContent::postGetList($pParamHash);
return $ret;
}
/**
* This function generates a list of records from the liberty_content database for use in a list page
**/
function getList(&$pParamHash)
{
global $gBitSystem, $gBitUser;
if (empty($pParamHash['sort_mode'])) {
if (empty($_REQUEST["sort_mode"])) {
$pParamHash['sort_mode'] = 'event_time_asc';
} else {
$pParamHash['sort_mode'] = $_REQUEST['sort_mode'];
}
}
// Hack until sort_mode can be filtered to acceptable values
$pParamHash['sort_mode'] = 'event_time_asc';
LibertyContent::prepGetList($pParamHash);
$selectSql = '';
$joinSql = '';
$whereSql = '';
$bindVars = array();
array_push($bindVars, $this->mContentTypeGuid);
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
// this will set $find, $sort_mode, $max_records and $offset
extract($pParamHash);
if (is_array($find)) {
// you can use an array of pages
$whereSql .= " AND lc.`title` IN( " . implode(',', array_fill(0, count($find), '?')) . " )";
$bindVars = array_merge($bindVars, $find);
} else {
if (is_string($find)) {
// or a string
$whereSql .= " AND UPPER( lc.`title` )like ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
} else {
if (@$this->verifyId($pUserId)) {
// or a string
$whereSql .= " AND lc.`creator_user_id` = ? ";
$bindVars[] = array($pUserId);
}
}
}
if (!empty($event_before)) {
$whereSql .= " AND lc.`event_time` <= ? ";
$bindVars[] = $event_before;
}
if (!empty($event_after)) {
$whereSql .= " AND lc.`event_time` > ? ";
$bindVars[] = $event_after;
}
$query = "SELECT e.*, et.`name` as `type_name`, lc.`title`, lc.`data`, lc.`modifier_user_id` AS `modifier_user_id`, lc.`user_id` AS `creator_user_id`,\n\t\t\tlc.`last_modified` AS `last_modified`, lc.`event_time` AS `event_time`, lc.`format_guid`, lcps.`pref_value` AS `show_start_time`, lcpe.`pref_value` AS `show_end_time`,\n\t\t\tla.`attachment_id` AS primary_attachment_id\n\t\t\t{$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "events` e\n\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "events_types` et ON (e.`type_id` = et.`type_id`)\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = e.`content_id` )\n\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "liberty_content_prefs` lcps ON (lc.`content_id` = lcps.`content_id` AND lcps.`pref_name` = 'show_start_time')\n\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "liberty_attachments` la ON (lc.`content_id` = la.`content_id` AND la.`is_primary` = 'y')\n\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "liberty_content_prefs` lcpe ON (lc.`content_id` = lcpe.`content_id` AND lcpe.`pref_name` = 'show_end_time')\n\t\t\t{$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}\n\t\t\tORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "SELECT COUNT( * )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "events` e\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = e.`content_id` ) {$joinSql}\n\t\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}";
$result = $this->mDb->query($query, $bindVars, $max_records, $offset);
$ret = array();
while ($res = $result->fetchRow()) {
if (!empty($parse_split)) {
$res = array_merge($this->parseSplit($res), $res);
}
$res['display_url'] = $this->getDisplayUrl($res['events_id'], $res);
$res['primary_attachment'] = LibertyMime::loadAttachment($res['primary_attachment_id']);
$ret[] = $res;
}
$pParamHash["data"] = $ret;
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
LibertyContent::postGetList($pParamHash);
return $ret;
}
/**
* get list of all pigeonholes
* @param $pListHash contains array of items used to limit search results
* @param $pListHash[sort_mode] column and orientation by which search results are sorted
* @param $pListHash[find] search for a pigeonhole title - case insensitive
* @param $pListHash[max_records] maximum number of rows to return
* @param $pListHash[offset] number of results data is offset by
* @param $pListHash[title] pigeonhole name
* @param $pListHash[parent_id] pigeonhole parent_id, optional
* @param $pListHash[root_structure_id] only load the pigoenhole this root_structure_id is part of
* @param $pListHash[load_only_root] only load top most items
* @param $pListHash[parent_content_id] all the sons of the pigeonhole parent content_id , optional
* @param $pListHash[load_also_root] if parent_content_id is set load also the father, optionnal
* @return array of pigeonholes in 'data' and count of pigeonholes in 'cant'
* @access public
**/
function getList(&$pListHash)
{
global $gBitSystem, $gBitUser, $gBitDbType;
LibertyContent::prepGetList($pListHash);
$ret = $bindVars = array();
$where = $order = $join = $select = '';
if (@BitBase::verifyId($pListHash['root_structure_id'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " ls.`root_structure_id`=? ";
$bindVars[] = $pListHash['root_structure_id'];
}
if (!empty($pListHash['load_only_root'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " ls.`structure_id`=ls.`root_structure_id` ";
}
if (!empty($pListHash['find'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " UPPER( lc.`title` ) LIKE ? ";
$bindVars[] = '%' . strtoupper($pListHash['find']) . '%';
}
if (!empty($pListHash['title'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= ' lc.`title` = ?';
$bindVars[] = $pListHash['title'];
}
if ($gBitSystem->isFeatureActive('pigeonholes_allow_forbid_insertion') && !empty($pListHash['insertable'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= ' lcp.`pref_value` IS NULL OR lcp.`pref_value` != \'on\' ';
$join .= ' LEFT JOIN `' . BIT_DB_PREFIX . 'liberty_content_prefs` lcp ON (lc.`content_id` = lcp.`content_id` AND lcp.`pref_name` = \'no_insert\') ';
$select .= ' , lcp.`pref_value` AS no_insert ';
}
if (isset($pListHash['parent_id'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= ' ls.`parent_id` = ? ';
$bindVars[] = $pListHash['parent_id'];
}
if (!empty($pListHash['parent_content_id'])) {
$join .= 'INNER JOIN `' . BIT_DB_PREFIX . 'liberty_structures` lsf ON (ls.`parent_id` = lsf.`structure_id`';
if (!empty($pListHash['load_also_root'])) {
$join .= ' OR ls.`structure_id`= lsf.`structure_id`';
}
$join .= ')';
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= ' lsf.`content_id` = ? ';
$bindVars[] = $pListHash['parent_content_id'];
}
if (!empty($pListHash['sort_mode'])) {
$order .= " ORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']) . " ";
} else {
// default sort mode makes list look nice
$order .= " ORDER BY ls.`root_structure_id`, ls.`structure_id` ASC";
}
// only use subselect for old crappy mysql
if ($gBitDbType != 'mysql') {
$subselect = ", (\n\t\t\t\tSELECT COUNT( pm.`content_id` )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "pigeonhole_members` pm\n\t\t\t\tWHERE pm.`parent_id`=pig.`content_id`\n\t\t\t) AS members_count";
} else {
$subselect = "";
}
$query = "SELECT pig.*, ls.`root_structure_id`, ls.`parent_id`, lc.`title`, lc.`data`, lc.`user_id`, lc.`content_type_guid`, lc.`format_guid`,\n\t\t\tuue.`login` AS modifier_user, uue.`real_name` AS modifier_real_name,\n\t\t\tuuc.`login` AS creator_user, uuc.`real_name` AS creator_real_name {$select} {$subselect}\n\t\t\tFROM `" . BIT_DB_PREFIX . "pigeonholes` pig\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = pig.`content_id` )\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "users_users` uue ON ( uue.`user_id` = lc.`modifier_user_id` )\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "users_users` uuc ON ( uuc.`user_id` = lc.`user_id` )\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_structures` ls ON ( ls.`structure_id` = pig.`structure_id` )\n\t\t\t{$join} {$where} {$order}";
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
while ($aux = $result->fetchRow()) {
//$content_ids[] = $aux['content_id'];
$aux['user'] = $aux['creator_user'];
$aux['real_name'] = isset($aux['creator_real_name']) ? $aux['creator_real_name'] : $aux['creator_user'];
$aux['display_name'] = BitUser::getDisplayNameFromHash(FALSE, $aux);
$aux['editor'] = isset($aux['modifier_real_name']) ? $aux['modifier_real_name'] : $aux['modifier_user'];
$aux['display_link'] = Pigeonholes::getDisplayLink($aux['title'], $aux);
// get member count for mysql - haha
if ($gBitDbType == 'mysql') {
$aux['members_count'] = $this->mDb->getOne("SELECT COUNT( pm.`content_id` ) FROM `" . BIT_DB_PREFIX . "pigeonhole_members` pm WHERE pm.`parent_id`=?", array($aux['content_id']));
}
if (!empty($pListHash['parse_data']) && !empty($aux['data'])) {
$aux['parsed_data'] = $this->parseData($aux['data'], $aux['format_guid']);
}
if (!empty($pListHash['force_extras']) || !empty($pListHash['load_extras']) && $aux['structure_id'] == @$pListHash['structure_id']) {
$aux['path'] = $this->getPigeonholePath($aux['structure_id']);
$aux['display_path'] = Pigeonholes::getDisplayPath($aux['path']);
// Move all the members data into the right place
$memberListHash = array('content_id' => $aux['content_id'], 'content_type_guid' => !empty($pListHash['content_type_guid']) ? $pListHash['content_type_guid'] : NULL, 'max_records' => !empty($pListHash['members_max_records']) ? $pListHash['members_max_records'] : NULL, 'list_page' => !empty($pListHash['members_list_page']) ? $pListHash['members_list_page'] : NULL, 'sort_mode' => !empty($pListHash['members_sort_mode']) ? $pListHash['members_sort_mode'] : NULL, 'find' => !empty($pListHash['members_find']) ? $pListHash['members_find'] : NULL);
$aux['members'] = $this->getMemberList($memberListHash);
$aux['listInfo'] = $memberListHash['listInfo'];
//$aux['members_count'] = count( $aux['members'] );
if ($gBitSystem->getConfig('pigeonholes_list_style') == 'table') {
$this->alphabetiseMembers($aux['members']);
}
}
$ret[$aux['structure_id']] = $aux;
}
$query = "SELECT COUNT( lc.`title` )\n\t\t\tFROM `" . BIT_DB_PREFIX . "pigeonholes` pig\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = pig.`content_id` )\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "users_users` uue ON ( uue.`user_id` = lc.`modifier_user_id` )\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "users_users` uuc ON ( uuc.`user_id` = lc.`user_id` )\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_structures` ls ON ( ls.`structure_id` = pig.`structure_id` )\n\t\t\t{$join} {$where}";
$pListHash['cant'] = $this->mDb->getOne($query, $bindVars);
LibertyContent::postGetList($pListHash);
return $ret;
}
/**
* This function generates a list of records from the liberty_content database for use in a list page
* @param $pParamHash contains an array of conditions to sort by
* @return array of articles
* @access public
**/
public function getList(&$pParamHash)
{
global $gBitSystem, $gBitUser, $gLibertySystem;
if (empty($pParamHash['sort_mode'])) {
// no idea what this is supposed to do
//$pParamHash['sort_mode'] = $gBitSystem->isFeatureActive('articles_auto_approve') ? 'order_key_desc' : 'publish_date_desc';
$pParamHash['sort_mode'] = 'publish_date_desc';
}
LibertyContent::prepGetList($pParamHash);
$joinSql = '';
$selectSql = '';
$bindVars = array();
array_push($bindVars, $this->mContentTypeGuid);
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars, NULL, $pParamHash);
$find = $pParamHash['find'];
if (is_array($find)) {
// you can use an array of articles
$whereSql .= " AND lc.`title` IN( " . implode(',', array_fill(0, count($find), '?')) . " )";
$bindVars = array_merge($bindVars, $find);
} elseif (is_string($find)) {
// or a string
$whereSql .= " AND UPPER( lc.`title` ) LIKE ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
} elseif (@$this->verifyId($pParamHash['user_id'])) {
// or gate on a user
$whereSql .= " AND lc.`user_id` = ? ";
$bindVars[] = (int) $pParamHash['user_id'];
}
if (@$this->verifyId($pParamHash['status_id'])) {
$whereSql .= " AND a.`status_id` = ? ";
$bindVars[] = $pParamHash['status_id'];
}
if (@$this->verifyId($pParamHash['type_id'])) {
$whereSql .= " AND a.`article_type_id` = ? ";
$bindVars[] = (int) $pParamHash['type_id'];
}
// TODO: we need to check if the article wants to be viewed before / after respective dates
// someone better at SQL please get this working without an additional db call - xing
$now = $gBitSystem->getUTCTime();
if (!empty($pParamHash['show_future']) && !empty($pParamHash['show_expired']) && $gBitUser->hasPermission('p_articles_admin')) {
// this will show all articles at once - future, current and expired
} elseif (!empty($pParamHash['show_future']) && $gBitUser->hasPermission('p_articles_admin')) {
// hide expired articles
$whereSql .= " AND ( a.`expire_date` > ? OR atype.`show_post_expire` = ? ) ";
$bindVars[] = (int) $now;
$bindVars[] = 'y';
} elseif (!empty($pParamHash['show_expired']) && $gBitUser->hasPermission('p_articles_admin')) {
// hide future articles
$whereSql .= " AND ( a.`publish_date` < ? OR atype.`show_pre_publ` = ? ) ";
$bindVars[] = (int) $now;
$bindVars[] = 'y';
} elseif (!empty($pParamHash['get_future'])) {
// show only future
// if we're trying to view these articles, we better have the perms to do so
if (!$gBitUser->hasPermission('p_articles_admin')) {
return array();
}
$whereSql .= " AND a.`publish_date` > ?";
$bindVars[] = (int) $now;
} elseif (!empty($pParamHash['get_expired'])) {
// show only expired articles
// if we're trying to view these articles, we better have the perms to do so
if (!$gBitUser->hasPermission('p_articles_admin')) {
return array();
}
$whereSql .= " AND a.`expire_date` < ? ";
$bindVars[] = (int) $now;
} else {
// hide future and expired articles - this is the default behaviour
// we need all these AND and ORs to ensure that other conditions such as status_id are respected as well
$whereSql .= " AND (( a.`publish_date` > a.`expire_date` ) OR (( a.`publish_date` < ? OR atype.`show_pre_publ` = ? ) AND ( a.`expire_date` > ? OR atype.`show_post_expire` = ? ))) ";
$bindVars[] = (int) $now;
$bindVars[] = 'y';
$bindVars[] = (int) $now;
$bindVars[] = 'y';
}
if (@$this->verifyId($pParamHash['topic_id'])) {
$whereSql .= " AND a.`topic_id` = ? ";
$bindVars[] = (int) $pParamHash['topic_id'];
} elseif (!empty($pParamHash['topic'])) {
$whereSql .= " AND UPPER( atopic.`topic_name` ) = ? ";
$bindVars[] = strtoupper($pParamHash['topic']);
} else {
$whereSql .= " AND ( atopic.`active_topic` != 'n' OR atopic.`active_topic` IS NULL ) ";
//$whereSql .= " AND atopic.`active_topic` != 'n' ";
}
// Oracle is very particular about naming multiple columns, so need to explicity name them ORA-00918: column ambiguously defined
$query = "SELECT\n\t\t\t\ta.`article_id`, a.`description`, a.`author_name`, a.`publish_date`, a.`expire_date`, a.`rating`,\n\t\t\t\tatopic.`topic_id`, atopic.`topic_name`, atopic.`has_topic_image`, atopic.`active_topic`,\n\t\t\t\tastatus.`status_id`, astatus.`status_name`,\n\t\t\t\tlch.`hits`,\n\t\t\t\tatype.*, lc.*, la.`attachment_id` AS `primary_attachment_id`, lf.`file_name` AS `image_attachment_path` {$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "articles` a\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = a.`content_id` )\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "article_status` astatus ON( astatus.`status_id` = a.`status_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON( lc.`content_id` = lch.`content_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "article_topics` atopic ON( atopic.`topic_id` = a.`topic_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "article_types` atype ON( atype.`article_type_id` = a.`article_type_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_attachments` la ON( la.`content_id` = lc.`content_id` AND la.`is_primary` = 'y' )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_files` lf ON( lf.`file_id` = la.`foreign_id` )\n\t\t\t\t{$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}\n\t\t\tORDER BY " . $this->mDb->convertSortmode($pParamHash['sort_mode']);
$query_cant = "SELECT COUNT( * )FROM `" . BIT_DB_PREFIX . "articles` a\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON lc.`content_id` = a.`content_id`\n\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "article_topics` atopic ON atopic.`topic_id` = a.`topic_id` {$joinSql}\n\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "article_types` atype ON atype.`article_type_id` = a.`article_type_id`\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}";
$result = $this->mDb->query($query, $bindVars, $pParamHash['max_records'], $pParamHash['offset']);
$ret = array();
$comment = new LibertyComment();
while ($res = $result->fetchRow()) {
// get this stuff parsed
$res = array_merge($this->parseSplit($res, $gBitSystem->getConfig('articles_description_length', 500)), $res);
$res['thumbnail_url'] = static::getImageThumbnails($res);
$res['num_comments'] = $comment->getNumComments($res['content_id']);
$res['display_url'] = self::getDisplayUrlFromHash($res);
$res['display_link'] = $this->getDisplayLink($res['title'], $res);
// fetch the primary attachment that we can display the file on the front page if needed
$res['primary_attachment'] = LibertyMime::loadAttachment($res['primary_attachment_id']);
$ret[] = $res;
}
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
LibertyContent::postGetList($pParamHash);
return $ret;
}
/**
* get a list of available packages
*
* @param array $pListHash
* @access public
* @return TRUE on success, FALSE on failure - mErrors will contain reason for failure
*/
function getList(&$pListHash)
{
global $gBitSystem;
$ret = $bindVars = array();
$selectSql = $joinSql = $orderSql = $whereSql = '';
// disable pagination for now
$pListHash['max_records'] = -1;
if (empty($pListHash['sort_mode'])) {
$pListHash['sort_mode'] = 'release_date_desc';
}
if (!empty($pListHash['user_id'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= ' pkgp.`user_id`=? ';
$bindVars[] = $pListHash['user_id'];
}
LibertyContent::prepGetList($pListHash);
$query = "\n\t\t\tSELECT pkgp.*, (\n\t\t\t\tSELECT MAX( pkgv.`release_date` )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "packager_versions` pkgv\n\t\t\t\tWHERE pkgv.`package`=pkgp.`package`\n\t\t\t) AS release_date\n\t\t\tFROM `" . BIT_DB_PREFIX . "packager_packages` pkgp\n\t\t\t{$whereSql} ORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']);
$result = $this->mDb->query($query, $bindVars);
while ($aux = $result->fetchRow()) {
$aux['display_url'] = $this->getDisplayUrlFromHash($aux);
$aux['latest_version'] = $this->getLatestVersion($aux['package']);
$aux['installed_version'] = $this->getInstalledVersion($aux['package']);
$aux['is_cvs'] = $this->versionCompare($aux['latest_version'], $aux['installed_version']) === -1;
$aux['is_uptodate'] = $this->versionCompare($aux['latest_version'], $aux['installed_version']) === 0;
$aux['is_upgradable'] = $this->versionCompare($aux['latest_version'], $aux['installed_version']) === 1;
$ret[] = $aux;
}
$pListHash['cant'] = count($ret);
LibertyContent::postGetList($pListHash);
return $ret;
}
/**
* Returns list of an IRList entries
*
* @param integer
* @param integer
* @param integer
* @return string Text for the title description
*/
function getList(&$pParamHash)
{
global $gBitSystem, $gBitUser;
if (empty($pParamHash["sort_mode"])) {
$pParamHash["sort_mode"] = 'ir_id_desc';
}
LibertyContent::prepGetList($pParamHash);
// this will set $find, $sort_mode, $max_records and $offset
extract($pParamHash);
$joinSql = '';
$selectSql = '';
$whereSql = '';
$bindVars = array();
array_push($bindVars, $this->mContentTypeGuid);
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
if ($find) {
$findesc = '%' . strtoupper($find) . '%';
$whereSql = " AND (UPPER(b.`title`) like ? or UPPER(b.`description`) like ?) ";
$bindVars = array($bindVars, $findesc, $findesc);
}
if (isset($project) and $project != " ") {
$add_sql = "`project_name` = '" . $project . "'";
if ($status != "A") {
$whereSql .= " AND `status` = '" . $status . "'";
}
if ($priority != "A") {
$whereSql .= " AND `priority` = " . $priority;
}
if (!isset($version)) {
$version = '';
}
$pParamHash['listInfo']['ihash']['project'] = trim($project);
$pParamHash['listInfo']['ihash']['status'] = $status;
$pParamHash['listInfo']['ihash']['priority'] = $priority;
$pParamHash['listInfo']['ihash']['version'] = trim($version);
}
$query = "SELECT ir.*, lc.*, \r\n\t\t\t\tuue.`login` AS modifier_user, uue.`real_name` AS modifier_real_name,\r\n\t\t\t\tuuc.`login` AS creator_user, uuc.`real_name` AS creator_real_name,\r\n\t\t\t\tuux.`login` AS closed_user, uuc.`real_name` AS closed_real_name\r\n\t\t\t\t{$selectSql}\r\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "irlist_secondary` ir\r\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = ir.`content_id` ) {$joinSql}\r\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "users_users` uue ON (uue.`user_id` = lc.`modifier_user_id`)\r\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "users_users` uuc ON (uuc.`user_id` = lc.`user_id`)\r\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "users_users` uux ON (uux.`user_id` = ir.`closed_user_id`)\r\n\t\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}\r\n\t\t\t\tORDER BY " . $this->mDb->convert_sortmode($sort_mode);
$result = $this->mDb->query($query, $bindVars, $max_records, $offset);
$ret = array();
while ($res = $result->fetchRow()) {
$res['irlist_url'] = $this->getDisplayUrl($res['content_id']);
$ret[] = $res;
}
// Get total result count
$query_cant = "SELECT COUNT(ir.`ir_id`) FROM `" . BIT_DB_PREFIX . "irlist_secondary` ir\r\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = ir.`content_id` ) {$joinSql}\r\n\t\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}";
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
// add all pagination info to pParamHash
LibertyContent::postGetList($pParamHash);
return $ret;
}
/**
* This function generates a list of records from the liberty_content database for use in a list page
**/
function getList(&$pParamHash)
{
global $gBitSystem, $gBitUser;
// this makes sure parameters used later on are set
LibertyContent::prepGetList($pParamHash);
$selectSql = $joinSql = $whereSql = '';
$bindVars = array();
array_push($bindVars, $this->mContentTypeGuid);
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars, NULL, $pParamHash);
// this will set $find, $sort_mode, $max_records and $offset
extract($pParamHash);
if (isset($pParamHash['user_id'])) {
$joinSql .= " INNER JOIN `" . BIT_DB_PREFIX . "users_groups_map` ugm ON (g.`group_id` = ugm.`group_id`)";
$whereSql .= " AND ugm.`user_id` = ?";
$bindVars[] = $pParamHash['user_id'];
}
if (isset($pParamHash['mapped_content_id'])) {
$joinSql .= " INNER JOIN `" . BIT_DB_PREFIX . "groups_content_cnxn_map` gccm1 ON (g.`content_id` = gccm1.`group_content_id`)";
$whereSql .= " AND gccm1.`to_content_id` = ?";
$bindVars[] = $pParamHash['mapped_content_id'];
}
if (is_array($find)) {
// you can use an array of groups
$whereSql .= " AND lc.`title` IN( " . implode(',', array_fill(0, count($find), '?')) . " )";
$bindVars = array_merge($bindVars, $find);
} elseif (is_string($find)) {
// or a string
$whereSql .= " AND UPPER( lc.`title` )like ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
}
$query = "SELECT g.*, \n\t\t\tlc.`content_id`, \n\t\t\tlc.`title`, \n\t\t\tlc.`data`, \n\t\t\tlcds.`data` AS `summary`, \n\t\t\tlc.`content_type_guid`, \n\t\t\tlct.`content_name`, \n\t\t\tlct.`content_name_plural`, \n\t\t\tlc.`last_modified`, \n\t\t\tlc.`created`,\n\t\t\tlfp.file_name AS `image_attachment_path`, \n\t\t\tuue.`login` AS `modifier_user`,\n\t\t\tuue.`real_name` AS `modifier_real_name`,\n\t\t\tuue.`user_id` AS `modifier_user_id`,\n\t\t\tuuc.`login` AS `creator_user`,\n\t\t\tuuc.`real_name` AS `creator_real_name`,\n\t\t\tuuc.`user_id` AS `creator_user_id`,\n\t\t\tug.*, \n\t\t\tbrd.`board_id`\n\t\t\t{$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "groups` g \n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = g.`content_id` ) \n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content_types` lct ON (lc.`content_type_guid`=lct.`content_type_guid`)\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_groups` ug ON( ug.`group_id` = g.`group_id` ) \n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_users` uuc ON (lc.`user_id`=uuc.`user_id`)\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_users` uue ON (lc.`modifier_user_id`=uue.`user_id`)\n\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_data` lcds ON (lc.`content_id` = lcds.`content_id` AND lcds.`data_type`='summary')\n\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_attachments` la ON( la.`content_id` = lc.`content_id` AND la.`is_primary` = 'y' ) \n\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_files` lfp ON( lfp.`file_id` = la.`foreign_id` )\n\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "groups_content_cnxn_map` gccm ON ( lc.`content_id` = gccm.`group_content_id` )\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "boards` brd ON (gccm.`to_content_id` = brd.`content_id`)\n\t\t\t{$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}\n\t\t\tORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "select count(*)\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "groups` g \n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = g.`content_id` )\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_groups` ug ON( ug.`group_id` = g.`group_id` ) \n\t\t\t \t{$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}";
$result = $this->mDb->query($query, $bindVars, $max_records, $offset);
$ret = array();
while ($res = $result->fetchRow()) {
$res['display_url'] = $this->getDisplayUrl($res);
$res['num_members'] = $this->getMembersCount($res['group_id']);
$res['thumbnail_url'] = liberty_fetch_thumbnails(array("storage_path" => $res['image_attachment_path']));
$res['display_urls'] = $this->getDisplayUrls($res);
$ret[] = $res;
}
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
// add all pagination info to pParamHash
LibertyContent::postGetList($pParamHash);
return $ret;
}
/**
* Get list of all treasury galleries
*
* @param $pListHash contains array of items used to limit search results
* @param $pListHash[sort_mode] column and orientation by which search results are sorted
* @param $pListHash[find] search for a gallery title - case insensitive
* @param $pListHash[max_records] maximum number of rows to return
* @param $pListHash[offset] number of results data is offset by
* @param $pListHash[title] gallery name
* @param $pListHash[parent_id] gallery parent_id
* @param $pListHash[get_sub_tree] get the subtree to every gallery
* @access public
* @return List of galleries
**/
function getList(&$pListHash)
{
global $gBitDbType, $gBitSystem, $gBitUser;
LibertyContent::prepGetList($pListHash);
$ret = $bindVars = array();
$selectSql = $joinSql = $orderSql = $whereSql = '';
if (@BitBase::verifyId($pListHash['root_structure_id'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " ls.`root_structure_id`=? ";
$bindVars[] = $pListHash['root_structure_id'];
}
if (!empty($pListHash['get_sub_tree'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " ls.`structure_id`=ls.`root_structure_id` ";
}
if (!empty($pListHash['find'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " UPPER( lc.`title` ) LIKE ? ";
$bindVars[] = '%' . strtoupper($pListHash['find']) . '%';
}
if (isset($pListHash['parent_id'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= ' ls.`parent_id` = ? ';
$bindVars[] = $pListHash['parent_id'];
}
if (!empty($pListHash['sort_mode'])) {
$orderSql .= " ORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']) . " ";
} else {
// default sort mode makes list look nice
$orderSql .= " ORDER BY ls.`root_structure_id`, ls.`structure_id` ASC";
}
// update query with service sql
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
// get the number of files in this gallery
if ($gBitDbType != 'mysql' && $gBitDbType != 'mysqli') {
$subselect = ", (\n\t\t\t\tSELECT COUNT( trm.`item_content_id` )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "treasury_map` trm\n\t\t\t\tWHERE trm.`gallery_content_id`=trg.`content_id`\n\t\t\t) AS item_count";
} else {
$subselect = "";
}
// don't fetch trg.`is_private` for list, because it conflicts with gks.is_private
// and it's not used on list anyway
$query = "\n\t\t\tSELECT trg.`content_id`, trg.`structure_id`,\n\t\t\tls.`root_structure_id`, ls.`parent_id`,\n\t\t\tlc.`title`, lc.`data`, lc.`user_id`, lc.`content_type_guid`, lc.`created`, lc.`format_guid`, lch.`hits`,\n\t\t\tuue.`login` AS modifier_user, uue.`real_name` AS modifier_real_name,\n\t\t\tuuc.`login` AS creator_user, uuc.`real_name` AS creator_real_name {$subselect} {$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "treasury_gallery` trg\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = trg.`content_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON ( lc.`content_id` = lch.`content_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "users_users` uue ON ( uue.`user_id` = lc.`modifier_user_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "users_users` uuc ON ( uuc.`user_id` = lc.`user_id` )\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_structures` ls ON ( ls.`structure_id` = trg.`structure_id` )\n\t\t\t\t{$joinSql}\n\t\t\t{$whereSql}\n\t\t\t{$orderSql}";
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
if (!empty($pListHash['get_sub_tree'])) {
$struct = new LibertyStructure();
}
while ($aux = $result->fetchRow()) {
$hasUserPerm = TRUE;
// check to see if we have premissions to do someing specific with this gallery
if (!empty($pListHash['content_permission'])) {
$gal = new TreasuryGallery(NULL, $aux['content_id']);
if (!$gal->hasUserPermission($pListHash['content_permission'])) {
$hasUserPerm = FALSE;
}
}
if ($hasUserPerm) {
$content_ids[] = $aux['content_id'];
$aux['user'] = $aux['creator_user'];
$aux['real_name'] = isset($aux['creator_real_name']) ? $aux['creator_real_name'] : $aux['creator_user'];
$aux['editor'] = isset($aux['modifier_real_name']) ? $aux['modifier_real_name'] : $aux['modifier_user'];
$aux['display_name'] = BitUser::getDisplayNameFromHash(FALSE, $aux);
$aux['display_url'] = self::getDisplayUrlFromHash($aux);
$aux['display_link'] = $this->getDisplayLink($aux['title'], $aux);
$aux['thumbnail_url'] = liberty_fetch_thumbnails(array('storage_path' => $this->getGalleryThumbBaseUrl($aux['content_id']), 'mime_image' => FALSE));
// deal with the parsing
$parseHash['format_guid'] = $aux['format_guid'];
$parseHash['content_id'] = $aux['content_id'];
$parseHash['user_id'] = $aux['user_id'];
$parseHash['data'] = $aux['data'];
$aux['parsed_data'] = $this->parseData($parseHash);
// sucky additional query to fetch item number without subselect
if ($gBitDbType == 'mysql' || $gBitDbType == 'mysqli') {
$item_count_query = "SELECT COUNT( trm.`item_content_id` ) FROM `" . BIT_DB_PREFIX . "treasury_map` trm WHERE trm.`gallery_content_id`=?";
$aux['item_count'] = $this->mDb->getOne($item_count_query, array($aux['content_id']));
}
if (!empty($pListHash['get_sub_tree'])) {
$aux['subtree'] = $struct->getSubTree($aux['structure_id']);
}
$ret[$aux['content_id']] = $aux;
}
}
$query = "SELECT COUNT( lc.`title` )\n\t\t\tFROM `" . BIT_DB_PREFIX . "treasury_gallery` trg\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = trg.`content_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "users_users` uue ON ( uue.`user_id` = lc.`modifier_user_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "users_users` uuc ON ( uuc.`user_id` = lc.`user_id` )\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_structures` ls ON ( ls.`structure_id` = trg.`structure_id` )\n\t\t\t{$joinSql} {$whereSql}";
$pListHash['cant'] = $this->mDb->getOne($query, $bindVars);
LibertyContent::postGetList($pListHash);
return $ret;
}
/**
* getList This function generates a list of records from the liberty_content database for use in a list page
*
* @param array $pParamHash
* @param array $idList List of ticket identifiers
* @access public
* @return array List of ticketss
*/
function getList(&$pParamHash, $idList = NULL)
{
global $gBitSystem, $gBitUser;
// this makes sure parameters used later on are set
LibertyContent::prepGetList($pParamHash);
$selectSql = $joinSql = $whereSql = '';
$bindVars = array();
array_push($bindVars, $this->mContentTypeGuid);
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
// this will set $find, $sort_mode, $max_records and $offset
extract($pParamHash);
if (is_array($find)) {
// you can use an array of pages
$whereSql .= " AND lc.`title` IN( " . implode(',', array_fill(0, count($find), '?')) . " )";
$bindVars = array_merge($bindVars, $find);
} elseif (is_string($find)) {
// or a string
$whereSql .= " AND UPPER( lc.`title` )like ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
}
if (is_array($idList)) {
//If there is identifiers list, but no element in it, just return empty array.
if (count($idList) == 0) {
return array();
}
$whereSql .= " AND ts.`ticket_id` IN( " . implode(',', array_fill(0, count($idList), '?')) . " )";
$bindVars = array_merge($bindVars, $idList);
}
$query = "\n\t\t\tSELECT ts.*, lc.`title`, lc.`data`, uuc.`login` AS creator_user, uuc.`real_name` AS creator_real_name {$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "tickets` ts\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = ts.`content_id` ) {$joinSql}\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "users_users` uuc ON( uuc.`user_id` = lc.`user_id` )\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}\n\t\t\tORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "\n\t\t\tSELECT COUNT(*)\n\t\t\tFROM `" . BIT_DB_PREFIX . "tickets` ts\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = ts.`content_id` ) {$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}";
$result = $this->mDb->query($query, $bindVars, $max_records, $offset);
$ret = array();
$ids = array();
while ($res = $result->fetchRow()) {
$ids[] = $res['ticket_id'];
$ret[$res['ticket_id']] = $res;
}
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
if ($pParamHash["cant"] > 0) {
$in = implode(',', array_fill(0, $pParamHash["cant"], '?'));
$query_attr = "SELECT ta.*, td.`def_id`, tf.`field_id`, tf.`field_value`\n FROM `" . BIT_DB_PREFIX . "ticket_attributes` ta\n LEFT JOIN `" . BIT_DB_PREFIX . "ticket_field_values` tf ON( ta.`field_id` = tf.`field_id` )\n\t\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "ticket_field_defs` td ON( tf.`def_id` = td.`def_id` )\n WHERE ta.`ticket_id` IN ({$in})\n\t\t\t\tORDER BY td.`sort_order`";
$result = $this->mDb->query($query_attr, $ids);
while ($res = $result->fetchRow()) {
$ret[$res['ticket_id']]['attributes'][$res['def_id']] = $res;
}
}
// add all pagination info to pParamHash
LibertyContent::postGetList($pParamHash);
return $ret;
}
/**
* This function generates a list of records from the liberty_content database for use in a list page
**/
function getList(&$pParamHash)
{
LibertyContent::prepGetList($pParamHash);
$find = $pParamHash['find'];
$sort_mode = $pParamHash['sort_mode'];
$max_records = $pParamHash['max_records'];
$offset = $pParamHash['offset'];
if (is_array($find)) {
// you can use an array of pages
$mid = " WHERE lc.`title` IN( " . implode(',', array_fill(0, count($find), '?')) . " )";
$bindvars = $find;
} else {
if (is_string($find)) {
// or a string
$mid = " WHERE UPPER( lc.`title` )like ? ";
$bindvars = array('%' . strtoupper($find) . '%');
} else {
if (!empty($pUserId)) {
// or a string
$mid = " WHERE lc.`creator_user_id` = ? ";
$bindvars = array($pUserId);
} else {
$mid = "";
$bindvars = array();
}
}
}
$query = "SELECT yp.*, lc.`content_id`, lc.`title`, lc.`data`\n\t\t\tFROM `" . BIT_DB_PREFIX . "yellowpages` yp INNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = yp.`content_id` )\n\t\t\t" . (!empty($mid) ? $mid . ' AND ' : ' WHERE ') . " lc.`content_type_guid` = '" . YELLOWPAGES_CONTENT_TYPE_GUID . "'\n\t\t\tORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "select count( * )from `" . BIT_DB_PREFIX . "liberty_content` lc " . (!empty($mid) ? $mid . ' AND ' : ' WHERE ') . " lc.`content_type_guid` = '" . YELLOWPAGES_CONTENT_TYPE_GUID . "'";
$result = $this->mDb->query($query, $bindvars, $max_records, $offset);
$ret = array();
while ($res = $result->fetchRow()) {
$ret[] = $res;
}
$pParamHash["data"] = $ret;
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindvars);
LibertyContent::postGetList($pParamHash);
return $pParamHash;
}
/**
* Generate list of GEDCOM archives
* $pListHash values used
* @param offset Number of the first record to list
* @param maxRecords Number of records to list
* @param letter Filter surname list by letter selected
*/
function listSurnames(&$pListHash)
{
LibertyContent::prepGetList($pListHash);
$bindVars = array($this->mGEDCOMId);
$selectSql = '';
if (isset($pListHash['letter']) && $pListHash['letter'] != '') {
$selectSql .= 'AND `n_surname` STARTING ? ';
array_push($bindVars, $pListHash['letter']);
}
$query = "SELECT UPPER(`n_surname`), COUNT(UPPER(`n_surname`))\n\t\t\t\t\tFROM " . PHPGEDVIEW_DB_PREFIX . "name` WHERE `n_file` = ? AND `n_surname` NOT STARTING '@' {$selectSql}\n\t\t\t\t\tGROUP BY UPPER(`n_surname`)\n\t\t\t\t\tORDER BY 1";
$query_cant = "SELECT DISTINCT UPPER(`n_surname`)\n\t\t\t\t\tFROM " . PHPGEDVIEW_DB_PREFIX . "name` WHERE `n_file` = ? AND `n_surname` NOT STARTING '@' {$selectSql}";
$query_tot = "SELECT COUNT(`n_surname`)\n\t\t\t\t\tFROM " . PHPGEDVIEW_DB_PREFIX . "name` WHERE `n_file` = ? AND `n_surname` NOT STARTING '@' {$selectSql}";
// If sort mode is versions then offset is 0, maxRecords is -1 (again) and sort_mode is nil
// If sort mode is links then offset is 0, maxRecords is -1 (again) and sort_mode is nil
// If sort mode is backlinks then offset is 0, maxRecords is -1 (again) and sort_mode is nil
$ret = array();
$this->mDb->StartTrans();
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
// total number of surnames
$cant = $this->mDb->query($query_cant, $bindVars);
// total number of individuals acorss selected surnames
$tot = $this->mDb->getOne($query_tot, $bindVars);
$this->mDb->CompleteTrans();
$ind_total = 0;
while ($res = $result->fetchRow()) {
$aux = array();
$aux = $res;
$ind_total += $res['count'];
$ret[] = $aux;
}
$pListHash['cant'] = $cant->NumRows();
$pListHash['listInfo']['sub_total'] = $tot;
LibertyContent::postGetList($pListHash);
return $ret;
}
function getList(&$pParamHash)
{
global $gBitSystem;
LibertyContent::prepGetList($pParamHash);
$selectSql = '';
$joinSql = '';
$whereSql = '';
$bindVars = array();
// array_push( $bindVars, $this->mContentTypeGuid );
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
// You can use a title or an array of blog_id
if (!empty($pParamHash['find'])) {
if (is_array($pParamHash['find'])) {
$whereSql .= " AND b.`blog_id` IN ( " . implode(',', array_fill(0, count($pParamHash['find']), '?')) . ") ";
$bindVars = array_merge($bindVars, $pParamHash['find']);
} else {
$findesc = '%' . strtoupper($pParamHash['find']) . '%';
$whereSql = " AND (UPPER(lc.`title`) like ? or UPPER(lc.`data`) like ?) ";
$bindVars = array($findesc, $findesc);
}
}
if (@$this->verifyId($pParamHash['user_id'])) {
$whereSql .= " AND uu.`user_id` = ? ";
$bindVars[] = $pParamHash['user_id'];
}
$this->getServicesSql('content_user_collection_function', $selectSql, $joinSql, $whereSql, $bindVars, $this, $pListHash);
if (!empty($pParamHash['is_active'])) {
$whereSql .= " AND b.`activity` IS NOT NULL";
}
if (!empty($pParamHash['is_hit'])) {
$whereSql .= " AND lch.`hits` IS NOT NULL";
}
if (!empty($pParamHash['content_perm_name'])) {
$this->getContentPermissionsSql($pParamHash['content_perm_name'], $selectSql, $joinSql, $whereSql, $bindVars);
}
if (!empty($whereSql)) {
$whereSql = preg_replace('/^[\\s]*AND/', ' WHERE ', $whereSql);
}
$ret = array();
// Return a data array, even if empty
$pParamHash["data"] = array();
# Get count of total number of items available
$query_cant = "\n\t\t\tSELECT COUNT(b.`blog_id`)\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "blogs` b\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON (lc.`content_id` = b.`content_id`)\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_users` uu ON (uu.`user_id` = lc.`user_id`)\n\t\t\t\t{$joinSql}\n\t\t\t{$whereSql}";
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
# Check for offset out of range
if ($pParamHash['offset'] < 0) {
$pParamHash['offset'] = 0;
} elseif ($pParamHash['offset'] > $pParamHash["cant"]) {
$lastPageNumber = ceil($pParamHash["cant"] / $pParamHash['max_records']) - 1;
$pParamHash['offset'] = $pParamHash['max_records'] * $lastPageNumber;
}
$query = "\n\t\t\tSELECT b.`content_id` AS `hash_key`,\n\t\t\t\tb.`blog_id`, b.`is_public`, b.`max_posts`, b.`activity`, b.`use_find`, b.`use_title`,\n\t\t\t\tb.`add_date`, b.`add_poster`, b.`allow_comments`,\n\t\t\t\tuu.`login`,\tuu.`real_name`, lc.*, lch.hits {$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "blogs` b\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON (lc.`content_id` = b.`content_id`)\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_users` uu ON (uu.`user_id` = lc.`user_id`)\n\t\t\t\t{$joinSql}\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON (lc.`content_id` = lch.`content_id`)\n\t\t\t{$whereSql} order by " . $this->mDb->convertSortmode($pParamHash['sort_mode']);
$result = $this->mDb->query($query, $bindVars, $pParamHash['max_records'], $pParamHash['offset']);
$ret = array();
while ($res = $result->fetchRow()) {
$blogContentId = $res['content_id'];
$ret[$blogContentId] = $res;
$ret[$blogContentId]['blog_url'] = $this->getDisplayUrlFromHash($res);
//get count of post in each blog
$ret[$blogContentId]['postscant'] = $this->getPostsCount($res['content_id']);
// deal with the parsing
$parseHash['format_guid'] = $res['format_guid'];
$parseHash['content_id'] = $res['content_id'];
$parseHash['data'] = $res['data'];
$ret[$blogContentId]['parsed'] = $this->parseData($parseHash);
}
LibertyContent::postGetList($pParamHash);
return $ret;
}
/**
* This function generates a list of records from the liberty_content database for use in a list page
**/
function getList(&$pParamHash)
{
global $gBitSystem, $gBitUser;
// this makes sure parameters used later on are set
LibertyContent::prepGetList($pParamHash);
$selectSql = $joinSql = $whereSql = '';
$bindVars = array();
array_push($bindVars, $this->mContentTypeGuid);
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
if (is_array($pParamHash['find'])) {
// you can use an array of pages
$whereSql .= " AND lc.`title` IN( " . implode(',', array_fill(0, count($pParamHash['find']), '?')) . " )";
$bindVars = array_merge($bindVars, $pParamHash['find']);
} elseif (is_string($pParamHash['find'])) {
// or a string
$whereSql .= " AND UPPER( lc.`title` )like ? ";
$bindVars[] = '%' . strtoupper($pParamHash['find']) . '%';
}
$query = "SELECT sten.*, lc.`title`, lc.`data` {$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "stencils` sten INNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = sten.`content_id` ) {$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}\n\t\t\tORDER BY " . $this->mDb->convertSortmode($pParamHash['sort_mode']);
$query_cant = "select count(*)\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "stencils` sten INNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = sten.`content_id` ) {$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}";
$result = $this->mDb->query($query, $bindVars, $pParamHash['max_records'], $pParamHash['offset']);
$ret = array();
while ($res = $result->fetchRow()) {
// generate output that can be copied and pasted into the textarea
if (!empty($pParamHash['get_usage'])) {
// extract all variables
preg_match_all($this->validRegexp, $res['data'], $matches);
$res['usage'] = "{{{$res['title']}|\n";
foreach ($matches[1] as $match) {
$res['usage'] .= "|{$match}=\n";
}
$res['usage'] .= "}}";
}
$ret[] = $res;
}
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
// add all pagination info to pParamHash
LibertyContent::postGetList($pParamHash);
return $ret;
}
/**
* get a list of available packages
*
* @param array $pListHash
* @access public
* @return TRUE on success, FALSE on failure - mErrors will contain reason for failure
*/
function getList(&$pListHash)
{
global $gBitSystem;
$ret = $bindVars = array();
$selectSql = $joinSql = $orderSql = $whereSql = '';
// disable pagination for now
$pListHash['max_records'] = -1;
if (empty($pListHash['sort_mode'])) {
$pListHash['sort_mode'] = 'version_desc';
}
if (!empty($pListHash['package'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= ' pkgv.`package`=? ';
$bindVars[] = $pListHash['package'];
}
LibertyContent::prepGetList($pListHash);
$query = "\n\t\t\tSELECT pkgv.*, pkgp.*, pkgi.`download_date`, pkgi.`install_date`\n\t\t\tFROM `" . BIT_DB_PREFIX . "packager_versions` pkgv\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "packager_packages` pkgp ON( pkgv.`package`=pkgp.`package` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "packager_installed` pkgi ON( pkgv.`packager_id`=pkgi.`packager_id` )\n\t\t\t{$whereSql} ORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']);
$result = $this->mDb->query($query, $bindVars);
while ($aux = $result->fetchRow()) {
$aux['display_url'] = $this->getDisplayUrlFromHash($aux);
$aux['package_url'] = $this->getPackageUrl($aux);
$ret[$aux['packager_id']] = $aux;
}
$pListHash['cant'] = count($ret);
LibertyContent::postGetList($pListHash);
return $ret;
}
/**
* get list of all rated content
* @param $pListHash contains array of items used to limit search results
* @param $pListHash[sort_mode] column and orientation by which search results are sorted
* @param $pListHash[find] search for a pigeonhole title - case insensitive
* @param $pListHash[max_records] maximum number of rows to return
* @param $pListHash[offset] number of results data is offset by
* @access public
* @return array of rated content
**/
function getList(&$pListHash)
{
global $gBitSystem, $gBitUser, $gLibertySystem;
$ret = $bindVars = array();
$where = $order = '';
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= ' sts.`version` = 0';
if (!empty($pListHash['sort_mode'])) {
$order .= " ORDER BY " . $this->mDb->convert_sortmode($pListHash['sort_mode']) . " ";
} else {
// set a default sort_mode
$order .= " ORDER BY sts.`rating` DESC";
}
LibertyContent::prepGetList($pListHash);
if (!empty($pListHash['find'])) {
$where .= empty($where) ? ' WHERE ' : ' AND ';
$where .= " UPPER( lc.`title` ) LIKE ? ";
$bindVars[] = '%' . strtoupper($pListHash['find']) . '%';
}
$query = "SELECT sts.*, lch.`hits`, lch.`last_hit`, lc.`event_time`, lc.`title`,\n\t\t\tlc.`last_modified`, lc.`content_type_guid`, lc.`ip`, lc.`created`,\n\t\t\tlct.`content_name`\n\t\t\tFROM `" . BIT_DB_PREFIX . "stars_version` sts\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = sts.`content_id` )\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content_types` lct ON ( lct.`content_type_guid` = lc.`content_type_guid` )\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON ( lc.`content_id` = lch.`content_id` )\n\t\t\t{$where} {$order}";
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
while ($aux = $result->fetchRow()) {
$type =& $gLibertySystem->mContentTypes[$aux['content_type_guid']];
if (empty($type['content_object'])) {
include_once $gBitSystem->mPackages[$type['handler_package']]['path'] . $type['handler_file'];
$type['content_object'] = new $type['handler_class']();
}
if (!empty($gBitSystem->mPackages[$type['handler_package']])) {
$aux['display_link'] = $type['content_object']->getDisplayLink($aux['title'], $aux);
$aux['title'] = $type['content_object']->getTitleFromHash($aux);
$aux['display_url'] = $type['content_object']->getDisplayUrl($aux['content_id'], $aux);
}
$ret[] = $aux;
}
$query = "SELECT COUNT( sts.`content_id` ) FROM `" . BIT_DB_PREFIX . "stars_version` sts {$where}";
$pListHash['cant'] = $this->mDb->getOne($query, $bindVars);
LibertyContent::postGetList($pListHash);
return $ret;
}
/**
* Returns list of an Pallet entries
*
* @param integer
* @param integer
* @param integer
* @return string Text for the title description
*/
function getList(&$pParamHash)
{
global $gBitSystem, $gBitUser;
if (empty($pParamHash["sort_mode"])) {
$pParamHash["sort_mode"] = 'pallet_asc';
}
LibertyContent::prepGetList($pParamHash);
// this will set $find, $sort_mode, $max_records and $offset
extract($pParamHash);
$joinSql = '';
$selectSql = '';
$whereSql = '';
$bindVars = array();
array_push($bindVars, $this->mContentTypeGuid);
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
if ($find) {
$findesc = '%' . strtoupper($find) . '%';
$whereSql = " AND (UPPER(b.`title`) like ? or UPPER(b.`description`) like ?) ";
$bindVars = array($bindVars, $findesc, $findesc);
}
/* Keep until LC links are available
$query = "SELECT wp.*, lc.*,
uue.`login` AS modifier_user, uue.`real_name` AS modifier_real_name,
uuc.`login` AS creator_user, uuc.`real_name` AS creator_real_name,
$selectSql
FROM `".BIT_DB_PREFIX."warehouse_pallet` wp
INNER JOIN `".BIT_DB_PREFIX."liberty_content` lc ON ( lc.`content_id` = wp.`content_id` ) $joinSql
LEFT JOIN `".BIT_DB_PREFIX."users_users` uue ON (uue.`user_id` = lc.`modifier_user_id`)
LEFT JOIN `".BIT_DB_PREFIX."users_users` uuc ON (uuc.`user_id` = lc.`user_id`)
WHERE lc.`content_type_guid` = ? $whereSql
ORDER BY ".$this->mDb->convertSortmode($sort_mode);
*/
$query = "SELECT wp.* \n\t\t\t\t{$selectSql}\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "warehouse_pallet` wp\n\t\t\t\tORDER BY " . $this->mDb->convertSortmode($sort_mode);
$result = $this->mDb->query($query, $bindVars, $max_records, $offset);
$ret = array();
while ($res = $result->fetchRow()) {
$res['display_url'] = WAREHOUSE_PKG_URL . 'index.php?pallet_id=' . trim($res['pallet']);
$ret[] = $res;
}
// Get total result count
$query_cant = "SELECT COUNT(wp.`pallet`) FROM `" . BIT_DB_PREFIX . "warehouse_pallet` wp";
// INNER JOIN `".BIT_DB_PREFIX."liberty_content` lc ON ( lc.`content_id` = ir.`content_id` ) $joinSql
// WHERE lc.`content_type_guid` = ? $whereSql";
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
// add all pagination info to pParamHash
LibertyContent::postGetList($pParamHash);
return $ret;
}
$gBitSmarty->assign('words', $_REQUEST["words"]);
$results = $searchlib->find($_REQUEST);
if ($_REQUEST['cant'] != 1) {
$where2 .= "s";
}
$gBitSmarty->assign('where2', tra($where2));
$gBitSmarty->assign('content_type_guid', $_REQUEST["content_type_guid"]);
$stubContent = new LibertyContent();
if ($_REQUEST['cant'] > 0) {
foreach (array_keys($results) as $k) {
if (empty($results[$k]['title'])) {
$date_format = $gBitSystem->get_long_date_format();
if ($gBitSystem->mServerTimestamp->get_display_offset()) {
$date_format = preg_replace("/ ?%Z/", "", $date_format);
} else {
$date_format = preg_replace("/%Z/", "UTC", $date_format);
}
$date_string = $gBitSystem->mServerTimestamp->getDisplayDateFromUTC($results[$k]['created']);
$results[$k]['title'] = $gBitSystem->mServerTimestamp->strftime($date_format, $date_string, true);
}
if (!empty($results[$k]['data'])) {
$results[$k]['parsed'] = $stubContent->parseData($results[$k]);
}
}
}
LibertyContent::postGetList($_REQUEST);
$gBitSmarty->assignByRef('listInfo', $_REQUEST['listInfo']);
// Find search results (build array)
$gBitSmarty->assignByRef('results', $results);
// Display the template
$gBitSystem->display('bitpackage:search/search.tpl', 'Search Results for: ' . strip_tags($_REQUEST["highlight"]), array('display_mode' => 'display'));
/**
* Get a list of action log entries
*
* @param array $pListHash List options
* @access public
* @return List of entries on success, FALSE on failure
*/
function getActionLogs(&$pListHash)
{
LibertyContent::prepGetList($pListHash);
$ret = $bindVars = array();
$selectSql = $joinSql = $orderSql = $whereSql = '';
if (!empty($pListHash['find'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " UPPER( lal.`log_message` ) LIKE ? ";
$bindVars[] = '%' . strtoupper($pListHash['find']) . '%';
}
if (!empty($pListHash['find_title'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " UPPER( lal.`title` ) LIKE ? ";
$bindVars[] = '%' . strtoupper($pListHash['find_log']) . '%';
}
if (!empty($pListHash['user_id'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " lal.`user_id` = ? ";
$bindVars[] = $pListHash['user_id'];
}
if (!empty($pListHash['content_id'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " lal.`content_id` = ? ";
$bindVars[] = $pListHash['content_id'];
}
if (!empty($pListHash['sort_mode'])) {
if (preg_match("/^last_modified|^title/", $pListHash['sort_mode'])) {
$pListHash['sort_mode'] = "lal." . $pListHash['sort_mode'];
}
$orderSql = " ORDER BY " . $this->convertSortMode($pListHash['sort_mode']) . " ";
}
$query = "\n\t\t\tSELECT lal.*,\n\t\t\t\tlc.`content_type_guid`, lc.`created`, lct.`content_name`, lct.`content_name_plural`,\n\t\t\t\tuue.`login` AS modifier_user, uue.`real_name` AS modifier_real_name\n\t\t\tFROM `" . BIT_DB_PREFIX . "liberty_action_log` lal\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = lal.`content_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_types` lct ON ( lct.`content_type_guid` = lc.`content_type_guid` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "users_users` uue ON ( uue.`user_id` = lal.`user_id` )\n\t\t\t{$whereSql} {$orderSql}";
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
while ($aux = $result->fetchRow()) {
$aux['user'] = $aux['modifier_user'];
$aux['editor'] = isset($aux['modifier_real_name']) ? $aux['modifier_real_name'] : $aux['modifier_user'];
$aux['display_name'] = BitUser::getDisplayNameFromHash(NULL, $aux);
$ret[] = $aux;
}
$query = "SELECT COUNT( lal.`user_id` ) FROM `" . BIT_DB_PREFIX . "liberty_action_log` lal {$whereSql}";
$pListHash['cant'] = $this->mDb->getOne($query, $bindVars);
LibertyContent::postGetList($pListHash);
return $ret;
}
/**
* getList
*
* @param array $pListHash
* @access public
* @return TRUE on success, FALSE on failure - mErrors will contain reason for failure
*/
function getList(&$pListHash, $pStructureId = NULL)
{
LibertyContent::prepGetList($pListHash);
$ret = $bindVars = array();
$selectSql = $joinSql = $whereSql = "";
if (@BitBase::verifyId($pListHash['gallery_content_id'])) {
$whereSql = " WHERE trm.`gallery_content_id` = ? ";
$bindVars[] = $pListHash['gallery_content_id'];
}
if (@BitBase::verifyId($pListHash['user_id'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " lc.`user_id` = ? ";
$bindVars[] = $pListHash['user_id'];
}
if (!empty($pListHash['title']) && is_string($pListHash['title'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " trm.`item_content_id` = lc.`content_id` AND UPPER( lc.`title` ) = ?";
$bindVars[] = strtoupper($pListHash['title']);
}
if (!empty($pListHash['max_age']) && is_numeric($pListHash['max_age'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " lc.`created` > ? ";
$bindVars[] = $pListHash['max_age'];
}
if (!empty($pListHash['sort_mode'])) {
$orderSql = " ORDER BY " . $this->mDb->convertSortmode($pListHash['sort_mode']) . " ";
} else {
$orderSql = " ORDER BY trm.`item_position` ASC ";
}
// only join attachments table when we need it for sorting
if (strstr($pListHash['sort_mode'], 'la.hits') !== FALSE) {
$joinSql .= " LEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_attachments` la ON ( la.`content_id` = lc.`content_id` ) ";
}
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
$ret = array();
$query = "\n\t\t\tSELECT\n\t\t\t\tlct.`content_name`,\n\t\t\t\tuu.`login`, uu.`real_name`,\n\t\t\t\tlc.`content_id`, lc.`last_modified`, lc.`user_id`, lc.`title`, lc.`content_type_guid`, lc.`created`, lc.`data`,\n\t\t\t\tlch.`hits` {$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "treasury_map` trm\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = trm.`item_content_id` )\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content_types` lct ON ( lc.`content_type_guid` = lct.`content_type_guid` )\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_users` uu ON ( uu.`user_id` = lc.`user_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON ( lch.`content_id` = lc.`content_id` )\n\t\t\t{$joinSql} {$whereSql} {$orderSql}";
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
while ($aux = $result->fetchRow()) {
$item = new TreasuryItem($aux['content_id']);
$item->load();
$ret[] = $item;
}
$query = "SELECT COUNT( trm.`item_content_id` )\n\t\t\tFROM `" . BIT_DB_PREFIX . "treasury_map` trm\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON ( lc.`content_id` = trm.`item_content_id` )\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content_types` lct ON ( lc.`content_type_guid` = lct.`content_type_guid` )\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_users` uu ON ( uu.`user_id` = lc.`user_id` )\n\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content_hits` lch ON ( lch.`content_id` = lc.`content_id` )\n\t\t\t{$joinSql} {$whereSql}";
$pListHash['cant'] = $this->mDb->getOne($query, $bindVars);
LibertyContent::postGetList($pListHash);
return count($this->mErrors) == 0 ? $ret : FALSE;
}
/**
* getCitizenList( &$pParamHash );
* Get list of citizen records
*/
function getCitizenList(&$pParamHash)
{
global $gBitSystem, $gBitUser;
if (empty($pParamHash['sort_mode'])) {
if (empty($_REQUEST["sort_mode"])) {
$pParamHash['sort_mode'] = 'surname_asc';
} else {
$pParamHash['sort_mode'] = $_REQUEST['sort_mode'];
}
}
LibertyContent::prepGetList($pParamHash);
$findSql = '';
$selectSql = '';
$joinSql = '';
$whereSql = '';
$bindVars = array();
$type = 'surname';
// this will set $find, $sort_mode, $max_records and $offset
extract($pParamHash);
if (isset($find_org) and is_string($find_org) and $find_org != '') {
$whereSql .= " AND UPPER( ci.`organisation` ) like ? ";
$bindVars[] = '%' . strtoupper($find_org) . '%';
$type = 'organisation';
$pParamHash["listInfo"]["ihash"]["find_org"] = $find_org;
}
if (isset($find_name) and is_string($find_name) and $find_name != '') {
$split = preg_split('|[,. ]|', $find_name, 2);
$whereSql .= " AND UPPER( ci.`surname` ) STARTING ? ";
$bindVars[] = strtoupper($split[0]);
if (array_key_exists(1, $split)) {
$split[1] = trim($split[1]);
$whereSql .= " AND UPPER( ci.`forename` ) STARTING ? ";
$bindVars[] = strtoupper($split[1]);
}
$pParamHash["listInfo"]["ihash"]["find_name"] = $find_name;
}
if (isset($find_street) and is_string($find_street) and $find_street != '') {
$whereSql .= " AND UPPER( a.`street` ) like ? ";
$bindVars[] = '%' . strtoupper($find_street) . '%';
$pParamHash["listInfo"]["ihash"]["find_street"] = $find_street;
}
if (isset($find_org) and is_string($find_postcode) and $find_postcode != '') {
$whereSql .= " AND UPPER( `a.postcode` ) LIKE ? ";
$bindVars[] = '%' . strtoupper($find_postcode) . '%';
$pParamHash["listInfo"]["ihash"]["find_postcode"] = $find_postcode;
}
$query = "SELECT ci.*, a.UPRN, a.POSTCODE, a.SAO, a.PAO, a.NUMBER, a.STREET, a.LOCALITY, a.TOWN, a.COUNTY, ci.parent_id as uprn,\n\t\t\t(SELECT COUNT(*) FROM `" . BIT_DB_PREFIX . "citizen_xref` x WHERE x.content_id = ci.content_id ) AS links, \n\t\t\t(SELECT COUNT(*) FROM `" . BIT_DB_PREFIX . "task_ticket` e WHERE e.usn = ci.usn ) AS enquiries {$selectSql} \n\t\t\tFROM `" . BIT_DB_PREFIX . "citizen` ci \n\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "citizen_address` a ON a.content_id = ci.content_id {$findSql}\n\t\t\t{$joinSql} \n\t\t\tWHERE ci.`" . $type . "` <> '' {$whereSql} ORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "SELECT COUNT( * )\n\t\t\tFROM `" . BIT_DB_PREFIX . "citizen` ci\n\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "citizen_address` a ON a.content_id = ci.content_id {$findSql}\n\t\t\t{$joinSql} WHERE ci.`" . $type . "` <> '' {$whereSql} ";
// INNER JOIN `".BIT_DB_PREFIX."citizen_address` a ON a.content_id = ci.content_id
$result = $this->mDb->query($query, $bindVars, $max_records, $offset);
$ret = array();
while ($res = $result->fetchRow()) {
if (!empty($parse_split)) {
$res = array_merge($this->parseSplit($res), $res);
}
$ret[] = $res;
}
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
LibertyContent::postGetList($pParamHash);
return $ret;
}
/**
* getList This function generates a list of records from the liberty_content database for use in a list page
*
* @param array $pParamHash
* @access public
* @return array List of sample data
*/
function getList(&$pParamHash)
{
// this makes sure parameters used later on are set
LibertyContent::prepGetList($pParamHash);
$selectSql = $joinSql = $whereSql = '';
$bindVars = array();
array_push($bindVars, $this->mContentTypeGuid);
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
// this will set $find, $sort_mode, $max_records and $offset
extract($pParamHash);
if (is_array($find)) {
// you can use an array of pages
$whereSql .= " AND lc.`title` IN( " . implode(',', array_fill(0, count($find), '?')) . " )";
$bindVars = array_merge($bindVars, $find);
} elseif (is_string($find)) {
// or a string
$whereSql .= " AND UPPER( lc.`title` )like ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
}
$query = "\n\t\t\tSELECT sample.*, lc.`content_id`, lc.`title`, lc.`data` {$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "sample_data` sample\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = sample.`content_id` ) {$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}\n\t\t\tORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "\n\t\t\tSELECT COUNT(*)\n\t\t\tFROM `" . BIT_DB_PREFIX . "sample_data` sample\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = sample.`content_id` ) {$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}";
$result = $this->mDb->query($query, $bindVars, $max_records, $offset);
$ret = array();
while ($res = $result->fetchRow()) {
$ret[] = $res;
}
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
// add all pagination info to pParamHash
LibertyContent::postGetList($pParamHash);
return $ret;
}
/**
* This function generates a list of records from the liberty_content database for use in a list page
**/
function getList(&$pParamHash)
{
global $gBitSystem, $gBitUser;
// this makes sure parameters used later on are set
$this->prepGetList($pParamHash);
$selectSql = $joinSql = $whereSql = '';
$bindVars = array();
array_push($bindVars, $this->mContentTypeGuid);
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
// this will set $find, $sort_mode, $max_records and $offset
extract($pParamHash);
if (is_array($find)) {
// you can use an array of pages
$whereSql .= " AND lc.`title` IN( " . implode(',', array_fill(0, count($find), '?')) . " )";
$bindVars = array_merge($bindVars, $find);
} elseif (is_string($find)) {
// or a string
$whereSql .= " AND UPPER( lc.`title` )like ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
}
$pagination = true;
if (!empty($pParamHash['paginationOff'])) {
$pagination = false;
}
if (!empty($pParamHash['boards']) && is_array($pParamHash['boards'])) {
$whereSql .= " AND lc.`content_id` IN ( " . implode(',', array_fill(0, count($pParamHash['boards']), '?')) . " )";
$bindVars = array_merge($bindVars, $pParamHash['boards']);
}
if (!empty($pParamHash['nboards']) && is_array($pParamHash['nboards'])) {
$whereSql .= " AND lc.`content_id` NOT IN ( " . implode(',', array_fill(0, count($pParamHash['nboards']), '?')) . " )";
$bindVars = array_merge($bindVars, $pParamHash['nboards']);
}
$track = $gBitSystem->isFeatureActive('boards_thread_track');
$track = true;
if ($track) {
$selectSql .= ", (\n\t\t\t\t\tSELECT COUNT(trk.`topic_id`)\n\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "boards_map` map\n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_comments` lcom ON (map.`topic_content_id` = lcom.`root_id`)\n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "boards_tracking` trk ON (trk.`topic_id` = lcom.`thread_forward_sequence`)\n\t\t\t\t\tWHERE lcom.`root_id`=lcom.`parent_id` AND map.`board_content_id`=lc.`content_id` AND trk.`user_id`=" . $gBitUser->mUserId . "\n\t\t\t\t) AS track_count ";
}
if ($gBitSystem->isFeatureActive('boards_posts_anon_moderation') && !($gBitUser->hasPermission('p_boards_update') || $gBitUser->hasPermission('p_boards_post_update'))) {
}
if ($gBitSystem->isFeatureActive('boards_posts_anon_moderation') && ($gBitUser->hasPermission('p_boards_update') || $gBitUser->hasPermission('p_boards_post_update'))) {
$selectSql .= ", ( SELECT COUNT(*)\n\t\t\tFROM `" . BIT_DB_PREFIX . "boards_map` map\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_comments` s_lcom ON (map.`topic_content_id` = s_lcom.`root_id`)\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` s_lc ON (s_lcom.`content_id` = s_lc.`content_id`)\n\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "boards_posts` s ON( s_lcom.`comment_id` = s.`comment_id` )\nWHERE map.`board_content_id`=lc.`content_id` AND ((s_lc.`user_id` < 0) AND (s.`is_approved` = 0 OR s.`is_approved` IS NULL) )\n\t\t\t) AS unreg";
} else {
$selectSql .= ", 0 AS unreg";
}
// always show our own content
$whereSql .= " AND ( lc.`user_id`= ? ";
$bindVars[] = $gBitUser->mUserId;
if (!empty($pParamHash['content_status_id'])) {
// TODO needs safety checking! - spider
$whereSql .= " OR lc.`content_status_id` = ? ";
$bindVars[] = $pParamHash['content_status_id'];
} elseif (!empty($pParamHash['min_content_status_id'])) {
$whereSql .= " OR lc.`content_status_id` >= ? ";
$bindVars[] = $pParamHash['min_content_status_id'];
}
$whereSql .= " ) ";
$query = "SELECT ts.*, lc.`content_id`, lc.`title`, lc.`data`, lc.`format_guid`\n\t\t\t {$selectSql}\n\t\t\tFROM `" . BIT_DB_PREFIX . "boards` ts\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = ts.`content_id` ) {$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}\n\t\t\tORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "select count(*)\n\t\t\tFROM `" . BIT_DB_PREFIX . "boards` ts INNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = ts.`content_id` ) {$joinSql}\n\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}";
$result = $this->mDb->query($query, $bindVars);
$ret = array();
while ($res = $result->fetchRow()) {
$res['url'] = BOARDS_PKG_URL . "index.php?b={$res['board_id']}";
$res['topic_count'] = $this->mDb->getOne("SELECT count(*)\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "boards_map` map\n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_comments` lcom ON (map.`topic_content_id` = lcom.`root_id`)\n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` slc ON( slc.`content_id` = lcom.`content_id` )\n\t\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "boards_posts` fp ON (fp.`comment_id` = lcom.`comment_id`)\n\t\t\t\tWHERE lcom.`root_id`=lcom.`parent_id` AND map.`board_content_id`=? AND ((fp.`is_approved` = 1) OR (fp.`is_approved` IS NULL))", array($res['content_id']));
$res['post_count'] = $this->mDb->getOne("SELECT count(*)\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "liberty_comments` lcom\n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` slc ON( slc.`content_id` = lcom.`content_id` )\n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "boards_map` map ON (lcom.`root_id`=map.`topic_content_id`)\n\t\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "boards_posts` fp ON (fp.`comment_id` = lcom.`comment_id`)\n\t\t\t\tWHERE map.`board_content_id`=? AND ((fp.`is_approved` = 1) OR (fp.`is_approved` IS NULL))", array($res['content_id']));
if ($track) {
if ($gBitUser->isRegistered()) {
$res['track']['on'] = true;
$res['track']['count'] = $res['track_count'];
if ($res['post_count'] > $res['track_count']) {
$res['track']['mod'] = true;
} else {
$res['track']['mod'] = false;
}
} else {
$res['track']['on'] = false;
}
unset($res['track_count']);
$res['parsed_data'] = $this->parseData($res);
$res['last'] = $this->getLastTopic($res);
}
$ret[] = $res;
}
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
// add all pagination info to pParamHash
LibertyContent::postGetList($pParamHash);
return $ret;
}
