orocrm / crm

     * @return int

     * @throws \Doctrine\ORM\NonUniqueResultException

     */

    public function getReturningCustomersWhoMadeOrderByPeriod(\DateTime $start, \DateTime $end, AclHelper $aclHelper)

    {

        $qb = $this->_em->createQueryBuilder();

        $qb->select('COUNT(customer.id) as val')

            ->from('OroCRMMagentoBundle:Order', 'orders')

            ->join('orders.customer', 'customer')

            ->having('COUNT(orders.id) > 0')

            ->andWhere('customer.createdAt < :dateStart')

            ->andWhere($qb->expr()->between('orders.createdAt', ':dateStart', ':dateEnd'))

            ->setParameter('dateStart', $start)

            ->setParameter('dateEnd', $end);

        $this->applyActiveChannelLimitation($qb);

        $value = $aclHelper->apply($qb)->getOneOrNullResult();

        return $value['val'] ?  : 0;

    }

    /**

     * Returns data grouped by created_at, data_channel_id

     * @param  array     $dateRange

     * @return array     [itemCount, label]

     */

    public function getOpportunitiesByLeadSource(AclHelper $aclHelper, $limit = 10, $dateRange = null)

    {

        $qb   = $this->createQueryBuilder('l')

            ->select('s.id as source, count(o.id) as itemCount')

            ->leftJoin('l.opportunities', 'o')

            ->leftJoin('l.source', 's')

            ->groupBy('source');

        if ($dateRange) {

            $qb->andWhere($qb->expr()->between('o.createdAt', ':dateStart', ':dateEnd'))

                ->setParameter('dateStart', $dateRange['start'])

                ->setParameter('dateEnd', $dateRange['end']);

        }

        $rows = $aclHelper->apply($qb)->getArrayResult();

        return $this->processOpportunitiesByLeadSource($rows, $limit);

    }

    /**

     * @param array $rows