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