Prepares and executes an SQL query and returns the result as an associative array.
/**
* @param QueryInterface $query
* @return array
*/
public function fetch(QueryInterface $query)
{
$query->checkReplacements();
$plainQuery = $query->getPlainQuery();
list($parameters, $plainQuery) = $this->modifyParametersFromArrayToScalar($query->getParameters(), $plainQuery);
return $this->connection->fetchAll($plainQuery, $parameters);
}
protected function getRecentFrequent(User $user, array $criteria = null, $limit = 10)
{
$sql = '
SELECT text, MAX(createdAt) AS recency, COUNT(*) AS frequency
FROM stress_log_factors
JOIN stress_logs ON stress_log_id = stress_logs.id
WHERE user_id = ?
';
$params = array($user->getId());
if (is_array($criteria)) {
foreach ($criteria as $key => $val) {
$op = '=';
if (is_array($val)) {
reset($val);
$op = key($val);
$val = current($val);
}
$sql .= 'AND `' . $key . '` ' . $op . ' ? ';
$params[] = $val;
}
}
$sql .= 'GROUP BY text ';
$sql .= 'ORDER BY recency DESC, frequency DESC ';
$sql .= 'LIMIT ' . (int) $limit;
$result = $this->conn->fetchAll($sql, $params);
return array_column($result, 'text');
}
/**
* Получить продукты по ID категории
*
* @param $categoryId ID категории
*
* @return array
*/
public function getProductsByCategoryId($categoryId)
{
return $this->db->fetchAll('SELECT `products`.* FROM `products`
INNER JOIN `products_categories`
ON `products`.`id` = `products_categories`.`product_id`
WHERE `products_categories`.`category_id` = ?', array($categoryId));
}
public function exportData($export, $reporter)
{
$this->conn->beginTransaction();
try {
$lastExportedAt = (int) $export['lastExportedAt'];
$areas = $this->conn->fetchAll('SELECT a.`id`, a.`name`, t.`id` AS `territoryId`, t.`name` AS `territoryName`, a.`customData`, a.`lastUpdatedAt` ' . 'FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . CoreTables::TERRITORY_TBL . '` t ON t.`id` = a.`territoryId` ' . 'WHERE a.`projectId` = :projectId AND a.`statusId` = :statusId', [':projectId' => $export['projectId'], ':statusId' => $export['areaStatusId']]);
$block = new ExportBlock();
foreach ($areas as $area) {
$block->addId($area['id']);
if ($area['lastUpdatedAt'] > $lastExportedAt) {
$area['customData'] = json_decode($area['customData']);
$block->addUpdatedId($area['id']);
$block->addUpdate($area);
}
}
$event = new ExportEvent($export['projectId'], $export['lastExportedAt'], $reporter);
$event->addBlock('area', $block);
$event = $this->eventDispatcher->dispatch(ExportEvents::EXPORT_ONGOING, $event);
$this->conn->executeQuery('UPDATE `' . ExportTables::DATA_EXPORT_TBL . '` SET `lastExportedAt` = :time WHERE `id` = :id', [':time' => time(), ':id' => $export['id']]);
$this->conn->commit();
return $event->output();
} catch (Exception $ex) {
$this->conn->rollBack();
throw $ex;
}
}
public function findAll()
{
$result = $this->db->fetchAll("select * from " . $this->table . ";");
$event = new GenericEvent($result);
$this->eventDispatcher->dispatch("post-findall-" . $this->entityName, $event);
return $event->getSubject();
}
/**
* 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 indexAction()
{
// $this->init($app);
$posts = $this->db->fetchAll('SELECT * FROM post');
//var_dump($posts);
return $this->twig->render('post/index.twig', array('posts' => $posts));
}
/**
* @param LoggerInterface $logger
* @param bool $dryRun
*/
protected function doExecute(LoggerInterface $logger, $dryRun = false)
{
$duplicateEntitiesQuery = 'SELECT
DISTINCT t2.id
FROM
orocrm_campaign_email_stats AS t1
LEFT JOIN orocrm_campaign_email_stats AS t2
ON t1.email_campaign_id = t2.email_campaign_id
AND t1.marketing_list_item_id = t2.marketing_list_item_id
AND t2.id > t1.id
WHERE t2.id IS NOT NULL';
// Done in 2 queries for cross DB support.
$idsToRemove = array_map(function ($item) {
if (is_array($item) && array_key_exists('id', $item)) {
return $item['id'];
}
return null;
}, $this->connection->fetchAll($duplicateEntitiesQuery));
if ($idsToRemove) {
$query = 'DELETE FROM orocrm_campaign_email_stats WHERE id IN (?)';
$logger->notice($query);
if (!$dryRun) {
$this->connection->executeQuery($query, [$idsToRemove], [Connection::PARAM_INT_ARRAY]);
}
}
}
public function collectData(IdentifiableInterface $root)
{
$routes = $this->conn->fetchAll('SELECT `routeAscent` ' . 'FROM `' . EdkTables::ROUTE_TBL . '` r ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON r.`areaId` = a.`id` ' . 'WHERE a.`projectId` = :projectId AND r.`routeType` = 0', [':projectId' => $root->getId()]);
if (sizeof($routes) == 0) {
return false;
}
foreach ($routes as &$row) {
$row['normalized'] = $this->step($row['routeAscent']);
}
$this->data = [];
$min = 10000;
$max = 0;
foreach ($routes as $row) {
if ($row['normalized'] < $min) {
$min = $row['normalized'];
}
if ($row['normalized'] > $max) {
$max = $row['normalized'];
}
}
if ($min >= $max) {
return false;
}
for ($i = $min; $i <= $max; $i++) {
$this->data[$i] = 0;
}
foreach ($routes as $row) {
$this->data[$row['normalized']]++;
}
return true;
}
/**
* Returns all records from this repository's table
*
* @param integer $limit
*
* @return array
*/
public function findAll($limit = null)
{
if (null === $limit) {
return $this->db->fetchAll(sprintf('SELECT * FROM %s', $this->getTableName()));
}
return $this->db->fetchAll(sprintf('SELECT * FROM %s LIMIT %d', $this->getTableName(), $limit));
}
/**
* @param $messageHandlerId
* @return array of task data
*/
public function findTasksOfMessageHandler($messageHandlerId)
{
$tasks = $this->connection->fetchAll('SELECT * FROM ' . Tables::TASK . ' WHERE message_handler_id = :mhid', ['mhid' => $messageHandlerId]);
foreach ($tasks as &$task) {
$this->fromDatabase($task);
}
return $tasks;
}
/**
* @return Exercise[]
*/
public function getAll()
{
$exercises = [];
foreach ($this->connection->fetchAll('SELECT id, title, description FROM exercises') as $data) {
$exercises[] = new Exercise($data['id'], $data['title'], $data['description']);
}
return $exercises;
}
private function getTableNames()
{
$tableNames = [];
foreach ($this->connection->fetchAll('SHOW TABLES') as $row) {
$tableNames[] = $row['Tables_in_' . $this->connection->getDatabase()];
}
return $tableNames;
}
/**
* @param string $organisation
* @param string $project
*
* @return array
*/
public function getProjects($organisation, $project)
{
if (false === empty($project)) {
return [$project];
}
$projects = $this->connection->fetchAll('SELECT project FROM events WHERE organisation = ? GROUP BY project', [$organisation]);
return array_column($projects, 'project');
}
/**
* @param Contract $streamContract
* @param Identifier $streamId
* @return EventEnvelope[]
*/
public function fetch(Contract $streamContract, Identifier $streamId)
{
$records = $this->connection->fetchAll(Query\Select::from(self::TABLE_NAME), ['streamContract' => $streamContract, 'streamId' => $streamId]);
$eventEnvelopes = array_map(function (array $record) {
return EventEnvelope::reconstitute(EventId::fromString($record['eventId']), Contract::with($record['eventContract']), $record['eventPayload']);
}, $records);
return $eventEnvelopes;
}
/**
* Fetch all
*
* @return array
*/
public function fetchAll()
{
$tableName = $this->connection->quoteIdentifier($this->tableName);
$sql = "SELECT version FROM {$tableName} ORDER BY version ASC";
$all = $this->connection->fetchAll($sql);
return array_map(function ($v) {
return $v['version'];
}, $all);
}
private function buildRepresentations(User $user)
{
$projects = $this->conn->fetchAll('SELECT g.`id`, g.`name`, g.`slug`, c.`role`, c.`note` FROM `' . CoreTables::GROUP_TBL . '` g ' . 'INNER JOIN `' . CoreTables::GROUP_MEMBER_TBL . '` c ON c.`groupId` = g.`id` ' . 'WHERE c.`userId` = :userId ORDER BY g.`name`', [':userId' => $user->getId()]);
$items = array();
foreach ($projects as $proj) {
$items[] = new ProjectRepresentation($proj['slug'], $proj['name'], 'group_dashboard', 'GroupNominative: 0', 'default', $this->resolver->getRole('Group', $proj['role']), $proj['note']);
}
return $items;
}
private function buildRepresentations(User $user)
{
$projects = $this->conn->fetchAll('SELECT a.`id`, a.`name`, a.`slug`, c.`role`, c.`note` FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . CoreTables::AREA_MEMBER_TBL . '` c ON c.`areaId` = a.`id` ' . 'WHERE c.`userId` = :userId ORDER BY a.`name`', [':userId' => $user->getId()]);
$items = array();
foreach ($projects as $proj) {
$items[] = new ProjectRepresentation($proj['slug'], $proj['name'], 'area_dashboard', 'AreaNominative: 0', 'purple', $this->resolver->getRole('Area', $proj['role']), $proj['note']);
}
return $items;
}
public function findAvailableCourses(User $user)
{
$items = $this->conn->fetchAll('SELECT c.`id`, c.`name`, c.`deadline`, r.`result` AS `user_result`, r.`passedQuestions` AS `user_passedQuestions`, ' . 'r.`totalQuestions` AS `user_totalQuestions`, r.`completedAt` AS `user_completedAt`, arr.`result` AS `area_result`, arr.`passedQuestions` AS `area_passedQuestions`, ' . 'arr.`totalQuestions` AS `area_totalQuestions`, arr.`completedAt` AS `area_completedAt` ' . 'FROM `' . CourseTables::COURSE_TBL . '` c ' . 'LEFT JOIN `' . CourseTables::COURSE_AREA_RESULT_TBL . '` ar ON (ar.`courseId` = c.`id` AND ar.`areaId` = :areaId) ' . 'LEFT JOIN `' . CourseTables::COURSE_RESULT_TBL . '` arr ON (arr.`courseId` = ar.`courseId` AND arr.`userId` = ar.`userId`) ' . 'LEFT JOIN `' . CourseTables::COURSE_RESULT_TBL . '` r ON (r.`courseId` = c.`id` AND r.`userId` = :userId) ' . 'WHERE c.`isPublished` = 1 AND c.`projectId` = :projectId ORDER BY c.`displayOrder`', [':areaId' => $this->area->getId(), ':userId' => $user->getId(), ':projectId' => $this->area->getProject()->getId()]);
foreach ($items as &$item) {
TestResult::processResults($item, 'user_');
TestResult::processResults($item, 'area_');
}
return $items;
}
public function getApplied()
{
$appliedMigrations = $this->connection->fetchAll(sprintf('SELECT * FROM %s', $this->migrationTableName));
$applied = [];
foreach ($appliedMigrations as $item) {
$applied[] = $item['name'];
}
return $applied;
}
public function findMilestoneProgressForAreasInGroup(Group $group)
{
$totalMilestones = $this->conn->fetchColumn('SELECT COUNT(`id`) FROM `' . MilestoneTables::MILESTONE_TBL . '` WHERE `projectId` = :projectId AND `entityType` = \'Area\'', [':projectId' => $group->getProject()->getId()]);
$results = $this->conn->fetchAll('SELECT a.`id`, a.`name`, a.`entityId`, p.`completedNum` ' . 'FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . MilestoneTables::MILESTONE_PROGRESS_TBL . '` p ON p.`entityId` = a.`entityId` ' . 'WHERE a.`groupId` = :groupId ' . 'ORDER BY p.`completedNum` DESC, a.`name`', [':groupId' => $group->getId()]);
foreach ($results as &$result) {
$this->processResult($totalMilestones, $result);
}
return $results;
}
/**
* @param int $limit
* @param int $offset
* @return array
*/
public function findAll(int $limit, int $offset = 0) : array
{
$qb = $this->connection->createQueryBuilder();
$qb->select('*')->from('dumplie_inventory_product')->setFirstResult($offset)->setMaxResults($limit);
$results = $this->connection->fetchAll($qb->getSQL(), $qb->getParameters());
return array_map(function ($data) {
return new Product($data['sku'], $data['price_amount'] / $data['price_precision'], $data['price_currency'], (bool) $data['is_in_stock'], $this->mao->getBy([Metadata::FIELD_SKU => $data['sku']]));
}, $results);
}
/**
* @return array<string,int>
*/
public function findCategories()
{
$sql = 'SELECT p_category, count(*) as products FROM shop_products GROUP BY p_category';
$rows = $this->conn->fetchAll($sql);
$categories = array();
foreach ($rows as $row) {
$categories[$row['p_category']] = $row['products'];
}
return $categories;
}
public function fetchMentors()
{
$data = $this->dbal->fetchAll('SELECT * FROM users WHERE isMentor = 1');
$users = [];
foreach ($data as $userData) {
$users[] = $this->hydrator->hydrate($userData, $user = new User());
$this->in_memory_users[$user->getId()] = $user;
}
return $users;
}
/**
* Updates the statistics for area requests in the current day.
*
* @param \Cantiga\CoreBundle\Repository\AreaRequestEvent $event
*/
public function onAreaRequestStatusChange(AreaRequestEvent $event)
{
$project = $event->getAreaRequest()->getProject();
$values = [0 => 0, 1 => 0, 2 => 0, 3 => 0];
$calculated = $this->conn->fetchAll('SELECT `status`, COUNT(`id`) AS `counted` FROM `' . CoreTables::AREA_REQUEST_TBL . '` WHERE `projectId` = :projectId GROUP BY `status`', [':projectId' => $project->getId()]);
foreach ($calculated as $row) {
$values[$row['status']] = $row['counted'];
}
$date = date('Y-m-d');
$this->conn->executeQuery('INSERT INTO `' . CoreTables::STAT_ARQ_TIME_TBL . '` (`projectId`, `datePoint`, `requestsNew`, `requestsVerification`, `requestsApproved`, `requestsRejected`)' . 'VALUES(:projectId, :datePoint, :rn1, :rv1, :ra1, :rr1) ON DUPLICATE KEY UPDATE `requestsNew` = :rn2, `requestsVerification` = :rv2, `requestsApproved` = :ra2, `requestsRejected` = :rr2', [':projectId' => $project->getId(), ':datePoint' => $date, ':rn1' => $values[AreaRequest::STATUS_NEW], ':rv1' => $values[AreaRequest::STATUS_VERIFICATION], ':ra1' => $values[AreaRequest::STATUS_APPROVED], ':rr1' => $values[AreaRequest::STATUS_REVOKED], ':rn2' => $values[AreaRequest::STATUS_NEW], ':rv2' => $values[AreaRequest::STATUS_VERIFICATION], ':ra2' => $values[AreaRequest::STATUS_APPROVED], ':rr2' => $values[AreaRequest::STATUS_REVOKED]]);
}
/**
* Get all custom components of the plugin. Is cached per request
*
* @return mixed
*/
public function getCustomComponents()
{
if ($this->components) {
return $this->components;
}
$components = $this->connection->fetchAll('SELECT * FROM `s_library_component` WHERE pluginID = ?', [$this->pluginId]);
foreach ($components as $component) {
$this->components[$component['cls']] = $component;
}
return $this->components;
}
/**
* get a user by its username
*
* @param $username
* @return null|User
*/
public function getUserByUsername($username)
{
$sql = "SELECT * FROM users WHERE username=:username LIMIT 0,1";
$params = ['username' => $username];
$data = $this->conn->fetchAll($sql, $params);
if ($data) {
$user = $this->hydrateUser($data[0]);
return $user;
}
return null;
}
/**
* Return a list of all articles, sorted by date (most recent first).
*
* @return array A list of all articles.
*/
public function findAll()
{
$sql = "SELECT * FROM articles";
$result = $this->db->fetchAll($sql);
$articles = array();
foreach ($result as $row) {
$articleId = $row['id'];
$articles[$articleId] = $this->buildArticle($row);
}
return $articles;
}
/**
* Return a list of all articles, sorted by date (most recent first).
*
* @return array A list of all articles.
*/
public function findAll()
{
$sql = "select * from t_article order by art_id desc";
$result = $this->db->fetchAll($sql);
// Convert query result to an array of domain objects
$articles = array();
foreach ($result as $row) {
$articleId = $row['art_id'];
$articles[$articleId] = $this->buildArticle($row);
}
return $articles;
}
/**
* Return a list of all movies, sorted by date (most recent first).
*
* @return array A list of all movies.
*/
public function findAll()
{
$sql = "select * from movie order by mov_id desc";
$result = $this->db->fetchAll($sql);
// Convert query result to an array of domain objects
$movies = array();
foreach ($result as $row) {
$movieId = $row['mov_id'];
$movies[$movieId] = $this->buildMovie($row);
}
return $movies;
}
