Example #1
5
 private function _fetchPaginationQueryBuilder($params, $queryBuilder)
 {
     $queryBuilder->setFirstResult($params['iDisplayStart'])->setMaxResults($params['iDisplayLength']);
     if (isset($params['order'])) {
         foreach ($params['order'] as $order) {
             $queryBuilder->addOrderBy($order['sort'], $order['order']);
         }
     }
     $query = $queryBuilder->getQuery()->setHydrationMode(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);
     $paginator = new Doctrine\ORM\Tools\Pagination\Paginator($query);
     $result['data'] = $paginator->getIterator();
     $result['total'] = $paginator->count();
     return $result;
 }
Example #2
4
 public function getList($q)
 {
     $this->checkPrivilege('read');
     if (empty($q)) {
         throw new ApiException\ParameterMissingException();
     }
     $query = Shopware()->Models()->createQuery($q);
     $query->setHydrationMode(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);
     $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
     //total count of result rows
     $totalResult = $paginator->count();
     //item data as array
     $items = $paginator->getIterator()->getArrayCopy();
     return array('data' => $items, 'total' => $totalResult);
 }
 /**
  * @param int $offset
  * @param int $limit
  * @param array $criteria
  * @param array $orderBy
  * @return array
  */
 public function getList($offset = 0, $limit = 25, array $criteria = array(), array $orderBy = array())
 {
     $this->checkPrivilege('read');
     $builder = $this->getRepository()->createQueryBuilder('Country');
     $builder->addFilter($criteria);
     $builder->addOrderBy($orderBy);
     $builder->setFirstResult($offset)->setMaxResults($limit);
     $query = $builder->getQuery();
     $query->setHydrationMode($this->getResultMode());
     $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
     //returns the total count of the query
     $totalResult = $paginator->count();
     //returns the User data
     $users = $paginator->getIterator()->getArrayCopy();
     return array('data' => $users, 'total' => $totalResult);
 }
Example #4
2
 public function getCategories(array $options = [])
 {
     $currentPage = isset($options['page']) ? $options['page'] : 0;
     $pageSize = isset($options['pageSize']) ? $options['pageSize'] : 10;
     $query = $this->qb->select('cat')->from('Api\\Model\\Categories', 'cat')->orderBy('cat.catId', 'DESC');
     $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
     $totalItems = $paginator->count();
     $paginator->getQuery()->setFirstResult($pageSize * $currentPage)->setMaxResults($pageSize)->getArrayResult();
     $list = [];
     foreach ($paginator as $item) {
         $list[] = $item;
     }
     return ['data' => $list, 'totalRecords' => $totalItems];
 }
 /**
  * List all entities.
  *
  * @param Request               $request      The request object.
  * @param ParamFetcherInterface $paramFetcher Param fetcher service.
  * @param string                $fullMode     Serialization mode for managers.
  * @param string                $standardMode Default serialization mode.
  *
  * @return View
  */
 protected function getAllItems(Request $request, ParamFetcherInterface $paramFetcher, $fullMode = null, $standardMode = null)
 {
     $this->checkUserAccess('get_all');
     $offset = $paramFetcher->get('offset');
     $offset = is_null($offset) ? 0 : intval($offset);
     $limit = $paramFetcher->get('limit');
     $orderBy = $paramFetcher->get('orderBy');
     $filterBy = $paramFetcher->get('filterBy');
     $dbAdapter = $this->container->getParameter('nami_core.database_adapter');
     $query = $this->getRepository()->getItems($this->getLoggedUser(), $offset, $limit, $orderBy, $filterBy);
     if ($dbAdapter === 'odm') {
         $count = $query->count();
         $elements = $query->getQuery()->execute();
     } else {
         $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
         $count = $paginator->count();
         $elements = $paginator->getIterator();
     }
     $collection = new PaginatedCollection($elements, $offset, $limit, $count, $request->get('_route'));
     return $this->restView($collection, null, $fullMode, $standardMode);
 }
 /**
  * @param int $offset
  * @param int $limit
  * @param array $criteria
  * @param array $orderBy
  * @return array
  */
 public function getList($offset = 0, $limit = 100, array $criteria = array(), array $orderBy = array())
 {
     $this->checkPrivilege('read');
     $filter = array();
     foreach ($criteria as $key => $value) {
         if (strpos($key, 'price.') === false) {
             $key = "price.{$key}";
         }
         $filter[$key] = $value;
     }
     $builder = $this->queryBuilder();
     $builder->addFilter($filter);
     $builder->addOrderBy($orderBy);
     $builder->setFirstResult($offset)->setMaxResults($limit);
     $query = $builder->getQuery();
     $query->setHydrationMode($this->getResultMode());
     $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
     //returns the total count of the query
     $totalResult = $paginator->count();
     //returns the Price data
     $results = $paginator->getIterator()->getArrayCopy();
     return array('data' => $results, 'offset' => $offset, 'limit' => $limit, 'total' => $totalResult);
 }
Example #7
0
    /**
     * Index action method
     */
    public function indexAction()
    {
        $categoryId = (int)$this->Request()->getQuery('sCategory');
        $sPage = !empty($this->Request()->sPage) ? (int)$this->Request()->sPage : 1;
        $sFilterDate = urldecode($this->Request()->sFilterDate);
        $sFilterAuthor = urldecode($this->Request()->sFilterAuthor);
        $sFilterTags = urldecode($this->Request()->sFilterTags);

        // PerPage
        if (!empty($this->Request()->sPerPage)) {
            Shopware()->Session()->sPerPage = (int)$this->Request()->sPerPage;
        }
        $sPerPage = Shopware()->Session()->sPerPage;
        if (empty($sPerPage)) {
            $sPerPage = (int)Shopware()->Config()->get('sARTICLESPERPAGE');
        }

        $filter = $this->createFilter($sFilterDate, $sFilterAuthor, $sFilterTags);

        // Start for Limit
        $sLimitStart = ($sPage - 1) * $sPerPage;
        $sLimitEnd = $sPerPage;

        //get all blog articles
        $query = $this->getCategoryRepository()->getBlogCategoriesByParentQuery($categoryId);
        $blogCategories = $query->getArrayResult();
        $blogCategoryIds = $this->getBlogCategoryListIds($blogCategories);
        $blogCategoryIds[] = $categoryId;
        $blogArticlesQuery = $this->getRepository()->getListQuery($blogCategoryIds, $sLimitStart, $sLimitEnd, $filter);
        $blogArticlesQuery->setHydrationMode(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);
        $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($blogArticlesQuery);

        //returns the total count of the query
        $totalResult = $paginator->count();

        //returns the blog article data
        $blogArticles = $paginator->getIterator()->getArrayCopy();

        foreach ($blogArticles as $key => $blogArticle) {
            //adding number of comments to the blog article
            $blogArticles[$key]["numberOfComments"] = count($blogArticle["comments"]);

            //adding tags and tag filter links to the blog article
            $tagsQuery = $this->repository->getTagsByBlogId($blogArticle["id"]);
            $tagsData = $tagsQuery->getArrayResult();
            $blogArticles[$key]["tags"] = $this->addLinksToFilter($tagsData, "sFilterTags", "name", false);

            //adding average vote data to the blog article
            $avgVoteQuery = $this->repository->getAverageVoteQuery($blogArticle["id"]);
            $blogArticles[$key]["sVoteAverage"] = $avgVoteQuery->getOneOrNullResult(\Doctrine\ORM\AbstractQuery::HYDRATE_SINGLE_SCALAR);

            //adding thumbnails to the blog article
            if (!empty($blogArticle["media"][0]['mediaId'])) {
                /**@var $mediaModel \Shopware\Models\Media\Media*/
                $mediaModel = Shopware()->Models()->find('Shopware\Models\Media\Media', $blogArticle["media"][0]['mediaId']);
                if ($mediaModel != null) {
                    $blogArticles[$key]["preview"]["thumbNails"] = array_values($mediaModel->getThumbnails());
                }
            }
        }

        //RSS and ATOM Feed part
        if ($this->Request()->getParam('sRss') || $this->Request()->getParam('sAtom')) {
            $this->Response()->setHeader('Content-Type', 'text/xml');
            $type = $this->Request()->getParam('sRss') ? 'rss' : 'atom';
            $this->View()->loadTemplate('frontend/blog/' . $type . '.tpl');
        }

        $categoryContent = Shopware()->Modules()->Categories()->sGetCategoryContent($categoryId);
        $assigningData = array(
            'sBanner' => Shopware()->Modules()->Marketing()->sBanner($categoryId),
            'sBreadcrumb' => $this->getCategoryBreadcrumb($categoryId),
            'sCategoryContent' => $categoryContent,
            'sNumberArticles' => $totalResult,
            'sPage' => $sPage,
            'sFilterDate' => $this->getDateFilterData($blogCategoryIds, $filter),
            'sFilterAuthor' => $this->getAuthorFilterData($blogCategoryIds, $filter),
            'sFilterTags' => $this->getTagsFilterData($blogCategoryIds, $filter),
            'sCategoryInfo' => $categoryContent,
            'sBlogArticles' => $blogArticles
        );

        $this->View()->assign(array_merge($assigningData, $this->getPagerData($totalResult, $sLimitEnd, $sPage, $categoryId)));
    }
Example #8
0
    /**
     * @param int $offset
     * @param int $limit
     * @param array $criteria
     * @param array $orderBy
     * @return array
     */
    public function getList($offset = 0, $limit = 25, array $criteria = array(), array $orderBy = array())
    {
        $this->checkPrivilege('read');

        $builder = $this->getRepository()->createQueryBuilder('orders');

        $builder->addFilter($criteria);
        $builder->addOrderBy($orderBy);
        $builder->setFirstResult($offset)
                ->setMaxResults($limit);

        $query = $builder->getQuery();

        $query->setHydrationMode($this->getResultMode());

        $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);

        //returns the total count of the query
        $totalResult = $paginator->count();

        //returns the order data
        $orders = $paginator->getIterator()->getArrayCopy();

        foreach ($orders as &$order) {
            if (is_array($order)) {
                $order['paymentStatusId'] = $order['cleared'];
                $order['orderStatusId'] = $order['status'];
                unset($order['cleared']);
                unset($order['status']);
            }
        }

        return array('data' => $orders, 'total' => $totalResult);
    }
Example #9
0
    /**
     * @param int $offset
     * @param int $limit
     * @param array $criteria
     * @param array $orderBy
     * @return array
     */
    public function getList($offset = 0, $limit = 25, array $criteria = array(), array $orderBy = array())
    {
        $this->checkPrivilege('read');

        $query = $this->getRepository()->getListQuery($criteria, $orderBy, $limit, $offset);
        $query->setHydrationMode($this->resultMode);

        $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);

        //returns the total count of the query
        $totalResult = $paginator->count();

        //returns the category data
        $categories = $paginator->getIterator()->getArrayCopy();

        return array('data' => $categories, 'total' => $totalResult);
    }
Example #10
0
    /**
     * Event listener function of the article backend module.
     * Will be fired when the user clicks the edit esd-button.
     */
    public function getSerialsAction()
    {
        $esdId = $this->Request()->getParam('esdId');

        $filter = $this->Request()->getParam('filter');
        $sort   = $this->Request()->getParam('sort');
        $start = $this->Request()->getParam('start', 0);
        $limit = $this->Request()->getParam('limit', 20);

        $query = $this->getRepository()->getSerialsByEsdQuery($esdId, $filter, $start, $limit, $sort);
        $query->setHydrationMode(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);

        $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);

        //returns the total count of the query
        $totalResult = $paginator->count();

        //returns the customer data
        $result = $paginator->getIterator()->getArrayCopy();

        $this->View()->assign(array(
            'data' =>  $result,
            'total' => $totalResult,
            'success' => true
        ));
    }
Example #11
0
 /**
  * Returns the total count of the passed query builder.
  *
  * @param \Doctrine\ORM\Query $query
  * @return int|null
  */
 public function getQueryCount(\Doctrine\ORM\Query $query)
 {
     $pagination = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
     return $pagination->count($query);
 }
Example #12
0
    /**
     * The getPlugins function returns an array with the shop plugins.
     */
    private function getPlugins($category = null, $offset = null, $limit = null, $orderBy = null, $filters = null)
    {
        $builder = Shopware()->Models()->createQueryBuilder();
        $builder->select(array('plugins', 'licenses'))
                ->from('Shopware\Models\Plugin\Plugin', 'plugins')
                ->leftJoin('plugins.licenses', 'licenses')
                ->andWhere('plugins.capabilityEnable = 1');

        if ($offset !== null && $limit !== null) {
            $builder->setFirstResult($offset)
                    ->setMaxResults($limit);
        }

        if (!empty($orderBy)) {
            foreach($orderBy as $order) {
                if (empty($order) || empty($order['property'])) {
                    continue;
                }
                $builder->addOrderBy('plugins.' . $order['property'], $order['direction']);
            }
        }

        if (!empty($category)) {
            $builder->andWhere('plugins.source = :source')
                    ->setParameter('source', $category);
        }

        if (!empty($filters)) {
            foreach($filters as $filter) {
                if ($filter['property'] === 'free') {
                    $builder->andWhere(
                        $builder->expr()->orX(
                            'plugins.name LIKE :free',
                            'plugins.label LIKE :free',
                            'plugins.namespace LIKE :free',
                            'plugins.description LIKE :free',
                            'plugins.added LIKE :free',
                            'plugins.installed LIKE :free',
                            'plugins.updated LIKE :free',
                            'plugins.author LIKE :free'
                        )
                    );
                    $builder->setParameter('free', '%' . $filter['value'] . '%');
                } else {
                    $builder->addFilter($filter);
                }
            }
        }

        $query = $builder->getQuery();

        $query->setHydrationMode(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);

        $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);

        //returns the total count of the query
        $total = $paginator->count();

        //returns the customer data
        $plugins = $paginator->getIterator()->getArrayCopy();
        $plugins = $this->setPluginIcons($plugins);

        return array(
            'plugins' => $plugins,
            'total' => $total
        );
    }
Example #13
0
    /**
     * Get a list of existing newslettes
     */
    public function listNewslettersAction() {
        $filter = $this->Request()->getParam('filter', null);
        $sort = $this->Request()->getParam('sort', array(array('property' => 'mailing.date', 'direction' => 'DESC')));
        $limit = $this->Request()->getParam('limit', 10);
        $offset = $this->Request()->getParam('start', 0);

        // Delete old previews
        $results = $this->getPreviewNewslettersQuery()->getResult();
        foreach($results as $model) {
            Shopware()->Models()->remove($model);
        }
        Shopware()->Models()->flush();

        // Get the revenue for the newsletters
        $sql = "SELECT
                partnerID, ROUND(SUM((o.invoice_amount_net-o.invoice_shipping_net)/currencyFactor),2) AS `revenue`
            FROM
                `s_order` as o
            WHERE
                o.status != 4
            AND
                o.status != -1
            AND
                o.partnerID <> ''
            GROUP BY o.partnerID";
        $revenues = Shopware()->Db()->fetchAssoc($sql);


        //get newsletters
        $query = $this->getCampaignsRepository()->getListNewslettersQuery($filter, $sort, $limit, $offset);

        $query->setHydrationMode(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);
        $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);

        //returns the total count of the query
        $totalResult = $paginator->count();

        //returns the customer data
        $result = $paginator->getIterator()->getArrayCopy();

        // join newsletters and corrsponding revenues
        foreach($result as $key => $value){
            // Groups are stored serialized in the database.
            // Here they will be unserialized and flattened in order to match the ExJS RecipientGroup store
            $result[$key]['groups'] = $this->unserializeGroup($result[$key]['groups']);

            $revenue = $revenues['sCampaign'. $value['id']]['revenue'];
            if($revenue !== null) {
                $result[$key]['revenue'] = $revenue;
            }

        }

        $this->View()->assign(array(
            'success' => true,
            'data' => $result,
            'total' => $totalResult,
        ));

    }
 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;
 }
Example #15
0
    /**
     * @param $filter
     * @param $sort
     * @param $offset
     * @param $limit
     * @return array
     */
    protected function getList($filter, $sort, $offset, $limit)
    {
        try {
            if (empty($sort)) {
                $sort = array(array('property' => 'orders.orderTime', 'direction' => 'DESC'));
            } else {
                $sort[0]['property'] = 'orders.' . $sort[0]['property'];
            }

            $query = $this->getRepository()->getOrdersQuery($filter, $sort, $offset, $limit);

            $query->setHydrationMode(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);

            $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);

            //returns the total count of the query
            $total = $paginator->count();

            //returns the customer data
            $orders = $paginator->getIterator()->getArrayCopy();

            foreach($orders as $key => $order) {
                //we need to set the billing and shipping attributes to the first array level to load the data into a form panel
                //same for locale
                $order[0]['billingAttribute'] = $order[0]['billing']['attribute'];
                $order[0]['shippingAttribute'] = $order[0]['shipping']['attribute'];
                $order['locale']= $order['languageSubShop']['locale'];

                unset($order[0]['billing']['attribute']);
                unset($order[0]['shipping']['attribute']);
                $orders[$key] = $order;
            }

            return array(
                'success' => true,
                'data' => $orders,
                'total' => $total
            );
        }
        catch (\Doctrine\ORM\ORMException $e) {
            return array(
                'success' => false,
                'data' => array(),
                'message' => $e->getMessage()
            );
        }
    }