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