/**
* @test
*/
public function it_saves_and_reads()
{
$schema = new Schema();
SnapshotStoreSchema::create($schema, 'foo_snapshot');
foreach ($schema->toSql($this->connection->getDatabasePlatform()) as $sql) {
$this->connection->executeQuery($sql);
}
$adapter = new DoctrineSnapshotAdapter($this->connection);
$aggregateType = AggregateType::fromString('foo');
$aggregateRoot = new \stdClass();
$aggregateRoot->foo = 'bar';
$time = microtime(true);
if (false === strpos($time, '.')) {
$time .= '.0000';
}
$now = \DateTimeImmutable::createFromFormat('U.u', $time);
$snapshot = new Snapshot($aggregateType, 'id', $aggregateRoot, 1, $now);
$adapter->save($snapshot);
$snapshot = new Snapshot($aggregateType, 'id', $aggregateRoot, 2, $now);
$adapter->save($snapshot);
$this->assertNull($adapter->get($aggregateType, 'invalid'));
$readSnapshot = $adapter->get($aggregateType, 'id');
$this->assertEquals($snapshot, $readSnapshot);
$statement = $this->connection->prepare('SELECT * FROM foo_snapshot');
$statement->execute();
$snapshots = $statement->fetchAll();
$this->assertCount(1, $snapshots);
}
public function restorePortfolioTitle()
{
$totalPortfolioProcessed = 0;
$nbPortfolioProcessed = 0;
if ($this->connection->getSchemaManager()->tablesExist(array('icap__portfolio_widget_title'))) {
$this->log('Restoring portfolio titles...');
$rowPortfolioTitles = $this->connection->query('SELECT * FROM icap__portfolio_widget_title');
$sql = 'SELECT aw.id, aw.user_id FROM icap__portfolio_abstract_widget aw WHERE id = :id';
$stmt = $this->connection->prepare($sql);
foreach ($rowPortfolioTitles as $rowPortfolioTitle) {
$stmt->bindValue('id', $rowPortfolioTitle['id']);
$stmt->execute();
foreach ($stmt->fetchAll() as $rowAbstractWidget) {
$this->connection->update('icap__portfolio', ['title' => $rowPortfolioTitle['title'], 'slug' => $rowPortfolioTitle['slug']], ['id' => $rowAbstractWidget['user_id']]);
}
$this->connection->delete('icap__portfolio_abstract_widget', ['id' => $rowPortfolioTitle['id']]);
++$nbPortfolioProcessed;
if ($nbPortfolioProcessed >= 10) {
$totalPortfolioProcessed += $nbPortfolioProcessed;
$nbPortfolioProcessed = 0;
$this->log(' processing portfolio...');
}
}
$this->log(sprintf(' %d portfolio processed', $totalPortfolioProcessed + $nbPortfolioProcessed));
$this->connection->delete('icap__portfolio_widget_type', ['name' => 'title']);
$this->connection->getSchemaManager()->dropTable('icap__portfolio_widget_title');
}
}
private function applyChanges($areas, $rules, $reverseRules)
{
$stmt = $this->conn->prepare('UPDATE `' . CoreTables::AREA_TBL . '` SET `statusId` = :statusId, `lastUpdatedAt` = :lastUpdated WHERE `id` = :id');
$count = 0;
foreach ($areas as $area) {
foreach ($rules as $rule) {
if ($this->isMatching($area, $rule)) {
$stmt->bindValue(':id', $area['id']);
$stmt->bindValue(':statusId', $rule['newStatusId']);
$stmt->bindValue(':lastUpdated', time());
$stmt->execute();
$count++;
continue 2;
}
}
foreach ($reverseRules as $rule) {
if ($this->isNotMatching($area, $rule)) {
$stmt->bindValue(':id', $area['id']);
$stmt->bindValue(':statusId', $rule['prevStatusId']);
$stmt->bindValue(':lastUpdated', time());
$stmt->execute();
$count++;
continue 2;
}
}
}
return $count;
}
/**
* @param $todoId
* @return null|\stdClass containing userData
*/
public function findUserOfTodo($todoId)
{
$stmt = $this->connection->prepare(sprintf("SELECT u.* FROM %s as u JOIN %s as t ON u.id = t.assignee_id where t.id = :todo_id", Table::USER, Table::TODO));
$stmt->bindValue('todo_id', $todoId);
$stmt->execute();
return $stmt->fetch();
}
/**
* @param Backlog[] $backlogs
*/
private function writeBacklog(array $backlogs)
{
$statement = $this->connection->prepare("\n INSERT IGNORE INTO s_es_backlog (`event`, `payload`, `time`)\n VALUES (:event, :payload, :time);\n ");
foreach ($backlogs as $backlog) {
$statement->execute([':event' => $backlog->getEvent(), ':payload' => json_encode($backlog->getPayload()), ':time' => $backlog->getTime()->format('Y-m-d H:i:s')]);
}
}
protected function buildOptions(ReportParameter $parameter)
{
$options = array();
if ($parameter->getType() == ReportParameter::TYPE_QUERY) {
$stmt = $this->connection->prepare($parameter->getChoices());
$stmt->execute();
$results = $stmt->fetchAll();
$values = array(null => '');
$keySet = array_keys(current($results));
$key = current(array_slice($keySet, 0, 1));
$value = current(array_slice($keySet, 1, 1));
foreach ($results as $result) {
$values[$result[$value]] = $result[$key];
}
$options = array_merge($options, array('choices' => $values));
$parameter->setType(ChoiceType::class);
}
$normalizer = new \Symfony\Component\Serializer\Normalizer\GetSetMethodNormalizer();
$normalizer->setIgnoredAttributes(array('id', 'name', 'type', 'created', 'modified', 'active'));
$serializer = new \Symfony\Component\Serializer\Serializer(array($normalizer));
$options = array_merge($serializer->normalize($parameter), $options);
foreach ($options as $key => $value) {
if (is_null($value)) {
unset($options[$key]);
}
}
$this->handleConstraints($options);
return $options;
}
/**
* @param $userId
* @return null|\stdClass containing userData
*/
public function findById($userId)
{
$stmt = $this->connection->prepare(sprintf("SELECT * FROM %s where id = :user_id", Table::USER));
$stmt->bindValue('user_id', $userId);
$stmt->execute();
return $stmt->fetch();
}
/**
* @param string $todoId
* @return \stdClass of todoData
*/
public function findById($todoId)
{
$stmt = $this->connection->prepare(sprintf("SELECT * FROM %s where id = :todo_id", Table::TODO));
$stmt->bindValue('todo_id', $todoId);
$stmt->execute();
return $stmt->fetch();
}
/**
* Execute
*/
public function execute(array $criteria = null)
{
$stmt = $this->connection->prepare($this->buildQuery());
$stmt->execute(array('mds' => $criteria['start_date']->format('m/d/Y'), 'mde' => $criteria['end_date']->format('m/d/Y')));
$results = $stmt->fetchAll();
$stmt->closeCursor();
return $results;
}
/**
* {@inheritDoc}
*/
public function getStreamIds()
{
$statement = $this->connection->prepare('SELECT DISTINCT uuid FROM ' . config('broadway.event-store.table'));
$statement->execute();
return array_map(function ($row) {
return $row['uuid'];
}, $statement->fetchAll());
}
/**
* @return ReadModelInterface[]
*/
public function findAll()
{
$statement = $this->connection->prepare(sprintf('SELECT serialized FROM %s WHERE class = :class', static::TABLE));
$statement->execute(['class' => $this->class]);
return array_map(function ($row) {
return $this->serializer->deserialize(json_decode($row['serialized'], true));
}, $statement->fetchAll(\PDO::FETCH_ASSOC));
}
/**
* Execute
*/
public function execute(array $criteria = null)
{
$stmt = $this->connection->prepare($this->buildQuery());
$stmt->execute(array('first_id' => $criteria['first_id'], 'last_id' => $criteria['last_id']));
$results = $stmt->fetchAll();
$stmt->closeCursor();
return $results;
}
public function __construct(Connection $con)
{
$this->con = $con;
$this->platform = $con->getDatabasePlatform();
$this->phpType = Type::getType(PhpTypeType::NAME);
$this->fStmt = $con->prepare(self::INSERT_FUNC_SQL);
$this->mStmt = $con->prepare(self::INSERT_METHOD_SQL);
}
public function getCategoryPaintings($catId)
{
$sql = 'SELECT * FROM pinturas where categorias_pintura_id = :catId';
$stmt = $this->db->prepare($sql);
$stmt->bindValue('catId', $catId);
$stmt->execute();
return $stmt->fetchAll();
}
public function getCategoryPerformances($catId)
{
$sql = 'SELECT * FROM teatros where categorias_teatro_id = :catId';
$stmt = $this->db->prepare($sql);
$stmt->bindValue('catId', $catId);
$stmt->execute();
return $stmt->fetchAll();
}
public function dropTable()
{
$schema = $this->getTableSchema();
$queries = $schema->toDropSql($this->connection->getDatabasePlatform());
foreach ($queries as $query) {
$this->connection->prepare($query)->execute();
}
}
public function postUpdate()
{
$em = $this->container->get('doctrine.orm.entity_manager');
$process = false;
if (in_array('claro_forum_subject_temp', $this->conn->getSchemaManager()->listTableNames())) {
$columns = $this->conn->getSchemaManager()->listTableColumns('claro_forum_subject_temp');
foreach ($columns as $column) {
if ($column->getName() === 'forum_id') {
$process = true;
break;
}
}
}
if ($process) {
$this->log('restoring the subjects...');
$forums = $em->getRepository('ClarolineForumBundle:Forum')->findAll();
$sql = 'SELECT * FROM claro_forum_subject_temp WHERE forum_id = :forumId';
$stmt = $this->conn->prepare($sql);
foreach ($forums as $forum) {
$category = new Category();
$category->setName($forum->getResourceNode()->getName());
$category->setForum($forum);
$em->persist($category);
$em->flush();
$stmt->bindValue('forumId', $forum->getId());
$stmt->execute();
foreach ($stmt->fetchAll() as $rowsSubject) {
$this->conn->query("INSERT INTO claro_forum_subject VALUES (\n {$rowsSubject['id']},\n {$category->getId()},\n {$rowsSubject['user_id']},\n {$this->conn->quote($rowsSubject['title'])},\n '{$rowsSubject['created']}',\n '{$rowsSubject['updated']}',\n false\n )");
}
}
$this->log('restoring the messages...');
$this->conn->query('INSERT IGNORE INTO claro_forum_message SELECT * FROM claro_forum_message_temp');
$this->conn->query('DROP TABLE claro_forum_message_temp');
$this->conn->query('DROP TABLE claro_forum_subject_temp');
$this->conn->query('DROP TABLE claro_forum_options');
} else {
$this->log('categories already added');
}
$widget = $em->getRepository('ClarolineCoreBundle:Widget\\Widget')->findBy(array('name' => 'claroline_forum_widget'));
if (!$widget) {
$this->log('adding the forum widget...');
$plugin = $em->getRepository('ClarolineCoreBundle:Plugin')->findOneBy(array('vendorName' => 'Claroline', 'bundleName' => 'ForumBundle'));
$widget = new Widget();
$widget->setName('claroline_forum_widget');
$widget->setDisplayableInDesktop(true);
$widget->setDisplayableInWorkspace(true);
$widget->setConfigurable(false);
$widget->setExportable(false);
$widget->setIcon('none');
$widget->setPlugin($plugin);
$em->persist($widget);
$plugin->setHasOptions(true);
$em->persist($widget);
$em->flush();
} else {
$this->log('forum widget already added');
}
}
/**
* totals the month revenue of buggl
* @param Integer $month month number
* @param Integer $year year
*
* @return Decimal
*/
public function countMonthlyBugglRevenue($month, $year)
{
// $sql = "SELECT sum(info.buggl_fee) FROM purchase_info info WHERE MONTH(info.date_of_transaction) = $month AND YEAR(info.date_of_transaction) = $year";
$sql = "SELECT sum(info.buggl_fee) FROM paypal_purchase_info info WHERE MONTH(info.date_of_transaction) = {$month} AND YEAR(info.date_of_transaction) = {$year}";
$stmt = $this->connection->prepare($sql);
$stmt->execute();
$count = $stmt->fetch(PDO::FETCH_NUM);
return is_null($count[0]) ? 0 : $count[0];
}
public function __construct(Connection $con)
{
$this->con = $con;
$this->platform = $con->getDatabasePlatform();
$this->phpType = Type::getType(PhpTypeType::NAME);
$this->propertyIdRef = new \ReflectionProperty('Scrutinizer\\PhpAnalyzer\\Model\\Property', 'id');
$this->propertyIdRef->setAccessible(true);
$this->propertyStmt = $con->prepare(self::INSERT_PROPERTY_SQL);
$this->cPropertyStmt = $con->prepare(self::INSERT_CLASS_PROPERTY_SQL);
}
public function push(array $list)
{
$this->create();
$this->connection->beginTransaction();
$statement = $this->connection->prepare("\n INSERT INTO `{$this->table}`(`path`)\n VALUES (:path)\n ");
foreach ($list as $path) {
$statement->execute(compact('path'));
}
$this->connection->commit();
}
/**
* @param TodoWasMarkedAsDone $event
* @throws \RuntimeException if data of the the assigned user can not be found
*/
public function onTodoWasMarkedAsDone(TodoWasMarkedAsDone $event)
{
$user = $this->userFinder->findUserOfTodo($event->todoId()->toString());
if (!$user) {
throw new \RuntimeException(sprintf("Data of the assigned user of the todo %s cannot be found", $event->todoId()->toString()));
}
$stmt = $this->connection->prepare(sprintf('UPDATE %s SET open_todos = open_todos - 1, done_todos = done_todos + 1 WHERE id = :assignee_id', Table::USER));
$stmt->bindValue('assignee_id', $user->id);
$stmt->execute();
}
/**
* Writes the current session data to the database.
*
* @param FilterResponseEvent $event The event object
*/
public function onKernelResponse(FilterResponseEvent $event)
{
if (!$this->hasUser() || !$this->isContaoMasterRequest($event)) {
return;
}
$user = $this->getUserObject();
if (!$user instanceof User) {
return;
}
$this->connection->prepare('UPDATE ' . $user->getTable() . ' SET session=? WHERE id=?')->execute([serialize($this->getSessionBag()->all()), $user->id]);
}
/**
* @return array of message handlers data
*/
public function findAll()
{
$handlers = [];
$stmt = $this->connection->prepare('SELECT * FROM ' . Tables::MESSAGE_HANDLER);
$stmt->execute();
foreach ($stmt as $row) {
$this->fromDatabase($row);
array_push($handlers, $row);
}
return $handlers;
}
public function __construct(Connection $con)
{
$this->con = $con;
$this->platform = $con->getDatabasePlatform();
$this->phpType = Type::getType(PhpTypeType::NAME);
$this->constantIdRef = new \ReflectionProperty('Scrutinizer\\PhpAnalyzer\\Model\\Constant', 'id');
$this->constantIdRef->setAccessible(true);
$this->constantStmt = $con->prepare(self::INSERT_CONSTANT_SQL);
$this->cConstantStmt = $con->prepare(self::INSERT_CLASS_CONSTANT_SQL);
$this->iConstantStmt = $con->prepare(self::INSERT_INTERFACE_CONSTANT_SQL);
}
/**
* @return \Doctrine\DBAL\Driver\Statement
* @throws \Doctrine\DBAL\DBALException
*/
private function getRouteStatement()
{
$sql = '
SELECT subshopID as shopId, path, org_path as orgPath, main
FROM s_core_rewrite_urls
WHERE path LIKE :pathInfo
ORDER BY main DESC, subshopID = :shopId DESC
LIMIT 1
';
$stmt = $this->connection->prepare($sql);
return $stmt;
}
/**
* @param $membershipEntity Entity whose members we want to view
* @return array
*/
public function findMembers(MembershipEntityInterface $membershipEntity)
{
$stmt = $this->conn->prepare('SELECT i.`id`, i.`name`, i.`avatar`, i.`lastVisit`, p.`location`, m.`role` AS `membershipRole`, m.`note` AS `membershipNote` ' . 'FROM `' . CoreTables::USER_TBL . '` i ' . 'INNER JOIN `' . CoreTables::USER_PROFILE_TBL . '` p ON p.`userId` = i.`id` ' . 'INNER JOIN `' . $this->membershipTable() . '` m ON m.`userId` = i.`id` ' . 'WHERE m.`' . $this->entityColumn() . '` = :entityId AND i.`active` = 1 AND i.`removed` = 0 ' . 'ORDER BY i.`name`');
$stmt->bindValue(':entityId', $membershipEntity->getId());
$stmt->execute();
$results = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$results[] = ['id' => $row['id'], 'name' => $row['name'], 'avatar' => $row['avatar'], 'lastVisit' => $row['lastVisit'], 'location' => $row['location'], 'membership' => new Membership($membershipEntity, $this->roleResolver->getRole($this->entityName(), $row['membershipRole']), $row['membershipNote'])];
}
$stmt->closeCursor();
return $results;
}
public function findUserRequests()
{
$stmt = $this->conn->prepare('SELECT r.`id`, p.`name` AS `projectName`, r.`name`, r.`createdAt`, r.`lastUpdatedAt`, r.`status`, r.`commentNum` ' . 'FROM `' . CoreTables::AREA_REQUEST_TBL . '` r ' . 'INNER JOIN `' . CoreTables::PROJECT_TBL . '` p ON p.`id` = r.`projectId` ' . 'WHERE r.`requestorId` = :id AND p.`archived` = 0 ORDER BY p.`id` DESC, r.`status`');
$stmt->bindValue(':id', $this->tokenStorage->getToken()->getUser()->getId());
$stmt->execute();
$results = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$row['statusLabel'] = AreaRequest::statusLabel($row['status']);
$row['statusText'] = AreaRequest::statusText($row['status']);
$results[] = $row;
}
$stmt->closeCursor();
return $results;
}
/**
* @return array
* @throws \Doctrine\DBAL\DBALException
*/
private function fetchRows()
{
$query = 'SELECT sbas_id, ord, viewname, label_en, label_fr, label_de, label_nl FROM sbas';
$statement = $this->connection->prepare($query);
$statement->execute();
$rows = [];
while ($row = $statement->fetch(\PDO::FETCH_ASSOC)) {
$id = $row['sbas_id'];
unset($row['sbas_id']);
$rows[$id] = $row;
}
$statement->closeCursor();
return $rows;
}
public function saveSettings($projectId, array $settings)
{
$stmt = $this->conn->prepare('INSERT INTO `' . CoreTables::PROJECT_SETTINGS_TBL . '` (`projectId`, `key`, `name`, `module`, `value`, `type`, `extensionPoint`) ' . 'VALUES(:projectId, :key, :name, :module, :value, :type, :extensionPoint) ON DUPLICATE KEY UPDATE `value` = :newValue');
foreach ($settings as $setting) {
$stmt->bindValue(':projectId', $projectId);
$stmt->bindValue(':key', $setting->getKey());
$stmt->bindValue(':name', $setting->getName());
$stmt->bindValue(':module', $setting->getModule());
$stmt->bindValue(':value', $setting->getNormalizedValue());
$stmt->bindValue(':type', $setting->getType());
$stmt->bindValue(':extensionPoint', $setting->getExtensionPoint());
$stmt->bindValue(':newValue', $setting->getNormalizedValue());
$stmt->execute();
}
}
/**
* @throws \Doctrine\DBAL\DBALException
*/
public function synchronize()
{
$categories = $this->pluginService->getCategories();
$this->connection->exec("DELETE FROM s_core_plugin_categories");
$statement = $this->connection->prepare("INSERT INTO s_core_plugin_categories (id, locale, parent_id, name)\n VALUES (:id, :locale, :parent_id, :name)");
$pseudo = $this->getPseudoCategories();
foreach ($pseudo as $category) {
$statement->execute($category);
}
foreach ($categories as $category) {
foreach ($category->getName() as $locale => $name) {
$statement->execute([':id' => $category->getId(), ':name' => $name, ':locale' => $locale, ':parent_id' => $category->getParentId()]);
}
}
}
