public function isValid($value, $context = null) { $result = true; $this->_value = $value; $whereAnd[] = $this->dbAdapter->quoteInto("ti.box_id = ? ", $this->_data['box_id']); if (isset($this->_data['start_dt']) && $this->_data['start_dt'] != '' && $this->_data['start_dt'] == $value) { $whereOr[] = $this->dbAdapter->quoteInto("(ti.start_dt < ? AND ti.end_dt > ? )", HCMS_Utils_Date::dateLocalToIso($this->_data['start_dt'])); } if (isset($this->_data['end_dt']) && $this->_data['end_dt'] != '' && $this->_data['end_dt'] == $value) { $whereOr[] = $this->dbAdapter->quoteInto("(ti.start_dt < ? AND ti.end_dt > ? )", HCMS_Utils_Date::dateLocalToIso($this->_data['end_dt'])); } if (isset($this->_data['id']) && $this->_data['end_dt'] != '') { $whereAnd[] = $this->dbAdapter->quoteInto("ti.id != ? ", $this->_data['id']); } if (isset($this->_data['end_dt']) && $this->_data['end_dt'] != '' && isset($this->_data['start_dt']) && $this->_data['start_dt'] != '') { $whereOr[] = $this->dbAdapter->quoteInto("(ti.start_dt >= ? AND ", HCMS_Utils_Date::dateLocalToIso($this->_data['start_dt'])) . $this->dbAdapter->quoteInto(" ti.end_dt <= ? )", HCMS_Utils_Date::dateLocalToIso($this->_data['end_dt'])); } $whereOr = implode(' OR ', $whereOr); $whereAnd = implode(' AND ', $whereAnd); $sql = "SELECT COUNT(ti.id) AS doubles\n FROM teaser_item AS ti\n WHERE ({$whereOr} )AND {$whereAnd}"; // echo "\n\n$sql\n\n"; die(); $resultSet = $this->dbAdapter->fetchAll($sql); if (0 == count($resultSet)) { return false; } $row = $resultSet[0]; $result = $row['doubles'] == 0; if (!$result) { $this->_error(self::PERIOD_OCCUPIED); } return $result; }
public function getTableDataDump($tableName, $step = 100) { $sql = ''; if ($this->_read) { $quotedTableName = $this->_read->quoteIdentifier($tableName); $colunms = $this->_read->fetchRow('SELECT * FROM ' . $quotedTableName . ' LIMIT 1'); if ($colunms) { $arrSql = array(); $colunms = array_keys($colunms); $quote = $this->_read->getQuoteIdentifierSymbol(); $sql = 'INSERT INTO ' . $quotedTableName . ' (' . $quote . implode($quote . ', ' . $quote, $colunms) . $quote . ')'; $sql .= ' VALUES '; $startRow = 0; $select = $this->_read->select(); $select->from($tableName)->limit($step, $startRow); while ($data = $this->_read->fetchAll($select)) { $dataSql = array(); foreach ($data as $row) { $dataSql[] = $this->_read->quoteInto('(?)', $row); } $arrSql[] = $sql . implode(', ', $dataSql) . ';'; $startRow += $step; $select->limit($step, $startRow); } $sql = implode("\n", $arrSql) . "\n"; } } return $sql; }
public function isValid($value, $context = null) { $result = true; $this->_value = $value; $where = array(); $where[] = $this->dbAdapter->quoteInto("(p.url_id = ? OR pt.url_id = ?)", $value); if (isset($this->_data['id'])) { $where[] = $this->dbAdapter->quoteInto("(p.id <> ?)", $this->_data['id']); } if (isset($this->_data['type_id'])) { $where[] = $this->dbAdapter->quoteInto("(p.type_id = ?)", $this->_data['type_id']); } $where = implode(' AND ', $where); $sql = "SELECT COUNT(p.id) AS doubles\n FROM cms_page AS p\n LEFT JOIN cms_page_tr AS pt ON (p.id = pt.translation_id AND pt.language = '" . $this->_lang . "')\n WHERE {$where}"; //echo "\n\n$sql\n\n"; die(); $resultSet = $this->dbAdapter->fetchAll($sql); if (0 == count($resultSet)) { return false; } $row = $resultSet[0]; $result = $row['doubles'] == 0; if (!$result) { $this->_error(self::URLID_EXISTS); } return $result; }
public function load($printQuery = false, $logQuery = false) { $this->_select = $this->_read->select(); $entityTable = $this->getEntity()->getEntityTable(); $paidTable = $this->getAttribute('grand_total')->getBackend()->getTable(); $idField = $this->getEntity()->getIdFieldName(); $this->getSelect()->from(array('sales' => $entityTable), array('store_id', 'lifetime' => 'sum(sales.base_grand_total)', 'avgsale' => 'avg(sales.base_grand_total)', 'num_orders' => 'count(sales.base_grand_total)'))->where('sales.entity_type_id=?', $this->getEntity()->getTypeId())->group('sales.store_id'); if ($this->_customer instanceof Mage_Customer_Model_Customer) { $this->getSelect()->where('sales.customer_id=?', $this->_customer->getId()); } $this->printLogQuery($printQuery, $logQuery); try { $values = $this->_read->fetchAll($this->getSelect()->__toString()); } catch (Exception $e) { $this->printLogQuery(true, true, $this->getSelect()->__toString()); throw $e; } $stores = Mage::getResourceModel('core/store_collection')->setWithoutDefaultFilter()->load()->toOptionHash(); if (!empty($values)) { foreach ($values as $v) { $obj = new Varien_Object($v); $storeName = isset($stores[$obj->getStoreId()]) ? $stores[$obj->getStoreId()] : null; $this->_items[$v['store_id']] = $obj; $this->_items[$v['store_id']]->setStoreName($storeName); $this->_items[$v['store_id']]->setAvgNormalized($obj->getAvgsale() * $obj->getNumOrders()); foreach ($this->_totals as $key => $value) { $this->_totals[$key] += $obj->getData($key); } } if ($this->_totals['num_orders']) { $this->_totals['avgsale'] = $this->_totals['lifetime'] / $this->_totals['num_orders']; } } return $this; }
/** * Run all sql scripts from scripts/dbupdates * * @return mixed */ public function dbupdAction() { $console = $this->getConsoleOptions(array('simulation|s=s' => 'Just simulate yes|no')); $simulation = $console->getOption("simulation") != 'no'; if ($simulation) { $this->writeLn('RUNNING in SIMULATION mode', null, 'yellow'); } else { $this->writeLn('RUNNING in LIVE mode', null, 'green'); } $appOptions = $this->getInvokeArg('bootstrap')->getOptions(); $dir = APPLICATION_PATH . '/../scripts/dbupdates'; //get files $sqlFiles = glob($dir . '/*.sql', GLOB_MARK); array_walk($sqlFiles, function (&$item) { $item = basename($item); }); usort($sqlFiles, function ($a, $b) { $al = strtolower($a); $bl = strtolower($b); if ($al == $bl) { return 0; } return $al > $bl ? +1 : -1; }); $sql = 'SELECT * FROM upgrade_db_log'; try { $result = $this->db->fetchAll($sql); foreach ($result as $row) { $file = trim($row['file']); $index = array_search($file, $sqlFiles); if ($index !== FALSE) { unset($sqlFiles[$index]); } } } catch (Exception $exc) { if ($exc->getCode() != 42) { $this->writeLn($exc->getMessage(), null, 'red'); exit(1); } } //check if any files if (!count($sqlFiles)) { $this->writeLn('No files for update', 'yellow'); exit(0); } $this->writeLn('Pending SQL files:', null, 'green'); foreach ($sqlFiles as $sqlFile) { $this->writeLn($sqlFile, 'green'); } if (!$simulation) { //get files foreach ($sqlFiles as $sqlFile) { $this->executeQuery($sqlFile, $dir); } } else { $this->writeLn('Please run [composer dbup-exec] to execute scripts.', 'yellow'); } exit(0); }
/** * Get all migrated version numbers * * @return array */ public function fetchAll() { $select = $this->adapter->select(); $select->from($this->tableName, 'version'); $select->order('version ASC'); $all = $this->adapter->fetchAll($select); return array_map(function ($v) { return $v['version']; }, $all); }
/** * Adds all jobs to the queue */ public function execute() { $sql = "SELECT gcj_id_job\n FROM gems__comm_jobs\n WHERE gcj_active = 1\n ORDER BY gcj_id_order, \n CASE WHEN gcj_id_survey IS NULL THEN 1 ELSE 0 END,\n CASE WHEN gcj_round_description IS NULL THEN 1 ELSE 0 END,\n CASE WHEN gcj_id_track IS NULL THEN 1 ELSE 0 END,\n CASE WHEN gcj_id_organization IS NULL THEN 1 ELSE 0 END"; $jobs = $this->db->fetchAll($sql); if ($jobs) { $batch = $this->getBatch(); foreach ($jobs as $job) { $batch->addTask('Mail\\ExecuteMailJobTask', $job['gcj_id_job']); } } else { $this->getBatch()->addMessage($this->_('Nothing to do, please create a mail job first.')); } }
public function load($reviewId) { $select = $this->_read->select(); $select->from($this->_reviewTable)->join($this->_reviewDetailTable, "{$this->_reviewTable}.review_id = {$this->_reviewDetailTable}.review_id")->where("{$this->_reviewTable}.review_id = ?", $reviewId); $data = $this->_read->fetchRow($select); $data['stores'] = array(); $storesSelect = $this->_read->select(); $storesSelect->from($this->_reviewStoreTable)->where('review_id=?', $reviewId); $stores = $this->_read->fetchAll($storesSelect); foreach ($stores as $store) { $data['stores'][] = $store['store_id']; } return $data; }
public function loadEnsuredNodes($category, $rootNode) { $pathIds = $category->getPathIds(); $rootNodeId = $rootNode->getId(); $rootNodePath = $rootNode->getData($this->_pathField); $select = clone $this->_select; // $select->order($this->_table.'.'.$this->_orderField . ' ASC'); if ($pathIds) { $condition = $this->_conn->quoteInto("{$this->_table}.{$this->_idField} in (?)", $pathIds); $select->where($condition); } $select->joinLeft(array('t6' => 'catalog_category_entity_varchar'), '(catalog_category_entity.entity_id = t6.entity_id and t6.attribute_id = 111)', array('t6.value as name')); $select->order('' . $this->_orderField . ' ASC'); // print_r((string)$select);die; $arrNodes = $this->_conn->fetchAll($select); if ($arrNodes) { $childrenItems = array(); foreach ($arrNodes as $nodeInfo) { $nodeId = $nodeInfo[$this->_idField]; if ($nodeId <= $rootNodeId) { continue; } $pathToParent = explode('/', $nodeInfo[$this->_pathField]); array_pop($pathToParent); $pathToParent = implode('/', $pathToParent); $childrenItems[$pathToParent][] = $nodeInfo; } $this->_addChildNodes($childrenItems, $rootNodePath, $rootNode, true); } }
public function getChildren($ID, $start_level = 0, $end_level = 0) { try { $info = $this->getNodeInfo($ID); } catch (Exception $e) { echo $e->getMessage(); exit; } $dbSelect = new Zend_Db_Select($this->_db); $dbSelect->from($this->_table)->where($this->_left . ' >= :left')->where($this->_right . ' <= :right')->order($this->_left); $this->_addExtTablesToSelect($dbSelect); $data = array(); $data['left'] = $info[$this->_left]; $data['right'] = $info[$this->_right]; if (!empty($start_level) && empty($end_level)) { $dbSelect->where($this->_level . ' = :minLevel'); $data['minLevel'] = $info[$this->_level] + $start_level; } //echo $dbSelect->__toString(); $data = $this->_db->fetchAll($dbSelect, $data); $nodeSet = new Varien_Db_Tree_NodeSet(); foreach ($data as $node) { $nodeSet->addNode(new Varien_Db_Tree_Node($node, $this->getKeys())); } return $nodeSet; }
public function addConfigField($path, $label, array $data = array(), $default = null) { $data['level'] = sizeof(explode('/', $path)); $data['path'] = $path; $data['frontend_label'] = $label; if ($id = $this->getTableRow('core/config_field', 'path', $path, 'field_id')) { $this->updateTableRow('core/config_field', 'field_id', $id, $data); } else { if (empty($data['sort_order'])) { $sql = "select max(sort_order) cnt from " . $this->getTable('core/config_field') . " where level=" . ($data['level'] + 1); if ($data['level'] > 1) { $sql .= $this->_conn->quoteInto(" and path like ?", dirname($path) . '/%'); } $result = $this->_conn->raw_fetchRow($sql); $this->_conn->fetchAll($sql); #print_r($result); die; $data['sort_order'] = $result['cnt'] + 1; /* // Triggers "Command out of sync" mysql error for next statement!?!? $data['sort_order'] = $this->_conn->fetchOne("select max(sort_order) from ".$this->getTable('core/config_field')." where level=?".$parentWhere, $data['level'])+1; */ } #$this->_conn->raw_query("insert into ".$this->getTable('core/config_field')." (".join(',', array_keys($data)).") values ('".join("','", array_values($data))."')"); $this->_conn->insert($this->getTable('core/config_field'), $data); } if (!is_null($default)) { $this->setConfigData($path, $default); } return $this; }
/** * Load tree * * @param int|Varien_Data_Tree_Node $parentNode * @return Varien_Data_Tree_Dbp */ public function load($parentNode = null) { $parentPath = ''; if ($parentNode instanceof Varien_Data_Tree_Node) { $parentPath = $parentNode->getData($this->_pathField); } elseif (is_numeric($parentNode)) { $parentNode = null; $select = $this->_conn->select(); $select->from($this->_table, $this->_pathField)->where("{$this->_idField} = ?", $parentNode); $parentPath = $this->_conn->fetchOne($select); } elseif (is_string($parentNode)) { $parentNode = null; $parentPath = $parentNode; } $select = clone $this->_select; $select->order($this->_table . '.' . $this->_orderField . ' ASC'); if ($parentPath) { $condition = $this->_conn->quoteInto("{$this->_table}.{$this->_pathField} like ?", "{$parentPath}/%"); $select->where($condition); } $arrNodes = $this->_conn->fetchAll($select); $childrenItems = array(); foreach ($arrNodes as $nodeInfo) { $pathToParent = explode('/', $nodeInfo[$this->_pathField]); array_pop($pathToParent); $pathToParent = implode('/', $pathToParent); $childrenItems[$pathToParent][] = $nodeInfo; } $this->addChildNodes($childrenItems, $parentPath, $parentNode); return $this; }
/** * Resolve credentials * * Only the first matching username/realm combination in the file is * returned. If the file contains credentials for Digest authentication, * the returned string is the password hash, or h(a1) from RFC 2617. The * returned string is the plain-text password for Basic authentication. * * The expected format of the file is: * username:realm:sharedSecret * * That is, each line consists of the user's username, the applicable * authentication realm, and the password or hash, each delimited by * colons. * * @param string $username Username * @param string $realm Authentication Realm * @throws Zend_Auth_Adapter_Http_Resolver_Exception * @return string|false User's shared secret, if the user is found in the * realm, false otherwise. */ public function resolve($username, $realm) { $exception = null; if ($this->_tableName == '') { $exception = 'A table must be supplied for the Zend_Auth_Adapter_DbTable authentication adapter.'; } elseif ($this->_identityColumn == '') { $exception = 'An identity column must be supplied for the Zend_Auth_Adapter_DbTable authentication adapter.'; } elseif ($this->_credentialColumn == '') { $exception = 'A credential column must be supplied for the Zend_Auth_Adapter_DbTable authentication adapter.'; } if (null !== $exception) { throw new Zend_Auth_Adapter_Http_Resolver_Exception($exception); } // create result array $authResult = array('code' => Zend_Auth_Result::FAILURE, 'identity' => $username, 'messages' => array()); // get select $select = $this->_zendDb->select(); $select->from($this->_tableName, array('credential' => $this->_credentialColumn))->where($this->_zendDb->quoteIdentifier($this->_identityColumn) . ' = ?', $username); // query for the identity try { $resultIdentities = $this->_zendDb->fetchAll($select->__toString()); } catch (Exception $e) { /** * @see Zend_Auth_Adapter_Exception */ require_once 'Zend/Auth/Adapter/Exception.php'; throw new Zend_Auth_Adapter_Exception('The supplied parameters to Zend_Auth_Adapter_DbTable failed to ' . 'produce a valid sql statement, please check table and column names ' . 'for validity.'); } if (count($resultIdentities) != 1) { return false; } $resultIdentity = $resultIdentities[0]; return $resultIdentity['credential']; }
/** * Creates all tokens that should exist, but do not exist * * NOTE: When overruling this function you should not create tokens because they * were deleted by the user * * @param \Gems_Tracker_RespondentTrack $respTrack The respondent track to check * @param int $userId Id of the user who takes the action (for logging) * @return int The number of tokens created by this code */ protected function addNewTokens(\Gems_Tracker_RespondentTrack $respTrack, $userId) { $orgId = $respTrack->getOrganizationId(); $respId = $respTrack->getRespondentId(); $respTrackId = $respTrack->getRespondentTrackId(); // $this->t $sql = "SELECT gro_id_round, gro_id_survey, gro_id_order, gro_icon_file, gro_round_description\n FROM gems__rounds\n WHERE gro_id_track = ? AND\n gro_active = 1 AND\n gro_id_round NOT IN (SELECT gto_id_round FROM gems__tokens WHERE gto_id_respondent_track = ?) AND\n (gro_organizations IS NULL OR gro_organizations LIKE CONCAT('%|',?,'|%'))\n ORDER BY gro_id_order"; $newRounds = $this->db->fetchAll($sql, array($this->_trackId, $respTrackId, $orgId)); $this->db->beginTransaction(); foreach ($newRounds as $round) { $values = array(); // From the respondent track $values['gto_id_respondent_track'] = $respTrackId; $values['gto_id_respondent'] = $respId; $values['gto_id_organization'] = $orgId; $values['gto_id_track'] = $this->_trackId; // From the rounds $values['gto_id_round'] = $round['gro_id_round']; $values['gto_id_survey'] = $round['gro_id_survey']; $values['gto_round_order'] = $round['gro_id_order']; $values['gto_icon_file'] = $round['gro_icon_file']; $values['gto_round_description'] = $round['gro_round_description']; // All other values are not changed by this query and get the default DB value on insertion $this->tracker->createToken($values, $userId); } $this->db->commit(); return count($newRounds); }
public function loadEnsuredNodes($category, $rootNode) { $pathIds = $category->getPathIds(); $rootNodeId = $rootNode->getId(); $rootNodePath = $rootNode->getData($this->_pathField); $select = clone $this->_select; $select->order($this->_table . '.' . $this->_orderField . ' ASC'); if ($pathIds) { $condition = $this->_conn->quoteInto("{$this->_table}.{$this->_idField} in (?)", $pathIds); $select->where($condition); } $arrNodes = $this->_conn->fetchAll($select); if ($arrNodes) { $childrenItems = array(); foreach ($arrNodes as $nodeInfo) { $nodeId = $nodeInfo[$this->_idField]; if ($nodeId <= $rootNodeId) { continue; } $pathToParent = explode('/', $nodeInfo[$this->_pathField]); array_pop($pathToParent); $pathToParent = implode('/', $pathToParent); $childrenItems[$pathToParent][] = $nodeInfo; } $this->_addChildNodes($childrenItems, $rootNodePath, $rootNode, true); } }
public function loadGroupByArrayValues($fieldnames, $condition, $countValues = false) { $upperlimit = $fieldnames['upperlimit']; $lowerlimit = $fieldnames['lowerlimit']; $fields = $upperlimit . ',' . $lowerlimit; if ($condition) { $condition = "WHERE " . $condition; } if ($countValues) { if ($this->model->getVariantMode() == OnlineShop_Framework_IProductList::VARIANT_MODE_INCLUDE_PARENT_OBJECT) { $query = "SELECT TRIM(`{$upperlimit}`) as `upperlimit`,TRIM(`{$lowerlimit}`) as `lowerlimit`, count(DISTINCT o_virtualProductId) as `count` FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition . " GROUP BY " . $fields . ""; } else { $query = "SELECT TRIM(`{$upperlimit}`) as `upperlimit`,TRIM(`{$lowerlimit}`) as `lowerlimit`, count(DISTINCT o_virtualProductId) as `count` FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition . " GROUP BY " . $fields . ""; } OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO); $result = $this->db->fetchAll($query); OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO); return $result; } else { $query = "SELECT " . $this->db->quoteIdentifier($fields) . " FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition . " GROUP BY " . $this->db->quoteIdentifier($fields); OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO); $result = $this->db->fetchCol($query); OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO); return $result; } }
/** * Get a list of records * @param array $params - optional parameters array('start'=>0,'limit'=>10,'sort'=>'fieldname','dir'=>'DESC') * @param array $filters - optional filters (where) the key - the field name, value * @param array $fields - optional list of fields to retrieve * @param boolean $useCache - use hard cache * @param string $query - optional string for search (since 0.9) * it is necessary to remember that hard cache gets invalidated only at the end of its life cycle (configs / main.php), * is used in case update triggers can’t be applied */ public function getList($params = false, $filters = false, $fields = '*', $useCache = false, $query = false) { $data = false; if ($useCache && $this->_cache) { $cacheKey = $this->getCacheKey(array('list', serialize(func_get_args()))); $data = $this->_cache->load($cacheKey); } if ($data === false) { $sql = $this->_dbSlave->select()->from($this->table(), $fields); if (is_array($filters) && !empty($filters)) { $this->queryAddFilters($sql, $this->_cleanFilters($filters)); } if ($params) { static::queryAddPagerParams($sql, $params); } if ($query && strlen($query)) { $this->_queryAddQuery($sql, $query); } $data = $this->_dbSlave->fetchAll($sql); if (!$data) { $data = array(); } if ($useCache && $this->_cache) { $this->_cache->save($data, $cacheKey, $this->_cacheTime); } } return $data; }
/** * Utility function for loading a query from cache * * @param string $cacheId The class is prepended to this id * @param mixed $sql string or \Zend_Db_Select * @param callable $function The function called with each row to form the result * @param string $keyField The field containing the key for each row * @param mixed $tags string or array of strings * @param string Optional function to sort on, only known functions will do * @return array */ protected function _getSelectProcessedCached($cacheId, $sql, $function, $keyField, $tags = array(), $sort = null) { $cacheId = get_class($this) . '_' . $cacheId; $result = false; //$this->cache->load($cacheId); if ($result) { return $result; } $result = array(); try { $rows = $this->db->fetchAll($sql); if ($rows) { foreach ($rows as $row) { if (!isset($result[$row[$keyField]])) { $result[$row[$keyField]] = call_user_func($function, $row); } } if ($sort) { $this->_sortResult($result, $sort); } } $this->cache->save($result, $cacheId, (array) $tags); } catch (\Zend_Db_Statement_Mysqli_Exception $e) { } return $result; }
/** * converts category to tag * * @param int $catId * @return string tagid */ public function getTag($catId) { if (!(isset($this->_tagMapCache[$catId]) || array_key_exists($catId, $this->_tagMapCache))) { $select = $this->_egwDb->select()->from(array('cats' => 'egw_categories'))->where($this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('cat_id') . ' = ?', $catId)); $cat = $this->_egwDb->fetchAll($select, NULL, Zend_Db::FETCH_ASSOC); $cat = count($cat) === 1 ? $cat[0] : NULL; if (!$cat) { $this->_log->DEBUG(__METHOD__ . '::' . __LINE__ . " category {$catId} not found in egw, skipping tag"); return $this->_tagMapCache[$catId] = NULL; } $tineDb = Tinebase_Core::getDb(); $select = $tineDb->select()->from(array('tags' => $tineDb->table_prefix . 'tags'))->where($tineDb->quoteInto($tineDb->quoteIdentifier('name') . ' LIKE ?', $cat['cat_name'])); $tag = $tineDb->fetchAll($select, NULL, Zend_Db::FETCH_ASSOC); $tag = count($tag) > 0 ? $tag[0] : NULL; if ($tag) { return $this->_tagMapCache[$catId] = $tag['id']; } // create tag $catData = unserialize($cat['cat_data']); $tagId = Tinebase_Record_Abstract::generateUID(); $tagType = $cat['cat_access'] == 'public' ? Tinebase_Model_Tag::TYPE_SHARED : Tinebase_Model_Tag::TYPE_PERSONAL; $tagOwner = $tagType == Tinebase_Model_Tag::TYPE_SHARED ? 0 : $this->mapAccountIdEgw2Tine($cat['cat_owner']); $this->_log->NOTICE(__METHOD__ . '::' . __LINE__ . " creating new {$tagType} tag '{$cat['cat_name']}'"); $tineDb->insert($tineDb->table_prefix . 'tags', array('id' => $tagId, 'type' => $tagType, 'owner' => $tagOwner, 'name' => $cat['cat_name'], 'description' => $cat['cat_description'], 'color' => $catData['color'], 'created_by' => $tagOwner ? $tagOwner : Tinebase_Core::getUser()->getId(), 'creation_time' => $cat['last_mod'] ? $this->convertDate($cat['last_mod']) : Tinebase_DateTime::now())); $right = new Tinebase_Model_TagRight(array('tag_id' => $tagId, 'account_type' => $tagType == Tinebase_Model_Tag::TYPE_SHARED ? Tinebase_Acl_Rights::ACCOUNT_TYPE_ANYONE : Tinebase_Acl_Rights::ACCOUNT_TYPE_USER, 'account_id' => $tagOwner, 'view_right' => true, 'use_right' => true)); Tinebase_Tags::getInstance()->setRights($right); Tinebase_Tags::getInstance()->setContexts(array(0), $tagId); $this->_tagMapCache[$catId] = $tagId; } return $this->_tagMapCache[$catId]; }
/** * returns grants by owner * * eGW has owner based grants whereas Tine 2.0 has container based grants. * this class reads the egw owner grants and converts them into Tine 2.0 grants * attacheable to a tine 2.0 container * * @param string $_application * @param string $_accountId * @return Tinebase_Record_RecordSet of Tinebase_Model_Grant * @throws Tinebase_Exception_NotFound */ public function getGrantsByOwner($_application, $_accountId) { $egwAccountId = $this->mapAccountIdTine2Egw($_accountId); $acl_account = array($egwAccountId); if ($egwAccountId > 0) { $user = Tinebase_User::getInstance()->getUserById($_accountId); $groupIds = $user->getGroupMemberships(); foreach ($groupIds as $groupId) { $acl_account[] = '-' . $this->mapAccountIdTine2Egw($groupId, 'Group'); } } $select = $this->_egwDb->select()->from(array('grants' => 'egw_acl'))->where($this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('acl_appname') . ' = ?', $_application))->where($this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('acl_account') . ' IN (?)', $acl_account)); $egwGrantDatas = $this->_egwDb->fetchAll($select, NULL, Zend_Db::FETCH_ASSOC); //print_r($egwGrantDatas); // in a first run we merge grants from different sources $effectiveGrants = array(); if ($egwAccountId > 0) { // owner has implicitly all grants in egw $effectiveGrants[$egwAccountId] = 15; } foreach ($egwGrantDatas as $egwGrantData) { // grants are int != 0 if ((int) $egwGrantData['acl_location'] == 0) { continue; } // NOTE: The grant source is not resolveable in Tine 2.0! // In Tine 2.0 grants are directly given to a container $grantsSource = $egwGrantData['acl_account']; $grantsDestination = $egwGrantData['acl_location']; $grantsGiven = $egwGrantData['acl_rights']; if (!array_key_exists($grantsDestination, $effectiveGrants)) { $effectiveGrants[$grantsDestination] = 0; } $effectiveGrants[$grantsDestination] |= $grantsGiven; } //print_r($effectiveGrants); // convert to tine grants $tineGrants = new Tinebase_Record_RecordSet('Tinebase_Model_Grants'); foreach ($effectiveGrants as $grantAccount => $egwGrants) { $tineGrant = new Tinebase_Model_Grants(array('account_id' => $this->mapAccountIdEgw2Tine($grantAccount), 'account_type' => (int) $grantAccount > 0 ? Tinebase_Acl_Rights::ACCOUNT_TYPE_USER : Tinebase_Acl_Rights::ACCOUNT_TYPE_GROUP)); foreach ($this->_grantMap as $egwGrant => $tineGrantString) { $tineGrant->{$tineGrantString} = (bool) ($egwGrants & $egwGrant); } // the owner also gets admin grants if ($egwAccountId > 0 && $grantAccount == $egwAccountId) { $tineGrant->{Tinebase_Model_Grants::GRANT_ADMIN} = TRUE; } $tineGrants->addRecord($tineGrant); } //print_r($tineGrants->toArray()); // for group owners (e.g. group addressbooks) we need an container admin if ($egwAccountId < 0) { $adminGroup = Tinebase_Group::getInstance()->getDefaultAdminGroup(); $tineGrant = new Tinebase_Model_Grants(array('account_id' => $this->mapAccountIdEgw2Tine($_accountId), 'account_type' => Tinebase_Acl_Rights::ACCOUNT_TYPE_GROUP)); $tineGrant->{Tinebase_Model_Grants::GRANT_ADMIN} = TRUE; $tineGrants->addRecord($tineGrant); } return $tineGrants; }
/** * Возвращает список разделов (полных цепочек), привязанных к определенному модулю * * @param string $moduleid Id модуля (если модуль не задан, будут выбраны все разделы) * @return array */ public function getCategoriesPathsByModule($moduleid = null) { $select = $this->_db->select()->distinct()->from(array('AA' => 'pw_categories'))->from(array('BB' => 'pw_categories'), array())->where('BB.cleft BETWEEN AA.cleft AND AA.cright')->order('AA.cleft'); if (!is_null($moduleid)) { $select->where('BB.moduleid=?', $moduleid); } return $this->_db->fetchAll($select); }
/** * Defined by Zend_Validate_Interface * * Returns true if and only if $value is an available username * * @param string $value * @throws Zend_Validate_Exception if there is a fatal error * @return boolean */ public function isValid($value) { $this->_setValue($value); $dbSelect = $this->_zendDb->select(); $dbSelect->from($this->_tableName); $dbSelect->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' = ?', $value); $users = $this->_zendDb->fetchAll($dbSelect->__toString()); if (count($users)) { $this->_error(); return false; } if (in_array($value, array('storytlr', 'mail', 'search', 'support', 'feedback', 'admin', 'info', 'host', 'contact', 'root', 'webmaster', 'dns', 'f**k', 'suck', 'forum', 'wiki', 'bugs', 'beta', 'user', 'users', 'username'))) { $this->_error(); return false; } return true; }
/** * 查询记录 * * @param string $where * @return array */ protected function _query($where) { $order = $this->_getOrder(); $sql = "SELECT {$this->_columns} FROM {$this->_table} {$where} {$order}"; $offset = $this->_pageSize * ($this->_currentPage - 1); $sql = $this->_db->limit($sql, $this->_pageSize, $offset); return $this->_db->fetchAll($sql); }
/** * Test the Adapter's fetchAll() method. */ public function testAdapterFetchAll() { $id = $this->getResultSetKey('id'); $table = $this->getIdentifier(self::TABLE_NAME); $result = $this->_db->fetchAll('SELECT * FROM ' . $this->_db->quoteIdentifier($table) . ' WHERE date_created > ? ORDER BY id ASC', array('2006-01-01')); $this->assertEquals(2, count($result)); $this->assertEquals('1', $result[0][$id]); }
/** * authenticate() - defined by Zend_Auth_Adapter_Interface. * * @throws Zend_Auth_Adapter_Exception if answering the authentication query is impossible * @return Zend_Auth_Result */ public function authenticate() { $exception = null; if ($this->_tableName == '') { $exception = 'A table must be supplied authentication adapter.'; } elseif ($this->_identityColumn == '') { $exception = 'A table column must be supplied for the identity.'; } elseif ($this->_identity == '') { $exception = 'A value for the identity must be provided to authenticate.'; } elseif ($this->_credentialColumn == '') { $exception = 'A credential column must be supplied to autheticate against.'; } elseif ($this->_credential === null) { $exception = 'A credential value must be provided to authenticate.'; } if (null !== $exception) { /** * @see Zend_Auth_Adapter_Exception */ require_once 'Zend/Auth/Adapter/Exception.php'; throw new Zend_Auth_Adapter_Exception($exception); } // create result array $authResult = array('isValid' => false, 'identity' => $this->_identity, 'messages' => array()); // build credential expression if (empty($this->_credentialTreatment) || strpos($this->_credentialTreatment, "?") === false) { $this->_credentialTreatment = '?'; } $credentialExpression = new Zend_Db_Expr($this->_zendDb->quoteInto($this->_zendDb->quoteIdentifier($this->_credentialColumn) . ' = ' . $this->_credentialTreatment, $this->_credential) . ' AS zend_auth_credential_match'); // get select $dbSelect = $this->_zendDb->select(); $dbSelect->from($this->_tableName, array('*', $credentialExpression))->where($this->_zendDb->quoteIdentifier($this->_identityColumn) . ' = ?', $this->_identity); // query for the identity try { $resultIdentities = $this->_zendDb->fetchAll($dbSelect->__toString()); } catch (Exception $e) { /** * @see Zend_Auth_Adapter_Exception */ require_once 'Zend/Auth/Adapter/Exception.php'; throw new Zend_Auth_Adapter_Exception($e->getMessage()); } if (count($resultIdentities) < 1) { $authResult['messages'][] = 'A record with the supplied identity could not be found.'; return new Zend_Auth_Result($authResult['isValid'], $authResult['identity'], $authResult['messages']); } elseif (count($resultIdentities) > 1) { $authResult['messages'][] = 'More than one record matches the supplied identity.'; return new Zend_Auth_Result($authResult['isValid'], $authResult['identity'], $authResult['messages']); } $resultIdentity = $resultIdentities[0]; if ($resultIdentity['zend_auth_credential_match'] != '1') { $authResult['messages'][] = 'Supplied credential is invalid.'; return new Zend_Auth_Result($authResult['isValid'], $authResult['identity'], $authResult['messages']); } unset($resultIdentity['zend_auth_credential_match']); $this->_resultRow = $resultIdentity; $authResult['isValid'] = true; return new Zend_Auth_Result($authResult['isValid'], $authResult['identity'], $authResult['messages']); }
/** * @param string $table * @param bool $cache * @return array|mixed */ protected function getValidTableColumns($table, $cache = true) { $cacheKey = "system_resource_columns_" . $table; if (Zend_Registry::isRegistered($cacheKey)) { $columns = Zend_Registry::get($cacheKey); } else { $columns = Pimcore_Model_Cache::load($cacheKey); if (!$columns || !$cache) { $columns = array(); $data = $this->db->fetchAll("SHOW COLUMNS FROM " . $table); foreach ($data as $d) { $columns[] = $d["Field"]; } Pimcore_Model_Cache::save($columns, $cacheKey, array("system", "resource"), null, 997); } Zend_Registry::set($cacheKey, $columns); } return $columns; }
/** * @param string $table * @param bool $cache * @return array|mixed */ public function getValidTableColumns($table, $cache = true) { $cacheKey = self::CACHEKEY . $table; if (\Zend_Registry::isRegistered($cacheKey)) { $columns = \Zend_Registry::get($cacheKey); } else { $columns = Cache::load($cacheKey); if (!$columns || !$cache) { $columns = []; $data = $this->db->fetchAll("SHOW COLUMNS FROM " . $table); foreach ($data as $d) { $columns[] = $d["Field"]; } Cache::save($columns, $cacheKey, ["system", "resource"], null, 997); } \Zend_Registry::set($cacheKey, $columns); } return $columns; }
/** * add table to DB object * @param $tableName * @param $scheme - table structure from DESCRIBE TABLE query */ public function addTable($tableName, $scheme) { if ($this->isTblWhiteListed($tableName) && !$this->isTblBlackListed($tableName)) { $this->_scheme[$tableName] = $scheme; $this->_indexes[$tableName] = $this->getIndexListFromTable($tableName); if (isset($this->_options['loaddata']) && $this->_options['loaddata'] == true) { $this->_data[$tableName] = $this->_db->fetchAll($this->_db->select()->from($tableName)); } } }
/** * Get the structural patches for the given patch level * @param int $patchLevel * @return array */ public function getStructuralPatches($patchLevel) { $patches = $this->db->fetchAll("SELECT * FROM gems__patches WHERE gpa_level = ? ORDER BY gpa_location, gpa_name, gpa_order", $patchLevel); foreach ($patches as $patchId => $patch) { if (\MUtil_String::startsWith(trim($patch['gpa_sql']), 'INSERT', true) || \MUtil_String::startsWith(trim($patch['gpa_sql']), 'UPDATE', true) || \MUtil_String::startsWith(trim($patch['gpa_sql']), 'DELETE', true)) { unset($patches[$patchId]); } } return $patches; }
/** * Get payments for this invoice. */ public function getPayments() { $tbl_prefix = SimpleInvoices_Db_Table_Abstract::getTablePrefix(); $select = new Zend_Db_Select($this->_db); $select->from($tbl_prefix . 'payment'); $select->joinInner($tbl_prefix . 'invoices', $tbl_prefix . 'payment.ac_inv_id=' . $tbl_prefix . 'invoices.id', NULL); $select->joinInner($tbl_prefix . 'customers', $tbl_prefix . 'customers.id=' . $tbl_prefix . 'invoices.customer_id', array('cname' => $tbl_prefix . 'customers.name')); $select->joinInner($tbl_prefix . 'biller', $tbl_prefix . 'biller.id=' . $tbl_prefix . 'invoices.biller_id', array('bname' => $tbl_prefix . 'biller.name')); $select->where($tbl_prefix . 'payment.ac_inv_id=?', $this->_id); $select->order($tbl_prefix . 'payment.id DESC'); return $this->_db->fetchAll($select); }