/** * @covers Zend\Db\Sql\Sql::select */ public function testSelect() { $select = $this->sql->select(); $this->assertInstanceOf('Zend\\Db\\Sql\\Select', $select); $this->assertSame('foo', $select->getRawState('table')); $this->setExpectedException('Zend\\Db\\Sql\\Exception\\InvalidArgumentException', 'This Sql object is intended to work with only the table "foo" provided at construction time.'); $this->sql->select('bar'); }
public function xhbExists($xhbId) { try { $select = $this->_sql->select(Xhb::MAIN_TABLE); $select->columns(array('id'))->where(array('id' => $xhbId)); $result = $this->_db->query($this->_sql->buildSqlString($select), Adapter::QUERY_MODE_EXECUTE); } catch (\RuntimeException $e) { return false; } return count($result) > 0; }
public function getChiTietPhieuDoiTra($array_conditions = array(), $array_columns_1 = array(), $array_columns_2 = array(), $array_columns_3 = array(), $array_columns_4 = array(), $array_columns_5 = array(), $array_columns_6 = array(), $array_columns_7 = array()) { /* chuyền vào 2 tham số: 1 tham số là mảng điều kiện, 1 tham số là mảng cột ở bảng 1 cần lấy ra, 1 tham số là mảng cột ở bảng 2 cần lấy ra, */ $adapter = $this->tableGateway->adapter; $sql = new Sql($adapter); // select $sqlSelect = $sql->select(); $sqlSelect->from(array('t1' => 'phieu_doi_tra')); $sqlSelect->join(array('t2' => 'hoa_don'), 't1.id_hoa_don=t2.id_hoa_don', $array_columns_2, 'LEFT'); $sqlSelect->join(array('t3' => 'ct_hoa_don'), 't2.id_hoa_don=t3.id_hoa_don', $array_columns_3, 'LEFT'); $sqlSelect->join(array('t4' => 'san_pham'), 't3.id_san_pham=t4.id_san_pham', $array_columns_4, 'LEFT'); $sqlSelect->join(array('t5' => 'khach_hang'), 't2.id_khach_hang=t5.id_khach_hang', $array_columns_5, 'LEFT'); $sqlSelect->join(array('t6' => 'kenh_phan_phoi'), 't5.id_kenh_phan_phoi=t6.id_kenh_phan_phoi', $array_columns_6, 'LEFT'); $sqlSelect->join(array('t7' => 'don_vi_tinh'), 't4.id_don_vi_tinh=t7.id_don_vi_tinh', $array_columns_7, 'LEFT'); if ($array_columns_1) { $sqlSelect->columns($array_columns_1); } if ($array_conditions) { $sqlSelect->where($array_conditions); } $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect); $resultSets = $statement->execute(); $allRow = array(); foreach ($resultSets as $key => $resultSet) { $allRow[] = $resultSet; } return $allRow; }
public function getPerTime($where_params = null, $is_super_admin = 0) { $obj_list = array(); $sql = new Sql($this->adapter); $select = $sql->select(); $select->from('publisherHourlyBidsBidsPerTime'); if (!empty($where_params['DateCreatedGreater'])) { $select->where($select->where->greaterThanOrEqualTo('DateCreated', $where_params['DateCreatedGreater'])); } if (!empty($where_params['DateCreatedLower'])) { $select->where($select->where->lessThanOrEqualTo('DateCreated', $where_params['DateCreatedLower'])); } $statement = $sql->prepareStatementForSqlObject($select); $results = $statement->execute(); foreach ($results as $obj) { if (!$is_super_admin) { array_walk($obj, function ($item, $key) use(&$obj) { if (array_search($key, $this->adminFields) !== FALSE) { $obj[$key] = FALSE; } }); $obj = array_filter($obj, function ($value) { return $value !== FALSE; }); } $obj['MDYH'] = $this->re_normalize_time($obj['MDYH']); $obj_list[] = $obj; } return $obj_list; }
public function getPhieuThuAndUserByArrayConditionAnd2ArrayColumn($array_conditions = array(), $array_columns_1 = array(), $array_columns_2 = array()) { /* chuyền vào 2 tham số: 1 tham số là mảng điều kiện, 1 tham số là mảng cột ở bảng 1 cần lấy ra, 1 tham số là mảng cột ở bảng 2 cần lấy ra, */ $adapter = $this->tableGateway->adapter; $sql = new Sql($adapter); // select $sqlSelect = $sql->select(); $sqlSelect->from(array('t1' => 'phieu_thu')); $sqlSelect->join(array('t2' => 'user'), 't1.id_user=t2.user_id', $array_columns_2, 'LEFT'); if ($array_columns_1) { $sqlSelect->columns($array_columns_1); } if ($array_conditions) { $sqlSelect->where($array_conditions); } $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect); $resultSets = $statement->execute(); $allRow = array(); foreach ($resultSets as $key => $resultSet) { $allRow[] = $resultSet; } return $allRow; }
/** * * @return Select */ public function select() { if ($this->select === null) { $this->select = $this->sql->select(); } return $this->select; }
/** * {@inheritdoc} */ public function findUsersOfSite($siteId, $order = null, $paginated = false) { $sql = new Sql($this->dbAdapter); $membershipCols = array(); foreach (DbSelectColumns::MEMBERSHIP as $col) { $membershipCols[DbViewMembership::TABLE . '_' . $col] = $col; } $activityCols = array(); foreach (DbSelectColumns::USER_ACTIVITY as $col) { $activityCols[DbViewUserActivity::TABLE . '_' . $col] = $col; } $userCols = array(); foreach (DbSelectColumns::USER as $col) { $userCols[DbViewUsers::TABLE . '_' . $col] = $col; } $select = $sql->select()->from(array('u' => DbViewUsers::TABLE))->columns($userCols)->join(array('a' => DbViewUserActivity::TABLE), 'u.' . DbViewUsers::USERID . ' = a.' . DbViewUserActivity::USERID, $activityCols)->join(array('m' => DbViewMembership::TABLE), 'm.' . DbViewMembership::USERID . ' = u.' . DbViewUsers::USERID . ' AND m.' . DbViewMembership::SITEID . ' = a.' . DbViewUserActivity::SITEID, $membershipCols, Select::JOIN_LEFT)->where(array('a.' . DbViewUserActivity::SITEID . ' = ?' => $siteId, '(m.' . DbViewMembership::JOINDATE . ' IS NOT NULL OR (a.' . DbViewUserActivity::VOTES . ' > 0) OR (a.' . DbViewUserActivity::REVISIONS . ' > 0) or (a.' . DbViewUserActivity::PAGES . ' > 0))')); if (is_array($order)) { $this->orderSelect($select, $order); } $resultSet = new \Zend\Db\ResultSet\HydratingResultSet($this->userSiteHydrator, $this->objectPrototype); if ($paginated) { $adapter = new \Zend\Paginator\Adapter\DbSelect($select, $this->dbAdapter, $resultSet); return new \Zend\Paginator\Paginator($adapter); } return $resultSet->initialize($this->fetch($sql, $select)); }
public function getUserByArrayConditionAndArrayColumn($array_conditions = array(), $array_columns = array()) { /* chuyền vào 2 tham số: 1 tham số là mảng điều kiện, 1 tham số là mảng cột cần lấy ra */ $adapter = $this->tableGateway->adapter; $sql = new Sql($adapter); // select $sqlSelect = $sql->select(); $sqlSelect->from(array('t1' => 'user')); if ($array_columns) { $sqlSelect->columns($array_columns); } if ($array_conditions) { $sqlSelect->where($array_conditions); } $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect); $resultSets = $statement->execute(); $allRow = array(); foreach ($resultSets as $key => $resultSet) { $allRow[] = $resultSet; } return $allRow; }
public function getUserRoles($where = array(), $columns = array(), $orderBy = '', $paging = false) { try { $sql = new Sql($this->getAdapter()); $select = $sql->select()->from(array('sa' => $this->table)); if (count($where) > 0) { $select->where($where); } $select->where($where); if (count($columns) > 0) { $select->columns($columns); } if (!empty($orderBy)) { $select->order($orderBy); } if ($paging) { $dbAdapter = new DbSelect($select, $this->getAdapter()); $paginator = new Paginator($dbAdapter); return $paginator; } else { $statement = $sql->prepareStatementForSqlObject($select); $clients = $this->resultSetPrototype->initialize($statement->execute())->toArray(); return $clients; } } catch (\Exception $e) { throw new \Exception($e->getPrevious()->getMessage()); } }
public function getCertificateUserAndCertificateByArrayConditionAndArrayColumns($array_conditions = array(), $array_columns_1 = array(), $array_columns_2 = array()) { /* chuyền vào 3 tham số: 1 tham số là mảng điều kiện, 1 tham số là mảng cột bảng thứ nhất cần lấy ra, 1 tham số là cột bảng thứ 2 cần lấy */ $adapter = $this->tableGateway->adapter; $sql = new Sql($adapter); // select $sqlSelect = $sql->select(); $sqlSelect->from(array('t1' => 'jos_certificate_user')); $sqlSelect->join(array('t2' => 'jos_certificate'), 't1.certificate_id=t2.value_id', $array_columns_2, 'LEFT'); if ($array_columns_1) { $sqlSelect->columns($array_columns_1); } if ($array_conditions) { $sqlSelect->where($array_conditions); } $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect); $resultSets = $statement->execute(); $allRow = array(); foreach ($resultSets as $key => $resultSet) { $allRow[] = $resultSet; } return $allRow; }
public function getConcertsByFilter($params) { $adapter = $this->tableGateway->getAdapter(); $sql = new Sql($adapter); $select = $sql->select(); $select->from(array('c' => 'concerts'), array('place', 'address', 'city', 'date_concert', 'cost', 'id_artist'))->join(array('a' => 'artists'), 'c.id_artist = a.id', array('name', 'image', 'tags')); // join table with alias if (count($params) == 5) { $select->where(array('city' => $params['city'], 'tags' => $params['tags'], 'cost' => $params['cost']))->where->between('date_concert', $params['date_start'], $params['date_end']); $statement = $sql->prepareStatementForSqlObject($select); $results = $statement->execute(); return $results->current(); } else { foreach ($params as $key => $value) { if ($key == 'date_start') { $select->where('date_concert >= ' . $value); } elseif ($key == 'date_end') { $select->where('date_concert <= ' . $value); } else { $select->where(array($key => $value)); } } $statement = $sql->prepareStatementForSqlObject($select); $results = $statement->execute(); return $results; } }
/** * @return array|PostInterface[] */ public function findAll() { $sql = new Sql($this->dbAdapter); $select = $sql->select('posts'); $stmt = $sql->prepareStatementForSqlObject($select); $result = $stmt->execute(); //return $result; //\Zend\Debug\Debug::dump($result);die(); // 141 //TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT /* if ($result instanceof ResultInterface && $result->isQueryResult()) { $resultSet = new ResultSet(); \Zend\Debug\Debug::dump($resultSet->initialize($result)); // 142 die(); } die("no data"); */ //LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL if ($result instanceof ResultInterface && $result->isQueryResult()) { // 143 $resultSet = new HydratingResultSet($this->hydrator, $this->postPrototype); return $resultSet->initialize($result); // 144 } return array(); // 145 }
function login($data, $adapter) { $sql = new Sql($adapter); $select = $sql->select()->from('users')->where(array('email' => $data['email'], 'password' => md5($data['password'])))->limit(1); $result = $adapter->query($sql->buildSqlString($select), $adapter::QUERY_MODE_EXECUTE); return $result; }
public function getFollowers($profileID) { $sql = new Sql($this->tableGateway->adapter); $select = $sql->select()->from('follows')->where(array('id_user' => $profileID))->join('users', 'users.id = follows.id_follower'); $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($select); $resultSet = $statement->execute(); return $resultSet; }
/** * This function returns the user by ID * @param int $userId * @return array */ public function getUser($userId) { $sql = new Sql($this->tableGateway->getAdapter()); $select = $sql->select()->from(array('u' => 'user'))->join(array('r' => 'role'), 'r.id = u.role_id', array('*'))->where(array('u.id' => $userId))->order('u.id'); $stmt = $sql->prepareStatementForSqlObject($select); $results = $stmt->execute(); return $results; }
public function getRole($roleId) { $sql = new Sql($this->tableGateway->getAdapter()); $select = $sql->select()->from(array('r' => 'role'))->where(array('r.id' => $roleId))->order('r.id'); $stmt = $sql->prepareStatementForSqlObject($select); $results = $stmt->execute(); return $results; }
public function findActiveDeals() { $sql = new \Zend\Db\Sql\Sql($this->getAdapter()); $select = $sql->select()->from($this->tableName)->join('product', 'deal.product=product.productId')->where('DATE(startDate) <= DATE(NOW())')->where('DATE(endDate) >= DATE(NOW())')->where('stock > 0'); $stmt = $sql->prepareStatementForSqlObject($select); $results = $stmt->execute(); return $this->hydrate($results); }
/** * Function for getting role permissions * * @author Kaushal Kishore <*****@*****.**> * @return unknown */ public function getRolePermissions() { $sql = new Sql($this->getAdapter()); $select = $sql->select()->from(array('t1' => 'role'))->columns(array('role_name'))->join(array('t2' => $this->table), 't1.rid = t2.role_id', array(), 'left')->join(array('t3' => 'permission'), 't3.id = t2.permission_id', array('permission_name'), 'left')->join(array('t4' => 'resource'), 't4.id = t3.resource_id', array('resource_name'), 'left')->where('t3.permission_name is not null and t4.resource_name is not null')->order('t1.rid'); $statement = $sql->prepareStatementForSqlObject($select); $result = $this->resultSetPrototype->initialize($statement->execute())->toArray(); return $result; }
/** * Returns all live products * * @return TableEntitySetInterface */ public function getLive() { $sql = new Sql\Sql($this->getAdapter()); $select = $sql->select(); $select->from($this->tableGateway->getTable()); $select->where(array('status' => 1)); $select->order('t_sort asc'); return $this->tableGateway->selectWith($select); }
public function obtenerActivos() { $sql = new Sql($this->tableGateway->adapter); $select = $sql->select(); $select->from('areas'); $select->where(array('areas.areas_estado' => 'A')); $resultSet = $this->tableGateway->selectWith($select); return $resultSet; }
public function count($where = []) { $db = $this->getAdapter(); $sql = new Sql\Sql($db); $select = $sql->select()->from($this->getTable())->columns(array('count' => new Sql\Expression('COUNT(*)')))->where($where); $statement = $sql->prepareStatementForSqlObject($select); $rowset = $statement->execute(); return $rowset->current()['count']; }
public function getAllCmsItemsOfFolder($id) { $sql = new Sql($this->getAdapter()); $select = $sql->select(); $select->from('cms_item')->where(array('fk_cms_folder' => $id)); $statement = $sql->prepareStatementForSqlObject($select); $result = $statement->execute(); return $result; }
public function obtenerTodos() { $sql = new Sql($this->tableGateway->adapter); $select = $sql->select(); $select->from('app_recursos'); $select->order('recursos_ordenamiento ASC'); $resultSet = $this->tableGateway->selectWith($select); return $resultSet; }
public function fetchAll() { $adapter = $this->tableGateway->getAdapter(); $sql = new Sql($adapter, 'Account'); $select = $sql->select(); $select->order('last_accessed ASC'); $selectString = $sql->getSqlStringForSqlObject($select); $resultSet = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE); return $resultSet; }
function loginByAdapter($data, $adapter) { $sql = new Sql($adapter); $select = $sql->select(); $select->from('users'); $select->where(array('email' => $data->email, 'pass_word' => md5($data->password))); $selectString = $sql->buildSqlString($select); $results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE); return $results; }
/** * Returns all notifications for user $userId * * @param integer $userId * @return TableEntitySetInterface */ public function getForUser($userId) { $sql = new Sql\Sql($this->getAdapter()); $select = $sql->select(); $select->from(array('main' => $this->tableGateway->getTable())); $select->join(array('lc' => 'launchcampaign'), 'main.n_camp_id = lc.campaign_id', array('uid' => 'user_id'), Sql\Select::JOIN_LEFT); $select->join(array('u' => 'users'), 'u.user_id = lc.user_id', array(), Sql\Select::JOIN_LEFT); $select->where(new Sql\Expression(sprintf("FIND_IN_SET(%d, n_user_id)", intval($userId)))); return $this->tableGateway->selectWith($select); }
public function _select($id) { $adapter = $this->ConfAdapter(); $sql = new Sql($adapter); $select = $sql->select('servicos')->columns(array('*'))->where(array('id' => $id)); $sqlString = $sql->getSqlStringForSqlObject($select); $stm = $adapter->query($sqlString); $result = $stm->execute(); return $result; }
/** * Select * * @param string|array|\Closure $where * @return ResultSet */ public function select($where = null) { $select = $this->sql->select(); if ($where instanceof \Closure) { $where($select); } elseif ($where !== null) { $select->where($where); } return $this->selectWith($select); }
/** * {@inheritdoc} */ public function getAggregatedValue($conditions, QueryAggregateInterface $aggregate) { $sql = new Sql($this->dbAdapter); $select = $sql->select(DbViewUserActivity::TABLE)->columns(array('a' => $aggregate->getAggregateExpression()))->where($conditions); $res = $this->fetchArray($sql, $select); if ($res) { return $res[0]['a']; } return null; }
public function obtenerActivasPorUsuario($usuarios_id) { $sql = new Sql($this->tableGateway->adapter); $select = $sql->select(); $select->from('actividades'); $select->where(array('actividades.actividades_estado' => 'A', 'actividades.actividades_responsable' => $usuarios_id)); $select->join('usuarios', 'usuarios.usuarios_id = actividades.actividades_responsable', array('usuarios_username', 'usuarios_nombres'), 'left'); $resultSet = $this->tableGateway->selectWith($select); return $resultSet; }