public function saveItem($arrParam = null, $options = null)
{
if ($options['task'] == 'add') {
$sqlObj = new \Zend\Db\Sql\Sql($this->adapter);
$insertObj = $sqlObj->insert('service_account_register');
$insertObj->values($arrParam);
$sqlString = $sqlObj->getSqlStringForSqlObject($insertObj);
$this->adapter->query($sqlString)->execute();
}
if ($options['task'] == 'edit') {
$this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
}
if ($options['task'] == 'multi-status') {
if (!empty($arrParam)) {
foreach ($arrParam['id'] as $key => $value) {
if ($arrParam['type'] == 'multi-active') {
$status = 1;
}
if ($arrParam['type'] == 'multi-in-active') {
$status = 0;
}
$data = array('id' => $value, 'status' => $status);
$this->tableGateway->update($data, array('id' => $value));
}
}
}
}
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 save(\Api\Entity\Post $post)
{
$hydrator = $this->getHydrator();
$action = null;
$postData = array('title' => $post->getTitle(), 'description' => $post->getDescription());
if ($post->getId()) {
$action = new Update('posts');
$action->set($postData);
$action->where(array('id = ?' => $post->getId()));
} else {
$postData['author_id'] = $post->getAuthorId();
$action = new Insert('posts');
$action->values($postData);
}
$sql = new Sql($this->getAdaptor());
$statement = $sql->prepareStatementForSqlObject($action);
$result = $statement->execute();
if ($result instanceof ResultInterface) {
if ($pk = $result->getGeneratedValue()) {
$post->setId($pk);
}
return $this->getPost($post->getId());
}
throw new \Exception('something went wrong.Please try again later');
}
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;
}
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 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 onDispatch(MvcEvent $e)
{
if (!$e->getRequest() instanceof ConsoleRequest) {
throw new RuntimeException('You can only use this action from a console!');
}
$table = new Ddl\CreateTable('queue_messages');
$table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true]));
$table->addColumn(new Ddl\Column\Varchar('queue_name', 100));
$table->addColumn(new Ddl\Column\Integer('status', false));
$table->addColumn(new Ddl\Column\Varchar('options', 250));
$table->addColumn(new Ddl\Column\Text('message', null, true));
$table->addColumn(new Ddl\Column\Text('output', null, true));
$table->addColumn(new Ddl\Column\Datetime('started_dt', true));
$table->addColumn(new Ddl\Column\Datetime('finished_dt', true));
$table->addColumn(new Ddl\Column\Datetime('created_dt', false));
$table->addColumn(new Ddl\Column\Datetime('updated_dt', true));
$table->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
$sql = new Sql($this->dbAdapter);
try {
$this->dbAdapter->query($sql->buildSqlString($table), DbAdapter::QUERY_MODE_EXECUTE);
} catch (\Exception $e) {
// currently there are no db-independent way to check if table exists
// so we assume that table exists when we catch exception
}
}
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;
}
}
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 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());
}
}
/**
* {@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));
}
/**
* Saves a user to a database
*
* @param array $user
*/
public function saveUser(array $user)
{
$sql = new Sql($this->dbAdapter);
$insert = $sql->insert('users');
$insert->columns(array_keys($user))->values($user);
$sql->prepareStatementForSqlObject($insert)->execute();
}
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 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;
}
protected function create(\Zend\Db\Sql\Ddl\CreateTable $dll)
{
return $this->generateCode(function () use($dll) {
$adapter = $this->getTableGateway()->adapter;
$sql = new Sql($adapter);
$adapter->query($sql->getSqlStringForSqlObject($dll), $adapter::QUERY_MODE_EXECUTE);
});
}
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;
}
/**
* Removes this record from the database
*/
protected function delete()
{
if ($this->getId()) {
$sql = new Sql(Database::getConnection(), $this->tablename);
$delete = $sql->delete()->where(['id' => $this->getId()]);
$sql->prepareStatementForSqlObject($delete)->execute();
}
}
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;
}
/**
* @covers Zend\Db\Sql\Sql::__construct
*/
public function test__construct()
{
$sql = new Sql($this->mockAdapter);
$this->assertFalse($sql->hasTable());
$sql->setTable('foo');
$this->assertSame('foo', $sql->getTable());
$this->setExpectedException('Zend\\Db\\Sql\\Exception\\InvalidArgumentException', 'Table must be a string or instance of TableIdentifier.');
$sql->setTable(null);
}
private function create(SqlInterface $table)
{
try {
$sql = new Sql($this->dbAdapter);
$this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE);
} catch (PDOException $e) {
$message = $e->getMessage() . PHP_EOL;
}
}
public function delete(PostInterface $postObject)
{
$action = new Delete($postObject);
$action->where(array('id=?' => $postObject->getId()));
$sql = new Sql($this->Adapter);
$stmt = $sql->prepareStatementForSqlObject($action);
$result = $stmt->execute();
return (bool) $result->getAffectedRows();
}
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 cleanPriorForgotRequests($userId)
{
$sql = new Sql($this->getDbAdapter(), $this->tableName);
$delete = $sql->delete();
$delete->where->equalTo($this->userField, $userId);
$statement = $sql->prepareStatementForSqlObject($delete);
$statement->execute();
return true;
}
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'];
}
/**
* 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 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 addRole($serviceManager, $userRoleTable, $userIdField, $roleIdField, $userId, $roleId)
{
$adapter = $serviceManager->get('Zend\\Db\\Adapter\\Adapter');
$sql = new Sql($adapter);
$insert = $sql->insert()->into($userRoleTable)->columns(array($userIdField, $roleIdField))->values(array($userId, $roleId));
$sqlString = $sql->getSqlStringForSqlObject($insert);
$execute = $adapter->query($sqlString, $adapter::QUERY_MODE_EXECUTE);
return $execute;
}
