public function fetchAll($paginate = true, $filter = array(), $orderBy = array()) { if ($paginate) { $select = new Select('service_language'); $select->join('lookup_status', 'lookup_status.status_id = service_language.status_id', array('status'), 'left'); /* Data filter code start here*/ if (count($filter) > 0) { $filter['language_name'] != "" ? $select->where("service_language.language_name LIKE '%" . $filter['language_name'] . "%'") : ""; $filter['status_id'] != "" ? $select->where("service_language.status_id = " . $filter['status_id']) : ""; } /* Data filter code end here*/ /* Data sorting code starts here */ if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') { switch ($orderBy['sort_field']) { case 'language': $select->order('service_language.language_name ' . $orderBy['sort_order']); break; case 'status': $select->order('lookup_status.status ' . $orderBy['sort_order']); break; } } /* Data sorting code ends here */ $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new ServiceLanguages()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } return $this->tableGateway->select(); }
public function fetchAll($method = 'asc') { $select = new Select(); $select->from('items'); if (is_numeric($method) and $method != 0) { $select->where->equalTo('items.item_category', $method); } //$select->columns(array('item_name','item_brand','item_price')); $select->join('brands', "brands.b_id = items.item_brand", array('b_name'), 'left'); $select->join('categories', "categories.cat_id = items.item_category", array('cat_name'), 'left'); $select->join('sub_categories', "sub_categories.subcat_id = items.item_sub_category", array('subcat_name'), 'left'); $select->join('images', "images.img_item_id = items.item_id", array('img_link'), 'left'); $select->group('items.item_id'); switch ($method) { case 'asc': $select->order(array('item_price ACS')); break; case 'desc': $select->order(array('item_price DESC')); break; } $select->order(array('item_price ' . $method)); //echo $select->getSqlString(); $resultSet = $this->tableGateway->selectWith($select); //$resultSet = $this->tableGateway->select(); return $resultSet; }
public function fetchAll($id, $paginate = true, $filter = array(), $orderBy = array()) { if ($paginate) { $select = new Select('media'); //$select->columns(array('*', new Expression("user_feature_setting.email as email_status"))); $select->join('users', 'users.id = media.user_id', array('first_name', 'last_name'), 'inner'); $select->join('lookup_status', 'lookup_status.status_id = media.status_id', array('status'), 'left'); $select->where(array('user_id' => $id, 'media_type' => 1)); /* Data filter code start here*/ if (count($filter) > 0) { isset($filter['name']) && $filter['name'] != "" ? $select->where("CONCAT(users.first_name,' ',users.last_name) LIKE '%" . $filter['name'] . "%'") : ""; isset($filter['title']) && $filter['title'] != "" ? $select->where("media.media_title LIKE '%" . $filter['title'] . "%'") : ""; isset($filter['media_type']) && $filter['media_type'] != "" ? $select->where("media.media_type = " . $filter['media_type']) : ""; if (isset($filter['from_date']) && $filter['from_date'] != "" && isset($filter['to_date']) && $filter['to_date'] != "") { $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') BETWEEN '" . $filter['from_date'] . "' AND '" . $filter['to_date'] . "'"); } else { if (isset($filter['from_date']) && !isset($filter['to_date']) && $filter['from_date'] != "") { $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') = '" . $filter['from_date'] . "'"); } else { if (!isset($filter['from_date']) && isset($filter['to_date']) && $filter['to_date'] != "") { $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') = '" . $filter['to_date'] . "'"); } } } isset($filter['status_id']) && $filter['status_id'] != "" ? $select->where("media.status_id = " . $filter['status_id']) : ""; } /* Data filter code end here*/ /* Data sorting code starts here */ if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') { switch ($orderBy['sort_field']) { case 'name': $select->order('users.first_name ' . $orderBy['sort_order']); break; case 'title': $select->order('media.media_title ' . $orderBy['sort_order']); break; case 'media_type': $select->order('media.media_type ' . $orderBy['sort_order']); break; case 'date': $select->order('media.created_date ' . $orderBy['sort_order']); break; case 'status': $select->order('lookup_status.status ' . $orderBy['sort_order']); break; } } /* Data sorting code ends here */ //echo str_replace('"', '', $select->getSqlString()); exit; $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new UsersMedia()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } else { $select = $this->tableGateway->getSql()->select(); return $this->tableGateway->selectwith($select); } }
public function fetchAll($paginate = true, $filter = array(), $orderBy = array()) { if ($paginate) { $select = new Select('booking'); $select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,payment_history.currency as currency,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus"))); $select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner'); $select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left'); $select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left'); $select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left'); $select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left'); $select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision'), 'inner'); $select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner'); $select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner'); $select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left'); $select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)'); count($filter) > 0 ? $select->where($filter) : ""; /* Data sorting code starts here */ if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') { switch ($orderBy['sort_field']) { case 'user': $select->order('users.first_name ' . $orderBy['sort_order']); break; case 'service_provider': $select->order('service_provider.first_name ' . $orderBy['sort_order']); break; case 'service': $select->order('service_category.category_name ' . $orderBy['sort_order']); break; case 'booked_date': $select->order('booking.booked_date ' . $orderBy['sort_order']); break; } } else { $select->order('booking.id desc'); } /* Data sorting code ends here */ //echo str_replace('"', '', $select->getSqlString()); exit; $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new Bookings()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } else { $select = $this->tableGateway->getSql()->select(); $select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus"))); $select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner'); $select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left'); $select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left'); $select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left'); $select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left'); $select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision', 'created_date'), 'inner'); $select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner'); $select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner'); $select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left'); $select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)'); count($filter) > 0 ? $select->where($filter) : ""; return $this->tableGateway->selectwith($select); } }
public function fetchAll($paginate = true, $filter = array(), $orderBy = array()) { if ($paginate) { $select = new Select('feedback'); $select->columns(array('*', new Expression("service_provider_contact.first_name AS sp_first_name, service_provider_contact.last_name AS sp_last_name"))); $select->join(array('service_provider_contact' => 'users'), 'service_provider_contact.id = feedback.users_id', array(), 'inner'); $select->join('users', 'users.id = feedback.created_by', array('first_name', 'last_name'), 'inner'); $select->join('service_provider_service', 'service_provider_service.id = feedback.service_id', array('duration'), 'inner'); $select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'inner'); $select->join('lookup_status', 'lookup_status.status_id = feedback.status_id', array('status'), 'left'); /* Data filter code start here*/ if (count($filter) > 0) { isset($filter['name']) && $filter['name'] != "" ? $select->where("CONCAT(service_provider_contact.first_name,' ',service_provider_contact.last_name) LIKE '%" . $filter['name'] . "%'") : ""; if (isset($filter['from_date']) && $filter['from_date'] != "" && isset($filter['to_date']) && $filter['to_date'] != "") { $select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') BETWEEN '" . $filter['from_date'] . "' AND '" . $filter['to_date'] . "'"); } else { if (isset($filter['from_date']) && !isset($filter['to_date']) && $filter['from_date'] != "") { $select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') = '" . $filter['from_date'] . "'"); } else { if (!isset($filter['from_date']) && isset($filter['to_date']) && $filter['to_date'] != "") { $select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') = '" . $filter['to_date'] . "'"); } } } if (isset($filter['service_id']) && $filter['service_id'] != "") { $select->where("feedback.service_id = " . $filter['service_id']); } isset($filter['status_id']) && $filter['status_id'] != "" ? $select->where("users.status_id = " . $filter['status_id']) : ""; } /* Data filter code end here*/ /* Data sorting code starts here */ if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') { switch ($orderBy['sort_field']) { case 'name': $select->order('service_provider_contact.first_name ' . $orderBy['sort_order']); break; case 'service': $select->order('service_category.category_name ' . $orderBy['sort_order']); break; case 'date': $select->order('feedback.created_date ' . $orderBy['sort_order']); break; } } /* Data sorting code ends here */ //echo str_replace('"','',$select->getSqlString()); exit; $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new Feedbacks()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } return $this->tableGateway->select(); }
/** * Sets the filter * @param \Zend\Db\Sql\Select $select * @param array $criteria * @return \Zend\Db\Sql\Select */ private function setFilter($select, $criteria) { if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('capitaleSociale', $criteria['filter']) && is_array($criteria['filter']['capitaleSociale']) && sizeof($criteria['filter']['capitaleSociale']) > 1) { if ($criteria['filter']['capitaleSociale'][0] == 0) { $select->where('capitaleSociale BETWEEN 0 AND ' . pow(10, $criteria['filter']['capitaleSociale'][1])); } else { $select->where('capitaleSociale BETWEEN ' . pow(10, $criteria['filter']['capitaleSociale'][0]) . ' AND ' . pow(10, $criteria['filter']['capitaleSociale'][1])); } } if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('fEventi', $criteria['filter']) && $criteria['filter']['fEventi'] != 'false') { $select->where('eventi > 0'); $select->order('eventi DESC'); } if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('fProtesti', $criteria['filter']) && $criteria['filter']['fProtesti'] != 'false') { $select->where('protesti > 0'); $select->order('protesti DESC'); } if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('fFallimenti', $criteria['filter']) && $criteria['filter']['fFallimenti'] != 'false') { $select->where('fallimenti > 0'); $select->order('fallimenti DESC'); } if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('fPregiudizievoli', $criteria['filter']) && $criteria['filter']['fPregiudizievoli'] != 'false') { $select->where('pregiudizievoli > 0'); $select->order('pregiudizievoli DESC'); } if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('fCassaIntegrazione', $criteria['filter']) && $criteria['filter']['fCassaIntegrazione'] != 'false') { $select->where('cassaIntegrazione > 0'); $select->order('cassaIntegrazione DESC'); } if (array_key_exists('filter', $criteria) && is_array($criteria['filter']) && array_key_exists('textSearch', $criteria['filter']) && $criteria['filter']['textSearch'] != '') { $select->where(' ( ragSoc LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%" OR indirizzo LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%" OR email LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%" OR telefono LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%" OR desISTAT LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%" OR desAttivita LIKE "%' . addslashes($criteria['filter']['textSearch']) . '%" ) '); } if ($this->codFisc != '') { $select->where('`partIVA` IN (SELECT `or_partIVA` FROM `person_to_company` WHERE `codFisc` = "' . addslashes($this->codFisc) . '")'); } if (is_numeric($this->partIVA)) { $select->where(' ( `partIVA` IN (SELECT `dest_partIVA` FROM `company_to_company` WHERE `or_partIVA` = "' . addslashes($this->partIVA) . '") OR `partIVA` IN (SELECT `or_partIVA` FROM `company_to_company` WHERE `dest_partIVA` = "' . addslashes($this->partIVA) . '") ) '); } return $select; }
/** * Method to fetch all data in pagination object **/ public function fetchAll($optionArray = array(), $paginated = false) { if ($paginated) { // create a new Select object for the table cmspage $select = new Select('cms_pages'); if (!empty($optionArray['fieldArray'])) { $select->columns($optionArray['fieldArray']); } if (!empty($optionArray['sortByColumn']['sort_column']) && !empty($optionArray['sortByColumn']['sort_order'])) { $orderBy = $optionArray['sortByColumn']['sort_column'] . ' ' . $optionArray['sortByColumn']['sort_order']; $select->order($orderBy); } else { if (!empty($optionArray['default_sort_column']) && !empty($optionArray['default_sort_order'])) { $orderBy = $optionArray['default_sort_column'] . ' ' . $optionArray['default_sort_order']; $select->order($orderBy); } } if (!empty($optionArray['searchColumns']['searchKey']) && !empty($optionArray['searchColumns']['searchCol'])) { $searchKey = "%" . $optionArray['searchColumns']['searchKey'] . "%"; $searchCol = $optionArray['searchColumns']['searchCol'] ? $optionArray['searchColumns']['searchCol'] : $optionArray['fieldArray'][1]; $select->where->like($searchCol, $searchKey); } // create a new result set based on the cmspage entity $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new Cmspage()); // create a new pagination adapter object $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } $resultSet = $this->tableGateway->select(function (Select $select) use($optionArray) { if (!empty($optionArray['fieldArray'])) { $select->columns($optionArray['fieldArray']); } if (!empty($optionArray['sortByColumn']['sort_column']) && !empty($optionArray['sortByColumn']['sort_order'])) { $orderBy = $optionArray['sortByColumn']['sort_column'] . ' ' . $optionArray['sortByColumn']['sort_order']; $select->order($orderBy); } else { if (!empty($optionArray['default_sort_column']) && !empty($optionArray['default_sort_order'])) { $orderBy = $optionArray['default_sort_column'] . ' ' . $optionArray['default_sort_order']; $select->order($orderBy); } } if (!empty($optionArray['searchColumns']['searchKey']) && !empty($optionArray['searchColumns']['searchCol'])) { $searchKey = "%" . $optionArray['searchColumns']['searchKey'] . "%"; $searchCol = $optionArray['searchColumns']['searchCol'] ? $optionArray['searchColumns']['searchCol'] : $optionArray['fieldArray'][1]; $select->where->like($searchCol, $searchKey); } }); return $resultSet; }
public function getBlogList() { $select = new Select(); $select->from($this->table); $select->order('date DESC'); return new \Zend\Paginator\Paginator(new \Zend\Paginator\Adapter\DbSelect($select, $this->adapter, $this->resultSetPrototype)); }
public function indexAction() { /* $temp = $this->forward()->dispatch('Application/Controller/Album', array('action' => 'index')); echo '<pre>'; print_r($temp); echo '<pre>';die; */ $auth = new AuthenticationService(); if (!$auth->hasIdentity()) { return $this->redirect()->toRoute('home'); } $select = new Select(); $search = @$_REQUEST['search']; if (!empty($search)) { $select->where->like('name', '%' . $search . '%'); } $order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id'; $order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_ASCENDING; $page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1; $category = $this->getCategoryTable()->fetchAllCategory($select->order($order_by . ' ' . $order), $search); $itemPerPage = 2; $category->current(); $paginator = new Paginator(new PaginatorIterator($category)); $paginator->setCurrentPageNumber($page); $paginator->setItemCountPerPage($itemPerPage); $paginator->setPageRange(10); return new ViewModel(array('order_by' => $order_by, 'order' => $order, 'page' => $page, 'paginator' => $paginator)); }
public function fetchActive() { $select = new Select($this->getTable()); $select->where->equalTo('active', 1); $select->order('id desc'); return $this->selectWith($select)->toArray(); }
public function indexAction() { /* $service1=new \Lib\Service1\Custom(); echo $service1->demo(); die; */ /* $plugin=$this->CustomPlugin(); print_r($plugin->doSomthing()); die; */ /* $facebook = new \Facebook(array( 'appId' => 'xxx', 'secret' => 'xxx', )); print_r($facebook); die; */ $auth = new AuthenticationService(); if (!$auth->hasIdentity()) { return $this->redirect()->toRoute('home'); } $select = new Select(); $search = @$_REQUEST['search']; if (!empty($search)) { $select->where->like('name', '%' . $search . '%')->or->like('email', '%' . $search . '%')->or->like('mob', '%' . $search . '%')->or->like('title', '%' . $search . '%'); } $order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id'; $order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_ASCENDING; $page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1; $album = $this->getAdminTable()->fetchAll($select->order($order_by . ' ' . $order), $search); $itemPerPage = 2; $album->current(); $paginator = new Paginator(new PaginatorIterator($album)); $paginator->setCurrentPageNumber($page); $paginator->setItemCountPerPage($itemPerPage); $paginator->setPageRange(10); //print_r($paginator); die; return new ViewModel(array('order_by' => $order_by, 'order' => $order, 'page' => $page, 'paginator' => $paginator)); }
public function indexAction() { $searchform = new AlbumSearchForm(); $searchform->get('submit')->setValue('Search'); $select = new Select(); $order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id'; $order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_ASCENDING; $page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1; $select->order($order_by . ' ' . $order); $search_by = $this->params()->fromRoute('search_by') ? $this->params()->fromRoute('search_by') : ''; $where = new \Zend\Db\Sql\Where(); $formdata = array(); if (!empty($search_by)) { $formdata = (array) json_decode($search_by); if (!empty($formdata['artist'])) { $where->addPredicate(new \Zend\Db\Sql\Predicate\Like('artist', '%' . $formdata['artist'] . '%')); } if (!empty($formdata['title'])) { $where->addPredicate(new \Zend\Db\Sql\Predicate\Like('title', '%' . $formdata['title'] . '%')); } } if (!empty($where)) { $select->where($where); } $album = $this->getAlbumTable()->fetchAll($select); $totalRecord = $album->count(); $itemsPerPage = 2; $album->current(); $paginator = new Paginator(new paginatorIterator($album)); $paginator->setCurrentPageNumber($page)->setItemCountPerPage($itemsPerPage)->setPageRange(7); $searchform->setData($formdata); return new ViewModel(array('search_by' => $search_by, 'order_by' => $order_by, 'order' => $order, 'page' => $page, 'paginator' => $paginator, 'pageAction' => 'album', 'form' => $searchform, 'totalRecord' => $totalRecord)); }
public function search($str) { $select = new Select('video'); $spec = new Where(); $spec->like('title', '% ' . $str . ' %'); $select->where($spec); $select->where->OR->like('title', $str . ' %'); $select->where->OR->like('title', '% ' . $str); //$select->where(array('title'=>$str)); //$select->where(array('catelog'=>$title,'description'=>$description)); $select->order('id ASC'); /* // create a new result set based on the Album entity $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new Search()); // create a new pagination adapter object $paginatorAdapter = new DbSelect( // our configured select object $select, // the adapter to run it against $this->tableGateway->getAdapter(), // the result set to hydrate $resultSetPrototype ); $paginator = new Paginator($paginatorAdapter); return $paginator; */ return $this->tableGateway->selectWith($select); }
public function fetchAll($request, $paginated = false) { if ($paginated) { // create a new Select object for the table album $select = new Select('album'); $select->order($request['sort'] . " " . $request['order']); // Search if ($request['search']) { $x = $request['search'] . '%'; $where = new \Zend\Db\Sql\Where(); $where->like('title', $x); $where->or->like('artist', $x); $select->where($where); } // New result set based on the Album entity $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new Album()); // New pagination adapter object $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } $resultSet = $this->tableGateway->select(); return $resultSet; }
public function getList($where = array(), $order = null, $offset = null, $limit = null) { if (empty($where['ProductFilterOption.productCategoryFilterOptionID'])) { $select = new Select(); $select->from(array('b' => 'Product')); $select->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName')); $select->where($where); $select->offset($offset); $select->limit($limit); $select->order($order); } else { $select = $this->getSelect(); $select->columns(array())->join(array('b' => 'Product'), 'ProductFilterOption.productID = b.productID')->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'))->where($where)->offset($offset)->limit($limit)->group(array('ProductFilterOption.productID'))->having('count(ProductFilterOption.productID) > ' . (count($where['ProductFilterOption.productCategoryFilterOptionID']) - 1)); $select->order($order); } $paginator = $this->paginate($select); $paginator->setCurrentPageNumber(ceil($offset / $limit) + 1); //$paginator->setItemCountPerPage(1); $products = $paginator->getCurrentItems()->getArrayCopy(); $pages = $paginator->getPages(); $productsCount = $paginator->getTotalItemCount(); foreach ($products as $k => $v) { $products[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']); } return array('products' => $products, 'productsCount' => $productsCount, 'pages' => $pages); }
/** * index Action * Receive the search params * Build the search query * Generate the search result as a list * @return type * Author: Tahmina Khatoon */ public function indexAction() { $searchform = new UserRolesSearchForm(); $searchform->get('submit')->setValue('Search'); $select = new Select(); $order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id'; $order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_DESCENDING; $page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1; $item_per_page = $this->params()->fromRoute('item_per_page') ? (int) $this->params()->fromRoute('item_per_page') : 10; $page_range = $this->params()->fromRoute('page_range') ? (int) $this->params()->fromRoute('page_range') : 7; $select->order($order_by . ' ' . $order); $search_by = $this->params()->fromRoute('search_by') ? $this->params()->fromRoute('search_by') : ''; $where = new \Zend\Db\Sql\Where(); $formdata = array(); if (!empty($search_by)) { $formdata = (array) json_decode($search_by); if (!empty($formdata['user_id'])) { $where->addPredicate(new \Zend\Db\Sql\Predicate\Like('user_id', '%' . $formdata['user_id'] . '%')); } if (!empty($formdata['role_id'])) { $where->addPredicate(new \Zend\Db\Sql\Predicate\Like('role_id', '%' . $formdata['role_id'] . '%')); } } if (!empty($where)) { $select->where($where); } $paginator = $this->getUserRolesTable()->fetchAll($select, true); $paginator->setCurrentPageNumber($page)->setItemCountPerPage($item_per_page)->setPageRange($page_range); $totalRecord = $paginator->getTotalItemCount(); $currentPage = $paginator->getCurrentPageNumber(); $totalPage = $paginator->count(); $searchform->setData($formdata); $this->vm->setVariables(array('flashMessages' => $this->flashMessenger()->getMessages(), 'search_by' => $search_by, 'order_by' => $order_by, 'order' => $order, 'page' => $page, 'item_per_page' => $item_per_page, 'paginator' => $paginator, 'pageAction' => 'user_roles/index', 'form' => $searchform, 'totalRecord' => $totalRecord, 'currentPage' => $currentPage, 'totalPage' => $totalPage)); return $this->vm; }
public function getNewsList() { $select = new Select(); $select->columns(['content' => 'en', 'title' => 'en_title', 'url' => 'en_title', 'date']); $select->from($this->table); $select->order('date DESC'); return new \Zend\Paginator\Paginator(new \Zend\Paginator\Adapter\DbSelect($select, $this->adapter, $this->resultSetPrototype)); }
public function fetchAll() { $select = new Select('orders'); $select->order('order_id desc'); $paginatorAdapter = new DbSelect($select, $this->adapter); $collection = new OrderCollection($paginatorAdapter); return $collection; }
/** * (non-PHPdoc) * * @see \MwAdmin\Service\DataTableProviderInterface::getDataTableSelect() */ public function getDataTableSelect($page = 1, $perPage = 10) { $table = $this->getDataTableGateway(); $sql = new Select($table->getTable()); $sql->order($this->getDefaultSort()); $sql->limit($perPage, $page * $perPage - $perPage); return $sql; }
/** * Setting ordering */ protected function order() { $column = $this->getParamAdapter()->getColumn(); $order = $this->getParamAdapter()->getOrder(); if ($column) { $this->select->reset('order'); $this->select->order($column . ' ' . $order); } }
public function fetchAll($paginate = true, $filter = array(), $orderBy = array()) { if ($paginate) { $select = new Select('state'); $select->join('country', 'country.id = state.country_id', array('country_name'), 'left'); $select->join('lookup_status', 'lookup_status.status_id = state.status_id', array('status'), 'left'); /* Data filter code start here*/ if (count($filter) > 0) { $filter['country_id'] != "" ? $select->where("state.country_id = " . $filter['country_id']) : ""; $filter['state_code'] != "" ? $select->where("state.state_code LIKE '%" . $filter['state_code'] . "%'") : ""; $filter['state_name'] != "" ? $select->where("state.state_name LIKE '%" . $filter['state_name'] . "%'") : ""; $filter['status_id'] != "" ? $select->where("state.status_id = " . $filter['status_id']) : ""; } /* Data filter code end here*/ /* Data sorting code starts here */ if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') { switch ($orderBy['sort_field']) { case 'state_name': $select->order('state.state_name ' . $orderBy['sort_order']); break; case 'country': $select->order('country.country_name ' . $orderBy['sort_order']); break; case 'status': $select->order('lookup_status.status ' . $orderBy['sort_order']); break; } } else { $select->order('state.state_name ASC'); } /* Data sorting code ends here */ //echo str_replace('"','',$select->getSqlString()); exit; $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new States()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } return $this->tableGateway->select(function (Select $select) { $select->order('state_name ASC'); }); }
public function getLogsByJob($id) { $jobid = (int) $id; $bsqlch = new BareosSqlCompatHelper($this->getDbDriverConfig()); $select = new Select(); $select->from($bsqlch->strdbcompat("Log")); $select->where($bsqlch->strdbcompat("JobId") . " = " . $jobid); $select->order($bsqlch->strdbcompat("LogId") . " DESC"); $resultSet = $this->tableGateway->selectWith($select); return $resultSet; }
/** * Sort results * * Default implementation: * - NULL: type specific default * - "id": item ID * - other: query table's hydrator for matching column name * * @param string $order Property to sort by * @param string $direction One of asc|desc */ public function order($order, $direction) { if (is_null($order)) { $tableClass = get_class($this->_table); $order = $this->_defaultOrder[substr($tableClass, strrpos($tableClass, '\\') + 1)]; } if ($order != 'id') { $order = $this->_table->getHydrator()->extractName($order); } $this->_select->order(array($this->_table->table . ".{$order}" => $direction)); }
public function listemail(Select $select = null) { if (null === $select) { $select = new Select(); } $select->from($this->table); $select->order('id DESC'); $resultSet = $this->selectWith($select); $resultSet->buffer(); return $resultSet; }
protected function _applyOrder() { if (!empty($this->_orders)) { $orderBy = array(); foreach ($this->_orders as $field => $dir) { $orderBy[] = $field . ' ' . ($dir == SORT_DESC ? 'DESC' : 'ASC'); } $this->_select->order($orderBy); } return $this; }
public function getlistnews($limit) { $select = new Select('catelog'); $select->order('id DESC'); $select->limit($limit); $resultSet = $this->tableGateway->selectWith($select); $array = array(); foreach ($resultSet as $value) { array_push($array, $value); } return $array; }
public function fetchLimitedPosts($perPage, $offset) { $select = new Select(self::$_tableName); $select->order('datetime DESC')->limit($perPage)->offset($offset); $socialPosts = $this->selectWith($select); $socialPosts = $socialPosts->toArray(); // Unserialize cached feed entry API-responses foreach ($socialPosts as &$post) { $post['data'] = json_decode($post['data'], true); } return $socialPosts; }
public function getMany($where = array(), $params = array()) { $select = new Select(self::TABLE_NAME); $select->where($where); $select->order("p_user_id DESC"); if (!empty($params)) { foreach ($params as $key => $value) { $select->{$key}($value); } } return $this->tableGateway->selectWith($select); }
public function fetchAll($paginate = true, $orderBy = array()) { if ($paginate) { $select = new Select('banner_booking'); $select->columns(array(new Expression("banner_booking.id, banner_booking.status_id, invoice.status_id AS invoice_status,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS payment_status, booking_date, start_date, end_date"))); $select->join('users', 'users.id = banner_booking.user_id', array('first_name', 'last_name'), 'inner'); $select->join('invoice', 'invoice.id = banner_booking.invoice_id', array('invoice_total', 'created_date'), 'inner'); $select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner'); $select->join('payment_history', 'payment_history.invoice_id = invoice.id', array('currency'), 'inner'); $select->join('lookup_status', 'lookup_status.status_id = banner_booking.status_id', array('status'), 'left'); /* Data sorting code starts here */ if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') { switch ($orderBy['sort_field']) { case 'name': $select->order('users.first_name ' . $orderBy['sort_order']); break; case 'date': $select->order('booking_date ' . $orderBy['sort_order']); break; case 'start_date': $select->order('start_date ' . $orderBy['sort_order']); break; case 'end_date': $select->order('end_date ' . $orderBy['sort_order']); break; case 'plan': $select->order('invoice_details.sale_item_details ' . $orderBy['sort_order']); break; } } /* Data sorting code ends here */ //echo str_replace('"', '', $select->getSqlString()); exit; $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new BannerBookings()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } return $this->tableGateway->select(); }
public function fetchAll($paginate = true, $orderBy = array()) { if ($paginate) { $select = new Select('user_subscriptions'); $select->columns(array(new Expression('user_subscriptions.id, user_subscriptions.subscription_start_date, user_subscriptions.subscription_end_date, user_subscriptions.status_id, CASE subscription_duration.duration_in WHEN 1 THEN "Years" WHEN 2 THEN "Months" WHEN 3 THEN "Days" END AS duration_in, invoice.status_id AS invoice_status, CASE invoice.status_id WHEN 0 THEN "Unpaid" WHEN 1 THEN "Paid" WHEN 2 THEN "Partially Paid" END AS payment_status'))); $select->join('users', 'users.id = user_subscriptions.user_id', array('first_name', 'last_name'), 'inner'); $select->join('subscription_duration', 'subscription_duration.id = user_subscriptions.subscription_duration_id', array('duration'), 'inner'); $select->join('subscription', 'subscription.id = subscription_duration.subscription_id', array('subscription_name'), 'inner'); $select->join('invoice', 'invoice.id = user_subscriptions.invoice_id', array('invoice_total'), 'inner'); $select->join('lookup_status', 'lookup_status.status_id = user_subscriptions.status_id', array('status'), 'left'); $select->join('payment_history', 'payment_history.invoice_id = user_subscriptions.invoice_id', array('currency'), 'inner'); /* Data sorting code starts here */ if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') { switch ($orderBy['sort_field']) { case 'name': $select->order('users.first_name ' . $orderBy['sort_order']); break; case 'subscription': $select->order('subscription.subscription_name ' . $orderBy['sort_order']); break; case 'start': $select->order('user_subscriptions.subscription_start_date ' . $orderBy['sort_order']); break; case 'end': $select->order('user_subscriptions.subscription_end_date ' . $orderBy['sort_order']); break; } } /* Data sorting code ends here */ //echo str_replace('"','',$select->getSqlString()); exit; $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new Subscriptions()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } return $this->tableGateway->select(); }