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