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