/**
* Return a set of ids based on a set of attribute criteria.
*
* @param array $criteria The array of criteria. Example:
* $criteria['OR'] = array(
* array('field' => 'name',
* 'op' => '=',
* 'test' => 'foo'),
* array('field' => 'name',
* 'op' => '=',
* 'test' => 'bar'));
* This would return all ids for which the field
* attribute_name is either 'foo' or 'bar'.
*/
function getByAttributes($criteria)
{
if (!count($criteria)) {
return array();
}
/* Build the query. */
$this->_table_count = 1;
$query = '';
foreach ($criteria as $key => $vals) {
if ($key == 'OR' || $key == 'AND') {
if (!empty($query)) {
$query .= ' ' . $key . ' ';
}
$query .= '(' . $this->_buildAttributeQuery($key, $vals) . ')';
}
}
/* Build the FROM/JOIN clauses. */
$joins = array();
$pairs = array();
for ($i = 1; $i <= $this->_table_count; $i++) {
$joins[] = sprintf('LEFT JOIN %1$s a%2$s ON a%2$s.%3$s = m.%3$s', $this->_params['attribute_table'], $i, $this->_params['id_column']);
$pairs[] = 'AND a1.attribute_name = a' . $i . '.attribute_name';
}
$joins = implode(' ', $joins);
$pairs = implode(' ', $pairs);
$query = sprintf('SELECT DISTINCT a1.%s FROM %s m %s WHERE %s %s', $this->_params['id_column'], $this->_params['primary_table'], $joins, $query, $pairs);
Horde::log('SQL Query by Hylax_SQL_Attributes::getByAttributes(): ' . $query, 'DEBUG');
return $this->_db->getCol($query);
}
/**
* Returns a numerically indexed 1D array of values from the first column
*
* The returned value will be based upon the sql specified by $sql. This is
* basically a wrapper for the
* {@link http://pear.php.net/manual/en/package.database.db.db-common.getcol.php DB_common::getCol}
* method of the Pear DB module.
*
* @param string $sql The SQL code to be executed
* @param array $data If $sql is a paramatised query, then the data for each parameter goes in this array
* @return array
*
*/
public function getColumn($sql, $col = 0, $data = array())
{
$result = $this->conn->getCol($sql, $col, $data);
self::logQuery();
if (DB::isError($result)) {
throw new LoggedException($result->getMessage() . ". SQL: {$sql}", $result->getCode(), self::module);
}
return $result;
}
/**
* Delete a folders contents from the VFS in the SQL database,
* recursively.
*
* @param string $path The path of the folder.
* @param string $name The foldername to use.
*
* @throws Horde_Vfs_Exception
*/
protected function _recursiveSQLDelete($path, $name)
{
$result = $this->_db->query(sprintf('DELETE FROM %s WHERE vfs_type = ? AND vfs_path = ?', $this->_params['table']), array(self::FILE, $this->_getSQLNativePath($path, $name)));
if ($result instanceof PEAR_Error) {
throw new Horde_Vfs_Exception($result->getMessage());
}
$folderList = $this->_db->getCol(sprintf('SELECT vfs_name FROM %s WHERE vfs_type = ? AND vfs_path = ?', $this->_params['table']), 0, array(self::FOLDER, $this->_getSQLNativePath($path, $name)));
foreach ($folderList as $folder) {
$this->_recursiveSQLDelete($this->_getSQLNativePath($path, $name), $folder);
}
$this->_db->query(sprintf('DELETE FROM %s WHERE vfs_type = ? AND vfs_name = ? AND vfs_path = ?', $this->_params['table']), array(self::FOLDER, $name, $path));
}
public function getAccountCodes()
{
/* Make sure we have a valid database connection. */
$this->_connect();
$sql = 'SELECT DISTINCT(accountcode) FROM ' . $this->_params['table'] . ' ORDER BY accountcode';
Horde::log(sprintf('Operator_Driver_asterisksql::getAccountCodes(): %s', $sql), 'DEBUG');
$res = $this->_db->getCol($sql, 'accountcode');
if (is_a($res, 'PEAR_Error')) {
Horde::log($res, 'ERR');
throw new Operator_Exception(_("Internal error. Details have been logged for the administrator."));
}
return $res;
}
/**
* Return a set of tag names given the tag_ids.
*
* @param array $ids An array of tag_ids to get names for.
*
* @return mixed An array of tag names | PEAR_Error.
*/
public function getTagNames($ids)
{
if (empty($ids)) {
return array();
}
$sql = 'SELECT t.tag_name FROM jonah_tags as t WHERE t.tag_id IN (' . implode(',', array_map(function ($v) {
return '?';
}, $ids)) . ')';
$tags = $this->_db->getCol($sql, 0, $ids);
if ($tags instanceof PEAR_Error) {
throw new Jonah_Exception($tags);
}
return $tags;
}
public static function paginator($sql, $klvo = 10, $pnum = 9, $bind_params = [])
{
$page = HTTP::$page;
$layout = $klvo * ($page - 1);
$sql_num = preg_replace('|SELECT(.*?)FROM|s', "SELECT 1 FROM", $sql);
$sql = str_replace(";", "", $sql);
$sql = preg_replace('/LIMIT(.*?)$/Uis', "", $sql);
$sql .= " LIMIT {$layout}, {$klvo};";
$rows = DB::getCol($sql_num, $bind_params);
$num_rows = sizeof($rows);
unset($rows);
$pages = ceil($num_rows / $klvo) + 1;
$url_params = explode("?", $_SERVER['REQUEST_URI']);
$url = preg_replace('/page-(.*?)$/Uis', "", $url_params[0]);
if (strpos($url, '/', strlen($url) - 1)) {
$url = substr($url, 0, -1);
}
$pos = floor($pnum / 2);
//Get pages num before and after current page
if ($page <= $pos) {
//if curent page in the begining, crop last page by pages number
$start_page = 1;
$end_page = $pages < $pnum ? $pages : $pnum;
} else {
$start_page = $page - $pos;
$end_page = $pages < $start_page + $pnum ? $pages : $start_page + $pnum;
}
$tpl = App::get('view');
$tpl->assign('page_items', $num_rows);
$tpl->assign('page_from', $layout + 1);
$tpl->assign('page_to', $layout + $klvo >= $num_rows ? $num_rows : $layout + $klvo);
if ($pages > 2) {
$tpl->assign('page_url', $url);
$tpl->assign('page_url_get', !empty($url_params[1]) ? "?" . $url_params[1] : "");
$tpl->assign('start_page', $start_page);
$tpl->assign('end_page', $end_page);
$tpl->assign('page', $page);
$tpl->assign('pages', 1);
$tpl->assign('pages_num', $pages);
}
return $sql;
}
/**
* Get saved search
*
* @return array saved searches
*/
protected function _getSavedSearch()
{
$query = 'SELECT search_name FROM ' . $this->_params['search'] . ' WHERE user_uid = ?';
return $this->_db->getCol($query, 'search_name', $GLOBALS['registry']->getAuth());
}
/**
*
*/
private static function loadRights($group_id = false)
{
$rights = DB::getCol('SELECT rule FROM users_rules WHERE users_groups_id = ?', [$group_id]);
return $rights;
}
public function testGetCol()
{
$r = DB::getCol('select name from fuck');
$this->assertTrue((bool) $r === true);
}
/**
* Get users who have you on friendlist
*
* @return array users
*/
public function friendOf()
{
$query = 'SELECT user_uid FROM ' . $this->_params['friends'] . ' WHERE friend_uid = ? AND friend_ask = ?' . ' ORDER BY friend_uid ASC';
return $this->_db->getCol($query, 0, array($this->_user, 0));
}
