public function getRandomUser($used = 1)
{
$usedCount = $this->getUnusedCount();
if (intval($usedCount) == 0) {
return false;
}
$userData = $this->getOrderByRand($this->table, array("is_used" => 0), 1);
if (!$userData) {
return false;
}
$uid = $userData[0]["id"];
$where = array("id" => $uid);
$connection = $this->dbAdapter->getDriver()->getConnection();
$connection->beginTransaction();
$sql = "select * from " . $this->table . " where id =? for update";
$result = $this->dbAdapter->query($sql, array($uid));
$userInfo = $result->toArray();
// $updateData = array("is_used" => $used, "used_time" => time());
$updateData = array("is_used" => $used);
if ($userInfo[0]["is_used"] == 0) {
$this->updateData($this->table, $where, $updateData);
$connection->commit();
if (!empty($userInfo)) {
return $userInfo[0];
}
} else {
$connection->commit();
return $this->getRandomUser($used);
}
}
public function __construct(AdapterInterface $adapter)
{
$this->adapter = $adapter;
$platform = $adapter->getPlatform();
switch (strtolower($platform->getName())) {
case 'mysql':
$platform = new Mysql\Mysql();
$this->decorators = $platform->decorators;
break;
case 'sqlserver':
$platform = new SqlServer\SqlServer();
$this->decorators = $platform->decorators;
break;
case 'oracle':
$platform = new Oracle\Oracle();
$this->decorators = $platform->decorators;
break;
case 'ibm db2':
case 'ibm_db2':
case 'ibmdb2':
$platform = new IbmDb2\IbmDb2();
$this->decorators = $platform->decorators;
default:
}
}
public function industries(AdapterInterface $adapter)
{
$sql = "SELECT industries_id,industry_name from industries where status=1 ORDER by xorder ASC";
$statement = $adapter->query($sql);
$result = $statement->execute();
return $result;
}
public function getNewRFollowID()
{
$sql = "select max(rfollowID) from rfollow";
// echo $sql;
$statement = $this->dbAdapter->query($sql);
$result = $statement->execute();
return $result->current()['max(rfollowID)'] + 1;
}
/**
* @param AdapterInterface $adapter
* @param DebugBar $debugbar
*/
protected function prepareDbAdapter(AdapterInterface $adapter, DebugBar $debugbar)
{
$driver = $adapter->getDriver();
if ($driver instanceof Pdo) {
$pdo = $driver->getConnection()->getResource();
$traceablePdo = new TraceablePDO($pdo);
$pdoCollector = new PDOCollector($traceablePdo);
$debugbar->addCollector($pdoCollector);
}
}
/**
* {@inheritDoc}
*
* @return StatementContainerInterface
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
$parameterContainer = $statementContainer->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statementContainer->setParameterContainer($parameterContainer);
}
$statementContainer->setSql($this->buildSqlString($adapter->getPlatform(), $adapter->getDriver(), $parameterContainer));
return $statementContainer;
}
public function UnsubscribeList(AdapterInterface $adapter, $from = null, $to = null)
{
$sql = 'SELECT * FROM user_unsubscribes';
$array_data = array();
if (!empty($from) && !empty($to)) {
$sql .= ' where created_at >= ? and created_at <= ?';
$array_data = array($from, $to);
}
$statement = $adapter->createStatement($sql, $array_data);
$result = $statement->execute();
return $result;
}
/**
* @todo add $columns support
*
* @param Insert $insert
* @return int
* @throws Exception\RuntimeException
*/
protected function executeInsert(Insert $insert)
{
$insertState = $insert->getRawState();
if ($insertState['table'] != $this->table) {
throw new Exception\RuntimeException('The table name of the provided Insert object must match that of the table');
}
// apply preInsert features
$this->featureSet->apply(EventFeatureEventsInterface::EVENT_PRE_INSERT, [$insert]);
// Most RDBMS solutions do not allow using table aliases in INSERTs
// See https://github.com/zendframework/zf2/issues/7311
$unaliasedTable = false;
if (is_array($insertState['table'])) {
$tableData = array_values($insertState['table']);
$unaliasedTable = array_shift($tableData);
$insert->into($unaliasedTable);
}
$statement = $this->sql->prepareStatementForSqlObject($insert);
$result = $statement->execute();
$this->lastInsertValue = $this->adapter->getDriver()->getConnection()->getLastGeneratedValue();
// apply postInsert features
$this->featureSet->apply(EventFeatureEventsInterface::EVENT_POST_INSERT, [$statement, $result]);
// Reset original table information in Insert instance, if necessary
if ($unaliasedTable) {
$insert->into($insertState['table']);
}
return $result->getAffectedRows();
}
public function rollbackTransaction()
{
self::$transactionCounter--;
if (!self::$transactionCounter) {
$connection = $this->adapter->getDriver()->getConnection();
$connection->rollback();
}
}
public function getNewPageID()
{
$sql1 = 'select max(pageID) from page';
$statement = $this->dbAdapter->query($sql1);
$result = $statement->execute();
$row = $result->current();
// Debug::dump($row );
return $row['max(pageID)'] + 1;
}
/**
* Get sql string using platform or sql object
*
* @param SqlInterface $sqlObject
* @param PlatformInterface $platform
*
* @return string
*/
public function getSqlStringForSqlObject(SqlInterface $sqlObject, PlatformInterface $platform = null)
{
$platform = $platform ?: $this->adapter->getPlatform();
if ($this->sqlPlatform) {
$this->sqlPlatform->setSubject($sqlObject);
return $this->sqlPlatform->getSqlString($platform);
}
return $sqlObject->getSqlString($platform);
}
/**
* Generates PHP files with field names as constants
*/
public function generateDbConstants()
{
$metadata = new \Zend\Db\Metadata\Metadata($this->dbAdapter);
$schema = $this->dbAdapter->getDriver()->getConnection()->getCurrentSchema();
$tables = $metadata->getTableNames($schema, true);
foreach ($tables as $table) {
$words = explode('_', $table);
$class = 'Db';
foreach ($words as $word) {
$word[0] = strtoupper($word[0]);
$class = $class . $word;
}
$filename = __DIR__ . '/../Utils/DbConsts/' . $class . '.php';
if (file_exists($filename)) {
unlink($filename);
}
$writer = new \Zend\Log\Writer\Stream($filename);
$writer->setFormatter(new \Zend\Log\Formatter\Simple('%message%'));
$logger = new \Zend\Log\Logger();
$logger->addWriter($writer);
$logger->info('<?php');
$logger->info('');
$logger->info('namespace Application\\Utils\\DbConsts;');
$logger->info('');
$logger->info("class {$class}");
$logger->info('{');
$logger->info(" const TABLE = '{$table}';");
$columns = $metadata->getColumnNames($table, $schema);
foreach ($columns as $column) {
$logger->info(vsprintf(" const %s = '%s';", array(strtoupper($column), $column)));
}
$logger->info('');
$hasConst = '
static public function hasField($field)
{
if (!is_string($field)) {
return false;
}
$field = strtoupper($field);
$reflect = new \\ReflectionClass(__CLASS__);
foreach ($reflect->getConstants() as $name => $value) {
if (strtoupper($value) === $field) {
return true;
}
};
return false;
}';
$logger->info($hasConst);
$logger->info('}');
$logger = null;
chmod($filename, 0777);
}
}
public function __construct(AdapterInterface $adapter)
{
$this->adapter = $adapter;
$platform = $adapter->getPlatform();
switch (strtolower($platform->getName())) {
case 'mysql':
$platform = new ZfPlatform\Mysql\Mysql();
$this->decorators = $platform->decorators;
$this->setTypeDecorator('Zend\\Db\\Sql\\Select', new Mysql\SelectDecorator());
break;
case 'sqlserver':
$platform = new ZfPlatform\SqlServer\SqlServer();
$this->decorators = $platform->decorators;
$this->setTypeDecorator('Zend\\Db\\Sql\\Select', new SqlServer\SelectDecorator());
break;
case 'oracle':
$platform = new ZfPlatform\Oracle\Oracle();
$this->decorators = $platform->decorators;
$this->setTypeDecorator('Zend\\Db\\Sql\\Select', new Oracle\SelectDecorator());
break;
default:
}
}
public function getCategories($categoryId = null)
{
$sql = 'SELECT * FROM blog.view_posts_categories';
if ($categoryId != null) {
$sql .= ' WHERE id = :id LIMIT 1';
}
$stmt = $this->dbAdapter->createStatement($sql);
$params = array();
if ($categoryId != null) {
$params['id'] = $categoryId;
}
$result = $stmt->execute($params);
return iterator_to_array($result);
}
/**
* @todo add $columns support
*
* @param Insert $insert
* @return mixed
* @throws Exception\RuntimeException
*/
protected function executeInsert(Insert $insert)
{
$insertState = $insert->getRawState();
if ($insertState['table'] != $this->table) {
throw new Exception\RuntimeException('The table name of the provided Insert object must match that of the table');
}
// apply preInsert features
$this->featureSet->apply('preInsert', array($insert));
$statement = $this->sql->prepareStatementForSqlObject($insert);
$result = $statement->execute();
$this->lastInsertValue = $this->adapter->getDriver()->getConnection()->getLastGeneratedValue();
// apply postInsert features
$this->featureSet->apply('postInsert', array($statement, $result));
return $result->getAffectedRows();
}
/**
* Returns result of a query if it's successful and false otherwise
* @param Sql $sql
* @param Select $select
* @param int $offset
* @param int $limit
* @return boolean|ResultInterface
*/
protected function fetch(Sql $sql, Select $select, $offset = 0, $limit = 0)
{
$this->logQuery($select);
if ($offset > 0) {
$select->offset($offset);
}
if ($limit > 0) {
$select->limit($limit);
}
$stmt = $sql->prepareStatementForSqlObject($select);
$profiler = $this->dbAdapter->getProfiler();
if ($profiler) {
$profiler->profilerStart($stmt);
$result = $stmt->execute();
$profile = $profiler->getLastProfile();
$this->logQuery($profile);
} else {
$result = $stmt->execute();
}
if ($result instanceof ResultInterface && $result->isQueryResult()) {
return $result;
}
return false;
}
/**
* Prepare statement
*
* @param AdapterInterface $adapter
* @param StatementContainerInterface $statementContainer
* @return void
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
// ensure statement has a ParameterContainer
$parameterContainer = $statementContainer->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statementContainer->setParameterContainer($parameterContainer);
}
$sqls = array();
$sqls[self::SHOW] = sprintf($this->specifications[static::SHOW], $this->show);
$likePart = $this->processLike($adapter->getPlatform(), $adapter->getDriver(), $parameterContainer);
if (is_array($likePart)) {
$sqls[self::LIKE] = $this->createSqlFromSpecificationAndParameters($this->specifications[static::LIKE], $likePart);
}
$sql = implode(' ', $sqls);
$statementContainer->setSql($sql);
return;
}
public function findRec()
{
if (WAuthUtil::get_auth() !== null) {
$userId = WAuthUtil::get_auth()->userID;
$sql = "select page.*,predictRating,user.* from (recs join page on recs.pageId = page.pageID)join user on recs.userId = user.userId where \n recs.userId = {$userId} and predictRating <> 0 order by predictRating desc";
$statement = $this->dbAdapter->query($sql);
$result = $statement->execute();
if ($result instanceof ResultInterface && $result->isQueryResult()) {
$resultSet = new WHydrateResultset($this->hydrator, $this->pagePrototype, $this->prototypeArr);
$tmp = $resultSet->initialize($result);
// foreach ($resultSet as $row){
// Debug::dump($row);
// }
return $tmp;
}
throw new \InvalidArgumentException("Forum with given ID:{$id} not found.");
} else {
return null;
}
}
/**
* @param array $fields
* @param array $orders
* @param null|int $limit
* @param null|int $offset
*
* @return ResultSet|ResultSetInterface
* @throws RuntimeException
* @throws \Exception
*/
public function find($fields = [], $orders = [], $limit = null, $offset = null)
{
if (!is_array($fields) || !is_array($orders)) {
throw new \Exception("Wrong input type of parameters !");
}
if ($this->profiler) {
$this->profiler->profilerStart($this);
}
// apply preSelect features
// $this -> featureSet -> apply( 'preSelect', array( $select ) );
$return = $this->collection->find($this->_where($fields));
foreach ($orders as $_k => $_v) {
if (strtolower($_v) == 'desc' || $_v < 0) {
$orders[$_k] = -1;
} else {
$orders[$_k] = 1;
}
}
if (!empty($orders)) {
$return->sort($this->_orders($orders));
}
if ($limit !== null) {
$return->limit($limit);
}
if ($offset !== null) {
$return->skip($offset);
}
if ($this->profiler) {
$this->profiler->profilerFinish();
}
if ($return === false) {
throw new RuntimeException($this->getDriver()->getConnection()->getDB()->lastError());
}
$result = $this->adapter->getDriver()->createResult($return, $this->getTable());
$resultSet = clone $this->resultSetPrototype;
$resultSet->initialize($result);
// apply postSelect features
// $this -> featureSet -> apply( 'postSelect', array( $result, $resultSet ) );
return $resultSet;
}
/**
* @param SqlInterface $sqlObject
* @param AdapterInterface $adapter
*
* @return string
*
* @throws Exception\InvalidArgumentException
*/
public function buildSqlString(SqlInterface $sqlObject, AdapterInterface $adapter = null)
{
return $this->sqlPlatform->setSubject($sqlObject)->getSqlString($adapter ? $adapter->getPlatform() : $this->adapter->getPlatform());
}
/**
* Prepare the delete statement
*
* @param AdapterInterface $adapter
* @param StatementContainerInterface $statementContainer
* @return void
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();
$parameterContainer = $statementContainer->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statementContainer->setParameterContainer($parameterContainer);
}
$table = $this->table;
$schema = null;
// create quoted table name to use in delete processing
if ($table instanceof TableIdentifier) {
list($table, $schema) = $table->getTableAndSchema();
}
$table = $platform->quoteIdentifier($table);
if ($schema) {
$table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table;
}
$sql = sprintf($this->specifications[static::SPECIFICATION_DELETE], $table);
// process where
if ($this->where->count() > 0) {
$whereParts = $this->processExpression($this->where, $platform, $driver, 'where');
$parameterContainer->merge($whereParts->getParameterContainer());
$sql .= ' ' . sprintf($this->specifications[static::SPECIFICATION_WHERE], $whereParts->getSql());
}
$statementContainer->setSql($sql);
}
/**
* Prepare statement
*
* @param AdapterInterface $adapter
* @param StatementContainerInterface $statementContainer
* @return void
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
// ensure statement has a ParameterContainer
$parameterContainer = $statementContainer->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statementContainer->setParameterContainer($parameterContainer);
}
$sqls = array();
$parameters = array();
$platform = $adapter->getPlatform();
$driver = $adapter->getDriver();
foreach ($this->specifications as $name => $specification) {
$parameters[$name] = $this->{'process' . $name}($platform, $driver, $parameterContainer, $sqls, $parameters);
if ($specification && is_array($parameters[$name])) {
$sqls[$name] = $this->createSqlFromSpecificationAndParameters($specification, $parameters[$name]);
}
}
$sql = implode(' ', $sqls);
$statementContainer->setSql($sql);
return;
}
/**
* @param null|PlatformInterface|AdapterInterface $adapterOrPlatform
*
* @return PlatformInterface
*
* @throws Exception\InvalidArgumentException
*/
protected function resolvePlatform($adapterOrPlatform)
{
if (!$adapterOrPlatform) {
return $this->getDefaultPlatform();
}
if ($adapterOrPlatform instanceof AdapterInterface) {
return $adapterOrPlatform->getPlatform();
}
if ($adapterOrPlatform instanceof PlatformInterface) {
return $adapterOrPlatform;
}
throw new Exception\InvalidArgumentException(sprintf('$adapterOrPlatform should be null, %s, or %s', 'Zend\\Db\\Adapter\\AdapterInterface', 'Zend\\Db\\Adapter\\Platform\\PlatformInterface'));
}
public function __construct(AdapterInterface $adapter)
{
$this->adapter = $adapter;
$this->defaultSchema = $adapter->getCurrentSchema() ?: self::DEFAULT_SCHEMA;
}
public function __invoke($value = null)
{
return 'Platform name ' . $this->dbAdapter->getPlatform()->getName();
}
/**
* Prepare statement
*
* @param AdapterInterface $adapter
* @param StatementContainerInterface $statementContainer
* @return void
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();
$parameterContainer = $statementContainer->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statementContainer->setParameterContainer($parameterContainer);
}
$table = $this->table;
$schema = null;
// create quoted table name to use in update processing
if ($table instanceof TableIdentifier) {
list($table, $schema) = $table->getTableAndSchema();
}
$table = $platform->quoteIdentifier($table);
if ($schema) {
$table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table;
}
$set = $this->set;
if (is_array($set)) {
$setSql = array();
foreach ($set as $column => $value) {
if ($value instanceof Expression) {
$exprData = $this->processExpression($value, $platform, $driver);
$setSql[] = $platform->quoteIdentifier($column) . ' = ' . $exprData->getSql();
$parameterContainer->merge($exprData->getParameterContainer());
} else {
$setSql[] = $platform->quoteIdentifier($column) . ' = ' . $driver->formatParameterName($column);
$parameterContainer->offsetSet($column, $value);
}
}
$set = implode(', ', $setSql);
}
$sql = sprintf($this->specifications[static::SPECIFICATION_UPDATE], $table, $set);
// process where
if ($this->where->count() > 0) {
$whereParts = $this->processExpression($this->where, $platform, $driver, 'where');
$parameterContainer->merge($whereParts->getParameterContainer());
$sql .= ' ' . sprintf($this->specifications[static::SPECIFICATION_WHERE], $whereParts->getSql());
}
$statementContainer->setSql($sql);
}
/**
* Prepare statement
*
* @param AdapterInterface $adapter
* @param StatementContainerInterface $statementContainer
* @return void
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();
$parameterContainer = $statementContainer->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statementContainer->setParameterContainer($parameterContainer);
}
$table = $this->table;
$schema = null;
// create quoted table name to use in insert processing
if ($table instanceof TableIdentifier) {
list($table, $schema) = $table->getTableAndSchema();
}
$table = $platform->quoteIdentifier($table);
if ($schema) {
$table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table;
}
$columns = array();
$values = array();
foreach ($this->columns as $cIndex => $column) {
$columns[$cIndex] = $platform->quoteIdentifier($column);
if (isset($this->values[$cIndex]) && $this->values[$cIndex] instanceof Expression) {
$exprData = $this->processExpression($this->values[$cIndex], $platform, $driver);
$values[$cIndex] = $exprData->getSql();
$parameterContainer->merge($exprData->getParameterContainer());
} else {
$values[$cIndex] = $driver->formatParameterName($column);
if (isset($this->values[$cIndex])) {
$parameterContainer->offsetSet($column, $this->values[$cIndex]);
} else {
$parameterContainer->offsetSet($column, null);
}
}
}
$sql = sprintf($this->specifications[self::SPECIFICATION_INSERT], $table, implode(', ', $columns), implode(', ', $values));
$statementContainer->setSql($sql);
}
public function childModuleAccessRights(AdapterInterface $adapter, $module_id, $function_name)
{
//Get user credentials
$session = new Container('base');
$user = $session->offsetGet('user');
$this->adapter = $adapter;
$statement = $adapter->query("SELECT `module_name`, function_name\n FROM\n `app_sub_accesses`\n LEFT JOIN\n `app_modules`\n ON\n `app_modules`.`app_modules_id` = `app_sub_accesses`.`module_id`\n LEFT JOIN\n `app_roles`\n ON\n `app_sub_accesses`.`role_id` = `app_roles`.`app_roles_id`\n WHERE\n `app_roles`.`app_roles_id`= " . $user['role_id'] . "\n AND `app_modules`.`app_modules_id` = {$module_id}\n AND `app_roles`.`status`=1\n AND function_name='" . $function_name . "'\n ");
$result = $statement->execute();
//Count number of records
if (count($result) > 0) {
return true;
} else {
return false;
}
}
/**
* Set the database adapter.
*
* @param AdapterInterface $adapter
* @return AbstractMapper
*/
public function setAdapter(AdapterInterface $adapter)
{
$this->adapter = $adapter;
if ($adapter instanceof MasterSlaveAdapterInterface) {
$this->slaveAdapter = $adapter->getSlaveAdapter();
}
return $this;
}
/**
* @param AdapterInterface $adapter
* @param StatementContainerInterface $statementContainer
* @return void
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();
$parameterContainer = $statementContainer->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statementContainer->setParameterContainer($parameterContainer);
}
$function = $platform->quoteIdentifierChain($this->function);
$resultKey = $platform->quoteIdentifier($this->resultKey);
$arguments = array();
$i = 0;
foreach ($this->arguments as $argument) {
if ($argument instanceof Expression) {
$exprData = $this->processExpression($argument, $platform, $driver);
$arguments[] = $exprData->getSql();
$parameterContainer->merge($exprData->getParameterContainer());
} else {
$parameterName = 'func_arg_' . $i++;
$arguments[] = $driver->formatParameterName($parameterName);
$parameterContainer->offsetSet($parameterName, $argument);
}
}
$sql = sprintf($this->specifications[$this->mode], $function, implode(', ', $arguments), $resultKey);
$statementContainer->setSql($sql);
}
