public function getBlocks($startBlockNumber = null, $endBlockNumber = null) { /*{{{*/ $blockList = array(); $whereSet = false; $qb = $this->objectManager->createQueryBuilder(); $qb->select('b')->from('Blockchain\\Entity\\Block', 'b')->orderBy('b.id', 'DESC')->setMaxResults(20); if ($startBlockNumber) { $qb->where($qb->expr()->gte('b.blockNumber', $startBlockNumber)); $whereSet = true; } if ($endBlockNumber) { $expr = $qb->expr()->lte('b.blockNumber', $endBlockNumber); if ($whereSet) { $qb->andWhere($expr); } else { $qb->where($expr); $whereSet = true; } } $query = $qb->getQuery(); $doctrinePaginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query); // the following line prevents doctrine from loading the entire table into a temp table $doctrinePaginator->setUseOutputWalkers(false); $doctrinePaginatorAdapter = new \DoctrineORMModule\Paginator\Adapter\DoctrinePaginator($doctrinePaginator); $paginator = new \Zend\Paginator\Paginator($doctrinePaginatorAdapter); $paginator->setItemCountPerPage(20); $paginator->setPageRange(14); /* if (count($result)) { foreach ($result as $blockEntity) { $blockList[] = array( 'blocknumber' => $blockEntity->getBlockNumber(), 'blockhash' => $blockEntity->getBlockhash(), 'blockhashTruncated' => substr($blockEntity->getBlockhash(), 0, 25).'...', 'time' => $blockEntity->getTime()->format('Y-m-d H:i:s'), 'transactionCount' => $blockEntity->getTransactions()->count(), 'totalBTC' => self::gmpSatoshisToFloatBTC(gmp_init($blockEntity->getTotalvalue())), 'offeredFees' => self::gmpSatoshisToFloatBTC(gmp_init($blockEntity->getOfferedFees())), 'takenFees' => self::gmpSatoshisToFloatBTC(gmp_init($blockEntity->getTakenFees())), 'size' => $blockEntity->getSize() ); } } return $blockList; */ return $paginator; }
public function setupDatatable($columnConfig = null, $filter = null) { $columns = $columnConfig; // Add an empty field to the beginning of columns (e.g. used for checkbox) array_unshift($columns, ['field' => '']); /* @var $qb \Doctrine\DBAL\Query\QueryBuilder */ $qb = $this->getEntityManager()->createQueryBuilder(); $qb->from($this->getEntityName(), static::ALIAS); // Initialize select clause foreach ($columns as $num => $c) { $field = $c['field']; if (!empty($field)) { // When requesting an simple attribute, e.g. an integer or string value like id and description if (!array_key_exists('joinEntity', $c)) { // Add select $qb->addSelect(static::ALIASDOT . $field); } else { // When requesting an entity, e.g. status, customer $entity = $c['joinEntity']; $join = static::ALIASDOT . $entity; $joinResultField = $c['joinResultField']; $jAlias = $entity . 'Alias'; // Add select $qb->addSelect("{$jAlias}.{$joinResultField} as {$entity}_{$joinResultField}_{$num}"); // Check whether join exists already $joinDqlParts = $qb->getDQLParts()['join']; $aliasAlreadyExists = false; /* @var $j \Doctrine\ORM\Query\Expr\Join */ foreach ($joinDqlParts as $joins) { foreach ($joins as $j) { if ($j->getAlias() === $jAlias) { $aliasAlreadyExists = true; break 2; } } } if ($aliasAlreadyExists === false) { // Add join $qb->leftJoin($join, $jAlias); } } } } // Group filter if (isset($_POST['customActionName']) && $_POST['customActionName'] == 'filterByStatus') { $value = $_POST['customActionValue']; if ($value) { $qb->andWhere("statusAlias.id = {$value}"); } } // Program filter if ($filter) { foreach ($filter as $f) { if (isset($f['method']) && $f['method'] == 'or') { $qb->orWhere($f['query']); } else { $qb->andWhere($f); } } } // Datatable Filter if (isset($_POST['filter'])) { $this->_applyFilterCriteriaStrategy($_POST['filter'], $columns, $qb); } if (isset($_POST['order'])) { $col = $_POST['order'][0]['column']; $sort = static::ALIASDOT . $columns[$col]['field']; if (array_key_exists('joinEntity', $columns[$col])) { $sort = $columns[$col]['joinEntity'] . 'Alias' . static::CONNECTOR . $columns[$col]['joinResultField']; } switch ($_POST['order'][0]['dir']) { case 'asc': $qb->orderBy($sort, 'asc'); break; case 'desc': $qb->orderBy($sort, 'desc'); break; } } // Pagination, returns a page of results each time based on above. $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($qb, $fetchJoinCollection = false); $paginator->setUseOutputWalkers(false); // display limited records if (array_key_exists('length', $_POST) && $_POST['length'] > 0) { $paginator->getQuery()->setFirstResult($_POST['start'])->setMaxResults($_POST['length']); } //$start = new \DateTime( date('Y-m-d H:i:s.'.sprintf("%06d",(microtime(true) - floor(microtime(true))) * 1000000), microtime(true)) ); echo '1st: ' . $start->format('d-m-Y h:i:s.u') . "<br>"; // Write own query to count total for efficiency $query = $this->getEntityManager()->createQuery('SELECT COUNT(q.id) FROM ' . $this->getEntityName() . ' q'); $iTotal = $query->getSingleScalarResult(); //$mid = new \DateTime( date('Y-m-d H:i:s.'.sprintf("%06d",(microtime(true) - floor(microtime(true))) * 1000000), microtime(true)) ); echo '2nd: ' . $mid->format('d-m-Y h:i:s.u') . "<br>"; if (empty($qb->getDQLParts()['where'])) { $iFilteredTotal = $iTotal; } else { $iFilteredTotal = $paginator->count(); } $result = array(); foreach ($paginator as $order) { $keys = array_keys($order); $row = array(); for ($i = 0; $i < count($columns); $i++) { if (empty($columns[$i]['field'])) { $row[] = ''; } else { $value = $this->_applyWordingStrategy($value = $order[$keys[$i - 1]], $strategyContainer = $columns[$i]); $row[] = $value; } $row['DT_RowId'] = $order['id']; } $result[] = $row; } $output = array("data" => $result, "draw" => intval($_POST['draw']), "recordsTotal" => $iTotal, "recordsFiltered" => $iFilteredTotal); //$end = new \DateTime( date('Y-m-d H:i:s.'.sprintf("%06d",(microtime(true) - floor(microtime(true))) * 1000000), microtime(true)) ); echo '3rd: ' . $end->format('d-m-Y h:i:s.u'); return $output; }