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