/** * 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 setParameters(Parameters $params) { $groupsPostsTable = Api::_()->getDbTable('Group\\DbTable\\GroupsPosts'); $groupsPostsTableName = $groupsPostsTable->initTableName()->getTable(); $groupsCategoriesTable = Api::_()->getDbTable('Group\\DbTable\\CategoriesGroups'); $groupsCategoriesTableName = $groupsCategoriesTable->initTableName()->getTable(); if ($params->group_id || $params->groupCategory) { $params->inGroup = true; } if ($params->inGroup) { $groupId = $params->group_id; $categoryId = $params->groupCategory; $this->where(function ($where) use($groupsPostsTableName, $groupsCategoriesTableName, $groupId, $categoryId) { $select = new Select($groupsPostsTableName); $select->columns(array('post_id')); if ($groupId) { $select->where(array('group_id' => $groupId)); } if ($categoryId) { $cateSelect = new Select($groupsCategoriesTableName); $cateSelect->columns(array('group_id')); $cateSelect->where(array('category_id' => $categoryId)); $select->where(function ($where) use($cateSelect) { $where->in('group_id', $cateSelect); return $where; }); } $where->in('id', $select); return $where; }); } return parent::setParameters($params); }
public function getBuilder() { if (empty($this->parsed['SELECT'])) { throw new \InvalidArgumentException('Queries other than SELECT are not supported yet'); } $sql = new Select(); $sql->columns($this->getBaseExprs($this->parsed['SELECT'])); foreach ($this->parsed['FROM'] as $table) { if (!$table['ref_type']) { $sql->from(array($table['alias']['name'] => $table['table'])); continue; } if ('JOIN' == $table['join_type']) { $table['join_type'] = 'INNER'; } $sql->join(array($table['alias']['name'] => $table['table']), join(' ', $this->getBaseExprs($table['ref_clause'])), array(), $table['join_type']); } if (isset($this->parsed['GROUP'])) { $sql->group($this->getBaseExprs($this->parsed['GROUP'])); } if (isset($this->parsed['WHERE'])) { $sql->where(join(' ', $this->getBaseExprs($this->parsed['WHERE']))); } unset($this->parsed['WHERE'], $this->parsed['GROUP'], $this->parsed['FROM'], $this->parsed['WHERE']); var_dump($this->parsed); #die('Stopped at ' . __FILE__ . ' on line ' . __LINE__); return $sql; }
/** * Returns all the comments pending approval for a thread. * * @param string $thread * @return ResultSet */ public function fetchAllPendingForThread($thread) { $select = new Select($this->tableGateway->getTable()); $select->columns(array('id', 'author', 'content', 'contact', 'published_on_raw' => 'published_on', 'published_on' => new Expression("DATE_FORMAT(published_on, '%M %d, %Y %H:%i')")))->where(array('thread' => $thread, 'visible' => 0))->order('published_on_raw ASC'); $resultSet = $this->tableGateway->selectWith($select); return $resultSet; }
public function setUp() { $this->column = $this->getMockBuilder('ZfcDatagrid\\Column\\Select')->disableOriginalConstructor()->getMock(); $this->column->method('getSelectPart1')->willReturn('myCol'); $this->column->method('getType')->willReturn(new \ZfcDatagrid\Column\Type\PhpString()); $this->column->setUniqueId('myCol'); $this->column->setSelect('myCol'); $this->column2 = $this->getMockBuilder('ZfcDatagrid\\Column\\Select')->disableOriginalConstructor()->getMock(); $this->column2->method('getSelectPart1')->willReturn('myCol2'); $this->column2->method('getType')->willReturn(new \ZfcDatagrid\Column\Type\PhpString()); $this->column2->setUniqueId('myCol2'); $this->column2->setSelect('myCol2'); $this->mockDriver = $this->getMock('Zend\\Db\\Adapter\\Driver\\DriverInterface'); $this->mockConnection = $this->getMock('Zend\\Db\\Adapter\\Driver\\ConnectionInterface'); $this->mockDriver->expects($this->any())->method('checkEnvironment')->will($this->returnValue(true)); $this->mockDriver->expects($this->any())->method('getConnection')->will($this->returnValue($this->mockConnection)); $this->mockPlatform = $this->getMock('Zend\\Db\\Adapter\\Platform\\PlatformInterface'); $this->mockStatement = $this->getMock('Zend\\Db\\Adapter\\Driver\\StatementInterface'); $this->mockDriver->expects($this->any())->method('createStatement')->will($this->returnValue($this->mockStatement)); $this->adapter = new Adapter($this->mockDriver, $this->mockPlatform); $sql = new Sql($this->adapter, 'foo'); $select = new Select('myTable'); $select->columns(['myCol', 'myCol2']); $this->filterSelect = new FilterSelect($sql, $select); }
public function getDetails_OderDetails($id_user, $id_oeder) { $select = new Select('odersdetails'); $select->columns(array('id_detail' => 'id', 'description' => 'description', 'status_details' => 'status', 'quantity' => 'quantity', 'cpmRate' => 'cpmRate', 'amount' => 'amount', 'type_details' => 'type', 'AdobeFlashEnabled' => 'AdobeFlashEnabled', 'MaxDailyBudget' => 'MaxDailyBudget', 'MobileTargeting' => 'MobileTargeting', 'trafficsources' => 'trafficsources', 'PreviousWebsite' => 'PreviousWebsite', 'payment_select' => 'payment_select'))->join('oders', 'oders.id = odersdetails.oder_id', array('id' => 'id', 'creatnamecampaign' => 'creatnamecampaign', 'date_creat' => 'date_creat', 'date_mod' => 'date_mod', 'id_user' => 'id_user', 'status' => 'status', 'status_oder' => 'status_oder', 'type' => 'type', 'DolarTotal' => 'DolarTotal', 'TotaVistor' => 'TotaVistor', 'Cpmrate' => 'Cpmrate'), 'left')->join('ads', 'ads.oder_id = oders.id', array('id_code' => 'id', 'delay' => 'delay', 'time_display' => 'time_display', 'namecampaigns' => 'name', 'js_id' => 'js_id', 'status_code' => 'status'), 'left')->join('process', 'process.oder_id = oders.id', array('date_reg' => 'date_reg', 'date_active' => 'date_active', 'checked' => 'checked', 'id_proc' => 'id', 'ads_id' => 'ads_id', 'advertiser_id' => 'advertiser_id', 'publisher_id' => 'publisher_id', 'type_id' => 'type_id', 'status_process' => 'status', 'website' => 'website'), 'left')->where(array('odersdetails.oder_id' => $id_oeder)); //->order ( 'oders.id DESC' )->limit ( $limit )->offset ( $offset ); $rowset = $this->tableGateway->getSql()->prepareStatementForSqlObject($select); $results = $rowset->execute(); /* <div id="shoppingnew"> <input type="hidden" class="form-control" id="spVietnamid" name="palpost[Vietnam][id]" value="218"> <input type="hidden" class="form-control" id="spVietnamoder_id" name="palpost[Vietnam][oder]" value="105"> <input type="hidden" class="form-control" id="spVietnamtype" name="palpost[Vietnam][type]" value="0"> <input type="hidden" class="form-control" id="spVietnamnamepackge" name="palpost[Vietnam][namepackge]" value="Vietnam"> <input type="hidden" class="form-control" id="spVietnamprice" name="palpost[Vietnam][price]" value="0.4"> <input type="hidden" class="form-control" id="spVietnamvistor" name="palpost[Vietnam][vistor]" value="5"> <input type="hidden" class="form-control" id="spVietnamamount" name="palpost[Vietnam][amount]" value="2"> </div> */ $i = 0; $array = array(); foreach ($results as $key => $value) { $array[$i]['namepackge'] = $value['description']; $array[$i]['id'] = $value['id_detail']; // id details $array[$i]['oder'] = $value['id']; // oder id $array[$i]['type'] = $value['type_details']; $array[$i]['price'] = $value['cpmRate']; $array[$i]['vistor'] = $value['quantity']; $array[$i]['amount'] = $value['cpmRate'] * $value['quantity']; $i++; } return $array; }
/** * * @param int $days_threshold days threshold * @param int $ca_threshold turnover threshold * @param int $limit * @return type */ public function getCustomerGeo($days_threshold = 300, $ca_threshold = 1000, $min_accuracy = 6, $limit = 1000) { $akilia2db = $this->configuration['synchronizer']['db_akilia2']; $select = new Select(); $bcg = new \Zend\Db\Sql\TableIdentifier('base_customer_geo', $akilia2db); $bc = new \Zend\Db\Sql\TableIdentifier('base_customer', $akilia2db); $bs = new \Zend\Db\Sql\TableIdentifier('base_state', $akilia2db); $bco = new \Zend\Db\Sql\TableIdentifier('base_country', $akilia2db); $so = new \Zend\Db\Sql\TableIdentifier('sal_order', $akilia2db); $sol = new \Zend\Db\Sql\TableIdentifier('sal_order_line', $akilia2db); $select->from(["bc" => $bc], [])->join(['bcg' => $bcg], "bc.id = bcg.customer_id", [], Select::JOIN_LEFT)->join(['bs' => $bs], "bs.id = bc.state_id", [], Select::JOIN_LEFT)->join(['bco' => $bco], "bco.id = bc.country_id", [], Select::JOIN_LEFT)->join(['so' => $so], "bc.id = so.customer_id", [], Select::JOIN_INNER)->join(['sol' => $sol], "so.id = sol.order_id", [], Select::JOIN_INNER)->where('bc.flag_archived <> 1'); $columns = ['customer_id' => new Expression('bc.id'), 'name' => new Expression('bc.name'), 'street' => new Expression('bc.street'), 'street_2' => new Expression('bc.street_2'), 'street_number' => new Expression('bc.street_number'), 'state_reference' => new Expression('bs.reference'), 'state_name' => new Expression('bs.name'), 'zipcode' => new Expression('bc.zipcode'), 'city' => new Expression('bc.city'), 'country' => new Expression('bco.name'), 'accuracy' => new Expression('bcg.accuracy'), 'latitude' => new Expression('bcg.latitude'), 'longitude' => new Expression('bcg.longitude')]; $select->columns(array_merge($columns, ['total_net' => new Expression('sum(sol.price_total_net)')]), true); $select->group($columns); $select->having("sum(sol.price_total_net) > {$ca_threshold}"); $select->where(function (Where $where) use($min_accuracy) { //$where->greaterThan('so.date_order', '2012-12-31'); $where->notLike('bc.name', '%FINISHED%'); $where->nest->lessThan('accuracy', $min_accuracy)->or->isNull('accuracy')->unnest; }); $select->where(new Expression("(TO_DAYS(NOW()) - TO_DAYS(so.date_order)) < {$days_threshold}")); if ($limit > 0) { $select->limit($limit); } $store = $this->getStore($select); $data = $store->getData()->toArray(); return $data; }
public function find($params) { $sql = new Sql($this->getAdapter()); $select = new Select(); $select->from($params['from']); if (!empty($params['columns'])) { $select->columns($params['columns']); } foreach ($params['where'] as $where) { $select->where($where); } foreach ($params['joins'] as $join) { if (empty($join['columns'])) { $join['columns'] = Select::SQL_STAR; } if (empty($join['type'])) { $join['type'] = Select::JOIN_INNER; } $select->join($join['name'], $join['on'], $join['columns'], $join['type']); } $query = $sql->getSqlStringForSqlObject($select); $results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE); $data = $results->toArray(); if (empty($data)) { return false; } else { if (count($data) == 1) { return $data[0]; } else { return $data; } } }
public function fetchAll($paginated = false, $sort = 'name', $order = 'ASC') { switch ($sort) { case "id": $sort = "C_Id"; break; case "name": $sort = "C_Name"; break; case "lastinvoice": $sort = "C_LastInvoice"; break; default: $sort = "C_Name"; } $select = new Select(); $select->from($this->table); $subquery = "(SELECT I_BaseDate FROM Invoice WHERE Invoice.I_ClientId = Client.C_Id ORDER BY I_Id DESC LIMIT 1)"; $select->columns(array('C_Id' => 'C_Id', 'C_Name' => 'C_Name', 'C_PaymentTerms' => 'C_PaymentTerms', 'C_Reference' => 'C_Reference', 'C_CRN' => 'C_CRN', 'C_Class' => 'C_Class', 'C_Updated' => 'C_Updated', 'C_LastInvoice' => new Expression($subquery)))->order($sort . ' ' . $order); if ($paginated) { return new Paginator(new DbSelect($select, $this->adapter, $this->resultSetPrototype)); } $resultSet = $this->select($select); return $resultSet; }
/** * @testdox unit test: Test columns() returns Select object (is chainable) * @covers Zend\Db\Sql\Select::columns */ public function testColumns() { $select = new Select(); $return = $select->columns(array('foo', 'bar')); $this->assertSame($select, $return); return $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 fetchDrafts() { $select = new Select(); $select->from($this->table); $select->columns(array('I_Id' => 'I_Id', 'I_Number' => 'I_Number', 'I_BaseDate' => 'I_BaseDate', 'I_Vat' => 'I_Vat', 'I_DueDays' => 'I_DueDays', 'I_SentDate' => 'I_SentDate', 'I_PayedDate' => 'I_PayedDate', 'I_ClientId' => 'I_ClientId', 'I_ClientReference' => 'I_ClientReference', 'I_OwnReference' => 'I_OwnReference', 'I_AddressId' => 'I_AddressId', 'I_Text' => 'I_Text', 'I_Updated' => 'I_Updated'))->where(array(new \Zend\Db\Sql\Predicate\IsNull('I_Number')))->join('Client', 'Invoice.I_ClientId = Client.C_Id'); $resultSet = $this->selectWith($select); return $resultSet; }
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 getLoginUser() { $select = new Select(self::TABLE_NAME); $columns = array('emailId' => 'p_user_email_id', 'password' => 'p_user_password'); $select->columns($columns); $sql = new Sql($this->tableGateway->getAdapter()); $db = $this->tableGateway->getAdapter()->getDriver()->getConnection()->getResource(); return $this->getSqlContent($db, $sql, $select); }
public function get($id, $status = null) { $select = new Select(); $select->from($this->tableGateway->getTable()); $select->columns(array('*')); $select->where($status === null ? array('users.id' => $id) : array('users.id' => $id, 'users.status' => $status)); $resultSet = $this->tableGateway->selectWith($select); $row = $resultSet->current(); return !$row ? false : $row; }
public function getActiveProducts() { $akilia1db = $this->configuration['synchronizer']['db_akilia1']; $select = new Select(); $select->from(["a" => new \Zend\Db\Sql\TableIdentifier('article', $akilia1db)], [])->where('a.flag_archive <> 1'); $select->columns(['id_article' => new Expression('TRIM(a.id_article)'), 'reference' => new Expression('a.reference'), 'id_marque' => new Expression('a.id_marque')], true); $store = $this->getStore($select); $data = $store->getData(); return $data; }
public function getSkillCount() { $select = new Select(); $select->from($this->table); $select->columns(array('skills_count' => new \Zend\Db\Sql\Expression('COUNT(skill_id)'))); //$select->where(array("skill_status"=>1)); $resultSet = $this->selectWith($select); $rowset = $resultSet->current(); return $rowset["skills_count"]; }
public function getDeptCount() { $select = new Select(); $select->from($this->table); $select->columns(array('departments_count' => new \Zend\Db\Sql\Expression('COUNT(dept_id)'))); //$select->where(array("{$this->table}.parent_id is NULL")); $resultSet = $this->selectWith($select); $rowset = $resultSet->current(); return $rowset["departments_count"]; }
/** * Sets the filter * @param \Zend\Db\Sql\Select $select * @param array $criteria * @return \Zend\Db\Sql\Select */ private function setFilter($select, $criteria) { $select->columns(array('*', 'role' => new \Zend\Db\Sql\Predicate\Expression('(SELECT `description` FROM `profile_role` WHERE `profile_role`.`id`=`profile`.`role_id` )'))); if (array_key_exists('role_id', $criteria) && $criteria['role_id'] != '') { $select->where('`profile`.`role_id` = ' . intval($criteria['role_id'])); } if (array_key_exists('sSearch', $criteria) && $criteria['sSearch'] != '') { $select->where(' ( `profile`.`first_name` LIKE "' . addslashes($criteria['sSearch']) . '%" OR `profile`.`last_name` LIKE "' . addslashes($criteria['sSearch']) . '%" ) '); } return $select; }
public function getPostCountForThread($threadId) { $predicate = new Predicate(null, Predicate::OP_AND); $predicate->equalTo('thread_id', $threadId); $select = new Select(); $select->from($this->tableName); $select->columns(['count' => new Expression('COUNT(*)')]); $select->where($predicate); $result = $this->select($select); return $result->toArray(); }
public function fetchBrands() { $select = new Select(); $select->from('brands'); $select->columns(array('b_name')); $resultSet = $this->tableGateway->selectWith($select); foreach ($resultSet as $res) { $selectData[$res['id']] = $res['title']; } return $selectData; }
public function getForums() { $selectCount = $this->getThreadCounts(); $selectLatest = $this->getForumLatest(); $select = new Select(); $select->from($this->tableName); $select->join(['count' => $selectCount], new Expression('forum.id = count.forum_id'), [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER)->join(['latest' => $selectLatest], new Expression('forum.id = latest.forum_id'), [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER)->join('forum_category', new Expression('forum.forum_category_id = forum_category.id'), [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER); $select->columns(['id' => new Expression('forum.id'), 'title' => new Expression('forum.title'), 'description' => new Expression('forum.description'), 'minimum_access' => new Expression('forum.minimum_access'), 'category_id' => new Expression('forum_category.id'), 'category' => new Expression('forum_category.category'), 'thread_count' => new Expression('count_for_forum'), 'last_poster' => new Expression('last_post'), 'last_post_date' => new Expression('latest.date_updated'), 'last_thread' => new Expression('last_thread'), 'last_thread_id' => new Expression('last_thread_id')], false); $result = $this->select($select); return $result->toArray(); }
public function fetchAll($cityId, $serviceId, $categoryId, $deliveryType = 'Regular') { $select = new Select('item_price'); $select->columns(array("item_price_id", "item_id", "service_id", "item_city_id", "delivery_type_name", "price")); $select->join("items", "items.item_id = item_price.item_id", array("item_name")); $select->where(" item_city_id = '{$cityId}' and item_price.service_id = '{$serviceId}' and items.category_id = '{$categoryId}' and delivery_type_name = '{$deliveryType}' "); // echo '<pre>'; print_r($select->getSqlString()); exit('Macro die'); $paginatorAdapter = new DbSelect($select, $this->adapter); $collection = new RateCollection($paginatorAdapter); return $collection; }
public function getCategorySpecificationUncheckValue() { $select = new Select($this->tableGateway->getTable()); $select->columns(array('specification')); $select->join('specifications', "specifications.id = " . $this->tableGateway->getTable() . ".specification", array('specification' => 'id', 'specification_name' => 'name'), 'right'); $select->where(array($this->tableGateway->getTable() . '.specification' => null)); $resulset = $this->tableGateway->selectWith($select); //echo str_replace('"', '', $select->getSqlString()); //exit(); return $resulset; }
public function getMostRecentListing($id = null) { $subSelect = new Select(); $subSelect->columns(array('mostRecent' => new Expression('MAX(`listings_id`)'))); $subSelect->from(ListingsTable::$tableName); $where = new Where(); $where->in('listings_id', $subSelect); $select = new Select(); $select->where($where)->from(ListingsTable::$tableName); return $this->selectWith($select)->current(); }
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(); }
function getCountry($id) { $id = (int) $id; $select = new Select($this->tableGateway->getTable()); $select->columns(['id', 'code', 'alpha2', 'alpha3', 'nom_en_gb', 'nom_fr_fr', 'devise', 'taux_tva'])->where(['id' => $id]); $rowSet = $this->tableGateway->selectWith($select); $row = $rowSet->current(); if (!$row) { throw new \Exception("could not find row {$id}"); } return $row; }
public function getSerialName($category) { $select = new Select($this->tableGateway->getTable()); $select->columns(array('id', 'name')); $select->join('categories_serials_name', '' . $this->tableGateway->getTable() . '.id = categories_serials_name.serial_name', array(), 'inner'); $rows = $this->tableGateway->selectWith($select); $result = array(); foreach ($rows as $row) { $result[$row->getId()] = $row->getName(); } return $result; }
public function get($id, $user) { $select = new Select($this->tableGateway->getTable()); $select->columns(array("id", "register_date", "guide", "observations")); $select->join('customers', "customers.id = " . $this->tableGateway->getTable() . ".client", array('client_first_name' => 'first_name', 'client_last_name' => 'first_name'), 'inner'); $select->join(array('sellers' => 'customers'), "sellers.id = " . $this->tableGateway->getTable() . ".seller", array('seller_first_name' => 'first_name', 'seller_last_name' => 'last_name', 'seller_company' => 'company'), 'inner'); $select->join('payments_methods', "payments_methods.id = " . $this->tableGateway->getTable() . ".payment_method", array('payment_method_name' => 'name'), 'inner'); $select->where(array($this->tableGateway->getTable() . ".id" => $id, $this->tableGateway->getTable() . ".user" => $user)); $rowset = $this->tableGateway->selectWith($select); $row = $rowset->current(); return $row; }
public function searchSerial($serial) { $select = new Select($this->tableGateway->getTable()); $select->columns(array()); $select->join("products", "products.id = " . $this->tableGateway->getTable() . ".product", array('id' => 'id')); $select->join("products_receive_inventory", "products_receive_inventory.details_receive_inventory = " . $this->tableGateway->getTable() . ".id", array('serial' => 'serial')); $select->where(array("products_receive_inventory.status" => 0)); $select->where->like("products_receive_inventory.serial", "%" . $serial . "%"); error_log($select->getSqlString()); $result = $this->tableGateway->selectWith($select); return $result; }