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;
}
/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
$_REQUEST["words"] = $_REQUEST["highlight"];
} else {
// a nice big, groovy search will be cool to have one day...
$gBitSystem->display('bitpackage:search/search.tpl', 'Search', array('display_mode' => 'display'));
die;
}
if ($gBitSystem->isFeatureActive("search_stats")) {
$searchlib->register_search(isset($_REQUEST["words"]) ? $_REQUEST["words"] : '');
}
if (!isset($_REQUEST["content_type_guid"])) {
$_REQUEST["content_type_guid"] = '';
$where2 = "Page";
} else {
$where2 = $contentTypes[$_REQUEST["content_type_guid"]];
}
LibertyContent::prepGetList($_REQUEST);
if (isset($_REQUEST['usePart']) && $_REQUEST['usePart'] == 'on') {
$_REQUEST['usePart'] = true;
} else {
$_REQUEST['usePart'] = false;
}
$gBitSmarty->assign('usePart', $_REQUEST['usePart']);
$gBitSmarty->assign('searchType', $_REQUEST['usePart'] ? "Using Partial Word Search" : "Using Exact Word Search");
if (isset($_REQUEST['useAnd']) && $_REQUEST['useAnd'] == 'on') {
$_REQUEST['useAnd'] = true;
} else {
$_REQUEST['useAnd'] = false;
}
$gBitSmarty->assign('useAnd', $_REQUEST['useAnd']);
// Build the query using words
if (!isset($_REQUEST["words"]) || empty($_REQUEST["words"])) {
/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
/**
* Get a list of all available attachments
*
* @param array $pListHash
* @access public
* @return TRUE on success, FALSE on failure - mErrors will contain reason for failure
*/
function getAttachmentList(&$pListHash)
{
global $gLibertySystem, $gBitUser, $gBitSystem;
LibertyContent::prepGetList($pListHash);
// initialise some variables
$attachments = $ret = $bindVars = array();
$whereSql = $joinSql = $selectSql = '';
// only admin may view attachments from other users
if (!$gBitUser->isAdmin()) {
$pListHash['user_id'] = $gBitUser->mUserId;
}
if (!empty($pListHash['user_id'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " la.user_id = ? ";
$bindVars[] = $pListHash['user_id'];
}
if (!empty($pListHash['content_id'])) {
$whereSql .= empty($whereSql) ? ' WHERE ' : ' AND ';
$whereSql .= " la.`content_id` = ? ";
$selectSql .= " , la.`content_id` ";
$bindVars[] = $pListHash['content_id'];
}
$query = "SELECT la.* {$selectSql} FROM `" . BIT_DB_PREFIX . "liberty_attachments` la INNER JOIN `" . BIT_DB_PREFIX . "users_users` uu ON(la.`user_id` = uu.`user_id`) {$joinSql} {$whereSql}";
$result = $this->mDb->query($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
while ($res = $result->fetchRow()) {
$attachments[] = $res;
}
foreach ($attachments as $attachment) {
if ($loadFunc = $gLibertySystem->getPluginFunction($attachment['attachment_plugin_guid'], 'load_function', 'mime')) {
/* @$prefs - quick hack to stop LibertyMime plugins from breaking until migration to LibertyMime is complete
* see expected arguments of liberty/plugins/mime.default.php::mime_default_load -wjames5
*/
$prefs = array();
$ret[$attachment['attachment_id']] = $loadFunc($attachment, $prefs);
}
}
// count all entries
$query = "SELECT COUNT(*)\n\t\t\tFROM `" . BIT_DB_PREFIX . "liberty_attachments` la\n\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "users_users` uu ON(la.`user_id` = uu.`user_id`)\n\t\t\t{$joinSql} {$whereSql}\n\t\t";
$pListHash['cant'] = $this->mDb->getOne($query, $bindVars);
$this->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;
}
function getQueue(&$pListHash)
{
$ret = array();
LibertyContent::prepGetList($pListHash);
$query = "SELECT mq.`mail_queue_id` AS `hash_key`, mq.*, lc.`title`, lc2.`title` AS newsletter_title\n\t\t\t\t FROM `" . BIT_DB_PREFIX . "mail_queue` mq \n\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON (lc.content_id=mq.content_id)\n\t\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc2 ON (lc2.content_id=mq.nl_content_id)\n\t\t\t\t WHERE begin_date IS NULL \n\t\t\t\t ORDER BY queue_date";
if ($rs = $this->mDb->query($query)) {
$ret = $rs->getAssoc();
}
return $ret;
}
function getList(&$pParamHash)
{
global $gBitSystem, $gLibertySystem;
if (!isset($pParamHash['sort_mode']) or $pParamHash['sort_mode'] == '') {
$pParamHash['sort_mode'] = 'created_desc';
}
if (empty($pParamHash['max_records'])) {
$pParamHash['max_records'] = $gBitSystem->getConfig('max_records');
}
LibertyContent::prepGetList($pParamHash);
$sort_mode = $this->mDb->convertSortmode($pParamHash['sort_mode']);
$joinSql = $whereSql = $selectSql = '';
$bindVars = $ret = array();
$pParamHash['include_comments'] = TRUE;
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars, NULL, $pParamHash);
if (!empty($pParamHash['root_content_type_guid'])) {
if (is_string($pParamHash['root_content_type_guid'])) {
$pParamHash['root_content_type_guid'] = array($pParamHash['root_content_type_guid']);
} elseif (is_array($pParamHash['root_content_type_guid'])) {
$contentTypes = array_keys($gLibertySystem->mContentTypes);
$max = count($pParamHash['root_content_type_guid']);
$guidSql = '';
for ($i = 0; $i < $max; $i++) {
if (in_array($pParamHash['root_content_type_guid'][$i], $contentTypes)) {
if (strlen($guidSql)) {
$guidSql .= ' OR ';
}
$guidSql .= " rlc.`content_type_guid`=? ";
$bindVars[] = $pParamHash['root_content_type_guid'][$i];
}
}
$whereSql .= " AND ( {$guidSql} )";
}
}
if (!empty($pParamHash['content_type_guid'])) {
$whereSql .= " AND rlc.`content_type_guid`=? ";
$bindVars[] = $pParamHash['content_type_guid'];
}
if (!empty($pParamHash['user_id'])) {
$whereSql .= " AND ptc.`user_id`=? ";
$bindVars[] = $pParamHash['user_id'];
}
if (!empty($pParamHash['created_ge'])) {
$whereSql .= " AND lc.`created`>=? ";
$bindVars[] = $pParamHash['created_ge'];
}
// left outer join on root so updater works
$query = "SELECT\n\t\t\t\t\tlcom.`comment_id`,\n\t\t\t\t\tlc.`content_id`,\n\t\t\t\t\tlcom.`parent_id`,\n\t\t\t\t\tlcom.`anon_name`,\n\t\t\t\t\tlcom.`root_id`,\n\t\t\t\t\tlc.`title` AS `content_title`,\n\t\t\t\t\trlc.`title` AS `root_content_title`,\n\t\t\t\t\tlc.`created`,\n\t\t\t\t\tlc.`data`,\n\t\t\t\t\tlc.`last_modified` as `last_modified`,\n\t\t\t\t\tlc.`title` as `title`,\n\t\t\t\t\tptc.`content_type_guid` as `parent_content_type_guid`,\n\t\t\t\t\trlc.`content_type_guid` as `root_content_type_guid`,\n\t\t\t\t\tlc.`content_type_guid`,\n\t\t\t\t\tuu.`login` AS `creator_user`,\n\t\t\t\t\tuu.`login`,\n\t\t\t\t\tuu.`real_name`,\n\t\t\t\t\tuu.`user_id`\n\t\t\t\t\t{$selectSql}\n\t\t\t\t FROM `" . BIT_DB_PREFIX . "liberty_comments` lcom\n\t\t\t\t \t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lcom.`content_id`=lc.`content_id` )\n\t\t\t \t\tINNER JOIN `" . BIT_DB_PREFIX . "users_users` uu ON( uu.`user_id`=lc.`user_id`)\n\t\t\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content` rlc ON( rlc.`content_id`=lcom.`root_id` )\n\t\t\t\t\t\t{$joinSql}, `" . BIT_DB_PREFIX . "liberty_content` ptc\n\t\t\t\t WHERE lcom.`parent_id`=ptc.`content_id` {$whereSql}\n\t\t\t\t ORDER BY {$sort_mode}";
if ($result = $this->mDb->query($query, $bindVars, $pParamHash['max_records'], $pParamHash['offset'])) {
while ($row = $result->FetchRow()) {
$row['display_link'] = $this->getDisplayLink($row['content_title'], $row);
$row['display_url'] = static::getDisplayUrlFromHash($row);
$row['direct_url'] = static::getDirectUrlFromHash($row);
if (!empty($pParamHash['parse'])) {
$row['parsed_data'] = $this->parseData($row);
}
$ret[] = $row;
}
}
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;
}
/**
* 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;
}
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;
}
/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
/**
* getAllRoless Get a list of all Roles
*
* @param array $pListHash List Hash
* @access public
* @return array of roles
*/
function getAllRoles(&$pListHash)
{
if (empty($pListHash['sort_mode']) || $pListHash['sort_mode'] == 'name_asc') {
$pListHash['sort_mode'] = 'role_name_asc';
}
LibertyContent::prepGetList($pListHash);
$sortMode = $this->mDb->convertSortmode($pListHash['sort_mode']);
if (!empty($pListHash['find_roles'])) {
$mid = " AND UPPER(`role_name`) like ?";
$bindvars[] = "%" . strtoupper($pListHash['find_roles']) . "%";
} elseif (!empty($pListHash['find'])) {
$mid = " AND UPPER(`role_name`) like ?";
$bindvars[] = "%" . strtoupper($pListHash['find']) . "%";
} else {
$mid = '';
$bindvars = array();
}
if (!empty($pListHash['hide_root_roles'])) {
$mid .= ' AND `user_id` <> ' . ROOT_USER_ID;
} elseif (!empty($pListHash['only_root_roles'])) {
$mid .= ' AND `user_id` = ' . ROOT_USER_ID;
}
if (!empty($pListHash['user_id'])) {
$mid .= ' AND `user_id` = ? ';
$bindvars[] = $pListHash['user_id'];
}
if (!empty($pListHash['is_public'])) {
$mid .= ' AND `is_public` = ?';
$bindvars[] = $pListHash['is_public'];
}
if (!empty($pListHash['visible']) && !$this->isAdmin()) {
global $gBitUser;
$mid .= ' AND `user_id` = ? OR `is_public` = ? ';
$bindvars[] = $gBitUser->mUserId;
$bindvars[] = 'y';
}
$mid = preg_replace('/^ AND */', ' WHERE ', $mid);
$query = "\n\t\t\tSELECT `user_id`, `role_id`, `role_name` , `role_desc`, `role_home`, `is_default`, `is_public`\n\t\t\tFROM `" . BIT_DB_PREFIX . "users_roles` {$mid}\n\t\t\tORDER BY {$sortMode}";
$ret = array();
if ($rs = $this->mDb->query($query, $bindvars)) {
while ($row = $rs->fetchRow()) {
$roleId = $row['role_id'];
$ret[$roleId] = $row;
$ret[$roleId]['perms'] = $this->getRolePermissions(array('role_id' => $roleId));
}
}
$pListHash['cant'] = $this->mDb->getOne("SELECT COUNT(*) FROM `" . BIT_DB_PREFIX . "users_roles` {$mid}", $bindvars);
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;
}
/**
* 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;
}
/**
* This function generates a list of records from the liberty_content database for use in a list page
**/
function getList(&$pParamHash)
{
global $commentsLib;
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);
// quick search support from find field
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) . '%';
}
$sortModePrefix = 'lc.';
$sort_mode = $sortModePrefix . $this->mDb->convertSortmode($pParamHash['sort_mode']);
$query = "SELECT bm.*, lc.`content_id`, lc.`title`, lc.`data`, lcds.`data` AS `summary`,\n\t\t\t\t\t\tlc.`format_guid`, lc.`last_modified`, lc.`created`, lc.`version` \n\t\t\t\t\t\t{$selectSql}\n\t\t\t\t FROM `" . BIT_DB_PREFIX . "gmaps` bm \n\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = bm.`content_id` )\n\t\t\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\t\t\t{$joinSql}\n\t\t\t\t WHERE lc.`content_type_guid` = ? {$whereSql}\n\t\t\t\t ORDER BY {$sort_mode}";
$query_cant = "SELECT COUNT( * )\n\t\t\t\t\t\tFROM\t`" . BIT_DB_PREFIX . "gmaps` bm \n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id` = bm.`content_id` ) {$joinSql}\n\t\t\t\t\t\tWHERE lc.`content_type_guid` = ? {$whereSql}";
$result = $this->mDb->query($query, $bindVars, $max_records, $offset);
$ret = array();
$comment = new LibertyComment();
while ($res = $result->fetchRow()) {
if ($this->getPreference('allow_comments', null, $res['content_id']) == 'y') {
$res['num_comments'] = $comment->getNumComments($res['content_id']);
}
$res['display_url'] = $this->getDisplayUrl(NULL, $res);
$ret[] = $res;
}
$pParamHash["data"] = $ret;
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
LibertyContent::postGetList($pParamHash);
return $pParamHash;
}
/**
* prepare ListHash to ensure errorfree usage
**/
public static function prepGetList(&$pListHash)
{
$pListHash['include_data'] = TRUE;
if (!empty($pListHash['focus_date'])) {
$calDates = $this->doRangeCalculations($pListHash);
$pListHash['time_limit_start'] = $calDates['view_start'] - $this->display_offset;
$pListHash['time_limit_stop'] = $calDates['view_end'] - $this->display_offset;
}
if (empty($pListHash['sort_mode'])) {
$pListHash['sort_mode'] = !empty($_REQUEST['sort_mode']) ? $_REQUEST['sort_mode'] : 'event_time_asc';
}
$pListHash['time_limit_column'] = preg_replace("/(_asc\$|_desc\$)/i", "", $pListHash['sort_mode']);
if (empty($pListHash['user_id'])) {
$pListHash['user_id'] = !empty($_REQUEST['user_id']) ? $_REQUEST['user_id'] : NULL;
}
if (!empty($_REQUEST['order_table'])) {
$pListHash['order_table'] = $_REQUEST['order_table'];
}
// Don't think this is required.
$pListHash['offset'] = 0;
// There should at least be a preference for this.
$pListHash['max_records'] = 500;
LibertyContent::prepGetList($pListHash);
return TRUE;
}
/**
* 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;
}
/**
* 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 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;
}
function getBreadcrumbLinks($pIncludeSelf = FALSE)
{
global $gBitSystem;
//$ret['fisheye'] = $gBitSystem->getConfig('site_title');
$ret = array();
if (!$this->getField('gallery_path')) {
if ($this->isValid() && ($parents = $this->getParentGalleries())) {
$gal = current($parents);
$this->setGalleryPath('/' . $gal['gallery_id']);
}
}
if ($this->getField('gallery_path')) {
$path = explode('/', ltrim($this->getField('gallery_path'), '/'));
$p = 0;
$c = 1;
$joinSql = '';
$selectSql = '';
//AS title$g, fg$g.gallery_id AS gallery_id$g";
$whereSql = '';
$bindVars = array();
// We need to get min_content_status_id
$pListHash = array();
LibertyContent::prepGetList($pListHash);
foreach ($path as $galleryId) {
if ($galleryId) {
$p++;
$c++;
$selectSql .= " lc{$p}.`title` AS `title{$p}`, fg{$p}.`gallery_id` AS `gallery_id{$p}`,";
$joinSql .= " `" . BIT_DB_PREFIX . "fisheye_gallery_image_map` fgim{$p}\n\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc{$p} ON(fgim{$p}.`gallery_content_id`=lc{$p}.`content_id`)\n\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "fisheye_gallery` fg{$p} ON(fg{$p}.`content_id`=lc{$p}.`content_id`),";
$whereSql .= " fg{$p}.`gallery_id`=? AND fgim{$p}.`item_content_id`=lc{$c}.`content_id` AND lc{$p}.`content_status_id` > ? AND";
array_push($bindVars, $galleryId);
array_push($bindVars, $pListHash['min_content_status_id']);
}
}
// $selectSql .= " lc$c.title AS title$c ";//AS title$g, fg$g.gallery_id AS gallery_id$g";
$joinSql .= " `" . BIT_DB_PREFIX . "fisheye_gallery_image_map` fgim{$c}\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc{$c} ON(fgim{$c}.`item_content_id`=lc{$c}.`content_id`) ";
$whereSql .= " lc{$c}.`content_id`=? AND fgim{$c}.`gallery_content_id`=lc{$p}.`content_id` ";
array_push($bindVars, $this->mContentId);
$rs = $this->mDb->query("SELECT " . rtrim($selectSql, ',') . " FROM " . rtrim($joinSql, ',') . " WHERE {$whereSql}", $bindVars);
if (!empty($rs->fields)) {
for ($i = 1; $i <= count($rs->fields) / 2; $i++) {
$ret[$rs->fields['gallery_id' . $i]] = $rs->fields['title' . $i];
}
}
}
if ($this->isValid() && $pIncludeSelf) {
$ret[$this->mGalleryId] = $this->getTitle();
}
return $ret;
}
