public function executeCategory() { $id = $this->get('id'); if (!$id || !($term = \Terms::retrieveById($id))) { return $this->raise404(t('Product Category not found!')); } $cats = [$term->getId()]; $descendants = (array) $term->getDescendants(); foreach ($descendants as $d) { $cats[] = $d->getId(); } //create query $q = \Items::select(); if (sizeof($cats) > 1) { $q->where('`cat_id` = :cat_id')->setParameter(':cat_id', $term->getId(), \PDO::PARAM_INT); } else { $q->where('`cat_id` IN (' . implode(', ', $cats) . ')'); } $q->andWhere('`status` = "ACTIVE"')->andWhere('`is_draft` = 0'); //filter $price_from = floatval(str_replace(',', '.', str_replace('.', '', $this->get('price_from')))); $price_to = floatval(str_replace(',', '.', str_replace('.', '', $this->get('price_to')))); if ($price_from > $price_to) { $t = $price_from; $price_from = $price_to; $price_to = $t; } if ($price_from) { $q->andWhere('`regular_price` >= :pf OR `sale_price` >= :pf')->setParameter(':pf', $price_from, \PDO::PARAM_STR); } if ($price_to) { $q->andWhere('`regular_price` <= :pf OR `sale_price` <= :pf')->setParameter(':pf', $price_to, \PDO::PARAM_STR); } $ordering = $this->get('ordering'); switch ($ordering) { case 'PRICE_DESCENDING': $q->orderBy('regular_price', 'DESC')->addOrderBy('sale_price', 'DESC'); break; case 'PRICE_ASCENDING': $q->orderBy('regular_price', 'ASC')->addOrderBy('sale_price', 'ASC'); break; default: $q->orderBy('public_time', 'DESC'); } $cq = clone $q; $total = $cq->count('`id`')->execute(); $page = abs($this->get('page', 'INT', 1)); $items = $q->execute(); $this->setView('Products/category'); $this->view()->assign(['term' => $term, 'items' => $items, 'total' => $total, 'page' => $page, 'page_size' => $this->pageSize, 'price_form' => $price_from, 'price_to' => $price_to, 'ordering' => $ordering]); return $this->renderComponent(); }
public function begin() { $terms = $this->getParams('terms'); $ordering = $this->getParams('ordering'); $fetchChild = $this->getParams('fetch_child', false); $limit = $this->getParams('limit'); $q = \Items::select()->where('`is_draft` = 0 AND `status` = :status')->setParameter(':status', 'ACTIVE', \PDO::PARAM_STR); if (!empty($ordering)) { foreach ($ordering as $_o) { $q->addOrderBy(@$_o['field'], @$_o['order']); } } else { $q->orderBy('created_time', 'DESC'); } if ($limit) { $q->setMaxResults((int) $limit); } if (is_array($terms) && !empty($terms)) { $t = $terms; if ($fetchChild) { foreach ($terms as $term_id) { if ($term = \Terms::retrieveById($term_id)) { $descendants = (array) $term->getDescendants(); foreach ($descendants as $d) { $t[] = $d->getId(); } unset($d); } } } $terms = $t; $q->andWhere('`cat_id` IN (' . implode(', ', $terms) . ')'); } elseif (is_scalar($terms) && is_numeric($terms)) { $t = [$terms]; if ($term = \Terms::retrieveById($terms)) { if ($fetchChild) { $descendants = (array) $term->getDescendants(); foreach ($descendants as $d) { $t[] = $d->getId(); } unset($d); } } if (sizeof($t) > 1) { $q->andWhere('`cat_id` IN (' . implode(', ', $t) . ')'); } else { $q->andWhere('`cat_id` = :term_id')->setParameter(':term_id', $terms, \PDO::PARAM_INT); } } $this->items = $q->execute(); }
public function showallAction() { $this->view->title = "Showing All PSI Inventory"; $items = new Items(); $select = $items->select()->setIntegrityCheck(false); $select->from('t_items'); $select->join('t_sites', 't_items.site_id = t_sites.id', 'name AS sitename'); $select->join('t_status', 't_items.status_id = t_status.id', 'name AS statusname'); //echo $select->__toString(); $this->view->items = $items->fetchAll($select); //TODO Figure out how to use a view instead /* $dbconfig = Zend_Registry::get('config'); $db = Zend_Db::factory($dbconfig->db); $listing = new Zend_Db_Select($db); $listing->from('t_items'); $items = $listing->query(); // $this->view->items = $items->fetchAll(); return $items->fetchAll(); $this->view-> // $db->closeConnection(); // return $result; */ }