/**
* Check DB connection
*
* @return bool
*/
public function hasConnection()
{
if (!$this->_read) {
return false;
}
$tables = $this->_read->fetchAssoc('show tables');
if (!isset($tables[$this->_sessionTable])) {
return false;
}
return true;
}
public function loadGroupByRelationValues($fieldname, $condition, $countValues = false)
{
if ($condition) {
$condition = "WHERE " . $condition;
}
if ($countValues) {
if ($this->model->getVariantMode() == OnlineShop_Framework_IProductList::VARIANT_MODE_INCLUDE_PARENT_OBJECT) {
$query = "SELECT dest as `value`, count(DISTINCT src_virtualProductId) as `count` FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
} else {
$query = "SELECT dest as `value`, count(*) as `count` FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
}
$subquery = "SELECT a.o_id FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition;
$query .= " AND src IN (" . $subquery . ") GROUP BY dest";
OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO);
$result = $this->db->fetchAssoc($query);
OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO);
return $result;
} else {
$query = "SELECT dest FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
$subquery = "SELECT a.o_id FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition;
$query .= " AND src IN (" . $subquery . ") GROUP BY dest";
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;
}
}
/**
* @param $sqlRelevanceField
* @param $sqlPriceField
* @param $sqlNameField
* @param $sqlDescriptionField
* @param $sqlFrom
* @param $sqlCategoryFilter
* @param $sqlTranslation
* @param $sqlWhere
* @param $sqlHaving
* @return array
* @throws Enlight_Exception
*/
public function getSearchExecuteQuery($sqlRelevanceField, $sqlPriceField, $sqlNameField, $sqlDescriptionField, $sqlFrom, $sqlCategoryFilter, $sqlTranslation, $sqlWhere, $sqlHaving)
{
$sql = '
SELECT
a.id as `key`, a.id as articleID, ' . $sqlRelevanceField . ' as relevance, ' . $sqlPriceField . ' as price, a.supplierID,
a.datum, d.sales as sales, ' . $sqlNameField . ' as name, ' . $sqlDescriptionField . ' as description, ai.img as image, ai.extension,
IFNULL((SELECT CONCAT(ROUND(AVG(points), 2), \'|\', COUNT(*)) as votes FROM s_articles_vote WHERE active=1 AND articleID=a.id), \'0.00|0\') as vote
' . $sqlFrom . '
JOIN s_articles_details d
ON a.id=d.articleID
AND d.kind=1
JOIN s_categories c
ON c.id=' . $sqlCategoryFilter . '
JOIN s_categories c2
ON c2.active=1
AND c2.left >= c.left
AND c2.right <= c.right
JOIN s_articles_categories ac
ON ac.articleID=a.id
AND ac.categoryID=c2.id
JOIN s_core_tax t
ON a.taxID = t.id
LEFT JOIN s_articles_img AS ai
ON ai.articleID=a.id
AND ai.article_detail_id IS NULL
AND ai.main = 1
' . $sqlTranslation . '
WHERE a.active=1
AND (
SELECT articleID
FROM s_articles_avoid_customergroups
WHERE articleID = a.id AND customergroupID = ' . $this->requestShopCustomerGroupId . '
) IS NULL
' . $sqlWhere . '
' . $sqlHaving . '
ORDER BY (' . $sqlRelevanceField . ') DESC, a.id
';
try {
$result = $this->database->fetchAssoc($sql);
} catch (PDOException $e) {
throw new Enlight_Exception($e->getMessage());
}
return $result;
}
/**
* Test the Adapter's fetchAssoc() method.
*/
public function testAdapterFetchAssoc()
{
$id = $this->getResultSetKey('id');
$table = $this->getIdentifier(self::TABLE_NAME);
$result = $this->_db->fetchAssoc('SELECT * FROM ' . $this->_db->quoteIdentifier($table) . ' WHERE date_created > ? ORDER BY id DESC', array('2006-01-01'));
foreach ($result as $idKey => $row) {
$this->assertEquals($idKey, $row[$id]);
}
}
public function updateOneshot($minutes = 60, $interval = 300)
{
$last_update = $this->_read->fetchOne("SELECT UNIX_TIMESTAMP(MAX(add_date)) FROM {$this->_summaryTable} WHERE type_id IS NULL");
$next_update = $last_update + $interval;
if (time() >= $next_update) {
$stats = $this->_read->fetchAssoc("SELECT\n u.visit_time,\n v.visitor_id,\n c.customer_id,\n ROUND( (UNIX_TIMESTAMP(u.visit_time) - UNIX_TIMESTAMP(" . now() . " - INTERVAL {$minutes} MINUTE )) / {$interval} ) as _diff,\n COUNT(DISTINCT(v.visitor_id)) as visitor_count,\n COUNT(DISTINCT(c.customer_id)) as customer_count\n FROM\n {$this->_urlTable} u\n LEFT JOIN {$this->_visitorTable} v ON(v.visitor_id = u.visitor_id)\n LEFT JOIN {$this->_customerTable} c on(c.visitor_id = v.visitor_id)\n WHERE\n UNIX_TIMESTAMP(u.visit_time) > {$next_update}\n group by _diff");
foreach ($stats as $stat) {
$data = array('type_id' => new Zend_Db_Expr('NULL'), 'visitor_count' => $stat['visitor_count'], 'customer_count' => $stat['customer_count'], 'add_date' => $stat['visit_time']);
$this->_write->insert($this->_summaryTable, $data);
}
}
}
/**
* Load the actions into memory from the database (and cache them)
*/
private function _getActionsDb()
{
try {
$rows = $this->_db->fetchAssoc("SELECT * FROM gems__log_setup ORDER BY gls_name");
} catch (\Exception $exc) {
$rows = array();
$this->_warn();
}
$output = array();
foreach ((array) $rows as $row) {
$output[$row['gls_name']] = $row;
}
// \MUtil_Echo::track($output);
$this->_cache->save($output, $this->_cacheId, array('accesslog_actions'));
return $output;
}
