function getById($jobid)
{
// do Bacula ACLs
Zend_Loader::loadClass('Job');
$table = new Job();
if (!$table->isJobIdExists($jobid)) {
return FALSE;
}
$select = new Zend_Db_Select($this->db);
switch ($this->db_adapter) {
case 'PDO_SQLITE':
// bug http://framework.zend.com/issues/browse/ZF-884
$select->distinct();
$select->from(array('l' => 'Log'), array('logid' => 'LogId', 'jobid' => 'JobId', 'LogTime' => 'Time', 'logtext' => 'LogText'));
$select->where("JobId = ?", $jobid);
$select->order(array('LogId', 'LogTime'));
break;
default:
// mysql, postgresql
$select->distinct();
$select->from(array('l' => 'Log'), array('LogId', 'JobId', 'LogTime' => 'Time', 'LogText'));
$select->where("JobId = ?", $jobid);
$select->order(array('LogId', 'LogTime'));
}
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
$stmt = $select->query();
return $stmt->fetchAll();
}
/**
* sortiert nach Spaltenname der durch den Paginator dargestellten
* Tabelle
*
* @param string $columnName
*/
public function orderBy($columnName)
{
if ($this->isRawcolumn($columnName)) {
$this->select->order($columnName);
}
//ENDE: if()
}
/**
* Render sql select orders
*
* @return Varien_Data_Collection_Db
*/
protected function _renderOrders()
{
foreach ($this->_orders as $orderExpr) {
$this->_select->order($orderExpr);
}
return $this;
}
/**
* Render sql select orders
*
* @return Varien_Data_Collection_Db
*/
protected function _renderOrders()
{
$ordersInSelect = $this->_select->getPart(Zend_Db_Select::ORDER);
foreach ($this->_orders as $orderExpr) {
if (!in_array($orderExpr, $ordersInSelect)) {
$this->_select->order($orderExpr);
}
}
return $this;
}
/**
* Render sql select orders
*
* @return Varien_Data_Collection_Db
*/
protected function _renderOrders()
{
if (!$this->_isOrdersRendered) {
foreach ($this->_orders as $field => $direction) {
$this->_select->order(new Zend_Db_Expr($field . ' ' . $direction));
}
$this->_isOrdersRendered = true;
}
return $this;
}
/**
* @return array $relatedEntries
*/
private function _getRelatedCompaniesLinks()
{
if (!is_null($this->_relatedCache)) {
return $this->_relatedCache;
}
$itemRow = $this->getData()->parent->row;
$categoryDirectory = $this->_getCategoryDirectory();
$connectTableName = Kwc_Abstract::getSetting($categoryDirectory->componentClass, 'categoryToItemModelName');
$linkLimit = $this->_getSetting('linkLimit');
$categoryReference = Kwc_Directories_Category_Detail_List_Component::getTableReferenceData($connectTableName, 'Category');
$categoryIds = array();
$categoryIdsRowset = $itemRow->getRow()->findDependentRowset($connectTableName);
foreach ($categoryIdsRowset as $categoryIdRow) {
$categoryIds[] = $categoryIdRow->{$categoryReference['itemColumn']};
}
if ($categoryIds) {
$itemReference = Kwc_Directories_Category_Detail_List_Component::getTableReferenceData($connectTableName, 'Item');
// anzahl holen
$s = new Zend_Db_Select(Kwf_Registry::get('db'));
$s->from($itemReference['tableName'], $itemReference['itemColumn']);
$s->where($categoryReference['itemColumn'] . ' IN(' . implode(',', $categoryIds) . ')');
$s->order($itemReference['tableName'] . '.id ASC');
$rowset = $s->query()->fetchAll();
// eigene position ermitteln und rauslöschen
$selfPos = 0;
$i = 0;
foreach ($rowset as $k => $row) {
if ($row[$itemReference['itemColumn']] == $itemRow->id) {
if (!$selfPos) {
$selfPos = $i;
}
unset($rowset[$k]);
}
$i++;
}
$rowset = array_values($rowset);
$categoryRowCount = count($rowset);
$itemIds = $ret = array();
if ($categoryRowCount) {
for ($i = 0; $i < $linkLimit; $i++) {
$offset = ($selfPos + floor($categoryRowCount / $linkLimit) * $i) % $categoryRowCount;
$itemIds[] = $rowset[$offset][$itemReference['itemColumn']];
}
$itemDirectory = $this->getData()->parent->parent;
$select = $itemDirectory->getGenerator('detail')->select($itemDirectory);
$select->where('id IN(' . implode(',', $itemIds) . ')');
$ret = $itemDirectory->getChildComponents($select);
}
$this->_relatedCache = $ret;
return $ret;
}
$this->_relatedCache = array();
return array();
}
public function fetchAllUsers($order = 'id')
{
$select = new Zend_Db_Select($this->db);
$select->from(array('user1' => 'webacula_users'), array('id', 'login', 'name', 'email', 'create_login', 'last_login', 'last_ip', 'active', 'role_id'));
$select->joinLeft(array('role1' => 'webacula_roles'), 'user1.role_id = role1.id', array('role_name' => 'name', 'role_id' => 'id'));
if ($order) {
$select->order(array($order . ' ASC'));
}
//$sql = $select->__toString(); var_dump($sql); exit; // for !!!debug!!!
$stmt = $select->query();
$result = $stmt->fetchAll();
return $result;
}
public function fetchAllRoles()
{
/*
SELECT roles.id, roles.name, inherits.name AS inherit_name
FROM webacula_roles AS roles
LEFT JOIN webacula_roles AS inherits ON inherits.id = roles.inherit_id
ORDER BY roles.inherit_id, roles.order_role ASC
*/
$select = new Zend_Db_Select($this->db);
$select->from(array('roles' => 'webacula_roles'), array('id', 'name', 'description', 'order_role', 'inherit_id'));
$select->joinLeft(array('inherits' => 'webacula_roles'), 'inherits.id = roles.inherit_id', array('inherit_name' => 'name'));
$select->order(array('roles.order_role, roles.id ASC'));
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
$stmt = $select->query();
return $stmt->fetchAll();
}
/**
* Builds query order
*
* @param string $field Field name
* @param string $order Query Sort Order
* @param bool $reset If we should reset the current order
*
* @return Bvb_Grid_Source_Zend_Select
*/
public function buildQueryOrder($field, $order, $reset = false)
{
if (!array_key_exists($field, $this->_fields)) {
return $this;
}
foreach ($this->_select->getPart(Zend_Db_Select::COLUMNS) as $col) {
if ($col[0] . '.' . $col[2] == $field && is_object($col[1])) {
$field = $col[2];
}
}
if ($reset === true) {
$this->_select->reset('order');
}
$this->_select->order($field . ' ' . $order);
return $this;
}
public function search($word = null, $limit = null)
{
$db = Zend_Registry::get('db');
$sql = new Zend_Db_Select($db);
$select = $sql->from('albums');
if (!is_null($word)) {
$select = $sql->where($db->quoteInto('title LIKE ? OR artist LIKE ?', "%" . $word . "%"));
}
if ($limit > 0) {
$select = $sql->limit($limit);
}
$select = $sql->order('artist DESC');
$results = $db->query($select);
$rows = $results->fetchAll();
return $rows;
}
public function getSelectFilesByJobId($jobid)
{
// do Bacula ACLs
Zend_Loader::loadClass('Job');
$table = new Job();
if (!$table->isJobIdExists($jobid)) {
return FALSE;
}
// !!! IMPORTANT !!! с Zend Paginator нельзя использовать DISTINCT иначе не работает в PDO_PGSQL
$select = new Zend_Db_Select($this->db);
$select->from(array('f' => 'File'), array('FileId', 'FileIndex', 'LStat'));
$select->joinLeft(array('p' => 'Path'), 'f.PathId = p.PathId', array('Path'));
$select->joinLeft(array('n' => 'Filename'), 'f.FileNameId = n.FileNameId', array('Name'));
$select->where("f.JobId = ?", $jobid);
$select->order(array('f.FileIndex', 'f.FileId'));
return $select;
}
/**
* @param number $filterState
* @param string $userIds
* @throws Zend_Exception
*/
public function selectByUsersIds($filterState = 1, $ids, $like = NULL)
{
try {
$select = new Zend_Db_Select($this->db);
$select->from('' . $this->name . '');
$select->where('state=?', $filterState);
$select->where('created_user_id IN ( ' . $ids . ' )');
if (!is_null($like)) {
$columns = array('cpf', 'dados_cliente');
$select->where($columns[0] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[1] . ' LIKE ?', '%' . $like . '%');
}
$select->order('id DESC');
return $select;
} catch (Zend_Exception $e) {
throw new Zend_Exception($e->getMessage());
return false;
}
}
/**
* Retorna uma query com informações do empreendimentos cadastrados
*
* @param number $filter
* @throws Zend_Db_Exception
* @return mixed|boolean
*/
public function selectQueryList($filter = 1, $like = NULL)
{
try {
$select = new Zend_Db_Select($this->db);
$select->from($this->name);
$select->where('state=?', $filter);
if (!is_null($like)) {
$columns = array('nome', 'categoria', 'logradouro', 'incorporadora');
$select->where($columns[0] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[1] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[2] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[3] . ' LIKE ?', '%' . $like . '%');
}
$select->order('id DESC');
return $select;
} catch (Zend_Db_Exception $e) {
throw new Zend_Db_Exception($e->getMessage());
return false;
}
}
/**
* Enter description here ...
* @param Zend_Db_Select $select
* @param array $params
* @return Zend_Db_Select | null
* @author Tung Ly
*/
public function createSQL($select, $params)
{
$andWhere = array();
$orWhere = array();
foreach ($params as $field => $value) {
if ($field === 'fieldsearch') {
$andWhere[] = str_replace('{{param}}', addslashes(trim($params['keywords'])), $this->_search[$value]);
} elseif ($field !== 'keywords' && $value !== 'none') {
if (isset($this->_search[$field])) {
$orWhere[] = str_replace('{{param}}', $params[$field], $this->_search[$field]);
} elseif (isset($this->_sort[$field])) {
$select->order(str_replace('{{param}}', $params[$field], $this->_sort[$field]));
}
}
}
if (count($andWhere)) {
$select->where(implode(' AND ', $andWhere));
}
if (count($orWhere)) {
$select->where(implode(' OR ', $orWhere));
}
//echo '<pre>'; print_r($params); echo $select;
return $select;
}
/**
* Get Select object (Zend_Db_Select) for sorting table records
*
* @param Zend_Db_Select $select
* @param string $order
*
* @return Zend_Db_Select
*/
public static function GetSelectForSort($select, $options)
{
$aliasTable = '';
$order = $options['order'];
//--------------------------
$arrOrder = explode('.', $order);
// Если в параметре сортировки не задан псевдоним таблицы
// то определим его, и если нужно присоединим,
// соответствующую таблицу
if (count($arrOrder) == 1) {
$joinTableForSort = $options['joinTableForSort'];
if ($joinTableForSort) {
// Определим какие таблицы уже присоединены
$fromTables = $select->getPart(Zend_Db_Select::FROM);
foreach ($fromTables as $alias => $joinParams) {
// Если таблица -> $joinTableForSort уже присоединена
// то получим ее псевдоним
if ($joinParams['tableName'] == $joinTableForSort) {
$aliasTable = $alias;
}
}
if ($aliasTable) {
$order = $aliasTable . '.' . $order;
} else {
// Получим поле сортировки
$arrOrder = explode(' ', trim($order));
$field = $arrOrder[0];
// Присоединим таблицу
$joinTable = $joinTableForSort;
switch ($joinTable) {
case 'blog_posts_profile':
$select->joinInner(array('p_profile' => $joinTable), 'p_profile.post_id = p.id', array())->where('p_profile.profile_key = ?', $field);
$order = 'p_profile.profile_value ' . $arrOrder[1];
break;
case 'blog_posts_images':
$select->joinInner(array('img' => $joinTable), 'img.post_id = p.id', array());
$order = 'img.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_audio':
$select->joinInner(array('au' => $joinTable), 'au.post_id = p.id', array());
$order = 'au.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_video':
$select->joinInner(array('v' => $joinTable), 'v.post_id = p.id', array());
$order = 'v.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_lacations':
$select->joinInner(array('l' => $joinTable), 'l.post_id = p.id', array());
$order = 'l.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_tags':
$select->joinInner(array('t' => $joinTable), 't.post_id = p.id', array());
$order = 't.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_comments':
$select->joinInner(array('c' => $joinTable), 'c.post_id = p.id', array());
$order = 'c.' . $field . ' ' . $arrOrder[1];
break;
case 'users':
$select->joinInner(array('u' => $joinTable), 'u.id = p.user_id', array());
$order = 'u.' . $field . ' ' . $arrOrder[1];
break;
case 'users_profile':
$select->joinInner(array('u_profile' => $joinTable), 'u_profile.user_id = p.user_id', array())->where('u_profile.profile_key = ?', $field);
$order = 'u_profile.profile_value ' . $arrOrder[1];
break;
default:
$order = 'p.' . $field . ' ' . $arrOrder[1];
break;
}
}
} else {
$order = 'p.' . $order;
}
}
$select->order($order);
return $select;
}
/**
* order
*
* @param string $column
* @param string $direction
*/
public function order($column, $direction)
{
$this->_select->order($column . ' ' . $direction);
}
/**
* Recupera dados da tabela #__clientes
*
* @param Zend_DB::FETCH $mode
* @return array
*/
public function selectAll($filterState = 1, $like = NULL)
{
try {
$select = new Zend_Db_Select($this->db);
$select->from(array('u' => $this->name), array('id', 'nome', 'email', 'id_perfil', 'acesso', 'state'));
$select->joinLeft(array('p' => $this->namePerfis), 'u.id_perfil = p.id', 'role');
$select->where('u.state = ?', $filterState);
if (!is_null($like)) {
$columns = array('u.nome', 'u.email', 'p.role');
$select->where($columns[0] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[1] . ' LIKE ?', '%' . $like . '%');
$select->orWhere($columns[2] . ' LIKE ?', '%' . $like . '%');
}
$select->order('u.id DESC');
return $select;
//return $result;
} catch (Zend_Db_Adapter_Exception $e) {
throw new Zend_Exception($e->getMessage());
}
}
/**
* Append the order-by clause to the specified query
*
* @param Zend_Db_Select $query
*/
protected function _appendOrderByClause(Zend_Db_Select $query)
{
$order = strtoupper($this->_getParam($this->_orderKey, $this->_defaultOrderDirection));
$orderBy = $this->_getParam($this->_orderByKey, $this->_defaultOrderColumn);
if (in_array($orderBy, $this->_getTable()->info('cols'))) {
$this->view->order = $order;
$this->view->orderBy = $orderBy;
$query->order($orderBy . ' ' . $order);
}
return $query;
}
/**
* 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);
}
/**
* LogBook find by logId
*
*/
function findLogBookById($id_begin, $id_end, $sort_order)
{
if (!isset($id_begin, $id_end)) {
return;
}
$db = Zend_Db_Table::getAdapter('db_bacula');
$select = new Zend_Db_Select($db);
switch ($this->db_adapter) {
case 'PDO_MYSQL':
$select->distinct();
$select->from(array('l' => 'webacula_logbook'), array('logId', 'logDateCreate', 'logDateLast', 'logTxt', 'logTypeId', 'logIsDel'));
$select->joinLeft(array('t' => 'webacula_logtype'), 'l.logTypeId = t.typeId', array('typeId', 'typeDesc'));
$select->where("('{$id_begin}' <= l.logId) AND (l.logId <= '{$id_end}')");
$select->order(array('l.logId ' . $sort_order));
break;
case 'PDO_PGSQL':
$select->distinct();
$select->from(array('l' => 'webacula_logbook'), array('logId', 'logDateCreate', 'logDateLast', 'logTxt', 'logTypeId', 'logIsDel'));
$select->joinLeft(array('t' => 'webacula_logtype'), 'l.logTypeId = t.typeId', array('typeId', 'typeDesc'));
$select->where("('{$id_begin}' <= l.logId) AND (l.logId <= '{$id_end}')");
$select->order(array('l.logId ' . $sort_order));
break;
case 'PDO_SQLITE':
// bug http://framework.zend.com/issues/browse/ZF-884
// http://sqlite.org/pragma.html
//$res = $db->query('PRAGMA short_column_names=1'); // not affected
//$res = $db->query('PRAGMA full_column_names=0'); // not affected
$select->distinct();
$select->from(array('l' => 'webacula_logbook'), array('logid' => 'logId', 'logdatecreate' => 'logDateCreate', 'logdatelast' => 'logDateLast', 'logtxt' => 'logTxt', 'logtypeid' => 'logTypeId', 'logisdel' => 'logIsDel'));
$select->joinLeft(array('t' => 'webacula_logtype'), 'l.logTypeId = t.typeId', array('typeid' => 'typeId', 'typedesc' => 'typeDesc'));
$select->where("('{$id_begin}' <= l.logId) AND (l.logId <= '{$id_end}')");
$select->order(array('l.logId ' . $sort_order));
break;
}
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
$result = $select->query();
return $result;
}
/**
* @param int
* @return Varien_Data_Collerction
*/
public function getCollection($catId)
{
$currentCatProducts = $this->_getProductDataByCategory($catId);
if (!$currentCatProducts) {
return new Varien_Data_Collection();
}
$productUrlToCategory = Mage::helper('xsitemap')->getHtmlSitemapProductUrlType();
$onlyCount = false;
$store = Mage::app()->getStore();
$storeId = $store->getStoreId();
$read = $this->_getReadAdapter();
$this->_select = $read->select()->distinct()->from(array('e' => $this->getMainTable()), array($onlyCount ? 'COUNT(*)' : $this->getIdFieldName()))->join(array('w' => $this->getTable('catalog/product_website')), "e.entity_id=w.product_id AND product_id IN (" . implode(',', array_keys($currentCatProducts)) . ")", array())->where('w.website_id=?', $store->getWebsiteId());
$name = Mage::getModel('catalog/product')->getResource()->getAttribute('name');
if ($name) {
$this->_select->join(array('cpev' => $name->getBackend()->getTable(), array($this->getIdFieldName())), "cpev.entity_id = e.entity_id", array('name' => 'cpev.value'));
$this->_select->joinRight(array('ea' => Mage::getSingleton('core/resource')->getTableName('eav_attribute')), "cpev.attribute_id = ea.attribute_id" . new Zend_Db_Expr(" AND cpev.store_id =\n IF(\n\t\t\t\t\t\t(SELECT `cpev_store`.`value` FROM `{$name->getBackend()->getTable()}` AS `cpev_store` WHERE `cpev_store`.`entity_id` = `e`.`entity_id` AND `attribute_id` = {$name->getAttributeId()} AND `store_id` = {$storeId}) IS NOT NULL ,\n\t\t\t\t\t\t(SELECT {$storeId}),\n\t\t\t\t\t\t(SELECT 0)\n\t\t\t\t\t)"), array())->where('ea.attribute_code=?', 'name');
}
$excludeAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('exclude_from_html_sitemap');
if ($excludeAttr) {
$this->_select->joinLeft(array('exclude_tbl' => $excludeAttr->getBackend()->getTable()), 'exclude_tbl.entity_id = e.entity_id AND exclude_tbl.attribute_id = ' . $excludeAttr->getAttributeId() . new Zend_Db_Expr(" AND exclude_tbl.store_id =\n IF(\n\t\t\t\t\t\t(SELECT `exclude`.`value` FROM `{$excludeAttr->getBackend()->getTable()}` AS `exclude` WHERE `exclude`.`entity_id` = `e`.`entity_id` AND `attribute_id` = {$excludeAttr->getAttributeId()} AND `store_id` = {$storeId}) ,\n\t\t\t\t\t\t(SELECT {$storeId}),\n\t\t\t\t\t\t(SELECT 0)\n\t\t\t\t\t)"), array())->where('exclude_tbl.value=0 OR exclude_tbl.value IS NULL');
}
if (Mage::helper('xsitemap')->isExcludeFromXMLOutOfStockProduct($storeId)) {
$cond = 'e.entity_id = csi.product_id';
if (Mage::getStoreConfig('cataloginventory/item_options/manage_stock', $storeId)) {
$cond .= ' AND IF (csi.use_config_manage_stock = 1, csi.is_in_stock = 1, (csi.manage_stock = 0 OR (csi.manage_stock = 1 AND csi.is_in_stock = 1)))';
} else {
$cond .= ' AND IF (csi.use_config_manage_stock = 1, TRUE, (csi.manage_stock = 0 OR (csi.manage_stock = 1 AND csi.is_in_stock = 1)))';
}
$this->_select->join(array('csi' => $this->getTable('cataloginventory/stock_item')), $cond, array('is_in_stock', 'manage_stock', 'use_config_manage_stock'));
}
$this->_addFilter($storeId, 'visibility', Mage::getSingleton('catalog/product_visibility')->getVisibleInSiteIds(), 'in');
$this->_addFilter($storeId, 'status', Mage::getSingleton('catalog/product_status')->getVisibleStatusIds(), 'in');
$suffix = '';
$suffix2 = '';
$sort = '';
if ($productUrlToCategory == 'canonical') {
$suffix = "AND canonical_url_rewrite.category_id IS NOT NULL";
$suffix2 = "AND category_id IS NOT NULL";
$productCanonicalType = Mage::helper('xsitemap')->getSeosuiteProductCanonicalType();
if ($productCanonicalType) {
if ($productCanonicalType == 1 || $productCanonicalType == 4) {
$sort = 'DESC';
} elseif ($productCanonicalType == 2 || $productCanonicalType == 5) {
$sort = 'ASC';
} elseif ($productCanonicalType == 3) {
} else {
$productUrlToCategory = 'yes';
}
}
}
if ($productUrlToCategory == 'no') {
if (!Mage::helper('xsitemap')->isProductUrlUseCategory()) {
$sort = '';
$cropCategory = true;
}
} elseif ($productUrlToCategory == 'yes') {
}
$canonicalAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_url');
$urlPathAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('url_path');
if (Mage::helper('xsitemap')->isEnterpriseSince113()) {
$urlSuffix = Mage::helper('catalog/product')->getProductUrlSuffix($storeId);
if ($urlSuffix) {
$urlSuffix = '.' . $urlSuffix;
} else {
$urlSuffix = '';
}
$this->_select->joinLeft(array('ecp' => $this->getTable('enterprise_catalog/product')), 'ecp.product_id = e.entity_id ' . 'AND ecp.store_id = ' . $storeId, array())->joinLeft(array('euur' => $this->getTable('enterprise_urlrewrite/url_rewrite')), 'ecp.url_rewrite_id = euur.url_rewrite_id AND euur.is_system = 1', array())->joinLeft(array('ecp2' => $this->getTable('enterprise_catalog/product')), 'ecp2.product_id = e.entity_id AND ecp2.store_id = 0', array())->joinLeft(array('euur2' => $this->getTable('enterprise_urlrewrite/url_rewrite')), 'ecp2.url_rewrite_id = euur2.url_rewrite_id', array('url' => 'concat( ' . $this->_getWriteAdapter()->getIfNullSql('euur.request_path', 'euur2.request_path') . ',"' . $urlSuffix . '")'));
} elseif ($canonicalAttr && $productUrlToCategory == 'canonical') {
$this->_select->columns(array('url' => new Zend_Db_Expr("\n IFNULL(\n (IFNULL((SELECT canonical_url_rewrite.`request_path`\n FROM `" . $canonicalAttr->getBackend()->getTable() . "` AS canonical_path\n LEFT JOIN `" . $this->getTable('core/url_rewrite') . "` AS canonical_url_rewrite ON canonical_url_rewrite.`id_path` = canonical_path.`value`\n WHERE canonical_path.`entity_id` = e.`entity_id` AND canonical_path.`attribute_id` = " . $canonicalAttr->getAttributeId() . " AND canonical_url_rewrite.`store_id` IN (0," . $storeId . ") {$suffix}" . ($sort ? " ORDER BY LENGTH(canonical_url_rewrite.`request_path`) " . $sort : "") . " LIMIT 1),\n (SELECT `request_path`\n FROM `" . $this->getTable('core/url_rewrite') . "`\n WHERE `product_id`=e.`entity_id` AND `store_id` IN (0," . $storeId . ") AND `is_system`=1 AND `request_path` IS NOT NULL {$suffix2}" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)\n )),\n (SELECT p.`value` FROM `" . $urlPathAttr->getBackend()->getTable() . "` AS p\n WHERE p.`entity_id` = e.`entity_id` AND p.`attribute_id` = " . $urlPathAttr->getAttributeId() . " AND p.`store_id` IN (0," . $storeId . ") LIMIT 1\n )\n )")));
} else {
$this->_select->columns(array('url' => new Zend_Db_Expr("(\n SELECT `request_path`\n FROM `" . $this->getTable('core/url_rewrite') . "`\n WHERE `product_id`=e.`entity_id` AND `store_id`=" . intval($storeId) . " AND `is_system`=1 AND `category_id`= " . intval($catId) . " AND `request_path` IS NOT NULL" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)")));
}
$crossDomainAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_cross_domain');
if ($crossDomainAttr && !empty($productCanonicalType)) {
$this->_select->joinLeft(array('cross_domain_tbl' => $crossDomainAttr->getBackend()->getTable()), 'cross_domain_tbl.entity_id = e.entity_id AND cross_domain_tbl.attribute_id = ' . $crossDomainAttr->getAttributeId(), array('canonical_cross_domain' => 'cross_domain_tbl.value'));
}
$sortOrder = Mage::helper('xsitemap')->getHtmlSitemapSort();
if ($sortOrder == 'position') {
$sortOrder = 'e.entity_id';
}
if ($sortOrder) {
$this->_select->order($sortOrder);
}
//echo $this->_select->assemble();
$query = $read->query($this->_select);
$collection = new Varien_Data_Collection();
while ($row = $query->fetch()) {
$product = $this->_prepareProduct($row);
//If use root canonical or Product url without category.
if (isset($productCanonicalType) && $productCanonicalType == 3 || isset($cropCategory) && $cropCategory) {
$urlArr = explode('/', $product->getUrl());
$product->setUrl(end($urlArr));
}
if (!in_array($product->getId(), $collection->getAllIds())) {
$collection->addItem($product);
}
}
return $collection;
}
public function getProduct($newsDate = null)
{
$start_date = new DateTime();
$start_date->setTime(20, 0, 0);
$start_date->setDate($newsDate->format("Y"), $newsDate->format("m"), $newsDate->format("d") - 1);
$end_date = new DateTime();
$end_date->setTime(10, 0, 0);
$end_date->setDate($newsDate->format("Y"), $newsDate->format("m"), $newsDate->format("d"));
$select = new Zend_Db_Select($this->getDefaultAdapter());
$select->from(array('c' => $this->_campanha));
$select->where('start_date = ?', $start_date->format("Y-m-d H:i:s"));
$select->where('start_date = end_date');
$select->where('is_banner = ?', '0');
$select->where('is_magazine = ?', '0');
$select->where('is_product = ?', '1');
$select->where('is_active = ?', '1');
$select->order("priority DESC");
$select->order("end_date ASC");
$select->limit(1);
return $this->_db->fetchAssoc($select);
}
public function getActivityAbout(Core_Model_Item_Abstract $about, User_Model_User $user, array $params = array())
{
// Proc args
extract($this->_getInfo($params));
// action_id, limit, min_id, max_id, actionFilter, filterValue
// Prepare main query
$streamTable = Engine_Api::_()->getDbtable('stream', 'activity');
$streamName = $streamTable->info('name');
$actionTableName = $this->info('name');
$db = $streamTable->getAdapter();
$union = new Zend_Db_Select($db);
// Prepare action types
$masterActionTypes = Engine_Api::_()->getDbtable('actionTypes', 'activity')->getActionTypes();
$subjectActionTypes = array();
$objectActionTypes = array();
// Filter types based on displayable
foreach ($masterActionTypes as $type) {
if ($about->getType() == 'event' && Engine_Api::_()->hasItemType('event') || $about->getType() == 'group' && Engine_Api::_()->hasItemType('group') || $about->getType() == 'ynbusinesspages_business' && Engine_Api::_()->hasItemType('ynbusinesspages_business')) {
if ($actionFilter == 'owner' && isset($type->is_object_thumb) && !$type->is_object_thumb) {
continue;
}
if ($actionFilter == 'membership' && isset($type->is_object_thumb) && $type->is_object_thumb) {
continue;
}
}
if ($type->displayable & 1) {
$subjectActionTypes[] = $type->type;
}
if ($type->displayable & 2) {
$objectActionTypes[] = $type->type;
}
}
// Filter types based on user request
if (isset($showTypes) && is_array($showTypes) && !empty($showTypes)) {
$subjectActionTypes = array_intersect($subjectActionTypes, $showTypes);
$objectActionTypes = array_intersect($objectActionTypes, $showTypes);
} else {
if (isset($hideTypes) && is_array($hideTypes) && !empty($hideTypes)) {
$subjectActionTypes = array_diff($subjectActionTypes, $hideTypes);
$objectActionTypes = array_diff($objectActionTypes, $hideTypes);
}
}
// Nothing to show
if (empty($subjectActionTypes) && empty($objectActionTypes)) {
return null;
}
if (empty($subjectActionTypes)) {
$subjectActionTypes = null;
} else {
if (count($subjectActionTypes) == count($masterActionTypes)) {
$subjectActionTypes = true;
} else {
$subjectActionTypes = "'" . join("', '", $subjectActionTypes) . "'";
}
}
if (empty($objectActionTypes)) {
$objectActionTypes = null;
} else {
if (count($objectActionTypes) == count($masterActionTypes)) {
$objectActionTypes = true;
} else {
$objectActionTypes = "'" . join("', '", $objectActionTypes) . "'";
}
}
// Prepare sub queries
$event = Engine_Hooks_Dispatcher::getInstance()->callEvent('getActivity', array('for' => $user, 'about' => $about));
$responses = (array) $event->getResponses();
if (empty($responses)) {
return null;
}
$friendsFlage = false;
$action_ids = array();
// Filter by hashtag
if ($actionFilter == 'hashtag' && !empty($filterValue)) {
$action_ids = Engine_Api::_()->getDbtable('hashtags', 'ynfeed')->getHashtagFeeds($filterValue, array(), array('limit' => $limit, 'max_id' => $max_id));
if (empty($action_ids)) {
return null;
}
}
// Filter by login as business post
if ($actionFilter == 'business') {
$select = $this->select()->where('`subject_type` = ?', 'ynbusinesspages_business')->where('subject_id', $about->getIdentity())->limit($limit);
if (null !== $max_id) {
$select->where('action_id <= ?', $max_id);
}
$data = $select->query()->fetchAll();
foreach ($data as $row) {
$action_ids[] = $row['action_id'];
}
if (empty($action_ids)) {
return null;
}
}
$member_ids = array();
if ($actionFilter == 'owner') {
if ($about instanceof User_Model_User) {
$member_ids[] = $about->getIdentity();
} elseif ($about instanceof Group_Model_Group || $about instanceof Advgroup_Model_Group) {
$objectParent = $about->getParent('user');
if ($objectParent instanceof User_Model_User) {
$member_ids[] = $objectParent->getIdentity();
}
} else {
$objectParent = $about->getParent('user');
if ($objectParent instanceof User_Model_User) {
$member_ids[] = $objectParent->getIdentity();
}
}
} elseif ($actionFilter == 'officers') {
if ($about instanceof Group_Model_Group || $about instanceof Advgroup_Model_Group) {
$objectParent = $about->getParent('user');
if ($objectParent instanceof User_Model_User) {
$member_ids[] = $objectParent->getIdentity();
}
foreach ($about->getOfficerList()->getAll() as $value) {
$member_ids[] = $value->child_id;
}
}
} elseif ($actionFilter == 'admins') {
if ($about instanceof Ynbusinesspages_Model_Business) {
$objectParent = $about->getParent('user');
if ($objectParent instanceof User_Model_User) {
$member_ids[] = $objectParent->getIdentity();
}
foreach ($about->getAdminList()->getAll() as $value) {
$member_ids[] = $value->child_id;
}
}
} elseif ($actionFilter == 'membership') {
if (in_array($about->getType(), array('event', 'group', 'ynbusinesspages_business'))) {
$members = $about->membership()->getMembers(true);
foreach ($members as $member) {
$member_ids[] = $member->getIdentity();
}
} else {
$member_ids = $user->membership()->getMembershipsOfIds();
}
if (empty($member_ids)) {
return array();
}
}
foreach ($responses as $response) {
if (empty($response)) {
continue;
}
// Target info
$select = $streamTable->select()->from($streamTable->info('name'), 'action_id')->where('target_type = ?', $response['type']);
if (empty($response['data'])) {
// Simple
$select->where('target_id = ?', 0);
} else {
if (is_scalar($response['data']) || count($response['data']) === 1) {
// Single
if (is_array($response['data'])) {
list($response['data']) = $response['data'];
}
$select->where('target_id = ?', $response['data']);
} else {
if (is_array($response['data'])) {
// Array
$select->where('target_id IN(?)', (array) $response['data']);
} else {
// Unknown
continue;
}
}
}
// Add action_id/max_id/min_id
if (null !== $action_id) {
$select->where('action_id = ?', $action_id);
} else {
if (null !== $min_id) {
$select->where('action_id >= ?', $min_id);
} else {
if (null !== $max_id) {
$select->where('action_id <= ?', $max_id);
}
}
}
// Add order/limit
$select->order('action_id DESC')->limit($limit);
if (!empty($action_ids)) {
$select->where($streamName . '.action_id IN(?)', (array) $action_ids);
}
// Add subject to main query
$selectSubject = clone $select;
if ($subjectActionTypes !== null) {
if ($subjectActionTypes !== true) {
$selectSubject->where('type IN(' . $subjectActionTypes . ')');
}
$selectSubject->where('subject_type = ?', $about->getType())->where('subject_id = ?', $about->getIdentity());
if (!empty($member_ids)) {
$selectSubject->where('object_type = ?', 'user')->where('object_id IN (?)', (array) $member_ids);
}
$union->union(array('(' . $selectSubject->__toString() . ')'));
}
// Add object to main query
$selectObject = clone $select;
if ($objectActionTypes !== null) {
if ($objectActionTypes !== true) {
$selectObject->where('type IN(' . $objectActionTypes . ')');
}
$selectObject->where('object_type = ?', $about->getType())->where('object_id = ?', $about->getIdentity());
if (!empty($member_ids)) {
$selectObject->where('subject_type = ?', 'user')->where('subject_id IN (?)', (array) $member_ids);
}
$union->union(array('(' . $selectObject->__toString() . ')'));
// (string) not work before PHP 5.2.0
}
}
// Finish main query
$union->order('action_id DESC')->limit($limit);
// Get actions
$actions = $db->fetchAll($union);
// Process ids
$ids = array();
if (in_array($actionFilter, array('all', 'posts')) && $action_id) {
$tag_ids = Engine_Api::_()->ynfeed()->getTaggedBaseActionIds($user, array('min' => $min_id, 'max' => $max_id));
if (in_array($action_id, $tag_ids)) {
$ids[] = $action_id;
}
}
// No visible actions
if (empty($actions) && empty($ids)) {
return null;
}
// Process ids
foreach ($actions as $data) {
$ids[] = $data['action_id'];
}
$ids = array_unique($ids);
// Finally get activity
return $this->fetchAll($this->select()->where('action_id IN(' . join(',', $ids) . ')')->order('action_id DESC')->limit($limit));
}
/**
* Get Select object (Zend_Db_Select) for sorting table records
*
* @param Zend_Db_Select $select
* @param string $order
*
* @return Zend_Db_Select
*/
public static function GetSelectForSort($select, $options)
{
$aliasTable = '';
$order = $options['order'];
//--------------------------
$arrOrder = explode('.', $order);
// Если в параметре сортировки не задан псевдоним таблицы
// то определим его, и если нужно присоединим,
// соответствующую таблицу
if (count($arrOrder) == 1) {
$joinTableForSort = $options['joinTableForSort'];
if ($joinTableForSort) {
// Определим какие таблицы уже присоединены
$fromTables = $select->getPart(Zend_Db_Select::FROM);
foreach ($fromTables as $alias => $joinParams) {
// Если таблица -> $joinTableForSort уже присоединена
// то получим ее псевдоним
if ($joinParams['tableName'] == $joinTableForSort) {
$aliasTable = $alias;
}
}
if ($aliasTable) {
$order = $aliasTable . '.' . $order;
} else {
// Получим поле сортировки
$arrOrder = explode(' ', trim($order));
$field = $arrOrder[0];
$order = 'l.' . $field . ' ' . $arrOrder[1];
}
} else {
$order = 'l.' . $order;
}
}
$select->order($order);
return $select;
}
public function getActivityAbout(Core_Model_Item_Abstract $about, User_Model_User $user, array $params = array())
{
// Proc args
extract($this->_getInfo($params));
// action_id, limit, min_id, max_id
// Prepare main query
$streamTable = Engine_Api::_()->getDbtable('stream', 'activity');
$db = $streamTable->getAdapter();
$union = new Zend_Db_Select($db);
// Prepare action types
$masterActionTypes = Engine_Api::_()->getDbtable('actionTypes', 'activity')->getActionTypes();
$subjectActionTypes = array();
$objectActionTypes = array();
// Filter types based on displayable
foreach ($masterActionTypes as $type) {
if ($type->displayable & 1) {
$subjectActionTypes[] = $type->type;
}
if ($type->displayable & 2) {
$objectActionTypes[] = $type->type;
}
}
// Filter types based on user request
if (isset($showTypes) && is_array($showTypes) && !empty($showTypes)) {
$subjectActionTypes = array_intersect($subjectActionTypes, $showTypes);
$objectActionTypes = array_intersect($objectActionTypes, $showTypes);
} else {
if (isset($hideTypes) && is_array($hideTypes) && !empty($hideTypes)) {
$subjectActionTypes = array_diff($subjectActionTypes, $hideTypes);
$objectActionTypes = array_diff($objectActionTypes, $hideTypes);
}
}
// Nothing to show
if (empty($subjectActionTypes) && empty($objectActionTypes)) {
return null;
}
if (empty($subjectActionTypes)) {
$subjectActionTypes = null;
} else {
if (count($subjectActionTypes) == count($masterActionTypes)) {
$subjectActionTypes = true;
} else {
$subjectActionTypes = "'" . join("', '", $subjectActionTypes) . "'";
}
}
if (empty($objectActionTypes)) {
$objectActionTypes = null;
} else {
if (count($objectActionTypes) == count($masterActionTypes)) {
$objectActionTypes = true;
} else {
$objectActionTypes = "'" . join("', '", $objectActionTypes) . "'";
}
}
// Prepare sub queries
$event = Engine_Hooks_Dispatcher::getInstance()->callEvent('getActivity', array('for' => $user, 'about' => $about));
$responses = (array) $event->getResponses();
if (empty($responses)) {
return null;
}
foreach ($responses as $response) {
if (empty($response)) {
continue;
}
// Target info
$select = $streamTable->select()->from($streamTable->info('name'), 'action_id')->where('target_type = ?', $response['type']);
if (empty($response['data'])) {
// Simple
$select->where('target_id = ?', 0);
} else {
if (is_scalar($response['data']) || count($response['data']) === 1) {
// Single
if (is_array($response['data'])) {
list($response['data']) = $response['data'];
}
$select->where('target_id = ?', $response['data']);
} else {
if (is_array($response['data'])) {
// Array
$select->where('target_id IN(?)', (array) $response['data']);
} else {
// Unknown
continue;
}
}
}
// Add action_id/max_id/min_id
if (null !== $action_id) {
$select->where('action_id = ?', $action_id);
} else {
if (null !== $min_id) {
$select->where('action_id >= ?', $min_id);
} else {
if (null !== $max_id) {
$select->where('action_id <= ?', $max_id);
}
}
}
// Add order/limit
$select->order('action_id DESC')->limit($limit);
// Add subject to main query
$selectSubject = clone $select;
if ($subjectActionTypes !== null) {
if ($subjectActionTypes !== true) {
$selectSubject->where('type IN(' . $subjectActionTypes . ')');
}
$selectSubject->where('subject_type = ?', $about->getType())->where('subject_id = ?', $about->getIdentity());
$union->union(array('(' . $selectSubject->__toString() . ')'));
// (string) not work before PHP 5.2.0
}
// Add object to main query
$selectObject = clone $select;
if ($objectActionTypes !== null) {
if ($objectActionTypes !== true) {
$selectObject->where('type IN(' . $objectActionTypes . ')');
}
$selectObject->where('object_type = ?', $about->getType())->where('object_id = ?', $about->getIdentity());
$union->union(array('(' . $selectObject->__toString() . ')'));
// (string) not work before PHP 5.2.0
}
}
// Finish main query
$union->order('action_id DESC')->limit($limit);
// Get actions
$actions = $db->fetchAll($union);
// No visible actions
if (empty($actions)) {
return null;
}
// Process ids
$ids = array();
foreach ($actions as $data) {
$ids[] = $data['action_id'];
}
$ids = array_unique($ids);
// Finally get activity
return $this->fetchAll($this->select()->where('action_id IN(' . join(',', $ids) . ')')->order('action_id DESC')->limit($limit));
}
/**
* Устанавтивает сортировку в запрос
* @param Zend_Db_Select $select
* @param array $conditions
*/
protected function setOrdersToSelect(Zend_Db_Select $select, $orders)
{
foreach ($orders as $order) {
$select->order($order);
}
}
/**
* Put data from DB to 2D array
*
* @param integer $y - year - YYYY
* @param integer $m - month
* @param integer $d - day
* @return array 2D
*/
public function getDataTimeline($date)
{
if (!empty($date)) {
$db = Zend_Db_Table::getDefaultAdapter();
// ********** query 1 *******************
$select = new Zend_Db_Select($db);
$select->distinct();
switch ($this->db_adapter) {
case 'PDO_MYSQL':
// http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
// %H - Hour (00..23)
// %i - Minutes, numeric (00..59)
$select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "DATE_FORMAT(StartTime, '%H')", 'm1' => "DATE_FORMAT(StartTime, '%i')", 'h2' => "DATE_FORMAT(EndTime, '%H')", 'm2' => "DATE_FORMAT(EndTime, '%i')"));
break;
case 'PDO_PGSQL':
// PostgreSQL
// http://www.postgresql.org/docs/8.0/static/functions-formatting.html
// HH24 - hour of day (00-23)
// MI - minute (00-59)
$select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "to_char(StartTime, 'HH24')", 'm1' => "to_char(StartTime, 'MI')", 'h2' => "to_char(EndTime, 'HH24')", 'm2' => "to_char(EndTime, 'MI')"));
break;
case 'PDO_SQLITE':
// SQLite3 Documentation
// http://sqlite.org/lang_datefunc.html
// %H - Hour (00 .. 23)
// %M - Minute (00 .. 59)
// bug http://framework.zend.com/issues/browse/ZF-884
$select->from('Job', array('jobid' => 'JobId', 'name' => 'Name', 'starttime' => 'StartTime', 'endtime' => 'EndTime', 'joberrors' => 'JobErrors', 'jobstatus' => 'JobStatus', 'h1' => "(strftime('%H',StartTime))", 'm1' => "(strftime('%M',StartTime))", 'h2' => "(strftime('%H',EndTime))", 'm2' => "(strftime('%M',EndTime))"));
break;
}
$select->joinLeft(array('sd' => 'webacula_jobdesc'), 'Job.Name = sd.name_job');
$select->where("(StartTime >= '{$date} 00:00:00') AND (StartTime <= '{$date} 23:59:59') AND\n (EndTime <= '{$date} 23:59:59')");
$select->order('JobId');
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
$stmt = $select->query();
$result = $stmt->fetchAll();
// забиваем результат в массив
$i = 0;
foreach ($result as $line) {
$this->atime[$i]['jobid'] = $line['jobid'];
$this->atime[$i]['name'] = $line['name'];
$this->atime[$i]['short_desc'] = $line['short_desc'];
$this->atime[$i]['h1'] = $line['h1'] + $line['m1'] / 60;
$this->atime[$i]['h2'] = $line['h2'] + $line['m2'] / 60;
$this->atime[$i]['flag'] = 0;
// признак, что задание уложилось в сутки
$this->atime[$i]['start'] = $line['starttime'];
$this->atime[$i]['end'] = $line['endtime'];
$i++;
}
$select->reset();
unset($select);
unset($stmt);
// задания, старт или окончание которых лежат за пределами указанных суток
// задание началось ранее
// либо задание еще длится
// ********** query 2 *******************
$select = new Zend_Db_Select($db);
$select->distinct();
switch ($this->db_adapter) {
case 'PDO_MYSQL':
// http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
// %H - Hour (00..23)
// %i - Minutes, numeric (00..59)
$select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "DATE_FORMAT(StartTime, '%H')", 'm1' => "DATE_FORMAT(StartTime, '%i')", 'h2' => "DATE_FORMAT(EndTime, '%H')", 'm2' => "DATE_FORMAT(EndTime, '%i')"));
break;
case 'PDO_PGSQL':
// PostgreSQL
// http://www.postgresql.org/docs/8.0/static/functions-formatting.html
// HH24 - hour of day (00-23)
// MI - minute (00-59)
$select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "to_char(StartTime, 'HH24')", 'm1' => "to_char(StartTime, 'MI')", 'h2' => "to_char(EndTime, 'HH24')", 'm2' => "to_char(EndTime, 'MI')"));
break;
case 'PDO_SQLITE':
// SQLite3 Documentation
// http://sqlite.org/lang_datefunc.html
// %H - Hour (00 .. 23)
// %M - Minute (00 .. 59)
// bug http://framework.zend.com/issues/browse/ZF-884
$select->from('Job', array('jobid' => 'JobId', 'name' => 'Name', 'starttime' => 'StartTime', 'endtime' => 'EndTime', 'joberrors' => 'JobErrors', 'jobstatus' => 'JobStatus', 'h1' => "(strftime('%H',StartTime))", 'm1' => "(strftime('%M',StartTime))", 'h2' => "(strftime('%H',EndTime))", 'm2' => "(strftime('%M',EndTime))"));
break;
}
$select->joinLeft(array('sd' => 'webacula_jobdesc'), 'Job.Name = sd.name_job');
$select->where("( \n (EndTime > '{$date} 00:00:00') AND \n ( \n (EndTime <= '{$date} 23:59:59') OR \n ( \n (EndTime IS NULL) AND \n (Job.jobstatus IN ('R', 'B', 'A', 'F', 'S', 'm', 'M', 's', 'j', 'c', 'd', 't', 'p', 'i', 'a', 'l', 'L') ) \n ) \n ) \n )\n AND\n (StartTime < '{$date} 00:00:00')");
$select->order('JobId');
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
$stmt = $select->query();
$result = $stmt->fetchAll();
// забиваем результат в массив
foreach ($result as $line) {
$this->atime[$i]['jobid'] = $line['jobid'];
$this->atime[$i]['name'] = $line['name'];
$this->atime[$i]['short_desc'] = $line['short_desc'];
$this->atime[$i]['h1'] = 0;
$this->atime[$i]['h2'] = $line['h2'] + $line['m2'] / 60;
$this->atime[$i]['flag'] = -1;
// признак, что задание началось ранее
$this->atime[$i]['start'] = $line['starttime'];
$this->atime[$i]['end'] = $line['endtime'];
$i++;
}
$select->reset();
unset($select);
unset($stmt);
// задание закончилось позднее
// либо задание еще длится
// ********** query 3 *******************
$select = new Zend_Db_Select($db);
$select->distinct();
switch ($this->db_adapter) {
case 'PDO_MYSQL':
// http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
// %H - Hour (00..23)
// %i - Minutes, numeric (00..59)
$select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "DATE_FORMAT(StartTime, '%H')", 'm1' => "DATE_FORMAT(StartTime, '%i')", 'h2' => "DATE_FORMAT(EndTime, '%H')", 'm2' => "DATE_FORMAT(EndTime, '%i')"));
break;
case 'PDO_PGSQL':
// PostgreSQL
// http://www.postgresql.org/docs/8.0/static/functions-formatting.html
// HH24 - hour of day (00-23)
// MI - minute (00-59)
$select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "to_char(StartTime, 'HH24')", 'm1' => "to_char(StartTime, 'MI')", 'h2' => "to_char(EndTime, 'HH24')", 'm2' => "to_char(EndTime, 'MI')"));
break;
case 'PDO_SQLITE':
// SQLite3 Documentation
// http://sqlite.org/lang_datefunc.html
// %H - Hour (00 .. 23)
// %M - Minute (00 .. 59)
// bug http://framework.zend.com/issues/browse/ZF-884
$select->from('Job', array('jobid' => 'JobId', 'name' => 'Name', 'starttime' => 'StartTime', 'endtime' => 'EndTime', 'joberrors' => 'JobErrors', 'jobstatus' => 'JobStatus', 'h1' => "(strftime('%H',StartTime))", 'm1' => "(strftime('%M',StartTime))", 'h2' => "(strftime('%H',EndTime))", 'm2' => "(strftime('%M',EndTime))"));
}
$select->joinLeft(array('sd' => 'webacula_jobdesc'), 'Job.Name = sd.name_job');
$select->where("( \n (StartTime >= '{$date} 00:00:00') AND (StartTime <= '{$date} 23:59:59') \n ) AND\n\t\t\t\t( \n (EndTime > '{$date} 23:59:59') OR \n (\n (EndTime IS NULL) AND\n (Job.jobstatus IN ('R', 'B', 'A', 'F', 'S', 'm', 'M', 's', 'j', 'c', 'd', 't', 'p', 'i', 'a', 'l', 'L') )\n )\n )");
$select->order('JobId');
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
$stmt = $select->query();
$result = $stmt->fetchAll();
// забиваем результат в массив
foreach ($result as $line) {
$this->atime[$i]['jobid'] = $line['jobid'];
$this->atime[$i]['name'] = $line['name'];
$this->atime[$i]['short_desc'] = $line['short_desc'];
$this->atime[$i]['h1'] = $line['h1'] + $line['m1'] / 60;
$this->atime[$i]['h2'] = 23.9;
$this->atime[$i]['flag'] = 1;
// признак, что задание окончилось позднее
$this->atime[$i]['start'] = $line['starttime'];
$this->atime[$i]['end'] = $line['endtime'];
$i++;
}
$select->reset();
unset($select);
unset($stmt);
// задание началось ранее и закончилось позднее (очень длинное задание)
// либо задание еще длится
// ********** query 4 *******************
$select = new Zend_Db_Select($db);
$select->distinct();
switch ($this->db_adapter) {
case 'PDO_MYSQL':
// http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
// %H - Hour (00..23)
// %i - Minutes, numeric (00..59)
$select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "DATE_FORMAT(StartTime, '%H')", 'm1' => "DATE_FORMAT(StartTime, '%i')", "h2" => "DATE_FORMAT(EndTime, '%H')", 'm2' => "DATE_FORMAT(EndTime, '%i')"));
break;
case 'PDO_PGSQL':
// PostgreSQL
// http://www.postgresql.org/docs/8.0/static/functions-formatting.html
// HH24 - hour of day (00-23)
// MI - minute (00-59)
$select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "to_char(StartTime, 'HH24')", 'm1' => "to_char(StartTime, 'MI')", "h2" => "to_char(EndTime, 'HH24')", 'm2' => "to_char(EndTime, 'MI')"));
break;
case 'PDO_SQLITE':
// SQLite3 Documentation
// http://sqlite.org/lang_datefunc.html
// %H - Hour (00 .. 23)
// %M - Minute (00 .. 59)
// bug http://framework.zend.com/issues/browse/ZF-884
$select->from('Job', array('jobid' => 'JobId', 'name' => 'Name', 'starttime' => 'StartTime', 'endtime' => 'EndTime', 'joberrors' => 'JobErrors', 'jobstatus' => 'JobStatus', 'h1' => "(strftime('%H',StartTime))", 'm1' => "(strftime('%M',StartTime))", 'h2' => "(strftime('%H',EndTime))", 'm2' => "(strftime('%M',EndTime))"));
break;
}
$select->joinLeft(array('sd' => 'webacula_jobdesc'), 'Job.Name = sd.name_job');
$select->where("(StartTime < '{$date} 00:00:00') AND \n ( \n (EndTime > '{$date} 23:59:59') OR \n (\n (EndTime IS NULL) AND\n (Job.jobstatus IN ('R', 'B', 'A', 'F', 'S', 'm', 'M', 's', 'j', 'c', 'd', 't', 'p', 'i', 'a', 'l', 'L') )\n )\n )");
$select->order('JobId');
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
$stmt = $select->query();
$result = $stmt->fetchAll();
// забиваем результат в массив
foreach ($result as $line) {
$this->atime[$i]['jobid'] = $line['jobid'];
$this->atime[$i]['name'] = $line['name'];
$this->atime[$i]['short_desc'] = $line['short_desc'];
$this->atime[$i]['h1'] = 0;
$this->atime[$i]['h2'] = 23.9;
$this->atime[$i]['flag'] = 2;
// признак, что задание началось ранее и окончилось позднее (очень длинное задание)
$this->atime[$i]['start'] = $line['starttime'];
$this->atime[$i]['end'] = $line['endtime'];
$i++;
}
$select->reset();
unset($select);
unset($stmt);
// do Bacula ACLs
$this->atime = $this->bacula_acl->doBaculaAcl($this->atime, 'name', 'job');
}
}
/**
*
* @param mixed $spec The column(s) and direction to order by.
* @return \Gems_Tracker_Token_TokenSelect
*/
public function order($spec)
{
$this->sql_select->order($spec);
return $this;
}
/**
* Refine the active pastes selection based on criteria provided
*
* Allows setting a limit to the number of records returend
*
* @param Zend_Db_Select $select
* @param array $criteria
* @return void
*/
protected function _refineSelection(Zend_Db_Select $select, array $criteria)
{
if (array_key_exists('start', $criteria) && $criteria['start'] == intval($criteria['start'])) {
if (array_key_exists('count', $criteria) && $criteria['count'] == intval($criteria['count'])) {
$select->limit($criteria['count'], $criteria['start']);
}
}
$sorted = false;
if (array_key_exists('sort', $criteria)) {
$sort = $criteria['sort'];
$dir = 'ASC';
if ('-' == substr($sort, 0, 1)) {
$sort = substr($sort, 1);
$dir = 'DESC';
}
$fields = $this->getTable()->info('cols');
if (in_array($sort, $fields)) {
$select->order("{$sort} {$dir}");
$sorted = true;
}
}
if (!$sorted) {
$select->order('created DESC');
}
}
protected function applyOrders(Zend_Db_Select $select, $orders)
{
foreach ($orders as $order) {
$select->order($order);
}
return $select;
}
