/**
* Find ungenerated objects and return an IterableResult
*
* @return IterableResult
*/
public function findUngeneratedIterator()
{
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder->select('t')->from($this->getEntityClassName(), 't')->where('t.resource IS NULL AND t.staticResource IS NULL');
return $queryBuilder->getQuery()->iterate();
}
private function cleanupTerms(Treatment $treatment)
{
// find terms for this treatment
$qb = $this->om->createQueryBuilder();
$qb->select('t2')->from('TermBundle:Term', 't2')->innerJoin('t2.termDocuments', 'td', Join::WITH, 'td.type = :treatmentType')->setParameter('treatmentType', TermDocument::TYPE_TREATMENT)->where('td.documentId = :treatmentId')->setParameter('treatmentId', $treatment->getId());
$terms = $qb->getQuery()->getResult();
$this->output->writeln('Cleaning terms for treatment #' . $treatment->getId() . ' [' . $treatment->getName() . ']');
if (\count($terms)) {
$hasInternal = false;
foreach ($terms as $term) {
$this->output->write($this->indent() . $term->getName() . " [#{$term->getId()}]" . $this->indent());
if (!$term->getInternal()) {
// if this has not been flagged as internal yet, flag it
$term->setInternal(\strtolower($term->getName()) == \strtolower($treatment->getName()));
}
if (!$hasInternal) {
$hasInternal = $term->getInternal();
}
$this->om->persist($term);
$this->output->writeln('[OK]');
}
if (!$hasInternal) {
$term = $this->createTermFromTreatment($treatment);
$this->om->persist($term);
$this->output->writeln($this->indent() . 'Added internal term');
}
} else {
$this->output->write($this->indent() . "Found no terms: ");
$term = $this->createTermFromTreatment($treatment);
$this->om->persist($term);
$this->output->writeln('[OK]');
}
}
/**
* Find users by given query
*
* @param string $q
* @param int $limit
* @return array
*/
public function find($q, $limit = 25)
{
if (empty($q)) {
return array();
}
$query = $this->em->createQueryBuilder()->select('u')->from('Newscoop\\Entity\\User', 'u')->where('u.email LIKE ?0')->orWhere('u.username LIKE ?0')->orderBy('u.id', 'asc')->setMaxResults($limit)->getQuery();
$query->setParameter(0, "%{$q}%");
return $query->getResult();
}
/**
* Transform single id value to an entity
*
* @param string $value
* @return mixed|null|object
*/
public function reverseTransform($value)
{
if (empty($value)) {
return null;
}
try {
$entity = $this->em->createQueryBuilder()->select('entity')->from($this->className, 'entity')->where('entity.' . $this->primaryKey . ' = :id')->setParameter('id', $value)->getQuery()->getSingleResult();
} catch (\Exception $ex) {
// this will happen if the form submits invalid data
throw new TransformationFailedException(sprintf('The choice "%s" does not exist or is not unique', $value));
}
if (!$entity) {
return null;
}
return $entity;
}
/**
* {@inheritdoc}
*/
public function load(ObjectManager $manager)
{
// get already present
$qb = $manager->createQueryBuilder();
$qb->from(SecurityType::class, 's', 's.id');
$qb->select('s');
$present = $qb->getQuery()->getResult();
// load xml
$file = $this->container->getParameter('sulu_security.security_types.fixture');
$doc = new \DOMDocument();
$doc->load($file);
$xpath = new \DOMXpath($doc);
$elements = $xpath->query('/security-types/security-type');
if (!is_null($elements)) {
/** @var $element \DOMNode */
foreach ($elements as $element) {
/** @var $child \DOMNode */
foreach ($element->childNodes as $child) {
if (isset($child->nodeName)) {
if ($child->nodeName == 'id') {
$typeId = $child->nodeValue;
}
if ($child->nodeName == 'name') {
$typeName = $child->nodeValue;
}
}
}
$securityType = array_key_exists($typeId, $present) ? $present[$typeId] : new SecurityType();
$securityType->setId($typeId);
$securityType->setName($typeName);
$manager->persist($securityType);
}
}
$manager->flush();
}
/**
*
* @param array $workspaces
* @return QueryBuilder
*/
protected function createQueryBuilder(array $workspaces)
{
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder->select('n')->from(NodeData::class, 'n')->where('n.workspace IN (:workspaces)')->setParameter('workspaces', $workspaces);
return $queryBuilder;
}
/**
*
* @param array $workspaces
* @return QueryBuilder
*/
protected function createQueryBuilder(array $workspaces)
{
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder->select('n')->from('TYPO3\\TYPO3CR\\Domain\\Model\\NodeData', 'n')->where('n.workspace IN (:workspaces)')->setParameter('workspaces', $workspaces);
return $queryBuilder;
}
/**
* Find all NodeData objects inside a given workspace sorted by path to be used
* in publishing. The order makes sure that parent nodes are published first.
*
* @param Workspace $workspace
* @return array<NodeData>
*/
public function findByWorkspace(Workspace $workspace)
{
/** @var \Doctrine\ORM\QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder->select('n')->distinct()->from('TYPO3\\TYPO3CR\\Domain\\Model\\NodeData', 'n')->where('n.workspace = :workspace')->orderBy('n.path', 'ASC')->setParameter('workspace', $workspace);
return $queryBuilder->getQuery()->getResult();
}
/**
* {@inheritdoc}
*/
public function load(ObjectManager $manager)
{
// get already stored countries
$qb = $manager->createQueryBuilder();
$qb->from(Country::class, 'c', 'c.code');
$qb->select('c');
$existingCountries = $qb->getQuery()->getResult();
// load xml
$file = dirname(__FILE__) . '/../countries.xml';
$doc = new \DOMDocument();
$doc->load($file);
$xpath = new \DOMXpath($doc);
$elements = $xpath->query('/Countries/Country');
if (!is_null($elements)) {
/** @var $element DOMNode */
foreach ($elements as $element) {
/** @var $child DOMNode */
foreach ($element->childNodes as $child) {
if (isset($child->nodeName)) {
if ($child->nodeName == 'Name') {
$countryName = $child->nodeValue;
}
if ($child->nodeName == 'Code') {
$countryCode = $child->nodeValue;
}
}
}
$country = array_key_exists($countryCode, $existingCountries) ? $existingCountries[$countryCode] : new Country();
$country->setName($countryName);
$country->setCode($countryCode);
$manager->persist($country);
}
}
$manager->flush();
}
/**
* Transform array to a collection of entities
*
* @param array $values
* @return array
*/
public function reverseTransform($values)
{
if (!is_array($values) || empty($values)) {
return array();
}
// add new tag entries
$newObjects = array();
$tagPrefixLength = strlen($this->newTagPrefix);
foreach ($values as $key => $value) {
$cleanValue = substr($value, $tagPrefixLength);
$valuePrefix = substr($value, 0, $tagPrefixLength);
if ($valuePrefix == $this->newTagPrefix) {
$object = new $this->className();
$this->accessor->setValue($object, $this->textProperty, $cleanValue);
$newObjects[] = $object;
unset($values[$key]);
}
}
try {
// get multiple entities with one query
$entities = $this->em->createQueryBuilder()->select('entity')->from($this->className, 'entity')->where('entity.' . $this->primaryKey . ' IN (:ids)')->setParameter('ids', $values)->getQuery()->getResult();
} catch (\Exception $ex) {
// this will happen if the form submits invalid data
throw new TransformationFailedException('One or more id values are invalid');
}
return array_merge($entities, $newObjects);
}
/**
* Transforms a string (id) to an object (city).
*/
public function reverseTransform($string)
{
if (empty($string)) {
return null;
}
$titles = explode(', ', $string);
$cityTitle = $titles[0];
$countryTitle = count($titles) > 1 ? $titles[1] : null;
$builder = $this->om->createQueryBuilder();
$builder->select('city')->from('LearningMainBundle:City', 'city')->leftJoin('LearningMainBundle:Country', 'country', 'WITH', 'country = city.country')->where('city.title = :cityTitle')->orderBy('country.id', 'ASC')->setParameter('cityTitle', $cityTitle)->setMaxResults(1);
if ($countryTitle) {
$builder->andWhere('country.title LIKE :countryTitle')->setParameter('countryTitle', '%' . $countryTitle . '%');
}
$city = $builder->getQuery()->getOneOrNullResult();
if (empty($city)) {
throw new TransformationFailedException(sprintf('Город "%s" не найден!', $string));
}
return $city;
}
/**
* Finds all objects and return an IterableResult
*
* @param string $host Full qualified host name
* @param callable $callback
* @return \Generator<Redirect>
*/
public function findAll($host = null, callable $callback = null)
{
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
$query = $queryBuilder->select('r')->from($this->getEntityClassName(), 'r');
if ($host !== null) {
$query->andWhere('r.host = :host')->setParameter('host', $host);
} else {
$query->andWhere('r.host IS NULL');
}
$query->orderBy('r.host', 'ASC');
$query->addOrderBy('r.sourceUriPath', 'ASC');
return $this->iterate($query->getQuery()->iterate(), $callback);
}
/**
* Repair votes action
*
* Compare number of votes between nodes and vote log and repair, if not dryRun
*
* @param boolean $dryRun Don't do anything, but report actions
* @return string
*/
public function repairCommand($dryRun = TRUE)
{
if ($dryRun) {
echo "Dry run, not making any changes\n";
}
$q = new FlowQuery(array($this->context->getRootNode()));
$answerNodes = $q->find('[instanceof Sfi.Encult:Answer]')->get();
foreach ($answerNodes as $answerNode) {
/** @var \Doctrine\ORM\QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
$nodes = $queryBuilder->select('v')->from('Sfi\\Encult\\Domain\\Model\\Vote', 'v')->andWhere('v.answerIdentifier = :answerIdentifier')->setParameters(array('answerIdentifier' => $answerNode->getIdentifier()))->getQuery()->getArrayResult();
$dbCount = count($nodes);
$crCount = $answerNode->getProperty('voteCount');
$path = $answerNode->getPath();
if ($dbCount !== $crCount) {
echo "Found mistake for {$path} (db: {$dbCount} vs. cr: {$crCount})\n";
if (!$dryRun) {
echo "Fixed\n";
$answerNode->setProperty('voteCount', $dbCount);
}
}
}
return "Done!\n";
}
/**
* Transforms a string (id) to an object (city).
*/
public function reverseTransform($string)
{
if (empty($string)) {
return null;
}
$titles = explode(',', $string);
$city = trim($titles[0]);
$region = null;
$country = null;
if (isset($titles[2])) {
$region = trim($titles[1]);
$country = trim($titles[2]);
}
$builder = $this->om->createQueryBuilder();
$builder->select('city')->from('VidalMainBundle:City', 'city')->leftJoin('VidalMainBundle:Country', 'country', 'WITH', 'country = city.country')->where('city.title = :city')->orderBy('country.id', 'ASC')->setParameter('city', $city)->setMaxResults(1);
if ($country) {
$builder->leftJoin('city.country', 'c')->leftJoin('city.region', 'r')->andWhere('c.title LIKE :country')->andWhere('r.title LIKE :region')->setParameter('country', $country)->setParameter('region', $region);
}
$city = $builder->getQuery()->getOneOrNullResult();
if (empty($city)) {
throw new TransformationFailedException(sprintf('Город "%s" не найден!', $string));
}
return $city;
}
/**
* Saves the given array as a node data entity without using the ORM.
*
* If the node data already exists (same dimensions, same identifier, same workspace)
* it is replaced.
*
* @param array $nodeData node data to save as an associative array ( $column_name => $value )
* @throws ImportException
* @return void
*/
protected function persistNodeData($nodeData)
{
if ($nodeData['workspace'] !== 'live') {
throw new ImportException('Saving NodeData with workspace != "live" using direct SQL not supported yet. Workspace is "' . $nodeData['workspace'] . '".');
}
if ($nodeData['path'] === '/') {
return;
}
// cleanup old data
/** @var \Doctrine\DBAL\Connection $connection */
$connection = $this->entityManager->getConnection();
// prepare node dimensions
$dimensionValues = $nodeData['dimensionValues'];
$dimensionsHash = Utility::sortDimensionValueArrayAndReturnDimensionsHash($dimensionValues);
$jsonPropertiesDataTypeHandler = JsonArrayType::getType(JsonArrayType::FLOW_JSON_ARRAY);
// post-process node data
$nodeData['dimensionsHash'] = $dimensionsHash;
$nodeData['dimensionValues'] = $jsonPropertiesDataTypeHandler->convertToDatabaseValue($dimensionValues, $connection->getDatabasePlatform());
$nodeData['properties'] = $jsonPropertiesDataTypeHandler->convertToDatabaseValue($nodeData['properties'], $connection->getDatabasePlatform());
$nodeData['accessRoles'] = $jsonPropertiesDataTypeHandler->convertToDatabaseValue($nodeData['accessRoles'], $connection->getDatabasePlatform());
$connection->executeQuery('DELETE FROM typo3_typo3cr_domain_model_nodedimension' . ' WHERE nodedata IN (' . ' SELECT persistence_object_identifier FROM typo3_typo3cr_domain_model_nodedata' . ' WHERE identifier = :identifier' . ' AND workspace = :workspace' . ' AND dimensionshash = :dimensionsHash' . ' )', array('identifier' => $nodeData['identifier'], 'workspace' => $nodeData['workspace'], 'dimensionsHash' => $nodeData['dimensionsHash']));
/** @var \Doctrine\ORM\QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder->delete()->from('TYPO3\\TYPO3CR\\Domain\\Model\\NodeData', 'n')->where('n.identifier = :identifier')->andWhere('n.dimensionsHash = :dimensionsHash')->andWhere('n.workspace = :workspace')->setParameter('identifier', $nodeData['identifier'])->setParameter('workspace', $nodeData['workspace'])->setParameter('dimensionsHash', $nodeData['dimensionsHash']);
$queryBuilder->getQuery()->execute();
// insert new data
// we need to use executeUpdate to execute the INSERT -- else the data types are not taken into account.
// That's why we build a DQL INSERT statement which is then executed.
$queryParts = array();
$queryArguments = array();
$queryTypes = array();
foreach ($this->nodeDataPropertyNames as $propertyName => $propertyConfig) {
if (isset($nodeData[$propertyName])) {
$queryParts[$propertyName] = ':' . $propertyName;
$queryArguments[$propertyName] = $nodeData[$propertyName];
if (isset($propertyConfig['columnType'])) {
$queryTypes[$propertyName] = $propertyConfig['columnType'];
}
}
}
$connection->executeUpdate('INSERT INTO typo3_typo3cr_domain_model_nodedata (' . implode(', ', array_keys($queryParts)) . ') VALUES (' . implode(', ', $queryParts) . ')', $queryArguments, $queryTypes);
foreach ($dimensionValues as $dimension => $values) {
foreach ($values as $value) {
$nodeDimension = array('persistence_object_identifier' => Algorithms::generateUUID(), 'nodedata' => $nodeData['Persistence_Object_Identifier'], 'name' => $dimension, 'value' => $value);
$connection->insert('typo3_typo3cr_domain_model_nodedimension', $nodeDimension);
}
}
}
/**
* Find all nodes of the specified workspace lying below the path specified by
* (and including) the given starting point.
*
* @param string $pathStartingPoint Absolute path specifying the starting point
* @param string $workspace The containing workspace
* @param string $nodeTypeFilter
* @return array an array of node-data in array format.
*/
protected function findNodeDataListToExport($pathStartingPoint, $workspace = 'live', $nodeTypeFilter = null)
{
/** @var \Doctrine\ORM\QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder->select('n.path AS path,' . ' n.identifier AS identifier,' . ' n.index AS sortingIndex,' . ' n.properties AS properties, ' . ' n.nodeType AS nodeType,' . ' n.removed AS removed,' . ' n.hidden,' . ' n.hiddenBeforeDateTime AS hiddenBeforeDateTime,' . ' n.hiddenAfterDateTime AS hiddenAfterDateTime,' . ' n.creationDateTime AS creationDateTime,' . ' n.lastModificationDateTime AS lastModificationDateTime,' . ' n.lastPublicationDateTime AS lastPublicationDateTime,' . ' n.hiddenInIndex AS hiddenInIndex,' . ' n.accessRoles AS accessRoles,' . ' n.version AS version,' . ' n.parentPath AS parentPath,' . ' n.pathHash AS pathHash,' . ' n.dimensionsHash AS dimensionsHash,' . ' n.parentPathHash AS parentPathHash,' . ' n.dimensionValues AS dimensionValues,' . ' w.name AS workspace')->distinct()->from('TYPO3\\TYPO3CR\\Domain\\Model\\NodeData', 'n')->innerJoin('n.workspace', 'w', 'WITH', 'n.workspace=w.name')->where('n.workspace = :workspace')->setParameter('workspace', $workspace)->andWhere('n.path = :pathPrefix OR n.path LIKE :pathPrefixMatch')->setParameter('pathPrefix', $pathStartingPoint)->setParameter('pathPrefixMatch', $pathStartingPoint === '/' ? '%' : $pathStartingPoint . '/%')->orderBy('n.identifier', 'ASC')->orderBy('n.path', 'ASC');
if ($nodeTypeFilter) {
$this->nodeDataRepository->addNodeTypeFilterConstraintsToQueryBuilder($queryBuilder, $nodeTypeFilter);
}
$nodeDataList = $queryBuilder->getQuery()->getResult();
// Sort nodeDataList by path, replacing "/" with "!" (the first visible ASCII character)
// because there may be characters like "-" in the node path
// that would break the sorting order
usort($nodeDataList, function ($node1, $node2) {
return strcmp(str_replace("/", "!", $node1['path']), str_replace("/", "!", $node2['path']));
});
return $nodeDataList;
}
/**
* Collects all nodes with missing shadow nodes
*
* @param Workspace $workspace
* @param boolean $dryRun
* @param NodeType $nodeType
* @return array
*/
protected function fixShadowNodesInWorkspace(Workspace $workspace, $dryRun, NodeType $nodeType = null)
{
$workspaces = array_merge([$workspace], $workspace->getBaseWorkspaces());
$fixedShadowNodes = 0;
foreach ($workspaces as $workspace) {
/** @var Workspace $workspace */
if ($workspace->getBaseWorkspace() === null) {
continue;
}
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder->select('n')->from(NodeData::class, 'n')->where('n.workspace = :workspace');
$queryBuilder->setParameter('workspace', $workspace->getName());
if ($nodeType !== null) {
$queryBuilder->andWhere('n.nodeType = :nodeType');
$queryBuilder->setParameter('nodeType', $nodeType->getName());
}
/** @var NodeData $nodeData */
foreach ($queryBuilder->getQuery()->getResult() as $nodeData) {
$nodeDataSeenFromParentWorkspace = $this->nodeDataRepository->findOneByIdentifier($nodeData->getIdentifier(), $workspace->getBaseWorkspace(), $nodeData->getDimensionValues());
// This is the good case, either the node does not exist or was shadowed
if ($nodeDataSeenFromParentWorkspace === null) {
continue;
}
// Also good, the node was not moved at all.
if ($nodeDataSeenFromParentWorkspace->getPath() === $nodeData->getPath()) {
continue;
}
$nodeDataOnSamePath = $this->nodeDataRepository->findOneByPath($nodeData->getPath(), $workspace->getBaseWorkspace(), $nodeData->getDimensionValues(), null);
// We cannot just put a shadow node in the path, something exists, but that should be fine.
if ($nodeDataOnSamePath !== null) {
continue;
}
if (!$dryRun) {
$nodeData->createShadow($nodeDataSeenFromParentWorkspace->getPath());
}
$fixedShadowNodes++;
}
}
return $fixedShadowNodes;
}
/**
* Removes a node and it's children in the given workspace.
*
* @param string $nodePath
* @param string $workspaceName
*/
protected function removeNodeAndChildNodesInWorkspaceByPath($nodePath, $workspaceName)
{
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder->resetDQLParts()->delete('TYPO3\\TYPO3CR\\Domain\\Model\\NodeData', 'n')->where('n.path LIKE :path')->orWhere('n.path LIKE :subpath')->andWhere('n.workspace = :workspace')->setParameters(array('path' => $nodePath, 'subpath' => $nodePath . '/%', 'workspace' => $workspaceName))->getQuery()->execute();
}
/**
* @param \Doctrine\Common\Persistence\ObjectManager $entityManager
* @return void
*/
public function injectEntityManager(\Doctrine\Common\Persistence\ObjectManager $entityManager)
{
$this->entityManager = $entityManager;
$this->queryBuilder = $entityManager->createQueryBuilder()->select('e')->from($this->entityClassName, 'e');
}
/**
* Interface implemented function
*
* @param \_OurBrand_\My\Domain\Model\User $user
* @param string $sortColumn
* @param string $sortOrder
* @param array $columns
* @param array $filters
* @param int $limit
* @param int $start
* @param string $searchQuery
*
* @return \TYPO3\Flow\Persistence\QueryResultInterface
*/
public function findAllAvailableByArguments($user, $sortColumn = 'title', $sortOrder = 'ASC', $columns = array(), $filters = array(), $limit = 10, $start = 0, $searchQuery = '', $type = 0)
{
$conn = $this->entityManager->getConnection();
//$query = $conn->createQueryBuilder();
$query = $this->entityManager->createQueryBuilder();
if ($user->isWorker()) {
$expression = $query->expr()->eq('q.Quiz', 1);
} else {
$expression = $query->expr()->andX($query->expr()->eq('q.Quiz', 1), $query->expr()->eq('q.isDraft', 0));
}
$query->select('q.persistence_object_identifier')->from('_OurBrand__quiz_domain_model_quiz', 'q')->where($query->expr()->andX($query->expr()->eq('q.type', intval($type)), $query->expr()->eq('q.isDeleted', 0), $query->expr()->isNull('q.snapshotOf'), $query->expr()->orX($query->expr()->like('q.creator', $query->expr()->literal($user->getIdentifier())), $expression)))->orderBy('q.title', $sortOrder == 'ASC' ? 'ASC' : 'DESC')->groupBy('q.persistence_object_identifier')->setFirstResult($start)->setMaxResults($limit);
if (strlen(trim($searchQuery))) {
$query->andWhere($query->expr()->like('q.title', $query->expr()->literal('%' . addslashes($searchQuery) . '%')));
}
if (!empty($filters)) {
foreach ($filters as $type => $filter) {
if (count($filter)) {
switch ($type) {
case "subjects":
$contains = array();
foreach ($filter as $data) {
$contains[] = $data['id'];
}
$query->leftJoin('_OurBrand__quiz_domain_model_quiz_subjects_join', 'quiz_subjects', 'ON', 'quiz_subjects.quiz_quiz = q.persistence_object_identifier');
$query->andWhere($query->expr()->in('quiz_subjects.quiz_subject', $contains));
break;
case "teamLevels":
$contains = array();
foreach ($filter as $data) {
$contains[] = $data['id'];
}
$query->leftJoin('_OurBrand__quiz_domain_model_quiz_levels_join', 'quiz_levels', 'ON', 'quiz_levels.quiz_quiz = q.persistence_object_identifier');
$query->andWhere($query->expr()->in('quiz_levels.quiz_teamlevel', $contains));
break;
case "examTypes":
$contains = array();
foreach ($filter as $data) {
$contains[] = $data['id'];
}
$query->andWhere($query->expr()->in('q.examtype', $contains));
break;
case "categories":
$contains = array();
foreach ($filter as $data) {
$contains[] = $data['id'];
}
$query->andWhere($query->expr()->in('q.quizcategory', $contains));
break;
}
}
}
}
if ($sortColumn == 'exercises') {
$query->addSelect('COUNT( e.persistence_object_identifier ) AS num');
$query->leftJoin('_OurBrand__quiz_domain_model_exercise', 'e', 'ON', 'e.quiz = q.persistence_object_identifier');
$query->orderBy('num', $sortOrder == 'ASC' ? 'ASC' : 'DESC');
$query->addOrderBy('q.title', 'ASC');
}
//echo $query->getDql();
// the following does not work (or unreliable at best) with dql and joins
// setFirstResult( $start )
// setMaxResults( $limit );
return $conn->fetchAll($query->getDql() . ' LIMIT ' . intval($start) . ' , ' . intval($limit));
}
public function load(ObjectManager $manager)
{
$stopwatch = new Stopwatch();
$stopwatch->start('dummyRecordGeneration');
// Populate dummy forms
$this->addDummyFemaleNames();
$this->addDummyMaleNmes();
$this->addDummyCourseNames();
$this->addDummyCourseLocations();
$this->addDummySponsors();
$loadUserData = new LoadUserData();
$loadUserData->addDummyUsers();
$dummyUsers = $loadUserData->getUsers();
$loadFieldData = new LoadFieldData();
$loadFieldData->addDummyFields();
$dummyFields = $loadFieldData->getFields();
$loadFormData = new LoadFormData();
$loadFormData->addDummyForms();
$dummyForms = $loadFormData->getForms();
$organiastionunits = $manager->getRepository('HrisOrganisationunitBundle:Organisationunit')->findAll();
/*
* Add data to facilities
*/
if (!empty($organiastionunits)) {
foreach ($organiastionunits as $organiastionunitKey => $organisationunit) {
/*
* Assign data to dispensary, hospital and health centres only.
*/
if (preg_match('/dispensary|hospital|health centre|council/i', $organisationunit->getLongname())) {
// Initiate record entering
// Enter two records for each orgunit
for ($recordIncr = 0; $recordIncr < $this->recordsPerOrganisationunit; $recordIncr++) {
$record = new Record();
$record->setOrganisationunit($organisationunit);
// Enter record for public and private form
$formNames = array('Public Employee Form', 'Private Employee Form');
$formName = $formNames[array_rand($formNames, 1)];
if (empty($formName)) {
$formName = 'Public Employee Form';
}
$form = $manager->getRepository('HrisFormBundle:Form')->findOneBy(array('name' => $formName));
// Find history fields belonging to this form for population of data
$queryBuilder = $manager->createQueryBuilder();
$historyFields = $queryBuilder->select('field')->from('HrisFormBundle:Field', 'field')->join('field.formFieldMember', 'formFieldMember')->join('formFieldMember.form', 'form')->where('form.id=:formId')->andWhere('field.hashistory=True')->setParameter('formId', $form->getId())->getQuery()->getResult();
$record->setForm($form);
$record->setComplete(True);
$record->setCorrect(True);
$record->setHashistory(False);
$record->setHastraining(False);
$dummyUserKey = array_rand($dummyUsers, 1);
$dummyUsername = $dummyUsers[$dummyUserKey]['username'];
$record->setUsername($dummyUsername);
// Constructing a Value Array
// @todo removing hard-coding of HrisRecordBundle:Record values
$value = array();
// Fetch all field members belonging to the form and add records
// Roll a dice with gender of employee to pick name
$genders = array('Male', 'Female');
$gender_picked = array_rand($genders, 1);
$formFieldMembers = $manager->getRepository('HrisFormBundle:FormFieldMember')->findBy(array('form' => $form));
foreach ($formFieldMembers as $formFieldMemberKey => $formFieldMember) {
/**
* Made dynamic, on which field column is used as key, i.e. uid, name or id.
*/
// Translates to $formFieldMember->getField()->getUid()
// or $formFieldMember->getField()->getUid() depending on value of $recordKeyName
$recordKeyName = ucfirst(Record::getFieldKey());
$valueKey = call_user_func_array(array($formFieldMember->getField(), "get{$recordKeyName}"), array());
if ($formFieldMember->getField()->getName() == "Firstname" || $formFieldMember->getField()->getName() == "Middlename" || $formFieldMember->getField()->getName() == "Surname" || $formFieldMember->getField()->getName() == "NextofKin") {
// Deal with names
if ($gender_picked == "Female" && ($formFieldMember->getField()->getName() == "Firstname" || $formFieldMember->getField()->getName() == "NextofKin")) {
$value[$valueKey] = $this->femaleNames[array_rand($this->femaleNames, 1)];
} else {
$value[$valueKey] = $this->maleNames[array_rand($this->maleNames, 1)];
}
if ($formFieldMember->getField()->getName() == "NextofKin") {
$value[$valueKey] .= ' ' . $this->maleNames[array_rand($this->maleNames, 1)];
}
//@todo remove hard-coding of instance
// used later for instance formulation
if ($formFieldMember->getField()->getName() == "Firstname") {
$firstName = $value[$valueKey];
}
if ($formFieldMember->getField()->getName() == "Middlename") {
$middleName = $value[$valueKey];
}
if ($formFieldMember->getField()->getName() == "Surname") {
$surname = $value[$valueKey];
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "Select") {
// Deal with select
/**
* Made dynamic, on which field column is used as key, i.e. uid, name or id.
*/
// Translates to $fieldOptions[0]->getUid()
// or $fieldOptions[0]->getValue() depending on value of $recordKeyName
// $fieldOptionKey = ucfirst($record->getFieldOptionKey());
//$valueKey = call_user_func_array(array($fieldOptions[0], "get${fieldOptionKey}"),array());
$fieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptions = $manager->getRepository('HrisFormBundle:FieldOption')->findBy(array('field' => $formFieldMember->getField()));
// For case of gender choose match name with gender
if ($formFieldMember->getField()->getName() == "Sex") {
// Made FieldOption key to store in record value array dynamic.
if ($fieldOptions[0]->getValue() == $gender_picked) {
$value[$valueKey] = call_user_func_array(array($fieldOptions[0], "get{$fieldOptionKey}"), array());
} else {
$value[$valueKey] = call_user_func_array(array($fieldOptions[1], "get{$fieldOptionKey}"), array());
}
} else {
// Made fieldOption key to store in record value array dynamic
$value[$valueKey] = call_user_func_array(array($fieldOptions[array_rand($fieldOptions, 1)], "get{$fieldOptionKey}"), array());
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "Date") {
// Deal with dates
// If birth date pick 20 - 55 date range
// If employment data set it to birth date range+18
// If confirmation date, set it to employment date+1
// If promotion date, set it to confirmation+3
$beginDateStart = 50;
$beginDateStop = 75;
$endDateStart = 40;
$endDateStop = 50;
if ($formFieldMember->getField()->getName() == "DateOfBirth") {
$beginDateStart = 50;
$beginDateStop = 75;
$endDateStart = 40;
$endDateStop = 50;
} elseif ($formFieldMember->getField()->getName() == "DateofFirstAppointment") {
$beginDateStart -= 36;
$beginDateStop -= 36;
$endDateStart -= 36;
$endDateStop -= 36;
} elseif ($formFieldMember->getField()->getName() == "DateofConfirmation") {
$beginDateStart -= 37;
$beginDateStop -= 37;
$endDateStart -= 37;
$endDateStop -= 37;
} elseif ($formFieldMember->getField()->getName() == "DateofLastPromotion") {
$beginDateStart -= 40;
$beginDateStop -= 40;
$endDateStart -= 40;
$endDateStop -= 40;
}
$value[$valueKey] = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
//@todo remove hard-coding of instance
if ($formFieldMember->getField()->getName() == "DateOfBirth") {
$dateOfBirth = $value[$valueKey];
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "Text") {
// Deal with numbers
if ($formFieldMember->getField()->getName() == "NumberofChildrenDependants") {
$value[$valueKey] = rand(0, 10);
} elseif ($formFieldMember->getField()->getName() == "CheckNumber") {
$value[$valueKey] = rand(9999999, 9999999999);
} elseif ($formFieldMember->getField()->getName() == "EmployersFileNumber") {
$value[$valueKey] = "FN/" . rand(100, 100000);
} elseif ($formFieldMember->getField()->getName() == "RegistrationNumber") {
$value[$valueKey] = "RB/" . rand(10, 10000);
} elseif ($formFieldMember->getField()->getName() == "MonthlyBasicSalary") {
$value[$valueKey] = rand(100, 1500) . '000';
} else {
$value[$valueKey] = $this->maleNames[array_rand($this->maleNames, 1)] . " Street";
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "TextArea") {
// Deal with domicile, contact
if ($formFieldMember->getField()->getName() == "ContactsofEmployee" || $formFieldMember->getField()->getName() == "ContactsofNextofKin") {
$value[$valueKey] = "+255" . rand(6, 7) . rand(53, 69) . rand(01, 998) . rand(01, 998);
}
}
}
}
}
}
}
$instance = md5($firstName . $middleName . $surname . $dateOfBirth->format('Y-m-d'));
$record->setInstance($instance);
$record->setValue($value);
//@todo check for uniqueness of instance and unique fields
$recordReference = strtolower(str_replace(' ', '', $record->getInstance())) . '-record';
$this->addReference($recordReference, $record);
$manager->persist($record);
// Randomly on flip of a coin assign history & training data
$outcomes = array(True, False);
if ($outcomes[array_rand($outcomes, 1)]) {
// Assign randomly between 2 to 4 histories per record
$numberofHistoriesToAssign = array(1, 2);
for ($incr = 0; $incr < $numberofHistoriesToAssign[array_rand($numberofHistoriesToAssign, 1)]; $incr++) {
$history = new History();
$history->setRecord($record);
$history->setUsername($record->getUsername());
//Calculate start date ranging starting form now-2yrs back and and stopping between 3-5 years back
$beginDateStart = 3;
$beginDateStop = 5;
$endDateStart = 0;
$endDateStop = 2;
$startDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
$history->setStartdate($startDate);
$historyField = $historyFields[array_rand($historyFields, 1)];
//echo get_class($historyField);exit;
// If history field is Combo assign combo if text assign text
if ($historyField->getInputType() == "Select") {
$historyFieldOptions = $historyField->getFieldOption();
$historyFieldOptions = $historyFieldOptions->getValues();
$selectedHistoryOption = $historyFieldOptions[array_rand($historyFieldOptions, 1)];
$historyValue = $selectedHistoryOption->getValue();
} elseif ($historyField->getInputType() == "Date") {
//Calculate start date ranging starting form 1-3yrs back and and stopping between 5-8 years back
$beginDateStart = 5;
$beginDateStop = 8;
$endDateStart = 1;
$endDateStop = 3;
$historyDateObject = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
$historyValue = $historyDateObject->format('Y-m-d');
} else {
// Deal with string history fields
if ($historyField->getName() == "Firstname" || $historyField->getName() == "Middlename" || $historyField->getName() == "Surname" || $historyField->getName() == "NextofKin") {
// Deal with names
if ($gender_picked == "Female" && ($historyField->getName() == "Firstname" || $historyField->getName() == "NextofKin")) {
$historyValue = $this->femaleNames[array_rand($this->femaleNames, 1)];
} else {
$historyValue = $this->maleNames[array_rand($this->maleNames, 1)];
}
if ($historyField->getName() == "NextofKin") {
$historyValue .= ' ' . $this->maleNames[array_rand($this->maleNames, 1)];
}
} else {
if ($historyField->getInputType()->getName() == "Text") {
// Deal with numbers
if ($historyField->getName() == "NumberofChildrenDependants") {
$historyValue = rand(0, 10);
} elseif ($historyField->getName() == "CheckNumber") {
$historyValue = rand(9999999, 9999999999);
} elseif ($historyField->getName() == "EmployersFileNumber") {
$historyValue = "FN/" . rand(100, 100000);
} elseif ($historyField->getName() == "RegistrationNumber") {
$historyValue = "RB/" . rand(10, 10000);
} elseif ($historyField->getName() == "MonthlyBasicSalary") {
$historyValue = rand(100, 1500) . '000';
} else {
$historyValue = $this->maleNames[array_rand($this->maleNames, 1)] . " Street";
}
} else {
if ($historyField->getInputType()->getName() == "TextArea") {
// Deal with domicile, contact
if ($historyField->getName() == "ContactsofEmployee" || $historyField->getName() == "ContactsofNextofKin") {
$historyValue = "+255" . rand(6, 7) . rand(53, 69) . rand(01, 998) . rand(01, 998);
}
}
}
}
}
$reason = $historyField->getCaption() . " changed.";
$history->setField($historyField);
$history->setHistory($historyValue);
$history->setReason($reason);
$manager->persist($history);
unset($history);
}
$record->setHashistory(True);
$manager->persist($record);
}
if ($outcomes[array_rand($outcomes, 1)]) {
// Assign randomly between 2 to 4 trainings per record
$numberofTrainingsToAssign = array(1, 2);
for ($incr = 0; $incr < $numberofTrainingsToAssign[array_rand($numberofTrainingsToAssign, 1)]; $incr++) {
$training = new Training();
$training->setRecord($record);
$training->setCoursename($this->courseNames[array_rand($this->courseNames, 1)]);
$training->setCourselocation($this->courseLocations[array_rand($this->courseLocations, 1)]);
$training->setSponsor($this->sponsor[array_rand($this->sponsor, 1)]);
//Calculate start date ranging starting form 9-10yrs back and and stopping between 10-12 years back
$beginDateStart = 10;
$beginDateStop = 12;
$endDateStart = 9;
$endDateStop = 10;
$startDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
//Calculate end date ranging starting form 11-13yrs back and and stopping between 13-15 years back
$beginDateStart = 13;
$beginDateStop = 15;
$endDateStart = 11;
$endDateStop = 13;
$endDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
$training->setStartdate($startDate);
$training->setEnddate($endDate);
$training->setUsername($record->getUsername());
$manager->persist($training);
unset($training);
}
$record->setHastraining(True);
$manager->persist($record);
}
unset($record);
}
}
}
}
$manager->flush();
/*
* Check Clock for time spent
*/
$dummyRecordGenerationTime = $stopwatch->stop('dummyRecordGeneration');
$duration = $dummyRecordGenerationTime->getDuration() / 1000;
unset($stopwatch);
if ($duration < 60) {
$durationMessage = round($duration, 2) . ' seconds';
} elseif ($duration >= 60 && $duration < 3600) {
$durationMessage = round($duration / 60, 2) . ' minutes';
} elseif ($duration >= 3600 && $duration < 216000) {
$durationMessage = round($duration / 3600, 2) . ' hours';
} else {
$durationMessage = round($duration / 86400, 2) . ' hours';
}
echo "\tDummy Records generation complete in " . $durationMessage . ".\n\n";
}
/**
* Get paginated results.
*
* @param int $page Current page
* @param int $limit Items per page limit
* @param array $sortby Sorting options
*
* @return PaginationInterface Returns a filtered paginator
*/
public function getPaginatedResults($page = 1, $limit = 20, array $sortby = array())
{
$qb = $this->objectManager->createQueryBuilder(self::ENTITY_ALIAS)->select(self::ENTITY_ALIAS)->from(self::ENTITY_CLASS, self::ENTITY_ALIAS)->orderBy(self::ENTITY_ALIAS . '.dateAt', 'DESC');
return $this->getPaginator()->paginate($qb, $page, $limit, $sortby);
}
/**
* @param ObjectManager $objectManager objectManager
* @param object $metadata metadata
* @param string $model model
* @param array $components components
* @param array $oids oids
* @param array $fields fields
*
* @return void
*/
public function locateComposite(ObjectManager $objectManager, $metadata, $model, array $components, array $oids, array $fields)
{
$alias = 'r';
$dqlFields = array_map(function ($alias, $v) {
return sprintf('%s.%s', $alias, $v);
}, $fields);
$concat = implode(",'#',", $dqlFields);
$oids = array_map(function ($v) {
return implode('#', $v);
}, $oids);
$qb = $objectManager->createQueryBuilder($alias);
$this->onPreLocateComposite($qb, $alias, $model, $oids);
$results = $qb->select($alias)->from($model, $alias)->where($qb->expr()->in(sprintf("MULTI_CONCAT(%s)", $concat), $oids))->getQuery()->getResult();
foreach ($results as $result) {
$hash = $this->buildHashFromResult($metadata, $model, $result, $fields);
if (array_key_exists($hash, $components)) {
$components[$hash]->setData($result);
}
}
}
/**
* Find all objects and return an IterableResult
*
* @return IterableResult
*/
public function findAllIterator()
{
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
return $queryBuilder->select('Thumbnail')->from($this->getEntityClassName(), 'Thumbnail')->getQuery()->iterate();
}
/**
* Find all objects and return an IterableResult
*
* @return IterableResult
*/
public function findAllIterator()
{
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
return $queryBuilder->select('a')->from($this->getEntityClassName(), 'a')->where('a NOT INSTANCE OF TYPO3\\Media\\Domain\\Model\\ImageVariant')->getQuery()->iterate();
}
/**
*
* @access public
* @return \Doctrine\ORM\QueryBuilder
*/
public function getQueryBuilder()
{
return $this->em->createQueryBuilder();
}
/**
* Get paginated results.
*
* @param int $page Current page
* @param int $limit Items per page limit
* @param array $sortby Sorting options
*
* @return \Knp\Component\Pager\Pagination\PaginationInterface Returns a filtered paginator
*/
public function getPaginatedResults(Company $company, $page = 1, $limit = 15, array $sortby = array())
{
$qb = $this->objectManager->createQueryBuilder(self::ENTITY_ALIAS)->select(self::ENTITY_ALIAS)->from(self::ENTITY_CLASS, self::ENTITY_ALIAS)->where(self::ENTITY_ALIAS . '.company = :company_id')->setParameter('company_id', $company->getId());
return $this->getPaginator()->paginate($qb, $page, $limit, $sortby);
}
/**
* Finds all objects by collection name and return an IterableResult
*
* @param string $collectionName
* @return IterableResult
*/
public function findByCollectionNameIterator($collectionName)
{
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->entityManager->createQueryBuilder();
return $queryBuilder->select('PersistentResource')->from($this->getEntityClassName(), 'PersistentResource')->where('PersistentResource.collectionName = :collectionName')->setParameter(':collectionName', $collectionName)->getQuery()->iterate();
}
/**
* @param ObjectManager $entityManager
* @return void
*/
public function injectEntityManager(ObjectManager $entityManager)
{
$this->entityManager = $entityManager;
$this->queryBuilder = $entityManager->createQueryBuilder()->select('e')->from($this->entityClassName, 'e');
}
/**
* @param Directory $id
* @param ObjectManager $em
* @return mixed
*/
private static function getPath(Directory $id, ObjectManager $em)
{
$queryBuilder = $em->createQueryBuilder();
$queryBuilder->select('d.path')->from('AppBundle:Directory', 'd')->where('d.id = ?1')->setParameter(1, $id->getId());
$query = $queryBuilder->getQuery();
unset($queryBuilder);
return $query->getSingleResult();
}
