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; }