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