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 listItem($arrParam = null, $options = null)
{
if ($options['task'] == 'list-item-type-real-estate-child') {
$sqlObj = new Sql($this->adapter);
$select = $sqlObj->select()->from('category')->columns(array('id', 'name', 'parent'));
$result = \ZendVN\Db\Sql\Result::toArray($select, $this->adapter);
return $result;
}
if ($options['task'] == 'list-item-district') {
$sqlObj = new Sql($this->adapter);
$select = $sqlObj->select()->from('city_district')->columns(array('id', 'name', 'city_id'));
$result = \ZendVN\Db\Sql\Result::toArray($select, $this->adapter);
return $result;
}
if ($options['task'] == 'list-items-paginator') {
$paginator = $arrParam['paginator'];
$ssFilter = $arrParam['ssFilter'];
$sqlObj = new Sql($this->adapter);
$select = $sqlObj->select();
$select->from(array('re' => 'register_email'))->join(array('ct' => 'city'), 'ct.id = re.city', array('name_city' => 'name'), $select::JOIN_LEFT)->join(array('ctd' => 'city_district'), 'ctd.id = re.district', array('name_district' => 'name'), $select::JOIN_LEFT)->join(array('retype' => 'category'), 'retype.id = re.cat_id', array('name_type' => 'name'), $select::JOIN_LEFT)->join(array('ju' => 'juridical'), 'ju.id = re.juridical', array('name_juridical' => 'name'), $select::JOIN_LEFT)->where('re.user_id =' . $arrParam['userId']);
$sqlString = $sqlObj->getSqlStringForSqlObject($select);
$result = $this->adapter->query($sqlString)->execute();
return $result;
}
}
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 listItem($arrParam = null, $options = null)
{
if ($options['task'] == 'list-item-hop-thu-di') {
$paginator = $arrParam['paginator'];
$ssFilter = $arrParam['ssFilter'];
$sqlObj = new Sql($this->adapter);
$select = $sqlObj->select();
$select->from(array('m' => 'message_send'))->join(array('u' => 'users'), 'u.id = m.user_id_receive', array('fullname' => 'fullname'), $select::JOIN_LEFT)->limit($paginator['itemCountPerPage'])->offset(($paginator['currentPageNumber'] - 1) * $paginator['itemCountPerPage']);
$select->where('m.user_id_send =' . $arrParam['idUser']);
$sqlString = $sqlObj->getSqlStringForSqlObject($select);
$result = $this->adapter->query($sqlString)->execute();
return $result;
}
if ($options['task'] == 'list-item-hop-thu-den') {
$paginator = $arrParam['paginator'];
$ssFilter = $arrParam['ssFilter'];
$sqlObj = new Sql($this->adapter);
$select = $sqlObj->select();
$select->from(array('m' => 'message_receive'))->join(array('u' => 'users'), 'u.id = m.user_id_send', array('fullname' => 'fullname'), $select::JOIN_LEFT)->limit($paginator['itemCountPerPage'])->offset(($paginator['currentPageNumber'] - 1) * $paginator['itemCountPerPage']);
$select->where('m.user_id_receive =' . $arrParam['idUser']);
$sqlString = $sqlObj->getSqlStringForSqlObject($select);
$result = $this->adapter->query($sqlString)->execute();
return $result;
}
}
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 delete()
{
$delete = new ZfSql\Delete($this->tableName);
$adapter = $this->sql->getAdapter();
if ($this->select->where) {
$delete->where($this->select->where);
}
$sqlString = $this->sql->getSqlStringForSqlObject($delete);
try {
$result = $adapter->query($sqlString, $adapter::QUERY_MODE_EXECUTE);
} catch (AdapterException\ExceptionInterface $e) {
throw new Exception\RecordNotDestroyedException($e->getMessage(), 0, $e);
}
if (!$result->count()) {
throw new Exception\RecordNotDestroyedException("No rows were affected");
}
return true;
}
/**
*
* @param string $tableName
* @param array $data
* @return bool
*/
public function insert($tableName, array $data)
{
$sql = new Sql($this->adapter);
$insert = $sql->insert($tableName);
$insert->values($data);
$sqlString = $sql->getSqlStringForSqlObject($insert);
$results = $this->adapter->query($sqlString, Adapter::QUERY_MODE_EXECUTE);
return $results;
}
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;
}
public function insert($data)
{
$adapter = $this->adapter;
$sql = new Sql($this->adapter);
$insert = $sql->insert('item_price');
$insert->values($data);
$selectString = $sql->getSqlStringForSqlObject($insert);
return $results = $this->adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
}
public function getUnUsuario()
{
$sql = new Sql($this->dbAdapter);
$select = $sql->select();
$select->columns(array('nombre', 'apellido', 'direccion', 'telefono'))->from('usuarios');
$selectString = $sql->getSqlStringForSqlObject($select);
$execute = $this->dbAdapter->query($selectString, Adapter::QUERY_MODE_EXECUTE);
$result = $execute->toArray();
return $result[0];
}
public function create($id)
{
$sql = new Sql($this->getAdapter());
$insert = $sql->insert($this->getTableName());
$insert->values(['id' => (string) $id]);
$query = $sql->getSqlStringForSqlObject($insert);
/** @var Result $results */
$results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);
return $results->getGeneratedValue();
}
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;
}
public function checkHash($hash)
{
$adapter = $this->sm->get('Zend\\Db\\Adapter\\Adapter');
$sql = new Sql($adapter);
//search hash
$select = $sql->select('tokens_table')->where(array('hash' => $hash))->order('id DESC');
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE)->toArray()[0];
return $results;
}
public function roles()
{
$this->dbAdapter = $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter');
$adapter = $this->dbAdapter;
$sql = new Sql($adapter);
$select = $sql->select()->from('ta_rol');
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
return $results;
}
public function create(array $data)
{
$sql = new Sql($this->adapter);
$this->adapter->getDriver()->getConnection()->beginTransaction();
$insert = $sql->insert('places');
$insert->values(['name' => $data['name'], 'latitude' => $data['latitude'], 'longitude' => $data['longitude']]);
$query = $sql->getSqlStringForSqlObject($insert);
/** @var Result $results */
$results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);
$placeId = $results->getGeneratedValue();
foreach ($data['types'] as $typeId) {
$insert = $sql->insert('places_place_types');
$insert->values(['place_id' => $placeId, 'place_type_id' => $typeId]);
$q = $sql->getSqlStringForSqlObject($insert);
$this->adapter->query($q, Adapter::QUERY_MODE_EXECUTE);
}
$this->adapter->getDriver()->getConnection()->commit();
return $placeId;
}
public function createTransaction($data)
{
$adapter = $this->adapter;
$sql = new Sql($this->adapter);
$insert = $sql->insert('user_transaction');
$insert->values($data);
$selectString = $sql->getSqlStringForSqlObject($insert);
$results = $this->adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
return array("trnx_id" => $results->getGeneratedValue());
}
/**
* Return the query string
*
* @throws Exception\InvalidUsageException
* @return string
*/
public function __toString()
{
if ($this->query_string != '') {
$sql = str_replace("\n", ' ', $this->query_string);
} elseif ($this->select !== null) {
$sql = $this->sql->getSqlStringForSqlObject($this->select);
} else {
throw new Exception\InvalidUsageException(__METHOD__ . ": No select given.");
}
return $sql;
}
public function listItem($arrParam = null, $options = null)
{
if ($options['task'] == 'list-items') {
$sqlObj = new Sql($this->adapter);
$select = $sqlObj->select();
$select->from(array('se' => 'service_account'));
$sqlString = $sqlObj->getSqlStringForSqlObject($select);
$result = $this->adapter->query($sqlString)->execute();
return $result;
}
}
/**
* Create one table. if it is exsist, check the columns. if the column is exsist, change it's type, else create the column.
* @param unknown $tableName
* @param array $table
*/
private function create_table($tableName, $tableStructureData)
{
$adapter = $this->adapter;
$sql = new Sql($adapter);
if (!in_array($tableName, $this->tables)) {
// Create the $table
$CreateTable = new Ddl\CreateTable($tableName);
foreach ($tableStructureData['column'] as $column) {
$CreateTable->addColumn($column);
}
foreach ($tableStructureData['constraint'] as $constraint) {
$CreateTable->addConstraint($constraint);
}
$adapter->query($sql->getSqlStringForSqlObject($CreateTable), $adapter::QUERY_MODE_EXECUTE);
} else {
// Check the columns
$columns = $this->getColumns($tableName);
$constraints = $this->getConstraints($tableName);
$AlterTable = new Ddl\AlterTable($tableName);
foreach ($tableStructureData['column'] as $createColumn) {
$column_exsist = false;
foreach ($columns as $column) {
if ($createColumn->getName() == $column->getName()) {
$column_exsist = true;
}
}
if ($column_exsist) {
// Alter the table, change the column.
$AlterTable->changeColumn($createColumn->getName(), $createColumn);
} else {
// Alter the table, add the column.
$AlterTable->addColumn($createColumn);
}
}
// Delete exsisted constraints(mysql index) but PRIMARY KEY
$exsisted_constraints = $this->getConstraints($tableName);
foreach ($exsisted_constraints as $exsisted_constraint) {
if ($exsisted_constraint->getType() != 'PRIMARY KEY') {
$adapter->query('ALTER TABLE `' . $tableName . '`
DROP index `' . str_replace('_zf_' . $tableName . '_', '', $exsisted_constraint->getName()) . '`', $adapter::QUERY_MODE_EXECUTE);
}
}
// Add all constraints but PRIMARY KEY
foreach ($tableStructureData['constraint'] as $constraint) {
if ($constraint instanceof Constraint\PrimaryKey) {
// Do nothing
} else {
// Add to DB
$AlterTable->addConstraint($constraint);
}
}
$adapter->query($sql->getSqlStringForSqlObject($AlterTable), $adapter::QUERY_MODE_EXECUTE);
}
}
public function prixCommande($commande_id)
{
$adapter = $this->tableGateway->adapter;
$sql = new Sql($adapter);
$select = $sql->select();
$select->from('commandeproduit');
$select->where(array('COMMANDE_ID' => $commande_id, 'COMMANDEPRODUIT_ACTIF' => 1));
$select->columns(array('COMMANDEPRODUIT_PRIX' => new Expression('SUM(COMMANDEPRODUIT_PRIX)')));
$selectString = $sql->getSqlStringForSqlObject($select);
$resultSet = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
$resultSetFinal = $resultSet->toArray();
return $resultSetFinal[0]['COMMANDEPRODUIT_PRIX'];
}
public function listItem($arrParam = null, $options = null)
{
if ($options['task'] == 'list-items-paginator') {
$paginator = $arrParam['paginator'];
$ssFilter = $arrParam['ssFilter'];
$sqlObj = new Sql($this->adapter);
$select = $sqlObj->select();
$select->from(array('c' => 'contact_agency'))->join(array('u' => 'users'), 'u.id = c.user_id', array('fullname_contact' => 'fullname'), $select::JOIN_LEFT)->limit($paginator['itemCountPerPage'])->offset(($paginator['currentPageNumber'] - 1) * $paginator['itemCountPerPage']);
$sqlString = $sqlObj->getSqlStringForSqlObject($select);
$result = $this->adapter->query($sqlString)->execute();
return $result;
}
}
public function insert($data)
{
$adapter = $this->adapter;
$sql = new Sql($this->adapter);
$insert = $sql->insert('order_products');
$insert->values($data);
$selectString = $sql->getSqlStringForSqlObject($insert);
$results = $this->adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
//if type package is there then reduce number of clothes and days
if ($results && $data['order_type'] == 'package') {
$this->updatePackage($data['order_id'], $data['quantity']);
}
}
public function getAlbum($id, $adapter)
{
$sql = new Sql($adapter);
$select = $sql->select()->from(array('f' => 'ta_usuario'))->join(array('b' => 'ta_rol'), 'f.Ta_rol_in_id = b.in_id');
//->where(array('f.in_id'=>$id));
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
$row = $results->current();
if (!$row) {
throw new \Exception("No existe registro con el parametro {$id}");
}
return $row;
}
public function getUsersBySubmission($id)
{
$id = (int) $id;
$adapter = $this->adapter;
$sql = new Sql($adapter);
$select = $sql->select(array('t' => $this->table));
$select->columns(array('user_id', 'main', 'status'), 0);
$select->join(array('tu' => $this->table_user), 't.user_id = tu.user_id', array('display_name', 'email'));
$select->where(array('submission_id' => $id));
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
return $results->count() ? $results->toArray() : array();
}
public function getAttendeesByConference($conference_id)
{
$adapter = $this->adapter;
$sql = new Sql($adapter);
$select = $sql->select();
$select->from(array('t' => $this->table));
$select->join(array('tu' => $this->table_users), 't.user_id = tu.user_id');
$select->where(array('t.conference_id' => $conference_id));
$select->order('register DESC, accreditation DESC');
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
return $results;
}
public function listItem($arrParam = null, $options = null)
{
if ($options == null) {
$result = $this->tableGateway->select();
$result->buffer();
return $result;
}
if ($options['task'] == 'list-items-paginator') {
return $this->tableGateway->select(function (Select $select) use($arrParam) {
$paginator = $arrParam['paginator'];
$ssFilter = $arrParam['ssFilter'];
$select->limit($paginator['itemCountPerPage']);
$select->offset(($paginator['currentPageNumber'] - 1) * $paginator['itemCountPerPage']);
//-------Lọc theo keyword và field----
if (!empty($ssFilter['keywords']) && !empty($ssFilter['field'])) {
$keywords = '%' . $ssFilter['keywords'] . '%';
$select->where->like($ssFilter['field'], $keywords);
}
//-------Lọc theo status--------------
if (!empty($ssFilter['status'])) {
$select->where->equalTo('status', $ssFilter['status']);
}
//------Sap xep theo cot-------------
if (!empty($ssFilter['col']) && !empty($ssFilter['order'])) {
$select->order(array($ssFilter['col'] . ' ' . $ssFilter['order']));
}
});
}
if ($options['task'] == 'block-ads-column-right') {
$sqlObj = new Sql($this->adapter);
$select = $sqlObj->select()->from(array('a' => 'ads'))->where('a.area_ads = "column_right"')->where('status = 1')->order('order ASC');
$sqlString = $sqlObj->getSqlStringForSqlObject($select);
$result = $this->adapter->query($sqlString)->execute();
return $result;
}
if ($options['task'] == 'block-ads-column-bottom') {
$sqlObj = new Sql($this->adapter);
$select = $sqlObj->select()->from(array('a' => 'ads'))->where('a.area_ads = "bottom"')->where('status = 1')->order('order ASC');
$sqlString = $sqlObj->getSqlStringForSqlObject($select);
$result = $this->adapter->query($sqlString)->execute();
return $result;
}
if ($options['task'] == 'block-ads-column-top') {
$sqlObj = new Sql($this->adapter);
$select = $sqlObj->select()->from(array('a' => 'ads'))->where('a.area_ads = "top"')->order('order ASC');
$sqlString = $sqlObj->getSqlStringForSqlObject($select);
$result = $this->adapter->query($sqlString)->execute();
return $result;
}
}
public function getRole()
{
$auth = new AuthenticationService();
$adapter = $this->getDbAdapter();
$sql = new Sql($adapter);
$select = $sql->select('login');
$select->where(array('username' => $auth->getIdentity()));
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
foreach ($results as $row) {
$role = $row->role;
}
return $role;
}
/**
* Create migrations table of not exists
*/
protected function checkCreateMigrationTable()
{
$table = new Ddl\CreateTable(MigrationVersion::TABLE_NAME);
$table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true]));
$table->addColumn(new Ddl\Column\BigInteger('version'));
$table->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
$table->addConstraint(new Ddl\Constraint\UniqueKey('version'));
$sql = new Sql($this->adapter);
try {
$this->adapter->query($sql->getSqlStringForSqlObject($table), Adapter::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
}
}
/**
* Create a new table
*
* @param string $name
* @return void
*/
public static function createTable($name)
{
$table = new CreateTable($name);
// Primary column
$primaryColumn = new Integer('id');
$primaryColumn->setOption('autoincrement', '');
$table->addColumn($primaryColumn);
$table->addConstraint(new PrimaryKey('id'));
// Status column
$statusColumn = new Boolean(STATUS_COLUMN_NAME, false, STATUS_DRAFT_NUM);
$table->addColumn($statusColumn);
$connection = Bootstrap::get('ZendDb');
$sql = new Sql($connection);
$connection->query($sql->getSqlStringForSqlObject($table), $connection::QUERY_MODE_EXECUTE);
}
public function getSubmissionsByConference($conference_id)
{
$adapter = $this->adapter;
$sql = new Sql($adapter);
$select = $sql->select();
$conference_id = (int) $conference_id;
$select->from(array('t' => $this->table));
$select->join(array('tr' => $this->tableRel), 't.submission_id = tr.submission_id');
$select->join(array('tu' => $this->tableUser), 'tr.user_id = tu.user_id', array('user' => 'display_name', 'email'));
$select->join(array('tt' => $this->tableTrack), 'tt.track_id = t.track_id', array('track_name' => 'name'));
$select->where(array('t.conference_id' => $conference_id, 'main' => 1));
$select->order('date_sent DESC');
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
return $results->count() ? $results->toArray() : array();
}
