Prepares and executes an SQL query and returns the first row of the result
as an associative array.
public static function fetchByRoot(Connection $conn, $id, MembershipEntityInterface $root)
{
if ($root instanceof Area) {
$data = $conn->fetchAssoc('SELECT * FROM `' . EdkTables::MESSAGE_TBL . '` WHERE `id` = :id AND `areaId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]);
} elseif ($root instanceof Group) {
$data = $conn->fetchAssoc('SELECT m.* FROM `' . EdkTables::MESSAGE_TBL . '` m ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.`id` = m.`areaId` ' . 'WHERE m.`id` = :id AND a.`groupId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]);
} elseif ($root instanceof Project) {
$data = $conn->fetchAssoc('SELECT m.* FROM `' . EdkTables::MESSAGE_TBL . '` m ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON a.`id` = m.`areaId` ' . 'WHERE m.`id` = :id AND a.`projectId` = :rootId', [':id' => $id, ':rootId' => $root->getId()]);
}
if (false === $data) {
return false;
}
$item = self::fromArray($data);
if ($root instanceof Area) {
$item->area = $root;
} elseif ($root instanceof Group) {
$item->area = Area::fetchByGroup($conn, $data['areaId'], $root);
} elseif ($root instanceof Project) {
$item->area = Area::fetchByProject($conn, $data['areaId'], $root);
}
if (!empty($data['responderId'])) {
$item->responder = User::fetchByCriteria($conn, QueryClause::clause('u.id = :id', ':id', $data['responderId']));
}
return $item;
}
/**
* Dispatches magic methods (findBy[Property]())
*
* @param string $methodName The name of the magic method
* @param string $arguments The arguments of the magic method
* @throws \Exception
* @return mixed
* @api
*/
public function __call($methodName, $arguments)
{
if (substr($methodName, 0, 6) === 'findBy' && strlen($methodName) > 7) {
$propertyName = lcfirst(substr($methodName, 6));
$sql = "select * from " . $this->tableName . " where " . $propertyName . " = :propertyValue order by id desc";
$result = $this->db->fetchAll($sql, array('propertyValue' => $arguments[0]));
// Convert query result to an array of objects
$objectList = array();
foreach ($result as $row) {
$id = $row['id'];
$objectList[$id] = $this->buildDomainObject($row);
}
return $objectList;
} elseif (substr($methodName, 0, 9) === 'findOneBy' && strlen($methodName) > 10) {
$propertyName = lcfirst(substr($methodName, 9));
$sql = "select * from " . $this->tableName . " where id = :id";
$row = $this->db->fetchAssoc($sql, array('id' => $arguments[0]));
if ($row) {
return $this->buildDomainObject($row);
} else {
throw new \Exception("No " . $this->objectName . " matching " . $propertyName . " " . $arguments[0]);
}
} elseif (substr($methodName, 0, 7) === 'countBy' && strlen($methodName) > 8) {
$propertyName = lcfirst(substr($methodName, 7));
$sql = "select COUNT(id) from " . $this->tableName . " where " . $propertyName . " = :propertyValue order by id desc";
$result = current($this->db->fetchAssoc($sql, array('propertyValue' => $arguments[0])));
return $result;
}
throw new \Exception('The method "' . $methodName . '" is not supported by the repository.', 1233180480);
}
public function findOne($id)
{
$sql = "SELECT * FROM articles WHERE id = ?";
$result = $this->db->fetchAssoc($sql, array((int) $id));
$article = $this->buildArticle($result[0]);
return $article;
}
public function findByNameAndPassword($admin)
{
$name = $admin->getName();
$password = $admin->getPassword();
$adminData = $this->db->fetchAssoc('SELECT * FROM admin WHERE name = ? and password = ?', array($name, $password));
return $adminData ? $this->buildAdmin($adminData) : FALSE;
}
public function count()
{
$qb = $this->dbal->createQueryBuilder();
$qb->select('count(*) c')->from('(' . $this->qb->getSQL() . ')', 'xxx');
$row = $this->dbal->fetchAssoc($qb->getSQL());
return $this->count = intval($row['c']);
}
public function findByUriValid($uri)
{
$sql = "SELECT * FROM {$this->entityTable} WHERE uri = ? AND compromised=0";
$row = $this->db->fetchAssoc($sql, array((string) $uri));
if ($row) {
return $this->createEntity($row);
}
}
public function find($id)
{
$record = $this->conn->fetchAssoc("SELECT id, message, author, parent FROM messages WHERE id = :id", [':id' => $id]);
if (!$record) {
throw new ObjectNotFoundException("Message not found for ID: {$id}");
}
return $record;
}
public function getCredentialChangeRequest($id, User $currentUser)
{
$data = $this->conn->fetchAssoc('SELECT * FROM `' . CoreTables::CREDENTIAL_CHANGE_TBL . '` WHERE `id` = :id', [':id' => $id]);
if (empty($data) || $data['userId'] != $currentUser->getId()) {
throw new ModelException('The specified credential change request does not exist.');
}
return CredentialChangeRequest::fromArray($currentUser, $data);
}
/**
* @param string $workflowId
* @return array|null
*/
public function getFlowchartConfig($workflowId)
{
$flowchartConfig = $this->connection->fetchAssoc("SELECT * FROM {$this->flowchartConfigTable} WHERE workflow_id = :id", ['id' => $workflowId]);
if (!$flowchartConfig) {
return null;
}
return ['workflow_id' => $flowchartConfig['workflow_id'], 'config' => json_decode($flowchartConfig['config']), 'last_updated_at' => $flowchartConfig['last_updated_at']];
}
public function findByUsername($username)
{
$record = $this->conn->fetchAssoc("SELECT id, username, age FROM users WHERE username = :username", [':username' => $username]);
if (!$record) {
throw new ObjectNotFoundException("User not found for name: {$username}");
}
return $record;
}
/**
* @return string
*/
public function getVersion()
{
$result = $this->connection->fetchAssoc('SELECT value AS version FROM pref WHERE prop="version"');
if ($result !== false) {
return $result['version'];
}
return VersionRepository::DEFAULT_VERSION;
}
/**
* @return string
*/
public function getVersion()
{
$result = $this->connection->fetchAssoc('SELECT version FROM sitepreff');
if ($result !== false) {
return $result['version'];
}
return VersionRepository::DEFAULT_VERSION;
}
/**
* @param string $id
* @return array|null
*/
public function find($id)
{
$handlerData = $this->connection->fetchAssoc('SELECT * FROM ' . Tables::MESSAGE_HANDLER . ' WHERE id = :id', ['id' => $id]);
if (empty($handlerData)) {
return null;
}
$this->fromDatabase($handlerData);
return $handlerData;
}
public function fetchVocabularyByName($name)
{
$vocabulary = $this->dbal->fetchAssoc('SELECT * FROM taxonomyVocabulary WHERE name = :name', ['name' => $name]);
if ($vocabulary) {
$vocabulary = $this->vocabularyHydrator->hydrate($vocabulary, new Vocabulary());
return $vocabulary;
}
throw new VocabularyNotFoundException('Could not find vocabulary ' . $name);
}
/**
* @param string $sku
* @return Product
* @throws QueryException
*/
public function getBySku(string $sku) : Product
{
$qb = $this->connection->createQueryBuilder();
$qb->select('*')->from('dumplie_inventory_product')->where('sku = :sku')->setParameter('sku', $sku);
$productData = $this->connection->fetchAssoc($qb->getSQL(), $qb->getParameters());
if (empty($productData)) {
throw QueryException::productNotFound($sku);
}
return new Product($productData['sku'], $productData['price_amount'] / $productData['price_precision'], $productData['price_currency'], (bool) $productData['is_in_stock'], $this->mao->getBy([Metadata::FIELD_SKU => $productData['sku']]));
}
/**
* @param SKU $sku
* @return Product
* @throws ProductNotFoundException
*/
public function getBySku(SKU $sku) : Product
{
$queryBuilder = $this->connection->createQueryBuilder();
$queryBuilder->select('sku', 'is_in_stock', 'price_amount', 'price_currency', 'price_precision')->from('dumplie_inventory_product')->where('sku = :sku')->setParameter('sku', (string) $sku);
$result = $this->connection->fetchAssoc($queryBuilder->getSQL(), $queryBuilder->getParameters());
if (!$result) {
throw ProductNotFoundException::bySku($sku);
}
return new Product(new SKU($result['sku']), new Price((int) $result['price_amount'], $result['price_currency'], (int) $result['price_precision']), (bool) $result['is_in_stock']);
}
/**
* @param string $sku
* @param int $quantity
*
* @return CartItem
* @throws QueryException
*/
private function getItemBySku(string $sku, int $quantity) : CartItem
{
$qb = $this->connection->createQueryBuilder();
$qb->select('*')->from('dumplie_inventory_product')->where('sku = :sku')->setParameter('sku', $sku);
$itemData = $this->connection->fetchAssoc($qb->getSQL(), $qb->getParameters());
if (empty($itemData)) {
throw QueryException::cartItemNotFound($sku);
}
return new CartItem($itemData['sku'], $quantity, $itemData['price_amount'] / $itemData['price_precision'], $itemData['price_currency'], $this->mao->getBy([Metadata::FIELD_SKU => $itemData['sku']]));
}
/**
* @return Project
*/
public function getItem($id)
{
$this->transaction->requestTransaction();
$data = $this->conn->fetchAssoc('SELECT * FROM `' . CoreTables::PROJECT_TBL . '` WHERE `id` = :id', [':id' => $id]);
if (null === $data) {
$this->transaction->requestRollback();
throw new ItemNotFoundException('The specified item has not been found.', $id);
}
return Project::fromArray($data);
}
/**
* {@inheritdoc}
*/
public function shouldBeRun()
{
$schemaManager = $this->connection->getSchemaManager();
if (!$schemaManager->tablesExist('tl_member')) {
return false;
}
$sql = $this->connection->getDatabasePlatform()->getListTableIndexesSQL('tl_member', $this->connection->getDatabase());
$index = $this->connection->fetchAssoc($sql . " AND INDEX_NAME = 'username'");
return '0' !== $index['Non_Unique'];
}
public function testDb1()
{
$result = self::$db->fetchAssoc('SELECT * FROM "user" LIMIT 1');
$this->assertArrayHasKey('user_id', $result);
$q = self::$db->createQueryBuilder();
/** @var \Doctrine\DBAL\Driver\Statement $stmt */
$stmt = $q->select('*')->from('"user"', 't')->setMaxResults(50)->execute();
$result = $stmt->rowCount();
$this->assertEquals(50, $result);
}
public function getCourseByIdentifier($id)
{
$sql = "SELECT * FROM vakken WHERE id=:id";
$params = ['id' => $id];
$data = $this->conn->fetchAssoc($sql, $params);
if ($data) {
return $this->hydrateCourse($data);
}
return null;
}
public function fetchUserByGithubUid($uid)
{
$user = $this->dbal->fetchAssoc('SELECT * FROM users WHERE githubUid = :githubUid', ['githubUid' => $uid]);
if ($user) {
$user = $this->hydrator->hydrate($user, new User());
$this->in_memory_users[$user->getId()] = $user;
return $user;
}
throw new UserNotFoundException('Could not find user with a UID of ' . $uid);
}
/**
* @param $id
* @return Conversation
*/
public function findById($id)
{
if (array_key_exists($id, $this->in_memory_convos)) {
return $this->in_memory_convos[$id];
}
$convo_data = $this->dbal->fetchAssoc('SELECT * FROM conversations WHERE id = :conversation_id', ['conversation_id' => $id]);
if (!$convo_data) {
throw new ConversationNotFoundException(sprintf('Could not find conversation with ID of %s', $id));
}
return $this->hydrateConversation($convo_data);
}
/**
* gets a user by its identifier
*
* @param $id
* @return null|User
*/
public function getUserByIdentifier($id)
{
$sql = "SELECT * FROM users WHERE id=:id LIMIT 0,1";
$params = ['id' => $id];
$data = $this->conn->fetchAssoc($sql, $params);
if ($data) {
$user = $this->hydrateUser($data);
return $user;
}
return null;
}
/**
* Authenticates the user from API
*
* @param array $phoneBook
*/
public function authenticate(array $responseData, $app)
{
$userData = $this->db->fetchAssoc('SELECT * FROM users WHERE username = ? and password = ?', array($responseData["username"], sha1($responseData["password"])));
if ($userData) {
//$_session["username"] = $responseData['username'];
$csrf = uniqid();
$app['session']->set('csrf', $csrf);
return array("csrf" => $csrf);
} else {
return false;
}
}
/**
* {@inheritdoc}
*/
public function get($id)
{
$query = sprintf('SELECT * FROM %s WHERE %s = ?', $this->connection->quoteIdentifier($this->tableName), $this->connection->quoteIdentifier(self::COLUMN_ID));
try {
$row = $this->connection->fetchAssoc($query, [$id]);
} catch (DBALException $e) {
throw DatabaseException::fromDBALException($e);
}
if ($row === false) {
return null;
}
return $this->rowToData($row);
}
private function getMetadata($place)
{
$tpl = $this->getActualPart($place);
$locale = $this->getLocalePart($place);
if (!empty($this->cachedMetadata[$place])) {
return $this->cachedMetadata[$place];
}
$metadata = $this->conn->fetchAssoc('SELECT `id`, `subject`, `lastUpdate` FROM `' . CoreTables::MAIL_TBL . '` WHERE `place` = :place AND `locale` = :locale', [':place' => $tpl, ':locale' => $locale]);
if (empty($metadata)) {
throw new MailException('No such mail template: ' . $place . ' with locale ' . $this->locale);
}
return $this->cachedMetadata[$place] = $metadata;
}
public function findOneById($id)
{
if (!is_int($id)) {
throw new \InvalidArgumentException("l\\'id est sensé etre un integer. vous avez donné: " . $id, 500);
}
$result = $this->db->fetchAssoc("select * from where id = :id;", array(':id' => int_val($id)));
if ($result == null || empty($result)) {
return null;
}
$event = new GenericEvent($result);
$event->setArgument("id", $id);
$this->eventDispatcher->dispatch("post-findonebyid-" . $this->entityName, $event);
return $event->getSubject();
}
public function isInitialised(Connection $dbConn)
{
$query = "\n SELECT\n SCHEMA_NAME\n FROM\n INFORMATION_SCHEMA.SCHEMATA\n WHERE\n SCHEMA_NAME = 'RollerRevisions'";
$results = $dbConn->fetchAssoc($query);
if (!$results) {
return false;
}
$query = "\n SELECT\n TABLE_NAME\n FROM\n INFORMATION_SCHEMA.TABLES\n WHERE\n TABLE_SCHEMA = 'RollerRevisions'\n AND\n TABLE_NAME = 'Revision'";
$results = $dbConn->fetchAssoc($query);
if (!$results) {
return false;
}
return true;
}
public function editAction(Request $request, $id)
{
if ($request->isMethod('POST')) {
// var_dump($request->request->all());
// die();
$this->db->update('post', ['published' => $request->request->has('published'), 'heading' => $request->request->get('heading'), 'content' => $request->request->get('content'), 'created_at' => $request->request->get('created_at')], ['id' => $id]);
$url = $this->generateUrl('admin_post_edit', ['id' => $id]);
return $this->app->redirect($url, 302);
} else {
// var_dump($request->query->all()); //For Get
}
// var_dump($request->server->all()); //For _SERVER
$post = $this->db->fetchAssoc('SELECT * FROM post WHERE id = ?', [$id]);
return $this->twig->render('/admin/post/edit.twig', array('post' => $post));
}
